package topsqlv2

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"io"
	"net/http"
	"net/http/httptest"
	"os"
	"strings"
	"testing"
	"time"

	"github.com/gin-gonic/gin"
	"github.com/stretchr/testify/require"
	"github.com/tidbcloud/observability/o11ydiagnosis/pkg/storage"
	"sigs.k8s.io/controller-runtime/pkg/client/fake"
)

// mockDuckDBAdapterForService is a mock implementation of duckDBAdapter for service testing
type mockDuckDBAdapterForService struct {
	selectFunc func(ctx context.Context, dest interface{}, tableAlias, table, query string) error
}

func (m *mockDuckDBAdapterForService) SelectSlowlogInDLWithoutFilterPushdown(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
	if m.selectFunc != nil {
		return m.selectFunc(ctx, dest, tableAlias, table, query)
	}
	return nil
}

// mockStorageForService is a mock implementation of storage.Storage for service testing
type mockStorageForService struct {
	objectsDelimiterFunc func(ctx context.Context, bucket, prefix string) ([]string, error)
}

func (m *mockStorageForService) Object(ctx context.Context, bucket, key string) (*storage.Object, error) {
	return nil, nil
}

func (m *mockStorageForService) Objects(ctx context.Context, bucket, prefix string) ([]storage.ObjectMeta, error) {
	return nil, nil
}

func (m *mockStorageForService) ObjectsCallback(ctx context.Context, bucket, prefix string, callback func(storage.ObjectMeta) bool) error {
	return nil
}

func (m *mockStorageForService) ObjectsCallbackWithStartKey(ctx context.Context, bucket, prefix, startKey string, callback func(storage.ObjectMeta) bool) error {
	return nil
}

func (m *mockStorageForService) ObjectExists(ctx context.Context, bucket, key string) (bool, error) {
	return false, nil
}

func (m *mockStorageForService) ObjectSize(ctx context.Context, bucket, key string) (int64, error) {
	return 0, nil
}

func (m *mockStorageForService) ObjectsDelimiter(ctx context.Context, bucket, prefix string) ([]string, error) {
	if m.objectsDelimiterFunc != nil {
		return m.objectsDelimiterFunc(ctx, bucket, prefix)
	}
	return []string{}, nil
}

func (m *mockStorageForService) SaveObject(ctx context.Context, bucket, key string, body io.Reader) error {
	return nil
}

func (m *mockStorageForService) DeleteObject(ctx context.Context, bucket, key string) error {
	return nil
}

func (m *mockStorageForService) DeleteObjects(ctx context.Context, bucket string, keys []string) error {
	return nil
}

func TestNewSQLsService(t *testing.T) {
	// Test with local vendor (no cloud credentials needed)
	mockStore := &mockStorageForService{}
	scheme := fake.NewClientBuilder().Build()

	// Test with local vendor
	service := NewSQLsService("", mockStore, scheme, DuckDBConfig{
		Vendor: "local",
		Region: "local",
	}, nil)

	require.NotNil(t, service)
	require.NotNil(t, service.SQLsDuckDBAdapter)
	require.NotNil(t, service.SQLsQuery)
	require.Equal(t, mockStore, service.store)
}

func TestInitSQLsApi(t *testing.T) {
	gin.SetMode(gin.TestMode)

	mockAdapter := &mockDuckDBAdapterForService{}
	service := &Service{
		SQLsDuckDBAdapter: mockAdapter,
		SQLsQuery:         mockAdapter,
		store:             &mockStorageForService{},
	}

	router := gin.New()
	group := router.Group("/")
	InitSQLsApi(group, service)

	// Test that routes are registered
	require.NotNil(t, router)
}

