-- ============================================================================
-- OSCE Nexus v2.2 – centralised exam round + bell broadcast
-- Idempotent. Apply after 002_extras.sql.
-- ============================================================================

CREATE TABLE IF NOT EXISTS `exam_rounds` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `round_label` VARCHAR(100) NULL,
  `duration_minutes` INT(11) NOT NULL DEFAULT 8,
  `warn_minutes` INT(11) NOT NULL DEFAULT 2,
  `started_at` DATETIME NULL,
  `ended_at` DATETIME NULL,
  `started_by` INT(11) NULL,
  `status` ENUM('idle','running','ended','aborted') NOT NULL DEFAULT 'idle',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_rounds_status` (`status`, `started_at`),
  KEY `idx_rounds_started_by` (`started_by`),
  CONSTRAINT `fk_rounds_user` FOREIGN KEY (`started_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `bell_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `round_id` INT(11) NULL,
  `event_type` ENUM('start','warn_2min','warn_1min','end','manual_1','manual_2','manual_3') NOT NULL,
  `triggered_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `triggered_by` INT(11) NULL,
  PRIMARY KEY (`id`),
  KEY `idx_bell_triggered` (`triggered_at`),
  KEY `idx_bell_round` (`round_id`),
  CONSTRAINT `fk_bell_round` FOREIGN KEY (`round_id`) REFERENCES `exam_rounds`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_bell_user`  FOREIGN KEY (`triggered_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
