-- =====================================================
-- Database Schema untuk Aplikasi Jurnal Kelas & Mengajar
-- Database: smanabid_db_jurnal
-- User: smanabid
-- =====================================================

-- Buat database jika belum ada
CREATE DATABASE IF NOT EXISTS smanabid_db_jurnal 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE smanabid_db_jurnal;

-- =====================================================
-- Tabel Users (untuk login)
-- =====================================================
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    identifier VARCHAR(50) UNIQUE COMMENT 'NIP untuk guru, NIS untuk siswa',
    password VARCHAR(255) NOT NULL,
    role ENUM('ADMIN', 'GURU', 'SEKRETARIS') NOT NULL DEFAULT 'GURU',
    class_name VARCHAR(20) NULL COMMENT 'Khusus untuk sekretaris kelas',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_identifier (identifier),
    INDEX idx_role (role)
) ENGINE=InnoDB;

-- =====================================================
-- Tabel Teachers (Data Guru)
-- =====================================================
CREATE TABLE IF NOT EXISTS teachers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    nip VARCHAR(30) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_nip (nip),
    INDEX idx_name (name)
) ENGINE=InnoDB;

-- =====================================================
-- Tabel Students (Data Murid)
-- =====================================================
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    nis VARCHAR(20) UNIQUE,
    class_name VARCHAR(20) NOT NULL,
    gender ENUM('L', 'P') NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_nis (nis),
    INDEX idx_class (class_name),
    INDEX idx_name (name)
) ENGINE=InnoDB;

-- =====================================================
-- Tabel Subjects (Mata Pelajaran)
-- =====================================================
CREATE TABLE IF NOT EXISTS subjects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB;

-- =====================================================
-- Tabel Journal Entries (Jurnal Mengajar Guru)
-- =====================================================
CREATE TABLE IF NOT EXISTS journal_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id VARCHAR(50) UNIQUE NOT NULL COMMENT 'ID unik format JRN-timestamp',
    date DATE NOT NULL,
    teacher_name VARCHAR(100) NOT NULL,
    nip VARCHAR(30),
    class_name VARCHAR(20) NOT NULL,
    subject VARCHAR(100) NOT NULL,
    topic TEXT,
    start_time VARCHAR(10),
    end_time VARCHAR(10),
    students_present INT DEFAULT 0,
    students_absent INT DEFAULT 0,
    sakit INT DEFAULT 0,
    izin INT DEFAULT 0,
    alpha INT DEFAULT 0,
    dispen INT DEFAULT 0,
    notes TEXT,
    photo_url VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_journal_id (journal_id),
    INDEX idx_date (date),
    INDEX idx_teacher (teacher_name),
    INDEX idx_class (class_name),
    INDEX idx_subject (subject)
) ENGINE=InnoDB;

-- =====================================================
-- Tabel Class Journal Entries (Jurnal Kelas oleh Sekretaris)
-- =====================================================
CREATE TABLE IF NOT EXISTS class_journal_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id VARCHAR(50) UNIQUE NOT NULL COMMENT 'ID unik format KLS-timestamp',
    date DATE NOT NULL,
    teacher_name VARCHAR(100) NOT NULL,
    subject VARCHAR(100) NOT NULL,
    class_name VARCHAR(20) NOT NULL,
    teacher_presence ENUM('Hadir', 'Tugas', 'Tidak Hadir') DEFAULT 'Hadir',
    start_time VARCHAR(10),
    end_time VARCHAR(10),
    students_present INT DEFAULT 0,
    students_absent INT DEFAULT 0,
    sakit INT DEFAULT 0,
    izin INT DEFAULT 0,
    alpha INT DEFAULT 0,
    dispen INT DEFAULT 0,
    notes TEXT,
    class_secretary VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_journal_id (journal_id),
    INDEX idx_date (date),
    INDEX idx_teacher (teacher_name),
    INDEX idx_class (class_name)
) ENGINE=InnoDB;

