# 渠道统计接口优化计划

## 需求概述

优化 `/admin/channels/:channel_id/stats` 接口：

| 指标 | 当前实现 | 优化后 |
|------|---------|--------|
| 累计用户 | `COUNT(users WHERE channel_id = X)` | 保持不变 — **全量统计，不限时间** |
| 累计订单 | `COUNT(orders JOIN users ...)` | 保持不变 — **全量统计，不限时间** |
| 累计实付金额 | `SUM(orders.actual_amount)` | remark → activityID → `activities.price_draw × count` — **全量统计，不限时间** |
| 趋势图表 | 按**月**分组（`days` 参数实际当月用） | 修正为按**天**分组，`days` 参数控制天数范围 |

## 确认的决策

- ✅ 直接用 remark 中 activityID 查 `activities.price_draw`
- ✅ 软删除活动也计入（使用 `Unscoped`）
- ✅ `days` 参数修正为按天计算
- ✅ Overview 三个指标为全量（不受 days 限制）

## 受影响的代码

| 方法 | 文件 | 行号 | 改动内容 |
|------|------|------|---------|
| `GetStats` | `internal/service/channel/channel.go` | L238-355 | 核心改动：金额计算 + days 修正 + 按天分组 |
| `List` | `internal/service/channel/channel.go` | L157-236 | 同步改动：列表 paid_amount 用 price_draw 计算 |
| `StatsOutput` / `StatsDailyItem` | `internal/service/channel/channel.go` | L66-84 | 结构体不变，`Daily` 改为按天粒度 |

## 实施步骤

### Step 1: 新增 `orderRemarkRow` 类型和 `calcPaidByPriceDraw` 辅助函数

**文件**：`internal/service/channel/channel.go`

```go
type orderRemarkRow struct {
    Remark    string
    CreatedAt time.Time
}

// calcPaidByPriceDraw 解析订单 remark 中的 activityID + count，
// 批量查 activities.price_draw（含软删除），计算实付金额
// 返回：总金额(分)、按日期key分组的金额
func (s *service) calcPaidByPriceDraw(ctx context.Context, rows []orderRemarkRow, dateFmt string) (int64, map[string]int64, error) {
    if len(rows) == 0 {
        return 0, nil, nil
    }

    // 1. 解析 remark，收集 unique activityIDs
    type parsed struct {
        activityID int64
        count      int64
        dateKey    string
    }
    var items []parsed
    idSet := make(map[int64]struct{})

    for _, r := range rows {
        rmk := remark.Parse(r.Remark)
        if rmk.ActivityID > 0 {
            items = append(items, parsed{
                activityID: rmk.ActivityID,
                count:      rmk.Count,
                dateKey:    r.CreatedAt.Format(dateFmt),
            })
            idSet[rmk.ActivityID] = struct{}{}
        }
    }

    // 2. 批量查 activities.price_draw（含软删除 Unscoped）
    actIDs := make([]int64, 0, len(idSet))
    for id := range idSet {
        actIDs = append(actIDs, id)
    }
    priceMap := make(map[int64]int64)
    if len(actIDs) > 0 {
        var acts []model.Activities
        s.readDB.Activities.WithContext(ctx).UnderlyingDB().
            Unscoped().
            Table("activities").
            Select("id, price_draw").
            Where("id IN ?", actIDs).
            Find(&acts)
        for _, a := range acts {
            priceMap[a.ID] = a.PriceDraw
        }
    }

    // 3. 计算
    var total int64
    byDate := make(map[string]int64)
    for _, item := range items {
        if price, ok := priceMap[item.activityID]; ok {
            amt := price * item.count
            total += amt
            byDate[item.dateKey] += amt
        }
    }
    return total, byDate, nil
}
```

### Step 2: 重写 `GetStats` — 日期逻辑修正 + 金额计算

**改动要点**：

1. **参数 `days` 真正按天**：`startDate = now.AddDate(0, 0, -days+1)`
2. **Overview 全量不限时间**：用户数、订单数、实付金额均查全量
3. **趋势按天分组**：`DATE_FORMAT(..., '%Y-%m-%d')` 替代 `'%Y-%m'`
4. **金额用 price_draw**：调用 `calcPaidByPriceDraw`

