-- ============================================================================
-- OSCE Nexus v2 schema upgrade
-- Apply this AFTER the original osce_pro_db.sql has been imported.
-- Idempotent where possible: uses IF NOT EXISTS / IF EXISTS guards.
-- Tested on MariaDB 10.4+ / MySQL 5.7+.
-- ============================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------------------
-- 1. Login throttling
-- ----------------------------------------------------------------------------
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;

-- ----------------------------------------------------------------------------
-- 2. Soft-delete columns on the data tables
-- ----------------------------------------------------------------------------
-- candidates.deleted_at
SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE table_schema = DATABASE()
      AND table_name = 'candidates'
      AND column_name = 'deleted_at'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `candidates` ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `created_at`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- attempts.deleted_at
SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE table_schema = DATABASE()
      AND table_name = 'attempts'
      AND column_name = 'deleted_at'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `attempts` ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `created_at`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- forms.deleted_at
SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE table_schema = DATABASE()
      AND table_name = 'forms'
      AND column_name = 'deleted_at'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `forms` ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `created_at`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ----------------------------------------------------------------------------
-- 3. Audit log – the legacy table is fine; just widen role enum
--    to include 'super_admin' / 'facility' if we want to log them verbatim.
--    The audit_log() helper already maps roles, so this is optional.
-- ----------------------------------------------------------------------------
ALTER TABLE `osce_logs`
    MODIFY COLUMN `user_role` VARCHAR(20) NULL DEFAULT NULL;

-- index for time-ordered queries
SET @idx_exists := (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE table_schema = DATABASE()
      AND table_name = 'osce_logs'
      AND index_name = 'idx_logs_created_at'
);
SET @sql := IF(@idx_exists = 0,
    'CREATE INDEX `idx_logs_created_at` ON `osce_logs`(`created_at`)',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ----------------------------------------------------------------------------
-- 4. Exam day / circuit model
--    An "exam" is one OSCE event (e.g. "Final OSCE – June 2026").
--    It has many stations (each tied to a form template).
-- ----------------------------------------------------------------------------
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,        -- e.g. "Station 1 – CVS"
  `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;

-- ----------------------------------------------------------------------------
-- 5. Attach an attempt to an exam_station for analytics
-- ----------------------------------------------------------------------------
SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE table_schema = DATABASE()
      AND table_name = 'attempts'
      AND column_name = 'exam_station_id'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `attempts` ADD COLUMN `exam_station_id` INT(11) NULL DEFAULT NULL AFTER `form_id`,
         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;

-- ----------------------------------------------------------------------------
-- 6. osce_sessions: add FKs (so deleted candidates don't leave orphans)
--    Only add if not already present.
-- ----------------------------------------------------------------------------
SET @fk_exists := (
    SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE table_schema = DATABASE()
      AND table_name = 'osce_sessions'
      AND constraint_name = 'fk_sessions_candidate'
);
SET @sql := IF(@fk_exists = 0,
    'ALTER TABLE `osce_sessions`
        ADD CONSTRAINT `fk_sessions_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `candidates`(`id`) ON DELETE CASCADE,
        ADD CONSTRAINT `fk_sessions_examiner` FOREIGN KEY (`examiner_id`)   REFERENCES `users`(`id`)      ON DELETE SET NULL',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ----------------------------------------------------------------------------
-- 7. Autosave drafts (per examiner+station)
-- ----------------------------------------------------------------------------
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,     -- JSON snapshot of scores/comments
  `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;

-- ----------------------------------------------------------------------------
-- 8. Helpful indexes on existing tables
-- ----------------------------------------------------------------------------
SET @idx_exists := (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE table_schema = DATABASE() AND table_name = 'attempts' AND index_name = 'idx_attempts_created'
);
SET @sql := IF(@idx_exists = 0,
    'CREATE INDEX `idx_attempts_created` ON `attempts`(`created_at`)',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================================
-- After running this file:
--   1. Update auth.php / config.php to v2 (use .osce_env).
--   2. Delete make_hash.php from the server.
--   3. Verify .osce_env is OUTSIDE public_html and chmod 600.
--   4. Force every existing user to change their password (recommended).
-- ============================================================================
