#!/bin/bash
#
# UUID Mismatch Fix Script
#
# Purpose: Fix template-database UUID misalignment by deleting seats and resyncing
# Safe to run: Only operates on events with NO active reservations
#
# Usage: ./scripts/fix-uuid-mismatch.sh <event_id> [--force]
#

set -e

EVENT_ID=$1
FORCE_FLAG=$2

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

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

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

# SAFETY CHECK 1: Verify event exists
EVENT_NAME=$(db_query "SELECT name FROM events WHERE id = $EVENT_ID")
if [ -z "$EVENT_NAME" ]; then
    echo "❌ Event $EVENT_ID not found"
    exit 1
fi

VENUE_ID=$(db_query "SELECT venue_id FROM events WHERE id = $EVENT_ID")
echo "📋 Event: $EVENT_NAME"
echo "   Event ID: $EVENT_ID"
echo "   Venue ID: $VENUE_ID"
echo ""

# SAFETY CHECK 2: Active reservations
ACTIVE_RESERVATIONS=$(db_count_where "seat_reservations" "event_id = $EVENT_ID AND status IN ('booked', 'held', 'shadow_sold', 'blocked')")

if [ "$ACTIVE_RESERVATIONS" -gt 0 ]; then
    echo "❌ UNSAFE: Event has $ACTIVE_RESERVATIONS active reservations"
    echo "   Cannot delete seats without losing reservation data"
    echo "   Status breakdown:"
    db_table "SELECT status, COUNT(*) as count FROM seat_reservations WHERE event_id = $EVENT_ID GROUP BY status"
    echo ""
    echo "   You must first:"
    echo "   1. Cancel/process all active reservations, OR"
    echo "   2. Use alternative fix: php artisan event:sync-template-uuids $EVENT_ID"
    exit 1
fi

echo "✅ SAFE: No active reservations found"
echo ""

# STEP 1: Current state
CURRENT_SEAT_COUNT=$(db_count "seats WHERE event_id = $EVENT_ID")
CURRENT_TABLE_COUNT=$(db_count_where "seats" "event_id = $EVENT_ID AND seat_type = 'table'")
CURRENT_CHILD_COUNT=$(db_count_where "seats" "event_id = $EVENT_ID AND seat_type = 'table_child'")

echo "=== Current Database State ==="
echo "Total seats: $CURRENT_SEAT_COUNT"
echo "  - Tables: $CURRENT_TABLE_COUNT"
echo "  - Table children: $CURRENT_CHILD_COUNT"
echo "  - Individual seats: $((CURRENT_SEAT_COUNT - CURRENT_TABLE_COUNT - CURRENT_CHILD_COUNT))"
echo ""

# STEP 2: Get template info
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")
TEMPLATE_VERSION=$(db_query "SELECT version FROM venue_templates WHERE id = $TEMPLATE_ID")

echo "=== Template Information ==="
echo "Template ID: $TEMPLATE_ID"
echo "Template Version: v$TEMPLATE_VERSION"
echo ""

# STEP 3: Warning about other events
OTHER_EVENTS=$(db_query "SELECT GROUP_CONCAT(CONCAT(id, ': ', name) SEPARATOR '\n') FROM events WHERE venue_id = $VENUE_ID AND id != $EVENT_ID")

if [ ! -z "$OTHER_EVENTS" ]; then
    echo "⚠️  COLLATERAL AWARENESS"
    echo "   Other events using same venue (may need same fix later):"
    echo "$OTHER_EVENTS" | while read line; do
        echo "   - $line"
    done
    echo ""
fi

# STEP 4: Confirmation
if [ "$FORCE_FLAG" != "--force" ]; then
    echo "⚠️  THIS WILL:"
    echo "   1. DELETE all $CURRENT_SEAT_COUNT database seats for Event $EVENT_ID"
    echo "   2. RESYNC seats from Template v$TEMPLATE_VERSION"
    echo "   3. New seats will use template UUIDs (frontend-backend alignment)"
    echo ""
    echo "⚠️  THIS WILL NOT:"
    echo "   - Affect other events"
    echo "   - Modify the template JSON"
    echo "   - Delete any reservations (none exist)"
    echo ""
    read -p "Proceed with fix? (yes/no): " CONFIRM

    if [ "$CONFIRM" != "yes" ]; then
        echo "Operation cancelled"
        exit 0
    fi
fi

echo ""
echo "=== EXECUTING FIX ==="
echo ""

# STEP 5: Delete seats
echo "Step 1/3: Deleting existing seats..."
db_query "DELETE FROM seats WHERE event_id = $EVENT_ID"
DELETED_COUNT=$CURRENT_SEAT_COUNT
echo "✅ Deleted $DELETED_COUNT seats"
echo ""

