package serviceconfiguration

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

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

const serviceConfigurationSchemaFixtureDatabase = "nexuscrm_service_configuration_test"

type serviceConfigurationSchemaFixture struct {
	tables []serviceConfigurationTableFixture
}

type serviceConfigurationTableFixture struct {
	name        string
	columns     []serviceConfigurationColumnFixture
	indexes     []serviceConfigurationIndexFixture
	foreignKeys []serviceConfigurationForeignKeyFixture
}

type serviceConfigurationColumnFixture struct {
	expectedServiceConfigurationColumn
	extra string
}

type serviceConfigurationIndexFixture struct {
	name      string
	unique    bool
	columns   []string
	prefixAt  int
	sequence0 int
}

type serviceConfigurationForeignKeyFixture struct {
	constraint       string
	column           string
	referencedSchema string
	referencedTable  string
	referencedColumn string
	currentSchema    string
	updateRule       string
	deleteRule       string
}

func TestVerifyServiceConfigurationSchemaAcceptsExactCatalogShape(t *testing.T) {
	db, mock := newServiceConfigurationSchemaMock(t)
	fixture := exactServiceConfigurationSchemaFixture()
	for tableIndex := range fixture.tables {
		for columnIndex := range fixture.tables[tableIndex].columns {
			column := &fixture.tables[tableIndex].columns[columnIndex]
			switch column.columnType {
			case "int":
				column.columnType = "int(11)"
			case "bigint":
				column.columnType = "bigint(20)"
			case "datetime(6)":
				column.extra = "default_generated"
			}
		}
	}
	expectServiceConfigurationSchema(mock, fixture)

	if err := verifyServiceConfigurationSchema(t.Context(), db); err != nil {
		t.Fatalf("verifyServiceConfigurationSchema: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyServiceConfigurationSchemaRejectsColumnDrift(t *testing.T) {
	tests := []struct {
		name       string
		tableIndex int
		mutate     func(*serviceConfigurationTableFixture)
		want       string
	}{
		{
			name:       "missing generation column",
			tableIndex: 0,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.columns = table.columns[:len(table.columns)-1]
			},
			want: "missing column LastModifiedDate",
		},
		{
			name:       "unexpected mutable binding column",
			tableIndex: 2,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.columns = append(table.columns, serviceConfigurationColumnFixture{
					expectedServiceConfigurationColumn: expectedServiceConfigurationColumn{
						name: "Enabled", columnType: "tinyint(1)",
					},
				})
			},
			want: "unexpected column Enabled",
		},
		{
			name:       "nullable canonical configuration",
			tableIndex: 2,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.columns[6].nullable = true
			},
			want: "CanonicalConfiguration is type=mediumblob nullable=YES",
		},
		{
			name:       "case folded credential reference",
			tableIndex: 3,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.columns[3].collation = "utf8mb4_general_ci"
			},
			want: "CredentialRef is type=varchar(255)",
		},
		{
			name:       "unsigned count",
			tableIndex: 0,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.columns[19].columnType = "bigint(20) unsigned"
			},
			want: "AccountCount is type=bigint(20) unsigned",
		},
		{
			name:       "implicit timestamp update",
			tableIndex: 1,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.columns[len(table.columns)-1].extra = "DEFAULT_GENERATED on update CURRENT_TIMESTAMP(6)"
			},
			want: "extra=\"DEFAULT_GENERATED on update CURRENT_TIMESTAMP(6)\"",
		},
	}

	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			db, mock := newServiceConfigurationSchemaMock(t)
			fixture := exactServiceConfigurationSchemaFixture()
			test.mutate(&fixture.tables[test.tableIndex])
			for index := 0; index <= test.tableIndex; index++ {
				expectServiceConfigurationColumns(mock, fixture.tables[index])
			}

			err := verifyServiceConfigurationSchema(t.Context(), db)
			requireServiceConfigurationSchemaUnavailable(t, err, test.want)
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("sql expectations: %v", err)
			}
		})
	}
}

