package service

import (
	"context"
	"encoding/json"
	"fmt"
	"regexp"
	"strconv"
	"strings"
	"time"

	"github.com/pingcap-inc/clinic/clinic_data_service/pkg/dto"
	"github.com/pingcap-inc/clinic/clinic_data_service/pkg/log"
	"go.uber.org/zap"
)

const (
	// HugeMemoryThreshold 大内存阈值：5GB
	HugeMemoryThreshold = 5 * 1024 * 1024 * 1024 // 5368709120 bytes

	// SlowQueryTimeThreshold 慢查询时间阈值：40秒
	SlowQueryTimeThreshold = 40 // seconds

	// ClinicNGMPath Clinic Dashboard NGM 页面固定路径
	ClinicNGMPath = "/portal/dashboard/cloud/ngm.html"
)

// SlowQueryAnalyzer 慢查询分析服务
type SlowQueryAnalyzer struct {
	dataProxyService *DataProxyService
}

// NewSlowQueryAnalyzer 创建慢查询分析器
func NewSlowQueryAnalyzer(dataProxyService *DataProxyService) *SlowQueryAnalyzer {
	return &SlowQueryAnalyzer{
		dataProxyService: dataProxyService,
	}
}

// HealthCheckCommonParameters 健康检查通用参数
type HealthCheckCommonParameters struct {
	Name              string          `json:"name"`
	ClusterId         string          `json:"clusterId"`
	PhysicalClusterId string          `json:"physicalClusterId"`
	ReportId          string          `json:"reportId"`
	DeployType        string          `json:"deployType"`
	ProjectId         string          `json:"projectId"`
	Provider          string          `json:"provider"`
	Region            string          `json:"region"`
	TenantId          string          `json:"tenantId"`
	ClinicOrgId       string          `json:"clinicOrgId"`      // Grafana var-org 参数
	DashboardVersion  string          `json:"dashboardVersion"` // TiDB 版本号（如 v8.5.x）
	Version           string          `json:"version"`          // 带具体 patch 版本号
	TimeRange         dto.TimeRange   `json:"timeRange"`
	ClusterUrls       dto.ClusterUrls `json:"clusterUrls"`
}

// SlowQueryAnalyzeParams 慢查询分析特有参数
type SlowQueryAnalyzeParams struct {
	MemMaxThreshold    *int64 `json:"memMaxThreshold"`    // 内存阈值（可选，默认 5GB）
	QueryTimeThreshold *int   `json:"queryTimeThreshold"` // 查询时间阈值（可选，默认 40秒）
}

// AnalyzeRequest 慢查询分析请求
type AnalyzeRequest struct {
	HealthCheckCommonParameters
	Params SlowQueryAnalyzeParams `json:"params"`
}

// PatternResult Pattern 结果（按 digest 聚合）
type PatternResult struct {
	Digest              string  `json:"digest"`
	SampleQuery         string  `json:"sample_query"`
	HitRecords          int     `json:"hit_records"`            // 总命中次数
	HitMemRecords       int     `json:"hit_mem_records"`        // 命中大内存规则的次数
	HitQueryTimeRecords int     `json:"hit_query_time_records"` // 命中长查询时间规则的次数
	AvgMaxMemB          float64 `json:"avg_max_mem_b"`          // 平均最大内存（B）
	MaxMaxMemB          float64 `json:"max_max_mem_b"`          // 最大内存峰值（B）
	// AvgMaxMemKB         float64               `json:"avg_max_mem_kb"`          // 平均最大内存（KB）
	// MaxMaxMemKB         float64               `json:"max_max_mem_kb"`          // 最大内存峰值（KB）
	AvgQueryTimeS float64               `json:"avg_query_time_s"`        // 平均查询时间（秒）
	MaxQueryTimeS float64               `json:"max_query_time_s"`        // 最大查询时间（秒）
	DigestDetail  []*DigestDetailRecord `json:"digest_detail,omitempty"` // Top2 详情记录
}

// DigestDetailRecord 单条 digest 详情记录（Top2）
type DigestDetailRecord struct {
	ConnID    string  `json:"conn_id"`
	Time      string  `json:"time"`
	MemMax    float64 `json:"mem_max"`
	QueryTime float64 `json:"query_time"`
	Source    string  `json:"source"` // "hit_mem_top2" 或 "hit_query_time_top2"
	Link      string  `json:"link"`
}

