-- SPJ Komite MySQL Database Schema
-- Database: db_spjkomite

-- Tabel untuk kode kegiatan
CREATE TABLE IF NOT EXISTS kode_kegiatan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(100) NOT NULL UNIQUE,
    uraian TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabel untuk pengaturan/manajemen
CREATE TABLE IF NOT EXISTS pengaturan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_ketua_komite VARCHAR(255),
    bendahara VARCHAR(255),
    nama_tim_teknis_a VARCHAR(255),
    jabatan_tim_teknis_a VARCHAR(255),
    nama_tim_teknis_b VARCHAR(255),
    jabatan_tim_teknis_b VARCHAR(255),
    nama_tim_teknis_c VARCHAR(255),
    jabatan_tim_teknis_c VARCHAR(255),
    tahun_anggaran VARCHAR(10),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabel untuk RKS (Rencana Kerja dan Syarat)
CREATE TABLE IF NOT EXISTS rks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pekerjaan TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Counter untuk nomor dokumen
CREATE TABLE IF NOT EXISTS nomor_counter (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tahun_anggaran VARCHAR(10) NOT NULL,
    counter INT DEFAULT 0,
    UNIQUE KEY unique_year (tahun_anggaran)
);

-- Tabel untuk pesanan (surat pesanan)
CREATE TABLE IF NOT EXISTS pesanan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tanggal_pesanan DATE NOT NULL,
    nomor_pesanan VARCHAR(255) NOT NULL,
    penyedia VARCHAR(255) NOT NULL,
    nama_jenis_barang VARCHAR(255) NOT NULL,
    spesifikasi_teknis VARCHAR(500),
    volume DECIMAL(10,2) NOT NULL,
    satuan VARCHAR(50) NOT NULL,
    keterangan TEXT,
    waktu_pengerjaan INT NOT NULL,
    batas_tanggal_pengerjaan DATE NOT NULL,
    penerima_pesanan VARCHAR(255),
    yang_memesan VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_nomor_pesanan (nomor_pesanan)
);

-- Tabel untuk BAP (Berita Acara Pemeriksaan)
CREATE TABLE IF NOT EXISTS bap (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nomor_bap VARCHAR(255) NOT NULL UNIQUE,
    pekerjaan TEXT NOT NULL,
    tanggal_pemeriksaan DATE NOT NULL,
    nama_tim_teknis_a VARCHAR(255),
    nama_tim_teknis_b VARCHAR(255),
    nama_tim_teknis_c VARCHAR(255),
    jabatan_tim_teknis_a VARCHAR(255),
    jabatan_tim_teknis_b VARCHAR(255),
    jabatan_tim_teknis_c VARCHAR(255),
    tujuan TEXT,
    alamat_penyedia TEXT,
    ketua_komite VARCHAR(255),
    nomor_pesanan VARCHAR(255),
    kode_kegiatan VARCHAR(100),
    kode_sub_kegiatan VARCHAR(100),
    kode_rekening VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_nomor_bap (nomor_bap),
    INDEX idx_nomor_pesanan (nomor_pesanan)
);

-- Tabel untuk BAST (Berita Acara Serah Terima)
CREATE TABLE IF NOT EXISTS bast (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nomor_bast VARCHAR(255) NOT NULL UNIQUE,
    tanggal_bast DATE NOT NULL,
    nomor_bap VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_nomor_bast (nomor_bast),
    INDEX idx_nomor_bap (nomor_bap)
);

-- Tabel untuk Nota
CREATE TABLE IF NOT EXISTS nota (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nomor INT NOT NULL,
    volume DECIMAL(10,2) NOT NULL,
    satuan VARCHAR(50) NOT NULL,
    nama_barang VARCHAR(255) NOT NULL,
    harga_satuan DECIMAL(15,2) NOT NULL,
    jumlah DECIMAL(15,2) NOT NULL,
    nomor_bap VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_nomor_bap (nomor_bap)
);

-- Tabel untuk Foto Dokumentasi
CREATE TABLE IF NOT EXISTS foto (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nomor_bap VARCHAR(255) NOT NULL,
    related_id VARCHAR(255),
    filename VARCHAR(255),
    url LONGTEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_nomor_bap (nomor_bap)
);

-- Tabel untuk Aset Aplikasi (Logo dan KOP)
CREATE TABLE IF NOT EXISTS aset_aplikasi (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tipe VARCHAR(50) NOT NULL UNIQUE,
    data_url LONGTEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default aset_aplikasi rows
INSERT INTO aset_aplikasi (tipe, data_url) 
VALUES ('logo', NULL), ('kop', NULL)
ON DUPLICATE KEY UPDATE tipe=tipe;

-- Tabel untuk Penyedia (Supplier) dengan KOP
CREATE TABLE IF NOT EXISTS penyedia (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(255) NOT NULL UNIQUE,
    alamat TEXT,
    kop_url LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabel untuk SPJ Lomba (Kegiatan Lomba - Pembayaran Non-Tunai)
CREATE TABLE IF NOT EXISTS spj_lomba (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nomor_spj VARCHAR(255) NOT NULL UNIQUE,
    tanggal DATE NOT NULL,
    nama_kegiatan VARCHAR(500) NOT NULL,
    jenis_pembayaran VARCHAR(100) NOT NULL,
    penerima VARCHAR(255) NOT NULL,
    nama_bank VARCHAR(100) NOT NULL,
    nomor_rekening VARCHAR(50) NOT NULL,
    atas_nama VARCHAR(255) NOT NULL,
    nominal DECIMAL(15,2) NOT NULL,
    keterangan TEXT,
    bukti_transfer LONGTEXT,
    kode_kegiatan VARCHAR(100),
    kode_sub_kegiatan VARCHAR(100),
    kode_rekening VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_nomor_spj (nomor_spj),
    INDEX idx_tanggal (tanggal)
);

-- Insert default pengaturan row
INSERT INTO pengaturan (id, nama_ketua_komite, bendahara, tahun_anggaran) 
VALUES (1, '', '', '2026')
ON DUPLICATE KEY UPDATE id=id;

-- Insert default counter for current year
INSERT INTO nomor_counter (tahun_anggaran, counter) 
VALUES ('2026', 0)
ON DUPLICATE KEY UPDATE tahun_anggaran=tahun_anggaran;