package commerce

import (
	"context"
	"crypto/rand"
	"encoding/hex"
	"errors"
	"fmt"
	"math"
	mathrand "math/rand"
	"net"
	"net/http"
	"strconv"
	"strings"
	"time"

	"github.com/go-sql-driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/clause"
)

type TaskAnswerRequest struct {
	Answer string `json:"answer"`
}

type WithdrawPointsRequest struct {
	Points int `json:"points"`
}

type InviteCodeUpdateRequest struct {
	InviteCode string `json:"invite_code"`
}

type PromotionCouponCreateRequest struct {
	Code              *string    `json:"code"`
	Description       *string    `json:"description"`
	Type              string     `json:"type"`
	Value             int        `json:"value"`
	ThresholdAmount   *int       `json:"threshold_amount"`
	MaxDiscountAmount *int       `json:"max_discount_amount"`
	MaxUses           int        `json:"max_uses"`
	MaxUsesPerUser    int        `json:"max_uses_per_user"`
	ExpiresAt         *time.Time `json:"expires_at"`
	ClaimMethod       string     `json:"claim_method"`
	ListOnMall        bool       `json:"list_on_mall"`
	PointsPrice       *int       `json:"points_price"`
	StockTotal        *int       `json:"stock_total"`
	MallTitle         *string    `json:"mall_title"`
	MallSubtitle      *string    `json:"mall_subtitle"`
	MallCoverImage    *string    `json:"mall_cover_image"`
	MallCategory      *string    `json:"mall_category"`
	MallScope         string     `json:"mall_scope"`
	MallSortOrder     int        `json:"mall_sort_order"`
}

type MallClaimRequest struct {
	MallItemID int64 `json:"mall_item_id"`
}

type AgentApplyRequest struct {
	Tier     string  `json:"tier"`
	Reason   *string `json:"reason"`
	Channels *string `json:"channels"`
}

type AffiliateClickRequest struct {
	AffCode     string  `json:"aff_code"`
	LandingURL  *string `json:"landing_url"`
	UTMSource   *string `json:"utm_source"`
	UTMMedium   *string `json:"utm_medium"`
	UTMCampaign *string `json:"utm_campaign"`
	UTMContent  *string `json:"utm_content"`
	UTMTerm     *string `json:"utm_term"`
}

func (s *Service) ListMemberLevels(ctx context.Context) ([]map[string]any, error) {
	var levels []MemberLevel
	if err := s.db.WithContext(ctx).Where("enabled = ?", true).Order("level ASC, id ASC").Find(&levels).Error; err != nil {
		return nil, err
	}
	out := make([]map[string]any, 0, len(levels))
	for _, level := range levels {
		out = append(out, mapFromJSON(level))
	}
	return out, nil
}

func (s *Service) GetMyMembership(ctx context.Context, userID int64) (map[string]any, error) {
	membership, err := s.getOrCreateMembership(ctx, userID)
	if err != nil {
		return nil, err
	}
	data := mapFromJSON(membership)
	level := (*MemberLevel)(nil)
	if membership.MemberLevelID != nil {
		var item MemberLevel
		if err := s.db.WithContext(ctx).Where("id = ?", *membership.MemberLevelID).First(&item).Error; err == nil {
			level = &item
			data["member_level"] = mapFromJSON(item)
			data["level"] = item.Level
			data["commission_rate"] = item.CommissionRate
		}
	}
	if level == nil {
		data["commission_rate"] = 0
	}
	currentQuarterSpent := s.currentQuarterSelfSpent(ctx, userID)
	data["current_quarter_self_spent"] = currentQuarterSpent
	data["current_quarter_spent"] = currentQuarterSpent
	return data, nil
}

func (s *Service) ListMyPointLogs(ctx context.Context, userID int64, limit int, offset int) ([]map[string]any, error) {
	var logs []PointLog
	if err := s.db.WithContext(ctx).Where("user_id = ?", userID).Order("id DESC").Limit(limit).Offset(offset).Find(&logs).Error; err != nil {
		return nil, err
	}
	out := make([]map[string]any, 0, len(logs))
	for _, log := range logs {
		out = append(out, mapFromJSON(log))
	}
	return out, nil
}

func (s *Service) PointsCenter(ctx context.Context, userID int64) (map[string]any, error) {
	membership, err := s.getOrCreateMembership(ctx, userID)
	if err != nil {
		return nil, err
	}
	var user UserAccount
	if err := s.db.WithContext(ctx).Where("id = ?", userID).First(&user).Error; err != nil {
		return nil, err
	}
	var tasks []PointTask
	_ = s.db.WithContext(ctx).Where("user_id = ?", userID).Find(&tasks).Error
	taskDone := map[string]bool{}
	var todayCheckin any
	today := pointTaskLocalDate(time.Now())
	for _, task := range tasks {
		taskDone[task.TaskType] = true
		if task.TaskType == "daily_checkin" && task.CheckinDate != nil && task.CheckinDate.In(pointTaskLocation()).Format("2006-01-02") == today {
			todayCheckin = task.CheckinPoints
		}
	}
	var withdrawals []PointWithdrawal
	_ = s.db.WithContext(ctx).Where("user_id = ?", userID).Order("id DESC").Limit(10).Find(&withdrawals).Error
	return map[string]any{
		"available_points":  membership.AvailablePoints,
		"balance_cents":     user.Balance,
		"qq_rewarded":       taskDone["qq_community"],
		"bilibili_rewarded": taskDone["bilibili_follow"],
		"today_checkin":     todayCheckin,
		"self_purchase":     s.rewardSummary(ctx, userID, "point_rewards"),
		"member_commission": s.settlementPointSummary(ctx, userID, "member_commission"),
		"agent_profit":      s.settlementPointSummary(ctx, userID, "agent_profit"),
		"settings": map[string]any{
			"qq_reward":           s.settingInt(ctx, "points.qq_reward", 100),
			"bilibili_reward":     s.settingInt(ctx, "points.bilibili_reward", 100),
			"checkin_min_points":  s.settingInt(ctx, "points.checkin_min_points", 10),
			"checkin_max_points":  s.settingInt(ctx, "points.checkin_max_points", 50),
			"withdraw_fee_rate":   s.settingInt(ctx, "points.withdraw_fee_rate", 200),
			"withdraw_min_points": s.settingInt(ctx, "points.withdraw_min_points", 1000),
		},
		"recent_withdrawals": mapFromList(withdrawals),
	}, nil
}

