"""Migration #2: verification runs and checks, audit snapshots, rejected observations.

Schema only (build plan §2): four new tables and one JSONB column with a
server default; nothing moves. Stream Q, Sun 7 Sep slot; revises the client
to-do migration rian deployed on Sat 5.

* verification_runs / verification_checks: what `app.cli verify` re-read and
  what it concluded, one row per listing checked. A correctness failure
  (MISMATCH_*, PARSE_FAIL) blocks publication of its source until a human
  clears the check (cleared_at, cleared_by -> accounts.id; who-columns rule).
* audit_snapshots: the whole `app.cli audit` document per run, so a threshold
  change reads against the measurement before it.
* rejected_observations: every listing ingest or a collector refused, with
  the raw payload. `ingest.record_rejection` has written here since migration
  #1 whenever the table exists; until now it only logged.
* collection_runs.skip_counts: the per-reason split of skipped_no_price
  ({"no_price": 40, "gtin_size_veto": 2, ...}). The old total stays.

Revision ID: d1e2f3a4b5c6
Revises: c8d9e0f1a2b3
Create Date: 2026-09-05
"""

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

from alembic import op

revision = "d1e2f3a4b5c6"
down_revision = "c8d9e0f1a2b3"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "verification_runs",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("started_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("finished_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("seed", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("n", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("per_source", postgresql.JSONB(), nullable=False, server_default="{}"),
        sa.Column("mode", sa.String(24), nullable=False, server_default="on_demand"),
    )
    op.create_table(
        "verification_checks",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("run_id", sa.Integer(), sa.ForeignKey("verification_runs.id"), nullable=False),
        sa.Column("listing_id", sa.Integer(), sa.ForeignKey("listings.id"), nullable=False),
        sa.Column("observation_id", sa.Integer(), sa.ForeignKey("price_observations.id"), nullable=True),
        sa.Column("source_id", sa.Integer(), sa.ForeignKey("sources.id"), nullable=True),
        sa.Column("live_price", sa.Numeric(12, 2), nullable=True),
        sa.Column("live_currency", sa.String(3), nullable=True),
        sa.Column("live_size_ml", sa.Integer(), nullable=True),
        sa.Column("live_name", sa.String(400), nullable=True),
        sa.Column("live_gtin", sa.String(20), nullable=True),
        sa.Column("live_in_stock", sa.Boolean(), nullable=True),
        sa.Column("live_was_price", sa.Numeric(12, 2), nullable=True),
        sa.Column("verdict", sa.String(24), nullable=False),
        sa.Column("detail", sa.Text(), nullable=True),
        sa.Column("checked_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("url", sa.String(600), nullable=True),
        sa.Column("cleared_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("cleared_by", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True),
        sa.Column("cleared_note", sa.Text(), nullable=True),
    )
    op.create_index("ix_verification_checks_run_id", "verification_checks", ["run_id"])
    op.create_index("ix_verification_checks_listing_id", "verification_checks", ["listing_id"])
    op.create_index("ix_verification_checks_source_id", "verification_checks", ["source_id"])
    op.create_index("ix_verification_checks_verdict", "verification_checks", ["verdict"])
    op.create_table(
        "audit_snapshots",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("taken_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("metrics", postgresql.JSONB(), nullable=False),
    )
    op.create_index("ix_audit_snapshots_taken_at", "audit_snapshots", ["taken_at"])
    op.create_table(
        "rejected_observations",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("stage", sa.String(16), nullable=False),
        sa.Column("reason", sa.String(64), nullable=False),
        sa.Column("source_sku", sa.String(96), nullable=True),
        sa.Column("url", sa.String(600), nullable=True),
        sa.Column("payload", postgresql.JSONB(), nullable=True),
        sa.Column("run_id", sa.Integer(), sa.ForeignKey("collection_runs.id"), nullable=True),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False
        ),
    )
    op.create_index("ix_rejected_observations_reason", "rejected_observations", ["reason"])
    op.create_index("ix_rejected_observations_run_id", "rejected_observations", ["run_id"])
    op.add_column(
        "collection_runs",
        sa.Column("skip_counts", postgresql.JSONB(), nullable=False, server_default="{}"),
    )


def downgrade() -> None:
    op.drop_column("collection_runs", "skip_counts")
    op.drop_index("ix_rejected_observations_run_id", table_name="rejected_observations")
    op.drop_index("ix_rejected_observations_reason", table_name="rejected_observations")
    op.drop_table("rejected_observations")
    op.drop_index("ix_audit_snapshots_taken_at", table_name="audit_snapshots")
    op.drop_table("audit_snapshots")
    for name in ("verdict", "source_id", "listing_id", "run_id"):
        op.drop_index(f"ix_verification_checks_{name}", table_name="verification_checks")
    op.drop_table("verification_checks")
    op.drop_table("verification_runs")