#!/bin/bash
#==========================================================================
# CRON & QUEUE WORKER DIAGNOSTIC
#==========================================================================
# Purpose: Comprehensive diagnostic for cron setup and queue worker issues
# Usage: ./scripts/diagnose-cron-and-queue.sh
#==========================================================================

set -euo pipefail

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

# Load database helper
source "$SCRIPT_DIR/_db-helper.sh"

# Colors
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
CYAN='\033[0;36m'
NC='\033[0m' # No Color

echo -e "${BLUE}========================================${NC}"
echo -e "${BLUE}  Cron & Queue Worker Diagnostic${NC}"
echo -e "${BLUE}========================================${NC}"
echo ""
echo -e "Server: $(hostname)"
echo -e "Date: $(date)"
echo -e "Working Directory: $(pwd)"
echo ""

#==========================================================================
# 1. CRONTAB CONFIGURATION
#==========================================================================
echo -e "${BLUE}[1/12] Checking Crontab Configuration...${NC}"
echo ""

if crontab -l > /dev/null 2>&1; then
    echo -e "${GREEN}✅ Crontab exists${NC}"
    echo ""
    echo -e "${CYAN}Current crontab:${NC}"
    crontab -l | sed 's/^/  /'
    echo ""

    # Check for Laravel scheduler
    if crontab -l | grep -q "artisan schedule:run"; then
        echo -e "${GREEN}✅ Laravel scheduler cron found${NC}"
    else
        echo -e "${RED}❌ Laravel scheduler cron NOT found${NC}"
        echo -e "  Expected: * * * * * cd $(pwd) && php artisan schedule:run >> /dev/null 2>&1"
    fi

    # Check for queue worker monitor
    if crontab -l | grep -q "queue:work\|start-queue-worker"; then
        echo -e "${GREEN}✅ Queue worker monitor cron found${NC}"
    else
        echo -e "${YELLOW}⚠️  Queue worker monitor cron NOT found${NC}"
        echo -e "  Recommended: */5 * * * * cd $(pwd) && (pgrep -f 'queue:work' > /dev/null || ./scripts/start-queue-worker.sh) >> storage/logs/cron-queue-monitor.log 2>&1"
    fi
else
    echo -e "${RED}❌ No crontab configured for this user${NC}"
fi
echo ""

#==========================================================================
# 2. PHP CONFIGURATION
#==========================================================================
echo -e "${BLUE}[2/12] Checking PHP Configuration...${NC}"
echo ""

PHP_PATH=$(which php 2>/dev/null || echo "NOT FOUND")
echo -e "PHP Binary: ${CYAN}${PHP_PATH}${NC}"

if [ "$PHP_PATH" != "NOT FOUND" ]; then
    PHP_VERSION=$(php -v | head -n 1)
    echo -e "PHP Version: ${CYAN}${PHP_VERSION}${NC}"

    # Check memory limit
    PHP_MEMORY=$(php -r "echo ini_get('memory_limit');")
    echo -e "PHP Memory Limit: ${CYAN}${PHP_MEMORY}${NC}"

    # Check max execution time
    PHP_MAX_TIME=$(php -r "echo ini_get('max_execution_time');")
    echo -e "PHP Max Execution Time: ${CYAN}${PHP_MAX_TIME}s${NC}"
else
    echo -e "${RED}❌ PHP not found in PATH${NC}"
fi
echo ""

#==========================================================================
# 3. QUEUE WORKER STATUS
#==========================================================================
echo -e "${BLUE}[3/12] Checking Queue Worker Status...${NC}"
echo ""

WORKER_COUNT=$(ps aux | grep "queue:work" | grep -v grep | wc -l | xargs)

if [ "$WORKER_COUNT" -gt 0 ]; then
    echo -e "${GREEN}✅ Queue workers running: ${WORKER_COUNT}${NC}"
    echo ""
    echo -e "${CYAN}Worker processes:${NC}"
    ps aux | grep "queue:work" | grep -v grep | sed 's/^/  /'
else
    echo -e "${RED}❌ NO queue workers running${NC}"
fi
echo ""

