"""Catalog schema.

Two rules from the project plan shape this file, and both are deliberate:

* A price is an OBSERVATION, not a fact. Duty-free pricing varies by destination,
  loyalty tier and increasingly by traveller, so we never store "the price" -- we
  store a timestamped observation with its context and surface the date in the UI.
* We store facts, not expression. There is intentionally no column for retailer
  marketing copy and no rehosted product imagery.
"""

from datetime import datetime

from sqlalchemy import (
    Boolean,
    DateTime,
    ForeignKey,
    Index,
    func,
    Integer,
    Numeric,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.models.base import Base, TimestampMixin
# Moved to models/discussion.py (Stream R2, 2026-09-11); re-exported so every importer stands.
from app.models.discussion import DiscussionComment, DiscussionItem, FeaturePriority  # noqa: F401


class Account(Base):
    """Who did something: the one identity table, the FK target for every who-column.

    `username` is the kit's key and the `@mention` handle: lowercase, immutable (members,
    grants, sessions and the audit log key on it), NULL only on a row that cannot sign in.
    `email` is the login alias; `display_name` the only editable name. The credential lives
    in `account_credentials` (`app/models/accounts.py`), never here, so no listing over this
    table can carry a hash. Status: active | invited | disabled.
    """

    __tablename__ = "accounts"

    id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str | None] = mapped_column(String(64), unique=True)
    email: Mapped[str | None] = mapped_column(String(320), unique=True)
    display_name: Mapped[str] = mapped_column(String(80), nullable=False)
    status: Mapped[str] = mapped_column(String(12), nullable=False, default="active", server_default="active")
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    last_login_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    disabled_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    invited_by_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))


class Source(Base, TimestampMixin):
    """A collector definition, with the per-source kill switch and politeness caps.

    The kill switch exists so a collector can be stopped in seconds without a
    deploy -- if a retailer ever asks us to stop, we stop that day.
    """

    __tablename__ = "sources"

    id: Mapped[int] = mapped_column(primary_key=True)
    slug: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(160), nullable=False)
    enabled: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    delay_seconds: Mapped[float] = mapped_column(Numeric(5, 2), default=1.0, nullable=False)
    max_concurrency: Mapped[int] = mapped_column(Integer, default=1, nullable=False)
    notes: Mapped[str | None] = mapped_column(Text)
    # How we present ourselves to this host: "declared" is the honest bot UA.
    # "browser_like" is allowed only when permission_record names who agreed,
    # when and how; without the record the collector refuses to switch. A UA
    # change is the ceiling; there is no stealth tier.
    identity_mode: Mapped[str] = mapped_column(
        String(16), default="declared", server_default="declared", nullable=False
    )
    permission_record: Mapped[str | None] = mapped_column(Text)

    runs: Mapped[list["CollectionRun"]] = relationship(back_populates="source")


class CollectionRun(Base):
    __tablename__ = "collection_runs"

    id: Mapped[int] = mapped_column(primary_key=True)
    source_id: Mapped[int] = mapped_column(ForeignKey("sources.id"), 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(24), default="running", nullable=False)
    products_seen: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    prices_written: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    skipped_no_price: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    # The per-reason split of skipped_no_price ({"no_price": 40, "gtin_size_veto":
    # 2}); the total above is kept. Reassigned, never mutated in place, so the
    # session notices the change (migration #2, Stream Q).
    skip_counts: Mapped[dict] = mapped_column(JSONB, default=dict, server_default="{}", nullable=False)
    error: Mapped[str | None] = mapped_column(Text)
    # "live" or "fallback", and when the rates were fetched: a run on the
    # fallback table must stay visibly different from a live one.
    fx_source: Mapped[str | None] = mapped_column(String(16))
    fx_fetched_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    source: Mapped[Source] = relationship(back_populates="runs")


class QuoteSelection(Base, TimestampMixin):
    """The client's yes/no on one quote line item; latest choice wins.

    One row per item (upsert), like FeaturePriority: the quote page shows
    where each line currently stands, and he can change his mind freely.
    """

    __tablename__ = "quote_selections"

    id: Mapped[int] = mapped_column(primary_key=True)
    item_key: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
    included: Mapped[bool] = mapped_column(Boolean, nullable=False)
    author: Mapped[str] = mapped_column(String(80), nullable=False)
    author_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))


