"""Unified expiry-alert state for the graduated cadence + escalation engine.

The cert / domain expiry crons each had their own per-resource dedup
table (``cmdb_cert_expiry_alert_state`` keyed by cert_id,
``cmdb_domain_expiry_alert_state`` keyed by domain_id) with an identical
shape. The cert-expiry-governance theme (PR2/2) replaces the weak
"one alert per coarse bucket transition" model with a graduated cadence
(T-30 / T-14 once, then DAILY inside T-7 and while expired) + last-mile
escalation (本人 + 域名负责人 + lead) + an acknowledge/snooze loop, driven
by a SHARED engine over both resource types.

This migration introduces ONE unified table
``cmdb_expiry_alert_state(resource_type, resource_id, …)`` carrying the
extra signals the engine needs:

* ``last_alerted_on`` (DATE) — supports daily dedup inside the escalation
  window (re-send once per calendar day, not once per bucket).
* ``escalation_started_on`` (DATE) — non-null ⇒ the resource entered the
  escalation window UNDER the cron's watch (a go-forward transition), so
  it is eligible for daily escalated sends. NULL ⇒ not in the window, or
  it was seeded as backlog at first-enable (suppressed) — this is the
  mechanism that stops first-enable from carpet-bombing the existing
  expired/expiring backlog.
* ``acknowledged_until`` (DATE) + who/when — an owner who marks a cert /
  domain "renewing" snoozes all alerts (incl. escalation) until that date.

The two old tables are DROPPED. They are pure dedup state with no audit
value, and both are EMPTY in every environment: the
``network.user_notifications_enabled`` kill switch has always been false,
so both crons short-circuit at entry before ever writing a state row.
Downgrade recreates the two old tables (empty).

Revision ID: 362
Revises: 361
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "362"
down_revision = "361"
branch_labels = None
depends_on = None

UNIFIED = "cmdb_expiry_alert_state"


def _table_exists(bind, name: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.tables "
            "WHERE table_schema = DATABASE() AND table_name = :n"
        ),
        {"n": name},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, UNIFIED):
        op.create_table(
            UNIFIED,
            # ('certificate'|'domain', resource uuid) — composite PK.
            sa.Column("resource_type", sa.String(16), nullable=False),
            sa.Column("resource_id", sa.String(36), nullable=False),
            sa.Column("last_bucket", sa.String(16), nullable=False),
            # Plain String(36) (no FK) — mirrors the old state tables and
            # sidesteps the TiDB CHAR↔collation FK trap (error 3780).
            sa.Column("last_owner_person_id", sa.String(36), nullable=True),
            # Date of the last ACTUAL send — daily dedup inside the window.
            sa.Column("last_alerted_on", sa.Date(), nullable=True),
            # Non-null ⇒ entered escalation window under the cron's watch.
            sa.Column("escalation_started_on", sa.Date(), nullable=True),
            # Snooze: suppress all alerts through this date when an owner
            # marks the resource "renewing".
            sa.Column("acknowledged_until", sa.Date(), nullable=True),
            sa.Column("acknowledged_by_person_id", sa.String(36), nullable=True),
            sa.Column(
                "acknowledged_at", sa.DateTime(timezone=True), nullable=True,
            ),
            sa.Column(
                "last_alerted_at", sa.DateTime(timezone=True),
                server_default=sa.func.now(), nullable=False,
            ),
            sa.PrimaryKeyConstraint("resource_type", "resource_id"),
        )

    # Drop the two superseded per-type tables (empty in every env — the
    # kill switch has always short-circuited the write path).
    for old in ("cmdb_cert_expiry_alert_state", "cmdb_domain_expiry_alert_state"):
        if _table_exists(bind, old):
            op.drop_table(old)


def downgrade() -> None:
    bind = op.get_bind()
    # Recreate the two old tables (empty) so a downgrade restores the
    # pre-361 schema shape.
    if not _table_exists(bind, "cmdb_cert_expiry_alert_state"):
        op.create_table(
            "cmdb_cert_expiry_alert_state",
            sa.Column("cert_id", sa.String(36), nullable=False),
            sa.Column("last_bucket", sa.String(16), nullable=False),
            sa.Column("last_owner_person_id", sa.String(36), nullable=True),
            sa.Column(
                "last_alerted_at", sa.DateTime(timezone=True),
                server_default=sa.func.now(), nullable=False,
            ),
            sa.PrimaryKeyConstraint("cert_id"),
        )
    if not _table_exists(bind, "cmdb_domain_expiry_alert_state"):
        op.create_table(
            "cmdb_domain_expiry_alert_state",
            sa.Column("domain_id", sa.String(36), nullable=False),
            sa.Column("last_bucket", sa.String(16), nullable=False),
            sa.Column("last_owner_person_id", sa.String(36), nullable=True),
            sa.Column(
                "last_alerted_at", sa.DateTime(timezone=True),
                server_default=sa.func.now(), nullable=False,
            ),
            sa.PrimaryKeyConstraint("domain_id"),
        )
    if _table_exists(bind, UNIFIED):
        op.drop_table(UNIFIED)