-- =====================================================
-- Tabel Attendance Details (Detail Absensi per Jurnal)
-- =====================================================
CREATE TABLE IF NOT EXISTS attendance_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_type ENUM('teacher', 'class') NOT NULL,
    journal_id VARCHAR(50) NOT NULL COMMENT 'Reference ke journal_entries atau class_journal_entries',
    student_name VARCHAR(100) NOT NULL,
    status ENUM('H', 'S', 'I', 'A', 'D') NOT NULL COMMENT 'Hadir, Sakit, Izin, Alpha, Dispensasi',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_journal (journal_type, journal_id),
    INDEX idx_student (student_name),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- =====================================================
-- Tabel School Settings (Pengaturan Sekolah)
-- =====================================================
CREATE TABLE IF NOT EXISTS school_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(50) UNIQUE NOT NULL,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Insert default settings
INSERT INTO school_settings (setting_key, setting_value) VALUES
    ('semester', 'Ganjil'),
    ('academic_year', '2024/2025'),
    ('headmaster_name', ''),
    ('headmaster_nip', ''),
    ('logo_url', ''),
    ('kop_url', '')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

-- =====================================================
-- Insert Default Admin User
-- Password: admin123 (Anda harus hash ini menggunakan bcrypt di production)
-- =====================================================
INSERT INTO users (name, identifier, password, role) VALUES
    ('Administrator', 'admin', 'admin123', 'ADMIN')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- =====================================================
-- Sample Data untuk Testing (opsional)
-- =====================================================

-- Sample Teachers
INSERT INTO teachers (name, nip) VALUES
    ('I Ketut Suardika, S.Pd', '196512311990031234'),
    ('Ni Made Dewi Lestari, S.Pd', '198001152006042001'),
    ('I Wayan Dharma Putra, M.Pd', '197503222000121004')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Sample Subjects
INSERT INTO subjects (name) VALUES
    ('Matematika'),
    ('Bahasa Indonesia'),
    ('Bahasa Inggris'),
    ('Fisika'),
    ('Kimia'),
    ('Biologi'),
    ('Sejarah'),
    ('Geografi'),
    ('Ekonomi'),
    ('Pendidikan Agama'),
    ('PJOK'),
    ('Seni Budaya'),
    ('Informatika')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Note: Sample students removed - Admin manages student data via import/manual entry

-- =====================================================
-- Views untuk memudahkan query
-- =====================================================

-- View untuk statistik per kelas
CREATE OR REPLACE VIEW v_class_statistics AS
SELECT 
    class_name,
    COUNT(*) as total_entries,
    SUM(students_present) as total_present,
    SUM(sakit) as total_sakit,
    SUM(izin) as total_izin,
    SUM(alpha) as total_alpha,
    SUM(dispen) as total_dispen
FROM journal_entries
GROUP BY class_name;

-- View untuk statistik per guru
CREATE OR REPLACE VIEW v_teacher_statistics AS
SELECT 
    teacher_name,
    nip,
    COUNT(*) as total_entries,
    COUNT(DISTINCT class_name) as total_classes,
    COUNT(DISTINCT subject) as total_subjects
FROM journal_entries
GROUP BY teacher_name, nip;

-- =====================================================
-- Stored Procedure untuk mendapatkan semua data
-- =====================================================
DELIMITER //

CREATE PROCEDURE IF NOT EXISTS sp_get_all_data()
BEGIN
    -- Teachers
    SELECT id, name, nip FROM teachers ORDER BY name;
    
    -- Students
    SELECT id, name, nis, class_name, gender FROM students ORDER BY class_name, name;
    
    -- Subjects
    SELECT id, name FROM subjects ORDER BY name;
    
    -- Journal Entries (latest 100)
    SELECT * FROM journal_entries ORDER BY created_at DESC LIMIT 100;
    
    -- Class Journal Entries (latest 100)
    SELECT * FROM class_journal_entries ORDER BY created_at DESC LIMIT 100;
    
    -- Settings
    SELECT setting_key, setting_value FROM school_settings;
END //

DELIMITER ;

-- =====================================================
-- Grants (jalankan sebagai root jika diperlukan)
-- =====================================================
-- GRANT ALL PRIVILEGES ON smanabid_db_jurnal.* TO 'smanabid'@'localhost';
-- FLUSH PRIVILEGES;