class QuoteRequest(Base):
    """A submitted "please quote this" with the basket frozen at that moment.

    Stored rather than emailed so the exact selection survives, whatever is
    toggled afterwards.
    """

    __tablename__ = "quote_requests"

    id: Mapped[int] = mapped_column(primary_key=True)
    author: Mapped[str] = mapped_column(String(80), nullable=False)
    author_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    note: Mapped[str | None] = mapped_column(String(2000))
    items: Mapped[str] = mapped_column(Text, nullable=False)
    total_usd: Mapped[int] = mapped_column(Integer, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class Retailer(Base, TimestampMixin):
    __tablename__ = "retailers"

    id: Mapped[int] = mapped_column(primary_key=True)
    slug: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(160), nullable=False)
    operator: Mapped[str | None] = mapped_column(String(160))
    homepage: Mapped[str | None] = mapped_column(String(400))

    locations: Mapped[list["Location"]] = relationship(back_populates="retailer")


class Location(Base, TimestampMixin):
    """An airport storefront. `iata` is null for a retailer-wide catalogue."""

    __tablename__ = "locations"
    __table_args__ = (UniqueConstraint("retailer_id", "code", name="uq_location_retailer_code"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    retailer_id: Mapped[int] = mapped_column(ForeignKey("retailers.id"), nullable=False)
    code: Mapped[str] = mapped_column(String(32), nullable=False)
    iata: Mapped[str | None] = mapped_column(String(4), index=True)
    name: Mapped[str] = mapped_column(String(160), nullable=False)
    city: Mapped[str | None] = mapped_column(String(120))
    country: Mapped[str | None] = mapped_column(String(80))
    currency: Mapped[str] = mapped_column(String(3), nullable=False)
    is_catalogue_only: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    # Display switch, distinct from Source.enabled: a hidden location keeps
    # collecting (its history accrues) but appears nowhere on the site. This is
    # how coverage is scoped per audience -- demo, launch, full. Off by default
    # (Decision 1): a newly collected airport is switched on deliberately.
    visible: Mapped[bool] = mapped_column(
        Boolean, default=False, server_default="false", nullable=False
    )

    retailer: Mapped[Retailer] = relationship(back_populates="locations")
    listings: Mapped[list["Listing"]] = relationship(back_populates="location")


class Brand(Base, TimestampMixin):
    """One row per brand, whatever the shops call it (migration #3, Decision 6).

    `slug` is the fold key (`normalize.brand_key`, hyphenated): "Moët & Chandon",
    "MOET CHANDON" and "moet-chandon" are one row. `name` is the spelling shown,
    the most common one at fold time until a human sets it. `canonical_id`
    points an alias ("YSL") at the house it belongs to ("Yves Saint Laurent");
    NULL means the row is canonical itself. Products keep their `brand` text as
    collected; `brand_id` is the fold.
    """

    __tablename__ = "brands"

    id: Mapped[int] = mapped_column(primary_key=True)
    slug: Mapped[str] = mapped_column(String(160), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(160), nullable=False)
    canonical_id: Mapped[int | None] = mapped_column(ForeignKey("brands.id"), index=True)

    products: Mapped[list["Product"]] = relationship(back_populates="brand_row")


# The version of the identity rules a product was last resolved under. Bumped
# when match_key semantics or the veto set change; `rederive` recomputes rows
# carrying an older version. "1": brand+name+size key, size veto. "2": the
# per-vertical attribute veto (fragrance concentration) as well.
IDENTITY_RULES_VERSION = "2"


class Product(Base, TimestampMixin):
    """A canonical product, keyed on GTIN where we have one.

    GTIN is what turns cross-retailer matching from fuzzy string comparison into
    a join, so it is the primary identity and everything else is a fallback.
    A merged product stays in the table with `merged_into_id` set (a tombstone
    that forwards, never a delete), so old links and observations keep meaning.
    """

    __tablename__ = "products"

    id: Mapped[int] = mapped_column(primary_key=True)
    gtin: Mapped[str | None] = mapped_column(String(20), unique=True, index=True)
    match_key: Mapped[str] = mapped_column(String(255), index=True, nullable=False)
    brand: Mapped[str | None] = mapped_column(String(160), index=True)
    brand_id: Mapped[int | None] = mapped_column(ForeignKey("brands.id"), index=True)
    name: Mapped[str] = mapped_column(String(400), nullable=False)
    vertical: Mapped[str] = mapped_column(String(32), default="liquor", nullable=False, index=True)
    category: Mapped[str | None] = mapped_column(String(80), index=True)
    # The size as the shop states it (70 cl, 100 ml, 75 g); size_ml is derived
    # from it for volumes and stays the comparable figure.
    size_value: Mapped[float | None] = mapped_column(Numeric(9, 2))
    size_unit: Mapped[str | None] = mapped_column(String(8))
    size_ml: Mapped[int | None] = mapped_column(Integer)
    abv: Mapped[float | None] = mapped_column(Numeric(5, 2))
    # Per-vertical facts that VETO a fallback match and never key it (Decision
    # 6): {"concentration": "edp"} for a fragrance. A missing value never splits
    # a product; two declared values that differ never join one.
    attributes: Mapped[dict] = mapped_column(JSONB, default=dict, server_default="{}", nullable=False)
    identity_rules_version: Mapped[str] = mapped_column(
        String(16), default=IDENTITY_RULES_VERSION, server_default="1", nullable=False
    )
    merged_into_id: Mapped[int | None] = mapped_column(ForeignKey("products.id"), index=True)
    country_of_origin: Mapped[str | None] = mapped_column(String(80))
    is_exclusive: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    # Imagery comes from an openly licensed source keyed on the barcode, never
    # from retailer photography. image_checked records that we looked, so a
    # product with no picture is not retried on every run.
    image_url: Mapped[str | None] = mapped_column(String(600))
    thumb_url: Mapped[str | None] = mapped_column(String(600))
    image_source: Mapped[str | None] = mapped_column(String(80))
    image_checked: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)

    listings: Mapped[list["Listing"]] = relationship(back_populates="product")
    awards: Mapped[list["Award"]] = relationship(back_populates="product")
    brand_row: Mapped[Brand | None] = relationship(back_populates="products")


class MergeCandidate(Base):
    """Two products the rules suspect are one, queued for a decision.

    Detected by the audit or the fold backfill; a human (or the algorithm, for
    the launch-scale duplicate groups) decides. `decided_by` is FK accounts,
    NULL for the algorithm; `decision` is merged | kept_apart | NULL.
    """

    __tablename__ = "merge_candidates"
    __table_args__ = (UniqueConstraint("product_id", "candidate_id", name="uq_merge_candidate_pair"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    product_id: Mapped[int] = mapped_column(ForeignKey("products.id"), nullable=False, index=True)
    candidate_id: Mapped[int] = mapped_column(ForeignKey("products.id"), nullable=False, index=True)
    reason: Mapped[str] = mapped_column(String(40), nullable=False)
    score: Mapped[float | None] = mapped_column(Numeric(5, 3))
    detail: Mapped[dict | None] = mapped_column(JSONB)
    detected_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    decided_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    decided_by: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    decision: Mapped[str | None] = mapped_column(String(16))


class ProductMerge(Base):
    """The record of one merge: `from_id` now forwards to `to_id`.

    Written by `backfill merges` and by any later human merge; never deleted,
    so a merge can be read back and undone by hand. `merged_by` is FK accounts
    (NULL when the algorithm did it); `detail` keeps both sides as they were.
    """

    __tablename__ = "product_merges"

    id: Mapped[int] = mapped_column(primary_key=True)
    from_id: Mapped[int] = mapped_column(ForeignKey("products.id"), nullable=False, index=True)
    to_id: Mapped[int] = mapped_column(ForeignKey("products.id"), nullable=False, index=True)
    merged_by: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    merged_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    reason: Mapped[str] = mapped_column(String(40), nullable=False, default="duplicate", server_default="duplicate")
    detail: Mapped[dict | None] = mapped_column(JSONB)


class Reverification(Base):
    """A product or listing queued for a fresh read because something disagreed.

    Decision 7: disagreements are scored and queued, never silently resolved.
    Columns only for now (§10 #1 keeps the columns, defers the scorer); the
    queue is worked by `app.cli verify` once it reads from here.
    """

    __tablename__ = "reverifications"

    id: Mapped[int] = mapped_column(primary_key=True)
    product_id: Mapped[int | None] = mapped_column(ForeignKey("products.id"), index=True)
    listing_id: Mapped[int | None] = mapped_column(ForeignKey("listings.id"), index=True)
    reason: Mapped[str] = mapped_column(String(40), nullable=False)
    score: Mapped[float | None] = mapped_column(Numeric(6, 3))
    queued_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    done_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    result: Mapped[str | None] = mapped_column(String(24))
    detail: Mapped[dict | None] = mapped_column(JSONB)


class Listing(Base, TimestampMixin):
    """One product as carried by one location."""

    __tablename__ = "listings"
    __table_args__ = (
        UniqueConstraint("location_id", "source_sku", name="uq_listing_location_sku"),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    product_id: Mapped[int] = mapped_column(ForeignKey("products.id"), nullable=False, index=True)
    location_id: Mapped[int] = mapped_column(ForeignKey("locations.id"), nullable=False, index=True)
    source_sku: Mapped[str] = mapped_column(String(96), nullable=False)
    url: Mapped[str | None] = mapped_column(String(600))

    product: Mapped[Product] = relationship(back_populates="listings")
    location: Mapped[Location] = relationship(back_populates="listings")
    observations: Mapped[list["PriceObservation"]] = relationship(back_populates="listing")
    raw_records: Mapped[list["RawRecord"]] = relationship(back_populates="listing")


class RawRecord(Base):
    """The parsed fragment a listing was derived from, kept per run.

    The tile, the JSON-LD offer, the API item: what the collector actually
    saw, not the page. Every derived value can then be re-run over it when a
    rule changes (`app.cli rederive`), instead of re-fetching. parser_version
    names the collector rules that read it.
    """

    __tablename__ = "raw_records"

    id: Mapped[int] = mapped_column(primary_key=True)
    listing_id: Mapped[int] = mapped_column(ForeignKey("listings.id"), nullable=False, index=True)
    run_id: Mapped[int | None] = mapped_column(ForeignKey("collection_runs.id"), index=True)
    payload: Mapped[dict] = mapped_column(JSONB, nullable=False)
    parser_version: Mapped[str] = mapped_column(String(40), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )

    listing: Mapped[Listing] = relationship(back_populates="raw_records")


class PriceObservation(Base):
    """A price seen at a point in time, with the context it was seen under."""

    __tablename__ = "price_observations"
    __table_args__ = (Index("ix_obs_listing_time", "listing_id", "observed_at"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    listing_id: Mapped[int] = mapped_column(ForeignKey("listings.id"), nullable=False)
    price: Mapped[float] = mapped_column(Numeric(12, 2), nullable=False)
    currency: Mapped[str] = mapped_column(String(3), nullable=False)
    price_usd: Mapped[float | None] = mapped_column(Numeric(12, 2))
    was_price: Mapped[float | None] = mapped_column(Numeric(12, 2))
    price_type: Mapped[str] = mapped_column(String(24), default="list", nullable=False)
    in_stock: Mapped[bool | None] = mapped_column(Boolean)
    observed_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    run_id: Mapped[int | None] = mapped_column(ForeignKey("collection_runs.id"))
    # collector | browser | human. A hand-entered or browser-rendered price is
    # never mistaken for a collected one, and the back office can mark it stale.
    source_kind: Mapped[str] = mapped_column(
        String(16), default="collector", server_default="collector", nullable=False
    )
    # Units of `currency` per USD used to derive price_usd, so the derivation
    # is re-doable and a fallback-rate figure can be found and redone.
    fx_rate: Mapped[float | None] = mapped_column(Numeric(14, 6))

    listing: Mapped[Listing] = relationship(back_populates="observations")


class Award(Base, TimestampMixin):
    """A competition medal attached to a product.

    Generic by design: populated today from the existing competition network and
    from awards the retailers themselves publish; a new competition slots in
    with no schema change.
    """

    __tablename__ = "awards"
    __table_args__ = (
        UniqueConstraint("product_id", "competition", "year", name="uq_award_product_comp_year"),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    product_id: Mapped[int] = mapped_column(ForeignKey("products.id"), nullable=False, index=True)
    competition: Mapped[str] = mapped_column(String(160), nullable=False)
    competition_slug: Mapped[str | None] = mapped_column(String(64), index=True)
    year: Mapped[int | None] = mapped_column(Integer)
    medal: Mapped[str | None] = mapped_column(String(64))
    score: Mapped[int | None] = mapped_column(Integer)
    is_own_competition: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    source: Mapped[str | None] = mapped_column(String(64))

    product: Mapped[Product] = relationship(back_populates="awards")