package bootstrapimpl

import (
	"regexp"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
)

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

	for _, table := range packGenerationConnectorBindingTableShapes {
		expectPackGenerationConnectorBindingColumns(mock, table, "")
	}
	// TiDB can assign the same generated constraint name independently in both
	// tables. The readiness join must scope that name by its owning table.
	for _, foreignKey := range packGenerationConnectorBindingForeignKeyShapes {
		expectPackGenerationConnectorBindingForeignKey(
			mock,
			foreignKey,
			"fk_PackGenerationId",
			"nexuscrm",
			"nexuscrm",
			foreignKey.deleteRule,
			"NO ACTION",
		)
	}

	if err := verifyPackGenerationConnectorBindingDefinitionAuthoritySchema(db); err != nil {
		t.Fatalf("verify exact connector binding authority schema: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("SQL expectations: %v", err)
	}
}

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

	expectPackGenerationConnectorBindingColumns(
		mock,
		packGenerationConnectorBindingTableShapes[0],
		"",
	)
	expectPackGenerationConnectorBindingColumns(
		mock,
		packGenerationConnectorBindingTableShapes[1],
		"PackInstallId",
	)
	err = verifyPackGenerationConnectorBindingDefinitionColumns(db)
	if err == nil ||
		!strings.Contains(err.Error(), "PackInstallId") ||
		!strings.Contains(err.Error(), "utf8mb4_bin") {
		t.Fatalf("non-binary identity readiness error = %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("SQL expectations: %v", err)
	}
}

func TestVerifyPackGenerationConnectorBindingDefinitionForeignKeysRejectsCascade(t *testing.T) {
	tests := []struct {
		name       string
		deleteRule string
		updateRule string
	}{
		{
			name:       "delete",
			deleteRule: "CASCADE",
			updateRule: "RESTRICT",
		},
		{
			name:       "update",
			deleteRule: "RESTRICT",
			updateRule: "CASCADE",
		},
	}

	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()

			required := packGenerationConnectorBindingForeignKeyShapes[0]
			expectPackGenerationConnectorBindingForeignKey(
				mock,
				required,
				"fk_definition_set_generation",
				"nexuscrm",
				"nexuscrm",
				test.deleteRule,
				test.updateRule,
			)
			err = verifyPackGenerationConnectorBindingDefinitionForeignKeys(db)
			if err == nil ||
				!strings.Contains(err.Error(), required.tableName) ||
				!strings.Contains(err.Error(), "ON DELETE RESTRICT ON UPDATE RESTRICT") {
				t.Fatalf("cascade readiness error = %v", err)
			}
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("SQL expectations: %v", err)
			}
		})
	}
}

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

	required := packGenerationConnectorBindingForeignKeyShapes[0]
	expectPackGenerationConnectorBindingForeignKey(
		mock,
		required,
		"fk_definition_set_generation",
		"same_named_parent_in_another_database",
		"nexuscrm",
		required.deleteRule,
		required.updateRule,
	)
	err = verifyPackGenerationConnectorBindingDefinitionForeignKeys(db)
	if err == nil || !strings.Contains(err.Error(), required.tableName) {
		t.Fatalf("cross-schema foreign-key readiness error = %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("SQL expectations: %v", err)
	}
}

func expectPackGenerationConnectorBindingColumns(
	mock sqlmock.Sqlmock,
	table packGenerationConnectorBindingTableShape,
	nonBinaryColumn string,
) {
	query := regexp.QuoteMeta(`SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND LOWER(TABLE_NAME) = LOWER(?)
ORDER BY ORDINAL_POSITION`)
	rows := sqlmock.NewRows([]string{
		"COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLLATION_NAME",
	})
	for _, column := range table.columns {
		var collation any
		if column.collation != "" {
			collation = column.collation
		}
		if column.name == nonBinaryColumn {
			collation = "utf8mb4_unicode_ci"
		}
		rows.AddRow(column.name, column.columnType, "NO", collation)
	}
	mock.ExpectQuery(query).
		WithArgs(table.tableName).
		WillReturnRows(rows)
}

func expectPackGenerationConnectorBindingForeignKey(
	mock sqlmock.Sqlmock,
	required packGenerationConnectorBindingForeignKeyShape,
	constraintName string,
	referencedSchema string,
	currentSchema string,
	deleteRule string,
	updateRule string,
) {
	query := regexp.QuoteMeta(`SELECT
k.CONSTRAINT_NAME, 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`)
	mock.ExpectQuery(query).
		WithArgs(required.tableName).
		WillReturnRows(sqlmock.NewRows([]string{
			"CONSTRAINT_NAME",
			"COLUMN_NAME",
			"REFERENCED_TABLE_SCHEMA",
			"DATABASE()",
			"REFERENCED_TABLE_NAME",
			"REFERENCED_COLUMN_NAME",
			"DELETE_RULE",
			"UPDATE_RULE",
		}).AddRow(
			constraintName,
			required.columnName,
			referencedSchema,
			currentSchema,
			required.referencedTable,
			required.referencedColumn,
			deleteRule,
			updateRule,
		))
}