-- =====================================================================
-- Database: siakad_db
-- Sistem Informasi Akademik (SIAKAD) — STT Injili Arastamar Nias Selatan
-- Import lewat phpMyAdmin atau: mysql -u root -p < database.sql
-- =====================================================================

CREATE DATABASE IF NOT EXISTS siakad_db
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE siakad_db;

-- =====================================================================
-- MASTER DATA
-- =====================================================================

CREATE TABLE IF NOT EXISTS program_studi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kode VARCHAR(20) NOT NULL UNIQUE,
  nama VARCHAR(150) NOT NULL,
  jenjang VARCHAR(20) DEFAULT 'S1'
) ENGINE=InnoDB;

INSERT INTO program_studi (kode, nama, jenjang) VALUES
('TH', 'Program Studi Teologi', 'S1'),
('PAK', 'Program Studi Pendidikan Agama Kristen', 'S1');

CREATE TABLE IF NOT EXISTS tahun_akademik (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(50) NOT NULL,               -- Contoh: 2025/2026 Ganjil
  status ENUM('aktif','nonaktif') NOT NULL DEFAULT 'nonaktif',
  periode_krs_mulai DATE DEFAULT NULL,
  periode_krs_selesai DATE DEFAULT NULL
) ENGINE=InnoDB;

INSERT INTO tahun_akademik (nama, status, periode_krs_mulai, periode_krs_selesai) VALUES
('2025/2026 Ganjil', 'aktif', '2025-08-01', '2025-09-30');