func TestInstancesHandler(t *testing.T) {
	gin.SetMode(gin.TestMode)

	tests := []struct {
		name           string
		headers        map[string]string
		query          string
		storeFunc      func(ctx context.Context, bucket, prefix string) ([]string, error)
		selectFunc     func(ctx context.Context, dest interface{}, tableAlias, table, query string) error
		expectedStatus int
		expectedData   []InstanceItem
	}{
		{
			name: "Valid request falls back to object listing",
			headers: map[string]string{
				"X-Provider":    "aws",
				"X-Region":      "us-west-2",
				"X-Org-Id":      "tenant-1",
				"X-Project-Id":  "project-1",
				"X-Cluster-Id":  "cluster-1",
				"X-Deploy-Type": "dedicated",
			},
			query: "start=1678886400&end=1678886400&data_source=deltalake",
			selectFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				return errors.New("catalog missing")
			},
			storeFunc: func(ctx context.Context, bucket, prefix string) ([]string, error) {
				switch {
				case strings.HasSuffix(prefix, "type=topsql/component="):
					return []string{
						prefix + "tidb/",
						prefix + "tikv/",
						prefix + "instance/",
					}, nil
				case strings.Contains(prefix, "component=tidb/"):
					return []string{prefix + "instance=db-tidb-0/"}, nil
				case strings.Contains(prefix, "component=tikv/"):
					return []string{prefix + "instance=db-tikv-0/"}, nil
				default:
					return []string{}, nil
				}
			},
			expectedStatus: http.StatusOK,
			expectedData: []InstanceItem{
				{Instance: "db-tidb-0", InstanceType: "tidb"},
				{Instance: "db-tikv-0", InstanceType: "tikv"},
			},
		},
		{
			name: "Valid request uses instance catalog table",
			headers: map[string]string{
				"X-Provider":    "aws",
				"X-Region":      "us-west-2",
				"X-Org-Id":      "tenant-1",
				"X-Project-Id":  "project-1",
				"X-Cluster-Id":  "cluster-1",
				"X-Deploy-Type": "dedicated",
			},
			query: "start=1678886400&end=1678886400&data_source=deltalake",
			selectFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				require.Contains(t, table, "component=instance")
				require.Contains(t, query, "window_begin")
				if destPtr, ok := dest.(*[]instanceCatalogRow); ok {
					*destPtr = []instanceCatalogRow{
						{Component: "tikv", InstanceID: "db-tikv-1"},
						{Component: "tidb", InstanceID: "db-tidb-1"},
						{Component: "tidb", InstanceID: "db-tidb-1"},
					}
				}
				return nil
			},
			storeFunc: func(ctx context.Context, bucket, prefix string) ([]string, error) {
				t.Fatalf("object listing should not be called when catalog has data")
				return nil, nil
			},
			expectedStatus: http.StatusOK,
			expectedData: []InstanceItem{
				{Instance: "db-tidb-1", InstanceType: "tidb"},
				{Instance: "db-tikv-1", InstanceType: "tikv"},
			},
		},
		{
			name:           "Missing headers",
			headers:        map[string]string{},
			query:          "start=1678886400&end=1678886400&data_source=deltalake",
			expectedStatus: http.StatusBadRequest,
		},
		{
			name: "Invalid query parameters",
			headers: map[string]string{
				"X-Provider":    "aws",
				"X-Region":      "us-west-2",
				"X-Org-Id":      "tenant-1",
				"X-Project-Id":  "project-1",
				"X-Cluster-Id":  "cluster-1",
				"X-Deploy-Type": "dedicated",
			},
			query:          "data_source=deltalake",
			expectedStatus: http.StatusBadRequest,
		},
		{
			name: "Store error after empty catalog",
			headers: map[string]string{
				"X-Provider":    "aws",
				"X-Region":      "us-west-2",
				"X-Org-Id":      "tenant-1",
				"X-Project-Id":  "project-1",
				"X-Cluster-Id":  "cluster-1",
				"X-Deploy-Type": "dedicated",
			},
			query: "start=1678886400&end=1678886400&data_source=deltalake",
			selectFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]instanceCatalogRow); ok {
					*destPtr = []instanceCatalogRow{}
				}
				return nil
			},
			storeFunc: func(ctx context.Context, bucket, prefix string) ([]string, error) {
				return nil, errors.New("store error")
			},
			expectedStatus: http.StatusInternalServerError,
		},
		{
			name: "BYOC deploy type",
			headers: map[string]string{
				"X-Provider":    "aws",
				"X-Region":      "us-west-2",
				"X-Org-Id":      "tenant-1",
				"X-Project-Id":  "project-1",
				"X-Cluster-Id":  "cluster-1",
				"X-Deploy-Type": "byoc",
			},
			query: "start=1678886400&end=1678886400&data_source=deltalake",
			selectFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				return errors.New("catalog missing")
			},
			storeFunc: func(ctx context.Context, bucket, prefix string) ([]string, error) {
				return []string{}, nil
			},
			expectedStatus: http.StatusOK,
			expectedData:   []InstanceItem{},
		},
		{
			name: "NextGen deploy type",
			headers: map[string]string{
				"X-Provider":    "aws",
				"X-Region":      "us-west-2",
				"X-Org-Id":      "tenant-1",
				"X-Project-Id":  "project-1",
				"X-Cluster-Id":  "cluster-1",
				"X-Deploy-Type": "nextgen",
			},
			query: "start=1678886400&end=1678886400&data_source=deltalake",
			selectFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				return errors.New("catalog missing")
			},
			storeFunc: func(ctx context.Context, bucket, prefix string) ([]string, error) {
				return []string{}, nil
			},
			expectedStatus: http.StatusOK,
			expectedData:   []InstanceItem{},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			mockStore := &mockStorageForService{
				objectsDelimiterFunc: tt.storeFunc,
			}
			mockAdapter := &mockDuckDBAdapterForService{
				selectFunc: tt.selectFunc,
			}

			service := &Service{
				SQLsDuckDBAdapter: mockAdapter,
				SQLsQuery:         mockAdapter,
				store:             mockStore,
			}

			router := gin.New()
			router.GET("/topsqlv2/instances", service.instancesHandler)

			req := httptest.NewRequest("GET", "/topsqlv2/instances?"+tt.query, nil)
			for k, v := range tt.headers {
				req.Header.Set(k, v)
			}

			w := httptest.NewRecorder()
			router.ServeHTTP(w, req)

			require.Equal(t, tt.expectedStatus, w.Code)
			if tt.expectedStatus == http.StatusOK {
				var response instancesEnvelope
				require.NoError(t, json.Unmarshal(w.Body.Bytes(), &response))
				require.Equal(t, "ok", response.Status)
				if tt.expectedData != nil {
					require.Equal(t, tt.expectedData, response.Data)
				}
			}
		})
	}
}

