package query

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"io"
	"io/ioutil"
	"os"
	"sort"
	"strings"
	"time"

	"github.com/duke-git/lancet/v2/slice"
	"github.com/pingcap-inc/clinic/clinic_data_service/pkg/common"
	"github.com/pingcap-inc/clinic/clinic_data_service/pkg/config"
	"github.com/pingcap-inc/clinic/clinic_data_service/pkg/constant"
	"github.com/pingcap-inc/clinic/clinic_data_service/pkg/dao"
	"github.com/pingcap-inc/clinic/clinic_data_service/pkg/dto"
	log2 "github.com/pingcap-inc/clinic/clinic_data_service/pkg/log"
	"github.com/pingcap-inc/clinic/clinic_data_service/pkg/model"
	"github.com/pingcap-inc/clinic/clinic_data_service/pkg/prometheus"
	"github.com/pingcap-inc/clinic/clinic_data_service/pkg/s3"

	_ "github.com/mattn/go-sqlite3"
	"go.uber.org/zap"
)

type QuerySqlLite struct {
	Org       dto.Org
	DBManager *dao.SQLiteManager
	Day       string
}

// Close Close database connection
func (q *QuerySqlLite) Close() error {
	if q.DBManager != nil {
		return q.DBManager.Close()
	}

	return nil
}

// NewQuerySqlLite Create a new QuerySqlLite instance
func NewQuerySqlLite(ctx context.Context, org dto.Org) (*QuerySqlLite, error) {

	tableName := config.AppConfig.Day
	dbPath := config.GetDbPath(org.OrgID)
	log2.Info(ctx, "dbPath:"+dbPath)
	// Initialize database manager, todo remove
	dbManager, err := dao.NewSQLiteManager(dbPath, tableName)
	ormDB, err := dao.InitSQLiteDB(dbPath)
	dbManager.SqlLiteDB = ormDB

	if err != nil {
		log2.PlainError("failed to create database manager ", zap.Error(err))
		return nil, err
	}
	// Note: Database connection is not closed here, the caller is responsible for closing it
	return &QuerySqlLite{
		Org:       org,
		DBManager: dbManager,
		Day:       tableName,
	}, nil
}

// RunS3SlowQueryAnalysis Run S3 slow query analysis and send Feishu report [This one]
// queryItem Structure representing a query item
type queryItem struct {
	Name   string
	Config config.QueryConfig
}

// convertS3Stats Convert s3Stats to local S3Statistics structure
func convertS3Stats(s3Stats interface{}) (s3.S3Statistics, error) {
	var localS3Stats s3.S3Statistics
	if statsMap, ok := s3Stats.(map[string]interface{}); ok {
		if val, exists := statsMap["slow_query_count"]; exists {
			if count, ok := val.(int); ok {
				localS3Stats.SlowQueryCount = count
			}
		}
		if val, exists := statsMap["slow_query_pattern_count"]; exists {
			if count, ok := val.(int); ok {
				localS3Stats.SlowQueryPatternCount = count
			}
		}
		if val, exists := statsMap["slow_query_analyze_count"]; exists {
			if count, ok := val.(int); ok {
				localS3Stats.SlowQueryAnalyzeCount = count
			}
		}
		if val, exists := statsMap["slow_query_ddl_count"]; exists {
			if count, ok := val.(int); ok {
				localS3Stats.SlowQueryDDLCount = count
			}
		}
	} else if stats, ok := s3Stats.(s3.S3Statistics); ok {
		localS3Stats = stats
	} else {
		return localS3Stats, fmt.Errorf("invalid s3Stats type")
	}
	return localS3Stats, nil
}

