-- Modul Analisa Margin Operasi BPJS RS BaliMéd
-- Update perhitungan reward & punishment terbaru

CREATE TABLE IF NOT EXISTS bpjs_margin_uploads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  judul VARCHAR(200) NOT NULL,
  periode_bulan VARCHAR(30) NULL,
  periode_tahun INT NULL,
  file_name VARCHAR(255) NULL,
  original_file_name VARCHAR(255) NULL,
  total_rows INT DEFAULT 0,
  uploaded_by INT NULL,
  uploaded_by_name VARCHAR(150) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bpjs_margin_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kelas ENUM('Kelas 1','Kelas 2','Kelas 3') NOT NULL UNIQUE,
  threshold_persen DECIMAL(5,2) NOT NULL,
  reward_persen DECIMAL(5,2) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Jika server mendukung IF NOT EXISTS, perintah ini aman dijalankan berulang.
ALTER TABLE bpjs_margin_settings ADD COLUMN IF NOT EXISTS reward_persen DECIMAL(5,2) NOT NULL DEFAULT 0 AFTER threshold_persen;

INSERT IGNORE INTO bpjs_margin_settings (kelas, threshold_persen, reward_persen) VALUES
('Kelas 1', 35.00, 40.00),
('Kelas 2', 25.00, 35.00),
('Kelas 3', 20.00, 25.00);

UPDATE bpjs_margin_settings SET threshold_persen=35.00, reward_persen=40.00 WHERE kelas='Kelas 1';
UPDATE bpjs_margin_settings SET threshold_persen=25.00, reward_persen=35.00 WHERE kelas='Kelas 2';
UPDATE bpjs_margin_settings SET threshold_persen=20.00, reward_persen=25.00 WHERE kelas='Kelas 3';

CREATE TABLE IF NOT EXISTS bpjs_margin_details (
  id INT AUTO_INCREMENT PRIMARY KEY,
  upload_id INT NOT NULL,
  sumber_sheet VARCHAR(80) NULL,
  no_urut INT NULL,
  tgl_masuk VARCHAR(30) NULL,
  tgl_keluar VARCHAR(30) NULL,
  nama_pasien VARCHAR(180) NULL,
  no_rm VARCHAR(80) NULL,
  no_registrasi VARCHAR(100) NULL,
  no_sep VARCHAR(120) NULL,
  kelas_raw VARCHAR(120) NULL,
  kelas_norm ENUM('Kelas 1','Kelas 2','Kelas 3') NULL,
  nama_dpjp VARCHAR(180) NULL,
  diagnosis TEXT NULL,
  inacbg VARCHAR(80) NULL,
  deskripsi_inacbg TEXT NULL,
  tarif_inacbg DECIMAL(18,2) DEFAULT 0,
  tarif_naik_kelas DECIMAL(18,2) DEFAULT 0,
  klaim_bpjs DECIMAL(18,2) DEFAULT 0,
  billing_rs DECIMAL(18,2) DEFAULT 0,
  jasa_rs DECIMAL(18,2) DEFAULT 0,
  persen_jasa_rs DECIMAL(12,6) DEFAULT 0,
  threshold_margin DECIMAL(12,6) DEFAULT 0,
  minimal_jasa_rs DECIMAL(18,2) DEFAULT 0,
  reward_threshold DECIMAL(12,6) DEFAULT 0,
  maksimal_jasa_rs DECIMAL(18,2) DEFAULT 0,
  selisih_margin DECIMAL(18,2) DEFAULT 0,
  status_margin ENUM('SURPLUS','MINUS','AMAN') NULL,
  nominal_surplus DECIMAL(18,2) DEFAULT 0,
  nominal_minus DECIMAL(18,2) DEFAULT 0,
  bagian_rs DECIMAL(18,2) DEFAULT 0,
  bagian_dpjp DECIMAL(18,2) DEFAULT 0,
  penyebab_minus TEXT NULL,
  rekomendasi TEXT NULL,
  is_minus_excluded TINYINT(1) NOT NULL DEFAULT 0,
  minus_exclude_note TEXT NULL,
  minus_excluded_by VARCHAR(150) NULL,
  minus_excluded_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_upload (upload_id),
  INDEX idx_dpjp (nama_dpjp),
  INDEX idx_status (status_margin),
  CONSTRAINT fk_bpjs_margin_upload FOREIGN KEY (upload_id) REFERENCES bpjs_margin_uploads(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE bpjs_margin_details MODIFY status_margin ENUM('SURPLUS','MINUS','AMAN') NULL;
ALTER TABLE bpjs_margin_details ADD COLUMN IF NOT EXISTS reward_threshold DECIMAL(12,6) DEFAULT 0 AFTER minimal_jasa_rs;
ALTER TABLE bpjs_margin_details ADD COLUMN IF NOT EXISTS maksimal_jasa_rs DECIMAL(18,2) DEFAULT 0 AFTER reward_threshold;
ALTER TABLE bpjs_margin_details ADD COLUMN IF NOT EXISTS is_minus_excluded TINYINT(1) NOT NULL DEFAULT 0 AFTER rekomendasi;
ALTER TABLE bpjs_margin_details ADD COLUMN IF NOT EXISTS minus_exclude_note TEXT NULL AFTER is_minus_excluded;
ALTER TABLE bpjs_margin_details ADD COLUMN IF NOT EXISTS minus_excluded_by VARCHAR(150) NULL AFTER minus_exclude_note;
ALTER TABLE bpjs_margin_details ADD COLUMN IF NOT EXISTS minus_excluded_at DATETIME NULL AFTER minus_excluded_by;

-- Update UI/field klinis: tampilkan Diagnosis dan Deskripsi INA-CBG di Detail serta modal Dasar Exclude.
ALTER TABLE bpjs_margin_details ADD COLUMN IF NOT EXISTS diagnosis TEXT NULL AFTER nama_dpjp;
ALTER TABLE bpjs_margin_details ADD COLUMN IF NOT EXISTS inacbg VARCHAR(80) NULL AFTER diagnosis;
ALTER TABLE bpjs_margin_details ADD COLUMN IF NOT EXISTS deskripsi_inacbg TEXT NULL AFTER inacbg;

-- Update revisi perhitungan terbaru: minus mengurangi jasa dokter, surplus menambah reward DPJP 50%.
ALTER TABLE bpjs_margin_details ADD COLUMN IF NOT EXISTS jasa_dokter_awal DECIMAL(18,2) DEFAULT 0 AFTER bagian_dpjp;
ALTER TABLE bpjs_margin_details ADD COLUMN IF NOT EXISTS potongan_minus_aktif DECIMAL(18,2) DEFAULT 0 AFTER jasa_dokter_awal;
ALTER TABLE bpjs_margin_details ADD COLUMN IF NOT EXISTS tambahan_reward_dpjp DECIMAL(18,2) DEFAULT 0 AFTER potongan_minus_aktif;
ALTER TABLE bpjs_margin_details ADD COLUMN IF NOT EXISTS jasa_dokter_final DECIMAL(18,2) DEFAULT 0 AFTER tambahan_reward_dpjp;
