"""add unique partial index on (merchant_id, code) for products_category

Revision ID: hwui002c
Revises: hwui002b
Create Date: 2026-04-02

Prevents duplicate category codes per merchant. Uses a partial index so
soft-deleted rows are excluded (deleted_at IS NULL).

Before creating the index, soft-deletes any duplicate active rows keeping
the one with the lowest id (earliest created) to make the migration
idempotent and safe against pre-existing dirty data.
"""

from alembic import op
from sqlalchemy import text

revision = "hwui002c"
down_revision = "hwui002b"
branch_labels = None
depends_on = None


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

    # Soft-delete duplicate active rows, keeping the one with the lowest id
    # per (merchant_id, code) pair.
    conn.execute(
        text(
            """
            UPDATE products_category
            SET deleted_at = NOW()
            WHERE deleted_at IS NULL
              AND id NOT IN (
                  SELECT MIN(id)
                  FROM products_category
                  WHERE deleted_at IS NULL
                  GROUP BY merchant_id, code
              )
            """
        )
    )

    # Now create the partial unique index — no duplicates remain.
    op.execute(
        """
        CREATE UNIQUE INDEX uq_products_category_merchant_code
        ON products_category (merchant_id, code)
        WHERE deleted_at IS NULL
        """
    )


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS uq_products_category_merchant_code")