"""Add manual attribute vocabulary tables for locked allowed values.

Revision ID: 0065_add_manual_attribute_vocabulary_tables
Revises: 0064_add_master_product_membership_mode
Create Date: 2026-07-29 14:30:00.000000
"""

from __future__ import annotations

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

revision = "0065_add_manual_attribute_vocabulary_tables"
down_revision = "0064_add_master_product_membership_mode"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "manual_attribute_vocabulary",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("attribute_code", sa.String(length=128), nullable=False),
        sa.Column("label", sa.Text(), nullable=True),
        sa.Column("is_locked", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("unknown_policy", sa.String(length=32), nullable=False, server_default="drop"),
        sa.Column("covered_codes_json", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="100"),
        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("attribute_code", name="uq_manual_attribute_vocabulary_code"),
    )
    op.create_index(
        "ix_manual_attribute_vocabulary_attribute_code",
        "manual_attribute_vocabulary",
        ["attribute_code"],
    )
    op.create_index(
        "ix_manual_attribute_vocabulary_is_locked",
        "manual_attribute_vocabulary",
        ["is_locked"],
    )
    op.create_index(
        "ix_manual_attribute_vocabulary_is_active",
        "manual_attribute_vocabulary",
        ["is_active"],
    )

    op.create_table(
        "manual_attribute_vocabulary_value",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column(
            "vocabulary_id",
            sa.Integer(),
            sa.ForeignKey("manual_attribute_vocabulary.id"),
            nullable=False,
        ),
        sa.Column("value", sa.Text(), nullable=False),
        sa.Column("aliases_json", 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("vocabulary_id", "value", name="uq_manual_attribute_vocabulary_value"),
    )
    op.create_index(
        "ix_manual_attribute_vocabulary_value_vocabulary_id",
        "manual_attribute_vocabulary_value",
        ["vocabulary_id"],
    )

    # Seed assembled_or_rta locked vocabulary (Assembled | Unassembled/RTA).
    vocab = sa.table(
        "manual_attribute_vocabulary",
        sa.column("attribute_code", sa.String),
        sa.column("label", sa.Text),
        sa.column("is_locked", sa.Boolean),
        sa.column("unknown_policy", sa.String),
        sa.column("covered_codes_json", postgresql.JSONB),
        sa.column("is_active", sa.Boolean),
        sa.column("sort_order", sa.Integer),
        sa.column("notes", sa.Text),
    )
    op.execute(
        vocab.insert().values(
            attribute_code="assembled_or_rta",
            label="Assembled or RTA",
            is_locked=True,
            unknown_policy="drop",
            covered_codes_json=["assembled_or_rta", "assembly_type"],
            is_active=True,
            sort_order=10,
            notes="Locked vocabulary: Assembled or Unassembled/RTA only",
        )
    )
    conn = op.get_bind()
    vocab_id = conn.execute(
        sa.text("SELECT id FROM manual_attribute_vocabulary WHERE attribute_code = :code"),
        {"code": "assembled_or_rta"},
    ).scalar()
    value_table = sa.table(
        "manual_attribute_vocabulary_value",
        sa.column("vocabulary_id", sa.Integer),
        sa.column("value", sa.Text),
        sa.column("aliases_json", postgresql.JSONB),
        sa.column("sort_order", sa.Integer),
        sa.column("is_active", sa.Boolean),
    )
    op.execute(
        value_table.insert().values(
            [
                {
                    "vocabulary_id": vocab_id,
                    "value": "Assembled",
                    "aliases_json": ["Assembled", "assembled"],
                    "sort_order": 10,
                    "is_active": True,
                },
                {
                    "vocabulary_id": vocab_id,
                    "value": "Unassembled/RTA",
                    "aliases_json": [
                        "Unassembled/RTA",
                        "RTA",
                        "Unassembled",
                        "unassembled",
                        "Ready-to-Assemble",
                        "ready to assemble",
                        "ready-to-assemble",
                    ],
                    "sort_order": 20,
                    "is_active": True,
                },
            ]
        )
    )


def downgrade() -> None:
    op.drop_index(
        "ix_manual_attribute_vocabulary_value_vocabulary_id",
        table_name="manual_attribute_vocabulary_value",
    )
    op.drop_table("manual_attribute_vocabulary_value")
    op.drop_index("ix_manual_attribute_vocabulary_is_active", table_name="manual_attribute_vocabulary")
    op.drop_index("ix_manual_attribute_vocabulary_is_locked", table_name="manual_attribute_vocabulary")
    op.drop_index("ix_manual_attribute_vocabulary_attribute_code", table_name="manual_attribute_vocabulary")
    op.drop_table("manual_attribute_vocabulary")