package admin

import (
	"database/sql"
	"net/http"
	"strconv"
	"time"

	"bindbox-game/internal/code"
	"bindbox-game/internal/pkg/core"
	"bindbox-game/internal/pkg/validation"

	"gorm.io/gorm"
)

type cardsRequest struct {
	RangeType string `form:"rangeType"`
	StartDate string `form:"start"`
	EndDate   string `form:"end"`
}

type cardStatResponse struct {
	ItemCardSales   int64  `json:"itemCardSales"`
	DrawCount       int64  `json:"drawCount"`
	NewUsers        int64  `json:"newUsers"`
	TotalPoints     int64  `json:"totalPoints"`
	TotalCoupons    int64  `json:"totalCoupons"`
	TotalItemCards  int64  `json:"totalItemCards"`
	TotalGamePasses int64  `json:"totalGamePasses"`
	ItemCardChange  string `json:"itemCardChange"`
	DrawChange      string `json:"drawChange"`
	NewUserChange   string `json:"newUserChange"`
	PointsChange    string `json:"pointsChange"`
}

func (h *handler) DashboardCards() core.HandlerFunc {
	return func(ctx core.Context) {
		req := new(cardsRequest)
		rsp := new(cardStatResponse)
		if err := ctx.ShouldBindForm(req); err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, code.ParamBindError, validation.Error(err)))
			return
		}
		start, end := parseRange(req.RangeType, req.StartDate, req.EndDate)
		prevStart, prevEnd := previousWindow(start, end)

		icCur, err := h.readDB.UserItemCards.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.UserItemCards.CreatedAt.Gte(start)).
			Where(h.readDB.UserItemCards.CreatedAt.Lte(end)).
			Count()
		if err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, 21001, err.Error()))
			return
		}
		icPrev, err := h.readDB.UserItemCards.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.UserItemCards.CreatedAt.Gte(prevStart)).
			Where(h.readDB.UserItemCards.CreatedAt.Lte(prevEnd)).
			Count()
		if err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, 21002, err.Error()))
			return
		}

		dlCur, err := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Gte(start)).
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Lte(end)).
			Count()
		if err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, 21003, err.Error()))
			return
		}
		dlPrev, err := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Gte(prevStart)).
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Lte(prevEnd)).
			Count()
		if err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, 21004, err.Error()))
			return
		}

		nuCur, err := h.readDB.Users.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.Users.CreatedAt.Gte(start)).
			Where(h.readDB.Users.CreatedAt.Lte(end)).
			Count()
		if err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, 21005, err.Error()))
			return
		}
		nuPrev, err := h.readDB.Users.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.Users.CreatedAt.Gte(prevStart)).
			Where(h.readDB.Users.CreatedAt.Lte(prevEnd)).
			Count()
		if err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, 21006, err.Error()))
			return
		}

		var tpRows []struct{ Sum int64 }
		if err := h.readDB.UserPoints.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.UserPoints.ValidEnd.Gt(time.Now())).
			Or(h.readDB.UserPoints.ValidEnd.Eq(time.Time{})).
			Select(h.readDB.UserPoints.Points.Sum().As("sum")).
			Scan(&tpRows); err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, 21007, err.Error()))
			return
		}
		var tpCur int64
		if len(tpRows) > 0 {
			tpCur = tpRows[0].Sum
		}
		// 使用积分流水计算净变动用于环比
		// 当前窗口净变动
		var curDeltaRows []struct{ Sum int64 }
		if err := h.readDB.UserPointsLedger.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.UserPointsLedger.CreatedAt.Gte(start)).
			Where(h.readDB.UserPointsLedger.CreatedAt.Lte(end)).
			Select(h.readDB.UserPointsLedger.Points.Sum().As("sum")).
			Scan(&curDeltaRows); err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, 21008, err.Error()))
			return
		}
		var curDelta int64
		if len(curDeltaRows) > 0 {
			curDelta = curDeltaRows[0].Sum
		}
		// 前一窗口净变动
		var prevDeltaRows []struct{ Sum int64 }
		if err := h.readDB.UserPointsLedger.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.UserPointsLedger.CreatedAt.Gte(prevStart)).
			Where(h.readDB.UserPointsLedger.CreatedAt.Lte(prevEnd)).
			Select(h.readDB.UserPointsLedger.Points.Sum().As("sum")).
			Scan(&prevDeltaRows); err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, 21009, err.Error()))
			return
		}
		var prevDelta int64
		if len(prevDeltaRows) > 0 {
			prevDelta = prevDeltaRows[0].Sum
		}

		// 批量：存量优惠券 (未使用)
		tcCur, _ := h.readDB.UserCoupons.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.UserCoupons.Status.Eq(1)).
			Count()

		// 批量：存量道具卡 (有效)
		ticCur, _ := h.readDB.UserItemCards.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.UserItemCards.Status.Eq(1)).
			Count()

		// 批量：存量次卡 (剩余次数)
		var tgpRows []struct{ Sum int64 }
		_ = h.readDB.UserGamePasses.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.UserGamePasses.ExpiredAt.Gt(time.Now())).
			Or(h.readDB.UserGamePasses.ExpiredAt.Eq(time.Time{})).
			Select(h.readDB.UserGamePasses.Remaining.Sum().As("sum")).
			Scan(&tgpRows)
		var tgpCur int64
		if len(tgpRows) > 0 {
			tgpCur = tgpRows[0].Sum
		}

		rsp.ItemCardSales = icCur
		rsp.DrawCount = dlCur
		rsp.NewUsers = nuCur
		rsp.TotalPoints = tpCur
		rsp.TotalCoupons = tcCur
		rsp.TotalItemCards = ticCur
		rsp.TotalGamePasses = tgpCur
		rsp.ItemCardChange = percentChange(icPrev, icCur)
		rsp.DrawChange = percentChange(dlPrev, dlCur)
		rsp.NewUserChange = percentChange(nuPrev, nuCur)
		rsp.PointsChange = percentChange(prevDelta, curDelta)
		ctx.Payload(rsp)
	}
}

type trendRequest struct {
	RangeType   string `form:"rangeType"`
	Granularity string `form:"granularity"`
}

type trendPoint struct {
	Date   string `json:"date"`
	Value  int64  `json:"value"`
	Gmv    int64  `json:"gmv"`
	Orders int64  `json:"orders"`
}

type salesDrawTrendResponse struct {
	Granularity string       `json:"granularity"`
	List        []trendPoint `json:"list"`
}

type userTrendResponse struct {
	Granularity string       `json:"granularity"`
	List        []trendPoint `json:"list"`
}

func (h *handler) DashboardUserTrend() core.HandlerFunc {
	return func(ctx core.Context) {
		req := new(trendRequest)
		rsp := new(userTrendResponse)
		if err := ctx.ShouldBindForm(req); err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, code.ParamBindError, validation.Error(err)))
			return
		}
		start, end := parseRange(req.RangeType, "", "")
		gran := normalizeGranularity(req.Granularity)
		buckets := buildBuckets(start, end, gran)
		list := make([]trendPoint, 0, len(buckets))
		for _, b := range buckets {
			c, err := h.readDB.Users.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.Users.CreatedAt.Gte(b.Start)).
				Where(h.readDB.Users.CreatedAt.Lte(b.End)).
				Count()
			if err != nil {
				ctx.AbortWithError(core.Error(http.StatusBadRequest, 21009, err.Error()))
				return
			}
			list = append(list, trendPoint{Date: b.Label, Value: c})
		}
		rsp.Granularity = gran
		rsp.List = list
		ctx.Payload(rsp)
	}
}

type drawTrendResponse struct {
	Granularity string       `json:"granularity"`
	List        []trendPoint `json:"list"`
}

func (h *handler) DashboardDrawTrend() core.HandlerFunc {
	return func(ctx core.Context) {
		req := new(trendRequest)
		rsp := new(drawTrendResponse)
		if err := ctx.ShouldBindForm(req); err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, code.ParamBindError, validation.Error(err)))
			return
		}
		start, end := parseRange(req.RangeType, "", "")
		gran := normalizeGranularity(req.Granularity)
		buckets := buildBuckets(start, end, gran)
		list := make([]trendPoint, 0, len(buckets))
		for _, b := range buckets {
			c, err := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.ActivityDrawLogs.CreatedAt.Gte(b.Start)).
				Where(h.readDB.ActivityDrawLogs.CreatedAt.Lte(b.End)).
				Count()
			if err != nil {
				ctx.AbortWithError(core.Error(http.StatusBadRequest, 21010, err.Error()))
				return
			}
			list = append(list, trendPoint{Date: b.Label, Value: c})
		}
		rsp.Granularity = gran
		rsp.List = list
		ctx.Payload(rsp)
	}
}

type newUsersRequest struct {
	Page     int    `form:"page"`
	PageSize int    `form:"page_size"`
	Period   string `form:"period"`
}

type newUserItem struct {
	ID             int64            `json:"id"`
	Nickname       string           `json:"nickname"`
	Avatar         string           `json:"avatar"`
	CreatedAt      string           `json:"createdAt"`
	PointsBalance  int64            `json:"pointsBalance"`
	InventoryCount int64            `json:"inventoryCount"`
	ItemCardCount  int64            `json:"itemCardCount"`
	CouponCount    int64            `json:"couponCount"`
	TitleCount     int64            `json:"titleCount"`
	LastOnlineAt   string           `json:"lastOnlineAt"`
	Titles         []userTitleBrief `json:"titles"`
}

type userTitleBrief struct {
	ID   int64  `json:"id"`
	Name string `json:"name"`
}

type newUsersResponse struct {
	Page     int           `json:"page"`
	PageSize int           `json:"pageSize"`
	Total    int64         `json:"total"`
	List     []newUserItem `json:"list"`
}

