-- =========================================================
-- SIMDATADIK
-- Schema Modul Laboratorium (Fisik + Virtual)
-- File: sql/schema_laboratory.sql
-- =========================================================
--
-- Catatan:
-- - File ini menambahkan struktur inti modul laboratorium.
-- - Menggunakan InnoDB + utf8mb4.
-- - Tabel global seperti schools, users, class_groups, subjects
--   diasumsikan sudah ada di schema inti aplikasi.
-- - Penamaan dan relasi disusun agar konsisten dengan prompt modul.
--
-- =========================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =========================================================
-- 1. MASTER RUANG LAB
-- =========================================================
CREATE TABLE IF NOT EXISTS `lab_rooms` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `lab_type` ENUM(
        'science_physics',
        'science_chemistry',
        'science_biology',
        'computer',
        'language',
        'vocational',
        'other'
    ) NOT NULL DEFAULT 'other',
    `location` VARCHAR(255) DEFAULT NULL,
    `capacity` INT UNSIGNED DEFAULT NULL,
    `head_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_lab_rooms_school_id` (`school_id`),
    KEY `idx_lab_rooms_lab_type` (`lab_type`),
    KEY `idx_lab_rooms_head_user_id` (`head_user_id`),
    KEY `idx_lab_rooms_school_lab_type` (`school_id`, `lab_type`),
    CONSTRAINT `fk_lab_rooms_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_rooms_head_user`
        FOREIGN KEY (`head_user_id`) REFERENCES `users` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 2. KATEGORI ALAT LAB
-- =========================================================
CREATE TABLE IF NOT EXISTS `lab_equipment_categories` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_lab_equipment_categories_school_name` (`school_id`, `name`),
    KEY `idx_lab_equipment_categories_school_id` (`school_id`),
    CONSTRAINT `fk_lab_equipment_categories_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 3. ALAT LAB
-- =========================================================
CREATE TABLE IF NOT EXISTS `lab_equipment` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `lab_room_id` BIGINT UNSIGNED DEFAULT NULL,
    `category_id` BIGINT UNSIGNED DEFAULT NULL,
    `code` VARCHAR(100) NOT NULL,
    `name` VARCHAR(200) NOT NULL,
    `brand` VARCHAR(150) DEFAULT NULL,
    `model` VARCHAR(150) DEFAULT NULL,
    `purchase_date` DATE DEFAULT NULL,
    `purchase_price` DECIMAL(15,2) DEFAULT NULL,
    `condition_status` ENUM(
        'good',
        'needs_maintenance',
        'damaged',
        'lost'
    ) NOT NULL DEFAULT 'good',
    `quantity` INT UNSIGNED NOT NULL DEFAULT 1,
    `description` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_lab_equipment_school_code` (`school_id`, `code`),
    KEY `idx_lab_equipment_school_id` (`school_id`),
    KEY `idx_lab_equipment_lab_room_id` (`lab_room_id`),
    KEY `idx_lab_equipment_category_id` (`category_id`),
    KEY `idx_lab_equipment_condition_status` (`condition_status`),
    KEY `idx_lab_equipment_name` (`name`),
    CONSTRAINT `fk_lab_equipment_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_equipment_lab_room`
        FOREIGN KEY (`lab_room_id`) REFERENCES `lab_rooms` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_equipment_category`
        FOREIGN KEY (`category_id`) REFERENCES `lab_equipment_categories` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 4. RIWAYAT PERAWATAN ALAT
