package main

import (
	"database/sql"
	"fmt"
	"os"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	dsn := "root:bindbox2025kdy@tcp(150.158.78.154:3306)/dev_game?charset=utf8mb4&parseTime=True&loc=Asia%2FShanghai"
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		fmt.Println("连接失败:", err)
		os.Exit(1)
	}
	defer db.Close()
	fmt.Println("✅ 数据库连接成功\n")

	// ============ 1. 全局汇总 ============
	fmt.Println("【1】全局汇总")
	var userCount, itemCount int64
	var totalYuan float64
	db.QueryRow(`
		SELECT COUNT(DISTINCT t.to_user_id), COUNT(DISTINCT t.inventory_id), IFNULL(SUM(i.value_cents)/100.0, 0)
		FROM user_inventory_transfers t
		JOIN user_inventory i ON i.id = t.inventory_id
		WHERE i.remark LIKE '%redeemed%'
	`).Scan(&userCount, &itemCount, &totalYuan)
	fmt.Printf("  涉及用户: %d | 涉及资产: %d | 总薅取金额: %.2f 元\n\n", userCount, itemCount, totalYuan)

	// ============ 2. 按用户汇总 ============
	fmt.Println("【2】按用户汇总薅取金额")
	fmt.Println(strings.Repeat("-", 95))
	fmt.Printf("  %-8s %-16s %-15s %-10s %-12s %-12s %s\n",
		"用户ID", "昵称", "手机号", "兑换资产数", "薅取金额(元)", "当前余额", "可扣回?")
	fmt.Println("  " + strings.Repeat("-", 90))

	rows2, _ := db.Query(`
		SELECT
			sub.user_id,
			IFNULL(u.nickname, '') AS nickname,
			IFNULL(u.mobile, '') AS mobile,
			sub.redeem_count,
			sub.total_yuan,
			IFNULL(pts.balance, 0) AS balance
		FROM (
			SELECT t.to_user_id AS user_id,
				COUNT(DISTINCT t.inventory_id) AS redeem_count,
				SUM(i.value_cents) / 100.0 AS total_yuan,
				SUM(i.value_cents) AS total_cents
			FROM user_inventory_transfers t
			JOIN user_inventory i ON i.id = t.inventory_id
			WHERE i.remark LIKE '%redeemed%'
			GROUP BY t.to_user_id
		) sub
		LEFT JOIN users u ON u.id = sub.user_id
		LEFT JOIN (SELECT user_id, SUM(points) AS balance FROM user_points GROUP BY user_id) pts ON pts.user_id = sub.user_id
		ORDER BY sub.total_yuan DESC
	`)
	if rows2 != nil {
		defer rows2.Close()
		for rows2.Next() {
			var uid, redeemCnt, balance int64
			var totalY float64
			var nick, mobile string
			rows2.Scan(&uid, &nick, &mobile, &redeemCnt, &totalY, &balance)
			canDeduct := "✅ 可全额"
			exploitCents := int64(totalY * 100)
			if balance < exploitCents {
				canDeduct = fmt.Sprintf("⚠️ 仅可扣%d", balance)
			}
			fmt.Printf("  %-8d %-16s %-15s %-10d %-12.2f %-12d %s\n",
				uid, nick, mobile, redeemCnt, totalY, balance, canDeduct)
		}
	}

	// ============ 3. 并发漏洞证据 ============
	fmt.Println("\n【3】并发漏洞证据 — 同一资产被多次转赠")
	fmt.Println(strings.Repeat("-", 100))
	rows3, _ := db.Query(`
		SELECT t.inventory_id, COUNT(*) AS cnt,
			GROUP_CONCAT(CONCAT(t.from_user_id,'→',t.to_user_id) ORDER BY t.created_at SEPARATOR ' | ') AS path,
			i.value_cents, IFNULL(p.name,'') AS pname
		FROM user_inventory_transfers t
		JOIN user_inventory i ON i.id = t.inventory_id
		LEFT JOIN products p ON p.id = i.product_id
		GROUP BY t.inventory_id, i.value_cents, p.name
		HAVING COUNT(*) > 1
		ORDER BY cnt DESC, i.value_cents DESC
	`)
	if rows3 != nil {
		defer rows3.Close()
		fmt.Printf("  %-10s %-6s %-10s %-28s %s\n", "资产ID", "次数", "价值(元)", "商品", "转赠路径")
		fmt.Println("  " + strings.Repeat("-", 95))
		for rows3.Next() {
			var invID, cnt, vc int64
			var path, pname string
			rows3.Scan(&invID, &cnt, &path, &vc, &pname)
			if len([]rune(pname)) > 14 {
				pname = string([]rune(pname)[:14]) + ".."
			}
			fmt.Printf("  %-10d %-6d %-10.2f %-28s %s\n", invID, cnt, float64(vc)/100.0, pname, path)
		}
	}

	// ============ 4. 转赠关系网络 Top15 ============
	fmt.Println("\n【4】转赠关系网络 Top15")
	fmt.Println(strings.Repeat("-", 110))
	rows4, _ := db.Query(`
		SELECT t.from_user_id, IFNULL(fu.nickname,'') AS fn,
			t.to_user_id, IFNULL(tu.nickname,'') AS tn,
			COUNT(*) AS xfer_cnt, COUNT(DISTINCT t.inventory_id) AS item_cnt,
			SUM(i.value_cents)/100.0 AS total_yuan,
			MIN(t.created_at) AS first_t, MAX(t.created_at) AS last_t
		FROM user_inventory_transfers t
		JOIN user_inventory i ON i.id = t.inventory_id
		LEFT JOIN users fu ON fu.id = t.from_user_id
		LEFT JOIN users tu ON tu.id = t.to_user_id
		GROUP BY t.from_user_id, fu.nickname, t.to_user_id, tu.nickname
		ORDER BY total_yuan DESC LIMIT 15
	`)
	if rows4 != nil {
		defer rows4.Close()
		fmt.Printf("  %-20s → %-20s %-6s %-6s %-12s %-12s %-12s\n",
			"赠送方", "接收方", "转赠次", "资产数", "金额(元)", "首次", "末次")
		fmt.Println("  " + strings.Repeat("-", 105))
		for rows4.Next() {
			var fuid, tuid, xcnt, icnt int64
			var yuan float64
			var fn, tn string
			var ft, lt time.Time
			rows4.Scan(&fuid, &fn, &tuid, &tn, &xcnt, &icnt, &yuan, &ft, &lt)
			from := fmt.Sprintf("%d(%s)", fuid, truncStr(fn, 6))
			to := fmt.Sprintf("%d(%s)", tuid, truncStr(tn, 6))
			fmt.Printf("  %-20s → %-20s %-6d %-6d %-12.2f %-12s %-12s\n",
				from, to, xcnt, icnt, yuan,
				ft.Format("01-02 15:04"), lt.Format("01-02 15:04"))
		}
	}

	// ============ 5. 典型利用链路样本（前10条） ============
	fmt.Println("\n【5】典型利用链路样本（转赠→取消发货→兑换积分）")
	fmt.Println(strings.Repeat("-", 130))
	rows5, _ := db.Query(`
		SELECT i.id, i.user_id, IFNULL(u.nickname,'') AS nick,
			i.value_cents, i.status, i.remark
		FROM user_inventory i
		LEFT JOIN users u ON u.id = i.user_id
		WHERE i.remark LIKE '%transferred_from_%'
		  AND i.remark LIKE '%shipping_cancelled%'
		  AND i.remark LIKE '%redeemed%'
		ORDER BY i.value_cents DESC
		LIMIT 10
	`)
	if rows5 != nil {
		defer rows5.Close()
		fmt.Printf("  %-8s %-8s %-14s %-10s %-6s %s\n", "资产ID", "用户ID", "昵称", "价值(元)", "状态", "操作链路")
		fmt.Println("  " + strings.Repeat("-", 125))
		for rows5.Next() {
			var id, uid, vc int64
			var status int32
			var nick, remark string
			rows5.Scan(&id, &uid, &nick, &vc, &status, &remark)
			fmt.Printf("  %-8d %-8d %-14s %-10.2f %-6s %s\n",
				id, uid, truncStr(nick, 12), float64(vc)/100.0,
				statusText(status), parseActions(remark))
		}
	}

	fmt.Println("\n✅ 核对完毕")
}

func truncStr(s string, maxRunes int) string {
	runes := []rune(s)
	if len(runes) > maxRunes {
		return string(runes[:maxRunes]) + ".."
	}
	return s
}

func parseActions(remark string) string {
	parts := strings.Split(remark, "|")
	actions := make([]string, 0, len(parts))
	for _, p := range parts {
		p = strings.TrimSpace(p)
		if p == "" {
			continue
		}
		if strings.HasPrefix(p, "transferred_from_") {
			actions = append(actions, "转赠")
		} else if p == "shipping_requested" {
			actions = append(actions, "发货")
		} else if strings.HasPrefix(p, "shipping_cancelled") {
			actions = append(actions, "取消发货")
		} else if strings.Contains(p, "redeemed") {
			actions = append(actions, "✖兑换积分")
		} else {
			actions = append(actions, p)
		}
	}
	return strings.Join(actions, " → ")
}

func statusText(s int32) string {
	switch s {
	case 1:
		return "持有"
	case 2:
		return "作废"
	case 3:
		return "已用"
	default:
		return fmt.Sprintf("%d", s)
	}
}