package packs

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"strings"

	"github.com/nexuscrm/metadata"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/pingcap-inc/nexuscrm-controlplane/layoutownership"
)

// relatedListCandidate is a lightweight struct for backfill candidate generation.
type relatedListCandidate struct {
	LookupFieldAPI    string
	LookupFieldLabel  string
	ChildObjectAPI    string
	ChildPluralLabel  string
	RelatedListLabel  sql.NullString
	RelatedListFields sql.NullString
}

// BackfillLayoutRelatedLists ensures persisted layouts have:
// 1. Seeded relatedLists when the stored list is empty
// 2. SecondaryLabel on every related list entry
// 3. Canonical IDs (objectApiName-lookupField) instead of random UUIDs
//
// Idempotent — safe to run multiple times.
func BackfillLayoutRelatedLists(db *sql.DB) error {
	log.Println("🔗 Backfilling layout related lists (secondaryLabel, canonical IDs, empty fills)...")

	// Pre-load name field mapping for all objects (one query)
	nameFieldMap, err := loadNameFieldMap(db)
	if err != nil {
		log.Printf("   ⚠️  Error loading name field map: %v", err)
		// Fall back to "Name" if map unavailable
		nameFieldMap = make(map[string]string)
	}

	// Query all non-deleted layouts
	rows, err := db.QueryContext(context.Background(),
		`SELECT Id, ObjectApiName, Config, COALESCE(Source,''), COALESCE(IsCustomized,0) FROM _System_PageLayout WHERE IsDeleted = 0 OR IsDeleted IS NULL`)
	if err != nil {
		log.Printf("   ⚠️  Error querying layouts: %v", err)
		return err
	}
	defer rows.Close()

	type layoutRow struct {
		ID            string
		ObjectAPIName string
		ConfigJSON    string
		Source        string
		IsCustomized  bool
	}

	var layouts []layoutRow
	for rows.Next() {
		var r layoutRow
		if err := rows.Scan(&r.ID, &r.ObjectAPIName, &r.ConfigJSON, &r.Source, &r.IsCustomized); err != nil {
			return err
		}
		layouts = append(layouts, r)
	}
	if err := rows.Err(); err != nil {
		return err
	}

	var filled, enriched, skipped int

	for _, lr := range layouts {
		layout, err := layoutownership.DecodePageLayoutConfig([]byte(lr.ConfigJSON))
		if err != nil {
			log.Printf("   ⚠️  Failed to parse layout %s config: %v", lr.ID, err)
			skipped++
			continue
		}

		changed := false

		if len(layout.RelatedLists) == 0 && !lr.IsCustomized && lr.Source != "cloned" && lr.Source != "admin_created" && lr.Source != "curated" {
			// Only seed layouts that were never intentionally configured.
			// Admin-saved (IsCustomized), cloned, admin-created, or curated
			// layouts with empty relatedLists are intentionally empty — do
			// not overwrite. (Curated layouts are owned by control-plane
			// packs that re-upsert Config every restart; backfilling here
			// would be thrash-written and create a fragile ordering
			// dependency on Apply step order.)
			candidates, err := getRelatedListCandidates(db, lr.ObjectAPIName)
			if err != nil {
				log.Printf("   ⚠️  Failed to get candidates for %s: %v", lr.ObjectAPIName, err)
				skipped++
				continue
			}
			resolvedCandidates := resolveMetadataRelatedListCandidates(lr.ObjectAPIName, nameFieldMap, candidates)
			for _, c := range resolvedCandidates {
				if !c.IsRecommended {
					continue
				}
				layout.RelatedLists = append(layout.RelatedLists, models.RelatedListConfig{
					ID:             c.ID,
					Label:          c.Label,
					SecondaryLabel: c.SecondaryLabel,
					ObjectAPIName:  c.ObjectAPIName,
					LookupField:    c.LookupField,
					Fields:         append([]string(nil), c.Fields...),
				})
			}
			// Reorder to match curated rule order
			metadata.SortByCuratedOrder(lr.ObjectAPIName, layout.RelatedLists)
			if len(layout.RelatedLists) > 0 {
				changed = true
				filled++
			}
		} else {
			if lr.Source == "curated" {
				// Pack-owned curated layouts are authoritative. Backfill must not
				// rewrite non-empty curated related-list metadata after apply,
				// or the subsequent pack verify pass will observe self-induced
				// config drift.
				continue
			}
			// For non-empty layouts, only normalize metadata. Never rewrite
			// the configured related-list membership.
			candidateMap, err := getRelatedListCandidateMap(db, lr.ObjectAPIName)
			if err != nil {
				log.Printf("   ⚠️  Failed to get candidate map for %s: %v", lr.ObjectAPIName, err)
				skipped++
				continue
			}

			// Remove system/internal objects and deduplicate by canonical ID
			cleanedLists := make([]models.RelatedListConfig, 0, len(layout.RelatedLists))
			seenIDs := make(map[string]bool)
			for _, rl := range layout.RelatedLists {
				if metadata.IsSystemRelatedObject(rl.ObjectAPIName) {
					changed = true
					continue
				}
				canonicalKey := strings.ToLower(fmt.Sprintf("%s-%s", rl.ObjectAPIName, rl.LookupField))
				if seenIDs[canonicalKey] {
					changed = true
					continue
				}
				seenIDs[canonicalKey] = true
				cleanedLists = append(cleanedLists, rl)
			}
			layout.RelatedLists = cleanedLists

			for i := range layout.RelatedLists {
				rl := &layout.RelatedLists[i]
				canonicalID := fmt.Sprintf("%s-%s", rl.ObjectAPIName, rl.LookupField)

				// Normalize UUID → canonical ID
				if rl.ID != canonicalID {
					rl.ID = canonicalID
					changed = true
				}

				// Refresh label from current PluralLabel (fixes stale relationship names)
				key := strings.ToLower(canonicalID)
				if c, ok := candidateMap[key]; ok {
					if c.ChildPluralLabel != "" && rl.Label != c.ChildPluralLabel {
						rl.Label = c.ChildPluralLabel
						changed = true
					}
				}

				// Refresh secondaryLabel from current field metadata (fixes stale values on rename)
				if c, ok := candidateMap[key]; ok {
					fieldLabel := c.LookupFieldLabel
					if fieldLabel == "" {
						fieldLabel = c.LookupFieldAPI
					}
					expected := "via " + fieldLabel
					if rl.SecondaryLabel != expected {
						rl.SecondaryLabel = expected
						changed = true
					}
				} else if rl.SecondaryLabel == "" {
					rl.SecondaryLabel = "via " + rl.LookupField
					changed = true
				}
			}
			if changed {
				enriched++
			}
		}

		if !changed {
			continue
		}

		// Serialize and update
		configBytes, err := json.Marshal(&layout)
		if err != nil {
			log.Printf("   ⚠️  Failed to marshal layout %s: %v", lr.ID, err)
			skipped++
			continue
		}

		_, err = db.ExecContext(context.Background(),
			`UPDATE _System_PageLayout SET Config = ?, LastModifiedDate = CURRENT_TIMESTAMP WHERE Id = ?`,
			string(configBytes), lr.ID)
		if err != nil {
			log.Printf("   ⚠️  Failed to update layout %s: %v", lr.ID, err)
			skipped++
			continue
		}
	}

	log.Printf("✅ Related list backfill complete: %d filled, %d enriched, %d skipped", filled, enriched, skipped)
	if skipped > 0 {
		return fmt.Errorf("related list backfill: %d layouts skipped due to errors (will retry next restart)", skipped)
	}
	return nil
}

