//go:build integration

package controlplane

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"strings"
	"testing"

	"github.com/pingcap-inc/nexuscrm-controlplane/packs"
)

// jiraLikeProbePackSourceDir resolves the in-repo, deliberately non-CRM probe
// app pack relative to this test file.
func jiraLikeProbePackSourceDir(t *testing.T) string {
	t.Helper()
	return controlplaneTestPackFixtureDir(t, "jira-like-probe")
}

// TestMultiAppNeutralityProbeInstallsOnCleanL0 is the multi-app neutrality
// probe. It installs the deliberately non-CRM "jira-like-probe" app pack onto a
// freshly bootstrapped clean L0 and verifies the platform's SCHEMA / install path
// accepts a non-Salesforce object (Issue, keyPrefix "ISS", self-referencing
// ParentIssueId, Jira-vocabulary picklists, Subject as the name field) the same
// way it does the CRM pack.
//
// SCOPE — read before trusting this as "L0 is neutral": this exercises ONLY the
// schema/deployment calibration leg. It does NOT exercise the permission-seeding
// MACHINERY (the probe ships no permission metadata, so Layer 2 below is a vacuous
// pass — nothing to grant), nor #1374 (synthetic-vs-imported permission IDs), nor
// workflow / record CRUD on the non-CRM object. It confirms "a non-CRM object's
// SCHEMA installs + verifies on L0", NOT "a non-CRM app RUNS on L0". Genuinely
// testing those needs the next probe step: ship a permission component (+ a flow +
// record CRUD) so the seed/apply path actually runs.
//
// This is a DIAGNOSTIC harness, not a fix. A failure rooted in an L0
// Salesforce-shape assumption is a substrate gap to be reported and scoped (the
// fix is a separate task), not patched here. The harness must not be made green
// by weakening an assertion to dodge a real gap.
//
// Two decisions, both grounded in the actual platform code:
//
//  1. VerifyL0 is the clean-room oracle. Its docstring: "verifies that the
//     database contains only the generic platform substrate and runtime-required
//     platform packs ... any installed app pack or app-shaped metadata is a
//     failure." It therefore MUST pass before the probe installs and MUST fail
//     after (by design, identically for the CRM pack — it is not a neutrality
//     check). So it is asserted only as a pre-install baseline; post-install
//     integrity is checked via the pack's own Verify plus direct schema reads.
//
//  2. Installation goes through InstallPackFromSource (the production app-pack
//     entry), not the lower-level pack.Apply. Only InstallPackFromSource records
//     the _System_InstalledPack ledger row and runs the install phase hooks
//     (including permission seeding, when the pack ships a permission component) —
//     which a bare pack.Apply would skip, making the ledger and Layer-2 checks
//     untestable.
//
// Expected outcome on a clean tree: both layers pass, but they are NOT equal in
// strength. Layer 1 (schema/deployment) is a GENUINE verification — L0 hosts the
// non-CRM Issue object cleanly (object row, all fields, self-reference, physical
// table, pack.Verify, ledger). Layer 2 (permission state) is a VACUOUS pass — the
// probe ships no permission metadata, so "grants the object to no non-admin profile"
// is near-tautological (nothing to grant) and the no-dangling check has zero rows to
// inspect. That zero-grant outcome IS Salesforce-correct (access comes from shipped
// permission metadata or admin config, never auto-granted by install; admins retain
// access via the IsSystemAdmin bypass) — an earlier revision wrongly asserted it as
// a FAILURE assuming auto-grant. But "correct AND vacuous" must not be read as
// "permissions verified": the seed/apply machinery and #1374 are untested here.
// See checkProbePermissionSeeding.
func TestMultiAppNeutralityProbeInstallsOnCleanL0(t *testing.T) {
	ctx := context.Background()
	cfg, cleanup := newTemporaryIntegrationDatabase(t)
	defer cleanup()

	conn := initializeControlPlaneMetadata(t, ctx, cfg)
	defer conn.Close()

	r, err := NewRunner(conn)
	if err != nil {
		t.Fatalf("new runner: %v", err)
	}
	if err := r.RefreshCache(ctx); err != nil {
		t.Fatalf("initial cache refresh: %v", err)
	}
	if err := ApplyRuntimeRequiredPacks(ctx, r); err != nil {
		t.Fatalf("apply runtime-required packs: %v", err)
	}

	// Baseline: a clean L0 with only platform substrate must pass VerifyL0 and
	// carry no app pack. This both establishes the starting state and documents
	// that VerifyL0 is the clean-room oracle (see decision 1 above).
	if err := VerifyL0(ctx, conn); err != nil {
		t.Fatalf("baseline VerifyL0 on clean L0 (pre-install) must pass: %v", err)
	}
	assertNoRows(t, conn.DB(), "app packs before probe install",
		"SELECT COUNT(*) FROM _System_InstalledPack WHERE Category = ? AND COALESCE(IsDeleted, 0) = 0",
		string(packs.AppPack))

	// Install the non-CRM probe app pack from its local directory.
	probeDir := jiraLikeProbePackSourceDir(t)
	if err := InstallPackFromSource(ctx, r, "local_path:"+probeDir); err != nil {
		t.Fatalf("GAP[install]: InstallPackFromSource(local_path:jira-like-probe) failed: %v", err)
	}

	// ---- Layer 1: schema / deployment ----
	// Collect every Layer-1 result in one run (t.Errorf), then stop before Layer 2
	// if anything is wrong (a Layer-1 failure WOULD be a real neutrality gap).
	checkProbeInstalledPackLedger(t, ctx, conn.DB())
	checkProbeObjectMetadata(t, ctx, conn.DB())
	checkProbeFieldMetadata(t, ctx, conn.DB())
	checkProbePhysicalTable(t, ctx, conn.DB())
	if err := VerifyInstalledPackByName(ctx, conn.DB(), "jira-like-probe", true); err != nil {
		t.Errorf("GAP[Layer1 pack.Verify]: deep verify of installed jira-like-probe failed: %v", err)
	}

	if t.Failed() {
		t.Fatalf("stopping at first gap in Layer 1 (schema/deployment); Layer 2 (permission seeding) not evaluated")
	}

	// ---- Layer 2: permission-seeding cleanliness (#1374) ----
	checkProbePermissionSeeding(t, ctx, conn.DB())
}

