//go:build integration

package controlplane

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

	"github.com/pingcap-inc/nexuscrm-controlplane/packs"
	"github.com/nexuscrm/db"
	"github.com/nexuscrm/records"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

func TestDefaultChildRowsResolverParity(t *testing.T) {
	ctx := context.Background()

	t.Run("rejects_invalid_same_install_engine_owned_field_before_mutation", func(t *testing.T) {
		conn, r, _ := setupDefaultChildRowsRuntime(t, ctx)
		defer conn.Close()

		pack := testControlplaneDefaultChildRowsAutoNumberPack(t)
		err := validateDefaultChildRowsInstallPreflight(ctx, r, pack)
		if err == nil || !strings.Contains(strings.ToLower(err.Error()), "engine-owned") {
			t.Fatalf("validateDefaultChildRowsInstallPreflight invalid defaultChildRows error = %v, want engine-owned rejection", err)
		}

		assertNoRows(t, conn.DB(), "rejected Parent__c object row", "SELECT COUNT(*) FROM `_System_Object` WHERE `ApiName` = 'Parent__c'")
		assertNoRows(t, conn.DB(), "rejected Child__c object row", "SELECT COUNT(*) FROM `_System_Object` WHERE `ApiName` = 'Child__c'")
		assertNoRows(t, conn.DB(), "rejected installed pack row", "SELECT COUNT(*) FROM `_System_InstalledPack` WHERE `Name` = 'jira-like-sample'")
	})

	t.Run("runtime_inserts_child_from_valid_normalized_contract", func(t *testing.T) {
		conn, r, sm := setupDefaultChildRowsRuntime(t, ctx)
		defer conn.Close()

		seedDefaultChildRowsRuntimeMetadata(t, ctx, conn)
		if err := r.RefreshCache(ctx); err != nil {
			t.Fatalf("refresh runner metadata after seed: %v", err)
		}
		if err := runInsertRulesValidators(ctx, r); err != nil {
			t.Fatalf("runInsertRulesValidators after seed: %v", err)
		}
		if err := sm.RefreshMetadataCache(ctx); err != nil {
			t.Fatalf("refresh metadata after seed: %v", err)
		}

		admin := systemAdminSession()
		insertCtx := constants.WithSecurityContext(ctx, constants.SystemContext, "default child rows parity test")
		parent, err := sm.Persistence().Insert(insertCtx, "ParentRecord", models.SObject{
			"Name": "Parent single",
		}, admin)
		if err != nil {
			t.Fatalf("insert ParentRecord with defaultChildRows: %v", err)
		}
		parentID, _ := parent["Id"].(string)
		if parentID == "" {
			t.Fatal("insert Parent__c returned empty Id")
		}

		assertDefaultChildRowsMetadataStored(t, ctx, conn, "ParentRecord")
		assertDefaultChildRowsChildCount(t, conn.DB(), parentID, 1)
		assertDefaultChildRowsChildFields(t, ctx, conn, parentID)
	})

	t.Run("runtime_bulk_insert_inserts_children_from_valid_normalized_contract", func(t *testing.T) {
		conn, r, sm := setupDefaultChildRowsRuntime(t, ctx)
		defer conn.Close()

		seedDefaultChildRowsRuntimeMetadata(t, ctx, conn)
		if err := r.RefreshCache(ctx); err != nil {
			t.Fatalf("refresh runner metadata after seed: %v", err)
		}
		if err := runInsertRulesValidators(ctx, r); err != nil {
			t.Fatalf("runInsertRulesValidators after seed: %v", err)
		}
		if err := sm.RefreshMetadataCache(ctx); err != nil {
			t.Fatalf("refresh metadata after seed: %v", err)
		}

		admin := systemAdminSession()
		insertCtx := constants.WithSecurityContext(ctx, constants.SystemContext, "default child rows parity test")
		result, err := sm.Persistence().BulkInsert(insertCtx, "ParentRecord", []models.SObject{
			{"Name": "Parent bulk 1"},
			{"Name": "Parent bulk 2"},
		}, admin, records.BulkInsertOptions{})
		if err != nil {
			t.Fatalf("bulk insert ParentRecord with defaultChildRows: %v", err)
		}
		if result.SuccessCount != 2 || result.FailedCount != 0 || len(result.Errors) != 0 {
			t.Fatalf("bulk insert result = %#v, want two successes and zero failures", result)
		}

		if got := queryCount(t, conn.DB(), "SELECT COUNT(*) FROM `ChildRecord`"); got != 2 {
			t.Fatalf("ChildRecord row count after bulk insert = %d, want 2", got)
		}
	})
}