func (s *Service) CompletePointTask(ctx context.Context, userID int64, taskType string, answer string) (map[string]any, error) {
	reward, correct := 0, ""
	switch taskType {
	case "qq_community":
		reward = s.settingInt(ctx, "points.qq_reward", 100)
		correct = s.userOpsSettingString(ctx, "points.qq_answer", "")
	case "bilibili_follow":
		reward = s.settingInt(ctx, "points.bilibili_reward", 100)
		correct = s.userOpsSettingString(ctx, "points.bilibili_answer", "")
	case "daily_checkin":
		minPoints := s.settingInt(ctx, "points.checkin_min_points", 10)
		maxPoints := s.settingInt(ctx, "points.checkin_max_points", 50)
		if maxPoints < minPoints {
			minPoints, maxPoints = maxPoints, minPoints
		}
		reward = minPoints + mathrand.Intn(maxInt(1, maxPoints-minPoints+1))
	default:
		return nil, BadRequest("invalid task type")
	}
	if reward <= 0 {
		return nil, BadRequest("task is not open")
	}
	if taskType != "daily_checkin" && strings.TrimSpace(correct) != "" && strings.TrimSpace(answer) != strings.TrimSpace(correct) {
		return nil, BadRequest("answer is incorrect")
	}
	var out map[string]any
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		txService := s.withDB(tx)
		query := tx.Clauses(clause.Locking{Strength: "UPDATE"}).Where("user_id = ? AND task_type = ?", userID, taskType)
		var checkinDate *time.Time
		var checkinPoints *int
		refNo := fmt.Sprintf("task:%s:%d", taskType, userID)
		if taskType == "daily_checkin" {
			todayText := pointTaskLocalDate(time.Now())
			today, _ := time.ParseInLocation("2006-01-02", todayText, pointTaskLocation())
			checkinDate = &today
			checkinPoints = &reward
			query = query.Where("checkin_date = ?", todayText)
			refNo = fmt.Sprintf("task:%s:%d:%s", taskType, userID, strings.ReplaceAll(todayText, "-", ""))
		}
		var existing PointTask
		if err := query.First(&existing).Error; err == nil {
			return BadRequest("task already completed")
		} else if err != nil && err != gorm.ErrRecordNotFound {
			return err
		}
		submitted := strings.TrimSpace(answer)
		task := PointTask{UserID: userID, TaskType: taskType, RewardPoints: reward, SubmittedValue: trimPtr(&submitted), CheckinDate: checkinDate, CheckinPoints: checkinPoints}
		now := time.Now().UTC()
		task.CompletedAt = &now
		if err := tx.Create(&task).Error; err != nil {
			if isDuplicateKeyError(err) && taskType == "daily_checkin" {
				return BadRequest("task already completed")
			}
			return err
		}
		log, err := txService.addPoints(ctx, userID, reward, "activity_gift", 0, refNo, fmt.Sprintf("complete point task %s", taskType))
		if err != nil {
			return err
		}
		out = map[string]any{"reward_points": reward, "available_points": log.BalanceAfter}
		return nil
	})
	return out, err
}

func (s *Service) WithdrawPoints(ctx context.Context, userID int64, points int) (map[string]any, error) {
	if points <= 0 {
		return nil, BadRequest("points must be positive")
	}
	minPoints := s.settingInt(ctx, "points.withdraw_min_points", 1000)
	if points < minPoints {
		return nil, BadRequest(fmt.Sprintf("minimum withdraw points is %d", minPoints))
	}
	feeRate := s.settingInt(ctx, "points.withdraw_fee_rate", 200)
	feePoints := int(math.Ceil(float64(points*feeRate) / 10000.0))
	netPoints := points - feePoints
	creditedCents := (netPoints / pointsToCentsRate) * 100
	if creditedCents <= 0 {
		return nil, BadRequest("points are insufficient to exchange balance")
	}
	var out map[string]any
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		txService := s.withDB(tx)
		membership, err := txService.getOrCreateMembership(ctx, userID)
		if err != nil {
			return err
		}
		if membership.AvailablePoints < points {
			return BadRequest("points not enough")
		}
		log, err := txService.addPoints(ctx, userID, -points, "points_use", 0, fmt.Sprintf("withdraw:%d:%d", userID, time.Now().UnixNano()), fmt.Sprintf("withdraw %d points, fee %d", points, feePoints))
		if err != nil {
			return err
		}
		var user UserAccount
		if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).Where("id = ?", userID).First(&user).Error; err != nil {
			return err
		}
		nextBalance := user.Balance + creditedCents
		if err := tx.Model(&UserAccount{}).Where("id = ?", userID).Update("balance", nextBalance).Error; err != nil {
			return err
		}
		remark := "points withdraw to balance"
		if err := tx.Create(&BalanceLog{UserID: userID, ChangeType: "admin_adjust", Delta: creditedCents, BalanceAfter: nextBalance, Remark: &remark}).Error; err != nil {
			return err
		}
		now := time.Now().UTC()
		if err := tx.Create(&PointWithdrawal{UserID: userID, PointsAmount: points, FeePoints: feePoints, CreditedCents: creditedCents, Status: "completed", Remark: &remark, ProcessedAt: &now}).Error; err != nil {
			return err
		}
		out = map[string]any{"available_points": log.BalanceAfter, "balance_cents": nextBalance, "points_amount": points, "fee_points": feePoints, "credited_cents": creditedCents}
		return nil
	})
	return out, err
}

func (s *Service) PromotionOverview(ctx context.Context, userID int64) (map[string]any, error) {
	user, err := s.userAccount(ctx, userID)
	if err != nil {
		return nil, err
	}
	code, err := s.ensureInviteCode(ctx, &user)
	if err != nil {
		return nil, err
	}
	membership, _ := s.getOrCreateMembership(ctx, userID)
	baseURL := s.userOpsSettingString(ctx, "promotion.base_url", s.userOpsSettingString(ctx, "platform.frontend_url", "http://localhost:5173"))
	var clickCount, registeredCount, settledPoints, frozenPoints, reversedPoints int64
	_ = s.db.WithContext(ctx).Model(&AffiliateClick{}).Where("agent_id = ?", userID).Count(&clickCount).Error
	_ = s.db.WithContext(ctx).Model(&UserAccount{}).Where("bound_agent_id = ?", userID).Count(&registeredCount).Error
	_ = s.db.WithContext(ctx).Model(&AgentSettlement{}).Where("user_id = ? AND settlement_type = ? AND status = ?", userID, "member_commission", "settled").Select("COALESCE(SUM(points_amount),0)").Scan(&settledPoints).Error
	_ = s.db.WithContext(ctx).Model(&AgentSettlement{}).Where("user_id = ? AND settlement_type = ? AND status IN ?", userID, "member_commission", []string{"pending", "frozen"}).Select("COALESCE(SUM(points_amount),0)").Scan(&frozenPoints).Error
	_ = s.db.WithContext(ctx).Model(&AgentSettlement{}).Where("user_id = ? AND settlement_type = ? AND status = ?", userID, "member_commission", "reversed").Select("COALESCE(SUM(points_amount),0)").Scan(&reversedPoints).Error
	memberLevel := 0
	memberLevelName := ""
	if membership.MemberLevelID != nil {
		var level MemberLevel
		if err := s.db.WithContext(ctx).Where("id = ?", *membership.MemberLevelID).First(&level).Error; err == nil {
			memberLevel = level.Level
			memberLevelName = level.Name
		}
	}
	return map[string]any{
		"invite_code":                  code,
		"invite_link":                  promotionLink(baseURL, code),
		"register_link":                promotionLink(baseURL, code),
		"invite_code_edit_enabled":     s.settingBool(ctx, "promotion.invite_code_edit_enabled", true),
		"invite_code_change_count":     user.InviteCodeChangeCount,
		"invite_code_change_limit":     inviteCodeChangeLimit(user),
		"invite_code_change_remaining": maxInt(0, inviteCodeChangeLimit(user)-user.InviteCodeChangeCount),
		"click_count":                  clickCount,
		"registered_count":             registeredCount,
		"reward_points":                settledPoints,
		"settled_reward_points":        settledPoints,
		"frozen_reward_points":         frozenPoints,
		"reversed_reward_points":       reversedPoints,
		"available_points":             membership.AvailablePoints,
		"reward_rate":                  s.membershipCommissionRate(ctx, membership),
		"member_level":                 memberLevel,
		"member_level_name":            memberLevelName,
	}, nil
}

