CREATE DATABASE IF NOT EXISTS `db_keuangan_darululum`;
USE `db_keuangan_darululum`;

-- 1. Tabel Unit / Lembaga
CREATE TABLE `units` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `nama_unit` VARCHAR(100) NOT NULL,
  `kode_unit` VARCHAR(20) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `units` (`id`, `nama_unit`, `kode_unit`) VALUES
(1, 'Yayasan Pusat', 'YYS'),
(2, 'PAUD Darul Ulum', 'PAUD'),
(3, 'TK Tunas Darul Ulum', 'TK'),
(4, 'MI Darul Ulum', 'MI'),
(5, 'MTs Darul Ulum', 'MTS'),
(6, 'SMP Negeri 3 Sugio', 'SMPN3'),
(7, 'SMA Darul Ulum', 'SMA'),
(8, 'Pondok Pesantren Darul Ulum', 'PONPES');

-- 2. Tabel Tahun Ajaran
CREATE TABLE `academic_years` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `tahun` VARCHAR(20) NOT NULL,
  `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `academic_years` (`id`, `tahun`, `is_active`) VALUES
(1, '2025/2026', 1),
(2, '2026/2027', 0);

-- 3. Tabel Users
CREATE TABLE `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `nama` VARCHAR(100) NOT NULL,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('super_admin', 'ketua_yayasan', 'bendahara_unit') NOT NULL,
  `unit_id` INT DEFAULT NULL,
  FOREIGN KEY (`unit_id`) REFERENCES `units`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Password bawaan untuk semua akun awal: admin123
INSERT INTO `users` (`nama`, `username`, `password`, `role`, `unit_id`) VALUES
('Super Admin', 'admin', '$2y$10$4eI4Gq8xI/I5q4zOaN/.uO9d5/7zJk.d9lGqXzQxQ9g1pW1yZ.Cq2', 'super_admin', 1),
('Ketua Yayasan', 'ketua', '$2y$10$4eI4Gq8xI/I5q4zOaN/.uO9d5/7zJk.d9lGqXzQxQ9g1pW1yZ.Cq2', 'ketua_yayasan', 1),
('Bendahara MI', 'bendahara_mi', '$2y$10$4eI4Gq8xI/I5q4zOaN/.uO9d5/7zJk.d9lGqXzQxQ9g1pW1yZ.Cq2', 'bendahara_unit', 4),
('Bendahara SMA', 'bendahara_sma', '$2y$10$4eI4Gq8xI/I5q4zOaN/.uO9d5/7zJk.d9lGqXzQxQ9g1pW1yZ.Cq2', 'bendahara_unit', 7);

-- 4. Tabel Transaksi Keuangan
CREATE TABLE `transactions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `no_bukti` VARCHAR(50) NOT NULL UNIQUE,
  `unit_id` INT NOT NULL,
  `academic_year_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `tanggal` DATE NOT NULL,
  `jenis_transaksi` ENUM('MASUK', 'KELUAR') NOT NULL,
  `kategori` VARCHAR(100) NOT NULL,
  `nominal` DECIMAL(15,2) NOT NULL,
  `keterangan` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`unit_id`) REFERENCES `units`(`id`),
  FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years`(`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Data Contoh Transaksi
INSERT INTO `transactions` (`no_bukti`, `unit_id`, `academic_year_id`, `user_id`, `tanggal`, `jenis_transaksi`, `kategori`, `nominal`, `keterangan`) VALUES
('TRX-YYS-202607-001', 1, 1, 1, '2026-07-01', 'MASUK', 'Infaq Donatur', 50000000.00, 'Sumbangan H. Ahmad'),
('TRX-MI-202607-001', 4, 1, 3, '2026-07-02', 'MASUK', 'SPP Siswa', 12500000.00, 'Pemasukan SPP Juli'),
('TRX-MI-202607-002', 4, 1, 3, '2026-07-05', 'KELUAR', 'Operasional', 2500000.00, 'Pembelian ATK Kantor'),
('TRX-SMA-202607-001', 7, 1, 4, '2026-07-10', 'MASUK', 'Uang Gedung', 18000000.00, 'Penerimaan Siswa Baru');