func TestSummaryHandler(t *testing.T) {
	gin.SetMode(gin.TestMode)

	now := time.Now()
	startSecs := float64(now.Add(-1 * time.Hour).Unix())
	endSecs := float64(now.Unix())

	tests := []struct {
		name           string
		headers        map[string]string
		query          string
		adapterFunc    func(ctx context.Context, dest interface{}, tableAlias, table, query string) error
		expectedStatus int
	}{
		{
			name: "Valid request",
			headers: map[string]string{
				"X-Provider":    "aws",
				"X-Region":      "us-west-2",
				"X-Org-Id":      "tenant-1",
				"X-Project-Id":  "project-1",
				"X-Cluster-Id":  "cluster-1",
				"X-Deploy-Type": "dedicated",
			},
			query: func() string {
				return "start=" + floatToString(startSecs) + "&end=" + floatToString(endSecs) + "&window=1m&top=10&instance=instance1&instance_type=tidb"
			}(),
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				// Mock query results
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{
						{
							SQLDigest:              stringPtr("sql1"),
							PlanDigest:             stringPtr("plan1"),
							TimeWindow:             timePtr(time.Now()),
							TotalCPUMs:             100.0,
							TotalNetworkBytes:      float64Ptr(1000.0),
							TotalLogicalIoBytes:    float64Ptr(2000.0),
							TotalStmtExecCount:     float64Ptr(10.0),
							TotalStmtDurationSumNs: float64Ptr(1000000.0),
							TotalStmtDurationCount: float64Ptr(10.0),
						},
					}
				}
				// Mock SQL metadata query
				if destPtr, ok := dest.(*[]sqlMetaResult); ok {
					*destPtr = []sqlMetaResult{
						{
							SQLDigest:     "sql1",
							NormalizedSQL: "SELECT * FROM table",
						},
					}
				}
				// Mock plan metadata query
				if destPtr, ok := dest.(*[]planMetaResult); ok {
					*destPtr = []planMetaResult{
						{
							PlanDigest:     "plan1",
							NormalizedPlan: "Plan text",
						},
					}
				}
				return nil
			},
			expectedStatus: http.StatusOK,
		},
		{
			name:           "Missing headers",
			headers:        map[string]string{},
			query:          "start=1678886400&end=1678886400",
			expectedStatus: http.StatusBadRequest,
		},
		{
			name: "Invalid query parameters",
			headers: map[string]string{
				"X-Provider":    "aws",
				"X-Region":      "us-west-2",
				"X-Org-Id":      "tenant-1",
				"X-Project-Id":  "project-1",
				"X-Cluster-Id":  "cluster-1",
				"X-Deploy-Type": "dedicated",
			},
			query:          "",
			expectedStatus: http.StatusBadRequest,
		},
		{
			name: "Adapter error",
			headers: map[string]string{
				"X-Provider":    "aws",
				"X-Region":      "us-west-2",
				"X-Org-Id":      "tenant-1",
				"X-Project-Id":  "project-1",
				"X-Cluster-Id":  "cluster-1",
				"X-Deploy-Type": "dedicated",
			},
			query: func() string {
				return "start=" + floatToString(startSecs) + "&end=" + floatToString(endSecs) + "&window=1m&top=10&instance=instance1&instance_type=tidb"
			}(),
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				return errors.New("adapter error")
			},
			expectedStatus: http.StatusInternalServerError,
		},
		{
			name: "BYOC deploy type",
			headers: map[string]string{
				"X-Provider":    "aws",
				"X-Region":      "us-west-2",
				"X-Org-Id":      "tenant-1",
				"X-Project-Id":  "project-1",
				"X-Cluster-Id":  "cluster-1",
				"X-Deploy-Type": "byoc",
			},
			query: func() string {
				return "start=" + floatToString(startSecs) + "&end=" + floatToString(endSecs) + "&window=1m&top=10&instance=instance1&instance_type=tidb"
			}(),
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				return nil
			},
			expectedStatus: http.StatusOK,
		},
		{
			name: "Unsupported provider",
			headers: map[string]string{
				"X-Provider":    "unknown",
				"X-Region":      "us-west-2",
				"X-Org-Id":      "tenant-1",
				"X-Project-Id":  "project-1",
				"X-Cluster-Id":  "cluster-1",
				"X-Deploy-Type": "dedicated",
			},
			query: func() string {
				return "start=" + floatToString(startSecs) + "&end=" + floatToString(endSecs) + "&window=1m&top=10&instance=instance1&instance_type=tidb"
			}(),
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				return nil
			},
			expectedStatus: http.StatusInternalServerError,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			mockAdapter := &mockDuckDBAdapterForService{
				selectFunc: tt.adapterFunc,
			}
			mockStore := &mockStorageForService{}

			service := &Service{
				SQLsDuckDBAdapter: mockAdapter,
				SQLsQuery:         mockAdapter,
				store:             mockStore,
			}

			router := gin.New()
			router.GET("/topsqlv2/summary", service.summaryHandler)

			req := httptest.NewRequest("GET", "/topsqlv2/summary?"+tt.query, nil)
			for k, v := range tt.headers {
				req.Header.Set(k, v)
			}

			w := httptest.NewRecorder()
			router.ServeHTTP(w, req)

			require.Equal(t, tt.expectedStatus, w.Code)
		})
	}
}