// PatternCount Fill summary statistics information
func (q *QuerySqlLite) PatternCount(summary *dto.Summary) error {
	db := q.DBManager.DB

	totalSql := fmt.Sprintf("SELECT COUNT(*) FROM `%s`", config.AppConfig.Day)

	row := db.QueryRow(totalSql)
	if err := row.Scan(&summary.TotalSlowQueries); err != nil {
		return err
	}
	log2.PlainInfo("TotalSlowQueries:" + fmt.Sprintf("%d", summary.TotalSlowQueries))

	PatternCountSql := "SELECT %s FROM `%s` WHERE Pattern_Name > ''"

	row = db.QueryRow(fmt.Sprintf(PatternCountSql, "COUNT(*)", config.AppConfig.Day))
	if err := row.Scan(&summary.TotalPatterns); err != nil {
		log2.PlainError("failed to get total slow query total count: %v", zap.Error(err))
		return err
	}
	log2.PlainInfo("TotalPatterns:" + fmt.Sprintf("%d", summary.TotalPatterns))

	row = db.QueryRow(fmt.Sprintf(PatternCountSql, "COUNT(DISTINCT Digest)", config.AppConfig.Day))
	if err := row.Scan(&summary.UniqueDigests); err != nil {
		log2.PlainError("failed to get unique digest count: %v", zap.Error(err))
		return err
	}
	log2.PlainInfo("UniqueDigests:" + fmt.Sprintf("%d", summary.UniqueDigests))

	return nil
}

// prepareSortedQueries Prepare and sort query items
func (q *QuerySqlLite) prepareSortedQueries() []queryItem {
	var sortedQueries []queryItem
	for name, queryConfig := range config.AppConfig.Queries {
		// Use patternName as the Name of queryItem, if empty use the map key
		queryName := queryConfig.PatternName
		if queryName == "" {
			queryName = name
		}
		sortedQueries = append(sortedQueries, queryItem{Name: queryName, Config: queryConfig})
	}

	sort.Slice(sortedQueries, func(i, j int) bool {
		return sortedQueries[i].Config.Order < sortedQueries[j].Config.Order
	})
	return sortedQueries
}

// processCriticalQuery Process Critical level queries
func (q *QuerySqlLite) processCriticalQuery(ctx context.Context, db *sql.DB, name string, queryConfig config.QueryConfig) (dto.PatternResult, error) {
	var TopResultArr []dto.TopResult
	var newTopResultArr []dto.TopResult
	PatternResult := dto.PatternResult{
		PatternName: name,
		Level:       queryConfig.Level,
	}

	// Process SQL template
	processedSQL, err := config.ProcessSQLTemplateGlobal(queryConfig.SQL)
	if err != nil {
		return PatternResult, err
	}

	rows, err := db.Query(processedSQL)
	if err != nil {
		return PatternResult, err
	}
	defer rows.Close()
	// var TopResultArr []dto.TopResult
	var digests []string
	for rows.Next() {
		var res dto.TopResult
		if err := rows.Scan(&res.Digest, &res.AVGMemMax, &res.MAXMem, &res.AVGQueryTime, &res.MAXQueryTime, &res.AvgRU, &res.SumRU, &res.TotalCount); err != nil {
			log2.PlainError("Failed to scan row for query "+name, zap.Error(err))
			continue
		}
		digests = append(digests, res.Digest)

		// Query detailed information for a single Digest
		// err = q.enrichTop10Result(&res)
		// if err != nil {
		// 	log2.PlainError("Failed to enrich result for digest "+res.Digest, zap.Error(err))
		// 	return PatternResult, err
		// }

		TopResultArr = append(TopResultArr, res)
	}

	// // Query the maximum count cluster ID for the current Digest
	maxCountClusterIdArr := q.getMaxCountClusterId(digests, queryConfig.PatternName)
	for i := range TopResultArr {
		topResut := &TopResultArr[i] // Get a pointer to the original element
		//
		err = q.enrichTop10Result(topResut, maxCountClusterIdArr)
		if err != nil {
			log2.PlainError("Failed to enrich result for digest "+topResut.Digest, zap.Error(err))
			return PatternResult, err
		}

	}

	if name == constant.PatternHugeResourceUsage {
		//  huge resource usage  delete ignore CpuTime
		newTopResultArr, err = q.checkLatencyAndCPUTime(ctx, TopResultArr)
		if err != nil {
			return PatternResult, err
		}
	} else {
		newTopResultArr = TopResultArr
	}

	PatternResult.CountGE10 = len(newTopResultArr)
	PatternResult.TopResult = newTopResultArr

	// Get total count
	// totalCountResult, err = q.getTotalCount(db, queryConfig, name)
	// if err != nil {
	// 	log2.PlainError("Failed to get total count for "+name, zap.Error(err))
	// 	return nil, totalCountResult, err
	// }

	return PatternResult, nil
}

