-- Face verify config + logs (ฐาน e-learning / DB_NAME2)
-- รัน: npm run migrate:004

CREATE TABLE IF NOT EXISTS tb_face_verify_config (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  scope ENUM('global', 'course', 'exam') NOT NULL DEFAULT 'global',
  course_id VARCHAR(50) NULL,
  recid VARCHAR(50) NULL,
  itemid VARCHAR(50) NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  preset VARCHAR(32) NOT NULL DEFAULT 'standard',
  similarity_alert_below DECIMAL(5,2) NOT NULL DEFAULT 65.00,
  similarity_critical_below DECIMAL(5,2) NOT NULL DEFAULT 45.00,
  fail_count_before_notify INT UNSIGNED NOT NULL DEFAULT 3,
  check_interval_sec INT UNSIGNED NOT NULL DEFAULT 120,
  grace_period_start_min INT UNSIGNED NOT NULL DEFAULT 10,
  notify_level ENUM('log', 'alert', 'lock') NOT NULL DEFAULT 'alert',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_face_verify_scope (scope, course_id, recid, itemid),
  KEY idx_face_verify_course (course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tb_face_verify_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id VARCHAR(50) NOT NULL,
  course_id VARCHAR(50) NOT NULL DEFAULT '',
  recid VARCHAR(50) NOT NULL DEFAULT '',
  itemid VARCHAR(50) NOT NULL DEFAULT '',
  snapshot_url VARCHAR(512) NULL,
  similarity DECIMAL(5,2) NULL,
  passed TINYINT(1) NOT NULL DEFAULT 0,
  reason VARCHAR(255) NULL,
  fail_streak INT UNSIGNED NOT NULL DEFAULT 0,
  notified TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_face_log_user_exam (user_id, course_id, recid, itemid, created_at),
  KEY idx_face_log_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO tb_face_verify_config (
  scope, enabled, preset,
  similarity_alert_below, similarity_critical_below,
  fail_count_before_notify, check_interval_sec, grace_period_start_min, notify_level
)
SELECT 'global', 1, 'standard', 65.00, 45.00, 3, 120, 10, 'alert'
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM tb_face_verify_config WHERE scope = 'global' LIMIT 1
);