cfc/cfc-backend/sql/init.sql

-- 知行益家数据库初始化脚本
-- 创建数据库
CREATE DATABASE IF NOT EXISTS xzyj DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE xzyj;

-- 家庭表
CREATE TABLE IF NOT EXISTS families (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    invite_code VARCHAR(8) UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_invite_code (invite_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 用户表
CREATE TABLE IF NOT EXISTS users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    openid VARCHAR(64) NOT NULL,
    unionid VARCHAR(64),
    family_id BIGINT NOT NULL,
    role ENUM('parent', 'child') NOT NULL,
    nickname VARCHAR(50),
    avatar VARCHAR(255),
    password VARCHAR(128),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_openid (openid),
    INDEX idx_family_id (family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 孩子详情表
CREATE TABLE IF NOT EXISTS children (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    family_id BIGINT NOT NULL,
    nickname VARCHAR(50) NOT NULL,
    age TINYINT NOT NULL,
    dan_level VARCHAR(2),
    penalty_enabled TINYINT DEFAULT 1 COMMENT '是否开启扣分:0否1是',
    theme VARCHAR(32) DEFAULT 'default',
    total_points INT DEFAULT 0,
    streak_days INT DEFAULT 0,
    last_task_date DATE,
    focus_max_daily TINYINT DEFAULT 3,
    focus_remaining TINYINT DEFAULT 3,
    focus_reset_date DATE DEFAULT (CURRENT_DATE),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_family_id (family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 任务表
CREATE TABLE IF NOT EXISTS tasks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL,
    creator_id BIGINT NOT NULL,
    child_id BIGINT NOT NULL,
    title VARCHAR(200) NOT NULL,
    description VARCHAR(500),
    points TINYINT DEFAULT 2 COMMENT '任务积分1-10',
    deadline DATETIME NOT NULL,
    repeat_type ENUM('none', 'daily', 'weekly') DEFAULT 'none',
    category VARCHAR(32),
    need_review TINYINT DEFAULT 0 COMMENT '是否需要审核',
    review_by_category TINYINT DEFAULT 0 COMMENT '是否按分类审核',
    status ENUM('pending', 'completed', 'overdue', 'cancelled') DEFAULT 'pending',
    completed_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_child_id_deadline (child_id, deadline),
    INDEX idx_status (status),
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 积分流水表
CREATE TABLE IF NOT EXISTS points_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL,
    task_id BIGINT,
    amount INT NOT NULL COMMENT '积分数量正负',
    type ENUM('earn', 'spend', 'bonus', 'penalty', 'adjust', 'reset', 'refund') NOT NULL,
    description VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_child_id_created (child_id, created_at),
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 积分清零记录表
CREATE TABLE IF NOT EXISTS points_reset_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL,
    balance_before INT NOT NULL,
    balance_after INT DEFAULT 0,
    reset_type ENUM('monthly', 'manual') NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_child_id (child_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 奖励表
CREATE TABLE IF NOT EXISTS rewards (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL,
    child_id BIGINT NOT NULL,
    title VARCHAR(100) NOT NULL,
    points_required INT NOT NULL,
    category VARCHAR(32),
    status ENUM('available', 'pending', 'exchanged', 'rejected') DEFAULT 'available',
    exchange_approved TINYINT COMMENT '是否审批通过',
    exchanged_at DATETIME,
    is_template TINYINT DEFAULT 0 COMMENT '是否为预设模板',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_child_id_status (child_id, status),
    INDEX idx_is_template (is_template)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 奖励分类表
CREATE TABLE IF NOT EXISTS reward_categories (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL,
    name VARCHAR(32) NOT NULL,
    icon VARCHAR(64),
    sort_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_family_id (family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 预设奖励模板数据
INSERT INTO rewards (family_id, child_id, title, points_required, category, is_template) VALUES
(0, 0, '看动画片30分钟', 30, '精神类', 1),
(0, 0, '玩平板游戏30分钟', 40, '特权类', 1),
(0, 0, '去游乐场', 100, '物质类', 1),
(0, 0, '买玩具', 150, '物质类', 1),
(0, 0, '吃冰淇淋', 20, '物质类', 1),
(0, 0, '选择晚餐', 25, '特权类', 1);

-- 默认奖励分类
INSERT INTO reward_categories (family_id, name, icon, sort_order) VALUES
(0, '物质类', 'gift', 1),
(0, '精神类', 'star', 2),
(0, '特权类', 'crown', 3);

-- 敏感词表
CREATE TABLE IF NOT EXISTS sensitive_words (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    word VARCHAR(50) NOT NULL,
    category VARCHAR(32) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_word (word)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 任务审核表
CREATE TABLE IF NOT EXISTS task_reviews (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    task_id BIGINT NOT NULL,
    child_id BIGINT NOT NULL,
    photo_url VARCHAR(255),
    voice_note_url VARCHAR(255),
    ai_result ENUM('pass', 'review', 'pending'),
    ai_confidence DECIMAL(3,2),
    ai_suggestion VARCHAR(255),
    ai_threshold ENUM('strict', 'normal', 'loose') DEFAULT 'normal',
    parent_result ENUM('pass', 'reject'),
    parent_override TINYINT DEFAULT 0,
    review_type ENUM('ai', 'manual') NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    reviewed_at DATETIME,
    INDEX idx_task_id (task_id),
    INDEX idx_ai_result (ai_result)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 勋章定义表
CREATE TABLE IF NOT EXISTS badge_definitions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    badge_type VARCHAR(32) NOT NULL,
    name VARCHAR(50) NOT NULL,
    description VARCHAR(200),
    icon VARCHAR(64),
    condition_type VARCHAR(32) NOT NULL,
    condition_value INT NOT NULL,
    reward_points INT DEFAULT 0,
    INDEX idx_badge_type (badge_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 勋章表
CREATE TABLE IF NOT EXISTS badges (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL,
    badge_type VARCHAR(32) NOT NULL,
    unlocked_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_child_id_type (child_id, badge_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 打卡里程碑表
CREATE TABLE IF NOT EXISTS streak_milestones (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    days INT NOT NULL,
    reward_points INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入打卡里程碑数据
INSERT INTO streak_milestones (days, reward_points) VALUES
(3, 5),
(7, 10),
(30, 30),
(100, 100);

-- 专注训练记录表
CREATE TABLE IF NOT EXISTS focus_sessions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL,
    mode ENUM('simple', 'pomodoro') NOT NULL,
    duration TINYINT NOT NULL,
    stars TINYINT COMMENT '评分1-5',
    points_earned INT,
    voice_note_url VARCHAR(255),
    started_at DATETIME NOT NULL,
    completed_at DATETIME,
    INDEX idx_child_id_completed (child_id, completed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 专注训练配置表
CREATE TABLE IF NOT EXISTS focus_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT,
    max_daily_count TINYINT DEFAULT 3,
    allow_exit TINYINT DEFAULT 1 COMMENT '是否允许退出0否1是',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- DAN测评表
CREATE TABLE IF NOT EXISTS dan_assessments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL,
    dan_level VARCHAR(2) NOT NULL,
    assessment_date DATE NOT NULL,
    details JSON,
    source ENUM('api', 'manual') DEFAULT 'manual',
    photo_url VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_child_id_level (child_id, dan_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 任务模板表
CREATE TABLE IF NOT EXISTS task_templates (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dan_level VARCHAR(2) NOT NULL,
    title VARCHAR(200) NOT NULL,
    points TINYINT DEFAULT 2,
    duration_minutes TINYINT,
    category VARCHAR(32),
    is_default TINYINT DEFAULT 1,
    family_id BIGINT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_dan_level (dan_level),
    INDEX idx_is_default (is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;