-- INSTALL MODUL LOGBOOK & KPI KEPERAWATAN
-- Jalankan di phpMyAdmin pada database aplikasi perawat.

CREATE TABLE IF NOT EXISTS logbook_kpi_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(10) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  description TEXT NULL,
  color VARCHAR(40) DEFAULT '#2563eb',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS logbook_kpi_activity_master (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role_level ENUM('pelaksana','katim','kasubdiv','kadiv') NOT NULL,
  category_code VARCHAR(10) NOT NULL,
  activity_name VARCHAR(220) NOT NULL,
  description TEXT NULL,
  ideal_frequency_type ENUM('per_shift','daily','weekly','monthly','event') NOT NULL DEFAULT 'daily',
  ideal_frequency_value DECIMAL(10,2) NOT NULL DEFAULT 1,
  unit_measure VARCHAR(60) DEFAULT 'kegiatan',
  weight DECIMAL(10,2) NOT NULL DEFAULT 1,
  kpi_domain ENUM('produktivitas','dokumentasi','patient_safety','disiplin','leadership','kompetensi','mutu','kolaborasi') NOT NULL DEFAULT 'produktivitas',
  evidence_required TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_role(role_level), INDEX idx_cat(category_code), INDEX idx_active(is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS logbook_kpi_entries (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
  employee_id BIGINT UNSIGNED NULL,
  employee_name VARCHAR(160) NOT NULL,
  role_level ENUM('pelaksana','katim','kasubdiv','kadiv') NOT NULL,
  unit_id BIGINT UNSIGNED NULL,
  unit_name VARCHAR(160) NULL,
  log_date DATE NOT NULL,
  shift ENUM('Pagi','Sore','Malam','Non Shift','Libur') NOT NULL DEFAULT 'Pagi',
  activity_id INT NOT NULL,
  activity_name VARCHAR(220) NOT NULL,
  category_code VARCHAR(10) NOT NULL,
  kpi_domain VARCHAR(40) NOT NULL,
  ideal_count DECIMAL(10,2) NOT NULL DEFAULT 1,
  real_count DECIMAL(10,2) NOT NULL DEFAULT 0,
  weight DECIMAL(10,2) NOT NULL DEFAULT 1,
  duration_minutes INT NOT NULL DEFAULT 0,
  patients_count INT NOT NULL DEFAULT 0,
  quality_score DECIMAL(5,2) NULL,
  notes TEXT NULL,
  evidence_note VARCHAR(255) NULL,
  status ENUM('draft','submitted','verified','rejected') NOT NULL DEFAULT 'submitted',
  verified_by BIGINT UNSIGNED NULL,
  verified_by_name VARCHAR(160) NULL,
  verified_at DATETIME NULL,
  verifier_note TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user_date(user_id, log_date), INDEX idx_unit_date(unit_id, log_date), INDEX idx_status(status), INDEX idx_role(role_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS logbook_kpi_salary_refs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  month TINYINT NOT NULL,
  year SMALLINT NOT NULL,
  salary_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
  note VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_salary(user_id, month, year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO logbook_kpi_categories(code,name,color) VALUES
('A','Asuhan Keperawatan Langsung','#0ea5e9'),
('B','Dokumentasi Keperawatan','#6366f1'),
('C','Edukasi Pasien/Keluarga','#22c55e'),
('D','Keselamatan Pasien','#ef4444'),
('E','Kolaborasi Interprofesional','#14b8a6'),
('F','Manajemen Shift/Tim','#f97316'),
('G','Mutu dan Audit','#8b5cf6'),
('H','Pendidikan dan Pelatihan','#06b6d4'),
('I','Administrasi dan Laporan','#64748b'),
('J','Pengembangan Profesional','#ec4899');

INSERT INTO logbook_kpi_activity_master(role_level,category_code,activity_name,description,ideal_frequency_type,ideal_frequency_value,unit_measure,weight,kpi_domain,evidence_required,is_active)
SELECT * FROM (
SELECT 'pelaksana' AS role_level, 'F' AS category_code, 'Operan pasien awal shift' AS activity_name, 'Menerima/melakukan timbang terima pasien dengan SBAR' AS description, 'per_shift' AS ideal_frequency_type, 1 AS ideal_frequency_value, 'kegiatan' AS unit_measure, 3 AS weight, 'kolaborasi' AS kpi_domain, 0 AS evidence_required, 1 AS is_active
UNION ALL
SELECT 'pelaksana','D','Identifikasi pasien sebelum tindakan','Kepatuhan identifikasi 2 identitas sebelum tindakan','per_shift',10,'tindakan',5,'patient_safety',0,1
UNION ALL
SELECT 'pelaksana','A','Pengkajian keperawatan pasien','Pengkajian awal/lanjutan sesuai pasien kelolaan','per_shift',5,'pasien',5,'produktivitas',0,1
UNION ALL
SELECT 'pelaksana','B','Dokumentasi CPPT/SOAP','Dokumentasi CPPT/SOAP lengkap dan tepat waktu','per_shift',5,'dokumen',5,'dokumentasi',1,1
UNION ALL
SELECT 'pelaksana','A','Pelaksanaan intervensi keperawatan','Implementasi intervensi sesuai rencana asuhan','per_shift',8,'tindakan',5,'produktivitas',0,1
UNION ALL
SELECT 'pelaksana','A','Monitoring tanda vital','Monitoring TTV sesuai kondisi pasien','per_shift',10,'observasi',4,'produktivitas',0,1
UNION ALL
SELECT 'pelaksana','D','Pencegahan risiko jatuh','Assesment risiko jatuh dan intervensi pencegahan','per_shift',3,'pasien',4,'patient_safety',1,1
UNION ALL
SELECT 'pelaksana','D','Pencegahan luka tekan','Reposisi, Braden, dan proteksi kulit pasien risiko','per_shift',3,'pasien',4,'patient_safety',1,1
UNION ALL
SELECT 'pelaksana','C','Edukasi pasien dan keluarga','Edukasi obat, diet, mobilisasi, DPJP, tanda bahaya','per_shift',2,'edukasi',3,'kompetensi',1,1
UNION ALL
SELECT 'pelaksana','E','Kolaborasi dengan tenaga kesehatan lain','Koordinasi dokter/farmasi/gizi/lab/fisioterapi','per_shift',2,'koordinasi',3,'kolaborasi',0,1
UNION ALL
SELECT 'pelaksana','D','Hand hygiene 5 moment','Kepatuhan PPI pada pelayanan pasien','per_shift',10,'momen',5,'patient_safety',0,1
UNION ALL
SELECT 'pelaksana','I','Pengisian logbook harian','Submit logbook harian dengan catatan jelas','daily',1,'laporan',3,'disiplin',0,1
UNION ALL
SELECT 'katim','F','Memimpin operan shift','Memimpin timbang terima, pembacaan risiko, dan rencana shift','per_shift',1,'kegiatan',4,'leadership',0,1
UNION ALL
SELECT 'katim','F','Pembagian pasien dan tugas staf','Assignment pasien sesuai kompleksitas dan kompetensi staf','per_shift',1,'assignment',5,'leadership',1,1
UNION ALL
SELECT 'katim','D','Identifikasi pasien risiko tinggi','Menentukan prioritas pasien kritis/jatuh/luka tekan','per_shift',1,'daftar',5,'patient_safety',1,1
UNION ALL
SELECT 'katim','B','Validasi dokumentasi askep/CPPT staf','Review CPPT, askep, dan kelengkapan dokumen staf','per_shift',5,'dokumen',5,'dokumentasi',1,1
UNION ALL
SELECT 'katim','A','Supervisi tindakan keperawatan berisiko','Pendampingan tindakan invasif/berisiko tinggi','event',1,'supervisi',5,'patient_safety',1,1
UNION ALL
SELECT 'katim','H','Coaching staf junior/staf baru','Pembinaan klinis singkat berbasis kasus','weekly',2,'sesi',4,'kompetensi',1,1
UNION ALL
SELECT 'katim','I','Laporan situasi shift','Laporan kondisi pasien, SDM, alat, dan masalah unit','per_shift',1,'laporan',4,'dokumentasi',1,1
UNION ALL
SELECT 'katim','G','Mini audit PPI/patient safety','Audit singkat hand hygiene, identifikasi, dan risiko jatuh','weekly',3,'audit',4,'mutu',1,1
UNION ALL
SELECT 'katim','F','Verifikasi logbook perawat pelaksana','Validasi aktivitas staf di bawah koordinasi shift','daily',5,'verifikasi',5,'leadership',0,1
UNION ALL
SELECT 'katim','E','Eskalasi masalah klinis/operasional','Melaporkan masalah signifikan ke Karu/Kasubdiv','event',1,'laporan',4,'kolaborasi',1,1
UNION ALL
SELECT 'kasubdiv','I','Monitoring laporan harian unit','Review laporan unit di bawah sub divisi','daily',1,'dashboard',5,'leadership',0,1
UNION ALL
SELECT 'kasubdiv','F','Validasi masalah unit dan tindak lanjut','Memastikan masalah unit mendapat PIC dan deadline','daily',1,'validasi',5,'leadership',1,1
UNION ALL
SELECT 'kasubdiv','I','Monitoring kecukupan SDM per shift','Analisis rasio pasien-perawat dan gap SDM','daily',1,'analisis',5,'mutu',1,1
UNION ALL
SELECT 'kasubdiv','G','Supervisi lapangan ke unit','Supervisi pelayanan, dokumentasi, dan kepatuhan SOP','weekly',3,'kunjungan',5,'mutu',1,1
UNION ALL
SELECT 'kasubdiv','G','Review indikator mutu unit','Review indikator mutu, insiden, dan trend capaian','weekly',1,'review',4,'mutu',1,1
UNION ALL
SELECT 'kasubdiv','D','Review insiden keselamatan pasien','Telaah IKP/near miss dan tindak lanjut awal','event',1,'kasus',5,'patient_safety',1,1
UNION ALL
SELECT 'kasubdiv','H','Pembinaan Katim/Karu','Coaching leadership dan manajemen unit','weekly',2,'sesi',4,'kompetensi',1,1
UNION ALL
SELECT 'kasubdiv','I','Menyusun laporan bulanan sub divisi','Konsolidasi capaian, masalah, SDM, dan rencana perbaikan','monthly',1,'laporan',5,'dokumentasi',1,1
UNION ALL
SELECT 'kasubdiv','G','Evaluasi logbook unit','Analisis real vs ideal aktivitas unit','monthly',1,'rekap',5,'produktivitas',1,1
UNION ALL
SELECT 'kasubdiv','E','Koordinasi lintas unit','Koordinasi masalah pelayanan lintas unit/divisi','event',1,'koordinasi',4,'kolaborasi',1,1
UNION ALL
SELECT 'kadiv','I','Monitoring dashboard seluruh sub divisi','Pemantauan capaian seluruh sub divisi keperawatan','daily',1,'dashboard',5,'leadership',0,1
UNION ALL
SELECT 'kadiv','F','Review masalah prioritas keperawatan','Penetapan isu strategis harian/mingguan','weekly',2,'review',5,'leadership',1,1
UNION ALL
SELECT 'kadiv','G','Menetapkan prioritas perbaikan mutu','Menentukan fokus mutu dan program PDSA','monthly',1,'program',5,'mutu',1,1
UNION ALL
SELECT 'kadiv','D','Telaah insiden mayor/risiko tinggi','RCA dan tindak lanjut insiden signifikan','event',1,'kasus',5,'patient_safety',1,1
UNION ALL
SELECT 'kadiv','I','Evaluasi kecukupan SDM dan beban kerja','Analisis workload, kebutuhan SDM, dan distribusi tenaga','monthly',1,'analisis',5,'mutu',1,1
UNION ALL
SELECT 'kadiv','G','Evaluasi produktivitas logbook seluruh unit','Komparasi skor real, ideal, dan kinerja unit','monthly',1,'rekap',5,'produktivitas',1,1
UNION ALL
SELECT 'kadiv','H','Monitoring jenjang karir dan kompetensi','Evaluasi PK, diklat, kredensial, dan kompetensi staf','monthly',1,'review',5,'kompetensi',1,1
UNION ALL
SELECT 'kadiv','I','Menyetujui KPI final keperawatan','Approval KPI bulanan berbasis data','monthly',1,'approval',5,'leadership',1,1
UNION ALL
SELECT 'kadiv','I','Laporan divisi keperawatan','Laporan resmi untuk direksi/manajemen','monthly',1,'laporan',5,'dokumentasi',1,1
UNION ALL
SELECT 'kadiv','J','Inovasi dan transformasi digital keperawatan','Pengembangan sistem, SOP digital, dan dashboard kinerja','monthly',1,'project',4,'kompetensi',1,1
) seed WHERE NOT EXISTS (SELECT 1 FROM logbook_kpi_activity_master LIMIT 1);
