package bootstrapimpl

import (
	"database/sql"
	"regexp"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/kernel/pkg/schema"
)

func TestVerifyConnectorServingEpochReproducibilityAuthoritySchemaAcceptsExactCatalogShape(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	definitions, err := connectorServingEpochReproducibilityDefinitions()
	if err != nil {
		t.Fatalf("connectorServingEpochReproducibilityDefinitions: %v", err)
	}
	expectConnectorServingEpochPhysicalColumns(mock, definitions)
	for _, definition := range definitions {
		for _, index := range connectorServingEpochRequiredIndexes(definition) {
			expectConnectorServingEpochExactIndex(mock, index, index.unique, index.columns)
		}
		expectConnectorServingEpochForeignKeys(mock, definition, nil)
	}

	if err := verifyConnectorServingEpochReproducibilityAuthoritySchema(db); err != nil {
		t.Fatalf("verify exact ServingEpoch reproducibility schema: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("SQL expectations: %v", err)
	}
}

func TestVerifyConnectorServingEpochReproducibilityColumnsRejectsPhysicalDrift(t *testing.T) {
	definitions, err := connectorServingEpochReproducibilityDefinitions()
	if err != nil {
		t.Fatalf("connectorServingEpochReproducibilityDefinitions: %v", err)
	}
	bindingOwner := connectorServingEpochDefinitionForTest(
		t,
		definitions,
		"_System_ConnectorServingEpochBindingOwner",
	)

	tests := []struct {
		name       string
		mutate     func(map[string]map[string]PhysicalColumnShape)
		wantDetail string
	}{
		{
			name: "definition-set coordinate must retain its parent-compatible collation",
			mutate: func(columns map[string]map[string]PhysicalColumnShape) {
				shape := columns[strings.ToLower(bindingOwner.TableName)][strings.ToLower("PackGenerationId")]
				shape.CollationName = sql.NullString{String: "utf8mb4_bin", Valid: true}
				columns[strings.ToLower(bindingOwner.TableName)][strings.ToLower("PackGenerationId")] = shape
			},
			wantDetail: "PackGenerationId",
		},
		{
			name: "managed timestamps require their catalog default",
			mutate: func(columns map[string]map[string]PhysicalColumnShape) {
				shape := columns[strings.ToLower(bindingOwner.TableName)][strings.ToLower("CreatedDate")]
				shape.DefaultVal = sql.NullString{}
				columns[strings.ToLower(bindingOwner.TableName)][strings.ToLower("CreatedDate")] = shape
			},
			wantDetail: "CreatedDate",
		},
		{
			name: "authority tables reject extra columns",
			mutate: func(columns map[string]map[string]PhysicalColumnShape) {
				columns[strings.ToLower(bindingOwner.TableName)]["unexpected"] = PhysicalColumnShape{
					ColumnType: "varchar(255)",
				}
			},
			wantDetail: "exact catalog-owned shape",
		},
	}

	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			columns := connectorServingEpochPhysicalColumnShapes([]schema.TableDefinition{bindingOwner})
			test.mutate(columns)
			err := verifyConnectorServingEpochReproducibilityColumns(columns, bindingOwner)
			if err == nil || !strings.Contains(err.Error(), test.wantDetail) || !strings.Contains(err.Error(), "cmd/setup apply") {
				t.Fatalf("column drift error = %v, want %q and apply guidance", err, test.wantDetail)
			}
		})
	}
}

