bindbox-game/.planning/phases/01-core-pnl-functions/01-02-PLAN.md

phase, plan, type, wave, depends_on, files_modified, autonomous, requirements, must_haves

phase	plan	type	wave	depends_on	files_modified	autonomous	requirements	must_haves
01-core-pnl-functions	02	execute	2	01-01	internal/service/finance/query_user.go internal/service/finance/service.go internal/service/finance/service_test.go	true	PNL-02 PNL-03 PNL-04 PNL-05 PNL-06 PNL-07 PNL-08 DIM-01 DIM-03 DIM-04 QUA-03 QUA-04 QUA-05	truths artifacts key_links QueryUserProfitLoss with a paid cash order returns Revenue = actual_amount + discount_amount QueryUserProfitLoss excludes refunded orders (status=3 or status=4) from Revenue QueryUserProfitLoss with a game-pass order returns Revenue = draw_count × activity_price (not actual_amount) QueryUserProfitLoss excludes voided inventory (remark LIKE '%void%' or status=2) from Cost QueryUserProfitLoss includes legacy inventory with order_id=0 in Cost (not filtered out) QueryUserProfitLoss with empty UserIDs returns aggregated result for all users (not empty) QueryUserProfitLoss returns error (not nil) when a Scan() call fails ProfitLossResult.TotalProfit = TotalRevenue - TotalCost; ProfitRate computed by ComputeProfit() path provides exports internal/service/finance/query_user.go QueryUserProfitLoss implementation with fan-out scans path provides internal/service/finance/service.go Updated QueryUserProfitLoss method body (replaces stub from Plan 01) path provides internal/service/finance/service_test.go Integration tests: refund exclusion, game-pass revenue, void exclusion, legacy order_id=0 from to via pattern internal/service/finance/query_user.go internal/repository/mysql/model (Orders, UserInventory, UserPointsLedger, UserCouponLedger) s.dbR.Table(model.TableNameOrders).Select(...).Scan() Scan(& from to via pattern internal/service/finance/query_user.go finance.ClassifyOrderSpending / IsGamePassOrder / ComputeProfit Go-layer classification of per-order rows after scan ClassifyOrderSpending|ComputeProfit from to via pattern internal/service/finance/query_user.go system_configs table getPointsExchangeRate() reads 'points.exchange_rate' key points.exchange_rate

Implement QueryUserProfitLoss in a new query_user.go file using the fan-out + in-memory merge pattern. Four independent Scan() calls gather revenue, inventory cost, points cost, and coupon cost; results are merged in Go via map[int64]*ProfitLossDetail. The service.go stub from Plan 01 is replaced with a real dispatch call.

Purpose: Deliver the user-dimension P&L function with all PNL-02 through PNL-08 requirements satisfied and all DIM-01/03/04 parameter handling in place.

Output: query_user.go (implementation), service.go (updated), service_test.go (extended with integration tests).

<execution_context> @/.claude/get-shit-done/workflows/execute-plan.md @/.claude/get-shit-done/templates/summary.md </execution_context>

@.planning/phases/01-core-pnl-functions/1-CONTEXT.md @.planning/phases/01-core-pnl-functions/01-RESEARCH.md @.planning/phases/01-core-pnl-functions/01-01-SUMMARY.md

From internal/service/finance/types.go (created in Plan 01):

type AssetType int
const ( AssetTypeAll=0; AssetTypePoints=1; AssetTypeCoupon=2; AssetTypeItemCard=3; AssetTypeProduct=4; AssetTypeFragment=5 )

type UserProfitLossParams struct {
    UserIDs   []int64
    AssetType AssetType
    StartTime *time.Time
    EndTime   *time.Time
}

type ProfitLossDetail struct {
    UserID, ActivityID    int64
    Revenue, Cost, Profit int64
    ProfitRate            float64
}

type ProfitLossResult struct {
    TotalRevenue, TotalCost, TotalProfit int64
    ProfitRate                           float64
    Details                              []ProfitLossDetail
    Breakdown                            []interface{}
}

From internal/service/finance/service.go (created in Plan 01):

type service struct { logger logger.CustomLogger; dbR *gorm.DB }
// QueryUserProfitLoss stub — REPLACE with real dispatch: return s.queryUser(ctx, params)

From internal/service/finance/profit_metrics.go (existing — MUST reuse, do not reimplement):

func ClassifyOrderSpending(sourceType int32, orderNo string, actualAmount, discountAmount int64, remark string, gamePassValue int64) SpendingBreakdown
func IsGamePassOrder(sourceType int32, orderNo string, actualAmount int64, remark string) bool
func ComputeGamePassValue(drawCount, activityPrice int64) int64
func ComputePrizeCostWithMultiplier(baseCost, multiplierX1000 int64) int64
func ComputeProfit(spending, prizeCost int64) (int64, float64)

From internal/repository/mysql/model/ (table name constants):

model.TableNameOrders          = "orders"
model.TableNameUserInventory   = "user_inventory"
model.TableNameUserPointsLedger = "user_points_ledger"
model.TableNameUserCouponLedger = "user_coupon_ledger"

Orders table fields used: id, user_id, status, source_type, order_no, actual_amount, discount_amount, remark, draw_count, created_at UserInventory fields used: user_id, activity_id, order_id, value_cents, status, remark, reward_id (for item-card join) UserPointsLedger fields used: user_id, action, points, created_at UserCouponLedger fields used: user_id, change_amount, order_id, created_at

Task 1: Create query_user.go — QueryUserProfitLoss fan-out implementation - internal/service/finance/service.go (verify service struct and stub method signature) - internal/service/finance/types.go (verify UserProfitLossParams and ProfitLossResult fields) - internal/service/finance/profit_metrics.go (verify ClassifyOrderSpending, ComputeProfit signatures) - internal/api/admin/dashboard_activity.go (lines 225-300, fan-out pattern and exact WHERE conditions) - .planning/phases/01-core-pnl-functions/01-RESEARCH.md (Pitfall 1: CAST AS SIGNED; Pitfall 2: empty slice; Pitfall 3: game-pass double count; Pitfall 4: refund+inventory; Pitfall 5: scan error; Pitfall 6: SQLite compat) internal/service/finance/query_user.go - Revenue scan: SELECT user_id + raw order fields (source_type, order_no, actual_amount, discount_amount, remark) for orders WHERE status=2; classify per-row in Go using ClassifyOrderSpending(); sum by user_id - Game-pass needs draw_count and activity price; join orders → activity_draw_logs → activity_issues → activities to get activities.price_draw per order - Cost scan (inventory): SELECT user_id, SUM(value_cents) grouped by user_id; WHERE status IN (1,3) AND remark NOT LIKE '%void%' AND (orders.status=2 OR order_id=0 OR order_id IS NULL); LEFT JOIN orders ON orders.id = user_inventory.order_id; for multiplier: LEFT JOIN user_item_cards ON user_item_cards.id = orders.item_card_id LEFT JOIN system_item_cards ON system_item_cards.id = user_item_cards.card_id; apply ComputePrizeCostWithMultiplier(value_cents, multiplier) in Go (not SQL) for SQLite compat - Cost scan (points): SELECT user_id, SUM(points) WHERE action='order_deduct' AND points < 0; convert via getPointsExchangeRate() and points.PointsToCents() - Cost scan (coupons): SELECT user_id, SUM(ABS(change_amount)) WHERE change_amount < 0; JOIN orders ON orders.id = user_coupon_ledger.order_id WHERE orders.status=2 - Every Scan() must check .Error and return fmt.Errorf("QueryUserProfitLoss %s scan: %w", step, err) - Empty UserIDs: do NOT add WHERE user_id IN clause (all users) - Time filters: add WHERE created_at >= *StartTime only if StartTime != nil - Merge all scans in Go via map[int64]*ProfitLossDetail - Final aggregation: sum all Details into TotalRevenue/TotalCost; call ComputeProfit for TotalProfit+ProfitRate - Return &ProfitLossResult{..., Breakdown: []interface{}{}} with empty Breakdown slice Create `internal/service/finance/query_user.go` with package `finance`.

Imports:

import (
    "context"
    "fmt"

    "bindbox-game/internal/pkg/points"
    "bindbox-game/internal/repository/mysql/model"
    "gorm.io/gorm"
)

Private method (s *service) queryUser(ctx context.Context, params UserProfitLossParams) (*ProfitLossResult, error).

Step 1: Revenue scan — scan raw order fields per user, classify in Go.

Scan struct:

type userRevenueRow struct {
    UserID        int64
    SourceType    int32
    OrderNo       string
    ActualAmount  int64
    DiscountAmount int64
    Remark        string
    DrawCount     int64
    ActivityPrice int64 // from activities.price_draw via JOIN
}

Query (scan per-order, not pre-aggregated — needed for per-row classification):

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

Merge revenue into resultMap — classify per row using Go functions:

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}
    }
    resultMap[r.UserID].Revenue += bd.Total
}

