package controlplane

import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"regexp"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/kernel/pkg/schemacatalog"
)

// TestPinnedSchemaDoesNotRequireRetiredConnectorEventIndex binds the
// control-plane migration to the schema contract compiled from its pinned
// NexusCRM core closure. A Catalog release must never both remove an index and
// publish a schema contract that requires the same index.
func TestPinnedSchemaDoesNotRequireRetiredConnectorEventIndex(t *testing.T) {
	definitions, err := schemacatalog.ManagedTableDefinitions()
	if err != nil {
		t.Fatalf("load pinned managed-table definitions: %v", err)
	}

	for _, definition := range definitions {
		if definition.TableName != connectorEventTableName {
			continue
		}
		for _, index := range definition.Indices {
			if index.Name == connectorEventLegacyRetirementIndexName {
				t.Fatalf(
					"pinned schema requires retired index %s.%s; Catalog migration and schema contract must come from one Runtime source closure",
					connectorEventTableName,
					connectorEventLegacyRetirementIndexName,
				)
			}
		}
		return
	}
	t.Fatalf("pinned schema does not define managed table %s", connectorEventTableName)
}

func TestDropConnectorEventLegacyRetirementIndex_AlreadyAppliedSkips(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 = ?")).
		WithArgs(migrationIDDropConnectorEventLegacyRetirementIndex).
		WillReturnRows(sqlmock.NewRows([]string{"_"}).AddRow(1))

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

func TestDropConnectorEventLegacyRetirementIndex_AbsentStamps(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 = ?")).
		WithArgs(migrationIDDropConnectorEventLegacyRetirementIndex).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))
	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS")).
		WithArgs(connectorEventTableName, connectorEventLegacyRetirementIndexName).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))
	mock.ExpectExec(regexp.QuoteMeta("INSERT INTO _System_Migration")).
		WillReturnResult(sqlmock.NewResult(1, 1))

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

func TestDropConnectorEventLegacyRetirementIndex_DropsAndStamps(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 = ?")).
		WithArgs(migrationIDDropConnectorEventLegacyRetirementIndex).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))
	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS")).
		WithArgs(connectorEventTableName, connectorEventLegacyRetirementIndexName).
		WillReturnRows(sqlmock.NewRows([]string{"_"}).AddRow(1))
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `_System_ConnectorEvent` DROP INDEX `idx_connector_event_legacy_retire`")).
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(regexp.QuoteMeta("INSERT INTO _System_Migration")).
		WillReturnResult(sqlmock.NewResult(1, 1))

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

func TestDropConnectorEventLegacyRetirementIndex_LookupErrorPropagates(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 = ?")).
		WithArgs(migrationIDDropConnectorEventLegacyRetirementIndex).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))
	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS")).
		WillReturnError(sql.ErrConnDone)

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

func TestDropConnectorEventLegacyRetirementIndex_DropErrorPropagates(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 = ?")).
		WithArgs(migrationIDDropConnectorEventLegacyRetirementIndex).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))
	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS")).
		WithArgs(connectorEventTableName, connectorEventLegacyRetirementIndexName).
		WillReturnRows(sqlmock.NewRows([]string{"_"}).AddRow(1))
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `_System_ConnectorEvent` DROP INDEX `idx_connector_event_legacy_retire`")).
		WillReturnError(fmt.Errorf("cannot drop index"))

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