func TestVerifyConnectorServingEpochReproducibilityIndexesRequiresCompositePrimaryKey(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	definitions, err := connectorServingEpochReproducibilityDefinitions()
	if err != nil {
		t.Fatalf("connectorServingEpochReproducibilityDefinitions: %v", err)
	}
	credential := connectorServingEpochDefinitionForTest(
		t,
		definitions,
		"_System_ConnectorServingEpochCredentialGeneration",
	)
	required := connectorServingEpochRequiredIndexes(credential)
	if len(required) == 0 || required[0].indexName != "PRIMARY" {
		t.Fatalf("credential index requirements = %+v, want PRIMARY first", required)
	}
	expectConnectorServingEpochExactIndex(mock, required[0], true, nil)

	err = verifyConnectorServingEpochReproducibilityIndexes(db, credential)
	if err == nil ||
		!strings.Contains(err.Error(), "PRIMARY(ServingEpochId,CredentialOrdinal)") ||
		!strings.Contains(err.Error(), "cmd/setup apply") {
		t.Fatalf("missing composite-primary readiness error = %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("SQL expectations: %v", err)
	}
}

func TestVerifyConnectorServingEpochReproducibilityForeignKeysRequiresExactRestrictiveSet(t *testing.T) {
	definitions, err := connectorServingEpochReproducibilityDefinitions()
	if err != nil {
		t.Fatalf("connectorServingEpochReproducibilityDefinitions: %v", err)
	}
	bindingOwner := connectorServingEpochDefinitionForTest(
		t,
		definitions,
		"_System_ConnectorServingEpochBindingOwner",
	)

	tests := []struct {
		name   string
		mutate func([]connectorServingEpochPhysicalForeignKey) []connectorServingEpochPhysicalForeignKey
	}{
		{
			name: "cascade",
			mutate: func(foreignKeys []connectorServingEpochPhysicalForeignKey) []connectorServingEpochPhysicalForeignKey {
				foreignKeys[1].deleteRule = "CASCADE"
				return foreignKeys
			},
		},
		{
			name: "cross-schema parent",
			mutate: func(foreignKeys []connectorServingEpochPhysicalForeignKey) []connectorServingEpochPhysicalForeignKey {
				foreignKeys[0].referencedSchema = "other_database"
				return foreignKeys
			},
		},
		{
			name: "extra foreign key",
			mutate: func(foreignKeys []connectorServingEpochPhysicalForeignKey) []connectorServingEpochPhysicalForeignKey {
				return append(foreignKeys, foreignKeys[0])
			},
		},
	}

	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			db, mock, err := sqlmock.New()
			if err != nil {
				t.Fatalf("sqlmock.New: %v", err)
			}
			defer db.Close()

			physical := connectorServingEpochPhysicalForeignKeys(bindingOwner)
			expectConnectorServingEpochForeignKeys(mock, bindingOwner, test.mutate(physical))
			err = verifyConnectorServingEpochReproducibilityForeignKeys(db, bindingOwner)
			if err == nil || !strings.Contains(err.Error(), bindingOwner.TableName) || !strings.Contains(err.Error(), "cmd/setup apply") {
				t.Fatalf("foreign-key drift error = %v", err)
			}
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("SQL expectations: %v", err)
			}
		})
	}
}

func connectorServingEpochDefinitionForTest(
	t *testing.T,
	definitions []schema.TableDefinition,
	tableName string,
) schema.TableDefinition {
	t.Helper()
	for _, definition := range definitions {
		if definition.TableName == tableName {
			return definition
		}
	}
	t.Fatalf("definition %s is missing", tableName)
	return schema.TableDefinition{}
}

func connectorServingEpochPhysicalColumnShapes(
	definitions []schema.TableDefinition,
) map[string]map[string]PhysicalColumnShape {
	physical := make(map[string]map[string]PhysicalColumnShape, len(definitions)+1)
	physical[strings.ToLower("_System_PackGenerationConnectorBindingDefinitionSet")] = map[string]PhysicalColumnShape{
		strings.ToLower("PackGenerationId"): {
			ColumnType:    "varchar(255)",
			CollationName: sql.NullString{String: "utf8mb4_unicode_ci", Valid: true},
		},
	}
	for _, definition := range definitions {
		tableKey := strings.ToLower(definition.TableName)
		physical[tableKey] = make(map[string]PhysicalColumnShape, len(definition.Columns))
		for _, column := range definition.Columns {
			collation := column.CollationName
			if definition.TableName == "_System_ConnectorServingEpochBindingOwner" && column.Name == "PackGenerationId" {
				collation = "utf8mb4_unicode_ci"
			}
			physical[tableKey][strings.ToLower(column.Name)] = PhysicalColumnShape{
				ColumnType: normalizeColumnType(DesiredColumnType(column)),
				Nullable:   column.Nullable,
				DefaultVal: sql.NullString{String: column.Default, Valid: column.Default != ""},
				CollationName: sql.NullString{
					String: collation,
					Valid:  collation != "",
				},
			}
		}
	}
	return physical
}