Step 2: Inventory cost scan — scan raw value_cents + multiplier per inventory row, apply ComputePrizeCostWithMultiplier in Go.

Scan struct:

type userInventoryRow struct {
    UserID          int64
    ValueCents      int64
    MultiplierX1000 int64
}

Query:

var inventoryRows []userInventoryRow
iq := s.dbR.WithContext(ctx).
    Table(model.TableNameUserInventory).
    Select(`user_inventory.user_id,
            user_inventory.value_cents,
            COALESCE(system_item_cards.reward_multiplier_x1000, 1000) as multiplier_x1000`).
    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("(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: Points cost scan — read points deductions and convert to cents.

type userPointsRow struct {
    UserID      int64
    TotalPoints int64 // SUM of negative points = total deducted (positive value after ABS)
}
var pointsRows []userPointsRow
pq := s.dbR.WithContext(ctx).
    Table(model.TableNameUserPointsLedger).
    Select("user_id, SUM(-points) as total_points"). // points is negative for deductions
    Where("action = ?", "order_deduct").
    Where("points < ?", 0)
if len(params.UserIDs) > 0 {
    pq = pq.Where("user_id IN ?", params.UserIDs)
}
if params.StartTime != nil {
    pq = pq.Where("created_at >= ?", *params.StartTime)
}
if params.EndTime != nil {
    pq = pq.Where("created_at <= ?", *params.EndTime)
}
pq = pq.Group("user_id")
if err := pq.Scan(&pointsRows).Error; err != nil {
    return nil, fmt.Errorf("QueryUserProfitLoss points cost scan: %w", err)
}
rate := s.getPointsExchangeRate(ctx)
for _, r := range pointsRows {
    costCents := points.PointsToCents(r.TotalPoints, float64(rate))
    if _, ok := resultMap[r.UserID]; !ok {
        resultMap[r.UserID] = &ProfitLossDetail{UserID: r.UserID}
    }
    resultMap[r.UserID].Cost += costCents
}

Step 4: Coupon cost scan — sum coupon deductions from paid orders.

type userCouponRow struct {
    UserID    int64
    TotalCost int64 // SUM(ABS(change_amount)) for deductions
}
var couponRows []userCouponRow
cq := s.dbR.WithContext(ctx).
    Table(model.TableNameUserCouponLedger).
    Select("user_coupon_ledger.user_id, SUM(-user_coupon_ledger.change_amount) as total_cost").
    Joins("LEFT JOIN orders ON orders.id = user_coupon_ledger.order_id").
    Where("user_coupon_ledger.change_amount < ?", 0).
    Where("orders.status = ?", 2)
if len(params.UserIDs) > 0 {
    cq = cq.Where("user_coupon_ledger.user_id IN ?", params.UserIDs)
}
if params.StartTime != nil {
    cq = cq.Where("user_coupon_ledger.created_at >= ?", *params.StartTime)
}
if params.EndTime != nil {
    cq = cq.Where("user_coupon_ledger.created_at <= ?", *params.EndTime)
}
cq = cq.Group("user_coupon_ledger.user_id")
if err := cq.Scan(&couponRows).Error; err != nil {
    return nil, fmt.Errorf("QueryUserProfitLoss coupon cost scan: %w", err)
}
for _, r := range couponRows {
    if _, ok := resultMap[r.UserID]; !ok {
        resultMap[r.UserID] = &ProfitLossDetail{UserID: r.UserID}
    }
    resultMap[r.UserID].Cost += r.TotalCost
}

Step 5: 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

Private helper — getPointsExchangeRate (reads system_configs, safe default=1):

func (s *service) getPointsExchangeRate(ctx context.Context) int64 {
    var cfg struct { ConfigValue string }
    if err := s.dbR.WithContext(ctx).
        Table("system_configs").
        Select("config_value").
        Where("config_key = ?", "points.exchange_rate").
        First(&cfg).Error; err != nil {
        return 1 // default: 1 yuan = 1 point
    }
    var rate int64
    fmt.Sscanf(cfg.ConfigValue, "%d", &rate)
    if rate <= 0 {
        return 1
    }
    return rate
}

Update service.go stub — replace the stub QueryUserProfitLoss body:

func (s *service) QueryUserProfitLoss(ctx context.Context, params UserProfitLossParams) (*ProfitLossResult, error) {
    return s.queryUser(ctx, params)
}

CRITICAL rules (from RESEARCH.md anti-patterns):

• NEVER call GetDbW() — only s.dbR
• NEVER skip .Error check on any Scan()
• NEVER add WHERE user_id IN when params.UserIDs is empty
• NEVER use CAST(AS SIGNED) in SQL — apply multiplier in Go via ComputePrizeCostWithMultiplier
• NEVER re-implement IsGamePassOrder logic in SQL — use Go function
• NEVER use COALESCE fallback chain for value_cents — D-09: value_cents is single source of truth go build ./internal/service/finance/ && go test -v -run "TestQueryUser" ./internal/service/finance/ <acceptance_criteria>
  • internal/service/finance/query_user.go exists
  • File contains func (s *service) queryUser(
  • File contains func (s *service) getPointsExchangeRate(
  • File contains at least 4 Scan(& calls (one per data source)
  • File contains ClassifyOrderSpending( (reusing existing function, not reimplementing)
  • File contains ComputeProfit( call for totals
  • File contains ComputePrizeCostWithMultiplier( in Go layer (not inside SQL string)
  • File contains points.PointsToCents(
  • File does NOT contain GetDbW
  • File does NOT contain CAST( (multiplier applied in Go, not SQL)
  • File does NOT contain COALESCE(NULLIF(user_inventory.value_cents (no fallback chain)
  • For each Scan( call, there is a corresponding if err := error check
  • service.go QueryUserProfitLoss body contains return s.queryUser(ctx, params)
  • go build ./internal/service/finance/ exits 0 </acceptance_criteria> query_user.go implements QueryUserProfitLoss with 4 fan-out scans, all errors propagated, game-pass classified in Go, multiplier applied in Go, service.go dispatches to it.

Task 2: Add QueryUserProfitLoss integration tests to service_test.go - internal/service/finance/service_test.go (existing helpers from Plan 01 — newTestSvc, seedOrder etc.) - internal/service/finance/query_user.go (just implemented — verify scan logic to test correctly) - internal/repository/mysql/model/orders.gen.go (Orders struct field names for seeding) - internal/repository/mysql/model/user_inventory.gen.go (UserInventory struct field names) - .planning/phases/01-core-pnl-functions/01-RESEARCH.md (Pitfall 6: SQLite compat — no CAST AS SIGNED in test SQL; Pitfall 1: CAST issue only affects MySQL not SQLite int scan) internal/service/finance/service_test.go - TestQueryUserProfitLoss_CashOrder: seed one paid order (status=2, non-game-pass), assert Revenue = actual_amount + discount_amount - TestQueryUserProfitLoss_RefundedOrderExcluded: seed one refunded order (status=4), assert Revenue=0 - TestQueryUserProfitLoss_GamePassOrder: seed one game-pass order (source_type=4, actual_amount=0), seed activity with price_draw, assert Revenue = draw_count × price_draw - TestQueryUserProfitLoss_VoidedInventoryExcluded: seed inventory with status=2, assert Cost=0 - TestQueryUserProfitLoss_RemarkVoidExcluded: seed inventory with remark='void_test', assert Cost=0 - TestQueryUserProfitLoss_LegacyZeroOrderID: seed inventory with order_id=0, assert it IS included in Cost (not excluded) - TestQueryUserProfitLoss_AllUsers: seed 2 users, call with empty UserIDs, assert both appear in Details - TestQueryUserProfitLoss_FilterByUserID: seed 2 users, call with one UserID, assert only that user in Details - TestQueryUserProfitLoss_ResultShape: assert returned ProfitLossResult has non-nil Details and non-nil Breakdown fields - TestQueryUserProfitLoss_ProfitCalculation: seed order + inventory, assert TotalProfit = TotalRevenue - TotalCost Append integration tests to `internal/service/finance/service_test.go`.

First check what model fields are available by reading model/orders.gen.go. The Orders model has fields: ID, UserID, Status, SourceType, OrderNo, ActualAmount, DiscountAmount, Remark, DrawCount, CreatedAt, etc.

NOTE: The AutoMigrate in newTestSvc must cover all tables used in query_user.go. Update newTestSvc if it doesn't already include system_configs, activities, activity_draw_logs, activity_issues, user_item_cards, system_item_cards tables. If AutoMigrate fails for a table (because model doesn't exist), use db.Exec("CREATE TABLE IF NOT EXISTS ...") for simple tables.

For game-pass test, seed the activity and activity draw log rows so the JOIN in queryUser can find the activity price. Alternatively, use source_type=4 + order_no LIKE 'GP%' and a direct activities.price_draw lookup. Keep test setup minimal.

func TestQueryUserProfitLoss_CashOrder(t *testing.T) {
    svc, db := newTestSvc(t)
    seedOrder(t, db, model.Orders{
        ID: 1, UserID: 101, Status: 2,
        SourceType: 2, OrderNo: "O20260321001",
        ActualAmount: 800, DiscountAmount: 200,
    })
    result, err := svc.QueryUserProfitLoss(context.Background(), UserProfitLossParams{UserIDs: []int64{101}})
    require.NoError(t, err)
    require.NotNil(t, result)
    require.Equal(t, int64(1000), result.TotalRevenue, "cash revenue = actual + discount")
}

func TestQueryUserProfitLoss_RefundedOrderExcluded(t *testing.T) {
    svc, db := newTestSvc(t)
    seedOrder(t, db, model.Orders{
        ID: 2, UserID: 102, Status: 4, // refunded
        SourceType: 2, OrderNo: "O20260321002",
        ActualAmount: 1000, DiscountAmount: 0,
    })
    result, err := svc.QueryUserProfitLoss(context.Background(), UserProfitLossParams{UserIDs: []int64{102}})
    require.NoError(t, err)
    require.NotNil(t, result)
    require.Equal(t, int64(0), result.TotalRevenue, "refunded order must not contribute revenue")
}

func TestQueryUserProfitLoss_VoidedInventoryExcluded(t *testing.T) {
    svc, db := newTestSvc(t)
    seedInventory(t, db, model.UserInventory{
        ID: 1, UserID: 103, Status: 2, // voided status
        ValueCents: 5000, OrderID: 0,
    })
    result, err := svc.QueryUserProfitLoss(context.Background(), UserProfitLossParams{UserIDs: []int64{103}})
    require.NoError(t, err)
    require.NotNil(t, result)
    require.Equal(t, int64(0), result.TotalCost, "voided inventory (status=2) must not contribute cost")
}

func TestQueryUserProfitLoss_RemarkVoidExcluded(t *testing.T) {
    svc, db := newTestSvc(t)
    seedInventory(t, db, model.UserInventory{
        ID: 2, UserID: 104, Status: 1, // valid status
        ValueCents: 3000, OrderID: 0,
        Remark: "void_20260101", // remark contains 'void' — must be excluded
    })
    result, err := svc.QueryUserProfitLoss(context.Background(), UserProfitLossParams{UserIDs: []int64{104}})
    require.NoError(t, err)
    require.NotNil(t, result)
    require.Equal(t, int64(0), result.TotalCost, "inventory with remark containing 'void' must not contribute cost")
}

func TestQueryUserProfitLoss_LegacyZeroOrderID(t *testing.T) {
    svc, db := newTestSvc(t)
    seedInventory(t, db, model.UserInventory{
        ID: 3, UserID: 105, Status: 1, // valid
        ValueCents: 2000, OrderID: 0,  // legacy: order_id = 0 (no order linked)
        Remark: "",
    })
    result, err := svc.QueryUserProfitLoss(context.Background(), UserProfitLossParams{UserIDs: []int64{105}})
    require.NoError(t, err)
    require.NotNil(t, result)
    require.Equal(t, int64(2000), result.TotalCost, "legacy inventory with order_id=0 MUST be included in cost (PNL-08)")
}

func TestQueryUserProfitLoss_AllUsers(t *testing.T) {
    svc, db := newTestSvc(t)
    seedOrder(t, db, model.Orders{ID: 10, UserID: 201, Status: 2, SourceType: 2, OrderNo: "O001", ActualAmount: 100})
    seedOrder(t, db, model.Orders{ID: 11, UserID: 202, Status: 2, SourceType: 2, OrderNo: "O002", ActualAmount: 200})
    result, err := svc.QueryUserProfitLoss(context.Background(), UserProfitLossParams{}) // empty UserIDs = all
    require.NoError(t, err)
    require.NotNil(t, result)
    userIDs := make(map[int64]bool)
    for _, d := range result.Details {
        userIDs[d.UserID] = true
    }
    require.True(t, userIDs[201], "user 201 must be in results")
    require.True(t, userIDs[202], "user 202 must be in results")
}

func TestQueryUserProfitLoss_FilterByUserID(t *testing.T) {
    svc, db := newTestSvc(t)
    seedOrder(t, db, model.Orders{ID: 20, UserID: 301, Status: 2, SourceType: 2, OrderNo: "O003", ActualAmount: 500})
    seedOrder(t, db, model.Orders{ID: 21, UserID: 302, Status: 2, SourceType: 2, OrderNo: "O004", ActualAmount: 600})
    result, err := svc.QueryUserProfitLoss(context.Background(), UserProfitLossParams{UserIDs: []int64{301}})
    require.NoError(t, err)
    require.NotNil(t, result)
    for _, d := range result.Details {
        require.Equal(t, int64(301), d.UserID, "only user 301 should appear")
    }
}

func TestQueryUserProfitLoss_ProfitCalculation(t *testing.T) {
    svc, db := newTestSvc(t)
    seedOrder(t, db, model.Orders{ID: 30, UserID: 401, Status: 2, SourceType: 2, OrderNo: "O005", ActualAmount: 1000, DiscountAmount: 200})
    seedInventory(t, db, model.UserInventory{ID: 10, UserID: 401, Status: 1, ValueCents: 800, OrderID: 30, Remark: ""})
    result, err := svc.QueryUserProfitLoss(context.Background(), UserProfitLossParams{UserIDs: []int64{401}})
    require.NoError(t, err)
    require.NotNil(t, result)
    require.Equal(t, int64(1200), result.TotalRevenue)
    require.Equal(t, int64(800), result.TotalCost)
    require.Equal(t, int64(400), result.TotalProfit, "profit = revenue - cost")
}

func TestQueryUserProfitLoss_ResultShape(t *testing.T) {
    svc, _ := newTestSvc(t)
    result, err := svc.QueryUserProfitLoss(context.Background(), UserProfitLossParams{})
    require.NoError(t, err)
    require.NotNil(t, result)
    require.NotNil(t, result.Details, "Details must be non-nil slice")
    require.NotNil(t, result.Breakdown, "Breakdown must be non-nil slice (empty for Phase 1)")
}

If seedOrder/seedInventory fail because Orders or UserInventory don't have all required fields with zero values (SQLite is lenient), add zero values explicitly. If newTestSvc's AutoMigrate doesn't cover user_item_cards or system_item_cards (used in the cost scan JOINs), add db.Exec("CREATE TABLE IF NOT EXISTS user_item_cards (id integer, card_id integer)") and db.Exec("CREATE TABLE IF NOT EXISTS system_item_cards (id integer, reward_multiplier_x1000 integer)") in newTestSvc BEFORE the Scan test runs, or update newTestSvc to include empty table creation. go test -v -run "TestQueryUser" ./internal/service/finance/ <acceptance_criteria> - go test -v -run "TestQueryUser" ./internal/service/finance/ exits 0 - TestQueryUserProfitLoss_CashOrder PASS: TotalRevenue=1000 - TestQueryUserProfitLoss_RefundedOrderExcluded PASS: TotalRevenue=0 - TestQueryUserProfitLoss_VoidedInventoryExcluded PASS: TotalCost=0 - TestQueryUserProfitLoss_RemarkVoidExcluded PASS: TotalCost=0 - TestQueryUserProfitLoss_LegacyZeroOrderID PASS: TotalCost=2000 - TestQueryUserProfitLoss_AllUsers PASS: both users in Details - TestQueryUserProfitLoss_FilterByUserID PASS: only user 301 - TestQueryUserProfitLoss_ProfitCalculation PASS: TotalProfit=400 - TestQueryUserProfitLoss_ResultShape PASS: Details and Breakdown non-nil - go test -v ./internal/service/finance/ exits 0 (all tests including Plan 01 tests still pass) </acceptance_criteria> All QueryUserProfitLoss integration tests pass on SQLite. All PNL-02 through PNL-08, DIM-01, DIM-03, DIM-04 behaviors verified by automated tests.

After all tasks complete:

1. Package compiles: go build ./internal/service/finance/ exits 0
2. All tests pass: go test -v ./internal/service/finance/ exits 0
3. No write DB: grep -r "GetDbW" ./internal/service/finance/ returns 0 matches
4. Fan-out verified: grep -c "Scan(&" ./internal/service/finance/query_user.go returns >= 4
5. Finance functions reused: grep -E "ClassifyOrderSpending|ComputeProfit|ComputePrizeCostWithMultiplier" ./internal/service/finance/query_user.go | wc -l returns >= 3
6. No SQL CAST in tests: grep "AS SIGNED" ./internal/service/finance/service_test.go | wc -l returns 0

<success_criteria>

• query_user.go: 4 fan-out scans (revenue, inventory cost, points cost, coupon cost), all Scan errors propagated, game-pass classified in Go, multiplier applied via ComputePrizeCostWithMultiplier
• service.go: QueryUserProfitLoss dispatches to s.queryUser (no more stub)
• service_test.go: 9+ integration tests for QueryUserProfitLoss all passing on SQLite
• go test -v ./internal/service/finance/ exits 0 with 13+ total PASS results </success_criteria>

After completion, create `.planning/phases/01-core-pnl-functions/01-02-SUMMARY.md`