package database

import (
	"database/sql"
	"fmt"
	"strings"

	"github.com/tidbcloud/AgentTeam/internal/store/resource/memory"
	"gorm.io/gorm"
)

func automationIndexColumns(db *gorm.DB, table string, index string) ([]string, error) {
	switch strings.ToLower(strings.TrimSpace(db.Dialector.Name())) {
	case "mysql":
		return automationIndexColumnsFromRows(db.Raw(`
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND INDEX_NAME = ?
ORDER BY SEQ_IN_INDEX`, table, index).Rows())
	case "sqlite":
		return automationIndexColumnsFromRows(db.Raw("PRAGMA index_info(" + quoteSQLString(index) + ")").Rows())
	case "postgres":
		return automationIndexColumnsFromRows(db.Raw(`
SELECT a.attname
FROM pg_class t
JOIN pg_index ix ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN unnest(ix.indkey) WITH ORDINALITY AS k(attnum, ord) ON true
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = k.attnum
WHERE t.relname = ? AND i.relname = ?
ORDER BY k.ord`, table, index).Rows())
	default:
		return nil, fmt.Errorf("verify automation index columns: unsupported dialect %q", db.Dialector.Name())
	}
}

func automationIndexColumnsFromRows(rows *sql.Rows, err error) ([]string, error) {
	if err != nil {
		return nil, fmt.Errorf("read automation index columns: %w", err)
	}
	defer rows.Close()

	columnNames, err := rows.Columns()
	if err != nil {
		return nil, fmt.Errorf("read automation index column metadata: %w", err)
	}
	nameIndex := -1
	for i, columnName := range columnNames {
		if strings.EqualFold(columnName, "name") || strings.EqualFold(columnName, "column_name") || strings.EqualFold(columnName, "attname") {
			nameIndex = i
			break
		}
	}
	if nameIndex < 0 {
		return nil, fmt.Errorf("read automation index columns: missing column name field")
	}

	var columns []string
	for rows.Next() {
		values := make([]any, len(columnNames))
		scans := make([]any, len(columnNames))
		for i := range values {
			scans[i] = &values[i]
		}
		if err := rows.Scan(scans...); err != nil {
			return nil, fmt.Errorf("scan automation index column: %w", err)
		}
		switch value := values[nameIndex].(type) {
		case string:
			columns = append(columns, strings.ToLower(strings.TrimSpace(value)))
		case []byte:
			columns = append(columns, strings.ToLower(strings.TrimSpace(string(value))))
		default:
			return nil, fmt.Errorf("scan automation index column: unexpected column name type %T", value)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate automation index columns: %w", err)
	}
	return columns, nil
}

func quoteSQLString(value string) string {
	return "'" + strings.ReplaceAll(value, "'", "''") + "'"
}

func sameStringSlice(left []string, right []string) bool {
	if len(left) != len(right) {
		return false
	}
	for i := range left {
		if !strings.EqualFold(strings.TrimSpace(left[i]), strings.TrimSpace(right[i])) {
			return false
		}
	}
	return true
}

func automationTableModels() map[string]any {
	out := make(map[string]any, len(automationSchemaTables))
	for _, table := range automationSchemaTables {
		out[table.table] = table.model
	}
	out["memories"] = &memory.Memory{}
	return out
}