"""Attribute seed review tables and canonical/UI/lifecycle fields.

Revision ID: 0071_add_v2_attribute_seed_review
Revises: 0070_add_v2_brand_collection
Create Date: 2026-08-21 20:30:00.000000
"""

from __future__ import annotations

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

revision = "0071_add_v2_attribute_seed_review"
down_revision = "0070_add_v2_brand_collection"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column(
        "v2_attribute",
        sa.Column("ui_control", sa.String(length=64), nullable=False, server_default="text_field"),
    )
    op.add_column(
        "v2_attribute",
        sa.Column("lifecycle_status", sa.String(length=32), nullable=False, server_default="active"),
    )
    op.add_column(
        "v2_attribute",
        sa.Column("aliases", postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default=sa.text("'[]'::jsonb")),
    )
    op.create_check_constraint(
        "v2_attribute_lifecycle_status_valid",
        "v2_attribute",
        "lifecycle_status IN ('active','inactive','deprecated')",
    )

    op.add_column(
        "v2_attribute_value",
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default=sa.text("0")),
    )
    op.add_column(
        "v2_attribute_value",
        sa.Column("lifecycle_status", sa.String(length=32), nullable=False, server_default="active"),
    )
    op.add_column(
        "v2_attribute_value",
        sa.Column("aliases", postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default=sa.text("'[]'::jsonb")),
    )
    op.create_check_constraint(
        "v2_attribute_value_lifecycle_status_valid",
        "v2_attribute_value",
        "lifecycle_status IN ('active','inactive','deprecated')",
    )

    op.add_column(
        "v2_channel_attribute_mapping",
        sa.Column("representation", postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default=sa.text("'{}'::jsonb")),
    )
    op.add_column(
        "v2_channel_attribute_mapping",
        sa.Column("compatibility_status", sa.String(length=32), nullable=False, server_default="unresolved"),
    )

    op.create_table(
        "v2_attribute_seed_candidate",
        sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("source_code", sa.String(length=255), nullable=False),
        sa.Column("proposed_code", sa.String(length=255), nullable=False),
        sa.Column("proposed_label", sa.Text(), nullable=False),
        sa.Column("discovered_data_type", sa.String(length=64), nullable=False),
        sa.Column("proposed_data_type", sa.String(length=64), nullable=False),
        sa.Column("proposed_ui_control", sa.String(length=64), nullable=False, server_default="text_field"),
        sa.Column("is_variant_axis", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        sa.Column("is_channel_required", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        sa.Column("lifecycle_status", sa.String(length=32), nullable=False, server_default="active"),
        sa.Column("review_status", sa.String(length=32), nullable=False, server_default="pending_review"),
        sa.Column("merge_into_code", sa.String(length=255), nullable=True),
        sa.Column("sources", postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default=sa.text("'[]'::jsonb")),
        sa.Column("aliases", postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default=sa.text("'[]'::jsonb")),
        sa.Column("product_usage_count", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("value_count", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("channel_map_count", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("magento_frontend_input", sa.Text(), nullable=True),
        sa.Column("magento_attribute_code", sa.Text(), nullable=True),
        sa.Column("shopify_representation", postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("conflict_reasons", postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default=sa.text("'[]'::jsonb")),
        sa.Column("proposed_action", sa.String(length=32), nullable=False, server_default="keep"),
        sa.Column("duplicate_group", sa.Text(), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("applied_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.UniqueConstraint("source_code", name="uq_v2_attribute_seed_candidate_source_code"),
        sa.CheckConstraint(
            "review_status IN ('pending_review','approved','modified','merged','excluded','conflict')",
            name="v2_attribute_seed_candidate_review_status_valid",
        ),
        sa.CheckConstraint(
            "lifecycle_status IN ('active','inactive','deprecated')",
            name="v2_attribute_seed_candidate_lifecycle_status_valid",
        ),
    )

    op.create_table(
        "v2_attribute_value_seed_candidate",
        sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("attribute_source_code", sa.String(length=255), sa.ForeignKey("v2_attribute_seed_candidate.source_code", ondelete="CASCADE"), nullable=False),
        sa.Column("source_value_code", sa.String(length=255), nullable=False),
        sa.Column("proposed_value_code", sa.String(length=255), nullable=False),
        sa.Column("proposed_label", sa.Text(), nullable=False),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("review_status", sa.String(length=32), nullable=False, server_default="pending_review"),
        sa.Column("merge_into_value_code", sa.String(length=255), nullable=True),
        sa.Column("aliases", postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default=sa.text("'[]'::jsonb")),
        sa.Column("product_usage_count", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("lifecycle_status", sa.String(length=32), nullable=False, server_default="active"),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.UniqueConstraint("attribute_source_code", "source_value_code", name="uq_v2_attribute_value_seed_candidate_attr_value"),
        sa.CheckConstraint(
            "review_status IN ('pending_review','approved','modified','merged','excluded')",
            name="v2_attribute_value_seed_candidate_review_status_valid",
        ),
        sa.CheckConstraint(
            "lifecycle_status IN ('active','inactive','deprecated')",
            name="v2_attribute_value_seed_candidate_lifecycle_status_valid",
        ),
    )

    op.create_table(
        "v2_attribute_seed_mapping_candidate",
        sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("attribute_source_code", sa.String(length=255), sa.ForeignKey("v2_attribute_seed_candidate.source_code", ondelete="CASCADE"), nullable=False),
        sa.Column("channel", sa.String(length=64), nullable=False),
        sa.Column("channel_attribute_code", sa.Text(), nullable=True),
        sa.Column("channel_attribute_id", sa.Text(), nullable=True),
        sa.Column("representation", postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("compatibility_status", sa.String(length=32), nullable=False, server_default="unresolved"),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.UniqueConstraint("channel", "attribute_source_code", name="uq_v2_attribute_seed_mapping_candidate_channel_attr"),
        sa.CheckConstraint(
            "compatibility_status IN ('ok','incompatible','unresolved')",
            name="v2_attribute_seed_mapping_candidate_compat_valid",
        ),
    )


def downgrade() -> None:
    op.drop_table("v2_attribute_seed_mapping_candidate")
    op.drop_table("v2_attribute_value_seed_candidate")
    op.drop_table("v2_attribute_seed_candidate")
    op.drop_column("v2_channel_attribute_mapping", "compatibility_status")
    op.drop_column("v2_channel_attribute_mapping", "representation")
    op.drop_constraint("v2_attribute_value_lifecycle_status_valid", "v2_attribute_value", type_="check")
    op.drop_column("v2_attribute_value", "aliases")
    op.drop_column("v2_attribute_value", "lifecycle_status")
    op.drop_column("v2_attribute_value", "sort_order")
    op.drop_constraint("v2_attribute_lifecycle_status_valid", "v2_attribute", type_="check")
    op.drop_column("v2_attribute", "aliases")
    op.drop_column("v2_attribute", "lifecycle_status")
    op.drop_column("v2_attribute", "ui_control")