func TestVerifyPackObjectDefaultChildRowsResolverFirstOwnership(t *testing.T) {
	ctx := context.Background()

	t.Run("defaults_only_customized_required_field_override_passes", func(t *testing.T) {
		conn, _, _ := setupDefaultChildRowsRuntime(t, ctx)
		defer conn.Close()

		seedDefaultChildRowsRuntimeMetadata(t, ctx, conn)
		addDefaultChildRowsTargetField(t, ctx, conn, "fld-child-requiredtext", "RequiredText", true)
		setDefaultChildRowsLiveOverride(t, ctx, conn, true, []models.DefaultChildRow{{
			TargetObject:      "ChildRecord",
			TargetLookupField: "ParentRecordId",
			FieldValues: models.SObject{
				"Name":         "Customized child",
				"SortOrder":    1,
				"RequiredText": "customized",
			},
		}})

		err := packs.VerifyPackObjectDefaultChildRows(conn.DB(), "test-pack", defaultChildRowsPackSpecs([]models.DefaultChildRow{{
			TargetObject:      "ChildRecord",
			TargetLookupField: "ParentRecordId",
			FieldValues:       models.SObject{"Name": "Seeded child", "SortOrder": 1},
		}}), packs.ManageabilityDefaultsOnly)
		if err != nil {
			t.Fatalf("VerifyPackObjectDefaultChildRows defaults_only customized required-field override = %v, want nil", err)
		}
	})

	t.Run("defaults_only_customized_invalid_source_target_passes", func(t *testing.T) {
		conn, _, _ := setupDefaultChildRowsRuntime(t, ctx)
		defer conn.Close()

		seedDefaultChildRowsRuntimeMetadata(t, ctx, conn)
		setDefaultChildRowsLiveOverride(t, ctx, conn, true, []models.DefaultChildRow{{
			TargetObject:      "ChildRecord",
			TargetLookupField: "ParentRecordId",
			FieldValues:       models.SObject{"Name": "Customized child", "SortOrder": 1},
		}})

		err := packs.VerifyPackObjectDefaultChildRows(conn.DB(), "test-pack", defaultChildRowsPackSpecs([]models.DefaultChildRow{{
			TargetObject:      "MissingChildRecord",
			TargetLookupField: "ParentRecordId",
			FieldValues:       models.SObject{"Name": "Pack source child", "SortOrder": 1},
		}}), packs.ManageabilityDefaultsOnly)
		if err != nil {
			t.Fatalf("VerifyPackObjectDefaultChildRows defaults_only customized invalid source target = %v, want nil", err)
		}
	})

	t.Run("locked_customized_override_still_fails_exact_match", func(t *testing.T) {
		conn, _, _ := setupDefaultChildRowsRuntime(t, ctx)
		defer conn.Close()

		seedDefaultChildRowsRuntimeMetadata(t, ctx, conn)
		setDefaultChildRowsLiveOverride(t, ctx, conn, true, []models.DefaultChildRow{{
			TargetObject:      "ChildRecord",
			TargetLookupField: "ParentRecordId",
			FieldValues:       models.SObject{"Name": "Customized child", "SortOrder": 2},
		}})

		err := packs.VerifyPackObjectDefaultChildRows(conn.DB(), "test-pack", defaultChildRowsPackSpecs([]models.DefaultChildRow{{
			TargetObject:      "ChildRecord",
			TargetLookupField: "ParentRecordId",
			FieldValues:       models.SObject{"Name": "Seeded child", "SortOrder": 1},
		}}), packs.ManageabilityLocked)
		if err == nil || !strings.Contains(err.Error(), "DefaultChildRows") {
			t.Fatalf("VerifyPackObjectDefaultChildRows locked customized override = %v, want exact-match failure", err)
		}
	})
}

