package packs

import (
	"database/sql"
	"database/sql/driver"
	"errors"
	"fmt"
	"io"
	"strings"
	"time"

	domainschema "github.com/nexuscrm/kernel/pkg/schema"
)

const packIndexDDLMaxAttempts = 3

type packIndexInfo struct {
	name   string
	unique bool
}

func reconcilePackTableIndices(db *sql.DB, defs []domainschema.TableDefinition) error {
	existingTables, err := loadPhysicalTableSet(db)
	if err != nil {
		return fmt.Errorf("load physical tables: %w", err)
	}
	existingIndices, err := loadPhysicalIndexSet(db)
	if err != nil {
		return fmt.Errorf("load physical indices: %w", err)
	}

	for _, def := range defs {
		if len(def.Indices) == 0 {
			continue
		}
		tableKey := strings.ToLower(def.TableName)
		if !existingTables[tableKey] {
			continue
		}
		tableIndices := existingIndices[tableKey]

		for _, idx := range def.Indices {
			colKey := packIndexColumnKey(idx.Columns)
			if existing, ok := tableIndices[colKey]; ok {
				if existing.unique != idx.Unique {
					return fmt.Errorf("index %s on %s has unique=%v, want %v", existing.name, def.TableName, existing.unique, idx.Unique)
				}
				continue
			}

			indexName := packIndexName(def.TableName, idx)
			ddlKind := "INDEX"
			if idx.Unique {
				ddlKind = "UNIQUE INDEX"
			}
			if err := addPackTableIndex(db, def.TableName, idx, ddlKind, indexName); err != nil {
				return fmt.Errorf("add index %s on %s(%s): %w", indexName, def.TableName, strings.Join(idx.Columns, ", "), err)
			}
			if tableIndices == nil {
				tableIndices = make(map[string]packIndexInfo)
				existingIndices[tableKey] = tableIndices
			}
			removePackIndexInfoByName(tableIndices, indexName)
			tableIndices[colKey] = packIndexInfo{name: indexName, unique: idx.Unique}
		}
	}
	return nil
}

func addPackTableIndex(db *sql.DB, tableName string, idx domainschema.IndexDefinition, ddlKind string, indexName string) error {
	ddl := fmt.Sprintf("ALTER TABLE `%s` ADD %s `%s` (%s)", tableName, ddlKind, indexName, quoteIndexColumns(idx.Columns))
	var lastErr error
	for attempt := 1; attempt <= packIndexDDLMaxAttempts; attempt++ {
		if _, err := db.Exec(ddl); err != nil {
			lastErr = err
			if isDuplicateIndexNameError(err) {
				converged, checkErr := packTableIndexConverged(db, tableName, idx)
				if checkErr == nil && converged {
					return nil
				}
				if checkErr != nil && isTransientDDLConnectionError(checkErr) && attempt < packIndexDDLMaxAttempts {
					sleepPackIndexDDLRetry(attempt)
					continue
				}
				if checkErr != nil {
					return fmt.Errorf("index %s already exists on %s but verifying existing columns failed: %w", indexName, tableName, checkErr)
				}
				return fmt.Errorf("index %s already exists on %s with different columns or uniqueness; refusing to replace without ownership proof", indexName, tableName)
			}
			if !isTransientDDLConnectionError(err) {
				return err
			}
			converged, checkErr := packTableIndexConverged(db, tableName, idx)
			if checkErr == nil && converged {
				return nil
			}
			if checkErr != nil && !isTransientDDLConnectionError(checkErr) {
				return fmt.Errorf("%w; post-check target index: %v", err, checkErr)
			}
			if attempt < packIndexDDLMaxAttempts {
				sleepPackIndexDDLRetry(attempt)
				continue
			}
			break
		}
		return nil
	}
	return lastErr
}

func packTableIndexConverged(db *sql.DB, tableName string, idx domainschema.IndexDefinition) (bool, error) {
	physicalIndices, err := loadPhysicalIndexSet(db)
	if err != nil {
		return false, err
	}
	tableIndices := physicalIndices[strings.ToLower(tableName)]
	existing, ok := tableIndices[packIndexColumnKey(idx.Columns)]
	return ok && existing.unique == idx.Unique, nil
}

func removePackIndexInfoByName(tableIndices map[string]packIndexInfo, indexName string) {
	for colKey, info := range tableIndices {
		if strings.EqualFold(info.name, indexName) {
			delete(tableIndices, colKey)
		}
	}
}

func sleepPackIndexDDLRetry(attempt int) {
	time.Sleep(time.Duration(attempt) * 200 * time.Millisecond)
}

func loadPhysicalIndexSet(db *sql.DB) (map[string]map[string]packIndexInfo, error) {
	rows, err := db.Query(
		`SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
		 FROM INFORMATION_SCHEMA.STATISTICS
		 WHERE TABLE_SCHEMA = DATABASE()
		 GROUP BY TABLE_NAME, INDEX_NAME, NON_UNIQUE`,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	indices := make(map[string]map[string]packIndexInfo)
	for rows.Next() {
		var tableName, indexName, cols string
		var nonUnique int
		if err := rows.Scan(&tableName, &indexName, &nonUnique, &cols); err != nil {
			return nil, err
		}
		tableKey := strings.ToLower(tableName)
		if indices[tableKey] == nil {
			indices[tableKey] = make(map[string]packIndexInfo)
		}
		colKey := packIndexColumnKey(strings.Split(cols, ","))
		info := packIndexInfo{name: indexName, unique: nonUnique == 0}
		if existing, ok := indices[tableKey][colKey]; ok {
			if !existing.unique && info.unique {
				indices[tableKey][colKey] = info
			}
			continue
		}
		indices[tableKey][colKey] = info
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return indices, nil
}

func packIndexName(tableName string, idx domainschema.IndexDefinition) string {
	if idx.Name != "" {
		return idx.Name
	}
	return fmt.Sprintf("idx_%s_%s", tableName, strings.Join(idx.Columns, "_"))
}

func packIndexColumnKey(columns []string) string {
	lowerCols := make([]string, 0, len(columns))
	for _, col := range columns {
		lowerCols = append(lowerCols, strings.ToLower(strings.TrimSpace(col)))
	}
	return strings.Join(lowerCols, ",")
}

func quoteIndexColumns(columns []string) string {
	quoted := make([]string, 0, len(columns))
	for _, col := range columns {
		quoted = append(quoted, fmt.Sprintf("`%s`", col))
	}
	return strings.Join(quoted, ", ")
}

func isDuplicateIndexNameError(err error) bool {
	if err == nil {
		return false
	}
	lower := strings.ToLower(err.Error())
	return strings.Contains(lower, "duplicate key name") ||
		strings.Contains(lower, "index already exist") ||
		strings.Contains(lower, "error 1061")
}

func isTransientDDLConnectionError(err error) bool {
	if err == nil {
		return false
	}
	if errors.Is(err, driver.ErrBadConn) || errors.Is(err, io.ErrUnexpectedEOF) || errors.Is(err, io.EOF) {
		return true
	}
	lower := strings.ToLower(err.Error())
	return strings.Contains(lower, "invalid connection") ||
		strings.Contains(lower, "unexpected eof") ||
		strings.Contains(lower, "bad connection") ||
		strings.Contains(lower, "broken pipe") ||
		strings.Contains(lower, "connection reset by peer")
}