-- 本示例文件以课程管理系统为背景进行介绍。
-- 按照OMA的要求每个SQL语句使用字符`$$`进行分割

-- 首先创建一些表结构

-- 创建学生表
CREATE TABLE IF NOT EXISTS students (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  registration_date DATE NOT NULL,
  INDEX idx_registration_date (registration_date)
) ENGINE=InnoDB
$$

-- 创建课程表
CREATE TABLE IF NOT EXISTS courses (
  course_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  credits INT NOT NULL
) ENGINE=InnoDB
$$

-- 创建教师表
CREATE TABLE IF NOT EXISTS teachers (
  teacher_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL
) ENGINE=InnoDB
$$

-- 采用范围分区创建课程安排表
CREATE TABLE IF NOT EXISTS course_schedules (
  schedule_id INT AUTO_INCREMENT PRIMARY KEY,
  course_id INT NOT NULL,
  teacher_id INT,
  schedule_time DATETIME NOT NULL,
  INDEX idx_schedule_time (schedule_time)
) ENGINE=InnoDB
PARTITION BY RANGE(YEAR(schedule_time)) (
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p_future VALUES LESS THAN MAXVALUE
)
$$

CREATE VIEW `vw_course_teacher` AS
SELECT 
  c.course_id,
  c.title AS course_title,
  c.credits,
  t.name AS teacher_name
FROM courses AS c
LEFT JOIN course_schedules AS cs ON c.course_id = cs.course_id
LEFT JOIN teachers AS t ON cs.teacher_id = t.teacher_id;
$$

CREATE VIEW `vw_upcoming_courses` AS
SELECT 
  c.title AS course_title,
  t.name AS teacher_name,
  cs.schedule_time
FROM course_schedules AS cs
JOIN courses AS c ON cs.course_id = c.course_id
JOIN teachers AS t ON cs.teacher_id = t.teacher_id
WHERE cs.schedule_time BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 1 WEEK);
$$

CREATE VIEW `vw_student_registration` AS
SELECT 
  student_id,
  name AS student_name,
  registration_date
FROM students;
