package duckdb

import (
	"fmt"

	"github.com/pingcap-inc/clinic/clinic_data_service/pkg/model"
)

// DigestInfo represents a single slow log entry.
type DigestInfo struct {
	ClusterId    string  `db:"cluster_id"`
	Digest       string  `db:"digest"`
	Query        string  `db:"query"`
	AvgMem       float64 `db:"avg_mem"`
	MaxMem       float64 `db:"max_mem"`
	AvgQueryTime float64 `db:"avg_query_time"`
	MaxQueryTime float64 `db:"max_query_time"`
	TotalCount   int64   `db:"total_count"`
}

type ClusterCount struct {
	ClusterId      string `db:"cluster_id"`
	DistinctDigest int64  `db:"distinct_digests"`
	TotalRecords   int64  `db:"total_records"`
	// Tag          int64 `db:"tag"`
}

type SlowLog struct {
	ClusterId string  `db:"cluster_id"`
	UnixTime  float64 `db:"unix_time"`
	Digest    string  `db:"digest"`
	Query     string  `db:"query"`
	QueryTime float64 `db:"query_time"`
	Alias     string  `db:"alias"`
	ConnId    string  `db:"conn_id"`
}

type RunDuckDBQueryReq struct {
	ClusterId string
	IsLocal   bool
	SQL       string
	Args      []any
}

