"""add missing indexes on asset_items and hr_sync tables

Revision ID: 017
Revises: 016
Create Date: 2026-04-15

Fixes #68: asset_items missing indexes on assigned_to and status
Fixes #69: hr_sync tables missing indexes on batch_id FK and status
"""
import sqlalchemy as sa
from alembic import op

revision = '017'
down_revision = '016'
branch_labels = None
depends_on = None


def _table_exists(conn, table_name: str) -> bool:
    result = conn.execute(sa.text(
        "SELECT COUNT(*) FROM information_schema.tables "
        "WHERE table_schema = DATABASE() AND table_name = :t"
    ), {"t": table_name})
    return (result.scalar() or 0) > 0


def _index_exists(conn, table_name: str, index_name: str) -> bool:
    result = conn.execute(sa.text(
        "SELECT COUNT(*) FROM information_schema.statistics "
        "WHERE table_schema = DATABASE() AND table_name = :t AND index_name = :i"
    ), {"t": table_name, "i": index_name})
    return result.scalar() > 0


def _ensure_index(conn, table: str, name: str, columns: list[str]) -> None:
    if not _table_exists(conn, table):
        return
    if _index_exists(conn, table, name):
        return
    op.create_index(name, table, columns)


def _drop_index_if_present(conn, table: str, name: str) -> None:
    if not _table_exists(conn, table):
        return
    if _index_exists(conn, table, name):
        op.drop_index(name, table_name=table)


def upgrade():
    conn = op.get_bind()

    _ensure_index(conn, "asset_items", "ix_asset_items_assigned_to", ["assigned_to"])
    _ensure_index(conn, "asset_items", "ix_asset_items_status", ["status"])
    _ensure_index(conn, "hr_sync_batches", "ix_hr_sync_batches_status", ["status"])
    _ensure_index(conn, "hr_sync_staged_events", "ix_hr_sync_staged_events_batch_id", ["batch_id"])
    _ensure_index(conn, "hr_sync_staged_events", "ix_hr_sync_staged_events_status", ["status"])


def downgrade():
    conn = op.get_bind()

    _drop_index_if_present(conn, "hr_sync_staged_events", "ix_hr_sync_staged_events_status")
    _drop_index_if_present(conn, "hr_sync_staged_events", "ix_hr_sync_staged_events_batch_id")
    _drop_index_if_present(conn, "hr_sync_batches", "ix_hr_sync_batches_status")
    _drop_index_if_present(conn, "asset_items", "ix_asset_items_status")
    _drop_index_if_present(conn, "asset_items", "ix_asset_items_assigned_to")