from pathlib import Path

ROOT = Path(__file__).resolve().parents[1]
MIGRATION = "awooop_conversation_event_hot_path_indexes_2026-07-01.sql"
ROLLBACK = "awooop_conversation_event_hot_path_indexes_2026-07-01_down.sql"
CONTENT_PREVIEW_MIGRATION = "awooop_conversation_event_content_preview_trgm_2026-07-01.sql"
CONTENT_PREVIEW_ROLLBACK = "awooop_conversation_event_content_preview_trgm_2026-07-01_down.sql"
AUTOMATION_LOG_MIGRATION = "automation_operation_log_truth_chain_hot_path_indexes_2026-07-01.sql"
AUTOMATION_LOG_ROLLBACK = "automation_operation_log_truth_chain_hot_path_indexes_2026-07-01_down.sql"


def _read(path: str) -> str:
    return (ROOT / path).read_text()


def _migration_sql() -> str:
    return _read(f"migrations/{MIGRATION}")


def _rollback_sql() -> str:
    return _read(f"migrations/{ROLLBACK}")


def _content_preview_migration_sql() -> str:
    return _read(f"migrations/{CONTENT_PREVIEW_MIGRATION}")


def _content_preview_rollback_sql() -> str:
    return _read(f"migrations/{CONTENT_PREVIEW_ROLLBACK}")


def _automation_log_migration_sql() -> str:
    return _read(f"migrations/{AUTOMATION_LOG_MIGRATION}")


def _automation_log_rollback_sql() -> str:
    return _read(f"migrations/{AUTOMATION_LOG_ROLLBACK}")


def test_hot_path_indexes_are_concurrent_and_transactionless() -> None:
    sql = _migration_sql()

    assert "BEGIN;" not in sql
    assert "COMMIT;" not in sql
    assert sql.count("CREATE INDEX CONCURRENTLY IF NOT EXISTS") == 12
    assert "idx_conv_event_recent" in sql
    assert (
        "ON awooop_conversation_event (project_id, channel_type, received_at DESC)"
        in sql
    )


def test_provider_recent_indexes_match_live_hot_queries() -> None:
    sql = _migration_sql()
    coverage_source = _read("src/services/channel_event_dossier_service.py")
    operator_source = _read("src/services/platform_operator_service.py")

    assert "COALESCE(NULLIF(source_envelope->>'provider', '')" in coverage_source
    assert "ORDER BY received_at DESC" in coverage_source
    assert "LOWER(COALESCE(NULLIF(source_envelope->>'provider', '')" in operator_source

    assert "idx_awooop_conv_event_project_provider_recent" in sql
    assert "idx_awooop_conv_event_project_provider_lower_recent" in sql
    assert "split_part(provider_event_id, ':', 1)" in sql
    assert "NULLIF(split_part(provider_event_id, ':', 1), '')" in sql
    assert "received_at DESC" in sql
    assert "event_id DESC" in sql


def test_truth_chain_source_lookup_indexes_match_or_terms() -> None:
    sql = _migration_sql()
    truth_chain_source = _read("src/services/awooop_truth_chain_service.py")

    assert "run_id::text = :source_id" in truth_chain_source
    assert "provider_event_id = :source_id" in truth_chain_source
    assert "content_preview ILIKE :source_needle" in truth_chain_source

    assert "idx_awooop_conv_event_project_run_id_text_recent" in sql
    assert "((run_id)::text)" in sql
    assert "idx_awooop_conv_event_project_provider_event_recent" in sql

    for ref_key in (
        "event_ids",
        "incident_ids",
        "approval_ids",
        "alert_ids",
        "sentry_issue_ids",
        "signoz_alerts",
        "fingerprints",
    ):
        assert f"{{source_refs,{ref_key}}}" in truth_chain_source
        assert f"{{source_refs,{ref_key}}}" in sql


