"""Add magento_source_registry, magento_stock_registry, magento_stock_source_links.

MSI (Multi-Source Inventory) registry for baseline sync.
Stores sources (warehouses) and stocks (sales-channel aggregations) from Magento.

Revision ID: 0020_add_magento_msi_registry_tables
Revises: 0019_add_attribute_set_registry_tables
Create Date: 2026-03-10

"""

from alembic import op
import sqlalchemy as sa

revision = "0020_add_magento_msi_registry_tables"
down_revision = "0019_add_attribute_set_registry_tables"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "magento_source_registry",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("connection_id", sa.Integer(), sa.ForeignKey("magento_connections.id"), nullable=False),
        sa.Column("source_code", sa.String(64), nullable=False),
        sa.Column("name", sa.Text(), nullable=True),
        sa.Column("enabled", sa.Boolean(), nullable=True),
        sa.Column("country_id", sa.String(8), nullable=True),
        sa.Column("postcode", sa.String(32), nullable=True),
        sa.Column("fetched_at", sa.DateTime(timezone=True), nullable=False),
        sa.UniqueConstraint("connection_id", "source_code", name="uq_magento_source_registry_conn_code"),
    )
    op.create_index("ix_magento_source_registry_conn", "magento_source_registry", ["connection_id"])

    op.create_table(
        "magento_stock_registry",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("connection_id", sa.Integer(), sa.ForeignKey("magento_connections.id"), nullable=False),
        sa.Column("stock_id", sa.Integer(), nullable=False),
        sa.Column("name", sa.Text(), nullable=False),
        sa.Column("sales_channels_json", sa.dialects.postgresql.JSONB(), nullable=True),
        sa.Column("fetched_at", sa.DateTime(timezone=True), nullable=False),
        sa.UniqueConstraint("connection_id", "stock_id", name="uq_magento_stock_registry_conn_stock"),
    )
    op.create_index("ix_magento_stock_registry_conn", "magento_stock_registry", ["connection_id"])

    op.create_table(
        "magento_stock_source_links",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("connection_id", sa.Integer(), sa.ForeignKey("magento_connections.id"), nullable=False),
        sa.Column("stock_id", sa.Integer(), nullable=False),
        sa.Column("source_code", sa.String(64), nullable=False),
        sa.Column("priority", sa.Integer(), nullable=True),
        sa.Column("fetched_at", sa.DateTime(timezone=True), nullable=False),
        sa.UniqueConstraint(
            "connection_id", "stock_id", "source_code",
            name="uq_magento_stock_source_links_conn_stock_src",
        ),
    )
    op.create_index(
        "ix_magento_stock_source_links_conn_stock",
        "magento_stock_source_links",
        ["connection_id", "stock_id"],
    )


def downgrade() -> None:
    op.drop_table("magento_stock_source_links")
    op.drop_table("magento_stock_registry")
    op.drop_table("magento_source_registry")