-- Step 24: Room status synchronization for started recordings.
-- Run after step23_plan_delete_archive_fix.sql.

SET @room_status_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'rooms'
    AND COLUMN_NAME = 'status'
);

SET @room_status_sql := IF(
  @room_status_exists = 0,
  "ALTER TABLE rooms ADD COLUMN status ENUM('Available','Occupied') NOT NULL DEFAULT 'Available' AFTER type",
  "SELECT 'rooms.status already exists'"
);

PREPARE room_status_stmt FROM @room_status_sql;
EXECUTE room_status_stmt;
DEALLOCATE PREPARE room_status_stmt;

UPDATE rooms r
SET r.status = CASE
  WHEN EXISTS (
    SELECT 1
    FROM schedule s
    WHERE s.room_id = r.id
      AND s.status = 'Started'
  )
  OR EXISTS (
    SELECT 1
    FROM daily_recording_instances d
    WHERE d.room_id = r.id
      AND d.status = 'Started'
  )
  THEN 'Occupied'
  ELSE 'Available'
END;