package bootstrap

import (
	"context"
	"regexp"
	"testing"

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

var (
	listObjectsPattern = regexp.QuoteMeta(
		"SELECT `Id`, `ApiName` FROM `_System_Object` WHERE `IsDeleted` = 0",
	)
	canonicalRowExistsPattern = regexp.QuoteMeta(
		"SELECT `Id` FROM `_System_Object` WHERE `Id` = ? AND `IsDeleted` = 0 LIMIT 1",
	)
	updateFieldObjectIdPattern = regexp.QuoteMeta(
		"UPDATE `_System_Field` SET `ObjectId` = ? WHERE `ObjectId` = ?",
	)
	updateRecordTypeObjectIdPattern = regexp.QuoteMeta(
		"UPDATE `_System_RecordType` SET `ObjectId` = ? WHERE `ObjectId` = ?",
	)
	updateObjectIdPattern = regexp.QuoteMeta(
		"UPDATE `_System_Object` SET `Id` = ? WHERE `Id` = ?",
	)
	fkOff = regexp.QuoteMeta("SET FOREIGN_KEY_CHECKS=0")
	fkOn  = regexp.QuoteMeta("SET FOREIGN_KEY_CHECKS=1")
)

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

	canonical := schema.GenerateObjectID("Order")
	mock.ExpectQuery(listObjectsPattern).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ApiName"}).
			AddRow(canonical, "Order"))

	require.NoError(t, NormalizeObjectIds(context.Background(), db))
	require.NoError(t, mock.ExpectationsWereMet())
}

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

	canonical := schema.GenerateObjectID("Order") // canonical: obj_order_<hash>
	mock.ExpectQuery(listObjectsPattern).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ApiName"}).
			AddRow("disc_order", "order"))

	// Per-row: check canonical-collision, FK off, tx, two updates, parent update, commit, FK on.
	mock.ExpectQuery(canonicalRowExistsPattern).
		WithArgs(canonical).
		WillReturnRows(sqlmock.NewRows([]string{"Id"})) // no existing canonical
	mock.ExpectExec(fkOff).WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectBegin()
	mock.ExpectExec(updateFieldObjectIdPattern).
		WithArgs(canonical, "disc_order").
		WillReturnResult(sqlmock.NewResult(0, 53))
	mock.ExpectExec(updateRecordTypeObjectIdPattern).
		WithArgs(canonical, "disc_order").
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(updateObjectIdPattern).
		WithArgs(canonical, "disc_order").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectCommit()
	mock.ExpectExec(fkOn).WillReturnResult(sqlmock.NewResult(0, 0))

	require.NoError(t, NormalizeObjectIds(context.Background(), db))
	require.NoError(t, mock.ExpectationsWereMet())
}

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

	canonical := schema.GenerateObjectID("Order")
	mock.ExpectQuery(listObjectsPattern).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ApiName"}).
			AddRow("disc_order", "order"))

	// Canonical Id already exists for this ApiName → skip without writing.
	mock.ExpectQuery(canonicalRowExistsPattern).
		WithArgs(canonical).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow(canonical))

	require.NoError(t, NormalizeObjectIds(context.Background(), db))
	require.NoError(t, mock.ExpectationsWereMet())
}

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

	canonical := schema.GenerateObjectID("Order")
	mock.ExpectQuery(listObjectsPattern).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ApiName"}).
			// Pre-normalization Pascal-case Id from old GenerateObjectID.
			AddRow("obj_Order_4b4166815d3baec56c6503e76fc92ca2", "Order"))

	mock.ExpectQuery(canonicalRowExistsPattern).
		WithArgs(canonical).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectExec(fkOff).WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectBegin()
	mock.ExpectExec(updateFieldObjectIdPattern).
		WithArgs(canonical, "obj_Order_4b4166815d3baec56c6503e76fc92ca2").
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(updateRecordTypeObjectIdPattern).
		WithArgs(canonical, "obj_Order_4b4166815d3baec56c6503e76fc92ca2").
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(updateObjectIdPattern).
		WithArgs(canonical, "obj_Order_4b4166815d3baec56c6503e76fc92ca2").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectCommit()
	mock.ExpectExec(fkOn).WillReturnResult(sqlmock.NewResult(0, 0))

	require.NoError(t, NormalizeObjectIds(context.Background(), db))
	require.NoError(t, mock.ExpectationsWereMet())
}