-- Seed test rooms and paired codes for local development
-- Usage: run this script against your Supabase/Postgres DB (psql or Supabase SQL editor).
-- It creates 3 rooms each with a pair of mutually linked codes (codes.linked_to).
BEGIN;

-- Room 1 + codes TST01 <-> TST02
WITH room AS (
  INSERT INTO rooms(name, description)
  VALUES ('Test Room 1', 'Seeded test room 1')
  RETURNING id
), code_a AS (
  INSERT INTO codes(code, linked_to, room_id)
  VALUES ('TST01', NULL, (SELECT id FROM room))
  RETURNING id
), code_b AS (
  INSERT INTO codes(code, linked_to, room_id)
  VALUES ('TST02', (SELECT id FROM code_a), (SELECT id FROM room))
  RETURNING id
)
UPDATE codes SET linked_to = (SELECT id FROM code_b) WHERE id = (SELECT id FROM code_a);

-- Room 2 + codes TST03 <-> TST04
WITH room AS (
  INSERT INTO rooms(name, description)
  VALUES ('Test Room 2', 'Seeded test room 2')
  RETURNING id
), code_a AS (
  INSERT INTO codes(code, linked_to, room_id)
  VALUES ('TST03', NULL, (SELECT id FROM room))
  RETURNING id
), code_b AS (
  INSERT INTO codes(code, linked_to, room_id)
  VALUES ('TST04', (SELECT id FROM code_a), (SELECT id FROM room))
  RETURNING id
)
UPDATE codes SET linked_to = (SELECT id FROM code_b) WHERE id = (SELECT id FROM code_a);

-- Room 3 + codes TST05 <-> TST06
WITH room AS (
  INSERT INTO rooms(name, description)
  VALUES ('Test Room 3', 'Seeded test room 3')
  RETURNING id
), code_a AS (
  INSERT INTO codes(code, linked_to, room_id)
  VALUES ('TST05', NULL, (SELECT id FROM room))
  RETURNING id
), code_b AS (
  INSERT INTO codes(code, linked_to, room_id)
  VALUES ('TST06', (SELECT id FROM code_a), (SELECT id FROM room))
  RETURNING id
)
UPDATE codes SET linked_to = (SELECT id FROM code_b) WHERE id = (SELECT id FROM code_a);

COMMIT;

-- Quick check: list seeded rooms and codes
SELECT id, name, description, status, date_created FROM rooms WHERE name LIKE 'Test Room %';
SELECT id, code, linked_to, room_id, used FROM codes WHERE code LIKE 'TST%';