"""Make SKU mappings safely connection-scoped.

Revision ID: 0038_make_sku_mappings_connection_scoped
Revises: 0037_add_taxonomy_registry_and_mapping
Create Date: 2026-06-13 18:00:00.000000
"""

from __future__ import annotations

from alembic import op
import sqlalchemy as sa


revision = "0038_make_sku_mappings_connection_scoped"
down_revision = "0037_add_taxonomy_registry_and_mapping"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.drop_constraint("uq_channel_sku_mapping_master_channel", "channel_sku_mapping", type_="unique")
    op.drop_constraint("uq_channel_sku_mapping_channel_sku", "channel_sku_mapping", type_="unique")
    op.drop_index("uq_channel_sku_mapping_master_connection", table_name="channel_sku_mapping")

    op.create_index(
        "uq_channel_sku_mapping_master_channel_global",
        "channel_sku_mapping",
        ["master_sku", "channel_code"],
        unique=True,
        postgresql_where=sa.text("connection_id IS NULL"),
    )
    op.create_index(
        "uq_channel_sku_mapping_channel_sku_global",
        "channel_sku_mapping",
        ["channel_code", "channel_sku"],
        unique=True,
        postgresql_where=sa.text("connection_id IS NULL"),
    )
    op.create_index(
        "uq_channel_sku_mapping_master_connection",
        "channel_sku_mapping",
        ["master_sku", "connection_id"],
        unique=True,
        postgresql_where=sa.text("connection_id IS NOT NULL"),
    )
    op.create_index(
        "uq_channel_sku_mapping_channel_sku_connection",
        "channel_sku_mapping",
        ["channel_sku", "connection_id"],
        unique=True,
        postgresql_where=sa.text("connection_id IS NOT NULL"),
    )

    op.drop_constraint("uq_channel_sku_prefix_channel_master", "channel_sku_prefix_mapping", type_="unique")
    op.drop_index("uq_channel_sku_prefix_master_connection", table_name="channel_sku_prefix_mapping")
    op.create_index(
        "uq_channel_sku_prefix_channel_master_global",
        "channel_sku_prefix_mapping",
        ["channel_code", "master_prefix"],
        unique=True,
        postgresql_where=sa.text("connection_id IS NULL"),
    )
    op.create_index(
        "uq_channel_sku_prefix_master_connection",
        "channel_sku_prefix_mapping",
        ["master_prefix", "connection_id"],
        unique=True,
        postgresql_where=sa.text("connection_id IS NOT NULL"),
    )


def downgrade() -> None:
    op.drop_index("uq_channel_sku_prefix_master_connection", table_name="channel_sku_prefix_mapping")
    op.drop_index("uq_channel_sku_prefix_channel_master_global", table_name="channel_sku_prefix_mapping")
    op.create_unique_constraint(
        "uq_channel_sku_prefix_channel_master",
        "channel_sku_prefix_mapping",
        ["channel_code", "master_prefix"],
    )
    op.create_index(
        "uq_channel_sku_prefix_master_connection",
        "channel_sku_prefix_mapping",
        ["master_prefix", "connection_id"],
        unique=True,
        postgresql_where=sa.text("connection_id IS NOT NULL"),
    )

    op.drop_index("uq_channel_sku_mapping_channel_sku_connection", table_name="channel_sku_mapping")
    op.drop_index("uq_channel_sku_mapping_master_connection", table_name="channel_sku_mapping")
    op.drop_index("uq_channel_sku_mapping_channel_sku_global", table_name="channel_sku_mapping")
    op.drop_index("uq_channel_sku_mapping_master_channel_global", table_name="channel_sku_mapping")
    op.create_unique_constraint(
        "uq_channel_sku_mapping_master_channel",
        "channel_sku_mapping",
        ["master_sku", "channel_code"],
    )
    op.create_unique_constraint(
        "uq_channel_sku_mapping_channel_sku",
        "channel_sku_mapping",
        ["channel_code", "channel_sku"],
    )
    op.create_index(
        "uq_channel_sku_mapping_master_connection",
        "channel_sku_mapping",
        ["master_sku", "connection_id"],
        unique=True,
        postgresql_where=sa.text("connection_id IS NOT NULL"),
    )