func (h *handler) DashboardNewUsers() core.HandlerFunc {
	return func(ctx core.Context) {
		req := new(newUsersRequest)
		rsp := new(newUsersResponse)
		if err := ctx.ShouldBindForm(req); err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, code.ParamBindError, validation.Error(err)))
			return
		}
		if req.Page <= 0 {
			req.Page = 1
		}
		if req.PageSize <= 0 {
			req.PageSize = 20
		}
		if req.PageSize > 100 {
			req.PageSize = 100
		}
		base := h.readDB.Users.WithContext(ctx.RequestContext()).ReadDB()
		if req.Period != "" {
			var s, e time.Time
			now := time.Now()
			switch req.Period {
			case "month":
				s = time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, now.Location())
				e = s.AddDate(0, 1, 0).Add(-time.Second)
			case "last_month":
				lm := now.AddDate(0, -1, 0)
				s = time.Date(lm.Year(), lm.Month(), 1, 0, 0, 0, 0, lm.Location())
				e = s.AddDate(0, 1, 0).Add(-time.Second)
			case "year":
				s = time.Date(now.Year(), 1, 1, 0, 0, 0, 0, now.Location())
				e = time.Date(now.Year(), 12, 31, 23, 59, 59, 0, now.Location())
			}
			if !s.IsZero() && !e.IsZero() {
				base = base.Where(h.readDB.Users.CreatedAt.Gte(s)).Where(h.readDB.Users.CreatedAt.Lte(e))
			}
		}
		total, err := base.Count()
		if err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, 21011, err.Error()))
			return
		}
		rows, err := base.Order(h.readDB.Users.ID.Desc()).Offset((req.Page - 1) * req.PageSize).Limit(req.PageSize).Find()
		if err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, 21012, err.Error()))
			return
		}
		// 收集用户ID做批量聚合
		ids := make([]int64, len(rows))
		for i, u := range rows {
			ids[i] = u.ID
		}

		// 批量：资产数
		type kvCount struct {
			UserID int64
			Cnt    int64
		}
		invCounts := map[int64]int64{}
		var invRows []kvCount
		_ = h.readDB.UserInventory.WithContext(ctx.RequestContext()).ReadDB().
			Select(h.readDB.UserInventory.UserID, h.readDB.UserInventory.UserID.Count().As("cnt")).
			Where(h.readDB.UserInventory.UserID.In(ids...)).
			Group(h.readDB.UserInventory.UserID).
			Scan(&invRows)
		for _, r := range invRows {
			invCounts[r.UserID] = r.Cnt
		}

		// 批量：道具卡（有效）
		icCounts := map[int64]int64{}
		var icRows []kvCount
		_ = h.readDB.UserItemCards.WithContext(ctx.RequestContext()).ReadDB().
			Select(h.readDB.UserItemCards.UserID, h.readDB.UserItemCards.UserID.Count().As("cnt")).
			Where(h.readDB.UserItemCards.UserID.In(ids...)).
			Where(h.readDB.UserItemCards.Status.Eq(1)).
			Group(h.readDB.UserItemCards.UserID).
			Scan(&icRows)
		for _, r := range icRows {
			icCounts[r.UserID] = r.Cnt
		}

		// 批量：优惠券
		cpCounts := map[int64]int64{}
		var cpRows []kvCount
		_ = h.readDB.UserCoupons.WithContext(ctx.RequestContext()).ReadDB().
			Select(h.readDB.UserCoupons.UserID, h.readDB.UserCoupons.UserID.Count().As("cnt")).
			Where(h.readDB.UserCoupons.UserID.In(ids...)).
			Group(h.readDB.UserCoupons.UserID).
			Scan(&cpRows)
		for _, r := range cpRows {
			cpCounts[r.UserID] = r.Cnt
		}

		// 批量：称号列表
		type titleRow struct {
			UserID int64
			ID     int64
			Name   string
		}
		titleMap := map[int64][]userTitleBrief{}
		var trows []titleRow
		_ = h.readDB.UserTitles.WithContext(ctx.RequestContext()).ReadDB().
			LeftJoin(h.readDB.SystemTitles, h.readDB.SystemTitles.ID.EqCol(h.readDB.UserTitles.TitleID)).
			Select(h.readDB.UserTitles.UserID, h.readDB.UserTitles.TitleID.As("id"), h.readDB.SystemTitles.Name).
			Where(h.readDB.UserTitles.UserID.In(ids...)).
			Scan(&trows)
		for _, tr := range trows {
			titleMap[tr.UserID] = append(titleMap[tr.UserID], userTitleBrief{ID: tr.ID, Name: tr.Name})
		}

		// 批量：有效积分余额
		pointsMap := map[int64]int64{}
		now := time.Now()
		pRows, _ := h.readDB.UserPoints.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.UserPoints.UserID.In(ids...)).
			Find()
		for _, r := range pRows {
			if !r.ValidEnd.IsZero() && r.ValidEnd.Before(now) {
				continue
			}
			pointsMap[r.UserID] += r.Points
		}

		// 批量：最后在线（各表 MAX）
		type tsRow struct {
			UserID int64
			Ts     time.Time
		}
		maxMap := map[int64]time.Time{}
		mergeMax := func(rows []tsRow) {
			for _, r := range rows {
				if r.Ts.After(maxMap[r.UserID]) {
					maxMap[r.UserID] = r.Ts
				}
			}
		}
		var dlMax, odMax, paidMax, plMax, icMax, ivMax []tsRow
		_ = h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			Select(h.readDB.ActivityDrawLogs.UserID, h.readDB.ActivityDrawLogs.CreatedAt.Max().As("ts")).
			Where(h.readDB.ActivityDrawLogs.UserID.In(ids...)).
			Group(h.readDB.ActivityDrawLogs.UserID).
			Scan(&dlMax)
		mergeMax(dlMax)
		_ = h.readDB.Orders.WithContext(ctx.RequestContext()).ReadDB().
			Select(h.readDB.Orders.UserID, h.readDB.Orders.UpdatedAt.Max().As("ts")).
			Where(h.readDB.Orders.UserID.In(ids...)).
			Group(h.readDB.Orders.UserID).
			Scan(&odMax)
		mergeMax(odMax)
		_ = h.readDB.Orders.WithContext(ctx.RequestContext()).ReadDB().
			Select(h.readDB.Orders.UserID, h.readDB.Orders.PaidAt.Max().As("ts")).
			Where(h.readDB.Orders.UserID.In(ids...)).
			Group(h.readDB.Orders.UserID).
			Scan(&paidMax)
		mergeMax(paidMax)
		_ = h.readDB.UserPointsLedger.WithContext(ctx.RequestContext()).ReadDB().
			Select(h.readDB.UserPointsLedger.UserID, h.readDB.UserPointsLedger.CreatedAt.Max().As("ts")).
			Where(h.readDB.UserPointsLedger.UserID.In(ids...)).
			Group(h.readDB.UserPointsLedger.UserID).
			Scan(&plMax)
		mergeMax(plMax)
		_ = h.readDB.UserItemCards.WithContext(ctx.RequestContext()).ReadDB().
			Select(h.readDB.UserItemCards.UserID, h.readDB.UserItemCards.UpdatedAt.Max().As("ts")).
			Where(h.readDB.UserItemCards.UserID.In(ids...)).
			Group(h.readDB.UserItemCards.UserID).
			Scan(&icMax)
		mergeMax(icMax)
		_ = h.readDB.UserInventory.WithContext(ctx.RequestContext()).ReadDB().
			Select(h.readDB.UserInventory.UserID, h.readDB.UserInventory.UpdatedAt.Max().As("ts")).
			Where(h.readDB.UserInventory.UserID.In(ids...)).
			Group(h.readDB.UserInventory.UserID).
			Scan(&ivMax)
		mergeMax(ivMax)

		list := make([]newUserItem, len(rows))
		for i, u := range rows {
			list[i] = newUserItem{
				ID:             u.ID,
				Nickname:       u.Nickname,
				Avatar:         u.Avatar,
				CreatedAt:      u.CreatedAt.Format("2006-01-02T15:04:05Z07:00"),
				PointsBalance:  pointsMap[u.ID],
				InventoryCount: invCounts[u.ID],
				ItemCardCount:  icCounts[u.ID],
				CouponCount:    cpCounts[u.ID],
				TitleCount:     int64(len(titleMap[u.ID])),
				LastOnlineAt: func() string {
					t := maxMap[u.ID]
					if t.IsZero() {
						return ""
					}
					return t.Format("2006-01-02T15:04:05Z07:00")
				}(),
				Titles: titleMap[u.ID],
			}
		}
		rsp.Page = req.Page
		rsp.PageSize = req.PageSize
		rsp.Total = total
		rsp.List = list
		ctx.Payload(rsp)
	}
}

type drawStreamRequest struct {
	SinceID *int64 `form:"since_id"`
	Limit   int    `form:"limit"`
}

type drawStreamItem struct {
	ID           int64  `json:"id"`
	UserID       int64  `json:"userId"`
	Nickname     string `json:"nickname"`
	IssueID      int64  `json:"issueId"`
	IssueName    string `json:"issueName"`
	ActivityName string `json:"activityName"`
	IssueNumber  string `json:"issueNumber"`
	PrizeName    string `json:"prizeName"`
	IsWinner     int32  `json:"isWinner"`
	CreatedAt    string `json:"createdAt"`
}

type drawStreamResponse struct {
	List    []drawStreamItem `json:"list"`
	SinceID *int64           `json:"sinceId,omitempty"`
}

func (h *handler) DashboardDrawStream() core.HandlerFunc {
	return func(ctx core.Context) {
		req := new(drawStreamRequest)
		rsp := new(drawStreamResponse)
		if err := ctx.ShouldBindForm(req); err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, code.ParamBindError, validation.Error(err)))
			return
		}
		if req.Limit <= 0 {
			req.Limit = 50
		}
		if req.Limit > 100 {
			req.Limit = 100
		}
		q := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB()
		if req.SinceID != nil {
			q = q.Where(h.readDB.ActivityDrawLogs.ID.Gt(*req.SinceID))
		}
		type row struct {
			ID           int64
			UserID       int64
			Nickname     string
			IssueID      int64
			IssueName    string
			ActivityName string
			IssueNumber  string
			PrizeName    string
			ProductID    int64
			IsWinner     int32
			CreatedAt    time.Time
		}
		var rows []row
		err := q.
			LeftJoin(h.readDB.Users, h.readDB.Users.ID.EqCol(h.readDB.ActivityDrawLogs.UserID)).
			LeftJoin(h.readDB.ActivityIssues, h.readDB.ActivityIssues.ID.EqCol(h.readDB.ActivityDrawLogs.IssueID)).
			LeftJoin(h.readDB.Activities, h.readDB.Activities.ID.EqCol(h.readDB.ActivityIssues.ActivityID)).
			LeftJoin(h.readDB.ActivityRewardSettings, h.readDB.ActivityRewardSettings.ID.EqCol(h.readDB.ActivityDrawLogs.RewardID)).
			Select(
				h.readDB.ActivityDrawLogs.ID,
				h.readDB.ActivityDrawLogs.UserID,
				h.readDB.Users.Nickname,
				h.readDB.ActivityDrawLogs.IssueID,
				h.readDB.Activities.Name.As("activity_name"),
				h.readDB.ActivityIssues.IssueNumber.As("issue_number"),
				h.readDB.ActivityRewardSettings.ProductID.As("product_id"),
				h.readDB.ActivityDrawLogs.IsWinner,
				h.readDB.ActivityDrawLogs.CreatedAt,
			).
			Order(h.readDB.ActivityDrawLogs.ID.Desc()).
			Limit(req.Limit).
			Scan(&rows)
		if err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, 21016, err.Error()))
			return
		}
		// 批量获取商品名称，通过 ProductID 关联查询
		productIDs := make([]int64, 0, len(rows))
		for _, v := range rows {
			if v.ProductID > 0 {
				productIDs = append(productIDs, v.ProductID)
			}
		}
		productNameMap := make(map[int64]string)
		if len(productIDs) > 0 {
			products, _ := h.readDB.Products.WithContext(ctx.RequestContext()).ReadDB().Where(h.readDB.Products.ID.In(productIDs...)).Find()
			for _, p := range products {
				productNameMap[p.ID] = p.Name
			}
		}
		list := make([]drawStreamItem, len(rows))
		var maxID int64
		for i, v := range rows {
			iname := v.ActivityName
			if iname == "" {
				iname = v.IssueName
			}
			// 只使用商品名称
			prizeName := ""
			if v.ProductID > 0 {
				if pn, ok := productNameMap[v.ProductID]; ok {
					prizeName = pn
				}
			}
			list[i] = drawStreamItem{
				ID:       v.ID,
				UserID:   v.UserID,
				Nickname: v.Nickname,
				IssueID:  v.IssueID,
				IssueName: func() string {
					if v.ActivityName != "" && v.IssueNumber != "" {
						return v.ActivityName + "-" + v.IssueNumber
					}
					return iname
				}(),
				ActivityName: v.ActivityName,
				IssueNumber:  v.IssueNumber,
				PrizeName:    prizeName,
				IsWinner:     v.IsWinner,
				CreatedAt:    v.CreatedAt.Format("2006-01-02T15:04:05Z07:00"),
			}
			if v.ID > maxID {
				maxID = v.ID
			}
		}
		if len(rows) > 0 {
			rsp.SinceID = &maxID
		}
		rsp.List = list
		ctx.Payload(rsp)
	}
}