#==========================================================================
# 4. SCREEN SESSIONS
#==========================================================================
echo -e "${BLUE}[4/12] Checking Screen Sessions...${NC}"
echo ""

if command -v screen > /dev/null 2>&1; then
    SCREEN_COUNT=$(screen -ls 2>&1 | grep -c "queue-worker" || echo "0")

    if [ "$SCREEN_COUNT" -gt 0 ]; then
        echo -e "${GREEN}✅ Queue worker screen sessions: ${SCREEN_COUNT}${NC}"
        echo ""
        screen -ls 2>&1 | grep -A 1 "queue-worker" | sed 's/^/  /'
    else
        echo -e "${YELLOW}⚠️  No queue worker screen sessions found${NC}"
    fi
else
    echo -e "${YELLOW}⚠️  Screen command not available${NC}"
fi
echo ""

#==========================================================================
# 5. LARAVEL SCHEDULER STATUS
#==========================================================================
echo -e "${BLUE}[5/12] Checking Laravel Scheduler...${NC}"
echo ""

echo -e "${CYAN}Scheduled commands:${NC}"
php artisan schedule:list | sed 's/^/  /'
echo ""

# Check if scheduler has run recently
if [ -f "storage/logs/laravel-$(date +%Y-%m-%d).log" ]; then
    SCHEDULER_RUNS=$(grep -c "Running scheduled command" storage/logs/laravel-$(date +%Y-%m-%d).log 2>/dev/null || echo "0")
    echo -e "Scheduler runs today: ${CYAN}${SCHEDULER_RUNS}${NC}"

    if [ "$SCHEDULER_RUNS" -gt 0 ]; then
        echo -e "${GREEN}✅ Scheduler is running${NC}"
        echo ""
        echo -e "${CYAN}Last 3 scheduler executions:${NC}"
        grep "Running scheduled command" storage/logs/laravel-$(date +%Y-%m-%d).log | tail -n 3 | sed 's/^/  /'
    else
        echo -e "${RED}❌ Scheduler has NOT run today${NC}"
    fi
else
    echo -e "${YELLOW}⚠️  No Laravel log file for today${NC}"
fi
echo ""

#==========================================================================
# 6. DATABASE QUEUE STATUS
#==========================================================================
echo -e "${BLUE}[6/12] Checking Database Queue...${NC}"
echo ""

echo -e "${CYAN}Jobs by queue:${NC}"
db_table "
SELECT queue, COUNT(*) as pending_jobs,
       MIN(created_at) as oldest_job,
       MAX(created_at) as newest_job
FROM jobs
GROUP BY queue
ORDER BY pending_jobs DESC
"

TOTAL_JOBS=$(db_query "SELECT COUNT(*) FROM jobs" | tail -n 1)
echo ""
echo -e "Total pending jobs: ${CYAN}${TOTAL_JOBS}${NC}"

if [ "$TOTAL_JOBS" -gt 100 ]; then
    echo -e "${RED}⚠️  HIGH job backlog - workers may be stuck${NC}"
elif [ "$TOTAL_JOBS" -gt 0 ]; then
    echo -e "${YELLOW}⚠️  Jobs pending - check if count is decreasing${NC}"
else
    echo -e "${GREEN}✅ No pending jobs${NC}"
fi
echo ""

#==========================================================================
# 7. FAILED JOBS (Last Hour)
#==========================================================================
echo -e "${BLUE}[7/12] Checking Failed Jobs...${NC}"
echo ""

FAILED_COUNT=$(db_query "SELECT COUNT(*) FROM failed_jobs WHERE failed_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)" | tail -n 1)
echo -e "Failed jobs (last hour): ${CYAN}${FAILED_COUNT}${NC}"

if [ "$FAILED_COUNT" -gt 0 ]; then
    echo -e "${RED}⚠️  Recent job failures detected${NC}"
    echo ""
    echo -e "${CYAN}Recent failures:${NC}"
    db_table "
    SELECT id, queue, LEFT(exception, 100) as error_preview, failed_at
    FROM failed_jobs
    WHERE failed_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)
    ORDER BY failed_at DESC
    LIMIT 5
    "
else
    echo -e "${GREEN}✅ No recent job failures${NC}"
fi
echo ""

