bindbox-game/.planning/phases/01-core-pnl-functions/01-03-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	03	execute	2	01-01	internal/service/finance/query_activity.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-02 DIM-03 DIM-04 RET-01 RET-03 QUA-03 QUA-04 QUA-05	truths artifacts key_links QueryActivityProfitLoss with a paid cash order returns Revenue = actual_amount + discount_amount attributed to that order's activity QueryActivityProfitLoss with a game-pass order returns Revenue = draw_count × activity_price for that activity QueryActivityProfitLoss excludes refunded orders (status=3 or status=4) from Revenue QueryActivityProfitLoss excludes voided inventory from Cost QueryActivityProfitLoss includes legacy inventory with order_id=0 in Cost QueryActivityProfitLoss with empty ActivityIDs returns aggregated result for all activities QueryActivityProfitLoss returns error (not nil) when a Scan() call fails 1:1 order-to-activity: no revenue proration subquery — revenue attributed directly from orders.activity_id or via single JOIN path provides exports internal/service/finance/query_activity.go QueryActivityProfitLoss implementation with fan-out scans path provides internal/service/finance/service.go Updated QueryActivityProfitLoss method body (replaces stub from Plan 01) path provides internal/service/finance/service_test.go Integration tests for QueryActivityProfitLoss — activity dimension variants from to via pattern internal/service/finance/query_activity.go orders table s.dbR.Table(TableNameOrders) JOIN activity_draw_logs JOIN activity_issues to get activity_id activity_issues.activity_id from to via pattern internal/service/finance/query_activity.go finance.ClassifyOrderSpending / ComputeProfit Go-layer classification per order row after scan ClassifyOrderSpending|ComputeProfit from to via pattern internal/service/finance/query_activity.go user_inventory.activity_id WHERE user_inventory.activity_id IN activityIDs for cost grouping user_inventory.activity_id

Implement QueryActivityProfitLoss in a new query_activity.go file using the same fan-out + in-memory merge pattern as Plan 02. The key difference from the user dimension: dimension key is activity_id (not user_id), and revenue is attributed to activities via the orders → activity_draw_logs → activity_issues → activities JOIN path (1:1 per D-01, no proration needed). The service.go stub is replaced with a real dispatch call.

Purpose: Deliver the activity-dimension P&L function completing all Phase 1 requirements. Plan 03 runs in parallel with Plan 02 since they touch different files (query_activity.go vs query_user.go).