-- =========================================================
CREATE TABLE IF NOT EXISTS `lab_equipment_maintenance` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `equipment_id` BIGINT UNSIGNED NOT NULL,
    `maintenance_date` DATE NOT NULL,
    `maintenance_type` ENUM(
        'routine',
        'repair',
        'inspection'
    ) NOT NULL DEFAULT 'routine',
    `description` TEXT DEFAULT NULL,
    `performed_by` VARCHAR(255) DEFAULT NULL,
    `cost` DECIMAL(15,2) DEFAULT NULL,
    `next_due_date` DATE DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_lab_equipment_maintenance_equipment_id` (`equipment_id`),
    KEY `idx_lab_equipment_maintenance_date` (`maintenance_date`),
    KEY `idx_lab_equipment_maintenance_next_due_date` (`next_due_date`),
    CONSTRAINT `fk_lab_equipment_maintenance_equipment`
        FOREIGN KEY (`equipment_id`) REFERENCES `lab_equipment` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 5. BAHAN HABIS PAKAI
-- =========================================================
CREATE TABLE IF NOT EXISTS `lab_materials` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `lab_room_id` BIGINT UNSIGNED DEFAULT NULL,
    `name` VARCHAR(200) NOT NULL,
    `unit` VARCHAR(50) NOT NULL,
    `current_stock` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `min_stock` DECIMAL(15,2) DEFAULT NULL,
    `storage_location` VARCHAR(255) DEFAULT NULL,
    `hazard_info` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_lab_materials_school_id` (`school_id`),
    KEY `idx_lab_materials_lab_room_id` (`lab_room_id`),
    KEY `idx_lab_materials_name` (`name`),
    KEY `idx_lab_materials_stock_alert` (`school_id`, `current_stock`, `min_stock`),
    CONSTRAINT `fk_lab_materials_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_materials_lab_room`
        FOREIGN KEY (`lab_room_id`) REFERENCES `lab_rooms` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 6. MUTASI STOK BAHAN
-- =========================================================
CREATE TABLE IF NOT EXISTS `lab_material_transactions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `material_id` BIGINT UNSIGNED NOT NULL,
    `transaction_type` ENUM(
        'in',
        'out',
        'adjustment'
    ) NOT NULL,
    `quantity` DECIMAL(15,2) NOT NULL,
    `transaction_date` DATETIME NOT NULL,
    `description` TEXT DEFAULT NULL,
    `related_practicum_id` BIGINT UNSIGNED DEFAULT NULL,
    `created_by` BIGINT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_lab_material_transactions_material_id` (`material_id`),
    KEY `idx_lab_material_transactions_type` (`transaction_type`),
    KEY `idx_lab_material_transactions_date` (`transaction_date`),
    KEY `idx_lab_material_transactions_related_practicum_id` (`related_practicum_id`),
    KEY `idx_lab_material_transactions_created_by` (`created_by`),
    CONSTRAINT `fk_lab_material_transactions_material`
        FOREIGN KEY (`material_id`) REFERENCES `lab_materials` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_material_transactions_created_by`
        FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 7. TEMPLATE PRAKTIKUM
-- =========================================================
CREATE TABLE IF NOT EXISTS `lab_practicum_templates` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `lab_room_id` BIGINT UNSIGNED DEFAULT NULL,
    `subject_id` BIGINT UNSIGNED DEFAULT NULL,
    `title` VARCHAR(200) NOT NULL,
    `objective` TEXT DEFAULT NULL,
    `competency_text` TEXT DEFAULT NULL,
    `required_equipment` TEXT DEFAULT NULL,
    `required_materials` TEXT DEFAULT NULL,
    `procedure_steps` LONGTEXT NOT NULL,
    `safety_instructions` LONGTEXT DEFAULT NULL,
    `created_by` BIGINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_lab_practicum_templates_school_id` (`school_id`),
    KEY `idx_lab_practicum_templates_lab_room_id` (`lab_room_id`),
    KEY `idx_lab_practicum_templates_subject_id` (`subject_id`),
    KEY `idx_lab_practicum_templates_created_by` (`created_by`),
    KEY `idx_lab_practicum_templates_title` (`title`),
    CONSTRAINT `fk_lab_practicum_templates_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_practicum_templates_lab_room`
        FOREIGN KEY (`lab_room_id`) REFERENCES `lab_rooms` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_practicum_templates_subject`
        FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_practicum_templates_created_by`
        FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 8. JADWAL / BOOKING LAB
