"""Add master product merchandising associations + Magento associations_hash.

Revision ID: 0056_add_master_product_association
Revises: 0055_add_magento_internal_api_transport
Create Date: 2026-07-15 12:00:00.000000
"""

from __future__ import annotations

from alembic import op
import sqlalchemy as sa

revision = "0056_add_master_product_association"
down_revision = "0055_add_magento_internal_api_transport"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "master_product_association",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("source_sku", sa.String(128), nullable=False),
        sa.Column("target_sku", sa.String(128), nullable=False),
        sa.Column("link_type", sa.String(32), nullable=False),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("origin", sa.String(32), nullable=False, server_default="auto"),
        sa.Column("source_label", sa.String(128), nullable=True),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.func.now(),
        ),
        sa.UniqueConstraint(
            "source_sku",
            "target_sku",
            "link_type",
            name="uq_master_product_association_source_target_type",
        ),
    )
    op.create_index(
        "ix_master_product_association_source_sku",
        "master_product_association",
        ["source_sku"],
    )
    op.create_index(
        "ix_master_product_association_target_sku",
        "master_product_association",
        ["target_sku"],
    )
    op.create_index(
        "ix_master_product_association_link_type",
        "master_product_association",
        ["link_type"],
    )

    op.add_column(
        "magento_sync_state",
        sa.Column("associations_hash", sa.String(64), nullable=True),
    )


def downgrade() -> None:
    op.drop_column("magento_sync_state", "associations_hash")
    op.drop_index(
        "ix_master_product_association_link_type",
        table_name="master_product_association",
    )
    op.drop_index(
        "ix_master_product_association_target_sku",
        table_name="master_product_association",
    )
    op.drop_index(
        "ix_master_product_association_source_sku",
        table_name="master_product_association",
    )
    op.drop_table("master_product_association")