-- ============================================================
-- SIPENARI - Sistem Pengelolaan Manajemen Risiko
-- Balai Kekarantinaan Kesehatan Kelas I Samarinda
-- Database Schema
-- ============================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+07:00";
SET NAMES utf8mb4;

CREATE DATABASE IF NOT EXISTS `sipenari_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `sipenari_db`;

-- ============================================================
-- TABLE: users
-- ============================================================
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `nama_lengkap` varchar(200) NOT NULL,
  `nip` varchar(30) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `jabatan` varchar(200) DEFAULT NULL,
  `role` enum('admin','pemilik_risiko','pemeriksa') NOT NULL DEFAULT 'pemilik_risiko',
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `avatar` varchar(255) DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: unit_pemilik_risiko (UPR)
-- ============================================================
CREATE TABLE `unit_pemilik_risiko` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kode_upr` varchar(20) NOT NULL,
  `nama_upr` varchar(300) NOT NULL,
  `level` enum('UPR-T.I','UPR-T.II') NOT NULL DEFAULT 'UPR-T.II',
  `ketua_upr_id` int(11) DEFAULT NULL,
  `sekretaris_upr_id` int(11) DEFAULT NULL,
  `deskripsi` text DEFAULT NULL,
  `aktif` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `kode_upr` (`kode_upr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: periode_risiko
-- ============================================================
CREATE TABLE `periode_risiko` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tahun` year(4) NOT NULL,
  `semester` enum('1','2') NOT NULL,
  `nama_periode` varchar(100) NOT NULL,
  `tanggal_mulai` date NOT NULL,
  `tanggal_selesai` date NOT NULL,
  `status` enum('draft','aktif','tutup') NOT NULL DEFAULT 'draft',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: sasaran_organisasi
-- ============================================================
CREATE TABLE `sasaran_organisasi` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `upr_id` int(11) NOT NULL,
  `periode_id` int(11) NOT NULL,
  `tujuan` text NOT NULL,
  `sasaran` text NOT NULL,
  `iku` text DEFAULT NULL COMMENT 'Indikator Kinerja Utama',
  `target` text DEFAULT NULL,
  `program` text DEFAULT NULL,
  `kegiatan` text DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: piagam_manajemen_risiko
-- ============================================================
CREATE TABLE `piagam_manajemen_risiko` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `upr_id` int(11) NOT NULL,
  `periode_id` int(11) NOT NULL,
  `tanggal_piagam` date NOT NULL,
  `nama_penandatangan` varchar(200) NOT NULL,
  `jabatan_penandatangan` varchar(200) NOT NULL,
  `isi_piagam` text DEFAULT NULL,
  `file_piagam` varchar(255) DEFAULT NULL,
  `status` enum('draft','aktif') NOT NULL DEFAULT 'draft',
  `created_by` int(11) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: kategori_risiko
-- ============================================================
CREATE TABLE `kategori_risiko` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kode` varchar(10) NOT NULL,
  `nama` varchar(100) NOT NULL,
  `deskripsi` text DEFAULT NULL,
  `wajib` tinyint(1) NOT NULL DEFAULT 0,
  `aktif` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: kertas_kerja_penilaian (Main Risk Assessment Form)
-- ============================================================
CREATE TABLE `kertas_kerja_penilaian` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sasaran_id` int(11) NOT NULL,
  `upr_id` int(11) NOT NULL,
  `periode_id` int(11) NOT NULL,
  -- Header
  `no_urut` int(3) NOT NULL,
  -- Identifikasi Risiko
  `kode_risiko` varchar(30) NOT NULL,
  `pernyataan_risiko` text NOT NULL COMMENT 'Kolom 2',
  `kategori_risiko_id` int(11) DEFAULT NULL,
  `penyebab` text DEFAULT NULL COMMENT 'Kolom 4',
  `sumber_risiko` enum('Internal','Eksternal') DEFAULT NULL COMMENT 'Kolom 5',
  `c_uc` enum('C','UC') DEFAULT NULL COMMENT 'Controllable/Uncontrollable - Kolom 6',
  `dampak` text DEFAULT NULL COMMENT 'Kolom 7',
  -- Pengendalian yang ada
  `pengendalian_uraian` text DEFAULT NULL COMMENT 'Kolom 8',
  `pengendalian_efektif` tinyint(1) DEFAULT NULL COMMENT 'Kolom 9',
  `pengendalian_tidak_efektif` tinyint(1) DEFAULT NULL COMMENT 'Kolom 10',
  -- Analisis Risiko
  `p_awal` tinyint(1) DEFAULT NULL COMMENT 'Kemungkinan - Kolom 11 (1-5)',
  `d_awal` tinyint(1) DEFAULT NULL COMMENT 'Dampak - Kolom 12 (1-5)',
  `bobot_awal` decimal(5,2) DEFAULT NULL COMMENT 'Kolom 13',
  `nilai_awal` decimal(8,2) DEFAULT NULL COMMENT 'Kolom 14',
  `tingkat_risiko_awal` enum('Sangat Rendah','Rendah','Sedang','Tinggi','Sangat Tinggi') DEFAULT NULL COMMENT 'Kolom 15',
  -- Evaluasi Risiko
  `prioritas_risiko` tinyint(1) DEFAULT NULL COMMENT 'Kolom 16 (1-5)',
  `selera_risiko` enum('Dalam batas selera risiko','Diatas batas selera risiko') DEFAULT NULL COMMENT 'Kolom 17',
  -- Rencana Penanganan
  `pilihan_penanganan` enum('Menerima risiko','Mitigasi Risiko','Menghindari risiko','Berbagi risiko') DEFAULT NULL COMMENT 'Kolom 18',
  `uraian_penanganan` text DEFAULT NULL COMMENT 'Kolom 19',
  `jadwal_pelaksanaan` text DEFAULT NULL COMMENT 'Kolom 20',
  -- Target Penurunan Risiko
  `p_target` tinyint(1) DEFAULT NULL COMMENT 'Kolom 21',
  `d_target` tinyint(1) DEFAULT NULL COMMENT 'Kolom 22',
  `bobot_target` decimal(5,2) DEFAULT NULL COMMENT 'Kolom 23',
  `nilai_target` decimal(8,2) DEFAULT NULL COMMENT 'Kolom 24',
  `tingkat_risiko_target` enum('Sangat Rendah','Rendah','Sedang','Tinggi','Sangat Tinggi') DEFAULT NULL COMMENT 'Kolom 25',
  -- Metadata
  `status` enum('draft','submit','approved','rejected') NOT NULL DEFAULT 'draft',
  `catatan_reviewer` text DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_upr_periode` (`upr_id`, `periode_id`),
  KEY `idx_sasaran` (`sasaran_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: profil_risiko
-- ============================================================
CREATE TABLE `profil_risiko` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kkp_id` int(11) NOT NULL COMMENT 'kertas_kerja_penilaian id',
  `upr_id` int(11) NOT NULL,
  `periode_id` int(11) NOT NULL,
  `no_urut` int(3) NOT NULL,
  `penanggungjawab` varchar(200) DEFAULT NULL COMMENT 'Kolom 13',
  -- Diambil dari KKP, disimpan untuk profil
  `no_urut_kkp` int(3) DEFAULT NULL,
  `status` enum('draft','final') NOT NULL DEFAULT 'draft',
  `created_by` int(11) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_kkp` (`kkp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: kertas_kerja_pemantauan (Monitoring & Review)
-- ============================================================
CREATE TABLE `kertas_kerja_pemantauan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kkp_id` int(11) NOT NULL COMMENT 'Referensi ke kertas_kerja_penilaian',
  `upr_id` int(11) NOT NULL,
  `periode_id` int(11) NOT NULL,
  `triwulan` enum('TW1','TW2','TW3','TW4') NOT NULL,
  `tanggal_pemantauan` date NOT NULL,
  -- Hasil Pemantauan
  `p_hasil` tinyint(1) DEFAULT NULL COMMENT 'Kolom 12 - P hasil pemantauan',
  `d_hasil` tinyint(1) DEFAULT NULL COMMENT 'Kolom 13',
  `bobot_hasil` decimal(5,2) DEFAULT NULL COMMENT 'Kolom 14',
  `nilai_hasil` decimal(8,2) DEFAULT NULL COMMENT 'Kolom 15',
  `tingkat_risiko_hasil` enum('Sangat Rendah','Rendah','Sedang','Tinggi','Sangat Tinggi') DEFAULT NULL COMMENT 'Kolom 16',
  -- Simpulan
  `simpulan_tingkat` enum('Tidak ada penurunan tingkat risiko','Tingkat risiko mengalami penurunan','Tingkat risiko mengalami peningkatan') DEFAULT NULL COMMENT 'Kolom 17',
  `efektivitas` enum('Efektif','Tidak Efektif') DEFAULT NULL COMMENT 'Kolom 18',
  `catatan` text DEFAULT NULL,
  -- Metadata
  `status` enum('draft','submit','approved') NOT NULL DEFAULT 'draft',
  `created_by` int(11) NOT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_kkp_pemantauan` (`kkp_id`, `triwulan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: loss_event_database (LED)
-- ============================================================
CREATE TABLE `loss_event_database` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `upr_id` int(11) NOT NULL,
  `periode_id` int(11) NOT NULL,
  `tanggal_kejadian` date NOT NULL,
  `deskripsi_kejadian` text NOT NULL,
  `dampak_kejadian` text DEFAULT NULL,
  `mitigasi_dilakukan` text DEFAULT NULL,
  `kondisi_setelah_mitigasi` text DEFAULT NULL,
  `tingkat_dampak` enum('Tidak Signifikan','Kecil','Sedang','Besar','Katastropik') DEFAULT NULL,
  `status_lapor` enum('draft','dilaporkan') NOT NULL DEFAULT 'draft',
  `created_by` int(11) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: laporan_penerapan (Reports)
-- ============================================================
CREATE TABLE `laporan_penerapan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `upr_id` int(11) NOT NULL,
  `periode_id` int(11) NOT NULL,
  `jenis_laporan` enum('profil_risiko','penerapan_mr','pengawasan_mr') NOT NULL,
  `judul` varchar(300) NOT NULL,
  `isi_laporan` longtext DEFAULT NULL,
  `file_laporan` varchar(255) DEFAULT NULL,
  `status` enum('draft','submit','approved') NOT NULL DEFAULT 'draft',
  `created_by` int(11) NOT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: audit_log
-- ============================================================
CREATE TABLE `audit_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `aksi` varchar(100) NOT NULL,
  `modul` varchar(100) NOT NULL,
  `referensi_id` int(11) DEFAULT NULL,
  `keterangan` text DEFAULT NULL,
  `ip_address` varchar(50) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: notifikasi
-- ============================================================
CREATE TABLE `notifikasi` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `judul` varchar(200) NOT NULL,
  `pesan` text NOT NULL,
  `jenis` enum('info','warning','success','danger') NOT NULL DEFAULT 'info',
  `url` varchar(255) DEFAULT NULL,
  `dibaca` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: pengaturan (Settings)
-- ============================================================
CREATE TABLE `pengaturan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kunci` varchar(100) NOT NULL,
  `nilai` text DEFAULT NULL,
  `keterangan` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `kunci` (`kunci`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: bobot_matriks (Risk Weight Matrix)
-- ============================================================
CREATE TABLE `bobot_matriks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kemungkinan` tinyint(1) NOT NULL COMMENT '1-5',
  `dampak` tinyint(1) NOT NULL COMMENT '1-5',
  `bobot` decimal(5,2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `kemungkinan_dampak` (`kemungkinan`, `dampak`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- FOREIGN KEYS
-- ============================================================
ALTER TABLE `unit_pemilik_risiko`
  ADD CONSTRAINT `fk_upr_ketua` FOREIGN KEY (`ketua_upr_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_upr_sekretaris` FOREIGN KEY (`sekretaris_upr_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

ALTER TABLE `sasaran_organisasi`
  ADD CONSTRAINT `fk_so_upr` FOREIGN KEY (`upr_id`) REFERENCES `unit_pemilik_risiko` (`id`),
  ADD CONSTRAINT `fk_so_periode` FOREIGN KEY (`periode_id`) REFERENCES `periode_risiko` (`id`),
  ADD CONSTRAINT `fk_so_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

ALTER TABLE `kertas_kerja_penilaian`
  ADD CONSTRAINT `fk_kkp_sasaran` FOREIGN KEY (`sasaran_id`) REFERENCES `sasaran_organisasi` (`id`),
  ADD CONSTRAINT `fk_kkp_upr` FOREIGN KEY (`upr_id`) REFERENCES `unit_pemilik_risiko` (`id`),
  ADD CONSTRAINT `fk_kkp_periode` FOREIGN KEY (`periode_id`) REFERENCES `periode_risiko` (`id`),
  ADD CONSTRAINT `fk_kkp_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

ALTER TABLE `kertas_kerja_pemantauan`
  ADD CONSTRAINT `fk_kkpm_kkp` FOREIGN KEY (`kkp_id`) REFERENCES `kertas_kerja_penilaian` (`id`),
  ADD CONSTRAINT `fk_kkpm_upr` FOREIGN KEY (`upr_id`) REFERENCES `unit_pemilik_risiko` (`id`),
  ADD CONSTRAINT `fk_kkpm_periode` FOREIGN KEY (`periode_id`) REFERENCES `periode_risiko` (`id`),
  ADD CONSTRAINT `fk_kkpm_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

-- ============================================================
-- SEED DATA
-- ============================================================

-- Bobot Matriks 5x5
INSERT INTO `bobot_matriks` (`kemungkinan`, `dampak`, `bobot`) VALUES
(5,1,1.50),(5,2,1.40),(5,3,1.13),(5,4,1.15),(5,5,1.00),
(4,1,1.20),(4,2,1.19),(4,3,1.30),(4,4,1.16),(4,5,1.20),
(3,1,1.17),(3,2,1.42),(3,3,1.43),(3,4,1.46),(3,5,1.47),
(2,1,1.00),(2,2,1.80),(2,3,1.83),(2,4,1.90),(2,5,2.10),
(1,1,1.00),(1,2,1.50),(1,3,2.00),(1,4,3.00),(1,5,4.00);

-- Kategori Risiko
INSERT INTO `kategori_risiko` (`kode`,`nama`,`deskripsi`,`wajib`) VALUES
('KEB','Kebijakan','Risiko yang berkaitan dengan kebijakan internal dan eksternal',0),
('REP','Reputasi','Risiko yang berkaitan dengan kepercayaan publik terhadap organisasi',0),
('FRD','Fraud','Risiko yang berhubungan dengan tindakan kecurangan yang dilakukan dengan sengaja',1),
('LEG','Legal','Risiko yang berkaitan dengan tuntutan hukum atau persoalan hukum terhadap organisasi',0),
('PAT','Kepatuhan','Risiko yang berkaitan dengan kepatuhan terhadap ketentuan peraturan perundang-undangan',0),
('OPS','Operasional','Risiko yang berkaitan dengan pelaksanaan operasional organisasi, termasuk risiko SPBE',1);

-- Default Settings
INSERT INTO `pengaturan` (`kunci`,`nilai`,`keterangan`) VALUES
('nama_instansi','Balai Kekarantinaan Kesehatan Kelas I Samarinda','Nama instansi'),
('singkatan_instansi','BKK Kelas I Samarinda','Singkatan instansi'),
('tahun_aktif','2025','Tahun aktif aplikasi'),
('app_version','1.0.0','Versi aplikasi'),
('email_notifikasi','admin@bkk-samarinda.go.id','Email untuk notifikasi'),
('logo','Logo_BKK.png','Logo instansi');

-- Default Admin User (password: Admin@123)
INSERT INTO `users` (`username`,`password`,`nama_lengkap`,`nip`,`email`,`jabatan`,`role`,`status`) VALUES
('admin','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','Administrator','198001012010011001','admin@bkk-samarinda.go.id','Administrator Sistem','admin',1),
('pemilik1','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','Dr. Bambang Susilo','198505152009011003','bambang@bkk-samarinda.go.id','Kepala Balai','pemilik_risiko',1),
('pemeriksa1','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','Sri Wahyuni, S.KM','199001012015012002','sri@bkk-samarinda.go.id','Pemeriksa Manajemen Risiko','pemeriksa',1);

-- Default UPR
INSERT INTO `unit_pemilik_risiko` (`kode_upr`,`nama_upr`,`level`,`ketua_upr_id`,`sekretaris_upr_id`) VALUES
('UPR-BKK-01','Balai Kekarantinaan Kesehatan Kelas I Samarinda','UPR-T.II',2,3);

-- Default Periode
INSERT INTO `periode_risiko` (`tahun`,`semester`,`nama_periode`,`tanggal_mulai`,`tanggal_selesai`,`status`) VALUES
(2025,'1','Semester I Tahun 2025','2025-01-01','2025-06-30','aktif'),
(2025,'2','Semester II Tahun 2025','2025-07-01','2025-12-31','draft');