package slowlog_explorer

import (
	"fmt"
	"net/http/httptest"
	"net/url"
	"strconv"
	"testing"
	"time"

	"github.com/gin-gonic/gin"
)

func asFloat(v interface{}) float64 {
	f, _ := strconv.ParseFloat(fmt.Sprintf("%v", v), 64)
	return f
}

func TestBuildTopDigestsSQLGroupByDB(t *testing.T) {
	start := time.Date(2026, 4, 9, 0, 0, 0, 0, time.UTC)
	end := time.Date(2026, 4, 9, 3, 0, 0, 0, time.UTC)
	dbGroup := topDigestGroupRegistry["db"]

	// Athena / default: group by db, keep metric ordering and sample_query padding.
	sql := buildTopDigestsSQLWithGroupBy(start, end, 10, nil, slowlogQueryDialectDefault, "sum_process_keys", dbGroup)
	t.Logf("Athena db breakdown SQL: %s", sql)
	for _, want := range []string{
		"COALESCE(db, '') AS db",
		"GROUP BY COALESCE(db, '')",
		"AS sample_query",
		"ORDER BY sum_process_keys DESC",
	} {
		if !contains(sql, want) {
			t.Errorf("missing %q in: %s", want, sql)
		}
	}
	if contains(sql, "GROUP BY digest") {
		t.Error("db breakdown must not group by digest")
	}

	// Synapse quotes the db column as [db].
	sqlSyn := buildTopDigestsSQLWithGroupBy(start, end, 5, nil, slowlogQueryDialectSynapse, "total_time", dbGroup)
	t.Logf("Synapse db breakdown SQL: %s", sqlSyn)
	if !contains(sqlSyn, "COALESCE([db], '') AS db") {
		t.Errorf("Synapse should quote db column: %s", sqlSyn)
	}
	if !contains(sqlSyn, "GROUP BY COALESCE([db], '')") {
		t.Errorf("Synapse GROUP BY [db]: %s", sqlSyn)
	}

	// BigQuery keeps the quantiles array alongside db grouping.
	sqlBq := buildTopDigestsSQLWithGroupBy(start, end, 5, nil, slowlogQueryDialectBigQuery, "total_time", dbGroup)
	t.Logf("BigQuery db breakdown SQL: %s", sqlBq)
	if !contains(sqlBq, "COALESCE(db, '') AS db") {
		t.Errorf("BigQuery db select: %s", sqlBq)
	}
	if !contains(sqlBq, "APPROX_QUANTILES(") {
		t.Error("BigQuery should keep quantiles array")
	}

	// Default digest dimension stays byte-for-byte the same as before.
	digestSQL := buildTopDigestsSQLForDialect(start, end, 10, nil, slowlogQueryDialectDefault, "sum_process_keys")
	if !contains(digestSQL, "GROUP BY digest") {
		t.Error("digest dimension must still group by digest")
	}
}

func TestBuildDigestDbBreakdownSQL(t *testing.T) {
	start := time.Date(2026, 4, 9, 0, 0, 0, 0, time.UTC)
	end := time.Date(2026, 4, 9, 3, 0, 0, 0, time.UTC)
	// An inherited filter from the parent table (e.g. user = 'app') must survive
	// alongside the digest scope.
	inherited := []string{"user = 'app'"}
	sql := buildDigestDbBreakdownSQL(start, end, 10, slowlogQueryDialectDefault, "sum_process_keys", "abcd1234", inherited)
	t.Logf("breakdown SQL: %s", sql)

	for _, want := range []string{
		"GROUP BY COALESCE(db, '')",
		"digest = 'abcd1234'", // digest scope appended
		"user = 'app'",        // inherited filter preserved
		"ORDER BY sum_process_keys DESC",
		"LIMIT 10",
	} {
		if !contains(sql, want) {
			t.Errorf("missing %q in: %s", want, sql)
		}
	}

	// The caller's slice must not be mutated by the append.
	if len(inherited) != 1 || inherited[0] != "user = 'app'" {
		t.Errorf("inherited filters were mutated: %v", inherited)
	}

	// Synapse uses TOP instead of LIMIT and [db] quoting.
	sqlSyn := buildDigestDbBreakdownSQL(start, end, 5, slowlogQueryDialectSynapse, "total_time", "deadbeef", nil)
	if !contains(sqlSyn, "GROUP BY COALESCE([db], '')") {
		t.Errorf("Synapse breakdown should group by [db]: %s", sqlSyn)
	}
	if !contains(sqlSyn, "[digest] = 'deadbeef'") {
		t.Errorf("Synapse breakdown missing digest scope: %s", sqlSyn)
	}
}

