"""Mark orphan welcome-email outbox rows as ``dead`` so they stop retrying.

Context (prod 2026-05-02): the HRIS hourly incremental sync was firing
welcome emails for active employees whose stored onboard lifecycle row's
``effective_date`` did not match the SF ``startDate`` currently being
seen — those events get classified ``action='update'`` and the original
post-confirm allow-list ``{create, reactivate, update}`` sent welcome
mail on every tick. The trigger has since been tightened (see
``server/app/domains/hr/service.py``) to ``{create, reactivate}`` only,
but rows already enqueued in ``notification_outbox`` continue to retry
on the cron until they hit ``max_attempts`` — wasted SMTP attempts and
noisy admin Outbox UI.

This script flips those orphan welcome rows to ``status='dead'`` so the
retry handler skips them and the admin Outbox surfaces them under the
already-existing dead-letter view, where they can be inspected and
purged manually.

Scope (defensive — narrowed deliberately so the script can NEVER touch
unrelated rows):

    template_key = 'welcome'
    AND status IN ('pending', 'failed', 'partial')
    AND attempts >= 1                     -- never blow away a row
                                          -- queued by THIS confirm
    AND created_at < :cutoff              -- only rows enqueued before
                                          -- the fix landed (caller-set)

Defaults to ``--dry-run``. Run with ``--commit`` to persist.
"""
from __future__ import annotations

import argparse
import asyncio
from datetime import UTC, datetime

from sqlalchemy import select, update

import app.audit.models  # noqa: F401
import app.auth.models  # noqa: F401
import app.domains.hr.models  # noqa: F401
import app.identity.person_models  # noqa: F401
import app.notifications.models  # noqa: F401
from app.core.database import async_session_factory
from app.notifications.models import (
    STATUS_DEAD,
    STATUS_FAILED,
    STATUS_PARTIAL,
    STATUS_PENDING,
    NotificationOutbox,
)


async def run(*, dry_run: bool, cutoff: datetime) -> None:
    async with async_session_factory() as session:
        rows = (await session.execute(
            select(NotificationOutbox)
            .where(NotificationOutbox.template_key == "welcome")
            .where(NotificationOutbox.status.in_(
                [STATUS_PENDING, STATUS_FAILED, STATUS_PARTIAL],
            ))
            .where(NotificationOutbox.attempts >= 1)
            .where(NotificationOutbox.created_at < cutoff)
            .order_by(NotificationOutbox.created_at.asc())
        )).scalars().all()

        print(f"Found {len(rows)} orphan welcome rows (cutoff={cutoff.isoformat()})")
        for row in rows:
            print(
                f"  {row.created_at.isoformat()}  "
                f"{(row.recipient_email or str(row.recipient_person_id)):40}  "
                f"status={row.status:8}  attempts={row.attempts}/{row.max_attempts}"
            )

        if not rows:
            return

        if dry_run:
            print("\n[DRY-RUN] No changes persisted. Re-run with --commit.")
            return

        await session.execute(
            update(NotificationOutbox)
            .where(NotificationOutbox.id.in_([r.id for r in rows]))
            .values(
                status=STATUS_DEAD,
                next_attempt_at=None,
                last_error=(
                    "[manual] dead-lettered by "
                    "dead_letter_orphan_welcome_outbox: orphan welcome "
                    "from action=update misfire (fixed 2026-05-02)"
                ),
            )
        )
        await session.commit()
        print(f"\n[COMMIT] Dead-lettered {len(rows)} row(s).")


def _main() -> None:
    ap = argparse.ArgumentParser(description=__doc__)
    ap.add_argument("--commit", action="store_true", help="persist changes")
    ap.add_argument(
        "--cutoff",
        default=datetime.now(UTC).isoformat(),
        help=(
            "ISO-8601 cutoff timestamp; only rows with created_at < cutoff "
            "are touched. Default: now (catches everything pre-deploy)."
        ),
    )
    args = ap.parse_args()
    cutoff = datetime.fromisoformat(args.cutoff)
    if cutoff.tzinfo is None:
        cutoff = cutoff.replace(tzinfo=UTC)
    asyncio.run(run(dry_run=not args.commit, cutoff=cutoff))


if __name__ == "__main__":
    _main()