package bootstrapimpl

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

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

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

	expectPhysicalColumns(mock,
		physicalColumnRow{tableName: "Lead", columnName: "ExternalRemoteId__c", columnType: "varchar(255)", nullable: "YES"},
	)
	expectSingleColumnUniqueIndexLookup(mock, "Lead", "ExternalRemoteId__c", 0)
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `Lead` ADD UNIQUE INDEX `uniq_Lead_ExternalRemoteId__c` (`ExternalRemoteId__c`)")).
		WillReturnResult(sqlmock.NewResult(0, 1))

	if err := ReconcileTableColumns(db, []schema.TableDefinition{{
		TableName: "Lead",
		Columns: []schema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
	}}); err != nil {
		t.Fatalf("ReconcileTableColumns: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

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

	expectPhysicalColumns(mock,
		physicalColumnRow{tableName: "Lead", columnName: "ExternalRemoteId__c", columnType: "varchar(255)", nullable: "YES"},
	)
	expectSingleColumnUniqueIndexLookup(mock, "Lead", "ExternalRemoteId__c", 1)

	if err := ReconcileTableColumns(db, []schema.TableDefinition{{
		TableName: "Lead",
		Columns: []schema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
	}}); err != nil {
		t.Fatalf("ReconcileTableColumns: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

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

	expectPhysicalColumns(mock,
		physicalColumnRow{tableName: "_System_Session", columnName: "Token", columnType: "varchar(750)", nullable: "NO"},
	)
	expectSingleColumnUniqueIndexLookup(mock, "_System_Session", "Token", 0)
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `_System_Session` ADD UNIQUE INDEX `uniq__System_Session_Token` (`Token`)")).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `_System_Session` ADD COLUMN `CurrentBearerToken` VARBINARY(3000)")).
		WillReturnResult(sqlmock.NewResult(0, 1))

	if err := ReconcileTableColumns(db, []schema.TableDefinition{{
		TableName: "_System_Session",
		Columns: []schema.ColumnDefinition{{
			Name:     "Token",
			Type:     "VARBINARY(3000)",
			Nullable: false,
			Unique:   true,
		}, {
			Name:     "CurrentBearerToken",
			Type:     "VARBINARY(3000)",
			Nullable: true,
		}},
	}}); err != nil {
		t.Fatalf("ReconcileTableColumns: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

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

	expectPhysicalColumns(mock,
		physicalColumnRow{tableName: "_System_Other", columnName: "Token", columnType: "varchar(750)", nullable: "NO"},
	)

	err = ReconcileTableColumns(db, []schema.TableDefinition{{
		TableName: "_System_Other",
		Columns: []schema.ColumnDefinition{{
			Name:     "UnrelatedMissingColumn",
			Type:     "VARCHAR(255)",
			Nullable: true,
		}, {
			Name:     "Token",
			Type:     "VARBINARY(3000)",
			Nullable: false,
		}},
	}})
	if err == nil || !strings.Contains(err.Error(), "explicit writer-quiesced migration") {
		t.Fatalf("ReconcileTableColumns error = %v, want writer-quiesced migration requirement", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

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

	expectPhysicalColumns(mock,
		physicalColumnRow{tableName: "_System_Session", columnName: "Token", columnType: "varchar(750)", nullable: "YES"},
	)
	mock.ExpectQuery(regexp.QuoteMeta("SELECT COUNT(*) FROM `_System_Session` WHERE `Token` IS NULL")).
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(0))
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `_System_Session` MODIFY COLUMN `Token` VARCHAR(750) NOT NULL")).
		WillReturnResult(sqlmock.NewResult(0, 0))

	if err := ReconcileTableColumns(db, []schema.TableDefinition{{
		TableName: "_System_Session",
		Columns: []schema.ColumnDefinition{{
			Name:     "Token",
			Type:     "VARBINARY(3000)",
			Nullable: false,
		}},
	}}); err != nil {
		t.Fatalf("ReconcileTableColumns: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

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

	expectPhysicalColumns(mock, physicalColumnRow{
		tableName:  "Task",
		columnName: "Status",
		columnType: "varchar(255)",
		nullable:   "YES",
		defaultVal: sql.NullString{String: "Not Started", Valid: true},
		collation:  sql.NullString{String: "utf8mb4_unicode_ci", Valid: true},
	})
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `Task` MODIFY COLUMN `Status` VARCHAR(255) DEFAULT 'Open'")).
		WillReturnResult(sqlmock.NewResult(0, 0))

	err = ReconcileTableColumns(db, []schema.TableDefinition{{
		TableName: "Task",
		Columns: []schema.ColumnDefinition{{
			Name:     "Status",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Default:  "'Open'",
		}},
	}})
	if err != nil {
		t.Fatalf("ReconcileTableColumns: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

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

	expectPhysicalColumns(mock,
		physicalColumnRow{tableName: "_System_Session", columnName: "Token", columnType: "varchar(750)", nullable: "YES"},
	)
	mock.ExpectQuery(regexp.QuoteMeta("SELECT COUNT(*) FROM `_System_Session` WHERE `Token` IS NULL")).
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(2))

	err = ReconcileTableColumns(db, []schema.TableDefinition{{
		TableName: "_System_Session",
		Columns: []schema.ColumnDefinition{{
			Name:     "Token",
			Type:     "VARBINARY(3000)",
			Nullable: false,
		}},
	}})
	if err == nil {
		t.Fatal("ReconcileTableColumns error = nil, want legacy Token NULL-row rejection")
	}
	for _, want := range []string{"_System_Session.Token", "2 existing NULL row(s)", "no default configured"} {
		if !strings.Contains(err.Error(), want) {
			t.Fatalf("ReconcileTableColumns error = %q, want %q", err, want)
		}
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

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

	expectPhysicalColumns(mock, physicalColumnRow{
		tableName:  "_System_ConnectorV2Receipt",
		columnName: "ReceiptProtocolVersion",
		columnType: "varchar(255)",
		nullable:   "NO",
		collation: sql.NullString{
			String: "utf8mb4_bin",
			Valid:  true,
		},
	})
	mock.ExpectQuery(regexp.QuoteMeta(
		"SELECT COALESCE(MAX(CHAR_LENGTH(`ReceiptProtocolVersion`)), 0) FROM `_System_ConnectorV2Receipt`",
	)).WillReturnRows(sqlmock.NewRows([]string{"max_length"}).AddRow(33))

	err = ReconcileTableColumns(db, []schema.TableDefinition{{
		TableName: "_System_ConnectorV2Receipt",
		Columns: []schema.ColumnDefinition{{
			Name:          "ReceiptProtocolVersion",
			Type:          "VARCHAR(32)",
			Nullable:      false,
			CollationName: "utf8mb4_bin",
		}},
	}})
	if err == nil {
		t.Fatal("ReconcileTableColumns error = nil, want oversize narrowing rejection")
	}
	for _, want := range []string{
		"cannot narrow _System_ConnectorV2Receipt.ReceiptProtocolVersion",
		"max data length is 33 chars",
		"exceeds target 32",
	} {
		if !strings.Contains(err.Error(), want) {
			t.Fatalf("ReconcileTableColumns error = %q, want %q", err, want)
		}
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

func TestVarcharToVarbinaryPreflightOnlyAllowsExactLegacySessionTokenRepresentation(t *testing.T) {
	tests := []struct {
		name         string
		tableName    string
		columnName   string
		existingType string
		desiredType  string
		wantErr      bool
	}{
		{
			name:         "exact legacy session token",
			tableName:    "_System_Session",
			columnName:   "Token",
			existingType: "varchar(750)",
			desiredType:  "VARBINARY(3000)",
		},
		{
			name:         "unrelated table",
			tableName:    "_System_Other",
			columnName:   "Token",
			existingType: "varchar(750)",
			desiredType:  "VARBINARY(3000)",
			wantErr:      true,
		},
		{
			name:         "unrelated column",
			tableName:    "_System_Session",
			columnName:   "OtherToken",
			existingType: "varchar(750)",
			desiredType:  "VARBINARY(3000)",
			wantErr:      true,
		},
		{
			name:         "different source width",
			tableName:    "_System_Session",
			columnName:   "Token",
			existingType: "varchar(749)",
			desiredType:  "VARBINARY(3000)",
			wantErr:      true,
		},
		{
			name:         "different canonical target width",
			tableName:    "_System_Session",
			columnName:   "Token",
			existingType: "varchar(750)",
			desiredType:  "VARBINARY(3001)",
			wantErr:      true,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			existing := map[string]map[string]PhysicalColumnShape{
				strings.ToLower(tt.tableName): {
					strings.ToLower(tt.columnName): {
						ColumnType: tt.existingType,
						Nullable:   false,
					},
				},
			}
			defs := []schema.TableDefinition{{
				TableName: tt.tableName,
				Columns: []schema.ColumnDefinition{{
					Name:     tt.columnName,
					Type:     tt.desiredType,
					Nullable: false,
				}},
			}}
			err := preflightUnsupportedVarcharToVarbinaryChanges(existing, defs)
			if tt.wantErr && err == nil {
				t.Fatal("preflight error = nil, want fail-closed rejection")
			}
			if !tt.wantErr && err != nil {
				t.Fatalf("preflight error = %v, want exact compatibility", err)
			}
		})
	}
}

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

	expectPhysicalColumns(mock,
		physicalColumnRow{
			tableName:  "_System_RecordEventOutbox",
			columnName: "LastModifiedDate",
			columnType: "datetime",
			nullable:   "NO",
			defaultVal: sql.NullString{String: "CURRENT_TIMESTAMP", Valid: true},
		},
	)
	mock.ExpectQuery(regexp.QuoteMeta("SELECT COUNT(*) FROM `_System_RecordEventOutbox` WHERE `LastModifiedDate` IS NULL")).
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `_System_RecordEventOutbox` MODIFY COLUMN `LastModifiedDate` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)")).
		WillReturnResult(sqlmock.NewResult(0, 0))

	if err := ReconcileTableColumns(db, []schema.TableDefinition{{
		TableName: "_System_RecordEventOutbox",
		Columns: []schema.ColumnDefinition{{
			Name:     "LastModifiedDate",
			Type:     "DATETIME(6)",
			Nullable: false,
			Default:  "CURRENT_TIMESTAMP(6)",
		}},
	}}); err != nil {
		t.Fatalf("ReconcileTableColumns: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

func TestVarcharToVarbinaryCapacityCheckDoesNotAuthorizeOnlineChange(t *testing.T) {
	for _, desired := range []string{"varbinary(2999)", "varbinary(3000)"} {
		if isAutoColumnTypeChangeSupported("varchar(750)", desired) {
			t.Fatalf("varchar(750) -> %s admitted as a generic online change", desired)
		}
	}
	if isSafeVarcharToVarbinaryChange("varchar(750)", "varbinary(2999)") {
		t.Fatal("varchar(750) -> varbinary(2999) classified as byte-capacity-preserving")
	}
	if !isSafeVarcharToVarbinaryChange("varchar(750)", "varbinary(3000)") {
		t.Fatal("varchar(750) -> varbinary(3000) did not preserve full utf8mb4 byte capacity")
	}
}

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

	expectPhysicalColumns(mock,
		physicalColumnRow{tableName: "Lead", columnName: "Id", columnType: "varchar(36)", nullable: "NO"},
	)
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `Lead` ADD COLUMN `ExternalRemoteId__c` VARCHAR(255)")).
		WillReturnResult(sqlmock.NewResult(0, 1))
	expectSingleColumnUniqueIndexLookup(mock, "Lead", "ExternalRemoteId__c", 0)
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `Lead` ADD UNIQUE INDEX `uniq_Lead_ExternalRemoteId__c` (`ExternalRemoteId__c`)")).
		WillReturnResult(sqlmock.NewResult(0, 1))

	if err := ReconcileTableColumns(db, []schema.TableDefinition{{
		TableName: "Lead",
		Columns: []schema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
	}}); err != nil {
		t.Fatalf("ReconcileTableColumns: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

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

	expectPhysicalColumns(mock,
		physicalColumnRow{tableName: "Lead", columnName: "Id", columnType: "varchar(36)", nullable: "NO"},
	)
	mock.ExpectQuery(regexp.QuoteMeta("SELECT COUNT(*) FROM `Lead`")).
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(2))

	err = ReconcileTableColumns(db, []schema.TableDefinition{{
		TableName: "Lead",
		Columns: []schema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
			Default:  "'legacy'",
		}},
	}})
	if err == nil {
		t.Fatal("ReconcileTableColumns err = nil, want unsafe unique default add failure")
	}
	if !strings.Contains(err.Error(), "cannot add unique column Lead.ExternalRemoteId__c with default 'legacy'") {
		t.Fatalf("ReconcileTableColumns err = %v, want unique default preflight detail", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

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

	expectPhysicalColumns(mock, physicalColumnRow{
		tableName:  "_System_PageLayout",
		columnName: "Config",
		columnType: "json",
		nullable:   "NO",
	})
	mock.ExpectQuery(regexp.QuoteMeta("SELECT COALESCE(MAX(OCTET_LENGTH(`Config`)), 0) FROM `_System_PageLayout`")).
		WillReturnRows(sqlmock.NewRows([]string{"max_bytes"}).AddRow(2_097_152))
	mock.ExpectQuery(regexp.QuoteMeta("SELECT COUNT(*) FROM `_System_PageLayout` WHERE `Config` IS NULL")).
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(0))
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `_System_PageLayout` MODIFY COLUMN `Config` MEDIUMTEXT NOT NULL")).
		WillReturnResult(sqlmock.NewResult(0, 0))

	err = ReconcileTableColumns(db, []schema.TableDefinition{{
		TableName: "_System_PageLayout",
		Columns: []schema.ColumnDefinition{{
			Name:     "Config",
			Type:     "MEDIUMTEXT",
			Nullable: false,
		}},
	}})
	if err != nil {
		t.Fatalf("ReconcileTableColumns: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

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

	expectPhysicalColumns(mock, physicalColumnRow{
		tableName:  "_System_PageLayout",
		columnName: "Config",
		columnType: "json",
		nullable:   "NO",
	})
	mock.ExpectQuery(regexp.QuoteMeta("SELECT COALESCE(MAX(OCTET_LENGTH(`Config`)), 0) FROM `_System_PageLayout`")).
		WillReturnRows(sqlmock.NewRows([]string{"max_bytes"}).AddRow(16_777_216))

	err = ReconcileTableColumns(db, []schema.TableDefinition{{
		TableName: "_System_PageLayout",
		Columns: []schema.ColumnDefinition{{
			Name:     "Config",
			Type:     "MEDIUMTEXT",
			Nullable: false,
		}},
	}})
	if err == nil || !strings.Contains(err.Error(), "largest serialized JSON value is 16777216 bytes") {
		t.Fatalf("ReconcileTableColumns error = %v, want retained-value capacity rejection", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

type physicalColumnRow struct {
	tableName  string
	columnName string
	columnType string
	nullable   string
	defaultVal sql.NullString
	collation  sql.NullString
}

func expectPhysicalColumns(mock sqlmock.Sqlmock, columns ...physicalColumnRow) {
	rows := sqlmock.NewRows([]string{"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME"})
	for _, col := range columns {
		rows.AddRow(col.tableName, col.columnName, col.columnType, col.nullable, col.defaultVal, col.collation)
	}
	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 expectSingleColumnUniqueIndexLookup(mock sqlmock.Sqlmock, tableName, columnName string, count int) {
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM INFORMATION_SCHEMA\\.STATISTICS s").
		WithArgs(tableName, columnName).
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(count))
}