package dao

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"reflect"
	"strings"
	"time"

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

	_ "github.com/mattn/go-sqlite3" // SQLite driver
	"go.uber.org/zap"
	"gorm.io/gorm"
)

// SlowQueryLog represents the structure of a slow query log entry.
// Fields are based on the JSON structure and inferred types from jsonslowlog2sql.py.
type SlowQueryLog struct {
	Formatted_Timestamp string      `json:"Formatted_Timestamp"`
	ClusterId           string      `json:"Cluster_Id"`
	Query               string      `json:"Query"`
	Query_time          float64     `json:"Query_time"`
	Plan                string      `json:"Plan"`
	Plan_digest         *string     `json:"Plan_digest"`
	Binary_plan         *string     `json:"Binary_plan"`        // MEDIUMTEXT in SQL
	Binary_plan_parsed  interface{} `json:"Binary_plan_parsed"` // JSON in SQL
	DB                  string      `json:"DB"`
	Succ                bool        `json:"Succ"`
	PatternName         string      `json:"Pattern_Name"`
	PatternNameSub      string      `json:"Pattern_name_sub"`
	SourceFile          string      `json:"Source_file"`
	Digest              string      `json:"Digest"`
	Index_names         *string     `json:"Index_names"`
	Mem_max             int64       `json:"Mem_max"`
	Stats               *string     `json:"Stats"`
	Warnings            *string     `json:"Warnings"` // MEDIUMTEXT in SQL
	Conn_ID             int64       `json:"Conn_ID"`
	Request_unit_read   *float64    `json:"Request_unit_read"`
	Request_unit_write  *float64    `json:"Request_unit_write"`
	Timestamp           float64     `json:"Timestamp"`
	Timestamp_str       string      `json:"Timestamp_str"`
	// Write_sql_response_total      float64     `json:"Write_sql_response_total"`

	// INSTANCE                      string      `json:"INSTANCE"`
	// Txn_start_ts                  int64       `json:"Txn_start_ts"`
	// User                          string      `json:"User"`
	// Host                          string      `json:"Host"`
	// Session_alias                 *string     `json:"Session_alias"` // Use pointer for nullable fields
	// Exec_retry_count              *int64      `json:"Exec_retry_count"`
	// Exec_retry_time               *float64    `json:"Exec_retry_time"`
	// Parse_time                    float64     `json:"Parse_time"`
	// Compile_time                  float64     `json:"Compile_time"`
	// Rewrite_time                  float64     `json:"Rewrite_time"`
	// Preproc_subqueries            *float64    `json:"Preproc_subqueries"`
	// Preproc_subqueries_time       *float64    `json:"Preproc_subqueries_time"`
	// Optimize_time                 float64     `json:"Optimize_time"`
	// Wait_TS                       float64     `json:"Wait_TS"`
	// Prewrite_time                 *float64    `json:"Prewrite_time"`
	// Wait_prewrite_binlog_time     *float64    `json:"Wait_prewrite_binlog_time"`
	// Commit_time                   *float64    `json:"Commit_time"`
	// Get_commit_ts_time            *float64    `json:"Get_commit_ts_time"`
	// Commit_backoff_time           *float64    `json:"Commit_backoff_time"`
	// Backoff_types                 *string     `json:"Backoff_types"`
	// Resolve_lock_time             *float64    `json:"Resolve_lock_time"`
	// Local_latch_wait_time         *float64    `json:"Local_latch_wait_time"`
	// Write_keys                    *int64      `json:"Write_keys"`
	// Write_size                    *int64      `json:"Write_size"`
	// Prewrite_region               *float64    `json:"Prewrite_region"`
	// Txn_retry                     *int64      `json:"Txn_retry"`
	// Cop_time                      *float64    `json:"Cop_time"`
	// Process_time                  *float64    `json:"Process_time"`
	// Wait_time                     *float64    `json:"Wait_time"`
	// Backoff_time                  *float64    `json:"Backoff_time"`
	// LockKeys_time                 *float64    `json:"LockKeys_time"`
	// Request_count                 *int64      `json:"Request_count"`
	// Total_keys                    *int64      `json:"Total_keys"`
	// Process_keys                  *int64      `json:"Process_keys"`
	// Rocksdb_delete_skipped_count  *int64      `json:"Rocksdb_delete_skipped_count"`
	// Rocksdb_key_skipped_count     *int64      `json:"Rocksdb_key_skipped_count"`
	// Rocksdb_block_cache_hit_count *int64      `json:"Rocksdb_block_cache_hit_count"`
	// Rocksdb_block_read_count      *int64      `json:"Rocksdb_block_read_count"`
	// Rocksdb_block_read_byte       *int64      `json:"Rocksdb_block_read_byte"`
	// Is_internal                   bool        `json:"Is_internal"`
	// Cop_proc_avg                  *float64    `json:"Cop_proc_avg"`
	// Cop_proc_p90                  *float64    `json:"Cop_proc_p90"`
	// Cop_proc_max                  *float64    `json:"Cop_proc_max"`
	// Cop_proc_addr                 *string     `json:"Cop_proc_addr"`
	// Cop_wait_avg                  *float64    `json:"Cop_wait_avg"`
	// Cop_wait_p90                  *float64    `json:"Cop_wait_p90"`
	// Cop_wait_max                  *float64    `json:"Cop_wait_max"`
	// Cop_wait_addr                 *string     `json:"Cop_wait_addr"`
	// Disk_max                      *int64      `json:"Disk_max"`
	// KV_total                      float64     `json:"KV_total"`
	// PD_total                      float64     `json:"PD_total"`
	// Backoff_total                 float64     `json:"Backoff_total"`

	// Result_rows                   int64       `json:"Result_rows"`
	// Backoff_Detail                *string     `json:"Backoff_Detail"`
	// Prepared                      bool        `json:"Prepared"`
	// IsExplicitTxn                 bool        `json:"IsExplicitTxn"`
	// IsWriteCacheTable             *bool       `json:"IsWriteCacheTable"`
	// Plan_from_cache               bool        `json:"Plan_from_cache"`
	// Plan_from_binding             bool        `json:"Plan_from_binding"`
	// Has_more_results              bool        `json:"Has_more_results"`
	// Resource_group                *string     `json:"Resource_group"`
	// Time_queued_by_rc             *float64    `json:"Time_queued_by_rc"`
	// Prev_stmt                     *string     `json:"Prev_stmt"`
	// Worker_role                   *string     `json:"Worker_role"`
	// Is_tiflash                    *bool       `json:"Is_tiflash"`
}

