bindbox-game/internal/service/finance/query_user.go

package finance

import (
	"context"
	"fmt"

	"bindbox-game/internal/repository/mysql/model"
)

// queryUser implements QueryUserProfitLoss using fan-out + in-memory merge pattern.
// Two independent Scan() calls gather revenue and inventory cost;
// results are merged in Go via map[int64]*ProfitLossDetail.
//
// Revenue = actual_amount only (real cash). Coupons/points are FREE marketing subsidies.
// Cost = inventory value × item card multiplier only.
func (s *service) queryUser(ctx context.Context, params UserProfitLossParams) (*ProfitLossResult, error) {
	// Step 1: Revenue scan — per-order rows classified in Go
	type userRevenueRow struct {
		UserID         int64
		SourceType     int32
		OrderNo        string
		ActualAmount   int64
		DiscountAmount int64
		PointsAmount   int64
		Remark         string
		DrawCount      int64
		ActivityPrice  int64
	}
	var revenueRows []userRevenueRow
	q := s.dbR.WithContext(ctx).
		Table(model.TableNameOrders).
		Select(`orders.user_id, orders.source_type, orders.order_no,
                orders.actual_amount, orders.discount_amount, orders.points_amount, orders.remark,
                COUNT(activity_draw_logs.id) as draw_count,
                COALESCE(MAX(activities.price_draw), 0) as activity_price`).
		Joins(`LEFT JOIN activity_draw_logs ON activity_draw_logs.order_id = orders.id`).
		Joins(`LEFT JOIN activity_issues ON activity_issues.id = activity_draw_logs.issue_id`).
		Joins(`LEFT JOIN activities ON activities.id = activity_issues.activity_id`).
		Where("orders.status = ?", 2).
		Group("orders.id, orders.user_id, orders.source_type, orders.order_no, orders.actual_amount, orders.discount_amount, orders.points_amount, orders.remark")
	if len(params.UserIDs) > 0 {
		q = q.Where("orders.user_id IN ?", params.UserIDs)
	}
	if params.StartTime != nil {
		q = q.Where("orders.created_at >= ?", *params.StartTime)
	}
	if params.EndTime != nil {
		q = q.Where("orders.created_at <= ?", *params.EndTime)
	}
	if err := q.Scan(&revenueRows).Error; err != nil {
		return nil, fmt.Errorf("QueryUserProfitLoss revenue scan: %w", err)
	}

	resultMap := make(map[int64]*ProfitLossDetail)
	for _, r := range revenueRows {
		gpValue := ComputeGamePassValue(r.DrawCount, r.ActivityPrice)
		bd := ClassifyOrderSpending(r.SourceType, r.OrderNo, r.ActualAmount, r.DiscountAmount, r.Remark, gpValue)
		if _, ok := resultMap[r.UserID]; !ok {
			resultMap[r.UserID] = &ProfitLossDetail{UserID: r.UserID}
		}
		d := resultMap[r.UserID]
		d.Revenue += bd.Total

		// Populate display-only fields
		if bd.IsGamePass {
			d.GamePassValue += bd.GamePass
		} else {
			d.CouponDiscount += r.DiscountAmount
			d.PointsDiscount += r.PointsAmount
		}
	}

	// Step 2: Inventory cost scan — apply multiplier in Go (not SQL, for SQLite compat)
	type userInventoryRow struct {
		UserID          int64
		ValueCents      int64
		MultiplierX1000 int64
	}
	iq := s.dbR.WithContext(ctx).
		Table(model.TableNameUserInventory).
		Select(`user_inventory.user_id,
                COALESCE(NULLIF(user_inventory.value_cents, 0), activity_reward_settings.price_snapshot_cents, products.price, 0) as value_cents,
                COALESCE(system_item_cards.reward_multiplier_x1000, 1000) as multiplier_x1000`).
		Joins("LEFT JOIN activity_reward_settings ON activity_reward_settings.id = user_inventory.reward_id").
		Joins("LEFT JOIN products ON products.id = user_inventory.product_id").
		Joins("LEFT JOIN orders ON orders.id = user_inventory.order_id").
		Joins("LEFT JOIN user_item_cards ON user_item_cards.id = orders.item_card_id").
		Joins("LEFT JOIN system_item_cards ON system_item_cards.id = user_item_cards.card_id").
		Where("user_inventory.status IN ?", []int{1, 3}).
		Where("COALESCE(user_inventory.remark, '') NOT LIKE ?", "%void%").
		Where("NOT (user_inventory.status = 3 AND (COALESCE(user_inventory.remark, '') LIKE ? OR COALESCE(user_inventory.remark, '') LIKE ?))", "%redeemed_points%", "%batch_redeemed%").
		Where("(orders.status = ? OR user_inventory.order_id = 0 OR user_inventory.order_id IS NULL)", 2)
	if len(params.UserIDs) > 0 {
		iq = iq.Where("user_inventory.user_id IN ?", params.UserIDs)
	}
	if params.StartTime != nil {
		iq = iq.Where("user_inventory.created_at >= ?", *params.StartTime)
	}
	if params.EndTime != nil {
		iq = iq.Where("user_inventory.created_at <= ?", *params.EndTime)
	}
	var inventoryRows []userInventoryRow
	if err := iq.Scan(&inventoryRows).Error; err != nil {
		return nil, fmt.Errorf("QueryUserProfitLoss inventory cost scan: %w", err)
	}
	for _, r := range inventoryRows {
		cost := ComputePrizeCostWithMultiplier(r.ValueCents, r.MultiplierX1000)
		if _, ok := resultMap[r.UserID]; !ok {
			resultMap[r.UserID] = &ProfitLossDetail{UserID: r.UserID}
		}
		resultMap[r.UserID].Cost += cost
	}

	// Step 3: Apply ComputeProfit per detail and aggregate totals
	details := make([]ProfitLossDetail, 0, len(resultMap))
	var totalRevenue, totalCost int64
	for _, d := range resultMap {
		d.Profit, d.ProfitRate = ComputeProfit(d.Revenue, d.Cost)
		totalRevenue += d.Revenue
		totalCost += d.Cost
		details = append(details, *d)
	}
	totalProfit, profitRate := ComputeProfit(totalRevenue, totalCost)
	return &ProfitLossResult{
		TotalRevenue: totalRevenue,
		TotalCost:    totalCost,
		TotalProfit:  totalProfit,
		ProfitRate:   profitRate,
		Details:      details,
		Breakdown:    []interface{}{},
	}, nil
}