-- ============================================================================
-- OSCE Nexus v2.3 – role expansion, multi-institute, candidate metadata.
-- Idempotent. Apply after 003_rounds.sql.
-- ============================================================================

-- 1) Add coordinator + examiner to users.role enum (keep 'facility' as legacy).
SET @cur := (SELECT COLUMN_TYPE FROM information_schema.COLUMNS
             WHERE table_schema = DATABASE() AND table_name = 'users' AND column_name = 'role');
SET @needs := IF(LOCATE('coordinator', @cur) = 0 OR LOCATE('examiner', @cur) = 0, 1, 0);
SET @sql := IF(@needs = 1,
    "ALTER TABLE users MODIFY COLUMN role ENUM('super_admin','coordinator','examiner','facility') NOT NULL DEFAULT 'examiner'",
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 2) Facilities: institute metadata
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
           WHERE table_schema=DATABASE() AND table_name='facilities' AND column_name='code');
SET @sql := IF(@c = 0,
    "ALTER TABLE facilities
        ADD COLUMN code VARCHAR(20) NULL UNIQUE AFTER name,
        ADD COLUMN address VARCHAR(255) NULL,
        ADD COLUMN city VARCHAR(100) NULL,
        ADD COLUMN country VARCHAR(100) NULL,
        ADD COLUMN contact_email VARCHAR(120) NULL,
        ADD COLUMN contact_phone VARCHAR(40) NULL,
        ADD COLUMN deleted_at DATETIME NULL DEFAULT NULL",
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 3) Candidates: section + circuit metadata
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
           WHERE table_schema=DATABASE() AND table_name='candidates' AND column_name='section');
SET @sql := IF(@c = 0,
    "ALTER TABLE candidates
        ADD COLUMN section VARCHAR(50) NULL AFTER name,
        ADD COLUMN circuit_type VARCHAR(50) NULL AFTER section,
        ADD COLUMN cohort_year VARCHAR(10) NULL AFTER circuit_type",
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 4) Exam rounds: facility scoping
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
           WHERE table_schema=DATABASE() AND table_name='exam_rounds' AND column_name='facility_id');
SET @sql := IF(@c = 0,
    "ALTER TABLE exam_rounds
        ADD COLUMN facility_id INT(11) NULL AFTER id,
        ADD KEY idx_rounds_facility (facility_id, status),
        ADD CONSTRAINT fk_rounds_facility FOREIGN KEY (facility_id) REFERENCES facilities(id) ON DELETE SET NULL",
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
