-- ========================================================= -- SIMDATADIK - Full Core Schema (All Main Modules) -- File : sql/schema_core.sql -- Descr: Tabel inti untuk seluruh modul utama SIMDATADIK -- Target: MySQL 8+, InnoDB, utf8mb4 -- ========================================================= -- (Opsional) Buat database jika belum ada CREATE DATABASE IF NOT EXISTS simdatadik CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE simdatadik; -- Matikan foreign key checks sementara untuk proses DROP SET FOREIGN_KEY_CHECKS = 0; -- ========================================================= -- DROP TABLES (untuk keperluan development/reset) -- ========================================================= DROP TABLE IF EXISTS notifications; DROP TABLE IF EXISTS activity_logs; DROP TABLE IF EXISTS content_post_files; DROP TABLE IF EXISTS content_posts; DROP TABLE IF EXISTS content_categories; DROP TABLE IF EXISTS lab_practicum_participants; DROP TABLE IF EXISTS lab_practicums; DROP TABLE IF EXISTS lab_schedules; DROP TABLE IF EXISTS lab_assets; DROP TABLE IF EXISTS lab_rooms; DROP TABLE IF EXISTS library_loans; DROP TABLE IF EXISTS library_items; DROP TABLE IF EXISTS library_collections; DROP TABLE IF EXISTS library_categories; DROP TABLE IF EXISTS library_members; DROP TABLE IF EXISTS ppdb_documents; DROP TABLE IF EXISTS ppdb_applicants; DROP TABLE IF EXISTS ppdb_periods; DROP TABLE IF EXISTS grade_book_scores; DROP TABLE IF EXISTS grade_book_items; DROP TABLE IF EXISTS grade_books; DROP TABLE IF EXISTS exam_answers; DROP TABLE IF EXISTS exam_participants; DROP TABLE IF EXISTS exam_question_sets; DROP TABLE IF EXISTS exams; DROP TABLE IF EXISTS exam_question_options; DROP TABLE IF EXISTS exam_questions; DROP TABLE IF EXISTS exam_question_banks; DROP TABLE IF EXISTS lms_discussion_posts; DROP TABLE IF EXISTS lms_discussions; DROP TABLE IF EXISTS lms_assignment_submissions; DROP TABLE IF EXISTS lms_assignments; DROP TABLE IF EXISTS lms_materials; DROP TABLE IF EXISTS lms_lessons; DROP TABLE IF EXISTS lms_courses; DROP TABLE IF EXISTS academic_calendar_events; DROP TABLE IF EXISTS teaching_schedules; DROP TABLE IF EXISTS class_group_subjects; DROP TABLE IF EXISTS subjects; DROP TABLE IF EXISTS student_enrollments; DROP TABLE IF EXISTS class_groups; DROP TABLE IF EXISTS grade_levels; DROP TABLE IF EXISTS academic_terms; DROP TABLE IF EXISTS academic_years; DROP TABLE IF EXISTS staff_profiles; DROP TABLE IF EXISTS teacher_profiles; DROP TABLE IF EXISTS student_profiles; DROP TABLE IF EXISTS role_permissions; DROP TABLE IF EXISTS permissions; DROP TABLE IF EXISTS user_school_roles; DROP TABLE IF EXISTS user_roles; DROP TABLE IF EXISTS files; DROP TABLE IF EXISTS schools; DROP TABLE IF EXISTS users; DROP TABLE IF EXISTS roles; SET FOREIGN_KEY_CHECKS = 1; -- ========================================================= -- 1. RBAC & USER MANAGEMENT -- ========================================================= -- Tabel: roles CREATE TABLE roles ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, slug VARCHAR(100) NOT NULL, scope ENUM('global', 'school') NOT NULL DEFAULT 'school', description VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_roles_slug (slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: permissions (opsional untuk RBAC lebih granular) CREATE TABLE permissions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, slug VARCHAR(150) NOT NULL, module VARCHAR(100) NULL, description VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_permissions_slug (slug), KEY idx_permissions_module (module) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: users CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, full_name VARCHAR(150) NOT NULL, email VARCHAR(150) NOT NULL, username VARCHAR(100) NOT NULL, password_hash VARCHAR(255) NOT NULL, default_role_id BIGINT UNSIGNED NULL, status ENUM('pending', 'active', 'suspended', 'deleted') NOT NULL DEFAULT 'pending', email_verified_at DATETIME NULL, last_login_at DATETIME NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_users_default_role FOREIGN KEY (default_role_id) REFERENCES roles(id) ON UPDATE CASCADE ON DELETE SET NULL, UNIQUE KEY uq_users_email (email), UNIQUE KEY uq_users_username (username), KEY idx_users_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: schools CREATE TABLE schools ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, npsn VARCHAR(20) NULL, jenjang ENUM('SD', 'SMP', 'SMA', 'SMK', 'SLB', 'LAINNYA') NOT NULL DEFAULT 'LAINNYA', type ENUM('NEGERI', 'SWASTA', 'LAINNYA') NOT NULL DEFAULT 'LAINNYA', status ENUM('pending', 'approved', 'rejected', 'inactive') NOT NULL DEFAULT 'pending', registration_code VARCHAR(64) NOT NULL, logo_path VARCHAR(255) NULL, province VARCHAR(100) NULL, city VARCHAR(100) NULL, district VARCHAR(100) NULL, address VARCHAR(255) NULL, phone VARCHAR(50) NULL, website VARCHAR(150) NULL, approved_by_user_id BIGINT UNSIGNED NULL, approved_at DATETIME NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_schools_approved_by_user FOREIGN KEY (approved_by_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL, UNIQUE KEY uq_schools_npsn (npsn), UNIQUE KEY uq_schools_registration_code (registration_code), KEY idx_schools_status (status), KEY idx_schools_city (city), KEY idx_schools_jenjang (jenjang) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: user_roles (role GLOBAL, tidak terkait sekolah) CREATE TABLE user_roles ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, role_id BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_user_roles_user_role (user_id, role_id), KEY idx_user_roles_role (role_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: user_school_roles (role per sekolah) CREATE TABLE user_school_roles ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, school_id BIGINT UNSIGNED NOT NULL, role_id BIGINT UNSIGNED NOT NULL, is_primary TINYINT(1) NOT NULL DEFAULT 0, status ENUM('active', 'inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_usr_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_usr_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_usr_role FOREIGN KEY (role_id) REFERENCES roles(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_usr_user_school_role (user_id, school_id, role_id), KEY idx_usr_user (user_id), KEY idx_usr_school (school_id), KEY idx_usr_role (role_id), KEY idx_usr_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: role_permissions (relasi roles - permissions) CREATE TABLE role_permissions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, role_id BIGINT UNSIGNED NOT NULL, permission_id BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_role_permission (role_id, permission_id), KEY idx_role_permissions_permission (permission_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================= -- 2. Tabel FILE UPLOAD GENERIK -- ========================================================= CREATE TABLE files ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NULL, uploader_user_id BIGINT UNSIGNED NULL, original_name VARCHAR(255) NOT NULL, file_path VARCHAR(255) NOT NULL, mime_type VARCHAR(100) NULL, size_bytes BIGINT UNSIGNED NULL, hash_sha1 CHAR(40) NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_files_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_files_uploader FOREIGN KEY (uploader_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL, KEY idx_files_school (school_id), KEY idx_files_uploader (uploader_user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================= -- 3. PROFIL PENGGUNA (Guru/Siswa/Staff) -- ========================================================= -- Tabel: student_profiles CREATE TABLE student_profiles ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, school_id BIGINT UNSIGNED NOT NULL, nisn VARCHAR(20) NULL, nis VARCHAR(20) NULL, gender ENUM('L', 'P') NULL, birth_place VARCHAR(100) NULL, birth_date DATE NULL, religion VARCHAR(50) NULL, address VARCHAR(255) NULL, phone VARCHAR(50) NULL, admission_year VARCHAR(9) NULL, -- contoh: 2024/2025 father_name VARCHAR(150) NULL, mother_name VARCHAR(150) NULL, guardian_name VARCHAR(150) NULL, status ENUM('active','graduated','moved','dropout') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_student_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_student_profiles_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_student_user_school (user_id, school_id), KEY idx_student_school_status (school_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: teacher_profiles CREATE TABLE teacher_profiles ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, school_id BIGINT UNSIGNED NOT NULL, nuptk VARCHAR(30) NULL, nip VARCHAR(30) NULL, gender ENUM('L','P') NULL, birth_place VARCHAR(100) NULL, birth_date DATE NULL, highest_education VARCHAR(50) NULL, major VARCHAR(100) NULL, address VARCHAR(255) NULL, phone VARCHAR(50) NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_teacher_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_teacher_profiles_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_teacher_user_school (user_id, school_id), KEY idx_teacher_school_status (school_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: staff_profiles CREATE TABLE staff_profiles ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, school_id BIGINT UNSIGNED NOT NULL, staff_type VARCHAR(100) NULL, -- contoh: TU, Pustakawan, Laboran, Bendahara gender ENUM('L','P') NULL, birth_place VARCHAR(100) NULL, birth_date DATE NULL, address VARCHAR(255) NULL, phone VARCHAR(50) NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_staff_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_staff_profiles_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_staff_user_school (user_id, school_id), KEY idx_staff_school_status (school_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================= -- 4. CORE SIS: Tahun Ajaran, Rombel, Mapel, Jadwal -- ========================================================= -- Tabel: academic_years CREATE TABLE academic_years ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, name VARCHAR(20) NOT NULL, -- contoh: 2024/2025 start_date DATE NULL, end_date DATE NULL, is_active TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_academic_years_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_ay_school_name (school_id, name), KEY idx_ay_school_active (school_id, is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: academic_terms (semester) CREATE TABLE academic_terms ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, academic_year_id BIGINT UNSIGNED NOT NULL, name VARCHAR(50) NOT NULL, -- contoh: Ganjil, Genap start_date DATE NULL, end_date DATE NULL, is_active TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_academic_terms_year FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_at_year_name (academic_year_id, name), KEY idx_at_year_active (academic_year_id, is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: grade_levels (tingkat/jenjang per sekolah) CREATE TABLE grade_levels ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, name VARCHAR(50) NOT NULL, -- contoh: 1, 2, 3 atau X, XI, XII order_no INT NOT NULL DEFAULT 1, description VARCHAR(100) NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_grade_levels_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_grade_school_name (school_id, name), KEY idx_grade_school_order (school_id, order_no) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: class_groups (rombongan belajar) CREATE TABLE class_groups ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, academic_year_id BIGINT UNSIGNED NOT NULL, grade_level_id BIGINT UNSIGNED NOT NULL, name VARCHAR(50) NOT NULL, -- contoh: 7A, X IPA 1 alias VARCHAR(50) NULL, homeroom_teacher_user_id BIGINT UNSIGNED NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_class_groups_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_class_groups_ay FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_class_groups_grade FOREIGN KEY (grade_level_id) REFERENCES grade_levels(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_class_groups_homeroom FOREIGN KEY (homeroom_teacher_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL, UNIQUE KEY uq_class_school_ay_name (school_id, academic_year_id, name), KEY idx_class_school_status (school_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: student_enrollments (buku induk / riwayat rombel per tahun) CREATE TABLE student_enrollments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, student_profile_id BIGINT UNSIGNED NOT NULL, academic_year_id BIGINT UNSIGNED NOT NULL, class_group_id BIGINT UNSIGNED NOT NULL, enrollment_date DATE NULL, status ENUM('active','promoted','repeated','moved','graduated') NOT NULL DEFAULT 'active', notes VARCHAR(255) NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_enroll_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_enroll_ay FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_enroll_class FOREIGN KEY (class_group_id) REFERENCES class_groups(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_enroll_student_ay (student_profile_id, academic_year_id), KEY idx_enroll_class (class_group_id), KEY idx_enroll_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: subjects (mapel) CREATE TABLE subjects ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NULL, -- NULL: template global code VARCHAR(50) NULL, name VARCHAR(150) NOT NULL, group_name VARCHAR(100) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_subjects_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE SET NULL, KEY idx_subject_school (school_id), KEY idx_subject_name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: class_group_subjects (mapel dalam rombel + guru pengampu) CREATE TABLE class_group_subjects ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, class_group_id BIGINT UNSIGNED NOT NULL, subject_id BIGINT UNSIGNED NOT NULL, teacher_user_id BIGINT UNSIGNED NULL, weekly_hours INT NOT NULL DEFAULT 0, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_cgs_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_cgs_class FOREIGN KEY (class_group_id) REFERENCES class_groups(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_cgs_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_cgs_teacher FOREIGN KEY (teacher_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL, UNIQUE KEY uq_cgs_class_subject (class_group_id, subject_id), KEY idx_cgs_teacher (teacher_user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: teaching_schedules (jadwal pelajaran) CREATE TABLE teaching_schedules ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, class_group_subject_id BIGINT UNSIGNED NOT NULL, day_of_week TINYINT NOT NULL, -- 1=Senin ... 7=Minggu start_time TIME NOT NULL, end_time TIME NOT NULL, room VARCHAR(50) NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_ts_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_ts_cgs FOREIGN KEY (class_group_subject_id) REFERENCES class_group_subjects(id) ON UPDATE CASCADE ON DELETE CASCADE, KEY idx_ts_school_day (school_id, day_of_week) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: academic_calendar_events CREATE TABLE academic_calendar_events ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, academic_year_id BIGINT UNSIGNED NOT NULL, academic_term_id BIGINT UNSIGNED NULL, title VARCHAR(200) NOT NULL, description TEXT NULL, event_type VARCHAR(50) NULL, -- contoh: "libur", "ujian", "rapor" start_date DATE NOT NULL, end_date DATE NOT NULL, is_holiday TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_ace_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_ace_ay FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_ace_term FOREIGN KEY (academic_term_id) REFERENCES academic_terms(id) ON UPDATE CASCADE ON DELETE SET NULL, KEY idx_ace_school_date (school_id, start_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================= -- 5. E-LEARNING (LMS) -- ========================================================= -- Tabel: lms_courses (kelas online) CREATE TABLE lms_courses ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, class_group_id BIGINT UNSIGNED NULL, subject_id BIGINT UNSIGNED NULL, teacher_user_id BIGINT UNSIGNED NOT NULL, title VARCHAR(200) NOT NULL, description TEXT NULL, status ENUM('draft','active','archived') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_lmsc_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_lmsc_class FOREIGN KEY (class_group_id) REFERENCES class_groups(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_lmsc_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_lmsc_teacher FOREIGN KEY (teacher_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, KEY idx_lmsc_school (school_id), KEY idx_lmsc_teacher (teacher_user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: lms_lessons (pertemuan/modul) CREATE TABLE lms_lessons ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, course_id BIGINT UNSIGNED NOT NULL, title VARCHAR(200) NOT NULL, description TEXT NULL, sequence_no INT NOT NULL DEFAULT 1, is_published TINYINT(1) NOT NULL DEFAULT 0, published_at DATETIME NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_lmsl_course FOREIGN KEY (course_id) REFERENCES lms_courses(id) ON UPDATE CASCADE ON DELETE CASCADE, KEY idx_lmsl_course_seq (course_id, sequence_no) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: lms_materials (materi per lesson) CREATE TABLE lms_materials ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, lesson_id BIGINT UNSIGNED NOT NULL, type ENUM('text','file','video','link') NOT NULL DEFAULT 'text', title VARCHAR(200) NOT NULL, content TEXT NULL, -- untuk teks / embed code / description file_id BIGINT UNSIGNED NULL, external_url VARCHAR(255) NULL, order_no INT NOT NULL DEFAULT 1, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_lmsm_lesson FOREIGN KEY (lesson_id) REFERENCES lms_lessons(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_lmsm_file FOREIGN KEY (file_id) REFERENCES files(id) ON UPDATE CASCADE ON DELETE SET NULL, KEY idx_lmsm_lesson_order (lesson_id, order_no) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: lms_assignments (tugas) CREATE TABLE lms_assignments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, course_id BIGINT UNSIGNED NOT NULL, lesson_id BIGINT UNSIGNED NULL, title VARCHAR(200) NOT NULL, description TEXT NULL, due_date DATETIME NULL, max_score DECIMAL(5,2) NOT NULL DEFAULT 100.00, allow_late TINYINT(1) NOT NULL DEFAULT 0, status ENUM('draft','published','closed') NOT NULL DEFAULT 'published', created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_lmsa_course FOREIGN KEY (course_id) REFERENCES lms_courses(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_lmsa_lesson FOREIGN KEY (lesson_id) REFERENCES lms_lessons(id) ON UPDATE CASCADE ON DELETE SET NULL, KEY idx_lmsa_course (course_id), KEY idx_lmsa_due_date (due_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: lms_assignment_submissions (pengumpulan tugas) CREATE TABLE lms_assignment_submissions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, assignment_id BIGINT UNSIGNED NOT NULL, student_user_id BIGINT UNSIGNED NOT NULL, submitted_at DATETIME NULL, answer_text TEXT NULL, file_id BIGINT UNSIGNED NULL, score DECIMAL(5,2) NULL, graded_by_user_id BIGINT UNSIGNED NULL, graded_at DATETIME NULL, status ENUM('submitted','graded','returned') NOT NULL DEFAULT 'submitted', created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_lmsas_assignment FOREIGN KEY (assignment_id) REFERENCES lms_assignments(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_lmsas_student FOREIGN KEY (student_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_lmsas_file FOREIGN KEY (file_id) REFERENCES files(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_lmsas_graded_by FOREIGN KEY (graded_by_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL, UNIQUE KEY uq_lmsas_assignment_student (assignment_id, student_user_id), KEY idx_lmsas_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- (Opsional) Forum Diskusi CREATE TABLE lms_discussions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, course_id BIGINT UNSIGNED NOT NULL, title VARCHAR(200) NOT NULL, is_closed TINYINT(1) NOT NULL DEFAULT 0, created_by_user_id BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_lmsd_course FOREIGN KEY (course_id) REFERENCES lms_courses(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_lmsd_creator FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, KEY idx_lmsd_course (course_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE lms_discussion_posts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, discussion_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, parent_post_id BIGINT UNSIGNED NULL, content TEXT NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_lmsdp_disc FOREIGN KEY (discussion_id) REFERENCES lms_discussions(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_lmsdp_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_lmsdp_parent FOREIGN KEY (parent_post_id) REFERENCES lms_discussion_posts(id) ON UPDATE CASCADE ON DELETE SET NULL, KEY idx_lmsdp_disc (discussion_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================= -- 6. E-UJIAN / ASSESSMENT -- ========================================================= -- Tabel: exam_question_banks CREATE TABLE exam_question_banks ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, name VARCHAR(200) NOT NULL, description TEXT NULL, subject_id BIGINT UNSIGNED NULL, grade_level_id BIGINT UNSIGNED NULL, created_by_user_id BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_eqb_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_eqb_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_eqb_grade FOREIGN KEY (grade_level_id) REFERENCES grade_levels(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_eqb_creator FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, KEY idx_eqb_school (school_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: exam_questions CREATE TABLE exam_questions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bank_id BIGINT UNSIGNED NOT NULL, question_type ENUM('PG','ESSAY') NOT NULL DEFAULT 'PG', question_text TEXT NOT NULL, explanation TEXT NULL, default_score DECIMAL(5,2) NOT NULL DEFAULT 1.00, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_eq_bank FOREIGN KEY (bank_id) REFERENCES exam_question_banks(id) ON UPDATE CASCADE ON DELETE CASCADE, KEY idx_eq_bank (bank_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: exam_question_options (pilihan PG) CREATE TABLE exam_question_options ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, question_id BIGINT UNSIGNED NOT NULL, option_label VARCHAR(10) NULL, -- A, B, C, D option_text TEXT NOT NULL, is_correct TINYINT(1) NOT NULL DEFAULT 0, order_no INT NOT NULL DEFAULT 1, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_eqo_question FOREIGN KEY (question_id) REFERENCES exam_questions(id) ON UPDATE CASCADE ON DELETE CASCADE, KEY idx_eqo_question (question_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: exams (ujian) CREATE TABLE exams ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, name VARCHAR(200) NOT NULL, description TEXT NULL, subject_id BIGINT UNSIGNED NULL, class_group_id BIGINT UNSIGNED NULL, academic_term_id BIGINT UNSIGNED NULL, start_time DATETIME NOT NULL, end_time DATETIME NOT NULL, duration_minutes INT NOT NULL, total_score DECIMAL(6,2) NULL, question_shuffle TINYINT(1) NOT NULL DEFAULT 1, status ENUM('draft','scheduled','ongoing','finished','archived') NOT NULL DEFAULT 'scheduled', created_by_user_id BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_exams_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_exams_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_exams_class FOREIGN KEY (class_group_id) REFERENCES class_groups(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_exams_term FOREIGN KEY (academic_term_id) REFERENCES academic_terms(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_exams_creator FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, KEY idx_exams_school (school_id), KEY idx_exams_time (start_time, end_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: exam_question_sets (soal yang dipakai di ujian) CREATE TABLE exam_question_sets ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, exam_id BIGINT UNSIGNED NOT NULL, question_id BIGINT UNSIGNED NOT NULL, sequence_no INT NOT NULL DEFAULT 1, weight DECIMAL(5,2) NOT NULL DEFAULT 1.00, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_eqs_exam FOREIGN KEY (exam_id) REFERENCES exams(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_eqs_question FOREIGN KEY (question_id) REFERENCES exam_questions(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_eqs_exam_question (exam_id, question_id), KEY idx_eqs_exam_seq (exam_id, sequence_no) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: exam_participants CREATE TABLE exam_participants ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, exam_id BIGINT UNSIGNED NOT NULL, student_user_id BIGINT UNSIGNED NOT NULL, token VARCHAR(100) NULL, started_at DATETIME NULL, finished_at DATETIME NULL, total_score DECIMAL(6,2) NULL, status ENUM('registered','in_progress','finished','cancelled') NOT NULL DEFAULT 'registered', created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_exp_exam FOREIGN KEY (exam_id) REFERENCES exams(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_exp_student FOREIGN KEY (student_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_exp_exam_student (exam_id, student_user_id), KEY idx_exp_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: exam_answers CREATE TABLE exam_answers ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, exam_participant_id BIGINT UNSIGNED NOT NULL, question_id BIGINT UNSIGNED NOT NULL, selected_option_id BIGINT UNSIGNED NULL, answer_text TEXT NULL, is_correct TINYINT(1) NULL, score DECIMAL(5,2) NULL, answered_at DATETIME NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_exa_participant FOREIGN KEY (exam_participant_id) REFERENCES exam_participants(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_exa_question FOREIGN KEY (question_id) REFERENCES exam_questions(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_exa_option FOREIGN KEY (selected_option_id) REFERENCES exam_question_options(id) ON UPDATE CASCADE ON DELETE SET NULL, UNIQUE KEY uq_exa_participant_question (exam_participant_id, question_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================= -- 7. GRADEBOOK / NILAI -- ========================================================= -- Tabel: grade_books (buku nilai per mapel/kelas) CREATE TABLE grade_books ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, academic_year_id BIGINT UNSIGNED NOT NULL, academic_term_id BIGINT UNSIGNED NULL, class_group_id BIGINT UNSIGNED NOT NULL, subject_id BIGINT UNSIGNED NOT NULL, teacher_user_id BIGINT UNSIGNED NOT NULL, name VARCHAR(200) NOT NULL, description TEXT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_gb_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_gb_ay FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_gb_term FOREIGN KEY (academic_term_id) REFERENCES academic_terms(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_gb_class FOREIGN KEY (class_group_id) REFERENCES class_groups(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_gb_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_gb_teacher FOREIGN KEY (teacher_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, KEY idx_gb_school (school_id), KEY idx_gb_class_subject (class_group_id, subject_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: grade_book_items (komponen nilai: tugas, ujian, dsb) CREATE TABLE grade_book_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, grade_book_id BIGINT UNSIGNED NOT NULL, title VARCHAR(200) NOT NULL, source_type ENUM('assignment','exam','manual') NOT NULL DEFAULT 'manual', source_id BIGINT UNSIGNED NULL, -- id dari tabel asal (misal lms_assignments.id / exams.id) max_score DECIMAL(5,2) NOT NULL DEFAULT 100.00, weight DECIMAL(5,2) NOT NULL DEFAULT 1.00, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_gbi_gb FOREIGN KEY (grade_book_id) REFERENCES grade_books(id) ON UPDATE CASCADE ON DELETE CASCADE, KEY idx_gbi_gb (grade_book_id), KEY idx_gbi_source (source_type, source_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: grade_book_scores (nilai per siswa per item) CREATE TABLE grade_book_scores ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, grade_book_item_id BIGINT UNSIGNED NOT NULL, student_user_id BIGINT UNSIGNED NOT NULL, score DECIMAL(5,2) NOT NULL, notes VARCHAR(255) NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_gbs_item FOREIGN KEY (grade_book_item_id) REFERENCES grade_book_items(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_gbs_student FOREIGN KEY (student_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_gbs_item_student (grade_book_item_id, student_user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================= -- 8. PPDB ONLINE -- ========================================================= -- Tabel: ppdb_periods (gelombang/periode PPDB) CREATE TABLE ppdb_periods ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, academic_year_id BIGINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, quota INT NULL, description TEXT NULL, status ENUM('draft','open','closed','archived') NOT NULL DEFAULT 'draft', created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_ppdbp_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_ppdbp_ay FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON UPDATE CASCADE ON DELETE CASCADE, KEY idx_ppdbp_school (school_id), KEY idx_ppdbp_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: ppdb_applicants (pendaftar) CREATE TABLE ppdb_applicants ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, ppdb_period_id BIGINT UNSIGNED NOT NULL, registration_number VARCHAR(50) NOT NULL, full_name VARCHAR(150) NOT NULL, gender ENUM('L','P') NULL, nisn VARCHAR(20) NULL, nik VARCHAR(20) NULL, birth_place VARCHAR(100) NULL, birth_date DATE NULL, religion VARCHAR(50) NULL, address VARCHAR(255) NULL, phone VARCHAR(50) NULL, email VARCHAR(150) NULL, origin_school VARCHAR(150) NULL, parent_name VARCHAR(150) NULL, parent_phone VARCHAR(50) NULL, desired_grade_level_id BIGINT UNSIGNED NULL, status ENUM('submitted','verified','accepted','rejected','cancelled') NOT NULL DEFAULT 'submitted', verification_notes VARCHAR(255) NULL, accepted_class_group_id BIGINT UNSIGNED NULL, linked_user_id BIGINT UNSIGNED NULL, -- user setelah diterima linked_student_profile_id BIGINT UNSIGNED NULL, -- profil siswa setelah diterima created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_ppdba_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_ppdba_period FOREIGN KEY (ppdb_period_id) REFERENCES ppdb_periods(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_ppdba_grade FOREIGN KEY (desired_grade_level_id) REFERENCES grade_levels(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_ppdba_class FOREIGN KEY (accepted_class_group_id) REFERENCES class_groups(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_ppdba_user FOREIGN KEY (linked_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_ppdba_student_profile FOREIGN KEY (linked_student_profile_id) REFERENCES student_profiles(id) ON UPDATE CASCADE ON DELETE SET NULL, UNIQUE KEY uq_ppdba_school_reg (school_id, registration_number), KEY idx_ppdba_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: ppdb_documents (berkas pendaftar) CREATE TABLE ppdb_documents ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ppdb_applicant_id BIGINT UNSIGNED NOT NULL, document_type VARCHAR(100) NOT NULL, -- contoh: ijazah, kk, akta file_id BIGINT UNSIGNED NOT NULL, is_verified TINYINT(1) NOT NULL DEFAULT 0, verified_at DATETIME NULL, verified_by_user_id BIGINT UNSIGNED NULL, notes VARCHAR(255) NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_ppdbd_applicant FOREIGN KEY (ppdb_applicant_id) REFERENCES ppdb_applicants(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_ppdbd_file FOREIGN KEY (file_id) REFERENCES files(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_ppdbd_verified_by FOREIGN KEY (verified_by_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL, KEY idx_ppdbd_type (document_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================= -- 9. PERPUSTAKAAN -- ========================================================= -- Tabel: library_members CREATE TABLE library_members ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, member_code VARCHAR(50) NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', registered_at DATE NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_libm_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_libm_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_libm_school_user (school_id, user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: library_categories CREATE TABLE library_categories ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, description VARCHAR(255) NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_libc_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_libc_school_name (school_id, name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: library_collections (judul buku/koleksi) CREATE TABLE library_collections ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, category_id BIGINT UNSIGNED NULL, code VARCHAR(50) NULL, title VARCHAR(255) NOT NULL, author VARCHAR(150) NULL, publisher VARCHAR(150) NULL, publish_year VARCHAR(4) NULL, isbn VARCHAR(30) NULL, collection_type ENUM('BOOK','MAGAZINE','DIGITAL','OTHER') NOT NULL DEFAULT 'BOOK', location VARCHAR(100) NULL, description TEXT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_libcol_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_libcol_category FOREIGN KEY (category_id) REFERENCES library_categories(id) ON UPDATE CASCADE ON DELETE SET NULL, KEY idx_libcol_school (school_id), KEY idx_libcol_title (title) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: library_items (eksemplar fisik) CREATE TABLE library_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, collection_id BIGINT UNSIGNED NOT NULL, barcode VARCHAR(50) NULL, status ENUM('available','borrowed','lost','damaged') NOT NULL DEFAULT 'available', created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_libi_collection FOREIGN KEY (collection_id) REFERENCES library_collections(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_libi_barcode (barcode), KEY idx_libi_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: library_loans (peminjaman) CREATE TABLE library_loans ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, library_item_id BIGINT UNSIGNED NOT NULL, library_member_id BIGINT UNSIGNED NOT NULL, loan_date DATE NOT NULL, due_date DATE NOT NULL, return_date DATE NULL, status ENUM('borrowed','returned','overdue','lost') NOT NULL DEFAULT 'borrowed', fine_amount DECIMAL(10,2) NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_libloan_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_libloan_item FOREIGN KEY (library_item_id) REFERENCES library_items(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_libloan_member FOREIGN KEY (library_member_id) REFERENCES library_members(id) ON UPDATE CASCADE ON DELETE CASCADE, KEY idx_libloan_member (library_member_id), KEY idx_libloan_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================= -- 10. LABORATORIUM -- ========================================================= -- Tabel: lab_rooms CREATE TABLE lab_rooms ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, location VARCHAR(150) NULL, description VARCHAR(255) NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_labr_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE KEY uq_labr_school_name (school_id, name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: lab_assets (alat & bahan) CREATE TABLE lab_assets ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, lab_room_id BIGINT UNSIGNED NULL, code VARCHAR(50) NULL, name VARCHAR(150) NOT NULL, category VARCHAR(100) NULL, quantity INT NOT NULL DEFAULT 0, unit VARCHAR(50) NULL, condition_status ENUM('good','fair','poor','broken') NOT NULL DEFAULT 'good', created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_laba_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_laba_room FOREIGN KEY (lab_room_id) REFERENCES lab_rooms(id) ON UPDATE CASCADE ON DELETE SET NULL, KEY idx_laba_school (school_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: lab_schedules (jadwal penggunaan lab) CREATE TABLE lab_schedules ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, lab_room_id BIGINT UNSIGNED NOT NULL, class_group_id BIGINT UNSIGNED NULL, subject_id BIGINT UNSIGNED NULL, teacher_user_id BIGINT UNSIGNED NULL, schedule_date DATE NOT NULL, start_time TIME NOT NULL, end_time TIME NOT NULL, status ENUM('scheduled','done','cancelled') NOT NULL DEFAULT 'scheduled', created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_labs_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_labs_room FOREIGN KEY (lab_room_id) REFERENCES lab_rooms(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_labs_class FOREIGN KEY (class_group_id) REFERENCES class_groups(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_labs_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_labs_teacher FOREIGN KEY (teacher_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL, KEY idx_labs_date (schedule_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: lab_practicums (pencatatan praktikum) CREATE TABLE lab_practicums ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NOT NULL, lab_room_id BIGINT UNSIGNED NOT NULL, class_group_id BIGINT UNSIGNED NOT NULL, subject_id BIGINT UNSIGNED NULL, teacher_user_id BIGINT UNSIGNED NOT NULL, title VARCHAR(200) NOT NULL, objective TEXT NULL, description TEXT NULL, practicum_date DATE NOT NULL, start_time TIME NULL, end_time TIME NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_labp_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_labp_room FOREIGN KEY (lab_room_id) REFERENCES lab_rooms(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_labp_class FOREIGN KEY (class_group_id) REFERENCES class_groups(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_labp_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_labp_teacher FOREIGN KEY (teacher_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, KEY idx_labp_date (practicum_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: lab_practicum_participants CREATE TABLE lab_practicum_participants ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, lab_practicum_id BIGINT UNSIGNED NOT NULL, student_user_id BIGINT UNSIGNED NOT NULL, presence_status ENUM('present','absent') NOT NULL DEFAULT 'present', report_file_id BIGINT UNSIGNED NULL, score DECIMAL(5,2) NULL, notes VARCHAR(255) NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_labpp_practicum FOREIGN KEY (lab_practicum_id) REFERENCES lab_practicums(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_labpp_student FOREIGN KEY (student_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_labpp_file FOREIGN KEY (report_file_id) REFERENCES files(id) ON UPDATE CASCADE ON DELETE SET NULL, UNIQUE KEY uq_labpp_practicum_student (lab_practicum_id, student_user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================= -- 11. KONTEN PUBLIK & MODERASI -- ========================================================= -- Tabel: content_categories CREATE TABLE content_categories ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, slug VARCHAR(100) NOT NULL, name VARCHAR(150) NOT NULL, description VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_cc_slug (slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: content_posts (artikel, materi, event, lomba, dsb) CREATE TABLE content_posts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NULL, -- NULL: global author_user_id BIGINT UNSIGNED NULL, category_id BIGINT UNSIGNED NULL, title VARCHAR(200) NOT NULL, slug VARCHAR(200) NOT NULL, summary TEXT NULL, content LONGTEXT NULL, type ENUM('artikel','materi','soal','event','lomba','pengumuman') NOT NULL DEFAULT 'artikel', status ENUM('draft','pending_review','published','rejected') NOT NULL DEFAULT 'draft', is_public TINYINT(1) NOT NULL DEFAULT 1, allow_comments TINYINT(1) NOT NULL DEFAULT 0, published_at DATETIME NULL, moderated_by_user_id BIGINT UNSIGNED NULL, moderated_at DATETIME NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_cp_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_cp_author FOREIGN KEY (author_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_cp_category FOREIGN KEY (category_id) REFERENCES content_categories(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_cp_moderator FOREIGN KEY (moderated_by_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL, UNIQUE KEY uq_cp_slug (slug), KEY idx_cp_status (status), KEY idx_cp_type (type), KEY idx_cp_school (school_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: content_post_files (lampiran konten) CREATE TABLE content_post_files ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, content_post_id BIGINT UNSIGNED NOT NULL, file_id BIGINT UNSIGNED NOT NULL, caption VARCHAR(255) NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_cpf_post FOREIGN KEY (content_post_id) REFERENCES content_posts(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_cpf_file FOREIGN KEY (file_id) REFERENCES files(id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================= -- 12. SUPPORT: ACTIVITY LOGS & NOTIFICATIONS -- ========================================================= -- Tabel: activity_logs CREATE TABLE activity_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL, action VARCHAR(100) NOT NULL, module VARCHAR(100) NULL, description TEXT NULL, ip_address VARCHAR(45) NULL, user_agent VARCHAR(255) NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_al_school FOREIGN KEY (school_id) REFERENCES schools(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_al_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL, KEY idx_al_school (school_id), KEY idx_al_user (user_id), KEY idx_al_action (action) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tabel: notifications CREATE TABLE notifications ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, title VARCHAR(200) NOT NULL, body TEXT NOT NULL, link_url VARCHAR(255) NULL, is_read TINYINT(1) NOT NULL DEFAULT 0, read_at DATETIME NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, KEY idx_notif_user (user_id), KEY idx_notif_read (is_read) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================= -- END OF SCHEMA -- =========================================================