func (s *Service) UpdateInviteCode(ctx context.Context, userID int64, code string) (map[string]any, error) {
	code = strings.ToUpper(strings.TrimSpace(code))
	if len(code) < 3 || len(code) > 8 || !alphaNum(code) {
		return nil, BadRequest("invalid invite code")
	}
	if !s.settingBool(ctx, "promotion.invite_code_edit_enabled", true) {
		return nil, HTTPError{Status: http.StatusForbidden, Detail: "invite code editing is disabled"}
	}
	var user UserAccount
	if err := s.db.WithContext(ctx).Clauses(clause.Locking{Strength: "UPDATE"}).Where("id = ?", userID).First(&user).Error; err != nil {
		return nil, err
	}
	if user.InviteCodeChangeCount >= inviteCodeChangeLimit(user) {
		return nil, BadRequest("invite code change limit reached")
	}
	var count int64
	if err := s.db.WithContext(ctx).Model(&UserAccount{}).Where("UPPER(invite_code) = ? AND id <> ?", code, userID).Count(&count).Error; err != nil {
		return nil, err
	}
	if count > 0 {
		return nil, BadRequest("invite code already exists")
	}
	nextCount := user.InviteCodeChangeCount + 1
	if err := s.db.WithContext(ctx).Model(&UserAccount{}).Where("id = ?", userID).Updates(map[string]any{"invite_code": code, "invite_code_change_count": nextCount}).Error; err != nil {
		return nil, err
	}
	baseURL := s.userOpsSettingString(ctx, "promotion.base_url", s.userOpsSettingString(ctx, "platform.frontend_url", "http://localhost:5173"))
	return map[string]any{"invite_code": code, "invite_link": promotionLink(baseURL, code), "register_link": promotionLink(baseURL, code), "invite_code_change_count": nextCount, "invite_code_change_limit": inviteCodeChangeLimit(user), "invite_code_change_remaining": maxInt(0, inviteCodeChangeLimit(user)-nextCount), "invite_code_edit_enabled": true}, nil
}

func (s *Service) ListPromotionCoupons(ctx context.Context, userID int64, limit int, offset int) (map[string]any, error) {
	if err := s.refundExpiredMemberCoupons(ctx, &userID, 100); err != nil {
		return nil, err
	}
	var total int64
	db := s.db.WithContext(ctx).Model(&PromoCode{}).Where("issuer_user_id = ?", userID)
	if err := db.Count(&total).Error; err != nil {
		return nil, err
	}
	var coupons []PromoCode
	if err := db.Order("id DESC").Limit(limit).Offset(offset).Find(&coupons).Error; err != nil {
		return nil, err
	}
	items := make([]map[string]any, 0, len(coupons))
	for _, coupon := range coupons {
		items = append(items, promoCodeMap(coupon))
	}
	return map[string]any{"items": items, "total": total}, nil
}

func (s *Service) CreatePromotionCoupon(ctx context.Context, userID int64, req PromotionCouponCreateRequest) (map[string]any, error) {
	req.Type = defaultString(req.Type, "fixed")
	req.ClaimMethod = defaultString(req.ClaimMethod, "code")
	if req.ExpiresAt != nil && !req.ExpiresAt.After(time.Now().UTC()) {
		return nil, BadRequest("优惠券有效期必须晚于当前时间")
	}
	if req.ClaimMethod != "code" && req.ClaimMethod != "mall" {
		return nil, BadRequest("请选择有效的领取方式")
	}
	if req.MaxUses <= 0 {
		req.MaxUses = 1
	}
	if req.MaxUsesPerUser <= 0 {
		req.MaxUsesPerUser = 1
	}
	var activeAgent UserAgent
	var activeTier AgentTier
	isAgent := false
	if result := s.db.WithContext(ctx).Where("user_id = ? AND status IN ?", userID, []string{"active", "observing"}).Find(&activeAgent); result.Error == nil && result.RowsAffected > 0 && activeAgent.AgentTierID != nil {
		if err := s.db.WithContext(ctx).Where("id = ?", *activeAgent.AgentTierID).First(&activeTier).Error; err == nil {
			isAgent = true
		}
	}
	if err := validatePromotionCouponRequest(req, isAgent); err != nil {
		return nil, err
	}
	code := ""
	if req.Code != nil {
		code = strings.ToUpper(strings.TrimSpace(*req.Code))
	}
	if code == "" {
		prefix := "MC"
		if isAgent {
			prefix = "AC"
		}
		code = s.generateCouponCode(ctx, prefix)
	}
	if len(code) < 4 || len(code) > 16 || !alphaNum(code) {
		return nil, BadRequest("优惠码只能包含 4-16 位字母或数字")
	}
	var exists int64
	if err := s.db.WithContext(ctx).Model(&PromoCode{}).Where("UPPER(code) = ?", code).Count(&exists).Error; err != nil {
		return nil, err
	}
	if exists > 0 {
		return nil, BadRequest("优惠码已存在，请换一个")
	}
	issuerType := "member"
	if isAgent {
		issuerType = "agent"
	}
	pointCost := 0
	if isAgent {
		if err := s.ensureAgentCouponQuota(ctx, activeAgent, activeTier); err != nil {
			return nil, err
		}
		if req.Type == "percent" && req.Value*100 < activeTier.CostRate {
			minBalance := s.settingInt(ctx, "promotion.min_agent_balance_for_subsidy_coupon_cents", 12800)
			if activeAgent.AgentBalance < minBalance {
				return nil, BadRequest(fmt.Sprintf("代理余额不足，创建补贴折扣券至少需要保留 ¥%.2f", float64(minBalance)/100))
			}
		}
	} else {
		maxDiscount := req.Value
		if req.Type == "percent" && req.MaxDiscountAmount != nil {
			maxDiscount = *req.MaxDiscountAmount
		}
		pointCost = maxDiscount * req.MaxUses * pointsToCentsRate / 100
	}
	now := time.Now().UTC()
	coupon := PromoCode{Code: code, Description: req.Description, IssuerUserID: &userID, IssuerType: &issuerType, Type: req.Type, Value: req.Value, Scope: "all", DiscountLevel: "order", MaxUses: req.MaxUses, MaxUsesPerUser: req.MaxUsesPerUser, ClaimMethod: req.ClaimMethod, ListOnMall: req.ListOnMall, IsEnabled: true, PointCost: pointCost, PointRefunded: 0, AppliesToRenewals: true, IsStackable: false, StartsAt: &now, ExpiresAt: req.ExpiresAt}
	if req.ThresholdAmount != nil {
		coupon.MinOrderAmount = *req.ThresholdAmount
	}
	if req.MaxDiscountAmount != nil {
		coupon.MaxDiscountAmount = *req.MaxDiscountAmount
	}
	var out map[string]any
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		txService := s.withDB(tx)
		if pointCost > 0 {
			if _, err := txService.addPoints(ctx, userID, -pointCost, "points_use", 0, fmt.Sprintf("coupon:create:%s", code), fmt.Sprintf("create member coupon %s", code)); err != nil {
				return err
			}
		}
		if err := tx.Create(&coupon).Error; err != nil {
			return err
		}
		out = map[string]any{"coupon": promoCodeMap(coupon)}
		if req.ClaimMethod == "mall" || req.ListOnMall {
			scope := defaultString(req.MallScope, "public")
			stock := intPtrValue(req.StockTotal)
			if stock <= 0 {
				stock = req.MaxUses
			}
			item := CouponMallItem{PromoCodeID: coupon.ID, IssuerUserID: userID, Title: firstStringPtr(req.MallTitle, req.Description, &coupon.Code), Subtitle: req.MallSubtitle, CoverImage: req.MallCoverImage, Description: req.Description, PointsPrice: intPtrValue(req.PointsPrice), StockTotal: stock, StockRemaining: stock, Status: "active", Scope: scope, SortOrder: req.MallSortOrder, Category: req.MallCategory, MaxClaimsPerUser: 1}
			if err := tx.Create(&item).Error; err != nil {
				return err
			}
			out["mall_item"] = mapFromJSON(item)
		}
		if isAgent {
			out["agent_balance"] = activeAgent.AgentBalance
		} else if membership, err := txService.getOrCreateMembership(ctx, userID); err == nil {
			out["available_points"] = membership.AvailablePoints
		}
		return nil
	})
	if err != nil {
		return nil, err
	}
	return out, nil
}

func (s *Service) ListMallItems(ctx context.Context, userID int64, query map[string]string, limit int, offset int) (map[string]any, error) {
	user, err := s.userAccount(ctx, userID)
	if err != nil {
		return nil, err
	}
	db := s.db.WithContext(ctx).Table("coupon_mall_items AS m").
		Select("m.*, p.code AS code, p.description AS promo_description, p.issuer_type AS issuer_type, p.type AS coupon_type, p.value AS coupon_value, p.min_order_amount AS threshold_amount, p.max_discount_amount AS max_discount_amount, p.expires_at AS coupon_expires_at").
		Joins("JOIN promo_codes p ON p.id = m.promo_code_id").
		Where("m.status = ? AND m.stock_remaining > ? AND p.is_enabled = ? AND (p.expires_at IS NULL OR p.expires_at > ?)", "active", 0, true, time.Now().UTC())
	if scope := query["scope"]; scope != "" {
		if scope == "agent_subordinates" {
			if user.BoundAgentID == nil {
				return map[string]any{"items": []map[string]any{}, "total": 0}, nil
			}
			db = db.Where("m.scope = ? AND m.issuer_user_id = ?", scope, *user.BoundAgentID)
		} else {
			db = db.Where("m.scope = ?", scope)
		}
	} else if user.BoundAgentID != nil {
		db = db.Where("(m.scope = ? OR (m.scope = ? AND m.issuer_user_id = ?))", "public", "agent_subordinates", *user.BoundAgentID)
	} else {
		db = db.Where("m.scope = ?", "public")
	}
	if q := strings.TrimSpace(query["q"]); q != "" {
		like := "%" + q + "%"
		db = db.Where("m.title LIKE ? OR m.subtitle LIKE ? OR m.category LIKE ? OR p.description LIKE ?", like, like, like, like)
	}
	if couponType := strings.TrimSpace(query["type"]); couponType != "" {
		db = db.Where("p.type = ?", couponType)
	}
	if minPoints, ok := parseOptionalNonNegativeInt(query["min_points"]); ok {
		db = db.Where("m.points_price >= ?", minPoints)
	}
	if maxPoints, ok := parseOptionalNonNegativeInt(query["max_points"]); ok {
		db = db.Where("m.points_price <= ?", maxPoints)
	}
	if isHot, ok := parseOptionalBool(query["is_hot"]); ok {
		db = db.Where("m.is_hot = ?", isHot)
	}
	var total int64
	if err := db.Count(&total).Error; err != nil {
		return nil, err
	}
	orderBy := "m.sort_order DESC, m.id DESC"
	switch query["sort_by"] {
	case "points":
		orderBy = "m.points_price ASC, m.id DESC"
	case "expires":
		orderBy = "p.expires_at ASC, m.id DESC"
	}
	rows, err := rawRows(db.Order(orderBy).Limit(limit).Offset(offset))
	if err != nil {
		return nil, err
	}
	for _, row := range rows {
		row["claimed"] = s.mallItemClaimed(ctx, userID, int64Value(row["id"]))
	}
	return map[string]any{"items": rows, "total": total}, nil
}

