cfc/cfc-backend/src/main/resources/migration_add_assessment_snapshot_fields.sql

-- 为 dan_assessment_results 表添加孩子快照字段（测评时保存孩子完整信息）
ALTER TABLE dan_assessment_results
    ADD COLUMN IF NOT EXISTS child_name VARCHAR(50) COMMENT '孩子姓名快照',
    ADD COLUMN IF NOT EXISTS child_birthday DATE COMMENT '出生日期快照',
    ADD COLUMN IF NOT EXISTS child_gender VARCHAR(10) COMMENT '性别快照',
    ADD COLUMN IF NOT EXISTS child_age INT COMMENT '年龄快照',
    ADD COLUMN IF NOT EXISTS school VARCHAR(100) COMMENT '学校快照',
    ADD COLUMN IF NOT EXISTS grade VARCHAR(20) COMMENT '年级快照',
    ADD COLUMN IF NOT EXISTS grade_class VARCHAR(20) COMMENT '班级快照',
    ADD COLUMN IF NOT EXISTS height INT COMMENT '身高(CM)快照',
    ADD COLUMN IF NOT EXISTS weight INT COMMENT '体重(KG)快照',
    ADD COLUMN IF NOT EXISTS family_status VARCHAR(50) COMMENT '家庭状况快照',
    ADD COLUMN IF NOT EXISTS parent_degree VARCHAR(50) COMMENT '父母最高学历快照',
    ADD COLUMN IF NOT EXISTS nation VARCHAR(20) COMMENT '民族快照',
    ADD COLUMN IF NOT EXISTS province VARCHAR(50) COMMENT '省快照',
    ADD COLUMN IF NOT EXISTS city VARCHAR(50) COMMENT '市快照',
    ADD COLUMN IF NOT EXISTS district VARCHAR(50) COMMENT '区快照',
    ADD COLUMN IF NOT EXISTS address VARCHAR(200) COMMENT '详细地址快照',
    ADD COLUMN IF NOT EXISTS single_child VARCHAR(10) COMMENT '是否独生子女快照',
    ADD COLUMN IF NOT EXISTS school_score VARCHAR(20) COMMENT '在校成绩快照',
    ADD COLUMN IF NOT EXISTS urgent_name VARCHAR(50) COMMENT '紧急联系人姓名',
    ADD COLUMN IF NOT EXISTS urgent_phone VARCHAR(20) COMMENT '紧急联系人电话',
    ADD COLUMN IF NOT EXISTS pics TEXT COMMENT '图片IDs',
    ADD COLUMN IF NOT EXISTS videos TEXT COMMENT '视频IDs',
    ADD COLUMN IF NOT EXISTS audio VARCHAR(100) COMMENT '音频ID',
    ADD COLUMN IF NOT EXISTS audio_url VARCHAR(255) COMMENT '音频URL',
    ADD COLUMN IF NOT EXISTS process_desc TEXT COMMENT '过程描述',
    ADD COLUMN IF NOT EXISTS result_desc TEXT COMMENT '结果简评';

-- 为 assessment_appointments 表添加新字段
ALTER TABLE assessment_appointments
    ADD COLUMN IF NOT EXISTS user_id BIGINT COMMENT '用户ID',
    ADD COLUMN IF NOT EXISTS guide_id BIGINT COMMENT '成长规划师ID',
    ADD COLUMN IF NOT EXISTS package_id BIGINT COMMENT '套餐ID',
    ADD COLUMN IF NOT EXISTS remark VARCHAR(500) COMMENT '备注';

-- 创建 assessment_results 表（成长规划师录入的测评结果）
CREATE TABLE IF NOT EXISTS assessment_results (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    child_id BIGINT COMMENT '孩子ID',
    teacher_id BIGINT COMMENT '成长规划师ID',
    appointment_id BIGINT COMMENT '预约ID',
    dan_level VARCHAR(10) COMMENT 'DAN等级',
    attention_score INT COMMENT '注意力得分',
    focus_score INT COMMENT '专注力得分',
    memory_score INT COMMENT '记忆力得分',
    logic_score INT COMMENT '逻辑思维得分',
    overall_score INT COMMENT '综合得分',
    assessment_date DATETIME COMMENT '测评日期',
    duration_minutes INT COMMENT '测评时长',
    analysis_report TEXT COMMENT '分析报告',
    growth_suggestions TEXT COMMENT '成长建议',
    next_review_date DATE COMMENT '下次复测日期',
    status VARCHAR(20) DEFAULT 'draft' COMMENT '状态',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    child_name VARCHAR(50) COMMENT '孩子姓名快照',
    child_birthday DATE COMMENT '出生日期快照',
    child_gender VARCHAR(10) COMMENT '性别快照',
    child_age INT COMMENT '年龄快照',
    school VARCHAR(100) COMMENT '学校快照',
    grade VARCHAR(20) COMMENT '年级快照',
    grade_class VARCHAR(20) COMMENT '班级快照',
    height INT COMMENT '身高(CM)快照',
    weight INT COMMENT '体重(KG)快照',
    family_status VARCHAR(50) COMMENT '家庭状况快照',
    parent_degree VARCHAR(50) COMMENT '父母最高学历快照',
    nation VARCHAR(20) COMMENT '民族快照',
    province VARCHAR(50) COMMENT '省快照',
    city VARCHAR(50) COMMENT '市快照',
    district VARCHAR(50) COMMENT '区快照',
    address VARCHAR(200) COMMENT '详细地址快照',
    single_child VARCHAR(10) COMMENT '是否独生子女快照',
    school_score VARCHAR(20) COMMENT '在校成绩快照',
    urgent_name VARCHAR(50) COMMENT '紧急联系人姓名',
    urgent_phone VARCHAR(20) COMMENT '紧急联系人电话',
    pics TEXT COMMENT '图片IDs',
    videos TEXT COMMENT '视频IDs',
    audio VARCHAR(100) COMMENT '音频ID',
    audio_url VARCHAR(255) COMMENT '音频URL',
    process_desc TEXT COMMENT '过程描述',
    result_desc TEXT COMMENT '结果简评',
    INDEX idx_teacher (teacher_id),
    INDEX idx_child (child_id),
    INDEX idx_appointment (appointment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测评结果表（成长规划师录入）';