// loadNameFieldMap builds a map of objectAPIName → nameField for all objects.
// Uses the IsNameField flag from _System_Field, falling back to "Name".
func loadNameFieldMap(db *sql.DB) (map[string]string, error) {
	rows, err := db.QueryContext(context.Background(), `
		SELECT o.ApiName, f.ApiName
		FROM _System_Field f
		INNER JOIN _System_Object o ON f.ObjectId = o.Id
		WHERE f.IsNameField = 1`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	m := make(map[string]string)
	for rows.Next() {
		var objAPI, fieldAPI string
		if err := rows.Scan(&objAPI, &fieldAPI); err != nil {
			return nil, err
		}
		m[strings.ToLower(objAPI)] = fieldAPI
	}
	return m, rows.Err()
}

// defaultColumnsForObject returns [nameField, "CreatedDate"] for a child object,
// using the actual name field from the map (or "Name" as fallback).
func defaultColumnsForObject(nameFieldMap map[string]string, objectAPIName string) []string {
	nameField := "Name"
	if nf, ok := nameFieldMap[strings.ToLower(objectAPIName)]; ok {
		nameField = nf
	}
	return []string{nameField, "CreatedDate"}
}

func parseConfiguredRelatedListFields(raw sql.NullString) []string {
	if !raw.Valid {
		return nil
	}

	trimmed := strings.TrimSpace(raw.String)
	if trimmed == "" {
		return nil
	}

	if strings.HasPrefix(trimmed, "[") {
		var parsed []string
		if err := json.Unmarshal([]byte(trimmed), &parsed); err == nil {
			return normalizeRelatedListFields(parsed)
		}
	}

	parts := strings.FieldsFunc(trimmed, func(r rune) bool {
		return r == ',' || r == '\n' || r == '\r' || r == '\t'
	})
	return normalizeRelatedListFields(parts)
}

func normalizeRelatedListFields(fields []string) []string {
	if len(fields) == 0 {
		return nil
	}

	normalized := make([]string, 0, len(fields))
	seen := make(map[string]bool, len(fields))
	for _, field := range fields {
		trimmed := strings.TrimSpace(strings.Trim(field, `"'`))
		if trimmed == "" || seen[trimmed] {
			continue
		}
		seen[trimmed] = true
		normalized = append(normalized, trimmed)
	}
	if len(normalized) == 0 {
		return nil
	}
	return normalized
}

func relatedListColumns(nameFieldMap map[string]string, candidate relatedListCandidate) []string {
	if configured := parseConfiguredRelatedListFields(candidate.RelatedListFields); len(configured) > 0 {
		return configured
	}
	return defaultColumnsForObject(nameFieldMap, candidate.ChildObjectAPI)
}

func preferredRelatedListLabel(candidate relatedListCandidate) string {
	// Always use the child object's PluralLabel (e.g., "Contacts", "Opportunities").
	// RelatedListLabel from _System_Relationship contains relationship names
	// (e.g., "Account__r", "Partner_Name__r") which are not user-friendly.
	return candidate.ChildPluralLabel
}

func resolveMetadataRelatedListCandidates(parentObjectAPI string, nameFieldMap map[string]string, candidates []relatedListCandidate) []metadata.RelatedListCandidate {
	return resolveMetadataRelatedListCandidatesWithRules(parentObjectAPI, nameFieldMap, candidates, nil)
}

func resolveMetadataRelatedListCandidatesWithRules(parentObjectAPI string, nameFieldMap map[string]string, candidates []relatedListCandidate, rules []metadata.RelatedListRule) []metadata.RelatedListCandidate {
	resolved := make([]metadata.RelatedListCandidate, 0, len(candidates))
	for _, c := range candidates {
		fieldLabel := c.LookupFieldLabel
		if fieldLabel == "" {
			fieldLabel = c.LookupFieldAPI
		}
		resolved = append(resolved, metadata.RelatedListCandidate{
			ID:             fmt.Sprintf("%s-%s", c.ChildObjectAPI, c.LookupFieldAPI),
			ObjectAPIName:  c.ChildObjectAPI,
			LookupField:    c.LookupFieldAPI,
			Label:          preferredRelatedListLabel(c),
			SecondaryLabel: "via " + fieldLabel,
			Fields:         relatedListColumns(nameFieldMap, c),
			FieldLabel:     fieldLabel,
		})
	}
	if rules != nil {
		return metadata.ResolveRelatedListCandidatesWithRules(parentObjectAPI, resolved, rules)
	}
	return metadata.ResolveRelatedListCandidates(parentObjectAPI, resolved)
}

// getRelatedListCandidates queries child relationships for a parent object.
func getRelatedListCandidates(db *sql.DB, parentObjectAPI string) ([]relatedListCandidate, error) {
	query := fmt.Sprintf(`
		SELECT
			f.ApiName,
			f.Label,
			o.ApiName,
			o.PluralLabel,
			rel.RelatedListLabel,
			rel.RelatedListFields
		FROM _System_Field f
		INNER JOIN _System_Object o ON f.ObjectId = o.Id
		LEFT JOIN _System_Relationship rel
		  ON rel.ChildObjectAPIName = o.ApiName
		 AND LOWER(rel.ParentObjectAPIName) = LOWER('%s')
		 AND rel.FieldApiName = f.ApiName
		WHERE JSON_CONTAINS(LOWER(f.ReferenceTo), LOWER('"%s"'))
		  AND f.Type IN ('reference', 'masterdetail')
		  AND o.PluralLabel != ''
		ORDER BY o.PluralLabel ASC, f.Label ASC, f.ApiName ASC`, parentObjectAPI, parentObjectAPI)

	rows, err := db.QueryContext(context.Background(), query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var candidates []relatedListCandidate
	for rows.Next() {
		var c relatedListCandidate
		if err := rows.Scan(&c.LookupFieldAPI, &c.LookupFieldLabel, &c.ChildObjectAPI, &c.ChildPluralLabel, &c.RelatedListLabel, &c.RelatedListFields); err != nil {
			return nil, err
		}
		candidates = append(candidates, c)
	}
	return candidates, rows.Err()
}

// getRelatedListCandidateMap returns candidates keyed by lowercase "childObject-lookupField".
func getRelatedListCandidateMap(db *sql.DB, parentObjectAPI string) (map[string]relatedListCandidate, error) {
	candidates, err := getRelatedListCandidates(db, parentObjectAPI)
	if err != nil {
		return nil, err
	}
	m := make(map[string]relatedListCandidate, len(candidates))
	for _, c := range candidates {
		key := strings.ToLower(fmt.Sprintf("%s-%s", c.ChildObjectAPI, c.LookupFieldAPI))
		m[key] = c
	}
	return m, nil
}