//go:build ignore

package main

import (
	"bufio"
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"os"
	"path/filepath"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"golang.org/x/crypto/bcrypt"
)

// loadEnvFile loads environment variables from .env file
func loadEnvFile() {
	cwd, err := os.Getwd()
	if err != nil {
		return
	}

	projectRoot, ok := findProjectRoot(cwd)
	if !ok {
		return
	}

	// Prefer .env.test exclusively when present so local E2E runs stay
	// isolated from the main .env (which may point at staging/prod DBs).
	envFiles := []string{
		filepath.Join(projectRoot, ".env.test"),
		filepath.Join(projectRoot, ".env"),
	}
	if _, err := os.Stat(envFiles[0]); err == nil {
		envFiles = envFiles[:1]
	}
	for _, envFile := range envFiles {
		file, err := os.Open(envFile)
		if err != nil {
			continue
		}
		scanner := bufio.NewScanner(file)
		for scanner.Scan() {
			line := strings.TrimSpace(scanner.Text())
			if line == "" || strings.HasPrefix(line, "#") {
				continue
			}

			parts := strings.SplitN(line, "=", 2)
			if len(parts) != 2 {
				continue
			}

			key := strings.TrimSpace(parts[0])
			value := strings.TrimSpace(parts[1])
			if os.Getenv(key) == "" {
				os.Setenv(key, value)
			}
		}
		_ = file.Close()
	}
}

func findProjectRoot(start string) (string, bool) {
	dir := start
	for {
		packageJSON := filepath.Join(dir, "package.json")
		backendDir := filepath.Join(dir, "backend")
		frontendDir := filepath.Join(dir, "frontend")
		if fileExists(packageJSON) && dirExists(backendDir) && dirExists(frontendDir) {
			return dir, true
		}

		parent := filepath.Dir(dir)
		if parent == dir {
			return "", false
		}
		dir = parent
	}
}

func fileExists(path string) bool {
	info, err := os.Stat(path)
	return err == nil && !info.IsDir()
}

func dirExists(path string) bool {
	info, err := os.Stat(path)
	return err == nil && info.IsDir()
}

// TestUser represents a test user account
type TestUser struct {
	Username  string `json:"username"`
	Email     string `json:"email"`
	Password  string `json:"password"`
	ProfileID string `json:"profile_id"`
}

// TestData represents seed data for E2E tests
type TestData struct {
	Users         []TestUser    `json:"users"`
	Accounts      []Account     `json:"accounts"`
	Contacts      []Contact     `json:"contacts"`
	Opportunities []Opportunity `json:"opportunities"`
}

type Account struct {
	Name          string  `json:"name"`
	Industry      string  `json:"industry"`
	AnnualRevenue float64 `json:"annual_revenue"`
}

type Contact struct {
	FirstName string `json:"first_name"`
	LastName  string `json:"last_name"`
	Email     string `json:"email"`
	Phone     string `json:"phone"`
}

type Opportunity struct {
	Name        string  `json:"name"`
	Stage       string  `json:"stage"`
	Amount      float64 `json:"amount"`
	CloseDate   string  `json:"close_date"`
	Probability int     `json:"probability"`
}

type StandardObjectSeed struct {
	APIName     string
	Label       string
	PluralLabel string
	Description string
	ListFields  []string
	Fields      []StandardFieldSeed
}

type StandardFieldSeed struct {
	APIName          string
	Label            string
	Type             string
	PhysicalName     string
	Required         bool
	IsSystem         bool
	IsNameField      bool
	ReferenceTo      []string
	RelationshipName string
}

func main() {
	// Try to load .env file if exists
	loadEnvFile()

	host := os.Getenv("TIDB_HOST")
	port := os.Getenv("TIDB_PORT")
	user := os.Getenv("TIDB_USER")
	password := os.Getenv("TIDB_PASSWORD")
	dbName := os.Getenv("TIDB_DATABASE")

	if host == "" {
		host = "localhost"
	}
	if port == "" {
		port = "4000"
	}
	if user == "" {
		user = "root"
	}
	if dbName == "" {
		dbName = "nexuscrm_test" // Use nexuscrm_test as default for E2E tests
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		user, password, host, port, dbName)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Fatalf("Failed to open database: %v", err)
	}
	defer db.Close()

	if err := db.Ping(); err != nil {
		log.Fatalf("Failed to connect to database: %v", err)
	}

	fmt.Println("Connected to database:", dbName)

	// Seed test data
	if err := seedUsers(db); err != nil {
		log.Fatalf("Failed to seed users: %v", err)
	}

	if err := seedAccounts(db); err != nil {
		log.Fatalf("Failed to seed accounts: %v", err)
	}

	if err := seedContacts(db); err != nil {
		log.Fatalf("Failed to seed contacts: %v", err)
	}

	if err := seedOpportunities(db); err != nil {
		log.Fatalf("Failed to seed opportunities: %v", err)
	}

	fmt.Println("\n✓ Test data seeded successfully!")
}

func seededID(prefix string) string {
	return fmt.Sprintf("%s-%x", prefix, time.Now().UnixNano())
}

func seedUsers(db *sql.DB) error {
	fmt.Println("\nSeeding users...")

	requiredProfiles := []struct {
		ID          string
		Name        string
		Description string
		IsSystem    bool
	}{
		{
			ID:          "system_admin",
			Name:        "System Administrator",
			Description: "Full system access with all permissions",
			IsSystem:    true,
		},
		{
			ID:          "standard_user",
			Name:        "Standard User",
			Description: "Standard access to CRM features",
			IsSystem:    true,
		},
	}

	for _, profile := range requiredProfiles {
		_, err := db.Exec(`
			INSERT INTO Profile (Id, Name, Description, IsActive, IsSystem, CreatedDate, LastModifiedDate)
			VALUES (?, ?, ?, 1, ?, NOW(), NOW())
			ON DUPLICATE KEY UPDATE
				Name = VALUES(Name),
				Description = VALUES(Description),
				IsSystem = VALUES(IsSystem),
				LastModifiedDate = NOW()
		`, profile.ID, profile.Name, profile.Description, profile.IsSystem)
		if err != nil {
			return fmt.Errorf("failed to ensure profile %s: %w", profile.ID, err)
		}
	}

	users := []TestUser{
		{
			Username:  "testuser",
			Email:     "testuser@nexuscrm.test",
			Password:  "Test123!",
			ProfileID: "standard_user",
		},
		{
			Username:  "readonly",
			Email:     "readonly@nexuscrm.test",
			Password:  "Read123!",
			ProfileID: "standard_user",
		},
	}

	for _, u := range users {
		// Check if user exists
		var exists bool
		err := db.QueryRow("SELECT EXISTS(SELECT 1 FROM User WHERE Username = ?)", u.Username).Scan(&exists)
		if err != nil {
			return err
		}

		if exists {
			fmt.Printf("  - User '%s' already exists, skipping\n", u.Username)
			continue
		}

		// Hash password
		hashedPassword, err := bcrypt.GenerateFromPassword([]byte(u.Password), bcrypt.DefaultCost)
		if err != nil {
			return fmt.Errorf("failed to hash password: %w", err)
		}

		var profileExists bool
		err = db.QueryRow("SELECT EXISTS(SELECT 1 FROM Profile WHERE Id = ?)", u.ProfileID).Scan(&profileExists)
		if err != nil {
			return fmt.Errorf("failed to check profile %s: %w", u.ProfileID, err)
		}
		if !profileExists {
			return fmt.Errorf("required profile %s does not exist", u.ProfileID)
		}

		// Generate user ID
		userID := fmt.Sprintf("user_%s_%d", u.Username, time.Now().UnixNano())

		// Insert user
		_, err = db.Exec(`INSERT INTO User
			(Id, Username, Email, Password, FirstName, LastName, ProfileId, IsActive, CreatedDate, LastModifiedDate)
			VALUES (?, ?, ?, ?, 'System', 'User', ?, 1, NOW(), NOW())`,
			userID, u.Username, u.Email, string(hashedPassword), u.ProfileID)
		if err != nil {
			return fmt.Errorf("failed to insert user %s: %w", u.Username, err)
		}

		fmt.Printf("  + Created user: %s (%s)\n", u.Username, u.Email)
	}

	return nil
}

