-- =========================================================
-- INSTALL MODUL KISI-KISI TELUSUR AKREDITASI PFP
-- Aplikasi: Keperawatan RS BaliMed
-- Catatan: Modul juga auto-create table dan auto-seed saat dibuka pertama kali.
-- =========================================================

CREATE TABLE IF NOT EXISTS `pfp_kisi_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `source_no` INT NOT NULL DEFAULT 0,
  `bab` VARCHAR(190) NOT NULL,
  `kode_bab` VARCHAR(20) NOT NULL,
  `no_std` VARCHAR(50) DEFAULT NULL,
  `no_ep` VARCHAR(50) DEFAULT NULL,
  `elemen_penilaian` TEXT,
  `kode_metode` VARCHAR(20) DEFAULT NULL,
  `metode_telusur` VARCHAR(120) DEFAULT NULL,
  `kelengkapan_bukti` TEXT,
  `pertanyaan_utama` MEDIUMTEXT,
  `probing` MEDIUMTEXT,
  `roleplay` MEDIUMTEXT,
  `petugas_sasaran` TEXT,
  `lokasi_telusur` TEXT,
  `ekspektasi_jawaban` MEDIUMTEXT,
  `bukti_wajib` MEDIUMTEXT,
  `checklist` MEDIUMTEXT,
  `red_flags` MEDIUMTEXT,
  `dasar` MEDIUMTEXT,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_source_no` (`source_no`),
  KEY `idx_kode_bab` (`kode_bab`),
  KEY `idx_std_ep` (`no_std`,`no_ep`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `pfp_read_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED DEFAULT NULL,
  `employee_id` BIGINT UNSIGNED DEFAULT NULL,
  `user_name` VARCHAR(190) DEFAULT NULL,
  `username` VARCHAR(100) DEFAULT NULL,
  `unit_id` BIGINT UNSIGNED DEFAULT NULL,
  `unit_name` VARCHAR(190) DEFAULT NULL,
  `section_key` ENUM('overview','pertanyaan','roleplay','bukti','checklist','all') NOT NULL DEFAULT 'overview',
  `read_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ip_address` VARCHAR(60) DEFAULT NULL,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_read_once` (`item_id`,`employee_id`,`username`,`section_key`),
  KEY `idx_employee` (`employee_id`),
  KEY `idx_unit` (`unit_id`),
  KEY `idx_section` (`section_key`),
  KEY `idx_read_at` (`read_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
