migrate-database.sql

-- ============================================================
-- MB-SQUARE & CO. — Database Migration Script
-- Run this ONCE in Supabase → SQL Editor → New Query
-- ============================================================

-- STEP 1: Add dedicated reservation_code and visiting_time columns
ALTER TABLE reservations
  ADD COLUMN IF NOT EXISTS reservation_code text,
  ADD COLUMN IF NOT EXISTS visiting_time text;

-- STEP 2: Extract reservation_code from notes (e.g. [Code: 22AUG01] → 22AUG01)
UPDATE reservations
SET reservation_code = (regexp_match(notes, '\[Code:\s*([A-Z0-9]+)\]'))[1]
WHERE notes IS NOT NULL
  AND notes LIKE '%[Code:%'
  AND reservation_code IS NULL;

-- STEP 3: Extract visiting_time from notes (e.g. Visiting Time: 10:00 AM → 10:00 AM)
UPDATE reservations
SET visiting_time = TRIM((regexp_match(notes, 'Visiting Time:\s*([0-9]{1,2}:[0-9]{2}\s*(?:AM|PM))'))[1])
WHERE notes IS NOT NULL
  AND notes LIKE '%Visiting Time:%'
  AND visiting_time IS NULL;

-- STEP 4: Clean notes — remove [Code: ...] prefix
UPDATE reservations
SET notes = TRIM(
  regexp_replace(notes, '^\[Code:\s*[A-Z0-9]+\]\s*', '', 'i')
)
WHERE notes IS NOT NULL AND notes LIKE '%[Code:%';

-- STEP 5: Clean notes — remove "Visiting Time: ..." segment (with optional leading separator)
UPDATE reservations
SET notes = TRIM(
  regexp_replace(notes, 'Visiting Time:\s*[0-9]{1,2}:[0-9]{2}\s*(?:AM|PM)\s*(\|\s*)?', '', 'i')
)
WHERE notes IS NOT NULL AND notes LIKE '%Visiting Time:%';

-- STEP 6: Set remaining empty notes to NULL
UPDATE reservations
SET notes = NULL
WHERE notes = '' OR notes IS NOT NULL AND TRIM(notes) = '';

-- STEP 7: Migrate all pending → confirmed (no pending status in new system)
UPDATE reservations
SET status = 'confirmed'
WHERE status = 'pending';

-- STEP 8: Add database constraint — only 'confirmed' or 'cancelled' allowed
ALTER TABLE reservations
  DROP CONSTRAINT IF EXISTS reservations_status_check;

ALTER TABLE reservations
  ADD CONSTRAINT reservations_status_check
  CHECK (status IN ('confirmed', 'cancelled'));

-- VERIFICATION — Run this to confirm the migration worked:
-- SELECT id, name, reservation_code, visiting_time, notes, status FROM reservations ORDER BY created_at DESC LIMIT 20;