-- awooop_conversation_event_hot_path_indexes_2026-07-01.sql
-- 2026-07-01 P0 post-reboot host pressure fix
--
-- Target:
-- - 188 k3s-postgres-recovery sustained CPU pressure from AWOOOI
--   awooop_conversation_event hot paths.
-- - Live readback showed idx_conv_event_recent missing, while current
--   provider/recent and truth-chain source lookup queries need expression
--   indexes.
--
-- CONCURRENTLY must run outside a transaction block.

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_conv_event_recent
    ON awooop_conversation_event (project_id, channel_type, received_at DESC);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_awooop_conv_event_project_provider_recent
    ON awooop_conversation_event (
        project_id,
        (
            COALESCE(
                NULLIF(source_envelope->>'provider', ''),
                split_part(provider_event_id, ':', 1),
                channel_type
            )
        ),
        received_at DESC,
        event_id DESC
    );

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_awooop_conv_event_project_provider_lower_recent
    ON awooop_conversation_event (
        project_id,
        (
            LOWER(COALESCE(
                NULLIF(source_envelope->>'provider', ''),
                NULLIF(split_part(provider_event_id, ':', 1), ''),
                channel_type
            ))
        ),
        received_at DESC,
        event_id DESC
    );

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_awooop_conv_event_project_provider_event_recent
    ON awooop_conversation_event (project_id, provider_event_id, received_at DESC);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_awooop_conv_event_project_run_id_text_recent
    ON awooop_conversation_event (project_id, ((run_id)::text), received_at DESC)
    WHERE run_id IS NOT NULL;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_awooop_conv_event_source_refs_event_ids_gin
    ON awooop_conversation_event
    USING GIN ((COALESCE(source_envelope #> '{source_refs,event_ids}', '[]'::jsonb)));

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_awooop_conv_event_source_refs_incident_ids_gin
    ON awooop_conversation_event
    USING GIN ((COALESCE(source_envelope #> '{source_refs,incident_ids}', '[]'::jsonb)));

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_awooop_conv_event_source_refs_approval_ids_gin
    ON awooop_conversation_event
    USING GIN ((COALESCE(source_envelope #> '{source_refs,approval_ids}', '[]'::jsonb)));

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_awooop_conv_event_source_refs_alert_ids_gin
    ON awooop_conversation_event
    USING GIN ((COALESCE(source_envelope #> '{source_refs,alert_ids}', '[]'::jsonb)));

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_awooop_conv_event_source_refs_sentry_issue_ids_gin
    ON awooop_conversation_event
    USING GIN ((COALESCE(source_envelope #> '{source_refs,sentry_issue_ids}', '[]'::jsonb)));

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_awooop_conv_event_source_refs_signoz_alerts_gin
    ON awooop_conversation_event
    USING GIN ((COALESCE(source_envelope #> '{source_refs,signoz_alerts}', '[]'::jsonb)));

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_awooop_conv_event_source_refs_fingerprints_gin
    ON awooop_conversation_event
    USING GIN ((COALESCE(source_envelope #> '{source_refs,fingerprints}', '[]'::jsonb)));