-- =========================================================
CREATE TABLE IF NOT EXISTS `lab_schedules` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `lab_room_id` BIGINT UNSIGNED NOT NULL,
    `class_group_id` BIGINT UNSIGNED DEFAULT NULL,
    `subject_id` BIGINT UNSIGNED DEFAULT NULL,
    `teacher_id` BIGINT UNSIGNED NOT NULL,
    `requested_by` BIGINT UNSIGNED DEFAULT NULL,
    `scheduled_date` DATE NOT NULL,
    `start_time` TIME NOT NULL,
    `end_time` TIME NOT NULL,
    `activity_type` ENUM(
        'practicum',
        'exam',
        'training',
        'other'
    ) NOT NULL DEFAULT 'practicum',
    `practicum_template_id` BIGINT UNSIGNED DEFAULT NULL,
    `status` ENUM(
        'requested',
        'approved',
        'rejected',
        'cancelled',
        'completed'
    ) NOT NULL DEFAULT 'requested',
    `reason` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_lab_schedules_school_id` (`school_id`),
    KEY `idx_lab_schedules_lab_room_id` (`lab_room_id`),
    KEY `idx_lab_schedules_class_group_id` (`class_group_id`),
    KEY `idx_lab_schedules_subject_id` (`subject_id`),
    KEY `idx_lab_schedules_teacher_id` (`teacher_id`),
    KEY `idx_lab_schedules_requested_by` (`requested_by`),
    KEY `idx_lab_schedules_template_id` (`practicum_template_id`),
    KEY `idx_lab_schedules_status` (`status`),
    KEY `idx_lab_schedules_date` (`scheduled_date`),
    KEY `idx_lab_schedules_booking_lookup` (`lab_room_id`, `scheduled_date`, `start_time`, `end_time`, `status`),
    CONSTRAINT `fk_lab_schedules_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_schedules_lab_room`
        FOREIGN KEY (`lab_room_id`) REFERENCES `lab_rooms` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_schedules_class_group`
        FOREIGN KEY (`class_group_id`) REFERENCES `class_groups` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_schedules_subject`
        FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_schedules_teacher`
        FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_schedules_requested_by`
        FOREIGN KEY (`requested_by`) REFERENCES `users` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_schedules_practicum_template`
        FOREIGN KEY (`practicum_template_id`) REFERENCES `lab_practicum_templates` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 9. SESI PRAKTIKUM
-- =========================================================
CREATE TABLE IF NOT EXISTS `lab_practicum_sessions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `lab_schedule_id` BIGINT UNSIGNED NOT NULL,
    `practicum_template_id` BIGINT UNSIGNED DEFAULT NULL,
    `teacher_id` BIGINT UNSIGNED NOT NULL,
    `notes` TEXT DEFAULT NULL,
    `status` ENUM(
        'planned',
        'in_progress',
        'completed'
    ) NOT NULL DEFAULT 'planned',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_lab_practicum_sessions_schedule_id` (`lab_schedule_id`),
    KEY `idx_lab_practicum_sessions_template_id` (`practicum_template_id`),
    KEY `idx_lab_practicum_sessions_teacher_id` (`teacher_id`),
    KEY `idx_lab_practicum_sessions_status` (`status`),
    CONSTRAINT `fk_lab_practicum_sessions_schedule`
        FOREIGN KEY (`lab_schedule_id`) REFERENCES `lab_schedules` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_practicum_sessions_template`
        FOREIGN KEY (`practicum_template_id`) REFERENCES `lab_practicum_templates` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_practicum_sessions_teacher`
        FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 10. CATATAN / PARTISIPASI SISWA PADA PRAKTIKUM
-- =========================================================
CREATE TABLE IF NOT EXISTS `lab_practicum_records` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `practicum_session_id` BIGINT UNSIGNED NOT NULL,
    `student_id` BIGINT UNSIGNED NOT NULL,
    `attendance_status` ENUM(
        'present',
        'absent',
        'late'
    ) NOT NULL DEFAULT 'present',
    `observation_notes` TEXT DEFAULT NULL,
    `score` DECIMAL(5,2) DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_lab_practicum_records_session_student` (`practicum_session_id`, `student_id`),
    KEY `idx_lab_practicum_records_student_id` (`student_id`),
    KEY `idx_lab_practicum_records_attendance_status` (`attendance_status`),
    CONSTRAINT `fk_lab_practicum_records_session`
        FOREIGN KEY (`practicum_session_id`) REFERENCES `lab_practicum_sessions` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_practicum_records_student`
        FOREIGN KEY (`student_id`) REFERENCES `users` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 11. INSIDEN KESELAMATAN LAB
-- =========================================================
CREATE TABLE IF NOT EXISTS `lab_safety_incidents` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `lab_room_id` BIGINT UNSIGNED DEFAULT NULL,
    `incident_date` DATETIME NOT NULL,
    `incident_type` ENUM(
        'spill',
        'injury',
        'equipment_damage',
        'near_miss',
        'other'
    ) NOT NULL DEFAULT 'other',
    `description` TEXT NOT NULL,
    `involved_users` TEXT DEFAULT NULL,
    `action_taken` TEXT DEFAULT NULL,
    `reported_by` BIGINT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_lab_safety_incidents_school_id` (`school_id`),
    KEY `idx_lab_safety_incidents_lab_room_id` (`lab_room_id`),
    KEY `idx_lab_safety_incidents_incident_date` (`incident_date`),
    KEY `idx_lab_safety_incidents_incident_type` (`incident_type`),
    KEY `idx_lab_safety_incidents_reported_by` (`reported_by`),
    CONSTRAINT `fk_lab_safety_incidents_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_safety_incidents_lab_room`
        FOREIGN KEY (`lab_room_id`) REFERENCES `lab_rooms` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_safety_incidents_reported_by`
        FOREIGN KEY (`reported_by`) REFERENCES `users` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 12. PELATIHAN K3 / SAFETY TRAINING
