package dao

import (
	"fmt"
	"log"
	"os"
	"time"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"

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

var (
	// SqlLiteDB is the global GORM database instance
	SqlLiteDB *gorm.DB
)

// InitSQLiteDB initializes GORM with SQLite database
func InitSQLiteDB(dbPath string) (*gorm.DB, error) {
	// Configure GORM logger
	newLogger := logger.New(
		log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
		logger.Config{
			SlowThreshold:             time.Second, // Slow SQL threshold
			LogLevel:                  logger.Info, // Log level (Silent, Error, Warn, Info)  // info print log （ logger.Silent）
			IgnoreRecordNotFoundError: true,        // Ignore ErrRecordNotFound error for logger
			ParameterizedQueries:      false,       // Don't include params in the SQL log
			Colorful:                  false,       // Disable color
		},
	)

	// Open database connection
	db, err := gorm.Open(sqlite.Open(dbPath), &gorm.Config{
		Logger: newLogger,
	})
	if err != nil {
		return nil, fmt.Errorf("failed to connect to SQLite database: %w", err)
	}

	// Set connection pool settings
	sqlDB, err := db.DB()
	if err != nil {
		return nil, fmt.Errorf("failed to get underlying sql.DB: %w", err)
	}

	// SetMaxIdleConns sets the maximum number of connections in the idle connection pool
	sqlDB.SetMaxIdleConns(10)

	// SetMaxOpenConns sets the maximum number of open connections to the database
	sqlDB.SetMaxOpenConns(100)

	// SetConnMaxLifetime sets the maximum amount of time a connection may be reused
	sqlDB.SetConnMaxLifetime(time.Hour)

	// Assign to global variable
	SqlLiteDB = db

	return db, nil
}

// CreateSlowQueryTable creates the slow query table for a specific date
// tableName should be in format like "20250701"
func CreateSlowQueryTable(orgId string, table ...string) error {
	dbPath := config.GetDbPath(orgId)
	tableName := config.AppConfig.Day
	if len(table) > 0 {
		tableName = table[0]
		dbPath = config.GetTikvDbPath(orgId)
	}
	orgmDB, err := InitSQLiteDB(dbPath)
	if err != nil {
		return fmt.Errorf("failed to init sqlite db: %w", err)
	}

	// Create table using GORM's Migrator
	err = orgmDB.Table(tableName).Migrator().CreateTable(&model.SlowQueryLog{})
	if err != nil {
		return fmt.Errorf("failed to create table %s: %w", tableName, err)
	}

	indexes := []string{
		// Digest single-column index, facilitates GROUP BY / IN / EXISTS acceleration
		fmt.Sprintf("CREATE INDEX IF NOT EXISTS idx_%s_digest ON `%s` (`Digest`)", tableName, tableName),

		// Pattern_Name single-column index (optional, can be omitted if covered by composite index)
		fmt.Sprintf("CREATE INDEX IF NOT EXISTS idx_%s_pattern_name ON `%s` (`Pattern_Name`)", tableName, tableName),

		// Mem_max and Query_time are used for aggregation analysis and sorting
		fmt.Sprintf("CREATE INDEX IF NOT EXISTS idx_%s_mem_max ON `%s` (`Mem_max`)", tableName, tableName),
		fmt.Sprintf("CREATE INDEX IF NOT EXISTS idx_%s_query_time ON `%s` (`Query_time`)", tableName, tableName),

		// Composite index: Pattern_Name + Digest, accelerates WHERE + EXISTS queries
		fmt.Sprintf("CREATE INDEX IF NOT EXISTS idx_%s_pattern_digest ON `%s` (`Pattern_Name`, `Digest`)", tableName, tableName),

		// Composite index: Cluster_Id + Digest + Plan, accelerates SQLPlanChanged analysis
		fmt.Sprintf("CREATE INDEX IF NOT EXISTS idx_%s_cluster_digest_plan ON `%s` (`Cluster_Id`, `Digest`, `Plan`)", tableName, tableName),
	}

	for _, indexSQL := range indexes {
		if err := orgmDB.Exec(indexSQL).Error; err != nil {
			return fmt.Errorf("failed to create index: %w", err)
		}
	}

	return nil
}

// GetGormDB returns the global GORM database instance
func GetGormDB() *gorm.DB {
	return SqlLiteDB
}

// CloseGormDB closes the GORM database connection
func CloseGormDB() error {
	if SqlLiteDB == nil {
		return nil
	}

	sqlDB, err := SqlLiteDB.DB()
	if err != nil {
		return err
	}

	return sqlDB.Close()
}