"""Round-28 pre-launch hardening: indexes for hot cron queries.

Revision ID: 178
Revises: 177
Create Date: 2026-05-18

Why this exists
---------------
Two cron queries scan the entire ``cmdb_dns_records`` table on every
tick:

1. ``cmdb.dns_owner_recertify`` filters on
   ``(deleted_at IS NULL AND owner_person_id IS NOT NULL AND
   (last_owner_review_at IS NULL OR < threshold) AND
   (last_recertify_nag_at IS NULL OR < dedup_threshold))``.

2. The recertify pre-launch audit (Round-28) found neither
   ``last_owner_review_at`` nor ``last_recertify_nag_at`` carries an
   index. With 10k+ records (target prod size) this becomes a daily
   full scan on TiDB — fine today, expensive at growth.

Add a composite index covering the two timestamp columns + the
``deleted_at`` predicate that gates the whole query. Owner FK already
has its own index from migration 009 so we don't duplicate that.

Idempotency
-----------
``CREATE INDEX IF NOT EXISTS``-equivalent via ``_index_exists`` check.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "178"
down_revision = "177"
branch_labels = None
depends_on = None


def _index_exists(bind, table: str, index: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.STATISTICS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND INDEX_NAME = :i LIMIT 1"
        ),
        {"t": table, "i": index},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    # Composite for the recertify cron's "due" filter. Leading
    # ``deleted_at`` matches the always-applied predicate, so the
    # range scan on ``last_recertify_nag_at`` benefits even when
    # ``last_owner_review_at`` is correlated with it.
    if not _index_exists(bind, "cmdb_dns_records", "ix_dns_recertify_due"):
        op.create_index(
            "ix_dns_recertify_due",
            "cmdb_dns_records",
            ["deleted_at", "last_recertify_nag_at", "last_owner_review_at"],
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _index_exists(bind, "cmdb_dns_records", "ix_dns_recertify_due"):
        op.drop_index("ix_dns_recertify_due", table_name="cmdb_dns_records")