func (s *Service) ClaimMallCoupon(ctx context.Context, userID int64, mallItemID int64) (map[string]any, error) {
	var out map[string]any
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		txService := s.withDB(tx)
		var item CouponMallItem
		if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).Where("id = ? AND status = ?", mallItemID, "active").First(&item).Error; err != nil {
			if err == gorm.ErrRecordNotFound {
				return NotFound("mall item not found")
			}
			return err
		}
		if item.StockRemaining <= 0 {
			return BadRequest("coupon is out of stock")
		}
		var coupon PromoCode
		if err := tx.Where("id = ?", item.PromoCodeID).First(&coupon).Error; err != nil {
			return err
		}
		if !coupon.IsEnabled || (coupon.ExpiresAt != nil && coupon.ExpiresAt.Before(time.Now().UTC())) {
			return BadRequest("coupon is unavailable")
		}
		var user UserAccount
		if err := tx.Where("id = ?", userID).First(&user).Error; err != nil {
			return err
		}
		if item.Scope == "agent_subordinates" && (user.BoundAgentID == nil || *user.BoundAgentID != item.IssuerUserID) {
			return HTTPError{Status: http.StatusForbidden, Detail: "coupon is only available for agent subordinates"}
		}
		var existing int64
		if err := tx.Model(&CouponMallClaim{}).Where("mall_item_id = ? AND user_id = ? AND status = ?", item.ID, userID, "claimed").Count(&existing).Error; err != nil {
			return err
		}
		if existing > 0 {
			return BadRequest("coupon already claimed")
		}
		if item.MaxClaimsPerUser > 0 && int(existing) >= item.MaxClaimsPerUser {
			return BadRequest("claim limit reached")
		}
		if item.MaxClaimsPerUserPerDay > 0 {
			start := time.Now().UTC().Truncate(24 * time.Hour)
			var todayCount int64
			if err := tx.Model(&CouponMallClaim{}).Where("mall_item_id = ? AND user_id = ? AND status = ? AND created_at >= ?", item.ID, userID, "claimed", start).Count(&todayCount).Error; err != nil {
				return err
			}
			if int(todayCount) >= item.MaxClaimsPerUserPerDay {
				return BadRequest("daily claim limit reached")
			}
		}
		if item.RequiredMemberLevel > 0 {
			membership, err := txService.getOrCreateMembership(ctx, userID)
			if err != nil {
				return err
			}
			if txService.membershipLevel(ctx, membership) < item.RequiredMemberLevel {
				return HTTPError{Status: http.StatusForbidden, Detail: "member level is not enough"}
			}
		}
		if item.PointsPrice > 0 {
			if _, err := txService.addPoints(ctx, userID, -item.PointsPrice, "points_use", 0, fmt.Sprintf("mall:claim:%d:%d", item.ID, userID), fmt.Sprintf("claim coupon %s", coupon.Code)); err != nil {
				return err
			}
		}
		if err := tx.Model(&CouponMallItem{}).Where("id = ?", item.ID).Update("stock_remaining", gorm.Expr("stock_remaining - 1")).Error; err != nil {
			return err
		}
		claim := CouponMallClaim{MallItemID: item.ID, PromoCodeID: coupon.ID, UserID: userID, PointsPaid: item.PointsPrice, Code: &coupon.Code, Status: "claimed"}
		if err := tx.Create(&claim).Error; err != nil {
			return err
		}
		membership, _ := txService.getOrCreateMembership(ctx, userID)
		out = map[string]any{"claim_id": claim.ID, "code": coupon.Code, "points_paid": item.PointsPrice, "available_points": membership.AvailablePoints}
		return nil
	})
	return out, err
}

func (s *Service) ListMyMallClaims(ctx context.Context, userID int64, status string) (map[string]any, error) {
	db := s.db.WithContext(ctx).Table("coupon_mall_claims AS c").
		Select("c.*, m.title AS title, m.subtitle AS subtitle, m.cover_image AS cover_image, m.category AS category, m.points_price AS points_price, p.type AS coupon_type, p.value AS coupon_value, p.min_order_amount AS threshold_amount, p.max_discount_amount AS max_discount_amount, p.expires_at AS coupon_expires_at").
		Joins("JOIN promo_codes p ON p.id = c.promo_code_id").
		Joins("JOIN coupon_mall_items m ON m.id = c.mall_item_id").
		Where("c.user_id = ?", userID)
	if strings.TrimSpace(status) != "" {
		db = db.Where("c.status = ?", status)
	}
	rows, err := rawRows(db.Order("c.id DESC"))
	if err != nil {
		return nil, err
	}
	return map[string]any{"items": rows}, nil
}

func (s *Service) DisablePromotionCoupon(ctx context.Context, userID int64, couponID int64) (map[string]any, error) {
	var out map[string]any
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var coupon PromoCode
		if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).Where("id = ? AND issuer_user_id = ?", couponID, userID).First(&coupon).Error; err != nil {
			if err == gorm.ErrRecordNotFound {
				return NotFound("coupon not found")
			}
			return err
		}
		refunded := 0
		if valueOrDefault(coupon.IssuerType, "") == "member" {
			points, err := s.refundUnusedMemberCoupon(ctx, &coupon, "disabled")
			if err != nil {
				return err
			}
			refunded = points
		} else if err := tx.Model(&PromoCode{}).Where("id = ?", coupon.ID).Update("is_enabled", false).Error; err != nil {
			return err
		}
		_ = tx.Model(&CouponMallItem{}).Where("promo_code_id = ? AND status = ?", coupon.ID, "active").Update("status", "paused").Error
		coupon.IsEnabled = false
		out = map[string]any{"coupon": promoCodeMap(coupon), "refunded_points": refunded}
		return nil
	})
	if err != nil {
		return nil, err
	}
	if membership, err := s.getOrCreateMembership(ctx, userID); err == nil {
		out["available_points"] = membership.AvailablePoints
	}
	return out, nil
}

func (s *Service) PromotionRewards(ctx context.Context, userID int64, limit int, offset int) ([]map[string]any, error) {
	var settlements []AgentSettlement
	if err := s.db.WithContext(ctx).Where("user_id = ? AND settlement_type = ?", userID, "member_commission").Order("created_at DESC").Limit(limit).Offset(offset).Find(&settlements).Error; err != nil {
		return nil, err
	}
	out := make([]map[string]any, 0, len(settlements))
	for _, item := range settlements {
		out = append(out, settlementRewardMap(item))
	}
	return out, nil
}

func (s *Service) AgentOverview(ctx context.Context, userID int64) (map[string]any, error) {
	user, err := s.userAccount(ctx, userID)
	if err != nil {
		return nil, err
	}
	code, _ := s.ensureInviteCode(ctx, &user)
	var agent UserAgent
	agentMap := any(nil)
	customerCount := int64(0)
	pendingProfit := int64(0)
	if err := s.db.WithContext(ctx).Where("user_id = ?", userID).First(&agent).Error; err == nil {
		data := mapFromJSON(agent)
		data["agent_tier"] = nil
		if agent.AgentTierID != nil {
			var tier AgentTier
			if err := s.db.WithContext(ctx).Where("id = ?", *agent.AgentTierID).First(&tier).Error; err == nil {
				data["agent_tier"] = mapFromJSON(tier)
			}
		}
		agentMap = data
		_ = s.db.WithContext(ctx).Model(&AgentCustomer{}).Where("agent_user_id = ?", userID).Count(&customerCount).Error
		_ = s.db.WithContext(ctx).Model(&AgentSettlement{}).Where("user_id = ? AND settlement_type = ? AND status IN ?", userID, "agent_profit", []string{"pending", "frozen"}).Select("COALESCE(SUM(delta_amount),0)").Scan(&pendingProfit).Error
	}
	membership, _ := s.getOrCreateMembership(ctx, userID)
	return map[string]any{"agent": agentMap, "customer_count": customerCount, "pending_profit": pendingProfit, "total_sales": agent.TotalSales, "total_profit": agent.TotalProfit, "invite_code": code, "invite_code_edit_enabled": s.settingBool(ctx, "promotion.invite_code_edit_enabled", true), "invite_code_change_count": user.InviteCodeChangeCount, "invite_code_change_limit": inviteCodeChangeLimit(user), "invite_code_change_remaining": maxInt(0, inviteCodeChangeLimit(user)-user.InviteCodeChangeCount), "member_commission_rate": s.membershipCommissionRate(ctx, membership), "available_points": membership.AvailablePoints, "total_points": membership.TotalPoints}, nil
}

func (s *Service) ListAgentTiers(ctx context.Context) ([]map[string]any, error) {
	var tiers []AgentTier
	if err := s.db.WithContext(ctx).Where("enabled = ?", true).Order("sort_order ASC, id ASC").Find(&tiers).Error; err != nil {
		return nil, err
	}
	return mapFromList(tiers), nil
}

func (s *Service) ApplyAgent(ctx context.Context, userID int64, req AgentApplyRequest) (map[string]any, error) {
	tierCode := strings.TrimSpace(req.Tier)
	var tier AgentTier
	if err := s.db.WithContext(ctx).Where("code = ? AND enabled = ?", tierCode, true).First(&tier).Error; err != nil {
		return nil, BadRequest("agent tier does not exist")
	}
	membership, err := s.getOrCreateMembership(ctx, userID)
	if err != nil {
		return nil, err
	}
	if level := s.membershipLevel(ctx, membership); level < tier.MinMemberLevel {
		return nil, HTTPError{Status: http.StatusForbidden, Detail: "member level is not enough"}
	}
	var agent UserAgent
	err = s.db.WithContext(ctx).Where("user_id = ?", userID).First(&agent).Error
	if err == gorm.ErrRecordNotFound {
		agent = UserAgent{UserID: userID}
	}
	agent.Status = "pending"
	agent.ApplyTier = &tierCode
	agent.ApplyReason = req.Reason
	agent.ApplyChannels = req.Channels
	if agent.ID > 0 {
		if err := s.db.WithContext(ctx).Model(&UserAgent{}).Where("id = ?", agent.ID).Updates(agent).Error; err != nil {
			return nil, err
		}
	} else if err := s.db.WithContext(ctx).Create(&agent).Error; err != nil {
		return nil, err
	}
	data := mapFromJSON(agent)
	data["agent_tier"] = nil
	return data, nil
}

func (s *Service) ListAgentCustomers(ctx context.Context, userID int64, limit int, offset int) (map[string]any, error) {
	if err := s.requireActiveAgent(ctx, userID); err != nil {
		return nil, err
	}
	db := s.db.WithContext(ctx).Table("agent_customers AS ac").
		Select("ac.*, u.id AS customer_id, u.username AS customer_username, u.nickname AS customer_nickname, u.email AS customer_email, u.phone AS customer_phone, u.balance AS customer_balance, u.created_at AS customer_created_at").
		Joins("JOIN users u ON u.id = ac.customer_user_id").
		Where("ac.agent_user_id = ?", userID)
	var total int64
	if err := db.Count(&total).Error; err != nil {
		return nil, err
	}
	rows, err := rawRows(db.Order("ac.id DESC").Limit(limit).Offset(offset))
	if err != nil {
		return nil, err
	}
	for _, row := range rows {
		row["customer"] = map[string]any{
			"id":         row["customer_id"],
			"username":   row["customer_username"],
			"nickname":   row["customer_nickname"],
			"email":      row["customer_email"],
			"phone":      row["customer_phone"],
			"balance":    row["customer_balance"],
			"created_at": row["customer_created_at"],
		}
		delete(row, "customer_id")
		delete(row, "customer_username")
		delete(row, "customer_nickname")
		delete(row, "customer_email")
		delete(row, "customer_phone")
		delete(row, "customer_balance")
		delete(row, "customer_created_at")
	}
	return map[string]any{"items": rows, "total": total}, nil
}

func (s *Service) ListAgentSettlements(ctx context.Context, userID int64, limit int, offset int) (map[string]any, error) {
	var total int64
	db := s.db.WithContext(ctx).Model(&AgentSettlement{}).Where("user_id = ?", userID)
	if err := db.Count(&total).Error; err != nil {
		return nil, err
	}
	var items []AgentSettlement
	if err := db.Order("id DESC").Limit(limit).Offset(offset).Find(&items).Error; err != nil {
		return nil, err
	}
	return map[string]any{"items": mapFromList(items), "total": total}, nil
}

func (s *Service) AffiliateInfo(ctx context.Context, code string) (map[string]any, error) {
	user, err := s.resolveAgentFromCode(ctx, code)
	if err != nil {
		return nil, err
	}
	if user == nil {
		return nil, nil
	}
	var agent UserAgent
	tierName := ""
	if err := s.db.WithContext(ctx).Where("user_id = ?", user.ID).First(&agent).Error; err == nil && agent.AgentTierID != nil {
		var tier AgentTier
		if err := s.db.WithContext(ctx).Where("id = ?", *agent.AgentTierID).First(&tier).Error; err == nil {
			tierName = tier.Name
		}
	}
	return map[string]any{"aff_code": code, "agent_name": firstString(user.Nickname, user.Username), "agent_tier": tierName}, nil
}

func (s *Service) RecordAffiliateClick(ctx context.Context, req AffiliateClickRequest, request *http.Request) (map[string]any, error) {
	code := strings.TrimSpace(req.AffCode)
	var agentID *int64
	if user, _ := s.resolveAgentFromCode(ctx, code); user != nil {
		agentID = &user.ID
	}
	landing := req.LandingURL
	if landing == nil && request != nil {
		value := request.URL.String()
		landing = &value
	}
	var ip *string
	if request != nil {
		host, _, err := net.SplitHostPort(request.RemoteAddr)
		if err != nil {
			host = request.RemoteAddr
		}
		ip = trimPtr(&host)
	}
	var ua, referer *string
	if request != nil {
		uav := request.Header.Get("user-agent")
		ref := request.Header.Get("referer")
		ua = trimPtr(&uav)
		referer = trimPtr(&ref)
	}
	click := AffiliateClick{AffCode: code, AgentID: agentID, LandingURL: landing, IP: ip, UserAgent: ua, Referer: referer, UTMSource: req.UTMSource, UTMMedium: req.UTMMedium, UTMCampaign: req.UTMCampaign, UTMContent: req.UTMContent, UTMTerm: req.UTMTerm}
	if err := s.db.WithContext(ctx).Create(&click).Error; err != nil {
		return nil, err
	}
	return map[string]any{"ok": true}, nil
}

func (s *Service) currentQuarterSelfSpent(ctx context.Context, userID int64) int64 {
	now := time.Now().UTC()
	month := int(now.Month())
	quarterMonth := ((month-1)/3)*3 + 1
	start := time.Date(now.Year(), time.Month(quarterMonth), 1, 0, 0, 0, 0, time.UTC)
	var total int64
	_ = s.db.WithContext(ctx).Model(&UserSpendEvent{}).Where("user_id = ? AND created_at >= ?", userID, start).Select("COALESCE(SUM(amount_cents),0)").Scan(&total).Error
	return total
}

func (s *Service) rewardSummary(ctx context.Context, userID int64, source string) map[string]any {
	var count int64
	var points int64
	_ = s.db.WithContext(ctx).Model(&PointReward{}).Where("user_id = ? AND status IN ?", userID, []string{"pending", "claimable"}).Count(&count).Error
	_ = s.db.WithContext(ctx).Model(&PointReward{}).Where("user_id = ? AND status IN ?", userID, []string{"pending", "claimable"}).Select("COALESCE(SUM(points),0)").Scan(&points).Error
	return map[string]any{"count": count, "points": points}
}