```go
func (s *service) GetStats(ctx context.Context, channelID int64, days int, startDateStr, endDateStr string) (*StatsOutput, error) {
    now := time.Now()

    // 校验渠道存在
    _, err := s.readDB.Channels.WithContext(ctx).Where(s.readDB.Channels.ID.Eq(channelID)).First()
    if err != nil {
        if errors.Is(err, gorm.ErrRecordNotFound) {
            return nil, ErrChannelNotFound
        }
        return nil, err
    }

    out := &StatsOutput{}

    // ========== 1. Overview（全量，不限时间）==========

    // 1a. 累计用户
    userCount, _ := s.readDB.Users.WithContext(ctx).
        Where(s.readDB.Users.ChannelID.Eq(channelID)).Count()
    out.Overview.TotalUsers = userCount

    // 1b. 累计订单数
    orderFilter := "users.channel_id = ? AND users.deleted_at IS NULL AND orders.status = 2 AND orders.actual_amount > 0 AND orders.source_type IN (1,2,3,4) AND (orders.ext_order_id = '' OR orders.ext_order_id IS NULL)"

    type countResult struct{ Count int64 }
    var cr countResult
    s.readDB.Orders.WithContext(ctx).UnderlyingDB().Table("orders").
        Joins("JOIN users ON users.id = orders.user_id").
        Select("count(*) as count").
        Where(orderFilter, channelID).
        Scan(&cr)
    out.Overview.TotalOrders = cr.Count

    // 1c. 累计实付金额（全量订单 remark → price_draw × count）
    var allRemarks []orderRemarkRow
    s.readDB.Orders.WithContext(ctx).UnderlyingDB().Table("orders").
        Joins("JOIN users ON users.id = orders.user_id").
        Select("orders.remark, orders.created_at").
        Where(orderFilter, channelID).
        Scan(&allRemarks)

    totalPaid, _, _ := s.calcPaidByPriceDraw(ctx, allRemarks, "2006-01-02")
    out.Overview.TotalPaidCents = totalPaid
    out.Overview.TotalGMV = totalPaid / 100

    // ========== 2. 趋势图（按天分组，受 days 限制）==========

    // 2a. 计算日期范围
    var startDate, endDate time.Time
    if startDateStr != "" && endDateStr != "" {
        startDate, _ = time.Parse("2006-01-02", startDateStr)
        endDate, _ = time.Parse("2006-01-02", endDateStr)
        endDate = endDate.Add(24*time.Hour - time.Second)
    } else {
        if days <= 0 {
            days = 12
        }
        startDate = time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location()).
            AddDate(0, 0, -days+1)
        endDate = now
    }

    // 2b. 初始化日期桶（每天一个）
    dateMap := make(map[string]*StatsDailyItem)
    var dateList []string
    for d := startDate; !d.After(endDate); d = d.AddDate(0, 0, 1) {
        key := d.Format("2006-01-02")
        dateList = append(dateList, key)
        dateMap[key] = &StatsDailyItem{Date: key}
    }

    // 2c. 每日新增用户
    type dailyCount struct {
        Date  string
        Count int64
    }
    var dailyUsers []dailyCount
    s.readDB.Users.WithContext(ctx).UnderlyingDB().Table("users").
        Select("DATE_FORMAT(created_at, '%Y-%m-%d') as date, count(*) as count").
        Where("channel_id = ? AND deleted_at IS NULL AND created_at >= ? AND created_at <= ?",
            channelID, startDate, endDate).
        Group("date").Scan(&dailyUsers)
    for _, u := range dailyUsers {
        if item, ok := dateMap[u.Date]; ok {
            item.UserCount = u.Count
        }
    }

    // 2d. 每日订单数
    var dailyOrders []dailyCount
    s.readDB.Orders.WithContext(ctx).UnderlyingDB().Table("orders").
        Joins("JOIN users ON users.id = orders.user_id").
        Select("DATE_FORMAT(orders.created_at, '%Y-%m-%d') as date, count(*) as count").
        Where(orderFilter+" AND orders.created_at >= ? AND orders.created_at <= ?",
            channelID, startDate, endDate).
        Group("date").Scan(&dailyOrders)
    for _, o := range dailyOrders {
        if item, ok := dateMap[o.Date]; ok {
            item.OrderCount = o.Count
        }
    }

    // 2e. 每日实付金额（remark → price_draw）
    var rangeRemarks []orderRemarkRow
    s.readDB.Orders.WithContext(ctx).UnderlyingDB().Table("orders").
        Joins("JOIN users ON users.id = orders.user_id").
        Select("orders.remark, orders.created_at").
        Where(orderFilter+" AND orders.created_at >= ? AND orders.created_at <= ?",
            channelID, startDate, endDate).
        Scan(&rangeRemarks)

    _, dailyPaid, _ := s.calcPaidByPriceDraw(ctx, rangeRemarks, "2006-01-02")
    for dateKey, paid := range dailyPaid {
        if item, ok := dateMap[dateKey]; ok {
            item.PaidCents = paid
            item.GMV = paid / 100
        }
    }

    // 2f. 组装输出
    for _, d := range dateList {
        out.Daily = append(out.Daily, *dateMap[d])
    }

    return out, nil
}
```