func setupDefaultChildRowsRuntime(t *testing.T, ctx context.Context) (*database.TiDBConnection, *Runner, ServiceGraph) {
	t.Helper()

	cfg, cleanup := newTemporaryIntegrationDatabase(t)
	t.Cleanup(cleanup)
	setDatabaseEnvFromConfig(t, cfg)

	conn := openIntegrationConnection(t, cfg)
	if err := InitL0(ctx, conn); err != nil {
		_ = conn.Close()
		t.Fatalf("init-l0: %v", err)
	}
	_ = conn.Close()

	conn = openIntegrationConnection(t, cfg)
	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)
	}

	// Reuse the Runner's service graph (built via the public BuildServiceGraph
	// seam) as the record-setup graph; it is constructed from the same
	// infrastructure deps a standalone ServiceManager would be.
	sm := r.SvcMgr
	if err := sm.RefreshMetadataCache(ctx); err != nil {
		t.Fatalf("runtime metadata refresh: %v", err)
	}
	return conn, r, sm
}

func seedDefaultChildRowsRuntimeMetadata(t *testing.T, ctx context.Context, conn *database.TiDBConnection) {
	t.Helper()

	stmts := []string{
		`CREATE TABLE IF NOT EXISTS ParentRecord (
			Id VARCHAR(255) NOT NULL PRIMARY KEY,
			OwnerId VARCHAR(255) NULL,
			CreatedById VARCHAR(255) NULL,
			LastModifiedById VARCHAR(255) NULL,
			CreatedDate DATETIME NOT NULL,
			LastModifiedDate DATETIME NOT NULL,
			SystemModstamp DATETIME NOT NULL,
			IsDeleted TINYINT(1) NOT NULL DEFAULT 0,
			Name VARCHAR(255) NOT NULL
		)`,
		`CREATE TABLE IF NOT EXISTS ChildRecord (
			Id VARCHAR(255) NOT NULL PRIMARY KEY,
			OwnerId VARCHAR(255) NULL,
			CreatedById VARCHAR(255) NULL,
			LastModifiedById VARCHAR(255) NULL,
			CreatedDate DATETIME NOT NULL,
			LastModifiedDate DATETIME NOT NULL,
			SystemModstamp DATETIME NOT NULL,
			IsDeleted TINYINT(1) NOT NULL DEFAULT 0,
			Name VARCHAR(255) NOT NULL,
			ParentRecordId VARCHAR(255) NULL,
			SortOrder DECIMAL(65,30) NOT NULL,
			Status VARCHAR(255) NOT NULL DEFAULT 'Draft'
		)`,
		`INSERT INTO _System_Table (Id, TableName, TableType, Category, Description, IsManaged, SchemaVersion, CreatedBy, CreatedDate, LastModifiedDate, IsDeleted)
		 VALUES ('tbl-parent-record', 'ParentRecord', 'standard_object', 'jira', 'Parent record', 1, '1.0.0', 'test', NOW(), NOW(), 0)`,
		`INSERT INTO _System_Table (Id, TableName, TableType, Category, Description, IsManaged, SchemaVersion, CreatedBy, CreatedDate, LastModifiedDate, IsDeleted)
		 VALUES ('tbl-child-record', 'ChildRecord', 'standard_object', 'jira', 'Child record', 1, '1.0.0', 'test', NOW(), NOW(), 0)`,
		`INSERT INTO _System_Object
		 (Id, ApiName, KeyPrefix, TableType, Label, PluralLabel, Description, IsCustom, Searchable, SharingModel, IsCustomized, EnableHierarchySharing, DefaultChildRows, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('obj-parent-record', 'ParentRecord', 'a90', 'standard_object', 'Parent', 'Parents', 'Parent record', 0, 1, 'Private', 0, 0,
		  '[{"targetObject":"ChildRecord","targetLookupField":"ParentRecordId","fieldValues":{"Name":"Seeded child","SortOrder":1}}]', 0, NOW(), NOW())`,
		`INSERT INTO _System_Object
		 (Id, ApiName, KeyPrefix, TableType, Label, PluralLabel, Description, IsCustom, Searchable, SharingModel, IsCustomized, EnableHierarchySharing, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('obj-child-record', 'ChildRecord', 'a91', 'standard_object', 'Child', 'Children', 'Child record', 0, 1, 'Private', 0, 0, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsSystem, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-parent-id', 'obj-parent-record', 'Id', 'Record ID', 'id', 1, 1, 0, 0, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsSystem, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-parent-createdby', 'obj-parent-record', 'CreatedById', 'Created By', 'reference', 1, 1, 0, 0, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsSystem, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-parent-createddate', 'obj-parent-record', 'CreatedDate', 'Created Date', 'datetime', 1, 1, 0, 0, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsSystem, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-parent-lastmodifiedby', 'obj-parent-record', 'LastModifiedById', 'Last Modified By', 'reference', 1, 1, 0, 0, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsSystem, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-parent-lastmodifieddate', 'obj-parent-record', 'LastModifiedDate', 'Last Modified Date', 'datetime', 1, 1, 0, 0, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsSystem, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-parent-systemmodstamp', 'obj-parent-record', 'SystemModstamp', 'System Modstamp', 'datetime', 1, 1, 0, 0, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsSystem, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-parent-isdeleted', 'obj-parent-record', 'IsDeleted', 'Deleted', 'boolean', 1, 1, 0, 0, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsNameField, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-parent-name', 'obj-parent-record', 'Name', 'Name', 'string', 1, 1, 1, 1, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsSystem, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-child-id', 'obj-child-record', 'Id', 'Record ID', 'id', 1, 1, 0, 0, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsSystem, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-child-createdby', 'obj-child-record', 'CreatedById', 'Created By', 'reference', 1, 1, 0, 0, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsSystem, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-child-createddate', 'obj-child-record', 'CreatedDate', 'Created Date', 'datetime', 1, 1, 0, 0, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsSystem, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-child-lastmodifiedby', 'obj-child-record', 'LastModifiedById', 'Last Modified By', 'reference', 1, 1, 0, 0, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsSystem, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-child-lastmodifieddate', 'obj-child-record', 'LastModifiedDate', 'Last Modified Date', 'datetime', 1, 1, 0, 0, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsSystem, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-child-systemmodstamp', 'obj-child-record', 'SystemModstamp', 'System Modstamp', 'datetime', 1, 1, 0, 0, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsSystem, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-child-isdeleted', 'obj-child-record', 'IsDeleted', 'Deleted', 'boolean', 1, 1, 0, 0, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsNameField, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-child-name', 'obj-child-record', 'Name', 'Name', 'string', 1, 1, 1, 1, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsNameField, Createable, Updateable, ReferenceTo, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-child-parent', 'obj-child-record', 'ParentRecordId', 'Parent', 'reference', 0, 0, 1, 1, '["ParentRecord"]', 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsNameField, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-child-sortorder', 'obj-child-record', 'SortOrder', 'Sort Order', 'double', 1, 0, 1, 1, 0, NOW(), NOW())`,
		`INSERT INTO _System_Field
		 (Id, ObjectId, ApiName, Label, Type, Required, IsNameField, Createable, Updateable, DefaultValue, IsDeleted, CreatedDate, LastModifiedDate)
		 VALUES
		 ('fld-child-status', 'obj-child-record', 'Status', 'Status', 'string', 1, 0, 1, 1, 'Draft', 0, NOW(), NOW())`,
	}
	for _, stmt := range stmts {
		if _, err := conn.DB().ExecContext(ctx, stmt); err != nil {
			t.Fatalf("seed defaultChildRows runtime metadata: %v\nstmt: %s", err, stmt)
		}
	}
}

