"""add pg_trgm and performance indexes for patient search and order worklists

Revision ID: 20260930_perf
Revises: 20260930_split
Create Date: 2026-09-30 21:40:00.000000

Adds trigram GIN indexes on patient (name, phone, patient_code) to accelerate
ILIKE '%...%' searches, plus composite indexes on visit_order and sample.
"""

from collections.abc import Sequence

from alembic import op

revision: str = "20260930_perf"
down_revision: str | None = "20260930_split"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.execute("CREATE EXTENSION IF NOT EXISTS pg_trgm;")
    op.execute("CREATE INDEX IF NOT EXISTS idx_patient_name_trgm ON patient USING gin (name gin_trgm_ops);")
    op.execute("CREATE INDEX IF NOT EXISTS idx_patient_phone_trgm ON patient USING gin (phone gin_trgm_ops);")
    op.execute(
        "CREATE INDEX IF NOT EXISTS idx_patient_code_trgm ON patient USING gin (patient_code gin_trgm_ops);"
    )
    op.execute(
        "CREATE INDEX IF NOT EXISTS idx_orders_lab_status ON visit_order(lab_id, status, created_at DESC);"
    )
    op.execute("CREATE INDEX IF NOT EXISTS idx_sample_lab_id ON sample(lab_id);")


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS idx_sample_lab_id;")
    op.execute("DROP INDEX IF EXISTS idx_orders_lab_status;")
    op.execute("DROP INDEX IF EXISTS idx_patient_code_trgm;")
    op.execute("DROP INDEX IF EXISTS idx_patient_phone_trgm;")
    op.execute("DROP INDEX IF EXISTS idx_patient_name_trgm;")