// enrichTop10Result Use GORM to enrich Top10Result with detailed information
func (q *QuerySqlLite) enrichTop10Result(res *dto.TopResult, maxCountClusterIdArr []MaxCountClusterIdArr) error {

	ctx := context.Background()
	gormDB := q.DBManager.SqlLiteDB

	dao := common.NewCommonDAOWithDB(gormDB)
	slowQueryLogMax := model.SlowQueryLog{}
	param := common.ComFilter{
		ModelFilters: model.SlowQueryLog{
			Digest:    res.Digest,
			QueryTime: res.MAXQueryTime,
		},
	}
	err := dao.GetEntity(ctx, param, &slowQueryLogMax)
	if err != nil {
		log2.Error(ctx, "enrichTop10Result failed", zap.Error(err))
		return err
	}
	// log2.Info(ctx, "objs", zap.Any("objs", objs))
	if slowQueryLogMax.QueryTime == 0 {
		return nil
	}
	// select distinct ClusterID from slow_query_log where Digest = res.Digest
	var clusterIdArr []string
	gormDB.Model(&model.SlowQueryLog{}).
		Distinct("Cluster_Id").
		Where("Digest = ?", res.Digest).
		Order("Cluster_Id").
		Pluck("Cluster_Id", &clusterIdArr)

	// Convert clusterIdArr to comma-separated string
	// clusterIdStr := strings.Join(clusterIdArr, " , ")
	var clusterAliasArr []string
	for _, clusterID := range clusterIdArr {
		// cluster := config.GetClusterByID(clusterID)
		cluster, err := common.GetCluster(context.Background(), clusterID)
		if err != nil {
			log2.PlainError("Failed to get cluster", zap.String("cluster", clusterID), zap.Error(err))
			return err
		}

		if cluster != nil {
			alias := cluster.Alias
			if alias == "" {
				alias = cluster.ClusterName
			}
			clusterAliasArr = append(clusterAliasArr, alias)
		}
	}

	// Sort the cluster alias array
	sort.Strings(clusterAliasArr)
	clusterAliasStr := strings.Join(clusterAliasArr, " , ")

	// Fill the results
	res.ClusterIds = clusterIdArr
	res.Alias = clusterAliasStr
	res.Query = slowQueryLogMax.Query
	res.ConnID = fmt.Sprintf("%d", slowQueryLogMax.ConnID)
	res.FormattedTimestamp = slowQueryLogMax.FormattedTimestamp
	cluster, err := common.GetCluster(ctx, slowQueryLogMax.ClusterID)
	if err != nil {
		log2.PlainError("Failed to get cluster", zap.String("cluster", slowQueryLogMax.ClusterID), zap.Error(err))
		return err
	}

	deployType := "dedicated"
	// slow_query Detail link
	res.QueryLink = fmt.Sprintf("%s?provider=%s&region=%s&orgId=%s&projectId=%s&clusterId=%s&clusterName=%s&deployType=%s#/slow_query/detail?digest=%s&connection_id=%s&timestamp=%s",
		config.AppConfig.Clinic.BaseUrl,
		cluster.Provider,
		cluster.Region,
		cluster.OrgID,
		cluster.ProjectID,
		cluster.ClusterID,
		cluster.ClusterName,
		deployType,
		res.Digest,
		res.ConnID,
		res.FormattedTimestamp)

	//   topSqlUrl: "https://clinic.pingcap.com/portal/dashboard/cloud/ngm.html?provider=%s&region=%s&orgId=%s&projectId=%s&clusterId=%s&clusterName=%s&deployType=dedicated#/topsql?from=%d&to=%d"

	// log2.PlainInfo("QueryLink: "+res.QueryLink)

	isEven := func(i int, obj MaxCountClusterIdArr) bool {
		return obj.Digest == res.Digest
	}

	maxCountClusterId := cluster.ClusterID
	result, ok := slice.FindBy(maxCountClusterIdArr, isEven)
	if ok {
		maxCountClusterId = result.MaxCountClusterId
	}

	from, _, err := s3.GetSlowQueryCheckTime(config.AppConfig.Day)
	to := from.Add(24 * time.Hour)
	if err != nil {
		log2.PlainError("Failed to get slow query check time", zap.Error(err))
		return err
	}
	// slow query List link
	res.Link = fmt.Sprintf("%s?provider=%s&region=%s&orgId=%s&projectId=%s&clusterId=%s&deployType=%s#/slow_query?show_internal=true&digest=%s&from=%d&row=0&to=%d",
		config.AppConfig.Clinic.BaseUrl,
		cluster.Provider,
		cluster.Region,
		cluster.OrgID,
		cluster.ProjectID,
		maxCountClusterId,
		deployType,
		res.Digest,
		from.Unix(), // from 5:00 utc
		to.Unix())   // to 4:59

	// log2.PlainInfo("Link: "+res.Link)

	return nil
}
func (q *QuerySqlLite) getMaxCountClusterId(digestArr []string, patternName string) []MaxCountClusterIdArr {
	var res []MaxCountClusterIdArr
	if len(digestArr) == 0 {
		return nil
	}
	// 1、 get max count cluster id
	Sql := `SELECT 
			a.Digest,
			(
				SELECT b.Cluster_Id
				FROM %s b
				WHERE b.Pattern_Name = '%s'
				AND b.Digest = a.Digest
				GROUP BY b.Cluster_Id
				ORDER BY COUNT(*) DESC, b.Cluster_Id   
				LIMIT 1
			) AS MaxCountClusterId
		FROM %s a
		WHERE a.Pattern_Name = '%s'
		AND a.Digest IN (?)
		GROUP BY a.Digest`

	tableName := model.SlowQueryLog{}.TableName()
	tableName = fmt.Sprintf("`%s` ", tableName)
	// patternName = fmt.Sprintf("'%s'", patternName)
	Sql = fmt.Sprintf(Sql, tableName, patternName, tableName, patternName)
	db := q.DBManager.SqlLiteDB
	log2.PlainInfo(Sql)

	if err := db.Raw(Sql, digestArr).Scan(&res).Error; err != nil {
		panic(err)
		// return nil
	}
	return res
}

