"""Replace IT escalation with a daily reminder to the owner.

Revision ID: 531
Revises: 530

Policy change (2026-08-24, product decision): an unanswered owner-
confirmation prompt is no longer escalated to IT admins after 14 days.
Instead the SAME card is re-sent to the owner **every day** until they
answer it. Rationale: the ask belongs to the owner, and handing 90 of
someone else's unanswered asks to 5 admins turned the admins into a
chase team without moving a single decision.

Three things happen here:

1. ``last_reminded_at`` / ``reminder_count`` on
   ``cmdb_cloud_scope_owner_confirmations`` — the daily cron's
   at-most-once-per-day guard, and the count the timeline renders
   ("已提醒 N 次"). ``notified_at`` keeps its original meaning (when the
   prompt was FIRST sent) so "how long has this been open" stays
   answerable.

2. Existing ``status='escalated'`` rows are re-opened to ``pending``.
   Under the new policy "escalated" is not a state anything can leave:
   the respond endpoints only accept a PENDING row, so an escalated row
   was a dead end — the owner could not answer it and nothing would ask
   them again. ``escalated_at`` is deliberately left in place as
   history.

3. Every open row is stamped ``last_reminded_at = UTC_TIMESTAMP()`` so
   deploying this does NOT fire a reminder wave inside the deploy
   window; the first reminders go out on the next scheduled tick.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "531"
down_revision = "530"
branch_labels = None
depends_on = None

_TABLE = "cmdb_cloud_scope_owner_confirmations"


def _columns() -> set[str]:
    bind = op.get_bind()
    rows = bind.execute(
        sa.text(
            "SELECT COLUMN_NAME FROM information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t"
        ),
        {"t": _TABLE},
    ).fetchall()
    return {r[0] for r in rows}


def upgrade() -> None:
    existing = _columns()

    if "last_reminded_at" not in existing:
        op.add_column(
            _TABLE,
            sa.Column("last_reminded_at", sa.DateTime(timezone=True), nullable=True),
        )
    if "reminder_count" not in existing:
        op.add_column(
            _TABLE,
            sa.Column(
                "reminder_count",
                sa.Integer(),
                nullable=False,
                server_default="0",
            ),
        )

    bind = op.get_bind()

    # 2. Re-open escalated rows — see the module docstring. Scoped to the
    #    two prompt kinds; leaf ``escalated_to_it`` rows are history and
    #    are never 'escalated' themselves.
    reopened = bind.execute(
        sa.text(
            f"UPDATE {_TABLE} SET status = 'pending' "
            "WHERE status = 'escalated' "
            "AND kind IN ('semiannual_pending', 'zero_spend_pending')"
        )
    ).rowcount
    print(f"531: re-opened {reopened} escalated prompt(s) to pending")

    # 3. Silence the deploy window.
    stamped = bind.execute(
        sa.text(
            f"UPDATE {_TABLE} SET last_reminded_at = UTC_TIMESTAMP() "
            "WHERE status = 'pending' AND last_reminded_at IS NULL"
        )
    ).rowcount
    print(f"531: stamped {stamped} open prompt(s) as reminded-now")


def downgrade() -> None:
    existing = _columns()
    if "reminder_count" in existing:
        op.drop_column(_TABLE, "reminder_count")
    if "last_reminded_at" in existing:
        op.drop_column(_TABLE, "last_reminded_at")
    # The escalated → pending flip is NOT reversed: which rows were
    # originally escalated is recoverable from ``escalated_at``, and
    # re-closing them would re-create the dead-end state this migration
    # exists to remove.