package credentialgeneration

import (
	"errors"
	"regexp"
	"strings"
	"testing"

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

type dispatchGateSchemaFixture struct {
	columns     []credentialGenerationColumnFixture
	indexes     []credentialGenerationIndexFixture
	foreignKeys []dispatchGateForeignKeyFixture
}

type dispatchGateForeignKeyFixture struct {
	constraint, column                string
	referencedSchema, referencedTable string
	referencedColumn, currentDatabase string
	updateRule, deleteRule            string
}

func TestVerifyDispatchGateSchemaAcceptsExactCatalogShape(t *testing.T) {
	for _, bigintShape := range []string{"bigint", "bigint(20)"} {
		t.Run(bigintShape, func(t *testing.T) {
			db, mock := newCredentialGenerationSchemaMock(t)
			fixture := exactDispatchGateSchemaFixture()
			fixture.columns[2].columnType = bigintShape
			expectDispatchGateSchema(mock, fixture, 3)
			if err := verifyDispatchGateSchema(t.Context(), db); err != nil {
				t.Fatalf("verifyDispatchGateSchema: %v", err)
			}
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("sql expectations: %v", err)
			}
		})
	}
}

func TestVerifyDispatchGateSchemaRejectsColumnAndIndexDrift(t *testing.T) {
	tests := []struct {
		name   string
		phase  int
		mutate func(*dispatchGateSchemaFixture)
		want   string
	}{
		{name: "missing column", phase: 1, mutate: func(f *dispatchGateSchemaFixture) {
			f.columns = f.columns[:len(f.columns)-1]
		}, want: "missing column LastModifiedDate"},
		{name: "extra column", phase: 1, mutate: func(f *dispatchGateSchemaFixture) {
			f.columns = append(f.columns, credentialGenerationColumnFixture{expectedCredentialGenerationColumn: expectedCredentialGenerationColumn{name: "OpenPermitCount", columnType: "bigint"}})
		}, want: "unexpected column OpenPermitCount"},
		{name: "nonbinary ID", phase: 1, mutate: func(f *dispatchGateSchemaFixture) {
			f.columns[0].collation = "utf8mb4_general_ci"
		}, want: "CredentialGenerationId is type=varchar(255)"},
		{name: "case-folded mode", phase: 1, mutate: func(f *dispatchGateSchemaFixture) {
			f.columns[1].collation = "utf8mb4_general_ci"
		}, want: "Mode is type=varchar(32)"},
		{name: "wrong mode default", phase: 1, mutate: func(f *dispatchGateSchemaFixture) {
			f.columns[1].columnDefault = "revoked"
		}, want: "Mode is type=varchar(32)"},
		{name: "unsigned fence", phase: 1, mutate: func(f *dispatchGateSchemaFixture) {
			f.columns[2].columnType = "bigint(20) unsigned"
		}, want: "FenceToken is type=bigint(20) unsigned"},
		{name: "on update timestamp", phase: 1, mutate: func(f *dispatchGateSchemaFixture) {
			f.columns[4].extra = "DEFAULT_GENERATED on update CURRENT_TIMESTAMP(6)"
		}, want: "LastModifiedDate is type=datetime(6)"},
		{name: "missing primary", phase: 2, mutate: func(f *dispatchGateSchemaFixture) {
			f.indexes = nil
		}, want: "index PRIMARY"},
		{name: "composite primary", phase: 2, mutate: func(f *dispatchGateSchemaFixture) {
			f.indexes[0].columns = append(f.indexes[0].columns, "Mode")
		}, want: "index PRIMARY"},
		{name: "secondary index", phase: 2, mutate: func(f *dispatchGateSchemaFixture) {
			f.indexes = append(f.indexes, credentialGenerationIndexFixture{name: "idx_gate_mode", columns: []string{"Mode"}})
		}, want: "unexpected secondary index idx_gate_mode"},
	}

	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			db, mock := newCredentialGenerationSchemaMock(t)
			fixture := exactDispatchGateSchemaFixture()
			test.mutate(&fixture)
			expectDispatchGateColumns(mock, fixture.columns)
			if test.phase >= 2 {
				expectDispatchGateIndexes(mock, fixture.indexes)
			}
			err := verifyDispatchGateSchema(t.Context(), db)
			requireCredentialGenerationSchemaUnavailable(t, err, test.want)
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("sql expectations: %v", err)
			}
		})
	}
}

