-- Migration: Add performance indexes for notification system
-- Purpose: Optimize queue processing queries and recipient lookups
-- Date: 2025-10-16

-- ==================================================================
-- 1. Composite index for queue processing
-- ==================================================================
-- This index optimizes the main queue processing query that filters by:
-- - status (queued/processing)
-- - scheduled_for (less than or equal to current time)
-- - priority (for ordering)
-- - last_attempt_at (for timeout recovery)

-- Check if index exists, drop if it does, then create
SET @exist := (SELECT COUNT(*) FROM information_schema.statistics
               WHERE table_schema = DATABASE()
               AND table_name = 'notification_queue'
               AND index_name = 'idx_queue_processing_composite');
SET @sqlstmt := IF(@exist > 0,
                   'SELECT "Index idx_queue_processing_composite already exists"',
                   'CREATE INDEX idx_queue_processing_composite ON notification_queue(status, scheduled_for, priority, last_attempt_at)');
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ==================================================================
-- 2. Index for stuck job recovery
-- ==================================================================
-- Note: WHERE clause not supported in MySQL, using regular index instead

SET @exist := (SELECT COUNT(*) FROM information_schema.statistics
               WHERE table_schema = DATABASE()
               AND table_name = 'notification_queue'
               AND index_name = 'idx_queue_stuck_recovery');
SET @sqlstmt := IF(@exist > 0,
                   'SELECT "Index idx_queue_stuck_recovery already exists"',
                   'CREATE INDEX idx_queue_stuck_recovery ON notification_queue(status, last_attempt_at)');
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ==================================================================
-- 3. Composite index for recipient status tracking
-- ==================================================================

SET @exist := (SELECT COUNT(*) FROM information_schema.statistics
               WHERE table_schema = DATABASE()
               AND table_name = 'notification_recipients'
               AND index_name = 'idx_recipients_notification_status');
SET @sqlstmt := IF(@exist > 0,
                   'SELECT "Index idx_recipients_notification_status already exists"',
                   'CREATE INDEX idx_recipients_notification_status ON notification_recipients(notification_id, status)');
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ==================================================================
-- 4. Index for notification logs by notification and date
-- ==================================================================

SET @exist := (SELECT COUNT(*) FROM information_schema.statistics
               WHERE table_schema = DATABASE()
               AND table_name = 'notification_logs'
               AND index_name = 'idx_logs_notification_created');
SET @sqlstmt := IF(@exist > 0,
                   'SELECT "Index idx_logs_notification_created already exists"',
                   'CREATE INDEX idx_logs_notification_created ON notification_logs(notification_id, created_at)');
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ==================================================================
-- 5. Index for notification logs by status and date
-- ==================================================================

SET @exist := (SELECT COUNT(*) FROM information_schema.statistics
               WHERE table_schema = DATABASE()
               AND table_name = 'notification_logs'
               AND index_name = 'idx_logs_status_created');
SET @sqlstmt := IF(@exist > 0,
                   'SELECT "Index idx_logs_status_created already exists"',
                   'CREATE INDEX idx_logs_status_created ON notification_logs(status, created_at)');
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ==================================================================
-- 6. Index for notification delivery config lookups
-- ==================================================================

SET @exist := (SELECT COUNT(*) FROM information_schema.statistics
               WHERE table_schema = DATABASE()
               AND table_name = 'notification_delivery_config'
               AND index_name = 'idx_delivery_config_channel_enabled');
SET @sqlstmt := IF(@exist > 0,
                   'SELECT "Index idx_delivery_config_channel_enabled already exists"',
                   'CREATE INDEX idx_delivery_config_channel_enabled ON notification_delivery_config(channel_type, is_enabled)');
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ==================================================================
-- 7. Index for queue items by recipient
-- ==================================================================

SET @exist := (SELECT COUNT(*) FROM information_schema.statistics
               WHERE table_schema = DATABASE()
               AND table_name = 'notification_queue'
               AND index_name = 'idx_queue_recipient');
SET @sqlstmt := IF(@exist > 0,
                   'SELECT "Index idx_queue_recipient already exists"',
                   'CREATE INDEX idx_queue_recipient ON notification_queue(recipient_id)');
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ==================================================================
-- Verify indexes were created
-- ==================================================================
SELECT
    table_name,
    index_name,
    column_name
FROM information_schema.statistics
WHERE table_schema = DATABASE()
  AND table_name IN ('notification_queue', 'notification_recipients', 'notification_logs', 'notification_delivery_config')
  AND index_name LIKE 'idx_%'
ORDER BY table_name, index_name, seq_in_index;