func (q *QuerySqlLite) checkLatencyAndCPUTime(ctx context.Context, topRes []dto.TopResult) ([]dto.TopResult, error) {

	newTopResult := make([]dto.TopResult, 0)
	// 1、 get latency slow query log , queryTime - responseTotal > 40s
	slowQueryLogs, err := q.getLatencySlowQueryLog(topRes)
	if err != nil {
		log2.PlainError("GetLatencySlowQueryLog failed", zap.Error(err))
		return newTopResult, err
	}
	var LatencyDigestArr []string
	for _, log := range slowQueryLogs {
		LatencyDigestArr = append(LatencyDigestArr, log.Digest)
	}

	// 2、 check cput time, get topRes cpu time
	for _, res := range topRes {

		isLatency := slice.Contain(LatencyDigestArr, res.Digest)

		// a、 CPUTimeIn5MinWindow
		maxCPUTimeIn5MinOfDay, err := prometheus.GetMaxCPUTimeIn5MinWindowFromClusterArr(res.ClusterIds, res.Digest)
		if err != nil {
			log2.PlainError("GetMaxCPUTimeIn5MinWindow failed", zap.Error(err))
			return newTopResult, err
		}
		isHitMaxCPUTimeIn5MinOfDay := maxCPUTimeIn5MinOfDay.MaxCPUTimeIn5Min > 16

		// b、 TotalCPUTimeInDay
		totalCPUTimeResp, err := prometheus.GetMaxTotalCPUTimeFromClusterArr(res.ClusterIds, res.Digest)
		if err != nil {
			log2.PlainError("GetMaxTotalCPUTimeFromClusterArr failed", zap.Error(err))
			return newTopResult, err
		}
		isHitTotalCPUTimeInDay := totalCPUTimeResp.MaxTotalCpuTime > 4000

		// if maxCPUTimeIn5MinOfDay.MaxValue > 1 || totalCPUTime > 1 {
		res.MaxCPUTimeIn5MinWindow = maxCPUTimeIn5MinOfDay.MaxCPUTimeIn5Min
		res.TotalCPUTimeInDay = totalCPUTimeResp.MaxTotalCpuTime
		res.MaxValueTimestamp = maxCPUTimeIn5MinOfDay.Timestamp
		msg := fmt.Sprintf("digest: %s , MaxCPUTimeIn5MinWindow(16min): %f, TotalCPUTime(4000min): %f", res.Digest, maxCPUTimeIn5MinOfDay.MaxCPUTimeIn5Min, totalCPUTimeResp.MaxTotalCpuTime)

		// if is top sql, set  TopSqlLink
		if isHitMaxCPUTimeIn5MinOfDay {
			res.Instance = maxCPUTimeIn5MinOfDay.Instance
			parsedTime, err := time.Parse("2006-01-02 15:04:05", maxCPUTimeIn5MinOfDay.Timestamp)
			if err != nil {
				log2.PlainError("Parse time failed", zap.Error(err))
				continue
			}

			from := parsedTime.Unix() - 15*60
			to := parsedTime.Unix() + 15*60
			cluster, err := common.GetCluster(ctx, maxCPUTimeIn5MinOfDay.ClusterId)
			if err != nil {
				log2.PlainError("Failed to get cluster", zap.String("cluster", maxCPUTimeIn5MinOfDay.ClusterId), zap.Error(err))
				continue
			}

			//https://clinic.pingcap.com/portal/dashboard/cloud/ngm.html?
			// provider=aws&region=us-east-1&orgId=1372813089209061633&projectId=1372813089454544954&clusterId=1379661944646416027&clusterName=prod-us-saas-region01-tidb-v75&deployType=dedicated#/topsql?instance=db-tikv-163.db-tikv-peer.tidb1379661944646416027.svc%3A20160&from=1755544920&to=1755546720
			res.TopSqlLink = fmt.Sprintf("%s?provider=%s&region=%s&orgId=%s&projectId=%s&clusterId=%s&clusterName=%s&deployType=dedicated#/topsql?instance=%s&from=%d&to=%d",
				config.AppConfig.Clinic.BaseUrl,
				cluster.Provider,
				cluster.Region,
				cluster.OrgID,
				cluster.ProjectID,
				cluster.ClusterID,
				cluster.ClusterName,
				res.Instance,
				from,
				to)

			log2.PlainInfo(msg)

		}
		// add to topsql:  MaxValue > 16 min   or totalCPUTime > 4000 min  or (queryTime - responseTotal > 40s)
		if isLatency || isHitMaxCPUTimeIn5MinOfDay || isHitTotalCPUTimeInDay {
			newTopResult = append(newTopResult, res)
		}

	}

	// countResult.TopResult = newTopResult

	// log2.PlainInfo(fmt.Sprintf("CountGE10[%d]=CountGE10[%d]-ignoreCpuNum[%d] ", countResult.CountGE10, oldCount, oldCount - len(newTopResult)))
	return newTopResult, nil
}

