from __future__ import annotations

from datetime import datetime
from decimal import Decimal
from typing import Any

from sqlalchemy import (
    BigInteger,
    Boolean,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    JSON,
    Numeric,
    String,
    Text,
    UniqueConstraint,
    func,
)
from sqlalchemy.orm import Mapped, mapped_column

from supply_infra.db.base import Base


class EvidencePackage(Base):
    """Immutable evidence snapshot owned by one durable pipeline run."""

    __tablename__ = "evidence_package"
    __table_args__ = (
        UniqueConstraint("run_id", name="uk_evidence_package_run"),
        Index("idx_evidence_package_biz", "biz_dt", "created_at"),
    )

    package_id: Mapped[str] = mapped_column(String(36), primary_key=True)
    run_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("pipeline_run.run_id", ondelete="RESTRICT"),
        nullable=False,
    )
    biz_dt: Mapped[str] = mapped_column(String(8), nullable=False)
    package_version: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    status: Mapped[str] = mapped_column(
        String(24), nullable=False, default="frozen"
    )
    source_snapshot_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    source_versions_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    evidence_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )


class RawDemandExpression(Base):
    """Unmodified source expression captured inside an evidence package."""

    __tablename__ = "raw_demand_expression"
    __table_args__ = (
        UniqueConstraint(
            "package_id",
            "source_type",
            "source_record_id",
            name="uk_raw_expression_source",
        ),
        Index("idx_raw_expression_package", "package_id"),
        Index("idx_raw_expression_hash", "content_hash"),
    )

    expression_id: Mapped[str] = mapped_column(String(36), primary_key=True)
    package_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("evidence_package.package_id", ondelete="RESTRICT"),
        nullable=False,
    )
    source_type: Mapped[str] = mapped_column(String(64), nullable=False)
    source_record_id: Mapped[str] = mapped_column(String(128), nullable=False)
    raw_text: Mapped[str] = mapped_column(String(512), nullable=False)
    original_payload_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    content_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    observed_biz_dt: Mapped[str] = mapped_column(String(8), nullable=False)
    data_quality: Mapped[str] = mapped_column(
        String(24), nullable=False, default="available"
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )


class StandardDemandTerm(Base):
    """Canonical term with stable identity across daily runs."""

    __tablename__ = "standard_demand_term"
    __table_args__ = (
        UniqueConstraint("canonical_key", name="uk_standard_demand_term_key"),
        Index("idx_standard_demand_term_text", "normalized_text"),
    )

    term_id: Mapped[str] = mapped_column(String(36), primary_key=True)
    canonical_key: Mapped[str] = mapped_column(String(64), nullable=False)
    canonical_text: Mapped[str] = mapped_column(String(256), nullable=False)
    normalized_text: Mapped[str] = mapped_column(String(256), nullable=False)
    status: Mapped[str] = mapped_column(
        String(24), nullable=False, default="active"
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now(), onupdate=func.now()
    )


class StandardDemandAlias(Base):
    """Traceable relationship from a canonical term to a raw expression."""

    __tablename__ = "standard_demand_alias"
    __table_args__ = (
        UniqueConstraint(
            "term_id",
            "expression_id",
            name="uk_standard_demand_alias_expression",
        ),
        Index("idx_standard_demand_alias_term", "term_id"),
    )

    alias_id: Mapped[str] = mapped_column(String(36), primary_key=True)
    term_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("standard_demand_term.term_id", ondelete="RESTRICT"),
        nullable=False,
    )
    expression_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("raw_demand_expression.expression_id", ondelete="RESTRICT"),
        nullable=False,
    )
    alias_text: Mapped[str] = mapped_column(String(512), nullable=False)
    relation_type: Mapped[str] = mapped_column(
        String(32), nullable=False, default="source_expression"
    )
    confidence: Mapped[Decimal] = mapped_column(
        Numeric(6, 5), nullable=False, default=1
    )
    reason: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )


class PlatformDemand(Base):
    """Stable business object consumed by scoring, people and agents."""

    __tablename__ = "platform_demand"
    __table_args__ = (
        UniqueConstraint("canonical_key", name="uk_platform_demand_key"),
        Index("idx_platform_demand_name", "name"),
        Index("idx_platform_demand_status", "status"),
    )

    platform_demand_id: Mapped[str] = mapped_column(String(36), primary_key=True)
    canonical_key: Mapped[str] = mapped_column(String(64), nullable=False)
    name: Mapped[str] = mapped_column(String(256), nullable=False)
    description: Mapped[str] = mapped_column(Text, nullable=False)
    status: Mapped[str] = mapped_column(
        String(24), nullable=False, default="active"
    )
    lifecycle_state: Mapped[str] = mapped_column(
        String(24), nullable=False, default="new"
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now(), onupdate=func.now()
    )


