318 lines
12 KiB
SQL
318 lines
12 KiB
SQL
-- CoreEdu 服务数据库初始化脚本（P3 完整版）
|
||
-- 共 14 表：考试域 3 + 作业域 3 + 成绩域 2 + 排课考勤域 4 + Outbox 1 + 教师关联 1
|
||
-- 仲裁依据：02-architecture-design.md §3.1 + president-final-rulings §2.5
|
||
|
||
-- ============================================================================
|
||
-- 考试域
|
||
-- ============================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS core_edu_exams (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
class_id CHAR(36) NOT NULL,
|
||
subject_id CHAR(36) NOT NULL,
|
||
title VARCHAR(200) NOT NULL,
|
||
description TEXT,
|
||
exam_date DATETIME NOT NULL,
|
||
duration INT NOT NULL,
|
||
total_score DECIMAL(6,2) NOT NULL,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'draft',
|
||
status_changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
status_changed_by CHAR(36),
|
||
school_id CHAR(36) NOT NULL,
|
||
created_by CHAR(36) NOT NULL,
|
||
archived_at DATETIME NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
INDEX idx_exams_class_status (class_id, status),
|
||
INDEX idx_exams_school_date (school_id, exam_date),
|
||
INDEX idx_exams_created_by (created_by)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS core_edu_exam_questions (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
exam_id CHAR(36) NOT NULL,
|
||
question_id CHAR(36) NOT NULL,
|
||
`order` INT NOT NULL,
|
||
score DECIMAL(6,2) NOT NULL,
|
||
question_type VARCHAR(30) NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_exam_questions_exam_id (exam_id),
|
||
INDEX idx_exam_questions_question_id (question_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS core_edu_exam_submissions (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
exam_id CHAR(36) NOT NULL,
|
||
student_id CHAR(36) NOT NULL,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'not_submitted',
|
||
submitted_at DATETIME NULL,
|
||
graded_at DATETIME NULL,
|
||
graded_by CHAR(36),
|
||
total_score DECIMAL(6,2) NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uniq_exam_student (exam_id, student_id),
|
||
INDEX idx_exam_submissions_exam_id (exam_id),
|
||
INDEX idx_exam_submissions_student_id (student_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ============================================================================
|
||
-- 作业域
|
||
-- ============================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS core_edu_homework (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
class_id CHAR(36) NOT NULL,
|
||
subject_id CHAR(36) NOT NULL,
|
||
title VARCHAR(200) NOT NULL,
|
||
description TEXT,
|
||
due_date DATETIME NOT NULL,
|
||
grace_period INT NOT NULL DEFAULT 300,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'assigned',
|
||
school_id CHAR(36) NOT NULL,
|
||
created_by CHAR(36) NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
INDEX idx_homework_class_status (class_id, status),
|
||
INDEX idx_homework_created_by (created_by)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS core_edu_homework_submissions (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
homework_id CHAR(36) NOT NULL,
|
||
student_id CHAR(36) NOT NULL,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'not_submitted',
|
||
submitted_at DATETIME NULL,
|
||
graded_at DATETIME NULL,
|
||
graded_by CHAR(36),
|
||
total_score DECIMAL(6,2) NULL,
|
||
feedback TEXT,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uniq_hw_student (homework_id, student_id),
|
||
INDEX idx_hw_submissions_homework_id (homework_id),
|
||
INDEX idx_hw_submissions_student_id (student_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS core_edu_homework_answers (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
submission_id CHAR(36) NOT NULL,
|
||
question_id CHAR(36) NOT NULL,
|
||
answer TEXT,
|
||
score DECIMAL(6,2) NULL,
|
||
teacher_comment TEXT,
|
||
is_correct BOOLEAN NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
INDEX idx_hw_answers_submission_id (submission_id),
|
||
INDEX idx_hw_answers_question_id (question_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ============================================================================
|
||
-- 成绩域
|
||
-- ============================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS core_edu_grades (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
student_id CHAR(36) NOT NULL,
|
||
exam_id CHAR(36),
|
||
homework_id CHAR(36),
|
||
score DECIMAL(6,2) NOT NULL,
|
||
total_score DECIMAL(6,2) NOT NULL,
|
||
feedback TEXT,
|
||
graded_by CHAR(36) NOT NULL,
|
||
school_id CHAR(36) NOT NULL,
|
||
idempotency_key VARCHAR(128),
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uniq_student_exam (student_id, exam_id),
|
||
UNIQUE KEY uniq_student_hw (student_id, homework_id),
|
||
UNIQUE KEY uniq_idempotency (idempotency_key),
|
||
INDEX idx_grades_student_id (student_id),
|
||
INDEX idx_grades_exam_id (exam_id),
|
||
INDEX idx_grades_homework_id (homework_id),
|
||
INDEX idx_grades_graded_by (graded_by)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS core_edu_grade_formulas (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
scope VARCHAR(20) NOT NULL,
|
||
scope_id CHAR(36) NOT NULL,
|
||
formula_type VARCHAR(20) NOT NULL,
|
||
weights JSON,
|
||
custom_expression TEXT,
|
||
effective_from DATETIME NOT NULL,
|
||
effective_to DATETIME NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_grade_formulas_scope (scope, scope_id),
|
||
INDEX idx_grade_formulas_effective (effective_from, effective_to)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ============================================================================
|
||
-- 排课考勤域
|
||
-- ============================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS core_edu_courses (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
class_id CHAR(36) NOT NULL,
|
||
subject_id CHAR(36) NOT NULL,
|
||
teacher_id CHAR(36) NOT NULL,
|
||
school_id CHAR(36) NOT NULL,
|
||
name VARCHAR(200) NOT NULL,
|
||
start_date DATE NOT NULL,
|
||
end_date DATE NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
INDEX idx_courses_class_id (class_id),
|
||
INDEX idx_courses_teacher_id (teacher_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS core_edu_lessons (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
course_id CHAR(36) NOT NULL,
|
||
title VARCHAR(200) NOT NULL,
|
||
`order` INT NOT NULL,
|
||
knowledge_point_ids JSON,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_lessons_course_id (course_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS core_edu_schedules (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
course_id CHAR(36) NOT NULL,
|
||
lesson_id CHAR(36),
|
||
teacher_id CHAR(36) NOT NULL,
|
||
class_id CHAR(36) NOT NULL,
|
||
room_id CHAR(36),
|
||
start_time DATETIME NOT NULL,
|
||
end_time DATETIME NOT NULL,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'scheduled',
|
||
school_id CHAR(36) NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
INDEX idx_schedules_teacher_time (teacher_id, start_time, end_time),
|
||
INDEX idx_schedules_class_time (class_id, start_time, end_time),
|
||
INDEX idx_schedules_course_id (course_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS core_edu_attendance (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
schedule_id CHAR(36) NOT NULL,
|
||
student_id CHAR(36) NOT NULL,
|
||
status VARCHAR(20) NOT NULL,
|
||
remark TEXT,
|
||
recorded_by CHAR(36) NOT NULL,
|
||
school_id CHAR(36) NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uniq_sched_student (schedule_id, student_id),
|
||
INDEX idx_attendance_student_id (student_id),
|
||
INDEX idx_attendance_schedule_id (schedule_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ============================================================================
|
||
-- Outbox（事务性发件箱）
|
||
-- ============================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS core_edu_outbox (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
event_id CHAR(36) NOT NULL,
|
||
aggregate_id CHAR(36) NOT NULL,
|
||
aggregate_type VARCHAR(50) NOT NULL,
|
||
event_type VARCHAR(100) NOT NULL,
|
||
occurred_at DATETIME NOT NULL,
|
||
payload TEXT NOT NULL,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
||
retry_count BIGINT NOT NULL DEFAULT 0,
|
||
next_retry_at DATETIME NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
processed_at TIMESTAMP NULL,
|
||
UNIQUE KEY uniq_event_id (event_id),
|
||
INDEX idx_outbox_status_retry (status, next_retry_at),
|
||
INDEX idx_outbox_aggregate (aggregate_type, aggregate_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ============================================================================
|
||
-- 教师关联（IAM 事件消费幂等）
|
||
-- ============================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS core_edu_teacher_associations (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
teacher_id CHAR(36) NOT NULL,
|
||
class_id CHAR(36) NOT NULL,
|
||
subject_id CHAR(36),
|
||
school_id CHAR(36) NOT NULL,
|
||
source VARCHAR(20) NOT NULL DEFAULT 'iam_event',
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uniq_teacher_class_subject (teacher_id, class_id, subject_id),
|
||
INDEX idx_teacher_assoc_teacher_id (teacher_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ============================================================================
|
||
-- 班级表（从 classes 服务合并，保留原表名兼容 CDC）
|
||
-- ============================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS classes (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
name VARCHAR(100) NOT NULL,
|
||
grade_id CHAR(36) NOT NULL,
|
||
head_teacher_id CHAR(36),
|
||
description TEXT,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
INDEX idx_classes_grade_id (grade_id),
|
||
INDEX idx_classes_head_teacher (head_teacher_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ============================================================================
|
||
-- P3.13 新增表：考试草稿、考试违规、请假申请
|
||
-- ============================================================================
|
||
|
||
-- 考试草稿表（学生考试过程中的自动保存）
|
||
CREATE TABLE IF NOT EXISTS core_edu_exam_drafts (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
exam_id CHAR(36) NOT NULL,
|
||
student_id CHAR(36) NOT NULL,
|
||
answers JSON,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uniq_exam_student_draft (exam_id, student_id),
|
||
INDEX idx_exam_drafts_student (student_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- 考试违规表（防作弊事件记录）
|
||
CREATE TABLE IF NOT EXISTS core_edu_exam_violations (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
exam_id CHAR(36) NOT NULL,
|
||
student_id CHAR(36) NOT NULL,
|
||
violation_type VARCHAR(40) NOT NULL,
|
||
detail TEXT,
|
||
severity INT NOT NULL DEFAULT 1,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_exam_violations_exam (exam_id),
|
||
INDEX idx_exam_violations_student (student_id),
|
||
INDEX idx_exam_violations_type (violation_type)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- 请假申请表
|
||
CREATE TABLE IF NOT EXISTS core_edu_leave_requests (
|
||
id CHAR(36) NOT NULL PRIMARY KEY,
|
||
student_id CHAR(36) NOT NULL,
|
||
class_id CHAR(36) NOT NULL,
|
||
leave_type VARCHAR(20) NOT NULL,
|
||
start_date DATE NOT NULL,
|
||
end_date DATE NOT NULL,
|
||
reason TEXT NOT NULL,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
||
submitted_by CHAR(36) NOT NULL,
|
||
reviewed_by CHAR(36),
|
||
review_comment TEXT,
|
||
school_id CHAR(36) NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
INDEX idx_leave_requests_student (student_id),
|
||
INDEX idx_leave_requests_class (class_id),
|
||
INDEX idx_leave_requests_status (status),
|
||
INDEX idx_leave_requests_school (school_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|