package credentialgeneration

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

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

type credentialGenerationSchemaFixture struct {
	columns     []credentialGenerationColumnFixture
	indexes     []credentialGenerationIndexFixture
	foreignKeys []credentialGenerationForeignKeyFixture
}

type credentialGenerationColumnFixture struct {
	expectedCredentialGenerationColumn
	extra string
}

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

type credentialGenerationForeignKeyFixture struct {
	constraint       string
	column           string
	referencedSchema string
	referencedTable  string
	referencedColumn string
}

func TestVerifyCredentialGenerationSchemaAcceptsExactCatalogShape(t *testing.T) {
	db, mock := newCredentialGenerationSchemaMock(t)
	expectCredentialGenerationSchema(mock, exactCredentialGenerationSchemaFixture(), 3)

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

func TestVerifyCredentialGenerationSchemaRejectsColumnDrift(t *testing.T) {
	tests := []struct {
		name   string
		mutate func(*credentialGenerationSchemaFixture)
		want   string
	}{
		{
			name: "missing column",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.columns = fixture.columns[:len(fixture.columns)-1]
			},
			want: "missing column LastModifiedDate",
		},
		{
			name: "extra column",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.columns = append(fixture.columns, credentialGenerationColumnFixture{
					expectedCredentialGenerationColumn: expectedCredentialGenerationColumn{
						name: "CurrentGeneration", columnType: "varchar(255)", collation: "utf8mb4_bin",
					},
				})
			},
			want: "unexpected column CurrentGeneration",
		},
		{
			name: "wrong type",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.columns[6].columnType = "longblob"
			},
			want: "TransportConfiguration is type=longblob",
		},
		{
			name: "nullable semantic field",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.columns[5].nullable = true
			},
			want: "SecretBackendVersionRef is type=varchar(255) nullable=YES",
		},
		{
			name: "non-binary identity",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.columns[0].collation = "utf8mb4_general_ci"
			},
			want: "Id is type=varchar(255)",
		},
		{
			name: "unexpected semantic default",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.columns[4].hasDefault = true
				fixture.columns[4].columnDefault = "current"
			},
			want: "SecretBackendProtocol is type=varchar(255)",
		},
		{
			name: "missing timestamp default",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.columns[len(fixture.columns)-2].hasDefault = false
			},
			want: "CreatedDate is type=datetime(6)",
		},
		{
			name: "wrong timestamp default",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.columns[len(fixture.columns)-1].columnDefault = "CURRENT_TIMESTAMP"
			},
			want: "LastModifiedDate is type=datetime(6)",
		},
		{
			name: "on update timestamp",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.columns[len(fixture.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 := newCredentialGenerationSchemaMock(t)
			fixture := exactCredentialGenerationSchemaFixture()
			test.mutate(&fixture)
			expectCredentialGenerationColumns(mock, fixture.columns)

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

func TestVerifyCredentialGenerationSchemaAcceptsTimestampDefaultGeneratedExtra(t *testing.T) {
	db, mock := newCredentialGenerationSchemaMock(t)
	fixture := exactCredentialGenerationSchemaFixture()
	fixture.columns[len(fixture.columns)-2].extra = "DEFAULT_GENERATED"
	fixture.columns[len(fixture.columns)-1].extra = "default_generated"
	expectCredentialGenerationSchema(mock, fixture, 3)

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

func TestVerifyCredentialGenerationSchemaRejectsRequiredIndexDrift(t *testing.T) {
	tests := []struct {
		name   string
		mutate func(*credentialGenerationSchemaFixture)
		want   string
	}{
		{
			name: "primary is nonunique",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.indexes[0].unique = false
			},
			want: "index PRIMARY",
		},
		{
			name: "identity index is unique",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.indexes[1].unique = true
			},
			want: "idx_connector_credential_generation_identity",
		},
		{
			name: "scope index is unique",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.indexes[2].unique = true
			},
			want: "idx_connector_credential_generation_scope",
		},
		{
			name: "missing required index",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.indexes = fixture.indexes[:2]
			},
			want: "idx_connector_credential_generation_scope",
		},
		{
			name: "wrong column order",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.indexes[1].columns[0], fixture.indexes[1].columns[1] =
					fixture.indexes[1].columns[1], fixture.indexes[1].columns[0]
			},
			want: "idx_connector_credential_generation_identity",
		},
		{
			name: "prefix index",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.indexes[2].prefixAt = 1
			},
			want: "unsafe sequence, column, uniqueness, or prefix",
		},
		{
			name: "unexpected unique index",
			mutate: func(fixture *credentialGenerationSchemaFixture) {
				fixture.indexes = append(fixture.indexes, credentialGenerationIndexFixture{
					name: "ux_connector_credential_current", unique: true, columns: []string{"StableCredentialIdentity"},
				})
			},
			want: "unexpected unique index ux_connector_credential_current",
		},
	}

	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			db, mock := newCredentialGenerationSchemaMock(t)
			fixture := exactCredentialGenerationSchemaFixture()
			test.mutate(&fixture)
			expectCredentialGenerationColumns(mock, fixture.columns)
			expectCredentialGenerationIndexes(mock, fixture.indexes)

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