// getTotalCount Get the total count of queries
func (q *QuerySqlLite) getTotalCount(queryConfig config.QueryConfig, name string) (dto.PatternResult, error) {
	var countRes dto.PatternResult
	countSqlTemplate := queryConfig.CountSql
	if countSqlTemplate == "" {
		countSqlTemplate = config.AppConfig.CommonSql.CountSql
	}

	countSQL, err := config.ProcessSQLTemplateWithPattern(countSqlTemplate, name)
	if err != nil {
		return countRes, fmt.Errorf("failed to process count SQL template: %v", err)
	}

	// Second pass to process any remaining template variables
	finalCountSQL, err := config.ProcessSQLTemplateWithPattern(countSQL, name)
	if err != nil {
		return countRes, fmt.Errorf("failed to process count SQL template second pass: %v", err)
	}

	// var totalCount int
	// err = db.QueryRow(finalCountSQL).Scan(&totalCount)
	SqlLiteDB := q.DBManager.SqlLiteDB

	err = SqlLiteDB.Raw(finalCountSQL).Scan(&countRes).Error
	countRes.PatternName = name
	countRes.Level = queryConfig.Level
	return countRes, err
}

// processMajorQuery Process Major level queries
func (q *QuerySqlLite) processMajorQuery(name string, queryConfig config.QueryConfig) (dto.PatternResult, error) {
	return q.getTotalCount(queryConfig, name)
}