Output: query_activity.go (implementation), service.go (updated), service_test.go (extended with activity 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 ActivityProfitLossParams struct {
    ActivityIDs []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 }
// QueryActivityProfitLoss stub — REPLACE with: return s.queryActivity(ctx, params)

From internal/service/finance/profit_metrics.go (existing — MUST reuse):

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

From internal/repository/mysql/model/:

// Orders fields: user_id, status (2=paid,3=cancelled,4=refunded), source_type, order_no,
//                actual_amount, discount_amount, remark, item_card_id, created_at
// UserInventory fields: activity_id, user_id, order_id, value_cents, status, remark, reward_id
// UserPointsLedger fields: user_id, action, points, ref_table, ref_id, created_at
// UserCouponLedger fields: user_id, change_amount, order_id, created_at
// Key table constants:
//   model.TableNameOrders, model.TableNameUserInventory
//   model.TableNameUserPointsLedger, model.TableNameUserCouponLedger

Key decision from CONTEXT.md (D-01): 1:1 order-to-activity — NO revenue proration subquery needed. The activity dimension gets revenue by joining orders to activity_draw_logs to get which activity each order belongs to. Game-pass revenue: draw_count per activity_draw_logs × activities.price_draw, grouped by activity_id.

Task 1: Create query_activity.go — QueryActivityProfitLoss fan-out implementation - internal/service/finance/service.go (verify service struct — dbR field, stub method to replace) - internal/service/finance/types.go (verify ActivityProfitLossParams and ProfitLossResult fields) - internal/service/finance/profit_metrics.go (verify ClassifyOrderSpending, ComputeProfit signatures) - internal/service/finance/query_user.go (if Plan 02 completed — compare fan-out structure to mirror) - internal/api/admin/dashboard_activity.go (lines 225-300 — cost scan pattern with activity_id grouping) - .planning/phases/01-core-pnl-functions/1-CONTEXT.md (D-01: 1:1 order-to-activity; D-09: value_cents single source; D-02: game-pass per activity) - .planning/phases/01-core-pnl-functions/01-RESEARCH.md (Pitfall 1: CAST; Pitfall 2: empty slice; Pitfall 5: scan error; Pitfall 6: SQLite compat) internal/service/finance/query_activity.go - Revenue scan: per-order rows joined to activity via activity_draw_logs → activity_issues → activities; classify per-row in Go using ClassifyOrderSpending(); sum revenue by activity_id - Game-pass revenue: count draws per activity per order via activity_draw_logs JOIN; multiply by activities.price_draw using ComputeGamePassValue() in Go - Cost scan (inventory): group by user_inventory.activity_id; WHERE status IN (1,3) AND remark NOT LIKE '%void%' AND (orders.status=2 OR order_id=0 OR order_id IS NULL); apply ComputePrizeCostWithMultiplier in Go (not SQL) - Cost scan (points): join user_points_ledger to orders via ref_table='orders' and ref_id=order_no, then join to activity to get activity_id; OR use a simpler approach: join to activity_draw_logs via user_id+order filters — use the approach that SQLite can handle; SUM points by activity WHERE action='order_deduct' - Cost scan (coupons): join user_coupon_ledger to orders (via order_id) to orders to activity_draw_logs to activity_issues to get activity_id; WHERE change_amount < 0 AND orders.status=2 - Every Scan() must check .Error and return fmt.Errorf("QueryActivityProfitLoss %s scan: %w", step, err) - Empty ActivityIDs: do NOT add WHERE activity_id IN clause - Time filters: add WHERE orders.created_at >= *StartTime only if non-nil - Merge in Go via map[int64]*ProfitLossDetail keyed by activity_id - Final aggregation: sum all Details into TotalRevenue/TotalCost; call ComputeProfit for totals - Return &ProfitLossResult{..., Breakdown: []interface{}{}} Create `internal/service/finance/query_activity.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) queryActivity(ctx context.Context, params ActivityProfitLossParams) (*ProfitLossResult, error).

Step 1: Revenue scan — per-order rows with activity attribution via draw logs JOIN.

D-01 simplification: one order belongs to one activity. Join orders → activity_draw_logs → activity_issues → activities to get the activity_id per order. Use MAX(activity_issues.activity_id) since 1:1.

type activityRevenueRow struct {
    ActivityID    int64
    SourceType    int32
    OrderNo       string
    ActualAmount  int64
    DiscountAmount int64
    Remark        string
    DrawCount     int64  // COUNT(activity_draw_logs.id) for game-pass value
    ActivityPrice int64  // activities.price_draw
}
var revenueRows []activityRevenueRow
q := s.dbR.WithContext(ctx).
    Table(model.TableNameOrders).
    Select(`activity_issues.activity_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("JOIN activity_draw_logs ON activity_draw_logs.order_id = orders.id").
    Joins("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, activity_issues.activity_id, orders.source_type, orders.order_no, orders.actual_amount, orders.discount_amount, orders.remark")
if len(params.ActivityIDs) > 0 {
    q = q.Where("activity_issues.activity_id IN ?", params.ActivityIDs)
}
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("QueryActivityProfitLoss revenue scan: %w", err)
}

Merge revenue — classify per row in Go:

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.ActivityID]; !ok {
        resultMap[r.ActivityID] = &ProfitLossDetail{ActivityID: r.ActivityID}
    }
    resultMap[r.ActivityID].Revenue += bd.Total
}

Step 2: Inventory cost scan — group by user_inventory.activity_id; apply multiplier in Go.