# STEP 6: Verify deletion
REMAINING_SEATS=$(db_count "seats WHERE event_id = $EVENT_ID")
if [ "$REMAINING_SEATS" -ne 0 ]; then
    echo "❌ ERROR: Deletion failed, $REMAINING_SEATS seats remain"
    exit 1
fi
echo "✅ Verified: 0 seats remain"
echo ""

# STEP 7: Resync from template
echo "Step 2/3: Resyncing seats from template..."
SYNC_OUTPUT=$(php artisan venue:sync-seats $EVENT_ID 2>&1)
echo "$SYNC_OUTPUT"
echo ""

# STEP 8: Verify resync
NEW_SEAT_COUNT=$(db_count "seats WHERE event_id = $EVENT_ID")
NEW_TABLE_COUNT=$(db_count_where "seats" "event_id = $EVENT_ID AND seat_type = 'table'")
NEW_CHILD_COUNT=$(db_count_where "seats" "event_id = $EVENT_ID AND seat_type = 'table_child'")

echo "Step 3/3: Verifying resync..."
echo "New seat count: $NEW_SEAT_COUNT"
echo "  - Tables: $NEW_TABLE_COUNT"
echo "  - Table children: $NEW_CHILD_COUNT"
echo "  - Individual seats: $((NEW_SEAT_COUNT - NEW_TABLE_COUNT - NEW_CHILD_COUNT))"
echo ""

# STEP 9: Seat count comparison
SEAT_DIFF=$((NEW_SEAT_COUNT - CURRENT_SEAT_COUNT))

if [ $SEAT_DIFF -gt 0 ]; then
    echo "✅ Seat count increased by $SEAT_DIFF (template had more seats)"
elif [ $SEAT_DIFF -lt 0 ]; then
    echo "⚠️  Seat count decreased by ${SEAT_DIFF#-} (template had fewer seats)"
else
    echo "✅ Seat count unchanged ($NEW_SEAT_COUNT seats)"
fi
echo ""

# STEP 10: UUID alignment verification
echo "=== UUID Alignment Verification ==="
echo "Checking if template UUIDs now exist in database..."

# Get template JSON and extract first 3 UUIDs
TEMPLATE_JSON=$(db_query "SELECT template_data FROM venue_templates WHERE id = $TEMPLATE_ID")
echo "$TEMPLATE_JSON" > /tmp/template_verify_$$.json

# Extract UUIDs and check if they exist
TEMPLATE_UUIDS=$(grep -oE '"seat_id":"[a-f0-9-]{36}"' /tmp/template_verify_$$.json | head -3 | sed 's/"seat_id":"//g' | sed 's/"//g')

FOUND_COUNT=0
TOTAL_CHECKED=0

for UUID in $TEMPLATE_UUIDS; do
    TOTAL_CHECKED=$((TOTAL_CHECKED + 1))
    EXISTS=$(db_count_where "seats" "event_id = $EVENT_ID AND seat_id = '$UUID'")
    if [ "$EXISTS" -gt 0 ]; then
        FOUND_COUNT=$((FOUND_COUNT + 1))
        echo "✅ Template UUID exists in database: $(echo $UUID | cut -c1-13)..."
    else
        echo "❌ Template UUID NOT in database: $(echo $UUID | cut -c1-13)..."
    fi
done

rm -f /tmp/template_verify_$$.json

echo ""

# STEP 11: Final status
echo "=================================================="
echo "FIX COMPLETE"
echo "=================================================="
echo ""
echo "Summary:"
echo "  - Deleted: $DELETED_COUNT seats"
echo "  - Created: $NEW_SEAT_COUNT seats"
echo "  - UUID Alignment: $FOUND_COUNT/$TOTAL_CHECKED sample UUIDs verified"
echo ""

if [ $FOUND_COUNT -eq $TOTAL_CHECKED ]; then
    echo "🟢 STATUS: SUCCESS"
    echo ""
    echo "Next Steps:"
    echo "  1. Test admin UI: https://admin.dev.globalgala.com/events/$EVENT_ID"
    echo "  2. Test reservation flow with seat selection"
    echo "  3. Verify no 'seats do not exist' errors"
else
    echo "🔴 STATUS: PARTIAL SUCCESS"
    echo ""
    echo "⚠️  Some template UUIDs still not in database"
    echo "   This may indicate a deeper template-database mismatch"
    echo "   Run diagnostic: ./scripts/diagnose-uuid-mismatch.sh $EVENT_ID"
fi

echo ""
echo "=================================================="