"""What verification and the audit record: runs, checks, snapshots, refusals.

Sources of truth: this module and alembic/versions/d1e2f3a4b5c6_verification_and_audit.py
(migration #2, Stream Q). The rules the rows encode are in docs/QUALITY.md.

Four tables, one idea: every decision that measures, removes or refuses data
is stored, never only logged, so the sampler and the human review can see it.

* A verification run re-reads N published listings through the same parser
  and records one check per listing with what the page said. A check is a
  fact about one moment; the verdict is the comparison.
* A correctness failure (MISMATCH_*, PARSE_FAIL) blocks publication of its
  source until a human clears it (Decision 10): the block is not a flag on
  the source but the existence of an uncleared failing check, so clearing one
  is itself recorded with who and when.
* An audit snapshot is the whole `audit` document, so a threshold change can
  be read against what was measured before it.
* A rejected observation is a listing ingest or a collector refused to
  publish, with the raw payload, so "why is this bottle missing" has an
  answer without a re-fetch.
"""

from datetime import datetime

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

from app.models.base import Base

# Verdicts a check can carry. Correctness failures block publication of the
# source; the rest are reported. REVIEW needs no network: it is a re-read
# price outside the plausible band, for a human to look at.
CORRECTNESS_FAILURES = ("MISMATCH_SIZE", "MISMATCH_IDENTITY", "MISMATCH_CURRENCY", "PARSE_FAIL")
VERDICTS = (
    "PASS", "PRICE_MOVED", *CORRECTNESS_FAILURES, "GONE", "BLOCKED", "REVIEW",
)


class VerificationRun(Base):
    __tablename__ = "verification_runs"

    id: Mapped[int] = mapped_column(primary_key=True)
    started_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    finished_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    # The sampling seed, so a run's sample can be re-drawn exactly.
    seed: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")
    # The N asked for; the checks table holds what was actually read.
    n: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")
    # {slug: {"checked": 20, "PASS": 18, "PRICE_MOVED": 2, "blocked": false, ...}}
    per_source: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict, server_default="{}")
    # on_demand | after_collection | weekly. Reporting only; the rules are the same.
    mode: Mapped[str] = mapped_column(String(24), nullable=False, default="on_demand", server_default="on_demand")

    checks: Mapped[list["VerificationCheck"]] = relationship(back_populates="run")


class VerificationCheck(Base):
    __tablename__ = "verification_checks"

    id: Mapped[int] = mapped_column(primary_key=True)
    run_id: Mapped[int] = mapped_column(ForeignKey("verification_runs.id"), nullable=False, index=True)
    listing_id: Mapped[int] = mapped_column(ForeignKey("listings.id"), nullable=False, index=True)
    # The observation the live read was compared against (the latest at the time).
    observation_id: Mapped[int | None] = mapped_column(ForeignKey("price_observations.id"))
    # Denormalised from the observation's run so "is this source blocked" is one query.
    source_id: Mapped[int | None] = mapped_column(ForeignKey("sources.id"), index=True)
    live_price: Mapped[float | None] = mapped_column(Numeric(12, 2))
    live_currency: Mapped[str | None] = mapped_column(String(3))
    live_size_ml: Mapped[int | None] = mapped_column(Integer)
    live_name: Mapped[str | None] = mapped_column(String(400))
    live_gtin: Mapped[str | None] = mapped_column(String(20))
    live_in_stock: Mapped[bool | None] = mapped_column(Boolean)
    live_was_price: Mapped[float | None] = mapped_column(Numeric(12, 2))
    verdict: Mapped[str] = mapped_column(String(24), nullable=False, index=True)
    detail: Mapped[str | None] = mapped_column(Text)
    checked_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    url: Mapped[str | None] = mapped_column(String(600))
    # A correctness failure stays in force until someone clears it; who and
    # when are the record of the human decision (who-columns rule, plan §2).
    cleared_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    cleared_by: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    cleared_note: Mapped[str | None] = mapped_column(Text)

    run: Mapped[VerificationRun] = relationship(back_populates="checks")


class AuditSnapshot(Base):
    __tablename__ = "audit_snapshots"

    id: Mapped[int] = mapped_column(primary_key=True)
    taken_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, index=True)
    metrics: Mapped[dict] = mapped_column(JSONB, nullable=False)


class RejectedObservation(Base):
    """A listing refused at the collector or at ingest, with why and what it was.

    Written by `ingest.record_rejection` (the one place a refusal is counted);
    the columns are exactly the ones its INSERT names.
    """

    __tablename__ = "rejected_observations"

    id: Mapped[int] = mapped_column(primary_key=True)
    # collector | ingest
    stage: Mapped[str] = mapped_column(String(16), nullable=False)
    reason: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    source_sku: Mapped[str | None] = mapped_column(String(96))
    url: Mapped[str | None] = mapped_column(String(600))
    payload: Mapped[dict | None] = mapped_column(JSONB)
    run_id: Mapped[int | None] = mapped_column(ForeignKey("collection_runs.id"), index=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )