"""Regression for prod incident 2026-05-26:

Full HRIS sync confirm crashed with
``IntegrityError (1062): Duplicate entry for key
identity_external_identities.uq_identity_external_person_provider``
on a batch of 438 events. Root cause:
``_upsert_external_identity`` filtered the SELECT by
``deleted_at IS NULL``, missing soft-deleted rows that the UNIQUE
(person_id, provider) constraint still considers a conflict. The
fix removes the filter and revives the tombstoned row on hit.

Setup mirrors the prod scenario: a person row already exists
(soft-deleted person reused by ``_upsert_person`` which is
deleted_at-tolerant) and carries a soft-deleted SF
``IdentityExternalIdentity`` row. Re-applying an HRIS event for that
person must NOT raise IntegrityError; the soft-deleted row must be
revived in-place.
"""
from __future__ import annotations

import uuid
from datetime import UTC, datetime

import pytest
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.domains.hr.service import SuccessFactorsLifecycleService
from app.identity.person_models import IdentityExternalIdentity, IdentityPerson


@pytest.mark.asyncio
async def test_upsert_external_identity_revives_soft_deleted(
    db_session: AsyncSession,
):
    """A soft-deleted (person_id, provider='successfactors') row must
    be revived in-place — not blocked by UNIQUE constraint via an
    INSERT path that ignored the tombstone."""
    pid = uuid.uuid4()
    person = IdentityPerson(
        id=pid,
        source_system="successfactors",
        hris_person_id="ECadmin",
        work_email="ecadmin@pingcap.com",
        display_name="EC Admin",
        employment_status="active",
    )
    db_session.add(person)
    await db_session.flush()

    # Pre-existing soft-deleted SF identity for this person — mirrors
    # the 12 tombstoned rows found in prod.
    tombstoned = IdentityExternalIdentity(
        id=uuid.uuid4(),
        person_id=pid,
        provider="successfactors",
        provider_subject="ECadmin",
        provider_username="ECadmin",
        identity_status="active",
        deleted_at=datetime(2026, 5, 7, 9, 45, 38, tzinfo=UTC),
    )
    db_session.add(tombstoned)
    await db_session.commit()

    service = SuccessFactorsLifecycleService(db_session)
    # Direct call to the helper under test — the full
    # ``confirm_batch`` path would also exercise master-data lookups
    # we don't care about here.
    await service._upsert_external_identity(
        person=person,
        user_id="ECadmin",
        user={"email": "ecadmin@pingcap.com", "lastModifiedDateTime": None},
    )
    await db_session.commit()

    # Exactly ONE row for this (person, provider) now, and it's the
    # original row revived — same id, deleted_at cleared,
    # identity_status='active'.
    rows = (await db_session.execute(
        select(IdentityExternalIdentity).where(
            IdentityExternalIdentity.person_id == pid,
            IdentityExternalIdentity.provider == "successfactors",
        )
    )).scalars().all()
    assert len(rows) == 1, (
        "duplicate INSERT path bug regressed — pre-fix this test fails "
        "with IntegrityError 1062 because the SELECT used to filter "
        "out the tombstone and the subsequent INSERT collided on "
        "uq_identity_external_person_provider."
    )
    revived = rows[0]
    assert revived.id == tombstoned.id, "must revive, not replace"
    assert revived.deleted_at is None, "tombstone must be cleared"
    assert revived.identity_status == "active"
    assert revived.provider_subject == "ECadmin"


@pytest.mark.asyncio
async def test_upsert_external_identity_creates_when_truly_new(
    db_session: AsyncSession,
):
    """Negative control: when no row (active OR tombstoned) exists,
    the helper still creates a fresh row as before — the fix doesn't
    break first-time inserts."""
    pid = uuid.uuid4()
    person = IdentityPerson(
        id=pid,
        source_system="successfactors",
        hris_person_id="NEW001",
        work_email="new001@pingcap.com",
        display_name="New User",
        employment_status="active",
    )
    db_session.add(person)
    await db_session.commit()

    service = SuccessFactorsLifecycleService(db_session)
    await service._upsert_external_identity(
        person=person,
        user_id="NEW001",
        user={"email": "new001@pingcap.com", "lastModifiedDateTime": None},
    )
    await db_session.commit()

    row = (await db_session.execute(
        select(IdentityExternalIdentity).where(
            IdentityExternalIdentity.person_id == pid,
            IdentityExternalIdentity.provider == "successfactors",
        )
    )).scalars().first()
    assert row is not None
    assert row.deleted_at is None
    assert row.provider_subject == "NEW001"
    assert row.provider_username == "NEW001"