-- ============================================
-- Reading Platform Database Schema
-- ============================================
-- Flyway 版本：V1
-- 数据库：MySQL 8.0+
-- 字符集：utf8mb4
-- 创建日期：2026-03-11
--
-- 说明：完整的数据库初始化脚本
-- 包含所有实体类字段的完整建表语句
--
-- 表列表：共 37 张表
-- - 用户表：6 张
-- - 班级表：3 张
-- - 课程表：10 张
-- - 课时表：3 张
-- - 任务表：4 张
-- - 成长表：1 张
-- - 资源表：2 张
-- - 日程表：2 张
-- - 系统表：5 张
-- ============================================

CREATE DATABASE IF NOT EXISTS reading_platform DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE reading_platform;

-- ============================================
-- Admin User (for super admin)
-- ============================================

CREATE TABLE IF NOT EXISTS t_admin_user (
    id VARCHAR(32) PRIMARY KEY COMMENT '管理员 ID',
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    avatar_url VARCHAR(500),
    status VARCHAR(20) DEFAULT 'active',
    last_login_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员用户';

-- ============================================
-- Tenant Tables
-- ============================================

CREATE TABLE IF NOT EXISTS t_tenant (
    id VARCHAR(32) PRIMARY KEY COMMENT '租户 ID',
    name VARCHAR(100) NOT NULL COMMENT 'Tenant name',
    code VARCHAR(50) NOT NULL UNIQUE COMMENT 'Tenant code',
    contact_name VARCHAR(50) COMMENT 'Contact person',
    contact_phone VARCHAR(20) COMMENT 'Contact phone',
    contact_email VARCHAR(100) COMMENT 'Contact email',
    address VARCHAR(255) COMMENT 'Address',
    logo_url VARCHAR(500) COMMENT 'Logo URL',
    status VARCHAR(20) DEFAULT 'active' COMMENT 'Status: active, expired, disabled',
    expire_at DATETIME COMMENT 'Expiration date',
    max_students INT DEFAULT 0 COMMENT 'Max students allowed',
    max_teachers INT DEFAULT 0 COMMENT 'Max teachers allowed',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='租户（幼儿园/机构）';

-- ============================================
-- User Tables
-- ============================================

CREATE TABLE IF NOT EXISTS t_teacher (
    id VARCHAR(32) PRIMARY KEY COMMENT '教师 ID',
    tenant_id VARCHAR(32) NOT NULL COMMENT '租户 ID',
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    avatar_url VARCHAR(500),
    gender VARCHAR(10),
    bio TEXT,
    status VARCHAR(20) DEFAULT 'active',
    last_login_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名',
    UNIQUE KEY uk_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教师';

CREATE TABLE IF NOT EXISTS t_student (
    id VARCHAR(32) PRIMARY KEY COMMENT '学生 ID',
    tenant_id VARCHAR(32) NOT NULL COMMENT '租户 ID',
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(10),
    birth_date DATE,
    avatar_url VARCHAR(500),
    grade VARCHAR(20),
    student_no VARCHAR(50),
    reading_level VARCHAR(20),
    interests TEXT,
    notes TEXT,
    status VARCHAR(20) DEFAULT 'active',
    class_id VARCHAR(32) COMMENT '班级 ID',
    parent_name VARCHAR(100) COMMENT '家长姓名',
    parent_phone VARCHAR(20) COMMENT '家长手机号',
    reading_count INT DEFAULT 0 COMMENT '阅读次数',
    lesson_count INT DEFAULT 0 COMMENT '课时数',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生';

CREATE TABLE IF NOT EXISTS t_parent (
    id VARCHAR(32) PRIMARY KEY COMMENT '家长 ID',
    tenant_id VARCHAR(32) NOT NULL COMMENT '租户 ID',
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    avatar_url VARCHAR(500),
    gender VARCHAR(10),
    status VARCHAR(20) DEFAULT 'active',
    last_login_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名',
    UNIQUE KEY uk_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家长';

CREATE TABLE IF NOT EXISTS t_parent_student (
    id VARCHAR(32) PRIMARY KEY COMMENT '主键 ID',
    parent_id VARCHAR(32) NOT NULL COMMENT '家长 ID',
    student_id VARCHAR(32) NOT NULL COMMENT '学生 ID',
    relationship VARCHAR(20) DEFAULT 'parent' COMMENT 'parent, guardian, etc.',
    is_primary TINYINT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名',
    UNIQUE KEY uk_parent_student (parent_id, student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家长 - 学生关联';

-- ============================================
-- Class Tables
-- ============================================

CREATE TABLE IF NOT EXISTS t_clazz (
    id VARCHAR(32) PRIMARY KEY COMMENT '班级 ID',
    tenant_id VARCHAR(32) NOT NULL COMMENT '租户 ID',
    name VARCHAR(100) NOT NULL,
    grade VARCHAR(20),
    description TEXT,
    capacity INT DEFAULT 30,
    status VARCHAR(20) DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班级';

CREATE TABLE IF NOT EXISTS t_class_teacher (
    id VARCHAR(32) PRIMARY KEY COMMENT '主键 ID',
    class_id VARCHAR(32) NOT NULL COMMENT '班级 ID',
    teacher_id VARCHAR(32) NOT NULL COMMENT '教师 ID',
    role VARCHAR(20) DEFAULT 'teacher' COMMENT 'head_teacher, teacher, assistant',
    is_primary BOOLEAN DEFAULT FALSE COMMENT '是否主教',
    sort_order INT DEFAULT 0 COMMENT '排序',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名',
    UNIQUE KEY uk_class_teacher (class_id, teacher_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班级 - 教师关联';

CREATE TABLE IF NOT EXISTS t_student_class_history (
    id VARCHAR(32) PRIMARY KEY COMMENT '主键 ID',
    student_id VARCHAR(32) NOT NULL COMMENT '学生 ID',
    class_id VARCHAR(32) NOT NULL COMMENT '班级 ID',
    start_date DATE NOT NULL,
    end_date DATE,
    status VARCHAR(20) DEFAULT 'active',
    reason VARCHAR(255) COMMENT '调班原因',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生班级历史';

-- ============================================
-- Course Tables
-- ============================================

CREATE TABLE IF NOT EXISTS t_course (
    id VARCHAR(32) PRIMARY KEY COMMENT '课程 ID',
    tenant_id VARCHAR(32) COMMENT 'NULL for system courses',
    name VARCHAR(200) NOT NULL,
    code VARCHAR(50),
    description TEXT,
    cover_url VARCHAR(500),
    category VARCHAR(50),
    age_range VARCHAR(50),
    difficulty_level VARCHAR(20),
    duration_minutes INT,
    objectives TEXT,
    status VARCHAR(20) DEFAULT 'draft',
    is_system TINYINT DEFAULT 0,
    -- 课程包重构字段
    core_content TEXT,
    intro_summary TEXT,
    intro_highlights TEXT,
    intro_goals TEXT,
    intro_schedule TEXT,
    intro_key_points TEXT,
    intro_methods TEXT,
    intro_evaluation TEXT,
    intro_notes TEXT,
    schedule_ref_data TEXT,
    environment_construction TEXT,
    theme_id VARCHAR(32) COMMENT '主题 ID',
    picture_book_name VARCHAR(100),
    cover_image_path VARCHAR(500),
    ebook_paths TEXT,
    audio_paths TEXT,
    video_paths TEXT,
    other_resources TEXT,
    ppt_path VARCHAR(500),
    ppt_name VARCHAR(100),
    poster_paths TEXT,
    tools TEXT,
    student_materials TEXT,
    lesson_plan_data TEXT,
    activities_data TEXT,
    assessment_data TEXT,
    grade_tags TEXT,
    domain_tags TEXT,
    has_collective_lesson TINYINT DEFAULT 0,
    -- 版本与审核字段
    version VARCHAR(20),
    parent_id VARCHAR(32) COMMENT '父课程 ID',
    is_latest TINYINT DEFAULT 0,
    submitted_at DATETIME,
    submitted_by VARCHAR(64),
    reviewed_at DATETIME,
    reviewed_by VARCHAR(64),
    review_comment TEXT,
    review_checklist TEXT,
    published_at DATETIME,
    -- 使用统计
    usage_count INT DEFAULT 0,
    teacher_count INT DEFAULT 0,
    avg_rating DECIMAL(10,2),
    -- 审计字段
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程';

CREATE TABLE IF NOT EXISTS t_course_version (
    id VARCHAR(32) PRIMARY KEY COMMENT '课程版本 ID',
    course_id VARCHAR(32) NOT NULL COMMENT '课程 ID',
    version VARCHAR(20) NOT NULL,
    description TEXT,
    status VARCHAR(20) DEFAULT 'draft',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程版本';

CREATE TABLE IF NOT EXISTS t_course_package (
    id VARCHAR(32) PRIMARY KEY COMMENT '课程包 ID',
    name VARCHAR(100) NOT NULL,
    description TEXT,
    cover_url VARCHAR(500),
    course_count INT DEFAULT 0,
    price DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'draft',
    is_system TINYINT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程包';

CREATE TABLE IF NOT EXISTS t_school_course (
    id VARCHAR(32) PRIMARY KEY COMMENT '校本课程 ID',
    tenant_id VARCHAR(32) NOT NULL COMMENT '租户 ID',
    name VARCHAR(200) NOT NULL,
    description TEXT,
    cover_url VARCHAR(500),
    category VARCHAR(50),
    age_range VARCHAR(50),
    status VARCHAR(20) DEFAULT 'draft',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='校本课程';

CREATE TABLE IF NOT EXISTS t_tenant_course (
    id VARCHAR(32) PRIMARY KEY COMMENT '租户课程 ID',
    tenant_id VARCHAR(32) NOT NULL COMMENT '租户 ID',
    course_id VARCHAR(32) NOT NULL COMMENT '课程 ID',
    enabled TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名',
    UNIQUE KEY uk_tenant_course (tenant_id, course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='租户 - 课程关联';

CREATE TABLE IF NOT EXISTS t_course_resource (
    id VARCHAR(32) PRIMARY KEY COMMENT '课程资源 ID',
    course_id VARCHAR(32) NOT NULL,
    title VARCHAR(200) NOT NULL,
    type VARCHAR(50) COMMENT 'video, audio, document, image, link',
    url VARCHAR(1000),
    file_path VARCHAR(500),
    file_size BIGINT,
    duration INT COMMENT 'Duration in seconds',
    description TEXT,
    sort_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程资源';

CREATE TABLE IF NOT EXISTS t_course_script (
    id VARCHAR(32) PRIMARY KEY COMMENT '课程脚本 ID',
    course_id VARCHAR(32) NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    sort_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程脚本';

CREATE TABLE IF NOT EXISTS t_course_script_page (
    id VARCHAR(32) PRIMARY KEY COMMENT '课程脚本页面 ID',
    script_id VARCHAR(32) NOT NULL,
    page_number INT NOT NULL,
    content TEXT,
    image_url VARCHAR(500),
    audio_url VARCHAR(500),
    duration INT COMMENT 'Duration in seconds',
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程脚本页面';

CREATE TABLE IF NOT EXISTS t_course_activity (
    id VARCHAR(32) PRIMARY KEY COMMENT '课程活动 ID',
    course_id VARCHAR(32) NOT NULL,
    title VARCHAR(200) NOT NULL,
    type VARCHAR(50) COMMENT 'quiz, discussion, practice, game',
    content TEXT,
    materials TEXT,
    duration_minutes INT,
    sort_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程活动';

CREATE TABLE IF NOT EXISTS t_course_lesson (
    id VARCHAR(32) PRIMARY KEY COMMENT '课程课时 ID',
    course_id VARCHAR(32) NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    content TEXT,
    sort_order INT DEFAULT 0,
    lesson_order INT COMMENT '课时顺序号',
    duration_minutes INT,
    video_url VARCHAR(500),
    status VARCHAR(20) DEFAULT 'draft',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程课时';

-- ============================================
-- Lesson Tables
-- ============================================

CREATE TABLE IF NOT EXISTS t_lesson (
    id VARCHAR(32) PRIMARY KEY COMMENT '课时 ID',
    tenant_id VARCHAR(32) NOT NULL COMMENT '租户 ID',
    course_id VARCHAR(32) NOT NULL COMMENT '课程 ID',
    class_id VARCHAR(32) COMMENT '班级 ID',
    teacher_id VARCHAR(32) NOT NULL COMMENT '教师 ID',
    title VARCHAR(200) NOT NULL,
    lesson_date DATE NOT NULL,
    start_time TIME,
    end_time TIME,
    location VARCHAR(100),
    status VARCHAR(20) DEFAULT 'scheduled',
    notes TEXT,
    -- 新增字段
    actual_duration INT COMMENT '实际时长（分钟）',
    overall_rating VARCHAR(10) COMMENT '整体评分',
    participation_rating VARCHAR(10) COMMENT '参与度评分',
    completion_note TEXT COMMENT '完成说明',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课时';

CREATE TABLE IF NOT EXISTS t_lesson_feedback (
    id VARCHAR(32) PRIMARY KEY COMMENT '课时反馈 ID',
    lesson_id VARCHAR(32) NOT NULL COMMENT '课时 ID',
    course_id VARCHAR(32) COMMENT '课程 ID',
    tenant_id VARCHAR(32) COMMENT '租户 ID',
    teacher_id VARCHAR(32) NOT NULL COMMENT '教师 ID',
    design_quality INT COMMENT '设计质量评分 (1-5)',
    participation INT COMMENT '参与度评分 (1-5)',
    goal_achievement INT COMMENT '目标达成度评分 (1-5)',
    overall_rating INT COMMENT '整体评分 (1-5)',
    step_feedbacks TEXT COMMENT '环节反馈 JSON',
    pros TEXT COMMENT '优点',
    suggestions TEXT COMMENT '建议',
    activities_done TEXT COMMENT '完成的活动 JSON',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课时反馈';

CREATE TABLE IF NOT EXISTS t_student_record (
    id VARCHAR(32) PRIMARY KEY COMMENT '学生记录 ID',
    lesson_id VARCHAR(32) NOT NULL COMMENT '课时 ID',
    student_id VARCHAR(32) NOT NULL COMMENT '学生 ID',
    attendance VARCHAR(20) DEFAULT 'present' COMMENT 'present, absent, late',
    performance TEXT,
    notes TEXT,
    focus INT COMMENT '专注力评分 (1-5)',
    participation INT COMMENT '参与度评分 (1-5)',
    interest INT COMMENT '兴趣评分 (1-5)',
    understanding INT COMMENT '理解度评分 (1-5)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名',
    UNIQUE KEY uk_lesson_student (lesson_id, student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生记录';

-- ============================================
-- Task Tables
-- ============================================

CREATE TABLE IF NOT EXISTS t_task (
    id VARCHAR(32) PRIMARY KEY COMMENT '任务 ID',
    tenant_id VARCHAR(32) NOT NULL COMMENT '租户 ID',
    title VARCHAR(200) NOT NULL,
    name VARCHAR(200) COMMENT '任务名称',
    description TEXT,
    type VARCHAR(50) DEFAULT 'homework' COMMENT 'reading, homework, activity',
    course_id VARCHAR(32) COMMENT '课程 ID',
    creator_id VARCHAR(32) NOT NULL,
    creator_role VARCHAR(20) NOT NULL,
    start_date DATE,
    due_date DATE,
    status VARCHAR(20) DEFAULT 'pending',
    attachments TEXT COMMENT 'JSON array of attachment URLs',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务';

CREATE TABLE IF NOT EXISTS t_task_template (
    id VARCHAR(32) PRIMARY KEY COMMENT '任务模板 ID',
    tenant_id VARCHAR(32) NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    type VARCHAR(50),
    content TEXT,
    is_public TINYINT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务模板';

CREATE TABLE IF NOT EXISTS t_task_target (
    id VARCHAR(32) PRIMARY KEY COMMENT '任务目标 ID',
    task_id VARCHAR(32) NOT NULL,
    target_type VARCHAR(20) NOT NULL COMMENT 'class, student',
    target_id VARCHAR(32) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务目标';

CREATE TABLE IF NOT EXISTS t_task_completion (
    id VARCHAR(32) PRIMARY KEY COMMENT '任务完成 ID',
    task_id VARCHAR(32) NOT NULL,
    student_id VARCHAR(32) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending, in_progress, completed',
    completed_at DATETIME,
    content TEXT,
    attachments TEXT COMMENT 'JSON array of attachment URLs',
    rating INT,
    feedback TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名',
    UNIQUE KEY uk_task_student (task_id, student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务完成';

-- ============================================
-- Growth Record Tables
-- ============================================

CREATE TABLE IF NOT EXISTS t_growth_record (
    id VARCHAR(32) PRIMARY KEY COMMENT '成长档案 ID',
    tenant_id VARCHAR(32) NOT NULL,
    student_id VARCHAR(32) NOT NULL,
    type VARCHAR(50) NOT NULL COMMENT 'reading, behavior, achievement, milestone',
    title VARCHAR(200) NOT NULL,
    content TEXT,
    images TEXT COMMENT 'JSON array of image URLs',
    recorded_by VARCHAR(32) NOT NULL,
    recorder_role VARCHAR(20) NOT NULL,
    record_date DATE NOT NULL,
    tags TEXT COMMENT 'JSON array of tags',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长档案';

-- ============================================
-- Resource Tables
-- ============================================

CREATE TABLE IF NOT EXISTS t_resource_library (
    id VARCHAR(32) PRIMARY KEY COMMENT '资源库 ID',
    tenant_id VARCHAR(32) NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    type VARCHAR(50) COMMENT 'book, material, equipment',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='资源库';

CREATE TABLE IF NOT EXISTS t_resource_item (
    id VARCHAR(32) PRIMARY KEY COMMENT '资源项 ID',
    library_id VARCHAR(32) NOT NULL,
    tenant_id VARCHAR(32) COMMENT '租户 ID',
    type VARCHAR(50) COMMENT '资源类型：book, material, equipment',
    name VARCHAR(200) NOT NULL,
    code VARCHAR(50),
    description TEXT,
    quantity INT DEFAULT 1,
    available_quantity INT DEFAULT 1,
    location VARCHAR(100),
    status VARCHAR(20) DEFAULT 'available',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='资源项';

-- ============================================
-- Schedule Tables
-- ============================================

CREATE TABLE IF NOT EXISTS t_schedule_plan (
    id VARCHAR(32) PRIMARY KEY COMMENT '日程计划 ID',
    tenant_id VARCHAR(32) NOT NULL,
    name VARCHAR(100) NOT NULL,
    class_id VARCHAR(32) COMMENT '班级 ID',
    course_id VARCHAR(32) COMMENT '课程 ID',
    teacher_id VARCHAR(32) COMMENT '教师 ID',
    day_of_week INT COMMENT '星期几 (1-7)',
    period INT COMMENT '第几节课',
    start_time TIME,
    end_time TIME,
    start_date DATE,
    end_date DATE,
    location VARCHAR(100),
    note TEXT,
    status VARCHAR(20) DEFAULT 'scheduled',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日程计划';

CREATE TABLE IF NOT EXISTS t_schedule_template (
    id VARCHAR(32) PRIMARY KEY COMMENT '日程模板 ID',
    tenant_id VARCHAR(32) NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    content TEXT COMMENT 'JSON schedule data',
    is_public TINYINT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日程模板';

-- ============================================
-- System Tables
-- ============================================

CREATE TABLE IF NOT EXISTS t_system_setting (
    id VARCHAR(32) PRIMARY KEY COMMENT '系统设置 ID',
    tenant_id VARCHAR(32) COMMENT 'NULL for global settings',
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT,
    description VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名',
    UNIQUE KEY uk_tenant_key (tenant_id, setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统设置';

CREATE TABLE IF NOT EXISTS t_notification (
    id VARCHAR(32) PRIMARY KEY COMMENT '通知 ID',
    tenant_id VARCHAR(32) NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    type VARCHAR(50) COMMENT 'system, course, task, growth',
    sender_id VARCHAR(32),
    sender_role VARCHAR(20),
    recipient_type VARCHAR(20) NOT NULL COMMENT 'all, class, teacher, parent, student',
    recipient_id VARCHAR(32),
    is_read TINYINT DEFAULT 0,
    read_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知';

CREATE TABLE IF NOT EXISTS t_operation_log (
    id VARCHAR(32) PRIMARY KEY COMMENT '操作日志 ID',
    tenant_id VARCHAR(32),
    user_id VARCHAR(32) NOT NULL,
    user_role VARCHAR(20),
    action VARCHAR(100) NOT NULL,
    module VARCHAR(50),
    target_type VARCHAR(50),
    target_id VARCHAR(32),
    details TEXT,
    ip_address VARCHAR(50),
    user_agent VARCHAR(500),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(64) COMMENT '创建人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志';

CREATE TABLE IF NOT EXISTS t_tag (
    id VARCHAR(32) PRIMARY KEY COMMENT '标签 ID',
    tenant_id VARCHAR(32) NOT NULL,
    name VARCHAR(50) NOT NULL,
    type VARCHAR(50) COMMENT 'student, course, resource',
    color VARCHAR(20),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名',
    UNIQUE KEY uk_tenant_name_type (tenant_id, name, type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='标签';

CREATE TABLE IF NOT EXISTS t_theme (
    id VARCHAR(32) PRIMARY KEY COMMENT '主题 ID',
    name VARCHAR(50) NOT NULL,
    display_name VARCHAR(100),
    color VARCHAR(20),
    icon VARCHAR(50),
    sort_order INT DEFAULT 0,
    is_enabled TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    created_by VARCHAR(64) COMMENT '创建人用户名',
    updated_by VARCHAR(64) COMMENT '更新人用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='主题';

-- ============================================
-- Indexes
-- ============================================

CREATE INDEX idx_teacher_tenant ON t_teacher(tenant_id);
CREATE INDEX idx_student_tenant ON t_student(tenant_id);
CREATE INDEX idx_parent_tenant ON t_parent(tenant_id);
CREATE INDEX idx_clazz_tenant ON t_clazz(tenant_id);
CREATE INDEX idx_course_tenant ON t_course(tenant_id);
CREATE INDEX idx_lesson_tenant ON t_lesson(tenant_id);
CREATE INDEX idx_task_tenant ON t_task(tenant_id);
CREATE INDEX idx_growth_record_tenant ON t_growth_record(tenant_id);
CREATE INDEX idx_notification_tenant ON t_notification(tenant_id);
