"""procurement_snapshot_category_and_unique_constraint

Revision ID: 2e7a33888d03
Revises: 2dc76e3c9a58
Create Date: 2026-10-10

Fixes applied:
  DESIGN-01: Add UniqueConstraint on chassis_bom_snapshot(chassis_id, rm_id)
             so that duplicate snapshot rows cannot exist.
  SPEC §3.3:  Add 'category' column to chassis_bom_snapshot
             (Base BOM / Feature: <name> / Ad-hoc)
"""
from alembic import op
import sqlalchemy as sa


# revision identifiers
revision = '2e7a33888d03'
down_revision = '2dc76e3c9a58'
branch_labels = None
depends_on = None


def upgrade() -> None:
    conn = op.get_bind()

    # 1. Add 'category' column to chassis_bom_snapshot if it doesn't already exist
    try:
        with conn.begin_nested():
            col_exists = conn.execute(sa.text(
                "SELECT 1 FROM information_schema.columns "
                "WHERE table_name='chassis_bom_snapshot' AND column_name='category'"
            )).fetchone()
            if not col_exists:
                op.add_column(
                    'chassis_bom_snapshot',
                    sa.Column('category', sa.String(length=50), nullable=True)
                )
    except Exception as e:
        print(f"Notice: skipped 2e7a33888d03 add_column: {e}")

    # 2. Remove any existing duplicate (chassis_id, rm_id) rows before adding constraint.
    try:
        with conn.begin_nested():
            op.execute("""
                DELETE FROM chassis_bom_snapshot
                WHERE id NOT IN (
                    SELECT DISTINCT ON (chassis_id, rm_id) id
                    FROM chassis_bom_snapshot
                    ORDER BY chassis_id, rm_id, reserved_qty DESC, created_at DESC
                )
            """)
    except Exception as e:
        print(f"Notice: skipped 2e7a33888d03 deduplicate: {e}")

    # 3. Add the unique constraint if not already created
    try:
        with conn.begin_nested():
            constraint_exists = conn.execute(sa.text(
                "SELECT 1 FROM information_schema.table_constraints "
                "WHERE table_name='chassis_bom_snapshot' AND constraint_name='uq_chassis_bom_snapshot'"
            )).fetchone()
            if not constraint_exists:
                op.create_unique_constraint(
                    'uq_chassis_bom_snapshot',
                    'chassis_bom_snapshot',
                    ['chassis_id', 'rm_id']
                )
    except Exception as e:
        print(f"Notice: skipped 2e7a33888d03 create_unique_constraint: {e}")


def downgrade() -> None:
    conn = op.get_bind()
    constraint_exists = conn.execute(sa.text(
        "SELECT 1 FROM information_schema.table_constraints "
        "WHERE table_name='chassis_bom_snapshot' AND constraint_name='uq_chassis_bom_snapshot'"
    )).fetchone()
    if constraint_exists:
        op.drop_constraint('uq_chassis_bom_snapshot', 'chassis_bom_snapshot', type_='unique')

    col_exists = conn.execute(sa.text(
        "SELECT 1 FROM information_schema.columns "
        "WHERE table_name='chassis_bom_snapshot' AND column_name='category'"
    )).fetchone()
    if col_exists:
        op.drop_column('chassis_bom_snapshot', 'category')