"""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 (
    JSON,
    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


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(
        JSON().with_variant(JSONB(), "postgresql"), 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 DiscussionItem(Base, TimestampMixin):
    """Something to raise with the client. Deliberately terse: a headline and a
    line of context, so the list stays scannable rather than becoming a document."""

    __tablename__ = "discussion_items"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(120), nullable=False)
    note: Mapped[str | None] = mapped_column(String(400))
    reality: Mapped[str | None] = mapped_column(String(500))
    recommendation: Mapped[str | None] = mapped_column(String(400))
    theme: Mapped[str | None] = mapped_column(String(40))
    needs_decision: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    resolved: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    sort_order: Mapped[int] = mapped_column(Integer, default=100, nullable=False)

    comments: Mapped[list["DiscussionComment"]] = relationship(
        back_populates="item", order_by="DiscussionComment.created_at"
    )


class DiscussionComment(Base, TimestampMixin):
    """A comment left on a discussion item or a feature-board card.

    Exactly one of item_id / feature_key is set (enforced by a check
    constraint): the same thread mechanism serves both surfaces. The site sits
    behind the shared password, so the author is a typed name, not an account.
    That is deliberate: one less hurdle between the client and the reply we
    want from him.
    """

    __tablename__ = "discussion_comments"

    id: Mapped[int] = mapped_column(primary_key=True)
    item_id: Mapped[int | None] = mapped_column(
        ForeignKey("discussion_items.id"), nullable=True, index=True
    )
    feature_key: Mapped[str | None] = mapped_column(String(64), nullable=True, index=True)
    # The typed name stays as the raw record; the id is the account it was written from
    # (the effective one under View As), NULL for a name that was never linked.
    author: Mapped[str] = mapped_column(String(80), nullable=False)
    author_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"), index=True)
    body: Mapped[str] = mapped_column(String(2000), nullable=False)

    item: Mapped[DiscussionItem | None] = relationship(back_populates="comments")


class FeaturePriority(Base, TimestampMixin):
    """The client's demo-scope ranking: one row per feature, latest choice wins.

    Deliberately one row per feature (upsert), not a vote log -- the page shows
    "where does this feature currently stand", and the client can change his
    mind freely. Like comments, the author is a typed name behind the shared
    password, not an account.
    """

    __tablename__ = "feature_priorities"

    id: Mapped[int] = mapped_column(primary_key=True)
    feature_key: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
    priority: Mapped[str] = mapped_column(String(16), nullable=False)  # must | nice | later
    author: Mapped[str] = mapped_column(String(80), nullable=False)
    author_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))


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)
    # Who pointed this row at its house (or chose its name) and when: the alias is a
    # recorded human decision, never a rule's guess (migration #6, Stream M).
    decided_by: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    decided_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

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


class ProductLine(Base, TimestampMixin):
    """The real product above the priced one: "1 Million" over its EDT, Parfum and Elixir
    at every size (migration #6, rian's decision of 12 Sep: one page per real product).

    One row per house and line key (`services/lines.py`, a pure function of the name);
    `name` is the preferred spelling, the most common one at backfill time until a person
    sets it; `slug` is the address the line page will answer at. `canonical_id` points an
    alias line at the one it was folded into (never deleted), and `decided_by` /
    `decided_at` say who and when. A product joins its line through `line_id`; readers
    follow the alias to the canonical.
    """

    __tablename__ = "product_lines"
    __table_args__ = (UniqueConstraint("brand_id", "key", name="uq_product_line_brand_key"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    brand_id: Mapped[int] = mapped_column(ForeignKey("brands.id"), nullable=False, index=True)
    key: Mapped[str] = mapped_column(String(160), nullable=False)
    name: Mapped[str] = mapped_column(String(200), nullable=False)
    slug: Mapped[str] = mapped_column(String(240), unique=True, nullable=False)
    canonical_id: Mapped[int | None] = mapped_column(ForeignKey("product_lines.id"), index=True)
    decided_by: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    decided_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

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


class VariationAlias(Base):
    """One wording of a variation and the canonical one it means, per vertical.

    The variation vocabulary (migration #6): "elixir parfum intense" and "elixir eau de
    parfum intense" are both the Elixir of a line; `display` is the wording shown. Rows
    are seeded by `backfill variations` from the rules in `services/lines.py` and set by
    a person in the merge session (`decided_by`, `decided_at`); a rule never overwrites a
    row a person decided. A vertical with no variations (drinks) has no rows.
    """

    __tablename__ = "variation_aliases"
    __table_args__ = (UniqueConstraint("vertical", "raw", name="uq_variation_alias_vertical_raw"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    vertical: Mapped[str] = mapped_column(String(32), nullable=False)
    raw: Mapped[str] = mapped_column(String(80), nullable=False)
    canonical: Mapped[str] = mapped_column(String(80), nullable=False)
    display: Mapped[str | None] = mapped_column(String(80))
    decided_by: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    decided_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


# 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. "3": the key is
# house | line | variation | size with a form marker (migration #6, Stream M).
IDENTITY_RULES_VERSION = "3"


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.
    # JSONB on Postgres; plain JSON elsewhere, so the identity backfills can be tested on the
    # in-memory SQLite the account suites use (tests/test_line_backfills.py).
    attributes: Mapped[dict] = mapped_column(
        JSON().with_variant(JSONB(), "postgresql"), 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)
    # The line this product is one size and variation of (migration #6); NULL until
    # `backfill lines` has run or for a product with no brand.
    line_id: Mapped[int | None] = mapped_column(ForeignKey("product_lines.id"), index=True)
    # Where the barcode came from: "collected" (a shop published it) or "merge" (it
    # arrived from a row folded into this one), so markup can tell (M7). NULL on rows
    # keyed before migration #6, which all collected theirs.
    gtin_source: Mapped[str | None] = mapped_column(String(16))
    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")
    line: Mapped[ProductLine | None] = relationship(back_populates="products")


class MergeCandidate(Base):
    """Two things the rules suspect are one, queued for a person: two products, two
    brands or two lines (`level`, migration #6).

    `left_id` / `right_id` are the ids at that level; for a product pair the two product
    FKs carry the same ids (kept for the readers written before there were levels, NULL
    on a brand or line pair). Every row carries a `reason` a person can read without the
    code and a `score`; `decided_by` is FK accounts, NULL for the algorithm; `decision`
    is merged | kept_apart | NULL. A rejected pair never resurfaces: `suggest` skips any
    pair that has a decision.
    """

    __tablename__ = "merge_candidates"
    __table_args__ = (
        UniqueConstraint("product_id", "candidate_id", name="uq_merge_candidate_pair"),
        UniqueConstraint("level", "left_id", "right_id", name="uq_merge_candidate_level_pair"),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    level: Mapped[str] = mapped_column(String(16), nullable=False, default="product", server_default="product", index=True)
    left_id: Mapped[int | None] = mapped_column(Integer)
    right_id: Mapped[int | None] = mapped_column(Integer)
    product_id: Mapped[int | None] = mapped_column(ForeignKey("products.id"), index=True)
    candidate_id: Mapped[int | None] = mapped_column(ForeignKey("products.id"), 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(JSON().with_variant(JSONB(), "postgresql"))
    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(JSON().with_variant(JSONB(), "postgresql"))


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(JSON().with_variant(JSONB(), "postgresql"), 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")