func seedAccounts(db *sql.DB) error {
	fmt.Println("\nSeeding accounts...")

	// Create Account table if not exists
	_, err := db.Exec(`CREATE TABLE IF NOT EXISTS Account (
		Id VARCHAR(255) NOT NULL PRIMARY KEY,
		Name VARCHAR(255) NOT NULL,
		Industry VARCHAR(255),
		AnnualRevenue DECIMAL(18,2),
		OwnerId VARCHAR(255),
		CreatedById VARCHAR(255),
		LastModifiedById VARCHAR(255),
		CreatedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		LastModifiedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		IsDeleted TINYINT(1) NOT NULL DEFAULT 0
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`)
	if err != nil {
		fmt.Printf("  - Failed to create Account table: %v\n", err)
		return nil
	}

	if err := ensureStandardObjectMetadata(db, StandardObjectSeed{
		APIName:     "Account",
		Label:       "Account",
		PluralLabel: "Accounts",
		Description: "Standard account object for e2e tests",
		ListFields:  []string{"Name", "Industry", "AnnualRevenue"},
		Fields: []StandardFieldSeed{
			{APIName: "Id", Label: "Record ID", Type: "string", PhysicalName: "Id", Required: true, IsSystem: true},
			{APIName: "Name", Label: "Account Name", Type: "string", PhysicalName: "Name", Required: true, IsNameField: true},
			{APIName: "Industry", Label: "Industry", Type: "string", PhysicalName: "Industry"},
			{APIName: "AnnualRevenue", Label: "Annual Revenue", Type: "currency", PhysicalName: "AnnualRevenue"},
			{APIName: "OwnerId", Label: "Owner", Type: "reference", PhysicalName: "OwnerId", IsSystem: true, ReferenceTo: []string{"User"}, RelationshipName: "Owner"},
			{APIName: "CreatedById", Label: "Created By", Type: "reference", PhysicalName: "CreatedById", IsSystem: true, ReferenceTo: []string{"User"}, RelationshipName: "CreatedBy"},
			{APIName: "LastModifiedById", Label: "Last Modified By", Type: "reference", PhysicalName: "LastModifiedById", IsSystem: true, ReferenceTo: []string{"User"}, RelationshipName: "LastModifiedBy"},
			{APIName: "CreatedDate", Label: "Created Date", Type: "datetime", PhysicalName: "CreatedDate", IsSystem: true},
			{APIName: "LastModifiedDate", Label: "Last Modified Date", Type: "datetime", PhysicalName: "LastModifiedDate", IsSystem: true},
			{APIName: "IsDeleted", Label: "Deleted", Type: "boolean", PhysicalName: "IsDeleted", IsSystem: true},
		},
	}); err != nil {
		return fmt.Errorf("failed to register Account metadata: %w", err)
	}

	accounts := []Account{
		{"Acme Corporation", "Technology", 10000000},
		{"Global Industries", "Manufacturing", 50000000},
		{"TechStart Inc", "Software", 2000000},
		{"MegaCorp", "Retail", 100000000},
		{"Small Biz LLC", "Services", 500000},
	}

	for _, a := range accounts {
		// Check if account exists
		var exists bool
		err := db.QueryRow("SELECT EXISTS(SELECT 1 FROM Account WHERE Name = ?)", a.Name).Scan(&exists)
		if err != nil {
			return err
		}

		if exists {
			fmt.Printf("  - Account '%s' already exists, skipping\n", a.Name)
			continue
		}

		accountID := seededID("acct")
		_, err = db.Exec(`INSERT INTO Account (
			Id, Name, Industry, AnnualRevenue, CreatedDate, LastModifiedDate, SystemModstamp
		) VALUES (?, ?, ?, ?, NOW(), NOW(), NOW())`,
			accountID, a.Name, a.Industry, a.AnnualRevenue)
		if err != nil {
			fmt.Printf("  - Failed to insert account %s: %v\n", a.Name, err)
			continue
		}

		fmt.Printf("  + Created account: %s\n", a.Name)
	}

	return nil
}

