#!/bin/bash
########################################
# Email Health Report
# Daily summary of email system health
########################################

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(dirname "$SCRIPT_DIR")"

cd "$PROJECT_ROOT" || exit 1

# Source DB helper
source "$SCRIPT_DIR/_db-helper.sh"

echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
echo " 📧 EMAIL SYSTEM HEALTH REPORT"
echo " Generated: $(date '+%Y-%m-%d %H:%M:%S')"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
echo ""

# 1. Today's Email Volume
echo "📊 TODAY'S EMAIL VOLUME ($(date +%Y-%m-%d))"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
db_query "
SELECT
    status,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) as percentage
FROM email_logs
WHERE DATE(created_at) = CURDATE()
GROUP BY status
ORDER BY count DESC
"
echo ""

# 2. Last 24 Hours Success Rate
echo "📈 LAST 24 HOURS - SUCCESS RATE"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
db_query "
SELECT
    COUNT(*) as total_emails,
    SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) as sent,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed,
    ROUND(SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as success_rate
FROM email_logs
WHERE created_at >= NOW() - INTERVAL 24 HOUR
"
echo ""

# 3. Email Types (Last 24 Hours)
echo "📋 EMAIL TYPES (Last 24 Hours)"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
db_query "
SELECT
    email_type,
    COUNT(*) as count,
    SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) as sent,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed
FROM email_logs
WHERE created_at >= NOW() - INTERVAL 24 HOUR
GROUP BY email_type
ORDER BY count DESC
LIMIT 10
"
echo ""

# 4. Recent Failures (Last 10)
echo "❌ RECENT FAILURES (Last 10)"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
db_query "
SELECT
    id,
    recipient_email,
    subject,
    error_message,
    created_at
FROM email_logs
WHERE status = 'failed'
ORDER BY created_at DESC
LIMIT 10
"
echo ""

# 5. Pending Emails (stuck in queue)
echo "⏳ PENDING EMAILS (Potentially Stuck)"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
db_query "
SELECT
    COUNT(*) as pending_count,
    MIN(created_at) as oldest_pending
FROM email_logs
WHERE status = 'pending'
AND created_at < NOW() - INTERVAL 10 MINUTE
"
echo ""

# 6. Email Volume by Hour (Today)
echo "⏰ EMAIL VOLUME BY HOUR (Today)"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
db_query "
SELECT
    HOUR(created_at) as hour,
    COUNT(*) as emails,
    SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) as sent,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed
FROM email_logs
WHERE DATE(created_at) = CURDATE()
GROUP BY HOUR(created_at)
ORDER BY hour DESC
LIMIT 10
"
echo ""

# 7. Queue Status
echo "🔄 QUEUE STATUS"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
db_query "SELECT COUNT(*) as pending_jobs FROM jobs WHERE queue = 'emails'"
echo ""

# 8. Queue Worker Status
echo "⚙️  QUEUE WORKER STATUS"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
if ps aux | grep "queue:work" | grep -v grep > /dev/null; then
    echo "✅ Queue worker is RUNNING"
    ps aux | grep "queue:work" | grep -v grep | awk '{print "   PID: " $2 " | Started: " $9 " | CPU: " $3 "% | Memory: " $4 "%"}'
else
    echo "❌ Queue worker is NOT RUNNING"
fi
echo ""

# 9. BCC Archive Check (Last 10 emails)
echo "📬 BCC ARCHIVE CHECK (Last 10 emails should have BCC)"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
echo "ℹ️  All sent emails should appear in archive@globalgala.com inbox"
db_query "
SELECT
    id,
    recipient_email,
    subject,
    sent_at
FROM email_logs
WHERE status = 'sent'
ORDER BY sent_at DESC
LIMIT 10
"
echo ""
echo "   👆 Check archive@globalgala.com inbox for these 10 emails"
echo ""

# 10. Overall System Health
echo "🏥 OVERALL SYSTEM HEALTH"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"

# Calculate health metrics
TOTAL_24H=$(db_query "SELECT COUNT(*) FROM email_logs WHERE created_at >= NOW() - INTERVAL 24 HOUR" 2>/dev/null | tail -1)
SENT_24H=$(db_query "SELECT COUNT(*) FROM email_logs WHERE created_at >= NOW() - INTERVAL 24 HOUR AND status = 'sent'" 2>/dev/null | tail -1)
PENDING_OLD=$(db_query "SELECT COUNT(*) FROM email_logs WHERE status = 'pending' AND created_at < NOW() - INTERVAL 10 MINUTE" 2>/dev/null | tail -1)

# Clean up output
TOTAL_24H=$(echo "$TOTAL_24H" | grep -o '[0-9]*' | head -1)
SENT_24H=$(echo "$SENT_24H" | grep -o '[0-9]*' | head -1)
PENDING_OLD=$(echo "$PENDING_OLD" | grep -o '[0-9]*' | head -1)

# Default to 0 if empty
TOTAL_24H=${TOTAL_24H:-0}
SENT_24H=${SENT_24H:-0}
PENDING_OLD=${PENDING_OLD:-0}

if [ "$TOTAL_24H" -gt 0 ]; then
    SUCCESS_RATE=$(awk "BEGIN {printf \"%.1f\", ($SENT_24H / $TOTAL_24H) * 100}")
else
    SUCCESS_RATE="0.0"
fi

echo "   📊 24h Volume: $TOTAL_24H emails"
echo "   ✅ Success Rate: ${SUCCESS_RATE}%"
echo "   ⏳ Stuck Emails: $PENDING_OLD"

# Determine overall health
if (( $(echo "$SUCCESS_RATE >= 95" | bc -l) )) && [ "$PENDING_OLD" -eq 0 ]; then
    echo ""
    echo "   🎉 SYSTEM HEALTHY"
elif (( $(echo "$SUCCESS_RATE >= 90" | bc -l) )); then
    echo ""
    echo "   ⚠️  SYSTEM OK (Minor Issues)"
else
    echo ""
    echo "   🚨 SYSTEM DEGRADED (Investigate)"
fi

echo ""
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
echo " Report complete. Run this daily for email health monitoring."
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"