// SQLiteManager handles SQLite database operations.
type SQLiteManager struct {
	db        *sql.DB
	tableName string
	DB        *sql.DB
	SqlLiteDB *gorm.DB
}

// retryWithBackoff executes a function with exponential backoff retry logic
func retryWithBackoff(operation func() error, maxRetries int, baseDelay time.Duration) error {
	var lastErr error
	for i := 0; i < maxRetries; i++ {
		err := operation()
		if err == nil {
			return nil
		}

		lastErr = err

		// Check if it's a database lock error
		if strings.Contains(err.Error(), "database is locked") ||
			strings.Contains(err.Error(), "SQLITE_BUSY") ||
			strings.Contains(err.Error(), "database table is locked") {
			if i < maxRetries-1 {
				// Exponential backoff delay
				delay := baseDelay * time.Duration(1<<uint(i))
				log2.PlainInfo(fmt.Sprintf("Database locked, retrying in %v (attempt %d/%d)", delay, i+1, maxRetries))
				time.Sleep(delay)
				continue
			}
		}

		// If not a lock error, return directly
		return err
	}

	return fmt.Errorf("operation failed after %d retries: %w", maxRetries, lastErr)
}

// NewSQLiteManager creates a new SQLiteManager instance with concurrent access support.
func NewSQLiteManager(dbPath, tableName string) (*SQLiteManager, error) {
	// Add WAL mode and other concurrent optimization parameters
	dsn := fmt.Sprintf("%s?_journal_mode=WAL&_synchronous=NORMAL&_cache_size=10000&_temp_store=memory&_busy_timeout=30000&_txlock=immediate", dbPath)
	db, err := sql.Open("sqlite3", dsn)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %w", err)
	}

	// Set connection pool parameters to support concurrency - reduce concurrent connections to avoid lock contention
	db.SetMaxOpenConns(5)    // Reduce maximum concurrent connections
	db.SetMaxIdleConns(2)    // Reduce maximum idle connections
	db.SetConnMaxLifetime(0) // Connection maximum lifetime (0 means never expire)

	// Test connection
	if err := db.Ping(); err != nil {
		return nil, fmt.Errorf("failed to ping database: %w", err)
	}

	// Enable WAL mode (if not already enabled)
	if _, err := db.Exec("PRAGMA journal_mode=WAL;"); err != nil {
		log2.PlainError("Warning: Failed to set WAL mode", zap.Error(err))
	}

	// Set other optimization parameters
	if _, err := db.Exec("PRAGMA synchronous=NORMAL;"); err != nil {
		log2.PlainError("Warning: Failed to set synchronous mode", zap.Error(err))
	}

	return &SQLiteManager{db: db, DB: db, tableName: tableName}, nil
}