type todosRequest struct {
	Limit int `form:"limit"`
}

type todoItem struct {
	UserID    int64  `json:"userId"`
	Nickname  string `json:"nickname"`
	Avatar    string `json:"avatar"`
	TaskType  string `json:"taskType"`
	TaskLabel string `json:"taskLabel"`
}

type todosResponse struct {
	List []todoItem `json:"list"`
}

func (h *handler) DashboardTodos() core.HandlerFunc {
	return func(ctx core.Context) {
		req := new(todosRequest)
		rsp := new(todosResponse)
		if err := ctx.ShouldBindForm(req); err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, code.ParamBindError, validation.Error(err)))
			return
		}
		if req.Limit <= 0 {
			req.Limit = 50
		}
		if req.Limit > 100 {
			req.Limit = 100
		}
		base := h.readDB.Users.WithContext(ctx.RequestContext()).ReadDB().Order(h.readDB.Users.ID.Desc())
		rows, err := base.Limit(req.Limit).Find()
		if err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, 21017, err.Error()))
			return
		}
		out := make([]todoItem, 0, len(rows))
		for _, u := range rows {
			dlCount, _ := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().Where(h.readDB.ActivityDrawLogs.UserID.Eq(u.ID)).Count()
			if dlCount == 0 {
				out = append(out, todoItem{UserID: u.ID, Nickname: u.Nickname, Avatar: u.Avatar, TaskType: "undrawn", TaskLabel: "从未参与抽奖"})
			}
		}
		rsp.List = out
		ctx.Payload(rsp)
	}
}

func parseRange(rangeType, startS, endS string) (time.Time, time.Time) {
	now := time.Now()
	switch rangeType {
	case "today":
		s := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location())
		e := s.Add(24 * time.Hour).Add(-time.Second)
		return s, e
	case "24h":
		e := now
		s := e.Add(-24 * time.Hour)
		return s, e
	case "30d":
		e := now
		s := e.Add(-30 * 24 * time.Hour)
		return s, e
	case "custom":
		if startS != "" && endS != "" {
			if st, err := time.Parse("2006-01-02", startS); err == nil {
				if et, err := time.Parse("2006-01-02", endS); err == nil {
					et = et.Add(24 * time.Hour).Add(-time.Second)
					if et.Sub(st) > 31*24*time.Hour {
						et = st.Add(30 * 24 * time.Hour).Add(-time.Second)
					}
					return st, et
				}
			}
		}
		fallthrough
	default:
		e := now
		s := e.Add(-7 * 24 * time.Hour)
		return s, e
	}
}

func previousWindow(start, end time.Time) (time.Time, time.Time) {
	dur := end.Sub(start) + time.Second
	ps := start.Add(-dur)
	pe := start.Add(-time.Second)
	return ps, pe
}

func percentChange(prev, cur int64) string {
	if prev <= 0 {
		return "+0%"
	}
	diff := float64(cur-prev) / float64(prev) * 100
	if diff >= 0 {
		return "+" + strconv.Itoa(int(diff)) + "%"
	}
	return "-" + strconv.Itoa(int(-diff)) + "%"
}

func daysBetween(start, end time.Time) []time.Time {
	s := time.Date(start.Year(), start.Month(), start.Day(), 0, 0, 0, 0, start.Location())
	e := time.Date(end.Year(), end.Month(), end.Day(), 0, 0, 0, 0, end.Location())
	var out []time.Time
	for d := s; !d.After(e); d = d.Add(24 * time.Hour) {
		out = append(out, d)
	}
	return out
}

type bucket struct {
	Label string
	Start time.Time
	End   time.Time
}

func normalizeGranularity(in string) string {
	switch in {
	case "week":
		return "week"
	case "month":
		return "month"
	case "hour":
		return "hour"
	case "minute":
		return "minute"
	default:
		return "day"
	}
}

func buildBuckets(start, end time.Time, gran string) []bucket {
	if gran == "day" {
		days := daysBetween(start, end)
		out := make([]bucket, 0, len(days))
		for _, d := range days {
			ds := time.Date(d.Year(), d.Month(), d.Day(), 0, 0, 0, 0, d.Location())
			de := ds.Add(24 * time.Hour).Add(-time.Second)
			out = append(out, bucket{Label: ds.Format("2006-01-02"), Start: ds, End: de})
		}
		return out
	}
	if gran == "week" {
		s := start
		for s.Weekday() != time.Monday {
			s = s.Add(-24 * time.Hour)
		}
		out := []bucket{}
		for cur := s; cur.Before(end) || cur.Equal(end); cur = cur.Add(7 * 24 * time.Hour) {
			ds := time.Date(cur.Year(), cur.Month(), cur.Day(), 0, 0, 0, 0, cur.Location())
			de := ds.Add(7 * 24 * time.Hour).Add(-time.Second)
			label := ds.Format("2006-01-02")
			if de.After(end) {
				de = end
			}
			out = append(out, bucket{Label: label, Start: ds, End: de})
			if de.Equal(end) {
				break
			}
		}
		return out
	}
	if gran == "hour" {
		out := []bucket{}
		// 步长 1 小时
		for cur := start; !cur.After(end); cur = cur.Add(time.Hour) {
			s := time.Date(cur.Year(), cur.Month(), cur.Day(), cur.Hour(), 0, 0, 0, cur.Location())
			de := s.Add(time.Hour).Add(-time.Second)
			out = append(out, bucket{Label: s.Format("01-02 15h"), Start: s, End: de})
		}
		return out
	}
	if gran == "minute" {
		out := []bucket{}
		// 步长 1 分钟
		for cur := start; !cur.After(end); cur = cur.Add(time.Minute) {
			s := time.Date(cur.Year(), cur.Month(), cur.Day(), cur.Hour(), cur.Minute(), 0, 0, cur.Location())
			de := s.Add(time.Minute).Add(-time.Second)
			out = append(out, bucket{Label: s.Format("01-02 15:04"), Start: s, End: de})
		}
		return out
	}
	s := time.Date(start.Year(), start.Month(), 1, 0, 0, 0, 0, start.Location())
	out := []bucket{}
	for cur := s; cur.Before(end) || cur.Equal(end); cur = cur.AddDate(0, 1, 0) {
		ds := time.Date(cur.Year(), cur.Month(), 1, 0, 0, 0, 0, cur.Location())
		de := ds.AddDate(0, 1, 0).Add(-time.Second)
		label := ds.Format("2006-01")
		if de.After(end) {
			de = end
		}
		out = append(out, bucket{Label: label, Start: ds, End: de})
		if de.Equal(end) {
			break
		}
	}
	return out
}

type funnelItem struct {
	Stage     string  `json:"stage"`
	Count     int64   `json:"count"`
	Rate      float64 `json:"rate"`
	LostCount int64   `json:"lostCount"`
}

func (h *handler) DashboardOrderFunnel() core.HandlerFunc {
	return func(ctx core.Context) {
		s, e := parseRange(ctx.Request().URL.Query().Get("rangeType"), ctx.Request().URL.Query().Get("start"), ctx.Request().URL.Query().Get("end"))
		visitors, _ := h.readDB.LogRequest.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.LogRequest.CreatedAt.Gte(s)).
			Where(h.readDB.LogRequest.CreatedAt.Lte(e)).
			Where(h.readDB.LogRequest.Path.Like("/api/app/%")).
			Count()
		orders, _ := h.readDB.Orders.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.Orders.CreatedAt.Gte(s)).
			Where(h.readDB.Orders.CreatedAt.Lte(e)).
			Count()
		payments, _ := h.readDB.Orders.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.Orders.Status.Eq(2)).
			Where(h.readDB.Orders.PaidAt.Gte(s)).
			Where(h.readDB.Orders.PaidAt.Lte(e)).
			Count()
		shipped, _ := h.readDB.ShippingRecords.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.ShippingRecords.Status.In(2, 3)).
			Where(h.readDB.ShippingRecords.UpdatedAt.Gte(s)).
			Where(h.readDB.ShippingRecords.UpdatedAt.Lte(e)).
			Count()
		consumed, _ := h.readDB.Orders.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.Orders.Status.Eq(2), h.readDB.Orders.IsConsumed.Eq(1)).
			Where(h.readDB.Orders.UpdatedAt.Gte(s)).
			Where(h.readDB.Orders.UpdatedAt.Lte(e)).
			Count()
		completions := shipped + consumed
		stages := []struct {
			name string
			val  int64
		}{
			{"访问用户", visitors}, {"下单用户", orders}, {"支付用户", payments}, {"完成订单", completions},
		}
		out := make([]funnelItem, 0, len(stages))
		var prev int64
		for i, st := range stages {
			var rate float64
			var lost int64
			if i == 0 {
				rate = 100
				lost = 0
			} else {
				if prev > 0 {
					rate = float64(st.val) / float64(prev) * 100
				}
				lost = prev - st.val
				if lost < 0 {
					lost = 0
				}
			}
			out = append(out, funnelItem{Stage: st.name, Count: st.val, Rate: float64(int(rate*10)) / 10.0, LostCount: lost})
			prev = st.val
		}
		ctx.Payload(out)
	}
}

type activitiesItem struct {
	ID                int64  `json:"id"`
	Name              string `json:"name"`
	Type              string `json:"type"`
	StartTime         string `json:"startTime"`
	EndTime           string `json:"endTime"`
	Status            string `json:"status"`
	TotalDraws        int64  `json:"totalDraws"`
	TotalParticipants int64  `json:"totalParticipants"`
}

