cfc/zxyj-backend/src/main/resources/schema-wishes.sql

-- 心愿表 (wishes)
-- 用途：每个人的实际心愿，与心愿库(rewards)区分
-- 创建时间: 2026-04-15

CREATE TABLE IF NOT EXISTS wishes (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    
    -- 归属信息
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    child_id BIGINT NOT NULL COMMENT '目标孩子ID',
    created_by BIGINT NOT NULL COMMENT '创建者用户ID',
    creator_type VARCHAR(10) NOT NULL COMMENT '创建者类型: child/parent',
    
    -- 心愿内容
    title VARCHAR(200) NOT NULL COMMENT '心愿名称',
    description TEXT COMMENT '详细描述',
    category VARCHAR(50) COMMENT '分类',
    
    -- 积分设置
    points_required INT COMMENT '所需积分',
    points_set_by BIGINT COMMENT '定价者ID',
    points_set_at DATETIME COMMENT '定价时间',
    
    -- 状态 (个人心愿状态机)
    status VARCHAR(20) DEFAULT 'draft' COMMENT '状态: draft/active/pending_exchange/approved/exchanging/fulfilled/cancelled/rejected',
    
    -- 兑换流程
    exchange_requested_at DATETIME COMMENT '申请兑换时间',
    exchange_approved_by BIGINT COMMENT '批准者ID',
    exchange_approved_at DATETIME COMMENT '批准时间',
    points_deducted_at DATETIME COMMENT '扣积分时间',
    
    -- 兑现流程
    fulfilled_by BIGINT COMMENT '兑现者ID',
    fulfilled_at DATETIME COMMENT '兑现时间',
    child_confirmed_at DATETIME COMMENT '孩子确认收货时间',
    
    -- 拒绝记录
    rejected_by BIGINT COMMENT '拒绝者ID',
    rejected_at DATETIME COMMENT '拒绝时间',
    reject_reason VARCHAR(500) COMMENT '拒绝原因',
    reject_count INT DEFAULT 0 COMMENT '被拒绝次数',
    
    -- 取消记录
    cancelled_by BIGINT COMMENT '取消者ID',
    cancelled_at DATETIME COMMENT '取消时间',
    cancel_reason VARCHAR(500) COMMENT '取消原因',
    
    -- 时间戳
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 索引
    INDEX idx_family (family_id),
    INDEX idx_child (child_id),
    INDEX idx_status (status),
    INDEX idx_creator (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='个人心愿表';