package repository

import (
	"context"
	"os"
	"path/filepath"
	"strings"
	"testing"
	"time"

	"billing/internal/collection"
)

func TestSQLiteUsageQueryAtMillionRows(t *testing.T) {
	if os.Getenv("CPA_EXT_MILLION_TEST") != "1" {
		t.Skip("set CPA_EXT_MILLION_TEST=1 to run the million-row query check")
	}
	store, err := OpenSQLiteUsage(filepath.Join(t.TempDir(), "usage.db"))
	if err != nil {
		t.Fatal(err)
	}
	defer store.Close()
	started := time.Now()
	_, err = store.db.Exec(`
WITH RECURSIVE sequence(value) AS (
  SELECT 1 UNION ALL SELECT value+1 FROM sequence WHERE value<1000000
)
INSERT INTO request_detail_index
  (lifecycle_request_id, requested_at, managed_key_id, model, result, auth_id, endpoint_kind, request_id)
SELECT 'load-' || value,
       printf('2026-08-%02dT%02d:%02d:%02d.000000000Z', 1 + ((value / 86400) % 15), (value / 3600) % 24, (value / 60) % 60, value % 60),
       'key-' || (value % 5), 'model-' || (value % 3),
       CASE WHEN value % 17=0 THEN 'failed' ELSE 'succeeded' END,
       'auth-' || (value % 2), 'responses', 'load-' || value
FROM sequence`)
	if err != nil {
		t.Fatal(err)
	}
	t.Logf("seeded one million indexed details in %s", time.Since(started))

	started = time.Now()
	page, err := store.QueryUsage(context.Background(), collection.UsageQuery{Page: 600, PageSize: 100, KeyID: "key-1", Model: "model-1"})
	if err != nil {
		t.Fatal(err)
	}
	if page.Total == 0 || len(page.Records) == 0 {
		t.Fatalf("deep filtered page is empty: %+v", page)
	}
	t.Logf("counted and loaded deep filtered page in %s", time.Since(started))

	rows, err := store.db.Query(`EXPLAIN QUERY PLAN SELECT id FROM request_detail_index d WHERE d.managed_key_id=? ORDER BY d.requested_at DESC, d.id DESC LIMIT 100 OFFSET 900000`, "key-1")
	if err != nil {
		t.Fatal(err)
	}
	defer rows.Close()
	var plan strings.Builder
	for rows.Next() {
		var id, parent, unused int
		var detail string
		if err := rows.Scan(&id, &parent, &unused, &detail); err != nil {
			t.Fatal(err)
		}
		plan.WriteString(detail)
	}
	if !strings.Contains(plan.String(), "idx_request_detail_key") {
		t.Fatalf("key query did not use projection index: %s", plan.String())
	}
}