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

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(60) NOT NULL UNIQUE,
  `nama_lengkap` VARCHAR(150) NOT NULL,
  `email` VARCHAR(120) DEFAULT NULL,
  `role` ENUM('admin', 'guru', 'siswa') NOT NULL DEFAULT 'siswa',
  `nisn` VARCHAR(20) DEFAULT NULL,
  `nip` VARCHAR(30) DEFAULT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_role` (`role`),
  INDEX `idx_nisn` (`nisn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ijazah` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nisn` VARCHAR(20) NOT NULL UNIQUE,
  `nism` VARCHAR(30) NOT NULL,
  `nama_lengkap` VARCHAR(150) NOT NULL,
  `tempat_lahir` VARCHAR(100) NOT NULL,
  `tanggal_lahir` DATE NOT NULL,
  `jenis_kelamin` ENUM('L', 'P') NOT NULL DEFAULT 'L',
  `nama_orang_tua` VARCHAR(150) NOT NULL,
  `peminatan` ENUM('IPA', 'IPS', 'Keagamaan', 'Bahasa') NOT NULL DEFAULT 'IPA',
  `tahun_lulus` SMALLINT UNSIGNED NOT NULL,
  `nomor_seri_ijazah` VARCHAR(64) NOT NULL UNIQUE,
  `nomor_skhun` VARCHAR(64) DEFAULT '',
  `nilai_rata_rata` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `tanggal_terbit` DATE NOT NULL,
  `status_verifikasi` ENUM('Terverifikasi', 'Menunggu Verifikasi', 'Perlu Revisi') NOT NULL DEFAULT 'Menunggu Verifikasi',
  `nomor_rak_arsip` VARCHAR(50) NOT NULL DEFAULT '',
  `file_scan_path` VARCHAR(255) DEFAULT NULL,
  `catatan` TEXT DEFAULT NULL,
  `verified_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_tahun_peminatan` (`tahun_lulus`, `peminatan`),
  INDEX `idx_status` (`status_verifikasi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `permohonan_legalisir` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ijazah_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `jenis_layanan` ENUM('Legalisir Ijazah', 'Koreksi Data', 'Salinan Digital') NOT NULL DEFAULT 'Legalisir Ijazah',
  `jumlah_lembar` TINYINT UNSIGNED NOT NULL DEFAULT 5,
  `keterangan` TEXT NOT NULL,
  `status` ENUM('Diajukan', 'Diproses', 'Selesai', 'Ditolak') NOT NULL DEFAULT 'Diajukan',
  `tanggapan_admin` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_legalisir_ijazah` FOREIGN KEY (`ijazah_id`) REFERENCES `ijazah` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_legalisir_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `audit_log` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `aksi` VARCHAR(80) NOT NULL,
  `ringkasan` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed awal akun contoh (password default: Madrasah@2026)
INSERT INTO `users` (`username`, `nama_lengkap`, `email`, `role`, `nisn`, `nip`, `password_hash`, `is_active`) VALUES
('admin_tu', 'Dra. Siti Aminah, M.Pd.', 'tu@ma-alfalah.sch.id', 'admin', NULL, '197408122005012004', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1),
('guru_wali', 'Ahmad Fauzan, S.Pd.I.', 'fauzan@ma-alfalah.sch.id', 'guru', NULL, '198603192011011007', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1),
('0064829103', 'Muhammad Rizqi Pratama', 'rizqi.alumni@gmail.com', 'siswa', '0064829103', NULL, '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1);

-- Catatan: Hash di atas adalah contoh standar bcrypt; jalankan setup_password.php atau ubah via panel Admin setelah login.

INSERT INTO `ijazah` (`nisn`, `nism`, `nama_lengkap`, `tempat_lahir`, `tanggal_lahir`, `jenis_kelamin`, `nama_orang_tua`, `peminatan`, `tahun_lulus`, `nomor_seri_ijazah`, `nomor_skhun`, `nilai_rata_rata`, `tanggal_terbit`, `status_verifikasi`, `nomor_rak_arsip`, `catatan`) VALUES
('0064829103', '131135090001240001', 'Muhammad Rizqi Pratama', 'Yogyakarta', '2006-04-17', 'L', 'H. Hendra Wijaya', 'IPA', 2024, 'MA-24/13-01/004821', 'SKL-MA/2024/001', 91.45, '2024-06-15', 'Terverifikasi', 'RAK-A1-04', 'Berkas fisik dan pindaian lengkap.'),
('0065193024', '131135090001240002', 'Aisyah Nurul Hidayah', 'Sleman', '2006-08-03', 'P', 'Drs. Abdul Karim', 'Keagamaan', 2024, 'MA-24/13-01/004822', 'SKL-MA/2024/002', 93.20, '2024-06-15', 'Terverifikasi', 'RAK-A1-05', 'Lulusan terbaik peminatan IIK.'),
('0071849201', '131135090001250014', 'Fadhilah Zahra Putri', 'Bantul', '2007-02-11', 'P', 'Ir. Bambang Sutrisno', 'IPS', 2025, 'MA-25/13-01/005109', 'SKL-MA/2025/014', 88.75, '2025-06-12', 'Menunggu Verifikasi', 'RAK-B2-11', 'Menunggu tanda tangan wali kelas.');
