pangolin/server/internal/store/sqlite_per_device_test.go

package store_test

import (
	"context"
	"database/sql"
	"testing"
	"time"

	"github.com/wangjia/pangolin/server/internal/nodes"
	"github.com/wangjia/pangolin/server/internal/usage"
)

// seedDevice inserts a device row with a NULL dp_uuid (awaiting lazy mint).
func seedDevice(t *testing.T, db *sql.DB, id, userID int64, uuid string) {
	t.Helper()
	if _, err := db.Exec(
		`INSERT INTO devices (id, uuid, user_id, name, platform, created_at)
		 VALUES (?, ?, ?, ?, 'ios', ?)`,
		id, uuid, userID, "dev"+uuid, time.Now().UTC()); err != nil {
		t.Fatalf("seed device %s: %v", uuid, err)
	}
}

// EnsureDeviceDpUUID mints a per-device dp_uuid, is idempotent for the same
// device, and yields distinct credentials for distinct devices.
func TestSQLite_EnsureDeviceDpUUID(t *testing.T) {
	ctx := context.Background()
	db := openSQLite(t)
	ns := nodes.NewSQLNodeStore(db)
	seedUser(t, db, 1)
	seedDevice(t, db, 10, 1, "dev-a")
	seedDevice(t, db, 11, 1, "dev-b")

	dpA, idA, err := ns.EnsureDeviceDpUUID(ctx, 1, "dev-a")
	if err != nil {
		t.Fatalf("ensure dev-a: %v", err)
	}
	if dpA == "" || idA != 10 {
		t.Fatalf("dev-a: dp=%q id=%d, want non-empty/10", dpA, idA)
	}

	// Idempotent: a second call returns the same dp_uuid (no re-mint).
	dpA2, idA2, err := ns.EnsureDeviceDpUUID(ctx, 1, "dev-a")
	if err != nil {
		t.Fatalf("ensure dev-a again: %v", err)
	}
	if dpA2 != dpA || idA2 != 10 {
		t.Errorf("not idempotent: dp=%q (want %q) id=%d", dpA2, dpA, idA2)
	}

	// Distinct device → distinct dp_uuid.
	dpB, idB, err := ns.EnsureDeviceDpUUID(ctx, 1, "dev-b")
	if err != nil {
		t.Fatalf("ensure dev-b: %v", err)
	}
	if dpB == dpA {
		t.Errorf("dev-b dp_uuid collides with dev-a: %q", dpB)
	}
	if idB != 11 {
		t.Errorf("dev-b id=%d, want 11", idB)
	}

	// Persisted to devices.dp_uuid.
	var stored string
	if err := db.QueryRow(`SELECT dp_uuid FROM devices WHERE id=10`).Scan(&stored); err != nil {
		t.Fatalf("read stored dp_uuid: %v", err)
	}
	if stored != dpA {
		t.Errorf("stored dp_uuid=%q, want %q", stored, dpA)
	}

	// Unknown device → error (not silently minted against nothing).
	if _, _, err := ns.EnsureDeviceDpUUID(ctx, 1, "ghost"); err == nil {
		t.Errorf("expected error for unknown device")
	}
}

// UserDeviceByDpUUID prefers the per-device credential and falls back to the
// legacy account-level users.dp_uuid (deviceID=0).
func TestSQLite_UserDeviceByDpUUID(t *testing.T) {
	ctx := context.Background()
	db := openSQLite(t)
	ns := nodes.NewSQLNodeStore(db)
	seedUser(t, db, 1) // seedUser sets users.dp_uuid='dp-u'
	seedDevice(t, db, 10, 1, "dev-a")

	dpA, _, err := ns.EnsureDeviceDpUUID(ctx, 1, "dev-a")
	if err != nil {
		t.Fatalf("ensure: %v", err)
	}

	// Per-device resolution.
	uid, did, ok, err := ns.UserDeviceByDpUUID(ctx, dpA)
	if err != nil || !ok {
		t.Fatalf("resolve device dp: ok=%v err=%v", ok, err)
	}
	if uid != 1 || did != 10 {
		t.Errorf("device dp → user=%d device=%d, want 1/10", uid, did)
	}

	// Legacy account fallback (no device dimension).
	uid, did, ok, err = ns.UserDeviceByDpUUID(ctx, "dp-u")
	if err != nil || !ok {
		t.Fatalf("resolve account dp: ok=%v err=%v", ok, err)
	}
	if uid != 1 || did != 0 {
		t.Errorf("account dp → user=%d device=%d, want 1/0", uid, did)
	}

	// Unknown dp_uuid.
	if _, _, ok, _ := ns.UserDeviceByDpUUID(ctx, "nope"); ok {
		t.Errorf("unknown dp_uuid resolved unexpectedly")
	}
}

