package persistence

import (
	"database/sql"
	"strings"
	"testing"

	domainschema "github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

func TestSchemaRepositoryAccessPolicySidecarOutcomesOnTiDB(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()
	createCanonicalSystemTablesForIntegration(t, db, constants.TableObject)
	repo := NewSchemaRepository(db, nil)

	seedMetadataSidecarObject(t, db, "Flow", "salesforce-crm-standard", `{"deny":[{"surfaces":["singleCreate"],"message":"closed"}]}`, "", nil)
	flow := metadataSidecarObject("Flow")
	if err := repo.BatchSaveObjectMetadata(testWriterContext(t), []*models.ObjectMetadata{flow}, nil); err != nil {
		t.Fatalf("BatchSaveObjectMetadata recordMutation: %v", err)
	}
	if flow.AccessPolicy == nil || flow.AccessPolicy.RecordMutation == nil || flow.AccessPolicy.RecordMutation.Deny[0].Message != "closed" {
		t.Fatalf("restored recordMutation = %#v", flow.AccessPolicy)
	}
	assertPersistedAccessPolicyContains(t, db, "Flow", "closed")

	seedMetadataSidecarObject(t, db, "LiveFlow", "salesforce-crm-standard", nil, "", nil)
	if _, err := db.Exec(`UPDATE _System_Object SET AccessPolicy = '{"recordMutation":{"deny":[{"surfaces":["singleCreate"],"message":"live"}]}}' WHERE ApiName = 'LiveFlow'`); err != nil {
		t.Fatal(err)
	}
	liveFlow := metadataSidecarObject("LiveFlow")
	liveFlow.AccessPolicy = &models.ObjectAccessPolicy{Mode: "derived", RowRead: &models.RowReadPolicy{Kind: "owner"}}
	if err := repo.BatchSaveObjectMetadata(testWriterContext(t), []*models.ObjectMetadata{liveFlow}, nil); err != nil {
		t.Fatalf("BatchSaveObjectMetadata live recordMutation: %v", err)
	}
	if liveFlow.AccessPolicy.RecordMutation == nil || liveFlow.AccessPolicy.RecordMutation.Deny[0].Message != "live" ||
		liveFlow.AccessPolicy.Mode != "derived" || liveFlow.AccessPolicy.RowRead == nil || liveFlow.AccessPolicy.RowRead.Kind != "owner" {
		t.Fatalf("merged live access policy = %#v", liveFlow.AccessPolicy)
	}
	assertPersistedAccessPolicyContains(t, db, "LiveFlow", "live")

	seedMetadataSidecarObject(t, db, "ProcessDefinition", "", nil, "salesforce-crm-standard", `{"hooks":[{"phase":"afterUpdate","capability":"approval"}]}`)
	process := metadataSidecarObject("ProcessDefinition")
	process.AccessPolicy = &models.ObjectAccessPolicy{
		WriteValidation: &models.RecordWriteValidationAccessPolicy{Validators: []models.RecordWriteValidationPolicy{{Phase: "beforeSave", Capability: "validate-process"}}},
	}
	if err := repo.BatchSaveObjectMetadata(testWriterContext(t), []*models.ObjectMetadata{process}, nil); err != nil {
		t.Fatalf("BatchSaveObjectMetadata lifecycle: %v", err)
	}
	if process.AccessPolicy.Lifecycle == nil || process.AccessPolicy.Lifecycle.Hooks[0].Capability != "approval" || process.AccessPolicy.WriteValidation.Validators[0].Capability != "validate-process" {
		t.Fatalf("merged access policy = %#v", process.AccessPolicy)
	}

	seedMetadataSidecarObject(t, db, "ProcessAction", "", nil, "salesforce-crm-standard", `{"hooks":[{"phase":"afterCreate","capability":"process-action"}]}`)
	processAction := metadataSidecarObject("ProcessAction")
	if err := repo.SaveObjectMetadata(testWriterContext(t), processAction, nil); err != nil {
		t.Fatalf("SaveObjectMetadata lifecycle: %v", err)
	}
	if processAction.AccessPolicy == nil || processAction.AccessPolicy.Lifecycle == nil || processAction.AccessPolicy.Lifecycle.Hooks[0].Capability != "process-action" {
		t.Fatalf("single-save lifecycle = %#v", processAction.AccessPolicy)
	}

	seedMetadataSidecarObject(t, db, "UnownedObject", "", nil, "", nil)
	unowned := metadataSidecarObject("UnownedObject")
	if err := repo.BatchSaveObjectMetadata(testWriterContext(t), []*models.ObjectMetadata{unowned}, nil); err != nil {
		t.Fatalf("BatchSaveObjectMetadata unowned: %v", err)
	}
	if unowned.AccessPolicy != nil {
		t.Fatalf("unowned AccessPolicy = %#v, want nil", unowned.AccessPolicy)
	}

	seedMetadataSidecarObject(t, db, "MissingSidecar", "salesforce-crm-standard", "null", "", nil)
	err := repo.BatchSaveObjectMetadata(testWriterContext(t), []*models.ObjectMetadata{metadataSidecarObject("MissingSidecar")}, nil)
	if err == nil || !strings.Contains(err.Error(), "live projection and sidecar value are both empty") {
		t.Fatalf("BatchSaveObjectMetadata error = %v, want missing owned sidecar projection", err)
	}
	var stored string
	if err := db.QueryRow("SELECT AccessPolicyRecordMutationPackValue FROM _System_Object WHERE ApiName = 'MissingSidecar'").Scan(&stored); err != nil {
		t.Fatal(err)
	}
	if stored != "null" {
		t.Fatalf("failed sidecar write mutated stored value to %q", stored)
	}
}

func TestSchemaRepositoryFieldOwnershipOutcomesOnTiDB(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()
	createCanonicalSystemTablesForIntegration(t, db, constants.TableObject, constants.TableField)
	repo := NewSchemaRepository(db, nil)
	objectID := domainschema.GenerateObjectID("Issue")
	if _, err := db.Exec("INSERT INTO _System_Object (Id, ApiName, Label) VALUES (?, 'Issue', 'Issue')", objectID); err != nil {
		t.Fatal(err)
	}
	for _, statement := range []string{
		`INSERT INTO _System_Field (Id, ObjectId, ApiName, Label, Type, OwnerKind, OwnerPack, IsCustomized, IsDeleted)
		 VALUES ('field-pack', ?, 'Subject', 'Old Subject', 'string', 'pack', 'jira-like-sample', 0, 0)`,
		`INSERT INTO _System_Field (Id, ObjectId, ApiName, Label, Type, OwnerKind, OwnerPack, IsCustomized, IsDeleted)
		 VALUES ('field-ownerless', ?, 'LegacyNote__c', 'Legacy Note', 'string', '', NULL, 0, 0)`,
		`INSERT INTO _System_Field (Id, ObjectId, ApiName, Label, Type, OwnerKind, OwnerPack, IsCustomized, IsDeleted)
		 VALUES ('field-retained', ?, 'RetainedNote__c', 'Retained Note', 'string', 'pack_retained', 'jira-like-sample', 1, 0)`,
	} {
		if _, err := db.Exec(statement, objectID); err != nil {
			t.Fatal(err)
		}
	}

	for _, tc := range []struct {
		apiName, fieldID, label, wantKind string
		wantPack                          *string
	}{
		{apiName: "Subject", fieldID: "field-pack", label: "Customized Subject", wantKind: constants.FieldOwnerKindPack, wantPack: stringPointer("jira-like-sample")},
		{apiName: "LegacyNote__c", fieldID: "field-ownerless", label: "Adopted Legacy Note", wantKind: constants.FieldOwnerKindAdmin},
		{apiName: "RetainedNote__c", fieldID: "field-retained", label: "Customized Retained Note", wantKind: constants.FieldOwnerKindPackRetained, wantPack: stringPointer("jira-like-sample")},
	} {
		field := &models.FieldMetadata{APIName: tc.apiName, Label: tc.label, Type: constants.FieldTypeString}
		if err := repo.SaveFieldMetadataWithIDs(testWriterContext(t), field, objectID, "Issue", tc.fieldID, nil); err != nil {
			t.Fatalf("SaveFieldMetadataWithIDs %s: %v", tc.apiName, err)
		}
		assertMetadataFieldOwner(t, db, tc.fieldID, tc.label, tc.wantKind, tc.wantPack, true)
	}

	forgedPack := "forged-pack"
	forged := &models.FieldMetadata{APIName: "AdminNote__c", Label: "Admin Note", Type: constants.FieldTypeString, OwnerKind: constants.FieldOwnerKindPack, OwnerPack: &forgedPack}
	if err := repo.SaveFieldMetadataWithIDs(testWriterContext(t), forged, objectID, "Issue", "field-admin", nil); err != nil {
		t.Fatalf("SaveFieldMetadataWithIDs forged owner: %v", err)
	}
	assertMetadataFieldOwner(t, db, "field-admin", "Admin Note", constants.FieldOwnerKindAdmin, nil, false)
	if forged.OwnerKind != constants.FieldOwnerKindPack || forged.OwnerPack == nil || *forged.OwnerPack != forgedPack {
		t.Fatalf("caller field projection was mutated: %#v", forged)
	}
}

func metadataSidecarObject(apiName string) *models.ObjectMetadata {
	return &models.ObjectMetadata{APIName: apiName, Label: apiName, PluralLabel: apiName + "s", SharingModel: constants.SharingModelPublicRead}
}

func seedMetadataSidecarObject(t *testing.T, db *sql.DB, apiName, mutationOwner string, mutationValue interface{}, lifecycleOwner string, lifecycleValue interface{}) {
	t.Helper()
	if _, err := db.Exec(`INSERT INTO _System_Object
		(Id, ApiName, Label, AccessPolicyRecordMutationPackName, AccessPolicyRecordMutationPackValue, AccessPolicyLifecyclePackName, AccessPolicyLifecyclePackValue)
		VALUES (?, ?, ?, ?, ?, ?, ?)`,
		domainschema.GenerateObjectID(apiName), apiName, apiName,
		nullableMetadataString(mutationOwner), mutationValue,
		nullableMetadataString(lifecycleOwner), lifecycleValue,
	); err != nil {
		t.Fatalf("seed %s sidecar: %v", apiName, err)
	}
}

func nullableMetadataString(value string) interface{} {
	if value == "" {
		return nil
	}
	return value
}

func assertPersistedAccessPolicyContains(t *testing.T, db *sql.DB, apiName, fragment string) {
	t.Helper()
	var accessPolicy sql.NullString
	if err := db.QueryRow("SELECT AccessPolicy FROM _System_Object WHERE ApiName = ?", apiName).Scan(&accessPolicy); err != nil {
		t.Fatal(err)
	}
	if !accessPolicy.Valid || !strings.Contains(accessPolicy.String, fragment) {
		t.Fatalf("persisted %s AccessPolicy = %#v, want %q", apiName, accessPolicy, fragment)
	}
}

func assertMetadataFieldOwner(t *testing.T, db *sql.DB, fieldID, label, ownerKind string, ownerPack *string, wantCustomized bool) {
	t.Helper()
	var gotLabel, gotOwnerKind string
	var gotOwnerPack sql.NullString
	var customized bool
	if err := db.QueryRow("SELECT Label, OwnerKind, OwnerPack, IsCustomized FROM _System_Field WHERE Id = ?", fieldID).Scan(&gotLabel, &gotOwnerKind, &gotOwnerPack, &customized); err != nil {
		t.Fatal(err)
	}
	if gotLabel != label || gotOwnerKind != ownerKind || customized != wantCustomized {
		t.Fatalf("field %s = label:%q owner:%q customized:%t", fieldID, gotLabel, gotOwnerKind, customized)
	}
	if ownerPack == nil && gotOwnerPack.Valid || ownerPack != nil && (!gotOwnerPack.Valid || gotOwnerPack.String != *ownerPack) {
		t.Fatalf("field %s OwnerPack = %#v, want %#v", fieldID, gotOwnerPack, ownerPack)
	}
}

func stringPointer(value string) *string {
	return &value
}