func TestVerifyServiceConfigurationSchemaRejectsUniqueIndexDrift(t *testing.T) {
	tests := []struct {
		name       string
		tableIndex int
		mutate     func(*serviceConfigurationTableFixture)
		want       string
	}{
		{
			name:       "missing account identity uniqueness",
			tableIndex: 1,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.indexes = table.indexes[:2]
			},
			want: "ux_connector_service_configuration_account_identity",
		},
		{
			name:       "binding ordinal is nonunique",
			tableIndex: 2,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.indexes[1].unique = false
			},
			want: "ux_connector_service_configuration_binding_ordinal",
		},
		{
			name:       "credential reference prefix",
			tableIndex: 3,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.indexes[2].prefixAt = 2
			},
			want: "unsafe sequence, column, uniqueness, or prefix",
		},
		{
			name:       "unexpected generation selector uniqueness",
			tableIndex: 0,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.indexes = append(table.indexes, serviceConfigurationIndexFixture{
					name: "ux_connector_service_configuration_current", unique: true, columns: []string{"OrgId"},
				})
			},
			want: "unexpected unique index ux_connector_service_configuration_current",
		},
	}

	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			db, mock := newServiceConfigurationSchemaMock(t)
			fixture := exactServiceConfigurationSchemaFixture()
			test.mutate(&fixture.tables[test.tableIndex])
			for _, table := range fixture.tables {
				expectServiceConfigurationColumns(mock, table)
			}
			for index := 0; index <= test.tableIndex; index++ {
				expectServiceConfigurationIndexes(mock, fixture.tables[index])
			}

			err := verifyServiceConfigurationSchema(t.Context(), db)
			requireServiceConfigurationSchemaUnavailable(t, err, test.want)
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("sql expectations: %v", err)
			}
		})
	}
}

func TestVerifyServiceConfigurationSchemaAllowsAdditionalNonuniqueIndex(t *testing.T) {
	db, mock := newServiceConfigurationSchemaMock(t)
	fixture := exactServiceConfigurationSchemaFixture()
	fixture.tables[0].indexes = append(fixture.tables[0].indexes, serviceConfigurationIndexFixture{
		name: "idx_connector_service_configuration_created", columns: []string{"CreatedDate"},
	})
	expectServiceConfigurationSchema(mock, fixture)

	if err := verifyServiceConfigurationSchema(t.Context(), db); err != nil {
		t.Fatalf("verifyServiceConfigurationSchema: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyServiceConfigurationSchemaRequiresExactSameDatabaseRestrictiveForeignKeys(t *testing.T) {
	tests := []struct {
		name       string
		tableIndex int
		mutate     func(*serviceConfigurationTableFixture)
		want       string
	}{
		{
			name:       "generation has foreign key",
			tableIndex: 0,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.foreignKeys = append(table.foreignKeys, serviceConfigurationForeignKeyFixture{
					constraint:       "fk_generation_current",
					column:           "OrgId",
					referencedSchema: serviceConfigurationSchemaFixtureDatabase,
					referencedTable:  "_System_Organization",
					referencedColumn: "Id",
					currentSchema:    serviceConfigurationSchemaFixtureDatabase,
					updateRule:       "RESTRICT",
					deleteRule:       "RESTRICT",
				})
			},
			want: "unexpected foreign key fk_generation_current",
		},
		{
			name:       "missing account parent",
			tableIndex: 1,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.foreignKeys = nil
			},
			want: "missing foreign key on column ServiceConfigurationGenerationId",
		},
		{
			name:       "same named account parent in another database",
			tableIndex: 1,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.foreignKeys[0].referencedSchema = "other_database"
			},
			want: "current=nexuscrm_service_configuration_test",
		},
		{
			name:       "binding cascades delete",
			tableIndex: 2,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.foreignKeys[0].deleteRule = "CASCADE"
			},
			want: "delete=CASCADE",
		},
		{
			name:       "binding cascades update",
			tableIndex: 2,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.foreignKeys[0].updateRule = "CASCADE"
			},
			want: "update=CASCADE",
		},
		{
			name:       "selection credential points to mutable table",
			tableIndex: 3,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.foreignKeys[1].referencedTable = "_System_NamedCredential"
			},
			want: "want same-database _System_ConnectorCredentialGeneration(Id)",
		},
		{
			name:       "selection credential points to nonidentity column",
			tableIndex: 3,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.foreignKeys[1].referencedColumn = "StableCredentialIdentity"
			},
			want: "want same-database _System_ConnectorCredentialGeneration(Id)",
		},
		{
			name:       "selection has additional foreign key",
			tableIndex: 3,
			mutate: func(table *serviceConfigurationTableFixture) {
				table.foreignKeys = append(table.foreignKeys, serviceConfigurationForeignKeyFixture{
					constraint:       "fk_selection_current",
					column:           "CredentialRef",
					referencedSchema: serviceConfigurationSchemaFixtureDatabase,
					referencedTable:  "_System_NamedCredential",
					referencedColumn: "DeveloperName",
					currentSchema:    serviceConfigurationSchemaFixtureDatabase,
					updateRule:       "RESTRICT",
					deleteRule:       "RESTRICT",
				})
			},
			want: "unexpected foreign key fk_selection_current",
		},
	}

	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			db, mock := newServiceConfigurationSchemaMock(t)
			fixture := exactServiceConfigurationSchemaFixture()
			test.mutate(&fixture.tables[test.tableIndex])
			for _, table := range fixture.tables {
				expectServiceConfigurationColumns(mock, table)
			}
			for _, table := range fixture.tables {
				expectServiceConfigurationIndexes(mock, table)
			}
			for index := 0; index <= test.tableIndex; index++ {
				expectServiceConfigurationForeignKeys(mock, fixture.tables[index])
			}

			err := verifyServiceConfigurationSchema(t.Context(), db)
			requireServiceConfigurationSchemaUnavailable(t, err, test.want)
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("sql expectations: %v", err)
			}
		})
	}
}

