#!/bin/bash
#==========================================================================
# BOOKING SYSTEM DIAGNOSTIC & REPAIR SCRIPT
#==========================================================================
# Purpose: Diagnose and fix common booking issues on production
# - Expired holds not being released
# - Duplicate seat errors
# - API performance bottlenecks
# - Race conditions
#
# Usage: ./scripts/diagnose-booking-system.sh [--fix] [--verbose]
#==========================================================================

set -euo pipefail

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

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

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

# Flags
FIX_MODE=false
VERBOSE=false

# Parse arguments
while [[ $# -gt 0 ]]; do
    case $1 in
        --fix)
            FIX_MODE=true
            shift
            ;;
        --verbose)
            VERBOSE=true
            shift
            ;;
        *)
            echo "Unknown option: $1"
            echo "Usage: $0 [--fix] [--verbose]"
            exit 1
            ;;
    esac
done

echo -e "${BLUE}========================================${NC}"
echo -e "${BLUE}  Booking System Diagnostic Tool${NC}"
echo -e "${BLUE}========================================${NC}"
echo ""

if [ "$FIX_MODE" = true ]; then
    echo -e "${YELLOW}⚠️  FIX MODE ENABLED - Will apply automated fixes${NC}"
    echo ""
fi

ISSUES_FOUND=0

#==========================================================================
# CHECK 1: Laravel Scheduler (Cron Job)
#==========================================================================
echo -e "${BLUE}[1/6] Checking Laravel Scheduler...${NC}"

if crontab -l 2>/dev/null | grep -q "schedule:run"; then
    echo -e "${GREEN}✅ Cron job configured${NC}"
    crontab -l | grep "schedule:run"
else
    echo -e "${RED}❌ No cron job found!${NC}"
    echo ""
    echo "Laravel scheduler requires a cron job to run."
    echo "Without it, expired holds are NEVER cleaned up."
    echo ""
    echo "Add this to crontab (run: crontab -e):"
    echo -e "${YELLOW}* * * * * cd $PROJECT_ROOT && php artisan schedule:run >> /dev/null 2>&1${NC}"
    echo ""
    ISSUES_FOUND=$((ISSUES_FOUND + 1))

    if [ "$FIX_MODE" = true ]; then
        echo -e "${YELLOW}💡 Fix: Manually running cleanup now...${NC}"
        php artisan seats:cleanup-expired
    fi
fi

#==========================================================================
# CHECK 2: Expired Holds Count
#==========================================================================
echo -e "${BLUE}[2/6] Checking for expired seat holds...${NC}"

