-- 评估订单表
CREATE TABLE IF NOT EXISTS assessment_orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(64) NOT NULL UNIQUE COMMENT '订单号',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    child_id BIGINT COMMENT '孩子ID',
    guide_id BIGINT NOT NULL COMMENT '成长规划师ID',
    package_id BIGINT NOT NULL COMMENT '套餐ID',
    guide_name VARCHAR(50) COMMENT '规划师姓名快照',
    package_name VARCHAR(100) COMMENT '套餐名称快照',
    total_price BIGINT DEFAULT 0 COMMENT '总价(分)',
    discount_amount BIGINT DEFAULT 0 COMMENT '优惠金额(分)',
    actual_price BIGINT DEFAULT 0 COMMENT '实付金额(分)',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态',
    pay_time VARCHAR(50) COMMENT '支付时间',
    pay_type VARCHAR(20) COMMENT '支付方式',
    transaction_id VARCHAR(64) COMMENT '交易号',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_family (family_id),
    INDEX idx_guide (guide_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评估订单表';