package bootstrap

import (
	"context"
	"encoding/json"
	"log"
	"strings"

	"github.com/nexuscrm/backend/internal/infrastructure/database"
)

// BackfillLookupReferences updates all Lookup fields that have NULL reference_to
// by inferring the target object from the field name pattern (e.g., account_id -> account)
// This ensures reference fields have proper relationship metadata.
func BackfillLookupReferences(db *database.TiDBConnection) error {
	log.Println("🔗 Backfilling reference_to for Lookup fields...")

	// Query all reference/masterdetail fields with NULL or empty reference_to
	query := `
		SELECT
			Id,
			ApiName,
			Type
		FROM _System_Field
		WHERE Type IN ('reference', 'masterdetail')
		  AND (ReferenceTo IS NULL OR ReferenceTo = '[]' OR ReferenceTo = '')`

	rows, err := db.DB().QueryContext(context.Background(), query)
	if err != nil {
		log.Printf("   ⚠️  Error querying reference fields: %v", err)
		return err
	}
	defer rows.Close()

	type fieldRecord struct {
		ID      string
		APIName string
		Type    string
	}

	var fieldsToUpdate []fieldRecord
	for rows.Next() {
		var rec fieldRecord
		if err := rows.Scan(&rec.ID, &rec.APIName, &rec.Type); err != nil {
			return err
		}
		fieldsToUpdate = append(fieldsToUpdate, rec)
	}

	if err := rows.Err(); err != nil {
		return err
	}

	if len(fieldsToUpdate) == 0 {
		log.Println("   ✅ No reference fields need reference_to backfill")
		return nil
	}

	log.Printf("   🔧 Found %d reference fields with NULL reference_to", len(fieldsToUpdate))

	// Update each field with inferred reference_to
	updateQuery := `
		UPDATE _System_Field
		SET ReferenceTo = ?
		WHERE Id = ?
	`

	updatedCount := 0
	for _, rec := range fieldsToUpdate {
		referenceTo := inferReferenceToFromField(rec.APIName)
		if referenceTo == "" {
			log.Printf("   ⚠️  Cannot infer reference_to for field: %s", rec.APIName)
			continue
		}

		// Serialize as JSON array
		refJSON, err := json.Marshal([]string{referenceTo})
		if err != nil {
			log.Printf("   ⚠️  Failed to serialize reference_to for %s: %v", rec.APIName, err)
			continue
		}

		_, err = db.DB().ExecContext(context.Background(), updateQuery, string(refJSON), rec.ID)
		if err != nil {
			log.Printf("   ⚠️  Failed to update reference_to for %s: %v", rec.APIName, err)
			continue
		}

		log.Printf("   ✅ Backfilled %s: reference_to=%s", rec.APIName, referenceTo)
		updatedCount++
	}

	log.Printf("   ✅ Backfilled reference_to for %d fields", updatedCount)
	return nil
}

// inferReferenceToFromField infers the target object from field name
// Examples:
//   - account_id -> account
//   - contact_id -> contact
//   - parent_account_id -> account
func inferReferenceToFromField(apiName string) string {
	lowerName := strings.ToLower(apiName)

	// Remove _id suffix
	if strings.HasSuffix(lowerName, "_id") {
		baseName := lowerName[:len(lowerName)-3]

		// Handle common patterns like parent_account_id -> account
		baseName = strings.TrimPrefix(baseName, "parent_")

		return baseName
	}

	// Common system fields
	switch lowerName {
	case "owner_id", "ownerid":
		return "user"
	case "created_by_id", "createdbyid":
		return "user"
	case "last_modified_by_id", "lastmodifiedbyid":
		return "user"
	}

	return ""
}