"""One term per concept, in the database as in the labels (rian, 16 Sep; the catalogue refactor
plan W2, Stream K1). Rename-only: no column changes type, no row changes value, no behaviour
changes. `products` is `product_variants` (the barcoded thing), `locations` is `shops`,
`merge_candidates` is `suggestions`, `product_merges` is `merges`, `variation_aliases` is
`attribute_aliases`; `line_id` is `product_line_id`, `canonical_id` is `alias_of_id`, the
`size_*` trio is `quantity_ml` and the stated pair, `product_id` on every table is `variant_id`,
`location_id` is `shop_id`. Every index, constraint and sequence follows its table, so a reader of
`\\d` sees the same words as the page.

The one drop: `merge_candidates.product_id` / `candidate_id`, the pre-Stream-M pair, redundant with
`left_id` / `right_id` on every row (rehearsed 16 Sep: 1,072 of 1,072 product-level rows equal);
the downgrade restores both columns from `left_id` / `right_id` where `level = 'product'`.

The two suggestion decision VALUES (`merged` -> `same`, `kept_apart` -> `separate`) are data, so
they move by `app.cli backfill suggestion_decisions` after this runs, never here; the JSON key
`attributes.variation` moves by `app.cli backfill attribute_keys`. Revises `b3c4d5e6f7a8`, the
single head at the time of writing.

Revision ID: c1d2e3f4a5b6
Revises: b3c4d5e6f7a8
Create Date: 2026-09-16
"""

from alembic import op

revision = "c1d2e3f4a5b6"
down_revision = "b3c4d5e6f7a8"
branch_labels = None
depends_on = None

# (table as named BEFORE any table rename in this revision, old column, new column)
COLUMNS = [
    ("products", "line_id", "product_line_id"),
    ("products", "size_value", "quantity_stated_value"),
    ("products", "size_unit", "quantity_stated_unit"),
    ("products", "size_ml", "quantity_ml"),
    ("listings", "product_id", "variant_id"),
    ("listings", "pinned_product_id", "pinned_variant_id"),
    ("listings", "location_id", "shop_id"),
    ("awards", "product_id", "variant_id"),
    ("reverifications", "product_id", "variant_id"),
    ("verification_checks", "live_size_ml", "live_quantity_ml"),
    ("brands", "canonical_id", "alias_of_id"),
    ("product_lines", "canonical_id", "alias_of_id"),
    ("airport_hours", "location_id", "shop_id"),
]

TABLES = [
    ("products", "product_variants"),
    ("locations", "shops"),
    ("merge_candidates", "suggestions"),
    ("product_merges", "merges"),
    ("variation_aliases", "attribute_aliases"),
]

SEQUENCES = [(f"{old}_id_seq", f"{new}_id_seq") for old, new in TABLES]

# (table as named AFTER the table renames, old constraint, new constraint)
CONSTRAINTS = [
    ("product_variants", "products_pkey", "product_variants_pkey"),
    ("product_variants", "products_brand_id_fkey", "product_variants_brand_id_fkey"),
    ("product_variants", "products_line_id_fkey", "product_variants_product_line_id_fkey"),
    ("product_variants", "products_merged_into_id_fkey", "product_variants_merged_into_id_fkey"),
    ("listings", "listings_product_id_fkey", "listings_variant_id_fkey"),
    ("listings", "fk_listings_pinned_product", "fk_listings_pinned_variant"),
    ("listings", "listings_location_id_fkey", "listings_shop_id_fkey"),
    ("listings", "uq_listing_location_sku", "uq_listing_shop_sku"),
    ("awards", "awards_product_id_fkey", "awards_variant_id_fkey"),
    ("awards", "uq_award_product_comp_year", "uq_award_variant_comp_year"),
    ("reverifications", "reverifications_product_id_fkey", "reverifications_variant_id_fkey"),
    ("brands", "brands_canonical_id_fkey", "brands_alias_of_id_fkey"),
    ("product_lines", "product_lines_canonical_id_fkey", "product_lines_alias_of_id_fkey"),
    ("shops", "locations_pkey", "shops_pkey"),
    ("shops", "locations_retailer_id_fkey", "shops_retailer_id_fkey"),
    ("shops", "uq_location_retailer_code", "uq_shop_retailer_code"),
    ("airport_hours", "airport_hours_location_id_fkey", "airport_hours_shop_id_fkey"),
    ("suggestions", "merge_candidates_pkey", "suggestions_pkey"),
    ("suggestions", "merge_candidates_decided_by_fkey", "suggestions_decided_by_fkey"),
    ("suggestions", "uq_merge_candidate_level_pair", "uq_suggestion_level_pair"),
    ("merges", "product_merges_pkey", "merges_pkey"),
    ("merges", "product_merges_from_id_fkey", "merges_from_id_fkey"),
    ("merges", "product_merges_to_id_fkey", "merges_to_id_fkey"),
    ("merges", "product_merges_merged_by_fkey", "merges_merged_by_fkey"),
    ("attribute_aliases", "variation_aliases_pkey", "attribute_aliases_pkey"),
    ("attribute_aliases", "variation_aliases_decided_by_fkey", "attribute_aliases_decided_by_fkey"),
    ("attribute_aliases", "uq_variation_alias_vertical_raw", "uq_attribute_alias_vertical_raw"),
]