func (h *handler) DashboardActivities() core.HandlerFunc {
	return func(ctx core.Context) {
		rows, _ := h.readDB.Activities.WithContext(ctx.RequestContext()).ReadDB().Find()
		out := make([]activitiesItem, len(rows))
		for i, a := range rows {
			issues, _ := h.readDB.ActivityIssues.WithContext(ctx.RequestContext()).ReadDB().Where(h.readDB.ActivityIssues.ActivityID.Eq(a.ID)).Find()
			var drawTotal int64
			var participants int64
			for _, iss := range issues {
				dt, _ := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().Where(h.readDB.ActivityDrawLogs.IssueID.Eq(iss.ID)).Count()
				drawTotal += dt
				pc, _ := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().Where(h.readDB.ActivityDrawLogs.IssueID.Eq(iss.ID)).Distinct(h.readDB.ActivityDrawLogs.UserID).Count()
				participants += pc
			}
			status := "active"
			if a.EndTime.Before(time.Now()) {
				status = "ended"
			}
			out[i] = activitiesItem{ID: a.ID, Name: a.Name, Type: "转盘抽奖", StartTime: a.StartTime.Format("2006-01-02 15:04:05"), EndTime: a.EndTime.Format("2006-01-02 15:04:05"), Status: status, TotalDraws: drawTotal, TotalParticipants: participants}
		}
		ctx.Payload(out)
	}
}

type activityPrize struct {
	PrizeID           int64   `json:"prizeId"`
	PrizeName         string  `json:"prizeName"`
	PrizeLevel        int32   `json:"prizeLevel"`
	PrizeType         string  `json:"prizeType"`
	PrizeValue        int64   `json:"prizeValue"`
	TotalQuantity     int64   `json:"totalQuantity"`
	IssuedQuantity    int64   `json:"issuedQuantity"`
	DrawCount         int64   `json:"drawCount"`         // 该奖品所属期数的总抽奖次数
	WinCount          int64   `json:"winCount"`          // 该奖品的中奖次数
	WinRate           float64 `json:"winRate"`           // 中奖率 (WinCount / DrawCount)
	Probability       float64 `json:"probability"`       // 配置概率 (Weight / IssueTotalWeight)
	ActualProbability float64 `json:"actualProbability"` // 实际概率 (同 WinRate)
	Cost              int64   `json:"cost"`
}

type activityInfo struct {
	ID                int64  `json:"id"`
	Name              string `json:"name"`
	Type              string `json:"type"`
	StartTime         string `json:"startTime"`
	EndTime           string `json:"endTime"`
	Status            string `json:"status"`
	TotalDraws        int64  `json:"totalDraws"`
	TotalParticipants int64  `json:"totalParticipants"`
	PriceDraw         int64  `json:"priceDraw"`
}

type activityPrizeAnalysis struct {
	Activity activityInfo    `json:"activity"`
	Prizes   []activityPrize `json:"prizes"`
	Summary  struct {
		TotalCost  int64   `json:"totalCost"`
		AvgWinRate float64 `json:"avgWinRate"`
		MaxWinRate float64 `json:"maxWinRate"`
		MinWinRate float64 `json:"minWinRate"`
	} `json:"summary"`
}

func (h *handler) DashboardActivityPrizeAnalysis() core.HandlerFunc {
	return func(ctx core.Context) {
		aidS := ctx.Request().URL.Query().Get("activity_id")
		if aidS == "" {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, code.ParamBindError, "缺少活动ID"))
			return
		}
		aid, err := strconv.ParseInt(aidS, 10, 64)
		if err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, code.ParamBindError, "活动ID格式错误"))
			return
		}

		// 1. 获取活动信息
		act, err := h.readDB.Activities.WithContext(ctx.RequestContext()).ReadDB().Where(h.readDB.Activities.ID.Eq(aid)).First()
		if err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, 21020, "活动不存在"))
			return
		}

		// 2. 获取该活动下的所有期数 (Issues)
		issues, _ := h.readDB.ActivityIssues.WithContext(ctx.RequestContext()).ReadDB().Where(h.readDB.ActivityIssues.ActivityID.Eq(aid)).Find()
		if len(issues) == 0 {
			ctx.Payload(activityPrizeAnalysis{Activity: activityInfo{ID: act.ID, Name: act.Name}})
			return
		}
		issueIDs := make([]int64, len(issues))
		for i, v := range issues {
			issueIDs[i] = v.ID
		}

		// 3. 批量获取所有奖品配置 (Rewards)
		rsAll, _ := h.readDB.ActivityRewardSettings.WithContext(ctx.RequestContext()).ReadDB().Where(h.readDB.ActivityRewardSettings.IssueID.In(issueIDs...)).Find()

		// 收集 Product IDs 以便批量查询价格
		productIDs := make([]int64, 0)
		// 计算每期的总权重 (IssueID -> TotalWeight)
		issueTotalWeights := make(map[int64]int64)
		for _, r := range rsAll {
			if r.ProductID != 0 {
				productIDs = append(productIDs, r.ProductID)
			}
			issueTotalWeights[r.IssueID] += int64(r.Weight)
		}

		// 4. 批量查询商品价格和名称 (Product Price and Name)
		priceMap := make(map[int64]int64)
		productNameMap := make(map[int64]string)
		if len(productIDs) > 0 {
			products, _ := h.readDB.Products.WithContext(ctx.RequestContext()).ReadDB().Where(h.readDB.Products.ID.In(productIDs...)).Find()
			for _, p := range products {
				priceMap[p.ID] = p.Price
				productNameMap[p.ID] = p.Name
			}
		}

		// 5. 批量聚合统计数据 (Draw Logs)
		// 5.1 每期的总抽奖数 (IssueID -> Count)
		type countResult struct {
			Key   int64
			Count int64
		}
		drawCounts := make(map[int64]int64)
		var dcRows []countResult
		_ = h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			Select(h.readDB.ActivityDrawLogs.IssueID.As("key"), h.readDB.ActivityDrawLogs.ID.Count().As("count")).
			Where(h.readDB.ActivityDrawLogs.IssueID.In(issueIDs...)).
			Group(h.readDB.ActivityDrawLogs.IssueID).
			Scan(&dcRows)
		for _, r := range dcRows {
			drawCounts[r.Key] = r.Count
		}

		// 5.2 每个奖品的中奖数 (RewardID -> Count)
		winCounts := make(map[int64]int64)
		var wcRows []countResult
		_ = h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			Select(h.readDB.ActivityDrawLogs.RewardID.As("key"), h.readDB.ActivityDrawLogs.ID.Count().As("count")).
			Where(h.readDB.ActivityDrawLogs.IssueID.In(issueIDs...)).
			Where(h.readDB.ActivityDrawLogs.IsWinner.Eq(1)).
			Group(h.readDB.ActivityDrawLogs.RewardID).
			Scan(&wcRows)
		for _, r := range wcRows {
			winCounts[r.Key] = r.Count
		}

		// 5.3 活动总参与人数 (Distinct UserID)
		participants, _ := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.ActivityDrawLogs.IssueID.In(issueIDs...)).
			Distinct(h.readDB.ActivityDrawLogs.UserID).
			Count()

		// 5.4 活动总抽奖次数 (Sum of Issue Draw Counts)
		var totalActivityDraws int64
		for _, c := range drawCounts {
			totalActivityDraws += c
		}

		// 6. 组装数据
		prizes := make([]activityPrize, len(rsAll))
		var totalCost int64
		var winRates []float64

		for i, r := range rsAll {
			dc := drawCounts[r.IssueID] // 该期的总抽奖数
			wc := winCounts[r.ID]       // 该奖品的中奖数
			price := priceMap[r.ProductID]

			issued := r.OriginalQty - r.Quantity

			// 计算配置概率：该奖品权重 / 该期总权重
			var prob float64
			if w := issueTotalWeights[r.IssueID]; w > 0 {
				prob = float64(r.Weight) / float64(w) * 100
			}

			// 计算实际中奖率：中奖数 / 该期总抽奖数
			var actual float64
			if dc > 0 {
				actual = float64(wc) / float64(dc) * 100
			}

			prizes[i] = activityPrize{
				PrizeID:    r.ID,
				PrizeName:  productNameMap[r.ProductID],
				PrizeLevel: r.Level,
				PrizeType: func() string {
					if r.ProductID != 0 {
						return "实物奖品"
					}
					return "虚拟/道具"
				}(),
				PrizeValue:        price,
				TotalQuantity:     r.OriginalQty,
				IssuedQuantity:    issued,
				DrawCount:         dc,
				WinCount:          wc,
				WinRate:           actual,
				Probability:       prob,
				ActualProbability: actual,
				Cost:              price,
			}

			totalCost += price * wc
			winRates = append(winRates, actual)
		}

		// 7. 计算汇总指标
		status := "active"
		if act.EndTime.Before(time.Now()) {
			status = "ended"
		}

		var avgW, maxW, minW float64
		if len(winRates) > 0 {
			var sum float64
			maxW = winRates[0]
			minW = winRates[0]
			for _, w := range winRates {
				sum += w
				if w > maxW {
					maxW = w
				}
				if w < minW {
					minW = w
				}
			}
			avgW = sum / float64(len(winRates))
		}

		out := activityPrizeAnalysis{}
		out.Activity = activityInfo{
			ID:                act.ID,
			Name:              act.Name,
			Type:              "转盘抽奖",
			StartTime:         act.StartTime.Format("2006-01-02 15:04:05"),
			EndTime:           act.EndTime.Format("2006-01-02 15:04:05"),
			Status:            status,
			TotalDraws:        totalActivityDraws,
			TotalParticipants: participants,
			PriceDraw:         act.PriceDraw,
		}
		out.Prizes = prizes
		out.Summary.TotalCost = totalCost
		out.Summary.AvgWinRate = avgW
		out.Summary.MaxWinRate = maxW
		out.Summary.MinWinRate = minW

		ctx.Payload(out)
	}
}

type userOverviewResponse struct {
	Chart []struct {
		Date  string `json:"date"`
		Value int64  `json:"value"`
	} `json:"chart"`
	Metrics struct {
		TotalUsers   int64  `json:"totalUsers"`
		TotalVisits  int64  `json:"totalVisits"`
		DailyVisits  int64  `json:"dailyVisits"`
		WeeklyGrowth string `json:"weeklyGrowth"`
	} `json:"metrics"`
}

