CREATE TABLE IF NOT EXISTS itc_instructors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NULL,
  instructor_code VARCHAR(50) NULL,
  name VARCHAR(200) NOT NULL,
  title_front VARCHAR(50) NULL,
  title_back VARCHAR(80) NULL,
  profession VARCHAR(100) NULL,
  expertise TEXT NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(150) NULL,
  status ENUM('aktif','nonaktif') DEFAULT 'aktif',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_employee_id (employee_id),
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS itc_curriculum (
  id INT AUTO_INCREMENT PRIMARY KEY,
  curriculum_code VARCHAR(50) NULL,
  title VARCHAR(200) NOT NULL,
  target_role VARCHAR(150) NULL,
  target_unit VARCHAR(150) NULL,
  competency_level VARCHAR(80) NULL,
  description TEXT NULL,
  min_score DECIMAL(5,2) DEFAULT 70,
  validity_months INT DEFAULT 24,
  status ENUM('aktif','nonaktif') DEFAULT 'aktif',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS itc_materials (
  id INT AUTO_INCREMENT PRIMARY KEY,
  material_code VARCHAR(50) NULL,
  curriculum_id INT NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT NULL,
  material_type ENUM('file','link','text','video') DEFAULT 'text',
  file_path VARCHAR(255) NULL,
  external_url TEXT NULL,
  content LONGTEXT NULL,
  unit_name VARCHAR(150) NULL,
  has_quiz TINYINT(1) DEFAULT 0,
  point_view INT DEFAULT 1,
  pass_score DECIMAL(5,2) DEFAULT 70,
  is_public TINYINT(1) DEFAULT 1,
  created_by INT NULL,
  view_count INT DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_curriculum (curriculum_id),
  KEY idx_unit (unit_name),
  KEY idx_public (is_public)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS itc_material_activity (
  id INT AUTO_INCREMENT PRIMARY KEY,
  material_id INT NOT NULL,
  employee_id INT NOT NULL DEFAULT 0,
  user_name VARCHAR(200) NULL,
  nik VARCHAR(80) NULL,
  unit_name VARCHAR(150) NULL,
  role_name VARCHAR(80) NULL,
  activity_date DATE NOT NULL,
  duration_seconds INT NOT NULL DEFAULT 0,
  status ENUM('mulai','sedang','selesai') DEFAULT 'mulai',
  source VARCHAR(30) DEFAULT 'web',
  started_at DATETIME NULL,
  last_seen_at DATETIME NULL,
  finished_at DATETIME NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_material_user_date (material_id, employee_id, activity_date),
  KEY idx_material (material_id),
  KEY idx_employee (employee_id),
  KEY idx_date (activity_date),
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS itc_schedules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  instructor_id INT NULL,
  curriculum_id INT NULL,
  title VARCHAR(200) NOT NULL,
  schedule_date DATE NOT NULL,
  start_time TIME NULL,
  end_time TIME NULL,
  location VARCHAR(200) NULL,
  method ENUM('offline','online','hybrid') DEFAULT 'offline',
  link_online TEXT NULL,
  quota INT DEFAULT 30,
  notes TEXT NULL,
  status ENUM('draft','aktif','selesai','batal') DEFAULT 'aktif',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_date (schedule_date),
  KEY idx_instructor (instructor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS itc_question_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  description TEXT NULL,
  status ENUM('aktif','nonaktif') DEFAULT 'aktif',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS itc_questions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NULL,
  question_text TEXT NOT NULL,
  option_a TEXT NULL,
  option_b TEXT NULL,
  option_c TEXT NULL,
  option_d TEXT NULL,
  option_e TEXT NULL,
  correct_answer CHAR(1) NOT NULL,
  explanation TEXT NULL,
  difficulty ENUM('mudah','sedang','sulit') DEFAULT 'sedang',
  status ENUM('aktif','nonaktif') DEFAULT 'aktif',
  created_by INT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_category (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS itc_tryouts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  curriculum_id INT NULL,
  category_id INT NULL,
  duration_minutes INT DEFAULT 30,
  pass_score DECIMAL(5,2) DEFAULT 70,
  randomize_questions TINYINT(1) DEFAULT 1,
  show_result TINYINT(1) DEFAULT 1,
  status ENUM('draft','aktif','nonaktif') DEFAULT 'aktif',
  created_by INT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS itc_exam_results (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tryout_id INT NOT NULL,
  employee_id INT NULL,
  user_name VARCHAR(200) NULL,
  score DECIMAL(5,2) DEFAULT 0,
  total_questions INT DEFAULT 0,
  correct_count INT DEFAULT 0,
  wrong_count INT DEFAULT 0,
  status ENUM('lulus','tidak_lulus') DEFAULT 'tidak_lulus',
  started_at DATETIME NULL,
  submitted_at DATETIME NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_tryout (tryout_id),
  KEY idx_employee (employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS itc_certificates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  certificate_number VARCHAR(100) NOT NULL,
  result_id INT NULL,
  employee_id INT NULL,
  participant_name VARCHAR(200) NOT NULL,
  title VARCHAR(220) NOT NULL,
  score DECIMAL(5,2) DEFAULT 0,
  issued_date DATE NOT NULL,
  valid_until DATE NULL,
  verification_code VARCHAR(100) NOT NULL,
  issued_by INT NULL,
  status ENUM('draft','issued','revoked') DEFAULT 'issued',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_cert_number (certificate_number),
  UNIQUE KEY uk_verify (verification_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO itc_question_categories (id, name, description, status) VALUES
(1, 'Keperawatan Umum', 'Bank soal kompetensi dasar keperawatan', 'aktif'),
(2, 'Patient Safety', 'Bank soal keselamatan pasien', 'aktif'),
(3, 'PPI', 'Bank soal pencegahan dan pengendalian infeksi', 'aktif');

ALTER TABLE itc_materials ADD COLUMN IF NOT EXISTS description TEXT NULL;
ALTER TABLE itc_materials ADD COLUMN IF NOT EXISTS has_quiz TINYINT(1) DEFAULT 0;
ALTER TABLE itc_materials ADD COLUMN IF NOT EXISTS point_view INT DEFAULT 1;
ALTER TABLE itc_materials ADD COLUMN IF NOT EXISTS pass_score DECIMAL(5,2) DEFAULT 70;
ALTER TABLE itc_materials ADD COLUMN IF NOT EXISTS view_count INT DEFAULT 0;
