"""Add manual taxonomy assignment persistence tables.

Revision ID: 0061_add_manual_taxonomy_assignment_tables
Revises: 0058_add_brochure_taxonomy_tables, 0060_add_master_product_image_role
Create Date: 2026-07-28 18:40:00.000000
"""

from __future__ import annotations

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

revision = "0061_add_manual_taxonomy_assignment_tables"
down_revision = ("0058_add_brochure_taxonomy_tables", "0060_add_master_product_image_role")
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "manual_taxonomy_assignment",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("source_sku", sa.String(length=128), nullable=False),
        sa.Column("canonical_taxonomy_path_slug", sa.String(length=512), nullable=False),
        sa.Column("canonical_taxonomy_path_label", sa.Text(), nullable=True),
        sa.Column("shopping_l1", sa.String(length=255), nullable=True),
        sa.Column("shopping_l2", sa.String(length=512), nullable=True),
        sa.Column("l2_category", sa.Text(), nullable=True),
        sa.Column("l3_category", sa.Text(), nullable=True),
        sa.Column("assignment_status", sa.String(length=32), nullable=False, server_default="active"),
        sa.Column("raw_payload", postgresql.JSONB(astext_type=sa.Text()), nullable=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("source_sku", "canonical_taxonomy_path_slug", name="uq_manual_taxonomy_assignment_source_path"),
    )
    op.create_index("ix_manual_taxonomy_assignment_source_sku", "manual_taxonomy_assignment", ["source_sku"])
    op.create_index(
        "ix_manual_taxonomy_assignment_canonical_taxonomy_path_slug",
        "manual_taxonomy_assignment",
        ["canonical_taxonomy_path_slug"],
    )
    op.create_index("ix_manual_taxonomy_assignment_shopping_l1", "manual_taxonomy_assignment", ["shopping_l1"])
    op.create_index("ix_manual_taxonomy_assignment_shopping_l2", "manual_taxonomy_assignment", ["shopping_l2"])
    op.create_index(
        "ix_manual_taxonomy_assignment_assignment_status",
        "manual_taxonomy_assignment",
        ["assignment_status"],
    )

    op.create_table(
        "manual_taxonomy_assignment_collection",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("assignment_id", sa.Integer(), sa.ForeignKey("manual_taxonomy_assignment.id"), nullable=False),
        sa.Column("collection_registry_id", sa.Integer(), sa.ForeignKey("master_collection_registry.id"), nullable=True),
        sa.Column("collection_code", sa.String(length=128), nullable=False),
        sa.Column("source_collection_code", sa.String(length=128), nullable=True),
        sa.Column("sku_prefix", sa.String(length=128), nullable=False),
        sa.Column("collection_name", sa.Text(), nullable=True),
        sa.Column("collection_path_slug", sa.String(length=512), nullable=True),
        sa.Column("shopping_collection", sa.String(length=255), nullable=True),
        sa.Column("master_sku", sa.String(length=128), nullable=False),
        sa.Column("alias_codes", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="100"),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("raw_payload", postgresql.JSONB(astext_type=sa.Text()), nullable=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("assignment_id", "collection_code", name="uq_manual_taxonomy_assignment_collection_code"),
    )
    op.create_index(
        "ix_manual_taxonomy_assignment_collection_assignment_id",
        "manual_taxonomy_assignment_collection",
        ["assignment_id"],
    )
    op.create_index(
        "ix_manual_taxonomy_assignment_collection_collection_registry_id",
        "manual_taxonomy_assignment_collection",
        ["collection_registry_id"],
    )
    op.create_index(
        "ix_manual_taxonomy_assignment_collection_collection_code",
        "manual_taxonomy_assignment_collection",
        ["collection_code"],
    )
    op.create_index(
        "ix_manual_taxonomy_assignment_collection_source_collection_code",
        "manual_taxonomy_assignment_collection",
        ["source_collection_code"],
    )
    op.create_index(
        "ix_manual_taxonomy_assignment_collection_sku_prefix",
        "manual_taxonomy_assignment_collection",
        ["sku_prefix"],
    )
    op.create_index(
        "ix_manual_taxonomy_assignment_collection_collection_path_slug",
        "manual_taxonomy_assignment_collection",
        ["collection_path_slug"],
    )
    op.create_index(
        "ix_manual_taxonomy_assignment_collection_shopping_collection",
        "manual_taxonomy_assignment_collection",
        ["shopping_collection"],
    )
    op.create_index(
        "ix_manual_taxonomy_assignment_collection_master_sku",
        "manual_taxonomy_assignment_collection",
        ["master_sku"],
    )


def downgrade() -> None:
    op.drop_index(
        "ix_manual_taxonomy_assignment_collection_master_sku",
        table_name="manual_taxonomy_assignment_collection",
    )
    op.drop_index(
        "ix_manual_taxonomy_assignment_collection_shopping_collection",
        table_name="manual_taxonomy_assignment_collection",
    )
    op.drop_index(
        "ix_manual_taxonomy_assignment_collection_collection_path_slug",
        table_name="manual_taxonomy_assignment_collection",
    )
    op.drop_index(
        "ix_manual_taxonomy_assignment_collection_sku_prefix",
        table_name="manual_taxonomy_assignment_collection",
    )
    op.drop_index(
        "ix_manual_taxonomy_assignment_collection_source_collection_code",
        table_name="manual_taxonomy_assignment_collection",
    )
    op.drop_index(
        "ix_manual_taxonomy_assignment_collection_collection_code",
        table_name="manual_taxonomy_assignment_collection",
    )
    op.drop_index(
        "ix_manual_taxonomy_assignment_collection_collection_registry_id",
        table_name="manual_taxonomy_assignment_collection",
    )
    op.drop_index(
        "ix_manual_taxonomy_assignment_collection_assignment_id",
        table_name="manual_taxonomy_assignment_collection",
    )
    op.drop_table("manual_taxonomy_assignment_collection")

    op.drop_index("ix_manual_taxonomy_assignment_assignment_status", table_name="manual_taxonomy_assignment")
    op.drop_index("ix_manual_taxonomy_assignment_shopping_l2", table_name="manual_taxonomy_assignment")
    op.drop_index("ix_manual_taxonomy_assignment_shopping_l1", table_name="manual_taxonomy_assignment")
    op.drop_index(
        "ix_manual_taxonomy_assignment_canonical_taxonomy_path_slug",
        table_name="manual_taxonomy_assignment",
    )
    op.drop_index("ix_manual_taxonomy_assignment_source_sku", table_name="manual_taxonomy_assignment")
    op.drop_table("manual_taxonomy_assignment")