// checkProbeInstalledPackLedger asserts the probe is recorded as an active,
// local-path-sourced AppPack in the installed-pack ledger.
func checkProbeInstalledPackLedger(t *testing.T, ctx context.Context, db *sql.DB) {
	t.Helper()
	var status, category, sourceKind sql.NullString
	err := db.QueryRowContext(ctx, `
		SELECT Status, Category, SourceKind
		  FROM _System_InstalledPack
		 WHERE Name = 'jira-like-probe' AND COALESCE(IsDeleted, 0) = 0
		 LIMIT 1`).Scan(&status, &category, &sourceKind)
	if err == sql.ErrNoRows {
		t.Errorf("GAP[Layer1 ledger]: no _System_InstalledPack row for jira-like-probe")
		return
	}
	if err != nil {
		t.Errorf("GAP[Layer1 ledger]: query _System_InstalledPack: %v", err)
		return
	}
	if category.String != string(packs.AppPack) {
		t.Errorf("GAP[Layer1 ledger]: jira-like-probe Category = %q, want %q", category.String, string(packs.AppPack))
	}
	if status.String != "active" {
		t.Errorf("GAP[Layer1 ledger]: jira-like-probe Status = %q, want active", status.String)
	}
	if sourceKind.String != string(packs.PackSourceKindLocalPath) {
		t.Errorf("GAP[Layer1 ledger]: jira-like-probe SourceKind = %q, want %q", sourceKind.String, string(packs.PackSourceKindLocalPath))
	}
}