func (h *handler) DashboardUserOverview() core.HandlerFunc {
	return func(ctx core.Context) {
		totalUsers, _ := h.readDB.Users.WithContext(ctx.RequestContext()).ReadDB().Count()
		todayS, todayE := parseRange("today", "", "")
		dailyVisits, _ := h.readDB.LogRequest.WithContext(ctx.RequestContext()).ReadDB().Where(h.readDB.LogRequest.CreatedAt.Gte(todayS)).Where(h.readDB.LogRequest.CreatedAt.Lte(todayE)).Where(h.readDB.LogRequest.Path.Like("/api/app/%")).Count()
		s7, e7 := parseRange("7d", "", "")
		lastS := s7.Add(-7 * 24 * time.Hour)
		lastE := s7.Add(-time.Second)
		curVisits, _ := h.readDB.LogRequest.WithContext(ctx.RequestContext()).ReadDB().Where(h.readDB.LogRequest.CreatedAt.Gte(s7)).Where(h.readDB.LogRequest.CreatedAt.Lte(e7)).Where(h.readDB.LogRequest.Path.Like("/api/app/%")).Count()
		prevVisits, _ := h.readDB.LogRequest.WithContext(ctx.RequestContext()).ReadDB().Where(h.readDB.LogRequest.CreatedAt.Gte(lastS)).Where(h.readDB.LogRequest.CreatedAt.Lte(lastE)).Where(h.readDB.LogRequest.Path.Like("/api/app/%")).Count()
		var wg string
		if prevVisits > 0 {
			diff := float64(curVisits-prevVisits) / float64(prevVisits) * 100
			if diff >= 0 {
				wg = "+" + strconv.Itoa(int(diff)) + "%"
			} else {
				wg = "-" + strconv.Itoa(int(-diff)) + "%"
			}
		} else {
			wg = "+0%"
		}
		totalVisits, _ := h.readDB.LogRequest.WithContext(ctx.RequestContext()).ReadDB().Where(h.readDB.LogRequest.Path.Like("/api/app/%")).Count()
		type chartPoint struct {
			Date  string `json:"date"`
			Value int64  `json:"value"`
		}
		chart := []chartPoint{}
		now := time.Now()
		for i := 8; i >= 0; i-- {
			dt := time.Date(now.Year(), now.Month()-time.Month(i), 1, 0, 0, 0, 0, now.Location())
			dn := dt.AddDate(0, 1, 0).Add(-time.Second)
			c, _ := h.readDB.Users.WithContext(ctx.RequestContext()).ReadDB().Where(h.readDB.Users.CreatedAt.Gte(dt)).Where(h.readDB.Users.CreatedAt.Lte(dn)).Count()
			chart = append(chart, chartPoint{Date: dt.Format("2006-01"), Value: c})
		}
		out := userOverviewResponse{}
		out.Chart = make([]struct {
			Date  string `json:"date"`
			Value int64  `json:"value"`
		}, len(chart))
		for i := range chart {
			out.Chart[i] = struct {
				Date  string `json:"date"`
				Value int64  `json:"value"`
			}{Date: chart[i].Date, Value: chart[i].Value}
		}
		out.Metrics.TotalUsers = totalUsers
		out.Metrics.TotalVisits = totalVisits
		out.Metrics.DailyVisits = dailyVisits
		out.Metrics.WeeklyGrowth = wg
		ctx.Payload(out)
	}
}

type retentionCohort struct {
	Date      string    `json:"date"`
	Total     int64     `json:"total"`
	Retention []float64 `json:"retention"`
}

type retentionAnalyticsResponse struct {
	List []retentionCohort `json:"list"`
}

func (h *handler) DashboardRetentionAnalytics() core.HandlerFunc {
	return func(ctx core.Context) {
		// 默认取 30 天
		end := time.Now()
		start := end.AddDate(0, 0, -30)
		days := daysBetween(start, end)

		out := make([]retentionCohort, 0, len(days))

		for _, d := range days {
			ds := time.Date(d.Year(), d.Month(), d.Day(), 0, 0, 0, 0, d.Location())
			de := ds.AddDate(0, 0, 1).Add(-time.Second)

			// 分群活跃用户：该日期注册的用户
			var userIDs []int64
			_ = h.readDB.Users.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.Users.CreatedAt.Gte(ds)).
				Where(h.readDB.Users.CreatedAt.Lte(de)).
				Pluck(h.readDB.Users.ID, &userIDs)

			total := int64(len(userIDs))
			if total == 0 {
				continue
			}

			retention := make([]float64, 8)
			retention[0] = 100

			for i := 1; i <= 7; i++ {
				rs := ds.AddDate(0, 0, i)
				re := rs.AddDate(0, 0, 1).Add(-time.Second)

				// 统计这批用户在 Day i 是否有下单行为作为活跃指标
				activeCount, _ := h.readDB.Orders.WithContext(ctx.RequestContext()).ReadDB().
					Where(h.readDB.Orders.UserID.In(userIDs...)).
					Where(h.readDB.Orders.CreatedAt.Gte(rs)).
					Where(h.readDB.Orders.CreatedAt.Lte(re)).
					Distinct(h.readDB.Orders.UserID).
					Count()

				rate := float64(activeCount) / float64(total) * 100
				retention[i] = float64(int(rate*10)) / 10.0
			}

			out = append(out, retentionCohort{
				Date:      ds.Format("2006-01-02"),
				Total:     total,
				Retention: retention,
			})
		}

		ctx.Payload(retentionAnalyticsResponse{List: out})
	}
}

type userEconomicsResponse struct {
	Arpu      int64   `json:"arpu"`
	ArpuTrend float64 `json:"arpuTrend"`
	Cac       int64   `json:"cac"`
	CacTrend  float64 `json:"cacTrend"`
	Clv       int64   `json:"clv"`
	KFactor   float64 `json:"kFactor"`
}

func (h *handler) DashboardUserEconomics() core.HandlerFunc {
	return func(ctx core.Context) {
		now := time.Now()
		start30 := now.AddDate(0, 0, -30)
		start60 := now.AddDate(0, 0, -60)

		// 1. ARPU: (总抽奖价值) / 活跃人数
		type gmvRow struct {
			TotalVal int64
		}
		var curGmv gmvRow
		h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).UnderlyingDB().
			Joins("JOIN activity_issues ON activity_issues.id = activity_draw_logs.issue_id").
			Joins("JOIN activities ON activities.id = activity_issues.activity_id").
			Where("activity_draw_logs.created_at >= ?", start30).
			Select("SUM(activities.price_draw) as total_val").
			Scan(&curGmv)

		var prevGmv gmvRow
		h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).UnderlyingDB().
			Joins("JOIN activity_issues ON activity_issues.id = activity_draw_logs.issue_id").
			Joins("JOIN activities ON activities.id = activity_issues.activity_id").
			Where("activity_draw_logs.created_at >= ? AND activity_draw_logs.created_at < ?", start60, start30).
			Select("SUM(activities.price_draw) as total_val").
			Scan(&prevGmv)

		activeUsers, _ := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Gte(start30)).
			Distinct(h.readDB.ActivityDrawLogs.UserID).
			Count()
		prevUsers, _ := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Gte(start60)).
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Lt(start30)).
			Distinct(h.readDB.ActivityDrawLogs.UserID).
			Count()

		var arpu int64
		var arpuTrend float64
		if activeUsers > 0 {
			arpu = curGmv.TotalVal / activeUsers
			if prevUsers > 0 && prevGmv.TotalVal > 0 {
				prevArpu := prevGmv.TotalVal / prevUsers
				if prevArpu > 0 {
					arpuTrend = float64(arpu-prevArpu) / float64(prevArpu) * 100
				}
			}
		}

		// 2. K-Factor
		invites, _ := h.readDB.UserInvites.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.UserInvites.CreatedAt.Gte(start30)).
			Count()

		var kFactor float64
		if activeUsers > 0 {
			kFactor = float64(invites) / float64(activeUsers)
		}

		// 3. CAC
		var marketingSpendNull sql.NullInt64
		_ = h.readDB.UserCoupons.WithContext(ctx.RequestContext()).UnderlyingDB().
			Joins("JOIN system_coupons ON system_coupons.id = user_coupons.coupon_id").
			Where("user_coupons.created_at >= ?", start30).
			Select("SUM(system_coupons.discount_value)").
			Scan(&marketingSpendNull)

		newUsers, _ := h.readDB.Users.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.Users.CreatedAt.Gte(start30)).
			Count()

		var cac int64
		if newUsers > 0 {
			cac = marketingSpendNull.Int64 / newUsers
		}

		clv := arpu * 5

		ctx.Payload(userEconomicsResponse{
			Arpu:      arpu / 100,
			ArpuTrend: float64(int(arpuTrend*10)) / 10.0,
			Cac:       cac / 100,
			CacTrend:  0.0,
			Clv:       clv / 100,
			KFactor:   float64(int(kFactor*100)) / 100.0,
		})
	}
}

type prizeDistributionItem struct {
	Level       int32   `json:"level"`
	LevelName   string  `json:"levelName"`
	WinnerCount int64   `json:"winnerCount"`
	PrizeCount  int64   `json:"prizeCount"`
	WinRate     float64 `json:"winRate"`
	Cost        int64   `json:"cost"`
}

func (h *handler) DashboardPrizeDistribution() core.HandlerFunc {
	return func(ctx core.Context) {
		// 聚合所有在线期数的奖品级别概率与产出
		var rows []struct {
			Level           int32
			LevelTotalQty   int64   // 该档位总库存 (SUM original_qty)
			LevelRemQty     int64   // 该档位剩余 (SUM quantity)
			LevelTotalProb  float64 // 该档位总权重 (SUM weight)
			PrizeCount      int64   // 该档位配置项数 (COUNT id)
			LevelTotalValue int64   // 该档位总货值 (SUM price * original_qty)
		}

		activityIdStr := ctx.Request().URL.Query().Get("activity_id")

		// 1. 构建基础查询条件（复用）
		buildBaseDB := func() *gorm.DB {
			base := h.readDB.ActivityRewardSettings.WithContext(ctx.RequestContext()).UnderlyingDB().
				Joins("JOIN activity_issues ON activity_issues.id = activity_reward_settings.issue_id").
				Joins("JOIN activities ON activities.id = activity_issues.activity_id")

			if activityIdStr != "" {
				base = base.Where("activities.id = ?", activityIdStr)
			} else {
				base = base.Where("activities.status = ?", 1)
			}
			return base
		}

		// 2. 计算活动总权重 (用于计算真实概率)
		var totalActivityWeight float64
		// 使用新的DB Session进行查询，避免GORM Scope污染
		weightDB := buildBaseDB()
		var weightResult sql.NullFloat64
		weightDB.Select("SUM(activity_reward_settings.weight)").Scan(&weightResult)
		totalActivityWeight = weightResult.Float64

		// 3. 分组统计各项指标
		// 注意: gorm GEN 模式下的 UnderlyingDB() 返回的是 *gorm.DB，可以链式调用
		mainDB := buildBaseDB().
			Joins("LEFT JOIN products ON products.id = activity_reward_settings.product_id").
			Select(
				"activity_reward_settings.level",
				"SUM(activity_reward_settings.original_qty) as level_total_qty",
				"SUM(activity_reward_settings.quantity) as level_rem_qty",
				"SUM(activity_reward_settings.weight) as level_total_prob",
				"COUNT(activity_reward_settings.id) as prize_count",
				"SUM(products.price * activity_reward_settings.original_qty) as level_total_value",
			).
			Group("activity_reward_settings.level").
			Order("activity_reward_settings.level").
			Scan(&rows)

		if mainDB.Error != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, 21030, mainDB.Error.Error()))
			return
		}

		out := make([]prizeDistributionItem, len(rows))
		levelNames := map[int32]string{1: "隐藏款", 2: "A赏", 3: "B赏", 4: "C赏", 5: "D赏", 6: "E赏", 7: "F赏", 8: "Last赏"}

		for i, r := range rows {
			// 防御性计算：已发出数量 (不能为负)
			winCount := r.LevelTotalQty - r.LevelRemQty
			if winCount < 0 {
				winCount = 0
			}

			name := levelNames[r.Level]
			if name == "" {
				name = strconv.Itoa(int(r.Level)) + "等奖"
			}

			// 真实概率计算：该档位总权重 / 活动总权重
			var winRate float64
			if totalActivityWeight > 0 {
				winRate = r.LevelTotalProb / totalActivityWeight
			}

			// 成本计算：(该档位总货值 / 该档位总数量) * 已发出数量
			// 即：加权平均单价 * 发出数量
			var cost int64
			if r.LevelTotalQty > 0 {
				avgPrice := float64(r.LevelTotalValue) / float64(r.LevelTotalQty)
				cost = int64(avgPrice * float64(winCount))
			}

			out[i] = prizeDistributionItem{
				Level:       r.Level,
				LevelName:   name,
				WinnerCount: winCount,
				PrizeCount:  r.LevelTotalQty, // 前端显示的总库存
				WinRate:     winRate,
				Cost:        cost / 100, // 分转元
			}
		}

		ctx.Payload(out)
	}
}

