-- =========================================================
-- INSTALL / MIGRASI MODUL PATIENT SAFETY - IKP KARS/PMKP
-- Aman dijalankan berulang. Modul PHP juga memiliki auto-setup.
-- =========================================================

CREATE TABLE IF NOT EXISTS patient_safety_incidents (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  incident_number VARCHAR(60) NOT NULL UNIQUE,
  report_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  incident_date DATETIME NOT NULL,
  unit_id BIGINT UNSIGNED NULL,
  location_detail VARCHAR(190) NULL,
  reporter_user_id BIGINT UNSIGNED NULL,
  reporter_name VARCHAR(190) NULL,
  reporter_unit VARCHAR(190) NULL,
  patient_name VARCHAR(190) NULL,
  patient_initial VARCHAR(50) NULL,
  medical_record_no VARCHAR(80) NULL,
  birth_date DATE NULL,
  age VARCHAR(50) NULL,
  gender ENUM('L','P','-') NOT NULL DEFAULT '-',
  service_type VARCHAR(80) NULL,
  dpjp VARCHAR(190) NULL,
  incident_category ENUM('KPCS','KNC','KTC','KTD','SENTINEL') NOT NULL DEFAULT 'KNC',
  incident_type VARCHAR(150) NOT NULL,
  incident_subtype VARCHAR(150) NULL,
  chronology LONGTEXT NOT NULL,
  immediate_action TEXT NULL,
  patient_impact TEXT NULL,
  family_disclosure ENUM('ya','tidak','tidak_perlu') NOT NULL DEFAULT 'tidak_perlu',
  witnesses VARCHAR(255) NULL,
  severity_score TINYINT UNSIGNED NOT NULL DEFAULT 1,
  probability_score TINYINT UNSIGNED NOT NULL DEFAULT 1,
  risk_score TINYINT UNSIGNED NOT NULL DEFAULT 1,
  risk_grade ENUM('BIRU','HIJAU','KUNING','MERAH') NOT NULL DEFAULT 'BIRU',
  investigation_type VARCHAR(80) NOT NULL DEFAULT 'Investigasi sederhana <= 7 hari',
  root_cause TEXT NULL,
  contributing_factors TEXT NULL,
  recommendation TEXT NULL,
  action_plan TEXT NULL,
  pic_name VARCHAR(190) NULL,
  target_date DATE NULL,
  follow_up TEXT NULL,
  effectiveness_monitoring TEXT NULL,
  external_report ENUM('tidak','ya') NOT NULL DEFAULT 'tidak',
  status ENUM('reported','verified','investigation','follow_up','closed') NOT NULL DEFAULT 'reported',
  verified_by VARCHAR(190) NULL,
  verified_at DATETIME NULL,
  closed_at DATETIME NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_ps_date (incident_date),
  KEY idx_ps_unit (unit_id),
  KEY idx_ps_category (incident_category),
  KEY idx_ps_grade (risk_grade),
  KEY idx_ps_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS report_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER incident_number;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS location_detail VARCHAR(190) NULL AFTER unit_id;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS reporter_name VARCHAR(190) NULL AFTER reporter_user_id;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS reporter_unit VARCHAR(190) NULL AFTER reporter_name;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS patient_initial VARCHAR(50) NULL AFTER patient_name;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS birth_date DATE NULL AFTER medical_record_no;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS age VARCHAR(50) NULL AFTER birth_date;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS gender ENUM('L','P','-') NOT NULL DEFAULT '-' AFTER age;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS service_type VARCHAR(80) NULL AFTER gender;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS dpjp VARCHAR(190) NULL AFTER service_type;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS incident_category ENUM('KPCS','KNC','KTC','KTD','SENTINEL') NOT NULL DEFAULT 'KNC' AFTER dpjp;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS incident_subtype VARCHAR(150) NULL AFTER incident_type;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS immediate_action TEXT NULL AFTER chronology;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS patient_impact TEXT NULL AFTER immediate_action;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS family_disclosure ENUM('ya','tidak','tidak_perlu') NOT NULL DEFAULT 'tidak_perlu' AFTER patient_impact;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS witnesses VARCHAR(255) NULL AFTER family_disclosure;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS severity_score TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER witnesses;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS probability_score TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER severity_score;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS risk_score TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER probability_score;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS risk_grade ENUM('BIRU','HIJAU','KUNING','MERAH') NOT NULL DEFAULT 'BIRU' AFTER risk_score;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS investigation_type VARCHAR(80) NOT NULL DEFAULT 'Investigasi sederhana <= 7 hari' AFTER risk_grade;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS root_cause TEXT NULL AFTER investigation_type;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS contributing_factors TEXT NULL AFTER root_cause;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS recommendation TEXT NULL AFTER contributing_factors;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS action_plan TEXT NULL AFTER recommendation;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS pic_name VARCHAR(190) NULL AFTER action_plan;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS target_date DATE NULL AFTER pic_name;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS follow_up TEXT NULL AFTER target_date;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS effectiveness_monitoring TEXT NULL AFTER follow_up;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS external_report ENUM('tidak','ya') NOT NULL DEFAULT 'tidak' AFTER effectiveness_monitoring;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS verified_by VARCHAR(190) NULL AFTER status;
ALTER TABLE patient_safety_incidents ADD COLUMN IF NOT EXISTS verified_at DATETIME NULL AFTER verified_by;

ALTER TABLE patient_safety_incidents MODIFY status ENUM('reported','verified','investigation','follow_up','closed') NOT NULL DEFAULT 'reported';
ALTER TABLE patient_safety_incidents MODIFY incident_category ENUM('KPCS','KNC','KTC','KTD','SENTINEL') NOT NULL DEFAULT 'KNC';
ALTER TABLE patient_safety_incidents MODIFY risk_grade ENUM('BIRU','HIJAU','KUNING','MERAH') NOT NULL DEFAULT 'BIRU';
ALTER TABLE patient_safety_incidents MODIFY external_report ENUM('tidak','ya') NOT NULL DEFAULT 'tidak';
ALTER TABLE patient_safety_incidents MODIFY family_disclosure ENUM('ya','tidak','tidak_perlu') NOT NULL DEFAULT 'tidak_perlu';

INSERT IGNORE INTO patient_safety_incidents
(incident_number, report_date, incident_date, unit_id, reporter_user_id, patient_name, medical_record_no, incident_category, incident_type, chronology, immediate_action, severity_score, probability_score, risk_score, risk_grade, investigation_type, follow_up, status, closed_at, created_at, updated_at)
SELECT incident_number, COALESCE(created_at, NOW()), incident_date, unit_id, reporter_user_id, patient_name, medical_record_no,
CASE WHEN severity='sentinel' THEN 'SENTINEL' WHEN severity='berat' THEN 'KTD' ELSE 'KNC' END,
incident_type, chronology, immediate_action,
CASE severity WHEN 'ringan' THEN 2 WHEN 'sedang' THEN 3 WHEN 'berat' THEN 4 WHEN 'sentinel' THEN 5 ELSE 1 END,
2,
CASE severity WHEN 'ringan' THEN 4 WHEN 'sedang' THEN 6 WHEN 'berat' THEN 8 WHEN 'sentinel' THEN 10 ELSE 2 END,
CASE WHEN severity IN ('berat','sentinel') THEN 'MERAH' WHEN severity='sedang' THEN 'KUNING' ELSE 'HIJAU' END,
CASE WHEN severity='sentinel' THEN 'RCA komprehensif <= 45 hari' WHEN severity='berat' THEN 'RCA / Simple RCA' ELSE 'Investigasi sederhana' END,
follow_up_notes,
CASE WHEN status IN ('selesai','ditutup') THEN 'closed' WHEN status='investigasi' THEN 'investigation' ELSE 'reported' END,
closed_at, created_at, updated_at
FROM patient_incidents;