func TestDropConnectorEventLegacyRetirementIndex_StampErrorPropagates(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 = ?")).
		WithArgs(migrationIDDropConnectorEventLegacyRetirementIndex).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))
	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS")).
		WithArgs(connectorEventTableName, connectorEventLegacyRetirementIndexName).
		WillReturnRows(sqlmock.NewRows([]string{"_"}).AddRow(1))
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `_System_ConnectorEvent` DROP INDEX `idx_connector_event_legacy_retire`")).
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(regexp.QuoteMeta("INSERT INTO _System_Migration")).
		WillReturnError(fmt.Errorf("stamp insert failed"))

	err = DropConnectorEventLegacyRetirementIndex(context.Background(), db)
	if err == nil {
		t.Fatal("DropConnectorEventLegacyRetirementIndex = nil, want stamp error")
	}
	if !strings.Contains(err.Error(), "stamp insert failed") {
		t.Fatalf("error = %v, want stamp failure", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

func TestConnectorEventRetirementWiringRespectsRuntimePackBoundary(t *testing.T) {
	source, err := os.ReadFile("operations.go")
	if err != nil {
		t.Fatalf("read operations.go: %v", err)
	}
	text := string(source)

	runRepairStart := strings.Index(text, "func RunRepairWithOptionsAndRunnerOptions(")
	runL0RepairStart := strings.Index(text, "func RunL0Repair(")
	if runRepairStart < 0 || runL0RepairStart < 0 || runRepairStart >= runL0RepairStart {
		t.Fatalf("repair function bounds missing: RunRepair=%d RunL0Repair=%d", runRepairStart, runL0RepairStart)
	}
	if !strings.Contains(text[runRepairStart:runL0RepairStart], `trackStepFatal("DropConnectorEventLegacyRetirementIndex"`) {
		t.Fatal("RunRepair does not run the legacy ConnectorEvent index migration")
	}
	if !strings.Contains(text[runRepairStart:runL0RepairStart], `trackStepFatal("VerifyConnectorExecutionProtocols"`) {
		t.Fatal("RunRepair does not verify the WorkItem-only ConnectorEvent precondition")
	}
	if !strings.Contains(text[runL0RepairStart:], `trackStepFatal("DropConnectorEventLegacyRetirementIndex"`) {
		t.Fatal("RunL0Repair does not run the legacy ConnectorEvent index migration")
	}
	runL0RepairEnd := strings.Index(text[runL0RepairStart:], "func EnsureIndexes(")
	if runL0RepairEnd < 0 {
		t.Fatal("RunL0Repair end bound missing")
	}
	runL0Repair := text[runL0RepairStart : runL0RepairStart+runL0RepairEnd]
	if strings.Contains(runL0Repair, `VerifyConnectorExecutionProtocols`) {
		t.Fatal("RunL0Repair must not require runtime-pack WorkItem schema before InitL0 installs runtime-required packs")
	}
}

func TestApplyAllDefersWorkItemCutoverUntilAfterRuntimePacksAndBeforeSourceReapply(t *testing.T) {
	source, err := os.ReadFile("operations.go")
	if err != nil {
		t.Fatalf("read operations.go: %v", err)
	}
	text := string(source)

	runRepairStart := strings.Index(text, "func RunRepairWithOptionsAndRunnerOptions(")
	applyAllStart := strings.Index(text, "func ApplyAllWithOptionsAndRunnerOptions(")
	applySourceConvergenceStart := strings.Index(text, "func ApplySourceConvergenceWithRunnerOptions(")
	if runRepairStart < 0 || applyAllStart < 0 || applySourceConvergenceStart < 0 {
		t.Fatalf("operation function bounds missing: repair=%d apply=%d source=%d", runRepairStart, applyAllStart, applySourceConvergenceStart)
	}
	if !strings.Contains(text[runRepairStart:applyAllStart], "if !options.DeferConnectorWorkItemCutover") {
		t.Fatal("RunRepair must allow ApplyAll to defer destructive connector cutover")
	}
	applyAll := text[applyAllStart:applySourceConvergenceStart]
	if !strings.Contains(applyAll, "DeferConnectorWorkItemCutover:") {
		t.Fatal("ApplyAll must defer cutover during its pre-pack repair phase")
	}
	applyPacks := strings.Index(applyAll, `runLoggedControlPlanePhase("ApplyAll", "apply-packs"`)
	cutover := strings.Index(applyAll, `runLoggedControlPlanePhase("ApplyAll", "verify-connector-workitem-only-execution-cutover"`)
	adoptSources := strings.Index(applyAll, `runLoggedControlPlanePhase("ApplyAll", "adopt-legacy-installed-pack-source-identities"`)
	advanceSources := strings.Index(applyAll, `runLoggedControlPlanePhase("ApplyAll", "apply-authorized-source-revision-advances"`)
	reconcileSources := strings.Index(applyAll, `runLoggedControlPlanePhase("ApplyAll", "reconcile-active-installed-pack-sources"`)
	if applyPacks < 0 || cutover < 0 || adoptSources < 0 || advanceSources < 0 || reconcileSources < 0 {
		t.Fatalf("ApplyAll lifecycle phases missing: packs=%d cutover=%d adopt=%d advance=%d reconcile=%d", applyPacks, cutover, adoptSources, advanceSources, reconcileSources)
	}
	if cutover <= applyPacks || cutover >= adoptSources || cutover >= advanceSources || cutover >= reconcileSources {
		t.Fatalf("ApplyAll cutover must follow runtime packs and precede every source reapply: packs=%d cutover=%d adopt=%d advance=%d reconcile=%d", applyPacks, cutover, adoptSources, advanceSources, reconcileSources)
	}
	sourceConvergence := text[applySourceConvergenceStart:]
	sourcePacks := strings.Index(sourceConvergence, "ApplyPacksWithOptions(")
	sourceCutover := strings.Index(sourceConvergence, "VerifyConnectorExecutionProtocols(")
	sourceAdopt := strings.Index(sourceConvergence, "adoptLegacyInstalledPackSourceIdentities(")
	sourceReconcile := strings.Index(sourceConvergence, "reconcileActiveInstalledPackSourceState(")
	if !strings.Contains(sourceConvergence, "DeferConnectorWorkItemCutover:") || sourcePacks < 0 || sourceCutover < 0 || sourceAdopt < 0 || sourceReconcile < 0 {
		t.Fatalf("ApplySourceConvergence lifecycle phases missing: packs=%d cutover=%d adopt=%d reconcile=%d", sourcePacks, sourceCutover, sourceAdopt, sourceReconcile)
	}
	if sourceCutover <= sourcePacks || sourceCutover >= sourceAdopt || sourceCutover >= sourceReconcile {
		t.Fatalf("ApplySourceConvergence cutover must follow runtime packs and precede source reapply: packs=%d cutover=%d adopt=%d reconcile=%d", sourcePacks, sourceCutover, sourceAdopt, sourceReconcile)
	}
}