EXPIRED_COUNT=$(db_query "
SELECT COUNT(*) as count
FROM seat_reservations
WHERE status = 'held'
AND expires_at < NOW()
AND reservation_type = 'public'
" | tail -n 1)

if [ "$EXPIRED_COUNT" -gt 0 ]; then
    echo -e "${RED}❌ Found $EXPIRED_COUNT expired holds!${NC}"
    echo ""
    echo "These should have been cleaned up automatically."
    echo "This confirms the scheduler is not running properly."
    echo ""

    if [ "$VERBOSE" = true ]; then
        echo "Sample expired holds:"
        db_table "
        SELECT id, event_id, expires_at,
               TIMESTAMPDIFF(MINUTE, expires_at, NOW()) as minutes_overdue
        FROM seat_reservations
        WHERE status = 'held'
        AND expires_at < NOW()
        AND reservation_type = 'public'
        LIMIT 5
        "
    fi

    ISSUES_FOUND=$((ISSUES_FOUND + 1))

    if [ "$FIX_MODE" = true ]; then
        echo -e "${YELLOW}🔧 Releasing expired holds...${NC}"
        php artisan seats:cleanup-expired
        echo -e "${GREEN}✅ Expired holds released${NC}"
    fi
else
    echo -e "${GREEN}✅ No expired holds found${NC}"
fi

echo ""

#==========================================================================
# CHECK 3: Duplicate Seat Errors (Recent)
#==========================================================================
echo -e "${BLUE}[3/6] Checking for recent duplicate seat errors...${NC}"

LOG_FILE="storage/logs/laravel-$(date +%Y-%m-%d).log"

if [ -f "$LOG_FILE" ]; then
    DUPLICATE_ERRORS=$(grep -c "Duplicate entry.*unique_event_seat" "$LOG_FILE" 2>/dev/null || echo "0")

    if [ "$DUPLICATE_ERRORS" -gt 50 ]; then
        echo -e "${RED}❌ High rate of duplicate errors: $DUPLICATE_ERRORS today${NC}"
        echo ""
        echo "This indicates:"
        echo "  1. Expired holds not being cleaned up (confirmed above)"
        echo "  2. Frontend showing stale availability data"
        echo "  3. Possible race conditions in booking logic"
        echo ""
        ISSUES_FOUND=$((ISSUES_FOUND + 1))
    elif [ "$DUPLICATE_ERRORS" -gt 10 ]; then
        echo -e "${YELLOW}⚠️  Moderate duplicate errors: $DUPLICATE_ERRORS today${NC}"
        echo "This is normal for a busy booking system."
    else
        echo -e "${GREEN}✅ Low duplicate error rate: $DUPLICATE_ERRORS today${NC}"
    fi
else
    echo -e "${YELLOW}⚠️  Today's log file not found${NC}"
fi

echo ""

#==========================================================================
# CHECK 4: API Performance - Slow Queries
#==========================================================================
echo -e "${BLUE}[4/6] Checking database indexes for performance...${NC}"

# Check if key indexes exist
MISSING_INDEXES=0

# Index 1: seat_reservations (event_id, status, expires_at)
INDEX_CHECK=$(db_query "
SHOW INDEXES FROM seat_reservations
WHERE Key_name LIKE '%event_id%'
AND Column_name = 'status'
" 2>/dev/null | wc -l || echo "0")

if [ "$INDEX_CHECK" -lt 2 ]; then
    echo -e "${YELLOW}⚠️  Missing composite index on (event_id, status, expires_at)${NC}"
    MISSING_INDEXES=$((MISSING_INDEXES + 1))
fi

# Index 2: seats (event_id, id) for hold queries
INDEX_CHECK=$(db_query "
SHOW INDEXES FROM seats
WHERE Key_name LIKE '%event_id%'
" 2>/dev/null | wc -l || echo "0")

if [ "$INDEX_CHECK" -lt 1 ]; then
    echo -e "${YELLOW}⚠️  Missing index on seats(event_id)${NC}"
    MISSING_INDEXES=$((MISSING_INDEXES + 1))
fi

if [ "$MISSING_INDEXES" -gt 0 ]; then
    echo ""
    echo "Missing indexes can cause slow API responses."
    echo "This makes the frontend show stale data longer."
    ISSUES_FOUND=$((ISSUES_FOUND + 1))

    if [ "$FIX_MODE" = true ]; then
        echo -e "${YELLOW}🔧 Creating missing indexes...${NC}"
        # Add indexes via migration (safer than direct ALTER)
        echo "Run: php artisan make:migration add_booking_performance_indexes"
    fi
else
    echo -e "${GREEN}✅ All critical indexes present${NC}"
fi

echo ""

#==========================================================================
# CHECK 5: Race Condition Detection
#==========================================================================
echo -e "${BLUE}[5/6] Checking for race condition patterns...${NC}"

# Check for holds created within 1 second of each other for same seat
if [ "$VERBOSE" = true ]; then
    RACE_COUNT=$(db_query "
    SELECT COUNT(*) as count FROM (
        SELECT seat_id, event_id, COUNT(*) as concurrent_holds
        FROM seat_reservations
        WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)
        AND status = 'held'
        GROUP BY seat_id, event_id, DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s')
        HAVING concurrent_holds > 1
    ) as races
    " 2>/dev/null | tail -n 1 || echo "0")

    if [ "$RACE_COUNT" -gt 0 ]; then
        echo -e "${RED}❌ Detected $RACE_COUNT potential race conditions${NC}"
        echo "Multiple holds created simultaneously for same seats."
        ISSUES_FOUND=$((ISSUES_FOUND + 1))
    else
        echo -e "${GREEN}✅ No race conditions detected (last hour)${NC}"
    fi
else
    echo -e "${BLUE}ℹ️  Run with --verbose to check for race conditions${NC}"
fi

echo ""

#==========================================================================
# CHECK 6: Health Summary
#==========================================================================
echo -e "${BLUE}[6/6] Booking System Health Summary...${NC}"

# Get current booking stats
TOTAL_HOLDS=$(db_query "SELECT COUNT(*) FROM seat_reservations WHERE status = 'held'" | tail -n 1)
TOTAL_CONFIRMED=$(db_query "SELECT COUNT(*) FROM seat_reservations WHERE status = 'confirmed'" | tail -n 1)

echo "Current System State:"
echo "  • Active holds: $TOTAL_HOLDS"
echo "  • Confirmed bookings: $TOTAL_CONFIRMED"
echo "  • Expired holds: $EXPIRED_COUNT"
echo ""

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

if [ "$ISSUES_FOUND" -eq 0 ]; then
    echo -e "${GREEN}✅ Booking system is healthy!${NC}"
    echo ""
    echo "All checks passed. No immediate action required."
else
    echo -e "${YELLOW}⚠️  Found $ISSUES_FOUND issue(s) requiring attention${NC}"
    echo ""

    if [ "$EXPIRED_COUNT" -gt 0 ]; then
        echo "1. ${RED}CRITICAL${NC}: Set up cron job for Laravel scheduler"
        echo "   Run: crontab -e"
        echo "   Add: * * * * * cd $PROJECT_ROOT && php artisan schedule:run >> /dev/null 2>&1"
        echo ""
    fi

    if [ "$DUPLICATE_ERRORS" -gt 50 ]; then
        echo "2. ${YELLOW}HIGH${NC}: Reduce duplicate errors"
        echo "   - Fix cron job (will clean up expired holds)"
        echo "   - Consider reducing hold TTL from 15min to 10min"
        echo "   - Add frontend seat availability refresh"
        echo ""
    fi

    if [ "$MISSING_INDEXES" -gt 0 ]; then
        echo "3. ${YELLOW}MEDIUM${NC}: Add database indexes"
        echo "   - Speeds up availability queries"
        echo "   - Reduces stale data on frontend"
        echo "   Run: php artisan make:migration add_booking_performance_indexes"
        echo ""
    fi

    echo "Quick Fix:"
    echo "  ./scripts/diagnose-booking-system.sh --fix"
    echo ""
fi

#==========================================================================
# EXIT CODE
#==========================================================================
if [ "$ISSUES_FOUND" -gt 0 ]; then
    exit 1
else
    exit 0
fi