#!/bin/bash
########################################
# Email Alert Monitor
# Run via cron every 15 minutes
# Sends alerts when email system has issues
########################################

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"

# Alert email (configure in .env or use default)
ALERT_EMAIL="${EMAIL_ALERT_ADDRESS:-test@globalgala.com}"

# Thresholds
FAILURE_RATE_THRESHOLD=10  # Alert if >10% failure rate
STUCK_EMAIL_THRESHOLD=5    # Alert if >5 emails stuck for >10 min
MIN_EMAILS_FOR_RATE=10     # Only check rate if we have enough volume

# Function to send alert
send_alert() {
    local subject=$1
    local message=$2

    echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
    echo " 🚨 EMAIL SYSTEM ALERT"
    echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
    echo " Time: $(date '+%Y-%m-%d %H:%M:%S')"
    echo " Alert: $subject"
    echo ""
    echo "$message"
    echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"

    # Send email alert if mail command is available
    if command -v mail >/dev/null 2>&1; then
        echo "$message" | mail -s "[EMAIL ALERT] $subject" "$ALERT_EMAIL"
    fi

    # Log to Laravel
    php artisan tinker --execute="\\Log::critical('Email system alert', ['subject' => '$subject', 'message' => '$message']);" 2>/dev/null
}

# Check 1: High Failure Rate
echo "🔍 Checking email failure rate (last hour)..."
STATS=$(db_query "
SELECT
    COUNT(*) as total,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed,
    ROUND(SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as failure_rate
FROM email_logs
WHERE created_at >= NOW() - INTERVAL 1 HOUR
" | tail -1)

TOTAL=$(echo "$STATS" | awk '{print $1}')
FAILED=$(echo "$STATS" | awk '{print $2}')
FAILURE_RATE=$(echo "$STATS" | awk '{print $3}')

# Clean up values
TOTAL=${TOTAL:-0}
FAILED=${FAILED:-0}
FAILURE_RATE=${FAILURE_RATE:-0}

echo "   Total: $TOTAL | Failed: $FAILED | Rate: ${FAILURE_RATE}%"

if [ "$TOTAL" -ge "$MIN_EMAILS_FOR_RATE" ]; then
    if (( $(echo "$FAILURE_RATE > $FAILURE_RATE_THRESHOLD" | bc -l) )); then
        send_alert "High Email Failure Rate" "Failure rate is ${FAILURE_RATE}% (threshold: ${FAILURE_RATE_THRESHOLD}%). Failed: $FAILED out of $TOTAL emails in last hour."
    fi
fi

# Check 2: Stuck Emails (pending for >10 min)
echo "🔍 Checking for stuck emails..."
STUCK=$(db_query "SELECT COUNT(*) FROM email_logs WHERE status = 'pending' AND created_at < NOW() - INTERVAL 10 MINUTE" | tail -1 | grep -o '[0-9]*')
STUCK=${STUCK:-0}

echo "   Stuck emails: $STUCK"

if [ "$STUCK" -ge "$STUCK_EMAIL_THRESHOLD" ]; then
    send_alert "Stuck Emails Detected" "$STUCK emails have been pending for more than 10 minutes. Queue worker may be down or overwhelmed."
fi

# Check 3: Queue Worker Status
echo "🔍 Checking queue worker status..."
if ! ps aux | grep "queue:work" | grep -v grep > /dev/null; then
    echo "   ❌ Queue worker NOT running"
    send_alert "Queue Worker Down" "Queue worker is not running. Emails will not be sent until it's restarted."
else
    echo "   ✅ Queue worker running"
fi

# Check 4: Recent Critical Failures (last 15 min)
echo "🔍 Checking for recent critical failures..."
RECENT_FAILURES=$(db_query "SELECT COUNT(*) FROM email_logs WHERE status = 'failed' AND created_at >= NOW() - INTERVAL 15 MINUTE" | tail -1 | grep -o '[0-9]*')
RECENT_FAILURES=${RECENT_FAILURES:-0}

echo "   Recent failures: $RECENT_FAILURES"

if [ "$RECENT_FAILURES" -ge 5 ]; then
    # Get sample error messages
    SAMPLE_ERRORS=$(db_query "
    SELECT
        CONCAT(recipient_email, ': ', SUBSTRING(error_message, 1, 100)) as error_sample
    FROM email_logs
    WHERE status = 'failed'
    AND created_at >= NOW() - INTERVAL 15 MINUTE
    LIMIT 3
    " | tail -n +2)

    send_alert "Multiple Email Failures" "$RECENT_FAILURES emails failed in last 15 minutes. Sample errors: $SAMPLE_ERRORS"
fi

echo "✅ Email monitoring check complete"