// Close closes the database connection.
func (sm *SQLiteManager) Close() error {
	return sm.db.Close()
}

// InsertSlowQueryRecord inserts a slow query record from map data
func (sm *SQLiteManager) InsertSlowQueryRecord(record map[string]interface{}) error {
	// Convert map to SlowQueryLog struct
	logEntry := SlowQueryLog{}
	val := reflect.ValueOf(&logEntry).Elem()
	typ := reflect.TypeOf(logEntry)

	for i := 0; i < typ.NumField(); i++ {
		field := typ.Field(i)
		jsonTag := field.Tag.Get("json")
		if jsonTag == "" {
			continue
		}

		if value, exists := record[jsonTag]; exists && value != nil {
			fieldVal := val.Field(i)
			if fieldVal.CanSet() {
				switch field.Type.Kind() {
				case reflect.String:
					if str, ok := value.(string); ok {
						fieldVal.SetString(str)
					}
				case reflect.Int64:
					if num, ok := value.(float64); ok {
						fieldVal.SetInt(int64(num))
					} else if num, ok := value.(int64); ok {
						fieldVal.SetInt(num)
					}
				case reflect.Float64:
					if num, ok := value.(float64); ok {
						fieldVal.SetFloat(num)
					}
				case reflect.Bool:
					if b, ok := value.(bool); ok {
						fieldVal.SetBool(b)
					}
				case reflect.Ptr:
					// Handle pointer types
					elemType := field.Type.Elem()
					newVal := reflect.New(elemType)
					switch elemType.Kind() {
					case reflect.String:
						if str, ok := value.(string); ok {
							newVal.Elem().SetString(str)
							fieldVal.Set(newVal)
						}
					case reflect.Int64:
						if num, ok := value.(float64); ok {
							newVal.Elem().SetInt(int64(num))
							fieldVal.Set(newVal)
						} else if num, ok := value.(int64); ok {
							newVal.Elem().SetInt(num)
							fieldVal.Set(newVal)
						}
					case reflect.Float64:
						if num, ok := value.(float64); ok {
							newVal.Elem().SetFloat(num)
							fieldVal.Set(newVal)
						}
					case reflect.Bool:
						if b, ok := value.(bool); ok {
							newVal.Elem().SetBool(b)
							fieldVal.Set(newVal)
						}
					}
				case reflect.Interface:
					// Handle interface{} fields like Binary_plan_parsed
					fieldVal.Set(reflect.ValueOf(value))
				}
			}
		}
	}

	return sm.InsertLogEntry(logEntry)
}

// CreateTable creates the slow query log table dynamically based on the SlowQueryLog struct.
// createIndexes creates indexes for frequently queried fields to optimize query performance
func (sm *SQLiteManager) createIndexes() error {
	// Index definitions based on query patterns in config.yaml
	indexes := []string{
		// Single column indexes for core query fields
		fmt.Sprintf("CREATE INDEX IF NOT EXISTS idx_%s_digest ON `%s` (`Digest`)", sm.tableName, sm.tableName),
		fmt.Sprintf("CREATE INDEX IF NOT EXISTS idx_%s_pattern_name ON `%s` (`Pattern_Name`)", sm.tableName, sm.tableName),

		// Performance metric indexes for sorting and filtering
		fmt.Sprintf("CREATE INDEX IF NOT EXISTS idx_%s_mem_max ON `%s` (`Mem_max`)", sm.tableName, sm.tableName),
		fmt.Sprintf("CREATE INDEX IF NOT EXISTS idx_%s_query_time ON `%s` (`Query_time`)", sm.tableName, sm.tableName),

		// Composite indexes for common query patterns
		fmt.Sprintf("CREATE INDEX IF NOT EXISTS idx_%s_pattern_digest ON `%s` (`Pattern_Name`, `Digest`)", sm.tableName, sm.tableName),

		// Additional useful indexes
		fmt.Sprintf("CREATE INDEX IF NOT EXISTS idx_%s_timestamp ON `%s` (`Formatted_Timestamp`)", sm.tableName, sm.tableName),
	}

	// Execute index creation statements
	for _, indexSQL := range indexes {
		_, err := sm.db.Exec(indexSQL)
		if err != nil {
			return fmt.Errorf("failed to create index: %s, error: %v", indexSQL, err)
		}
	}

	return nil
}

