-- =====================================================
-- 幼儿园阅读平台数据库扩展脚本
-- 版本: V3
-- 创建时间: 2026-03-14
-- 描述: 添加任务、成长记录、通知等业务表
-- =====================================================

-- -----------------------------------------------------
-- 表21: 任务表 (task)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `task` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `tenant_id` BIGINT NOT NULL COMMENT '租户ID',
    `title` VARCHAR(200) NOT NULL COMMENT '任务标题',
    `description` TEXT COMMENT '任务描述',
    `type` VARCHAR(50) COMMENT '任务类型',
    `course_id` BIGINT COMMENT '课程ID',
    `creator_id` BIGINT COMMENT '创建人ID',
    `creator_role` VARCHAR(50) COMMENT '创建人角色',
    `start_date` DATE COMMENT '开始日期',
    `due_date` DATE COMMENT '截止日期',
    `status` VARCHAR(20) DEFAULT 'PENDING' COMMENT '状态',
    `attachments` JSON COMMENT '附件(JSON数组)',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_course_id` (`course_id`),
    KEY `idx_creator_id` (`creator_id`),
    KEY `idx_status` (`status`),
    KEY `idx_due_date` (`due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务表';

-- -----------------------------------------------------
-- 表22: 任务完成表 (task_completion)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `task_completion` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `task_id` BIGINT NOT NULL COMMENT '任务ID',
    `student_id` BIGINT NOT NULL COMMENT '学生ID',
    `status` VARCHAR(20) DEFAULT 'PENDING' COMMENT '完成状态',
    `completed_at` DATETIME COMMENT '完成时间',
    `content` TEXT COMMENT '完成内容',
    `attachments` JSON COMMENT '附件(JSON数组)',
    `rating` INT COMMENT '评分',
    `feedback` TEXT COMMENT '反馈',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_task_id` (`task_id`),
    KEY `idx_student_id` (`student_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务完成表';

-- -----------------------------------------------------
-- 表23: 任务目标表 (task_target)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `task_target` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `task_id` BIGINT NOT NULL COMMENT '任务ID',
    `target_type` VARCHAR(50) COMMENT '目标类型',
    `target_id` BIGINT COMMENT '目标ID',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_task_id` (`task_id`),
    KEY `idx_target_type` (`target_type`),
    KEY `idx_target_id` (`target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务目标表';

-- -----------------------------------------------------
-- 表24: 任务模板表 (task_template)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `task_template` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `tenant_id` BIGINT COMMENT '租户ID',
    `name` VARCHAR(200) NOT NULL COMMENT '模板名称',
    `description` TEXT COMMENT '模板描述',
    `type` VARCHAR(50) COMMENT '模板类型',
    `content` TEXT COMMENT '模板内容',
    `is_public` TINYINT DEFAULT 0 COMMENT '是否公开',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_type` (`type`),
    KEY `idx_is_public` (`is_public`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务模板表';

-- -----------------------------------------------------
-- 表25: 成长记录表 (growth_record)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `growth_record` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `tenant_id` BIGINT NOT NULL COMMENT '租户ID',
    `student_id` BIGINT NOT NULL COMMENT '学生ID',
    `type` VARCHAR(50) COMMENT '记录类型',
    `title` VARCHAR(200) NOT NULL COMMENT '记录标题',
    `content` TEXT COMMENT '记录内容',
    `images` JSON COMMENT '图片(JSON数组)',
    `recorded_by` BIGINT COMMENT '记录人ID',
    `recorder_role` VARCHAR(50) COMMENT '记录人角色',
    `record_date` DATE COMMENT '记录日期',
    `tags` JSON COMMENT '标签(JSON数组)',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_student_id` (`student_id`),
    KEY `idx_type` (`type`),
    KEY `idx_record_date` (`record_date`),
    KEY `idx_recorded_by` (`recorded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='成长记录表';

-- -----------------------------------------------------
-- 表26: 通知表 (notification)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `notification` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `tenant_id` BIGINT COMMENT '租户ID',
    `title` VARCHAR(200) NOT NULL COMMENT '通知标题',
    `content` TEXT NOT NULL COMMENT '通知内容',
    `type` VARCHAR(50) COMMENT '通知类型',
    `sender_id` BIGINT COMMENT '发送人ID',
    `sender_role` VARCHAR(50) COMMENT '发送人角色',
    `recipient_type` VARCHAR(50) COMMENT '接收人类型',
    `recipient_id` BIGINT COMMENT '接收人ID',
    `is_read` TINYINT DEFAULT 0 COMMENT '是否已读',
    `read_at` DATETIME COMMENT '阅读时间',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_sender_id` (`sender_id`),
    KEY `idx_recipient_type` (`recipient_type`),
    KEY `idx_recipient_id` (`recipient_id`),
    KEY `idx_is_read` (`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='通知表';

-- -----------------------------------------------------
-- 表27: 家长学生关联表 (parent_student)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `parent_student` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `parent_id` BIGINT NOT NULL COMMENT '家长ID',
    `student_id` BIGINT NOT NULL COMMENT '学生ID',
    `relationship` VARCHAR(50) COMMENT '关系',
    `is_primary` TINYINT DEFAULT 0 COMMENT '是否主要监护人',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_parent_id` (`parent_id`),
    KEY `idx_student_id` (`student_id`),
    UNIQUE KEY `uk_parent_student` (`parent_id`, `student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='家长学生关联表';

-- -----------------------------------------------------
-- 表28: 学生班级历史表 (student_class_history)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `student_class_history` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `student_id` BIGINT NOT NULL COMMENT '学生ID',
    `class_id` BIGINT NOT NULL COMMENT '班级ID',
    `start_date` DATE COMMENT '开始日期',
    `end_date` DATE COMMENT '结束日期',
    `status` VARCHAR(20) DEFAULT 'ACTIVE' COMMENT '状态',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_student_id` (`student_id`),
    KEY `idx_class_id` (`class_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生班级历史表';

-- -----------------------------------------------------
-- 表29: 学生记录表 (student_record)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `student_record` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `lesson_id` BIGINT COMMENT '课程ID',
    `student_id` BIGINT NOT NULL COMMENT '学生ID',
    `attendance` VARCHAR(20) COMMENT '出勤状态',
    `performance` TEXT COMMENT '表现评价',
    `notes` TEXT COMMENT '备注',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_lesson_id` (`lesson_id`),
    KEY `idx_student_id` (`student_id`),
    KEY `idx_attendance` (`attendance`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生记录表';