"""HRIS lifecycle event — enforce UNIQUE (person_id, event_type, effective_date).

Revision ID: 312
Revises: 311
Create Date: 2026-06-21

``_upsert_lifecycle_event`` does select-then-insert on exactly
(person_id, event_type, effective_date) as if it were a natural key, but the
backing index ``ix_hr_lifecycle_events_person_type_date`` was only a plain
(non-unique) Index. Two concurrent SuccessFactors webhook redeliveries of the
same event therefore both passed the existence check and both INSERTed →
onboarding/offboarding coordinators fired twice (double provisioning / double
offboarding + duplicate immutable ledger rows).

This migration:

1. **Dedupes** any pre-existing race-duplicates — keeps the lowest ``id`` per
   (person_id, event_type, effective_date) group and deletes the rest. The
   code has always treated this triple as unique, so any duplicate is an
   erroneous race artifact, not a distinct legitimate event. No-op when there
   are no duplicates (the common case).
2. **(Re)creates** the index as UNIQUE. The non-unique index was created
   conditionally by migration 242 (``if not _index_exists``), so it may be
   absent on some databases — the drop is therefore guarded and the create is
   idempotent, mirroring the 242 convention.

Pairs with the begin_nested/IntegrityError guard added to
``HrSyncService._upsert_lifecycle_event`` in the same PR.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "312"
down_revision = "311"
branch_labels = None
depends_on = None

_INDEX = "ix_hr_lifecycle_events_person_type_date"
_TABLE = "hr_lifecycle_events"
_COLS = ["person_id", "event_type", "effective_date"]


def _index_exists(table: str, index: str) -> bool:
    conn = op.get_bind()
    result = conn.execute(
        sa.text(
            "SELECT COUNT(*) FROM information_schema.statistics "
            "WHERE table_schema = DATABASE() "
            "AND table_name = :t AND index_name = :i"
        ),
        {"t": table, "i": index},
    )
    return (result.scalar() or 0) > 0


def upgrade() -> None:
    # 1. Dedupe — keep MIN(id) per natural-key group, drop the rest. The
    #    derived-table wrapper sidesteps MySQL/TiDB's "can't DELETE from a
    #    table referenced in the subquery" restriction. No-op if no dups.
    op.execute(
        f"""
        DELETE FROM {_TABLE}
        WHERE id NOT IN (
            SELECT keep_id FROM (
                SELECT MIN(id) AS keep_id
                FROM {_TABLE}
                GROUP BY person_id, event_type, effective_date
            ) AS k
        )
        """
    )
    # 2. (Re)create as UNIQUE. The composite is the leftmost-covering index
    #    for the person_id FK, so MySQL refuses to drop it (error 1553) unless
    #    the FK has another covering index. Create a temporary standalone
    #    person_id index first, swap the composite to UNIQUE (which then covers
    #    the FK again), then drop the temp. The composite-drop is guarded
    #    because 242 created it conditionally — it may be absent on some DBs.
    _fk_idx = "ix_hr_lifecycle_events_person_fk"
    created_fk = False
    if not _index_exists(_TABLE, _fk_idx):
        op.create_index(_fk_idx, _TABLE, ["person_id"])
        created_fk = True
    if _index_exists(_TABLE, _INDEX):
        op.drop_index(_INDEX, table_name=_TABLE)
    op.create_index(_INDEX, _TABLE, _COLS, unique=True)
    # Unique composite now covers the FK; drop the temp helper for a clean
    # single-index end state (only if we created it).
    if created_fk and _index_exists(_TABLE, _fk_idx):
        op.drop_index(_fk_idx, table_name=_TABLE)


def downgrade() -> None:
    # The composite index is the leftmost-covering index for the person_id
    # FK, so MySQL refuses to drop it (error 1553) unless the FK has another
    # covering index. Create a standalone person_id index first (this also
    # restores the pre-unique FK-auto-index shape), then swap the composite
    # back to non-unique.
    _fk_idx = "ix_hr_lifecycle_events_person_fk"
    if not _index_exists(_TABLE, _fk_idx):
        op.create_index(_fk_idx, _TABLE, ["person_id"])
    if _index_exists(_TABLE, _INDEX):
        op.drop_index(_INDEX, table_name=_TABLE)
    op.create_index(_INDEX, _TABLE, _COLS, unique=False)