func (h *handler) DashboardSalesDrawTrend() core.HandlerFunc {
	return func(ctx core.Context) {
		req := new(trendRequest)
		if err := ctx.ShouldBindForm(req); err != nil {
			ctx.AbortWithError(core.Error(http.StatusBadRequest, code.ParamBindError, validation.Error(err)))
			return
		}

		s, e := parseRange(req.RangeType, "", "")
		gran := normalizeGranularity(req.Granularity)
		buckets := buildBuckets(s, e, gran)

		list := make([]trendPoint, len(buckets))
		for i, b := range buckets {
			// 抽奖数 (Value)
			draws, _ := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.ActivityDrawLogs.CreatedAt.Gte(b.Start)).
				Where(h.readDB.ActivityDrawLogs.CreatedAt.Lte(b.End)).
				Count()

			// 总业务价值 (GMV) - 基于抽奖单价计算
			var gmv struct {
				Total int64
			}
			_ = h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).UnderlyingDB().
				Joins("JOIN activity_issues ON activity_issues.id = activity_draw_logs.issue_id").
				Joins("JOIN activities ON activities.id = activity_issues.activity_id").
				Where("activity_draw_logs.created_at >= ?", b.Start).
				Where("activity_draw_logs.created_at <= ?", b.End).
				Select("SUM(activities.price_draw) as total").
				Scan(&gmv)

			// 订单数 (仅支付成功的订单)
			orders, _ := h.readDB.Orders.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.Orders.Status.Eq(2)).
				Where(h.readDB.Orders.PaidAt.Gte(b.Start)).
				Where(h.readDB.Orders.PaidAt.Lte(b.End)).
				Count()

			list[i] = trendPoint{
				Date:   b.Label,
				Value:  draws,
				Gmv:    gmv.Total / 100, // 转为元
				Orders: orders,
			}
		}

		ctx.Payload(salesDrawTrendResponse{
			Granularity: gran,
			List:        list,
		})
	}
}

// =====================================================================
// 运营分析接口 (Operations Analytics)
// =====================================================================

// 1. 产品动销排行
type productPerformanceItem struct {
	ID                int64   `json:"id"`
	SeriesName        string  `json:"seriesName"`
	SalesCount        int64   `json:"salesCount"`
	Amount            int64   `json:"amount"`
	ContributionRate  float64 `json:"contributionRate"`
	InventoryTurnover float64 `json:"inventoryTurnover"`
}

func (h *handler) OperationsProductPerformance() core.HandlerFunc {
	return func(ctx core.Context) {
		s, e := parseRange(ctx.Request().URL.Query().Get("rangeType"), "", "")

		// 按活动聚合抽奖数据
		type drawRow struct {
			ActivityID int64
			Count      int64
			Winners    int64
		}
		var rows []drawRow

		// 统计抽奖日志，按活动分组
		_ = h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			LeftJoin(h.readDB.ActivityIssues, h.readDB.ActivityIssues.ID.EqCol(h.readDB.ActivityDrawLogs.IssueID)).
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Gte(s)).
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Lte(e)).
			Select(
				h.readDB.ActivityIssues.ActivityID,
				h.readDB.ActivityDrawLogs.ID.Count().As("count"),
				h.readDB.ActivityDrawLogs.IsWinner.Sum().As("winners"),
			).
			Group(h.readDB.ActivityIssues.ActivityID).
			Order(h.readDB.ActivityDrawLogs.ID.Count().Desc()).
			Limit(10).
			Scan(&rows)

		// 获取活动详情（名称和单价）
		activityIDs := make([]int64, len(rows))
		for i, r := range rows {
			activityIDs[i] = r.ActivityID
		}

		type actInfo struct {
			Name      string
			PriceDraw int64
		}
		actMap := make(map[int64]actInfo)
		if len(activityIDs) > 0 {
			acts, _ := h.readDB.Activities.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.Activities.ID.In(activityIDs...)).Find()
			for _, a := range acts {
				actMap[a.ID] = actInfo{Name: a.Name, PriceDraw: a.PriceDraw}
			}
		}

		// 计算总数用于贡献率
		var totalCount int64
		for _, r := range rows {
			totalCount += r.Count
		}

		out := make([]productPerformanceItem, len(rows))
		for i, r := range rows {
			info := actMap[r.ActivityID]

			var contribution float64
			if totalCount > 0 {
				contribution = float64(r.Count) / float64(totalCount) * 100
			}

			// 周转率简化计算
			days := e.Sub(s).Hours() / 24
			if days < 1 {
				days = 1
			}
			turnover := float64(r.Count) / days * 7

			out[i] = productPerformanceItem{
				ID:                r.ActivityID,
				SeriesName:        info.Name,
				SalesCount:        r.Count,
				Amount:            (r.Count * info.PriceDraw) / 100, // 转换为元
				ContributionRate:  float64(int(contribution*10)) / 10.0,
				InventoryTurnover: float64(int(turnover*10)) / 10.0,
			}
		}

		ctx.Payload(out)
	}
}

// 2. 积分经济总览
type pointsEconomySummaryResponse struct {
	TotalIssued           int64   `json:"totalIssued"`
	TotalConsumed         int64   `json:"totalConsumed"`
	NetChange             int64   `json:"netChange"`
	ActiveUsersWithPoints int64   `json:"activeUsersWithPoints"`
	ConversionRate        float64 `json:"conversionRate"`
}

func (h *handler) OperationsPointsEconomySummary() core.HandlerFunc {
	return func(ctx core.Context) {
		s, e := parseRange(ctx.Request().URL.Query().Get("rangeType"), "", "")

		// 发行总额 (正数积分)
		var issuedNull sql.NullInt64
		_ = h.readDB.UserPointsLedger.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.UserPointsLedger.CreatedAt.Gte(s)).
			Where(h.readDB.UserPointsLedger.CreatedAt.Lte(e)).
			Where(h.readDB.UserPointsLedger.Points.Gt(0)).
			Select(h.readDB.UserPointsLedger.Points.Sum()).
			Scan(&issuedNull)
		issued := issuedNull.Int64

		// 消耗总额 (负数积分)
		var consumedNull sql.NullInt64
		_ = h.readDB.UserPointsLedger.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.UserPointsLedger.CreatedAt.Gte(s)).
			Where(h.readDB.UserPointsLedger.CreatedAt.Lte(e)).
			Where(h.readDB.UserPointsLedger.Points.Lt(0)).
			Select(h.readDB.UserPointsLedger.Points.Sum()).
			Scan(&consumedNull)
		consumed := -consumedNull.Int64 // 转为正数

		// 持分活跃用户数
		activeUsers, _ := h.readDB.UserPoints.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.UserPoints.Points.Gt(0)).
			Count()

		// 活跃持仓率
		totalUsers, _ := h.readDB.Users.WithContext(ctx.RequestContext()).ReadDB().Count()
		var conversionRate float64
		if totalUsers > 0 {
			conversionRate = float64(activeUsers) / float64(totalUsers) * 100
		}

		ctx.Payload(pointsEconomySummaryResponse{
			TotalIssued:           issued,
			TotalConsumed:         consumed,
			NetChange:             issued - consumed,
			ActiveUsersWithPoints: activeUsers,
			ConversionRate:        float64(int(conversionRate*10)) / 10.0,
		})
	}
}

// 3. 积分趋势
type pointsTrendItem struct {
	Date      string `json:"date"`
	Issued    int64  `json:"issued"`
	Consumed  int64  `json:"consumed"`
	Expired   int64  `json:"expired"`
	NetChange int64  `json:"netChange"`
	Balance   int64  `json:"balance"`
}

func (h *handler) OperationsPointsTrend() core.HandlerFunc {
	return func(ctx core.Context) {
		s, e := parseRange(ctx.Request().URL.Query().Get("rangeType"), "", "")
		buckets := buildBuckets(s, e, "day")

		out := make([]pointsTrendItem, len(buckets))
		var runningBalance int64

		// 获取初始余额
		var initBalanceNull sql.NullInt64
		_ = h.readDB.UserPointsLedger.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.UserPointsLedger.CreatedAt.Lt(s)).
			Select(h.readDB.UserPointsLedger.Points.Sum()).
			Scan(&initBalanceNull)
		runningBalance = initBalanceNull.Int64

		for i, b := range buckets {
			// 当日发行
			var issuedNull sql.NullInt64
			_ = h.readDB.UserPointsLedger.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.UserPointsLedger.CreatedAt.Gte(b.Start)).
				Where(h.readDB.UserPointsLedger.CreatedAt.Lte(b.End)).
				Where(h.readDB.UserPointsLedger.Points.Gt(0)).
				Select(h.readDB.UserPointsLedger.Points.Sum()).
				Scan(&issuedNull)
			issued := issuedNull.Int64

			// 当日消耗
			var consumedNull sql.NullInt64
			_ = h.readDB.UserPointsLedger.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.UserPointsLedger.CreatedAt.Gte(b.Start)).
				Where(h.readDB.UserPointsLedger.CreatedAt.Lte(b.End)).
				Where(h.readDB.UserPointsLedger.Points.Lt(0)).
				Select(h.readDB.UserPointsLedger.Points.Sum()).
				Scan(&consumedNull)
			consumed := -consumedNull.Int64

			netChange := issued - consumed
			runningBalance += netChange

			out[i] = pointsTrendItem{
				Date:      b.Label,
				Issued:    issued,
				Consumed:  consumed,
				Expired:   0,
				NetChange: netChange,
				Balance:   runningBalance,
			}
		}

		ctx.Payload(out)
	}
}