func seedContacts(db *sql.DB) error {
	fmt.Println("\nSeeding contacts...")

	// Create Contact table if not exists
	_, err := db.Exec(`CREATE TABLE IF NOT EXISTS Contact (
		Id VARCHAR(255) NOT NULL PRIMARY KEY,
		FirstName VARCHAR(255) NOT NULL,
		LastName VARCHAR(255) NOT NULL,
		Email VARCHAR(255),
		Phone VARCHAR(255),
		AccountId VARCHAR(255),
		OwnerId VARCHAR(255),
		CreatedById VARCHAR(255),
		LastModifiedById VARCHAR(255),
		CreatedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		LastModifiedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		IsDeleted TINYINT(1) NOT NULL DEFAULT 0
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`)
	if err != nil {
		fmt.Printf("  - Failed to create Contact table: %v\n", err)
		return nil
	}

	if err := ensureStandardObjectMetadata(db, StandardObjectSeed{
		APIName:     "Contact",
		Label:       "Contact",
		PluralLabel: "Contacts",
		Description: "Standard contact object for e2e tests",
		ListFields:  []string{"FirstName", "LastName", "Email", "Phone"},
		Fields: []StandardFieldSeed{
			{APIName: "Id", Label: "Record ID", Type: "string", PhysicalName: "Id", Required: true, IsSystem: true},
			{APIName: "FirstName", Label: "First Name", Type: "string", PhysicalName: "FirstName", Required: true},
			{APIName: "LastName", Label: "Last Name", Type: "string", PhysicalName: "LastName", Required: true, IsNameField: true},
			{APIName: "Email", Label: "Email", Type: "email", PhysicalName: "Email"},
			{APIName: "Phone", Label: "Phone", Type: "phone", PhysicalName: "Phone"},
			{APIName: "AccountId", Label: "Account", Type: "reference", PhysicalName: "AccountId", ReferenceTo: []string{"Account"}, RelationshipName: "Account"},
			{APIName: "OwnerId", Label: "Owner", Type: "reference", PhysicalName: "OwnerId", IsSystem: true, ReferenceTo: []string{"User"}, RelationshipName: "Owner"},
			{APIName: "CreatedById", Label: "Created By", Type: "reference", PhysicalName: "CreatedById", IsSystem: true, ReferenceTo: []string{"User"}, RelationshipName: "CreatedBy"},
			{APIName: "LastModifiedById", Label: "Last Modified By", Type: "reference", PhysicalName: "LastModifiedById", IsSystem: true, ReferenceTo: []string{"User"}, RelationshipName: "LastModifiedBy"},
			{APIName: "CreatedDate", Label: "Created Date", Type: "datetime", PhysicalName: "CreatedDate", IsSystem: true},
			{APIName: "LastModifiedDate", Label: "Last Modified Date", Type: "datetime", PhysicalName: "LastModifiedDate", IsSystem: true},
			{APIName: "IsDeleted", Label: "Deleted", Type: "boolean", PhysicalName: "IsDeleted", IsSystem: true},
		},
	}); err != nil {
		return fmt.Errorf("failed to register Contact metadata: %w", err)
	}

	contacts := []Contact{
		{"John", "Doe", "john.doe@acme.test", "555-0101"},
		{"Jane", "Smith", "jane.smith@global.test", "555-0102"},
		{"Bob", "Johnson", "bob.j@techstart.test", "555-0103"},
		{"Alice", "Williams", "alice.w@megacorp.test", "555-0104"},
		{"Charlie", "Brown", "charlie.b@smallbiz.test", "555-0105"},
	}

	for _, c := range contacts {
		// Check if contact exists
		var exists bool
		err := db.QueryRow("SELECT EXISTS(SELECT 1 FROM Contact WHERE Email = ?)", c.Email).Scan(&exists)
		if err != nil {
			return err
		}

		if exists {
			fmt.Printf("  - Contact '%s' already exists, skipping\n", c.Email)
			continue
		}

		contactID := seededID("cont")
		_, err = db.Exec(`INSERT INTO Contact (
			Id, FirstName, LastName, Email, Phone, CreatedDate, LastModifiedDate, SystemModstamp
		) VALUES (?, ?, ?, ?, ?, NOW(), NOW(), NOW())`,
			contactID, c.FirstName, c.LastName, c.Email, c.Phone)
		if err != nil {
			fmt.Printf("  - Failed to insert contact %s %s: %v\n", c.FirstName, c.LastName, err)
			continue
		}

		fmt.Printf("  + Created contact: %s %s\n", c.FirstName, c.LastName)
	}

	return nil
}

