package controlplane

import (
	"context"
	"regexp"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/shared/pkg/constants"
)

func TestCheckPermissionSetParentScopeUsesDeclaredSurfacePredicate(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	const (
		table      = "_System_PermissionSetAssignmentPolicyClaim"
		parentCol  = "PermissionSetId"
		sampleExpr = "CONCAT(j.PermissionSetId, '/', j.OwnerPack, '/', j.PolicyKey)"
		baseWhere  = "1 = 1"
	)
	baseJoin := table + " j LEFT JOIN PermissionSet ps ON ps.Id = j." + parentCol
	mock.ExpectQuery(regexp.QuoteMeta("SELECT COUNT(*) FROM " + baseJoin + " WHERE " + baseWhere + " AND ps.Id IS NULL")).
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery(regexp.QuoteMeta("SELECT COUNT(*) FROM " + baseJoin + " WHERE " + baseWhere + " AND ps.Id IS NOT NULL AND NOT (COALESCE(ps.IsOwnedByProfile, 0) = 1 AND COALESCE(ps.IsDeleted, 0) = 0 AND LOWER(ps.ProfileId) IN (?))")).
		WithArgs("system_admin").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))

	report := &l0DiagnosticReport{}
	oracle := &l0Oracle{profileIDsSorted: []string{"system_admin"}}
	if err := checkPermissionSetParentScope(context.Background(), db, oracle, report, table, parentCol, sampleExpr, baseWhere); err != nil {
		t.Fatalf("checkPermissionSetParentScope: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("expectations: %v", err)
	}
}

// TestCheckInstalledPackContaminationFlagsConnectorPackResidue verifies that a
// non-deleted connector-pack row with Status="failed" (left by a crashed
// install) is detected by checkInstalledPackContamination. Before the fix,
// the first enumeration used an app-pack-only filter and would have silently
// missed this row.
func TestCheckInstalledPackContaminationFlagsConnectorPackResidue(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	// listInstalledSourceBackedPacks: Category IN ('app-pack', 'connector-pack')
	cols := []string{
		"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion",
		"PlatformCompatibility", "DesiredPayloadHash", "AppliedPayloadHash",
		"PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
	}
	expectInstalledPackRuntimeDeclarationColumns(mock, false)
	mock.ExpectQuery("SELECT Name, Category, Status").
		WithArgs("app-pack", "connector-pack").
		WillReturnRows(sqlmock.NewRows(cols).AddRow(
			"test-connector", "connector-pack", "failed",
			nil, nil, nil, nil, nil, nil, nil, nil, nil, nil,
		))

	// addCountDiagnostic for "active installed-pack rows outside runtime-required
	// pack set": oracle has empty runtimePacksSorted, so predicate is "1 = 1"
	// and no extra args.
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_InstalledPack WHERE COALESCE\\(IsDeleted, 0\\) = 0 AND Status = 'active'").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))

	oracle := &l0Oracle{}
	report := &l0DiagnosticReport{}

	if err := checkInstalledPackContamination(context.Background(), db, oracle, report); err != nil {
		t.Fatalf("checkInstalledPackContamination: %v", err)
	}

	if len(report.contamination) == 0 {
		t.Fatal("expected contamination report for failed connector-pack row, got none")
	}
	msg := report.contamination[0]
	if !strings.Contains(msg, "test-connector") {
		t.Errorf("contamination message %q does not mention the pack name", msg)
	}
	if !strings.Contains(msg, "connector-pack") {
		t.Errorf("contamination message %q does not mention connector-pack category", msg)
	}
	if !strings.Contains(msg, "source-backed") {
		t.Errorf("contamination message %q does not use category-neutral wording", msg)
	}

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestCheckConnectedAppStageSurfacesFlagsCleanL0Residue(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_ConnectedAppStage s LEFT JOIN _System_InstalledPack ip").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_ConnectedAppStage s").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))
	mock.ExpectQuery("SELECT CONCAT\\(s.InstalledPackName, '/', s.ConnectedAppId\\) FROM _System_ConnectedAppStage s").
		WillReturnRows(sqlmock.NewRows([]string{"sample"}).AddRow("crm-pack/public-client"))

	report := &l0DiagnosticReport{}
	if err := checkConnectedAppStageSurfaces(context.Background(), db, report); err != nil {
		t.Fatalf("checkConnectedAppStageSurfaces: %v", err)
	}
	if len(report.contamination) != 1 {
		t.Fatalf("contamination = %#v, want one ConnectedAppStage diagnostic", report.contamination)
	}
	if msg := report.contamination[0]; !strings.Contains(msg, "_System_ConnectedAppStage rows present in clean L0") || !strings.Contains(msg, "crm-pack/public-client") {
		t.Fatalf("contamination message = %q", msg)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestBuildL0OracleIncludesStandardSystemFieldsForSystemSpecs(t *testing.T) {
	oracle, err := buildL0Oracle()
	if err != nil {
		t.Fatalf("buildL0Oracle: %v", err)
	}

	for _, fieldName := range constants.StandardSystemFields() {
		key := fieldComponentKey("RecordType", fieldName)
		if _, ok := oracle.fieldKeys[key]; !ok {
			t.Fatalf("oracle missing system-spec standard field %q for RecordType", fieldName)
		}
	}
}

func TestValidationRuleSurfacesIgnoreSoftDeletedRows(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM information_schema.columns").
		WithArgs("_System_ValidationRule", "IsDeleted").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))

	where, err := validationRuleLiveRowsPredicate(context.Background(), db)
	if err != nil {
		t.Fatalf("validationRuleLiveRowsPredicate with IsDeleted: %v", err)
	}
	if !strings.Contains(where, "COALESCE(IsDeleted, 0) = 0") {
		t.Fatalf("validationRuleLiveRowsPredicate with IsDeleted = %q, want IsDeleted filter", where)
	}

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestValidationRuleSurfacesFallbackWhenIsDeletedMissing(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM information_schema.columns").
		WithArgs("_System_ValidationRule", "IsDeleted").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM information_schema.columns").
		WithArgs("_System_ValidationRule", "Active").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))

	where, err := validationRuleLiveRowsPredicate(context.Background(), db)
	if err != nil {
		t.Fatalf("validationRuleLiveRowsPredicate without IsDeleted: %v", err)
	}
	if where != "COALESCE(Active, 0) = 1" {
		t.Fatalf("validationRuleLiveRowsPredicate without IsDeleted = %q, want Active filter", where)
	}

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestValidationRuleSurfacesFallbackWhenIsDeletedAndActiveMissing(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM information_schema.columns").
		WithArgs("_System_ValidationRule", "IsDeleted").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM information_schema.columns").
		WithArgs("_System_ValidationRule", "Active").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))

	where, err := validationRuleLiveRowsPredicate(context.Background(), db)
	if err != nil {
		t.Fatalf("validationRuleLiveRowsPredicate without IsDeleted/Active: %v", err)
	}
	if where != "1 = 1" {
		t.Fatalf("validationRuleLiveRowsPredicate without IsDeleted/Active = %q, want 1 = 1", where)
	}

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}