-- ---------------------------------------------------------------------
-- Akun pengguna: dosen, mahasiswa, admin (tabel terpisah karena field
-- dan hak aksesnya berbeda; semua password memakai password_hash bcrypt)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS dosen (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nidn VARCHAR(30) NOT NULL UNIQUE,
  nama VARCHAR(150) NOT NULL,
  password VARCHAR(255) NOT NULL,
  email VARCHAR(150) DEFAULT NULL,
  no_hp VARCHAR(30) DEFAULT NULL,
  prodi_id INT DEFAULT NULL,
  status ENUM('aktif','nonaktif') NOT NULL DEFAULT 'aktif',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (prodi_id) REFERENCES program_studi(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- password: dosen123
INSERT INTO dosen (nidn, nama, password, email, prodi_id, status) VALUES
('0001018501', 'Pdt. Andi Waruwu, M.Th', '$2b$10$qmdfu7C5/5Q.GsXYxH1B4OAFWmrEdDOKoJhYPRPnPIs3qWvBWb3.u', 'andi.waruwu@arastamar.ac.id', 1, 'aktif'),
('0002028502', 'Pdt. Yohana Zebua, M.Pd', '$2b$10$qmdfu7C5/5Q.GsXYxH1B4OAFWmrEdDOKoJhYPRPnPIs3qWvBWb3.u', 'yohana.zebua@arastamar.ac.id', 2, 'aktif');

CREATE TABLE IF NOT EXISTS mahasiswa (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nim VARCHAR(30) NOT NULL UNIQUE,
  nama VARCHAR(150) NOT NULL,
  password VARCHAR(255) NOT NULL,
  email VARCHAR(150) DEFAULT NULL,
  no_hp VARCHAR(30) DEFAULT NULL,
  prodi_id INT NOT NULL,
  angkatan YEAR DEFAULT NULL,
  semester_berjalan INT NOT NULL DEFAULT 1,
  dosen_pa_id INT DEFAULT NULL,           -- Dosen Pembimbing Akademik
  status ENUM('aktif','cuti','nonaktif','lulus') NOT NULL DEFAULT 'aktif',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (prodi_id) REFERENCES program_studi(id),
  FOREIGN KEY (dosen_pa_id) REFERENCES dosen(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- password: mhs123
INSERT INTO mahasiswa (nim, nama, password, email, prodi_id, angkatan, semester_berjalan, dosen_pa_id, status) VALUES
('2023001', 'Bahagia Halawa', '$2b$10$ZoaowgnIUdoIX.NNI43N8OSdK/INd4o68u2qFOsWvfYH1MyXqr9WO', 'bahagia.halawa@mhs.arastamar.ac.id', 1, 2023, 3, 1, 'aktif'),
('2023002', 'Kasih Karunia Laoli', '$2b$10$ZoaowgnIUdoIX.NNI43N8OSdK/INd4o68u2qFOsWvfYH1MyXqr9WO', 'kasih.laoli@mhs.arastamar.ac.id', 2, 2023, 3, 2, 'aktif');

CREATE TABLE IF NOT EXISTS admin (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  nama_lengkap VARCHAR(100) NOT NULL,
  role ENUM('akademik','keuangan','superadmin') NOT NULL DEFAULT 'akademik',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- password: admin123
INSERT INTO admin (username, password, nama_lengkap, role) VALUES
('admin', '$2b$10$4N5yK4ioTvKkJghNmfuCNevFU4gOvVQqNHhpg0tcdv7K51cxe8FCi', 'Administrator SIAKAD', 'superadmin');

-- =====================================================================
-- KURIKULUM & PERKULIAHAN
-- =====================================================================

CREATE TABLE IF NOT EXISTS mata_kuliah (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kode VARCHAR(20) NOT NULL UNIQUE,
  nama VARCHAR(150) NOT NULL,
  sks INT NOT NULL DEFAULT 2,
  semester_ke INT NOT NULL DEFAULT 1,     -- semester kurikulum (1,2,3,...)
  prodi_id INT NOT NULL,
  status ENUM('aktif','nonaktif') NOT NULL DEFAULT 'aktif',
  FOREIGN KEY (prodi_id) REFERENCES program_studi(id)
) ENGINE=InnoDB;

INSERT INTO mata_kuliah (kode, nama, sks, semester_ke, prodi_id) VALUES
('TH301', 'Teologi Sistematika I', 3, 3, 1),
('TH302', 'Homiletika', 2, 3, 1),
('TH303', 'Bahasa Yunani', 3, 3, 1),
('PAK301', 'Metodologi Pengajaran PAK', 3, 3, 2),
('PAK302', 'Psikologi Pendidikan', 2, 3, 2),
('UM301', 'Pendidikan Kewarganegaraan', 2, 3, 1);

-- Kelas: penawaran satu mata kuliah pada satu tahun akademik, diampu
-- satu dosen, dengan jadwal tertentu. Mahasiswa mengambil KRS per kelas.
CREATE TABLE IF NOT EXISTS kelas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mata_kuliah_id INT NOT NULL,
  dosen_id INT NOT NULL,
  tahun_akademik_id INT NOT NULL,
  nama_kelas VARCHAR(10) NOT NULL DEFAULT 'A',
  hari VARCHAR(20) DEFAULT NULL,
  jam_mulai TIME DEFAULT NULL,
  jam_selesai TIME DEFAULT NULL,
  ruang VARCHAR(50) DEFAULT NULL,
  kuota INT NOT NULL DEFAULT 40,
  status ENUM('aktif','nonaktif') NOT NULL DEFAULT 'aktif',
  FOREIGN KEY (mata_kuliah_id) REFERENCES mata_kuliah(id),
  FOREIGN KEY (dosen_id) REFERENCES dosen(id),
  FOREIGN KEY (tahun_akademik_id) REFERENCES tahun_akademik(id)
) ENGINE=InnoDB;

INSERT INTO kelas (mata_kuliah_id, dosen_id, tahun_akademik_id, nama_kelas, hari, jam_mulai, jam_selesai, ruang, kuota) VALUES
(1, 1, 1, 'A', 'Senin', '08:00', '10:30', 'R.101', 40),
(2, 1, 1, 'A', 'Selasa', '08:00', '09:40', 'R.101', 40),
(3, 1, 1, 'A', 'Rabu', '08:00', '10:30', 'R.102', 40),
(4, 2, 1, 'A', 'Senin', '10:30', '13:00', 'R.201', 40),
(5, 2, 1, 'A', 'Selasa', '10:00', '11:40', 'R.201', 40),
(6, 1, 1, 'A', 'Kamis', '08:00', '09:40', 'R.103', 60);

-- =====================================================================
-- KRS (Kartu Rencana Studi) — dengan alur validasi Dosen PA
-- =====================================================================

-- Header pengajuan KRS per mahasiswa per tahun akademik
CREATE TABLE IF NOT EXISTS krs_pengajuan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mahasiswa_id INT NOT NULL,
  tahun_akademik_id INT NOT NULL,
  status ENUM('diajukan','disetujui','ditolak') NOT NULL DEFAULT 'diajukan',
  tanggal_ajukan TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  tanggal_validasi TIMESTAMP NULL DEFAULT NULL,
  catatan_dosen VARCHAR(500) DEFAULT NULL,
  UNIQUE KEY unik_krs (mahasiswa_id, tahun_akademik_id),
  FOREIGN KEY (mahasiswa_id) REFERENCES mahasiswa(id),
  FOREIGN KEY (tahun_akademik_id) REFERENCES tahun_akademik(id)
) ENGINE=InnoDB;

-- Detail mata kuliah/kelas yang diambil dalam satu pengajuan KRS.
-- Nilai akhir juga disimpan di sini setelah KRS disetujui & perkuliahan selesai.
CREATE TABLE IF NOT EXISTS krs_detail (
  id INT AUTO_INCREMENT PRIMARY KEY,
  krs_pengajuan_id INT NOT NULL,
  kelas_id INT NOT NULL,
  nilai_angka DECIMAL(5,2) DEFAULT NULL,
  nilai_huruf VARCHAR(2) DEFAULT NULL,
  nilai_bobot DECIMAL(3,2) DEFAULT NULL,
  UNIQUE KEY unik_detail (krs_pengajuan_id, kelas_id),
  FOREIGN KEY (krs_pengajuan_id) REFERENCES krs_pengajuan(id) ON DELETE CASCADE,
  FOREIGN KEY (kelas_id) REFERENCES kelas(id)
) ENGINE=InnoDB;

-- =====================================================================
-- ABSENSI
-- =====================================================================

CREATE TABLE IF NOT EXISTS absensi_pertemuan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kelas_id INT NOT NULL,
  pertemuan_ke INT NOT NULL,
  tanggal DATE NOT NULL,
  topik VARCHAR(255) DEFAULT NULL,
  dibuat_oleh INT NOT NULL,               -- dosen_id
  UNIQUE KEY unik_pertemuan (kelas_id, pertemuan_ke),
  FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE CASCADE,
  FOREIGN KEY (dibuat_oleh) REFERENCES dosen(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS absensi_detail (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pertemuan_id INT NOT NULL,
  mahasiswa_id INT NOT NULL,
  status ENUM('hadir','izin','sakit','alpa') NOT NULL DEFAULT 'alpa',
  UNIQUE KEY unik_absen (pertemuan_id, mahasiswa_id),
  FOREIGN KEY (pertemuan_id) REFERENCES absensi_pertemuan(id) ON DELETE CASCADE,
  FOREIGN KEY (mahasiswa_id) REFERENCES mahasiswa(id)
) ENGINE=InnoDB;

-- =====================================================================
-- TUGAS
-- =====================================================================

CREATE TABLE IF NOT EXISTS tugas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kelas_id INT NOT NULL,
  judul VARCHAR(200) NOT NULL,
  deskripsi TEXT,
  file_lampiran VARCHAR(255) DEFAULT NULL,
  deadline DATETIME DEFAULT NULL,
  dibuat_pada TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS pengumpulan_tugas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tugas_id INT NOT NULL,
  mahasiswa_id INT NOT NULL,
  file_jawaban VARCHAR(255) NOT NULL,
  catatan_mahasiswa VARCHAR(500) DEFAULT NULL,
  waktu_kumpul TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  nilai DECIMAL(5,2) DEFAULT NULL,
  catatan_dosen VARCHAR(500) DEFAULT NULL,
  UNIQUE KEY unik_kumpul (tugas_id, mahasiswa_id),
  FOREIGN KEY (tugas_id) REFERENCES tugas(id) ON DELETE CASCADE,
  FOREIGN KEY (mahasiswa_id) REFERENCES mahasiswa(id)
) ENGINE=InnoDB;

-- =====================================================================
-- KEUANGAN
-- =====================================================================

CREATE TABLE IF NOT EXISTS pembayaran (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mahasiswa_id INT NOT NULL,
  tahun_akademik_id INT NOT NULL,
  jenis_pembayaran VARCHAR(100) NOT NULL,   -- SPP, Uang Gedung, dll
  jumlah DECIMAL(12,2) NOT NULL,
  status ENUM('belum_bayar','menunggu_verifikasi','lunas') NOT NULL DEFAULT 'belum_bayar',
  metode VARCHAR(50) DEFAULT NULL,
  bukti_file VARCHAR(255) DEFAULT NULL,
  tanggal_tagihan DATE DEFAULT NULL,
  tanggal_bayar DATE DEFAULT NULL,
  dicatat_oleh INT DEFAULT NULL,            -- admin_id yang input/verifikasi
  catatan VARCHAR(300) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (mahasiswa_id) REFERENCES mahasiswa(id),
  FOREIGN KEY (tahun_akademik_id) REFERENCES tahun_akademik(id)
) ENGINE=InnoDB;

INSERT INTO pembayaran (mahasiswa_id, tahun_akademik_id, jenis_pembayaran, jumlah, status, tanggal_tagihan) VALUES
(1, 1, 'SPP Semester Ganjil 2025/2026', 2500000.00, 'belum_bayar', '2025-08-01'),
(2, 1, 'SPP Semester Ganjil 2025/2026', 2500000.00, 'lunas', '2025-08-01');

UPDATE pembayaran SET tanggal_bayar = '2025-08-10', metode = 'Transfer Bank', dicatat_oleh = 1 WHERE mahasiswa_id = 2;