// DigestDetailParams digest detail 查询和 link 生成所需参数
type DigestDetailParams struct {
	ClusterID     string
	Provider      string
	Region        string
	OrgId         string
	ProjectId     string
	DeployType    string
	ClinicBaseURL string
}

// AnalysisResult 分析结果
type AnalysisResult struct {
	TotalScanned  int              `json:"total_scanned"`  // Athena 扫描的总记录数
	FilteredCount int              `json:"filtered_count"` // 粗筛选后的记录数
	PatternCount  int              `json:"pattern_count"`  // 最终匹配的 Pattern 数量
	Patterns      []*PatternResult `json:"patterns"`       // Pattern 详情
	Statistics    map[string]int   `json:"statistics"`     // 各类 Pattern 统计
}

// HealthCheckAnalysisResponse 健康检查分析响应
type HealthCheckAnalysisResponse struct {
	Name          string                 `json:"name"`          // 检查项名称
	Description   string                 `json:"description"`   // 检查项描述
	CheckType     string                 `json:"checkType"`     // 检查类型
	Status        string                 `json:"status"`        // 状态：success/failed
	Params        map[string]interface{} `json:"params"`        // 执行参数
	ResultJsonUrl string                 `json:"resultJsonUrl"` // S3 报告存储地址
	Comment       string                 `json:"comment"`       // 字段单位说明
	Summary       HealthCheckSummary     `json:"summary"`       // 概览统计
	Results       []*PatternResult       `json:"results"`       // 结果列表（按 digest 聚合）
}

// HealthCheckSummary 健康检查概览统计
type HealthCheckSummary struct {
	TotalRecords                    int `json:"totalRecords"`                    // 总记录数
	HugeResourceUsageRecords        int `json:"hugeResourceUsageRecords"`        // 资源使用异常记录数
	DistinctDigestHugeResourceUsage int `json:"distinctDigestHugeResourceUsage"` // 不同的异常 digest 数量
	HitMemMaxThresholdDigest        int `json:"hitMemMaxThresholdDigest"`        // 命中内存阈值的 digest 数
	HitQueryTimeThresholdDigest     int `json:"hitQueryTimeThresholdDigest"`     // 命中查询时间阈值的 digest 数
}

// ConvertToHealthCheckResponse 将 AnalysisResult 转换为健康检查响应格式
func (a *SlowQueryAnalyzer) ConvertToHealthCheckResponse(result *AnalysisResult, memThreshold int64, queryTimeThreshold int) *HealthCheckAnalysisResponse {
	// 计算统计信息
	totalRecords := 0
	hugeResourceUsageRecords := 0
	distinctDigestCount := len(result.Patterns)
	hitMemDigests := 0
	hitQueryTimeDigests := 0

	for _, pattern := range result.Patterns {
		totalRecords += pattern.HitRecords
		hugeResourceUsageRecords += pattern.HitRecords
		if pattern.HitMemRecords > 0 {
			hitMemDigests++
		}
		if pattern.HitQueryTimeRecords > 0 {
			hitQueryTimeDigests++
		}
	}

	// 转换结果：内存字节转 KB，时间保持秒，保留2位小数
	convertedResults := make([]*PatternResult, len(result.Patterns))
	for i, pattern := range result.Patterns {
		convertedResults[i] = &PatternResult{
			Digest:              pattern.Digest,
			SampleQuery:         pattern.SampleQuery,
			HitRecords:          pattern.HitRecords,
			HitMemRecords:       pattern.HitMemRecords,
			HitQueryTimeRecords: pattern.HitQueryTimeRecords,
			AvgMaxMemB:          roundToTwoDecimals(pattern.AvgMaxMemB), // byte
			MaxMaxMemB:          roundToTwoDecimals(pattern.MaxMaxMemB), // byte
			// AvgMaxMemKB:         roundToTwoDecimals(pattern.AvgMaxMemB),    // byte
			// MaxMaxMemKB:         roundToTwoDecimals(pattern.MaxMaxMemB),    // byte
			AvgQueryTimeS: roundToTwoDecimals(pattern.AvgQueryTimeS), // 秒，保留2位小数
			MaxQueryTimeS: roundToTwoDecimals(pattern.MaxQueryTimeS), // 秒，保留2位小数
			DigestDetail:  pattern.DigestDetail,
		}
	}

	return &HealthCheckAnalysisResponse{
		Name:        "hugeResourceUsage_slow_query",
		Description: "Detects slow queries with high memory usage or long execution time",
		CheckType:   "sql",
		Status:      "success",
		Params: map[string]interface{}{
			"memMaxThreshold":    memThreshold,
			"queryTimeThreshold": queryTimeThreshold,
		},
		Comment: "Unit description: avg_max_mem_b/max_max_mem_b are in bytes, avg_query_time_s/max_query_time_s are in seconds",
		Summary: HealthCheckSummary{
			TotalRecords:                    totalRecords,
			HugeResourceUsageRecords:        hugeResourceUsageRecords,
			DistinctDigestHugeResourceUsage: distinctDigestCount,
			HitMemMaxThresholdDigest:        hitMemDigests,
			HitQueryTimeThresholdDigest:     hitQueryTimeDigests,
		},
		Results: convertedResults,
	}
}

