package controlplane

import (
	"context"
	"regexp"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
)

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

	// Migration stamp already exists → should skip.
	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM _System_Migration WHERE Id = ? LIMIT 1")).
		WithArgs(migrationIdIsPolymorphicMultiTargetBackfill).
		WillReturnRows(sqlmock.NewRows([]string{"_"}).AddRow(1))

	count, err := MigrateIsPolymorphicMultiTargetBackfill(context.Background(), db)
	if err != nil {
		t.Fatalf("MigrateIsPolymorphicMultiTargetBackfill (already applied) = error %v, want nil", err)
	}
	if count != 0 {
		t.Fatalf("count = %d, want 0 for already-applied migration", count)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

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

	// Migration not yet applied.
	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM _System_Migration WHERE Id = ? LIMIT 1")).
		WithArgs(migrationIdIsPolymorphicMultiTargetBackfill).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))

	// No rows match the candidate query.
	mock.ExpectQuery(regexp.QuoteMeta("SELECT f.Id, o.ApiName, f.ApiName, f.Type, f.ReferenceTo, f.IsPolymorphic")).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ApiName", "ApiName", "Type", "ReferenceTo", "IsPolymorphic"}))

	// Stamp migration.
	mock.ExpectExec(regexp.QuoteMeta("INSERT INTO _System_Migration")).
		WillReturnResult(sqlmock.NewResult(1, 1))

	count, err := MigrateIsPolymorphicMultiTargetBackfill(context.Background(), db)
	if err != nil {
		t.Fatalf("MigrateIsPolymorphicMultiTargetBackfill (no rows) = error %v, want nil", err)
	}
	if count != 0 {
		t.Fatalf("count = %d, want 0 when no rows need stamping", count)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

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

	// Migration not yet applied.
	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM _System_Migration WHERE Id = ? LIMIT 1")).
		WithArgs(migrationIdIsPolymorphicMultiTargetBackfill).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))

	// Two multi-target rows: one reference, one lookup.
	mock.ExpectQuery(regexp.QuoteMeta("SELECT f.Id, o.ApiName, f.ApiName, f.Type, f.ReferenceTo, f.IsPolymorphic")).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ApiName", "ApiName", "Type", "ReferenceTo", "IsPolymorphic"}).
			AddRow("f1", "Task", "WhatId", "reference", `["Account","Contact"]`, false).
			AddRow("f2", "Event", "WhoId", "lookup", `["Lead","Contact"]`, false))

	// Update each row.
	mock.ExpectPrepare(regexp.QuoteMeta("UPDATE _System_Field SET IsPolymorphic = 1 WHERE Id = ?"))
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_Field SET IsPolymorphic = 1 WHERE Id = ?")).
		WithArgs("f1").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_Field SET IsPolymorphic = 1 WHERE Id = ?")).
		WithArgs("f2").
		WillReturnResult(sqlmock.NewResult(0, 1))

	// Stamp migration.
	mock.ExpectExec(regexp.QuoteMeta("INSERT INTO _System_Migration")).
		WillReturnResult(sqlmock.NewResult(1, 1))

	count, err := MigrateIsPolymorphicMultiTargetBackfill(context.Background(), db)
	if err != nil {
		t.Fatalf("MigrateIsPolymorphicMultiTargetBackfill = error %v, want nil", err)
	}
	if count != 2 {
		t.Fatalf("count = %d, want 2", count)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

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

	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM _System_Migration WHERE Id = ? LIMIT 1")).
		WithArgs(migrationIdIsPolymorphicMultiTargetBackfill).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))

	// One single-target reference row (should NOT be stamped).
	mock.ExpectQuery(regexp.QuoteMeta("SELECT f.Id, o.ApiName, f.ApiName, f.Type, f.ReferenceTo, f.IsPolymorphic")).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ApiName", "ApiName", "Type", "ReferenceTo", "IsPolymorphic"}).
			AddRow("f1", "Contact", "AccountId", "reference", `["Account"]`, false))

	// Stamp migration (no updates expected).
	mock.ExpectExec(regexp.QuoteMeta("INSERT INTO _System_Migration")).
		WillReturnResult(sqlmock.NewResult(1, 1))

	count, err := MigrateIsPolymorphicMultiTargetBackfill(context.Background(), db)
	if err != nil {
		t.Fatalf("MigrateIsPolymorphicMultiTargetBackfill = error %v, want nil", err)
	}
	if count != 0 {
		t.Fatalf("count = %d, want 0 (single-target rows should not be stamped)", count)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

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

	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM _System_Migration WHERE Id = ? LIMIT 1")).
		WithArgs(migrationIdIsPolymorphicMultiTargetBackfill).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))

	// One row with malformed JSON — migration must fail closed, not skip.
	mock.ExpectQuery(regexp.QuoteMeta("SELECT f.Id, o.ApiName, f.ApiName, f.Type, f.ReferenceTo, f.IsPolymorphic")).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ApiName", "ApiName", "Type", "ReferenceTo", "IsPolymorphic"}).
			AddRow("f1", "Task", "WhatId", "reference", `not-valid-json`, false))

	// No INSERT into _System_Migration expected — stamp must not be written
	// when ambiguous persisted metadata is encountered.

	_, err = MigrateIsPolymorphicMultiTargetBackfill(context.Background(), db)
	if err == nil {
		t.Fatal("MigrateIsPolymorphicMultiTargetBackfill should fail closed on malformed ReferenceTo JSON")
	}
	if !strings.Contains(err.Error(), "malformed ReferenceTo JSON") {
		t.Fatalf("error = %v, want mention of malformed ReferenceTo JSON", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

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

	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM _System_Migration WHERE Id = ? LIMIT 1")).
		WithArgs(migrationIdIsPolymorphicMultiTargetBackfill).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))

	// Multi-target array with a blank entry — ambiguous persisted metadata.
	mock.ExpectQuery(regexp.QuoteMeta("SELECT f.Id, o.ApiName, f.ApiName, f.Type, f.ReferenceTo, f.IsPolymorphic")).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ApiName", "ApiName", "Type", "ReferenceTo", "IsPolymorphic"}).
			AddRow("f1", "Task", "WhatId", "reference", `["Account",""]`, false))

	// No INSERT expected — stamp must not be written.

	_, err = MigrateIsPolymorphicMultiTargetBackfill(context.Background(), db)
	if err == nil {
		t.Fatal("MigrateIsPolymorphicMultiTargetBackfill should fail closed on blank target in multi-target array")
	}
	if !strings.Contains(err.Error(), "blank ReferenceTo") {
		t.Fatalf("error = %v, want mention of blank ReferenceTo", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

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

	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM _System_Migration WHERE Id = ? LIMIT 1")).
		WithArgs(migrationIdIsPolymorphicMultiTargetBackfill).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))

	// No un-stamped rows remain (all already have IsPolymorphic=true or are
	// single-target — the SQL WHERE clause excludes them).
	mock.ExpectQuery(regexp.QuoteMeta("SELECT f.Id, o.ApiName, f.ApiName, f.Type, f.ReferenceTo, f.IsPolymorphic")).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ApiName", "ApiName", "Type", "ReferenceTo", "IsPolymorphic"}))

	mock.ExpectExec(regexp.QuoteMeta("INSERT INTO _System_Migration")).
		WillReturnResult(sqlmock.NewResult(1, 1))

	count, err := MigrateIsPolymorphicMultiTargetBackfill(context.Background(), db)
	if err != nil {
		t.Fatalf("MigrateIsPolymorphicMultiTargetBackfill = error %v, want nil", err)
	}
	if count != 0 {
		t.Fatalf("count = %d, want 0 for idempotent re-run", count)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestRepairOrdering_CorrectOrderStampsEmptyRefToRow proves the ordering
// contract: when a ReferenceTo-populating repair runs BEFORE
// MigrateIsPolymorphicMultiTargetBackfill, a legacy multi-target row that
// started with empty ReferenceTo is correctly stamped IsPolymorphic=true.
// If the polymorphic migration ran first, it would skip the empty-ReferenceTo
// row, stamp completion, and the row would be permanently non-polymorphic
// after the later backfill populates ReferenceTo.
func TestRepairOrdering_CorrectOrderStampsEmptyRefToRow(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// === Step 1: Simulate the ReferenceTo-populating repair. ===
	// A legacy multi-target row (e.g. Task.WhatId) has empty ReferenceTo.
	// The repair populates it via UPDATE _System_Field SET ReferenceTo = ?
	// We model this step as a raw update because the actual repair source
	// may vary (BackfillLookupReferences, app-pack reapply, or a future
	// multi-target repair).
	mock.ExpectExec("UPDATE _System_Field SET ReferenceTo").
		WithArgs(`["Account","Contact"]`, "fld_whatid").
		WillReturnResult(sqlmock.NewResult(0, 1))

	// === Step 2: MigrateIsPolymorphicMultiTargetBackfill runs second. ===
	// Stamp check: not yet applied.
	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM _System_Migration WHERE Id = ? LIMIT 1")).
		WithArgs(migrationIdIsPolymorphicMultiTargetBackfill).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))

	// Candidate scan: WhatId now has multi-target ReferenceTo (from step 1).
	mock.ExpectQuery(regexp.QuoteMeta("SELECT f.Id, o.ApiName, f.ApiName, f.Type, f.ReferenceTo, f.IsPolymorphic")).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ApiName", "ApiName", "Type", "ReferenceTo", "IsPolymorphic"}).
			AddRow("fld_whatid", "Task", "WhatId", "reference", `["Account","Contact"]`, false))

	// Stamp IsPolymorphic=1.
	mock.ExpectPrepare(regexp.QuoteMeta("UPDATE _System_Field SET IsPolymorphic = 1 WHERE Id = ?"))
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_Field SET IsPolymorphic = 1 WHERE Id = ?")).
		WithArgs("fld_whatid").
		WillReturnResult(sqlmock.NewResult(0, 1))

	// Record migration completion.
	mock.ExpectExec(regexp.QuoteMeta("INSERT INTO _System_Migration")).
		WillReturnResult(sqlmock.NewResult(1, 1))

	// Execute step 1: simulate ReferenceTo backfill.
	_, err = db.ExecContext(context.Background(),
		"UPDATE _System_Field SET ReferenceTo = ? WHERE Id = ?",
		`["Account","Contact"]`, "fld_whatid")
	if err != nil {
		t.Fatalf("ReferenceTo backfill: %v", err)
	}

	// Execute step 2: polymorphic stamp migration.
	count, err := MigrateIsPolymorphicMultiTargetBackfill(context.Background(), db)
	if err != nil {
		t.Fatalf("MigrateIsPolymorphicMultiTargetBackfill: %v", err)
	}
	if count != 1 {
		t.Fatalf("polymorphic stamp count = %d, want 1 (empty-ReferenceTo row should be visible after backfill)", count)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestRepairOrdering_WrongOrderMissesEmptyRefToRow proves that running the
// polymorphic migration BEFORE the ReferenceTo-populating repair leaves a
// legacy multi-target row permanently non-polymorphic. The migration skips
// the empty-ReferenceTo row, stamps completion, and the later backfill
// populates ReferenceTo without IsPolymorphic.
func TestRepairOrdering_WrongOrderMissesEmptyRefToRow(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// === Step 1 (WRONG ORDER): Polymorphic migration runs first. ===
	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM _System_Migration WHERE Id = ? LIMIT 1")).
		WithArgs(migrationIdIsPolymorphicMultiTargetBackfill).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))

	// Candidate scan: WhatId has empty ReferenceTo → filtered out by
	// "AND TRIM(f.ReferenceTo) != '' AND TRIM(f.ReferenceTo) != '[]'".
	// No rows returned.
	mock.ExpectQuery(regexp.QuoteMeta("SELECT f.Id, o.ApiName, f.ApiName, f.Type, f.ReferenceTo, f.IsPolymorphic")).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ApiName", "ApiName", "Type", "ReferenceTo", "IsPolymorphic"}))

	// Migration stamps completion despite not seeing the row.
	mock.ExpectExec(regexp.QuoteMeta("INSERT INTO _System_Migration")).
		WillReturnResult(sqlmock.NewResult(1, 1))

	// === Step 2: ReferenceTo backfill runs later. ===
	mock.ExpectExec("UPDATE _System_Field SET ReferenceTo").
		WithArgs(`["Account","Contact"]`, "fld_whatid").
		WillReturnResult(sqlmock.NewResult(0, 1))

	// Execute wrong order: migration first.
	count, err := MigrateIsPolymorphicMultiTargetBackfill(context.Background(), db)
	if err != nil {
		t.Fatalf("MigrateIsPolymorphicMultiTargetBackfill: %v", err)
	}
	if count != 0 {
		t.Fatalf("count = %d, want 0 (empty-ReferenceTo row invisible to migration)", count)
	}

	// Execute backfill later.
	_, err = db.ExecContext(context.Background(),
		"UPDATE _System_Field SET ReferenceTo = ? WHERE Id = ?",
		`["Account","Contact"]`, "fld_whatid")
	if err != nil {
		t.Fatalf("ReferenceTo backfill: %v", err)
	}

	// Row is now: Type=reference, ReferenceTo=["Account","Contact"],
	// IsPolymorphic=false. The migration is already stamped complete and
	// will not re-run. The row has permanently lost polymorphic ownership.
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}