// AccumulateDeviceUsage sums within (device, date) and keeps devices separate.
func TestSQLite_AccumulateDeviceUsage(t *testing.T) {
	ctx := context.Background()
	db := openSQLite(t)
	ns := nodes.NewSQLNodeStore(db)
	seedUser(t, db, 1)
	seedDevice(t, db, 10, 1, "dev-a")
	seedDevice(t, db, 11, 1, "dev-b")
	day := time.Date(2026, 6, 24, 0, 0, 0, 0, time.UTC)

	if err := ns.AccumulateDeviceUsage(ctx, 1, 10, day, 100, 200, 1); err != nil {
		t.Fatalf("accum a1: %v", err)
	}
	if err := ns.AccumulateDeviceUsage(ctx, 1, 10, day, 50, 25, 2); err != nil {
		t.Fatalf("accum a2: %v", err)
	}
	if err := ns.AccumulateDeviceUsage(ctx, 1, 11, day, 7, 8, 9); err != nil {
		t.Fatalf("accum b1: %v", err)
	}

	var up, down, mins int64
	if err := db.QueryRow(
		`SELECT bytes_up, bytes_down, minutes_used FROM usage_device_daily WHERE device_id=10`,
	).Scan(&up, &down, &mins); err != nil {
		t.Fatalf("read dev-a: %v", err)
	}
	if up != 150 || down != 225 || mins != 3 {
		t.Errorf("dev-a usage up=%d down=%d mins=%d, want 150/225/3", up, down, mins)
	}

	if err := db.QueryRow(
		`SELECT bytes_up, bytes_down, minutes_used FROM usage_device_daily WHERE device_id=11`,
	).Scan(&up, &down, &mins); err != nil {
		t.Fatalf("read dev-b: %v", err)
	}
	if up != 7 || down != 8 || mins != 9 {
		t.Errorf("dev-b usage up=%d down=%d mins=%d, want 7/8/9", up, down, mins)
	}
}

// AccountDayBytes returns the account's total (up+down) bytes for the day,
// the basis for the GB 综合配额 connect gate.
func TestSQLite_AccountDayBytes(t *testing.T) {
	ctx := context.Background()
	db := openSQLite(t)
	ns := nodes.NewSQLNodeStore(db)
	seedUser(t, db, 1)
	day := time.Date(2026, 6, 24, 0, 0, 0, 0, time.UTC)
	other := time.Date(2026, 6, 23, 0, 0, 0, 0, time.UTC)

	// No usage yet → 0.
	if b, err := ns.AccountDayBytes(ctx, 1, day); err != nil || b != 0 {
		t.Fatalf("empty: bytes=%d err=%v, want 0/nil", b, err)
	}

	if err := ns.AccumulateUsage(ctx, 1, day, 300, 700, 5); err != nil {
		t.Fatalf("accum: %v", err)
	}
	if err := ns.AccumulateUsage(ctx, 1, day, 100, 100, 1); err != nil {
		t.Fatalf("accum2: %v", err)
	}
	if b, err := ns.AccountDayBytes(ctx, 1, day); err != nil || b != 1200 {
		t.Errorf("day bytes=%d err=%v, want 1200", b, err)
	}
	// A different date is isolated.
	if b, err := ns.AccountDayBytes(ctx, 1, other); err != nil || b != 0 {
		t.Errorf("other day bytes=%d, want 0", b)
	}
}

