"""People HR does not want pre-selected in the leave-expiry reminder.

Revision ID: 516
Revises: 515
Create Date: 2026-08-18

The cohort is derived from HRIS leave data, and derived-from-data is exactly
right for "who still has days about to lapse" — but it cannot know that a
particular person is on long-term leave, is mid-transfer, or has already been
handled offline. Before this table the only way to keep them out of a send was
to remember to untick them by hand, every time, from a list of ~145 rows.

Deliberately a **default**, not a block: the row makes them start unticked and
labelled, the same shape as the existing ``reminded_recently`` guard. HR can
still tick them for a specific send — the platform's job is to remember the
standing decision, not to overrule the person holding the mouse.

Global (not per-batch), mirroring ``attendance_roster_overrides``: the standing
decision applies to every subsequent send until HR removes it. ``person_id`` is
a plain GUID with no DB FK (TiDB-FK-safe, per the migration-324 convention).
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

from app.core.migration_helpers import id_type_for, table_kw_for

revision = "516"
down_revision = "515"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "hr_leave_reminder_exclusions",
        sa.Column("id", id_type_for(), primary_key=True),
        sa.Column("person_id", id_type_for("identity_persons"), nullable=False),
        # Why this person is excluded. Optional, but it is the only thing that
        # lets the NEXT person to open this list decide whether the exclusion is
        # still true — an unexplained row outlives its reason and quietly drops
        # someone from every send forever.
        sa.Column("note", sa.Text(), nullable=True),
        sa.Column("created_by", id_type_for("identity_persons"), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.func.now(),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.func.now(),
            onupdate=sa.func.now(),
        ),
        # One standing decision per person; re-adding edits the note in place.
        sa.UniqueConstraint(
            "person_id", name="uq_hr_leave_reminder_exclusions_person",
        ),
        **table_kw_for("hr_leave_reminder_exclusions"),
    )


def downgrade() -> None:
    op.drop_table("hr_leave_reminder_exclusions")