-- ============================================================
-- 國際生學習管理系統 (ISLMS) - MySQL Database Schema
-- International Student Learning Management System
-- 技術棧：PHP + MySQL + jQuery + Bootstrap
-- 版本：1.0
-- 建立日期：2025-01-01
-- ============================================================


-- ============================================================
-- 2. 中文程度定義 (levels)
-- ============================================================
CREATE TABLE `levels` (
    `level_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '程度ID',
    `level_code` VARCHAR(10) NOT NULL COMMENT '程度代碼(BEG/BAS/ELE1/ELE2/INT1/INT2/INT3/ADV1)',
    `level_name` VARCHAR(100) NOT NULL COMMENT '程度名稱(中文)',
    `level_name_en` VARCHAR(200) NULL COMMENT '程度名稱(英文)',
    `textbook` VARCHAR(200) NULL COMMENT '對應教材',
    `sort_order` INT NOT NULL DEFAULT 0 COMMENT '排序',
    `status` TINYINT NOT NULL DEFAULT 1 COMMENT '狀態(1啟用/0停用)',
    `update_date` DATETIME NULL COMMENT '更新日期',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    `updated_by` VARCHAR(100) NULL COMMENT '更新人員',
    UNIQUE KEY `uk_levels_code` (`level_code`),
    KEY `idx_levels_sort` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中文程度定義';

-- ============================================================
-- 3. 學生資料 (students)
-- ============================================================
CREATE TABLE `students` (
    `student_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '學生ID(系統內部)',
    `student_no` VARCHAR(50) NOT NULL COMMENT '學號',
    `chinese_name` VARCHAR(100) NULL COMMENT '中文姓名',
    `name` VARCHAR(200) NOT NULL COMMENT '英文姓名(護照)',
    `nationality` VARCHAR(100) NOT NULL COMMENT '國籍',
    `gender` ENUM('M','F') NOT NULL COMMENT '性別(M男/F女)',
    `level_code` VARCHAR(10) NOT NULL COMMENT '中文程度代碼',
    `email` VARCHAR(200) NOT NULL COMMENT '聯絡Email',
    `phone` VARCHAR(50) NULL COMMENT '聯絡電話',
    `student_type` ENUM('degree','exchange') NOT NULL DEFAULT 'exchange' COMMENT '學生類型(degree學位生/exchange交換生)',
    `status` TINYINT NOT NULL DEFAULT 1 COMMENT '狀態(1在學/0離校)',
    `note` TEXT NULL COMMENT '備註',
    `update_date` DATETIME NULL COMMENT '更新日期',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    `updated_by` VARCHAR(100) NULL COMMENT '更新人員',
    UNIQUE KEY `uk_students_no` (`student_no`),
    KEY `idx_students_level` (`level_code`),
    KEY `idx_students_nationality` (`nationality`),
    KEY `idx_students_type` (`student_type`),
    KEY `idx_students_email` (`email`),
    KEY `idx_students_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='國際生資料';

-- ============================================================
-- 4. 課程主檔 (courses)
-- ============================================================
CREATE TABLE `courses` (
    `course_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '課程ID',
    `course_code` VARCHAR(50) NULL COMMENT '課程代碼(如CLC101)',
    `course_name` VARCHAR(200) NOT NULL COMMENT '課程名稱(中文)',
    `course_name_en` VARCHAR(300) NULL COMMENT '課程名稱(英文)',
    `level_code` VARCHAR(10) NOT NULL COMMENT '對應中文程度代碼',
    `textbook` VARCHAR(300) NULL COMMENT '使用教材',
    `description` TEXT NULL COMMENT '課程說明',
    `status` TINYINT NOT NULL DEFAULT 1 COMMENT '狀態(1啟用/0停用)',
    `update_date` DATETIME NULL COMMENT '更新日期',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    `updated_by` VARCHAR(100) NULL COMMENT '更新人員',
    UNIQUE KEY `uk_courses_code` (`course_code`),
    KEY `idx_courses_level` (`level_code`),
    KEY `idx_courses_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='課程主檔';

-- ============================================================
-- 5. 開班資料 (offerings)
-- ============================================================
CREATE TABLE `offerings` (
    `offering_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '開班ID',
    `course_id` BIGINT NOT NULL COMMENT '課程ID',
    `semester` VARCHAR(20) NOT NULL COMMENT '學期(如114-1)',
    `teacher_id` BIGINT NULL COMMENT '預設授課教師ID',
    `classroom` VARCHAR(100) NULL COMMENT '預設教室',
    `start_date` DATE NOT NULL COMMENT '開課日期',
    `end_date` DATE NOT NULL COMMENT '結課日期',
    `class_time` VARCHAR(100) NOT NULL COMMENT '上課時間(如09:10-12:00)',
    `schedule` VARCHAR(200) NOT NULL COMMENT '上課周期(如週二、四)',
    `hours_per_week` DECIMAL(5,1) NULL COMMENT '每週時數',
    `total_hours` DECIMAL(6,1) NULL COMMENT '總時數',
    `max_sections` INT NULL COMMENT '最大班級數',
    `max_students_per_section` INT NOT NULL DEFAULT 12 COMMENT '每班人數上限',
    `template_id` BIGINT NULL COMMENT '通知信模板ID',
    `offering_status` ENUM('draft','active','closed') NOT NULL DEFAULT 'draft' COMMENT '狀態(draft草稿/active進行中/closed已結束)',
    `note` TEXT NULL COMMENT '備註',
    `update_date` DATETIME NULL COMMENT '更新日期',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    `updated_by` VARCHAR(100) NULL COMMENT '更新人員',
    KEY `idx_offerings_course` (`course_id`),
    KEY `idx_offerings_semester` (`semester`),
    KEY `idx_offerings_teacher` (`teacher_id`),
    KEY `idx_offerings_status` (`offering_status`),
    KEY `idx_offerings_dates` (`start_date`, `end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='開班資料';

-- ============================================================
-- 6. 班級 (sections)
-- ============================================================
CREATE TABLE `sections` (
    `section_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '班級ID',
    `offering_id` BIGINT NOT NULL COMMENT '開班ID',
    `section_name` VARCHAR(50) NOT NULL COMMENT '班級名稱(如A班/B班)',
    `teacher_id` BIGINT NULL COMMENT '班級授課教師ID',
    `classroom` VARCHAR(100) NULL COMMENT '班級專屬教室',
    `max_students` INT NOT NULL DEFAULT 12 COMMENT '最大人數',
    `current_count` INT NOT NULL DEFAULT 0 COMMENT '目前人數',
    `section_status` ENUM('active','closed') NOT NULL DEFAULT 'active' COMMENT '狀態(active進行中/closed已結束)',
    `note` TEXT NULL COMMENT '備註',
    `update_date` DATETIME NULL COMMENT '更新日期',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    `updated_by` VARCHAR(100) NULL COMMENT '更新人員',
    KEY `idx_sections_offering` (`offering_id`),
    KEY `idx_sections_teacher` (`teacher_id`),
    KEY `idx_sections_status` (`section_status`),
    UNIQUE KEY `uk_sections_offering_name` (`offering_id`, `section_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班級';

-- ============================================================
-- 7. 選課記錄 (enrollments)
-- ============================================================
CREATE TABLE `enrollments` (
    `enrollment_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '選課ID',
    `student_id` BIGINT NOT NULL COMMENT '學生ID',
    `section_id` BIGINT NOT NULL COMMENT '班級ID',
    `enrollment_status` ENUM('active','dropped','withdrawn') NOT NULL DEFAULT 'active' COMMENT '狀態(active在學/dropped退選/withdrawn停修)',
    `class_type` ENUM('group','individual') NOT NULL DEFAULT 'group' COMMENT '班別(group團班/individual個別班)',
    `enrolled_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '選課時間',
    `dropped_at` DATETIME NULL COMMENT '退選/停修時間',
    `final_score` DECIMAL(5,2) NULL COMMENT '最終總分',
    `final_grade` VARCHAR(10) NULL COMMENT '最終等第(A/B/C/D)',
    `attendance_rate` DECIMAL(5,2) NULL COMMENT '出席率(%)',
    `note` TEXT NULL COMMENT '備註',
    `update_date` DATETIME NULL COMMENT '更新日期',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    `updated_by` VARCHAR(100) NULL COMMENT '更新人員',
    KEY `idx_enrollments_student` (`student_id`),
    KEY `idx_enrollments_section` (`section_id`),
    KEY `idx_enrollments_status` (`enrollment_status`),
    UNIQUE KEY `uk_enrollments_student_section` (`student_id`, `section_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='選課記錄';

-- ============================================================
-- 8. 選課異動記錄 (enrollment_changes)
-- ============================================================
CREATE TABLE `enrollment_changes` (
    `change_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '異動ID',
    `enrollment_id` BIGINT NOT NULL COMMENT '選課ID',
    `student_id` BIGINT NOT NULL COMMENT '學生ID',
    `change_type` ENUM('TRANSFER','DROP','WITHDRAW','ADD') NOT NULL COMMENT '異動類型(TRANSFER調班/DROP退選/WITHDRAW停修/ADD補選)',
    `from_section_id` BIGINT NULL COMMENT '原班級ID',
    `to_section_id` BIGINT NULL COMMENT '新班級ID',
    `reason` TEXT NULL COMMENT '異動原因',
    `operator_id` BIGINT NOT NULL COMMENT '操作人員ID',
    `notification_status` ENUM('pending','sent','failed','skipped') NOT NULL DEFAULT 'pending' COMMENT '通知狀態(pending待發送/sent已發送/failed發送失敗/skipped略過)',
    `notified_at` DATETIME NULL COMMENT '通知發送時間',
    `changed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '異動時間',
    `update_date` DATETIME NULL COMMENT '更新日期',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    `updated_by` VARCHAR(100) NULL COMMENT '更新人員',
    KEY `idx_changes_enrollment` (`enrollment_id`),
    KEY `idx_changes_student` (`student_id`),
    KEY `idx_changes_type` (`change_type`),
    KEY `idx_changes_operator` (`operator_id`),
    KEY `idx_changes_notification` (`notification_status`),
    KEY `idx_changes_date` (`changed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='選課異動記錄';

-- ============================================================
-- 9. 點名記錄 (attendance)
-- ============================================================
CREATE TABLE `attendance` (
    `attendance_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '點名ID',
    `section_id` BIGINT NOT NULL COMMENT '班級ID',
    `student_id` BIGINT NOT NULL COMMENT '學生ID',
    `class_date` DATE NOT NULL COMMENT '上課日期',
    `attendance_status` ENUM('P','L','E','A','O','S') NOT NULL DEFAULT 'P' COMMENT '出席狀態(P出席/L遲到/E請假/A缺席/O公假/S病假)',
    `hours` DECIMAL(4,1) NULL COMMENT '本節時數',
    `note` VARCHAR(500) NULL COMMENT '備註',
    `is_locked` TINYINT NOT NULL DEFAULT 0 COMMENT '是否鎖定(1是/0否)',
    `locked_at` DATETIME NULL COMMENT '鎖定時間',
    `locked_by` BIGINT NULL COMMENT '鎖定人員ID',
    `recorded_by` BIGINT NULL COMMENT '點名人員ID',
    `update_date` DATETIME NULL COMMENT '更新日期',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    `updated_by` VARCHAR(100) NULL COMMENT '更新人員',
    KEY `idx_attendance_section` (`section_id`),
    KEY `idx_attendance_student` (`student_id`),
    KEY `idx_attendance_date` (`class_date`),
    KEY `idx_attendance_status` (`attendance_status`),
    KEY `idx_attendance_locked` (`is_locked`),
    UNIQUE KEY `uk_attendance_section_student_date` (`section_id`, `student_id`, `class_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='點名記錄';

-- ============================================================
-- 10. 點名日期 (attendance_dates)
-- ============================================================
CREATE TABLE `attendance_dates` (
    `date_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '點名日ID',
    `section_id` BIGINT NOT NULL COMMENT '班級ID',
    `class_date` DATE NOT NULL COMMENT '上課日期',
    `hours` DECIMAL(4,1) NULL COMMENT '本日時數',
    `is_locked` TINYINT NOT NULL DEFAULT 0 COMMENT '是否鎖定(1是/0否)',
    `locked_at` DATETIME NULL COMMENT '鎖定時間',
    `locked_by` BIGINT NULL COMMENT '鎖定人員ID',
    `note` VARCHAR(500) NULL COMMENT '備註',
    `update_date` DATETIME NULL COMMENT '更新日期',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    `updated_by` VARCHAR(100) NULL COMMENT '更新人員',
    KEY `idx_att_dates_section` (`section_id`),
    KEY `idx_att_dates_date` (`class_date`),
    UNIQUE KEY `uk_att_dates_section_date` (`section_id`, `class_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='點名日期';

-- ============================================================
-- 11. 評分項目 (grade_items)
-- ============================================================
CREATE TABLE `grade_items` (
    `item_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '評分項目ID',
    `offering_id` BIGINT NULL COMMENT '開班ID(NULL表示全域預設)',
    `item_name` VARCHAR(100) NOT NULL COMMENT '項目名稱(中文)',
    `item_name_en` VARCHAR(100) NULL COMMENT '項目名稱(英文)',
    `weight` DECIMAL(5,2) NOT NULL COMMENT '權重百分比',
    `max_score` DECIMAL(5,2) NOT NULL DEFAULT 100.00 COMMENT '滿分',
    `sort_order` INT NOT NULL DEFAULT 0 COMMENT '排序',
    `is_default` TINYINT NOT NULL DEFAULT 0 COMMENT '是否為預設項目(1是/0否)',
    `status` TINYINT NOT NULL DEFAULT 1 COMMENT '狀態(1啟用/0停用)',
    `update_date` DATETIME NULL COMMENT '更新日期',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    `updated_by` VARCHAR(100) NULL COMMENT '更新人員',
    KEY `idx_grade_items_offering` (`offering_id`),
    KEY `idx_grade_items_sort` (`sort_order`),
    KEY `idx_grade_items_default` (`is_default`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='評分項目';

-- ============================================================
-- 12. 學生成績 (grades)
-- ============================================================
CREATE TABLE `grades` (
    `grade_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '成績ID',
    `enrollment_id` BIGINT NOT NULL COMMENT '選課ID',
    `item_id` BIGINT NOT NULL COMMENT '評分項目ID',
    `score` DECIMAL(5,2) NULL COMMENT '原始分數',
    `weighted_score` DECIMAL(5,2) NULL COMMENT '加權後分數',
    `note` VARCHAR(500) NULL COMMENT '備註',
    `scored_by` BIGINT NULL COMMENT '評分人員ID',
    `scored_at` DATETIME NULL COMMENT '評分時間',
    `update_date` DATETIME NULL COMMENT '更新日期',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    `updated_by` VARCHAR(100) NULL COMMENT '更新人員',
    KEY `idx_grades_enrollment` (`enrollment_id`),
    KEY `idx_grades_item` (`item_id`),
    UNIQUE KEY `uk_grades_enrollment_item` (`enrollment_id`, `item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='學生成績';

-- ============================================================
-- 13. 信件模板 (email_templates)
-- ============================================================
CREATE TABLE `email_templates` (
    `template_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '模板ID',
    `template_name` VARCHAR(200) NOT NULL COMMENT '模板名稱',
    `template_type` ENUM('enrollment','change','grade','general') NOT NULL DEFAULT 'general' COMMENT '模板類型(enrollment選課通知/change異動通知/grade成績通知/general一般)',
    `subject` VARCHAR(500) NOT NULL COMMENT '郵件主旨',
    `body` TEXT NOT NULL COMMENT '郵件內容(含變數)',
    `is_default` TINYINT NOT NULL DEFAULT 0 COMMENT '是否為預設模板(1是/0否)',
    `status` TINYINT NOT NULL DEFAULT 1 COMMENT '狀態(1啟用/0停用)',
    `update_date` DATETIME NULL COMMENT '更新日期',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    `updated_by` VARCHAR(100) NULL COMMENT '更新人員',
    KEY `idx_templates_type` (`template_type`),
    KEY `idx_templates_default` (`is_default`),
    KEY `idx_templates_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='信件模板';

-- ============================================================
-- 14. 郵件發送記錄 (email_logs)
-- ============================================================
CREATE TABLE `email_logs` (
    `log_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '記錄ID',
    `recipient_email` VARCHAR(200) NOT NULL COMMENT '收件人Email',
    `recipient_name` VARCHAR(200) NULL COMMENT '收件人姓名',
    `recipient_type` ENUM('student','teacher','staff') NOT NULL COMMENT '收件人類型',
    `recipient_id` BIGINT NULL COMMENT '收件人ID(對應各類型資料表)',
    `template_id` BIGINT NULL COMMENT '使用的模板ID',
    `subject` VARCHAR(500) NOT NULL COMMENT '實際郵件主旨',
    `body` TEXT NOT NULL COMMENT '實際郵件內容',
    `attachment_path` VARCHAR(500) NULL COMMENT '附件檔案路徑',
    `related_type` VARCHAR(50) NULL COMMENT '關聯類型(enrollment/change/grade等)',
    `related_id` BIGINT NULL COMMENT '關聯ID',
    `send_status` ENUM('pending','sending','sent','failed') NOT NULL DEFAULT 'pending' COMMENT '發送狀態',
    `error_message` TEXT NULL COMMENT '錯誤訊息',
    `retry_count` INT NOT NULL DEFAULT 0 COMMENT '重試次數',
    `scheduled_at` DATETIME NULL COMMENT '排程發送時間',
    `sent_at` DATETIME NULL COMMENT '實際發送時間',
    `update_date` DATETIME NULL COMMENT '更新日期',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    `updated_by` VARCHAR(100) NULL COMMENT '更新人員',
    KEY `idx_logs_recipient` (`recipient_email`),
    KEY `idx_logs_type` (`recipient_type`),
    KEY `idx_logs_template` (`template_id`),
    KEY `idx_logs_status` (`send_status`),
    KEY `idx_logs_sent_at` (`sent_at`),
    KEY `idx_logs_related` (`related_type`, `related_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='郵件發送記錄';

-- ============================================================
-- 16. 系統設定 (system_settings)
-- ============================================================
CREATE TABLE `system_settings` (
    `setting_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '設定ID',
    `setting_key` VARCHAR(100) NOT NULL COMMENT '設定鍵名',
    `setting_value` TEXT NOT NULL COMMENT '設定值',
    `setting_type` ENUM('string','int','float','boolean','json') NOT NULL DEFAULT 'string' COMMENT '值類型',
    `description` VARCHAR(500) NULL COMMENT '說明',
    `is_public` TINYINT NOT NULL DEFAULT 0 COMMENT '是否公開(1是/0否)',
    `update_date` DATETIME NULL COMMENT '更新日期',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    `updated_by` VARCHAR(100) NULL COMMENT '更新人員',
    UNIQUE KEY `uk_settings_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系統設定';

-- ============================================================
-- 17. 操作日誌 (audit_logs)
-- ============================================================
CREATE TABLE `audit_logs` (
    `log_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '日誌ID',
    `user_id` BIGINT NULL COMMENT '操作使用者ID',
    `user_account` VARCHAR(100) NULL COMMENT '操作使用者帳號',
    `action` VARCHAR(100) NOT NULL COMMENT '操作動作',
    `table_name` VARCHAR(100) NULL COMMENT '資料表名稱',
    `record_id` BIGINT NULL COMMENT '記錄ID',
    `old_data` JSON NULL COMMENT '變更前資料(JSON)',
    `new_data` JSON NULL COMMENT '變更後資料(JSON)',
    `ip_address` VARCHAR(50) NULL COMMENT 'IP位址',
    `user_agent` VARCHAR(500) NULL COMMENT '瀏覽器資訊',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    KEY `idx_audit_user` (`user_id`),
    KEY `idx_audit_action` (`action`),
    KEY `idx_audit_table` (`table_name`),
    KEY `idx_audit_date` (`create_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日誌';

-- ============================================================
-- 18. 匯入批次記錄 (import_batches)
-- ============================================================
CREATE TABLE `import_batches` (
    `batch_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '批次ID',
    `import_type` ENUM('students','attendance','grades') NOT NULL COMMENT '匯入類型',
    `file_name` VARCHAR(300) NOT NULL COMMENT '原始檔案名稱',
    `file_path` VARCHAR(500) NULL COMMENT '檔案儲存路徑',
    `total_rows` INT NOT NULL DEFAULT 0 COMMENT '總筆數',
    `success_count` INT NOT NULL DEFAULT 0 COMMENT '成功筆數',
    `fail_count` INT NOT NULL DEFAULT 0 COMMENT '失敗筆數',
    `error_details` JSON NULL COMMENT '錯誤詳情(JSON)',
    `import_status` ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending' COMMENT '匯入狀態',
    `imported_by` BIGINT NOT NULL COMMENT '匯入人員ID',
    `started_at` DATETIME NULL COMMENT '開始時間',
    `completed_at` DATETIME NULL COMMENT '完成時間',
    `update_date` DATETIME NULL COMMENT '更新日期',
    `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    `updated_by` VARCHAR(100) NULL COMMENT '更新人員',
    KEY `idx_import_type` (`import_type`),
    KEY `idx_import_status` (`import_status`),
    KEY `idx_import_user` (`imported_by`),
    KEY `idx_import_date` (`create_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='匯入批次記錄';

-- ============================================================
-- 外鍵約束 (Foreign Keys)
-- ============================================================

-- offerings 外鍵
ALTER TABLE `offerings` 
    ADD CONSTRAINT `fk_offerings_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_offerings_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_offerings_template` FOREIGN KEY (`template_id`) REFERENCES `email_templates` (`template_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- sections 外鍵
ALTER TABLE `sections` 
    ADD CONSTRAINT `fk_sections_offering` FOREIGN KEY (`offering_id`) REFERENCES `offerings` (`offering_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_sections_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- enrollments 外鍵
ALTER TABLE `enrollments` 
    ADD CONSTRAINT `fk_enrollments_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_enrollments_section` FOREIGN KEY (`section_id`) REFERENCES `sections` (`section_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- enrollment_changes 外鍵
ALTER TABLE `enrollment_changes` 
    ADD CONSTRAINT `fk_changes_enrollment` FOREIGN KEY (`enrollment_id`) REFERENCES `enrollments` (`enrollment_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_changes_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_changes_from_section` FOREIGN KEY (`from_section_id`) REFERENCES `sections` (`section_id`) ON DELETE SET NULL ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_changes_to_section` FOREIGN KEY (`to_section_id`) REFERENCES `sections` (`section_id`) ON DELETE SET NULL ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_changes_operator` FOREIGN KEY (`operator_id`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- attendance 外鍵
ALTER TABLE `attendance` 
    ADD CONSTRAINT `fk_attendance_section` FOREIGN KEY (`section_id`) REFERENCES `sections` (`section_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_attendance_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_attendance_recorded` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_attendance_locked` FOREIGN KEY (`locked_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- attendance_dates 外鍵
ALTER TABLE `attendance_dates` 
    ADD CONSTRAINT `fk_att_dates_section` FOREIGN KEY (`section_id`) REFERENCES `sections` (`section_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_att_dates_locked` FOREIGN KEY (`locked_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- grade_items 外鍵
ALTER TABLE `grade_items` 
    ADD CONSTRAINT `fk_grade_items_offering` FOREIGN KEY (`offering_id`) REFERENCES `offerings` (`offering_id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- grades 外鍵
ALTER TABLE `grades` 
    ADD CONSTRAINT `fk_grades_enrollment` FOREIGN KEY (`enrollment_id`) REFERENCES `enrollments` (`enrollment_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_grades_item` FOREIGN KEY (`item_id`) REFERENCES `grade_items` (`item_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_grades_scored` FOREIGN KEY (`scored_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- email_logs 外鍵
ALTER TABLE `email_logs` 
    ADD CONSTRAINT `fk_email_logs_template` FOREIGN KEY (`template_id`) REFERENCES `email_templates` (`template_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- import_batches 外鍵
ALTER TABLE `import_batches` 
    ADD CONSTRAINT `fk_import_user` FOREIGN KEY (`imported_by`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- ============================================================
-- 初始資料 (Initial Data)
-- ============================================================

-- 中文程度初始資料
INSERT INTO `levels` (`level_code`, `level_name`, `level_name_en`, `textbook`, `sort_order`) VALUES
('BEG', '入門華語', 'Beginner Mandarin Course', '漢語拼音＋基礎會話 L1-4', 1),
('BAS', '基礎華語', 'Basic Mandarin Course', '基礎會話 L5-8', 2),
('ELE1', '初級華語一', 'Elementary Mandarin Course Level 1', '初級會話 L1-4', 3),
('ELE2', '初級華語二', 'Elementary Mandarin Course Level 2', '初級會話 L5-7', 4),
('INT1', '中級華語一', 'Intermediate Mandarin Course Level 1', '實用視聽華語三 L1-4', 5),
('INT2', '中級華語二', 'Intermediate Mandarin Course Level 2', '實用視聽華語三 L5-8', 6),
('INT3', '中級華語三', 'Intermediate Mandarin Course Level 3', '實用視聽華語三 L9-12', 7),
('ADV1', '進階華語一', 'Advanced Mandarin Course Level 1', '實用視聽華語四 L1-4', 8);

-- 預設評分項目
INSERT INTO `grade_items` (`offering_id`, `item_name`, `item_name_en`, `weight`, `max_score`, `sort_order`, `is_default`) VALUES
(NULL, '平時成績', 'Regular Performance', 30.00, 100.00, 1, 1),
(NULL, '期中成績', 'Midterm Exam', 30.00, 100.00, 2, 1),
(NULL, '期末成績', 'Final Exam', 40.00, 100.00, 3, 1);

-- 預設信件模板
INSERT INTO `email_templates` (`template_name`, `template_type`, `subject`, `body`, `is_default`) VALUES
('選課通知', 'enrollment', '【華語教學中心】課程選課通知 Course Enrollment Notification', 
'親愛的 {{student_name}} 同學您好：\n\n您已成功選修以下課程：\n\n課程名稱：{{course_name}}\n班級：{{section_name}}\n授課教師：{{teacher_name}}\n教室：{{classroom}}\n上課時間：{{class_time}}\n上課週期：{{schedule}}\n課程期間：{{start_date}} ~ {{end_date}}\n\n請準時出席上課。如有任何問題，請洽華語教學中心。\n\nDear {{student_name}},\n\nYou have successfully enrolled in the following course:\n\nCourse: {{course_name}}\nSection: {{section_name}}\nTeacher: {{teacher_name}}\nClassroom: {{classroom}}\nClass Time: {{class_time}}\nSchedule: {{schedule}}\nPeriod: {{start_date}} ~ {{end_date}}\n\nPlease attend class on time. If you have any questions, please contact the Chinese Language Center.\n\n華語教學中心 敬上\nChinese Language Center', 1),

('異動通知', 'change', '【華語教學中心】課程異動通知 Course Change Notification',
'親愛的 {{student_name}} 同學您好：\n\n您的課程資料已異動，詳情如下：\n\n課程名稱：{{course_name}}\n異動類型：{{change_type}}\n原班級：{{from_section}}\n新班級：{{to_section}}\n\n如有任何問題，請洽華語教學中心。\n\nDear {{student_name}},\n\nYour course information has been updated:\n\nCourse: {{course_name}}\nChange Type: {{change_type}}\nOriginal Section: {{from_section}}\nNew Section: {{to_section}}\n\nIf you have any questions, please contact the Chinese Language Center.\n\n華語教學中心 敬上\nChinese Language Center', 1),

('成績單通知', 'grade', '【華語教學中心】成績單 Transcript',
'親愛的 {{student_name}} 同學您好：\n\n附件為您的成績單，請查收。\n\nDear {{student_name}},\n\nPlease find your transcript attached.\n\n華語教學中心 敬上\nChinese Language Center', 1);

-- 系統設定初始資料
INSERT INTO `system_settings` (`setting_key`, `setting_value`, `setting_type`, `description`) VALUES
('max_students_per_section', '12', 'int', '每班最大人數'),
('passing_grade', '60', 'int', '及格分數'),
('transcript_link_expire_days', '30', 'int', '成績單下載連結有效天數'),
('smtp_host', '', 'string', 'SMTP伺服器位址'),
('smtp_port', '587', 'int', 'SMTP連接埠'),
('smtp_encryption', 'tls', 'string', 'SMTP加密方式(tls/ssl)'),
('smtp_username', '', 'string', 'SMTP帳號'),
('smtp_password', '', 'string', 'SMTP密碼'),
('sender_email', '', 'string', '系統發信地址'),
('sender_name', '華語教學中心', 'string', '發信人名稱'),
('center_name', '華語教學中心', 'string', '中心名稱(中文)'),
('center_name_en', 'Chinese Language Center', 'string', '中心名稱(英文)'),
('school_name', '國立中山大學', 'string', '學校名稱(中文)'),
('school_name_en', 'National Sun Yat-sen University', 'string', '學校名稱(英文)'),
('director_name', '', 'string', '主任姓名'),
('director_name_en', '', 'string', '主任姓名(英文)');


-- ============================================================
-- 完成
-- ============================================================