// checkProbeObjectMetadata asserts the Issue _System_Object row carries the
// deliberately non-Salesforce shape: alphabetic KeyPrefix "ISS", and Subject
// (not "Name") as the declared name field.
//
// Note: the spec's object-level `category:"jira"` is intentionally NOT asserted
// here — _System_Object has no Category column. Object category is a spec/pack
// classifier (used by the verify oracle's category="system" platform-spec
// filter), not a persisted per-object attribute. That is itself a mild
// neutrality positive: L0 cannot branch on a per-object category it never stores.
func checkProbeObjectMetadata(t *testing.T, ctx context.Context, db *sql.DB) {
	t.Helper()
	var keyPrefix, tableType, sharingModel, displayNameField sql.NullString
	err := db.QueryRowContext(ctx, `
		SELECT KeyPrefix, TableType, SharingModel, DisplayNameField
		  FROM _System_Object
		 WHERE ApiName = 'Issue' AND COALESCE(IsDeleted, 0) = 0
		 LIMIT 1`).Scan(&keyPrefix, &tableType, &sharingModel, &displayNameField)
	if err == sql.ErrNoRows {
		t.Errorf("GAP[Layer1 object]: no _System_Object row for Issue")
		return
	}
	if err != nil {
		t.Errorf("GAP[Layer1 object]: query _System_Object for Issue: %v", err)
		return
	}
	if keyPrefix.String != "ISS" {
		t.Errorf("GAP[Layer1 object]: Issue KeyPrefix = %q (valid=%v), want \"ISS\"", keyPrefix.String, keyPrefix.Valid)
	}
	if tableType.String != "standard_object" {
		t.Errorf("GAP[Layer1 object]: Issue TableType = %q, want standard_object", tableType.String)
	}
	if sharingModel.String != "Private" {
		t.Errorf("GAP[Layer1 object]: Issue SharingModel = %q, want Private", sharingModel.String)
	}
	// DisplayNameField is the OPTIONAL object-level display-name override. L0
	// populates _System_Object.DisplayNameField only from spec.displayNameField
	// (platform_core.go:368/816) and never derives it from a field's isNameField
	// flag. The probe omits the override — exactly like the CRM pack's Account
	// (name field "Name"), Contact/Lead ("LastName"), which also rely on
	// field-level isNameField and leave DisplayNameField empty. (Only the 3 CRM
	// line-item objects, whose display name is a Product2Id lookup rather than a
	// text field, set the override.) So an empty value here is correct-by-
	// convention, NOT a gap; the name field is asserted via IsNameField in
	// checkProbeFieldMetadata. An earlier revision asserted == "Subject" here and
	// failed; the probe-intersection comparison against CRM proved that was an
	// over-strict harness assertion, not an L0 Salesforce-shape leak.
	t.Logf("Layer1 object: Issue DisplayNameField = %q (empty-by-convention; the name field is carried by IsNameField=Subject, as for CRM Account/Contact/Lead)", displayNameField.String)
}

