package controlplane

import (
	"context"
	"strings"
	"testing"

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

// TestVerifyFlowIntegrityOrphanDetected verifies that checkOrphanFlowVersions
// returns an error containing "orphan" when the DB returns orphan Flow rows.
func TestVerifyFlowIntegrityOrphanDetected(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(`SELECT fv\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).
			AddRow("flow-version-orphan-1").
			AddRow("flow-version-orphan-2"))

	err = checkOrphanFlowVersions(context.Background(), db)
	if err == nil {
		t.Fatal("checkOrphanFlowVersions = nil, want error for orphan rows")
	}
	if !strings.Contains(err.Error(), "orphan") {
		t.Fatalf("error %q does not contain 'orphan'", err.Error())
	}
	if !strings.Contains(err.Error(), "flow-version-orphan-1") {
		t.Fatalf("error %q does not include orphan id", err.Error())
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestVerifyFlowIntegrityNoOrphan verifies that checkOrphanFlowVersions returns
// nil when no orphan Flow rows exist.
func TestVerifyFlowIntegrityNoOrphan(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(`SELECT fv\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))

	if err := checkOrphanFlowVersions(context.Background(), db); err != nil {
		t.Fatalf("checkOrphanFlowVersions = %v, want nil for clean DB", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestVerifyFlowIntegrityDispatchesToHelper verifies that VerifyFlowDataIntegrity
// correctly wraps a non-nil error from the helper with the "flow integrity:"
// prefix.
func TestVerifyFlowIntegrityDispatchesToHelper(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(`SELECT fv\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow("orphan-id-99"))

	conn := database.NewConnectionFromDB(db)
	verifyErr := VerifyFlowDataIntegrity(context.Background(), conn)
	if verifyErr == nil {
		t.Fatal("VerifyFlowDataIntegrity = nil, want error")
	}
	if !strings.Contains(verifyErr.Error(), "flow integrity") {
		t.Fatalf("error %q does not contain 'flow integrity'", verifyErr.Error())
	}
	if !strings.Contains(verifyErr.Error(), "orphan") {
		t.Fatalf("error %q does not contain 'orphan'", verifyErr.Error())
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestVerifyFlowIntegrityActiveVersionBrokenRef verifies that
// checkActiveVersionIdRefs returns an error containing "ActiveVersionId" (or
// "active version") when a FlowDefinition row points to a missing Flow row.
func TestVerifyFlowIntegrityActiveVersionBrokenRef(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(`SELECT fd\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).
			AddRow("def-broken-1").
			AddRow("def-broken-2"))

	err = checkActiveVersionIdRefs(context.Background(), db)
	if err == nil {
		t.Fatal("checkActiveVersionIdRefs = nil, want error for broken ActiveVersionId ref")
	}
	if !strings.Contains(err.Error(), "ActiveVersionId") && !strings.Contains(err.Error(), "active version") {
		t.Fatalf("error %q does not contain 'ActiveVersionId' or 'active version'", err.Error())
	}
	if !strings.Contains(err.Error(), "def-broken-1") {
		t.Fatalf("error %q does not include broken definition id", err.Error())
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestVerifyFlowIntegrityActiveVersionValidRef verifies that
// checkActiveVersionIdRefs returns nil when all ActiveVersionId refs are valid.
func TestVerifyFlowIntegrityActiveVersionValidRef(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(`SELECT fd\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))

	if err := checkActiveVersionIdRefs(context.Background(), db); err != nil {
		t.Fatalf("checkActiveVersionIdRefs = %v, want nil for valid refs", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestVerifyFlowIntegrityCollisionDetected verifies that
// checkDeletedActiveCollision returns an error containing "deleted" or
// "collision" when a FlowDefinition row has IsDeleted=TRUE and a non-null
// ActiveVersionId — the impossible state that indicates a write-ordering bug.
func TestVerifyFlowIntegrityCollisionDetected(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(`SELECT Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).
			AddRow("def-deleted-active-1").
			AddRow("def-deleted-active-2"))

	err = checkDeletedActiveCollision(context.Background(), db)
	if err == nil {
		t.Fatal("checkDeletedActiveCollision = nil, want error for deleted+active collision rows")
	}
	if !strings.Contains(err.Error(), "deleted") && !strings.Contains(err.Error(), "collision") {
		t.Fatalf("error %q does not contain 'deleted' or 'collision'", err.Error())
	}
	if !strings.Contains(err.Error(), "def-deleted-active-1") {
		t.Fatalf("error %q does not include offending id", err.Error())
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestVerifyFlowIntegrityJSONInvalid verifies that checkFlowVersionJSONValid
// returns an error containing the offending row id when the Metadata column
// holds content that is not valid JSON.
func TestVerifyFlowIntegrityJSONInvalid(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(`SELECT Id, Metadata`).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Metadata"}).
			AddRow("flow-bad-json-1", `{not valid json`).
			AddRow("flow-bad-json-2", `{"ok": true}`))

	err = checkFlowVersionJSONValid(context.Background(), db)
	if err == nil {
		t.Fatal("checkFlowVersionJSONValid = nil, want error for invalid JSON row")
	}
	if !strings.Contains(err.Error(), "flow-bad-json-1") {
		t.Fatalf("error %q does not include offending id", err.Error())
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestVerifyFlowIntegrityJSONValid verifies that checkFlowVersionJSONValid
// returns nil when all Metadata columns contain valid JSON.
func TestVerifyFlowIntegrityJSONValid(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(`SELECT Id, Metadata`).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Metadata"}).
			AddRow("flow-good-1", `{"type":"recordTriggered"}`).
			AddRow("flow-good-2", `{}`))

	if err := checkFlowVersionJSONValid(context.Background(), db); err != nil {
		t.Fatalf("checkFlowVersionJSONValid = %v, want nil for valid JSON rows", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestVerifyActiveVersionIdRefs_CrossDefinitionPointer verifies that
// checkActiveVersionIdRefs returns an error when a FlowDefinition's
// ActiveVersionId points to a Flow row that belongs to a DIFFERENT
// FlowDefinition (fv.DefinitionId != fd.Id).
func TestVerifyActiveVersionIdRefs_CrossDefinitionPointer(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// The stricter ON clause excludes the cross-definition flow, so
	// fd1 surfaces as a broken-ref row.
	mock.ExpectQuery(`SELECT fd\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).
			AddRow("fd1-cross-def-owner"))

	err = checkActiveVersionIdRefs(context.Background(), db)
	if err == nil {
		t.Fatal("checkActiveVersionIdRefs = nil, want error for cross-definition pointer")
	}
	if !strings.Contains(err.Error(), "ActiveVersionId") && !strings.Contains(err.Error(), "active version") {
		t.Fatalf("error %q does not contain 'ActiveVersionId' or 'active version'", err.Error())
	}
	if !strings.Contains(err.Error(), "fd1-cross-def-owner") {
		t.Fatalf("error %q does not include offending FlowDefinition id", err.Error())
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestVerifyActiveVersionIdRefs_SoftDeletedActiveVersion verifies that
// checkActiveVersionIdRefs returns an error when a FlowDefinition's
// ActiveVersionId points to a Flow row that is soft-deleted (IsDeleted = TRUE).
func TestVerifyActiveVersionIdRefs_SoftDeletedActiveVersion(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// The ON clause excludes soft-deleted flows, so the FlowDefinition
	// surfaces as a broken-ref row.
	mock.ExpectQuery(`SELECT fd\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).
			AddRow("fd-soft-deleted-active"))

	err = checkActiveVersionIdRefs(context.Background(), db)
	if err == nil {
		t.Fatal("checkActiveVersionIdRefs = nil, want error for soft-deleted active version pointer")
	}
	if !strings.Contains(err.Error(), "ActiveVersionId") && !strings.Contains(err.Error(), "active version") {
		t.Fatalf("error %q does not contain 'ActiveVersionId' or 'active version'", err.Error())
	}
	if !strings.Contains(err.Error(), "fd-soft-deleted-active") {
		t.Fatalf("error %q does not include offending FlowDefinition id", err.Error())
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestVerifyActiveVersionIdRefs_NonActiveStatus verifies that
// checkActiveVersionIdRefs returns an error when a FlowDefinition's
// ActiveVersionId points to a Flow whose Status is not 'Active' (e.g. 'Draft'
// or 'Obsolete').
func TestVerifyActiveVersionIdRefs_NonActiveStatus(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// The ON clause requires fv.Status = 'Active'; a Draft/Obsolete flow is
	// excluded, so the FlowDefinition surfaces as a broken-ref row.
	mock.ExpectQuery(`SELECT fd\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).
			AddRow("fd-draft-status-pointer"))

	err = checkActiveVersionIdRefs(context.Background(), db)
	if err == nil {
		t.Fatal("checkActiveVersionIdRefs = nil, want error for non-Active status pointer")
	}
	if !strings.Contains(err.Error(), "ActiveVersionId") && !strings.Contains(err.Error(), "active version") {
		t.Fatalf("error %q does not contain 'ActiveVersionId' or 'active version'", err.Error())
	}
	if !strings.Contains(err.Error(), "fd-draft-status-pointer") {
		t.Fatalf("error %q does not include offending FlowDefinition id", err.Error())
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestApplyPathReconcileBeforeVerify verifies the HIGH-1 wiring: an orphan
// FlowDefinition.ActiveVersionId that would trip VerifyFlowDataIntegrity is
// cleared by reconcileFlowOrphans BEFORE verify is called, allowing the verify
// gate to pass.  This mirrors the step-8/9 sequence added to ApplyAll.
func TestApplyPathReconcileBeforeVerify(t *testing.T) {
	// Step A: reconcile detects and repairs the orphan.
	reconcileDB, reconcileMock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New (reconcile): %v", err)
	}
	defer reconcileDB.Close()

	reconcileMock.ExpectQuery(`SELECT fd\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow("def-orphan-apply-1"))
	// Atomic UPDATE has no placeholder args — predicate is embedded in SQL.
	reconcileMock.ExpectExec(`UPDATE FlowDefinition`).
		WillReturnResult(sqlmock.NewResult(0, 1))

	touched, err := reconcileFlowOrphans(context.Background(), reconcileDB)
	if err != nil {
		t.Fatalf("reconcileFlowOrphans = error %v, want nil", err)
	}
	if touched != 1 {
		t.Fatalf("reconcileFlowOrphans touched = %d, want 1", touched)
	}
	if err := reconcileMock.ExpectationsWereMet(); err != nil {
		t.Fatalf("reconcile sql expectations unmet: %v", err)
	}

	// Step B: after repair, the ActiveVersionId check returns nil (clean DB).
	verifyDB, verifyMock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New (verify): %v", err)
	}
	defer verifyDB.Close()

	verifyMock.ExpectQuery(`SELECT fd\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"})) // no broken refs remain

	if err := checkActiveVersionIdRefs(context.Background(), verifyDB); err != nil {
		t.Fatalf("checkActiveVersionIdRefs post-reconcile = %v, want nil", err)
	}
	if err := verifyMock.ExpectationsWereMet(); err != nil {
		t.Fatalf("verify sql expectations unmet: %v", err)
	}
}