hyapp-server/services/statistics-service/internal/storage/mysql/query_test.go

package mysql

import (
	"context"
	"encoding/json"
	"math"
	"runtime"
	"strconv"
	"strings"
	"testing"
	"time"

	"github.com/DATA-DOG/go-sqlmock"
	"hyapp/internal/testutil/mysqlschema"
	"hyapp/pkg/appcode"
)

func TestQueryOverviewUsesActivityLuckyGiftDayStats(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_query_test",
	})
	activitySchema := mysqlschema.New(t, mysqlschema.Config{
		EnvVar:         "ACTIVITY_SERVICE_MYSQL_TEST_DSN",
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "..", "activity-service", "deploy", "mysql", "initdb", "001_activity_service.sql"),
		DatabasePrefix: "hy_activity_query_test",
	})
	repository, err := Open(ctx, statsSchema.DSN, activitySchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	if _, err := repository.db.ExecContext(ctx, `
		INSERT INTO stat_app_day_country (
			app_code, stat_day, country_id, lucky_gift_turnover, lucky_gift_payout, updated_at_ms
		) VALUES ('lalu', '2026-06-06', 210, 100, 900, 1)`); err != nil {
		t.Fatalf("seed stale statistics overview: %v", err)
	}
	if _, err := repository.db.ExecContext(ctx, `
		INSERT INTO stat_lucky_gift_pool_day_country (
			app_code, stat_day, country_id, pool_id, turnover_coin, payout_coin, updated_at_ms
		) VALUES ('lalu', '2026-06-06', 210, 'lucky', 100, 900, 1)`); err != nil {
		t.Fatalf("seed stale statistics pool: %v", err)
	}
	if _, err := repository.activityDB.ExecContext(ctx, `
		INSERT INTO lucky_draw_pool_day_stats (
			app_code, stat_day, visible_region_id, pool_id, gift_id, draw_count, turnover_coin, payout_coin,
			base_reward_coin, room_atmosphere_reward_coin, activity_subsidy_coin, created_at_ms, updated_at_ms
		) VALUES
		  ('lalu', '2026-06-06', 210, 'super_lucky', '', 10, 2744832, 2401885, 2401885, 0, 0, 1, 1),
		  ('lalu', '2026-06-06', 210, 'lucky', '', 5, 148322, 126415, 126415, 0, 0, 1, 1)`); err != nil {
		t.Fatalf("seed activity databi stats: %v", err)
	}

	start := time.Date(2026, 6, 6, 0, 0, 0, 0, time.UTC).UnixMilli()
	overview, err := repository.QueryOverview(ctx, OverviewQuery{AppCode: "lalu", StartMS: start, EndMS: start + int64(24*time.Hour/time.Millisecond), CountryID: 210})
	if err != nil {
		t.Fatalf("query overview: %v", err)
	}
	if overview.LuckyGiftTurnover != 2_893_154 || overview.LuckyGiftPayout != 2_528_300 || overview.LuckyGiftProfit != 364_854 {
		t.Fatalf("activity lucky gift overview not applied: %+v", overview)
	}
	if len(overview.LuckyGiftPools) != 2 {
		t.Fatalf("activity lucky gift pools not applied: %+v", overview.LuckyGiftPools)
	}
	if overview.LuckyGiftPools[0].PoolID != "super_lucky" || overview.LuckyGiftPools[0].TurnoverCoin != 2_744_832 || overview.LuckyGiftPools[0].PayoutCoin != 2_401_885 {
		t.Fatalf("super_lucky pool mismatch: %+v", overview.LuckyGiftPools[0])
	}
}

func TestConsumeAppTrackingEventsStoresRawEventsAndDeduplicates(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_app_tracking_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	// 漏斗读取按 App+毫秒窗口裁剪，索引顺序属于性能契约；event_name 放在时间前会让全事件漏斗退化为按 App 扫描。
	indexRows, err := repository.db.QueryContext(ctx, `
		SELECT COLUMN_NAME
		FROM information_schema.STATISTICS
		WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'app_tracking_events' AND INDEX_NAME = 'idx_app_tracking_time_window'
		ORDER BY SEQ_IN_INDEX`)
	if err != nil {
		t.Fatalf("query app tracking time window index: %v", err)
	}
	var indexColumns []string
	for indexRows.Next() {
		var column string
		if err := indexRows.Scan(&column); err != nil {
			_ = indexRows.Close()
			t.Fatalf("scan app tracking time window index: %v", err)
		}
		indexColumns = append(indexColumns, column)
	}
	if err := indexRows.Close(); err != nil {
		t.Fatalf("close app tracking time window index rows: %v", err)
	}
	if got, want := strings.Join(indexColumns, ","), "app_code,occurred_at_ms,event_name,identity_key,device_id"; got != want {
		t.Fatalf("app tracking time window index mismatch: got=%s want=%s", got, want)
	}

	occurredAt := time.Date(2026, 7, 2, 23, 59, 59, 0, time.UTC).UnixMilli()
	result, err := repository.ConsumeAppTrackingEvents(appcode.WithContext(ctx, "lalu"), []AppTrackingEvent{
		{
			AppCode:      "lalu",
			EventID:      "evt-banner-1",
			EventName:    "banner_view",
			EventType:    "view",
			Screen:       "home",
			TargetType:   "banner",
			TargetID:     "17",
			UserID:       42,
			DeviceID:     "dev-1",
			SessionID:    "sess-1",
			Platform:     "ios",
			AppVersion:   "1.2.3",
			Language:     "en-US",
			Timezone:     "Asia/Shanghai",
			CountryID:    86,
			RegionID:     210,
			DurationMS:   12,
			Success:      true,
			Properties:   json.RawMessage(`{"slot":"top"}`),
			OccurredAtMS: occurredAt,
		},
	})
	if err != nil {
		t.Fatalf("consume app tracking event: %v", err)
	}
	if !result.Accepted || result.Received != 1 || result.Stored != 1 || result.Duplicated != 0 || result.ServerTimeMS <= 0 {
		t.Fatalf("unexpected first consume result: %+v", result)
	}

	duplicate, err := repository.ConsumeAppTrackingEvents(appcode.WithContext(ctx, "lalu"), []AppTrackingEvent{
		{
			AppCode:      "lalu",
			EventID:      "evt-banner-1",
			EventName:    "banner_view",
			Screen:       "changed",
			UserID:       42,
			DeviceID:     "dev-1",
			OccurredAtMS: occurredAt,
		},
	})
	if err != nil {
		t.Fatalf("consume duplicate app tracking event: %v", err)
	}
	if duplicate.Received != 1 || duplicate.Stored != 0 || duplicate.Duplicated != 1 {
		t.Fatalf("duplicate consume result mismatch: %+v", duplicate)
	}

	var count int64
	var statDay, screen, identityKey, propertiesJSON string
	var userID, countryID, regionID, durationMS int64
	var success bool
	if err := repository.db.QueryRowContext(ctx, `
		SELECT COUNT(*), MAX(CAST(stat_day AS CHAR)), MAX(screen), MAX(identity_key), MAX(user_id), MAX(country_id), MAX(region_id),
		       MAX(duration_ms), MAX(success), COALESCE(MAX(CAST(properties_json AS CHAR)), '{}')
		FROM app_tracking_events
		WHERE app_code = 'lalu' AND event_id = 'evt-banner-1'
	`).Scan(&count, &statDay, &screen, &identityKey, &userID, &countryID, &regionID, &durationMS, &success, &propertiesJSON); err != nil {
		t.Fatalf("query app tracking event: %v", err)
	}
	if count != 1 || statDay != "2026-07-02" || screen != "home" || identityKey != "u:42" || userID != 42 || countryID != 86 || regionID != 210 || durationMS != 12 || !success {
		t.Fatalf("stored event mismatch: count=%d statDay=%s screen=%s identityKey=%s userID=%d countryID=%d regionID=%d durationMS=%d success=%v", count, statDay, screen, identityKey, userID, countryID, regionID, durationMS, success)
	}
	if DecodeJSON(propertiesJSON)["slot"] != "top" {
		t.Fatalf("properties_json mismatch: %s", propertiesJSON)
	}
}

