# 数据库设计文档（订单 / 支付 / 邀请模块）

## 一、订单模块

### 1.1 orders 表 - 订单主表

```sql
CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(50) NOT NULL UNIQUE COMMENT '订单号(全局唯一)',
  user_id BIGINT NOT NULL COMMENT '用户ID',
  total_amount DECIMAL(10, 2) NOT NULL COMMENT '应付总金额(元)',
  cash_amount DECIMAL(10, 2) DEFAULT 0.00 COMMENT '现金支付部分',
  points_used INT DEFAULT 0 COMMENT '使用积分数',
  points_deduct_amount DECIMAL(10, 2) DEFAULT 0.00 COMMENT '积分抵扣金额',
  status ENUM('pending','paid','completed','cancelled','refunding','refunded') DEFAULT 'pending',
  payment_method ENUM('wechat','alipay','points','mixed') COMMENT '支付方式',
  remark VARCHAR(500) COMMENT '备注',
  cancel_reason VARCHAR(255) COMMENT '取消原因',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  paid_at TIMESTAMP NULL COMMENT '支付时间',
  expired_at TIMESTAMP NULL COMMENT '超时取消时间',
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_user_id (user_id),
  INDEX idx_status (status),
  INDEX idx_order_no (order_no),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';
```

**订单号生成规则**：`ORD + 年月日时分秒 + 6位序列号`，例如 `ORD20260316143022000001`

**状态流转**：
```
pending → paid → completed
pending → cancelled（超时/主动取消）
paid    → refunding → refunded
```

### 1.2 order_items 表 - 订单项

```sql
CREATE TABLE order_items (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL COMMENT '订单ID',
  skill_id BIGINT NOT NULL COMMENT 'SkillID',
  skill_name VARCHAR(200) NOT NULL COMMENT 'Skill名称快照',
  skill_cover VARCHAR(500) COMMENT 'Skill封面快照(腾讯云COS)',
  unit_price DECIMAL(10, 2) NOT NULL COMMENT '下单时单价快照',
  quantity INT DEFAULT 1,
  total_price DECIMAL(10, 2) NOT NULL COMMENT '小计',
  FOREIGN KEY (order_id) REFERENCES orders(id),
  INDEX idx_order_id (order_id),
  INDEX idx_skill_id (skill_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单项表';
```

### 1.3 order_refunds 表 - 退款记录

```sql
CREATE TABLE order_refunds (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL COMMENT '订单ID',
  refund_no VARCHAR(50) NOT NULL UNIQUE COMMENT '退款单号',
  refund_amount DECIMAL(10, 2) NOT NULL COMMENT '退款金额',
  refund_points INT DEFAULT 0 COMMENT '退回积分',
  reason VARCHAR(255) COMMENT '退款原因',
  images JSON COMMENT '凭证图片(腾讯云COS URL数组)',
  status ENUM('pending','approved','rejected','completed') DEFAULT 'pending',
  reject_reason VARCHAR(255) COMMENT '拒绝原因',
  operator_id BIGINT COMMENT '处理人(管理员ID)',
  processed_at TIMESTAMP NULL COMMENT '处理时间',
  remark VARCHAR(255) COMMENT '处理备注',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  completed_at TIMESTAMP NULL COMMENT '退款完成时间',
  FOREIGN KEY (order_id) REFERENCES orders(id),
  INDEX idx_order_id (order_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='退款表';
```

> **变更说明**：原 `handled_by` 重命名为 `operator_id`；新增 `processed_at`（处理时间）和 `remark`（处理备注），与 `AdminServiceImpl.processRefund()` 对齐。

## 二、支付模块

### 2.1 recharge_orders 表 - 充值订单

```sql
CREATE TABLE recharge_orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  recharge_no VARCHAR(50) NOT NULL UNIQUE COMMENT '充值单号',
  user_id BIGINT NOT NULL,
  amount DECIMAL(10, 2) NOT NULL COMMENT '充值金额(元)',
  bonus_points INT DEFAULT 0 COMMENT '赠送积分',
  total_points INT NOT NULL COMMENT '到账总积分(按金额换算+赠送)',
  payment_method ENUM('wechat','alipay') NOT NULL,
  status ENUM('pending','paid','failed','cancelled') DEFAULT 'pending',
  transaction_id VARCHAR(100) COMMENT '微信/支付宝交易流水号',
  notify_data TEXT COMMENT '支付回调原始数据',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  paid_at TIMESTAMP NULL COMMENT '支付完成时间',
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_user_id (user_id),
  INDEX idx_status (status),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='充值订单表';
```

**充值赠送规则**（在 `application.yml` 中配置）：

| 充值金额 | 赠送积分 | 到账总积分 |
|---------|---------|----------|
| ¥10     | 10      | 1010      |
| ¥50     | 60      | 5060      |
| ¥100    | 150     | 10150     |
| ¥500    | 800     | 50800     |
| ¥1000   | 2000    | 102000    |

