-- =====================================================================
-- DATABASE: keuangan_sekolah
-- Aplikasi Pencatatan Keuangan Sekolah (Umum + SPP)
-- =====================================================================

CREATE DATABASE IF NOT EXISTS keuangan_sekolah CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE keuangan_sekolah;

-- =====================================================================
-- 1. USERS
-- =====================================================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    nama VARCHAR(100) NOT NULL,
    role ENUM('admin','bendahara','kepala_sekolah') NOT NULL DEFAULT 'bendahara',
    foto VARCHAR(255) DEFAULT NULL,
    status ENUM('aktif','nonaktif') NOT NULL DEFAULT 'aktif',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Default admin (password: admin123)
INSERT INTO users (username, password, nama, role) VALUES
('admin', '$2y$10$nx6bV99d7rXaqv9sf77/du3GjfXT4RiaoO2Eqh.e53ASJOXXoGm/a', 'Administrator', 'admin');

-- =====================================================================
-- 2. PENGATURAN (Profil Sekolah)
-- =====================================================================
CREATE TABLE pengaturan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_sekolah VARCHAR(150) NOT NULL DEFAULT 'Nama Sekolah',
    npsn VARCHAR(20) DEFAULT NULL,
    alamat TEXT,
    desa_kecamatan VARCHAR(150) DEFAULT NULL,
    kabupaten VARCHAR(100) DEFAULT NULL,
    provinsi VARCHAR(100) DEFAULT NULL,
    kode_pos VARCHAR(10) DEFAULT NULL,
    telp VARCHAR(30) DEFAULT NULL,
    email VARCHAR(100) DEFAULT NULL,
    website VARCHAR(100) DEFAULT NULL,
    logo VARCHAR(255) DEFAULT NULL,
    kepala_sekolah VARCHAR(100) DEFAULT NULL,
    nip_kepala VARCHAR(30) DEFAULT NULL,
    bendahara VARCHAR(100) DEFAULT NULL,
    nip_bendahara VARCHAR(30) DEFAULT NULL,
    tahun_ajaran_aktif INT DEFAULT NULL
) ENGINE=InnoDB;

INSERT INTO pengaturan (nama_sekolah) VALUES ('Madrasah Aliyah As-Syafi\'iyah 02 Kota Bekasi');

