//go:build integration

package controlplane

import (
	"context"
	"database/sql"
	"os"
	"path/filepath"
	"strings"
	"testing"
	"time"

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

func TestSchemaReadinessOutcomesOnTiDB(t *testing.T) {
	ctx, conn := openRemainingTestQualityDatabase(t)
	db := conn.DB()

	verifySchemaReadinessOutcome(t, conn)

	t.Run("missing managed column", func(t *testing.T) {
		mustExecRemainingTestQuality(t, ctx, db, "hide ProjectionOnly", `ALTER TABLE _System_ObjectFieldMapping
			RENAME COLUMN ProjectionOnly TO ProjectionOnlyMissing`)
		t.Cleanup(func() {
			_, _ = db.ExecContext(context.Background(), `ALTER TABLE _System_ObjectFieldMapping
				RENAME COLUMN ProjectionOnlyMissing TO ProjectionOnly`)
		})
		assertRemainingTestQualityError(t, bootstrapimpl.VerifySchemaReadiness(conn),
			"_System_ObjectFieldMapping.ProjectionOnly", "cmd/setup apply")
		mustExecRemainingTestQuality(t, ctx, db, "restore ProjectionOnly", `ALTER TABLE _System_ObjectFieldMapping
			RENAME COLUMN ProjectionOnlyMissing TO ProjectionOnly`)
		verifySchemaReadinessOutcome(t, conn)
	})

	t.Run("managed field metadata drift", func(t *testing.T) {
		mustExecRemainingTestQuality(t, ctx, db, "require User.FirstName metadata", `UPDATE _System_Field f
			JOIN _System_Object o ON o.Id = f.ObjectId
			SET f.Required = 1
			WHERE o.ApiName = 'User' AND f.ApiName = 'FirstName'`)
		t.Cleanup(func() {
			_, _ = db.ExecContext(context.Background(), `UPDATE _System_Field f
				JOIN _System_Object o ON o.Id = f.ObjectId
				SET f.Required = 0
				WHERE o.ApiName = 'User' AND f.ApiName = 'FirstName'`)
		})
		assertRemainingTestQualityError(t, bootstrapimpl.VerifySchemaReadiness(conn),
			"User.FirstName", "Required = 1", "cmd/setup apply")
		mustExecRemainingTestQuality(t, ctx, db, "restore User.FirstName metadata", `UPDATE _System_Field f
			JOIN _System_Object o ON o.Id = f.ObjectId
			SET f.Required = 0
			WHERE o.ApiName = 'User' AND f.ApiName = 'FirstName'`)
		verifySchemaReadinessOutcome(t, conn)
	})

	t.Run("record type canonical identity drift", func(t *testing.T) {
		mustExecRemainingTestQuality(t, ctx, db, "insert non-canonical record type", `INSERT INTO _System_RecordType
			(Id, ObjectId, Name, DeveloperName, SobjectType, IsActive, IsMaster, IsDeleted, CreatedDate, LastModifiedDate)
			SELECT 'readiness-record-type-drift', Id, 'Readiness Probe', 'Readiness_Probe', 'WrongObject', 1, 0, 0, NOW(), NOW()
			FROM _System_Object WHERE ApiName = 'User'`)
		t.Cleanup(func() {
			_, _ = db.ExecContext(context.Background(), `DELETE FROM _System_RecordType WHERE Id = 'readiness-record-type-drift'`)
		})
		assertRemainingTestQualityError(t, bootstrapimpl.VerifySchemaReadiness(conn),
			"_System_RecordType", "SobjectType", "cmd/setup apply")
		mustExecRemainingTestQuality(t, ctx, db, "delete non-canonical record type",
			`DELETE FROM _System_RecordType WHERE Id = 'readiness-record-type-drift'`)
		verifySchemaReadinessOutcome(t, conn)
	})
}

func TestStandardFlowLifecycleOutcomesOnTiDB(t *testing.T) {
	ctx, conn := openRemainingTestQualityDatabase(t)
	db := conn.DB()
	const ownerPack = "standard-flow-outcomes"

	active := map[string][]byte{
		"Outcome_Flow.flow.json": []byte(`{"label":"Outcome Flow","processType":"AutoLaunchedFlow","status":"Active"}`),
	}
	draft := map[string][]byte{
		"Outcome_Flow.flow.json": []byte(`{"label":"Outcome Flow","processType":"AutoLaunchedFlow","status":"Draft"}`),
	}

	if err := packs.InitializeStandardFlowsFromPayloadsForOwner(db, active, ownerPack); err != nil {
		t.Fatalf("initial standard flow install: %v", err)
	}
	if err := packs.VerifyStandardFlowsFromPayloadsForOwner(db, active, ownerPack); err != nil {
		t.Fatalf("verify initial standard flow: %v", err)
	}
	definitionID, activeVersionID := assertRemainingStandardFlowState(t, db, "Outcome_Flow", ownerPack, true, false)
	assertRemainingStandardFlowVersionCount(t, db, definitionID, 1)

	if err := packs.InitializeStandardFlowsFromPayloadsForOwner(db, active, ownerPack); err != nil {
		t.Fatalf("idempotent standard flow install: %v", err)
	}
	assertRemainingStandardFlowVersionCount(t, db, definitionID, 1)

	var shippedMetadata string
	if err := db.QueryRowContext(ctx, `SELECT Metadata FROM Flow WHERE Id = ?`, activeVersionID).Scan(&shippedMetadata); err != nil {
		t.Fatalf("read shipped standard flow metadata: %v", err)
	}
	mustExecRemainingTestQuality(t, ctx, db, "drift active standard flow metadata",
		`UPDATE Flow SET Metadata = '{"label":"Drifted","processType":"AutoLaunchedFlow"}' WHERE Id = ?`, activeVersionID)
	assertRemainingTestQualityError(t, packs.VerifyStandardFlowsFromPayloadsForOwner(db, active, ownerPack),
		"metadata differs from shipped metadata")
	mustExecRemainingTestQuality(t, ctx, db, "restore active standard flow metadata",
		`UPDATE Flow SET Metadata = ? WHERE Id = ?`, shippedMetadata, activeVersionID)

	if err := packs.InitializeStandardFlowsFromPayloadsForOwner(db, draft, ownerPack); err != nil {
		t.Fatalf("converge standard flow to Draft: %v", err)
	}
	assertRemainingStandardFlowState(t, db, "Outcome_Flow", ownerPack, false, false)
	if err := packs.VerifyStandardFlowsFromPayloadsForOwner(db, draft, ownerPack); err != nil {
		t.Fatalf("verify authored Draft: %v", err)
	}

	if err := packs.InitializeStandardFlowsFromPayloadsForOwner(db, active, ownerPack); err != nil {
		t.Fatalf("reactivate existing standard flow version: %v", err)
	}
	_, reactivatedVersionID := assertRemainingStandardFlowState(t, db, "Outcome_Flow", ownerPack, true, false)
	if reactivatedVersionID != activeVersionID {
		t.Fatalf("reactivated version = %q, want original %q", reactivatedVersionID, activeVersionID)
	}
	assertRemainingStandardFlowVersionCount(t, db, definitionID, 2)

	mustExecRemainingTestQuality(t, ctx, db, "customize same-owner flow definition",
		`UPDATE FlowDefinition SET IsCustomized = 1 WHERE Id = ?`, definitionID)
	if err := packs.InitializeStandardFlowsFromPayloadsForOwner(db, active, ownerPack); err != nil {
		t.Fatalf("preserve same-owner customized flow: %v", err)
	}
	assertRemainingStandardFlowState(t, db, "Outcome_Flow", ownerPack, true, true)
	assertRemainingStandardFlowVersionCount(t, db, definitionID, 2)

	mustExecRemainingTestQuality(t, ctx, db, "insert ownerless customized definition", `INSERT INTO FlowDefinition
		(Id, DeveloperName, MasterLabel, Description, ProcessType, IsDeleted, IsCustomized, CreatedDate, LastModifiedDate)
		VALUES ('flowdef-ownerless-outcome', 'Ownerless_Outcome', 'Ownerless Outcome', '', 'AutoLaunchedFlow', 0, 1, NOW(), NOW())`)
	ownerless := map[string][]byte{
		"Ownerless_Outcome.flow.json": []byte(`{"label":"Ownerless Outcome","processType":"AutoLaunchedFlow"}`),
	}
	assertRemainingTestQualityError(t,
		packs.InitializeStandardFlowsFromPayloadsForOwner(db, ownerless, ownerPack), `OwnerPack = ""`)
}

func TestRecordTypeOwnershipOutcomesOnTiDB(t *testing.T) {
	ctx, conn := openRemainingTestQualityDatabase(t)
	db := conn.DB()
	const ownerPack = "record-type-outcomes"

	mustExecRemainingTestQuality(t, ctx, db, "insert record type probe object", `INSERT INTO _System_Object
		(Id, ApiName, Label, PluralLabel, IsDeleted, CreatedDate, LastModifiedDate)
		VALUES ('record-type-outcome-object', 'RecordTypeOutcome', 'Record Type Outcome', 'Record Type Outcomes', 0, NOW(), NOW())`)

	apply := func(t *testing.T, payload string, wantError ...string) {
		t.Helper()
		err := packs.ApplyRecordTypesForOwner(db, []byte(payload), ownerPack)
		if len(wantError) == 0 {
			if err != nil {
				t.Fatalf("ApplyRecordTypesForOwner: %v", err)
			}
			return
		}
		assertRemainingTestQualityError(t, err, wantError...)
	}

	apply(t, `{"recordTypes":[{"objectApiName":"RecordTypeOutcome","types":[{"name":"A-B"}]}]}`)
	var recordTypeID, name, developerName, sobjectType, storedOwner string
	if err := db.QueryRowContext(ctx, `SELECT Id, Name, DeveloperName, SobjectType, COALESCE(OwnerPack, '')
		FROM _System_RecordType WHERE ObjectId = 'record-type-outcome-object' AND IsDeleted = 0`).
		Scan(&recordTypeID, &name, &developerName, &sobjectType, &storedOwner); err != nil {
		t.Fatalf("read created record type: %v", err)
	}
	if name != "A-B" || developerName == "" || sobjectType != "RecordTypeOutcome" || storedOwner != ownerPack {
		t.Fatalf("created record type = id:%q name:%q developer:%q sobject:%q owner:%q", recordTypeID, name, developerName, sobjectType, storedOwner)
	}

	apply(t, `{"recordTypes":[{"objectApiName":"RecordTypeOutcome","types":[{"name":"AB"}]}]}`)
	var renamedID, renamedName, renamedDeveloperName string
	if err := db.QueryRowContext(ctx, `SELECT Id, Name, DeveloperName FROM _System_RecordType
		WHERE ObjectId = 'record-type-outcome-object' AND IsDeleted = 0`).
		Scan(&renamedID, &renamedName, &renamedDeveloperName); err != nil {
		t.Fatalf("read renamed record type: %v", err)
	}
	if renamedID != recordTypeID || renamedName != "AB" || renamedDeveloperName != developerName {
		t.Fatalf("renamed record type = id:%q name:%q developer:%q, want id:%q name:AB developer:%q",
			renamedID, renamedName, renamedDeveloperName, recordTypeID, developerName)
	}

	mustExecRemainingTestQuality(t, ctx, db, "insert ownerless collision", `INSERT INTO _System_RecordType
		(Id, ObjectId, Name, DeveloperName, SobjectType, IsActive, IsMaster, IsDeleted, CreatedDate, LastModifiedDate)
		VALUES ('record-type-ownerless-collision', 'record-type-outcome-object', 'Ownerless', 'Ownerless', 'RecordTypeOutcome', 1, 0, 0, NOW(), NOW())`)
	apply(t, `{"recordTypes":[{"objectApiName":"RecordTypeOutcome","types":[{"name":"Ownerless"}]}]}`, "ownerless row")

	mustExecRemainingTestQuality(t, ctx, db, "insert deleted tombstone", `INSERT INTO _System_RecordType
		(Id, ObjectId, Name, DeveloperName, SobjectType, IsActive, IsMaster, IsDeleted, CreatedDate, LastModifiedDate)
		VALUES ('record-type-deleted-tombstone', 'record-type-outcome-object', 'Reclaimed', 'Reclaimed', 'RecordTypeOutcome', 0, 0, 1, NOW(), NOW())`)
	apply(t, `{"recordTypes":[{"objectApiName":"RecordTypeOutcome","types":[{"name":"Reclaimed"}]}]}`)
	var reclaimedOwner string
	var reclaimedDeleted bool
	if err := db.QueryRowContext(ctx, `SELECT COALESCE(OwnerPack, ''), IsDeleted FROM _System_RecordType
		WHERE Id = 'record-type-deleted-tombstone'`).Scan(&reclaimedOwner, &reclaimedDeleted); err != nil {
		t.Fatalf("read reclaimed record type: %v", err)
	}
	if reclaimedOwner != ownerPack || reclaimedDeleted {
		t.Fatalf("reclaimed record type owner=%q deleted=%t, want owner=%q deleted=false", reclaimedOwner, reclaimedDeleted, ownerPack)
	}

	apply(t, `{"recordTypes":[{"objectApiName":"RecordTypeOutcome","types":[{"id":"record-type-deleted-tombstone","name":"Different Identity"}]}]}`,
		"different canonical identity")
}

func TestLegacyPageLayoutIDBackfillOutcomesOnTiDB(t *testing.T) {
	ctx, conn := openRemainingTestQualityDatabase(t)
	db := conn.DB()

	const (
		packName              = "remaining-layout-outcome"
		layoutName            = "Outcome Layout"
		canonicalLayoutID     = "curated_User_default"
		legacyLayoutID        = "legacy-user-default-outcome"
		canonicalAssignmentID = "curated_User_assignment_wildcard"
		legacyAssignmentID    = "legacy-user-assignment-outcome"
	)

	packRoot := filepath.Join(t.TempDir(), packName)
	if err := os.MkdirAll(filepath.Join(packRoot, "layouts"), 0o755); err != nil {
		t.Fatalf("create page-layout outcome pack: %v", err)
	}
	manifest := `{
  "manifestFormatVersion": "1",
  "name": "remaining-layout-outcome",
  "displayName": "Remaining Layout Outcome",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {"min": "0.2.0", "maxExclusive": "0.3.0"},
  "dependencies": [],
  "components": [{
    "kind": "page_layout",
    "businessKey": "User/default",
    "manageability": "defaults_only",
    "path": "layouts/user.json"
  }]
}`
	payload := `{
  "objectApiName": "User",
  "layoutName": "Outcome Layout",
  "type": "Detail",
  "isDefault": true,
  "sections": []
}`
	if err := os.WriteFile(filepath.Join(packRoot, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write page-layout outcome manifest: %v", err)
	}
	if err := os.WriteFile(filepath.Join(packRoot, "layouts", "user.json"), []byte(payload), 0o644); err != nil {
		t.Fatalf("write page-layout outcome payload: %v", err)
	}
	mustExecRemainingTestQuality(t, ctx, db, "seed active page-layout outcome pack", `INSERT INTO _System_InstalledPack
		(Id, Name, PackInstallId, Category, Status, PackVersion, ManifestFormatVersion, SourceKind, SourceURI, InstallSource, IsDeleted)
		VALUES ('installed-remaining-layout-outcome', ?, 'remaining-layout-outcome-install', 'app-pack', 'active', '0.1.0', '1', 'local_path', ?, 'apply', 0)`,
		packName, packRoot)

	mustExecRemainingTestQuality(t, ctx, db, "disable fixture foreign keys", `SET FOREIGN_KEY_CHECKS = 0`)
	t.Cleanup(func() { _, _ = db.ExecContext(context.Background(), `SET FOREIGN_KEY_CHECKS = 1`) })
	mustExecRemainingTestQuality(t, ctx, db, "seed legacy source-backed layout", `INSERT INTO _System_PageLayout
		(Id, ObjectApiName, LayoutName, Type, IsDefault, IsDeleted, Config, Source, OwnerPack, IsCustomized, CreatedDate, LastModifiedDate)
		VALUES (?, 'User', ?, 'record', 1, 0,
			JSON_OBJECT('id', ?, 'objectApiName', 'User', 'layoutName', ?, 'isDefault', true, 'sections', JSON_ARRAY()),
			'curated', ?, 0, NOW(), NOW())`, legacyLayoutID, layoutName, legacyLayoutID, layoutName, packName)
	mustExecRemainingTestQuality(t, ctx, db, "seed legacy wildcard assignment", `INSERT INTO _System_LayoutAssignment
		(Id, ObjectApiName, LayoutId, Specificity, RouteSource, OwnerPack, CreatedDate, LastModifiedDate)
		VALUES (?, 'User', ?, 0, 'managed_seed', ?, NOW(), NOW())`, legacyAssignmentID, legacyLayoutID, packName)
	mustExecRemainingTestQuality(t, ctx, db, "seed legacy profile layout reference", `INSERT INTO ProfileLayout
		(Id, ProfileId, ObjectApiName, LayoutId, CreatedDate, LastModifiedDate)
		VALUES ('profile-layout-user-outcome', ?, 'User', ?, NOW(), NOW())`, constants.ProfileSystemAdmin, legacyLayoutID)
	mustExecRemainingTestQuality(t, ctx, db, "seed legacy page layout history reference", `INSERT INTO _System_PageLayoutHistory
		(Id, LayoutId, VersionNumber, Config, ChangeReason, CreatedDate, LastModifiedDate)
		VALUES ('page-layout-history-user-outcome', ?, 1, JSON_OBJECT('id', ?), 'outcome fixture', NOW(), NOW())`,
		legacyLayoutID, legacyLayoutID)
	mustExecRemainingTestQuality(t, ctx, db, "restore fixture foreign keys", `SET FOREIGN_KEY_CHECKS = 1`)

	updated, err := packs.BackfillLegacySourceBackedCuratedDefaultLayoutIDsFromActivePacks(ctx, db)
	if err != nil {
		t.Fatalf("backfill legacy source-backed page layout IDs: %v", err)
	}
	if updated != 1 {
		t.Fatalf("backfill updated = %d, want 1", updated)
	}

	var gotLayoutName, gotConfigID string
	if err := db.QueryRowContext(ctx, `SELECT LayoutName, JSON_UNQUOTE(JSON_EXTRACT(Config, '$.id'))
		FROM _System_PageLayout WHERE Id = ?`, canonicalLayoutID).Scan(&gotLayoutName, &gotConfigID); err != nil {
		t.Fatalf("read canonicalized User layout: %v", err)
	}
	if gotLayoutName != layoutName || gotConfigID != canonicalLayoutID {
		t.Fatalf("canonicalized User layout name=%q config.id=%q, want name=%q config.id=%q",
			gotLayoutName, gotConfigID, layoutName, canonicalLayoutID)
	}
	for label, query := range map[string]string{
		"legacy layout":            `SELECT COUNT(*) FROM _System_PageLayout WHERE Id = '` + legacyLayoutID + `'`,
		"canonical profile ref":    `SELECT COUNT(*) FROM ProfileLayout WHERE Id = 'profile-layout-user-outcome' AND LayoutId = '` + canonicalLayoutID + `'`,
		"canonical history ref":    `SELECT COUNT(*) FROM _System_PageLayoutHistory WHERE Id = 'page-layout-history-user-outcome' AND LayoutId = '` + canonicalLayoutID + `'`,
		"canonical assignment/ref": `SELECT COUNT(*) FROM _System_LayoutAssignment WHERE Id = '` + canonicalAssignmentID + `' AND LayoutId = '` + canonicalLayoutID + `'`,
	} {
		var count int
		if err := db.QueryRowContext(ctx, query).Scan(&count); err != nil {
			t.Fatalf("read %s: %v", label, err)
		}
		want := 1
		if label == "legacy layout" {
			want = 0
		}
		if count != want {
			t.Fatalf("%s count = %d, want %d", label, count, want)
		}
	}

	updated, err = packs.BackfillLegacySourceBackedCuratedDefaultLayoutIDsFromActivePacks(ctx, db)
	if err != nil {
		t.Fatalf("idempotent page layout ID backfill: %v", err)
	}
	if updated != 0 {
		t.Fatalf("idempotent page layout ID backfill updated = %d, want 0", updated)
	}
}

func openRemainingTestQualityDatabase(t *testing.T) (context.Context, *database.TiDBConnection) {
	t.Helper()
	cfg, cleanup := newTemporaryIntegrationDatabase(t)
	t.Cleanup(cleanup)
	ctx, cancel := context.WithTimeout(context.Background(), 4*time.Minute)
	t.Cleanup(cancel)
	conn := initializeControlPlaneMetadata(t, ctx, cfg)
	t.Cleanup(func() { _ = conn.Close() })
	return ctx, conn
}

func verifySchemaReadinessOutcome(t *testing.T, conn *database.TiDBConnection) {
	t.Helper()
	if err := bootstrapimpl.VerifySchemaReadiness(conn); err != nil {
		t.Fatalf("canonical schema readiness: %v", err)
	}
}

func mustExecRemainingTestQuality(t *testing.T, ctx context.Context, db *sql.DB, label, statement string, args ...any) {
	t.Helper()
	if _, err := db.ExecContext(ctx, statement, args...); err != nil {
		t.Fatalf("%s: %v", label, err)
	}
}

func assertRemainingTestQualityError(t *testing.T, err error, fragments ...string) {
	t.Helper()
	if err == nil {
		t.Fatalf("error = nil, want %q", fragments)
	}
	for _, fragment := range fragments {
		if !strings.Contains(err.Error(), fragment) {
			t.Fatalf("error = %v, want %q", err, fragment)
		}
	}
}

func assertRemainingStandardFlowState(t *testing.T, db *sql.DB, developerName, ownerPack string, wantActive, wantCustomized bool) (string, string) {
	t.Helper()
	var definitionID, storedOwner string
	var activeVersionID sql.NullString
	var customized, deleted bool
	if err := db.QueryRow(`SELECT Id, COALESCE(OwnerPack, ''), ActiveVersionId, IsCustomized, IsDeleted
		FROM FlowDefinition WHERE DeveloperName = ?`, developerName).
		Scan(&definitionID, &storedOwner, &activeVersionID, &customized, &deleted); err != nil {
		t.Fatalf("read FlowDefinition %s: %v", developerName, err)
	}
	if storedOwner != ownerPack || customized != wantCustomized || deleted {
		t.Fatalf("FlowDefinition %s owner=%q customized=%t deleted=%t, want owner=%q customized=%t deleted=false",
			developerName, storedOwner, customized, deleted, ownerPack, wantCustomized)
	}
	active := strings.TrimSpace(activeVersionID.String)
	if (active != "") != wantActive {
		t.Fatalf("FlowDefinition %s active version=%q, want active=%t", developerName, active, wantActive)
	}
	return definitionID, active
}

func assertRemainingStandardFlowVersionCount(t *testing.T, db *sql.DB, definitionID string, want int) {
	t.Helper()
	var got int
	if err := db.QueryRow(`SELECT COUNT(*) FROM Flow WHERE DefinitionId = ?`, definitionID).Scan(&got); err != nil {
		t.Fatalf("count Flow versions for %s: %v", definitionID, err)
	}
	if got != want {
		t.Fatalf("Flow version count for %s = %d, want %d", definitionID, got, want)
	}
}