-- Perangkat Guru: MySQL 8.0+ schema
-- Import this file into an empty UTF-8 MySQL database.

SET NAMES utf8mb4;
SET time_zone = '+07:00';

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'teacher') NOT NULL DEFAULT 'teacher',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE school_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_name VARCHAR(190) NOT NULL,
    address TEXT NULL,
    phone VARCHAR(50) NULL,
    accreditation VARCHAR(50) NULL,
    principal_name VARCHAR(150) NULL,
    principal_identity_number VARCHAR(100) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE academic_periods (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    semester ENUM('Gasal', 'Genap') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    UNIQUE KEY uq_period_name_semester (name, semester)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE teachers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    identity_number VARCHAR(100) NULL,
    CONSTRAINT fk_teachers_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE classes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    grade_level VARCHAR(20) NOT NULL,
    homeroom_teacher_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_classes_homeroom FOREIGN KEY (homeroom_teacher_id) REFERENCES teachers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subjects (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(50) NULL UNIQUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE students (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    student_number VARCHAR(100) NOT NULL UNIQUE,
    nisn VARCHAR(30) NULL UNIQUE,
    name VARCHAR(190) NOT NULL,
    gender ENUM('L', 'P') NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE enrollments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    student_id BIGINT UNSIGNED NOT NULL,
    class_id BIGINT UNSIGNED NOT NULL,
    academic_period_id BIGINT UNSIGNED NOT NULL,
    attendance_number VARCHAR(20) NULL,
    UNIQUE KEY uq_enrollment (student_id, class_id, academic_period_id),
    KEY idx_enrollment_class_period (class_id, academic_period_id),
    CONSTRAINT fk_enrollment_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    CONSTRAINT fk_enrollment_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    CONSTRAINT fk_enrollment_period FOREIGN KEY (academic_period_id) REFERENCES academic_periods(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE calendar_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    academic_period_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(190) NOT NULL,
    category ENUM('pembelajaran', 'libur', 'asesmen', 'sekolah', 'lainnya') NOT NULL DEFAULT 'lainnya',
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    is_effective TINYINT(1) NOT NULL DEFAULT 0,
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_calendar_period_date (academic_period_id, start_date),
    CONSTRAINT fk_calendar_period FOREIGN KEY (academic_period_id) REFERENCES academic_periods(id) ON DELETE CASCADE,
    CONSTRAINT fk_calendar_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE effective_week_plans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    academic_period_id BIGINT UNSIGNED NOT NULL,
    class_id BIGINT UNSIGNED NOT NULL,
    subject_id BIGINT UNSIGNED NOT NULL,
    teacher_id BIGINT UNSIGNED NULL,
    lessons_per_week DECIMAL(5,2) NOT NULL DEFAULT 0,
    total_calendar_weeks DECIMAL(5,2) NOT NULL DEFAULT 0,
    non_effective_weeks DECIMAL(5,2) NOT NULL DEFAULT 0,
    effective_weeks DECIMAL(5,2) NOT NULL DEFAULT 0,
    effective_lessons DECIMAL(7,2) NOT NULL DEFAULT 0,
    UNIQUE KEY uq_effective_plan (academic_period_id, class_id, subject_id),
    CONSTRAINT fk_ewp_period FOREIGN KEY (academic_period_id) REFERENCES academic_periods(id) ON DELETE CASCADE,
    CONSTRAINT fk_ewp_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    CONSTRAINT fk_ewp_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    CONSTRAINT fk_ewp_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE effective_week_months (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    effective_week_plan_id BIGINT UNSIGNED NOT NULL,
    month_name VARCHAR(30) NOT NULL,
    week_count DECIMAL(5,2) NOT NULL DEFAULT 0,
    effective_day_count INT UNSIGNED NULL,
    notes VARCHAR(255) NULL,
    CONSTRAINT fk_ewm_plan FOREIGN KEY (effective_week_plan_id) REFERENCES effective_week_plans(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE non_effective_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    effective_week_plan_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(190) NOT NULL,
    week_count DECIMAL(5,2) NOT NULL DEFAULT 0,
    notes TEXT NULL,
    CONSTRAINT fk_nee_plan FOREIGN KEY (effective_week_plan_id) REFERENCES effective_week_plans(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE teaching_schedules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    academic_period_id BIGINT UNSIGNED NOT NULL,
    class_id BIGINT UNSIGNED NOT NULL,
    subject_id BIGINT UNSIGNED NOT NULL,
    teacher_id BIGINT UNSIGNED NULL,
    day_of_week TINYINT UNSIGNED NOT NULL COMMENT '1 Monday to 7 Sunday',
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    room VARCHAR(100) NULL,
    UNIQUE KEY uq_schedule (academic_period_id, class_id, subject_id, day_of_week, start_time),
    CONSTRAINT fk_schedule_period FOREIGN KEY (academic_period_id) REFERENCES academic_periods(id) ON DELETE CASCADE,
    CONSTRAINT fk_schedule_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    CONSTRAINT fk_schedule_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    CONSTRAINT fk_schedule_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE teaching_journals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    teaching_schedule_id BIGINT UNSIGNED NOT NULL,
    effective_week_plan_id BIGINT UNSIGNED NULL,
    journal_date DATE NOT NULL,
    learning_objective TEXT NOT NULL,
    material TEXT NOT NULL,
    activities TEXT NOT NULL,
    allocation_minutes SMALLINT UNSIGNED NOT NULL,
    students_present SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    students_absent SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    absent_student_names TEXT NULL,
    implementation_status ENUM('terlaksana', 'sebagian', 'tidak_terlaksana') NOT NULL DEFAULT 'terlaksana',
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_journal_schedule_date (teaching_schedule_id, journal_date),
    CONSTRAINT fk_journal_schedule FOREIGN KEY (teaching_schedule_id) REFERENCES teaching_schedules(id) ON DELETE CASCADE,
    CONSTRAINT fk_journal_effective_plan FOREIGN KEY (effective_week_plan_id) REFERENCES effective_week_plans(id) ON DELETE SET NULL,
    CONSTRAINT fk_journal_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE assessment_sets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    academic_period_id BIGINT UNSIGNED NOT NULL,
    class_id BIGINT UNSIGNED NOT NULL,
    subject_id BIGINT UNSIGNED NOT NULL,
    teacher_id BIGINT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    tp_code VARCHAR(30) NOT NULL,
    tp_description TEXT NULL,
    components JSON NOT NULL,
    is_report_sumatif_1 TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_assessment_set_context (academic_period_id, class_id, subject_id),
    CONSTRAINT fk_aset_period FOREIGN KEY (academic_period_id) REFERENCES academic_periods(id) ON DELETE CASCADE,
    CONSTRAINT fk_aset_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    CONSTRAINT fk_aset_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    CONSTRAINT fk_aset_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE knowledge_scores (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    assessment_set_id BIGINT UNSIGNED NOT NULL,
    student_id BIGINT UNSIGNED NOT NULL,
    component_scores JSON NOT NULL,
    calculated_average DECIMAL(5,2) NULL,
    updated_by BIGINT UNSIGNED NOT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_knowledge_score (assessment_set_id, student_id),
    CONSTRAINT fk_kscore_set FOREIGN KEY (assessment_set_id) REFERENCES assessment_sets(id) ON DELETE CASCADE,
    CONSTRAINT fk_kscore_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    CONSTRAINT fk_kscore_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE diagnostic_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    academic_period_id BIGINT UNSIGNED NOT NULL,
    class_id BIGINT UNSIGNED NOT NULL,
    subject_id BIGINT UNSIGNED NOT NULL,
    student_id BIGINT UNSIGNED NOT NULL,
    non_cognitive JSON NOT NULL,
    cognitive JSON NOT NULL,
    updated_by BIGINT UNSIGNED NOT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_diagnostic_profile (academic_period_id, class_id, subject_id, student_id),
    CONSTRAINT fk_diag_period FOREIGN KEY (academic_period_id) REFERENCES academic_periods(id) ON DELETE CASCADE,
    CONSTRAINT fk_diag_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    CONSTRAINT fk_diag_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    CONSTRAINT fk_diag_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    CONSTRAINT fk_diag_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE daily_assessments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    academic_period_id BIGINT UNSIGNED NOT NULL,
    class_id BIGINT UNSIGNED NOT NULL,
    subject_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(190) NOT NULL,
    competency_description TEXT NULL,
    assessment_date DATE NOT NULL,
    item_count SMALLINT UNSIGNED NOT NULL,
    maximum_score DECIMAL(6,2) NOT NULL DEFAULT 100,
    kktp DECIMAL(5,2) NOT NULL DEFAULT 75,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_da_period FOREIGN KEY (academic_period_id) REFERENCES academic_periods(id) ON DELETE CASCADE,
    CONSTRAINT fk_da_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    CONSTRAINT fk_da_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    CONSTRAINT fk_da_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE daily_assessment_scores (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    daily_assessment_id BIGINT UNSIGNED NOT NULL,
    student_id BIGINT UNSIGNED NOT NULL,
    item_scores JSON NOT NULL,
    total_score DECIMAL(7,2) NULL,
    achievement DECIMAL(6,2) NULL,
    is_complete TINYINT(1) NULL,
    updated_by BIGINT UNSIGNED NOT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_daily_score (daily_assessment_id, student_id),
    CONSTRAINT fk_dascore_assessment FOREIGN KEY (daily_assessment_id) REFERENCES daily_assessments(id) ON DELETE CASCADE,
    CONSTRAINT fk_dascore_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    CONSTRAINT fk_dascore_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE learning_objectives (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    academic_period_id BIGINT UNSIGNED NOT NULL,
    class_id BIGINT UNSIGNED NOT NULL,
    subject_id BIGINT UNSIGNED NOT NULL,
    objective_number SMALLINT UNSIGNED NOT NULL,
    objective_text TEXT NOT NULL,
    UNIQUE KEY uq_objective (academic_period_id, class_id, subject_id, objective_number),
    CONSTRAINT fk_lo_period FOREIGN KEY (academic_period_id) REFERENCES academic_periods(id) ON DELETE CASCADE,
    CONSTRAINT fk_lo_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    CONSTRAINT fk_lo_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE report_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    academic_period_id BIGINT UNSIGNED NOT NULL,
    class_id BIGINT UNSIGNED NOT NULL,
    subject_id BIGINT UNSIGNED NOT NULL,
    teacher_id BIGINT UNSIGNED NULL,
    kktp DECIMAL(5,2) NOT NULL DEFAULT 75,
    UNIQUE KEY uq_report_setting (academic_period_id, class_id, subject_id),
    CONSTRAINT fk_rs_period FOREIGN KEY (academic_period_id) REFERENCES academic_periods(id) ON DELETE CASCADE,
    CONSTRAINT fk_rs_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    CONSTRAINT fk_rs_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    CONSTRAINT fk_rs_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE report_scores (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    report_setting_id BIGINT UNSIGNED NOT NULL,
    student_id BIGINT UNSIGNED NOT NULL,
    sumatif_1 DECIMAL(5,2) NULL,
    sumatif_2 DECIMAL(5,2) NULL,
    sumatif_3 DECIMAL(5,2) NULL,
    sumatif_4 DECIMAL(5,2) NULL,
    psts DECIMAL(5,2) NULL,
    psas_non_test DECIMAL(5,2) NULL,
    psas_test DECIMAL(5,2) NULL,
    final_score DECIMAL(5,2) NULL,
    rounded_score SMALLINT UNSIGNED NULL,
    predicate VARCHAR(30) NULL,
    completion_status VARCHAR(30) NULL,
    strongest_objective TEXT NULL,
    improvement_objective TEXT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_report_score (report_setting_id, student_id),
    CONSTRAINT fk_rscore_setting FOREIGN KEY (report_setting_id) REFERENCES report_settings(id) ON DELETE CASCADE,
    CONSTRAINT fk_rscore_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;