"""OffboardingReminderLog — enforce UNIQUE (process_id, reminder_kind).

Revision ID: 179
Revises: 178
Create Date: 2026-05-18

Why this exists
---------------
``OffboardingReminderLog`` is the idempotency marker for leaver
reminders (T-7 / T-3 / T-1 / escalation / IT grace window / DNS
pre-leaver handover buckets). The cron handler in
[handlers.py:run_dns_pre_leaver_handover](server/app/scheduled_jobs/handlers.py)
and the manager-escalation worker both rely on
``(process_id, reminder_kind)`` being de facto unique — they do a
``SELECT ... WHERE process_id=? AND reminder_kind=?`` to dedup, then
``session.add(OffboardingReminderLog(...))`` if absent.

The cron itself runs under the scheduled-job single-flight lock, so the
race window is closed for the *cron* path. But any future code path
that writes a reminder row outside the cron (manual admin re-send, a
parallel handover trigger, a backfill script) would race silently and
produce duplicate rows because there was no DB-level constraint.

The fix is to add the unique constraint the application has always
assumed. Pre-existing duplicates (if any) get collapsed first so the
constraint can be applied; we keep the row with the lowest ``id`` per
group so the earliest fire is preserved and later (presumably
identical) rows are discarded.

Idempotency
-----------
``_constraint_exists`` guards the ALTER so re-runs are no-ops. The
dedup ``DELETE`` is also harmless on a clean table because the
subquery returns zero rows when no group has > 1 row.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "179"
down_revision = "178"
branch_labels = None
depends_on = None


def _constraint_exists(bind, table: str, name: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLE_CONSTRAINTS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND CONSTRAINT_NAME = :c LIMIT 1"
        ),
        {"t": table, "c": name},
    ).scalar() is not None


def _index_exists(bind, table: str, index: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.STATISTICS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND INDEX_NAME = :i LIMIT 1"
        ),
        {"t": table, "i": index},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    # 1. Collapse pre-existing duplicates (if any). Keep the row with
    #    the lowest id per (process_id, reminder_kind) — that's the
    #    earliest fire, which is what the cron's "at most once" intent
    #    actually wants to preserve. Use a self-join via a subquery on
    #    MIN(id) GROUP BY (process_id, reminder_kind) so the DELETE is
    #    bounded to provably-redundant rows.
    bind.execute(
        sa.text(
            """
            DELETE r
            FROM offboarding_reminder_log AS r
            JOIN (
                SELECT MIN(id) AS keep_id, process_id, reminder_kind
                FROM offboarding_reminder_log
                GROUP BY process_id, reminder_kind
                HAVING COUNT(*) > 1
            ) AS d
              ON r.process_id = d.process_id
             AND r.reminder_kind = d.reminder_kind
             AND r.id <> d.keep_id
            """,
        ),
    )

    # 2. Add the unique constraint. Idempotent — skip if a re-run.
    #    MySQL / TiDB record a UNIQUE constraint as BOTH a row in
    #    TABLE_CONSTRAINTS and an index in STATISTICS (sometimes only
    #    one of the two, depending on how the constraint was created).
    #    Check both so a re-apply after a partial prior run still
    #    short-circuits cleanly — same shape as migration 116.
    name = "uq_offboarding_reminder_log_process_kind"
    table = "offboarding_reminder_log"
    if not _constraint_exists(bind, table, name) and not _index_exists(
        bind, table, name,
    ):
        op.create_unique_constraint(name, table, ["process_id", "reminder_kind"])


def downgrade() -> None:
    bind = op.get_bind()
    name = "uq_offboarding_reminder_log_process_kind"
    table = "offboarding_reminder_log"
    if _constraint_exists(bind, table, name) or _index_exists(bind, table, name):
        op.drop_constraint(name, table, type_="unique")