type activityInventoryRow struct {
    ActivityID      int64
    ValueCents      int64
    MultiplierX1000 int64
}
iq := s.dbR.WithContext(ctx).
    Table(model.TableNameUserInventory).
    Select(`user_inventory.activity_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.ActivityIDs) > 0 {
    iq = iq.Where("user_inventory.activity_id IN ?", params.ActivityIDs)
}
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 []activityInventoryRow
if err := iq.Scan(&inventoryRows).Error; err != nil {
    return nil, fmt.Errorf("QueryActivityProfitLoss inventory cost scan: %w", err)
}
for _, r := range inventoryRows {
    cost := ComputePrizeCostWithMultiplier(r.ValueCents, r.MultiplierX1000)
    if _, ok := resultMap[r.ActivityID]; !ok {
        resultMap[r.ActivityID] = &ProfitLossDetail{ActivityID: r.ActivityID}
    }
    resultMap[r.ActivityID].Cost += cost
}

Step 3: Points cost scan — link points to activity via activity_draw_logs. Since user_points_ledger.ref_table = 'orders' and ref_id = order_no, join via orders then to draw_logs:

type activityPointsRow struct {
    ActivityID  int64
    TotalPoints int64
}
pq := s.dbR.WithContext(ctx).
    Table(model.TableNameUserPointsLedger).
    Select("activity_issues.activity_id, SUM(-user_points_ledger.points) as total_points").
    Joins("JOIN orders ON orders.order_no = user_points_ledger.ref_id AND user_points_ledger.ref_table = 'orders'").
    Joins("JOIN activity_draw_logs ON activity_draw_logs.order_id = orders.id").
    Joins("JOIN activity_issues ON activity_issues.id = activity_draw_logs.issue_id").
    Where("user_points_ledger.action = ?", "order_deduct").
    Where("user_points_ledger.points < ?", 0).
    Where("orders.status = ?", 2)
if len(params.ActivityIDs) > 0 {
    pq = pq.Where("activity_issues.activity_id IN ?", params.ActivityIDs)
}
if params.StartTime != nil {
    pq = pq.Where("user_points_ledger.created_at >= ?", *params.StartTime)
}
if params.EndTime != nil {
    pq = pq.Where("user_points_ledger.created_at <= ?", *params.EndTime)
}
pq = pq.Group("activity_issues.activity_id")
var pointsRows []activityPointsRow
if err := pq.Scan(&pointsRows).Error; err != nil {
    return nil, fmt.Errorf("QueryActivityProfitLoss points cost scan: %w", err)
}
rate := s.getPointsExchangeRate(ctx)
for _, r := range pointsRows {
    costCents := points.PointsToCents(r.TotalPoints, float64(rate))
    if _, ok := resultMap[r.ActivityID]; !ok {
        resultMap[r.ActivityID] = &ProfitLossDetail{ActivityID: r.ActivityID}
    }
    resultMap[r.ActivityID].Cost += costCents
}

Step 4: Coupon cost scan — link coupons to activity via orders → draw_logs.

type activityCouponRow struct {
    ActivityID int64
    TotalCost  int64
}
cq := s.dbR.WithContext(ctx).
    Table(model.TableNameUserCouponLedger).
    Select("activity_issues.activity_id, SUM(-user_coupon_ledger.change_amount) as total_cost").
    Joins("JOIN orders ON orders.id = user_coupon_ledger.order_id").
    Joins("JOIN activity_draw_logs ON activity_draw_logs.order_id = orders.id").
    Joins("JOIN activity_issues ON activity_issues.id = activity_draw_logs.issue_id").
    Where("user_coupon_ledger.change_amount < ?", 0).
    Where("orders.status = ?", 2)
if len(params.ActivityIDs) > 0 {
    cq = cq.Where("activity_issues.activity_id IN ?", params.ActivityIDs)
}
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("activity_issues.activity_id")
var couponRows []activityCouponRow
if err := cq.Scan(&couponRows).Error; err != nil {
    return nil, fmt.Errorf("QueryActivityProfitLoss coupon cost scan: %w", err)
}
for _, r := range couponRows {
    if _, ok := resultMap[r.ActivityID]; !ok {
        resultMap[r.ActivityID] = &ProfitLossDetail{ActivityID: r.ActivityID}
    }
    resultMap[r.ActivityID].Cost += r.TotalCost
}

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

NOTE: getPointsExchangeRate is defined in query_user.go in the same package — it is accessible from query_activity.go without redefinition. Do NOT redefine it.

Update service.go stub — replace QueryActivityProfitLoss body:

func (s *service) QueryActivityProfitLoss(ctx context.Context, params ActivityProfitLossParams) (*ProfitLossResult, error) {
    return s.queryActivity(ctx, params)
}

CRITICAL rules (same as Plan 02):

• NEVER call GetDbW() — only s.dbR
• NEVER skip .Error check on any Scan()
• NEVER add WHERE activity_id IN when params.ActivityIDs is empty
• NEVER use CAST(AS SIGNED) in SQL — apply multiplier via ComputePrizeCostWithMultiplier in Go
• NEVER use COALESCE fallback chain for value_cents — D-09: value_cents only
• NEVER re-implement IsGamePassOrder in SQL CASE expressions — classify in Go via ClassifyOrderSpending go build ./internal/service/finance/ && go test -v -run "TestQueryActivity" ./internal/service/finance/ <acceptance_criteria>
  • internal/service/finance/query_activity.go exists
  • File contains func (s *service) queryActivity(
  • File does NOT contain func (s *service) getPointsExchangeRate( (defined in query_user.go, not here)
  • File contains at least 4 Scan(& calls (revenue, inventory, points, coupon)
  • File contains ClassifyOrderSpending( in Go layer
  • File contains ComputeProfit( 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( (no SQL-layer casting — multiplier in Go)
  • File does NOT contain COALESCE(NULLIF(user_inventory.value_cents (no fallback chain)
  • For each Scan( call, there is a if err := error check with return nil, fmt.Errorf
  • service.go QueryActivityProfitLoss body contains return s.queryActivity(ctx, params)
  • go build ./internal/service/finance/ exits 0 </acceptance_criteria> query_activity.go implements QueryActivityProfitLoss with 4 fan-out scans attributed to activity dimension, all errors propagated, game-pass classified in Go, multiplier applied in Go.

Task 2: Add QueryActivityProfitLoss integration tests to service_test.go - internal/service/finance/service_test.go (existing helpers and tests from Plans 01+02) - internal/service/finance/query_activity.go (just implemented — verify scan logic to test correctly) - internal/repository/mysql/model/orders.gen.go (Orders struct fields for seeding) - internal/repository/mysql/model/user_inventory.gen.go (UserInventory fields) - .planning/phases/01-core-pnl-functions/01-RESEARCH.md (Pitfall 6: SQLite compat — avoid CAST AS SIGNED; activity JOIN tables must exist for SQLite AutoMigrate or CREATE TABLE) internal/service/finance/service_test.go - TestQueryActivityProfitLoss_EmptyParams_ReturnsResult: call with empty params, assert no error, result not nil - TestQueryActivityProfitLoss_CashOrderRevenue: seed order + draw_log + activity, assert Revenue = actual + discount - TestQueryActivityProfitLoss_RefundedOrderExcluded: seed refunded order (status=4), assert Revenue=0 - TestQueryActivityProfitLoss_VoidedInventoryExcluded: seed inventory with status=2 for activity, assert Cost=0 - TestQueryActivityProfitLoss_LegacyZeroOrderID: seed inventory with order_id=0 and activity_id set, assert included in Cost - TestQueryActivityProfitLoss_AllActivities: seed 2 activities with orders, call with empty ActivityIDs, assert both in Details - TestQueryActivityProfitLoss_FilterByActivityID: seed 2 activities, call with one ActivityID, assert only that activity in Details - TestQueryActivityProfitLoss_ProfitCalculation: revenue - cost = profit - TestQueryActivityProfitLoss_ResultShape: Details and Breakdown non-nil - For SQLite compat: seed draw log tables with db.Exec CREATE TABLE IF NOT EXISTS or via AutoMigrate using struct (if model exists); the query JOINs activity_draw_logs, activity_issues, activities — these tables must exist in test DB Append activity integration tests to `internal/service/finance/service_test.go`.

The activity-dimension tests require activity_draw_logs, activity_issues, and activities tables to exist in the SQLite test DB. Update newTestSvc if needed to create these tables (use db.Exec for tables without model structs, or check if model structs exist for AutoMigrate).

First check if model.ActivityDrawLogs, model.ActivityIssues, model.Activities exist in the model package — if they do, add them to AutoMigrate; if not, create minimal SQLite tables via db.Exec.

For the tests, create a helper seedActivityWithDrawLog that seeds an activity, an activity_issue, and an activity_draw_log linked to a given order:

// seedActivity creates minimal activity + issue + draw_log for JOIN tests
// activityID: the activity.id, orderID: the linked order.id, userID: the draw user
func seedActivitySetup(t *testing.T, db *gorm.DB, activityID, issueID, orderID, userID int64, priceDraw int64) {
    t.Helper()
    // Create tables if not covered by AutoMigrate
    db.Exec("CREATE TABLE IF NOT EXISTS activities (id integer primary key, price_draw integer not null default 0)")
    db.Exec("CREATE TABLE IF NOT EXISTS activity_issues (id integer primary key, activity_id integer not null)")
    db.Exec("CREATE TABLE IF NOT EXISTS activity_draw_logs (id integer primary key, order_id integer, issue_id integer, user_id integer)")
    // Seed rows
    require.NoError(t, db.Exec("INSERT OR IGNORE INTO activities (id, price_draw) VALUES (?, ?)", activityID, priceDraw).Error)
    require.NoError(t, db.Exec("INSERT OR IGNORE INTO activity_issues (id, activity_id) VALUES (?, ?)", issueID, activityID).Error)
    require.NoError(t, db.Exec("INSERT OR IGNORE INTO activity_draw_logs (id, order_id, issue_id, user_id) VALUES (?, ?, ?, ?)", orderID*100+issueID, orderID, issueID, userID).Error)
}

Tests:

func TestQueryActivityProfitLoss_CashOrderRevenue(t *testing.T) {
    svc, db := newTestSvc(t)
    seedOrder(t, db, model.Orders{
        ID: 50, UserID: 501, Status: 2,
        SourceType: 2, OrderNo: "A001",
        ActualAmount: 600, DiscountAmount: 150,
    })
    seedActivitySetup(t, db, 1001, 2001, 50, 501, 100)
    result, err := svc.QueryActivityProfitLoss(context.Background(), ActivityProfitLossParams{ActivityIDs: []int64{1001}})
    require.NoError(t, err)
    require.NotNil(t, result)
    require.Equal(t, int64(750), result.TotalRevenue, "cash revenue = actual(600) + discount(150)")
}

func TestQueryActivityProfitLoss_RefundedOrderExcluded(t *testing.T) {
    svc, db := newTestSvc(t)
    seedOrder(t, db, model.Orders{
        ID: 51, UserID: 502, Status: 4, // refunded
        SourceType: 2, OrderNo: "A002",
        ActualAmount: 800, DiscountAmount: 0,
    })
    seedActivitySetup(t, db, 1002, 2002, 51, 502, 100)
    result, err := svc.QueryActivityProfitLoss(context.Background(), ActivityProfitLossParams{ActivityIDs: []int64{1002}})
    require.NoError(t, err)
    require.NotNil(t, result)
    require.Equal(t, int64(0), result.TotalRevenue, "refunded order must not contribute revenue")
}

func TestQueryActivityProfitLoss_VoidedInventoryExcluded(t *testing.T) {
    svc, db := newTestSvc(t)
    seedInventory(t, db, model.UserInventory{
        ID: 20, UserID: 503, ActivityID: 1003,
        Status: 2, // voided status
        ValueCents: 4000, OrderID: 0,
    })
    result, err := svc.QueryActivityProfitLoss(context.Background(), ActivityProfitLossParams{ActivityIDs: []int64{1003}})
    require.NoError(t, err)
    require.NotNil(t, result)
    require.Equal(t, int64(0), result.TotalCost, "voided inventory must not contribute cost")
}

func TestQueryActivityProfitLoss_LegacyZeroOrderID(t *testing.T) {
    svc, db := newTestSvc(t)
    seedInventory(t, db, model.UserInventory{
        ID: 21, UserID: 504, ActivityID: 1004,
        Status: 1, ValueCents: 3500, OrderID: 0,
        Remark: "",
    })
    result, err := svc.QueryActivityProfitLoss(context.Background(), ActivityProfitLossParams{ActivityIDs: []int64{1004}})
    require.NoError(t, err)
    require.NotNil(t, result)
    require.Equal(t, int64(3500), result.TotalCost, "legacy inventory with order_id=0 MUST be included in cost (PNL-08)")
}

func TestQueryActivityProfitLoss_AllActivities(t *testing.T) {
    svc, db := newTestSvc(t)
    seedOrder(t, db, model.Orders{ID: 60, UserID: 601, Status: 2, SourceType: 2, OrderNo: "A010", ActualAmount: 100})
    seedOrder(t, db, model.Orders{ID: 61, UserID: 602, Status: 2, SourceType: 2, OrderNo: "A011", ActualAmount: 200})
    seedActivitySetup(t, db, 2001, 3001, 60, 601, 50)
    seedActivitySetup(t, db, 2002, 3002, 61, 602, 50)
    result, err := svc.QueryActivityProfitLoss(context.Background(), ActivityProfitLossParams{}) // empty = all
    require.NoError(t, err)
    require.NotNil(t, result)
    actIDs := make(map[int64]bool)
    for _, d := range result.Details {
        actIDs[d.ActivityID] = true
    }
    require.True(t, actIDs[2001], "activity 2001 must be in results")
    require.True(t, actIDs[2002], "activity 2002 must be in results")
}

func TestQueryActivityProfitLoss_FilterByActivityID(t *testing.T) {
    svc, db := newTestSvc(t)
    seedOrder(t, db, model.Orders{ID: 70, UserID: 701, Status: 2, SourceType: 2, OrderNo: "A020", ActualAmount: 300})
    seedOrder(t, db, model.Orders{ID: 71, UserID: 702, Status: 2, SourceType: 2, OrderNo: "A021", ActualAmount: 400})
    seedActivitySetup(t, db, 3001, 4001, 70, 701, 50)
    seedActivitySetup(t, db, 3002, 4002, 71, 702, 50)
    result, err := svc.QueryActivityProfitLoss(context.Background(), ActivityProfitLossParams{ActivityIDs: []int64{3001}})
    require.NoError(t, err)
    require.NotNil(t, result)
    for _, d := range result.Details {
        require.Equal(t, int64(3001), d.ActivityID, "only activity 3001 should appear")
    }
}

func TestQueryActivityProfitLoss_ProfitCalculation(t *testing.T) {
    svc, db := newTestSvc(t)
    seedOrder(t, db, model.Orders{ID: 80, UserID: 801, Status: 2, SourceType: 2, OrderNo: "A030", ActualAmount: 2000, DiscountAmount: 500})
    seedActivitySetup(t, db, 4001, 5001, 80, 801, 100)
    seedInventory(t, db, model.UserInventory{ID: 30, UserID: 801, ActivityID: 4001, Status: 1, ValueCents: 1200, OrderID: 80})
    result, err := svc.QueryActivityProfitLoss(context.Background(), ActivityProfitLossParams{ActivityIDs: []int64{4001}})
    require.NoError(t, err)
    require.NotNil(t, result)
    require.Equal(t, int64(2500), result.TotalRevenue, "revenue = actual(2000) + discount(500)")
    require.Equal(t, int64(1200), result.TotalCost)
    require.Equal(t, int64(1300), result.TotalProfit, "profit = 2500 - 1200")
}

func TestQueryActivityProfitLoss_ResultShape(t *testing.T) {
    svc, _ := newTestSvc(t)
    result, err := svc.QueryActivityProfitLoss(context.Background(), ActivityProfitLossParams{})
    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 empty slice")
}

NOTE on SQLite compat: The revenue scan JOINs activity_draw_logs, activity_issues, activities. For tests without these rows seeded, the INNER JOIN will return 0 rows (not error) — so TestQueryActivityProfitLoss_VoidedInventoryExcluded and TestQueryActivityProfitLoss_LegacyZeroOrderID only test the cost scan (inventory), which uses user_inventory.activity_id directly, not the draw_log JOIN. This is correct — cost and revenue are independent fan-out scans. go test -v -run "TestQueryActivity" ./internal/service/finance/ <acceptance_criteria> - go test -v -run "TestQueryActivity" ./internal/service/finance/ exits 0 - TestQueryActivityProfitLoss_CashOrderRevenue PASS: TotalRevenue=750 - TestQueryActivityProfitLoss_RefundedOrderExcluded PASS: TotalRevenue=0 - TestQueryActivityProfitLoss_VoidedInventoryExcluded PASS: TotalCost=0 - TestQueryActivityProfitLoss_LegacyZeroOrderID PASS: TotalCost=3500 - TestQueryActivityProfitLoss_AllActivities PASS: both activities in Details - TestQueryActivityProfitLoss_FilterByActivityID PASS: only activity 3001 in Details - TestQueryActivityProfitLoss_ProfitCalculation PASS: TotalProfit=1300 - TestQueryActivityProfitLoss_ResultShape PASS: Details and Breakdown non-nil - go test -v ./internal/service/finance/ exits 0 (ALL tests pass including Plan 01 and 02 tests) </acceptance_criteria> All QueryActivityProfitLoss integration tests pass on SQLite. All DIM-02, DIM-03, DIM-04, PNL-02 through PNL-08, RET-01, RET-03 behaviors verified for the activity dimension.

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_activity.go returns >= 4
5. Finance functions reused: grep -E "ClassifyOrderSpending|ComputeProfit|ComputePrizeCostWithMultiplier" ./internal/service/finance/query_activity.go | wc -l returns >= 3
6. No duplicate helper: grep -c "getPointsExchangeRate" ./internal/service/finance/query_activity.go returns 1 (call) not 2 (definition would mean duplicate)
7. Full build: go build ./... exits 0

<success_criteria>

• query_activity.go: 4 fan-out scans attributed to activity dimension, all errors propagated, game-pass classified in Go, multiplier applied in Go, no GetDbW()
• service.go: QueryActivityProfitLoss dispatches to s.queryActivity
• service_test.go: 8+ TestQueryActivity* tests all PASS on SQLite
• go test -v ./internal/service/finance/ exits 0 with 20+ total PASS results
• go build ./... exits 0 </success_criteria>

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