### Step 3: 同步修改 `List` 方法的金额计算

**文件**：`internal/service/channel/channel.go`，L206-223

**当前**：`SUM(orders.actual_amount)` 聚合。

**修改为**：按渠道查询所有订单 remark，分渠道调用 `calcPaidByPriceDraw`。

```go
// 替换原有 paidResults 查询逻辑：
if len(channelIDs) > 0 {
    // ... userCount 查询保持不变 ...

    // 实付金额：查所有渠道的订单 remark
    type remarkWithChannel struct {
        ChannelID int64
        Remark    string
        CreatedAt time.Time
    }
    var chRemarks []remarkWithChannel
    s.readDB.Orders.WithContext(ctx).UnderlyingDB().Table("orders").
        Joins("JOIN users ON users.id = orders.user_id").
        Select("users.channel_id, orders.remark, orders.created_at").
        Where("users.channel_id IN ? AND users.deleted_at IS NULL AND orders.status = 2 AND orders.actual_amount > 0 AND orders.source_type IN (1,2,3,4) AND (orders.ext_order_id = '' OR orders.ext_order_id IS NULL)", channelIDs).
        Scan(&chRemarks)

    // 按渠道分组
    grouped := make(map[int64][]orderRemarkRow)
    for _, r := range chRemarks {
        grouped[r.ChannelID] = append(grouped[r.ChannelID], orderRemarkRow{
            Remark: r.Remark, CreatedAt: r.CreatedAt,
        })
    }

    for chID, rows := range grouped {
        total, _, _ := s.calcPaidByPriceDraw(ctx, rows, "2006-01-02")
        paidStats[chID] = total
    }
}
```

### Step 4: 添加 remark import

确保文件顶部 import 包含：
```go
"bindbox-game/internal/pkg/util/remark"
```

## 风险与缓解

| 风险 | 严重程度 | 缓解措施 |
|------|---------|----------|
| remark 格式不一致 | 中 | `remark.Parse()` 已处理 `activity:` 和 `lottery:activity:` 两种前缀 |
| 软删除活动 | 已解决 | 使用 `Unscoped()` 查询，确保被删活动仍有 price_draw |
| List 方法大量订单性能 | 中 | 单次查询所有渠道订单 remark，Go 中分组计算，比 N+1 高效 |
| days 参数前端兼容 | 低 | 前端传 `days=12` 原意应为12天，修正后行为与参数名一致 |

## 验收标准

- [ ] Overview 累计用户：全量统计 `users.channel_id = X` 的用户数（不限时间）
- [ ] Overview 累计订单：全量统计有效订单数（不限时间）
- [ ] Overview 累计实付金额：全量基于 `activities.price_draw × count` 计算（包含软删除活动）
- [ ] 趋势图按**天**分组，`days` 参数控制显示天数
- [ ] 渠道列表页 `paid_amount` 同步使用 price_draw 计算
- [ ] 编译通过 `make build-mac`
- [ ] 现有功能无回归

## SESSION_ID（供 /ccg:execute 使用）

- CODEX_SESSION: N/A
- GEMINI_SESSION: N/A