package dao

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

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

	_ "github.com/go-sql-driver/mysql"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

// MySQLClient MySQL client struct
type MySQLClient struct {
	db     *sql.DB
	gormDB *gorm.DB
}

// NewMySQLClient Create a new MySQL client
func NewMySQLClient() (*MySQLClient, error) {
	// Read database connection info from config file
	mysqlConfig := config.AppConfig.Database.MySQL

	// Build DSN and add TLS support for TiDB Cloud connection
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s&parseTime=True&loc=Local&tls=true&allowNativePasswords=true",
		mysqlConfig.User,
		mysqlConfig.Password,
		mysqlConfig.Host,
		mysqlConfig.Port,
		mysqlConfig.Database,
		mysqlConfig.Charset,
	)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %v", err)
	}

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

	gormDB, err := gorm.Open(mysql.New(mysql.Config{
		Conn: db,
	}), &gorm.Config{})
	if err != nil {
		return nil, fmt.Errorf("failed to connect to gorm: %v", err)
	}

	return &MySQLClient{db: db, gormDB: gormDB}, nil
}

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

// IsNewDigest Check if digest is newly appeared
// Determine by checking if the digest exists in yesterday's daily_stats table
func (c *MySQLClient) IsNewDigest(digest string, currentDate time.Time) (bool, error) {
	yesterdayDate := currentDate.AddDate(0, 0, -1)

	sqlStr := `SELECT COUNT(*) FROM daily_stats WHERE digest = ? AND report_date = ?`

	var count int
	err := c.db.QueryRow(sqlStr, digest, yesterdayDate.Format("2006-01-02")).Scan(&count)
	if err != nil {
		return false, fmt.Errorf("failed to check digest existence: %v", err)
	}

	// If there is no record yesterday, it is a new digest
	return count == 0, nil
}

// CheckNewDigests Batch check if multiple digests are new
func (c *MySQLClient) CheckNewDigests(digests []string, currentDate time.Time) (map[string]bool, error) {
	if len(digests) == 0 {
		return make(map[string]bool), nil
	}

	// Calculate yesterday's date
	yesterdayDate := currentDate.AddDate(0, 0, -1)

	result := make(map[string]bool)

	// Initialize all digests as new
	for _, digest := range digests {
		result[digest] = true
	}

	// Build placeholders for IN query
	placeholderStr := ""
	args := make([]interface{}, len(digests)+1)
	args[0] = yesterdayDate.Format("2006-01-02")

	for i, digest := range digests {
		if i > 0 {
			placeholderStr += ","
		}
		placeholderStr += "?"
		args[i+1] = digest
	}

	sqlStr := fmt.Sprintf(`SELECT DISTINCT digest FROM daily_stats WHERE report_date = ? AND digest IN (%s)`, placeholderStr)

	rows, err := c.db.Query(sqlStr, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query existing digests: %v", err)
	}
	defer rows.Close()

	// Mark existing digests as not new
	for rows.Next() {
		var digest string
		if err := rows.Scan(&digest); err != nil {
			return nil, fmt.Errorf("failed to scan digest: %v", err)
		}
		result[digest] = false
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating rows: %v", err)
	}

	return result, nil
}

// SaveAnalysisResult Save the complete analysis result
func (c *MySQLClient) SaveAnalysisResult(finalOutput dto.FinalOutput) error {
	log2.PlainInfo(fmt.Sprintf("Saving analysis result to MySQL for date: %v", time.Now().Format("2006-01-02")))

	summary := finalOutput.Summary
	// reportDate := time.Now().Format("2006-01-02")
	reportDate := config.AppConfig.Day
	details := map[string]interface{}{
		"critical_details": summary.CriticalDetails,
		"major_details":    summary.MajorDetails,
	}
	detailsJson, _ := json.Marshal(details)
	_, err := c.db.Exec(`INSERT INTO daily_summary (report_date, total_slow_queries, unique_digests, critical_count, major_count, details_json, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)`,
		reportDate,
		summary.TotalSlowQueries,
		summary.UniqueDigests,
		summary.CriticalCount,
		summary.MajorCount,
		string(detailsJson),
		time.Now(),
	)
	if err != nil {
		return fmt.Errorf("failed to save daily_summary: %v", err)
	}

	for _, stat := range finalOutput.QueryResults {
		for _, t := range stat.Top10 {
			_, err := c.db.Exec(`INSERT INTO daily_stats (report_date, sql_timestamp, pattern_name, level, query, digest, total_count, avg_query_time, avg_mem_max, max_mem, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
				reportDate,
				t.FormattedTimestamp,
				stat.QueryName,
				stat.Level,
				t.Query,
				t.Digest,
				t.TotalCount,
				t.AVGQueryTime,
				t.AVGMemMax,
				t.MAXMem,
				time.Now(),
			)
			if err != nil {
				return fmt.Errorf("failed to save daily_stats: %v", err)
			}
		}
	}
	return nil
}

/*
// SaveAnalysisResultWithGorm Save the complete analysis result using GORM
func (c *MySQLClient) SaveAnalysisResultWithGorm(finalOutput dto.FinalOutput) error {
	log2.PlainInfo(fmt.Sprintf("Saving analysis result to MySQL with GORM for date: %v", time.Now().Format("2006-01-02")))

	summary := finalOutput.Summary
	reportDate := config.AppConfig.Day

	details := model.DetailsData{
		CriticalDetails: summary.CriticalDetails,
		MajorDetails:    summary.MajorDetails,
	}

	dailySummary := model.DailySummary{
		OrgID:            summary.OrgID,
		ReportDate:       reportDate,
		TotalSlowQueries: summary.TotalSlowQueries,
		UniqueDigests:    summary.UniqueDigests,
		CriticalCount:    summary.CriticalCount,
		MajorCount:       summary.MajorCount,
		DetailsJson:      details,
		CreatedAt:        time.Now(),
	}

	if err := c.gormDB.Create(&dailySummary).Error; err != nil {
		return fmt.Errorf("failed to save daily_summary with GORM: %v", err)
	}

	for _, stat := range finalOutput.QueryResults {
		for _, t := range stat.Top10 {
			dailyStat := model.DailyStat{
				ClusterID:  strings.Join(t.ClusterIds, ","),
				ReportDate: reportDate,
				// SqlTimestamp: t.FormattedTimestamp,
				PatternName:  stat.QueryName,
				Level:        stat.Level,
				Query:        t.Query,
				Digest:       t.Digest,
				TotalCount:   t.TotalCount,
				AvgQueryTime: t.AVGQueryTime,
				AvgMemMax:    t.AVGMemMax,
				// MaxMem:       t.MAXMem,
				CreatedAt: time.Now(),
			}
			if err := c.gormDB.Create(&dailyStat).Error; err != nil {
				return err
			}
		}
	}

	return nil
}
*/