"""add UNIQUE constraint on identity_persons.work_email

Revision ID: 018
Revises: 017
Create Date: 2026-04-15

Fixes #83: work_email had no UNIQUE constraint, allowing duplicate emails
which breaks Google SSO login (scalar_one() fails on duplicate match).
"""
import sqlalchemy as sa
from alembic import op


revision = '018'
down_revision = '017'
branch_labels = None
depends_on = None


def _constraint_exists(constraint_name: str, table_name: str) -> bool:
    """Check if a UNIQUE constraint exists (MySQL information_schema)."""
    conn = op.get_bind()
    result = conn.execute(
        sa.text(
            "SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "AND TABLE_NAME = :table AND CONSTRAINT_NAME = :name "
            "AND CONSTRAINT_TYPE = 'UNIQUE'"
        ),
        {"table": table_name, "name": constraint_name},
    )
    return (result.scalar() or 0) > 0


def _column_exists(table_name: str, column_name: str) -> bool:
    conn = op.get_bind()
    result = conn.execute(
        sa.text(
            "SELECT COUNT(*) FROM information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "AND TABLE_NAME = :table AND COLUMN_NAME = :column"
        ),
        {"table": table_name, "column": column_name},
    )
    return (result.scalar() or 0) > 0


def upgrade() -> None:
    # Skip entirely if identity_persons doesn't have the columns we need —
    # this migration only makes sense after 001 created the full schema.
    if not (
        _column_exists("identity_persons", "work_email")
        and _column_exists("identity_persons", "created_at")
    ):
        return

    # Resolve duplicates before adding UNIQUE. We cannot DELETE losers
    # because other tables (asset_items.assigned_to, etc.) FK into
    # identity_persons.id and deleting would orphan legitimate assets.
    # Instead, rename each loser's work_email to a guaranteed-unique sentinel
    # that keeps the original address embedded for later manual cleanup.
    #
    # Canonical row per email is picked by:
    #   1. Active (deleted_at IS NULL) beats soft-deleted.
    #   2. Then oldest created_at.
    #   3. Then smallest id (tie-breaker so equal timestamps resolve).
    op.execute(sa.text("""
        UPDATE identity_persons AS p
        JOIN identity_persons AS keeper
            ON keeper.work_email = p.work_email
            AND keeper.id != p.id
            AND (
                (keeper.deleted_at IS NULL AND p.deleted_at IS NOT NULL)
                OR (
                    (keeper.deleted_at IS NULL) = (p.deleted_at IS NULL)
                    AND (
                        keeper.created_at < p.created_at
                        OR (keeper.created_at = p.created_at AND keeper.id < p.id)
                    )
                )
            )
        SET p.work_email = CONCAT('__dup__', p.id, '@helix.invalid')
    """))

    if not _constraint_exists("uq_identity_persons_work_email", "identity_persons"):
        op.create_unique_constraint(
            "uq_identity_persons_work_email",
            "identity_persons",
            ["work_email"],
        )


def downgrade() -> None:
    if _constraint_exists("uq_identity_persons_work_email", "identity_persons"):
        op.drop_constraint(
            "uq_identity_persons_work_email",
            "identity_persons",
            type_="unique",
        )