// roundToTwoDecimals 保留2位小数
func roundToTwoDecimals(value float64) float64 {
	return float64(int(value*100+0.5)) / 100
}

// Analyze 执行慢查询分析（按 digest 聚合）
func (a *SlowQueryAnalyzer) Analyze(ctx context.Context, req *AnalyzeRequest) (*AnalysisResult, error) {
	// 获取阈值（支持动态配置）
	memThreshold := int64(HugeMemoryThreshold)
	if req.Params.MemMaxThreshold != nil {
		memThreshold = *req.Params.MemMaxThreshold
	}
	queryTimeThreshold := int64(SlowQueryTimeThreshold)
	if req.Params.QueryTimeThreshold != nil {
		queryTimeThreshold = int64(*req.Params.QueryTimeThreshold)
	}

	// 1. 从 time_range 提取日期范围
	startDate := req.TimeRange.Start.Format("20060102") // 如 "20260303"
	endDate := req.TimeRange.End.Format("20060102")

	// 2. 构建聚合分析 SQL
	sql := a.buildAggregateSQLWithThreshold(req.ClusterId, startDate, endDate, req.TimeRange, memThreshold, queryTimeThreshold)
	log.Info(ctx, "Aggregate Analysis SQL", zap.String("sql", sql))

	// 3. 调用 DataProxy 查询（设置 15 分钟超时）
	queryOptions := dto.DataProxyQueryOptions{
		DataSource: req.ClusterUrls.SlowQueryS3URL,
		Region:     req.Region,
		Vendor:     req.Provider,
	}

	result, err := a.dataProxyService.Query(ctx, &dto.DataProxyQueryRequest{
		ClusterId: req.ClusterId,
		SQL:       sql,
		Options:   queryOptions,
		Timeout:   15 * time.Minute,
	})
	if err != nil {
		return nil, fmt.Errorf("query failed: %w", err)
	}
	log.Info(ctx, "Aggregate Analysis Result", zap.Int("digest_count", int(result.Metadata.RowCount)))

	// 4. 解析聚合结果
	patterns := make([]*PatternResult, 0)
	totalHitRecords := 0
	hitMemCount := 0
	hitQueryTimeCount := 0

	for _, row := range result.Rows {
		item := a.rowToMap(row, result.Columns)

		// 使用 ShouldKeep() 过滤非 SELECT 查询
		sampleQuery := getString(item, "sample_query")
		if !ShouldKeep(sampleQuery) {
			continue // 跳过非 SELECT 查询
		}

		hitRecords := getInt(item, "hit_records")
		hitMemRecords := getInt(item, "hit_mem_records")
		hitQueryTimeRecords := getInt(item, "hit_query_time_records")

		patterns = append(patterns, &PatternResult{
			Digest:              getString(item, "digest"),
			SampleQuery:         sampleQuery,
			HitRecords:          hitRecords,
			HitMemRecords:       hitMemRecords,
			HitQueryTimeRecords: hitQueryTimeRecords,
			AvgMaxMemB:          getFloat64(item, "avg_max_mem"), // byte
			MaxMaxMemB:          getFloat64(item, "max_max_mem"), // byte
			AvgQueryTimeS:       getFloat64(item, "avg_query_time"),
			MaxQueryTimeS:       getFloat64(item, "max_query_time"),
		})

		totalHitRecords += hitRecords
		if hitMemRecords > 0 {
			hitMemCount++
		}
		if hitQueryTimeRecords > 0 {
			hitQueryTimeCount++
		}
	}

	// 统计信息
	statistics := map[string]int{
		"total_digests":          len(patterns),
		"total_hit_records":      totalHitRecords,
		"hit_mem_digests":        hitMemCount,
		"hit_query_time_digests": hitQueryTimeCount,
	}

	return &AnalysisResult{
		TotalScanned:  totalHitRecords,
		FilteredCount: totalHitRecords,
		PatternCount:  len(patterns),
		Patterns:      patterns,
		Statistics:    statistics,
	}, nil
}

