package controlplane

import (
	"context"
	"testing"

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

// TestReconcileFlowOrphansTouched verifies that ReconcileFlowOrphans sets
// ActiveVersionId to NULL for every FlowDefinition row that has a dangling
// ActiveVersionId reference, and returns the number of rows repaired.
func TestReconcileFlowOrphansTouched(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// Audit SELECT returns one broken row (used for logging only).
	mock.ExpectQuery(`SELECT fd\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).
			AddRow("def-orphan-1"))

	// Atomic UPDATE re-evaluates predicate; rows affected = 1.
	mock.ExpectExec(`UPDATE FlowDefinition`).
		WillReturnResult(sqlmock.NewResult(0, 1))

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

// TestReconcileFlowOrphansIdempotent verifies that a second call to
// ReconcileFlowOrphans against an already-repaired database returns touched=0
// and makes no UPDATE call (idempotency guarantee).
func TestReconcileFlowOrphansIdempotent(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// Second run: no broken rows remain → SELECT returns empty set.
	mock.ExpectQuery(`SELECT fd\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	// No UPDATE should be issued.

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

// TestReconcileFlowOrphansCollisionRepair verifies that ReconcileFlowOrphans
// NULLs out ActiveVersionId on FlowDefinition rows that are soft-deleted
// (IsDeleted = TRUE) but still carry a non-null ActiveVersionId — the
// deleted+active collision case added in MEDIUM-1.
func TestReconcileFlowOrphansCollisionRepair(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// Audit SELECT returns a deleted+active collision row.
	mock.ExpectQuery(`SELECT fd\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).
			AddRow("def-deleted-collision-1"))

	// Atomic UPDATE clears the ActiveVersionId on that row.
	mock.ExpectExec(`UPDATE FlowDefinition`).
		WillReturnResult(sqlmock.NewResult(0, 1))

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

// TestReconcileFlowOrphansCollisionRepairIdempotent verifies that a second
// call after a collision row has been repaired (ActiveVersionId already NULL)
// returns touched=0 and issues no UPDATE.
func TestReconcileFlowOrphansCollisionRepairIdempotent(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// After repair the collision row no longer appears in the SELECT.
	mock.ExpectQuery(`SELECT fd\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))

	touched, err := reconcileFlowOrphans(context.Background(), db)
	if err != nil {
		t.Fatalf("reconcileFlowOrphans collision idempotent = error %v, want nil", err)
	}
	if touched != 0 {
		t.Fatalf("reconcileFlowOrphans collision idempotent touched = %d, want 0", touched)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestReconcileFlowOrphans_ClearsCrossDefinition verifies that
// ReconcileFlowOrphans NULLs out ActiveVersionId on a FlowDefinition row
// whose active pointer targets a Flow belonging to a different FlowDefinition
// (cross-definition pointer).  A second run against the repaired DB returns
// touched=0 (idempotency guarantee).
func TestReconcileFlowOrphans_ClearsCrossDefinition(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// Audit SELECT: cross-definition pointer detected.
	mock.ExpectQuery(`SELECT fd\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).
			AddRow("def-cross-def-1"))

	// Atomic UPDATE repairs it.
	mock.ExpectExec(`UPDATE FlowDefinition`).
		WillReturnResult(sqlmock.NewResult(0, 1))

	touched, err := reconcileFlowOrphans(context.Background(), db)
	if err != nil {
		t.Fatalf("reconcileFlowOrphans cross-def = error %v, want nil", err)
	}
	if touched != 1 {
		t.Fatalf("reconcileFlowOrphans cross-def touched = %d, want 1", touched)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}

	// Second run: already repaired — SELECT returns empty, no UPDATE issued.
	db2, mock2, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New (idempotent): %v", err)
	}
	defer db2.Close()

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

	touched2, err := reconcileFlowOrphans(context.Background(), db2)
	if err != nil {
		t.Fatalf("reconcileFlowOrphans cross-def idempotent = error %v, want nil", err)
	}
	if touched2 != 0 {
		t.Fatalf("reconcileFlowOrphans cross-def idempotent touched = %d, want 0", touched2)
	}
	if err := mock2.ExpectationsWereMet(); err != nil {
		t.Fatalf("idempotent sql expectations unmet: %v", err)
	}
}

// TestReconcileFlowOrphans_ClearsSoftDeletedActive verifies that
// ReconcileFlowOrphans NULLs out ActiveVersionId on a FlowDefinition whose
// active pointer targets a soft-deleted Flow (IsDeleted = TRUE).  A second run
// returns touched=0 (idempotency guarantee).
func TestReconcileFlowOrphans_ClearsSoftDeletedActive(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// Audit SELECT: soft-deleted active version detected.
	mock.ExpectQuery(`SELECT fd\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).
			AddRow("def-soft-deleted-active-1"))

	// Atomic UPDATE repairs it.
	mock.ExpectExec(`UPDATE FlowDefinition`).
		WillReturnResult(sqlmock.NewResult(0, 1))

	touched, err := reconcileFlowOrphans(context.Background(), db)
	if err != nil {
		t.Fatalf("reconcileFlowOrphans soft-deleted-active = error %v, want nil", err)
	}
	if touched != 1 {
		t.Fatalf("reconcileFlowOrphans soft-deleted-active touched = %d, want 1", touched)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}

	// Second run: idempotent.
	db2, mock2, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New (idempotent): %v", err)
	}
	defer db2.Close()

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

	touched2, err := reconcileFlowOrphans(context.Background(), db2)
	if err != nil {
		t.Fatalf("reconcileFlowOrphans soft-deleted-active idempotent = error %v, want nil", err)
	}
	if touched2 != 0 {
		t.Fatalf("reconcileFlowOrphans soft-deleted-active idempotent touched = %d, want 0", touched2)
	}
	if err := mock2.ExpectationsWereMet(); err != nil {
		t.Fatalf("idempotent sql expectations unmet: %v", err)
	}
}

// TestReconcileFlowOrphans_ClearsNonActiveStatus verifies that
// ReconcileFlowOrphans NULLs out ActiveVersionId on a FlowDefinition whose
// active pointer targets a Flow with a non-Active Status (e.g. 'Draft').
// A second run returns touched=0 (idempotency guarantee).
func TestReconcileFlowOrphans_ClearsNonActiveStatus(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// Audit SELECT: Draft/Obsolete status pointer detected.
	mock.ExpectQuery(`SELECT fd\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).
			AddRow("def-non-active-status-1"))

	// Atomic UPDATE repairs it.
	mock.ExpectExec(`UPDATE FlowDefinition`).
		WillReturnResult(sqlmock.NewResult(0, 1))

	touched, err := reconcileFlowOrphans(context.Background(), db)
	if err != nil {
		t.Fatalf("reconcileFlowOrphans non-active-status = error %v, want nil", err)
	}
	if touched != 1 {
		t.Fatalf("reconcileFlowOrphans non-active-status touched = %d, want 1", touched)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}

	// Second run: idempotent.
	db2, mock2, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New (idempotent): %v", err)
	}
	defer db2.Close()

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

	touched2, err := reconcileFlowOrphans(context.Background(), db2)
	if err != nil {
		t.Fatalf("reconcileFlowOrphans non-active-status idempotent = error %v, want nil", err)
	}
	if touched2 != 0 {
		t.Fatalf("reconcileFlowOrphans non-active-status idempotent touched = %d, want 0", touched2)
	}
	if err := mock2.ExpectationsWereMet(); err != nil {
		t.Fatalf("idempotent sql expectations unmet: %v", err)
	}
}

// TestReconcileFlowOrphansAtomicUpdateRespectsConcurrentRepair is the race
// regression test for PR #174 round-2.
//
// Scenario: the audit SELECT observes "def-concurrent-1" as a candidate orphan.
// Between the SELECT and the atomic UPDATE a concurrent Tooling API writer
// legitimately repairs the row (re-assigns ActiveVersionId to a valid Flow).
// Because the atomic UPDATE re-evaluates the broken predicate at write time
// (DB row lock), the row no longer satisfies the WHERE clause; the UPDATE
// skips it and returns rows-affected=0.
//
// The reconciler MUST NOT count that row as repaired: touched MUST be 0, and
// no spurious NULL-write must have been issued by the reconciler's UPDATE.
//
// In sqlmock terms: the audit SELECT returns the candidate ID (simulating what
// the reconciler observes before the concurrent fix), and the subsequent UPDATE
// returns rows-affected=0 (simulating the atomic predicate re-check skipping
// the now-valid row).  The test asserts touched==0 and all expectations are met.
func TestReconcileFlowOrphansAtomicUpdateRespectsConcurrentRepair(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// Audit SELECT: reconciler sees "def-concurrent-1" as a candidate.
	// (Simulates the pre-concurrent-repair snapshot.)
	mock.ExpectQuery(`SELECT fd\.Id`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).
			AddRow("def-concurrent-1"))

	// Atomic UPDATE: the row was repaired by a concurrent writer before the
	// UPDATE ran, so the WHERE predicate no longer matches — rows-affected=0.
	// The UPDATE must NOT be issued with id-based args (by-id-only would
	// unconditionally deactivate the now-valid row); the predicate-based SQL
	// naturally returns 0 for a row whose ActiveVersionId is now valid.
	mock.ExpectExec(`UPDATE FlowDefinition`).
		WillReturnResult(sqlmock.NewResult(0, 0))

	touched, err := reconcileFlowOrphans(context.Background(), db)
	if err != nil {
		t.Fatalf("reconcileFlowOrphans concurrent repair = error %v, want nil", err)
	}
	// The reconciler must NOT report the concurrently-repaired row as touched.
	if touched != 0 {
		t.Fatalf("reconcileFlowOrphans concurrent repair touched = %d, want 0 — atomic UPDATE must not deactivate a row that a concurrent writer legitimately repaired", touched)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}