-- ===================================================================
-- DATABASE: SI-PERKASA BINMAS
-- Deskripsi : Sistem Informasi Digitalisasi Perwabku 
--             (Satker Ditbinmas Polda Sulteng)
-- Engine    : MySQL / MariaDB (InnoDB untuk referential integrity)
-- Charset   : UTF-8 (utf8mb4) untuk dukungan Unicode penuh
-- ===================================================================

-- -----------------------------------------------------------
-- 1. Tabel: roles
--    Menyimpan daftar peran (RBAC) dalam sistem.
--    Relasi: Diacu oleh tabel `users` (one-to-many).
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `roles` (
    `id`        INT             NOT NULL AUTO_INCREMENT,
    `nama_role` VARCHAR(50)     NOT NULL,
    `deskripsi` VARCHAR(255)    DEFAULT NULL,
    `created_at` TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_nama_role` (`nama_role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 2. Tabel: users
--    Menyimpan data pengguna sistem.
--    - nrp: Nomor Registrasi Pokok, digunakan sebagai username.
--    - password_hash: Disimpan menggunakan password_hash() Bcrypt.
--    - role_id: Foreign Key ke tabel roles.
--    - is_active: Flag untuk menonaktifkan akun (0=nonaktif, 1=aktif).
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id`             INT             NOT NULL AUTO_INCREMENT,
    `nama_lengkap`   VARCHAR(100)    NOT NULL,
    `nrp`            VARCHAR(30)     NOT NULL,
    `password_hash`  VARCHAR(255)    NOT NULL,
    `role_id`        INT             NOT NULL,
    `pangkat`        VARCHAR(50)     DEFAULT NULL,
    `jabatan`        VARCHAR(100)    DEFAULT NULL,
    `nomor_wa`       VARCHAR(20)     DEFAULT NULL COMMENT 'Nomor WhatsApp untuk notifikasi',
    `foto_profile`   VARCHAR(255)    DEFAULT NULL,
    `is_active`      TINYINT(1)      NOT NULL DEFAULT 1,
    `terakhir_login` DATETIME        DEFAULT NULL,
    `created_at`     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_nrp` (`nrp`),
    KEY `idx_role_id` (`role_id`),
    KEY `idx_is_active` (`is_active`),
    CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) 
        REFERENCES `roles` (`id`)
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 3. Tabel: dokumen_perwabku
--    Menyimpan data dokumen Perwabku yang diinput oleh Bamin.
--    Alur Status (Workflow):
--      'Menunggu'    → Dokumen baru dari Bamin, antre validasi
--      'Review Kaur' → Disetujui Verifikator, diteruskan ke Kaur Keu
--      'Koreksi'     → Ditolak Verifikator, perlu perbaikan
--      'Ditolak'     → Ditolak final (tidak bisa diperbaiki)
--      'Selesai'     → TTE oleh Kaur Keu, proses selesai
--    Relasi:
--      - user_id_bamin → users.id (pembuat dokumen)
--      - verified_by    → users.id (verifikator staf)
--      - ttd_by         → users.id (kaur keu yang melakukan TTE)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `dokumen_perwabku` (
    `id`                INT             NOT NULL AUTO_INCREMENT,
    `nomor_dokumen`     VARCHAR(100)    NOT NULL,
    `user_id_bamin`     INT             NOT NULL COMMENT 'Pembuat dokumen (Bamin)',
    `nama_kegiatan`     VARCHAR(255)    NOT NULL,
    `nominal_anggaran`  DECIMAL(15,2)   NOT NULL DEFAULT 0.00,
    `file_path`         VARCHAR(255)    NOT NULL COMMENT 'Path relatif file PDF di storage',
    `file_mime`         VARCHAR(100)    DEFAULT NULL COMMENT 'MIME type hasil validasi upload',
    `file_size`         INT             DEFAULT NULL COMMENT 'Ukuran file dalam bytes',
    `status`            ENUM('Menunggu','Review Kaur','Koreksi','Ditolak','Selesai') 
                        NOT NULL DEFAULT 'Menunggu',
    `verified_by`       INT             DEFAULT NULL COMMENT 'Verifikator Staf',
    `verified_at`       DATETIME        DEFAULT NULL,
    `ttd_by`            INT             DEFAULT NULL COMMENT 'Kaur Keu (TTE)',
    `ttd_at`            DATETIME        DEFAULT NULL,
    `catatan_verifikator` TEXT          DEFAULT NULL,
    `created_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_nomor_dokumen` (`nomor_dokumen`),
    KEY `idx_status` (`status`),
    KEY `idx_user_id_bamin` (`user_id_bamin`),
    KEY `idx_verified_by` (`verified_by`),
    KEY `idx_ttd_by` (`ttd_by`),
    KEY `idx_created_at` (`created_at`),
    CONSTRAINT `fk_dokumen_bamin` FOREIGN KEY (`user_id_bamin`) 
        REFERENCES `users` (`id`)
        ON UPDATE CASCADE,
    CONSTRAINT `fk_dokumen_verifikator` FOREIGN KEY (`verified_by`) 
        REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT `fk_dokumen_ttd` FOREIGN KEY (`ttd_by`) 
        REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 4. Tabel: checklist_result
--    Menyimpan hasil validasi per-item checklist oleh Verifikator.
--    Satu dokumen bisa memiliki banyak item checklist.
--    Relasi: dokumen_id → dokumen_perwabku.id (CASCADE hapus)
--             validated_by → users.id
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `checklist_result` (
    `id`              INT             NOT NULL AUTO_INCREMENT,
    `dokumen_id`      INT             NOT NULL,
    `item_nama`       VARCHAR(100)    NOT NULL COMMENT 'Nama item: Sprin, RPD, Kuitansi, GPS, dll',
    `is_valid`        TINYINT(1)      NOT NULL DEFAULT 0 COMMENT '1=Valid, 0=Tidak Valid',
    `catatan_koreksi` TEXT            DEFAULT NULL,
    `validated_by`    INT             NOT NULL,
    `validated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_at`      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_dokumen_id` (`dokumen_id`),
    KEY `idx_validated_by` (`validated_by`),
    KEY `idx_item_dokumen` (`dokumen_id`, `item_nama`),
    CONSTRAINT `fk_checklist_dokumen` FOREIGN KEY (`dokumen_id`) 
        REFERENCES `dokumen_perwabku` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_checklist_validator` FOREIGN KEY (`validated_by`) 
        REFERENCES `users` (`id`)
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 5. Tabel: audit_logs
--    Menyimpan jejak audit (log) semua aktivitas penting.
--    Wajib untuk akuntabilitas sistem instansi.
--    Kolom id menggunakan BIGINT karena bisa sangat banyak.
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id`          BIGINT          NOT NULL AUTO_INCREMENT,
    `user_id`     INT             DEFAULT NULL COMMENT 'NULL jika aksi sistem',
    `action`      VARCHAR(50)     NOT NULL COMMENT 'Kategori aksi: LOGIN, UPLOAD, VALIDASI, APPROVE, REJECT, TTE, DLL',
    `description` TEXT            DEFAULT NULL,
    `ip_address`  VARCHAR(45)     DEFAULT NULL COMMENT 'Mendukung IPv4 dan IPv6',
    `user_agent`  VARCHAR(500)    DEFAULT NULL,
    `created_at`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_action` (`action`),
    KEY `idx_created_at` (`created_at`),
    CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) 
        REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 6. Tabel: notifikasi_wa
--    Antrean pengiriman notifikasi WhatsApp.
--    Sistem akan memproses antrean secara background (cron).
--    Status: Pending → Sent / Failed
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `notifikasi_wa` (
    `id`            INT             NOT NULL AUTO_INCREMENT,
    `dokumen_id`    INT             DEFAULT NULL,
    `nomor_tujuan`  VARCHAR(20)     NOT NULL,
    `pesan`         TEXT            NOT NULL,
    `status_kirim`  ENUM('Pending','Sent','Failed') NOT NULL DEFAULT 'Pending',
    `response_api`  TEXT            DEFAULT NULL COMMENT 'Response mentah dari API Gateway WA',
    `sent_at`       DATETIME        DEFAULT NULL,
    `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_status_kirim` (`status_kirim`),
    KEY `idx_dokumen_id` (`dokumen_id`),
    CONSTRAINT `fk_notif_dokumen` FOREIGN KEY (`dokumen_id`) 
        REFERENCES `dokumen_perwabku` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===================================================================
-- DATA AWAL (SEEDER)
-- ===================================================================

-- -----------------------------------------------------------
-- Seed: roles
-- 5 role sesuai dengan spesifikasi RBAC di Master Plan.
-- PENJELASAN ROLE:
-- 1. Admin       : Memiliki akses penuh ke semua fitur, termasuk manajemen user & data master
-- 2. Bamin       : (Bagian Administrasi) - Bertugas menginput dokumen Perwabku & upload file PDF
-- 3. Verifikator Staf : Petugas yang memvalidasi kelengkapan berkas & checklist dokumen
-- 4. Kaur Keu    : (Kepala Urusan Keuangan) - Melakukan Tanda Tangan Elektronik (TTE)
-- 5. Pimpinan    : Pejabat/pimpinan yang memonitor dashboard, anggaran, & laporan eksekutif
-- -----------------------------------------------------------
INSERT INTO `roles` (`id`, `nama_role`, `deskripsi`) VALUES
(1, 'Admin',           'Manajemen sistem: kelola user, data master, dan seluruh konfigurasi aplikasi'),
(2, 'Bamin',           'Bagian Administrasi - Input data kegiatan dan upload dokumen PDF Perwabku'),
(3, 'Verifikator Staf','Validasi kelengkapan berkas dokumen menggunakan smart checklist'),
(4, 'Kaur Keu',        'Kepala Urusan Keuangan - TTE (Tanda Tangan Elektronik) finalisasi dokumen'),
(5, 'Pimpinan',        'Monitoring real-time dashboard, serapan anggaran, dan laporan eksekutif');

-- -----------------------------------------------------------
-- Seed: users
-- NRP menggunakan format jelas per role (bukan angka acak)
-- Password menggunakan bcrypt hash, AMAN.
-- -----------------------------------------------------------
INSERT INTO `users` (`id`, `nama_lengkap`, `nrp`, `password_hash`, `role_id`, `pangkat`, `jabatan`, `nomor_wa`, `is_active`) VALUES
(1, 'Admin Sistem',        'ADM001', '$2y$10$Q1E6nfKPEgVK03WLrqSDWOLLQaa5rhXb4nfAYhwvVj3Z47AH1aJkO', 1, NULL,     'Administrator',     NULL, 1),
(2, 'Bamin Operasional',   'BAM001', '$2y$10$G1PzZgzD8Cldzcs0qf4z3OwtGGAW5xSJM4eJz9lyKwiacCM4aJDjC', 2, 'Bripka', 'Bamin / Pembantu Administrasi', '081234567890', 1),
(3, 'Verifikator Staf',    'VRF001', '$2y$10$XC1MNsuXFslJwcKkYUmORemVwkv4zG2sGma/ZNJLugrYwbJ4HLRGa', 3, 'Aiptu',  'Staf Verifikator',  '081234567891', 1),
(4, 'Kaur Keuangan',       'KEU001', '$2y$10$qYtVJNEr0JYQT3OI/q/KOufCuQGEeW/1QDm7PZd3WL2FV7hKky5zi', 4, 'Ipda',   'Kepala Urusan Keuangan', '081234567892', 1),
(5, 'Pimpinan Satker',     'PIM001', '$2y$10$Qy/7pzHRGVEbyBZ94kV.YOsNLBbVP8cIPqhqCPkLVrVSB5in5Rdyq', 5, 'Kompol', 'Kasubdit Binmas',  '081234567893', 1);

-- ===================================================================
-- ╔══════════════════════════════════════════════════════════════╗
-- ║              KREDENSIAL LOGIN (DEFAULT)                     ║
-- ╠══════════════════════════════════════════════════════════════╣
-- ║  ROLE              │  NRP      │  PASSWORD                  ║
-- ║────────────────────┼───────────┼────────────────────────────║
-- ║  Admin             │  ADM001   │  Admin@123                 ║
-- ║  Bamin             │  BAM001   │  Bamin@123                 ║
-- ║  Verifikator Staf  │  VRF001   │  Verif@123                 ║
-- ║  Kaur Keu          │  KEU001   │  Keu@123                   ║
-- ║  Pimpinan          │  PIM001   │  Pimpinan@123              ║
-- ╚══════════════════════════════════════════════════════════════╝
-- ===================================================================
-- ===================================================================
-- SI-PERKASA BINMAS - Data Master Migration
-- ===================================================================
-- Menambahkan tabel untuk Data Master yang diperlukan Admin.
-- Relasi: satker → pagu_anggaran (one-to-many)
--          jenis_kegiatan → kategorisasi standar
-- ===================================================================

-- -----------------------------------------------------------
-- 7. Tabel: satker
--    Daftar Satuan Kerja di Polda Sulteng
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `satker` (
    `id`          INT             NOT NULL AUTO_INCREMENT,
    `kode_satker` VARCHAR(20)     NOT NULL COMMENT 'Kode unit, misal: BINMAS-01',
    `nama_satker` VARCHAR(200)    NOT NULL COMMENT 'Nama Satuan Kerja',
    `singkatan`   VARCHAR(50)     DEFAULT NULL,
    `alamat`      TEXT            DEFAULT NULL,
    `is_active`   TINYINT(1)      NOT NULL DEFAULT 1,
    `created_at`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_kode_satker` (`kode_satker`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 8. Tabel: tahun_anggaran
--    Tahun buku anggaran. Satu tahun bisa aktif atau tidak.
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tahun_anggaran` (
    `id`          INT             NOT NULL AUTO_INCREMENT,
    `tahun`       YEAR            NOT NULL,
    `is_active`   TINYINT(1)      NOT NULL DEFAULT 0 COMMENT 'Hanya 1 tahun yang aktif',
    `keterangan`  VARCHAR(255)    DEFAULT NULL,
    `created_at`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_tahun` (`tahun`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 9. Tabel: jenis_kegiatan
--    Standarisasi jenis kegiatan untuk dropdown
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `jenis_kegiatan` (
    `id`            INT             NOT NULL AUTO_INCREMENT,
    `kode_kegiatan` VARCHAR(20)     NOT NULL,
    `nama_kegiatan` VARCHAR(255)    NOT NULL,
    `deskripsi`     TEXT            DEFAULT NULL,
    `is_active`     TINYINT(1)      NOT NULL DEFAULT 1,
    `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_kode_kegiatan` (`kode_kegiatan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 10. Tabel: pagu_anggaran
--     Pagu anggaran per satker per tahun
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `pagu_anggaran` (
    `id`              INT             NOT NULL AUTO_INCREMENT,
    `satker_id`       INT             NOT NULL,
    `tahun_anggaran_id` INT           NOT NULL,
    `jumlah_pagu`     DECIMAL(15,2)   NOT NULL DEFAULT 0.00 COMMENT 'Total pagu anggaran',
    `created_at`      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_satker_tahun` (`satker_id`, `tahun_anggaran_id`),
    KEY `idx_tahun_anggaran_id` (`tahun_anggaran_id`),
    CONSTRAINT `fk_pagu_satker` FOREIGN KEY (`satker_id`) 
        REFERENCES `satker` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_pagu_tahun` FOREIGN KEY (`tahun_anggaran_id`) 
        REFERENCES `tahun_anggaran` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- Tahun Anggaran
INSERT INTO `tahun_anggaran` (`tahun`, `is_active`, `keterangan`) VALUES
(2026, 1, 'Tahun Anggaran Berjalan');

-- Satker
INSERT INTO `satker` (`kode_satker`, `nama_satker`, `singkatan`) VALUES
('BINMAS-01', 'Subdit BINMAS Polda Sulteng', 'BINMAS'),
('BINMAS-02', 'Subdit Bhabinkamtibmas', 'BHABIN'),
('BINMAS-03', 'Subdit Pembinaan Masyarakat', 'BINMAS');

-- Jenis Kegiatan
INSERT INTO `jenis_kegiatan` (`kode_kegiatan`, `nama_kegiatan`, `deskripsi`) VALUES
('PATROLI',   'Patroli Dialogis',          'Patroli dan dialog dengan masyarakat'),
('SOSIAL',    'Sosialisasi Kamtibmas',      'Sosialisasi keamanan dan ketertiban masyarakat'),
('BINLAT',    'Pembinaan dan Pelatihan',     'Pembinaan dan pelatihan masyarakat'),
('BANSOS',    'Bantuan Sosial',             'Penyaluran bantuan sosial ke masyarakat'),
('POSKAMLING','Pembentukan Pos Kamling',    'Pembentukan dan pembinaan pos kamling');

-- Pagu Anggaran (contoh)
INSERT INTO `pagu_anggaran` (`satker_id`, `tahun_anggaran_id`, `jumlah_pagu`) VALUES
(1, 1, 500000000),
(2, 1, 350000000),
(3, 1, 300000000);

-- ===================================================================
-- END OF MIGRATION
-- ===================================================================