"""Regression (2026-06-16): recreating a soft-deleted mail group's email
must return a clean 409, not a raw DB-level 500.

The ``email`` column carries a plain UNIQUE index that also covers
soft-deleted (status='deleted') tombstones, so a deleted group still
holds its address. The create pre-flight's active-only existence check
can't see the tombstone, so without an explicit include_deleted guard
the INSERT hits errno 1062 and surfaces as a bare 500. The guard turns
that into a clean EMAIL_GROUP_EXISTS / 409 before any Google call.
"""
from __future__ import annotations

import uuid

import pytest
from sqlalchemy import func, select

from app.core.exceptions import AppException
from app.domains.email_group.models import EmailGroup
from app.domains.email_group.schemas import EmailGroupCreateRequest
from app.domains.email_group.service import EmailGroupService
from app.identity.person_models import IdentityPerson


@pytest.mark.asyncio
async def test_recreate_deleted_group_email_returns_clean_409(db_session):
    requester = IdentityPerson(
        id=uuid.uuid4(),
        work_email="req@capastro.com",
        display_name="Requester",
        employment_status="active",
        source_system="successfactors",
        hris_person_id=f"sf-{uuid.uuid4().hex[:8]}",
    )
    # Soft-deleted tombstone still holding the address. created_by=None so
    # it can't count toward the requester's create rate limit.
    db_session.add(requester)
    db_session.add(EmailGroup(
        id=uuid.uuid4(),
        email="reuse@capastro.com",
        name="Old group",
        status="deleted",
        created_by=None,
    ))
    await db_session.flush()

    svc = EmailGroupService(db_session)
    body = EmailGroupCreateRequest(
        local_part="reuse",
        suffix="capastro.com",  # in the built-in default whitelist
        name="New group",
        reason="trying to reuse a deleted address",
    )

    with pytest.raises(AppException) as ei:
        await svc.create_mail_group(body=body, requester=requester, trace_id="t")
    assert ei.value.status_code == 409
    assert ei.value.code == "EMAIL_GROUP_EXISTS"

    # No new row was created — still exactly the one tombstone.
    n = (await db_session.execute(
        select(func.count()).select_from(EmailGroup).where(
            EmailGroup.email == "reuse@capastro.com",
        )
    )).scalar_one()
    assert n == 1