func TestQueryAppTrackingFunnelAggregatesStepsAndD1Cohorts(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_app_tracking_funnel_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	start := time.Date(2026, 7, 1, 0, 0, 0, 0, time.UTC)
	event := func(userID int64, deviceID string, eventName string, offset time.Duration, properties string) AppTrackingEvent {
		return AppTrackingEvent{
			AppCode:      "lalu",
			EventID:      "evt-" + strconv.FormatInt(userID, 10) + "-" + deviceID + "-" + eventName + "-" + strconv.FormatInt(int64(offset/time.Second), 10),
			EventName:    eventName,
			UserID:       userID,
			DeviceID:     deviceID,
			Language:     "en-US",
			CountryID:    86,
			RegionID:     210,
			Properties:   json.RawMessage(properties),
			OccurredAtMS: start.Add(offset).UnixMilli(),
		}
	}
	user1Props := `{"country":"US","channel":"ads","login_method":"google"}`
	user1AffiliateProps := `{"country":"US","channel":"affiliate","login_method":"google"}`
	user2Props := `{"country":"BR","channel":"organic","login_method":"phone"}`
	affiliateLogin := event(1, "dev-1", "login_success", 1500*time.Millisecond, user1AffiliateProps)
	affiliateLogin.EventID += "-affiliate"
	retainedOutsideFilter := event(1, "dev-1", "send_message", 24*time.Hour+time.Second, user1Props)
	// D1 表示次日任意活跃，不能继承首日查询的区域过滤，否则用户次日跨区会被错误判定流失。
	retainedOutsideFilter.RegionID = 999
	events := []AppTrackingEvent{
		event(1, "dev-1", "login_start", 0, user1Props),
		event(1, "dev-1", "login_success", time.Second, user1Props),
		affiliateLogin,
		event(1, "dev-1", "profile_start", 2*time.Second, user1Props),
		event(1, "dev-1", "profile_complete", 3*time.Second, user1Props),
		event(1, "dev-1", "home_room_impression", 4*time.Second, user1Props),
		event(1, "dev-1", "home_room_click", 5*time.Second, user1Props),
		event(1, "dev-1", "room_join_success", 6*time.Second, user1Props),
		event(1, "dev-1", "stay_30s", 30*time.Second, user1Props),
		event(1, "dev-1", "stay_3m", 3*time.Minute, user1Props),
		event(1, "dev-1", "send_message", 4*time.Minute, user1Props),
		event(1, "dev-1", "follow_host", 5*time.Minute, user1Props),
		event(1, "dev-1", "push_permission", 6*time.Minute, user1Props),
		retainedOutsideFilter,
		event(2, "dev-2", "login_start", 0, user2Props),
		event(2, "dev-2", "login_success", time.Second, user2Props),
		event(2, "dev-2", "home_room_impression", 2*time.Second, user2Props),
		event(2, "dev-2", "home_room_click", 3*time.Second, user2Props),
		event(2, "dev-2", "room_join_fail", 4*time.Second, user2Props),
	}
	excluded := event(3, "dev-3", "login_start", 0, user1Props)
	excluded.EventID = "evt-excluded-region-login-start"
	excluded.RegionID = 999
	events = append(events, excluded)
	if _, err := repository.ConsumeAppTrackingEvents(appcode.WithContext(ctx, "lalu"), events); err != nil {
		t.Fatalf("consume app tracking events: %v", err)
	}

	funnel, err := repository.QueryAppTrackingFunnel(ctx, AppTrackingFunnelQuery{
		AppCode:   "lalu",
		StartMS:   start.UnixMilli(),
		EndMS:     start.Add(24 * time.Hour).UnixMilli(),
		RegionIDs: []int64{210},
	})
	if err != nil {
		t.Fatalf("query app tracking funnel: %v", err)
	}
	if funnel.Totals.LoginStartUsers != 2 || funnel.Totals.LoginSuccessUsers != 2 || funnel.Totals.RoomJoinSuccessUsers != 1 || funnel.Totals.RoomJoinFailUsers != 1 {
		t.Fatalf("unexpected funnel totals: %+v", funnel.Totals)
	}
	if funnel.Totals.D1RetentionBaseUsers != 2 || funnel.Totals.D1RetentionUsers != 1 || math.Abs(funnel.Totals.D1RetentionRate-0.5) > 0.0001 {
		t.Fatalf("unexpected d1 totals: %+v", funnel.Totals)
	}

	stepByEvent := map[string]AppTrackingFunnelStep{}
	for _, step := range funnel.Steps {
		stepByEvent[step.EventName] = step
	}
	if stepByEvent["login_start"].UserCount != 2 || stepByEvent["profile_complete"].UserCount != 1 || stepByEvent["stay_3m"].UserCount != 1 {
		t.Fatalf("unexpected step counts: %+v", stepByEvent)
	}
	if !stepByEvent["room_join_fail"].IsFailure {
		t.Fatalf("room_join_fail must be marked as failure: %+v", stepByEvent["room_join_fail"])
	}

	cohort := func(dimension, value string) AppTrackingFunnelCohort {
		for _, item := range funnel.Cohorts {
			if item.Dimension == dimension && item.Value == value {
				return item
			}
		}
		t.Fatalf("missing cohort %s=%s in %+v", dimension, value, funnel.Cohorts)
		return AppTrackingFunnelCohort{}
	}
	us := cohort("country", "US")
	if us.BaseUsers != 1 || us.D1RetentionUsers != 1 || math.Abs(us.D1RetentionRate-1) > 0.0001 {
		t.Fatalf("unexpected US cohort: %+v", us)
	}
	cohortSteps := func(item AppTrackingFunnelCohort) map[string]int64 {
		steps := map[string]int64{}
		for _, step := range item.Steps {
			steps[step.EventName] = step.UserCount
		}
		return steps
	}
	usSteps := cohortSteps(us)
	if usSteps["login_success"] != 1 || usSteps["room_join_success"] != 1 || usSteps["send_message"] != 1 {
		t.Fatalf("unexpected US cohort steps: %+v", usSteps)
	}
	brSteps := cohortSteps(cohort("country", "BR"))
	if brSteps["login_success"] != 1 || brSteps["room_join_success"] != 0 || brSteps["room_join_fail"] != 1 {
		t.Fatalf("unexpected BR cohort steps: %+v", brSteps)
	}
	// 同一 identity+day 的相同国家只计一次；同日出现两个渠道值时，各渠道仍按既有口径分别计入。
	if cohort("country", "US").BaseUsers != 1 || cohort("channel", "ads").BaseUsers != 1 || cohort("channel", "affiliate").BaseUsers != 1 {
		t.Fatalf("multi-value cohort deduplication mismatch: %+v", funnel.Cohorts)
	}
	stay := cohort("first_room_stay", "3m-10m")
	if stay.BaseUsers != 1 || stay.D1RetentionUsers != 1 {
		t.Fatalf("unexpected stay cohort: %+v", stay)
	}
}

