package controlplane

import (
	"regexp"
	"testing"

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

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

	mock.ExpectQuery(regexp.QuoteMeta("SELECT COUNT(*) FROM `_System_Object` o WHERE o.`IsDeleted` = 0 AND o.`TableType` NOT LIKE 'system%' AND o.`ApiName` NOT LIKE '%\\\\_bak\\\\_%' ESCAPE '\\\\' AND (o.`AccessPolicy` IS NULL OR COALESCE(LOWER(JSON_UNQUOTE(JSON_EXTRACT(o.`AccessPolicy`, '$.mode'))), '') <> 'derived')")).
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(2))
	mock.ExpectQuery(regexp.QuoteMeta("SELECT COUNT(*) FROM `_System_Field` f JOIN `_System_Object` o ON f.`ObjectId` = o.`Id` WHERE f.`IsDeleted` = 0 AND o.`IsDeleted` = 0 AND o.`TableType` NOT LIKE 'system%' AND o.`ApiName` NOT LIKE '%\\\\_bak\\\\_%' ESCAPE '\\\\' AND (o.`AccessPolicy` IS NULL OR COALESCE(LOWER(JSON_UNQUOTE(JSON_EXTRACT(o.`AccessPolicy`, '$.mode'))), '') <> 'derived')")).
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(5))
	mock.ExpectQuery(`SELECT\s+ps\.ProfileId,\s+COALESCE\(p\.Name, ps\.ProfileId\),.*FROM PermissionSet ps.*LEFT JOIN \(\s+SELECT\s+op\.ParentId AS parent_id,.*FROM ObjectPermissions op.*JOIN _System_Object o.*GROUP BY op\.ParentId\s+\) op_counts.*LEFT JOIN \(\s+SELECT\s+fp\.ParentId AS parent_id,.*FROM FieldPermissions fp.*JOIN _System_Object o.*GROUP BY fp\.ParentId\s+\) fp_counts.*WHERE ps\.IsOwnedByProfile = 1\s+AND ps\.IsDeleted = 0`).
		WillReturnRows(sqlmock.NewRows([]string{"ProfileId", "Name", "object_rows", "field_rows"}).
			AddRow("p-admin", "System Administrator", 2, 5).
			AddRow("p-standard", "Standard User", 2, 5))

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

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

	mock.ExpectQuery(regexp.QuoteMeta("SELECT COUNT(*) FROM `_System_Object` o WHERE o.`IsDeleted` = 0 AND o.`TableType` NOT LIKE 'system%' AND o.`ApiName` NOT LIKE '%\\\\_bak\\\\_%' ESCAPE '\\\\' AND (o.`AccessPolicy` IS NULL OR COALESCE(LOWER(JSON_UNQUOTE(JSON_EXTRACT(o.`AccessPolicy`, '$.mode'))), '') <> 'derived')")).
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(2))
	mock.ExpectQuery(regexp.QuoteMeta("SELECT COUNT(*) FROM `_System_Field` f JOIN `_System_Object` o ON f.`ObjectId` = o.`Id` WHERE f.`IsDeleted` = 0 AND o.`IsDeleted` = 0 AND o.`TableType` NOT LIKE 'system%' AND o.`ApiName` NOT LIKE '%\\\\_bak\\\\_%' ESCAPE '\\\\' AND (o.`AccessPolicy` IS NULL OR COALESCE(LOWER(JSON_UNQUOTE(JSON_EXTRACT(o.`AccessPolicy`, '$.mode'))), '') <> 'derived')")).
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(5))
	mock.ExpectQuery(`SELECT\s+ps\.ProfileId,\s+COALESCE\(p\.Name, ps\.ProfileId\),.*FROM PermissionSet ps.*LEFT JOIN \(\s+SELECT\s+op\.ParentId AS parent_id,.*FROM ObjectPermissions op.*JOIN _System_Object o.*GROUP BY op\.ParentId\s+\) op_counts.*LEFT JOIN \(\s+SELECT\s+fp\.ParentId AS parent_id,.*FROM FieldPermissions fp.*JOIN _System_Object o.*GROUP BY fp\.ParentId\s+\) fp_counts.*WHERE ps\.IsOwnedByProfile = 1\s+AND ps\.IsDeleted = 0`).
		WillReturnRows(sqlmock.NewRows([]string{"ProfileId", "Name", "object_rows", "field_rows"}).
			AddRow("p-admin", "System Administrator", 2, 5).
			AddRow("p-standard", "Standard User", 1, 4))

	err = VerifyProfilePermissionCoverage(db)
	require.Error(t, err)
	assert.Contains(t, err.Error(), "profile permission coverage drift detected on 1 profile(s)")
	assert.Contains(t, err.Error(), "Standard User (p-standard): objects 1/2, fields 4/5")
	assert.Contains(t, err.Error(), "cmd/setup repair")
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestBusinessObjectOwnsSelfPermissionsFilter_OnlyExcludesDerivedMode(t *testing.T) {
	filter := businessObjectOwnsSelfPermissionsFilter("o")

	assert.Contains(t, filter, "o.`AccessPolicy` IS NULL")
	assert.Contains(t, filter, "JSON_EXTRACT(o.`AccessPolicy`, '$.mode')")
	assert.Contains(t, filter, "<> 'derived'")
}

func TestBusinessObjectFilter_SkipsDerivedPredicateWithoutAccessPolicyColumn(t *testing.T) {
	filter := businessObjectFilter(businessObjectFilterOptions{})

	assert.Contains(t, filter, "o.`IsDeleted` = 0")
	assert.NotContains(t, filter, "AccessPolicy")
}