package controlplane

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"log"

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

// BackfillLookupReferences updates all Lookup fields that have NULL reference_to
// within bootstrap-owned metadata. L0 managed table definitions and
// platform-universal field targets are the only authoritative sources this
// repair may use; app-pack/admin/discovery rows are reconciled by their owning
// metadata path.
func BackfillLookupReferences(db *database.TiDBConnection) error {
	log.Println("🔗 Backfilling reference_to for Lookup fields...")

	repairTargets := schemaProvisioner.LookupReferenceRepairTargets()

	// Query active reference/masterdetail candidates with object names. The row
	// loop below applies the ownership scope so bootstrap only mutates
	// L0/platform facts.
	query := `
			SELECT
				f.Id,
				o.ApiName,
				f.ApiName,
				f.Type
			FROM _System_Field f
			JOIN _System_Object o ON f.ObjectId = o.Id
			WHERE f.Type IN (?, ?)
			  AND COALESCE(f.IsDeleted, 0) = 0
			  AND COALESCE(o.IsDeleted, 0) = 0
			  AND (f.ReferenceTo IS NULL OR TRIM(f.ReferenceTo) = '' OR TRIM(f.ReferenceTo) = '[]')`

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

	type fieldRecord struct {
		ID            string
		ObjectAPIName string
		APIName       string
		Type          string
	}

	var fieldsToUpdate []fieldRecord
	for rows.Next() {
		var rec fieldRecord
		if err := rows.Scan(&rec.ID, &rec.ObjectAPIName, &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 from its authoritative bootstrap-owned target.
	updateQuery := `
		UPDATE _System_Field
		SET ReferenceTo = ?
		WHERE Id = ?
	`

	updatedCount := 0
	skippedCount := 0
	var rowErrors []error
	for _, rec := range fieldsToUpdate {
		referenceTo, inScope := repairTargets.ReferenceTo(rec.ObjectAPIName, rec.APIName)
		if !inScope {
			skippedCount++
			continue
		}
		if len(referenceTo) == 0 {
			err := fmt.Errorf("field %s.%s (%s): bootstrap-owned lookup has no reference_to target", rec.ObjectAPIName, rec.APIName, rec.ID)
			log.Printf("   ❌ %v", err)
			rowErrors = append(rowErrors, err)
		} else {
			// Serialize as JSON array
			refJSON, err := json.Marshal(referenceTo)
			if err != nil {
				err = fmt.Errorf("field %s.%s (%s): serialize reference_to: %w", rec.ObjectAPIName, rec.APIName, rec.ID, err)
				log.Printf("   ❌ %v", err)
				rowErrors = append(rowErrors, err)
				continue
			}

			_, err = db.DB().ExecContext(context.Background(), updateQuery, string(refJSON), rec.ID)
			if err != nil {
				err = fmt.Errorf("field %s.%s (%s): update reference_to: %w", rec.ObjectAPIName, rec.APIName, rec.ID, err)
				log.Printf("   ❌ %v", err)
				rowErrors = append(rowErrors, err)
				continue
			}

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

	log.Printf("   ✅ Backfilled reference_to for %d fields", updatedCount)
	if skippedCount > 0 {
		log.Printf("   ⏭  Skipped %d lookup field(s) outside bootstrap ownership", skippedCount)
	}
	if len(rowErrors) > 0 {
		return fmt.Errorf("backfill lookup references failed for %d field(s): %w", len(rowErrors), errors.Join(rowErrors...))
	}
	return nil
}