#==========================================================================
# 8. CRON LOGS
#==========================================================================
echo -e "${BLUE}[8/12] Checking Cron Logs...${NC}"
echo ""

# Check for cron-specific logs
if [ -f "storage/logs/cron-scheduler.log" ]; then
    echo -e "${GREEN}✅ Scheduler cron log found${NC}"
    echo ""
    echo -e "${CYAN}Last 10 lines of scheduler log:${NC}"
    tail -n 10 storage/logs/cron-scheduler.log | sed 's/^/  /'
    echo ""
else
    echo -e "${YELLOW}⚠️  No scheduler cron log (storage/logs/cron-scheduler.log)${NC}"
fi

if [ -f "storage/logs/cron-queue-monitor.log" ]; then
    echo -e "${GREEN}✅ Queue monitor cron log found${NC}"
    echo ""
    echo -e "${CYAN}Last 10 lines of queue monitor log:${NC}"
    tail -n 10 storage/logs/cron-queue-monitor.log | sed 's/^/  /'
    echo ""
else
    echo -e "${YELLOW}⚠️  No queue monitor cron log (storage/logs/cron-queue-monitor.log)${NC}"
fi

#==========================================================================
# 9. FILE PERMISSIONS
#==========================================================================
echo -e "${BLUE}[9/12] Checking File Permissions...${NC}"
echo ""

echo -e "${CYAN}Key directories:${NC}"
ls -ld storage storage/logs scripts/start-queue-worker.sh 2>/dev/null | sed 's/^/  /'
echo ""

if [ -x "scripts/start-queue-worker.sh" ]; then
    echo -e "${GREEN}✅ start-queue-worker.sh is executable${NC}"
else
    echo -e "${RED}❌ start-queue-worker.sh is NOT executable${NC}"
    echo -e "  Fix: chmod +x scripts/start-queue-worker.sh"
fi

if [ -w "storage/logs" ]; then
    echo -e "${GREEN}✅ storage/logs is writable${NC}"
else
    echo -e "${RED}❌ storage/logs is NOT writable${NC}"
    echo -e "  Fix: chmod -R 775 storage/logs"
fi
echo ""

#==========================================================================
# 10. RECENT LARAVEL ERRORS
#==========================================================================
echo -e "${BLUE}[10/12] Checking Recent Laravel Errors...${NC}"
echo ""

if [ -f "storage/logs/laravel-$(date +%Y-%m-%d).log" ]; then
    ERROR_COUNT=$(grep -ci "error\|exception\|fatal" storage/logs/laravel-$(date +%Y-%m-%d).log || echo "0")
    echo -e "Errors/Exceptions today: ${CYAN}${ERROR_COUNT}${NC}"

    if [ "$ERROR_COUNT" -gt 0 ]; then
        echo ""
        echo -e "${CYAN}Recent errors (last 10):${NC}"
        grep -i "error\|exception\|fatal" storage/logs/laravel-$(date +%Y-%m-%d).log | tail -n 10 | sed 's/^/  /'
    fi
else
    echo -e "${YELLOW}⚠️  No Laravel log file for today${NC}"
fi
echo ""

#==========================================================================
# 11. ENVIRONMENT CHECK
#==========================================================================
echo -e "${BLUE}[11/12] Checking Environment...${NC}"
echo ""

if [ -f ".env" ]; then
    echo -e "${GREEN}✅ .env file exists${NC}"

    QUEUE_CONNECTION=$(grep "^QUEUE_CONNECTION=" .env | cut -d'=' -f2)
    echo -e "Queue Driver: ${CYAN}${QUEUE_CONNECTION}${NC}"

    if [ "$QUEUE_CONNECTION" != "database" ]; then
        echo -e "${YELLOW}⚠️  Queue driver is not 'database' - this diagnostic assumes database queue${NC}"
    fi
else
    echo -e "${RED}❌ .env file NOT found${NC}"
fi
echo ""

#==========================================================================
# 12. SYSTEM LOAD
#==========================================================================
echo -e "${BLUE}[12/12] Checking System Load...${NC}"
echo ""

if command -v uptime > /dev/null 2>&1; then
    LOAD=$(uptime | awk -F'load average:' '{print $2}')
    echo -e "Load Average: ${CYAN}${LOAD}${NC}"
