package controlplane

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

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

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

func expectProcessInstanceHistoryBackfillStampPresent(mock sqlmock.Sqlmock) {
	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM _System_Migration WHERE Id = ?")).
		WithArgs(migrationIdBackfillProcessInstanceHistory).
		WillReturnRows(sqlmock.NewRows([]string{"_"}).AddRow(1))
}

func expectProcessInstanceHistoryBackfillStampInsert(mock sqlmock.Sqlmock) {
	mock.ExpectExec(regexp.QuoteMeta("INSERT INTO _System_Migration")).
		WithArgs(migrationIdBackfillProcessInstanceHistory, 1, sqlmock.AnyArg()).
		WillReturnResult(sqlmock.NewResult(0, 1))
}

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

	expectProcessInstanceHistoryBackfillStampNotPresent(mock)
	mock.ExpectQuery(regexp.QuoteMeta("FROM INFORMATION_SCHEMA.TABLES")).
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(5))
	mock.ExpectExec(regexp.QuoteMeta(processInstanceHistoryTargetObjectTypeBackfillSQL)).
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(regexp.QuoteMeta(processInstanceHistoryStartedBackfillSQL)).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec(regexp.QuoteMeta(processInstanceHistoryCompletedNodeBackfillSQL)).
		WillReturnResult(sqlmock.NewResult(0, 2))
	mock.ExpectExec(regexp.QuoteMeta(processInstanceHistoryTerminalInstanceBackfillSQL)).
		WillReturnResult(sqlmock.NewResult(0, 4))
	mock.ExpectExec(regexp.QuoteMeta(processInstanceHistoryPendingWorkitemBackfillSQL)).
		WillReturnResult(sqlmock.NewResult(0, 3))
	mock.ExpectQuery(regexp.QuoteMeta(processInstanceHistoryBackfillClosureSQL)).
		WillReturnRows(sqlmock.NewRows([]string{"remaining"}).AddRow(0))
	expectProcessInstanceHistoryBackfillStampInsert(mock)

	updated, err := BackfillProcessInstanceHistory(context.Background(), db)
	if err != nil {
		t.Fatalf("BackfillProcessInstanceHistory = %v", err)
	}
	if updated != 10 {
		t.Fatalf("updated = %d, want 10", updated)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	expectProcessInstanceHistoryBackfillStampPresent(mock)

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

func TestBackfillProcessInstanceHistorySQLCoversLegacyCommentsAndTerminalInstances(t *testing.T) {
	if !strings.Contains(processInstanceHistoryStartedBackfillSQL, "MIN(NULLIF(Comments, '')) AS SubmitComments") {
		t.Fatalf("started backfill SQL must recover submit comments from legacy workitems:\n%s", processInstanceHistoryStartedBackfillSQL)
	}
	if !strings.Contains(processInstanceHistoryTargetObjectTypeBackfillSQL, "h.TargetObjectType = pd.TableEnumOrId") ||
		!strings.Contains(processInstanceHistoryBackfillClosureSQL, "COALESCE(h.TargetObjectType, '') = ''") {
		t.Fatalf("ProcessInstanceHistory backfill SQL must repair and verify TargetObjectType for rowRead RLS")
	}
	if !strings.Contains(processInstanceHistoryTerminalInstanceBackfillSQL, "CONCAT('04h-terminal-', pi.Id)") ||
		!strings.Contains(processInstanceHistoryTerminalInstanceBackfillSQL, "pi.Status <> 'Pending'") ||
		!strings.Contains(processInstanceHistoryBackfillClosureSQL, "pi.Status <> 'Pending'") {
		t.Fatalf("terminal ProcessInstance backfill/closure SQL missing auto-approved coverage")
	}
}

func TestBackfillProcessInstanceHistorySQLUsesCanonicalApprovalTableNames(t *testing.T) {
	sqlSnippets := map[string]string{
		"target object type repair": processInstanceHistoryTargetObjectTypeBackfillSQL,
		"started":                   processInstanceHistoryStartedBackfillSQL,
		"completed nodes":           processInstanceHistoryCompletedNodeBackfillSQL,
		"terminal instances":        processInstanceHistoryTerminalInstanceBackfillSQL,
		"pending workitems":         processInstanceHistoryPendingWorkitemBackfillSQL,
		"closure":                   processInstanceHistoryBackfillClosureSQL,
	}

	for name, sqlText := range sqlSnippets {
		if strings.Contains(sqlText, "_System_Process") {
			t.Fatalf("%s SQL must use canonical approval table names, got:\n%s", name, sqlText)
		}
	}
}