func TestVerifyCredentialGenerationSchemaAllowsAdditionalNonuniqueIndex(t *testing.T) {
	db, mock := newCredentialGenerationSchemaMock(t)
	fixture := exactCredentialGenerationSchemaFixture()
	fixture.indexes = append(fixture.indexes, credentialGenerationIndexFixture{
		name: "idx_connector_credential_generation_created", columns: []string{"CreatedDate"},
	})
	expectCredentialGenerationSchema(mock, fixture, 3)

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

func TestVerifyCredentialGenerationSchemaRequiresZeroForeignKeys(t *testing.T) {
	db, mock := newCredentialGenerationSchemaMock(t)
	fixture := exactCredentialGenerationSchemaFixture()
	fixture.foreignKeys = []credentialGenerationForeignKeyFixture{{
		constraint:       "fk_connector_credential_mutable",
		column:           "StableCredentialIdentity",
		referencedSchema: "nexuscrm_other",
		referencedTable:  "_System_NamedCredential",
		referencedColumn: "Id",
	}}
	expectCredentialGenerationSchema(mock, fixture, 3)

	err := verifyCredentialGenerationSchema(t.Context(), db)
	requireCredentialGenerationSchemaUnavailable(t, err, "unexpected foreign key fk_connector_credential_mutable")
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyCredentialGenerationSchemaWrapsInspectionFailures(t *testing.T) {
	tests := []struct {
		name   string
		phase  int
		query  string
		detail string
	}{
		{name: "columns", phase: 1, query: inspectCredentialGenerationColumnsSQL, detail: "columns unavailable"},
		{name: "indexes", phase: 2, query: inspectCredentialGenerationIndexesSQL, detail: "indexes unavailable"},
		{name: "foreign keys", phase: 3, query: inspectCredentialGenerationForeignKeysSQL, detail: "foreign keys unavailable"},
	}

	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			db, mock := newCredentialGenerationSchemaMock(t)
			fixture := exactCredentialGenerationSchemaFixture()
			if test.phase > 1 {
				expectCredentialGenerationColumns(mock, fixture.columns)
			}
			if test.phase > 2 {
				expectCredentialGenerationIndexes(mock, fixture.indexes)
			}
			mock.ExpectQuery(regexp.QuoteMeta(test.query)).
				WithArgs(credentialGenerationTable).
				WillReturnError(errors.New(test.detail))

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

func TestVerifyCredentialGenerationSchemaRejectsMissingDependencies(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, _ := newCredentialGenerationSchemaMock(t)
				db = opened
			}
			var err error
			if test.ctx {
				err = verifyCredentialGenerationSchema(t.Context(), db)
			} else {
				err = verifyCredentialGenerationSchema(nil, db)
			}
			requireCredentialGenerationSchemaUnavailable(t, err, "database or context is unavailable")
		})
	}
}

func TestCredentialGenerationSchemaInspectionIsDatabaseScopedAndReadOnly(t *testing.T) {
	for name, query := range map[string]string{
		"columns":      inspectCredentialGenerationColumnsSQL,
		"indexes":      inspectCredentialGenerationIndexesSQL,
		"foreign keys": inspectCredentialGenerationForeignKeysSQL,
	} {
		t.Run(name, func(t *testing.T) {
			upper := strings.ToUpper(strings.TrimSpace(query))
			if !strings.HasPrefix(upper, "SELECT ") {
				t.Fatalf("inspection query is not read-only: %s", query)
			}
			if !strings.Contains(upper, "DATABASE()") {
				t.Fatalf("inspection query is not scoped to DATABASE(): %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 newCredentialGenerationSchemaMock(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 exactCredentialGenerationSchemaFixture() credentialGenerationSchemaFixture {
	return credentialGenerationSchemaFixture{
		columns: credentialGenerationColumnFixtures(credentialGenerationColumns),
		indexes: credentialGenerationIndexFixtures(credentialGenerationIndexes),
	}
}

func credentialGenerationColumnFixtures(columns []expectedCredentialGenerationColumn) []credentialGenerationColumnFixture {
	result := make([]credentialGenerationColumnFixture, len(columns))
	for i, column := range columns {
		result[i] = credentialGenerationColumnFixture{expectedCredentialGenerationColumn: column}
	}
	return result
}

func credentialGenerationIndexFixtures(indexes []expectedCredentialGenerationIndex) []credentialGenerationIndexFixture {
	result := make([]credentialGenerationIndexFixture, len(indexes))
	for i, index := range indexes {
		result[i] = credentialGenerationIndexFixture{
			name:    index.name,
			unique:  index.unique,
			columns: append([]string(nil), index.columns...),
		}
	}
	return result
}

func expectCredentialGenerationSchema(mock sqlmock.Sqlmock, fixture credentialGenerationSchemaFixture, through int) {
	if through >= 1 {
		expectCredentialGenerationColumns(mock, fixture.columns)
	}
	if through >= 2 {
		expectCredentialGenerationIndexes(mock, fixture.indexes)
	}
	if through >= 3 {
		expectCredentialGenerationForeignKeys(mock, fixture.foreignKeys)
	}
}

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

func expectCredentialGenerationIndexes(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 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(inspectCredentialGenerationIndexesSQL)).
		WithArgs(credentialGenerationTable).
		WillReturnRows(rows)
}

func expectCredentialGenerationForeignKeys(mock sqlmock.Sqlmock, foreignKeys []credentialGenerationForeignKeyFixture) {
	rows := sqlmock.NewRows([]string{
		"CONSTRAINT_NAME", "COLUMN_NAME", "REFERENCED_TABLE_SCHEMA", "REFERENCED_TABLE_NAME", "REFERENCED_COLUMN_NAME",
	})
	for _, foreignKey := range foreignKeys {
		rows.AddRow(
			foreignKey.constraint,
			foreignKey.column,
			foreignKey.referencedSchema,
			foreignKey.referencedTable,
			foreignKey.referencedColumn,
		)
	}
	mock.ExpectQuery(regexp.QuoteMeta(inspectCredentialGenerationForeignKeysSQL)).
		WithArgs(credentialGenerationTable).
		WillReturnRows(rows)
}

func requireCredentialGenerationSchemaUnavailable(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)
	}
}