-- ==========================================================
-- UPGRADE SPJ KASBON MENJADI COST CONTROL PROYEK
-- Disesuaikan dengan database admin_pekerjaan:
-- pengajuan_kasbon: id_kasbon, id_karyawan, id_proyek, jumlah,
-- status_pengajuan, sisa_utang
-- ==========================================================

CREATE TABLE IF NOT EXISTS kasbon_spj (
    id_spj BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_kasbon INT NOT NULL,
    tanggal_spj DATE NOT NULL,
    total_spj DECIMAL(15,2) DEFAULT 0,
    keterangan TEXT NULL,
    status ENUM('Draft','Diajukan','Disetujui','Ditolak') DEFAULT 'Draft',
    created_by INT NULL,
    approved_by INT NULL,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_kasbon_spj_kasbon (id_kasbon),
    INDEX idx_kasbon_spj_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS kasbon_spj_items (
    id_item BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_spj BIGINT UNSIGNED NOT NULL,
    tanggal_biaya DATE NULL,
    jenis_biaya ENUM('Material','Jasa','Operasional','Transport','Akomodasi','Lainnya') DEFAULT 'Operasional',
    nama_barang VARCHAR(150) NULL,
    deskripsi TEXT NOT NULL,
    qty DECIMAL(15,2) DEFAULT 1,
    satuan VARCHAR(50) NULL,
    harga_satuan DECIMAL(15,2) DEFAULT 0,
    nominal DECIMAL(15,2) DEFAULT 0,
    id_proyek BIGINT UNSIGNED NULL,
    id_mitra BIGINT UNSIGNED NULL,
    id_spk BIGINT UNSIGNED NULL,
    id_progress BIGINT UNSIGNED NULL,
    file_bukti VARCHAR(255) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_spj_items_spj (id_spj),
    INDEX idx_spj_items_proyek (id_proyek),
    INDEX idx_spj_items_jenis (jenis_biaya)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Jika tabel kasbon_spj_items sudah pernah dibuat sederhana, jalankan ALTER ini.
-- Untuk MySQL/MariaDB modern yang mendukung IF NOT EXISTS:
ALTER TABLE kasbon_spj_items
    ADD COLUMN IF NOT EXISTS jenis_biaya ENUM('Material','Jasa','Operasional','Transport','Akomodasi','Lainnya') DEFAULT 'Operasional' AFTER tanggal_biaya,
    ADD COLUMN IF NOT EXISTS nama_barang VARCHAR(150) NULL AFTER jenis_biaya,
    ADD COLUMN IF NOT EXISTS qty DECIMAL(15,2) DEFAULT 1 AFTER deskripsi,
    ADD COLUMN IF NOT EXISTS satuan VARCHAR(50) NULL AFTER qty,
    ADD COLUMN IF NOT EXISTS harga_satuan DECIMAL(15,2) DEFAULT 0 AFTER satuan,
    ADD COLUMN IF NOT EXISTS id_proyek BIGINT UNSIGNED NULL AFTER nominal,
    ADD COLUMN IF NOT EXISTS id_mitra BIGINT UNSIGNED NULL AFTER id_proyek,
    ADD COLUMN IF NOT EXISTS id_spk BIGINT UNSIGNED NULL AFTER id_mitra,
    ADD COLUMN IF NOT EXISTS id_progress BIGINT UNSIGNED NULL AFTER id_spk;

-- View ringkasan biaya proyek dari SPJ yang disetujui.
CREATE OR REPLACE VIEW v_cost_control_spj_proyek AS
SELECT
    pk.id_proyek,
    ksi.jenis_biaya,
    SUM(ksi.nominal) AS total_realisasi
FROM kasbon_spj_items ksi
INNER JOIN kasbon_spj ks ON ks.id_spj = ksi.id_spj
INNER JOIN pengajuan_kasbon pk ON pk.id_kasbon = ks.id_kasbon
WHERE ks.status = 'Disetujui'
  AND pk.id_proyek IS NOT NULL
GROUP BY pk.id_proyek, ksi.jenis_biaya;