//go:build integration

package controlplane

import (
	"context"
	"database/sql"
	"strings"
	"testing"

	"github.com/nexuscrm/kernel/pkg/schema"
	"github.com/pingcap-inc/nexuscrm-controlplane/bootstrapimpl"
)

const runtimePlatformCommandTable = "_System_RuntimePlatformCommand"

var runtimePlatformCommandDependencyColumns = []string{
	"DependencyKind",
	"DependencyObjectAPIName",
	"DependencyRecordId",
	"DependencyKeyHash",
	"DependencyConnectorOwnerKey",
	"DependencyConnectorNamespace",
	"DependencyConnectorSourceObject",
	"DependencyDestinationSlotId",
	"DependencyConnectorRemoteId",
	"DependencyBlockedAt",
}

func TestRuntimePlatformCommandDependencySchemaUpgradePreservesExistingRows(t *testing.T) {
	ctx := context.Background()
	cfg, cleanup := newTemporaryIntegrationDatabase(t)
	defer cleanup()

	conn := initializeControlPlaneMetadata(t, ctx, cfg)
	defer conn.Close()
	db := conn.DB()

	const rowID = "runtime-command-dependency-upgrade-proof"
	if _, err := db.ExecContext(ctx, `
		INSERT INTO `+runtimePlatformCommandTable+` (
			Id, OrgId, AppInstallationId, CallerWorkloadId, RuntimeCapabilityId,
			MetadataBinding, ExecutionScopeDigest, ScopeDigest, CommandId,
			IdempotencyKey, CommandKind, PayloadFormatVersion, RequestDigest,
			Payload, Receipts, State
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		rowID,
		"org-upgrade-proof",
		"app-installation-upgrade-proof",
		"integration-upgrade-proof",
		"runtime-capability-upgrade-proof",
		`{"catalogRevision":"upgrade-proof"}`,
		strings.Repeat("e", 64),
		strings.Repeat("s", 64),
		"command-upgrade-proof",
		"idempotency-upgrade-proof",
		"integration.command.execute",
		1,
		"sha256:"+strings.Repeat("d", 64),
		[]byte(`{"payload":"upgrade-proof"}`),
		`[]`,
		"ACCEPTED",
	); err != nil {
		t.Fatalf("insert pre-upgrade Runtime command row: %v", err)
	}

	for _, column := range runtimePlatformCommandDependencyColumns {
		if _, err := db.ExecContext(ctx, "ALTER TABLE `"+runtimePlatformCommandTable+"` DROP COLUMN `"+column+"`"); err != nil {
			t.Fatalf("prepare supported prior Runtime command schema without %s: %v", column, err)
		}
	}
	assertRuntimePlatformCommandDependencyColumns(t, db, 0)

	if err := bootstrapimpl.ReconcileTableColumns(db, []schema.TableDefinition{runtimePlatformCommandDefinition(t)}); err != nil {
		t.Fatalf("upgrade Runtime command dependency schema from supported prior state: %v", err)
	}
	assertRuntimePlatformCommandDependencyColumns(t, db, 1)
	assertRuntimePlatformCommandUpgradeRow(t, db, rowID)

	if err := bootstrapimpl.ReconcileTableColumns(db, []schema.TableDefinition{runtimePlatformCommandDefinition(t)}); err != nil {
		t.Fatalf("idempotent Runtime command dependency schema reconciliation: %v", err)
	}
	assertRuntimePlatformCommandDependencyColumns(t, db, 1)
	assertRuntimePlatformCommandUpgradeRow(t, db, rowID)
}

func runtimePlatformCommandDefinition(t *testing.T) schema.TableDefinition {
	t.Helper()
	for _, definition := range bootstrapimpl.GetManagedTableDefinitions() {
		if definition.TableName == runtimePlatformCommandTable {
			return definition
		}
	}
	t.Fatalf("managed definition for %s is missing", runtimePlatformCommandTable)
	return schema.TableDefinition{}
}

func assertRuntimePlatformCommandDependencyColumns(t *testing.T, db *sql.DB, want int) {
	t.Helper()
	for _, column := range runtimePlatformCommandDependencyColumns {
		var count int
		if err := db.QueryRow(`SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
			WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?
			AND IS_NULLABLE = 'YES'`, runtimePlatformCommandTable, column).Scan(&count); err != nil {
			t.Fatalf("inspect Runtime command dependency column %s: %v", column, err)
		}
		if count != want {
			t.Fatalf("%s.%s nullable-column count = %d, want %d", runtimePlatformCommandTable, column, count, want)
		}
	}
}

func assertRuntimePlatformCommandUpgradeRow(t *testing.T, db *sql.DB, rowID string) {
	t.Helper()
	var commandID, state string
	var dependencyCount int
	if err := db.QueryRow(`SELECT CommandId, State,
		(DependencyKind IS NOT NULL) +
		(DependencyObjectAPIName IS NOT NULL) +
		(DependencyRecordId IS NOT NULL) +
		(DependencyKeyHash IS NOT NULL) +
		(DependencyConnectorOwnerKey IS NOT NULL) +
		(DependencyConnectorNamespace IS NOT NULL) +
		(DependencyConnectorSourceObject IS NOT NULL) +
		(DependencyDestinationSlotId IS NOT NULL) +
		(DependencyConnectorRemoteId IS NOT NULL) +
		(DependencyBlockedAt IS NOT NULL)
		FROM `+runtimePlatformCommandTable+` WHERE Id = ?`, rowID).Scan(&commandID, &state, &dependencyCount); err != nil {
		t.Fatalf("read upgraded Runtime command row: %v", err)
	}
	if commandID != "command-upgrade-proof" || state != "ACCEPTED" || dependencyCount != 0 {
		t.Fatalf("upgraded Runtime command row = command %q state %q dependency values %d, want command-upgrade-proof ACCEPTED and all dependency values NULL", commandID, state, dependencyCount)
	}
}