> 到账总积分 = 充值金额 × 100（1元=100积分）+ 赠送积分

### 2.2 payment_records 表 - 支付流水

```sql
CREATE TABLE payment_records (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  biz_type ENUM('order','recharge') NOT NULL COMMENT '业务类型',
  biz_id BIGINT NOT NULL COMMENT '业务ID(order_id 或 recharge_id)',
  biz_no VARCHAR(50) NOT NULL COMMENT '业务单号',
  amount DECIMAL(10, 2) NOT NULL COMMENT '支付金额',
  payment_method ENUM('wechat','alipay','points') NOT NULL,
  transaction_id VARCHAR(100) COMMENT '三方交易号',
  status ENUM('pending','success','failed') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_user_id (user_id),
  INDEX idx_biz_no (biz_no),
  INDEX idx_transaction_id (transaction_id),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='支付流水表';
```

## 三、邀请模块

### 3.1 invite_codes 表 - 邀请码

```sql
CREATE TABLE invite_codes (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL COMMENT '邀请人ID',
  code VARCHAR(50) NOT NULL UNIQUE COMMENT '邀请码(大写字母+数字)',
  invite_url VARCHAR(500) COMMENT '邀请链接',
  is_active BOOLEAN DEFAULT TRUE COMMENT '是否启用',
  use_count INT DEFAULT 0 COMMENT '已使用次数',
  max_use_count INT DEFAULT -1 COMMENT '最大使用次数(-1为不限)',
  expired_at TIMESTAMP NULL COMMENT '过期时间(NULL为永不过期)',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  UNIQUE KEY uk_user_code (user_id),
  INDEX idx_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='邀请码表';
```

> **变更说明**：原 `status ENUM('active','inactive')` 改为 `is_active BOOLEAN`，`used_count` 重命名为 `use_count`；新增 `max_use_count`（最大使用次数）、`expired_at`（过期时间）、`updated_at`，与 `InviteCode.java` 实体对齐。

### 3.2 invite_records 表 - 邀请记录

```sql
CREATE TABLE invite_records (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  inviter_id BIGINT NOT NULL COMMENT '邀请人ID',
  invitee_id BIGINT NOT NULL COMMENT '被邀请人ID',
  invite_code VARCHAR(50) COMMENT '使用的邀请码',
  status ENUM('registered','first_paid') DEFAULT 'registered' COMMENT '状态',
  inviter_reward_points INT DEFAULT 0 COMMENT '邀请人获得积分',
  invitee_reward_points INT DEFAULT 0 COMMENT '被邀请人获得积分',
  reward_given BOOLEAN DEFAULT FALSE COMMENT '奖励是否已发放',
  rewarded_at TIMESTAMP NULL COMMENT '奖励发放时间',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (inviter_id) REFERENCES users(id),
  FOREIGN KEY (invitee_id) REFERENCES users(id),
  UNIQUE KEY uk_invitee (invitee_id),
  INDEX idx_inviter_id (inviter_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='邀请记录表';
```

> **变更说明**：字段 `inviter_reward_points` / `invitee_reward_points` 与 `InviteRecord.java` 实体对齐；新增 `rewarded_at` 字段。

## 四、Redis 缓存 Key 设计

| Key | 说明 | TTL |
|-----|------|-----|
| `user:info:{userId}` | 用户基本信息缓存 | 30分钟 |
| `user:token:{token}` | JWT Token黑名单(登出时写入) | 与token同期 |
| `user:points:{userId}` | 用户积分余额缓存 | 5分钟 |
| `skill:detail:{skillId}` | Skill详情缓存 | 10分钟 |
| `skill:hot:list` | 热门Skill列表 | 1小时 |
| `skill:category:list` | 分类列表 | 24小时 |
| `sign_in:lock:{userId}:{date}` | 签到幂等锁 | 24小时 |
| `invite:code:{code}` | 邀请码验证缓存 | 1小时 |
| `order:lock:{orderNo}` | 订单支付分布式锁 | 30秒 |
| `captcha:sms:{phone}` | 短信验证码 | 5分钟 |

## 五、数据库初始化 SQL 执行顺序

```
1. V1__init_users.sql          -- users, user_profiles, user_auth
2. V2__init_skills.sql         -- skill_categories, skills, skill_reviews, skill_downloads
3. V3__init_points.sql         -- user_points, points_records, points_rules
4. V4__init_orders.sql         -- orders, order_items, order_refunds
5. V5__init_payments.sql       -- recharge_orders, payment_records
6. V6__init_invites.sql        -- invite_codes, invite_records
```

---

**文档版本**：v1.1
**创建日期**：2026-03-16
**最后更新**：2026-03-16（修复字段缺失问题）