"""Add master taxonomy registries and FK links.

Revision ID: 0046_add_master_taxonomy_registries
Revises: 0045_add_collection_parent_path_slug
Create Date: 2026-06-18 20:00:00.000000
"""

from __future__ import annotations

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import JSONB

revision = "0046_add_master_taxonomy_registries"
down_revision = "0045_add_collection_parent_path_slug"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "master_brand",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("code", sa.String(128), nullable=False),
        sa.Column("name", sa.Text(), nullable=False),
        sa.Column("aliases", JSONB, nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.UniqueConstraint("code", name="uq_master_brand_code"),
    )
    op.create_index("ix_master_brand_code", "master_brand", ["code"])

    op.create_table(
        "master_product_family",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("code", sa.String(128), nullable=False),
        sa.Column("name", sa.Text(), nullable=False),
        sa.Column("aliases", JSONB, nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.UniqueConstraint("code", name="uq_master_product_family_code"),
    )
    op.create_index("ix_master_product_family_code", "master_product_family", ["code"])

    op.create_table(
        "master_product_type",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("family_id", sa.Integer(), sa.ForeignKey("master_product_family.id"), nullable=True),
        sa.Column("code", sa.String(128), nullable=False),
        sa.Column("name", sa.Text(), nullable=False),
        sa.Column("aliases", JSONB, nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.UniqueConstraint("code", "family_id", name="uq_master_product_type_code_family"),
    )
    op.create_index("ix_master_product_type_code", "master_product_type", ["code"])
    op.create_index("ix_master_product_type_family_id", "master_product_type", ["family_id"])

    op.create_table(
        "master_collection_registry",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("code", sa.String(128), nullable=False),
        sa.Column("name", sa.Text(), nullable=False),
        sa.Column("path_slug", sa.String(512), nullable=True),
        sa.Column("brand_id", sa.Integer(), sa.ForeignKey("master_brand.id"), nullable=True),
        sa.Column("family_id", sa.Integer(), sa.ForeignKey("master_product_family.id"), nullable=True),
        sa.Column("aliases", JSONB, nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.UniqueConstraint("code", name="uq_master_collection_registry_code"),
    )
    op.create_index("ix_master_collection_registry_code", "master_collection_registry", ["code"])
    op.create_index("ix_master_collection_registry_path_slug", "master_collection_registry", ["path_slug"])
    op.create_index("ix_master_collection_registry_brand_id", "master_collection_registry", ["brand_id"])
    op.create_index("ix_master_collection_registry_family_id", "master_collection_registry", ["family_id"])

    op.create_table(
        "master_catalog_intent_run",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("status", sa.String(32), nullable=False, server_default="preview"),
        sa.Column("source_label", sa.String(128), nullable=True),
        sa.Column("preview_json", JSONB, nullable=True),
        sa.Column("result_json", JSONB, nullable=True),
        sa.Column("sku_count", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("confirmed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("completed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
    )
    op.create_index("ix_master_catalog_intent_run_status", "master_catalog_intent_run", ["status"])

    op.add_column("master_product", sa.Column("brand_id", sa.Integer(), sa.ForeignKey("master_brand.id"), nullable=True))
    op.add_column(
        "master_product",
        sa.Column("family_id", sa.Integer(), sa.ForeignKey("master_product_family.id"), nullable=True),
    )
    op.add_column(
        "master_product",
        sa.Column("product_type_id", sa.Integer(), sa.ForeignKey("master_product_type.id"), nullable=True),
    )
    op.add_column(
        "master_product",
        sa.Column(
            "collection_registry_id",
            sa.Integer(),
            sa.ForeignKey("master_collection_registry.id"),
            nullable=True,
        ),
    )
    op.create_index("ix_master_product_brand_id", "master_product", ["brand_id"])
    op.create_index("ix_master_product_family_id", "master_product", ["family_id"])
    op.create_index("ix_master_product_product_type_id", "master_product", ["product_type_id"])
    op.create_index("ix_master_product_collection_registry_id", "master_product", ["collection_registry_id"])

    op.add_column(
        "collection_landing_page",
        sa.Column(
            "collection_registry_id",
            sa.Integer(),
            sa.ForeignKey("master_collection_registry.id"),
            nullable=True,
        ),
    )
    op.create_index(
        "ix_collection_landing_page_collection_registry_id",
        "collection_landing_page",
        ["collection_registry_id"],
    )


def downgrade() -> None:
    op.drop_index("ix_collection_landing_page_collection_registry_id", table_name="collection_landing_page")
    op.drop_column("collection_landing_page", "collection_registry_id")

    op.drop_index("ix_master_product_collection_registry_id", table_name="master_product")
    op.drop_index("ix_master_product_product_type_id", table_name="master_product")
    op.drop_index("ix_master_product_family_id", table_name="master_product")
    op.drop_index("ix_master_product_brand_id", table_name="master_product")
    op.drop_column("master_product", "collection_registry_id")
    op.drop_column("master_product", "product_type_id")
    op.drop_column("master_product", "family_id")
    op.drop_column("master_product", "brand_id")

    op.drop_index("ix_master_catalog_intent_run_status", table_name="master_catalog_intent_run")
    op.drop_table("master_catalog_intent_run")

    op.drop_index("ix_master_collection_registry_family_id", table_name="master_collection_registry")
    op.drop_index("ix_master_collection_registry_brand_id", table_name="master_collection_registry")
    op.drop_index("ix_master_collection_registry_path_slug", table_name="master_collection_registry")
    op.drop_index("ix_master_collection_registry_code", table_name="master_collection_registry")
    op.drop_table("master_collection_registry")

    op.drop_index("ix_master_product_type_family_id", table_name="master_product_type")
    op.drop_index("ix_master_product_type_code", table_name="master_product_type")
    op.drop_table("master_product_type")

    op.drop_index("ix_master_product_family_code", table_name="master_product_family")
    op.drop_table("master_product_family")

    op.drop_index("ix_master_brand_code", table_name="master_brand")
    op.drop_table("master_brand")