CREATE DATABASE IF NOT EXISTS edujurnal_pro CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE edujurnal_pro;
CREATE TABLE IF NOT EXISTS academic_years (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_academic_year_dates(start_date,end_date)) ENGINE=InnoDB;
INSERT IGNORE INTO academic_years(name,start_date,end_date,is_active) VALUES('2026/2027','2026-07-01','2027-06-30',1);
UPDATE academic_years SET is_active=0;
UPDATE academic_years SET is_active=1 WHERE name='2026/2027';
CREATE TABLE IF NOT EXISTS users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, username VARCHAR(80) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','guru','orang_tua') NOT NULL DEFAULT 'guru', active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS teachers (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL UNIQUE, nip VARCHAR(40) NULL UNIQUE, name VARCHAR(150) NOT NULL, email VARCHAR(150) NULL, phone VARCHAR(30) NULL, subject_area VARCHAR(120) NULL, employment_status ENUM('Aktif','Nonaktif') NOT NULL DEFAULT 'Aktif', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_teacher_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_teacher_name(name)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS students (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nis VARCHAR(40) NOT NULL, nisn VARCHAR(40) NULL, name VARCHAR(150) NOT NULL, gender ENUM('L','P') NOT NULL, class_name VARCHAR(40) NOT NULL, academic_year_id BIGINT UNSIGNED NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_student_nis_year(nis,academic_year_id), INDEX idx_students_class(class_name), INDEX idx_students_name(name), INDEX idx_student_year_class(academic_year_id,class_name), CONSTRAINT fk_student_academic_year FOREIGN KEY(academic_year_id) REFERENCES academic_years(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS attendance (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, student_id BIGINT UNSIGNED NOT NULL, attendance_date DATE NOT NULL, class_name VARCHAR(40) NOT NULL, status ENUM('H','S','I','A') NOT NULL, note VARCHAR(255) NULL, recorded_by BIGINT UNSIGNED NOT NULL, academic_year_id BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_attendance(student_id,attendance_date), INDEX idx_att_year_date(academic_year_id,attendance_date,class_name), CONSTRAINT fk_att_academic_year FOREIGN KEY(academic_year_id) REFERENCES academic_years(id), CONSTRAINT fk_att_student FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE, CONSTRAINT fk_att_user FOREIGN KEY(recorded_by) REFERENCES users(id), INDEX idx_att_date(attendance_date,class_name)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS journals (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, journal_date DATE NOT NULL, class_name VARCHAR(40) NOT NULL, subject_name VARCHAR(120) NOT NULL, topic VARCHAR(180) NOT NULL, activity TEXT NOT NULL, obstacles TEXT NULL, follow_up TEXT NULL, academic_year_id BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_journal_year_date(academic_year_id,journal_date), CONSTRAINT fk_journal_academic_year FOREIGN KEY(academic_year_id) REFERENCES academic_years(id), CONSTRAINT fk_journal_user FOREIGN KEY(user_id) REFERENCES users(id), INDEX idx_journal_date(journal_date)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, action VARCHAR(80) NOT NULL, detail TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_audit_created(created_at)) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS parent_students (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, parent_user_id BIGINT UNSIGNED NOT NULL, student_id BIGINT UNSIGNED NOT NULL, relationship VARCHAR(40) NOT NULL DEFAULT 'Orang Tua/Wali', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_parent_student(parent_user_id,student_id), INDEX idx_parent_student_student(student_id), CONSTRAINT fk_parent_students_user FOREIGN KEY(parent_user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_parent_students_student FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE) ENGINE=InnoDB;