// checkProbeFieldMetadata asserts every declared field materialized, that
// Subject is the sole name field, and that the ParentIssueId self-reference
// resolved (referenceTo Issue, relationshipName ParentIssue).
func checkProbeFieldMetadata(t *testing.T, ctx context.Context, db *sql.DB) {
	t.Helper()
	rows, err := db.QueryContext(ctx, `
		SELECT f.ApiName, f.Type, COALESCE(f.IsNameField,0),
		       COALESCE(f.ReferenceTo,''), COALESCE(f.RelationshipName,''), COALESCE(f.Options,'')
		  FROM _System_Field f
		  JOIN _System_Object o ON o.Id = f.ObjectId
		 WHERE o.ApiName = 'Issue'
		   AND COALESCE(o.IsDeleted,0)=0 AND COALESCE(f.IsDeleted,0)=0`)
	if err != nil {
		t.Errorf("GAP[Layer1 fields]: query _System_Field for Issue: %v", err)
		return
	}
	defer rows.Close()

	gotType := map[string]string{}
	var nameFields []string
	var parentRef, parentRel, statusOptions string
	for rows.Next() {
		var api, typ, refTo, relName, options string
		var isNameField int
		if err := rows.Scan(&api, &typ, &isNameField, &refTo, &relName, &options); err != nil {
			t.Errorf("GAP[Layer1 fields]: scan row: %v", err)
			return
		}
		gotType[api] = typ
		if isNameField == 1 {
			nameFields = append(nameFields, api)
		}
		if api == "ParentIssueId" {
			parentRef, parentRel = refTo, relName
		}
		if api == "Status" {
			statusOptions = options
		}
	}
	if err := rows.Err(); err != nil {
		// A mid-stream driver error must not be silently swallowed into a
		// partial result set that then reports real fields as "missing".
		t.Errorf("GAP[Layer1 fields]: iterate _System_Field rows for Issue: %v", err)
		return
	}
	t.Logf("Layer1 fields: stored types = %v", gotType)

	// Every declared field present, type compared case-insensitively (the stored
	// representation is an internal detail; presence is the unambiguous fact).
	want := map[string]string{
		"Subject": "string", "Description": "textarea", "Status": "picklist",
		"Priority": "picklist", "IssueType": "picklist", "ResolutionDate": "date",
		"EstimatedHours": "double", "ParentIssueId": "reference",
	}
	for api, wantType := range want {
		got, ok := gotType[api]
		if !ok {
			t.Errorf("GAP[Layer1 fields]: declared field %q missing from _System_Field", api)
			continue
		}
		if !strings.EqualFold(got, wantType) {
			t.Errorf("GAP[Layer1 fields]: field %q Type = %q, want (case-insensitive) %q", api, got, wantType)
		}
	}
	if len(nameFields) != 1 || nameFields[0] != "Subject" {
		t.Errorf("GAP[Layer1 fields]: name field(s) = %v, want [Subject]", nameFields)
	}

	// Self-reference: ParentIssueId.ReferenceTo is stored as a JSON string array
	// and must resolve to exactly ["Issue"]. Decode rather than substring-match —
	// Contains(_, "Issue") would also accept ["IssueType"] or a polymorphic
	// ["Account","Issue"], masking exactly the self-reference-resolution regression
	// this probe exists to catch. (Guarded on presence; absence is reported above.)
	if _, ok := gotType["ParentIssueId"]; ok {
		var refTargets []string
		if err := json.Unmarshal([]byte(parentRef), &refTargets); err != nil {
			t.Errorf("GAP[Layer1 fields]: ParentIssueId ReferenceTo = %q is not a JSON string array: %v", parentRef, err)
		} else if len(refTargets) != 1 || refTargets[0] != "Issue" {
			t.Errorf("GAP[Layer1 fields]: ParentIssueId ReferenceTo = %v, want [Issue] (self-reference)", refTargets)
		}
		if parentRel != "ParentIssue" {
			t.Errorf("GAP[Layer1 fields]: ParentIssueId RelationshipName = %q, want ParentIssue", parentRel)
		}
	}

	// The distinctly non-Salesforce picklist VALUE must survive. Decode the Options
	// JSON and check the value set — a substring match would pass on a label-only or
	// mangled value (e.g. value drifted but the "In Progress" label retained).
	if _, ok := gotType["Status"]; ok {
		var statusValues []struct {
			Value string `json:"value"`
		}
		if err := json.Unmarshal([]byte(statusOptions), &statusValues); err != nil {
			t.Errorf("GAP[Layer1 fields]: Status options = %q is not a JSON option array: %v", statusOptions, err)
		} else {
			hasInProgress := false
			for _, o := range statusValues {
				if o.Value == "In Progress" {
					hasInProgress = true
					break
				}
			}
			if !hasInProgress {
				t.Errorf("GAP[Layer1 fields]: Status option values = %+v, want one with value %q", statusValues, "In Progress")
			}
		}
	}
}

// checkProbePhysicalTable asserts the Issue physical table and its declared
// business columns exist in the live schema.
func checkProbePhysicalTable(t *testing.T, ctx context.Context, db *sql.DB) {
	t.Helper()
	rows, err := db.QueryContext(ctx, `
		SELECT COLUMN_NAME FROM information_schema.COLUMNS
		 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'Issue'`)
	if err != nil {
		t.Errorf("GAP[Layer1 table]: query information_schema for Issue: %v", err)
		return
	}
	defer rows.Close()
	cols := map[string]bool{}
	for rows.Next() {
		var c string
		if err := rows.Scan(&c); err != nil {
			t.Errorf("GAP[Layer1 table]: scan column: %v", err)
			return
		}
		cols[c] = true
	}
	if err := rows.Err(); err != nil {
		// Distinguish a driver/iteration error from a genuinely empty result, so
		// a transport error is not misreported as "table does not exist".
		t.Errorf("GAP[Layer1 table]: iterate information_schema columns for Issue: %v", err)
		return
	}
	if len(cols) == 0 {
		t.Errorf("GAP[Layer1 table]: physical table Issue does not exist (no columns)")
		return
	}
	for _, c := range []string{"Subject", "Description", "Status", "Priority", "IssueType", "ResolutionDate", "EstimatedHours", "ParentIssueId"} {
		if !cols[c] {
			t.Errorf("GAP[Layer1 table]: physical column Issue.%s missing", c)
		}
	}
}

