"""Per-domain expiry-alert state for dedup.

Revision ID: 176
Revises: 175
Create Date: 2026-05-18

Why this exists
---------------
The original ``cmdb.domain_expiry_alert`` cron pushed the same concat
text of every expired-or-expiring domain to a shared Feishu group
every tick. No per-domain dedup, no per-owner routing, no payload cap.
Admins muted the channel and real new expirations got buried.

This table backs the rewritten cron handler:

* One row per ``cmdb_domains.id`` recording the bucket the cron LAST
  fired for and the owner at that time.
* Cron only re-fires for a (domain, owner) when the bucket has
  transitioned (e.g. ``<=30d`` → ``<=7d`` → ``<=1d`` → ``expired``)
  OR the owner has changed.
* When a domain renews and the new ``expiry_date`` puts it back in
  the ``safe`` bucket, the row is updated so the next transition out
  of safe fires again. (We deliberately do NOT delete the row — that
  would cause a second cron tick to re-fire ``<=30d`` immediately,
  defeating the dedup.)

Buckets
-------
``safe`` — expiry_date > today + 30d (no notify)
``<=30d`` — between today + 1d and today + 30d
``<=7d`` — between today + 1d and today + 7d
``<=1d`` — between today and today + 1d
``expired`` — past expiry_date (capped at 30 days past — older rows
              are assumed intentionally decommissioned without delete
              and would only generate noise)

State transitions either-direction (renewals back to safe, or expiry
forward) are first-class so the cron's "fire on change" semantic
covers both improvements and degradations.

Idempotency
-----------
``CREATE TABLE IF NOT EXISTS`` semantics via ``_table_exists``. No
seed — the cron populates rows lazily on first observation per
domain (new rows that are already in the ``safe`` bucket get a row
written so we don't fire ``<=30d`` the moment they slip past 30d).
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "176"
down_revision = "175"
branch_labels = None
depends_on = None


STATE_TABLE = "cmdb_domain_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 _table_exists(bind, STATE_TABLE):
        return
    op.create_table(
        STATE_TABLE,
        sa.Column("domain_id", sa.String(36), primary_key=True),
        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),
            nullable=False,
            server_default=sa.func.now(),
        ),
    )


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, STATE_TABLE):
        op.drop_table(STATE_TABLE)