"""Migration #6: the line above the product, the variation vocabulary, aliases at every level.

Schema only (build plan §2): two new tables, nullable or defaulted columns, two NOT NULLs
relaxed; the moves are `app.cli backfill lines`, `backfill variations`, `rederive` and
`backfill merges`, then `app.cli suggest`, run after the deploy in that order, each safe to
repeat. Stream M, from rian's decision of 12 Sep (one page per real product;
`.logs/planning/streams/M-merging.md`). Revises Stream R's migration #4, the single head.

* product_lines: the real product above the priced one ("1 Million" over its EDT, Parfum
  and Elixir at every size), one row per house and line key; `name` is the preferred
  spelling; `canonical_id` points an alias line at the one it was folded into, with who
  decided and when. `products.line_id` is the join.
* variation_aliases: one wording of a variation and the canonical one it means, per
  vertical ("elixir parfum intense" -> "elixir"), the display wording, who decided.
* brands.decided_by / decided_at: the brand alias (`canonical_id`, migration #3) gains who
  set it and when (plan §2, the who-columns rule).
* merge_candidates.level (brand | line | product, default product), left_id, right_id: one
  queue holds the suggestions at every level, so the two product FKs become nullable and a
  second unique constraint covers the level pair. A downgrade drops the brand and line
  rows before restoring NOT NULL; `app.cli suggest` regenerates them.
* products.gtin_source: "collected" or "merge", so a barcode that arrived through a merge
  can be told from one the shop published (M7).

Revision ID: c6d7e8f9a0b1
Revises: c7d8e9f0a1b2
Create Date: 2026-09-11
"""

import sqlalchemy as sa

from alembic import op

revision = "c6d7e8f9a0b1"
down_revision = "c7d8e9f0a1b2"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "product_lines",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("brand_id", sa.Integer(), sa.ForeignKey("brands.id"), nullable=False),
        sa.Column("key", sa.String(160), nullable=False),
        sa.Column("name", sa.String(200), nullable=False),
        sa.Column("slug", sa.String(240), nullable=False, unique=True),
        sa.Column("canonical_id", sa.Integer(), sa.ForeignKey("product_lines.id"), nullable=True),
        sa.Column("decided_by", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True),
        sa.Column("decided_at", sa.DateTime(timezone=True), 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),
        sa.UniqueConstraint("brand_id", "key", name="uq_product_line_brand_key"),
    )
    op.create_index("ix_product_lines_brand_id", "product_lines", ["brand_id"])
    op.create_index("ix_product_lines_canonical_id", "product_lines", ["canonical_id"])

    op.add_column("products", sa.Column("line_id", sa.Integer(), sa.ForeignKey("product_lines.id"), nullable=True))
    op.create_index("ix_products_line_id", "products", ["line_id"])
    op.add_column("products", sa.Column("gtin_source", sa.String(16), nullable=True))

    op.create_table(
        "variation_aliases",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("vertical", sa.String(32), nullable=False),
        sa.Column("raw", sa.String(80), nullable=False),
        sa.Column("canonical", sa.String(80), nullable=False),
        sa.Column("display", sa.String(80), nullable=True),
        sa.Column("decided_by", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True),
        sa.Column("decided_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.UniqueConstraint("vertical", "raw", name="uq_variation_alias_vertical_raw"),
    )

    op.add_column("brands", sa.Column("decided_by", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True))
    op.add_column("brands", sa.Column("decided_at", sa.DateTime(timezone=True), nullable=True))

    op.add_column("merge_candidates", sa.Column("level", sa.String(16), nullable=False, server_default="product"))
    op.add_column("merge_candidates", sa.Column("left_id", sa.Integer(), nullable=True))
    op.add_column("merge_candidates", sa.Column("right_id", sa.Integer(), nullable=True))
    op.alter_column("merge_candidates", "product_id", existing_type=sa.Integer(), nullable=True)
    op.alter_column("merge_candidates", "candidate_id", existing_type=sa.Integer(), nullable=True)
    op.create_unique_constraint("uq_merge_candidate_level_pair", "merge_candidates", ["level", "left_id", "right_id"])
    op.create_index("ix_merge_candidates_level", "merge_candidates", ["level"])


def downgrade() -> None:
    op.drop_index("ix_merge_candidates_level", table_name="merge_candidates")
    op.drop_constraint("uq_merge_candidate_level_pair", "merge_candidates", type_="unique")
    op.execute("DELETE FROM merge_candidates WHERE level <> 'product' OR product_id IS NULL OR candidate_id IS NULL")
    op.alter_column("merge_candidates", "candidate_id", existing_type=sa.Integer(), nullable=False)
    op.alter_column("merge_candidates", "product_id", existing_type=sa.Integer(), nullable=False)
    op.drop_column("merge_candidates", "right_id")
    op.drop_column("merge_candidates", "left_id")
    op.drop_column("merge_candidates", "level")
    op.drop_column("brands", "decided_at")
    op.drop_column("brands", "decided_by")
    op.drop_table("variation_aliases")
    op.drop_column("products", "gtin_source")
    op.drop_index("ix_products_line_id", table_name="products")
    op.drop_column("products", "line_id")
    op.drop_index("ix_product_lines_canonical_id", table_name="product_lines")
    op.drop_index("ix_product_lines_brand_id", table_name="product_lines")
    op.drop_table("product_lines")