package mysql

import (
	"fmt"
	"regexp"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	"github.com/tidbcloud/pov-service/internal/config"
)

// NewDB creates a new MySQL database connection with proper configuration
func NewDB(cfg config.DatabaseConfig) (*sqlx.DB, error) {
	// Build DSN (Data Source Name)
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&collation=utf8mb4_unicode_ci&parseTime=true&loc=UTC&tls=%s",
		cfg.User,
		cfg.Password,
		cfg.Host,
		cfg.Port,
		cfg.Database,
		cfg.TLSMode,
	)

	// Open database connection
	db, err := sqlx.Connect("mysql", dsn)
	if err != nil {
		return nil, fmt.Errorf("failed to connect to database: %w", err)
	}

	// Configure connection pool
	db.SetMaxOpenConns(cfg.MaxOpenConns)
	db.SetMaxIdleConns(cfg.MaxIdleConns)
	db.SetConnMaxLifetime(time.Hour)
	db.SetConnMaxIdleTime(time.Minute * 30)

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

	return db, nil
}

// RunMigrations executes database migrations from migration files
// This will be used in production with proper migration tools
func RunMigrations(db *sqlx.DB, migrationSQL string) error {
	return runSQLStatements(db, migrationSQL)
}

// RunMigrationsFromSQL executes database migrations from custom SQL (for backward compatibility)
func RunMigrationsFromSQL(db *sqlx.DB, migrationSQL string) error {
	return runSQLStatements(db, migrationSQL)
}

// runSQLStatements executes multiple SQL statements with proper parsing
func runSQLStatements(db *sqlx.DB, sqlContent string) error {
	statements := parseSQLStatements(sqlContent)

	for i, stmt := range statements {
		stmt = strings.TrimSpace(stmt)
		if stmt == "" {
			continue
		}

		_, err := db.Exec(stmt)
		if err != nil {
			return fmt.Errorf("failed to execute SQL statement %d: %s, error: %w", i+1, stmt, err)
		}
	}

	return nil
}

// runSQLStatementsInTx executes multiple SQL statements within a transaction
func runSQLStatementsInTx(tx *sqlx.Tx, sqlContent string) error {
	statements := parseSQLStatements(sqlContent)

	for i, stmt := range statements {
		stmt = strings.TrimSpace(stmt)
		if stmt == "" {
			continue
		}

		_, err := tx.Exec(stmt)
		if err != nil {
			return fmt.Errorf("failed to execute SQL statement %d: %s, error: %w", i+1, stmt, err)
		}
	}

	return nil
}

// parseSQLStatements properly parses SQL content into individual statements
// Handles multi-line statements and removes comments
func parseSQLStatements(sqlContent string) []string {
	// Remove single-line comments (-- comments)
	commentRegex := regexp.MustCompile(`(?m)--.*?$`)
	sqlContent = commentRegex.ReplaceAllString(sqlContent, "")

	// Remove multi-line comments (/* comments */)
	multiCommentRegex := regexp.MustCompile(`(?s)/\*.*?\*/`)
	sqlContent = multiCommentRegex.ReplaceAllString(sqlContent, "")

	// Split by semicolons but be more careful about it
	// This handles the case where semicolons appear in string literals or other contexts
	var statements []string
	var current strings.Builder
	var inQuotes bool
	var quoteChar rune

	runes := []rune(sqlContent)
	for i := 0; i < len(runes); i++ {
		r := runes[i]

		switch {
		case !inQuotes && (r == '\'' || r == '"' || r == '`'):
			// Start of quoted string
			inQuotes = true
			quoteChar = r
			current.WriteRune(r)
		case inQuotes && r == quoteChar:
			// End of quoted string (check for escaped quotes)
			if i+1 < len(runes) && runes[i+1] == quoteChar {
				// Escaped quote, include both
				current.WriteRune(r)
				current.WriteRune(runes[i+1])
				i++ // Skip next character
			} else {
				// End of quoted string
				inQuotes = false
				current.WriteRune(r)
			}
		case !inQuotes && r == ';':
			// End of statement
			stmt := strings.TrimSpace(current.String())
			if stmt != "" {
				statements = append(statements, stmt)
			}
			current.Reset()
		default:
			current.WriteRune(r)
		}
	}

	// Add any remaining statement
	if stmt := strings.TrimSpace(current.String()); stmt != "" {
		statements = append(statements, stmt)
	}

	return statements
}