"""Scheduling week-window query index (INDL-36)

Adds a composite partial index on the ``services`` table to keep the weekly
calendar / list queries (filter by tenant + service_date range + status, with
``deleted_at IS NULL``) index-backed. This prevents connection-pool abuse from
unbounded week-range scans (CIS 12) and supports the INDL-36 calendar view.

No data changes and no CHECK constraint are added here — ``service_type`` stays
free-text at the DB level; canonicalisation is enforced at the schema layer.

Chains off 0061 (service scheduling phase 2, renumbered from 0057 when
develop was merged into this branch). Idempotent (IF [NOT] EXISTS) so it
is safe to re-run. Fully reversible via downgrade().
"""
from alembic import op
import sqlalchemy as sa

revision = "0062"
down_revision = "0061"
branch_labels = None
depends_on = None


def upgrade() -> None:
    conn = op.get_bind()

    conn.execute(sa.text(
        """
        CREATE INDEX IF NOT EXISTS idx_services_tenant_date_status
            ON services (tenant_id, service_date, status)
            WHERE deleted_at IS NULL
        """
    ))


def downgrade() -> None:
    conn = op.get_bind()

    conn.execute(sa.text(
        "DROP INDEX IF EXISTS idx_services_tenant_date_status"
    ))