// updateSummaryStats Update summary statistics
func (q *QuerySqlLite) updateSummaryStats(f *dto.FinalOutput) {
	// Initialize NewDigests field
	f.Summary.NewDigests = make(map[string]int)

	for _, queryResult := range f.QueryResults {
		totalCount10 := queryResult.TotalCount.CountGE10
		// sumCount:=queryResult.TotalCount.TotalOccurrences
		switch queryResult.Level {
		case "Critical":
			f.Summary.CriticalCount += totalCount10
			f.Summary.CriticalDetails[queryResult.QueryName] = totalCount10
		case "Major":
			f.Summary.MajorCount += totalCount10
			f.Summary.MajorDetails[queryResult.QueryName] = totalCount10
		}
		// f.Summary.UniqueDigests = f.Summary.CriticalCount + f.Summary.MajorCount
		// f.Summary.TotalSlowQueries += sumCount
	}
}

// saveResultToFile Save results to file
func (q *QuerySqlLite) saveResultToFile(finalOutput dto.FinalOutput) error {
	output, err := json.MarshalIndent(finalOutput, "", "  ")
	if err != nil {
		return fmt.Errorf("failed to marshal JSON: %v", err)
	}

	now := time.Now()
	dateStr := now.Format("2006-01-02-15-04") // YYYYMMDDHHMM format
	dir := fmt.Sprintf("%s/%s", config.AppConfig.ResultDir, finalOutput.Summary.OrgName)
	if _, err := os.Stat(dir); os.IsNotExist(err) {
		if err := os.MkdirAll(dir, 0755); err != nil {
			return fmt.Errorf("failed to create dir: %v", err)
		}
	}
	filename := fmt.Sprintf("%s/%s_%s.json", dir, config.AppConfig.Day, dateStr)
	err = ioutil.WriteFile(filename, output, 0644)
	if err != nil {
		return fmt.Errorf("failed to write to file: %v", err)
	}
	// no ctx here: keep plain log
	log2.PlainInfo("save parse result " + filename)
	return nil
}

// RunS3SlowQueryAnalysis Run S3 slow query analysis [Official version]
func (q *QuerySqlLite) RunS3SlowQueryAnalysis(ctx context.Context, s3Stats interface{}) (*dto.FinalOutput, error) {
	// Convert S3 statistics
	_, err := convertS3Stats(s3Stats)
	if err != nil {
		return nil, err
	}

	// Use the already initialized database manager
	var finalOutput dto.FinalOutput
	finalOutput.Summary.OrgID = q.Org.OrgID
	finalOutput.DocLink = q.Org.DocumentUrl
	finalOutput.Summary.Day = config.AppConfig.Day
	finalOutput.Summary.OrgName = q.Org.OrgName
	var queryResults []dto.QueryResult
	db := q.DBManager.DB

	// Summary data statistics
	if err = q.PatternCount(&finalOutput.Summary); err != nil {
		return nil, err
	}
	finalOutput.Summary.CriticalDetails = make(map[string]int)
	finalOutput.Summary.MajorDetails = make(map[string]int)

	// Prepare sorted query items
	sortedQueries := q.prepareSortedQueries()

	// Process each query item
	for _, item := range sortedQueries {
		name := item.Name
		queryConfig := item.Config
		log2.Info(ctx, "query item", zap.String("name", name))

		var totalCountResult dto.PatternResult
		// var top10 []dto.TopResult

		if queryConfig.Level == "Critical" {
			totalCountResult, err = q.processCriticalQuery(ctx, db, name, queryConfig)
			if err != nil {
				log2.Error(ctx, "Failed to process critical query "+name, zap.Error(err))
				// panic(err)

			}
			if totalCountResult.CountGE10 > 0 {
				finalOutput.Summary.CriticalDetails[name] = totalCountResult.CountGE10

			}
		} else if queryConfig.Level == "Major" {
			totalCountResult, err = q.processMajorQuery(name, queryConfig)
			if err != nil {
				log2.Error(ctx, "Failed to process major query", zap.String("name", name), zap.Error(err))
				// panic(err)
			}
			if totalCountResult.CountGE10 > 0 {
				finalOutput.Summary.MajorDetails[name] = totalCountResult.CountGE10
			}
		}
		log2.Info(ctx, "pattern count", zap.String("name", name), zap.Int("count_ge_10", totalCountResult.CountGE10))

		if totalCountResult.CountGE10 > 0 {
			qr := dto.QueryResult{
				QueryName:  name,
				TotalCount: totalCountResult,
				Top10:      totalCountResult.TopResult, // For Major level, this will be empty
				Level:      queryConfig.Level,
			}

			queryResults = append(queryResults, qr)
			if name == "SQLPlanChanged" {
				finalOutput.Summary.TotalPatterns += totalCountResult.CountGE10
			}

		}

	}

	finalOutput.QueryResults = queryResults

	// Summary statistics

	q.updateSummaryStats(&finalOutput)

	// Update Top10Result.IsNewDigest value, compare with previous day's data to check if Digest exists
	if err := q.updateIsNewDigestFlags(&finalOutput); err != nil {
		log2.Error(ctx, "Failed to update IsNewDigest flags", zap.Error(err))
	}

	// Save results to file
	if err := q.saveResultToFile(finalOutput); err != nil {
		return nil, err
	}

	// Save results to MySQL database
	if err := q.saveAnalysisResultToDatabase(q.Org, finalOutput); err != nil {
		log2.Error(ctx, "Failed to save analysis result to database", zap.Error(err))
		return nil, err
	}

	// // Push to Feishu document
	// q.PushSummaryToFeishuWithS3Stats(finalOutput, localS3Stats, q.Org)

	return &finalOutput, nil
}

