-- =========================
-- USERS
-- =========================

CREATE TABLE rkas_users (
    id_user INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(75) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','bendahara','kepsek') DEFAULT 'admin',
    status ENUM('aktif','nonaktif') DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO rkas_users (nama, username, password, role) VALUES
('Administrator','admin','$2y$10$V8PtluHI1/dXD10hfAfOAudCL3XtRqAkiPyF5QcMRzNXiRCuLNe/a','admin');

-- =========================
-- TAHUN ANGGARAN
-- =========================

CREATE TABLE rkas_tahun_anggaran (
    id_tahun INT AUTO_INCREMENT PRIMARY KEY,
    tahun YEAR NOT NULL UNIQUE,
    status ENUM('aktif','nonaktif') DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP
);

-- =========================
-- SUMBER DANA
-- =========================

CREATE TABLE rkas_sumber_dana (
    id_sumber INT AUTO_INCREMENT PRIMARY KEY,
    id_tahun INT NOT NULL,
    nama_sumber VARCHAR(100) NOT NULL,
    nominal DECIMAL(15,2),
    keterangan TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE (id_tahun, nama_sumber),
    FOREIGN KEY (id_tahun) REFERENCES rkas_tahun_anggaran(id_tahun) ON DELETE CASCADE
);

-- =========================
-- KEGIATAN
-- =========================

CREATE TABLE rkas_kegiatan (
    id_kegiatan INT AUTO_INCREMENT PRIMARY KEY,
    id_tahun INT NOT NULL,
    id_sumber INT,
    kode_kegiatan VARCHAR(50),
    nama_kegiatan VARCHAR(200) NOT NULL,
    keterangan TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE (id_tahun, kode_kegiatan),
    FOREIGN KEY (id_tahun) REFERENCES rkas_tahun_anggaran(id_tahun) ON DELETE CASCADE,
    FOREIGN KEY (id_sumber) REFERENCES rkas_sumber_dana(id_sumber) ON DELETE CASCADE
);

-- =========================
-- RINCIAN KEGIATAN
-- =========================

CREATE TABLE rkas_rincian_kegiatan (
    id_rincian INT AUTO_INCREMENT PRIMARY KEY,
    id_kegiatan INT NOT NULL,
    kode_rincian VARCHAR(50),
    nama_rincian VARCHAR(200) NOT NULL,
    satuan VARCHAR(50),
    jumlah INT,
    harga_satuan DECIMAL(15,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_kegiatan) REFERENCES rkas_kegiatan(id_kegiatan) ON DELETE CASCADE
);

-- =========================
-- REALISASI
-- =========================

CREATE TABLE rkas_realisasi (
    id_realisasi INT AUTO_INCREMENT PRIMARY KEY,
    id_rincian INT NOT NULL,
    tanggal DATE NOT NULL,
    nominal DECIMAL(15,2) NOT NULL,
    keterangan TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_rincian) REFERENCES rkas_rincian_kegiatan(id_rincian) ON DELETE CASCADE
);

CREATE INDEX idx_kegiatan_tahun ON rkas_kegiatan(id_tahun);
CREATE INDEX idx_kegiatan_sumber ON rkas_kegiatan(id_sumber);
CREATE INDEX idx_rincian_kegiatan  ON rkas_rincian_kegiatan(id_kegiatan);
CREATE INDEX idx_realisasi_rincian ON rkas_realisasi(id_rincian);
CREATE INDEX idx_sumber_tahun ON rkas_sumber_dana(id_tahun);