// DeviceUsageRange aggregates usage_device_daily per device over a window,
// joins devices for display metadata, sums across days within the window,
// excludes out-of-window dates and zero-usage devices, and orders busiest-first.
func TestSQLite_DeviceUsageRange(t *testing.T) {
	ctx := context.Background()
	db := openSQLite(t)
	ns := nodes.NewSQLNodeStore(db)
	us := usage.NewStore(db)
	seedUser(t, db, 1)
	seedDevice(t, db, 10, 1, "dev-a")
	seedDevice(t, db, 11, 1, "dev-b")
	seedDevice(t, db, 12, 1, "dev-quiet") // no usage → omitted

	from := time.Date(2026, 6, 20, 0, 0, 0, 0, time.UTC)
	to := time.Date(2026, 6, 24, 0, 0, 0, 0, time.UTC)
	inWindow1 := time.Date(2026, 6, 21, 0, 0, 0, 0, time.UTC)
	inWindow2 := time.Date(2026, 6, 23, 0, 0, 0, 0, time.UTC)
	outWindow := time.Date(2026, 6, 19, 0, 0, 0, 0, time.UTC)

	// dev-a: two in-window days summed + one out-of-window day excluded.
	if err := ns.AccumulateDeviceUsage(ctx, 1, 10, inWindow1, 1000, 2000, 5); err != nil {
		t.Fatalf("accum a1: %v", err)
	}
	if err := ns.AccumulateDeviceUsage(ctx, 1, 10, inWindow2, 500, 500, 3); err != nil {
		t.Fatalf("accum a2: %v", err)
	}
	if err := ns.AccumulateDeviceUsage(ctx, 1, 10, outWindow, 9999, 9999, 99); err != nil {
		t.Fatalf("accum a-out: %v", err)
	}
	// dev-b: smaller total (so it sorts after dev-a).
	if err := ns.AccumulateDeviceUsage(ctx, 1, 11, inWindow1, 100, 100, 1); err != nil {
		t.Fatalf("accum b1: %v", err)
	}

	rows, err := us.DeviceUsageRange(ctx, 1, from, to)
	if err != nil {
		t.Fatalf("DeviceUsageRange: %v", err)
	}
	if len(rows) != 2 {
		t.Fatalf("got %d device rows, want 2 (dev-quiet omitted)", len(rows))
	}

	// Busiest first: dev-a (4000 bytes) before dev-b (200 bytes).
	if rows[0].UUID != "dev-a" {
		t.Errorf("rows[0].UUID=%q, want dev-a (busiest first)", rows[0].UUID)
	}
	a := rows[0]
	if a.BytesUp != 1500 || a.BytesDown != 2500 || a.MinutesUsed != 8 {
		t.Errorf("dev-a sums up=%d down=%d min=%d, want 1500/2500/8 (out-of-window excluded)",
			a.BytesUp, a.BytesDown, a.MinutesUsed)
	}
	if a.Name != "devdev-a" || a.Platform != "ios" {
		t.Errorf("dev-a join meta name=%q platform=%q, want devdev-a/ios", a.Name, a.Platform)
	}
	if rows[1].UUID != "dev-b" || rows[1].BytesUp != 100 || rows[1].BytesDown != 100 {
		t.Errorf("dev-b row wrong: %+v", rows[1])
	}

	// A different user sees none of this account's devices.
	if _, err := db.Exec(
		`INSERT INTO users (id, uuid, email, pw_hash, dp_uuid, status, created_at)
		 VALUES (2, 'u2-uuid', 'u2@example.com', 'x', 'dp-u2', 'active', ?)`,
		time.Now().UTC()); err != nil {
		t.Fatalf("seed user2: %v", err)
	}
	other, err := us.DeviceUsageRange(ctx, 2, from, to)
	if err != nil {
		t.Fatalf("DeviceUsageRange user2: %v", err)
	}
	if len(other) != 0 {
		t.Errorf("user2 leaked %d rows, want 0", len(other))
	}
}

// EntitlementForUser loads daily_mb: free fallback = 500; an active pro
// subscription = 102400 (from the 000015 seed).
func TestSQLite_EntitlementForUser_DailyMB(t *testing.T) {
	ctx := context.Background()
	db := openSQLite(t)
	ns := nodes.NewSQLNodeStore(db)
	seedUser(t, db, 1)

	// No subscription → free defaults.
	ent, err := ns.EntitlementForUser(ctx, 1)
	if err != nil || ent == nil {
		t.Fatalf("entitlement: %v", err)
	}
	if !ent.AdGate || !ent.DailyMB.Valid || ent.DailyMB.Int64 != 500 {
		t.Errorf("free: adGate=%v dailyMB=%v/%d, want true/valid/500",
			ent.AdGate, ent.DailyMB.Valid, ent.DailyMB.Int64)
	}

	// Active pro subscription (plan_id=2 from seed) → daily_mb 102400, no ad gate.
	if _, err := db.Exec(
		`INSERT INTO subscriptions (user_id, plan_id, expires_at, source, created_at)
		 VALUES (1, 2, ?, 'code', ?)`,
		time.Now().UTC().Add(24*time.Hour), time.Now().UTC()); err != nil {
		t.Fatalf("seed subscription: %v", err)
	}
	ent, err = ns.EntitlementForUser(ctx, 1)
	if err != nil || ent == nil {
		t.Fatalf("entitlement pro: %v", err)
	}
	if ent.AdGate || !ent.DailyMB.Valid || ent.DailyMB.Int64 != 102400 {
		t.Errorf("pro: adGate=%v dailyMB=%v/%d, want false/valid/102400",
			ent.AdGate, ent.DailyMB.Valid, ent.DailyMB.Int64)
	}
}