"""Composite indexes for proposal list query patterns (issue #35 Phase 7)

Revision ID: 0044
Revises: 0043

Note: CONCURRENTLY indexes cannot run inside a transaction block.
Uses op.get_context().autocommit_block() (Alembic 1.1+) to run each
DDL statement outside the implicit transaction Alembic opens.
"""
from __future__ import annotations

from alembic import op
from sqlalchemy import text

revision: str = "0044"
down_revision: str = "0043"
branch_labels = None
depends_on = None

_IDX_PROPOSAL_STATE_CREATED = "ix_musehub_proposals_repo_state_created"
_IDX_PROPOSAL_STATE_RISK    = "ix_musehub_proposals_repo_state_risk"
_IDX_REVIEW_PROPOSAL_STATE  = "ix_musehub_proposal_reviews_proposal_state"
_IDX_IDENTITY_HANDLE_TYPE   = "ix_musehub_identities_handle_type"

_CREATE = [
    f"CREATE INDEX CONCURRENTLY IF NOT EXISTS {_IDX_PROPOSAL_STATE_CREATED}"
    f" ON musehub_proposals (repo_id, state, created_at DESC)",

    f"CREATE INDEX CONCURRENTLY IF NOT EXISTS {_IDX_PROPOSAL_STATE_RISK}"
    f" ON musehub_proposals (repo_id, state, risk_score DESC NULLS LAST)",

    f"CREATE INDEX CONCURRENTLY IF NOT EXISTS {_IDX_REVIEW_PROPOSAL_STATE}"
    f" ON musehub_proposal_reviews (proposal_id, state)",

    f"CREATE INDEX CONCURRENTLY IF NOT EXISTS {_IDX_IDENTITY_HANDLE_TYPE}"
    f" ON musehub_identities (handle, identity_type)",
]

_DROP = [
    f"DROP INDEX CONCURRENTLY IF EXISTS {_IDX_PROPOSAL_STATE_CREATED}",
    f"DROP INDEX CONCURRENTLY IF EXISTS {_IDX_PROPOSAL_STATE_RISK}",
    f"DROP INDEX CONCURRENTLY IF EXISTS {_IDX_REVIEW_PROPOSAL_STATE}",
    f"DROP INDEX CONCURRENTLY IF EXISTS {_IDX_IDENTITY_HANDLE_TYPE}",
]


def upgrade() -> None:
    with op.get_context().autocommit_block():
        for stmt in _CREATE:
            op.execute(text(stmt))


def downgrade() -> None:
    with op.get_context().autocommit_block():
        for stmt in _DROP:
            op.execute(text(stmt))