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

-- DAN测评任务系统数据库
-- 浠艾福

-- 1. 测评资料表
CREATE TABLE IF NOT EXISTS assessment_materials (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    title VARCHAR(100) NOT NULL COMMENT '资料标题',
    content TEXT COMMENT '测评介绍内容',
    cover_image VARCHAR(255) COMMENT '封面图片',
    status VARCHAR(20) DEFAULT 'active' COMMENT '状态:active/inactive',
    created_at DATETIME COMMENT '创建时间',
    updated_at DATETIME COMMENT '更新时间',
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测评资料表';

-- 2. 积分设置表
CREATE TABLE IF NOT EXISTS assessment_points_config (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    points_value INT DEFAULT 100 COMMENT '完成测评获得积分',
    points_value_yuan INT DEFAULT 1 COMMENT '1积分等于多少分(原元→现分)',
    is_active TINYINT DEFAULT 1 COMMENT '是否启用',
    created_at DATETIME COMMENT '创建时间',
    updated_at DATETIME COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分设置表';

-- 初始化默认积分配置
INSERT INTO assessment_points_config (points_value, points_value_yuan, is_active, created_at, updated_at)
VALUES (100, 1, 1, NOW(), NOW());

-- 3. 家庭测评配置表
CREATE TABLE IF NOT EXISTS family_assessment_config (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    material_id BIGINT COMMENT '测评资料ID',
    remind_months INT DEFAULT 12 COMMENT '提醒周期(月)',
    last_remind_at DATETIME COMMENT '上次提醒时间',
    next_remind_at DATETIME COMMENT '下次提醒时间',
    is_enabled TINYINT DEFAULT 1 COMMENT '是否启用:1启用0关闭',
    created_by BIGINT COMMENT '创建人(规划师ID)',
    created_at DATETIME COMMENT '创建时间',
    updated_at DATETIME COMMENT '更新时间',
    UNIQUE KEY uk_family (family_id),
    INDEX idx_next_remind (next_remind_at),
    INDEX idx_enabled (is_enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭测评配置表';

-- 4. 测评记录表
CREATE TABLE IF NOT EXISTS assessment_records (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    family_id BIGINT COMMENT '家庭ID',
    child_id BIGINT COMMENT '孩子ID',
    material_id BIGINT COMMENT '测评资料ID',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态:pending待完成/completed已完成',
    completed_at DATETIME COMMENT '完成时间',
    created_at DATETIME COMMENT '创建时间',
    INDEX idx_family (family_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测评记录表';

-- 5. 扩展任务模板表 - 添加系统任务字段
ALTER TABLE admin_task_templates
ADD COLUMN task_level VARCHAR(20) DEFAULT 'normal' COMMENT '任务级别:normal普通/system系统',
ADD COLUMN is_system_task TINYINT DEFAULT 0 COMMENT '是否系统任务:0否1是',
ADD COLUMN assessment_category VARCHAR(50) COMMENT '测评分类:DAN/其他',
ADD COLUMN remind_months INT COMMENT '提醒周期(月)';

-- 创建索引
CREATE INDEX idx_task_level ON admin_task_templates(task_level);
CREATE INDEX idx_is_system_task ON admin_task_templates(is_system_task);

-- ============================================
-- 认知常模表（百分位参考）
-- ============================================
CREATE TABLE IF NOT EXISTS cognitive_norms (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    dimension_key VARCHAR(50) NOT NULL COMMENT '维度键:perception/focus/memory/logic/spatial/processingSpeed',
    gender VARCHAR(10) DEFAULT 'all' COMMENT '性别:male/female/all',
    age_min INT NOT NULL COMMENT '最小月龄（含）',
    age_max INT NOT NULL COMMENT '最大月龄（不含）',
    percentile5 INT COMMENT '第5百分位分数',
    percentile25 INT COMMENT '第25百分位分数',
    percentile50 INT COMMENT '第50百分位分数(中位数)',
    percentile75 INT COMMENT '第75百分位分数',
    percentile95 INT COMMENT '第95百分位分数',
    source VARCHAR(100) DEFAULT 'DAN常模' COMMENT '数据来源',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_dimension_age (dimension_key, gender, age_min, age_max)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='认知维度百分位常模表';

-- 种子数据: 6-12岁 各维度常模（年龄月: 72-144月, 性别: all）
-- 感知能力
INSERT INTO cognitive_norms (dimension_key, gender, age_min, age_max, percentile5, percentile25, percentile50, percentile75, percentile95, source) VALUES
('perception', 'all', 0, 84, 20, 35, 50, 65, 80, 'DAN常模'),
('perception', 'all', 84, 108, 25, 40, 55, 70, 85, 'DAN常模'),
('perception', 'all', 108, 144, 30, 45, 60, 75, 88, 'DAN常模'),
('perception', 'all', 144, 216, 35, 50, 65, 78, 90, 'DAN常模'),
('focus', 'all', 0, 84, 15, 28, 42, 55, 70, 'DAN常模'),
('focus', 'all', 84, 108, 20, 33, 48, 62, 78, 'DAN常模'),
('focus', 'all', 108, 144, 25, 38, 53, 68, 82, 'DAN常模'),
('focus', 'all', 144, 216, 30, 45, 58, 72, 85, 'DAN常模'),
('memory', 'all', 0, 84, 18, 32, 46, 60, 75, 'DAN常模'),
('memory', 'all', 84, 108, 22, 37, 52, 65, 80, 'DAN常模'),
('memory', 'all', 108, 144, 28, 42, 56, 70, 84, 'DAN常模'),
('memory', 'all', 144, 216, 32, 47, 60, 74, 86, 'DAN常模'),
('logic', 'all', 0, 84, 12, 25, 38, 52, 65, 'DAN常模'),
('logic', 'all', 84, 108, 18, 30, 45, 58, 72, 'DAN常模'),
('logic', 'all', 108, 144, 25, 38, 52, 65, 78, 'DAN常模'),
('logic', 'all', 144, 216, 30, 45, 58, 70, 82, 'DAN常模'),
('spatial', 'all', 0, 84, 15, 28, 40, 55, 70, 'DAN常模'),
('spatial', 'all', 84, 108, 20, 35, 48, 62, 76, 'DAN常模'),
('spatial', 'all', 108, 144, 28, 40, 55, 68, 80, 'DAN常模'),
('spatial', 'all', 144, 216, 32, 45, 58, 72, 84, 'DAN常模'),
('processingSpeed', 'all', 0, 84, 10, 22, 35, 48, 62, 'DAN常模'),
('processingSpeed', 'all', 84, 108, 15, 28, 42, 55, 70, 'DAN常模'),
('processingSpeed', 'all', 108, 144, 22, 35, 48, 62, 75, 'DAN常模'),
('processingSpeed', 'all', 144, 216, 28, 42, 55, 68, 80, 'DAN常模');

-- ============================================
-- 训练方案系统（guide端训练计划管理）
-- ============================================
CREATE TABLE IF NOT EXISTS training_plans (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    guide_id BIGINT NOT NULL COMMENT '规划师ID',
    child_id BIGINT NOT NULL COMMENT '孩子ID',
    title VARCHAR(200) NOT NULL COMMENT '方案标题',
    description TEXT COMMENT '方案描述',
    status VARCHAR(20) DEFAULT 'draft' COMMENT '状态:draft草稿/published已下发/completed已完成',
    source VARCHAR(20) DEFAULT 'manual' COMMENT '来源:manual手动/recommendations推荐生成',
    deployed_at DATETIME COMMENT '下发时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_guide (guide_id),
    INDEX idx_child (child_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='训练方案表';

CREATE TABLE IF NOT EXISTS training_plan_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    plan_id BIGINT NOT NULL COMMENT '所属方案ID',
    sequence INT DEFAULT 0 COMMENT '排序号',
    dimension_key VARCHAR(50) COMMENT '认知维度:perception/focus/memory/logic/spatial/processingSpeed',
    suggestion TEXT COMMENT '建议文案',
    task_title VARCHAR(200) COMMENT '任务标题',
    task_desc TEXT COMMENT '任务描述',
    minigame_code VARCHAR(50) COMMENT '关联小游戏code',
    article_id BIGINT COMMENT '关联文章ID',
    task_points INT DEFAULT 2 COMMENT '任务积分',
    deadline_days INT DEFAULT 7 COMMENT '截止天数(从部署日起)',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态:pending/completed/skipped',
    completed_at DATETIME COMMENT '完成时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_plan (plan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='训练方案项目表';

CREATE TABLE IF NOT EXISTS training_plan_tasks (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    plan_id BIGINT NOT NULL COMMENT '方案ID',
    plan_item_id BIGINT NOT NULL COMMENT '方案项目ID',
    task_id BIGINT NOT NULL COMMENT '实际Task ID',
    child_id BIGINT NOT NULL COMMENT '执行孩子ID',
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/completed',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_plan (plan_id),
    INDEX idx_task (task_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='训练方案任务关联表';

-- ============================================
-- 活动管理（guide端活动创建/编辑/发布）
-- ============================================
CREATE TABLE IF NOT EXISTS guide_activities (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    guide_id BIGINT NOT NULL COMMENT '规划师ID',
    title VARCHAR(200) NOT NULL COMMENT '活动标题',
    description TEXT COMMENT '活动描述',
    cover_image VARCHAR(500) COMMENT '封面图片URL',
    category VARCHAR(50) DEFAULT 'other' COMMENT '分类:outdoor/nature/culture/sports/other',
    location VARCHAR(300) COMMENT '活动地点',
    activity_date DATETIME COMMENT '活动日期时间',
    max_participants INT DEFAULT 20 COMMENT '最大参与人数',
    current_participants INT DEFAULT 0 COMMENT '当前报名人数',
    price BIGINT DEFAULT 0 COMMENT '价格(分),0表示免费',
    status VARCHAR(20) DEFAULT 'draft' COMMENT '状态:draft草稿/published已发布/cancelled已取消/completed已完成',
    source VARCHAR(20) DEFAULT 'manual' COMMENT '来源',
    published_at DATETIME COMMENT '发布时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_guide (guide_id),
    INDEX idx_status (status),
    INDEX idx_activity_date (activity_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='规划师活动表';