func TestGetSummaryRequest_Validate(t *testing.T) {
	tests := []struct {
		name    string
		query   GetSummaryRequest
		wantErr bool
		errMsg  string
	}{
		{
			name: "Valid query",
			query: GetSummaryRequest{
				Start:        "1678886400",
				End:          "1678886401",
				Window:       "1m",
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			wantErr: false,
		},
		{
			name: "Valid query with group_by and order_by",
			query: GetSummaryRequest{
				Start:        "1678886400",
				End:          "1678886401",
				Window:       "1m",
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
				GroupBy:      "query",
				OrderBy:      "cpu",
			},
			wantErr: false,
		},
		{
			name: "Valid query with rocksdb_block_read order_by",
			query: GetSummaryRequest{
				Start:        "1678886400",
				End:          "1678886401",
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tikv",
				OrderBy:      "rocksdb_block_read",
			},
			wantErr: false,
		},
		{
			name: "Valid query with network_in order_by",
			query: GetSummaryRequest{
				Start:        "1678886400",
				End:          "1678886401",
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tikv",
				OrderBy:      "network_in",
			},
			wantErr: false,
		},
		{
			name: "Valid query with logical_write order_by",
			query: GetSummaryRequest{
				Start:        "1678886400",
				End:          "1678886401",
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tikv",
				OrderBy:      "logical_write",
			},
			wantErr: false,
		},
		{
			name: "Missing start",
			query: GetSummaryRequest{
				End:          "1678886401",
				Window:       "1m",
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			wantErr: true,
			errMsg:  "start not found",
		},
		{
			name: "Missing end",
			query: GetSummaryRequest{
				Start:        "1678886400",
				Window:       "1m",
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			wantErr: true,
			errMsg:  "end not found",
		},
		{
			name: "Missing top",
			query: GetSummaryRequest{
				Start:        "1678886400",
				End:          "1678886401",
				Window:       "1m",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			wantErr: true,
			errMsg:  "top not found",
		},
		{
			name: "Missing instance",
			query: GetSummaryRequest{
				Start:        "1678886400",
				End:          "1678886401",
				Window:       "1m",
				Top:          "10",
				InstanceType: "tidb",
			},
			wantErr: true,
			errMsg:  "instance not found",
		},
		{
			name: "Missing instance_type",
			query: GetSummaryRequest{
				Start:    "1678886400",
				End:      "1678886401",
				Window:   "1m",
				Top:      "10",
				Instance: "instance1",
			},
			wantErr: true,
			errMsg:  "instance_type not found",
		},
		{
			name: "Invalid group_by",
			query: GetSummaryRequest{
				Start:        "1678886400",
				End:          "1678886401",
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
				GroupBy:      "invalid",
			},
			wantErr: true,
			errMsg:  "invalid group_by",
		},
		{
			name: "Invalid order_by",
			query: GetSummaryRequest{
				Start:        "1678886400",
				End:          "1678886401",
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
				OrderBy:      "invalid",
			},
			wantErr: true,
			errMsg:  "invalid order_by",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			err := tt.query.Validate()
			if tt.wantErr {
				require.Error(t, err)
				require.Contains(t, err.Error(), tt.errMsg)
			} else {
				require.NoError(t, err)
			}
		})
	}
}

func TestInstanceQuery_Validate(t *testing.T) {
	tests := []struct {
		name    string
		query   InstanceQuery
		wantErr bool
		errMsg  string
	}{
		{
			name: "Valid query",
			query: InstanceQuery{
				StartSecs: float64Ptr(1678886400.0),
				EndSecs:   float64Ptr(1678886401.0),
			},
			wantErr: false,
		},
		{
			name: "Missing start",
			query: InstanceQuery{
				EndSecs: float64Ptr(1678886401.0),
			},
			wantErr: true,
			errMsg:  "start not found",
		},
		{
			name: "Missing end",
			query: InstanceQuery{
				StartSecs: float64Ptr(1678886400.0),
			},
			wantErr: true,
			errMsg:  "end not found",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			err := tt.query.Validate()
			if tt.wantErr {
				require.Error(t, err)
				require.Contains(t, err.Error(), tt.errMsg)
			} else {
				require.NoError(t, err)
			}
		})
	}
}

func TestIsValidFieldName(t *testing.T) {
	tests := []struct {
		name     string
		field    string
		expected bool
	}{
		{
			name:     "Valid field name",
			field:    "topsql_cpu_time_ms",
			expected: true,
		},
		{
			name:     "Valid field with dot",
			field:    "topsql.cpu_time_ms",
			expected: true,
		},
		{
			name:     "Invalid field with space",
			field:    "topsql cpu time",
			expected: false,
		},
		{
			name:     "Invalid field with special characters",
			field:    "topsql-cpu-time",
			expected: false,
		},
		{
			name:     "Empty field",
			field:    "",
			expected: false,
		},
		{
			name:     "Field with SQL injection attempt",
			field:    "field; DROP TABLE users;",
			expected: false,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			result := isValidFieldName(tt.field)
			require.Equal(t, tt.expected, result)
		})
	}
}

func TestGetStringValue(t *testing.T) {
	tests := []struct {
		name     string
		m        map[string]interface{}
		key      string
		expected string
	}{
		{
			name:     "String value",
			m:        map[string]interface{}{"key": "value"},
			key:      "key",
			expected: "value",
		},
		{
			name:     "Non-string value",
			m:        map[string]interface{}{"key": 123},
			key:      "key",
			expected: "123",
		},
		{
			name:     "Missing key",
			m:        map[string]interface{}{},
			key:      "key",
			expected: "",
		},
		{
			name:     "Nil value",
			m:        map[string]interface{}{"key": nil},
			key:      "key",
			expected: "",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			result := getStringValue(tt.m, tt.key)
			require.Equal(t, tt.expected, result)
		})
	}
}

