-- Debug script to check TAT alerts
-- Run this to see if alerts are being created

-- 1. Check if tat_alerts table exists
SELECT 
    table_name,
    column_name,
    data_type
FROM information_schema.columns
WHERE table_name = 'tat_alerts'
ORDER BY ordinal_position;

-- 2. Count total TAT alerts
SELECT COUNT(*) as total_alerts FROM tat_alerts;

-- 3. Show recent TAT alerts (if any)
SELECT 
    alert_id,
    threshold_percentage,
    alert_sent_at,
    alert_message,
    metadata
FROM tat_alerts
ORDER BY alert_sent_at DESC
LIMIT 5;

-- 4. Check approval levels with TAT status
SELECT 
    level_id,
    request_id,
    level_number,
    approver_name,
    tat_hours,
    status,
    tat50_alert_sent,
    tat75_alert_sent,
    tat_breached,
    tat_start_time
FROM approval_levels
WHERE tat_start_time IS NOT NULL
ORDER BY tat_start_time DESC
LIMIT 5;

-- 5. Check if Redis is needed (are there any pending/in-progress levels?)
SELECT 
    w.request_number,
    al.level_number,
    al.approver_name,
    al.status,
    al.level_start_time,
    al.tat_hours
FROM approval_levels al
JOIN workflow_requests w ON al.request_id = w.request_id
WHERE al.status IN ('PENDING', 'IN_PROGRESS')
ORDER BY al.level_start_time DESC;