// saveAnalysisResultToDatabase Save analysis results to database
func (q *QuerySqlLite) saveAnalysisResultToDatabase(org dto.Org, finalOutput dto.FinalOutput) error {
	ctx := context.Background()
	dao := common.NewCommonDAO()

	// Get current date
	dateStr := config.AppConfig.Day

	// Convert string date to time format
	currentDate, err := time.Parse("20060102", dateStr)
	if err != nil {
		return fmt.Errorf("failed to parse date %s: %v", dateStr, err)
	}
	reportDate := currentDate.Format("2006-01-02")
	// Save DailySummary
	dailySummary := model.DailySummary{
		ReportDate:       reportDate,
		OrgID:            org.OrgID,
		TotalSlowQueries: finalOutput.Summary.TotalSlowQueries,
		UniqueDigests:    finalOutput.Summary.UniqueDigests,
		CriticalCount:    finalOutput.Summary.CriticalCount,
		MajorCount:       finalOutput.Summary.MajorCount,
		DetailsJson: model.DetailsData{
			CriticalDetails: finalOutput.Summary.CriticalDetails,
			MajorDetails:    finalOutput.Summary.MajorDetails,
		},
	}
	// Delete old report
	err = dao.DeleteEntity(ctx, common.ComFilter{
		ModelFilters: model.DailySummary{
			OrgID:      org.OrgID,
			ReportDate: reportDate,
		},
	}, &model.DailySummary{})

	if err != nil {
		return fmt.Errorf("failed to delete daily summary: %v", err)
	}

	if err = dao.CreateEntity(ctx, &dailySummary); err != nil {
		return fmt.Errorf("failed to save daily summary: %v", err)
	}

	// Delete old DailyStat
	err = dao.DeleteEntity(ctx, common.ComFilter{
		ModelFilters: model.DailyStat{
			OrgID:      org.OrgID,
			ReportDate: reportDate,
		},
	}, &model.DailyStat{})

	if err != nil {
		return fmt.Errorf("failed to delete daily stat: %v", err)
	}

	// Save DailyStat records
	var dailyStats []model.DailyStat
	for _, queryResult := range finalOutput.QueryResults {
		for _, top10Result := range queryResult.Top10 {
			clusterIds := strings.Join(top10Result.ClusterIds, ",")
			d := model.DailyStat{
				OrgID:        org.OrgID,
				ClusterID:    clusterIds,
				ReportDate:   currentDate.Format("2006-01-02"),
				SqlTimestamp: float64(currentDate.Unix()),
				PatternName:  queryResult.QueryName,
				Level:        queryResult.Level,
				Digest:       top10Result.Digest,
				Query:        top10Result.Query,
				TotalCount:   top10Result.TotalCount,
				AvgQueryTime: top10Result.AVGQueryTime,
				AvgMemMax:    top10Result.AVGMemMax,
				MaxMem:       int64(top10Result.MAXMem),
				IsNew:        top10Result.IsNewDigest,
			}
			dailyStats = append(dailyStats, d)
		}
	}
	if len(dailyStats) > 0 {
		if err := dao.CreateEntity(ctx, &dailyStats); err != nil {
			return fmt.Errorf("failed to save daily stat: %v", err)
		}
	}

	return nil
}

