-- ============================================================
-- SAFE MIGRATION: Move table 609 orders to 606 seat UUIDs
-- ============================================================
--
-- WHAT THIS DOES:
--   1. Deletes blocked reservations on 606 seats (no orders affected)
--   2. Updates seat_reservations: 609 UUIDs → 606 UUIDs
--   3. Updates order_line_items: 609 UUIDs → 606 UUIDs
--   4. Swaps seat_numbers: 606 becomes "609", 609 becomes "606"
--
-- AFFECTED DATA:
--   - 12 seats (609-A1 to B6 and 606-A1 to B6)
--   - 4 orders (354, 383, 454, 463)
--   - 12 order_line_items
--   - 12 seat_reservations
--
-- NOT AFFECTED:
--   - QR codes / ticket_verification_codes (no seat_id in QR data)
--   - Existing tickets (remain valid)
--   - Other orders/seats
--
-- ROLLBACK: Run migrate-609-to-606-rollback.sql
-- ============================================================

-- Enable strict mode and start transaction
SET @OLD_SQL_SAFE_UPDATES = @@SQL_SAFE_UPDATES;
SET SQL_SAFE_UPDATES = 0;

START TRANSACTION;

-- ============================================================
-- STEP 0: Pre-flight verification
-- ============================================================
SELECT '=== PRE-FLIGHT CHECK ===' as step;

-- Verify 609 reservations exist and have orders
SELECT 'Verifying 609 reservations with orders...' as status;
SELECT COUNT(*) as expected_12_reservations
FROM seat_reservations sr
JOIN seats s ON sr.seat_id = s.seat_id AND s.event_id = 1
WHERE s.seat_number LIKE '609-%' AND sr.status = 'booked';

-- Verify 606 blocked reservations exist
SELECT 'Verifying 606 blocked reservations...' as status;
SELECT COUNT(*) as expected_12_blocked
FROM seat_reservations sr
JOIN seats s ON sr.seat_id = s.seat_id AND s.event_id = 1
WHERE s.seat_number LIKE '606-%' AND sr.status = 'blocked';

-- ============================================================
-- STEP 1: Delete blocked reservations on 606 seats
-- ============================================================
SELECT '=== STEP 1: Delete blocked 606 reservations ===' as step;

DELETE sr FROM seat_reservations sr
INNER JOIN seats s ON sr.seat_id = s.seat_id AND s.event_id = 1
WHERE s.seat_number LIKE '606-%' AND sr.status = 'blocked' AND sr.order_id IS NULL;

SELECT ROW_COUNT() as deleted_606_blocked_reservations;

-- ============================================================
-- STEP 2: Update seat_reservations - 609 UUIDs → 606 UUIDs
-- ============================================================
SELECT '=== STEP 2: Update seat_reservations ===' as step;

-- Create temporary mapping table
DROP TEMPORARY TABLE IF EXISTS seat_uuid_mapping;
CREATE TEMPORARY TABLE seat_uuid_mapping AS
SELECT
    s609.seat_id as old_uuid,
    s606.seat_id as new_uuid,
    s609.seat_number as seat_label
FROM seats s609
JOIN seats s606 ON REPLACE(s609.seat_number, '609-', '606-') = s606.seat_number
    AND s606.event_id = 1
WHERE s609.seat_number LIKE '609-%' AND s609.event_id = 1;

-- Show mapping
SELECT 'UUID Mapping:' as info;
SELECT * FROM seat_uuid_mapping;

-- Update seat_reservations
UPDATE seat_reservations sr
INNER JOIN seat_uuid_mapping m ON sr.seat_id = m.old_uuid
SET sr.seat_id = m.new_uuid;

SELECT ROW_COUNT() as updated_seat_reservations;

-- ============================================================
-- STEP 3: Update order_line_items - 609 UUIDs → 606 UUIDs
-- ============================================================
SELECT '=== STEP 3: Update order_line_items ===' as step;

UPDATE order_line_items oli
INNER JOIN seat_uuid_mapping m ON oli.seat_id = m.old_uuid
SET oli.seat_id = m.new_uuid;

SELECT ROW_COUNT() as updated_order_line_items;

-- ============================================================
-- STEP 4: Swap seat_numbers (606 ↔ 609)
-- ============================================================
SELECT '=== STEP 4: Swap seat_numbers ===' as step;

-- First, rename 606 → X609 (short prefix to fit VARCHAR(10))
UPDATE seats
SET seat_number = REPLACE(seat_number, '606-', 'X609-')
WHERE seat_number LIKE '606-%' AND event_id = 1;

-- Update parent table too
UPDATE seats
SET seat_number = 'X609'
WHERE seat_number = '606' AND event_id = 1 AND seat_type = 'table';

-- Rename 609 → 606
UPDATE seats
SET seat_number = REPLACE(seat_number, '609-', '606-')
WHERE seat_number LIKE '609-%' AND event_id = 1;

UPDATE seats
SET seat_number = '606'
WHERE seat_number = '609' AND event_id = 1 AND seat_type = 'table';

-- Rename X609 → 609
UPDATE seats
SET seat_number = REPLACE(seat_number, 'X609-', '609-')
WHERE seat_number LIKE 'X609-%' AND event_id = 1;

UPDATE seats
SET seat_number = '609'
WHERE seat_number = 'X609' AND event_id = 1 AND seat_type = 'table';

SELECT 'Seat numbers swapped' as status;

-- ============================================================
-- STEP 5: Post-migration verification
-- ============================================================
SELECT '=== POST-MIGRATION VERIFICATION ===' as step;

-- Verify 609 seats now have the booked reservations
SELECT 'Checking 609 seats now have orders...' as status;
SELECT s.seat_number, sr.status, sr.order_id, o.customer_name
FROM seats s
LEFT JOIN seat_reservations sr ON s.seat_id = sr.seat_id
LEFT JOIN orders o ON sr.order_id = o.id
WHERE s.seat_number LIKE '609-%' AND s.event_id = 1
ORDER BY s.seat_number;

-- Verify 606 seats are now empty (the old 609 UUIDs)
SELECT 'Checking 606 seats are now empty...' as status;
SELECT s.seat_number, sr.status, sr.order_id
FROM seats s
LEFT JOIN seat_reservations sr ON s.seat_id = sr.seat_id
WHERE s.seat_number LIKE '606-%' AND s.event_id = 1
ORDER BY s.seat_number;

-- Verify order_line_items point to correct seats
SELECT 'Checking order_line_items...' as status;
SELECT oli.order_id, oli.description, oli.seat_id, s.seat_number
FROM order_line_items oli
LEFT JOIN seats s ON oli.seat_id = s.seat_id AND s.event_id = 1
WHERE oli.order_id IN (354, 383, 454, 463) AND oli.item_type = 'ticket'
ORDER BY oli.order_id, oli.description;

-- ============================================================
-- COMMIT or ROLLBACK
-- ============================================================
-- If everything looks good, uncomment COMMIT:
-- COMMIT;

-- If something is wrong, run:
-- ROLLBACK;

SELECT '=== MIGRATION COMPLETE - REVIEW OUTPUT ABOVE ===' as step;
SELECT 'Run COMMIT; to finalize or ROLLBACK; to undo' as action;

-- Cleanup
DROP TEMPORARY TABLE IF EXISTS seat_uuid_mapping;
SET SQL_SAFE_UPDATES = @OLD_SQL_SAFE_UPDATES;