-- Query to view trigger execution logs
-- Useful for debugging and monitoring trigger performance

-- View recent trigger executions
SELECT 
    id,
    trigger_name,
    task_id,
    customer_id,
    store_code,
    week_start_date,
    old_status,
    new_status,
    status,
    execution_duration_ms,
    error_message,
    error_code,
    update_store_metrics_result IS NOT NULL AS metrics_updated,
    update_product_store_data_called,
    availability_found,
    store_visibility_updated,
    note,
    execution_started_at,
    execution_completed_at,
    created_at
FROM trigger_execution_log
ORDER BY created_at DESC
LIMIT 50;

-- View only errors
SELECT 
    id,
    task_id,
    customer_id,
    store_code,
    week_start_date,
    status,
    error_message,
    error_code,
    note,
    execution_duration_ms,
    created_at
FROM trigger_execution_log
WHERE status = 'error'
ORDER BY created_at DESC
LIMIT 20;

-- View only successful executions
SELECT 
    id,
    task_id,
    customer_id,
    store_code,
    week_start_date,
    execution_duration_ms,
    availability_found,
    store_visibility_updated,
    note,
    created_at
FROM trigger_execution_log
WHERE status = 'success'
ORDER BY created_at DESC
LIMIT 20;

-- View skipped executions (with reasons)
SELECT 
    id,
    task_id,
    customer_id,
    store_code,
    old_status,
    new_status,
    note,
    created_at
FROM trigger_execution_log
WHERE status = 'skipped'
ORDER BY created_at DESC
LIMIT 20;

-- Performance stats
SELECT 
    status,
    COUNT(*) as count,
    AVG(execution_duration_ms) as avg_duration_ms,
    MIN(execution_duration_ms) as min_duration_ms,
    MAX(execution_duration_ms) as max_duration_ms,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY execution_duration_ms) as median_duration_ms
FROM trigger_execution_log
WHERE execution_duration_ms IS NOT NULL
GROUP BY status;

-- Recent executions for a specific task
-- Replace <task_id> with actual task ID
/*
SELECT *
FROM trigger_execution_log
WHERE task_id = <task_id>
ORDER BY created_at DESC;
*/

-- Recent executions for a specific store
-- Replace <store_code> and <customer_id> with actual values
/*
SELECT *
FROM trigger_execution_log
WHERE store_code = '<store_code>'
  AND customer_id = <customer_id>
ORDER BY created_at DESC
LIMIT 20;
*/