func (a *SlowQueryAnalyzer) buildAggregateSQLWithThreshold(clusterId, startDate, endDate string, timeRange dto.TimeRange, memThreshold, queryTimeThreshold int64, ) string {
	dateList := a.generateDateRange(startDate, endDate)
	dateInClause := "'" + strings.Join(dateList, "','") + "'"

	timeFilter := ""
	if !timeRange.Start.IsZero() && !timeRange.End.IsZero() {
		timeFilter = fmt.Sprintf(`AND time BETWEEN %d AND %d`,
			timeRange.Start.Unix(), timeRange.End.Unix())
	} else if !timeRange.Start.IsZero() {
		timeFilter = fmt.Sprintf(`AND time >= %d`, timeRange.Start.Unix())
	} else if !timeRange.End.IsZero() {
		timeFilter = fmt.Sprintf(`AND time <= %d`, timeRange.End.Unix())
	}

	return fmt.Sprintf(`
		SELECT
			digest,
			MIN(Query) AS sample_query,
			COUNT(*) AS hit_records,
			SUM(CASE WHEN Mem_max > %d THEN 1 ELSE 0 END) AS hit_mem_records,
			SUM(CASE WHEN Query_time > %d THEN 1 ELSE 0 END) AS hit_query_time_records,
			AVG(Mem_max) AS avg_max_mem,
			MAX(Mem_max) AS max_max_mem,
			AVG(Query_time) AS avg_query_time,
			MAX(Query_time) AS max_query_time
		FROM clinic_data_proxy.slow_query_logs
		WHERE date IN (%s)
		%s

		-- 只分析 SELECT（允许注释开头）
		AND (
			lower(trim(Query)) LIKE 'select%%'
			OR lower(trim(Query)) LIKE '/*%%'
			OR lower(trim(Query)) LIKE '--%%'
		)

		-- 排除 tiflash
		AND NOT (
			lower(Query) LIKE '%%read_from_storage(tiflash%%'
			AND lower(Query) NOT LIKE '%%/*+ read_from_storage(tiflash%%'
		)

		-- 排除 force index
		AND NOT lower(Query) LIKE '%%force index%%'

		-- ⭐ 只保留命中规则的数据（关键优化）
		AND (
			Mem_max > %d
			OR Query_time > %d
		)

		GROUP BY digest
		ORDER BY hit_records DESC
		LIMIT 10000
	`,
		memThreshold,
		queryTimeThreshold,
		dateInClause,
		timeFilter,
		memThreshold,
		queryTimeThreshold,
	)
}

// generateDateRange 生成日期范围列表
func (a *SlowQueryAnalyzer) generateDateRange(startDate, endDate string) []string {
	if startDate == endDate {
		return []string{startDate}
	}

	start, err := time.Parse("20060102", startDate)
	if err != nil {
		return []string{startDate}
	}
	end, err := time.Parse("20060102", endDate)
	if err != nil {
		return []string{startDate, endDate}
	}

	var dates []string
	for d := start; !d.After(end) && len(dates) < 31; d = d.AddDate(0, 0, 1) {
		dates = append(dates, d.Format("20060102"))
	}

	return dates
}

// parseDecodedPlan 解析 decoded_plan 字符串为 JSON
func (a *SlowQueryAnalyzer) parseDecodedPlan(item map[string]interface{}) map[string]interface{} {
	decodedPlanStr := getString(item, "decoded_plan")
	if decodedPlanStr == "" {
		return nil
	}

	var planMap map[string]interface{}
	if err := json.Unmarshal([]byte(decodedPlanStr), &planMap); err != nil {
		return nil
	}

	return planMap
}

