package controlplane

import (
	"errors"
	"strings"
	"testing"

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

// businessObjectFilter assertions: parent-controlled exclusion appears ONLY
// when the caller passes permKindObject, and the exclusion clause matches the
// runtime helper (`MetadataService.GetParentControlledObject`) byte-for-byte
// — SharingModel='ControlledByParent' AND an EXISTS subquery that recognizes
// either a live `_System_Field` master-detail edge or an explicit
// `_System_Relationship.IsSharingParent` overlay. The SQL- and runtime
// predicates must agree on missing default rows while preserving explicit
// overlays, otherwise the verifier flags drift the runtime won't enforce (or
// vice versa).

func TestBusinessObjectFilterIncludesParentControlledExclusionForObjectKind(t *testing.T) {
	got := businessObjectFilter(businessObjectFilterOptions{ExcludeDerivedSelfPermissions: true}, permKindObject)
	requireSubstring(t, got, "'ControlledByParent'")
	requireSubstring(t, got, "EXISTS")
	requireSubstring(t, got, "_System_Field")
	requireSubstring(t, got, "ObjectId")
	requireSubstring(t, got, "ReferenceTo")
	requireSubstring(t, got, "IsMasterDetail")
	requireSubstring(t, got, "Type")
	requireSubstring(t, got, "_System_Relationship")
	requireSubstring(t, got, "IsSharingParent")
	// Pin the EXISTS subquery structure: it must join field rows to the outer
	// object's metadata row and accept either a live master-detail field with at
	// least one parent target or an explicit sharing-parent overlay row whose
	// backing field still exists on the child object.
	requireSubstring(t, got, "f.`ObjectId` = o.`Id`")
	requireSubstring(t, got, "f.`IsDeleted` = 0")
	requireSubstring(t, got, "COALESCE(f.`ReferenceTo`, '') NOT IN ('', '[]')")
	requireSubstring(t, got, "LOWER(COALESCE(f.`Type`, '')) = 'masterdetail'")
	requireSubstring(t, got, "COALESCE(f.`IsMasterDetail`, 0) = 1")
	requireSubstring(t, got, "r.`IsSharingParent` = 1")
	requireSubstring(t, got, "r.`ChildObjectApiName` = o.`ApiName`")
	requireAbsent(t, got, "r.`IsDeleted`")
	// Orphan-overlay guard: the overlay branch must additionally require that
	// the overlay row's FieldApiName corresponds to a live relationship field on
	// the child object that is still a relationship type (reference/masterdetail
	// or IsMasterDetail=1) and still targets r.ParentObjectApiName, matching the
	// resolver's behavior of ignoring overlay rows whose backing field-derived
	// edge no longer exists (field retargeted, type changed away from
	// reference/masterdetail, or ReferenceTo no longer contains the parent).
	requireSubstring(t, got, "f2.`ObjectId` = o.`Id`")
	requireSubstring(t, got, "f2.`ApiName` = r.`FieldApiName`")
	requireSubstring(t, got, "f2.`IsDeleted` = 0")
	requireSubstring(t, got, "COALESCE(f2.`ReferenceTo`, '') NOT IN ('', '[]')")
	requireSubstring(t, got, "JSON_CONTAINS(LOWER(f2.`ReferenceTo`), LOWER(CONCAT('\"', r.`ParentObjectApiName`, '\"')))")
	// The overlay branch must require f2 to still be a relationship field type,
	// matching the runtime resolver's isRelationship check (reference or
	// masterdetail Type, or IsMasterDetail=1).
	requireSubstring(t, got, "LOWER(COALESCE(f2.`Type`, '')) IN ('reference', 'masterdetail')")
	requireSubstring(t, got, "COALESCE(f2.`IsMasterDetail`, 0) = 1")
}

func TestBusinessObjectFilterOmitsParentControlledExclusionForFieldKind(t *testing.T) {
	got := businessObjectFilter(businessObjectFilterOptions{ExcludeDerivedSelfPermissions: true}, permKindField)
	requireAbsent(t, got, "'ControlledByParent'")
	requireAbsent(t, got, "_System_Field")
	requireAbsent(t, got, "IsMasterDetail")
	requireAbsent(t, got, "IsSharingParent")
}

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

	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM `_System_Object`").
		WillReturnError(errors.New("Error 1064: syntax error near ')'"))

	err = VerifyProfilePermissionCoverage(db)
	if err == nil {
		t.Fatal("VerifyProfilePermissionCoverage error = nil, want DB execution error")
	}
	if !strings.Contains(err.Error(), "count expected business objects") {
		t.Fatalf("error = %q, want query family context", err.Error())
	}
	if !strings.Contains(err.Error(), "syntax error") {
		t.Fatalf("error = %q, want underlying DB error", err.Error())
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

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

	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM `_System_Object`").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(2))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM `_System_Field`").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(3))
	mock.ExpectQuery("SELECT\\s+ps\\.ProfileId").
		WillReturnRows(sqlmock.NewRows([]string{"ProfileId", "ProfileName", "object_rows", "field_rows"}).
			AddRow(constants.ProfileSystemAdmin, "System Administrator", 1, 3))

	err = VerifyProfilePermissionCoverage(db)
	if err == nil {
		t.Fatal("VerifyProfilePermissionCoverage error = nil, want profile coverage drift")
	}
	if !strings.Contains(err.Error(), "profile permission coverage drift detected") {
		t.Fatalf("error = %q, want drift failure", err.Error())
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

func requireSubstring(t *testing.T, haystack, needle string) {
	t.Helper()
	if !strings.Contains(haystack, needle) {
		t.Fatalf("filter must contain %q; got:\n%s", needle, haystack)
	}
}

func requireAbsent(t *testing.T, haystack, needle string) {
	t.Helper()
	if strings.Contains(haystack, needle) {
		t.Fatalf("filter must NOT contain %q; got:\n%s", needle, haystack)
	}
}