// 4. 积分收支结构
type pointsStructureItem struct {
	Category   string  `json:"category"`
	Amount     int64   `json:"amount"`
	Percentage float64 `json:"percentage"`
	Trend      string  `json:"trend"`
}

func (h *handler) OperationsPointsStructure() core.HandlerFunc {
	return func(ctx core.Context) {
		s, e := parseRange(ctx.Request().URL.Query().Get("rangeType"), "", "")

		// 按Action分组统计
		type actionRow struct {
			Action string
			Total  int64
		}
		var rows []actionRow

		_ = h.readDB.UserPointsLedger.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.UserPointsLedger.CreatedAt.Gte(s)).
			Where(h.readDB.UserPointsLedger.CreatedAt.Lte(e)).
			Select(
				h.readDB.UserPointsLedger.Action,
				h.readDB.UserPointsLedger.Points.Sum().As("total"),
			).
			Group(h.readDB.UserPointsLedger.Action).
			Scan(&rows)

		// 计算总量
		var absTotal int64
		for _, r := range rows {
			if r.Total > 0 {
				absTotal += r.Total
			} else {
				absTotal += -r.Total
			}
		}

		// Action名称映射
		actionNames := map[string]string{
			"signin":         "签到奖励",
			"order_deduct":   "订单消耗",
			"refund_restore": "退款返还",
			"manual":         "手动调整",
			"task_reward":    "任务奖励",
			"draw_win":       "抽奖中奖",
		}

		out := make([]pointsStructureItem, 0, len(rows))
		for _, r := range rows {
			name := actionNames[r.Action]
			if name == "" {
				name = r.Action
			}
			var pct float64
			if absTotal > 0 {
				amt := r.Total
				if amt < 0 {
					amt = -amt
				}
				pct = float64(amt) / float64(absTotal) * 100
			}
			out = append(out, pointsStructureItem{
				Category:   name,
				Amount:     r.Total,
				Percentage: float64(int(pct*10)) / 10.0,
				Trend:      "+0%",
			})
		}

		ctx.Payload(out)
	}
}

// 5. 优惠券效能排行
type couponEffectivenessItem struct {
	CouponID      int64   `json:"couponId"`
	CouponName    string  `json:"couponName"`
	Type          string  `json:"type"`
	IssuedCount   int64   `json:"issuedCount"`
	UsedCount     int64   `json:"usedCount"`
	UsedRate      float64 `json:"usedRate"`
	BroughtOrders int64   `json:"broughtOrders"`
	BroughtAmount int64   `json:"broughtAmount"`
	ROI           float64 `json:"roi"`
}

func (h *handler) OperationsCouponEffectiveness() core.HandlerFunc {
	return func(ctx core.Context) {
		s, e := parseRange(ctx.Request().URL.Query().Get("rangeType"), "", "")

		// 获取所有券模板
		coupons, _ := h.readDB.SystemCoupons.WithContext(ctx.RequestContext()).ReadDB().Find()

		out := make([]couponEffectivenessItem, 0, len(coupons))
		for _, c := range coupons {
			// 发放数量
			issued, _ := h.readDB.UserCoupons.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.UserCoupons.CouponID.Eq(c.ID)).
				Where(h.readDB.UserCoupons.CreatedAt.Gte(s)).
				Where(h.readDB.UserCoupons.CreatedAt.Lte(e)).
				Count()

			// 使用数量
			used, _ := h.readDB.UserCoupons.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.UserCoupons.CouponID.Eq(c.ID)).
				Where(h.readDB.UserCoupons.Status.Eq(2)).
				Where(h.readDB.UserCoupons.UsedAt.Gte(s)).
				Where(h.readDB.UserCoupons.UsedAt.Lte(e)).
				Count()

			// 带动订单和总价值 (实收 + 优惠券面值)
			var orderStats struct {
				Orders int64
				Amount int64
			}
			_ = h.readDB.Orders.WithContext(ctx.RequestContext()).UnderlyingDB().
				Where("coupon_id = ?", c.ID).
				Where("status = ?", 2).
				Where("paid_at >= ?", s).
				Where("paid_at <= ?", e).
				Select("COUNT(id) as orders, SUM(actual_amount) as amount").
				Scan(&orderStats)

			discountTotal := c.DiscountValue * used
			broughtTotalValue := orderStats.Amount + discountTotal // 总业务价值

			var usedRate float64
			if issued > 0 {
				usedRate = float64(used) / float64(issued) * 100
			}

			// ROI计算: 总价值 / 优惠成本
			var roi float64
			if discountTotal > 0 {
				roi = float64(broughtTotalValue) / float64(discountTotal)
			}

			typeStr := "直减券"
			switch c.DiscountType {
			case 2:
				typeStr = "满减券"
			case 3:
				typeStr = "折扣券"
			}

			out = append(out, couponEffectivenessItem{
				CouponID:      c.ID,
				CouponName:    c.Name,
				Type:          typeStr,
				IssuedCount:   issued,
				UsedCount:     used,
				UsedRate:      float64(int(usedRate*10)) / 10.0,
				BroughtOrders: orderStats.Orders,
				BroughtAmount: broughtTotalValue,
				ROI:           float64(int(roi*10)) / 10.0,
			})
		}

		ctx.Payload(out)
	}
}

// 6. 库存预警列表
type inventoryAlertItem struct {
	ID         int64   `json:"id"`
	Name       string  `json:"name"`
	Type       string  `json:"type"`
	Stock      int64   `json:"stock"`
	Threshold  int64   `json:"threshold"`
	SalesSpeed float64 `json:"salesSpeed"`
}

func (h *handler) OperationsInventoryAlerts() core.HandlerFunc {
	return func(ctx core.Context) {
		threshold := int64(20)
		out := make([]inventoryAlertItem, 0)

		// 1. 奖品库存预警 (活动奖品设置表)
		type rewardRow struct {
			ID           int64
			ProductID    int64
			ProductName  string
			ActivityName string
			Level        int32
			Quantity     int64
			OriginalQty  int64
		}
		var rows []rewardRow

		_ = h.readDB.ActivityRewardSettings.WithContext(ctx.RequestContext()).ReadDB().
			LeftJoin(h.readDB.Products, h.readDB.Products.ID.EqCol(h.readDB.ActivityRewardSettings.ProductID)).
			LeftJoin(h.readDB.ActivityIssues, h.readDB.ActivityIssues.ID.EqCol(h.readDB.ActivityRewardSettings.IssueID)).
			LeftJoin(h.readDB.Activities, h.readDB.Activities.ID.EqCol(h.readDB.ActivityIssues.ActivityID)).
			Where(h.readDB.ActivityRewardSettings.Quantity.Lt(threshold)).
			Where(h.readDB.ActivityRewardSettings.Quantity.Gt(0)).
			Select(
				h.readDB.ActivityRewardSettings.ID,
				h.readDB.ActivityRewardSettings.ProductID,
				h.readDB.Products.Name.As("product_name"),
				h.readDB.Activities.Name.As("activity_name"),
				h.readDB.ActivityRewardSettings.Level,
				h.readDB.ActivityRewardSettings.Quantity,
				h.readDB.ActivityRewardSettings.OriginalQty,
			).
			Order(h.readDB.ActivityRewardSettings.Quantity).
			Limit(15).
			Scan(&rows)

		for _, r := range rows {
			consumed := r.OriginalQty - r.Quantity
			speed := float64(consumed) / 30.0

			// 构建显示名称
			displayName := r.ProductName
			if displayName == "" {
				displayName = r.ActivityName + " " + strconv.Itoa(int(r.Level)) + "等奖"
			}

			out = append(out, inventoryAlertItem{
				ID:         r.ID,
				Name:       displayName,
				Type:       "physical",
				Stock:      r.Quantity,
				Threshold:  threshold,
				SalesSpeed: float64(int(speed*10)) / 10.0,
			})
		}

		// 2. 道具卡库存预警 (库存较少的道具卡)
		cardThreshold := int64(50)
		type cardRow struct {
			ID       int64
			CardID   int64
			CardName string
			Stock    int64
		}
		var cardRows []cardRow

		_ = h.readDB.UserItemCards.WithContext(ctx.RequestContext()).ReadDB().
			LeftJoin(h.readDB.SystemItemCards, h.readDB.SystemItemCards.ID.EqCol(h.readDB.UserItemCards.CardID)).
			Where(h.readDB.UserItemCards.Status.Eq(1)). // 未使用
			Select(
				h.readDB.SystemItemCards.ID,
				h.readDB.UserItemCards.CardID,
				h.readDB.SystemItemCards.Name.As("card_name"),
				h.readDB.UserItemCards.ID.Count().As("stock"),
			).
			Group(h.readDB.UserItemCards.CardID).
			Having(h.readDB.UserItemCards.ID.Count().Lt(int(cardThreshold))).
			Scan(&cardRows)

		for _, r := range cardRows {
			out = append(out, inventoryAlertItem{
				ID:         r.CardID,
				Name:       r.CardName,
				Type:       "virtual",
				Stock:      r.Stock,
				Threshold:  cardThreshold,
				SalesSpeed: 2.0, // 简化
			})
		}

		ctx.Payload(out)
	}
}

// 7. 风险事件监控
type riskEventItem struct {
	UserID      int64  `json:"userId"`
	Nickname    string `json:"nickname"`
	Avatar      string `json:"avatar"`
	Type        string `json:"type"`
	Description string `json:"description"`
	RiskLevel   string `json:"riskLevel"`
	CreatedAt   string `json:"createdAt"`
}

func (h *handler) OperationsRiskEvents() core.HandlerFunc {
	return func(ctx core.Context) {
		now := time.Now()
		last24h := now.Add(-24 * time.Hour)

		type winnerRow struct {
			UserID   int64
			WinCount int64
		}
		var winners []winnerRow

		_ = h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.ActivityDrawLogs.IsWinner.Eq(1)).
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Gte(last24h)).
			Select(
				h.readDB.ActivityDrawLogs.UserID,
				h.readDB.ActivityDrawLogs.ID.Count().As("win_count"),
			).
			Group(h.readDB.ActivityDrawLogs.UserID).
			Having(h.readDB.ActivityDrawLogs.ID.Count().Gte(3)).
			Scan(&winners)

		userIDs := make([]int64, len(winners))
		for i, w := range winners {
			userIDs[i] = w.UserID
		}

		userMap := make(map[int64]struct {
			Nickname string
			Avatar   string
		})
		if len(userIDs) > 0 {
			users, _ := h.readDB.Users.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.Users.ID.In(userIDs...)).Find()
			for _, u := range users {
				userMap[u.ID] = struct {
					Nickname string
					Avatar   string
				}{Nickname: u.Nickname, Avatar: u.Avatar}
			}
		}

		out := make([]riskEventItem, 0, len(winners))
		for _, w := range winners {
			u := userMap[w.UserID]
			level := "medium"
			if w.WinCount >= 5 {
				level = "high"
			}
			out = append(out, riskEventItem{
				UserID:      w.UserID,
				Nickname:    u.Nickname,
				Avatar:      u.Avatar,
				Type:        "frequent_win",
				Description: "24小时内中奖" + strconv.FormatInt(w.WinCount, 10) + "次",
				RiskLevel:   level,
				CreatedAt:   now.Format("15:04"),
			})
		}

		ctx.Payload(out)
	}
}

