package persistence

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

	"github.com/nexuscrm/shared/pkg/constants"
)

// SchemaDefinition mirrors the structure of system_tables.json
type SchemaDefinition struct {
	TableName string `json:"tableName"`
	Columns   []struct {
		Name string `json:"name"`
	} `json:"columns"`
}

// TestColumnParity ensures that the hardcoded SQL column lists in metadata_repository.go
// matches the source of truth in system_tables.json.
func TestColumnParity(t *testing.T) {
	// Locate system_tables.json relative to this test file
	// We are in internal/infrastructure/persistence
	// JSON is in internal/bootstrap
	path := "../../../internal/bootstrap/system_tables.json"
	absPath, err := filepath.Abs(path)
	if err != nil {
		t.Fatalf("Failed to resolve absolute path: %v", err)
	}

	data, err := os.ReadFile(absPath)
	if err != nil {
		t.Fatalf("Failed to read system_tables.json at %s: %v", absPath, err)
	}

	var definitions []SchemaDefinition
	if err := json.Unmarshal(data, &definitions); err != nil {
		t.Fatalf("Failed to parse schema: %v", err)
	}

	// Map definitions for lookup
	schemaMap := make(map[string]map[string]bool)
	for _, def := range definitions {
		cols := make(map[string]bool)
		for _, col := range def.Columns {
			cols[col.Name] = true
			// Physical Names cleanly mapped without systemic prefix tricks
		}
		schemaMap[def.TableName] = cols
	}

	// Define the mapping from Go variable to JSON Table Name
	// Note: We only check if the Go slice *contains* the JSON columns.
	// Go slice may imply logical aliasing, so we check stricter:
	// "Every column in the Go slice MUST exist in the JSON definition (unless it's a known alias)"
	tests := []struct {
		GoSlice   []string
		TableName string
		VarName   string
	}{
		{constants.ColsSystemObject, "_System_Object", "ColsSystemObject"},
		{constants.ColsSystemField, "_System_Field", "ColsSystemField"},
		{constants.ColsSystemAction, "_System_Action", "ColsSystemAction"},
		{constants.ColsSystemValidationRule, "_System_ValidationRule", "ColsSystemValidationRule"},
		{constants.ColsSharingRule, "SharingRule", "ColsSharingRule"},
	}

	for _, tt := range tests {
		t.Run(tt.VarName, func(t *testing.T) {
			jsonCols, exists := schemaMap[tt.TableName]
			if !exists {
				t.Errorf("Table %s not found in system_tables.json", tt.TableName)
				return
			}

			// 1. Check if Go slice has columns not in JSON (Drift: Code expects column that doesn't exist)
			for _, col := range tt.GoSlice {
				// Handle known aliases or computed columns if any
				if col == "physical_name" {
					continue // Virtual field in scan
				}
				if !jsonCols[col] {
					t.Errorf("Code Variable '%s' expects column '%s' but it is MISSING in system_tables.json definition for table '%s'", tt.VarName, col, tt.TableName)
				}
			}

			// 2. Check coverage (Optional: Does Code miss any JSON columns? Maybe okay if unused, but good to know)
			// For now, strict correctness is (1).
		})
	}
}