package bootstrap

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

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/backend/internal/infrastructure/database"
	"github.com/stretchr/testify/require"
)

func TestShouldRunBootstrapPhase(t *testing.T) {
	require.True(t, shouldRunBootstrapPhase("", "phase1_batch_tables"))
	require.True(t, shouldRunBootstrapPhase("phase1_batch_tables", "phase1_batch_tables"))
	require.True(t, shouldRunBootstrapPhase("phase1_batch_tables", "phase2_object_metadata"))
	require.False(t, shouldRunBootstrapPhase("phase2_object_metadata", "phase1_batch_tables"))
	// Unknown phase values fail open to avoid deadlock on bad metadata.
	require.True(t, shouldRunBootstrapPhase("unknown", "phase1_batch_tables"))
}

func TestIsDuplicateIndexNameError(t *testing.T) {
	require.False(t, isDuplicateIndexNameError(sql.ErrNoRows))
	require.True(t, isDuplicateIndexNameError(assertError("Error 1061 (42000): Duplicate key name 'uq_idx'")))
	require.True(t, isDuplicateIndexNameError(assertError("Error 1061 (42000): index already exist uq_idx")))
	require.True(t, isDuplicateIndexNameError(assertError("some index already exist in schema")))
	require.False(t, isDuplicateIndexNameError(assertError("permission denied")))
}

