"""Add hierarchical master taxonomy nodes and per-channel remote links.

Revision ID: 0048_add_master_taxonomy_hierarchy
Revises: 0047_master_product_type_code_global
"""

from __future__ import annotations

from alembic import op
import sqlalchemy as sa

revision = "0048_add_master_taxonomy_hierarchy"
down_revision = "0047_master_product_type_code_global"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "master_taxonomy_node",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("parent_id", sa.Integer(), sa.ForeignKey("master_taxonomy_node.id"), nullable=True),
        sa.Column("node_kind", sa.String(32), nullable=False, server_default="category"),
        sa.Column("code", sa.String(128), nullable=False),
        sa.Column("name", sa.Text(), nullable=False),
        sa.Column("path_slug", sa.String(512), nullable=False),
        sa.Column("collection_registry_id", sa.Integer(), sa.ForeignKey("master_collection_registry.id"), 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("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("path_slug", name="uq_master_taxonomy_node_path_slug"),
    )
    op.create_index("ix_master_taxonomy_node_parent_id", "master_taxonomy_node", ["parent_id"])
    op.create_index("ix_master_taxonomy_node_node_kind", "master_taxonomy_node", ["node_kind"])
    op.create_index("ix_master_taxonomy_node_path_slug", "master_taxonomy_node", ["path_slug"])
    op.create_index("ix_master_taxonomy_node_code", "master_taxonomy_node", ["code"])

    op.create_table(
        "master_taxonomy_channel_link",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("taxonomy_node_id", sa.Integer(), sa.ForeignKey("master_taxonomy_node.id"), nullable=False),
        sa.Column("channel_code", sa.String(32), nullable=False),
        sa.Column("connection_id", sa.Integer(), nullable=True),
        sa.Column("taxonomy_kind", sa.String(32), nullable=False, server_default="category"),
        sa.Column("remote_id", sa.String(255), nullable=False),
        sa.Column("remote_parent_id", sa.String(255), nullable=True),
        sa.Column("remote_path", sa.Text(), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.UniqueConstraint(
            "taxonomy_node_id",
            "channel_code",
            "connection_id",
            "taxonomy_kind",
            name="uq_master_taxonomy_channel_link",
        ),
    )
    op.create_index("ix_master_taxonomy_channel_link_node", "master_taxonomy_channel_link", ["taxonomy_node_id"])
    op.create_index("ix_master_taxonomy_channel_link_channel", "master_taxonomy_channel_link", ["channel_code"])


def downgrade() -> None:
    op.drop_index("ix_master_taxonomy_channel_link_channel", table_name="master_taxonomy_channel_link")
    op.drop_index("ix_master_taxonomy_channel_link_node", table_name="master_taxonomy_channel_link")
    op.drop_table("master_taxonomy_channel_link")
    op.drop_index("ix_master_taxonomy_node_code", table_name="master_taxonomy_node")
    op.drop_index("ix_master_taxonomy_node_path_slug", table_name="master_taxonomy_node")
    op.drop_index("ix_master_taxonomy_node_node_kind", table_name="master_taxonomy_node")
    op.drop_index("ix_master_taxonomy_node_parent_id", table_name="master_taxonomy_node")
    op.drop_table("master_taxonomy_node")