// updateIsNewDigestFlags Update IsNewDigest flag in Top10Result
// Determine if a Digest is new by querying the previous day's data
func (q *QuerySqlLite) updateIsNewDigestFlags(finalOutput *dto.FinalOutput) error {
	ctx := context.Background()
	dao := common.NewCommonDAO()

	parseDay, err := time.Parse("20060102", config.AppConfig.Day)
	if err != nil {
		return fmt.Errorf("failed to parse date %s: %v", config.AppConfig.Day, err)
	}
	// Get the previous day's date
	endDay := parseDay.AddDate(0, 0, -1).Format("2006-01-02")
	startDay := parseDay.AddDate(0, 0, -7).Format("2006-01-02")

	// Query all Digests from the previous day
	var yesterdayStats []model.DailyStat
	listParams := common.ListParams{
		AdvancedFilters: map[string]any{
			"org_id":               finalOutput.Summary.OrgID,
			"report_date  >= (?) ": startDay,
			"report_date  <= (?) ": endDay,
		},
	}

	if err := dao.ListEntities(ctx, listParams, &yesterdayStats); err != nil {
		return fmt.Errorf("failed to query yesterday's digests: %v", err)
	}
	if len(yesterdayStats) == 0 {
		return nil
	}
	// Create a set of previous day's Digests for quick lookup
	yesterdayDigests := make(map[string]bool)
	for _, stat := range yesterdayStats {
		yesterdayDigests[stat.Digest] = true
	}

	// Update IsNewDigest flag in Top10 of each QueryResult
	for i := range finalOutput.QueryResults {
		for j := range finalOutput.QueryResults[i].Top10 {
			digest := finalOutput.QueryResults[i].Top10[j].Digest
			// If the Digest did not exist the previous day, mark it as new
			if !yesterdayDigests[digest] {
				finalOutput.QueryResults[i].Top10[j].IsNewDigest = true
				finalOutput.Summary.NewDigests[finalOutput.QueryResults[i].QueryName]++
			}
		}
	}

	return nil
}

// // RunS3SlowQueryAnalysis Global function wrapper, maintains backward compatibility
// func RunS3SlowQueryAnalysis(org dto.Org, s3Stats interface{}) error {
// 	querySqlLite, err := NewQuerySqlLite(org)
// 	if err != nil {
// 		return err
// 	}
// 	defer querySqlLite.Close()
// 	return querySqlLite.RunS3SlowQueryAnalysis(s3Stats)
// }

// Added at the end of the file:
type relativePathWriter struct {
	w io.Writer
}

func (r *relativePathWriter) Write(p []byte) (n int, err error) {
	// Replace absolute paths with relative paths
	cwd, _ := os.Getwd()
	rel := strings.ReplaceAll(string(p), cwd+"/", "")
	return r.w.Write([]byte(rel))
}

func (q *QuerySqlLite) getLatencySlowQueryLog(topRes []dto.TopResult) ([]model.SlowQueryLog, error) {
	ctx := context.Background()
	gormDB := q.DBManager.SqlLiteDB
	dao := common.NewCommonDAOWithDB(gormDB)

	var slowQueryLogs []model.SlowQueryLog
	listPrarm := common.ListParams{
		ModelFilters: model.SlowQueryLog{
			PatternName:    constant.PatternHugeResourceUsage,
			PatternNameSub: constant.PatternHugeResourceUsageSubLatency,
		},
	}
	err := dao.ListEntities(ctx, listPrarm, &slowQueryLogs)
	if err != nil {
		log2.Error(ctx, "checkQueryTime", zap.Error(err))
		return slowQueryLogs, err
	}

	return slowQueryLogs, nil
}

type MaxCountClusterIdArr struct {
	Digest            string
	MaxCountClusterId string
}