#!/bin/bash
#
# UUID Mismatch Diagnostic Script
#
# Purpose: Verify template UUID vs database UUID alignment
# Safe to run: Read-only, makes no changes
#
# Usage: ./scripts/diagnose-uuid-mismatch.sh <event_id>
#

set -e

EVENT_ID=$1

if [ -z "$EVENT_ID" ]; then
    echo "Usage: $0 <event_id>"
    exit 1
fi

# Load database helper
source "$(dirname "$0")/_db-helper.sh"

echo "=================================================="
echo "UUID MISMATCH DIAGNOSTIC FOR EVENT $EVENT_ID"
echo "=================================================="
echo ""

# STEP 1: Event info
echo "=== STEP 1: Event Information ==="
db_table "SELECT id, name, venue_id, status FROM events WHERE id = $EVENT_ID"
echo ""

VENUE_ID=$(db_query "SELECT venue_id FROM events WHERE id = $EVENT_ID")
if [ -z "$VENUE_ID" ]; then
    echo "❌ Event $EVENT_ID not found"
    exit 1
fi

# STEP 2: Template info
echo "=== STEP 2: Venue Template Information ==="
db_table "SELECT id, venue_id, version, status, is_active,
          CHAR_LENGTH(template_data) as json_size_bytes,
          created_at, updated_at
          FROM venue_templates
          WHERE venue_id = $VENUE_ID
          AND status = 'published'
          AND is_active = 1
          ORDER BY version DESC LIMIT 1"
echo ""

TEMPLATE_ID=$(db_query "SELECT id FROM venue_templates WHERE venue_id = $VENUE_ID AND status = 'published' AND is_active = 1 ORDER BY version DESC LIMIT 1")

# STEP 3: Database seat count
echo "=== STEP 3: Database Seat Count ==="
TOTAL_SEATS=$(db_count "seats WHERE event_id = $EVENT_ID")
INDIVIDUAL_SEATS=$(db_count_where "seats" "event_id = $EVENT_ID AND seat_type = 'individual'")
TABLES=$(db_count_where "seats" "event_id = $EVENT_ID AND seat_type = 'table'")
TABLE_CHILDREN=$(db_count_where "seats" "event_id = $EVENT_ID AND seat_type = 'table_child'")

echo "Total database seats: $TOTAL_SEATS"
echo "  - Individual seats: $INDIVIDUAL_SEATS"
echo "  - Tables: $TABLES"
echo "  - Table children: $TABLE_CHILDREN"
echo ""

# STEP 4: Sample database UUIDs
echo "=== STEP 4: Sample Database UUIDs (First 5 Seats) ==="
db_table "SELECT seat_number, seat_id, seat_type
          FROM seats
          WHERE event_id = $EVENT_ID
          ORDER BY seat_number
          LIMIT 5"
echo ""

# STEP 5: Extract sample UUIDs from template JSON
echo "=== STEP 5: Sample Template UUIDs (Extracting from JSON) ==="
TEMPLATE_JSON=$(db_query "SELECT template_data FROM venue_templates WHERE id = $TEMPLATE_ID")

# Save to temp file for processing
echo "$TEMPLATE_JSON" > /tmp/template_$TEMPLATE_ID.json

# Extract first 5 seat UUIDs from JSON using grep
echo "Searching for seat_id patterns in template JSON..."
grep -oE '"seat_id":"[a-f0-9-]{36}"' /tmp/template_$TEMPLATE_ID.json | head -5 | sed 's/"seat_id":"//g' | sed 's/"//g' > /tmp/template_uuids.txt

if [ ! -s /tmp/template_uuids.txt ]; then
    echo "⚠️  No seat_id UUIDs found in template JSON"
else
    echo "Sample UUIDs from template JSON:"
    cat /tmp/template_uuids.txt
fi
echo ""

# STEP 6: Check if template UUIDs exist in database
echo "=== STEP 6: UUID Alignment Check ==="
echo "Testing if template UUIDs exist in database..."

MISMATCHES=0
while read -r TEMPLATE_UUID; do
    EXISTS=$(db_count_where "seats" "event_id = $EVENT_ID AND seat_id = '$TEMPLATE_UUID'")
    if [ "$EXISTS" -eq 0 ]; then
        echo "❌ Template UUID NOT in database: $TEMPLATE_UUID"
        MISMATCHES=$((MISMATCHES + 1))
    else
        echo "✅ Template UUID found in database: $TEMPLATE_UUID"
    fi
done < /tmp/template_uuids.txt
echo ""

# STEP 7: Diagnosis
echo "=== STEP 7: DIAGNOSIS ==="
if [ $MISMATCHES -gt 0 ]; then
    echo "🔴 STATUS: UUID MISMATCH DETECTED"
    echo ""
    echo "Problem:"
    echo "  - Template JSON contains UUIDs that don't exist in database"
    echo "  - Frontend loads template → sends these UUIDs to backend"
    echo "  - Backend searches database → UUIDs not found → ERROR"
    echo ""
    echo "Root Cause:"
    echo "  - Event $EVENT_ID had seat UUIDs regenerated (via event:regenerate-uuids)"
    echo "  - Template v$(db_query "SELECT version FROM venue_templates WHERE id = $TEMPLATE_ID") still has OLD UUIDs"
    echo "  - Database has NEW UUIDs"
    echo ""
    echo "Solution:"
    echo "  Option A: Delete database seats + resync from template (uses template UUIDs)"
    echo "    DELETE FROM seats WHERE event_id = $EVENT_ID;"
    echo "    php artisan venue:sync-seats $EVENT_ID"
    echo ""
    echo "  Option B: Update template JSON to match database UUIDs (surgical)"
    echo "    php artisan event:sync-template-uuids $EVENT_ID"
    echo ""
    echo "  ✅ RECOMMENDED: Option A (simpler, proven on DEV)"
else
    echo "🟢 STATUS: UUIDs ALIGNED"
    echo ""
    echo "All sampled template UUIDs exist in database."
    echo "Frontend and backend should be working correctly."
fi
echo ""

# STEP 8: Reservation check
echo "=== STEP 8: Active Reservations Check ==="
ACTIVE_RESERVATIONS=$(db_count_where "seat_reservations" "event_id = $EVENT_ID AND status IN ('booked', 'held', 'shadow_sold', 'blocked')")
echo "Active reservations: $ACTIVE_RESERVATIONS"

if [ "$ACTIVE_RESERVATIONS" -gt 0 ]; then
    echo "⚠️  WARNING: Event has $ACTIVE_RESERVATIONS active reservations"
    echo "   Cannot safely delete seats without losing reservation data"
    echo "   Must use Option B (sync-template-uuids) if fix needed"
else
    echo "✅ SAFE: No active reservations, can safely delete + resync seats"
fi
echo ""

# Cleanup
rm -f /tmp/template_$TEMPLATE_ID.json /tmp/template_uuids.txt

echo "=================================================="
echo "DIAGNOSTIC COMPLETE"
echo "=================================================="