package parser

import (
	"encoding/json"
	"fmt"
	"io/ioutil"

	log2 "github.com/pingcap-inc/clinic/clinic_data_service/pkg/log"

	_ "github.com/mattn/go-sqlite3" // SQLite driver

	"path/filepath"
	"regexp"
	"strconv"
	"strings"

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

var slowQueryCount int64
var slowQueryPatternCount int64
var slowQueryAnalyzeCount int64
var slowQueryDDLCount int64

// AbandonedShouldKeep determines whether the JSON object meets the filter criteria (exported for use by other packages)
func AbandonedShouldKeep(item map[string]interface{}) bool {
	// Skip condition set
	if queryStr, ok := item["Query"].(string); ok {
		// Query contains "read_from_storage(tiflash"
		if strings.Contains(queryStr, "read_from_storage(tiflash") {
			return false
		}
		// SQL starting with analyze (case-insensitive)
		if strings.HasPrefix(strings.ToLower(strings.TrimSpace(queryStr)), "analyze") {
			slowQueryAnalyzeCount++
			return false
		}

		// Exclude DDL statements (CREATE, ALTER, DROP, TRUNCATE, etc.)
		queryLower := strings.ToLower(strings.TrimSpace(queryStr))
		ddlKeywords := []string{"create", "alter", "drop", "truncate", "rename"}
		for _, keyword := range ddlKeywords {
			if strings.HasPrefix(queryLower, keyword) {
				slowQueryDDLCount++
				return false
			}
		}

		// SQL not starting with select (case-insensitive)
		if !strings.HasPrefix(strings.ToLower(strings.TrimSpace(queryStr)), "select") {
			return false
		}

	}

	// Condition HugeResourceUsage: Mem_max > 5G
	if memMaxVal, ok := item["Mem_max"].(float64); ok {
		if memMaxVal > 5*1024*1024*1024 {
			item[config.AppConfig.Database.PatternName] = "HugeResourceUsage"
			return true
		}
	}

	// Condition HugeResourceUsage: Query_time > 20
	if queryTime, ok := item["Query_time"].(float64); ok {
		if queryTime > 20 {
			item[config.AppConfig.Database.PatternName] = "HugeResourceUsage"
			return true
		}
	}
	// Condition Failed: Succ is false
	if succ, ok := item["Succ"].(bool); ok {
		if !succ {
			item[config.AppConfig.Database.PatternName] = "Failed"
			return true
		}
	}

	// Condition TableFullScan: Recursively find TableFullScan_xxx + estRows > 10000 in Binary_plan_parsed
	if planParsed, ok := item["Binary_plan_parsed"].(map[string]interface{}); ok {
		for _, v := range planParsed {
			if hasTableFullScan(v) {
				item[config.AppConfig.Database.PatternName] = "TableFullScan"
				return true
			}
		}
	}

	// Condition DeepOffset: Query contains OFFSET and the number is greater than 100000
	if queryStr, ok := item["Query"].(string); ok {
		re := regexp.MustCompile(`OFFSET\s+(\d+)`)
		if match := re.FindStringSubmatch(queryStr); len(match) == 2 {
			if offset, err := strconv.Atoi(match[1]); err == nil && offset > 100000 {
				item[config.AppConfig.Database.PatternName] = "DeepOffset"
				return true
			}
		}
	}
	// Condition ExcessiveCopTask: cop_task node exists under Binary_plan_parsed and num>100
	if planParsed, ok := item["Binary_plan_parsed"].(map[string]interface{}); ok {
		for _, v := range planParsed {
			if hasCopTaskNumGt100(v) {
				item[config.AppConfig.Database.PatternName] = "ExcessiveCopTask"
				return true
			}
		}
	}
	// Condition Stats missing: Stats string contains "missing"
	if statsStr, ok := item["Stats"].(string); ok {
		if regexp.MustCompile(`missing`).MatchString(statsStr) {
			item[config.AppConfig.Database.PatternName] = "StatsMissing"
			return true
		}
	}

	return false
}

// Determine if TableFullScan_xxx + estRows > 10000
func hasTableFullScan(node interface{}) bool {
	nodeMap, ok := node.(map[string]interface{})
	if !ok {
		return false
	}

	name, _ := nodeMap["name"].(string)
	if matched, _ := regexp.MatchString(`TableFullScan_\d+`, name); matched {
		// sikp tiflash
		storeType, _ := nodeMap["storeType"].(string)

		if est, ok := nodeMap["estRows"]; ok {
			var estVal float64
			switch v := est.(type) {
			case string:
				if f, err := strconv.ParseFloat(v, 64); err == nil {
					estVal = f
				}
			case float64:
				estVal = v
			}
			if estVal > 100000 && "tiflash" != storeType {
				return true
			}
		}
	}

	// Recursively traverse children
	if children, ok := nodeMap["children"].([]interface{}); ok {
		for _, child := range children {
			if hasTableFullScan(child) {
				return true
			}
		}
	}

	return false
}

// Add auxiliary function
func hasCopTaskNumGt100(node interface{}) bool {
	nodeMap, ok := node.(map[string]interface{})
	if !ok {
		return false
	}
	// Check rootGroupExecInfo or cop_task string
	if arr, ok := nodeMap["rootGroupExecInfo"].([]interface{}); ok {
		for _, s := range arr {
			if str, ok := s.(string); ok {
				re := regexp.MustCompile(`cop_task:\s*\{[^}]*num:\s*(\d+)`)
				if match := re.FindStringSubmatch(str); len(match) == 2 {
					if num, err := strconv.Atoi(match[1]); err == nil && num > 100 {
						return true
					}
				}
			}
		}
	}
	// Recursively traverse children
	if children, ok := nodeMap["children"].([]interface{}); ok {
		for _, child := range children {
			if hasCopTaskNumGt100(child) {
				return true
			}
		}
	}
	return false
}

// Process a single file: read, filter, write
func processFile2(inputPath, outputPath string, sm *dao.SQLiteManager) error {
	bytes, err := ioutil.ReadFile(inputPath)
	if err != nil {
		return err
	}

	var data []map[string]interface{}
	if err := json.Unmarshal(bytes, &data); err != nil {
		return fmt.Errorf("failed to unmarshal JSON from %s: %v", inputPath, err)
	}
	slowQueryCount = slowQueryCount + int64(len(data))
	// Filter data that meets the criteria and write to SQLite
	var filtered []map[string]interface{}
	for _, item := range data {
		if AbandonedShouldKeep(item) {
			filtered = append(filtered, item)

			// Convert map[string]interface{} to SlowQueryLog struct
			jsonBytes, err := json.Marshal(item)
			if err != nil {
				return fmt.Errorf("failed to marshal item to JSON: %w", err)
			}
			var logEntry dao.SlowQueryLog
			if err := json.Unmarshal(jsonBytes, &logEntry); err != nil {
				return fmt.Errorf("failed to unmarshal JSON to SlowQueryLog: %w", err)
			}

			// Insert into SQLite
			if err := sm.InsertLogEntry(logEntry); err != nil {
				return fmt.Errorf("failed to insert log entry into SQLite: %w", err)
			}

		}
	}

	// Write to new file (formatted output)
	if len(filtered) > 0 {
		slowQueryPatternCount = slowQueryPatternCount + int64(len(filtered))
		outputBytes, err := json.MarshalIndent(filtered, "", "  ")
		if err != nil {
			return err
		}
		newNamePath := GenerateRenamedFilePath(outputPath, len(filtered))
		log2.PlainInfo("Save: " + newNamePath)
		return ioutil.WriteFile(newNamePath, outputBytes, 0644)
	}

	return nil
}

func GenerateRenamedFilePath(originalPath string, size int) string {
	// Extract path and file name components
	dir := filepath.Dir(originalPath)
	base := filepath.Base(originalPath)
	ext := filepath.Ext(base)
	name := strings.TrimSuffix(base, ext)

	// Construct new file name
	newName := fmt.Sprintf("%s_%d%s", name, size, ext)

	// Combine new path
	newPath := filepath.Join(dir, newName)

	return newPath
}