// hasTableFullScan 递归检查全表扫描
func (a *SlowQueryAnalyzer) hasTableFullScan(node interface{}) bool {
	nodeMap, ok := node.(map[string]interface{})
	if !ok {
		return false
	}

	name := getString(nodeMap, "name")
	matched, _ := regexp.MatchString(`TableFullScan_\d+`, name)
	if matched {
		storeType := getString(nodeMap, "storeType")
		estRows := getFloat64(nodeMap, "estRows")

		if estRows > 100000 && storeType != "tiflash" {
			return true
		}
	}

	// 递归检查 children
	if children, ok := nodeMap["children"].([]interface{}); ok {
		for _, child := range children {
			if a.hasTableFullScan(child) {
				return true
			}
		}
	}

	return false
}

// hasIndexFullScan 递归检查索引全扫描
func (a *SlowQueryAnalyzer) hasIndexFullScan(node interface{}) bool {
	nodeMap, ok := node.(map[string]interface{})
	if !ok {
		return false
	}

	name := getString(nodeMap, "name")
	matched, _ := regexp.MatchString(`IndexFullScan_\d+`, name)
	if matched {
		storeType := getString(nodeMap, "storeType")
		estRows := getFloat64(nodeMap, "estRows")

		if estRows > 100000 && storeType != "tiflash" {
			return true
		}
	}

	// 递归检查 children
	if children, ok := nodeMap["children"].([]interface{}); ok {
		for _, child := range children {
			if a.hasIndexFullScan(child) {
				return true
			}
		}
	}

	return false
}

// hasCopTaskNumGt100 检查 cop_task.num > 100
func (a *SlowQueryAnalyzer) hasCopTaskNumGt100(node interface{}) bool {
	nodeMap, ok := node.(map[string]interface{})
	if !ok {
		return false
	}

	// 检查 rootGroupExecInfo 中是否包含 cop_task: {num: > 100
	if rootGroupExecInfo, ok := nodeMap["rootGroupExecInfo"].([]interface{}); ok {
		for _, info := range rootGroupExecInfo {
			if infoStr, ok := info.(string); ok {
				if strings.Contains(infoStr, "cop_task") {
					re := regexp.MustCompile(`num:\s*(\d+)`)
					if match := re.FindStringSubmatch(infoStr); len(match) == 2 {
						if num, err := strconv.Atoi(match[1]); err == nil && num > 100 {
							return true
						}
					}
				}
			}
		}
	}

	// 递归检查 children
	if children, ok := nodeMap["children"].([]interface{}); ok {
		for _, child := range children {
			if a.hasCopTaskNumGt100(child) {
				return true
			}
		}
	}

	return false
}

// rowToMap 将行数据转换为 map
func (a *SlowQueryAnalyzer) rowToMap(row []interface{}, columns []string) map[string]interface{} {
	result := make(map[string]interface{})
	for i, col := range columns {
		if i < len(row) {
			result[col] = row[i]
		}
	}
	return result
}

// Helper functions
func getString(m map[string]interface{}, key string) string {
	if v, ok := m[key]; ok {
		if s, ok := v.(string); ok {
			return s
		}
	}
	return ""
}

func getFloat64(m map[string]interface{}, key string) float64 {
	if v, ok := m[key]; ok {
		switch val := v.(type) {
		case float64:
			return val
		case string:
			if f, err := strconv.ParseFloat(val, 64); err == nil {
				return f
			}
		case int:
			return float64(val)
		case int64:
			return float64(val)
		}
	}
	return 0
}

func getInt(m map[string]interface{}, key string) int {
	if v, ok := m[key]; ok {
		switch val := v.(type) {
		case int:
			return val
		case int64:
			return int(val)
		case float64:
			return int(val)
		case string:
			if i, err := strconv.Atoi(val); err == nil {
				return i
			}
		}
	}
	return 0
}

func getBool(m map[string]interface{}, key string) bool {
	if v, ok := m[key]; ok {
		if b, ok := v.(bool); ok {
			return b
		}
	}
	return false
}

func getMap(m map[string]interface{}, key string) map[string]interface{} {
	if v, ok := m[key]; ok {
		if mapVal, ok := v.(map[string]interface{}); ok {
			return mapVal
		}
	}
	return nil
}