func TestDropIndexIfExists_DropsWhenPresent(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	mock.ExpectExec("ALTER TABLE `_System_File` DROP INDEX `uk__System_File_compat_projection`").
		WillReturnResult(sqlmock.NewResult(0, 0))

	require.NoError(t, dropIndexIfExists(db, "_System_File", "uk__System_File_compat_projection"))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestDropIndexIfExists_IgnoresMissingIndex(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	mock.ExpectExec("ALTER TABLE `_System_File` DROP INDEX `uk__System_File_compat_projection`").
		WillReturnError(assertError("Error 1091 (42000): index uk__system_file_compat_projection doesn't exist"))

	require.NoError(t, dropIndexIfExists(db, "_System_File", "uk__System_File_compat_projection"))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestDropObsoleteFileServiceIndexes_DropsKnownLegacyIndexes(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	for _, stmt := range []string{
		"ALTER TABLE `_System_File` DROP INDEX `uk__System_File_compat_projection`",
		"ALTER TABLE `_System_File` DROP INDEX `idx__System_File_parent_active`",
		"ALTER TABLE `_System_File` DROP INDEX `idx__System_File_parent_kind`",
		"ALTER TABLE `_System_File` DROP INDEX `idx__System_File_deleted`",
		"ALTER TABLE `_System_FileUploadOperation` DROP INDEX `idx__System_FileUploadOperation_state_expires`",
		"ALTER TABLE `_System_FileUploadOperation` DROP INDEX `idx__System_FileUploadOperation_parent`",
		"ALTER TABLE `_System_FileUploadOperation` DROP INDEX `idx__System_FileUploadOperation_system_file`",
	} {
		mock.ExpectExec(stmt).WillReturnResult(sqlmock.NewResult(0, 0))
	}

	require.NoError(t, dropObsoleteFileServiceIndexes(db, map[string]bool{
		strings.ToLower("_System_File"):                true,
		strings.ToLower("_System_FileUploadOperation"): true,
	}))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestEnsureActivityLookupIndexes_IgnoresExistingIndexes(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	for _, item := range []struct {
		tableName string
		stmt      string
	}{
		{tableName: "task", stmt: "CREATE INDEX `idx_task_whatid` ON `task` \\(`WhatId`\\)"},
		{tableName: "task", stmt: "CREATE INDEX `idx_task_whoid` ON `task` \\(`WhoId`\\)"},
		{tableName: "event", stmt: "CREATE INDEX `idx_event_whatid` ON `event` \\(`WhatId`\\)"},
		{tableName: "event", stmt: "CREATE INDEX `idx_event_whoid` ON `event` \\(`WhoId`\\)"},
	} {
		mock.ExpectQuery("SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE\\(\\) AND TABLE_NAME = \\?").
			WithArgs(item.tableName).
			WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME"}).AddRow(item.tableName))
		mock.ExpectExec(item.stmt).WillReturnError(assertError("Error 1061 (42000): index already exist"))
	}

	require.NoError(t, ensureActivityLookupIndexes(db))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestEnsureBootstrapStateTable(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	mock.ExpectExec("CREATE TABLE IF NOT EXISTS _System_BootstrapState").
		WillReturnResult(sqlmock.NewResult(0, 0))

	require.NoError(t, ensureBootstrapStateTable(db))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestPersistAndLoadBootstrapState(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	mock.ExpectExec("INSERT INTO `_System_BootstrapState`").
		WillReturnResult(sqlmock.NewResult(1, 1))
	require.NoError(t, persistBootstrapState(db, "run-1", "phase1_batch_tables", "running", nil))

	rows := sqlmock.NewRows([]string{"RunID", "Phase", "Status"}).
		AddRow("run-1", "phase1_batch_tables", "running")
	mock.ExpectQuery("SELECT `RunID`, `Phase`, `Status` FROM `_System_BootstrapState` WHERE `StateKey` = \\?").
		WithArgs(bootstrapStateKey).
		WillReturnRows(rows)

	state, err := loadBootstrapState(db)
	require.NoError(t, err)
	require.NotNil(t, state)
	require.Equal(t, "run-1", state.RunID)
	require.Equal(t, "phase1_batch_tables", state.Phase)
	require.Equal(t, "running", state.Status)

	require.NoError(t, mock.ExpectationsWereMet())
}

func TestLoadBootstrapState_NoRows(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	mock.ExpectQuery("SELECT `RunID`, `Phase`, `Status` FROM `_System_BootstrapState` WHERE `StateKey` = \\?").
		WithArgs(bootstrapStateKey).
		WillReturnError(sql.ErrNoRows)

	state, err := loadBootstrapState(db)
	require.NoError(t, err)
	require.Nil(t, state)
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestRunBootstrapMutationUnderLock(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	rows := sqlmock.NewRows([]string{"GET_LOCK('nexuscrm_bootstrap_mutation', 600)"}).AddRow(1)
	mock.ExpectQuery("SELECT GET_LOCK\\('nexuscrm_bootstrap_mutation', 600\\)").
		WillReturnRows(rows)
	mock.ExpectExec("SELECT RELEASE_LOCK\\('nexuscrm_bootstrap_mutation'\\)").
		WillReturnResult(sqlmock.NewResult(0, 0))

	called := false
	err = runBootstrapMutationUnderLock(db, func() error {
		called = true
		return nil
	})
	require.NoError(t, err)
	require.True(t, called)
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestVerifySchemaReadiness_Healthy(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	expectedRows := sqlmock.NewRows([]string{"TableName"}).AddRow("Account")
	mock.ExpectQuery("FROM `_System_Table`").
		WithArgs(1).
		WillReturnRows(expectedRows)

	actualRows := sqlmock.NewRows([]string{"TABLE_NAME"}).AddRow("Account")
	mock.ExpectQuery("FROM INFORMATION_SCHEMA.TABLES").
		WillReturnRows(actualRows)
	mock.ExpectQuery("FROM INFORMATION_SCHEMA.COLUMNS").
		WithArgs("_System_Object", "IsCustomized").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	mock.ExpectQuery("FROM INFORMATION_SCHEMA.COLUMNS").
		WithArgs("_System_Object", "AccessPolicy").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))

	conn := database.NewConnectionFromDB(db)
	require.NoError(t, VerifySchemaReadiness(conn))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestVerifySchemaReadiness_Unhealthy(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	expectedRows := sqlmock.NewRows([]string{"TableName"}).AddRow("Account")
	mock.ExpectQuery("FROM `_System_Table`").
		WithArgs(1).
		WillReturnRows(expectedRows)

	actualRows := sqlmock.NewRows([]string{"TABLE_NAME"}).AddRow("Contact")
	mock.ExpectQuery("FROM INFORMATION_SCHEMA.TABLES").
		WillReturnRows(actualRows)

	conn := database.NewConnectionFromDB(db)
	err = VerifySchemaReadiness(conn)
	require.Error(t, err, "VerifySchemaReadiness should return error when schema registry is unhealthy")
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestVerifySchemaReadiness_MissingRequiredColumn(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	expectedRows := sqlmock.NewRows([]string{"TableName"}).AddRow("Account")
	mock.ExpectQuery("FROM `_System_Table`").
		WithArgs(1).
		WillReturnRows(expectedRows)

	actualRows := sqlmock.NewRows([]string{"TABLE_NAME"}).AddRow("Account")
	mock.ExpectQuery("FROM INFORMATION_SCHEMA.TABLES").
		WillReturnRows(actualRows)
	mock.ExpectQuery("FROM INFORMATION_SCHEMA.COLUMNS").
		WithArgs("_System_Object", "IsCustomized").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(0))
	mock.ExpectQuery("FROM INFORMATION_SCHEMA.COLUMNS").
		WithArgs("_System_Object", "AccessPolicy").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))

	conn := database.NewConnectionFromDB(db)
	err = VerifySchemaReadiness(conn)
	require.Error(t, err, "VerifySchemaReadiness should return error when critical column is missing")
	require.NoError(t, mock.ExpectationsWereMet())
}

type assertError string

func (e assertError) Error() string { return string(e) }