package persistence

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

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

const (
	integrationOutboundCommandTable        = "_System_IntegrationOutboundCommand"
	integrationOutboundDependencyIndex     = "idx_integration_outbound_dependency"
	integrationOutboundReconcileIndex      = "idx_integration_outbound_dependency_reconcile"
	integrationOutboundColumnInspectionSQL = `SELECT LOWER(COLUMN_TYPE), IS_NULLABLE, COALESCE(COLLATION_NAME, ''), COLUMN_DEFAULT, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?`
)

var integrationOutboundDependencyColumnNames = []string{
	"DependencyKind",
	"DependencyKey",
	"DependencyReadinessKeys",
	"DependencyBlockedAt",
}

var integrationOutboundDependencyIndexColumns = []string{
	"ScopeDigest",
	"DependencyKind",
	"DependencyKey",
	"State",
	"Id",
}

type integrationOutboundPhysicalColumn struct {
	columnType string
	nullable   string
	collation  string
	defaultVal sql.NullString
	extra      string
}

// reconcileIntegrationOutboundDependencySchema is the warm-schema half of the
// outbound dependency-wake cutover. system_tables.json remains the shape
// authority; this function only converges an existing managed command table to
// that declared shape before a dependency-aware Connector is allowed to start.
func (r *SchemaRepository) reconcileIntegrationOutboundDependencySchema(
	ctx context.Context,
	def schema.TableDefinition,
) error {
	columns, indexes, err := integrationOutboundDependencyDeclaration(def)
	if err != nil {
		return err
	}

	missing := make([]schema.ColumnDefinition, 0, len(columns))
	for _, column := range columns {
		physical, found, inspectErr := inspectIntegrationOutboundDependencyColumn(
			ctx, r.db, def.TableName, column.Name,
		)
		if inspectErr != nil {
			return fmt.Errorf("reconcile Integration outbound dependency column %s: %w", column.Name, inspectErr)
		}
		if !found {
			missing = append(missing, column)
			continue
		}
		if err := validateIntegrationOutboundDependencyColumn(column, physical); err != nil {
			return err
		}
	}

	missingIndexes := make([]schema.IndexDefinition, 0, len(indexes))
	for _, index := range indexes {
		indexColumns, unique, hasPrefix, inspectErr := inspectManagedPhysicalIndex(
			ctx, r.db, def.TableName, index.Name,
		)
		if inspectErr != nil {
			return fmt.Errorf("reconcile Integration outbound dependency index %s: %w", index.Name, inspectErr)
		}
		if len(indexColumns) == 0 {
			missingIndexes = append(missingIndexes, index)
			continue
		}
		if unique || hasPrefix || !equalExactColumnNames(indexColumns, index.Columns) {
			return fmt.Errorf(
				"reconcile Integration outbound dependency index: physical %s.%s has incompatible shape (unique=%t, prefix=%t, columns=%v)",
				def.TableName, index.Name, unique, hasPrefix, indexColumns,
			)
		}
	}

	if len(missing) != 0 {
		clauses := make([]string, 0, len(missing))
		for _, column := range missing {
			clauses = append(clauses, "ADD COLUMN "+r.buildColumnDDL(column, false))
		}
		if _, err := r.db.ExecContext(
			ctx,
			fmt.Sprintf("ALTER TABLE `%s` %s", def.TableName, strings.Join(clauses, ", ")),
		); err != nil {
			return fmt.Errorf("reconcile Integration outbound dependency columns: %w", err)
		}
	}

	for _, index := range missingIndexes {
		if _, err := r.db.ExecContext(
			ctx,
			fmt.Sprintf(
				"ALTER TABLE `%s` ADD INDEX `%s` (`%s`)",
				def.TableName, index.Name, strings.Join(index.Columns, "`, `"),
			),
		); err != nil {
			return fmt.Errorf("reconcile Integration outbound dependency index %s: %w", index.Name, err)
		}
	}

	for _, column := range columns {
		physical, found, err := inspectIntegrationOutboundDependencyColumn(ctx, r.db, def.TableName, column.Name)
		if err != nil {
			return fmt.Errorf("verify migrated Integration outbound dependency column %s: %w", column.Name, err)
		}
		if !found {
			return fmt.Errorf("verify migrated Integration outbound dependency column %s: column is missing", column.Name)
		}
		if err := validateIntegrationOutboundDependencyColumn(column, physical); err != nil {
			return err
		}
	}
	for _, index := range indexes {
		if err := VerifyExactTableIndex(ctx, r.db, def.TableName, index.Name, false, index.Columns); err != nil {
			return fmt.Errorf("verify migrated Integration outbound dependency index %s: %w", index.Name, err)
		}
	}
	return nil
}

