-- =====================================================
-- 成长规划师注册与升级系统数据库表
-- version: 1.1
-- date: 2026-05-05
-- =====================================================

-- 成长规划师申请表（注册/升级申请）
CREATE TABLE IF NOT EXISTS `guide_applications` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `user_id` BIGINT NOT NULL COMMENT '申请人用户ID',
    `type` VARCHAR(20) NOT NULL DEFAULT 'new_apply' COMMENT '申请类型: new_apply-新注册, upgrade-升级',
    `target_level` VARCHAR(20) NOT NULL COMMENT '目标等级: junior-初级, intermediate-中级, senior-高级',
    `current_level` VARCHAR(20) DEFAULT NULL COMMENT '当前等级（升级时填写）',
    `package_id` BIGINT COMMENT '申请的套餐ID',
    `payment_proof` VARCHAR(500) COMMENT '付款凭证URL',
    `payment_amount` INT COMMENT '付款金额(分)',
    `status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '状态: pending-待审核, approved-已通过, rejected-已拒绝',
    `reject_reason` VARCHAR(500) COMMENT '拒绝原因',
    `reviewed_at` DATETIME COMMENT '审核时间',
    `reviewed_by` BIGINT COMMENT '审核人ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长规划师申请表';

-- 成长规划师表（已认证的规划师）
CREATE TABLE IF NOT EXISTS `guides` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `user_id` BIGINT NOT NULL UNIQUE COMMENT '用户ID（唯一）',
    `level` VARCHAR(20) NOT NULL DEFAULT 'junior' COMMENT '等级: junior-初级, intermediate-中级, senior-高级',
    `status` VARCHAR(20) NOT NULL DEFAULT 'active' COMMENT '状态: active-活跃, inactive-停用',
    `service_count` INT DEFAULT 0 COMMENT '当前服务家庭数',
    `package_id` BIGINT COMMENT '购买套餐ID',
    `nickname` VARCHAR(100) COMMENT '规划师昵称',
    `phone` VARCHAR(20) COMMENT '联系电话',
    `intro` TEXT COMMENT '个人介绍',
    `avatar` VARCHAR(500) COMMENT '头像URL',
    `commission_rate` INT DEFAULT 1000 COMMENT '佣金比例(基点,1000=10%)',
    `approved_at` DATETIME COMMENT '审核通过时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_level` (`level`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长规划师表';

-- 升级服务表（可供购买的升级服务）
CREATE TABLE IF NOT EXISTS `upgrade_services` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `from_level` VARCHAR(20) NOT NULL COMMENT '原等级: junior, intermediate, senior',
    `to_level` VARCHAR(20) NOT NULL COMMENT '目标等级: junior, intermediate, senior',
    `name` VARCHAR(100) NOT NULL COMMENT '服务名称',
    `description` TEXT COMMENT '描述',
    `price` INT NOT NULL COMMENT '价格(分)',
    `service_months` INT DEFAULT 12 COMMENT '服务期限（月）',
    `service_limit_add` INT DEFAULT 0 COMMENT '额外增加的服务名额',
    `is_active` TINYINT DEFAULT 1 COMMENT '是否上架: 0-否, 1-是',
    `sort_order` INT DEFAULT 0 COMMENT '排序',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY `uk_from_to` (`from_level`, `to_level`),
    INDEX `idx_from_level` (`from_level`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='升级服务表';

-- 规划师套餐表（管理员配置的套餐模板）
CREATE TABLE IF NOT EXISTS `guide_package_templates` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `name` VARCHAR(100) NOT NULL COMMENT '套餐名称',
    `level` VARCHAR(20) NOT NULL COMMENT '等级: junior, intermediate, senior',
    `price` INT NOT NULL COMMENT '价格(分)',
    `min_price` INT COMMENT '最低价(分)',
    `description` TEXT COMMENT '套餐描述',
    `service_months` INT DEFAULT 12 COMMENT '服务期限（月）',
    `service_limit` INT DEFAULT 50 COMMENT '服务名额限制',
    `features` JSON COMMENT '功能权限列表',
    `is_active` TINYINT DEFAULT 1 COMMENT '是否上架: 0-否, 1-是',
    `sort_order` INT DEFAULT 0 COMMENT '排序',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX `idx_level` (`level`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='规划师套餐模板表';

-- 规划师-家庭关联表
CREATE TABLE IF NOT EXISTS `guide_family_relations` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `guide_id` BIGINT NOT NULL COMMENT '规划师ID',
    `family_id` BIGINT NOT NULL COMMENT '家庭ID',
    `service_type` VARCHAR(50) DEFAULT 'basic' COMMENT '服务类型: basic-基础, premium-高级',
    `service_price` INT DEFAULT 0 COMMENT '服务价格(分)',
    `status` VARCHAR(20) DEFAULT 'active' COMMENT '状态: active-服务中, paused-已暂停',
    `bound_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '绑定时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY `uk_guide_family` (`guide_id`, `family_id`),
    INDEX `idx_guide_id` (`guide_id`),
    INDEX `idx_family_id` (`family_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='规划师-家庭关联表';
CREATE TABLE IF NOT EXISTS `guide_invite_codes` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `guide_id` BIGINT NOT NULL COMMENT '成长规划师ID',
  `code` VARCHAR(20) NOT NULL COMMENT '邀请码',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1-有效，0-已失效',
  `use_count` INT NOT NULL DEFAULT 0 COMMENT '使用次数',
  `max_use_count` INT NOT NULL DEFAULT 100 COMMENT '最大使用次数',
  `expire_time` DATETIME COMMENT '过期时间',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`),
  KEY `idx_guide_id` (`guide_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长规划师邀请码表';

-- 成长规划师层级关系表
CREATE TABLE IF NOT EXISTS `guide_hierarchy` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `parent_id` BIGINT NOT NULL COMMENT '上级成长规划师ID',
  `child_id` BIGINT NOT NULL COMMENT '下级成长规划师ID',
  `level` INT NOT NULL DEFAULT 1 COMMENT '层级深度：1-直接下级，2-二级下级，以此类推',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_parent_child` (`parent_id`, `child_id`),
  KEY `idx_child_id` (`child_id`),
  KEY `idx_level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长规划师层级关系表';

-- 成长规划师套餐表
CREATE TABLE IF NOT EXISTS `guide_packages` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `guide_id` BIGINT NOT NULL COMMENT '成长规划师ID',
  `package_id` BIGINT NOT NULL COMMENT '套餐ID',
  `price` INT NOT NULL COMMENT '成长规划师定价(分)',
  `commission_rate` INT NOT NULL DEFAULT 0 COMMENT '上级佣金比例(基点)',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1-上架，0-下架',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_guide_package` (`guide_id`, `package_id`),
  KEY `idx_package_id` (`package_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长规划师套餐表';

-- 订单表
CREATE TABLE IF NOT EXISTS `orders` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `order_no` VARCHAR(32) NOT NULL COMMENT '订单号',
  `user_id` BIGINT NOT NULL COMMENT '用户ID',
  `guide_id` BIGINT COMMENT '成长规划师ID',
  `package_id` BIGINT NOT NULL COMMENT '套餐ID',
  `package_name` VARCHAR(100) NOT NULL COMMENT '套餐名称',
  `amount` INT NOT NULL COMMENT '订单金额(分)',
  `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0-待支付，1-已支付，2-已取消，3-已退款',
  `payment_method` VARCHAR(20) COMMENT '支付方式：wechat-微信支付，alipay-支付宝',
  `payment_time` DATETIME COMMENT '支付时间',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_guide_id` (`guide_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 支付记录表
CREATE TABLE IF NOT EXISTS `payment_records` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `order_no` VARCHAR(32) NOT NULL COMMENT '订单号',
  `transaction_id` VARCHAR(64) COMMENT '第三方交易号',
  `payment_method` VARCHAR(20) NOT NULL COMMENT '支付方式：wechat-微信支付，alipay-支付宝',
  `amount` INT NOT NULL COMMENT '支付金额(分)',
  `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0-待支付，1-支付成功，2-支付失败',
  `notify_time` DATETIME COMMENT '回调时间',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付记录表';

-- 测评预约表
CREATE TABLE IF NOT EXISTS `assessment_appointments` (
  `id` BIGINT NOT NULL AUTO_INCREMENT 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',
  `appointment_date` DATE NOT NULL COMMENT '预约日期',
  `appointment_time` VARCHAR(20) NOT NULL COMMENT '预约时间段',
  `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0-待确认，1-已确认，2-已完成，3-已取消',
  `remark` VARCHAR(500) COMMENT '备注',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_guide_id` (`guide_id`),
  KEY `idx_appointment_date` (`appointment_date`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测评预约表';

-- 测评结果表
CREATE TABLE IF NOT EXISTS `assessment_results` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `appointment_id` BIGINT COMMENT '预约ID',
  `user_id` BIGINT NOT NULL COMMENT '用户ID',
  `child_id` BIGINT COMMENT '孩子ID',
  `child_name` VARCHAR(50) COMMENT '孩子姓名',
  `guide_id` BIGINT NOT NULL COMMENT '成长规划师ID',
  `guide_name` VARCHAR(50) COMMENT '成长规划师姓名',
  `assessment_date` DATE NOT NULL COMMENT '测评日期',
  `duration_minutes` INT COMMENT '测评时长（分钟）',
  `attention_score` INT COMMENT '注意力得分',
  `focus_score` INT COMMENT '专注力得分',
  `memory_score` INT COMMENT '记忆力得分',
  `logic_score` INT COMMENT '逻辑思维得分',
  `overall_score` INT COMMENT '综合得分',
  `analysis_report` TEXT COMMENT '分析报告',
  `growth_suggestions` TEXT COMMENT '成长建议',
  `next_review_date` DATE COMMENT '下次复测日期',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_guide_id` (`guide_id`),
  KEY `idx_assessment_date` (`assessment_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测评结果表';