-- Database Schema untuk Modul PPDB (Penerimaan Peserta Didik Baru)
-- Tambahkan ke database yang sudah ada

-- 1. Tabel untuk menyimpan data pendaftaran
CREATE TABLE IF NOT EXISTS ppdb_registrations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  registration_number VARCHAR(50) UNIQUE NOT NULL,
  full_name VARCHAR(255) NOT NULL,
  nickname VARCHAR(100),
  gender ENUM('L', 'P') NOT NULL,
  birth_place VARCHAR(100) NOT NULL,
  birth_date DATE NOT NULL,
  religion VARCHAR(50) NOT NULL,
  address TEXT NOT NULL,
  rt_rw VARCHAR(20),
  kelurahan VARCHAR(100),
  kecamatan VARCHAR(100),
  city VARCHAR(100) NOT NULL,
  province VARCHAR(100) NOT NULL,
  postal_code VARCHAR(10),
  phone VARCHAR(20),
  email VARCHAR(255),
  
  -- Data Orang Tua/Wali
  father_name VARCHAR(255),
  father_occupation VARCHAR(100),
  father_phone VARCHAR(20),
  mother_name VARCHAR(255),
  mother_occupation VARCHAR(100),
  mother_phone VARCHAR(20),
  guardian_name VARCHAR(255),
  guardian_relation VARCHAR(50),
  guardian_phone VARCHAR(20),
  
  -- Data Sekolah Asal
  previous_school VARCHAR(255),
  previous_school_address TEXT,
  graduation_year YEAR,
  nisn VARCHAR(20),
  
  -- Pilihan Jurusan
  major_choice_1 VARCHAR(100),
  major_choice_2 VARCHAR(100),
  
  -- Dokumen
  photo VARCHAR(255),
  birth_certificate VARCHAR(255),
  family_card VARCHAR(255),
  report_card VARCHAR(255),
  
  -- Status Pendaftaran
  status ENUM('pending', 'verified', 'accepted', 'rejected') DEFAULT 'pending',
  notes TEXT,
  
  -- User yang mendaftar
  user_id INT,
  
  -- Timestamps
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  verified_at TIMESTAMP NULL,
  verified_by INT NULL,
  
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Tabel untuk pengaturan PPDB
CREATE TABLE IF NOT EXISTS ppdb_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(100) UNIQUE NOT NULL,
  setting_value TEXT,
  description TEXT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Insert pengaturan default
INSERT INTO ppdb_settings (setting_key, setting_value, description) VALUES
('ppdb_open', '1', 'Status PPDB: 1=Buka, 0=Tutup'),
('ppdb_start_date', '2024-01-01', 'Tanggal mulai pendaftaran'),
('ppdb_end_date', '2024-12-31', 'Tanggal akhir pendaftaran'),
('ppdb_quota', '100', 'Kuota penerimaan siswa baru'),
('ppdb_year', '2024/2025', 'Tahun ajaran'),
('registration_fee', '0', 'Biaya pendaftaran (0 = gratis)'),
('announcement_date', '2024-07-01', 'Tanggal pengumuman')
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);

-- 4. Tambah kolom role di tabel users jika belum ada
ALTER TABLE users ADD COLUMN IF NOT EXISTS role ENUM('admin', 'operator', 'student', 'teacher') DEFAULT 'student';

-- 5. Update user admin yang sudah ada
UPDATE users SET role = 'admin' WHERE username = 'admin' OR id = 1;

-- 6. Tabel untuk log aktivitas
CREATE TABLE IF NOT EXISTS ppdb_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  registration_id INT,
  user_id INT,
  action VARCHAR(100) NOT NULL,
  description TEXT,
  ip_address VARCHAR(45),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  FOREIGN KEY (registration_id) REFERENCES ppdb_registrations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Index untuk performa
CREATE INDEX idx_registration_number ON ppdb_registrations(registration_number);
CREATE INDEX idx_status ON ppdb_registrations(status);
CREATE INDEX idx_user_id ON ppdb_registrations(user_id);
CREATE INDEX idx_created_at ON ppdb_registrations(created_at);