func (sm *SQLiteManager) CreateTable() error {
	columns := []string{}
	// Use reflection to get struct fields and their types
	t := reflect.TypeOf(SlowQueryLog{})
	for i := 0; i < t.NumField(); i++ {
		field := t.Field(i)
		jsonTag := field.Tag.Get("json")
		if jsonTag == "" {
			continue // Skip fields without json tag
		}

		colName := jsonTag
		colType := "TEXT"

		switch field.Type.Kind() {
		case reflect.Bool:
			colType = "TINYINT(1)"
		case reflect.Int, reflect.Int64:
			colType = "BIGINT"
		case reflect.Float64:
			colType = "DOUBLE"
		case reflect.String:
			// Special handling for large text fields
			if colName == "Binary_plan" || colName == "Warnings" {
				colType = "MEDIUMTEXT"
			}
		case reflect.Interface:
			// For Binary_plan_parsed, which is JSON
			if colName == "Binary_plan_parsed" {
				colType = "JSON"
			}
		case reflect.Ptr:
			// Handle pointers to basic types (nullable fields)
			switch field.Type.Elem().Kind() {
			case reflect.Bool:
				colType = "TINYINT(1)"
			case reflect.Int, reflect.Int64:
				colType = "BIGINT"
			case reflect.Float64:
				colType = "DOUBLE"
			case reflect.String:
				colType = "TEXT"
			}
		}
		columns = append(columns, fmt.Sprintf("`%s` %s", colName, colType))
	}

	createTableSQL := fmt.Sprintf("CREATE TABLE IF NOT EXISTS `%s` (\n\t%s\n);", sm.tableName, strings.Join(columns, ",\n\t"))
	//log.Printf("Creating table with SQL:\n%s\n", createTableSQL)
	_, err := sm.db.Exec(createTableSQL)
	if err != nil {
		return err
	}

	// Create indexes for frequently queried fields
	err = sm.createIndexes()
	return err
}

// ClearTable clears all data from the table
func (sm *SQLiteManager) ClearTable() error {
	clearSQL := fmt.Sprintf("DELETE FROM `%s`", sm.tableName)
	_, err := sm.db.Exec(clearSQL)
	if err != nil {
		return fmt.Errorf("failed to clear table %s: %w", sm.tableName, err)
	}
	log2.PlainInfo("Table " + sm.tableName + " cleared successfully")
	return nil
}

// BatchInsertLogEntries inserts multiple SlowQueryLog entries into the database in a single transaction.
func (sm *SQLiteManager) BatchInsertLogEntries(logEntries []SlowQueryLog) error {
	if len(logEntries) == 0 {
		return nil
	}

	// Use retry mechanism to wrap the entire batch insert operation
	return retryWithBackoff(func() error {
		return sm.batchInsertWithTransaction(logEntries)
	}, 5, 100*time.Millisecond)
}

// batchInsertWithTransaction Execute the actual batch insert operation
func (sm *SQLiteManager) batchInsertWithTransaction(logEntries []SlowQueryLog) error {
	// Start transaction
	tx, err := sm.db.Begin()
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	// Use reflection to dynamically build the insert statement
	typ := reflect.TypeOf(SlowQueryLog{})
	cols := []string{}
	placeholders := []string{}

	for i := 0; i < typ.NumField(); i++ {
		field := typ.Field(i)
		jsonTag := field.Tag.Get("json")
		if jsonTag == "" {
			continue
		}
		cols = append(cols, fmt.Sprintf("`%s`", jsonTag))
		placeholders = append(placeholders, "?")
	}

	insertSQL := fmt.Sprintf("INSERT INTO `%s` (%s) VALUES (%s)",
		sm.tableName, strings.Join(cols, ", "), strings.Join(placeholders, ", "))

	// Prepare statement
	stmt, err := tx.Prepare(insertSQL)
	if err != nil {
		return fmt.Errorf("failed to prepare statement: %w", err)
	}
	defer stmt.Close()

	// Insert each entry
	for _, logEntry := range logEntries {
		val := reflect.ValueOf(logEntry)
		args := []interface{}{}

		for i := 0; i < typ.NumField(); i++ {
			field := typ.Field(i)
			jsonTag := field.Tag.Get("json")
			if jsonTag == "" {
				continue
			}

			fieldVal := val.Field(i)

			// Handle nullable fields (pointers)
			if field.Type.Kind() == reflect.Ptr {
				if fieldVal.IsNil() {
					args = append(args, nil)
				} else {
					args = append(args, fieldVal.Elem().Interface())
				}
			} else if field.Type.Kind() == reflect.Interface && jsonTag == "Binary_plan_parsed" {
				// Special handling for JSON field
				jsonBytes, err := json.Marshal(fieldVal.Interface())
				if err != nil {
					return fmt.Errorf("failed to marshal Binary_plan_parsed to JSON: %w", err)
				}
				args = append(args, string(jsonBytes))
			} else {
				args = append(args, fieldVal.Interface())
			}
		}

		_, err = stmt.Exec(args...)
		if err != nil {
			return fmt.Errorf("failed to execute batch insert: %w", err)
		}
	}

	// Commit transaction
	err = tx.Commit()
	if err != nil {
		return fmt.Errorf("failed to commit transaction: %w", err)
	}

	return nil
}