func TestQuerySocialRequirementsAggregatesP0ToP5(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_social_requirements_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	start := time.Date(2026, 7, 1, 0, 0, 0, 0, time.UTC)
	user1, user2 := int64(1001), int64(1002)
	for _, event := range []UserRegisteredEvent{
		{AppCode: "lalu", EventID: "social:user:1001", UserID: user1, Role: "host", CountryID: 86, RegionID: 210, OccurredAtMS: start.UnixMilli()},
		{AppCode: "lalu", EventID: "social:user:1002", UserID: user2, Role: "user", CountryID: 86, RegionID: 210, OccurredAtMS: start.Add(time.Minute).UnixMilli()},
	} {
		if err := repository.ConsumeUserRegistered(ctx, event); err != nil {
			t.Fatalf("consume registration %d: %v", event.UserID, err)
		}
	}
	for _, event := range []AppTrackingEvent{
		{AppCode: "lalu", EventID: "social:first-open:1001", EventName: "app_first_open", UserID: user1, DeviceID: "dev-1001", CountryID: 86, RegionID: 210, Success: true, OccurredAtMS: start.UnixMilli()},
		{AppCode: "lalu", EventID: "social:first-open:1002", EventName: "app_first_open", UserID: user2, DeviceID: "dev-1002", CountryID: 86, RegionID: 210, Success: true, OccurredAtMS: start.UnixMilli()},
		{AppCode: "lalu", EventID: "social:app-session:1001", EventName: "app_session", UserID: user1, DeviceID: "dev-1001", CountryID: 86, RegionID: 210, DurationMS: 600_000, Success: true, OccurredAtMS: start.Add(2 * time.Minute).UnixMilli()},
		{AppCode: "lalu", EventID: "social:room-stay:1001", EventName: "room_stay", UserID: user1, DeviceID: "dev-1001", CountryID: 86, RegionID: 210, DurationMS: 300_000, Success: true, OccurredAtMS: start.Add(3 * time.Minute).UnixMilli()},
		{AppCode: "lalu", EventID: "social:gift-panel:1001", EventName: "gift_panel_open", UserID: user1, DeviceID: "dev-1001", CountryID: 86, RegionID: 210, Success: true, OccurredAtMS: start.Add(4 * time.Minute).UnixMilli()},
		{AppCode: "lalu", EventID: "social:prob-panel:1001", EventName: "probability_game_panel_open", UserID: user1, DeviceID: "dev-1001", CountryID: 86, RegionID: 210, Success: true, OccurredAtMS: start.Add(5 * time.Minute).UnixMilli()},
		{AppCode: "lalu", EventID: "social:chat:1001", EventName: "send_message", UserID: user1, DeviceID: "dev-1001", CountryID: 86, RegionID: 210, Success: true, OccurredAtMS: start.Add(6 * time.Minute).UnixMilli()},
		{AppCode: "lalu", EventID: "social:follow-user:1001", EventName: "follow_host", UserID: user1, DeviceID: "dev-1001", CountryID: 86, RegionID: 210, Success: true, OccurredAtMS: start.Add(7 * time.Minute).UnixMilli()},
		{AppCode: "lalu", EventID: "social:follow-room:1001", EventName: "follow_room", UserID: user1, DeviceID: "dev-1001", CountryID: 86, RegionID: 210, Success: true, OccurredAtMS: start.Add(8 * time.Minute).UnixMilli()},
		{AppCode: "lalu", EventID: "social:d1-active:1001", EventName: "home_view", UserID: user1, DeviceID: "dev-1001", CountryID: 86, RegionID: 210, Success: true, OccurredAtMS: start.Add(24*time.Hour + time.Minute).UnixMilli()},
	} {
		if _, err := repository.ConsumeAppTrackingEvents(appcode.WithContext(ctx, "lalu"), []AppTrackingEvent{event}); err != nil {
			t.Fatalf("consume app event %s: %v", event.EventName, err)
		}
	}
	if err := repository.ConsumeUserActive(ctx, UserActiveEvent{AppCode: "lalu", EventID: "social:room-join:1001", EventType: "RoomUserJoined", UserID: user1, Role: "host", CountryID: 86, RegionID: 210, OccurredAtMS: start.Add(9 * time.Minute).UnixMilli()}); err != nil {
		t.Fatalf("consume room join: %v", err)
	}
	if err := repository.ConsumeUserMicDailyStatsUpdated(ctx, UserMicDailyStatsUpdatedEvent{AppCode: "lalu", EventID: "social:mic:1001", UserID: user1, StatDate: "2026-07-01", MicOnlineDeltaMS: 120_000, OccurredAtMS: start.Add(10 * time.Minute).UnixMilli()}); err != nil {
		t.Fatalf("consume mic: %v", err)
	}
	for _, event := range []RechargeEvent{
		{AppCode: "lalu", EventID: "social:recharge:first", EventType: "WalletRechargeRecorded", UserID: user1, USDMinor: 500, RechargeSequence: 1, TargetCountryID: 86, TargetRegionID: 210, RechargeType: "google", OccurredAtMS: start.Add(11 * time.Minute).UnixMilli()},
		{AppCode: "lalu", EventID: "social:recharge:repeat", EventType: "WalletRechargeRecorded", UserID: user1, USDMinor: 700, RechargeSequence: 2, TargetCountryID: 86, TargetRegionID: 210, RechargeType: "mifapay", OccurredAtMS: start.Add(12 * time.Minute).UnixMilli()},
	} {
		if err := repository.ConsumeRecharge(ctx, event); err != nil {
			t.Fatalf("consume recharge %s: %v", event.EventID, err)
		}
	}
	financeOrder := FinanceCoinSellerRechargeOrderEvent{
		AppCode: "lalu", EventID: "social:finance:coin-seller:verified", VerifyStatus: "verified", TargetUserID: user2,
		USDMinor: 900, CoinAmount: 90_000, CountryID: 86, RegionID: 210, CreatedAtMS: start.Add(12*time.Minute + 30*time.Second).UnixMilli(),
	}
	if err := repository.ConsumeFinanceCoinSellerRechargeOrder(ctx, financeOrder); err != nil {
		t.Fatalf("consume finance coin-seller order: %v", err)
	}
	financeOrder.USDMinor = 1200
	if err := repository.ConsumeFinanceCoinSellerRechargeOrder(ctx, financeOrder); err != nil {
		t.Fatalf("consume duplicate finance coin-seller order: %v", err)
	}
	if err := repository.ConsumeFinanceCoinSellerRechargeOrder(ctx, FinanceCoinSellerRechargeOrderEvent{
		AppCode: "lalu", EventID: "social:finance:coin-seller:pending", VerifyStatus: "pending", TargetUserID: user2,
		USDMinor: 300, CoinAmount: 30_000, CountryID: 86, RegionID: 210, CreatedAtMS: start.Add(12*time.Minute + 45*time.Second).UnixMilli(),
	}); err != nil {
		t.Fatalf("consume pending finance coin-seller order: %v", err)
	}
	for _, event := range []RoomGiftEvent{
		{AppCode: "lalu", EventID: "social:gift:normal", SenderUserID: user1, CountryID: 86, VisibleRegionID: 210, GiftValue: 100, CoinSpent: 100, GiftTypeCode: "normal", OccurredAtMS: start.Add(13 * time.Minute).UnixMilli()},
		{AppCode: "lalu", EventID: "social:gift:lucky", SenderUserID: user1, CountryID: 86, VisibleRegionID: 210, GiftValue: 200, CoinSpent: 200, PoolID: "lucky", GiftTypeCode: "lucky", OccurredAtMS: start.Add(14 * time.Minute).UnixMilli()},
	} {
		if err := repository.ConsumeRoomGift(ctx, event); err != nil {
			t.Fatalf("consume gift %s: %v", event.EventID, err)
		}
	}
	if err := repository.ConsumeLuckyGiftReward(ctx, LuckyGiftRewardEvent{AppCode: "lalu", EventID: "social:lucky:payout", UserID: user1, CountryID: 86, RegionID: 210, PoolID: "lucky", Amount: 50, OccurredAtMS: start.Add(15 * time.Minute).UnixMilli()}); err != nil {
		t.Fatalf("consume lucky payout: %v", err)
	}
	if err := repository.ConsumeGameOrder(ctx, GameOrderEvent{AppCode: "lalu", EventID: "social:probability:bet", UserID: user1, CountryID: 86, RegionID: 210, PlatformCode: "vendor", GameID: "slot", OpType: "bet", CoinAmount: 300, OccurredAtMS: start.Add(16 * time.Minute).UnixMilli()}); err != nil {
		t.Fatalf("consume probability game: %v", err)
	}
	if err := repository.ConsumeSelfGameMatch(ctx, SelfGameMatchEvent{
		AppCode: "lalu", EventID: "social:self-game:settled", EventType: "SelfGameMatchSettled", GameID: "dice", CountryID: 86, RegionID: 210,
		StakeCoin: 400, UserParticipants: 1, UserStakeCoin: 400, Participants: []SelfGameMatchParticipantEvent{{UserID: user1, ParticipantType: "user", StakeCoin: 400, Result: "win"}},
		OccurredAtMS: start.Add(17 * time.Minute).UnixMilli(),
	}); err != nil {
		t.Fatalf("consume self game: %v", err)
	}
	// day2 再押一次 dice：用于验证按游戏的同游戏次留（day1 cohort 1 人，day2 继续押注 → 100%）。
	if err := repository.ConsumeSelfGameMatch(ctx, SelfGameMatchEvent{
		AppCode: "lalu", EventID: "social:self-game:settled-day2", EventType: "SelfGameMatchSettled", GameID: "dice", CountryID: 86, RegionID: 210,
		StakeCoin: 100, UserParticipants: 1, UserStakeCoin: 100, Participants: []SelfGameMatchParticipantEvent{{UserID: user1, ParticipantType: "user", StakeCoin: 100, Result: "lose"}},
		OccurredAtMS: start.Add(24*time.Hour + 30*time.Minute).UnixMilli(),
	}); err != nil {
		t.Fatalf("consume self game day2: %v", err)
	}

	query := SocialRequirementsQuery{
		AppCode:   "lalu",
		StartMS:   start.UnixMilli(),
		EndMS:     start.Add(24 * time.Hour).UnixMilli(),
		RegionIDs: []int64{210},
	}
	result, err := repository.QuerySocialRequirements(ctx, query)
	if err != nil {
		t.Fatalf("query social requirements: %v", err)
	}
	newUsers := socialTestSection(t, result, SocialSectionNewUsers)
	if socialMetricInt(t, newUsers.Total, "app_download_users") != 2 ||
		socialMetricInt(t, newUsers.Total, "registered_users") != 2 ||
		socialMetricInt(t, newUsers.Total, "new_host_users") != 1 ||
		socialMetricInt(t, newUsers.Total, "new_normal_users") != 1 ||
		math.Abs(socialMetricFloat(t, newUsers.Total, "room_join_success_rate")-0.5) > 0.0001 ||
		math.Abs(socialMetricFloat(t, newUsers.Total, "new_host_d1_retention_rate")-1) > 0.0001 ||
		// 新用户平均进房/上麦时长只统计当天注册用户：room_stay 300s/1人=5min，mic 120s/1人=2min。
		math.Abs(socialMetricFloat(t, newUsers.Total, "avg_room_stay_min")-5) > 0.0001 ||
		math.Abs(socialMetricFloat(t, newUsers.Total, "avg_mic_stay_min")-2) > 0.0001 ||
		// 新用户充值金额=全渠道：user1 普通充值 1200 + user2 币商入账 900。
		socialMetricInt(t, newUsers.Total, "new_user_recharge_usd_minor") != 2100 {
		t.Fatalf("P0 metrics mismatch: %+v", newUsers.Total.Metrics)
	}

	revenue := socialTestSection(t, result, SocialSectionRevenue)
	// 文档口径：总充值 2100 = Google 500 + 三方(MiFaPay) 700 + 币商(finance verified) 900。
	if socialMetricInt(t, revenue.Total, "recharge_usd_minor") != 2100 ||
		socialMetricInt(t, revenue.Total, "google_recharge_usd_minor") != 500 ||
		socialMetricInt(t, revenue.Total, "third_party_recharge_usd_minor") != 700 ||
		socialMetricInt(t, revenue.Total, "coin_seller_recharge_usd_minor") != 900 ||
		socialMetricInt(t, revenue.Total, "recharge_count") != 3 ||
		socialMetricInt(t, revenue.Total, "recharge_users") != 1 ||
		math.Abs(socialMetricFloat(t, revenue.Total, "paid_rate")-0.5) > 0.0001 ||
		socialMetricInt(t, revenue.Total, "arppu_usd_minor") != 1200 {
		t.Fatalf("P1 default metrics mismatch: %+v", revenue.Total.Metrics)
	}
	paidRevenue, err := repository.QuerySocialRequirements(ctx, SocialRequirementsQuery{AppCode: "lalu", StartMS: query.StartMS, EndMS: query.EndMS, Section: SocialSectionRevenue, PayerType: "paid"})
	if err != nil {
		t.Fatalf("query revenue requirements: %v", err)
	}
	paidRevenueSection := socialTestSection(t, paidRevenue, SocialSectionRevenue)
	// paid 过滤只剩 user1：Google 500 + MiFaPay(三方) 700；user2 的币商订单不置付费标记被排除。
	if socialMetricInt(t, paidRevenueSection.Total, "recharge_usd_minor") != 1200 ||
		socialMetricInt(t, paidRevenueSection.Total, "google_recharge_usd_minor") != 500 ||
		socialMetricInt(t, paidRevenueSection.Total, "third_party_recharge_usd_minor") != 700 ||
		socialMetricInt(t, paidRevenueSection.Total, "recharge_count") != 2 ||
		socialMetricInt(t, paidRevenueSection.Total, "first_recharge_users") != 1 ||
		socialMetricInt(t, paidRevenueSection.Total, "repeat_recharge_users") != 1 ||
		math.Abs(socialMetricFloat(t, paidRevenueSection.Total, "paid_rate")-1) > 0.0001 {
		t.Fatalf("P1 paid metrics mismatch: %+v", paidRevenueSection.Total.Metrics)
	}

	retention := socialTestSection(t, result, SocialSectionRetention)
	// 用户次留按全体活跃用户 cohort：day1 活跃 user1+user2，day2 仅 user1 回访 → 0.5。
	if socialMetricInt(t, retention.Total, "active_d1_active_base_users") != 2 ||
		socialMetricInt(t, retention.Total, "active_d1_active_users") != 1 ||
		math.Abs(socialMetricFloat(t, retention.Total, "active_d1_active_rate")-0.5) > 0.0001 ||
		math.Abs(socialMetricFloat(t, retention.Total, "after_room_d1_room_rate")-1) > 0.0001 {
		t.Fatalf("P2 retention metrics mismatch: %+v", retention.Total.Metrics)
	}
	active := socialTestSection(t, result, SocialSectionActive)
	if socialMetricInt(t, active.Total, "active_users") != 2 ||
		socialMetricInt(t, active.Total, "room_join_success_users") != 1 ||
		socialMetricInt(t, active.Total, "mic_up_users") != 1 ||
		math.Abs(socialMetricFloat(t, active.Total, "avg_app_stay_min")-5) > 0.0001 {
		t.Fatalf("P2 active metrics mismatch: %+v", active.Total.Metrics)
	}
	gift := socialTestSection(t, result, SocialSectionGift)
	if socialMetricInt(t, gift.Total, "normal_gift_coin") != 100 ||
		socialMetricInt(t, gift.Total, "lucky_gift_coin") != 200 ||
		socialMetricInt(t, gift.Total, "lucky_gift_payout_coin") != 50 ||
		math.Abs(socialMetricFloat(t, gift.Total, "gift_panel_conversion_rate")-1) > 0.0001 ||
		math.Abs(socialMetricFloat(t, gift.Total, "lucky_gift_rtp")-0.25) > 0.0001 {
		t.Fatalf("P4 metrics mismatch: %+v", gift.Total.Metrics)
	}
	game := socialTestSection(t, result, SocialSectionGame)
	if socialMetricInt(t, game.Total, "self_game_bet_coin") != 400 ||
		socialMetricInt(t, game.Total, "probability_game_bet_coin") != 300 ||
		socialMetricInt(t, game.Total, "probability_game_bet_count") != 1 ||
		math.Abs(socialMetricFloat(t, game.Total, "self_game_conversion_rate")-0.5) > 0.0001 ||
		math.Abs(socialMetricFloat(t, game.Total, "probability_game_bet_arppu_coin")-300) > 0.0001 {
		t.Fatalf("P5 metrics mismatch: %+v", game.Total.Metrics)
	}

	// 按游戏明细：dice 自研（day1 押 400，day2 继续押 → 同游戏次留 100%），slot 厂商（300，无次留）。
	if len(game.Games) != 2 {
		t.Fatalf("expected 2 game breakdown rows, got %+v", game.Games)
	}
	gameByName := map[string]SocialRequirementGame{}
	for _, item := range game.Games {
		gameByName[item.GameName] = item
	}
	dice, ok := gameByName["dice"]
	if !ok ||
		socialMetricInt(t, dice.Total, "game_bet_users") != 1 ||
		socialMetricInt(t, dice.Total, "game_bet_coin") != 400 ||
		math.Abs(socialMetricFloat(t, dice.Total, "game_arppu_coin")-400) > 0.0001 ||
		math.Abs(socialMetricFloat(t, dice.Total, "game_d1_retention_rate")-1) > 0.0001 {
		t.Fatalf("dice breakdown mismatch: %+v", dice.Total.Metrics)
	}
	slot, ok := gameByName["slot (vendor)"]
	if !ok ||
		socialMetricInt(t, slot.Total, "game_bet_users") != 1 ||
		socialMetricInt(t, slot.Total, "game_bet_coin") != 300 ||
		math.Abs(socialMetricFloat(t, slot.Total, "game_d1_retention_rate")) > 0.0001 {
		t.Fatalf("slot breakdown mismatch: %+v", slot.Total.Metrics)
	}
	if len(game.GameColumns) == 0 {
		t.Fatalf("expected game breakdown columns")
	}

	// 是否新用户筛选：day1 两人都是当天注册 → new=2、old=0。
	newUserActive, err := repository.QuerySocialRequirements(ctx, SocialRequirementsQuery{AppCode: "lalu", StartMS: query.StartMS, EndMS: query.EndMS, RegionIDs: []int64{210}, Section: SocialSectionActive, NewUserType: "new"})
	if err != nil {
		t.Fatalf("query new-user active requirements: %v", err)
	}
	if socialMetricInt(t, socialTestSection(t, newUserActive, SocialSectionActive).Total, "active_users") != 2 {
		t.Fatalf("new-user filtered DAU mismatch: %+v", socialTestSection(t, newUserActive, SocialSectionActive).Total.Metrics)
	}
	oldUserActive, err := repository.QuerySocialRequirements(ctx, SocialRequirementsQuery{AppCode: "lalu", StartMS: query.StartMS, EndMS: query.EndMS, RegionIDs: []int64{210}, Section: SocialSectionActive, NewUserType: "old"})
	if err != nil {
		t.Fatalf("query old-user active requirements: %v", err)
	}
	if socialMetricInt(t, socialTestSection(t, oldUserActive, SocialSectionActive).Total, "active_users") != 0 {
		t.Fatalf("old-user filtered DAU mismatch: %+v", socialTestSection(t, oldUserActive, SocialSectionActive).Total.Metrics)
	}

	// 平均值行：两天窗口 day1 活跃 2 人、day2 活跃 1 人（user1 home_view）→ 汇总 3 人天、日均 1.5。
	twoDay, err := repository.QuerySocialRequirements(ctx, SocialRequirementsQuery{AppCode: "lalu", StartMS: query.StartMS, EndMS: start.Add(48 * time.Hour).UnixMilli(), RegionIDs: []int64{210}, Section: SocialSectionActive})
	if err != nil {
		t.Fatalf("query two-day active requirements: %v", err)
	}
	twoDayActive := socialTestSection(t, twoDay, SocialSectionActive)
	if socialMetricInt(t, twoDayActive.Total, "active_users") != 3 ||
		math.Abs(socialMetricFloat(t, twoDayActive.Average, "active_users")-1.5) > 0.0001 {
		t.Fatalf("average row mismatch: total=%+v average=%+v", twoDayActive.Total.Metrics, twoDayActive.Average.Metrics)
	}
}

