package constants

import (
	"encoding/json"
	"os"
	"path/filepath"
	"strings"
	"testing"
)

// schemaCatalogRelPath is the live system-table catalog, read as DATA (not
// imported — shared must not depend on nexuscrm-kernel). It is the source of
// truth for which _System_ tables exist at runtime.
const schemaCatalogRelPath = "../../../nexuscrm-kernel/pkg/schemacatalog/system_tables.json"

type catalogTable struct {
	TableName string          `json:"tableName"`
	Columns   []catalogColumn `json:"columns"`
}

type catalogColumn struct {
	Name string `json:"name"`
}

// TestAllSystemTableNamesCoversSchemaCatalog binds the explicit table inventory
// to the live schema catalog so constants, scope-floor documentation, and
// inventory consumers cannot silently drift behind it again (#3484).
//
// The historical codegen source (internal/bootstrap/system_tables.json) and its
// tool no longer exist, so AllSystemTableNames had fallen 28 tables behind the
// catalog. IsManagedSystemTable now independently fail-closes every _System_
// prefix before consulting this inventory, so an omitted entry is not a
// destructive-operation bypass. This test still fails the moment a new table
// lands without its deliberate constants and documentation propagation.
func TestAllSystemTableNamesCoversSchemaCatalog(t *testing.T) {
	path, err := filepath.Abs(schemaCatalogRelPath)
	if err != nil {
		t.Fatalf("resolve catalog path: %v", err)
	}
	raw, err := os.ReadFile(path)
	if err != nil {
		// The catalog lives in a sibling module; if the checkout layout differs
		// the freshness invariant simply cannot be evaluated here. Skip rather
		// than fail so a partial checkout does not produce a spurious red.
		t.Skipf("schema catalog not readable at %s: %v", path, err)
	}

	var tables []catalogTable
	if err := json.Unmarshal(raw, &tables); err != nil {
		t.Fatalf("parse schema catalog: %v", err)
	}

	// Build a case-insensitive membership set of AllSystemTableNames.
	managed := make(map[string]struct{}, len(AllSystemTableNames))
	for _, name := range AllSystemTableNames {
		managed[strings.ToLower(name)] = struct{}{}
	}

	var missing []string
	seen := 0
	for _, ct := range tables {
		if !strings.HasPrefix(ct.TableName, SystemTablePrefix) {
			continue // only _System_ tables are catalog-owned substrate here
		}
		seen++
		if _, ok := managed[strings.ToLower(ct.TableName)]; !ok {
			missing = append(missing, ct.TableName)
		}
	}

	if seen == 0 {
		t.Fatalf("schema catalog at %s declared no _System_ tables — path or format changed", path)
	}
	if len(missing) > 0 {
		t.Fatalf("AllSystemTableNames is missing %d _System_ table(s) present in the schema catalog: %v\n"+
			"add them to shared/pkg/constants/z_generated_tables.go and the managed-table scope floor",
			len(missing), missing)
	}

	// Also assert the specific substrate tables the #3484 hole named, so a
	// reader sees the concrete regression this guards.
	for _, critical := range []string{
		"_System_InstalledPack",
		"_System_NamedCredentialSecret",
		"_System_FlowAuditLog",
		"_System_ConnectorEvent",
		"_System_RuntimeFlowInvocation",
	} {
		if !IsManagedSystemTable(critical) {
			t.Fatalf("IsManagedSystemTable(%q) = false, want true (substrate must be guarded)", critical)
		}
		// The routing layer lowercases names; the guard must still catch them.
		if !IsManagedSystemTable(strings.ToLower(critical)) {
			t.Fatalf("IsManagedSystemTable(%q) = false, want true (lowercased route form must be guarded)", strings.ToLower(critical))
		}
	}
}

// TestGeneratedColumnsMatchSchemaCatalog protects the generated select-column
// projections that persistence uses for security-sensitive protocol state.
// A catalog field without its generated projection silently becomes invisible
// to a read/round-trip path, so keep these tables tied to their one data SSoT.
func TestGeneratedColumnsMatchSchemaCatalog(t *testing.T) {
	path, err := filepath.Abs(schemaCatalogRelPath)
	if err != nil {
		t.Fatalf("resolve catalog path: %v", err)
	}
	raw, err := os.ReadFile(path)
	if err != nil {
		t.Skipf("schema catalog not readable at %s: %v", path, err)
	}
	var tables []catalogTable
	if err := json.Unmarshal(raw, &tables); err != nil {
		t.Fatalf("parse schema catalog: %v", err)
	}

	catalogColumns := make(map[string][]string, len(tables))
	for _, table := range tables {
		columns := make([]string, 0, len(table.Columns))
		for _, column := range table.Columns {
			columns = append(columns, column.Name)
		}
		catalogColumns[table.TableName] = columns
	}

	constants := map[string][]string{
		TableConnectedAuthorizationRequest: ColsSystemConnectedAuthorizationRequest,
		TableConnectedDeliveryJob:          ColsSystemConnectedDeliveryJob,
		TableConnectedDeliveryPayload:      ColsSystemConnectedDeliveryPayload,
		TableConnectedPrincipal:            ColsSystemConnectedPrincipal,
		TableConnectedPrincipalToken:       ColsSystemConnectedPrincipalToken,
		TableOAuthAuthorizeRequest:         ColsSystemOAuthAuthorizeRequest,
		TableOAuthCode:                     ColsSystemOAuthCode,
	}
	for tableName, columns := range constants {
		catalog, ok := catalogColumns[tableName]
		if !ok {
			t.Fatalf("schema catalog does not contain %s", tableName)
		}
		if strings.Join(columns, ",") != strings.Join(catalog, ",") {
			t.Fatalf("%s constants = %v, catalog columns = %v", tableName, columns, catalog)
		}
	}
}