const (
	// 	LocalQuery = `
	//     SELECT time, digest, query_time, query, binary_plan
	//     FROM read_parquet('/Users/wlt/Downloads/slow-query/1.parquet')
	//     WHERE time >= ? AND time < ?
	//     ORDER BY time DESC
	//     LIMIT 10;
	// `
	LocalQuery = `
		SELECT "time" AS unix_time, digest, query_time, binary_plan
		FROM read_parquet('/Users/wlt/Downloads/slow-query/1.parquet')
		ORDER BY "time" DESC
		LIMIT 10;
	`

	QueryTimeThreshold = 100000
	// delta_scan : 's3://o11y-prod-shared-us-east-1/deltalake/1372813089209061633/ed869d81-91c5-4a87-a28d-20795c738d3f/slowlogs'
	ListSQL = `
		WITH filtered AS (
			SELECT "time", digest, query_time, query, lower(query) AS q
			FROM delta_scan(deltalake_uri)
			WHERE "time" BETWEEN ? AND ?
		)
		SELECT "time", digest, query_time, query
		FROM filtered
		WHERE NOT (
			q LIKE 'analyze%%'
			OR q LIKE 'create%%'
			OR q LIKE 'alter%%'
			OR q LIKE 'drop%%'
			OR q LIKE 'truncate%%'
			OR q LIKE 'rename%%'
			OR q LIKE '%%force index%%'
			OR (q LIKE '%%read_from_storage(tiflash%%' AND q NOT LIKE '%%/*+ read_from_storage(tiflash%%')
			OR q NOT LIKE 'select%%'
		)
		ORDER BY "time" DESC;
		`
	DigestCountSQL = `
		WITH filtered AS (
			SELECT 
				digest, 
				query, 
				lower(query) AS q
            FROM delta_scan(deltalake_uri)
            WHERE "time" BETWEEN %d AND %d
		),
		final_filtered AS (
			SELECT 
				digest
			FROM filtered
			WHERE NOT (
					q LIKE 'analyze%%'
					OR q LIKE 'create%%'
					OR q LIKE 'alter%%'
					OR q LIKE 'drop%%'
					OR q LIKE 'truncate%%'
					OR q LIKE 'rename%%'
					OR q LIKE '%%force index%%'
					OR (q LIKE '%%read_from_storage(tiflash%%' AND q NOT LIKE '%%/*+ read_from_storage(tiflash%%')
					OR q NOT LIKE 'select%%'
				)
		)
		SELECT 
		COUNT(*) AS total_records,                 
		COUNT(DISTINCT digest) AS distinct_digests 
		FROM final_filtered;
	`

/* 
	DigestGourpSQL = `
		WITH filtered AS (
			SELECT 
				digest, 
				mem_max,
				query_time,
				query, 
				lower(query) AS q
			FROM delta_scan(deltalake_uri)
			WHERE "time" BETWEEN ? AND ? 	
			AND digest IN (%s)
		)
		SELECT 
			COALESCE(digest, '') AS digest,
			COALESCE(ROUND(AVG(mem_max), 0), 0) AS avg_mem,
			COALESCE(ROUND(MAX(mem_max), 0), 0) AS max_mem,
			COALESCE(ROUND(AVG(query_time), 2), 0) AS avg_query_time,
			COALESCE(MAX(query_time), 0) AS max_query_time,
			COUNT(*) AS total_count
		FROM filtered
		Group BY digest;
	`
 



	SQLByTimeRange = `
		SELECT "time" AS unix_time, 
				COALESCE(digest, '') AS digest,
				query_time AS query_time,
		        COALESCE(db, '') AS db
		FROM delta_scan(
		deltalake_uri
		)
		WHERE "time" >= ? AND "time" < ?
		ORDER BY "time" DESC;`

	SQLTopADigest = `
		SELECT
			COALESCE(digest, '') AS digest,
			ANY_VALUE(query) AS query, 
			COALESCE(ROUND(AVG(mem_max), 0), 0) AS avg_mem_max,
			COALESCE(ROUND(AVG(query_time), 2), 0) AS avg_query_time,
			COUNT(*) AS total_count
		FROM delta_scan(
			deltalake_uri
		)
		WHERE "time" >= ? AND "time" < ?
		GROUP BY digest
		ORDER BY total_count DESC
		LIMIT 200;
		`

*/

	ClusterATopN = `
		SELECT 
			COALESCE(digest, '') AS digest,
			COALESCE(ROUND(AVG(mem_max), 0), 0) AS avg_mem,
			COALESCE(ROUND(MAX(mem_max), 0), 0) AS max_mem,
			COALESCE(ROUND(AVG(query_time), 2), 0) AS avg_query_time,
			COALESCE(ROUND(MAX(query_time), 2), 0)  AS max_query_time,
			COUNT(*) AS total_count
		FROM delta_scan(deltalake_uri)
		WHERE "time" BETWEEN %d AND %d 	
			AND lower(query) LIKE 'select%%'
			AND lower(query) NOT LIKE 'analyze%%'
			AND lower(query) NOT LIKE 'create%%'
			AND lower(query) NOT LIKE 'alter%%'
			AND lower(query) NOT LIKE 'drop%%'
			AND lower(query) NOT LIKE 'truncate%%'
			AND lower(query) NOT LIKE 'rename%%'
			AND lower(query) NOT LIKE '%%force index%%'
			AND NOT (lower(query) LIKE '%%read_from_storage(tiflash%%' AND lower(query) NOT LIKE '%%/*+ read_from_storage(tiflash%%')
		GROUP BY digest
		ORDER BY total_count DESC
		LIMIT %d ;
    `
	ClusterBDigestInA = `
		SELECT 
			COALESCE(digest, '') AS digest,
			COALESCE(ROUND(AVG(mem_max), 0), 0) AS avg_mem,
			COALESCE(ROUND(MAX(mem_max), 0), 0) AS max_mem,
			COALESCE(ROUND(AVG(query_time), 2), 0) AS avg_query_time,
			COALESCE(ROUND(MAX(query_time), 2), 0)  AS max_query_time,
			COUNT(*) AS total_count
		FROM delta_scan(deltalake_uri)
		WHERE "time" BETWEEN %d AND %d 		
			AND digest IN (%s)
			AND lower(query) LIKE 'select%%'
			AND lower(query) NOT LIKE 'analyze%%'
			AND lower(query) NOT LIKE 'create%%'
			AND lower(query) NOT LIKE 'alter%%'
			AND lower(query) NOT LIKE 'drop%%'
			AND lower(query) NOT LIKE 'truncate%%'
			AND lower(query) NOT LIKE 'rename%%'
			AND lower(query) NOT LIKE '%%force index%%'
			AND NOT (lower(query) LIKE '%%read_from_storage(tiflash%%' AND lower(query) NOT LIKE '%%/*+ read_from_storage(tiflash%%')
		GROUP BY digest
		ORDER BY total_count DESC ;
    `

	SQLTopBDigest = `
		SELECT
			COALESCE(digest, '') AS digest,
			ANY_VALUE(query) AS query, 
			COALESCE(ROUND(AVG(mem_max), 0), 0) AS avg_mem_max,
			COALESCE(ROUND(AVG(query_time), 2), 0) AS avg_query_time,
			COUNT(*) AS total_count
		FROM delta_scan(
			deltalake_uri
		)
		WHERE digest IN (%s)
		AND "time" >= ?
		AND "time" < ?
		GROUP BY digest
		ORDER BY total_count DESC;
		`
	QueryByQueryTime = `
		SELECT
			COALESCE(digest, '') AS digest,
			query, 
			"time" AS unix_time,
			query_time,
			conn_id
		FROM delta_scan(
			deltalake_uri
		)
		WHERE digest = ?
		AND query_time = ?
		AND "time" >= ?
		AND "time" < ?;
		`
)

func GetDeltaScan(cluster model.Cluster) (string, error) {

	if cluster.OrgID == "" || cluster.ClusterID == "" {
		return "", fmt.Errorf("invalid cluster or org configuration，clusterId: %s", cluster.ClusterID)
	}
	if cluster.MetricsRouting == "" || cluster.Region == "" {
		return "", fmt.Errorf("missing cluster metricsRouting or region")
	}
	deltaScan := `s3://o11y-prod-shared-%s/deltalake/%s/%s/slowlogs`
	s3Region := cluster.Region
	// Append premium suffix to bucket region when deployType is premium
	if cluster.DeployType == "premium" {
		s3Region = cluster.Region + "-premium"
	}
	metricsRouting := cluster.MetricsRouting

	return fmt.Sprintf(deltaScan, s3Region, cluster.OrgID, metricsRouting), nil
}