func TestVerifyDispatchGateSchemaRequiresExactSameDatabaseRestrictiveForeignKey(t *testing.T) {
	tests := []struct {
		name   string
		mutate func(*dispatchGateSchemaFixture)
		want   string
	}{
		{name: "missing", mutate: func(f *dispatchGateSchemaFixture) { f.foreignKeys = nil }, want: "foreign key count = 0"},
		{name: "same named parent in another database", mutate: func(f *dispatchGateSchemaFixture) {
			f.foreignKeys[0].referencedSchema = "other_database"
		}, want: "current=nexuscrm_test"},
		{name: "wrong parent", mutate: func(f *dispatchGateSchemaFixture) {
			f.foreignKeys[0].referencedTable = "_System_NamedCredential"
		}, want: "same-database exact generation"},
		{name: "wrong parent column", mutate: func(f *dispatchGateSchemaFixture) {
			f.foreignKeys[0].referencedColumn = "DeveloperName"
		}, want: "same-database exact generation"},
		{name: "cascade delete", mutate: func(f *dispatchGateSchemaFixture) {
			f.foreignKeys[0].deleteRule = "CASCADE"
		}, want: "delete=CASCADE"},
		{name: "cascade update", mutate: func(f *dispatchGateSchemaFixture) {
			f.foreignKeys[0].updateRule = "CASCADE"
		}, want: "update=CASCADE"},
		{name: "additional foreign key", mutate: func(f *dispatchGateSchemaFixture) {
			f.foreignKeys = append(f.foreignKeys, f.foreignKeys[0])
			f.foreignKeys[1].constraint = "fk_second"
		}, want: "unexpected additional foreign key fk_second"},
	}

	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			db, mock := newCredentialGenerationSchemaMock(t)
			fixture := exactDispatchGateSchemaFixture()
			test.mutate(&fixture)
			expectDispatchGateSchema(mock, fixture, 3)
			err := verifyDispatchGateSchema(t.Context(), db)
			requireCredentialGenerationSchemaUnavailable(t, err, test.want)
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("sql expectations: %v", err)
			}
		})
	}
}

func TestVerifyDispatchGateSchemaWrapsInspectionFailures(t *testing.T) {
	for _, test := range []struct {
		name  string
		step  int
		query string
	}{
		{name: "columns", step: 1, query: inspectDispatchGateColumnsSQL},
		{name: "indexes", step: 2, query: inspectDispatchGateIndexesSQL},
		{name: "foreign key", step: 3, query: inspectDispatchGateForeignKeysSQL},
	} {
		t.Run(test.name, func(t *testing.T) {
			db, mock := newCredentialGenerationSchemaMock(t)
			fixture := exactDispatchGateSchemaFixture()
			if test.step > 1 {
				expectDispatchGateColumns(mock, fixture.columns)
			}
			if test.step > 2 {
				expectDispatchGateIndexes(mock, fixture.indexes)
			}
			mock.ExpectQuery(regexp.QuoteMeta(test.query)).WithArgs(dispatchGateTable).WillReturnError(errors.New("inspection unavailable"))
			err := verifyDispatchGateSchema(t.Context(), db)
			requireCredentialGenerationSchemaUnavailable(t, err, "inspection unavailable")
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("sql expectations: %v", err)
			}
		})
	}
}

func TestDispatchGateSchemaInspectionIsDatabaseScopedAndReadOnly(t *testing.T) {
	for name, query := range map[string]string{
		"columns":     inspectDispatchGateColumnsSQL,
		"indexes":     inspectDispatchGateIndexesSQL,
		"foreign key": inspectDispatchGateForeignKeysSQL,
	} {
		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 key" && !strings.Contains(upper, "REFERENCED_TABLE_SCHEMA") {
				t.Fatalf("foreign-key query does not inspect referenced schema: %s", 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 exactDispatchGateSchemaFixture() dispatchGateSchemaFixture {
	return dispatchGateSchemaFixture{
		columns: credentialGenerationColumnFixtures(dispatchGateColumns),
		indexes: credentialGenerationIndexFixtures(dispatchGateIndexes),
		foreignKeys: []dispatchGateForeignKeyFixture{{
			constraint:       "fk_connector_credential_generation_dispatch_gate",
			column:           "CredentialGenerationId",
			referencedSchema: "nexuscrm_test",
			referencedTable:  credentialGenerationTable,
			referencedColumn: "Id",
			currentDatabase:  "nexuscrm_test",
			updateRule:       "RESTRICT",
			deleteRule:       "RESTRICT",
		}},
	}
}

func expectDispatchGateSchema(mock sqlmock.Sqlmock, fixture dispatchGateSchemaFixture, through int) {
	if through >= 1 {
		expectDispatchGateColumns(mock, fixture.columns)
	}
	if through >= 2 {
		expectDispatchGateIndexes(mock, fixture.indexes)
	}
	if through >= 3 {
		expectDispatchGateForeignKeys(mock, fixture.foreignKeys)
	}
}

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

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

func expectDispatchGateForeignKeys(mock sqlmock.Sqlmock, foreignKeys []dispatchGateForeignKeyFixture) {
	rows := sqlmock.NewRows([]string{
		"CONSTRAINT_NAME", "COLUMN_NAME", "REFERENCED_TABLE_SCHEMA", "REFERENCED_TABLE_NAME",
		"REFERENCED_COLUMN_NAME", "DATABASE()", "UPDATE_RULE", "DELETE_RULE",
	})
	for _, foreignKey := range foreignKeys {
		rows.AddRow(
			foreignKey.constraint, foreignKey.column, foreignKey.referencedSchema,
			foreignKey.referencedTable, foreignKey.referencedColumn, foreignKey.currentDatabase,
			foreignKey.updateRule, foreignKey.deleteRule,
		)
	}
	mock.ExpectQuery(regexp.QuoteMeta(inspectDispatchGateForeignKeysSQL)).WithArgs(dispatchGateTable).WillReturnRows(rows)
}