from datetime import datetime
from decimal import Decimal
from typing import Any
from uuid import UUID

from sqlalchemy import Boolean, DateTime, ForeignKey, ForeignKeyConstraint, Integer, Numeric, String, Text, UniqueConstraint
from sqlalchemy.dialects.postgresql import JSONB, UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column

from app.persistence.models import Base


class ProductCategoryRecord(Base):
    __tablename__ = "product_categories"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    name: Mapped[str] = mapped_column(String(256), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="ACTIVE")


class BuyingProjectRecord(Base):
    __tablename__ = "buying_projects"
    __table_args__ = (
        ForeignKeyConstraint(
            ["template_id", "template_version"],
            ["product_profiles.id", "product_profiles.version"],
            name="fk_buying_projects_template",
        ),
    )

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    name: Mapped[str] = mapped_column(String(256), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    workspace_id: Mapped[str | None] = mapped_column(String(64))
    product_category_id: Mapped[str] = mapped_column(ForeignKey("product_categories.id"), nullable=False)
    market: Mapped[str] = mapped_column(String(8), nullable=False)
    currency: Mapped[str] = mapped_column(String(3), nullable=False)
    target_budget: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
    template_id: Mapped[str | None] = mapped_column(String(128))
    template_version: Mapped[int | None] = mapped_column(Integer)
    shortlist_size: Mapped[int] = mapped_column(Integer, nullable=False, default=5)
    criteria_version: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    archived_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    @property
    def profile_id(self) -> str | None:
        return self.template_id

    @property
    def profile_version(self) -> int | None:
        return self.template_version


class BuyingProjectCriteriaRecord(Base):
    __tablename__ = "buying_project_criteria"

    project_id: Mapped[UUID] = mapped_column(ForeignKey("buying_projects.id"), primary_key=True)
    version: Mapped[int] = mapped_column(Integer, primary_key=True)
    definition_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class ProjectCriterionRecord(Base):
    __tablename__ = "project_criteria"
    __table_args__ = (
        ForeignKeyConstraint(
            ["project_id", "version"],
            ["buying_project_criteria.project_id", "buying_project_criteria.version"],
            name="fk_project_criteria_version",
        ),
        UniqueConstraint("project_id", "version", "item_id", name="uq_project_criterion_item"),
    )

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    project_id: Mapped[UUID] = mapped_column(nullable=False)
    version: Mapped[int] = mapped_column(Integer, nullable=False)
    item_id: Mapped[str] = mapped_column(String(128), nullable=False)
    criterion_definition_id: Mapped[UUID] = mapped_column(ForeignKey("criterion_definitions.id"), nullable=False)
    criterion_key: Mapped[str | None] = mapped_column(String(128))
    operator: Mapped[str] = mapped_column(String(16), nullable=False)
    value: Mapped[Any] = mapped_column(JSONB)
    enabled_for_discovery: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)

    @property
    def mandatory(self) -> bool:
        return self.enabled_for_discovery

    @property
    def criteria_version(self) -> int:
        return self.version


class SourceCategoryRecord(Base):
    __tablename__ = "source_categories"
    __table_args__ = (UniqueConstraint("source_id", "external_category_key", name="uq_source_category"),)

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    source_id: Mapped[str] = mapped_column(ForeignKey("sources.id"), nullable=False)
    external_category_key: Mapped[str] = mapped_column(String(64), nullable=False)
    name: Mapped[str] = mapped_column(String(256), nullable=False)
    source_url: Mapped[str] = mapped_column(Text, nullable=False)
    product_category_id: Mapped[str | None] = mapped_column(ForeignKey("product_categories.id"))
    canonical_category: Mapped[str | None] = mapped_column(String(128))
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    taxonomy_status: Mapped[str] = mapped_column(String(32), nullable=False, default="NOT_LEARNED")
    last_taxonomy_scan_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    last_verified_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))


class SourceFilterDefinitionRecord(Base):
    __tablename__ = "source_filter_definitions"
    __table_args__ = (UniqueConstraint("source_category_id", "external_filter_key", name="uq_source_filter"),)

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    source_category_id: Mapped[UUID] = mapped_column(ForeignKey("source_categories.id"), nullable=False)
    external_filter_key: Mapped[str] = mapped_column(String(64), nullable=False)
    label: Mapped[str] = mapped_column(String(256), nullable=False)
    group_label: Mapped[str | None] = mapped_column(String(128))
    filter_type: Mapped[str] = mapped_column(String(32), nullable=False)
    canonical_unit: Mapped[str | None] = mapped_column(String(32))
    status: Mapped[str] = mapped_column(String(32), nullable=False)


