package bootstrap

import (
	"context"
	"regexp"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
)

var (
	orphanListPattern = "SELECT DISTINCT f\\.`ObjectId` FROM `_System_Field` f " +
		"LEFT JOIN `_System_Object` o ON f\\.`ObjectId` = o\\.`Id` " +
		"WHERE f\\.`IsDeleted` = 0 AND o\\.`Id` IS NULL"
	canonicalLookupPattern = regexp.QuoteMeta(
		"SELECT `Id` FROM `_System_Object` WHERE LOWER(`ApiName`) = LOWER(?) AND `IsDeleted` = 0 LIMIT 1",
	)
	orphanFieldsByObjectPattern = regexp.QuoteMeta(
		"SELECT `Id`, `ApiName` FROM `_System_Field` WHERE `ObjectId` = ? AND `IsDeleted` = 0",
	)
	canonicalExistsPattern = regexp.QuoteMeta(
		"SELECT 1 FROM `_System_Field` WHERE `ObjectId` = ? AND `ApiName` = ? AND `IsDeleted` = 0 LIMIT 1",
	)
	updatePattern = regexp.QuoteMeta(
		"UPDATE `_System_Field` SET `ObjectId` = ? WHERE `Id` = ?",
	)
	deletePattern = regexp.QuoteMeta(
		"DELETE FROM `_System_Field` WHERE `Id` = ?",
	)
	deleteAllForObjectPattern = regexp.QuoteMeta(
		"DELETE FROM `_System_Field` WHERE `ObjectId` = ?",
	)
)

func TestRecoverAPINameFromOrphanID(t *testing.T) {
	cases := []struct {
		name string
		id   string
		want string
		ok   bool
	}{
		{"standard order", "obj_Order_4b4166815d3baec56c6503e76fc92ca2", "Order", true},
		{"compound apiName", "obj_OpportunityLineItem_4b4166815d3baec56c6503e76fc92ca2", "OpportunityLineItem", true},
		{"too short", "obj_X_abc", "", false},
		{"missing prefix", "disc_order", "", false},
		{"missing hash", "obj_Order", "", false},
		{"empty apiName", "obj__4b4166815d3baec56c6503e76fc92ca2", "", false},
	}
	for _, c := range cases {
		t.Run(c.name, func(t *testing.T) {
			got, ok := recoverAPINameFromOrphanID(c.id)
			assert.Equal(t, c.ok, ok)
			assert.Equal(t, c.want, got)
		})
	}
}

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

	mock.ExpectQuery(orphanListPattern).
		WillReturnRows(sqlmock.NewRows([]string{"ObjectId"}))

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

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

	orphanID := "obj_Order_4b4166815d3baec56c6503e76fc92ca2"
	canonicalID := "disc_order"

	mock.ExpectQuery(orphanListPattern).
		WillReturnRows(sqlmock.NewRows([]string{"ObjectId"}).AddRow(orphanID))
	mock.ExpectQuery(canonicalLookupPattern).
		WithArgs("Order").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow(canonicalID))
	mock.ExpectQuery(orphanFieldsByObjectPattern).
		WithArgs(orphanID).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ApiName"}).
			AddRow("fld_Order_StatusCode_xyz", "StatusCode"))
	// Canonical row already exists for (disc_order, StatusCode) → DELETE the orphan.
	mock.ExpectQuery(canonicalExistsPattern).
		WithArgs(canonicalID, "StatusCode").
		WillReturnRows(sqlmock.NewRows([]string{"1"}).AddRow(1))
	mock.ExpectExec(deletePattern).
		WithArgs("fld_Order_StatusCode_xyz").
		WillReturnResult(sqlmock.NewResult(0, 1))

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

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

	orphanID := "obj_Order_4b4166815d3baec56c6503e76fc92ca2"
	canonicalID := "disc_order"

	mock.ExpectQuery(orphanListPattern).
		WillReturnRows(sqlmock.NewRows([]string{"ObjectId"}).AddRow(orphanID))
	mock.ExpectQuery(canonicalLookupPattern).
		WithArgs("Order").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow(canonicalID))
	mock.ExpectQuery(orphanFieldsByObjectPattern).
		WithArgs(orphanID).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ApiName"}).
			AddRow("fld_Order_NewField_xyz", "NewField"))
	// No canonical row for (disc_order, NewField) → REDIRECT.
	mock.ExpectQuery(canonicalExistsPattern).
		WithArgs(canonicalID, "NewField").
		WillReturnRows(sqlmock.NewRows([]string{"1"}))
	mock.ExpectExec(updatePattern).
		WithArgs(canonicalID, "fld_Order_NewField_xyz").
		WillReturnResult(sqlmock.NewResult(0, 1))

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

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

	orphanID := "some-uuid-that-does-not-match-prefix"
	mock.ExpectQuery(orphanListPattern).
		WillReturnRows(sqlmock.NewRows([]string{"ObjectId"}).AddRow(orphanID))
	// recoverAPINameFromOrphanID returns false → no canonical lookup → drop.
	mock.ExpectExec(deleteAllForObjectPattern).
		WithArgs(orphanID).
		WillReturnResult(sqlmock.NewResult(0, 9))

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

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

	orphanID := "obj_GoneAndForgotten_4b4166815d3baec56c6503e76fc92ca2"
	mock.ExpectQuery(orphanListPattern).
		WillReturnRows(sqlmock.NewRows([]string{"ObjectId"}).AddRow(orphanID))
	mock.ExpectQuery(canonicalLookupPattern).
		WithArgs("GoneAndForgotten").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	// Canonical not found → hard-delete the orphan field rows so verify
	// doesn't loop forever.
	mock.ExpectExec(deleteAllForObjectPattern).
		WithArgs(orphanID).
		WillReturnResult(sqlmock.NewResult(0, 5))

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