func expectConnectorServingEpochPhysicalColumns(
	mock sqlmock.Sqlmock,
	definitions []schema.TableDefinition,
) {
	rows := sqlmock.NewRows([]string{
		"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME",
	})
	physical := connectorServingEpochPhysicalColumnShapes(definitions)
	for _, definition := range definitions {
		for _, column := range definition.Columns {
			shape := physical[strings.ToLower(definition.TableName)][strings.ToLower(column.Name)]
			var defaultValue any
			if shape.DefaultVal.Valid {
				defaultValue = shape.DefaultVal.String
			}
			var collation any
			if shape.CollationName.Valid {
				collation = shape.CollationName.String
			}
			nullable := "NO"
			if shape.Nullable {
				nullable = "YES"
			}
			rows.AddRow(definition.TableName, column.Name, shape.ColumnType, nullable, defaultValue, collation)
		}
	}
	parent := physical[strings.ToLower("_System_PackGenerationConnectorBindingDefinitionSet")][strings.ToLower("PackGenerationId")]
	rows.AddRow(
		"_System_PackGenerationConnectorBindingDefinitionSet",
		"PackGenerationId",
		parent.ColumnType,
		"NO",
		nil,
		parent.CollationName.String,
	)
	mock.ExpectQuery(regexp.QuoteMeta(
		"SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE()",
	)).WillReturnRows(rows)
}

func expectConnectorServingEpochExactIndex(
	mock sqlmock.Sqlmock,
	required requiredManagedIndex,
	unique bool,
	columns []string,
) {
	rows := sqlmock.NewRows([]string{"NON_UNIQUE", "SEQ_IN_INDEX", "COLUMN_NAME", "SUB_PART"})
	nonUnique := 1
	if unique {
		nonUnique = 0
	}
	for position, column := range columns {
		rows.AddRow(nonUnique, position+1, column, nil)
	}
	mock.ExpectQuery(regexp.QuoteMeta("SELECT NON_UNIQUE, SEQ_IN_INDEX, COLUMN_NAME, SUB_PART")).
		WithArgs(required.tableName, required.indexName).
		WillReturnRows(rows)
}

func connectorServingEpochPhysicalForeignKeys(
	definition schema.TableDefinition,
) []connectorServingEpochPhysicalForeignKey {
	foreignKeys := make([]connectorServingEpochPhysicalForeignKey, 0, len(definition.ForeignKeys))
	for position, required := range definition.ForeignKeys {
		referencedTable, referencedColumn, ok := parseConnectorServingEpochForeignKeyReference(required.References)
		if !ok {
			panic("test catalog contains invalid foreign-key reference")
		}
		deleteRule := "RESTRICT"
		updateRule := "NO ACTION"
		if position%2 == 1 {
			deleteRule, updateRule = "NO ACTION", "RESTRICT"
		}
		foreignKeys = append(foreignKeys, connectorServingEpochPhysicalForeignKey{
			columnName:       required.Column,
			referencedSchema: "nexuscrm",
			currentSchema:    "nexuscrm",
			referencedTable:  referencedTable,
			referencedColumn: referencedColumn,
			deleteRule:       deleteRule,
			updateRule:       updateRule,
		})
	}
	return foreignKeys
}

func expectConnectorServingEpochForeignKeys(
	mock sqlmock.Sqlmock,
	definition schema.TableDefinition,
	override []connectorServingEpochPhysicalForeignKey,
) {
	foreignKeys := override
	if foreignKeys == nil {
		foreignKeys = connectorServingEpochPhysicalForeignKeys(definition)
	}
	rows := sqlmock.NewRows([]string{
		"COLUMN_NAME",
		"REFERENCED_TABLE_SCHEMA",
		"DATABASE()",
		"REFERENCED_TABLE_NAME",
		"REFERENCED_COLUMN_NAME",
		"DELETE_RULE",
		"UPDATE_RULE",
	})
	for _, foreignKey := range foreignKeys {
		rows.AddRow(
			foreignKey.columnName,
			foreignKey.referencedSchema,
			foreignKey.currentSchema,
			foreignKey.referencedTable,
			foreignKey.referencedColumn,
			foreignKey.deleteRule,
			foreignKey.updateRule,
		)
	}
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT
k.COLUMN_NAME, k.REFERENCED_TABLE_SCHEMA, DATABASE(), k.REFERENCED_TABLE_NAME,
k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS r
  ON r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
 AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME
 AND r.TABLE_NAME = k.TABLE_NAME
WHERE k.TABLE_SCHEMA = DATABASE()
  AND LOWER(k.TABLE_NAME) = LOWER(?)
  AND k.REFERENCED_TABLE_NAME IS NOT NULL`)).
		WithArgs(definition.TableName).
		WillReturnRows(rows)
}