"""notification_outbox: index the ``related_resource`` lookup.

``(related_resource_type, related_resource_id)`` is how 12 places already find
"the outbox rows that belong to this entity", but it was never indexed — those
call sites were crons and digests where a full scan is invisible. The
年假到期提醒 batch detail (``GET /hr/leave/expiry-reminder/batches/{id}``) and its
delivery tally put that same shape on an **interactive** request, against a
table that grows by every notification the platform ever sends.

Revision ID: 509
Revises: 508
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision = "509"
down_revision = "508"
branch_labels = None
depends_on = None

_TABLE = "notification_outbox"
_INDEX = "ix_notification_outbox_related_resource"


def _index_exists(bind, table: str, name: 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": name},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    if not _index_exists(bind, _TABLE, _INDEX):
        op.create_index(
            _INDEX, _TABLE, ["related_resource_type", "related_resource_id"],
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _index_exists(bind, _TABLE, _INDEX):
        op.drop_index(_INDEX, table_name=_TABLE)