package persistence

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

	"github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/kernel/pkg/schemacatalog"
)

func TestIntegration_ConnectorCredentialGenerationSchemaPreservesExactImmutableFacts(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()

	definitions, err := schemacatalog.SystemTableDefinitions()
	if err != nil {
		t.Fatalf("SystemTableDefinitions(): %v", err)
	}
	byName := make(map[string]schema.TableDefinition, len(definitions))
	for _, definition := range definitions {
		byName[definition.TableName] = definition
	}
	const tableName = "_System_ConnectorCredentialGeneration"
	definition, ok := byName[tableName]
	if !ok {
		t.Fatalf("%s absent from canonical schema catalog", tableName)
	}

	repository := NewSchemaRepository(db, nil)
	for pass := 1; pass <= 2; pass++ {
		if err := repository.CreatePhysicalTable(context.Background(), definition); err != nil {
			t.Fatalf("CreatePhysicalTable(%s), pass %d: %v", tableName, pass, err)
		}
	}
	for _, column := range []string{
		"Id",
		"StableCredentialIdentity",
		"ProviderScopeId",
		"ConnectorAccountId",
		"SecretBackendProtocol",
		"SecretBackendVersionRef",
		"TransportConfigurationDigest",
		"ReceiptProtocolVersion",
		"OwnerReceiptDigest",
	} {
		requireConnectorCredentialGenerationBinaryColumn(t, db, tableName, column)
	}

	digest := func(value string) string {
		return "sha256:" + strings.Repeat(value, 64)
	}
	insertGeneration := `INSERT INTO _System_ConnectorCredentialGeneration (
		Id, StableCredentialIdentity, ProviderScopeId, ConnectorAccountId,
		SecretBackendProtocol, SecretBackendVersionRef,
		TransportConfiguration, TransportConfigurationDigest,
		ReceiptProtocolVersion, OwnerReceipt, OwnerReceiptDigest
	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
	transportConfiguration := []byte("connector-transport-configuration-v1\x00endpoint=https://example.invalid")
	ownerReceipt := []byte("connector-credential-generation-receipt-v1\x00generation-1")
	args := func(id, receiptDigest string, receipt []byte) []any {
		return []any{
			id,
			"credential-stable-1",
			"provider-scope-1",
			"connector-account-1",
			"nexuscrm.secret-backend.v1",
			"secret-version-1",
			transportConfiguration,
			digest("a"),
			"1.0",
			receipt,
			receiptDigest,
		}
	}
	if _, err := db.Exec(insertGeneration, args("generation-1", digest("b"), ownerReceipt)...); err != nil {
		t.Fatalf("insert first CredentialGeneration: %v", err)
	}
	caseDistinctReceipt := []byte("connector-credential-generation-receipt-v1\x00Generation-1")
	if _, err := db.Exec(insertGeneration, args("Generation-1", digest("c"), caseDistinctReceipt)...); err != nil {
		t.Fatalf("insert byte-distinct CredentialGeneration Id: %v", err)
	}
	if _, err := db.Exec(insertGeneration, args("generation-1", digest("d"), []byte("conflict"))...); !IsDuplicateKeyError(err) {
		t.Fatalf("duplicate CredentialGeneration Id error = %v, want duplicate-key rejection", err)
	}

	var (
		stableIdentity            string
		providerScopeID           string
		connectorAccountID        string
		secretBackendProtocol     string
		secretBackendVersionRef   string
		storedTransportConfig     []byte
		storedTransportConfigSize int
		storedOwnerReceipt        []byte
		storedOwnerReceiptSize    int
	)
	if err := db.QueryRow(
		`SELECT StableCredentialIdentity, ProviderScopeId, ConnectorAccountId,
		        SecretBackendProtocol, SecretBackendVersionRef,
		        TransportConfiguration, OCTET_LENGTH(TransportConfiguration),
		        OwnerReceipt, OCTET_LENGTH(OwnerReceipt)
		   FROM _System_ConnectorCredentialGeneration
		  WHERE Id = ?`,
		"generation-1",
	).Scan(
		&stableIdentity,
		&providerScopeID,
		&connectorAccountID,
		&secretBackendProtocol,
		&secretBackendVersionRef,
		&storedTransportConfig,
		&storedTransportConfigSize,
		&storedOwnerReceipt,
		&storedOwnerReceiptSize,
	); err != nil {
		t.Fatalf("read exact CredentialGeneration: %v", err)
	}
	if stableIdentity != "credential-stable-1" ||
		providerScopeID != "provider-scope-1" ||
		connectorAccountID != "connector-account-1" ||
		secretBackendProtocol != "nexuscrm.secret-backend.v1" ||
		secretBackendVersionRef != "secret-version-1" {
		t.Fatalf(
			"stored exact identities = %q/%q/%q/%q/%q",
			stableIdentity,
			providerScopeID,
			connectorAccountID,
			secretBackendProtocol,
			secretBackendVersionRef,
		)
	}
	if string(storedTransportConfig) != string(transportConfiguration) ||
		storedTransportConfigSize != len(transportConfiguration) ||
		string(storedOwnerReceipt) != string(ownerReceipt) ||
		storedOwnerReceiptSize != len(ownerReceipt) {
		t.Fatalf(
			"stored canonical bytes = transport:%x/%d receipt:%x/%d",
			storedTransportConfig,
			storedTransportConfigSize,
			storedOwnerReceipt,
			storedOwnerReceiptSize,
		)
	}
}

func requireConnectorCredentialGenerationBinaryColumn(t *testing.T, db interface {
	QueryRow(query string, args ...any) *sql.Row
}, tableName, columnName string) {
	t.Helper()

	var collation string
	if err := db.QueryRow(
		`SELECT COLLATION_NAME
		   FROM INFORMATION_SCHEMA.COLUMNS
		  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?`,
		tableName,
		columnName,
	).Scan(&collation); err != nil {
		t.Fatalf("inspect %s.%s physical collation: %v", tableName, columnName, err)
	}
	if collation != "utf8mb4_bin" {
		t.Fatalf("%s.%s physical collation = %q, want utf8mb4_bin", tableName, columnName, collation)
	}
}