func seedOpportunities(db *sql.DB) error {
	fmt.Println("\nSeeding opportunities...")

	// Create Opportunity table if not exists
	_, err := db.Exec(`CREATE TABLE IF NOT EXISTS Opportunity (
		Id VARCHAR(255) NOT NULL PRIMARY KEY,
		Name VARCHAR(255) NOT NULL,
		Stage VARCHAR(255),
		Amount DECIMAL(18,2),
		CloseDate DATE,
		Probability DECIMAL(65,30),
		AccountId VARCHAR(255),
		OwnerId VARCHAR(255),
		CreatedById VARCHAR(255),
		LastModifiedById VARCHAR(255),
		CreatedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		LastModifiedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		IsDeleted TINYINT(1) NOT NULL DEFAULT 0
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`)
	if err != nil {
		fmt.Printf("  - Failed to create Opportunity table: %v\n", err)
		return nil
	}

	if err := ensureStandardObjectMetadata(db, StandardObjectSeed{
		APIName:     "Opportunity",
		Label:       "Opportunity",
		PluralLabel: "Opportunities",
		Description: "Standard opportunity object for e2e tests",
		ListFields:  []string{"Name", "StageName", "Amount", "CloseDate"},
		Fields: []StandardFieldSeed{
			{APIName: "Id", Label: "Record ID", Type: "string", PhysicalName: "Id", Required: true, IsSystem: true},
			{APIName: "Name", Label: "Opportunity Name", Type: "string", PhysicalName: "Name", Required: true, IsNameField: true},
			{APIName: "StageName", Label: "Stage", Type: "string", PhysicalName: "StageName"},
			{APIName: "Amount", Label: "Amount", Type: "currency", PhysicalName: "Amount"},
			{APIName: "CloseDate", Label: "Close Date", Type: "date", PhysicalName: "CloseDate"},
			{APIName: "Probability", Label: "Probability", Type: "percent", PhysicalName: "Probability"},
			{APIName: "AccountId", Label: "Account", Type: "reference", PhysicalName: "AccountId", ReferenceTo: []string{"Account"}, RelationshipName: "Account"},
			{APIName: "OwnerId", Label: "Owner", Type: "reference", PhysicalName: "OwnerId", IsSystem: true, ReferenceTo: []string{"User"}, RelationshipName: "Owner"},
			{APIName: "CreatedById", Label: "Created By", Type: "reference", PhysicalName: "CreatedById", IsSystem: true, ReferenceTo: []string{"User"}, RelationshipName: "CreatedBy"},
			{APIName: "LastModifiedById", Label: "Last Modified By", Type: "reference", PhysicalName: "LastModifiedById", IsSystem: true, ReferenceTo: []string{"User"}, RelationshipName: "LastModifiedBy"},
			{APIName: "CreatedDate", Label: "Created Date", Type: "datetime", PhysicalName: "CreatedDate", IsSystem: true},
			{APIName: "LastModifiedDate", Label: "Last Modified Date", Type: "datetime", PhysicalName: "LastModifiedDate", IsSystem: true},
			{APIName: "IsDeleted", Label: "Deleted", Type: "boolean", PhysicalName: "IsDeleted", IsSystem: true},
		},
	}); err != nil {
		return fmt.Errorf("failed to register Opportunity metadata: %w", err)
	}

	opportunities := []Opportunity{
		{"Enterprise Deal", "Prospecting", 500000, "2026-06-30", 20},
		{"Cloud Migration", "Qualification", 250000, "2026-05-15", 40},
		{"Digital Transformation", "Proposal", 1000000, "2026-07-31", 60},
		{"Software Upgrade", "Negotiation", 150000, "2026-04-30", 80},
		{"Consulting Services", "Closed Won", 75000, time.Now().Format("2006-01-02"), 100},
	}

	for _, o := range opportunities {
		// Check if opportunity exists
		var exists bool
		err := db.QueryRow("SELECT EXISTS(SELECT 1 FROM Opportunity WHERE Name = ?)", o.Name).Scan(&exists)
		if err != nil {
			return err
		}

		if exists {
			fmt.Printf("  - Opportunity '%s' already exists, skipping\n", o.Name)
			continue
		}

		oppID := seededID("opp")
		_, err = db.Exec(`INSERT INTO Opportunity (
			Id, Name, StageName, Amount, CloseDate, Probability, CreatedDate, LastModifiedDate, SystemModstamp
		) VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW(), NOW())`,
			oppID, o.Name, o.Stage, o.Amount, o.CloseDate, o.Probability)
		if err != nil {
			fmt.Printf("  - Failed to insert opportunity %s: %v\n", o.Name, err)
			continue
		}

		fmt.Printf("  + Created opportunity: %s ($%.0f)\n", o.Name, o.Amount)
	}

	return nil
}

