bindbox-game/cmd/channel_stats_compare/verify.go

package main

import (
	"fmt"
	"strings"

	"gorm.io/driver/mysql"
	"gorm.io/gorm"

	"bindbox-game/internal/pkg/util/remark"
)

func main() {
	dsn := "root:bindbox2025kdy@tcp(150.158.78.154:3306)/dev_game?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		fmt.Println("连接失败:", err)
		return
	}

	channelID := 3
	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)"

	// 1. 查所有订单 remark + source_type
	type RemarkRow struct {
		ID         int64
		Remark     string
		SourceType int32
	}
	var rows []RemarkRow
	db.Table("orders").
		Joins("JOIN users ON users.id = orders.user_id").
		Select("orders.id, orders.remark, orders.source_type").
		Where(orderFilter, channelID).
		Scan(&rows)

	fmt.Printf("渠道 %d 总有效订单: %d\n\n", channelID, len(rows))

	// 2. 三路分类统计
	var case1, case2, case3, unmatched int
	actIDSet := make(map[int64]struct{})
	issueIDSet := make(map[int64]struct{})
	pkgIDSet := make(map[int64]struct{})

	type parsed struct {
		orderID    int64
		caseType   int
		activityID int64
		issueID    int64
		pkgID      int64
		count      int64
	}
	var items []parsed

	for _, r := range rows {
		rmk := remark.Parse(r.Remark)
		p := parsed{orderID: r.ID, count: rmk.Count}

		if rmk.ActivityID > 0 {
			p.caseType = 1
			p.activityID = rmk.ActivityID
			actIDSet[rmk.ActivityID] = struct{}{}
			case1++
		} else if rmk.IssueID > 0 {
			p.caseType = 2
			p.issueID = rmk.IssueID
			issueIDSet[rmk.IssueID] = struct{}{}
			case2++
		} else if rmk.PkgID > 0 {
			p.caseType = 3
			p.pkgID = rmk.PkgID
			pkgIDSet[rmk.PkgID] = struct{}{}
			case3++
		} else {
			unmatched++
		}
		items = append(items, p)
	}

	fmt.Println("=== 三路分类统计 ===")
	fmt.Printf("  Case1 (抽奖/直购, ActivityID>0): %d 笔\n", case1)
	fmt.Printf("  Case2 (对对碰, IssueID>0):       %d 笔\n", case2)
	fmt.Printf("  Case3 (一番赏, PkgID>0):         %d 笔\n", case3)
	fmt.Printf("  未匹配:                          %d 笔\n", unmatched)
	fmt.Println()

	// 3. 查 activity_issues (Case2)
	issueActivityMap := make(map[int64]int64)
	if len(issueIDSet) > 0 {
		issueIDs := make([]int64, 0, len(issueIDSet))
		for id := range issueIDSet {
			issueIDs = append(issueIDs, id)
		}
		type IssueRow struct {
			ID         int64
			ActivityID int64
		}
		var issueRows []IssueRow
		db.Table("activity_issues").
			Select("id, activity_id").
			Where("id IN ?", issueIDs).
			Scan(&issueRows)
		for _, ir := range issueRows {
			issueActivityMap[ir.ID] = ir.ActivityID
			actIDSet[ir.ActivityID] = struct{}{}
		}
		fmt.Printf("activity_issues 查到: %d / %d\n", len(issueRows), len(issueIDs))
	}

	// 4. 查 activities.price_draw (Case1+2)
	priceMap := make(map[int64]int64)
	if len(actIDSet) > 0 {
		actIDs := make([]int64, 0, len(actIDSet))
		for id := range actIDSet {
			actIDs = append(actIDs, id)
		}
		type ActRow struct {
			ID        int64
			PriceDraw int64
		}
		var actRows []ActRow
		db.Unscoped().Table("activities").
			Select("id, price_draw").
			Where("id IN ?", actIDs).
			Scan(&actRows)
		for _, a := range actRows {
			priceMap[a.ID] = a.PriceDraw
		}
		fmt.Printf("activities 查到: %d / %d\n", len(actRows), len(actIDs))
	}

	// 5. 查 game_pass_packages.price (Case3)
	pkgPriceMap := make(map[int64]int64)
	if len(pkgIDSet) > 0 {
		pkgIDs := make([]int64, 0, len(pkgIDSet))
		for id := range pkgIDSet {
			pkgIDs = append(pkgIDs, id)
		}
		type PkgRow struct {
			ID    int64
			Price int64
		}
		var pkgRows []PkgRow
		db.Unscoped().Table("game_pass_packages").
			Select("id, price").
			Where("id IN ?", pkgIDs).
			Scan(&pkgRows)
		for _, p := range pkgRows {
			pkgPriceMap[p.ID] = p.Price
		}
		fmt.Printf("game_pass_packages 查到: %d / %d\n", len(pkgRows), len(pkgIDs))
	}
	fmt.Println()

	// 6. 计算金额
	var totalCase1, totalCase2, totalCase3 int64
	var matchedCase1, matchedCase2, matchedCase3 int
	var unmatchedCase1, unmatchedCase2, unmatchedCase3 int

	for _, item := range items {
		switch item.caseType {
		case 1:
			if price, ok := priceMap[item.activityID]; ok {
				totalCase1 += price * item.count
				matchedCase1++
			} else {
				unmatchedCase1++
			}
		case 2:
			if actID, ok := issueActivityMap[item.issueID]; ok {
				if price, ok := priceMap[actID]; ok {
					totalCase2 += price * item.count
					matchedCase2++
				} else {
					unmatchedCase2++
				}
			} else {
				unmatchedCase2++
			}
		case 3:
			if price, ok := pkgPriceMap[item.pkgID]; ok {
				totalCase3 += price * item.count
				matchedCase3++
			} else {
				unmatchedCase3++
			}
		}
	}

	total := totalCase1 + totalCase2 + totalCase3

	fmt.Println("=== 金额统计 (price_draw/price × count) ===")
	fmt.Printf("  Case1 抽奖/直购:  %d 分 = %.2f 元 (匹配 %d, 未匹配 %d)\n",
		totalCase1, float64(totalCase1)/100, matchedCase1, unmatchedCase1)
	fmt.Printf("  Case2 对对碰:     %d 分 = %.2f 元 (匹配 %d, 未匹配 %d)\n",
		totalCase2, float64(totalCase2)/100, matchedCase2, unmatchedCase2)
	fmt.Printf("  Case3 一番赏:     %d 分 = %.2f 元 (匹配 %d, 未匹配 %d)\n",
		totalCase3, float64(totalCase3)/100, matchedCase3, unmatchedCase3)
	fmt.Println(strings.Repeat("-", 60))
	fmt.Printf("  合计:             %d 分 = %.2f 元\n", total, float64(total)/100)
	fmt.Printf("  覆盖订单:         %d / %d (%.1f%%)\n",
		matchedCase1+matchedCase2+matchedCase3, len(rows),
		float64(matchedCase1+matchedCase2+matchedCase3)/float64(len(rows))*100)
	fmt.Println()

	// 7. 对比 actual_amount
	type AmountResult struct {
		TotalCents int64
	}
	var ar AmountResult
	db.Table("orders").
		Joins("JOIN users ON users.id = orders.user_id").
		Select("COALESCE(SUM(orders.actual_amount), 0) as total_cents").
		Where(orderFilter, channelID).
		Scan(&ar)

	fmt.Println("=== 对比 ===")
	fmt.Printf("  SUM(actual_amount):           %d 分 = %.2f 元\n", ar.TotalCents, float64(ar.TotalCents)/100)
	fmt.Printf("  price_draw/price × count:     %d 分 = %.2f 元\n", total, float64(total)/100)
	diff := total - ar.TotalCents
	fmt.Printf("  差额:                         %d 分 = %.2f 元\n", diff, float64(diff)/100)
	if diff > 0 {
		fmt.Printf("  说明: 用户享受了 %.2f 元优惠\n", float64(diff)/100)
	}

	// 8. 打印未匹配订单示例
	if unmatched > 0 {
		fmt.Printf("\n=== 未匹配 remark 示例 (共 %d 笔) ===\n", unmatched)
		shown := 0
		for _, item := range items {
			if item.caseType == 0 && shown < 5 {
				for _, r := range rows {
					if r.ID == item.orderID {
						rmk := r.Remark
						if len(rmk) > 80 {
							rmk = rmk[:80] + "..."
						}
						fmt.Printf("  ID=%-6d type=%d remark=[%s]\n", r.ID, r.SourceType, rmk)
						shown++
						break
					}
				}
			}
		}
	}
}