func TestGetFloat64Value(t *testing.T) {
	tests := []struct {
		name     string
		m        map[string]interface{}
		key      string
		expected float64
	}{
		{
			name:     "Float64 value",
			m:        map[string]interface{}{"key": 123.45},
			key:      "key",
			expected: 123.45,
		},
		{
			name:     "Float32 value",
			m:        map[string]interface{}{"key": float32(123.45)},
			key:      "key",
			expected: float64(float32(123.45)), // Account for float32 precision
		},
		{
			name:     "Int value",
			m:        map[string]interface{}{"key": 123},
			key:      "key",
			expected: 123.0,
		},
		{
			name:     "Int64 value",
			m:        map[string]interface{}{"key": int64(123)},
			key:      "key",
			expected: 123.0,
		},
		{
			name:     "Int32 value",
			m:        map[string]interface{}{"key": int32(123)},
			key:      "key",
			expected: 123.0,
		},
		{
			name:     "Int16 value",
			m:        map[string]interface{}{"key": int16(123)},
			key:      "key",
			expected: 123.0,
		},
		{
			name:     "Int8 value",
			m:        map[string]interface{}{"key": int8(123)},
			key:      "key",
			expected: 123.0,
		},
		{
			name:     "Uint64 value",
			m:        map[string]interface{}{"key": uint64(123)},
			key:      "key",
			expected: 123.0,
		},
		{
			name:     "Uint32 value",
			m:        map[string]interface{}{"key": uint32(123)},
			key:      "key",
			expected: 123.0,
		},
		{
			name:     "Uint16 value",
			m:        map[string]interface{}{"key": uint16(123)},
			key:      "key",
			expected: 123.0,
		},
		{
			name:     "Uint8 value",
			m:        map[string]interface{}{"key": uint8(123)},
			key:      "key",
			expected: 123.0,
		},
		{
			name:     "Uint value",
			m:        map[string]interface{}{"key": uint(123)},
			key:      "key",
			expected: 123.0,
		},
		{
			name:     "String number",
			m:        map[string]interface{}{"key": "123.45"},
			key:      "key",
			expected: 123.45,
		},
		{
			name:     "String invalid number",
			m:        map[string]interface{}{"key": "invalid"},
			key:      "key",
			expected: 0,
		},
		{
			name:     "Other type via fmt.Sprintf",
			m:        map[string]interface{}{"key": true},
			key:      "key",
			expected: 0,
		},
		{
			name:     "Missing key",
			m:        map[string]interface{}{},
			key:      "key",
			expected: 0,
		},
		{
			name:     "Nil value",
			m:        map[string]interface{}{"key": nil},
			key:      "key",
			expected: 0,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			result := getFloat64Value(tt.m, tt.key)
			require.Equal(t, tt.expected, result)
		})
	}
}

func TestGetInt64Value(t *testing.T) {
	tests := []struct {
		name     string
		m        map[string]interface{}
		key      string
		expected int64
	}{
		{
			name:     "Int64 value",
			m:        map[string]interface{}{"key": int64(123)},
			key:      "key",
			expected: 123,
		},
		{
			name:     "Int value",
			m:        map[string]interface{}{"key": 123},
			key:      "key",
			expected: 123,
		},
		{
			name:     "Float64 value",
			m:        map[string]interface{}{"key": 123.45},
			key:      "key",
			expected: 123,
		},
		{
			name:     "Uint64 value",
			m:        map[string]interface{}{"key": uint64(123)},
			key:      "key",
			expected: 123,
		},
		{
			name:     "Missing key",
			m:        map[string]interface{}{},
			key:      "key",
			expected: 0,
		},
		{
			name:     "Nil value",
			m:        map[string]interface{}{"key": nil},
			key:      "key",
			expected: 0,
		},
		{
			name:     "Invalid type",
			m:        map[string]interface{}{"key": "invalid"},
			key:      "key",
			expected: 0,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			result := getInt64Value(tt.m, tt.key)
			require.Equal(t, tt.expected, result)
		})
	}
}

// Helper types and functions for testing

func stringPtr(s string) *string {
	return &s
}

func float64Ptr(f float64) *float64 {
	return &f
}

func timePtr(t time.Time) *time.Time {
	return &t
}

func floatToString(f float64) string {
	return fmt.Sprintf("%.0f", f)
}

