-- ============================================================================
-- OSCE Nexus v2.1 schema extras
--   - per-form default timer
--   - face-login columns on users
--   - per-session grading_started_at / submitted_at timestamps
--   - extra index for "recent submissions" queries
-- Idempotent.
-- ============================================================================

-- forms.default_duration_minutes -------------------------------------------
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
           WHERE table_schema=DATABASE() AND table_name='forms' AND column_name='default_duration_minutes');
SET @sql := IF(@c = 0,
    'ALTER TABLE forms ADD COLUMN default_duration_minutes INT NOT NULL DEFAULT 8 AFTER description',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- users face-login columns -------------------------------------------------
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
           WHERE table_schema=DATABASE() AND table_name='users' AND column_name='face_descriptor');
SET @sql := IF(@c = 0,
    'ALTER TABLE users
        ADD COLUMN face_descriptor MEDIUMTEXT NULL,
        ADD COLUMN face_enrolled_at DATETIME NULL,
        ADD COLUMN face_login_enabled TINYINT(1) NOT NULL DEFAULT 0',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- osce_sessions timestamps -------------------------------------------------
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
           WHERE table_schema=DATABASE() AND table_name='osce_sessions' AND column_name='grading_started_at');
SET @sql := IF(@c = 0,
    'ALTER TABLE osce_sessions
        ADD COLUMN grading_started_at DATETIME NULL AFTER start_time,
        ADD COLUMN submitted_at DATETIME NULL AFTER end_time',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- attempts (examiner, created) index --------------------------------------
SET @i := (SELECT COUNT(*) FROM information_schema.STATISTICS
           WHERE table_schema=DATABASE() AND table_name='attempts' AND index_name='idx_attempts_examiner_created');
SET @sql := IF(@i = 0,
    'CREATE INDEX idx_attempts_examiner_created ON attempts(examiner_id, created_at)',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