func assertDefaultChildRowsMetadataStored(t *testing.T, ctx context.Context, conn *database.TiDBConnection, objectAPIName string) {
	t.Helper()

	var stored sql.NullString
	if err := conn.DB().QueryRowContext(ctx, "SELECT `DefaultChildRows` FROM `_System_Object` WHERE `ApiName` = ?", objectAPIName).Scan(&stored); err != nil {
		t.Fatalf("query stored DefaultChildRows for %s: %v", objectAPIName, err)
	}
	if !stored.Valid ||
		!strings.Contains(stored.String, `"ChildRecord"`) ||
		!strings.Contains(stored.String, `"ParentRecordId"`) {
		t.Fatalf("_System_Object.DefaultChildRows for %s = %+v, want stored declaration", objectAPIName, stored)
	}
}

func assertDefaultChildRowsChildCount(t *testing.T, db *sql.DB, parentID string, want int) {
	t.Helper()
	if got := queryCount(t, db, "SELECT COUNT(*) FROM `ChildRecord` WHERE `ParentRecordId` = ?", parentID); got != want {
		t.Fatalf("ChildRecord rows for parent %s = %d, want %d", parentID, got, want)
	}
}

func assertDefaultChildRowsChildFields(t *testing.T, ctx context.Context, conn *database.TiDBConnection, parentID string) {
	t.Helper()

	var lookupID, name, sortOrder, status sql.NullString
	if err := conn.DB().QueryRowContext(ctx,
		"SELECT CAST(ParentRecordId AS CHAR), CAST(Name AS CHAR), CAST(SortOrder AS CHAR), CAST(Status AS CHAR) "+
			"FROM ChildRecord WHERE ParentRecordId = ? LIMIT 1",
		parentID,
	).Scan(&lookupID, &name, &sortOrder, &status); err != nil {
		t.Fatalf("query inserted ChildRecord row: %v", err)
	}
	if !lookupID.Valid || lookupID.String != parentID {
		t.Fatalf("ChildRecord.ParentRecordId = %+v, want %s", lookupID, parentID)
	}
	if !name.Valid || name.String != "Seeded child" {
		t.Fatalf("ChildRecord.Name = %+v, want Seeded child", name)
	}
	if !sortOrder.Valid || !strings.HasPrefix(sortOrder.String, "1") {
		t.Fatalf("ChildRecord.SortOrder = %+v, want decimal 1", sortOrder)
	}
	if !status.Valid || status.String != "Draft" {
		t.Fatalf("ChildRecord.Status = %+v, want Draft", status)
	}
}

func addDefaultChildRowsTargetField(t *testing.T, ctx context.Context, conn *database.TiDBConnection, fieldID, apiName string, required bool) {
	t.Helper()

	if _, err := conn.DB().ExecContext(ctx, `
		INSERT INTO _System_Field
			(Id, ObjectId, ApiName, Label, Type, Required, IsNameField, Createable, Updateable, IsDeleted, CreatedDate, LastModifiedDate)
		VALUES
			(?, 'obj-child-record', ?, ?, 'string', ?, 0, 1, 1, 0, NOW(), NOW())
	`, fieldID, apiName, apiName, required); err != nil {
		t.Fatalf("insert child target field %s: %v", apiName, err)
	}
}

func setDefaultChildRowsLiveOverride(t *testing.T, ctx context.Context, conn *database.TiDBConnection, customized bool, rows []models.DefaultChildRow) {
	t.Helper()

	raw, err := json.Marshal(rows)
	if err != nil {
		t.Fatalf("marshal live DefaultChildRows override: %v", err)
	}
	if _, err := conn.DB().ExecContext(ctx, `
		UPDATE _System_Object
		SET DefaultChildRows = ?,
		    DefaultChildRowsCustomized = ?,
		    LastModifiedDate = NOW()
		WHERE ApiName = 'ParentRecord'
	`, string(raw), customized); err != nil {
		t.Fatalf("update live DefaultChildRows override: %v", err)
	}
}

func defaultChildRowsPackSpecs(rows []models.DefaultChildRow) []packs.ObjectSpec {
	return []packs.ObjectSpec{{
		APIName:          "ParentRecord",
		Label:            "Parent",
		PluralLabel:      "Parents",
		TableType:        string(constants.TableTypeStandardObject),
		SharingModel:     string(constants.SharingModelPrivate),
		DefaultChildRows: rows,
	}}
}