-- ============================================================
-- ROLLBACK: Undo migrate-609-to-606.sql
-- ============================================================
--
-- This reverses the migration by:
--   1. Swapping seat_numbers back: 609 ↔ 606
--   2. Updating seat_reservations back to original UUIDs
--   3. Updating order_line_items back to original UUIDs
--   4. Re-creating blocked reservations on original 606 seats
--
-- NOTE: Only run this if the migration COMMIT was executed.
--       If ROLLBACK was used, the transaction was already undone.
-- ============================================================

SET @OLD_SQL_SAFE_UPDATES = @@SQL_SAFE_UPDATES;
SET SQL_SAFE_UPDATES = 0;

START TRANSACTION;

SELECT '=== ROLLBACK: Undoing 609-606 migration ===' as step;

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

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

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

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

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

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

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

-- ============================================================
-- STEP 2: Create reverse UUID mapping
-- ============================================================
SELECT '=== STEP 2: Reverse UUID mapping ===' as step;

DROP TEMPORARY TABLE IF EXISTS seat_uuid_mapping_reverse;
CREATE TEMPORARY TABLE seat_uuid_mapping_reverse AS
SELECT
    s606.seat_id as current_uuid,  -- After swap, 606 has the booked orders
    s609.seat_id as original_uuid  -- 609 should have them back
FROM seats s606
JOIN seats s609 ON REPLACE(s606.seat_number, '606-', '609-') = s609.seat_number
    AND s609.event_id = 1
WHERE s606.seat_number LIKE '606-%' AND s606.event_id = 1;

SELECT * FROM seat_uuid_mapping_reverse;

-- ============================================================
-- STEP 3: Update seat_reservations back to 609 UUIDs
-- ============================================================
SELECT '=== STEP 3: Restore seat_reservations ===' as step;

UPDATE seat_reservations sr
INNER JOIN seat_uuid_mapping_reverse m ON sr.seat_id = m.current_uuid
SET sr.seat_id = m.original_uuid;

SELECT ROW_COUNT() as restored_seat_reservations;

-- ============================================================
-- STEP 4: Update order_line_items back to 609 UUIDs
-- ============================================================
SELECT '=== STEP 4: Restore order_line_items ===' as step;

UPDATE order_line_items oli
INNER JOIN seat_uuid_mapping_reverse m ON oli.seat_id = m.current_uuid
SET oli.seat_id = m.original_uuid;

SELECT ROW_COUNT() as restored_order_line_items;

-- ============================================================
-- STEP 5: Re-create blocked reservations on 606 seats
-- ============================================================
SELECT '=== STEP 5: Re-create blocked reservations on 606 ===' as step;

INSERT INTO seat_reservations (event_id, seat_id, status, reservation_type, payment_mode,
                               payment_gateway, currency, include_booking_fee, include_tax,
                               created_at, updated_at)
SELECT
    1 as event_id,
    s.seat_id,
    'blocked' as status,
    'admin' as reservation_type,
    'immediate' as payment_mode,
    'stripe' as payment_gateway,
    '£' as currency,
    1 as include_booking_fee,
    1 as include_tax,
    NOW() as created_at,
    NOW() as updated_at
FROM seats s
WHERE s.seat_number LIKE '606-%'
  AND s.event_id = 1
  AND s.seat_type = 'table_child'
  AND NOT EXISTS (
    SELECT 1 FROM seat_reservations sr WHERE sr.seat_id = s.seat_id
  );

SELECT ROW_COUNT() as recreated_blocked_reservations;

-- ============================================================
-- STEP 6: Verification
-- ============================================================
SELECT '=== VERIFICATION ===' as step;

SELECT 'Checking 609 seats have orders again...' 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;

SELECT 'Checking 606 seats are blocked again...' 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;

-- COMMIT; -- Uncomment to finalize rollback
-- ROLLBACK; -- If something went wrong

SELECT '=== ROLLBACK COMPLETE ===' as step;

DROP TEMPORARY TABLE IF EXISTS seat_uuid_mapping_reverse;
SET SQL_SAFE_UPDATES = @OLD_SQL_SAFE_UPDATES;