// checkProbePermissionSeeding inspects the permission state after installing a
// pack that ships NO permission metadata, and confirms it matches the
// Salesforce-correct model. It is NOT a gap.
//
// Correct behavior (verified against the CRM pack and the access path):
//   - A pack that ships no permission component grants the object to NO non-admin
//     profile. Access comes from permission metadata shipped by the pack (the CRM
//     pack ships permissions/crm_standard_permissions.json) or configured by an
//     admin — never auto-granted as a side effect of install. So zero Issue
//     ObjectPermissions/FieldPermissions rows is EXPECTED, not a deficiency.
//   - System Administrator / superuser still has full access: the access path
//     bypasses ObjectPermissions for IsSystemAdmin (persistence_service.go:1037,
//     permissions/service.go:115/176).
//   - Whatever grants DO exist must reference a live PermissionSet (the #1374
//     dangling-identity concern). With zero grants this passes vacuously; to
//     exercise it for real, a future revision should ship a small permission
//     component in the probe (the higher-value #1374 test).
//
// (An earlier revision asserted zero-grants as a FAILURE — that assumed install
// should auto-grant baseline access, which is the wrong model: it would bake "which
// profile sees which object" into L0 and diverge from Salesforce.)
//
// The one genuine L0/L1 follow-up this surfaced (a purity item, not a behavioral
// bug): the platform's OWN standard objects receive default permissions via an
// implicit bootstrap step (InitializePermissions, which only fully-seeds
// completely-unseeded profiles — permissions.go:154-162) rather than via applied
// pack metadata the way app packs do. Expressing those defaults as shipped pack
// metadata would give ONE metadata-driven rule for permissions instead of a
// bootstrap-vs-install split.
func checkProbePermissionSeeding(t *testing.T, ctx context.Context, db *sql.DB) {
	t.Helper()

	// A permission-less pack correctly grants nothing to non-admins; log the counts
	// as documentation of that expected state (admins access via the bypass above).
	objPerms := queryCount(t, db,
		`SELECT COUNT(*) FROM ObjectPermissions WHERE SobjectType = 'Issue' AND COALESCE(IsDeleted,0)=0`)
	fieldPerms := queryCount(t, db,
		`SELECT COUNT(*) FROM FieldPermissions WHERE SobjectType = 'Issue' AND COALESCE(IsDeleted,0)=0`)
	t.Logf("Layer2: Issue non-admin grants after a permission-less install — ObjectPermissions=%d, FieldPermissions=%d (zero is correct; non-admin access requires shipped permission metadata or admin config)", objPerms, fieldPerms)

	// The real consistency assertion (#1374): any grant that exists must reference a
	// live PermissionSet. Vacuously true here (zero grants); becomes meaningful once
	// the probe ships a permission component.
	assertNoDanglingParentId(t, db, "ObjectPermissions")
	assertNoDanglingParentId(t, db, "FieldPermissions")
}

// assertNoDanglingParentId fails if any non-deleted Issue permission row in the
// given table references a ParentId with no live PermissionSet (#1374). `table`
// is an in-code literal ("ObjectPermissions"/"FieldPermissions"), not user input.
func assertNoDanglingParentId(t *testing.T, db *sql.DB, table string) {
	t.Helper()
	dangling := queryCount(t, db, fmt.Sprintf(`
		SELECT COUNT(*) FROM %s p
		 WHERE p.SobjectType='Issue' AND COALESCE(p.IsDeleted,0)=0
		   AND NOT EXISTS (SELECT 1 FROM PermissionSet ps WHERE ps.Id = p.ParentId AND COALESCE(ps.IsDeleted,0)=0)`, table))
	if dangling != 0 {
		t.Errorf("GAP[Layer2 perms]: %d Issue %s rows have a dangling ParentId (no live PermissionSet)", dangling, table)
	}
}