"""Migration #3: brands, stated sizes, the per-vertical attribute, recorded merges.

Schema only (build plan §2): four new tables, seven nullable-or-defaulted columns on
products, no data moves. The moves are `app.cli backfill brands`, `sizes`, `categories`
and `merges`, run after the deploy in that order, each safe to repeat. Stream A, Wave 2;
revises the running-list migration the planning session deployed on Sat 5.

* brands: one row per brand fold (`normalize.brand_key`, hyphenated as `slug`), with
  `canonical_id` for an alias of another house; `products.brand_id` is the fold.
* products.size_value + size_unit: the size as the shop states it; size_ml stays derived.
* products.attributes (JSONB): per-vertical facts that veto a fallback match and never
  key it (Decision 6): a fragrance's concentration today.
* products.identity_rules_version: which rules a row was last resolved under; existing
  rows get "1", ingest writes the current one.
* products.merged_into_id: a merged product forwards to its survivor, never deleted.
* merge_candidates, product_merges: the queue and the record; who-columns are FK accounts.
* reverifications: the disagreement queue (Decision 7), columns only for now (§10 #1).

Revision ID: f3a4b5c6d7e8
Revises: e2f3a4b5c6d7
Create Date: 2026-09-05
"""

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

from alembic import op

revision = "f3a4b5c6d7e8"
down_revision = "e2f3a4b5c6d7"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "brands",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("slug", sa.String(160), nullable=False, unique=True),
        sa.Column("name", sa.String(160), nullable=False),
        sa.Column("canonical_id", sa.Integer(), sa.ForeignKey("brands.id"), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
    )
    op.create_index("ix_brands_canonical_id", "brands", ["canonical_id"])

    op.add_column("products", sa.Column("brand_id", sa.Integer(), sa.ForeignKey("brands.id"), nullable=True))
    op.create_index("ix_products_brand_id", "products", ["brand_id"])
    op.add_column("products", sa.Column("size_value", sa.Numeric(9, 2), nullable=True))
    op.add_column("products", sa.Column("size_unit", sa.String(8), nullable=True))
    op.add_column(
        "products",
        sa.Column("attributes", postgresql.JSONB(), nullable=False, server_default="{}"),
    )
    op.add_column(
        "products",
        sa.Column("identity_rules_version", sa.String(16), nullable=False, server_default="1"),
    )
    op.add_column(
        "products", sa.Column("merged_into_id", sa.Integer(), sa.ForeignKey("products.id"), nullable=True)
    )
    op.create_index("ix_products_merged_into_id", "products", ["merged_into_id"])

    op.create_table(
        "merge_candidates",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("product_id", sa.Integer(), sa.ForeignKey("products.id"), nullable=False),
        sa.Column("candidate_id", sa.Integer(), sa.ForeignKey("products.id"), nullable=False),
        sa.Column("reason", sa.String(40), nullable=False),
        sa.Column("score", sa.Numeric(5, 3), nullable=True),
        sa.Column("detail", postgresql.JSONB(), nullable=True),
        sa.Column("detected_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column("decided_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("decided_by", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True),
        sa.Column("decision", sa.String(16), nullable=True),
        sa.UniqueConstraint("product_id", "candidate_id", name="uq_merge_candidate_pair"),
    )
    op.create_index("ix_merge_candidates_product_id", "merge_candidates", ["product_id"])
    op.create_index("ix_merge_candidates_candidate_id", "merge_candidates", ["candidate_id"])

    op.create_table(
        "product_merges",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("from_id", sa.Integer(), sa.ForeignKey("products.id"), nullable=False),
        sa.Column("to_id", sa.Integer(), sa.ForeignKey("products.id"), nullable=False),
        sa.Column("merged_by", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True),
        sa.Column("merged_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column("reason", sa.String(40), nullable=False, server_default="duplicate"),
        sa.Column("detail", postgresql.JSONB(), nullable=True),
    )
    op.create_index("ix_product_merges_from_id", "product_merges", ["from_id"])
    op.create_index("ix_product_merges_to_id", "product_merges", ["to_id"])

    op.create_table(
        "reverifications",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("product_id", sa.Integer(), sa.ForeignKey("products.id"), nullable=True),
        sa.Column("listing_id", sa.Integer(), sa.ForeignKey("listings.id"), nullable=True),
        sa.Column("reason", sa.String(40), nullable=False),
        sa.Column("score", sa.Numeric(6, 3), nullable=True),
        sa.Column("queued_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column("done_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("result", sa.String(24), nullable=True),
        sa.Column("detail", postgresql.JSONB(), nullable=True),
    )
    op.create_index("ix_reverifications_product_id", "reverifications", ["product_id"])
    op.create_index("ix_reverifications_listing_id", "reverifications", ["listing_id"])


def downgrade() -> None:
    op.drop_index("ix_reverifications_listing_id", table_name="reverifications")
    op.drop_index("ix_reverifications_product_id", table_name="reverifications")
    op.drop_table("reverifications")
    op.drop_index("ix_product_merges_to_id", table_name="product_merges")
    op.drop_index("ix_product_merges_from_id", table_name="product_merges")
    op.drop_table("product_merges")
    op.drop_index("ix_merge_candidates_candidate_id", table_name="merge_candidates")
    op.drop_index("ix_merge_candidates_product_id", table_name="merge_candidates")
    op.drop_table("merge_candidates")
    op.drop_index("ix_products_merged_into_id", table_name="products")
    op.drop_column("products", "merged_into_id")
    op.drop_column("products", "identity_rules_version")
    op.drop_column("products", "attributes")
    op.drop_column("products", "size_unit")
    op.drop_column("products", "size_value")
    op.drop_index("ix_products_brand_id", table_name="products")
    op.drop_column("products", "brand_id")
    op.drop_index("ix_brands_canonical_id", table_name="brands")
    op.drop_table("brands")