// TestQueryTopSQLSummary tests the queryTopSQLSummary function with various scenarios
func TestQueryTopSQLSummary(t *testing.T) {
	now := time.Now()
	startSecs := now.Add(-1 * time.Hour).Unix()
	endSecs := now.Unix()

	tests := []struct {
		name          string
		header        *ClusterHeader
		query         *GetSummaryRequest
		adapterFunc   func(ctx context.Context, dest interface{}, tableAlias, table, query string) error
		expectedError bool
		errorContains string
	}{
		{
			name: "Valid query with query group",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
				GroupBy:      "query",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					timeWindow := time.Unix(startSecs, 0)
					*destPtr = []queryResult{
						{
							SQLDigest:              stringPtr("sql1"),
							PlanDigest:             stringPtr("plan1"),
							TimeWindow:             &timeWindow,
							TotalCPUMs:             100.0,
							TotalNetworkBytes:      float64Ptr(1000.0),
							TotalLogicalIoBytes:    float64Ptr(2000.0),
							TotalStmtExecCount:     float64Ptr(10.0),
							TotalStmtDurationSumNs: float64Ptr(1000000.0),
							TotalStmtDurationCount: float64Ptr(10.0),
						},
					}
				}
				if destPtr, ok := dest.(*[]sqlMetaResult); ok {
					*destPtr = []sqlMetaResult{
						{
							SQLDigest:     "sql1",
							NormalizedSQL: "SELECT * FROM table",
						},
					}
				}
				if destPtr, ok := dest.(*[]planMetaResult); ok {
					*destPtr = []planMetaResult{
						{
							PlanDigest:     "plan1",
							NormalizedPlan: "Plan text",
						},
					}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with non-query group",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
				GroupBy:      "region",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					timeWindow := time.Unix(startSecs, 0)
					*destPtr = []queryResult{
						{
							GroupKey:               stringPtr("region1"),
							Text:                   stringPtr("region1"),
							TimeWindow:             &timeWindow,
							TotalCPUMs:             100.0,
							TotalNetworkBytes:      float64Ptr(1000.0),
							TotalLogicalIoBytes:    float64Ptr(2000.0),
							TotalStmtExecCount:     float64Ptr(10.0),
							TotalStmtDurationSumNs: float64Ptr(1000000.0),
							TotalStmtDurationCount: float64Ptr(10.0),
						},
					}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with network order_by",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
				OrderBy:      "network",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with logical_io order_by",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
				OrderBy:      "logical_io",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with rocksdb_block_read order_by",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tikv",
				OrderBy:      "rocksdb_block_read",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with network_in order_by",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tikv",
				OrderBy:      "network_in",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with logical_read order_by",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tikv",
				OrderBy:      "logical_read",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with invalid order_by",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
				OrderBy:      "invalid",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				return nil
			},
			expectedError: true,
			errorContains: "invalid order_by",
		},
		{
			name: "Query with invalid group_by",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
				GroupBy:      "invalid",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				return nil
			},
			expectedError: true,
			errorContains: "invalid group_by",
		},
		{
			name: "Query with adapter error",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				return errors.New("adapter error")
			},
			expectedError: true,
			errorContains: "failed to query topsql summary",
		},
		{
			name: "Query with empty results",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with GCP provider",
			header: &ClusterHeader{
				Provider:   stringPtr("gcp"),
				Region:     stringPtr("us-central1"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with Azure provider",
			header: &ClusterHeader{
				Provider:   stringPtr("azure"),
				Region:     stringPtr("eastus"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with Alicloud provider",
			header: &ClusterHeader{
				Provider:   stringPtr("alicloud"),
				Region:     stringPtr("cn-hangzhou"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with local provider",
			header: &ClusterHeader{
				Provider:   stringPtr("local"),
				Region:     stringPtr("local"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with shared tier",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("shared"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with nextgen tier",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("nextgen"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with premium tier",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("premium"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with other group and empty sql_digest",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
				GroupBy:      "query",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					timeWindow := time.Unix(startSecs, 0)
					emptySQL := ""
					*destPtr = []queryResult{
						{
							SQLDigest:              &emptySQL,
							PlanDigest:             stringPtr("plan1"),
							TimeWindow:             &timeWindow,
							TotalCPUMs:             100.0,
							TotalNetworkBytes:      float64Ptr(1000.0),
							TotalLogicalIoBytes:    float64Ptr(2000.0),
							TotalStmtExecCount:     float64Ptr(10.0),
							TotalStmtDurationSumNs: float64Ptr(1000000.0),
							TotalStmtDurationCount: float64Ptr(10.0),
						},
					}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with multiple SQL digests and plans",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
				GroupBy:      "query",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					timeWindow1 := time.Unix(startSecs, 0)
					timeWindow2 := time.Unix(startSecs+60, 0)
					sql1 := "sql1"
					sql2 := "sql2"
					plan1 := "plan1"
					plan2 := "plan2"
					*destPtr = []queryResult{
						{
							SQLDigest:              &sql1,
							PlanDigest:             &plan1,
							TimeWindow:             &timeWindow1,
							TotalCPUMs:             100.0,
							TotalNetworkBytes:      float64Ptr(1000.0),
							TotalLogicalIoBytes:    float64Ptr(2000.0),
							TotalStmtExecCount:     float64Ptr(10.0),
							TotalStmtDurationSumNs: float64Ptr(1000000.0),
							TotalStmtDurationCount: float64Ptr(10.0),
						},
						{
							SQLDigest:              &sql1,
							PlanDigest:             &plan2,
							TimeWindow:             &timeWindow1,
							TotalCPUMs:             200.0,
							TotalNetworkBytes:      float64Ptr(2000.0),
							TotalLogicalIoBytes:    float64Ptr(4000.0),
							TotalStmtExecCount:     float64Ptr(20.0),
							TotalStmtDurationSumNs: float64Ptr(2000000.0),
							TotalStmtDurationCount: float64Ptr(20.0),
						},
						{
							SQLDigest:              &sql2,
							PlanDigest:             &plan1,
							TimeWindow:             &timeWindow2,
							TotalCPUMs:             300.0,
							TotalNetworkBytes:      float64Ptr(3000.0),
							TotalLogicalIoBytes:    float64Ptr(6000.0),
							TotalStmtExecCount:     float64Ptr(30.0),
							TotalStmtDurationSumNs: float64Ptr(3000000.0),
							TotalStmtDurationCount: float64Ptr(30.0),
						},
					}
				}
				if destPtr, ok := dest.(*[]sqlMetaResult); ok {
					*destPtr = []sqlMetaResult{
						{SQLDigest: "sql1", NormalizedSQL: "SELECT * FROM table1"},
						{SQLDigest: "sql2", NormalizedSQL: "SELECT * FROM table2"},
					}
				}
				if destPtr, ok := dest.(*[]planMetaResult); ok {
					*destPtr = []planMetaResult{
						{PlanDigest: "plan1", NormalizedPlan: "Plan 1"},
						{PlanDigest: "plan2", NormalizedPlan: "Plan 2"},
					}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with nil time window",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					sql1 := "sql1"
					*destPtr = []queryResult{
						{
							SQLDigest:              &sql1,
							PlanDigest:             nil,
							TimeWindow:             nil,
							TotalCPUMs:             100.0,
							TotalNetworkBytes:      nil,
							TotalLogicalIoBytes:    nil,
							TotalStmtExecCount:     nil,
							TotalStmtDurationSumNs: nil,
							TotalStmtDurationCount: nil,
						},
					}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with zero time range",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", startSecs), // Same as start
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with SQL metadata error",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
				GroupBy:      "query",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					timeWindow := time.Unix(startSecs, 0)
					sql1 := "sql1"
					*destPtr = []queryResult{
						{
							SQLDigest:              &sql1,
							PlanDigest:             stringPtr("plan1"),
							TimeWindow:             &timeWindow,
							TotalCPUMs:             100.0,
							TotalNetworkBytes:      float64Ptr(1000.0),
							TotalLogicalIoBytes:    float64Ptr(2000.0),
							TotalStmtExecCount:     float64Ptr(10.0),
							TotalStmtDurationSumNs: float64Ptr(1000000.0),
							TotalStmtDurationCount: float64Ptr(10.0),
						},
					}
				}
				// Return error for SQL metadata query
				if _, ok := dest.(*[]sqlMetaResult); ok {
					return errors.New("SQL metadata error")
				}
				return nil
			},
			expectedError: false, // Error in metadata query should be logged but not fail the main query
		},
		{
			name: "Query with plan metadata error",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
				GroupBy:      "query",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					timeWindow := time.Unix(startSecs, 0)
					sql1 := "sql1"
					*destPtr = []queryResult{
						{
							SQLDigest:              &sql1,
							PlanDigest:             stringPtr("plan1"),
							TimeWindow:             &timeWindow,
							TotalCPUMs:             100.0,
							TotalNetworkBytes:      float64Ptr(1000.0),
							TotalLogicalIoBytes:    float64Ptr(2000.0),
							TotalStmtExecCount:     float64Ptr(10.0),
							TotalStmtDurationSumNs: float64Ptr(1000000.0),
							TotalStmtDurationCount: float64Ptr(10.0),
						},
					}
				}
				if destPtr, ok := dest.(*[]sqlMetaResult); ok {
					*destPtr = []sqlMetaResult{
						{SQLDigest: "sql1", NormalizedSQL: "SELECT * FROM table"},
					}
				}
				// Return error for plan metadata query
				if _, ok := dest.(*[]planMetaResult); ok {
					return errors.New("Plan metadata error")
				}
				return nil
			},
			expectedError: false, // Error in metadata query should be logged but not fail the main query
		},
		{
			name: "Query with nil plan digest",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
				GroupBy:      "query",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					timeWindow := time.Unix(startSecs, 0)
					sql1 := "sql1"
					*destPtr = []queryResult{
						{
							SQLDigest:              &sql1,
							PlanDigest:             nil, // nil plan digest
							TimeWindow:             &timeWindow,
							TotalCPUMs:             100.0,
							TotalStmtExecCount:     float64Ptr(10.0),
							TotalStmtDurationSumNs: float64Ptr(1000000.0),
							TotalStmtDurationCount: float64Ptr(10.0),
						},
					}
				}
				if destPtr, ok := dest.(*[]sqlMetaResult); ok {
					*destPtr = []sqlMetaResult{
						{SQLDigest: "sql1", NormalizedSQL: "SELECT * FROM table"},
					}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with custom env",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("dev"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Query with very large time range",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", startSecs+86400*10), // 10 days
				Top:          "10",
				Instance:     "instance1",
				InstanceType: "tidb",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				if destPtr, ok := dest.(*[]queryResult); ok {
					*destPtr = []queryResult{}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Premium TiKV query resolves sql_text via keyspace cluster sql_meta",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("1369847559692509719"),
				ClusterID:  stringPtr("629262825109323777"), // shared pool
				DeployType: stringPtr("premium"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "db-833b4de-tikv-4tt8bs",
				InstanceType: "tikv",
				GroupBy:      "query",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				switch destPtr := dest.(type) {
				case *[]queryResult:
					require.Contains(t, query, "keyspace")
					require.Contains(t, query, "sql_digest, plan_digest, keyspace")
					timeWindow := time.Unix(startSecs, 0)
					*destPtr = []queryResult{
						{
							SQLDigest:              stringPtr("D28680C29009B286DD9708A1372FA8A9D6A01F73CA44C2B2A940DD3A119A2FD9"),
							PlanDigest:             stringPtr("F68E03B89B5A9B8C6D324D85DFB36202163616CA542D3D5163DDD19622DFA216"),
							Keyspace:               stringPtr("10262747196199462033"),
							TimeWindow:             &timeWindow,
							TotalCPUMs:             655.0,
							TotalStmtExecCount:     float64Ptr(1.0),
							TotalStmtDurationSumNs: float64Ptr(1000.0),
							TotalStmtDurationCount: float64Ptr(1.0),
						},
					}
				case *[]keyspaceResult:
					*destPtr = []keyspaceResult{
						{Keyspace: stringPtr("10262747196199462033")},
					}
				case *[]sqlMetaResult:
					// Meta must be loaded from keyspace cluster, not shared-pool cluster.
					require.Contains(t, table, "cluster=10262747196199462033")
					require.Contains(t, table, "component=topsql_sql_meta")
					require.NotContains(t, table, "cluster=629262825109323777")
					*destPtr = []sqlMetaResult{
						{
							SQLDigest:     "D28680C29009B286DD9708A1372FA8A9D6A01F73CA44C2B2A940DD3A119A2FD9",
							NormalizedSQL: "SELECT * FROM t WHERE id = ?",
						},
					}
				case *[]planMetaResult:
					require.Contains(t, table, "cluster=10262747196199462033")
					require.Contains(t, table, "component=topsql_plan_meta")
					*destPtr = []planMetaResult{
						{
							PlanDigest:     "F68E03B89B5A9B8C6D324D85DFB36202163616CA542D3D5163DDD19622DFA216",
							NormalizedPlan: "Point_Get",
						},
					}
				}
				return nil
			},
			expectedError: false,
		},
		{
			name: "Dedicated TiKV keeps request cluster for sql_meta",
			header: &ClusterHeader{
				Provider:   stringPtr("aws"),
				Region:     stringPtr("us-west-2"),
				TenantID:   stringPtr("tenant-1"),
				ClusterID:  stringPtr("dedicated-cluster-1"),
				DeployType: stringPtr("dedicated"),
				Env:        stringPtr("prod"),
			},
			query: &GetSummaryRequest{
				Start:        fmt.Sprintf("%d", startSecs),
				End:          fmt.Sprintf("%d", endSecs),
				Top:          "10",
				Instance:     "tikv-0",
				InstanceType: "tikv",
				GroupBy:      "query",
			},
			adapterFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
				switch destPtr := dest.(type) {
				case *[]queryResult:
					timeWindow := time.Unix(startSecs, 0)
					*destPtr = []queryResult{
						{
							SQLDigest:              stringPtr("sql-dedicated"),
							PlanDigest:             stringPtr("plan-dedicated"),
							TimeWindow:             &timeWindow,
							TotalCPUMs:             10.0,
							TotalStmtExecCount:     float64Ptr(1.0),
							TotalStmtDurationSumNs: float64Ptr(1000.0),
							TotalStmtDurationCount: float64Ptr(1.0),
						},
					}
				case *[]keyspaceResult:
					t.Fatalf("dedicated TiKV should not list keyspaces")
				case *[]sqlMetaResult:
					require.Contains(t, table, "cluster=dedicated-cluster-1")
					require.Contains(t, table, "component=topsql_sql_meta")
					*destPtr = []sqlMetaResult{
						{SQLDigest: "sql-dedicated", NormalizedSQL: "SELECT 1"},
					}
				case *[]planMetaResult:
					require.Contains(t, table, "cluster=dedicated-cluster-1")
					*destPtr = []planMetaResult{
						{PlanDigest: "plan-dedicated", NormalizedPlan: "Point_Get"},
					}
				}
				return nil
			},
			expectedError: false,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			// Set AZURE_STORAGE_ACCOUNT for Azure tests
			if tt.header.Provider != nil && *tt.header.Provider == "azure" {
				originalEnv := os.Getenv("AZURE_STORAGE_ACCOUNT")
				os.Setenv("AZURE_STORAGE_ACCOUNT", "test-account")
				defer func() {
					if originalEnv != "" {
						os.Setenv("AZURE_STORAGE_ACCOUNT", originalEnv)
					} else {
						os.Unsetenv("AZURE_STORAGE_ACCOUNT")
					}
				}()
			}

			mockAdapter := &mockDuckDBAdapterForService{
				selectFunc: func(ctx context.Context, dest interface{}, tableAlias, table, query string) error {
					if strings.Contains(query, "information_schema.columns") || strings.Contains(query, "DESCRIBE SELECT") {
						if destPtr, ok := dest.(*[]topsqlColumnInfo); ok {
							*destPtr = []topsqlColumnInfo{
								{ColumnName: "topsql_cpu_time_ms"},
								{ColumnName: "topsql_network_in_bytes"},
								{ColumnName: "topsql_network_out_bytes"},
								{ColumnName: "topsql_logical_read_bytes"},
								{ColumnName: "topsql_logical_write_bytes"},
								{ColumnName: "topsql_rocksdb_block_read_count"},
								{ColumnName: "topsql_stmt_exec_count"},
								{ColumnName: "topsql_stmt_duration_sum_ns"},
								{ColumnName: "topsql_stmt_duration_count"},
								{ColumnName: "timestamps"},
								{ColumnName: "sql_digest"},
								{ColumnName: "plan_digest"},
								{ColumnName: "region_id"},
							}
						}
						return nil
					}
					if tt.adapterFunc != nil {
						return tt.adapterFunc(ctx, dest, tableAlias, table, query)
					}
					return nil
				},
			}
			mockStore := &mockStorageForService{}

			service := &Service{
				SQLsDuckDBAdapter: mockAdapter,
				SQLsQuery:         mockAdapter,
				store:             mockStore,
			}

			result, err := service.queryTopSQLSummary(context.Background(), tt.header, tt.query)

			if tt.expectedError {
				require.Error(t, err)
				if tt.errorContains != "" {
					require.Contains(t, err.Error(), tt.errorContains)
				}
			} else {
				require.NoError(t, err)
				require.NotNil(t, result)
				if tt.name == "Premium TiKV query resolves sql_text via keyspace cluster sql_meta" {
					require.Len(t, result.Data, 1)
					require.Equal(t, "SELECT * FROM t WHERE id = ?", result.Data[0].SQLText)
					require.Equal(t, "Point_Get", result.Data[0].Plans[0].PlanText)
					require.Equal(t, "10262747196199462033", result.Data[0].Keyspace)
				}
				if tt.name == "Dedicated TiKV keeps request cluster for sql_meta" {
					require.Len(t, result.Data, 1)
					require.Equal(t, "SELECT 1", result.Data[0].SQLText)
				}
			}
		})
	}
}