// 8. 实时中奖播报
type liveWinnerItem struct {
	ID        int64  `json:"id"`
	Nickname  string `json:"nickname"`
	Avatar    string `json:"avatar"`
	IssueName string `json:"issueName"`
	PrizeName string `json:"prizeName"`
	IsBigWin  bool   `json:"isBigWin"`
	Time      string `json:"time"`
}

type liveWinnersResponse struct {
	List  []liveWinnerItem `json:"list"`
	Stats struct {
		HourlyWinRate  float64 `json:"hourlyWinRate"`
		DrawsPerMinute int64   `json:"drawsPerMinute"`
	} `json:"stats"`
}

func (h *handler) DashboardLiveWinners() core.HandlerFunc {
	return func(ctx core.Context) {
		limitS := ctx.Request().URL.Query().Get("limit")
		limit := 20
		if l, err := strconv.Atoi(limitS); err == nil && l > 0 && l <= 50 {
			limit = l
		}

		type winRow struct {
			ID        int64
			UserID    int64
			Nickname  string
			Avatar    string
			IssueName string
			PrizeName string
			Level     int32
			CreatedAt time.Time
		}
		var rows []winRow

		_ = h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			LeftJoin(h.readDB.Users, h.readDB.Users.ID.EqCol(h.readDB.ActivityDrawLogs.UserID)).
			LeftJoin(h.readDB.ActivityIssues, h.readDB.ActivityIssues.ID.EqCol(h.readDB.ActivityDrawLogs.IssueID)).
			LeftJoin(h.readDB.ActivityRewardSettings, h.readDB.ActivityRewardSettings.ID.EqCol(h.readDB.ActivityDrawLogs.RewardID)).
			LeftJoin(h.readDB.Products, h.readDB.Products.ID.EqCol(h.readDB.ActivityRewardSettings.ProductID)).
			Where(h.readDB.ActivityDrawLogs.IsWinner.Eq(1)).
			Order(h.readDB.ActivityDrawLogs.ID.Desc()).
			Limit(limit).
			Select(
				h.readDB.ActivityDrawLogs.ID,
				h.readDB.ActivityDrawLogs.UserID,
				h.readDB.Users.Nickname,
				h.readDB.Users.Avatar,
				h.readDB.ActivityIssues.IssueNumber.As("issue_name"),
				h.readDB.Products.Name.As("prize_name"),
				h.readDB.ActivityRewardSettings.Level,
				h.readDB.ActivityDrawLogs.CreatedAt,
			).
			Scan(&rows)

		now := time.Now()
		out := make([]liveWinnerItem, len(rows))
		for i, r := range rows {
			diff := now.Sub(r.CreatedAt)
			var timeStr string
			if diff < time.Minute {
				timeStr = "刚刚"
			} else if diff < time.Hour {
				timeStr = strconv.Itoa(int(diff.Minutes())) + "分钟前"
			} else {
				timeStr = strconv.Itoa(int(diff.Hours())) + "小时前"
			}

			out[i] = liveWinnerItem{
				ID:        r.ID,
				Nickname:  r.Nickname,
				Avatar:    r.Avatar,
				IssueName: r.IssueName,
				PrizeName: r.PrizeName,
				IsBigWin:  r.Level <= 2,
				Time:      timeStr,
			}
		}

		lastHour := now.Add(-time.Hour)
		hourDraws, _ := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Gte(lastHour)).
			Count()
		hourWins, _ := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Gte(lastHour)).
			Where(h.readDB.ActivityDrawLogs.IsWinner.Eq(1)).
			Count()

		var winRate float64
		if hourDraws > 0 {
			winRate = float64(hourWins) / float64(hourDraws) * 100
		}

		rsp := liveWinnersResponse{}
		rsp.List = out
		rsp.Stats.HourlyWinRate = float64(int(winRate*10)) / 10.0
		rsp.Stats.DrawsPerMinute = hourDraws / 60

		ctx.Payload(rsp)
	}
}

// =====================================================================
// 补充接口：订单趋势、活动统计、道具卡销售
// =====================================================================

// 订单转化趋势
type orderTrendItem struct {
	Date           string  `json:"date"`
	Visitors       int64   `json:"visitors"`
	Orders         int64   `json:"orders"`
	Payments       int64   `json:"payments"`
	Completions    int64   `json:"completions"`
	ConversionRate float64 `json:"conversionRate"`
}

func (h *handler) DashboardOrderTrend() core.HandlerFunc {
	return func(ctx core.Context) {
		s, e := parseRange(ctx.Request().URL.Query().Get("rangeType"), "", "")
		buckets := buildBuckets(s, e, "day")

		out := make([]orderTrendItem, len(buckets))
		for i, b := range buckets {
			// 访问数 (请求日志)
			visitors, _ := h.readDB.LogRequest.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.LogRequest.CreatedAt.Gte(b.Start)).
				Where(h.readDB.LogRequest.CreatedAt.Lte(b.End)).
				Where(h.readDB.LogRequest.Path.Like("/api/app/%")).
				Count()

			// 下单数
			orders, _ := h.readDB.Orders.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.Orders.CreatedAt.Gte(b.Start)).
				Where(h.readDB.Orders.CreatedAt.Lte(b.End)).
				Count()

			// 支付数
			payments, _ := h.readDB.Orders.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.Orders.Status.Eq(2)).
				Where(h.readDB.Orders.PaidAt.Gte(b.Start)).
				Where(h.readDB.Orders.PaidAt.Lte(b.End)).
				Count()

			// 完成数 (已消费 + 已发货)
			consumed, _ := h.readDB.Orders.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.Orders.Status.Eq(2), h.readDB.Orders.IsConsumed.Eq(1)).
				Where(h.readDB.Orders.UpdatedAt.Gte(b.Start)).
				Where(h.readDB.Orders.UpdatedAt.Lte(b.End)).
				Count()

			var rate float64
			if visitors > 0 {
				rate = float64(consumed) / float64(visitors) * 100
			}

			out[i] = orderTrendItem{
				Date:           b.Label,
				Visitors:       visitors,
				Orders:         orders,
				Payments:       payments,
				Completions:    consumed,
				ConversionRate: float64(int(rate*100)) / 100.0,
			}
		}

		ctx.Payload(out)
	}
}

// 活动抽奖统计
type activityStatsResponse struct {
	TotalActivities   int64   `json:"totalActivities"`
	TotalParticipants int64   `json:"totalParticipants"`
	TotalDraws        int64   `json:"totalDraws"`
	WinnerCount       int64   `json:"winnerCount"`
	OverallWinRate    float64 `json:"overallWinRate"`
	CostControl       float64 `json:"costControl"`
}

func (h *handler) DashboardActivityStats() core.HandlerFunc {
	return func(ctx core.Context) {
		s, e := parseRange(ctx.Request().URL.Query().Get("rangeType"), "", "")

		// 活动总数
		totalActivities, _ := h.readDB.Activities.WithContext(ctx.RequestContext()).ReadDB().Count()

		// 总抽奖次数
		totalDraws, _ := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Gte(s)).
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Lte(e)).
			Count()

		// 中奖数
		winnerCount, _ := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Gte(s)).
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Lte(e)).
			Where(h.readDB.ActivityDrawLogs.IsWinner.Eq(1)).
			Count()

		// 参与人数
		totalParticipants, _ := h.readDB.ActivityDrawLogs.WithContext(ctx.RequestContext()).ReadDB().
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Gte(s)).
			Where(h.readDB.ActivityDrawLogs.CreatedAt.Lte(e)).
			Distinct(h.readDB.ActivityDrawLogs.UserID).
			Count()

		var winRate float64
		if totalDraws > 0 {
			winRate = float64(winnerCount) / float64(totalDraws) * 100
		}

		ctx.Payload(activityStatsResponse{
			TotalActivities:   totalActivities,
			TotalParticipants: totalParticipants,
			TotalDraws:        totalDraws,
			WinnerCount:       winnerCount,
			OverallWinRate:    float64(int(winRate*100)) / 100.0,
			CostControl:       85.0, // 简化处理
		})
	}
}

// 道具卡销售数据
type itemCardSalesItem struct {
	CardID      int64   `json:"cardId"`
	CardName    string  `json:"cardName"`
	CardType    string  `json:"cardType"`
	SalesCount  int64   `json:"salesCount"`
	SalesAmount int64   `json:"salesAmount"`
	UsageRate   float64 `json:"usageRate"`
	AvgEffect   float64 `json:"avgEffect"`
}

func (h *handler) DashboardItemCardSales() core.HandlerFunc {
	return func(ctx core.Context) {
		s, e := parseRange(ctx.Request().URL.Query().Get("rangeType"), "", "")

		// 获取道具卡列表
		cards, _ := h.readDB.SystemItemCards.WithContext(ctx.RequestContext()).ReadDB().Find()

		out := make([]itemCardSalesItem, 0, len(cards))
		for _, c := range cards {
			// 发放数量
			issued, _ := h.readDB.UserItemCards.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.UserItemCards.CardID.Eq(c.ID)).
				Where(h.readDB.UserItemCards.CreatedAt.Gte(s)).
				Where(h.readDB.UserItemCards.CreatedAt.Lte(e)).
				Count()

			// 使用数量 (status=2表示已使用)
			used, _ := h.readDB.UserItemCards.WithContext(ctx.RequestContext()).ReadDB().
				Where(h.readDB.UserItemCards.CardID.Eq(c.ID)).
				Where(h.readDB.UserItemCards.Status.Eq(2)).
				Where(h.readDB.UserItemCards.UsedAt.Gte(s)).
				Where(h.readDB.UserItemCards.UsedAt.Lte(e)).
				Count()

			var usageRate float64
			if issued > 0 {
				usageRate = float64(used) / float64(issued) * 100
			}

			// 卡类型映射
			cardTypeMap := map[int32]string{1: "双倍奖励", 2: "概率提升", 3: "保护卡"}
			cardTypeName := cardTypeMap[c.CardType]
			if cardTypeName == "" {
				cardTypeName = "其他"
			}

			out = append(out, itemCardSalesItem{
				CardID:      c.ID,
				CardName:    c.Name,
				CardType:    cardTypeName,
				SalesCount:  issued,
				SalesAmount: issued * c.Price / 100,
				UsageRate:   float64(int(usageRate*10)) / 10.0,
				AvgEffect:   10.0,
			})
		}

		ctx.Payload(out)
	}
}