class PlatformDemandVersion(Base):
    """Append-only daily cognition version for a platform demand."""

    __tablename__ = "platform_demand_version"
    __table_args__ = (
        UniqueConstraint(
            "platform_demand_id",
            "version_no",
            name="uk_platform_demand_version_no",
        ),
        UniqueConstraint(
            "platform_demand_id",
            "run_id",
            name="uk_platform_demand_version_run",
        ),
        Index("idx_platform_demand_version_biz", "biz_dt", "run_id"),
    )

    platform_demand_version_id: Mapped[str] = mapped_column(
        String(36), primary_key=True
    )
    platform_demand_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("platform_demand.platform_demand_id", ondelete="RESTRICT"),
        nullable=False,
    )
    term_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("standard_demand_term.term_id", ondelete="RESTRICT"),
        nullable=False,
    )
    package_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("evidence_package.package_id", ondelete="RESTRICT"),
        nullable=False,
    )
    run_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("pipeline_run.run_id", ondelete="RESTRICT"),
        nullable=False,
    )
    source_demand_grade_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    version_no: Mapped[int] = mapped_column(Integer, nullable=False)
    biz_dt: Mapped[str] = mapped_column(String(8), nullable=False)
    name: Mapped[str] = mapped_column(String(256), nullable=False)
    description: Mapped[str] = mapped_column(Text, nullable=False)
    cognition_confidence: Mapped[Decimal] = mapped_column(
        Numeric(6, 5), nullable=False
    )
    reason: Mapped[str] = mapped_column(Text, nullable=False)
    change_type: Mapped[str] = mapped_column(String(24), nullable=False)
    evidence_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )


class PlatformDemandCategoryRel(Base):
    """Versioned graph edge from one platform-demand version to the tree."""

    __tablename__ = "platform_demand_category_rel"
    __table_args__ = (
        UniqueConstraint(
            "platform_demand_version_id",
            "category_id",
            "relation_type",
            name="uk_platform_demand_category_rel",
        ),
        Index("idx_platform_demand_category_node", "category_id"),
    )

    rel_id: Mapped[str] = mapped_column(String(36), primary_key=True)
    platform_demand_version_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey(
            "platform_demand_version.platform_demand_version_id",
            ondelete="RESTRICT",
        ),
        nullable=False,
    )
    category_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    relation_type: Mapped[str] = mapped_column(String(32), nullable=False)
    relation_source: Mapped[str] = mapped_column(String(64), nullable=False)
    reason: Mapped[str] = mapped_column(Text, nullable=False)
    confidence: Mapped[Decimal] = mapped_column(
        Numeric(6, 5), nullable=False
    )
    is_inferred: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False
    )
    status: Mapped[str] = mapped_column(
        String(24), nullable=False, default="active"
    )
    valid_from_biz_dt: Mapped[str] = mapped_column(String(8), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )


class StrategyVersion(Base):
    """Versioned scoring and allocation policy; published versions are immutable."""

    __tablename__ = "strategy_version"
    __table_args__ = (
        UniqueConstraint("version_key", name="uk_strategy_version_key"),
        Index("idx_strategy_version_status", "status", "activated_at"),
    )

    strategy_version_id: Mapped[str] = mapped_column(String(36), primary_key=True)
    version_key: Mapped[str] = mapped_column(String(64), nullable=False)
    status: Mapped[str] = mapped_column(String(24), nullable=False)
    definition_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    change_reason: Mapped[str] = mapped_column(Text, nullable=False)
    created_by: Mapped[str] = mapped_column(String(128), nullable=False)
    activated_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )


class DemandEvaluationSnapshot(Base):
    """Two reproducible 0-1 decisions for one platform-demand version."""

    __tablename__ = "demand_evaluation_snapshot"
    __table_args__ = (
        UniqueConstraint(
            "platform_demand_version_id",
            name="uk_demand_evaluation_version",
        ),
        Index("idx_demand_evaluation_biz", "biz_dt", "action_tier"),
    )

    evaluation_id: Mapped[str] = mapped_column(String(36), primary_key=True)
    platform_demand_version_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey(
            "platform_demand_version.platform_demand_version_id",
            ondelete="RESTRICT",
        ),
        nullable=False,
    )
    run_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("pipeline_run.run_id", ondelete="RESTRICT"),
        nullable=False,
    )
    strategy_version_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("strategy_version.strategy_version_id", ondelete="RESTRICT"),
        nullable=False,
    )
    biz_dt: Mapped[str] = mapped_column(String(8), nullable=False)
    validity_score: Mapped[Decimal] = mapped_column(Numeric(6, 5), nullable=False)
    local_supply_priority: Mapped[Decimal] = mapped_column(
        Numeric(6, 5), nullable=False
    )
    data_confidence: Mapped[Decimal] = mapped_column(Numeric(6, 5), nullable=False)
    posterior_state: Mapped[str] = mapped_column(String(24), nullable=False)
    action_tier: Mapped[str] = mapped_column(String(32), nullable=False)
    metrics_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    missing_dimensions_json: Mapped[list[str]] = mapped_column(JSON, nullable=False)
    decision_reason: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )


class DemandScoreContribution(Base):
    """Expandable score contribution; contributions sum to the stored score."""

    __tablename__ = "demand_score_contribution"
    __table_args__ = (
        UniqueConstraint(
            "evaluation_id",
            "score_type",
            "dimension",
            name="uk_demand_score_contribution",
        ),
        Index("idx_demand_score_contribution_eval", "evaluation_id"),
    )

    contribution_id: Mapped[str] = mapped_column(String(36), primary_key=True)
    evaluation_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("demand_evaluation_snapshot.evaluation_id", ondelete="RESTRICT"),
        nullable=False,
    )
    score_type: Mapped[str] = mapped_column(String(24), nullable=False)
    dimension: Mapped[str] = mapped_column(String(64), nullable=False)
    raw_value_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    normalized_value: Mapped[Decimal] = mapped_column(
        Numeric(8, 7), nullable=False
    )
    configured_weight: Mapped[Decimal] = mapped_column(
        Numeric(8, 7), nullable=False
    )
    effective_weight: Mapped[Decimal] = mapped_column(
        Numeric(8, 7), nullable=False
    )
    contribution: Mapped[Decimal] = mapped_column(Numeric(8, 7), nullable=False)
    data_status: Mapped[str] = mapped_column(String(24), nullable=False)
    reason: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )


class DailyDemandPackage(Base):
    """The only published daily contract shared by people and agents."""

    __tablename__ = "daily_demand_package"
    __table_args__ = (
        UniqueConstraint("run_id", name="uk_daily_demand_package_run"),
        Index("idx_daily_demand_package_biz", "biz_dt", "status"),
    )

    demand_package_id: Mapped[str] = mapped_column(String(36), primary_key=True)
    run_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("pipeline_run.run_id", ondelete="RESTRICT"),
        nullable=False,
    )
    evidence_package_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("evidence_package.package_id", ondelete="RESTRICT"),
        nullable=False,
    )
    strategy_version_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("strategy_version.strategy_version_id", ondelete="RESTRICT"),
        nullable=False,
    )
    biz_dt: Mapped[str] = mapped_column(String(8), nullable=False)
    package_version: Mapped[int] = mapped_column(Integer, nullable=False)
    status: Mapped[str] = mapped_column(String(24), nullable=False)
    content_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    item_count: Mapped[int] = mapped_column(Integer, nullable=False)
    published_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )


class DailyDemandTask(Base):
    """Immutable agent/human task view for one evaluated demand."""

    __tablename__ = "daily_demand_task"
    __table_args__ = (
        UniqueConstraint(
            "demand_package_id",
            "platform_demand_version_id",
            name="uk_daily_demand_task_version",
        ),
        Index("idx_daily_demand_task_tier", "demand_package_id", "action_tier"),
    )

    task_id: Mapped[str] = mapped_column(String(36), primary_key=True)
    demand_package_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("daily_demand_package.demand_package_id", ondelete="RESTRICT"),
        nullable=False,
    )
    platform_demand_version_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey(
            "platform_demand_version.platform_demand_version_id",
            ondelete="RESTRICT",
        ),
        nullable=False,
    )
    evaluation_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("demand_evaluation_snapshot.evaluation_id", ondelete="RESTRICT"),
        nullable=False,
    )
    action_tier: Mapped[str] = mapped_column(String(32), nullable=False)
    search_terms_json: Mapped[list[str]] = mapped_column(JSON, nullable=False)
    exclude_terms_json: Mapped[list[str]] = mapped_column(JSON, nullable=False)
    hit_rules_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    hypotheses_json: Mapped[list[str]] = mapped_column(JSON, nullable=False)
    evidence_gaps_json: Mapped[list[str]] = mapped_column(JSON, nullable=False)
    existing_content_json: Mapped[list[str]] = mapped_column(JSON, nullable=False)
    allocation_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    task_payload_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )


class ContentDemandCoverage(Base):
    """Many-to-many content coverage edge linked to a published task."""

    __tablename__ = "content_demand_coverage"
    __table_args__ = (
        UniqueConstraint(
            "platform_demand_version_id",
            "content_id",
            "coverage_role",
            name="uk_content_demand_coverage",
        ),
        Index("idx_content_demand_coverage_content", "content_id"),
    )

    coverage_id: Mapped[str] = mapped_column(String(36), primary_key=True)
    platform_demand_version_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey(
            "platform_demand_version.platform_demand_version_id",
            ondelete="RESTRICT",
        ),
        nullable=False,
    )
    task_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("daily_demand_task.task_id", ondelete="RESTRICT"),
        nullable=False,
    )
    content_id: Mapped[str] = mapped_column(String(128), nullable=False)
    content_url: Mapped[str | None] = mapped_column(String(1024), nullable=True)
    source_type: Mapped[str] = mapped_column(String(32), nullable=False)
    coverage_role: Mapped[str] = mapped_column(String(24), nullable=False)
    coverage_score: Mapped[Decimal] = mapped_column(Numeric(6, 5), nullable=False)
    evidence_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    status: Mapped[str] = mapped_column(String(24), nullable=False)
    discovered_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    content_published_at: Mapped[datetime | None] = mapped_column(
        DateTime, nullable=True
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )


class ContentPerformanceFact(Base):
    """Immutable raw content performance observation."""

    __tablename__ = "content_performance_fact"
    __table_args__ = (
        UniqueConstraint(
            "content_id",
            "biz_dt",
            "source",
            "payload_hash",
            name="uk_content_performance_fact",
        ),
        Index("idx_content_performance_fact_content", "content_id", "biz_dt"),
    )

    performance_fact_id: Mapped[str] = mapped_column(String(36), primary_key=True)
    content_id: Mapped[str] = mapped_column(String(128), nullable=False)
    biz_dt: Mapped[str] = mapped_column(String(8), nullable=False)
    source: Mapped[str] = mapped_column(String(64), nullable=False)
    payload_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    exposure_count: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    sample_size: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    content_quality: Mapped[Decimal | None] = mapped_column(
        Numeric(6, 5), nullable=True
    )
    rov: Mapped[Decimal | None] = mapped_column(Numeric(16, 8), nullable=True)
    vov: Mapped[Decimal | None] = mapped_column(Numeric(16, 8), nullable=True)
    raw_payload_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    observed_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )


class DemandAttributionSnapshot(Base):
    """Reproducible attribution from one content fact to one demand version."""

    __tablename__ = "demand_attribution_snapshot"
    __table_args__ = (
        UniqueConstraint(
            "coverage_id",
            "performance_fact_id",
            "strategy_version_id",
            name="uk_demand_attribution_snapshot",
        ),
        Index(
            "idx_demand_attribution_version",
            "platform_demand_version_id",
            "created_at",
        ),
    )

    attribution_id: Mapped[str] = mapped_column(String(36), primary_key=True)
    platform_demand_version_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey(
            "platform_demand_version.platform_demand_version_id",
            ondelete="RESTRICT",
        ),
        nullable=False,
    )
    coverage_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("content_demand_coverage.coverage_id", ondelete="RESTRICT"),
        nullable=False,
    )
    performance_fact_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey(
            "content_performance_fact.performance_fact_id",
            ondelete="RESTRICT",
        ),
        nullable=False,
    )
    strategy_version_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("strategy_version.strategy_version_id", ondelete="RESTRICT"),
        nullable=False,
    )
    support_state: Mapped[str] = mapped_column(String(24), nullable=False)
    attributable_rov: Mapped[Decimal | None] = mapped_column(
        Numeric(16, 8), nullable=True
    )
    attributable_vov: Mapped[Decimal | None] = mapped_column(
        Numeric(16, 8), nullable=True
    )
    attribution_confidence: Mapped[Decimal] = mapped_column(
        Numeric(6, 5), nullable=False
    )
    validity_influence: Mapped[Decimal] = mapped_column(
        Numeric(8, 7), nullable=False
    )
    priority_influence: Mapped[Decimal] = mapped_column(
        Numeric(8, 7), nullable=False
    )
    reason: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )


class StrategyChangeProposal(Base):
    """Draft/preview/approval workflow for changing business strategy."""

    __tablename__ = "strategy_change_proposal"
    __table_args__ = (Index("idx_strategy_proposal_status", "status", "created_at"),)

    proposal_id: Mapped[str] = mapped_column(String(36), primary_key=True)
    base_strategy_version_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("strategy_version.strategy_version_id", ondelete="RESTRICT"),
        nullable=False,
    )
    status: Mapped[str] = mapped_column(String(24), nullable=False)
    requested_change_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    proposed_definition_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    diff_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    preview_json: Mapped[dict[str, Any] | None] = mapped_column(JSON, nullable=True)
    reason: Mapped[str] = mapped_column(Text, nullable=False)
    created_by: Mapped[str] = mapped_column(String(128), nullable=False)
    approved_by: Mapped[str | None] = mapped_column(String(128), nullable=True)
    approved_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()
    )