# A primary-key or unique constraint's index carries the constraint's name and is renamed with
# it; these are the plain indexes.
INDEXES = [
    ("ix_products_brand", "ix_product_variants_brand"),
    ("ix_products_brand_id", "ix_product_variants_brand_id"),
    ("ix_products_category", "ix_product_variants_category"),
    ("ix_products_gtin", "ix_product_variants_gtin"),
    ("ix_products_line_id", "ix_product_variants_product_line_id"),
    ("ix_products_match_key", "ix_product_variants_match_key"),
    ("ix_products_merged_into_id", "ix_product_variants_merged_into_id"),
    ("ix_products_name_key", "ix_product_variants_name_key"),
    ("ix_products_quantity", "ix_product_variants_quantity"),
    ("ix_products_vertical", "ix_product_variants_vertical"),
    ("ix_listings_product_id", "ix_listings_variant_id"),
    ("ix_listings_pinned_product_id", "ix_listings_pinned_variant_id"),
    ("ix_listings_location_id", "ix_listings_shop_id"),
    ("ix_awards_product_id", "ix_awards_variant_id"),
    ("ix_reverifications_product_id", "ix_reverifications_variant_id"),
    ("ix_brands_canonical_id", "ix_brands_alias_of_id"),
    ("ix_product_lines_canonical_id", "ix_product_lines_alias_of_id"),
    ("ix_locations_iata", "ix_shops_iata"),
    ("ix_airport_hours_location_id", "ix_airport_hours_shop_id"),
    ("ix_merge_candidates_level", "ix_suggestions_level"),
    ("ix_product_merges_from_id", "ix_merges_from_id"),
    ("ix_product_merges_to_id", "ix_merges_to_id"),
]


def _rename_index(old: str, new: str) -> None:
    op.execute(f'ALTER INDEX "{old}" RENAME TO "{new}"')


def _rename_constraint(table: str, old: str, new: str) -> None:
    op.execute(f'ALTER TABLE "{table}" RENAME CONSTRAINT "{old}" TO "{new}"')


def _rename_sequence(old: str, new: str) -> None:
    op.execute(f'ALTER SEQUENCE "{old}" RENAME TO "{new}"')


def upgrade() -> None:
    # The legacy pair on merge_candidates goes first, while the table still has its old name.
    op.drop_constraint("uq_merge_candidate_pair", "merge_candidates", type_="unique")
    op.drop_constraint("merge_candidates_product_id_fkey", "merge_candidates", type_="foreignkey")
    op.drop_constraint("merge_candidates_candidate_id_fkey", "merge_candidates", type_="foreignkey")
    op.drop_index("ix_merge_candidates_product_id", table_name="merge_candidates")
    op.drop_index("ix_merge_candidates_candidate_id", table_name="merge_candidates")
    op.drop_column("merge_candidates", "product_id")
    op.drop_column("merge_candidates", "candidate_id")

    for table, old, new in COLUMNS:
        op.alter_column(table, old, new_column_name=new)
    for old, new in TABLES:
        op.rename_table(old, new)
    for old, new in SEQUENCES:
        _rename_sequence(old, new)
    for table, old, new in CONSTRAINTS:
        _rename_constraint(table, old, new)
    for old, new in INDEXES:
        _rename_index(old, new)


def downgrade() -> None:
    for old, new in INDEXES:
        _rename_index(new, old)
    for table, old, new in CONSTRAINTS:
        _rename_constraint(table, new, old)
    for old, new in SEQUENCES:
        _rename_sequence(new, old)
    for old, new in TABLES:
        op.rename_table(new, old)
    for table, old, new in COLUMNS:
        op.alter_column(table, new, new_column_name=old)

    # The legacy pair, restored from left_id / right_id on the product-level rows.
    op.execute("ALTER TABLE merge_candidates ADD COLUMN product_id INTEGER, ADD COLUMN candidate_id INTEGER")
    op.execute("UPDATE merge_candidates SET product_id = left_id, candidate_id = right_id WHERE level = 'product'")
    op.create_foreign_key("merge_candidates_product_id_fkey", "merge_candidates", "products", ["product_id"], ["id"])
    op.create_foreign_key("merge_candidates_candidate_id_fkey", "merge_candidates", "products", ["candidate_id"], ["id"])
    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_unique_constraint("uq_merge_candidate_pair", "merge_candidates", ["product_id", "candidate_id"])