func TestVerifyServiceConfigurationSchemaWrapsInspectionFailure(t *testing.T) {
	db, mock := newServiceConfigurationSchemaMock(t)
	mock.ExpectQuery(regexp.QuoteMeta(inspectServiceConfigurationColumnsSQL)).
		WithArgs(serviceConfigurationGenerationTable).
		WillReturnError(errors.New("information schema unavailable"))

	err := verifyServiceConfigurationSchema(t.Context(), db)
	requireServiceConfigurationSchemaUnavailable(t, err, "information schema unavailable")
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyServiceConfigurationSchemaRejectsMissingDependencies(t *testing.T) {
	for _, test := range []struct {
		name string
		ctx  bool
		db   bool
	}{
		{name: "nil context", db: true},
		{name: "nil database", ctx: true},
	} {
		t.Run(test.name, func(t *testing.T) {
			var db *sql.DB
			if test.db {
				opened, _ := newServiceConfigurationSchemaMock(t)
				db = opened
			}
			var err error
			if test.ctx {
				err = verifyServiceConfigurationSchema(t.Context(), db)
			} else {
				err = verifyServiceConfigurationSchema(nil, db)
			}
			requireServiceConfigurationSchemaUnavailable(t, err, "database or context is unavailable")
		})
	}
}

func TestServiceConfigurationSchemaInspectionIsDatabaseScopedAndReadOnly(t *testing.T) {
	for name, query := range map[string]string{
		"columns":      inspectServiceConfigurationColumnsSQL,
		"indexes":      inspectServiceConfigurationIndexesSQL,
		"foreign keys": inspectServiceConfigurationForeignKeysSQL,
	} {
		t.Run(name, func(t *testing.T) {
			upper := strings.ToUpper(strings.TrimSpace(query))
			if !strings.HasPrefix(upper, "SELECT ") || !strings.Contains(upper, "DATABASE()") {
				t.Fatalf("inspection query is not a database-scoped SELECT: %s", query)
			}
			if name == "foreign keys" {
				for _, required := range []string{"REFERENCED_TABLE_SCHEMA", "TABLE_SCHEMA = DATABASE()", "UPDATE_RULE", "DELETE_RULE"} {
					if !strings.Contains(upper, required) {
						t.Fatalf("foreign-key query does not inspect %s: %s", required, query)
					}
				}
			}
			for _, forbidden := range []string{"CREATE ", "ALTER ", "DROP ", "TRUNCATE ", "INSERT ", "UPDATE ", "DELETE "} {
				if strings.Contains(upper, forbidden) {
					t.Fatalf("inspection query contains %s: %s", strings.TrimSpace(forbidden), query)
				}
			}
		})
	}
}

func newServiceConfigurationSchemaMock(t *testing.T) (*sql.DB, sqlmock.Sqlmock) {
	t.Helper()
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })
	return db, mock
}