-- =====================================================================
-- 3. TAHUN AJARAN
-- =====================================================================
CREATE TABLE tahun_ajaran (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(20) NOT NULL,            -- contoh: 2025/2026
    semester ENUM('Ganjil','Genap') DEFAULT 'Ganjil',
    status_aktif TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO tahun_ajaran (nama, semester, status_aktif) VALUES ('2025/2026','Ganjil',1);

-- =====================================================================
-- 4. TINGKAT & KELAS
-- =====================================================================
CREATE TABLE tingkat (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(10) NOT NULL              -- X, XI, XII
) ENGINE=InnoDB;

INSERT INTO tingkat (nama) VALUES ('X'),('XI'),('XII');

CREATE TABLE kelas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tingkat_id INT NOT NULL,
    nama VARCHAR(20) NOT NULL,             -- IPA 1, IPS 2, dst
    FOREIGN KEY (tingkat_id) REFERENCES tingkat(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =====================================================================
-- 5. SISWA
-- =====================================================================
CREATE TABLE siswa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nis VARCHAR(20) NOT NULL UNIQUE,
    nisn VARCHAR(20) DEFAULT NULL,
    nama VARCHAR(100) NOT NULL,
    kelas_id INT DEFAULT NULL,
    jenis_kelamin ENUM('L','P') DEFAULT 'L',
    nama_ortu VARCHAR(100) DEFAULT NULL,
    no_hp_ortu VARCHAR(20) DEFAULT NULL,
    nominal_spp DECIMAL(12,2) NOT NULL DEFAULT 0,   -- nominal SPP per bulan (bisa beda per siswa)
    status ENUM('aktif','lulus','pindah','nonaktif') NOT NULL DEFAULT 'aktif',
    foto VARCHAR(255) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =====================================================================
-- 6. AKUN KAS / BANK (sumber dana)
-- =====================================================================
CREATE TABLE akun_kas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_akun VARCHAR(100) NOT NULL,        -- Kas Tunai, Bank BRI, dst
    jenis ENUM('kas','bank') NOT NULL DEFAULT 'kas',
    no_rekening VARCHAR(50) DEFAULT NULL,
    saldo_awal DECIMAL(15,2) NOT NULL DEFAULT 0,
    keterangan VARCHAR(255) DEFAULT NULL,
    status ENUM('aktif','nonaktif') NOT NULL DEFAULT 'aktif',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO akun_kas (nama_akun, jenis, saldo_awal) VALUES ('Kas Tunai Sekolah','kas',0);

-- =====================================================================
-- 7. KATEGORI (Akun Buku Besar Sederhana)
-- =====================================================================
CREATE TABLE kategori (
    id INT AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(20) DEFAULT NULL,
    nama VARCHAR(100) NOT NULL,
    tipe ENUM('pemasukan','pengeluaran') NOT NULL,
    kelompok ENUM('umum','spp') NOT NULL DEFAULT 'umum',
    keterangan VARCHAR(255) DEFAULT NULL,
    status ENUM('aktif','nonaktif') NOT NULL DEFAULT 'aktif'
) ENGINE=InnoDB;

INSERT INTO kategori (kode, nama, tipe, kelompok) VALUES
('PM-SPP','Pembayaran SPP','pemasukan','spp'),
('PM-DON','Donasi / Sumbangan','pemasukan','umum'),
('PM-DAN','Dana BOS','pemasukan','umum'),
('PM-LAIN','Pemasukan Lain-lain','pemasukan','umum'),
('PK-GAJI','Gaji & Honor Guru/Staf','pengeluaran','umum'),
('PK-OPS','Operasional Sekolah','pengeluaran','umum'),
('PK-LIST','Listrik, Air & Internet','pengeluaran','umum'),
('PK-ATK','ATK & Sarana Belajar','pengeluaran','umum'),
('PK-PEM','Pemeliharaan Gedung/Sarana','pengeluaran','umum'),
('PK-KEG','Kegiatan Sekolah','pengeluaran','umum'),
('PK-LAIN','Pengeluaran Lain-lain','pengeluaran','umum');

-- =====================================================================
-- 8. TAGIHAN SPP
-- =====================================================================
CREATE TABLE tagihan_spp (
    id INT AUTO_INCREMENT PRIMARY KEY,
    siswa_id INT NOT NULL,
    tahun_ajaran_id INT NOT NULL,
    bulan TINYINT NOT NULL,            -- 1-12
    tahun SMALLINT NOT NULL,
    nominal DECIMAL(12,2) NOT NULL DEFAULT 0,
    jumlah_dibayar DECIMAL(12,2) NOT NULL DEFAULT 0,
    status ENUM('belum_lunas','lunas') NOT NULL DEFAULT 'belum_lunas',
    jatuh_tempo DATE DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_tagihan (siswa_id, tahun_ajaran_id, bulan, tahun),
    FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
    FOREIGN KEY (tahun_ajaran_id) REFERENCES tahun_ajaran(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =====================================================================
-- 9. TRANSAKSI (Buku Besar Utama: semua pemasukan & pengeluaran)
-- =====================================================================
CREATE TABLE transaksi (
    id INT AUTO_INCREMENT PRIMARY KEY,
    no_transaksi VARCHAR(30) NOT NULL UNIQUE,
    tanggal DATE NOT NULL,
    tipe ENUM('pemasukan','pengeluaran') NOT NULL,
    kategori_id INT NOT NULL,
    akun_id INT NOT NULL,
    jumlah DECIMAL(15,2) NOT NULL,
    keterangan VARCHAR(255) DEFAULT NULL,
    siswa_id INT DEFAULT NULL,             -- diisi jika terkait pembayaran SPP
    tagihan_id INT DEFAULT NULL,           -- diisi jika terkait pembayaran SPP
    bukti_file VARCHAR(255) DEFAULT NULL,
    created_by INT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (kategori_id) REFERENCES kategori(id),
    FOREIGN KEY (akun_id) REFERENCES akun_kas(id),
    FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE SET NULL,
    FOREIGN KEY (tagihan_id) REFERENCES tagihan_spp(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE INDEX idx_transaksi_tanggal ON transaksi(tanggal);
CREATE INDEX idx_transaksi_tipe ON transaksi(tipe);