-- =========================================================
CREATE TABLE IF NOT EXISTS `lab_safety_trainings` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `training_date` DATE NOT NULL,
    `trainer_name` VARCHAR(255) DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_lab_safety_trainings_school_id` (`school_id`),
    KEY `idx_lab_safety_trainings_training_date` (`training_date`),
    KEY `idx_lab_safety_trainings_title` (`title`),
    CONSTRAINT `fk_lab_safety_trainings_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 13. PESERTA PELATIHAN K3
-- =========================================================
CREATE TABLE IF NOT EXISTS `lab_safety_training_participants` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `training_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `attendance_status` ENUM(
        'present',
        'absent'
    ) NOT NULL DEFAULT 'present',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_lab_safety_training_participants_training_user` (`training_id`, `user_id`),
    KEY `idx_lab_safety_training_participants_user_id` (`user_id`),
    KEY `idx_lab_safety_training_participants_attendance_status` (`attendance_status`),
    CONSTRAINT `fk_lab_safety_training_participants_training`
        FOREIGN KEY (`training_id`) REFERENCES `lab_safety_trainings` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_safety_training_participants_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 14. MODUL LAB VIRTUAL
-- =========================================================
CREATE TABLE IF NOT EXISTS `lab_virtual_modules` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED DEFAULT NULL,
    `title` VARCHAR(200) NOT NULL,
    `lab_type` ENUM(
        'science_physics',
        'science_chemistry',
        'science_biology',
        'computer',
        'language',
        'vocational',
        'other'
    ) NOT NULL DEFAULT 'other',
    `subject_id` BIGINT UNSIGNED DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `content_url` VARCHAR(255) NOT NULL,
    `created_by` BIGINT UNSIGNED DEFAULT 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,
    PRIMARY KEY (`id`),
    KEY `idx_lab_virtual_modules_school_id` (`school_id`),
    KEY `idx_lab_virtual_modules_lab_type` (`lab_type`),
    KEY `idx_lab_virtual_modules_subject_id` (`subject_id`),
    KEY `idx_lab_virtual_modules_created_by` (`created_by`),
    KEY `idx_lab_virtual_modules_is_active` (`is_active`),
    KEY `idx_lab_virtual_modules_school_active_type` (`school_id`, `is_active`, `lab_type`),
    CONSTRAINT `fk_lab_virtual_modules_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_virtual_modules_subject`
        FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_virtual_modules_created_by`
        FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 15. LOG PENGGUNAAN LAB VIRTUAL
-- =========================================================
CREATE TABLE IF NOT EXISTS `lab_virtual_usage_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `virtual_module_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `accessed_at` DATETIME NOT NULL,
    `duration_seconds` INT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_lab_virtual_usage_logs_virtual_module_id` (`virtual_module_id`),
    KEY `idx_lab_virtual_usage_logs_user_id` (`user_id`),
    KEY `idx_lab_virtual_usage_logs_accessed_at` (`accessed_at`),
    KEY `idx_lab_virtual_usage_logs_module_user_accessed` (`virtual_module_id`, `user_id`, `accessed_at`),
    CONSTRAINT `fk_lab_virtual_usage_logs_virtual_module`
        FOREIGN KEY (`virtual_module_id`) REFERENCES `lab_virtual_modules` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_lab_virtual_usage_logs_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- FK TAMBAHAN UNTUK TRANSAKSI BAHAN
-- Didefinisikan setelah tabel sesi praktikum tersedia
-- =========================================================
ALTER TABLE `lab_material_transactions`
    ADD CONSTRAINT `fk_lab_material_transactions_related_practicum`
        FOREIGN KEY (`related_practicum_id`) REFERENCES `lab_practicum_sessions` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE;

SET FOREIGN_KEY_CHECKS = 1;