func exactServiceConfigurationSchemaFixture() serviceConfigurationSchemaFixture {
	fixture := serviceConfigurationSchemaFixture{
		tables: make([]serviceConfigurationTableFixture, len(serviceConfigurationSchemas)),
	}
	for tableIndex, table := range serviceConfigurationSchemas {
		copyTable := serviceConfigurationTableFixture{name: table.name}
		copyTable.columns = make([]serviceConfigurationColumnFixture, len(table.columns))
		for columnIndex, column := range table.columns {
			copyTable.columns[columnIndex] = serviceConfigurationColumnFixture{
				expectedServiceConfigurationColumn: column,
			}
		}
		copyTable.indexes = make([]serviceConfigurationIndexFixture, len(table.indexes))
		for indexIndex, index := range table.indexes {
			copyTable.indexes[indexIndex] = serviceConfigurationIndexFixture{
				name: index.name, unique: index.unique, columns: append([]string(nil), index.columns...),
			}
		}
		copyTable.foreignKeys = make([]serviceConfigurationForeignKeyFixture, len(table.foreignKeys))
		for foreignKeyIndex, foreignKey := range table.foreignKeys {
			copyTable.foreignKeys[foreignKeyIndex] = serviceConfigurationForeignKeyFixture{
				constraint:       "fk_service_configuration_" + foreignKey.column,
				column:           foreignKey.column,
				referencedSchema: serviceConfigurationSchemaFixtureDatabase,
				referencedTable:  foreignKey.referencedTable,
				referencedColumn: foreignKey.referencedColumn,
				currentSchema:    serviceConfigurationSchemaFixtureDatabase,
				updateRule:       "NO ACTION",
				deleteRule:       "RESTRICT",
			}
		}
		fixture.tables[tableIndex] = copyTable
	}
	return fixture
}

func expectServiceConfigurationSchema(mock sqlmock.Sqlmock, fixture serviceConfigurationSchemaFixture) {
	for _, table := range fixture.tables {
		expectServiceConfigurationColumns(mock, table)
	}
	for _, table := range fixture.tables {
		expectServiceConfigurationIndexes(mock, table)
	}
	for _, table := range fixture.tables {
		expectServiceConfigurationForeignKeys(mock, table)
	}
}

func expectServiceConfigurationColumns(mock sqlmock.Sqlmock, table serviceConfigurationTableFixture) {
	rows := sqlmock.NewRows([]string{
		"COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLLATION_NAME", "COLUMN_DEFAULT", "EXTRA",
	})
	for _, column := range table.columns {
		nullable := "NO"
		if column.nullable {
			nullable = "YES"
		}
		var collation, columnDefault any
		if column.collation != "" {
			collation = column.collation
		}
		if column.hasDefault {
			columnDefault = column.columnDefault
		}
		rows.AddRow(column.name, column.columnType, nullable, collation, columnDefault, column.extra)
	}
	mock.ExpectQuery(regexp.QuoteMeta(inspectServiceConfigurationColumnsSQL)).
		WithArgs(table.name).
		WillReturnRows(rows)
}

func expectServiceConfigurationIndexes(mock sqlmock.Sqlmock, table serviceConfigurationTableFixture) {
	rows := sqlmock.NewRows([]string{"INDEX_NAME", "NON_UNIQUE", "SEQ_IN_INDEX", "COLUMN_NAME", "SUB_PART"})
	for _, index := range table.indexes {
		nonUnique := 1
		if index.unique {
			nonUnique = 0
		}
		for columnIndex, column := range index.columns {
			var subPart any
			if index.prefixAt == columnIndex+1 {
				subPart = 16
			}
			rows.AddRow(index.name, nonUnique, index.sequence0+columnIndex+1, column, subPart)
		}
	}
	mock.ExpectQuery(regexp.QuoteMeta(inspectServiceConfigurationIndexesSQL)).
		WithArgs(table.name).
		WillReturnRows(rows)
}

func expectServiceConfigurationForeignKeys(mock sqlmock.Sqlmock, table serviceConfigurationTableFixture) {
	rows := sqlmock.NewRows([]string{
		"CONSTRAINT_NAME", "COLUMN_NAME", "REFERENCED_TABLE_SCHEMA",
		"REFERENCED_TABLE_NAME", "REFERENCED_COLUMN_NAME", "CURRENT_SCHEMA",
		"UPDATE_RULE", "DELETE_RULE",
	})
	for _, foreignKey := range table.foreignKeys {
		rows.AddRow(
			foreignKey.constraint,
			foreignKey.column,
			foreignKey.referencedSchema,
			foreignKey.referencedTable,
			foreignKey.referencedColumn,
			foreignKey.currentSchema,
			foreignKey.updateRule,
			foreignKey.deleteRule,
		)
	}
	mock.ExpectQuery(regexp.QuoteMeta(inspectServiceConfigurationForeignKeysSQL)).
		WithArgs(table.name).
		WillReturnRows(rows)
}

func requireServiceConfigurationSchemaUnavailable(t *testing.T, err error, detail string) {
	t.Helper()
	if !errors.Is(err, ErrSchemaUnavailable) {
		t.Fatalf("error = %v, want ErrSchemaUnavailable", err)
	}
	if !strings.Contains(err.Error(), detail) {
		t.Fatalf("error = %v, want detail %q", err, detail)
	}
}