package admin

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

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

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"`
	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
		}

		rsp.ItemCardSales = icCur
		rsp.DrawCount = dlCur
		rsp.NewUsers = nuCur
		rsp.TotalPoints = tpCur
		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"`
}

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)
	}
}