// QueryDigestDetails 查询每个 digest 的 Top2 详情记录（按 mem_max 和 query_time），并生成 link
func (a *SlowQueryAnalyzer) QueryDigestDetails(ctx context.Context, req *AnalyzeRequest, patterns []*PatternResult, params *DigestDetailParams) error {
	// 1. 从 patterns 中收集命中 mem/query_time 规则的 digest 列表
	var memDigests, queryTimeDigests []string
	patternMap := make(map[string]*PatternResult) // digest -> PatternResult
	for _, p := range patterns {
		patternMap[p.Digest] = p
		if p.HitMemRecords > 0 {
			memDigests = append(memDigests, p.Digest)
		}
		if p.HitQueryTimeRecords > 0 {
			queryTimeDigests = append(queryTimeDigests, p.Digest)
		}
	}

	if len(memDigests) == 0 && len(queryTimeDigests) == 0 {
		log.Info(ctx, "No digests hit any rule, skip digest detail query")
		return nil
	}

	// 2. 获取阈值
	memThreshold := int64(HugeMemoryThreshold)
	if req.Params.MemMaxThreshold != nil {
		memThreshold = *req.Params.MemMaxThreshold
	}
	queryTimeThreshold := int64(SlowQueryTimeThreshold)
	if req.Params.QueryTimeThreshold != nil {
		queryTimeThreshold = int64(*req.Params.QueryTimeThreshold)
	}

	// 3. 构建日期和时间过滤
	startDate := req.TimeRange.Start.Format("20060102")
	endDate := req.TimeRange.End.Format("20060102")
	dateList := a.generateDateRange(startDate, endDate)
	dateInClause := "'" + strings.Join(dateList, "','") + "'"

	timeFilter := ""
	if !req.TimeRange.Start.IsZero() && !req.TimeRange.End.IsZero() {
		timeFilter = fmt.Sprintf(`AND time BETWEEN %d AND %d`, req.TimeRange.Start.Unix(), req.TimeRange.End.Unix())
	} else if !req.TimeRange.Start.IsZero() {
		timeFilter = fmt.Sprintf(`AND time >= %d`, req.TimeRange.Start.Unix())
	} else if !req.TimeRange.End.IsZero() {
		timeFilter = fmt.Sprintf(`AND time <= %d`, req.TimeRange.End.Unix())
	}

	// 4. 构建 SQL
	sql := a.buildDigestDetailSQL(dateInClause, timeFilter, memThreshold, queryTimeThreshold, memDigests, queryTimeDigests)
	log.Info(ctx, "Digest Detail SQL", zap.String("sql", sql))

	// 5. 执行查询
	queryOptions := dto.DataProxyQueryOptions{
		DataSource: req.ClusterUrls.SlowQueryS3URL,
		Region:     req.Region,
		Vendor:     req.Provider,
	}

	result, err := a.dataProxyService.Query(ctx, &dto.DataProxyQueryRequest{
		ClusterId: req.ClusterId,
		SQL:       sql,
		Options:   queryOptions,
		Timeout:   15 * time.Minute,
	})
	if err != nil {
		return fmt.Errorf("digest detail query failed: %w", err)
	}
	log.Info(ctx, "Digest Detail Result", zap.Int("row_count", int(result.Metadata.RowCount)))

	// 6. 解析结果并填充到 patterns
	for _, row := range result.Rows {
		item := a.rowToMap(row, result.Columns)
		digest := getString(item, "digest")
		connID := getString(item, "conn_id")
		memMax := getFloat64(item, "mem_max")
		queryTime := getFloat64(item, "query_time")
		source := getString(item, "source")

		// time 字段是 DOUBLE 类型，需要格式化为普通小数（如 1774855218.870357），避免科学计数法
		timeFloat := getFloat64(item, "time")
		timeVal := strconv.FormatFloat(timeFloat, 'f', -1, 64)

		link := buildDetailLink(
			params.ClinicBaseURL,
			params.Provider,
			params.Region,
			params.OrgId,
			params.ProjectId,
			params.ClusterID,
			params.DeployType,
			digest, connID, timeVal,
		)

		record := &DigestDetailRecord{
			ConnID:    connID,
			Time:      timeVal,
			MemMax:    memMax,
			QueryTime: queryTime,
			Source:    source,
			Link:      link,
		}

		if p, ok := patternMap[digest]; ok {
			p.DigestDetail = append(p.DigestDetail, record)
		}
	}

	return nil
}