// InsertLogEntry inserts a single SlowQueryLog entry into the database.
func (sm *SQLiteManager) InsertLogEntry(logEntry SlowQueryLog) error {
	// Use retry mechanism to wrap single insert operation
	return retryWithBackoff(func() error {
		return sm.insertSingleEntry(logEntry)
	}, 3, 50*time.Millisecond)
}

// insertSingleEntry Execute the actual single insert operation
func (sm *SQLiteManager) insertSingleEntry(logEntry SlowQueryLog) error {
	// Use reflection to dynamically build the insert statement and values
	val := reflect.ValueOf(logEntry)
	typ := reflect.TypeOf(logEntry)

	cols := []string{}
	placeholders := []string{}
	args := []interface{}{} // Use interface{} for arguments

	for i := 0; i < typ.NumField(); i++ {
		field := typ.Field(i)
		jsonTag := field.Tag.Get("json")
		if jsonTag == "" {
			continue
		}

		fieldVal := val.Field(i)

		// Handle nullable fields (pointers)
		if field.Type.Kind() == reflect.Ptr {
			if fieldVal.IsNil() {
				args = append(args, nil)
			} else {
				args = append(args, fieldVal.Elem().Interface())
			}
		} else if field.Type.Kind() == reflect.Interface && jsonTag == "Binary_plan_parsed" {
			// Special handling for JSON field
			jsonBytes, err := json.Marshal(fieldVal.Interface())
			if err != nil {
				return fmt.Errorf("failed to marshal Binary_plan_parsed to JSON: %w", err)
			}
			args = append(args, string(jsonBytes))
		} else {
			args = append(args, fieldVal.Interface())
		}

		cols = append(cols, fmt.Sprintf("`%s`", jsonTag))
		placeholders = append(placeholders, "?")
	}

	insertSQL := fmt.Sprintf("INSERT INTO `%s` (%s) VALUES (%s)",
		sm.tableName, strings.Join(cols, ", "), strings.Join(placeholders, ", "))

	_, err := sm.db.Exec(insertSQL, args...)
	if err != nil {
		return fmt.Errorf("failed to insert data into %s: %w", sm.tableName, err)
	}
	return nil
}

// ClearTable is a global function to clear table data, used by main.go
func ClearTable(dbPath, tableName string) error {
	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		return fmt.Errorf("failed to open database: %w", err)
	}
	defer db.Close()

	clearSQL := fmt.Sprintf("DELETE FROM `%s`", tableName)
	_, err = db.Exec(clearSQL)
	if err != nil {
		return fmt.Errorf("failed to clear table %s: %w", tableName, err)
	}
	log2.PlainInfo("Table " + tableName + " cleared successfully")
	return nil
}

// DropTable drops the entire table from the database
func DropTable(dbPath, tableName string) error {
	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		return fmt.Errorf("failed to open database: %w", err)
	}
	defer db.Close()

	dropSQL := fmt.Sprintf("DROP TABLE IF EXISTS `%s`", tableName)
	_, err = db.Exec(dropSQL)
	if err != nil {
		return fmt.Errorf("failed to drop table %s: %w", tableName, err)
	}
	log2.PlainInfo(dbPath + " ,Table " + tableName + " dropped successfully")
	return nil
}