"""add unique constraints on waitlist email and phone

Revision ID: d4e5f6a7b8c9
Revises: a1b2c3d4e5f6
Create Date: 2026-02-16

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


revision: str = "d4e5f6a7b8c9"
down_revision: Union[str, Sequence[str], None] = "a1b2c3d4e5f6"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Deduplicate: keep one row per email (prefer has_applied, then partner source, then oldest)
    op.execute("""
        DELETE FROM waitlist
        WHERE id IN (
            SELECT id FROM (
                SELECT id,
                    ROW_NUMBER() OVER (
                        PARTITION BY LOWER(email)
                        ORDER BY
                            CASE WHEN has_applied THEN 0 ELSE 1 END,
                            CASE WHEN source IN ('corporate','edu') THEN 0 ELSE 1 END,
                            created_at
                    ) AS rn
                FROM waitlist
            ) sub
            WHERE rn > 1
        )
    """)
    # Deduplicate phone similarly
    op.execute("""
        DELETE FROM waitlist
        WHERE id IN (
            SELECT id FROM (
                SELECT id,
                    ROW_NUMBER() OVER (
                        PARTITION BY phone
                        ORDER BY
                            CASE WHEN has_applied THEN 0 ELSE 1 END,
                            CASE WHEN source IN ('corporate','edu') THEN 0 ELSE 1 END,
                            created_at
                    ) AS rn
                FROM waitlist
            ) sub
            WHERE rn > 1
        )
    """)
    # Add unique constraints (case-insensitive for email)
    op.execute("CREATE UNIQUE INDEX uq_waitlist_email_lower ON waitlist (LOWER(email))")
    op.create_unique_constraint("uq_waitlist_phone", "waitlist", ["phone"])


def downgrade() -> None:
    op.drop_constraint("uq_waitlist_phone", "waitlist", type_="unique")
    op.execute("DROP INDEX IF EXISTS uq_waitlist_email_lower")