// buildDigestDetailSQL 构建 digest 详情 Top2 查询 SQL
/*
SELECT
  digest,
  conn_id,
  time,
  mem_max,
  query_time,
  source
FROM (

  -- ✅ Mem_max 规则 Top2
  SELECT
    digest,
    conn_id,
    time,
    mem_max,
    query_time,
    'hit_mem' AS source
  FROM (
    SELECT
      digest,
      conn_id,
      time,
      mem_max,
      query_time,
      ROW_NUMBER() OVER (
        PARTITION BY digest
        ORDER BY mem_max DESC
      ) AS rn
    FROM clinic_data_proxy.slow_query_logs_10989049060142230334
    WHERE date IN ('20260327')
      AND digest IN (
        'dc76d3d871202d9db6dc595ceef2f7d50f2e0bd4fa9f7532a3b2045553b7c176',
        '5118b05f28adc1eee92481558ffca423ddd1fda4b046a05a20ab8125dae45e64'
      )
      AND mem_max > 668709120
  ) t1
  WHERE rn <= 2

  UNION ALL

  -- ✅ Query_time 规则 Top2
  SELECT
    digest,
    conn_id,
    time,
    mem_max,
    query_time,
    'hit_query_time' AS source
  FROM (
    SELECT
      digest,
      conn_id,
      time,
      mem_max,
      query_time,
      ROW_NUMBER() OVER (
        PARTITION BY digest
        ORDER BY query_time DESC
      ) AS rn
    FROM clinic_data_proxy.slow_query_logs_10989049060142230334
    WHERE date IN ('20260327')
      AND digest IN (
        'dc76d3d871202d9db6dc595ceef2f7d50f2e0bd4fa9f7532a3b2045553b7c176',
        '5118b05f28adc1eee92481558ffca423ddd1fda4b046a05a20ab8125dae45e64'
      )
      AND query_time > 8
  ) t2
  WHERE rn <= 2

) final;
*/
func (a *SlowQueryAnalyzer) buildDigestDetailSQL(dateInClause, timeFilter string, memThreshold, queryTimeThreshold int64, memDigests, queryTimeDigests []string) string {
	var parts []string

	// mem_max Top2 部分
	if len(memDigests) > 0 {
		digestInClause := "'" + strings.Join(memDigests, "','") + "'"
		memPart := fmt.Sprintf(`
		SELECT digest, conn_id, time, mem_max, query_time, source FROM (
			SELECT
				digest, conn_id, time, mem_max, query_time,
				'hit_mem_top2' AS source,
				ROW_NUMBER() OVER (PARTITION BY digest ORDER BY mem_max DESC) AS rn
			FROM clinic_data_proxy.slow_query_logs
			WHERE date IN (%s) %s
				AND digest IN (%s)
				AND mem_max > %d
		) t1 WHERE rn <= 2`, dateInClause, timeFilter, digestInClause, memThreshold)
		parts = append(parts, memPart)
	}

	// query_time Top2 部分
	if len(queryTimeDigests) > 0 {
		digestInClause := "'" + strings.Join(queryTimeDigests, "','") + "'"
		queryTimePart := fmt.Sprintf(`
		SELECT digest, conn_id, time, mem_max, query_time, source FROM (
			SELECT
				digest, conn_id, time, mem_max, query_time,
				'hit_query_time_top2' AS source,
				ROW_NUMBER() OVER (PARTITION BY digest ORDER BY query_time DESC) AS rn
			FROM clinic_data_proxy.slow_query_logs
			WHERE date IN (%s) %s
				AND digest IN (%s)
				AND query_time > %d
		) t2 WHERE rn <= 2`, dateInClause, timeFilter, digestInClause, queryTimeThreshold)
		parts = append(parts, queryTimePart)
	}

	return strings.Join(parts, "\n\t\tUNION ALL\n")
}

// buildDetailLink 生成 Clinic Dashboard 慢查询详情页 link
func buildDetailLink(clinicBaseURL, provider, region, orgId, projectId, clusterId, deployType, digest, connID, timestamp string) string {
	return fmt.Sprintf("%s%s?provider=%s&region=%s&orgId=%s&projectId=%s&clusterId=%s&deployType=%s#/slow_query/detail?digest=%s&connection_id=%s&timestamp=%s",
		clinicBaseURL, ClinicNGMPath,
		provider, region, orgId, projectId, clusterId, deployType,
		digest, connID, timestamp)
}