func socialTestSection(t *testing.T, result SocialRequirements, key string) SocialRequirementSection {
	t.Helper()
	for _, section := range result.Sections {
		if section.Key == key {
			return section
		}
	}
	t.Fatalf("missing social section %s in %+v", key, result.Sections)
	return SocialRequirementSection{}
}

func socialMetricInt(t *testing.T, row SocialRequirementRow, key string) int64 {
	t.Helper()
	value, ok := row.Metrics[key]
	if !ok {
		t.Fatalf("missing metric %s in %+v", key, row.Metrics)
	}
	switch typed := value.(type) {
	case int64:
		return typed
	case int:
		return int64(typed)
	case float64:
		return int64(typed)
	default:
		t.Fatalf("metric %s has unexpected type %T", key, value)
		return 0
	}
}

func socialMetricFloat(t *testing.T, row SocialRequirementRow, key string) float64 {
	t.Helper()
	value, ok := row.Metrics[key]
	if !ok {
		t.Fatalf("missing metric %s in %+v", key, row.Metrics)
	}
	switch typed := value.(type) {
	case float64:
		return typed
	case int64:
		return float64(typed)
	case int:
		return float64(typed)
	default:
		t.Fatalf("metric %s has unexpected type %T", key, value)
		return 0
	}
}

func TestConsumeAppTrackingEventsRejectsInvalidProperties(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_app_tracking_invalid_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	for _, test := range []struct {
		name       string
		properties json.RawMessage
	}{
		{name: "invalid json", properties: json.RawMessage(`{"bad"`)},
		{name: "too large json", properties: json.RawMessage(`"` + strings.Repeat("a", appTrackingMaxPropertiesBytes+1) + `"`)},
	} {
		t.Run(test.name, func(t *testing.T) {
			_, err := repository.ConsumeAppTrackingEvents(appcode.WithContext(ctx, "lalu"), []AppTrackingEvent{
				{EventID: "evt-" + test.name, EventName: "open", DeviceID: "dev-1", Properties: test.properties, OccurredAtMS: time.Date(2026, 7, 2, 0, 0, 0, 0, time.UTC).UnixMilli()},
			})
			if err == nil {
				t.Fatalf("invalid properties must be rejected")
			}
		})
	}
}