func integrationOutboundDependencyDeclaration(
	def schema.TableDefinition,
) ([]schema.ColumnDefinition, []schema.IndexDefinition, error) {
	if def.TableName != integrationOutboundCommandTable {
		return nil, nil, fmt.Errorf("Integration outbound dependency schema received table %q", def.TableName)
	}
	byName := make(map[string]schema.ColumnDefinition, len(def.Columns))
	for _, column := range def.Columns {
		byName[column.Name] = column
	}
	columns := make([]schema.ColumnDefinition, 0, len(integrationOutboundDependencyColumnNames))
	for _, name := range integrationOutboundDependencyColumnNames {
		column, ok := byName[name]
		if !ok {
			return nil, nil, fmt.Errorf("Integration outbound catalog is missing dependency column %s", name)
		}
		if !column.Nullable || column.Default != "" || column.PrimaryKey || column.Unique || column.AutoIncrement {
			return nil, nil, fmt.Errorf("Integration outbound catalog dependency column %s has incompatible semantics", name)
		}
		columns = append(columns, column)
	}
	if !strings.EqualFold(columns[0].Type, "VARCHAR(64)") ||
		!strings.EqualFold(columns[1].Type, "VARCHAR(255)") ||
		!strings.EqualFold(columns[1].CollationName, "utf8mb4_bin") ||
		!strings.EqualFold(columns[2].Type, "JSON") ||
		!strings.EqualFold(columns[3].Type, "DATETIME(6)") {
		return nil, nil, fmt.Errorf("Integration outbound catalog dependency columns have incompatible physical types")
	}
	wantIndexes := map[string][]string{
		integrationOutboundDependencyIndex: integrationOutboundDependencyIndexColumns,
		integrationOutboundReconcileIndex:  {"ScopeDigest", "CatalogRevision", "State", "Id"},
	}
	indexes := make([]schema.IndexDefinition, 0, len(wantIndexes))
	for _, index := range def.Indices {
		want, needed := wantIndexes[index.Name]
		if !needed {
			continue
		}
		if index.Primary || index.Unique || !equalExactColumnNames(index.Columns, want) {
			return nil, nil, fmt.Errorf("Integration outbound catalog dependency index %s has incompatible shape", index.Name)
		}
		indexes = append(indexes, index)
		delete(wantIndexes, index.Name)
	}
	if len(wantIndexes) != 0 {
		return nil, nil, fmt.Errorf("Integration outbound catalog is missing dependency indexes %v", wantIndexes)
	}
	return columns, indexes, nil
}

func inspectIntegrationOutboundDependencyColumn(
	ctx context.Context,
	db *sql.DB,
	tableName string,
	columnName string,
) (integrationOutboundPhysicalColumn, bool, error) {
	var physical integrationOutboundPhysicalColumn
	err := db.QueryRowContext(
		ctx, integrationOutboundColumnInspectionSQL, tableName, columnName,
	).Scan(
		&physical.columnType,
		&physical.nullable,
		&physical.collation,
		&physical.defaultVal,
		&physical.extra,
	)
	if err == sql.ErrNoRows {
		return integrationOutboundPhysicalColumn{}, false, nil
	}
	return physical, err == nil, err
}

func validateIntegrationOutboundDependencyColumn(
	declared schema.ColumnDefinition,
	physical integrationOutboundPhysicalColumn,
) error {
	desiredType := strings.ToLower(ResolveColumnType(declared))
	collationMatches := declared.CollationName == "" ||
		strings.EqualFold(physical.collation, declared.CollationName)
	if physical.columnType == desiredType && physical.nullable == "YES" &&
		collationMatches && !physical.defaultVal.Valid && strings.TrimSpace(physical.extra) == "" {
		return nil
	}
	return fmt.Errorf(
		"reconcile Integration outbound dependency column %s: incompatible physical shape (type=%s, nullable=%s, collation=%s, default=%v, extra=%q)",
		declared.Name, physical.columnType, physical.nullable, physical.collation,
		physical.defaultVal, physical.extra,
	)
}