func init() {
	// Suppress unused import error
	_ = json.Marshal
}

func ensureStandardObjectMetadata(db *sql.DB, seed StandardObjectSeed) error {
	objectID := fmt.Sprintf("seed_object_%s", strings.ToLower(seed.APIName))

	var listFields interface{}
	if len(seed.ListFields) > 0 {
		listFieldsJSON, err := json.Marshal(seed.ListFields)
		if err != nil {
			return fmt.Errorf("failed to marshal list fields for %s: %w", seed.APIName, err)
		}
		listFields = string(listFieldsJSON)
	}

	// The control plane is authoritative for standard-object metadata. E2E
	// seeding may run after cmd/setup apply, so never overwrite existing rows.
	err := db.QueryRow(`SELECT Id FROM _System_Object WHERE ApiName = ?`, seed.APIName).Scan(&objectID)
	if err != nil {
		if err != sql.ErrNoRows {
			return fmt.Errorf("failed to check object metadata for %s: %w", seed.APIName, err)
		}

		_, err = db.Exec(`
			INSERT INTO _System_Object (
				Id, ApiName, TableType, Label, PluralLabel, Description,
				IsCustom, Searchable, ListFields, CreatedDate, LastModifiedDate
			) VALUES (?, ?, 'standard_object', ?, ?, ?, 0, 1, ?, NOW(), NOW())
		`, objectID, seed.APIName, seed.Label, seed.PluralLabel, seed.Description, listFields)
		if err != nil {
			return err
		}
	}

	for _, field := range seed.Fields {
		if err := ensureStandardFieldMetadata(db, objectID, seed.APIName, field); err != nil {
			return err
		}
	}

	return nil
}

func ensureStandardFieldMetadata(db *sql.DB, objectID, objectAPIName string, field StandardFieldSeed) error {
	fieldID := fmt.Sprintf("seed_field_%s_%s", strings.ToLower(objectAPIName), strings.ToLower(field.APIName))

	var exists bool
	if err := db.QueryRow(`
		SELECT EXISTS(
			SELECT 1 FROM _System_Field
			WHERE ObjectId = ? AND ApiName = ?
		)
	`, objectID, field.APIName).Scan(&exists); err != nil {
		return fmt.Errorf("failed to check field metadata for %s.%s: %w", objectAPIName, field.APIName, err)
	}
	if exists {
		return nil
	}

	physicalName := field.PhysicalName
	if physicalName == "" {
		physicalName = field.APIName
	}

	var referenceTo interface{}
	if len(field.ReferenceTo) > 0 {
		referenceToJSON, err := json.Marshal(field.ReferenceTo)
		if err != nil {
			return fmt.Errorf("failed to marshal referenceTo for %s.%s: %w", objectAPIName, field.APIName, err)
		}
		referenceTo = string(referenceToJSON)
	}

	var relationshipName interface{}
	if field.RelationshipName != "" {
		relationshipName = field.RelationshipName
	}

	_, err := db.Exec(`
		INSERT INTO _System_Field (
			Id, ObjectId, ApiName, Label, PhysicalName, Type,
			Required, IsSystem, IsNameField, ReferenceTo, RelationshipName,
			CreatedDate, LastModifiedDate
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
	`, fieldID, objectID, field.APIName, field.Label, physicalName, field.Type, field.Required, field.IsSystem, field.IsNameField, referenceTo, relationshipName)
	if err != nil {
		return fmt.Errorf("failed to register field metadata for %s.%s: %w", objectAPIName, field.APIName, err)
	}

	return nil
}