-- ============================================================================
-- OSCE Nexus™ v2 – complete fresh-install schema
-- Run this on an empty database. The setup wizard executes it automatically.
-- Idempotent: uses IF NOT EXISTS so re-running is safe.
-- ============================================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

-- ----------------------------------------------------------------------------
-- Core tables
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `facilities` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `code` VARCHAR(20) NULL,
  `address` VARCHAR(255) NULL,
  `city` VARCHAR(100) NULL,
  `country` VARCHAR(100) NULL,
  `contact_email` VARCHAR(120) NULL,
  `contact_phone` VARCHAR(40) NULL,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_facility_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(100) NOT NULL,
  `role` ENUM('super_admin','coordinator','examiner','facility') NOT NULL DEFAULT 'examiner',
  `facility_id` INT(11) NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `face_descriptor` MEDIUMTEXT NULL,
  `face_enrolled_at` DATETIME NULL,
  `face_login_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `facility_id` (`facility_id`),
  CONSTRAINT `fk_users_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `candidates` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `facility_id` INT(11) NOT NULL,
  `candidate_number` VARCHAR(50) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `section` VARCHAR(50) NULL,
  `circuit_type` VARCHAR(50) NULL,
  `cohort_year` VARCHAR(10) NULL,
  `barcode` VARCHAR(50) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `barcode` (`barcode`),
  KEY `facility_id` (`facility_id`),
  CONSTRAINT `fk_candidates_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `forms` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `facility_id` INT(11) NULL,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT NULL,
  `default_duration_minutes` INT(11) NOT NULL DEFAULT 8,
  `created_by` INT(11) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `fk_forms_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `form_items` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `form_id` INT(11) NOT NULL,
  `label` TEXT NOT NULL,
  `max_score` INT(11) NOT NULL DEFAULT 1,
  `has_comment` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `form_id` (`form_id`),
  CONSTRAINT `fk_form_items_form` FOREIGN KEY (`form_id`) REFERENCES `forms`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `grade_bands` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `form_id` INT(11) NOT NULL,
  `label` VARCHAR(100) NOT NULL,
  `min_percent` DECIMAL(5,2) NOT NULL,
  `max_percent` DECIMAL(5,2) NOT NULL,
  `sort_order` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `form_id` (`form_id`),
  CONSTRAINT `fk_bands_form` FOREIGN KEY (`form_id`) REFERENCES `forms`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `attempts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `candidate_id` INT(11) NOT NULL,
  `form_id` INT(11) NOT NULL,
  `exam_station_id` INT(11) NULL DEFAULT NULL,
  `examiner_id` INT(11) NOT NULL,
  `station_name` VARCHAR(100) NULL,
  `total_score` INT(11) NOT NULL,
  `max_score` INT(11) NOT NULL,
  `percentage` DECIMAL(5,2) NOT NULL,
  `final_grade` VARCHAR(100) NOT NULL,
  `global_comment` TEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `candidate_id` (`candidate_id`),
  KEY `form_id` (`form_id`),
  KEY `examiner_id` (`examiner_id`),
  KEY `idx_attempts_created` (`created_at`),
  KEY `idx_attempts_examiner_created` (`examiner_id`, `created_at`),
  CONSTRAINT `fk_attempts_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `candidates`(`id`),
  CONSTRAINT `fk_attempts_form` FOREIGN KEY (`form_id`) REFERENCES `forms`(`id`),
  CONSTRAINT `fk_attempts_examiner` FOREIGN KEY (`examiner_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `attempt_items` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `attempt_id` INT(11) NOT NULL,
  `form_item_id` INT(11) NOT NULL,
  `score` INT(11) NOT NULL,
  `comment` TEXT NULL,
  PRIMARY KEY (`id`),
  KEY `attempt_id` (`attempt_id`),
  KEY `form_item_id` (`form_item_id`),
  CONSTRAINT `fk_attempt_items_attempt` FOREIGN KEY (`attempt_id`) REFERENCES `attempts`(`id`),
  CONSTRAINT `fk_attempt_items_item` FOREIGN KEY (`form_item_id`) REFERENCES `form_items`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `osce_sessions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `candidate_id` INT(11) NOT NULL,
  `station` VARCHAR(100) NULL,
  `examiner_id` INT(11) NULL,
  `status` ENUM('waiting','checked_in','in_progress','submitted','timeout') NOT NULL DEFAULT 'waiting',
  `start_time` DATETIME NULL,
  `grading_started_at` DATETIME NULL,
  `end_time` DATETIME NULL,
  `submitted_at` DATETIME NULL,
  `duration_minutes` INT(11) NULL DEFAULT 10,
  `last_action` VARCHAR(255) NULL,
  `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sessions_candidate` (`candidate_id`),
  KEY `idx_sessions_status` (`status`, `start_time`),
  CONSTRAINT `fk_sessions_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `candidates`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sessions_examiner` FOREIGN KEY (`examiner_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- Audit + security
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `osce_logs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `event_type` VARCHAR(50) NULL,
  `user_role` VARCHAR(20) NULL,
  `user_id` INT(11) NULL,
  `message` TEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_logs_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `success` TINYINT(1) NOT NULL DEFAULT 0,
  `attempted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_attempts_username` (`username`, `attempted_at`),
  KEY `idx_attempts_ip` (`ip_address`, `attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `grade_drafts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `examiner_id` INT(11) NOT NULL,
  `candidate_id` INT(11) NOT NULL,
  `form_id` INT(11) NOT NULL,
  `exam_station_id` INT(11) NULL,
  `payload` MEDIUMTEXT NOT NULL,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_draft` (`examiner_id`, `candidate_id`, `form_id`),
  KEY `idx_drafts_examiner` (`examiner_id`),
  CONSTRAINT `fk_drafts_examiner` FOREIGN KEY (`examiner_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_drafts_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `candidates`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_drafts_form` FOREIGN KEY (`form_id`) REFERENCES `forms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- Exam circuit
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `exams` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `facility_id` INT(11) NULL,
  `name` VARCHAR(150) NOT NULL,
  `description` TEXT NULL,
  `exam_date` DATE NULL,
  `status` ENUM('draft','active','completed','archived') NOT NULL DEFAULT 'draft',
  `created_by` INT(11) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_exams_facility` (`facility_id`),
  KEY `idx_exams_status` (`status`),
  CONSTRAINT `fk_exams_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_exams_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `exam_stations` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `exam_id` INT(11) NOT NULL,
  `form_id` INT(11) NOT NULL,
  `station_label` VARCHAR(100) NOT NULL,
  `room` VARCHAR(50) NULL,
  `duration_minutes` INT(11) NOT NULL DEFAULT 8,
  `sort_order` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_es_exam` (`exam_id`),
  KEY `idx_es_form` (`form_id`),
  CONSTRAINT `fk_es_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_es_form` FOREIGN KEY (`form_id`) REFERENCES `forms`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `examiner_assignments` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `exam_station_id` INT(11) NOT NULL,
  `examiner_id` INT(11) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_assign` (`exam_station_id`, `examiner_id`),
  KEY `idx_assign_examiner` (`examiner_id`),
  CONSTRAINT `fk_assign_station` FOREIGN KEY (`exam_station_id`) REFERENCES `exam_stations`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_assign_examiner` FOREIGN KEY (`examiner_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `exam_candidates` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `exam_id` INT(11) NOT NULL,
  `candidate_id` INT(11) NOT NULL,
  `seat_number` VARCHAR(20) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_exam_cand` (`exam_id`, `candidate_id`),
  KEY `idx_ec_candidate` (`candidate_id`),
  CONSTRAINT `fk_ec_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ec_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `candidates`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- Centralised round + bell broadcast
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `exam_rounds` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `facility_id` INT(11) NULL,
  `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_facility` (`facility_id`, `status`),
  CONSTRAINT `fk_rounds_user` FOREIGN KEY (`started_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_rounds_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities`(`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;

-- ----------------------------------------------------------------------------
-- Attach FK from attempts → exam_stations (table now exists)
-- ----------------------------------------------------------------------------
SET @fk_exists := (
    SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE table_schema = DATABASE()
      AND table_name   = 'attempts'
      AND constraint_name = 'fk_attempts_exam_station'
);
SET @sql := IF(@fk_exists = 0,
    'ALTER TABLE `attempts`
        ADD KEY `idx_attempts_exam_station`(`exam_station_id`),
        ADD CONSTRAINT `fk_attempts_exam_station`
        FOREIGN KEY (`exam_station_id`) REFERENCES `exam_stations`(`id`) ON DELETE SET NULL',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ----------------------------------------------------------------------------
-- Seed: one default facility for first-time use
-- ----------------------------------------------------------------------------
INSERT INTO `facilities` (`id`, `name`)
SELECT 1, 'Default OSCE Centre'
WHERE NOT EXISTS (SELECT 1 FROM `facilities` WHERE id = 1);

SET FOREIGN_KEY_CHECKS = 1;