func TestBuildTopDigestItemDBBreakdownPairMetric(t *testing.T) {
	// row shape: 0=db, 1=cnt, 2=avg_ms, 3=p99, 4=total_ms, 5=sample_query, 6,7=pair metrics
	row := []interface{}{"mydb", "100", "5", "20", "500", "select 1", "1000", "2000"}
	displayMetrics := topDigestDisplayMetrics("sum_process_keys") // [sum_total_keys, sum_process_keys]
	item := buildTopDigestItem(row, slowlogQueryDialectDefault, "sum_process_keys", displayMetrics, "db", false)

	if _, ok := item["digest"]; ok {
		t.Error("db breakdown item must not contain digest")
	}
	if _, ok := item["sample_query"]; ok {
		t.Error("db breakdown item must not contain sample_query")
	}
	if item["db"] != "mydb" {
		t.Errorf("db = %v, want mydb", item["db"])
	}
	mv, ok := item["metric_values"].(map[string]interface{})
	if !ok {
		t.Fatalf("metric_values missing or wrong type: %v", item["metric_values"])
	}
	if asFloat(mv["sum_total_keys"]) != 1000 {
		t.Errorf("sum_total_keys = %v, want 1000", mv["sum_total_keys"])
	}
	if asFloat(mv["sum_process_keys"]) != 2000 {
		t.Errorf("sum_process_keys = %v, want 2000", mv["sum_process_keys"])
	}
	if asFloat(item["sort_value"]) != 2000 {
		t.Errorf("sort_value = %v, want 2000 (sum_process_keys)", item["sort_value"])
	}
	if asFloat(item["count"]) != 100 || asFloat(item["avg_ms"]) != 5 || asFloat(item["total_ms"]) != 500 {
		t.Errorf("base columns wrong: %+v", item)
	}
}

func TestBuildTopDigestItemCountMetric(t *testing.T) {
	row := []interface{}{"db_c", "50", "5", "20", "500", "select 1"}
	displayMetrics := topDigestDisplayMetrics("count") // []
	item := buildTopDigestItem(row, slowlogQueryDialectDefault, "count", displayMetrics, "db", false)

	mv := item["metric_values"].(map[string]interface{})
	if len(mv) != 0 {
		t.Errorf("count metric should have empty metric_values, got %v", mv)
	}
	if asFloat(item["sort_value"]) != 50 {
		t.Errorf("count sort_value = %v, want 50", item["sort_value"])
	}
	if item["db"] != "db_c" {
		t.Errorf("db = %v", item["db"])
	}
}

func TestBuildTopDigestItemBigQueryP99(t *testing.T) {
	// BigQuery returns p99 as a quantiles array; index 99 is the p99 value (seconds).
	quantiles := make([]interface{}, 101)
	quantiles[99] = "0.5"
	row := []interface{}{"db_bq", "10", "5", quantiles, "100", "select 1"}
	displayMetrics := topDigestDisplayMetrics("total_time") // [total_time], read from col 4
	item := buildTopDigestItem(row, slowlogQueryDialectBigQuery, "total_time", displayMetrics, "db", false)

	if asFloat(item["p99_ms"]) != 500 { // 0.5s -> 500ms
		t.Errorf("p99_ms = %v, want 500 (from quantiles[99]=0.5s)", item["p99_ms"])
	}
	if asFloat(item["sort_value"]) != 100 { // total_time reads total_ms (col 4)
		t.Errorf("sort_value = %v, want 100", item["sort_value"])
	}
}

func TestParseDigestParam(t *testing.T) {
	gin.SetMode(gin.TestMode)
	cases := []struct {
		in     string
		wantOK bool
		want   string
	}{
		{"abcDEF123", true, "abcdef123"}, // upper-cased hex normalized to lower
		{"0123456789abcdef", true, "0123456789abcdef"},
		{"", false, ""},
		{"xyz", false, ""},
		{"abc'; DROP TABLE t;--", false, ""},
		{"ab cd", false, ""},
	}
	for _, tc := range cases {
		c, _ := gin.CreateTestContext(httptest.NewRecorder())
		c.Request = httptest.NewRequest("GET", "/?digest="+url.QueryEscape(tc.in), nil)
		got, ok := parseDigestParam(c)
		if ok != tc.wantOK || got != tc.want {
			t.Errorf("digest=%q: got (%q,%v), want (%q,%v)", tc.in, got, ok, tc.want, tc.wantOK)
		}
	}
}