func (s *Service) settlementPointSummary(ctx context.Context, userID int64, settlementType string) map[string]any {
	var count int64
	var points int64
	_ = s.db.WithContext(ctx).Model(&AgentSettlement{}).Where("user_id = ? AND settlement_type = ? AND status IN ?", userID, settlementType, []string{"pending", "frozen"}).Count(&count).Error
	_ = s.db.WithContext(ctx).Model(&AgentSettlement{}).Where("user_id = ? AND settlement_type = ? AND status IN ?", userID, settlementType, []string{"pending", "frozen"}).Select("COALESCE(SUM(points_amount),0)").Scan(&points).Error
	return map[string]any{"count": count, "points": points}
}

func (s *Service) ensureInviteCode(ctx context.Context, user *UserAccount) (string, error) {
	if user.InviteCode != nil && strings.TrimSpace(*user.InviteCode) != "" {
		return strings.TrimSpace(*user.InviteCode), nil
	}
	code := strings.ToUpper(randomToken(6))
	for i := 0; i < 10; i++ {
		var count int64
		_ = s.db.WithContext(ctx).Model(&UserAccount{}).Where("invite_code = ?", code).Count(&count).Error
		if count == 0 {
			user.InviteCode = &code
			return code, s.db.WithContext(ctx).Model(&UserAccount{}).Where("id = ?", user.ID).Update("invite_code", code).Error
		}
		code = strings.ToUpper(randomToken(6))
	}
	return "", fmt.Errorf("generate invite code failed")
}

func (s *Service) generateCouponCode(ctx context.Context, prefix string) string {
	for i := 0; i < 20; i++ {
		code := prefix + strings.ToUpper(randomToken(8))
		var count int64
		_ = s.db.WithContext(ctx).Model(&PromoCode{}).Where("code = ?", code).Count(&count).Error
		if count == 0 {
			return code
		}
	}
	return prefix + strings.ToUpper(randomToken(10))
}

func (s *Service) membershipCommissionRate(ctx context.Context, membership UserMembership) int {
	if membership.MemberLevelID == nil {
		return 0
	}
	var level MemberLevel
	if err := s.db.WithContext(ctx).Where("id = ?", *membership.MemberLevelID).First(&level).Error; err != nil {
		return 0
	}
	return level.CommissionRate
}

func (s *Service) membershipLevel(ctx context.Context, membership UserMembership) int {
	if membership.MemberLevelID == nil {
		return 0
	}
	var level MemberLevel
	if err := s.db.WithContext(ctx).Where("id = ?", *membership.MemberLevelID).First(&level).Error; err != nil {
		return 0
	}
	return level.Level
}

func (s *Service) requireActiveAgent(ctx context.Context, userID int64) error {
	var count int64
	if err := s.db.WithContext(ctx).Model(&UserAgent{}).Where("user_id = ? AND status IN ?", userID, []string{"active", "observing"}).Count(&count).Error; err != nil {
		return err
	}
	if count == 0 {
		return HTTPError{Status: http.StatusForbidden, Detail: "agent is not active"}
	}
	return nil
}

func (s *Service) resolveAgentFromCode(ctx context.Context, code string) (*UserAccount, error) {
	code = strings.TrimSpace(code)
	var user UserAccount
	if err := s.db.WithContext(ctx).Where("invite_code = ?", code).First(&user).Error; err == nil {
		return &user, nil
	} else if err != gorm.ErrRecordNotFound {
		return nil, err
	}
	var agent UserAgent
	if err := s.db.WithContext(ctx).Where("invite_code = ?", code).First(&agent).Error; err != nil {
		if err == gorm.ErrRecordNotFound {
			return nil, nil
		}
		return nil, err
	}
	if err := s.db.WithContext(ctx).Where("id = ?", agent.UserID).First(&user).Error; err != nil {
		return nil, err
	}
	return &user, nil
}

func (s *Service) mallItemClaimed(ctx context.Context, userID int64, itemID int64) bool {
	var count int64
	_ = s.db.WithContext(ctx).Model(&CouponMallClaim{}).Where("user_id = ? AND mall_item_id = ? AND status = ?", userID, itemID, "claimed").Count(&count).Error
	return count > 0
}

func (s *Service) settingInt(ctx context.Context, key string, fallback int) int {
	var item SystemSetting
	if err := s.db.WithContext(ctx).Where("`key` = ?", key).First(&item).Error; err != nil {
		return fallback
	}
	return settingInt(item.Value, fallback)
}

func (s *Service) settingBool(ctx context.Context, key string, fallback bool) bool {
	var item SystemSetting
	if err := s.db.WithContext(ctx).Where("`key` = ?", key).First(&item).Error; err != nil {
		return fallback
	}
	return settingBool(item.Value, fallback)
}

func (s *Service) userOpsSettingString(ctx context.Context, key string, fallback string) string {
	var item SystemSetting
	if err := s.db.WithContext(ctx).Where("`key` = ?", key).First(&item).Error; err != nil {
		return fallback
	}
	return settingStringCommerce(item.Value, fallback)
}

func promotionLink(baseURL string, code string) string {
	return strings.TrimRight(baseURL, "/") + "/register?ref=" + code
}

func inviteCodeChangeLimit(user UserAccount) int {
	if user.Role == "admin" || user.Role == "super_admin" {
		return 99
	}
	return 3 + maxInt(0, user.InviteCodeChangeExtra)
}

func validatePromotionCouponRequest(req PromotionCouponCreateRequest, isAgent bool) error {
	if req.ClaimMethod == "mall" || req.ListOnMall {
		if req.PointsPrice == nil || *req.PointsPrice <= 0 {
			return BadRequest("上架积分商城时必须填写领取所需积分")
		}
		if req.StockTotal == nil || *req.StockTotal <= 0 {
			return BadRequest("上架积分商城时必须填写库存数量")
		}
	}
	switch req.Type {
	case "percent":
		if !isAgent {
			return BadRequest("当前账号还不是代理，不能创建折扣券；普通会员只能创建立减券或满减券")
		}
		if req.Value <= 0 || req.Value > 100 {
			return BadRequest("折扣数值必须在 1 到 100 之间，例如 30 表示 3 折")
		}
	case "threshold_fixed":
		if req.ThresholdAmount == nil || *req.ThresholdAmount <= 0 {
			return BadRequest("满减券必须填写满减门槛")
		}
		if req.Value <= 0 {
			return BadRequest("优惠金额必须大于 0")
		}
	case "fixed":
		if req.Value <= 0 {
			return BadRequest("优惠金额必须大于 0")
		}
	default:
		return BadRequest("不支持的优惠券类型")
	}
	return nil
}