class SourceFilterOptionRecord(Base):
    __tablename__ = "source_filter_options"
    __table_args__ = (UniqueConstraint("filter_id", "source_value", name="uq_filter_option"),)

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    filter_id: Mapped[UUID] = mapped_column(ForeignKey("source_filter_definitions.id"), nullable=False)
    source_value: Mapped[str] = mapped_column(String(256), nullable=False)
    normalized_value: Mapped[Any] = mapped_column(JSONB)
    operator: Mapped[str | None] = mapped_column(String(16))
    unit: Mapped[str | None] = mapped_column(String(32))


class SourceFilterRangeRecord(Base):
    __tablename__ = "source_filter_ranges"

    filter_id: Mapped[UUID] = mapped_column(ForeignKey("source_filter_definitions.id"), primary_key=True)
    minimum: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
    maximum: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
    step: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
    unit: Mapped[str | None] = mapped_column(String(32))


class CriterionDefinitionRecord(Base):
    __tablename__ = "criterion_definitions"
    __table_args__ = (UniqueConstraint("key", name="uq_criterion_definition_key"),)

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    key: Mapped[str] = mapped_column(String(128), nullable=False)
    origin: Mapped[str] = mapped_column(String(16), nullable=False, default="SOURCE")
    source: Mapped[str | None] = mapped_column(String(64))
    product_category_id: Mapped[str | None] = mapped_column(ForeignKey("product_categories.id"))
    label: Mapped[str] = mapped_column(String(256), nullable=False)
    value_type: Mapped[str] = mapped_column(String(32), nullable=False)
    unit: Mapped[str | None] = mapped_column(String(32))
    semantic: Mapped[str | None] = mapped_column(String(128))
    category: Mapped[str | None] = mapped_column(String(128))
    description: Mapped[str | None] = mapped_column(Text)
    scoring_attribute_key: Mapped[str | None] = mapped_column(String(128))