fi

if command -v free > /dev/null 2>&1; then
    echo ""
    echo -e "${CYAN}Memory:${NC}"
    free -h | sed 's/^/  /'
elif command -v vm_stat > /dev/null 2>&1; then
    echo ""
    echo -e "${CYAN}Memory (macOS):${NC}"
    vm_stat | head -n 5 | sed 's/^/  /'
fi
echo ""

#==========================================================================
# SUMMARY & RECOMMENDATIONS
#==========================================================================
echo -e "${BLUE}========================================${NC}"
echo -e "${BLUE}  Summary & Recommendations${NC}"
echo -e "${BLUE}========================================${NC}"
echo ""

# Analyze results
CRITICAL_ISSUES=0
WARNINGS=0

# Check critical issues
if ! crontab -l > /dev/null 2>&1; then
    echo -e "${RED}❌ CRITICAL: No crontab configured${NC}"
    CRITICAL_ISSUES=$((CRITICAL_ISSUES + 1))
elif ! crontab -l | grep -q "artisan schedule:run"; then
    echo -e "${RED}❌ CRITICAL: Laravel scheduler not in crontab${NC}"
    CRITICAL_ISSUES=$((CRITICAL_ISSUES + 1))
fi

if [ "$WORKER_COUNT" -eq 0 ]; then
    echo -e "${RED}❌ CRITICAL: No queue workers running${NC}"
    CRITICAL_ISSUES=$((CRITICAL_ISSUES + 1))
fi

if [ "$TOTAL_JOBS" -gt 100 ]; then
    echo -e "${YELLOW}⚠️  WARNING: High job backlog (${TOTAL_JOBS} jobs)${NC}"
    WARNINGS=$((WARNINGS + 1))
fi

if [ "$FAILED_COUNT" -gt 10 ]; then
    echo -e "${YELLOW}⚠️  WARNING: High failure rate (${FAILED_COUNT} failed jobs in last hour)${NC}"
    WARNINGS=$((WARNINGS + 1))
fi

echo ""
echo -e "Critical Issues: ${RED}${CRITICAL_ISSUES}${NC}"
echo -e "Warnings: ${YELLOW}${WARNINGS}${NC}"
echo ""

if [ "$CRITICAL_ISSUES" -eq 0 ] && [ "$WARNINGS" -eq 0 ]; then
    echo -e "${GREEN}✅ System appears healthy!${NC}"
else
    echo -e "${CYAN}Recommended Actions:${NC}"
    echo ""

    if [ "$WORKER_COUNT" -eq 0 ]; then
        echo -e "1. ${YELLOW}Start queue workers immediately:${NC}"
        echo -e "   ./scripts/start-queue-worker.sh"
        echo ""
    fi

    if ! crontab -l | grep -q "artisan schedule:run" 2>/dev/null; then
        echo -e "2. ${YELLOW}Add Laravel scheduler to crontab:${NC}"
        echo -e "   (crontab -l 2>/dev/null; echo '* * * * * cd $(pwd) && $(which php) artisan schedule:run >> /dev/null 2>&1') | crontab -"
        echo ""
    fi

    if ! crontab -l | grep -q "queue:work\|start-queue-worker" 2>/dev/null; then
        echo -e "3. ${YELLOW}Add queue worker monitor to crontab:${NC}"
        echo -e "   (crontab -l 2>/dev/null; echo '*/5 * * * * cd $(pwd) && (pgrep -f \"queue:work\" > /dev/null || ./scripts/start-queue-worker.sh) >> storage/logs/cron-queue-monitor.log 2>&1') | crontab -"
        echo ""
    fi

    if [ "$TOTAL_JOBS" -gt 100 ]; then
        echo -e "4. ${YELLOW}Investigate stuck jobs:${NC}"
        echo -e "   - Check if workers are processing (run this script again in 5 minutes)"
        echo -e "   - Check Laravel logs for errors"
        echo -e "   - Consider restarting workers: pkill -f queue:work && ./scripts/start-queue-worker.sh"
        echo ""
    fi
fi

echo -e "${BLUE}========================================${NC}"
echo ""