func (s *Service) ensureAgentCouponQuota(ctx context.Context, agent UserAgent, tier AgentTier) error {
	if tier.MaxMonthlyCouponCount <= 0 {
		return nil
	}
	now := time.Now().UTC()
	monthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
	var count int64
	if err := s.db.WithContext(ctx).Model(&PromoCode{}).Where("issuer_user_id = ? AND issuer_type = ? AND created_at >= ?", agent.UserID, "agent", monthStart).Count(&count).Error; err != nil {
		return err
	}
	if int(count) >= tier.MaxMonthlyCouponCount {
		return BadRequest(fmt.Sprintf("本月代理发券数量已达上限：%d 张", tier.MaxMonthlyCouponCount))
	}
	return nil
}

func (s *Service) refundExpiredMemberCoupons(ctx context.Context, userID *int64, limit int) error {
	db := s.db.WithContext(ctx).Where("issuer_type = ? AND is_enabled = ? AND expires_at IS NOT NULL AND expires_at <= ?", "member", true, time.Now().UTC()).Order("expires_at ASC, id ASC").Limit(limit)
	if userID != nil {
		db = db.Where("issuer_user_id = ?", *userID)
	}
	var coupons []PromoCode
	if err := db.Find(&coupons).Error; err != nil {
		return err
	}
	for i := range coupons {
		if _, err := s.refundUnusedMemberCoupon(ctx, &coupons[i], "expired"); err != nil {
			return err
		}
	}
	return nil
}

func (s *Service) refundUnusedMemberCoupon(ctx context.Context, coupon *PromoCode, reason string) (int, error) {
	if coupon == nil || valueOrDefault(coupon.IssuerType, "") != "member" {
		return 0, nil
	}
	refundPoints := couponRefundablePoints(*coupon)
	updates := map[string]any{"is_enabled": false}
	if refundPoints > 0 {
		updates["point_refunded"] = coupon.PointRefunded + refundPoints
	}
	if err := s.db.WithContext(ctx).Model(&PromoCode{}).Where("id = ?", coupon.ID).Updates(updates).Error; err != nil {
		return 0, err
	}
	coupon.IsEnabled = false
	if refundPoints <= 0 || coupon.IssuerUserID == nil {
		return 0, nil
	}
	if _, err := s.addPoints(ctx, *coupon.IssuerUserID, refundPoints, "activity_gift", 0, coupon.Code+":refund", fmt.Sprintf("refund unused member coupon %s: %s", coupon.Code, reason)); err != nil {
		return 0, err
	}
	coupon.PointRefunded += refundPoints
	return refundPoints, nil
}

func couponRefundablePoints(coupon PromoCode) int {
	if valueOrDefault(coupon.IssuerType, "") != "member" || coupon.PointCost <= 0 {
		return 0
	}
	usedCost := couponMaxDiscountPerUse(coupon) * maxInt(0, coupon.UsedCount) * pointsToCentsRate / 100
	return maxInt(0, coupon.PointCost-usedCost-coupon.PointRefunded)
}

func couponMaxDiscountPerUse(coupon PromoCode) int {
	if coupon.Type == "percent" {
		return coupon.MaxDiscountAmount
	}
	return coupon.Value
}

func alphaNum(value string) bool {
	for _, r := range value {
		if (r >= 'A' && r <= 'Z') || (r >= 'a' && r <= 'z') || (r >= '0' && r <= '9') {
			continue
		}
		return false
	}
	return value != ""
}

func parseOptionalNonNegativeInt(value string) (int, bool) {
	value = strings.TrimSpace(value)
	if value == "" {
		return 0, false
	}
	n, err := strconv.Atoi(value)
	if err != nil || n < 0 {
		return 0, false
	}
	return n, true
}

func parseOptionalBool(value string) (bool, bool) {
	switch strings.ToLower(strings.TrimSpace(value)) {
	case "1", "true", "yes", "on":
		return true, true
	case "0", "false", "no", "off":
		return false, true
	default:
		return false, false
	}
}

func pointTaskLocalDate(now time.Time) string {
	return now.In(pointTaskLocation()).Format("2006-01-02")
}

func pointTaskLocation() *time.Location {
	loc, err := time.LoadLocation("Asia/Shanghai")
	if err != nil {
		return time.FixedZone("Asia/Shanghai", 8*3600)
	}
	return loc
}

func isDuplicateKeyError(err error) bool {
	var mysqlErr *mysql.MySQLError
	return errors.As(err, &mysqlErr) && mysqlErr.Number == 1062
}

func randomToken(n int) string {
	raw := make([]byte, n)
	if _, err := rand.Read(raw); err != nil {
		return fmt.Sprintf("%d", time.Now().UnixNano())
	}
	return hex.EncodeToString(raw)[:n]
}

func promoCodeMap(coupon PromoCode) map[string]any {
	return map[string]any{
		"id":                  coupon.ID,
		"code":                coupon.Code,
		"description":         coupon.Description,
		"issuer_type":         coupon.IssuerType,
		"issuer_user_id":      coupon.IssuerUserID,
		"point_cost":          coupon.PointCost,
		"point_refunded":      coupon.PointRefunded,
		"refundable_points":   couponRefundablePoints(coupon),
		"type":                coupon.Type,
		"value":               coupon.Value,
		"threshold_amount":    coupon.MinOrderAmount,
		"max_discount_amount": coupon.MaxDiscountAmount,
		"discount_level":      coupon.DiscountLevel,
		"scope":               coupon.Scope,
		"claim_method":        coupon.ClaimMethod,
		"list_on_mall":        coupon.ListOnMall,
		"starts_at":           coupon.StartsAt,
		"expires_at":          coupon.ExpiresAt,
		"max_uses":            coupon.MaxUses,
		"max_uses_per_user":   coupon.MaxUsesPerUser,
		"used_count":          coupon.UsedCount,
		"new_users_only":      coupon.NewUsersOnly,
		"applies_to_renewals": coupon.AppliesToRenewals,
		"is_stackable":        coupon.IsStackable,
		"is_enabled":          coupon.IsEnabled,
		"created_at":          coupon.CreatedAt,
	}
}

func settlementRewardMap(item AgentSettlement) map[string]any {
	return map[string]any{"id": fmt.Sprintf("settlement-%d", item.ID), "source": "settlement", "order_id": item.OrderID, "ref_no": item.OrderNo, "remark": item.Remark, "points": item.PointsAmount, "status": item.Status, "frozen_until": item.FrozenUntil, "settled_at": item.SettledAt, "reversed_at": item.ReversedAt, "created_at": item.CreatedAt}
}

func mapFromList[T any](items []T) []map[string]any {
	out := make([]map[string]any, 0, len(items))
	for _, item := range items {
		out = append(out, mapFromJSON(item))
	}
	return out
}

func intPtrValue(value *int) int {
	if value == nil {
		return 0
	}
	return *value
}

func int64Value(value any) int64 {
	if parsed, ok := int64FromAny(value); ok {
		return parsed
	}
	return 0
}

func firstStringPtr(values ...*string) *string {
	for _, value := range values {
		if value != nil && strings.TrimSpace(*value) != "" {
			text := strings.TrimSpace(*value)
			return &text
		}
	}
	return nil
}
