"""created_at indexes for the execution-plane health surfaces.

Post-review fix (2026-06-10, execution-plane theme): the new
``GET /dashboard/insights/automation-health`` endpoint and the outbox
``error-clusters`` view filter ``notification_outbox`` and
``provisioning_tasks`` by ``created_at`` ranges (rolling 7d window /
``ORDER BY created_at DESC LIMIT 20000``). Neither table had a
``created_at`` index — both queries were full scans on tables that
grow without bound (outbox is already >5k rows in prod and is hit on
every admin-page load).

Guarded with IF-NOT-EXISTS-style checks so a re-run converges (TiDB
and MySQL both reject duplicate index names).

Revision ID: 272
Revises: 271
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "272"
down_revision = "271"
branch_labels = None
depends_on = None

_INDEXES: tuple[tuple[str, str], ...] = (
    ("notification_outbox", "ix_notification_outbox_created_at"),
    ("provisioning_tasks", "ix_provisioning_tasks_created_at"),
)


def _index_exists(table: str, name: str) -> bool:
    bind = op.get_bind()
    row = bind.execute(
        sa.text(
            "SELECT COUNT(*) FROM information_schema.STATISTICS "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t "
            "AND INDEX_NAME = :n"
        ),
        {"t": table, "n": name},
    ).scalar()
    return bool(row)


def upgrade() -> None:
    for table, name in _INDEXES:
        if not _index_exists(table, name):
            op.create_index(name, table, ["created_at"])


def downgrade() -> None:
    for table, name in _INDEXES:
        if _index_exists(table, name):
            op.drop_index(name, table_name=table)