package controlplane

import (
	"regexp"
	"testing"

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

var orphanCountPattern = "SELECT f\\.`ObjectId`, COUNT\\(\\*\\) FROM `_System_Field` f " +
	"LEFT JOIN `_System_Object` o ON f\\.`ObjectId` = o\\.`Id` " +
	"WHERE f\\.`IsDeleted` = 0 AND o\\.`Id` IS NULL " +
	"GROUP BY f\\.`ObjectId`"

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

	mock.ExpectQuery(orphanCountPattern).
		WillReturnRows(sqlmock.NewRows([]string{"ObjectId", "n"}))

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

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

	mock.ExpectQuery(orphanCountPattern).
		WillReturnRows(sqlmock.NewRows([]string{"ObjectId", "n"}).
			AddRow("obj_Order_4b4166815d3baec56c6503e76fc92ca2", 5).
			AddRow("obj_Lead_1234567890abcdef1234567890abcdef", 2))

	err = VerifyOrphanSystemFields(db)
	require.Error(t, err)
	assert.Contains(t, err.Error(), "2 object(s)")
	assert.Contains(t, err.Error(), "7 rows total")
	assert.Contains(t, err.Error(), "cmd/setup apply")
	require.NoError(t, mock.ExpectationsWereMet())
}

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

	mock.ExpectQuery(orphanCountPattern).
		WillReturnError(assertNoOpError("query failed"))

	err = VerifyOrphanSystemFields(db)
	require.Error(t, err)
	assert.Contains(t, err.Error(), "query _System_Field for orphans")
}

type fakeQueryError struct{ s string }

func (e *fakeQueryError) Error() string { return e.s }
func assertNoOpError(s string) error    { return &fakeQueryError{s: s} }

// Compile-time check that the regex itself parses (defensive — sqlmock will
// fail with a confusing message if the pattern is malformed).
func TestOrphanCountPattern_ParsesAsRegex(t *testing.T) {
	_, err := regexp.Compile(orphanCountPattern)
	require.NoError(t, err)
}