class SourceAttributeMappingRecord(Base):
    __tablename__ = "source_attribute_mappings"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    source_id: Mapped[str] = mapped_column(ForeignKey("sources.id"), nullable=False)
    source_category_id: Mapped[UUID | None] = mapped_column(ForeignKey("source_categories.id"))
    raw_name: Mapped[str] = mapped_column(String(256), nullable=False)
    criterion_definition_id: Mapped[UUID] = mapped_column(ForeignKey("criterion_definitions.id"), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class CriterionMappingRecord(Base):
    __tablename__ = "criterion_mappings"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    criterion_definition_id: Mapped[UUID] = mapped_column(ForeignKey("criterion_definitions.id"), nullable=False)
    criterion_key: Mapped[str] = mapped_column(String(128), nullable=False)
    source_id: Mapped[str] = mapped_column(ForeignKey("sources.id"), nullable=False)
    source_category_id: Mapped[UUID] = mapped_column(ForeignKey("source_categories.id"), nullable=False)
    external_filter_key: Mapped[str] = mapped_column(String(64), nullable=False)
    source_value: Mapped[str | None] = mapped_column(String(256))
    encoding: Mapped[dict[str, Any]] = mapped_column(JSONB, nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    definition_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class DiscoverySearchRecord(Base):
    __tablename__ = "discovery_searches"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    buying_project_id: Mapped[UUID] = mapped_column(ForeignKey("buying_projects.id"), nullable=False)
    source_id: Mapped[str] = mapped_column(ForeignKey("sources.id"), nullable=False)
    source_category_id: Mapped[UUID] = mapped_column(ForeignKey("source_categories.id"), nullable=False)
    criteria_version: Mapped[int] = mapped_column(Integer, nullable=False)
    mapping_version: Mapped[str] = mapped_column(String(64), nullable=False)
    generated_url: Mapped[str] = mapped_column(Text, nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    schedule_interval_minutes: Mapped[int] = mapped_column(Integer, nullable=False, default=180)
    broad_result_threshold: Mapped[int] = mapped_column(Integer, nullable=False, default=300)
    next_run_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    last_successful_run_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class SearchRunRecord(Base):
    __tablename__ = "search_runs"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    discovery_search_id: Mapped[UUID] = mapped_column(ForeignKey("discovery_searches.id"), nullable=False)
    criteria_version: Mapped[int] = mapped_column(Integer, nullable=False)
    mapping_version: Mapped[str] = mapped_column(String(64), nullable=False)
    generated_url: Mapped[str] = mapped_column(Text, nullable=False)
    started_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    finished_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    result_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    page_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    new_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    changed_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    unchanged_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    missing_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    http_status_summary: Mapped[dict[str, Any]] = mapped_column(JSONB, nullable=False, default=dict)
    error_code: Mapped[str | None] = mapped_column(String(64))


class SourceProductReferenceRecord(Base):
    __tablename__ = "source_product_references"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    source_id: Mapped[str] = mapped_column(ForeignKey("sources.id"), nullable=False)
    source_category_id: Mapped[UUID] = mapped_column(ForeignKey("source_categories.id"), nullable=False)
    source_external_id: Mapped[str | None] = mapped_column(String(256))
    source_url: Mapped[str | None] = mapped_column(Text)
    source_title: Mapped[str] = mapped_column(String(512), nullable=False)
    manufacturer: Mapped[str | None] = mapped_column(String(256))
    model: Mapped[str | None] = mapped_column(String(256))
    manufacturer_sku: Mapped[str | None] = mapped_column(String(128))
    gtin_ean: Mapped[str | None] = mapped_column(String(32))
    normalized_spec_fingerprint: Mapped[str | None] = mapped_column(Text)
    first_seen_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    last_seen_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    lifecycle_status: Mapped[str] = mapped_column(String(32), nullable=False)
    match_status: Mapped[str] = mapped_column(String(32), nullable=False)
    consecutive_missing_success_runs: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    product_variant_id: Mapped[UUID | None] = mapped_column(ForeignKey("product_variants.id"))


class ProductObservationRecord(Base):
    __tablename__ = "product_observations"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    source_product_reference_id: Mapped[UUID] = mapped_column(ForeignKey("source_product_references.id"), nullable=False)
    search_run_id: Mapped[UUID] = mapped_column(ForeignKey("search_runs.id"), nullable=False)
    observed_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    raw_title: Mapped[str] = mapped_column(String(512), nullable=False)
    best_price: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
    currency: Mapped[str | None] = mapped_column(String(3))
    availability: Mapped[str | None] = mapped_column(String(32))
    seller_count: Mapped[int | None] = mapped_column(Integer)


class ProductObservationAttributeRecord(Base):
    __tablename__ = "product_observation_attributes"
    __table_args__ = (UniqueConstraint("observation_id", "attribute_key", name="uq_observation_attribute"),)

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    observation_id: Mapped[UUID] = mapped_column(ForeignKey("product_observations.id"), nullable=False)
    criterion_definition_id: Mapped[UUID | None] = mapped_column(ForeignKey("criterion_definitions.id"))
    attribute_key: Mapped[str] = mapped_column(String(128), nullable=False)
    raw_name: Mapped[str | None] = mapped_column(String(256))
    raw_value: Mapped[Any] = mapped_column(JSONB)
    normalized_value: Mapped[Any] = mapped_column(JSONB)
    unit: Mapped[str | None] = mapped_column(String(32))


class SearchRunItemRecord(Base):
    __tablename__ = "search_run_items"
    __table_args__ = (UniqueConstraint("search_run_id", "source_product_reference_id", name="uq_run_item"),)

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    search_run_id: Mapped[UUID] = mapped_column(ForeignKey("search_runs.id"), nullable=False)
    source_product_reference_id: Mapped[UUID] = mapped_column(ForeignKey("source_product_references.id"), nullable=False)
    observation_id: Mapped[UUID | None] = mapped_column(ForeignKey("product_observations.id"))
    diff_status: Mapped[str] = mapped_column(String(32), nullable=False)


class ProjectCandidateRecord(Base):
    __tablename__ = "project_candidates"
    __table_args__ = (
        UniqueConstraint("buying_project_id", "source_product_reference_id", name="uq_project_candidate"),
    )

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    buying_project_id: Mapped[UUID] = mapped_column(ForeignKey("buying_projects.id"), nullable=False)
    source_product_reference_id: Mapped[UUID] = mapped_column(ForeignKey("source_product_references.id"), nullable=False)
    product_variant_id: Mapped[UUID | None] = mapped_column(ForeignKey("product_variants.id"))
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    match_status: Mapped[str] = mapped_column(String(32), nullable=False)
    product_eligibility: Mapped[str | None] = mapped_column(String(16))
    product_score: Mapped[Decimal | None] = mapped_column(Numeric(6, 2))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class CandidateStatusHistoryRecord(Base):
    __tablename__ = "candidate_status_history"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    candidate_id: Mapped[UUID] = mapped_column(ForeignKey("project_candidates.id"), nullable=False)
    from_status: Mapped[str | None] = mapped_column(String(32))
    to_status: Mapped[str] = mapped_column(String(32), nullable=False)
    reason: Mapped[str | None] = mapped_column(String(128))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)