func TestConsumeUserRegisteredCountsOnlyNewRegistration(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_registration_idempotent_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	event := UserRegisteredEvent{
		AppCode:      "lalu",
		EventID:      "user_registered:327300172394536960",
		UserID:       327300172394536960,
		CountryID:    15,
		RegionID:     10,
		OccurredAtMS: time.Date(2026, 6, 22, 1, 2, 3, 0, time.UTC).UnixMilli(),
	}
	if err := repository.ConsumeUserRegistered(ctx, event); err != nil {
		t.Fatalf("consume registration first time: %v", err)
	}
	// 生产环境会遇到 MQ 重投或历史补偿；注册 cohort 表去重后，国家日新增只能按真实新行累加。
	event.EventID = "user_registered:327300172394536960:replay"
	if err := repository.ConsumeUserRegistered(ctx, event); err != nil {
		t.Fatalf("consume registration replay: %v", err)
	}

	var newUsers, activeUsers int64
	if err := repository.db.QueryRowContext(ctx, `
		SELECT new_users, active_users
		FROM stat_app_day_country
		WHERE app_code = 'lalu' AND stat_tz = 'UTC' AND stat_day = '2026-06-22' AND country_id = 15 AND region_id = 10
	`).Scan(&newUsers, &activeUsers); err != nil {
		t.Fatalf("query app day country: %v", err)
	}
	if newUsers != 1 {
		t.Fatalf("registration replay must not duplicate new users, got %d", newUsers)
	}
	if activeUsers != 1 {
		t.Fatalf("registration must count as one same-day active user, got %d", activeUsers)
	}

	var activeRows int64
	if err := repository.db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM stat_user_day_activity
		WHERE app_code = 'lalu'
		  AND stat_tz = 'UTC'
		  AND stat_day = '2026-06-22'
		  AND country_id = 15
		  AND region_id = 10
		  AND user_id = 327300172394536960
	`).Scan(&activeRows); err != nil {
		t.Fatalf("query registration active row: %v", err)
	}
	if activeRows != 1 {
		t.Fatalf("registration replay must keep one user-day active row, got %d", activeRows)
	}
}

func TestConsumeReportMetricsAggregatesWalletSalaryGrantAndMic(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_report_metrics_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	occurredAt := time.Date(2026, 6, 22, 3, 4, 5, 0, time.UTC).UnixMilli()
	for _, event := range []UserRegisteredEvent{
		{AppCode: "lalu", EventID: "user_registered:report:1", UserID: 7001, CountryID: 15, RegionID: 10, OccurredAtMS: occurredAt},
		{AppCode: "lalu", EventID: "user_registered:report:2", UserID: 7002, CountryID: 15, RegionID: 10, OccurredAtMS: occurredAt},
	} {
		if err := repository.ConsumeUserRegistered(ctx, event); err != nil {
			t.Fatalf("consume user dimension: %v", err)
		}
	}
	for _, event := range []WalletBalanceChangedEvent{
		{AppCode: "lalu", EventID: "balance:coin:recharge", UserID: 7001, AssetType: "COIN", BizType: "google_play_recharge", AvailableDelta: 1000, OccurredAtMS: occurredAt},
		{AppCode: "lalu", EventID: "balance:coin:red-packet-create", UserID: 7001, AssetType: "COIN", BizType: "red_packet_create", AvailableDelta: -200, OccurredAtMS: occurredAt},
		{AppCode: "lalu", EventID: "balance:coin:red-packet-refund", UserID: 7001, AssetType: "COIN", BizType: "red_packet_refund", AvailableDelta: 50, OccurredAtMS: occurredAt},
		{AppCode: "lalu", EventID: "balance:coin:checkin-resource", UserID: 7001, AssetType: "COIN", BizType: "resource_grant", CommandID: "wcheckin_claim_1", AvailableDelta: 66, OccurredAtMS: occurredAt},
		{AppCode: "lalu", EventID: "balance:coin:manual", UserID: 7001, AssetType: "COIN", BizType: "manual_credit", AvailableDelta: 70, OccurredAtMS: occurredAt},
		{AppCode: "lalu", EventID: "balance:salary:host", UserID: 7001, AssetType: "HOST_SALARY_USD", BizType: "host_salary_settlement", AvailableDelta: 123, OccurredAtMS: occurredAt},
		{AppCode: "lalu", EventID: "balance:salary:transfer-debit", UserID: 7001, AssetType: "HOST_SALARY_USD", BizType: "salary_transfer_to_coin_seller", AvailableDelta: -23, OccurredAtMS: occurredAt},
		{AppCode: "lalu", EventID: "balance:salary:coin-seller", UserID: 9001, DimensionUserID: 7001, AssetType: "COIN_SELLER_COIN", BizType: "salary_transfer_to_coin_seller", AvailableDelta: 444, OccurredAtMS: occurredAt},
	} {
		if err := repository.ConsumeWalletBalanceChanged(ctx, event); err != nil {
			t.Fatalf("consume wallet balance changed %s: %v", event.EventID, err)
		}
	}
	if err := repository.ConsumePlatformGrantCoin(ctx, PlatformGrantCoinEvent{
		AppCode: "lalu", EventID: "grant:task:1", EventType: "WalletTaskRewardCredited", UserID: 7001, Amount: 33, OccurredAtMS: occurredAt,
	}); err != nil {
		t.Fatalf("consume platform grant: %v", err)
	}
	if err := repository.ConsumePlatformGrantCoin(ctx, PlatformGrantCoinEvent{
		AppCode: "lalu", EventID: "grant:wheel:1", EventType: "WalletWheelRewardCredited", UserID: 7001, Amount: 40, OccurredAtMS: occurredAt,
	}); err != nil {
		t.Fatalf("consume wheel output: %v", err)
	}
	for _, event := range []UserMicDailyStatsUpdatedEvent{
		{AppCode: "lalu", EventID: "mic:7001:first", UserID: 7001, StatDate: "2026-06-22", MicOnlineDeltaMS: 60_000, OccurredAtMS: occurredAt},
		{AppCode: "lalu", EventID: "mic:7001:second", UserID: 7001, StatDate: "2026-06-22", MicOnlineDeltaMS: 30_000, OccurredAtMS: occurredAt},
		{AppCode: "lalu", EventID: "mic:7002:first", UserID: 7002, StatDate: "2026-06-22", MicOnlineDeltaMS: 30_000, OccurredAtMS: occurredAt},
	} {
		if err := repository.ConsumeUserMicDailyStatsUpdated(ctx, event); err != nil {
			t.Fatalf("consume mic stats %s: %v", event.EventID, err)
		}
	}

	start := time.Date(2026, 6, 22, 0, 0, 0, 0, time.UTC).UnixMilli()
	overview, err := repository.QueryOverview(ctx, OverviewQuery{AppCode: "lalu", StartMS: start, EndMS: start + int64(24*time.Hour/time.Millisecond)})
	if err != nil {
		t.Fatalf("query overview: %v", err)
	}
	if overview.CoinTotal != 986 || overview.ConsumedCoin != 200 || overview.OutputCoin != 90 || overview.ConsumeOutputDelta != 110 || overview.ConsumeOutputRatio != 0.45 || overview.PlatformGrantCoin != 99 || overview.ManualGrantCoin != 70 || overview.SalaryUSDMinor != 100 || overview.SalaryTransferCoin != 444 {
		t.Fatalf("report wallet metrics mismatch: %+v", overview)
	}
	if overview.MicOnlineMS != 120_000 || overview.MicOnlineUsers != 2 || overview.AvgMicOnlineMS != 60_000 {
		t.Fatalf("mic metrics mismatch: %+v", overview)
	}
	if len(overview.CountryBreakdown) != 1 {
		t.Fatalf("country breakdown missing: %+v", overview.CountryBreakdown)
	}
	country := overview.CountryBreakdown[0]
	if country.CoinTotal != 986 || country.ConsumedCoin != 200 || country.OutputCoin != 90 || country.PlatformGrantCoin != 99 || country.SalaryTransferCoin != 444 || country.AvgMicOnlineMS != 60_000 {
		t.Fatalf("country report metrics mismatch: %+v", country)
	}
	if len(overview.DailySeries) != 1 || overview.DailySeries[0].OutputCoin != 90 || overview.DailySeries[0].SalaryTransferCoin != 444 || overview.DailySeries[0].AvgMicOnlineMS != 60_000 {
		t.Fatalf("daily report metrics mismatch: %+v", overview.DailySeries)
	}
}

func TestQueryOverviewUsesEndDayBalanceSnapshotsForRange(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_coin_snapshot_range_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	if _, err := repository.db.ExecContext(ctx, `
		INSERT INTO stat_app_day_country (
			app_code, stat_day, country_id, region_id, coin_total, salary_usd_minor, consumed_coin, output_coin, updated_at_ms
		) VALUES
		  ('lalu', '2026-06-24', 15, 10, 100, 12, 7, 3, 1),
		  ('lalu', '2026-06-25', 15, 10, 250, 34, 11, 4, 1)`); err != nil {
		t.Fatalf("seed coin snapshots: %v", err)
	}

	start := time.Date(2026, 6, 24, 0, 0, 0, 0, time.UTC).UnixMilli()
	overview, err := repository.QueryOverview(ctx, OverviewQuery{AppCode: "lalu", StartMS: start, EndMS: start + int64(48*time.Hour/time.Millisecond), CountryID: 15, RegionID: 10})
	if err != nil {
		t.Fatalf("query overview: %v", err)
	}
	if overview.CoinTotal != 250 || overview.SalaryUSDMinor != 34 || overview.ConsumedCoin != 18 || overview.OutputCoin != 7 {
		t.Fatalf("overview must use end-day balance snapshots and range flow sums: %+v", overview)
	}
	if len(overview.DailySeries) != 2 || overview.DailySeries[0].CoinTotal != 100 || overview.DailySeries[0].SalaryUSDMinor != 12 || overview.DailySeries[1].CoinTotal != 250 || overview.DailySeries[1].SalaryUSDMinor != 34 {
		t.Fatalf("daily series must keep each day's balance snapshots: %+v", overview.DailySeries)
	}
	if len(overview.CountryBreakdown) != 1 || overview.CountryBreakdown[0].CoinTotal != 250 || overview.CountryBreakdown[0].SalaryUSDMinor != 34 {
		t.Fatalf("country breakdown must use end-day balance snapshots: %+v", overview.CountryBreakdown)
	}
}

func TestQueryPlatformGrantCoinDrilldown(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_platform_grant_detail_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	occurredAt := time.Date(2026, 6, 22, 3, 4, 5, 0, time.UTC).UnixMilli()
	for _, event := range []UserRegisteredEvent{
		{AppCode: "lalu", EventID: "user_registered:grant_detail:7001", UserID: 7001, CountryID: 15, RegionID: 10, OccurredAtMS: occurredAt},
		{AppCode: "lalu", EventID: "user_registered:grant_detail:7002", UserID: 7002, CountryID: 15, RegionID: 10, OccurredAtMS: occurredAt},
	} {
		if err := repository.ConsumeUserRegistered(ctx, event); err != nil {
			t.Fatalf("consume user dimension %d: %v", event.UserID, err)
		}
	}
	if err := repository.ConsumePlatformGrantCoin(ctx, PlatformGrantCoinEvent{
		AppCode: "lalu", EventID: "grant:task:7001", EventType: "WalletTaskRewardCredited", UserID: 7001, Amount: 33, OccurredAtMS: occurredAt,
	}); err != nil {
		t.Fatalf("consume task grant: %v", err)
	}
	if err := repository.ConsumeWalletBalanceChanged(ctx, WalletBalanceChangedEvent{
		AppCode: "lalu", EventID: "grant:resource:7001", EventType: "WalletBalanceChanged", UserID: 7001, AssetType: "COIN", BizType: "resource_grant", CommandID: "weekly_star:2026-06-22", AvailableDelta: 7, OccurredAtMS: occurredAt,
	}); err != nil {
		t.Fatalf("consume resource grant: %v", err)
	}
	if err := repository.ConsumeLuckyGiftReward(ctx, LuckyGiftRewardEvent{
		AppCode: "lalu", EventID: "grant:lucky:7001", UserID: 7001, CountryID: 15, RegionID: 10, Amount: 22, OccurredAtMS: occurredAt,
	}); err != nil {
		t.Fatalf("consume lucky grant: %v", err)
	}
	if err := repository.ConsumePlatformGrantCoin(ctx, PlatformGrantCoinEvent{
		AppCode: "lalu", EventID: "grant:task:7002", EventType: "WalletTaskRewardCredited", UserID: 7002, Amount: 12, OccurredAtMS: occurredAt,
	}); err != nil {
		t.Fatalf("consume second task grant: %v", err)
	}

	users, err := repository.QueryPlatformGrantCoinUsers(ctx, PlatformGrantCoinQuery{
		AppCode:   "lalu",
		StatDay:   "2026-06-22",
		CountryID: 15,
		RegionID:  10,
		Page:      1,
		PageSize:  1,
	})
	if err != nil {
		t.Fatalf("query grant users: %v", err)
	}
	if users.Total != 2 || users.Page != 1 || users.PageSize != 1 || len(users.Items) != 1 {
		t.Fatalf("grant user page mismatch: %+v", users)
	}
	if got := users.Items[0]; got.UserID != 7001 || got.TotalCoin != 40 || got.RecordCount != 2 || got.CountryID != 15 || got.RegionID != 10 {
		t.Fatalf("first grant user mismatch: %+v", got)
	}

	records, err := repository.QueryPlatformGrantCoinRecords(ctx, PlatformGrantCoinQuery{
		AppCode:   "lalu",
		StatDay:   "2026-06-22",
		CountryID: 15,
		RegionID:  10,
		UserID:    7001,
		Page:      1,
		PageSize:  20,
	})
	if err != nil {
		t.Fatalf("query grant records: %v", err)
	}
	if records.Total != 2 || len(records.Items) != 2 {
		t.Fatalf("grant record page mismatch: %+v", records)
	}
	amountByType := map[string]int64{}
	for _, item := range records.Items {
		amountByType[item.EventType] += item.Amount
		if item.UserID != 7001 || item.CountryID != 15 || item.RegionID != 10 {
			t.Fatalf("grant record dimension mismatch: %+v", item)
		}
	}
	if amountByType["WalletTaskRewardCredited"] != 33 || amountByType["WalletBalanceChanged"] != 7 || amountByType["WalletLuckyGiftRewardCredited"] != 0 {
		t.Fatalf("grant record sources mismatch: %+v", amountByType)
	}
	taskRecords, err := repository.QueryPlatformGrantCoinRecords(ctx, PlatformGrantCoinQuery{
		AppCode:   "lalu",
		StatDay:   "2026-06-22",
		CountryID: 15,
		RegionID:  10,
		UserID:    7001,
		EventType: "WalletTaskRewardCredited",
		Page:      1,
		PageSize:  20,
	})
	if err != nil {
		t.Fatalf("query source filtered grant records: %v", err)
	}
	if taskRecords.Total != 1 || len(taskRecords.Items) != 1 || taskRecords.Items[0].EventType != "WalletTaskRewardCredited" || taskRecords.Items[0].Amount != 33 {
		t.Fatalf("source filtered grant records mismatch: %+v", taskRecords)
	}
}

func TestConsumeRechargeWritesUTCAndChinaStatDays(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_stat_tz_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	occurredAt := time.Date(2026, 6, 23, 16, 30, 0, 0, time.UTC).UnixMilli()
	if err := repository.ConsumeRecharge(ctx, RechargeEvent{
		AppCode:         "lalu",
		EventID:         "recharge:tz-boundary:1",
		EventType:       "WalletRechargeRecorded",
		UserID:          9001,
		TargetCountryID: 63,
		TargetRegionID:  210,
		USDMinor:        80_000,
		RechargeType:    "mifapay",
		OccurredAtMS:    occurredAt,
	}); err != nil {
		t.Fatalf("consume recharge: %v", err)
	}

	utcStart := time.Date(2026, 6, 23, 0, 0, 0, 0, time.UTC).UnixMilli()
	utcOverview, err := repository.QueryOverview(ctx, OverviewQuery{AppCode: "lalu", StartMS: utcStart, EndMS: utcStart + int64(24*time.Hour/time.Millisecond), CountryID: 63})
	if err != nil {
		t.Fatalf("query utc overview: %v", err)
	}
	if utcOverview.StatTZ != StatTZUTC || utcOverview.RechargeUSDMinor != 80_000 || utcOverview.MifaPayRechargeUSDMinor != 80_000 {
		t.Fatalf("UTC stat day mismatch: %+v", utcOverview)
	}

	beijingStart := time.Date(2026, 6, 23, 16, 0, 0, 0, time.UTC).UnixMilli()
	chinaOverview, err := repository.QueryOverview(ctx, OverviewQuery{AppCode: "lalu", StatTZ: StatTZAsiaShanghai, StartMS: beijingStart, EndMS: beijingStart + int64(24*time.Hour/time.Millisecond), CountryID: 63})
	if err != nil {
		t.Fatalf("query china overview: %v", err)
	}
	if chinaOverview.StatTZ != StatTZAsiaShanghai || chinaOverview.RechargeUSDMinor != 80_000 || len(chinaOverview.DailySeries) != 1 || chinaOverview.DailySeries[0].StatDay != "2026-06-24" {
		t.Fatalf("China stat day mismatch: %+v", chinaOverview)
	}

	nextUTCStart := time.Date(2026, 6, 24, 0, 0, 0, 0, time.UTC).UnixMilli()
	nextUTCOverview, err := repository.QueryOverview(ctx, OverviewQuery{AppCode: "lalu", StartMS: nextUTCStart, EndMS: nextUTCStart + int64(24*time.Hour/time.Millisecond), CountryID: 63})
	if err != nil {
		t.Fatalf("query next utc overview: %v", err)
	}
	if nextUTCOverview.RechargeUSDMinor != 0 {
		t.Fatalf("UTC 2026-06-24 must not include previous UTC day recharge: %+v", nextUTCOverview)
	}
}

func TestConsumeRechargeWithStatTZScopeDoesNotTouchUTC(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_stat_tz_scope_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	occurredAt := time.Date(2026, 6, 23, 16, 30, 0, 0, time.UTC).UnixMilli()
	if err := repository.ConsumeRecharge(WithStatTZScope(ctx, StatTZAsiaShanghai), RechargeEvent{
		AppCode:         "lalu",
		EventID:         "recharge:tz-scope:1",
		EventType:       "WalletRechargeRecorded",
		UserID:          9002,
		TargetCountryID: 63,
		TargetRegionID:  210,
		USDMinor:        90_000,
		RechargeType:    "mifapay",
		OccurredAtMS:    occurredAt,
	}); err != nil {
		t.Fatalf("consume scoped recharge: %v", err)
	}

	utcStart := time.Date(2026, 6, 23, 0, 0, 0, 0, time.UTC).UnixMilli()
	utcOverview, err := repository.QueryOverview(ctx, OverviewQuery{AppCode: "lalu", StartMS: utcStart, EndMS: utcStart + int64(24*time.Hour/time.Millisecond), CountryID: 63})
	if err != nil {
		t.Fatalf("query utc overview: %v", err)
	}
	if utcOverview.RechargeUSDMinor != 0 {
		t.Fatalf("scoped replay must not mutate UTC rows: %+v", utcOverview)
	}

	beijingStart := time.Date(2026, 6, 23, 16, 0, 0, 0, time.UTC).UnixMilli()
	chinaOverview, err := repository.QueryOverview(ctx, OverviewQuery{AppCode: "lalu", StatTZ: StatTZAsiaShanghai, StartMS: beijingStart, EndMS: beijingStart + int64(24*time.Hour/time.Millisecond), CountryID: 63})
	if err != nil {
		t.Fatalf("query china overview: %v", err)
	}
	if chinaOverview.RechargeUSDMinor != 90_000 || chinaOverview.StatTZ != StatTZAsiaShanghai {
		t.Fatalf("scoped replay must write China stat_tz only: %+v", chinaOverview)
	}
}

func TestQueryOverviewReturnsPreviousPeriodDeltaRates(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_query_delta_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	if _, err := repository.db.ExecContext(ctx, `
		INSERT INTO stat_app_day_country (
			app_code, stat_day, country_id, active_users, paid_users, recharge_usd_minor, new_user_recharge_usd_minor,
			mifapay_recharge_usd_minor, gift_coin_spent, coin_seller_transfer_coin, lucky_gift_turnover, lucky_gift_payout,
			game_turnover, game_payout, game_refund, updated_at_ms
		) VALUES
		  ('lalu', '2026-06-05', 210, 10, 2, 100, 50, 100, 300, 80, 100, 50, 500, 100, 50, 1),
		  ('lalu', '2026-06-06', 210, 20, 5, 200, 100, 200, 600, 200, 400, 100, 1000, 400, 100, 1)`); err != nil {
		t.Fatalf("seed delta statistics: %v", err)
	}

	start := time.Date(2026, 6, 6, 0, 0, 0, 0, time.UTC).UnixMilli()
	overview, err := repository.QueryOverview(ctx, OverviewQuery{AppCode: "lalu", StartMS: start, EndMS: start + int64(24*time.Hour/time.Millisecond), CountryID: 210})
	if err != nil {
		t.Fatalf("query overview: %v", err)
	}
	if overview.RechargeDeltaRate != 1 || overview.NewUserRechargeDeltaRate != 1 || overview.ActiveUsersDeltaRate != 1 {
		t.Fatalf("top delta rates mismatch: %+v", overview)
	}
	if overview.PaidUsersDeltaRate != 1.5 || overview.ARPUDeltaRate != 0 || overview.ARPPUDeltaRate != -0.2 {
		t.Fatalf("user delta rates mismatch: %+v", overview)
	}
	if overview.RechargeUsers != overview.PaidUsers || overview.PaidConversionRate != 0.25 || overview.RechargeConversionRate != 0.25 || overview.PayerRateDeltaPP != 5 {
		t.Fatalf("conversion aliases mismatch: %+v", overview)
	}
	if overview.GiftCoinSpentDeltaRate != 1 || overview.CoinSellerTransferDeltaRate != 1.5 || overview.LuckyGiftTurnoverDeltaRate != 3 || overview.LuckyGiftPayoutDeltaRate != 1 || overview.LuckyGiftProfitDeltaRate != 5 {
		t.Fatalf("coin delta rates mismatch: %+v", overview)
	}
	if overview.GameTurnoverDeltaRate != 1 || overview.GameProfitDeltaRate < 0.428 || overview.GameProfitDeltaRate > 0.429 {
		t.Fatalf("game delta rates mismatch: %+v", overview)
	}
}

func TestQueryOverviewReturnsRetentionForAllBreakdowns(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_query_retention_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	today, err := time.Parse("2006-01-02", statDayIn(time.Now().UnixMilli(), StatTZUTC))
	if err != nil {
		t.Fatalf("parse today: %v", err)
	}
	registeredAt := today.AddDate(0, 0, -1)
	registeredDay := registeredAt.Format("2006-01-02")
	retainedDay := today.Format("2006-01-02")

	if _, err := repository.db.ExecContext(ctx, `
		INSERT INTO stat_app_day_country (
			app_code, stat_day, country_id, region_id, new_users, active_users, updated_at_ms
		) VALUES ('lalu', ?, 86, 210, 2, 2, 1)`, registeredDay); err != nil {
		t.Fatalf("seed day country stats: %v", err)
	}
	if _, err := repository.db.ExecContext(ctx, `
		INSERT INTO stat_user_registration (
			app_code, stat_tz, user_id, registered_day, country_id, region_id, registered_at_ms
		) VALUES
		  ('lalu', 'UTC', 9001, ?, 86, 210, 1),
		  ('lalu', 'UTC', 9002, ?, 86, 210, 2)`, registeredDay, registeredDay); err != nil {
		t.Fatalf("seed registrations: %v", err)
	}
	if _, err := repository.db.ExecContext(ctx, `
		INSERT INTO stat_user_day_activity (
			app_code, stat_tz, stat_day, country_id, region_id, user_id, first_active_at_ms
		) VALUES ('lalu', 'UTC', ?, 86, 210, 9001, 3)`, retainedDay); err != nil {
		t.Fatalf("seed retained activity: %v", err)
	}

	overview, err := repository.QueryOverview(ctx, OverviewQuery{
		AppCode:  "lalu",
		StartMS:  registeredAt.UnixMilli(),
		EndMS:    registeredAt.AddDate(0, 0, 1).UnixMilli(),
		RegionID: 210,
	})
	if err != nil {
		t.Fatalf("query overview: %v", err)
	}
	if overview.Retention.RegisteredUsers != 2 {
		t.Fatalf("retention registered users mismatch: %+v", overview.Retention)
	}
	requireInt64Ptr(t, "overview d1 users", overview.Retention.Day1Users, 1)
	requireInt64Ptr(t, "overview d1 base", overview.Retention.Day1BaseUsers, 2)
	requireFloat64Ptr(t, "overview d1 rate", overview.Retention.Day1Rate, 0.5)
	requireNilRetention(t, "overview d7", overview.Retention.Day7Users, overview.Retention.Day7BaseUsers, overview.Retention.Day7Rate)
	requireNilRetention(t, "overview d30", overview.Retention.Day30Users, overview.Retention.Day30BaseUsers, overview.Retention.Day30Rate)

	if len(overview.DailySeries) != 1 {
		t.Fatalf("daily series missing retention day: %+v", overview.DailySeries)
	}
	requireInt64Ptr(t, "daily d1 users", overview.DailySeries[0].D1RetentionUsers, 1)
	requireInt64Ptr(t, "daily d1 base", overview.DailySeries[0].D1RetentionBaseUsers, 2)
	requireFloat64Ptr(t, "daily d1 rate", overview.DailySeries[0].D1RetentionRate, 0.5)
	requireNilRetention(t, "daily d7", overview.DailySeries[0].D7RetentionUsers, overview.DailySeries[0].D7RetentionBaseUsers, overview.DailySeries[0].D7RetentionRate)

	if len(overview.CountryBreakdown) != 1 {
		t.Fatalf("country breakdown missing retention country: %+v", overview.CountryBreakdown)
	}
	requireInt64Ptr(t, "country d1 users", overview.CountryBreakdown[0].D1RetentionUsers, 1)
	requireInt64Ptr(t, "country d1 base", overview.CountryBreakdown[0].D1RetentionBaseUsers, 2)
	requireFloat64Ptr(t, "country d1 rate", overview.CountryBreakdown[0].D1RetentionRate, 0.5)

	if len(overview.DailyCountryBreakdown) != 1 {
		t.Fatalf("daily country breakdown missing retention country: %+v", overview.DailyCountryBreakdown)
	}
	requireInt64Ptr(t, "daily country d1 users", overview.DailyCountryBreakdown[0].D1RetentionUsers, 1)
	requireInt64Ptr(t, "daily country d1 base", overview.DailyCountryBreakdown[0].D1RetentionBaseUsers, 2)
	requireFloat64Ptr(t, "daily country d1 rate", overview.DailyCountryBreakdown[0].D1RetentionRate, 0.5)
}

func TestQueryRetentionCubeRollsUpDailyCountryRows(t *testing.T) {
	ctx := context.Background()
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })
	repository := &Repository{db: db}

	today := statDayIn(time.Now().UnixMilli(), StatTZUTC)
	rows := sqlmock.NewRows([]string{
		"registered_day", "country_id", "registered_users",
		"d1_users", "d1_base", "d7_users", "d7_base", "d30_users", "d30_base",
	}).
		AddRow("2026-06-01", int64(86), int64(2), int64(1), int64(2), int64(0), int64(0), int64(0), int64(0)).
		AddRow("2026-06-01", int64(66), int64(3), int64(0), int64(3), int64(1), int64(3), int64(0), int64(0)).
		AddRow("2026-06-02", int64(86), int64(4), int64(2), int64(4), int64(0), int64(0), int64(0), int64(0))
	mock.ExpectQuery("SELECT DATE_FORMAT").
		WithArgs(today, today, today, today, today, today, "lalu", StatTZUTC, "2026-06-01", "2026-06-02", int64(210)).
		WillReturnRows(rows)

	cube, err := repository.queryRetentionCube(ctx, "lalu", StatTZUTC, "2026-06-01", "2026-06-02", 0, 210)
	if err != nil {
		t.Fatalf("query retention cube: %v", err)
	}
	if cube.Total.RegisteredUsers != 9 || cube.Total.D1Users != 3 || cube.Total.D1Base != 9 || cube.Total.D7Users != 1 || cube.Total.D7Base != 3 {
		t.Fatalf("total retention rollup mismatch: %+v", cube.Total)
	}
	retention := cube.Total.toRetention()
	requireInt64Ptr(t, "total d1 users", retention.Day1Users, 3)
	requireFloat64Ptr(t, "total d1 rate", retention.Day1Rate, 1.0/3.0)
	requireNilRetention(t, "total d30", retention.Day30Users, retention.Day30BaseUsers, retention.Day30Rate)

	day := cube.ByDay["2026-06-01"]
	if day.RegisteredUsers != 5 || day.D1Users != 1 || day.D1Base != 5 || day.D7Users != 1 || day.D7Base != 3 {
		t.Fatalf("day retention rollup mismatch: %+v", day)
	}
	country := cube.ByCountry[86]
	if country.RegisteredUsers != 6 || country.D1Users != 3 || country.D1Base != 6 {
		t.Fatalf("country retention rollup mismatch: %+v", country)
	}
	dayCountry := cube.ByDayCountry[dailyCountryKey("2026-06-01", 66)]
	if dayCountry.RegisteredUsers != 3 || dayCountry.D1Users != 0 || dayCountry.D1Base != 3 || dayCountry.D7Users != 1 || dayCountry.D7Base != 3 {
		t.Fatalf("daily country retention rollup mismatch: %+v", dayCountry)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestQueryOverviewReturnsRealRoomRobotGiftStats(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_query_robot_gift_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	if _, err := repository.db.ExecContext(ctx, `
		INSERT INTO stat_real_room_robot_gift_day_rooms (
			app_code, stat_day, country_id, region_id, room_id,
			normal_gift_coin, lucky_gift_coin, super_lucky_gift_coin, updated_at_ms
		) VALUES
		  ('lalu', '2026-06-06', 210, 8, 'room-a', 100, 200, 0, 1),
		  ('lalu', '2026-06-06', 210, 8, 'room-b', 0, 0, 300, 1),
		  ('lalu', '2026-06-06', 211, 9, 'room-c', 900, 0, 0, 1)`); err != nil {
		t.Fatalf("seed real room robot gifts: %v", err)
	}

	start := time.Date(2026, 6, 6, 0, 0, 0, 0, time.UTC).UnixMilli()
	overview, err := repository.QueryOverview(ctx, OverviewQuery{AppCode: "lalu", StartMS: start, EndMS: start + int64(24*time.Hour/time.Millisecond), CountryID: 210, RegionID: 8})
	if err != nil {
		t.Fatalf("query overview: %v", err)
	}
	if overview.RealRoomRobotNormalGiftCoin != 100 || overview.RealRoomRobotLuckyGiftCoin != 200 || overview.RealRoomRobotSuperGiftCoin != 300 {
		t.Fatalf("robot gift breakdown mismatch: %+v", overview)
	}
	if overview.RealRoomRobotTotalGiftCoin != 600 || overview.RealRoomRobotGiftRoomCount != 2 || overview.RealRoomRobotAvgRoomGiftCoin != 300 {
		t.Fatalf("robot gift aggregate mismatch: %+v", overview)
	}
}

func TestQueryOverviewReturnsCountryBreakdownAndIncludesCoinSellerRecharge(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_query_country_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	if _, err := repository.db.ExecContext(ctx, `
		INSERT INTO stat_app_day_country (
			app_code, stat_day, country_id, region_id, active_users, paid_users, recharge_usd_minor,
			coin_seller_recharge_usd_minor, coin_seller_stock_coin, google_recharge_usd_minor, updated_at_ms
		) VALUES
		  ('lalu', '2026-06-06', 86, 210, 10, 2, 150, 400, 880000, 150, 1),
		  ('lalu', '2026-06-06', 66, 210, 5, 1, 100, 0, 0, 100, 1),
		  ('lalu', '2026-06-06', 99, 220, 3, 1, 900, 0, 0, 900, 1)`); err != nil {
		t.Fatalf("seed country statistics: %v", err)
	}

	start := time.Date(2026, 6, 6, 0, 0, 0, 0, time.UTC).UnixMilli()
	overview, err := repository.QueryOverview(ctx, OverviewQuery{AppCode: "lalu", StartMS: start, EndMS: start + int64(24*time.Hour/time.Millisecond), RegionID: 210})
	if err != nil {
		t.Fatalf("query overview: %v", err)
	}
	if overview.RechargeUSDMinor != 650 || overview.CoinSellerRechargeUSDMinor != 400 || overview.CoinSellerStockCoin != 880000 {
		t.Fatalf("overview recharge mismatch: %+v", overview)
	}
	if overview.RechargeUsers != 3 || overview.PaidConversionRate != 0.2 || overview.RechargeConversionRate != 0.2 {
		t.Fatalf("overview conversion fields mismatch: %+v", overview)
	}
	if len(overview.CountryBreakdown) != 2 {
		t.Fatalf("country breakdown should come from aggregate query: %+v", overview.CountryBreakdown)
	}
	if overview.CountryBreakdown[0].CountryID != 86 || overview.CountryBreakdown[0].RechargeUSDMinor != 550 || overview.CountryBreakdown[0].CoinSellerStockCoin != 880000 || overview.CountryBreakdown[0].ARPPUUSDMinor != 275 {
		t.Fatalf("first country breakdown mismatch: %+v", overview.CountryBreakdown[0])
	}
	if overview.CountryBreakdown[0].RechargeUsers != 2 || overview.CountryBreakdown[0].PaidConversionRate != 0.2 || overview.CountryBreakdown[0].RechargeConversionRate != 0.2 {
		t.Fatalf("first country conversion fields mismatch: %+v", overview.CountryBreakdown[0])
	}
}

func TestQueryOverviewReturnsDailyCountryBreakdownAndSuperLuckyGift(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_query_daily_country_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	if _, err := repository.db.ExecContext(ctx, `
		INSERT INTO stat_app_day_country (
			app_code, stat_day, country_id, region_id, active_users, paid_users, recharge_usd_minor,
			coin_seller_stock_coin, lucky_gift_turnover, lucky_gift_payout, updated_at_ms
		) VALUES
		  ('lalu', '2026-06-05', 86, 210, 10, 2, 100, 7000, 900, 200, 1),
		  ('lalu', '2026-06-05', 66, 210, 5, 1, 80, 1000, 100, 20, 1),
		  ('lalu', '2026-06-06', 86, 210, 12, 3, 150, 9000, 1200, 300, 1)`); err != nil {
		t.Fatalf("seed app country stats: %v", err)
	}
	if _, err := repository.db.ExecContext(ctx, `
		INSERT INTO stat_lucky_gift_pool_day_country (
			app_code, stat_day, country_id, region_id, pool_id, turnover_coin, payout_coin, updated_at_ms
		) VALUES
		  ('lalu', '2026-06-05', 86, 210, 'super_lucky', 700, 120, 1),
		  ('lalu', '2026-06-05', 66, 210, 'lucky', 100, 20, 1),
		  ('lalu', '2026-06-06', 86, 210, 'super_lucky_v2', 900, 180, 1)`); err != nil {
		t.Fatalf("seed lucky gift pools: %v", err)
	}

	start := time.Date(2026, 6, 5, 0, 0, 0, 0, time.UTC).UnixMilli()
	overview, err := repository.QueryOverview(ctx, OverviewQuery{AppCode: "lalu", StartMS: start, EndMS: start + int64(48*time.Hour/time.Millisecond), RegionID: 210})
	if err != nil {
		t.Fatalf("query overview: %v", err)
	}
	if overview.SuperLuckyGiftTurnover != 1600 || overview.SuperLuckyGiftPayout != 300 || overview.SuperLuckyGiftProfit != 1300 {
		t.Fatalf("super lucky overview mismatch: %+v", overview)
	}
	if len(overview.DailyCountryBreakdown) != 3 {
		t.Fatalf("daily country breakdown length mismatch: %+v", overview.DailyCountryBreakdown)
	}
	first := overview.DailyCountryBreakdown[0]
	if first.StatDay != "2026-06-05" || first.CountryID != 86 || first.CoinSellerStockCoin != 7000 || first.SuperLuckyGiftTurnover != 700 || first.SuperLuckyGiftProfitRate <= 0 {
		t.Fatalf("first daily country row mismatch: %+v", first)
	}
	if first.RechargeUsers != 2 || first.PaidConversionRate != 0.2 || first.RechargeConversionRate != 0.2 {
		t.Fatalf("first daily country conversion fields mismatch: %+v", first)
	}
	if len(overview.ReportMetricSources) == 0 {
		t.Fatalf("report metric sources should document available and missing report fields")
	}
}

func TestQuerySelfGameOverviewUsesRealUserStakeForPlatformProfitRate(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_self_game_profit_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	if _, err := repository.db.ExecContext(ctx, `
		INSERT INTO stat_self_game_match_day (
			app_code, stat_day, game_id, stake_coin, settled_matches,
			user_stake_coin, robot_stake_coin, payout_coin, refund_coin, platform_profit_coin, updated_at_ms
		) VALUES
		  ('lalu', '2026-06-06', 'dice', 100, 1, 1000, 1000, 700, 100, 200, 1),
		  ('lalu', '2026-06-06', 'dice', 500, 1, 2000, 2000, 1500, 100, 400, 1)`); err != nil {
		t.Fatalf("seed self game match stats: %v", err)
	}

	start := time.Date(2026, 6, 6, 0, 0, 0, 0, time.UTC).UnixMilli()
	overview, err := repository.QuerySelfGameOverview(ctx, SelfGameOverviewQuery{AppCode: "lalu", StartMS: start, EndMS: start + int64(24*time.Hour/time.Millisecond), GameID: "dice"})
	if err != nil {
		t.Fatalf("query self game overview: %v", err)
	}
	if overview.MatchTotals.PlatformProfitRate != 0.2 {
		t.Fatalf("platform profit rate should use real user stake only: %+v", overview.MatchTotals)
	}
	if len(overview.StakeBreakdown) != 2 || overview.StakeBreakdown[0].PlatformProfitRate != 0.2 || overview.StakeBreakdown[1].PlatformProfitRate != 0.2 {
		t.Fatalf("stake platform profit rate should use real user stake only: %+v", overview.StakeBreakdown)
	}
}

func TestQuerySelfGameOverviewKeepsParticipantWinRateInStakeBucket(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_self_game_participant_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	if _, err := repository.db.ExecContext(ctx, `
		INSERT INTO stat_self_game_participant_day (
			app_code, stat_day, game_id, stake_coin, participant_type, result, dice_point, participant_count, updated_at_ms
		) VALUES
		  ('lalu', '2026-06-06', 'dice', 100, 'user', 'win', 6, 1, 1),
		  ('lalu', '2026-06-06', 'dice', 100, 'user', 'lose', 6, 3, 1),
		  ('lalu', '2026-06-06', 'dice', 500, 'user', 'win', 6, 4, 1)`); err != nil {
		t.Fatalf("seed self game participant stats: %v", err)
	}

	start := time.Date(2026, 6, 6, 0, 0, 0, 0, time.UTC).UnixMilli()
	overview, err := repository.QuerySelfGameOverview(ctx, SelfGameOverviewQuery{AppCode: "lalu", StartMS: start, EndMS: start + int64(24*time.Hour/time.Millisecond), GameID: "dice"})
	if err != nil {
		t.Fatalf("query self game overview: %v", err)
	}
	rates := map[int64]float64{}
	for _, item := range overview.ParticipantDistribution {
		if item.Result == "win" {
			rates[item.StakeCoin] = item.WinRate
		}
	}
	if rates[100] != 0.25 || rates[500] != 1 {
		t.Fatalf("participant win rate should stay in stake bucket: %+v", overview.ParticipantDistribution)
	}
}

func TestQuerySelfGameOverviewUsesLatestPoolBalanceSnapshot(t *testing.T) {
	ctx := context.Background()
	_, file, _, _ := runtime.Caller(0)
	statsSchema := mysqlschema.New(t, mysqlschema.Config{
		InitDBPath:     mysqlschema.InitDBPath(t, file, "..", "..", "..", "deploy", "mysql", "initdb", "001_statistics_service.sql"),
		DatabasePrefix: "hy_stats_self_game_pool_test",
	})
	repository, err := Open(ctx, statsSchema.DSN)
	if err != nil {
		t.Fatalf("open repository: %v", err)
	}
	t.Cleanup(func() { _ = repository.Close() })

	if _, err := repository.db.ExecContext(ctx, `
		INSERT INTO stat_self_game_pool_day (
			app_code, stat_day, game_id, direction, reason, transaction_count, in_coin, out_coin, balance_after_coin, updated_at_ms
		) VALUES
		  ('lalu', '2026-06-05', 'dice', 'out', 'dice_settlement', 1, 0, 100, 5000, 1),
		  ('lalu', '2026-06-06', 'dice', 'out', 'dice_settlement', 1, 0, 200, 4800, 2)`); err != nil {
		t.Fatalf("seed self game pool stats: %v", err)
	}

	start := time.Date(2026, 6, 5, 0, 0, 0, 0, time.UTC).UnixMilli()
	overview, err := repository.QuerySelfGameOverview(ctx, SelfGameOverviewQuery{AppCode: "lalu", StartMS: start, EndMS: start + int64(48*time.Hour/time.Millisecond), GameID: "dice"})
	if err != nil {
		t.Fatalf("query self game overview: %v", err)
	}
	if len(overview.PoolStats) != 1 || overview.PoolStats[0].OutCoin != 300 || overview.PoolStats[0].BalanceAfterCoin != 4800 {
		t.Fatalf("pool balance should be latest snapshot, not max balance: %+v", overview.PoolStats)
	}
}

func requireInt64Ptr(t *testing.T, name string, got *int64, want int64) {
	t.Helper()
	if got == nil || *got != want {
		t.Fatalf("%s mismatch: got=%v want=%d", name, got, want)
	}
}

func requireFloat64Ptr(t *testing.T, name string, got *float64, want float64) {
	t.Helper()
	if got == nil || math.Abs(*got-want) > 0.000001 {
		t.Fatalf("%s mismatch: got=%v want=%f", name, got, want)
	}
}

func requireNilRetention(t *testing.T, name string, users *int64, base *int64, rate *float64) {
	t.Helper()
	if users != nil || base != nil || rate != nil {
		t.Fatalf("%s should be nil before cohort matures: users=%v base=%v rate=%v", name, users, base, rate)
	}
}