def test_rollback_only_drops_indexes_owned_by_hot_path_migration() -> None:
    rollback = _rollback_sql()

    assert "BEGIN;" not in rollback
    assert "COMMIT;" not in rollback
    assert "DROP INDEX CONCURRENTLY IF EXISTS idx_conv_event_recent" not in rollback

    for index_name in (
        "idx_awooop_conv_event_project_provider_recent",
        "idx_awooop_conv_event_project_provider_lower_recent",
        "idx_awooop_conv_event_project_provider_event_recent",
        "idx_awooop_conv_event_project_run_id_text_recent",
        "idx_awooop_conv_event_source_refs_event_ids_gin",
        "idx_awooop_conv_event_source_refs_incident_ids_gin",
        "idx_awooop_conv_event_source_refs_approval_ids_gin",
        "idx_awooop_conv_event_source_refs_alert_ids_gin",
        "idx_awooop_conv_event_source_refs_sentry_issue_ids_gin",
        "idx_awooop_conv_event_source_refs_signoz_alerts_gin",
        "idx_awooop_conv_event_source_refs_fingerprints_gin",
    ):
        assert f"DROP INDEX CONCURRENTLY IF EXISTS {index_name};" in rollback


def test_content_preview_trgm_index_bounds_truth_chain_preview_fallback() -> None:
    sql = _content_preview_migration_sql()
    rollback = _content_preview_rollback_sql()
    truth_chain_source = _read("src/services/awooop_truth_chain_service.py")

    assert "BEGIN;" not in sql
    assert "COMMIT;" not in sql
    assert "CREATE EXTENSION IF NOT EXISTS pg_trgm" in sql
    assert "CREATE EXTENSION IF NOT EXISTS btree_gin" in sql
    assert "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_awooop_conv_event_content_preview_trgm" in sql
    assert "USING gin (content_preview gin_trgm_ops)" in sql
    assert "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_awooop_conv_event_project_content_preview_trgm" in sql
    assert "USING gin (project_id, content_preview gin_trgm_ops)" in sql
    assert "WHERE content_preview IS NOT NULL" in sql
    assert "content_preview ILIKE :source_needle" in truth_chain_source
    assert "content_preview ILIKE :fingerprint_needle" in truth_chain_source

    assert "DROP INDEX CONCURRENTLY IF EXISTS idx_awooop_conv_event_content_preview_trgm;" in rollback
    assert "DROP INDEX CONCURRENTLY IF EXISTS idx_awooop_conv_event_project_content_preview_trgm;" in rollback
    assert "DROP EXTENSION" not in rollback


def test_automation_operation_log_indexes_bound_truth_chain_lookups() -> None:
    sql = _automation_log_migration_sql()
    rollback = _automation_log_rollback_sql()
    truth_chain_source = _read("src/services/awooop_truth_chain_service.py")

    assert "BEGIN;" not in sql
    assert "COMMIT;" not in sql
    assert "CREATE EXTENSION IF NOT EXISTS pg_trgm" in sql
    assert "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_automation_operation_log_incident_text" in sql
    assert "ON automation_operation_log ((incident_id::text))" in sql
    assert "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_automation_operation_log_input_text_trgm" in sql
    assert "coalesce(input::text, '')" in sql
    assert "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_automation_operation_log_output_text_trgm" in sql
    assert "coalesce(output::text, '')" in sql
    assert "gin_trgm_ops" in sql

    assert "FROM automation_operation_log" in truth_chain_source
    assert "incident_id::text" in truth_chain_source
    assert "coalesce(input::text, '') LIKE" in truth_chain_source
    assert "coalesce(output::text, '') LIKE" in truth_chain_source
    assert "coalesce(array_to_string(tags, ','), '') LIKE" in truth_chain_source

    for index_name in (
        "idx_automation_operation_log_output_text_trgm",
        "idx_automation_operation_log_input_text_trgm",
        "idx_automation_operation_log_incident_text",
    ):
        assert f"DROP INDEX CONCURRENTLY IF EXISTS {index_name};" in rollback
    assert "DROP EXTENSION" not in rollback
