// Package specdrift provides a generic field-spec drift verifier that
// compares an embedded spec JSON against live _System_Field rows and
// reports Type / Required / Default / Options disagreements.
//
// The package is tenant-agnostic: it operates on whatever spec bytes the
// caller hands in. Callers under controlplane/** invoke it for the
// standard spec; callers under controlplane/tenants/** invoke it for
// their own tenant spec. Platform code references no tenant identifier
// here — that contract is what lets the tenant drift check live inside
// the tenant quarantine instead of the platform binary.
package specdrift

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

	"github.com/nexuscrm/backend/internal/controlplane/packspec"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// fieldSpec mirrors the subset of the packspec field schema relevant to
// drift detection. Default is json.RawMessage — not string — so a spec
// entry with a bare JSON number / boolean (e.g. `"default": 0`) does not
// hard-fail at Unmarshal. packspec.CoerceSpecDefault normalizes the raw
// form to the string stored in _System_Field.DefaultValue before
// comparison.
type fieldSpec struct {
	APIName  string                 `json:"apiName"`
	Type     string                 `json:"type"`
	Required bool                   `json:"required,omitempty"`
	Default  json.RawMessage        `json:"default,omitempty"`
	Options  models.PicklistOptions `json:"options,omitempty"`
}

type objectSpec struct {
	APIName string      `json:"apiName"`
	Fields  []fieldSpec `json:"fields"`
}

type liveField struct {
	typ          string
	required     bool
	defaultValue *string
	options      *string
	isCustomized bool
}

// Verify walks specJSON and logs one warning (or fatal) per field whose
// live _System_Field row disagrees with the spec on Type, Required,
// DefaultValue, or (for picklists) the Options value set.
//
// skipFields is a set of "lower(object).lower(field)" keys the caller
// wants excluded from the comparison — typically fields known to be
// owned by a later-applied spec (e.g. standard verify skips
// tenant-owned fields).
//
// strictMissing controls whether "spec declares this field but no live
// row exists" is fatal. Set true when the caller's apply pass is
// expected to have created every spec row (tenant pack verifying
// itself). Set false when missing rows are a legitimate
// "apply-did-not-run" state (platform verifying its own spec on a fresh
// install; admin soft-deletes).
//
// Contract: `cmd/setup apply` heals non-customized rows (IsCustomized=0
// OR NULL) from spec via the IsCustomized-guarded upsert in
// BatchSaveFieldMetadata; customized rows (IsCustomized=1) are
// preserved. This verifier surfaces customized-row drift so operators
// can decide whether to update the spec, coerce the row, or accept the
// divergence. The drift signal is meaningful precisely because apply is
// non-destructive there — silent overwrites would strip the
// operator-visible disagreement.
func Verify(rawDB *sql.DB, specJSON []byte, specName string, skipFields map[string]bool, strictMissing bool) error {
	return VerifyForTableTypes(
		rawDB,
		specJSON,
		specName,
		skipFields,
		strictMissing,
		constants.TableTypeStandardObject,
	)
}

// VerifyForTableTypes is the same drift verifier scoped to explicit
// _System_Object.TableType values. Platform callers use Verify's
// standard_object default; tenant packs pass custom_object as well when
// they own tenant-created objects under the quarantine package.
func VerifyForTableTypes(rawDB *sql.DB, specJSON []byte, specName string, skipFields map[string]bool, strictMissing bool, tableTypes ...constants.TableType) error {
	var specs []objectSpec
	if err := json.Unmarshal(specJSON, &specs); err != nil {
		return fmt.Errorf("parse %s: %w", specName, err)
	}

	tableTypePredicate, tableTypeArgs := tableTypeWhereClause(tableTypes)

	// Scope to the caller-owned table types. Without this filter a custom
	// object whose ApiName collides with a standard name (case-insensitive)
	// could shadow the real row in the live map and fabricate/mask drift.
	query := fmt.Sprintf(
		"SELECT o.`%s`, f.`%s`, f.`%s`, f.`%s`, f.`%s`, f.`%s`, COALESCE(f.`%s`, 0) "+
			"FROM `%s` f JOIN `%s` o ON f.`%s` = o.`%s` "+
			"WHERE f.`%s` = 0 AND o.`%s` = 0 AND %s",
		constants.FieldAPIName, constants.FieldAPIName,
		constants.ColType, constants.ColRequired,
		"DefaultValue", "Options", constants.FieldSysObject_IsCustomized,
		constants.TableField, constants.TableObject,
		constants.FieldObjectID, constants.ColID,
		constants.ColIsDeleted, constants.ColIsDeleted,
		tableTypePredicate,
	)

	rows, err := rawDB.Query(query, tableTypeArgs...)
	if err != nil {
		return fmt.Errorf("query _System_Field for drift: %w", err)
	}
	defer rows.Close()

	live := make(map[string]liveField)
	for rows.Next() {
		var (
			objAPI, fieldAPI, typ string
			required              bool
			isCustomized          bool
			defaultValue, options sql.NullString
		)
		if err := rows.Scan(&objAPI, &fieldAPI, &typ, &required, &defaultValue, &options, &isCustomized); err != nil {
			return fmt.Errorf("scan drift row: %w", err)
		}
		key := strings.ToLower(objAPI) + "." + strings.ToLower(fieldAPI)
		lr := liveField{typ: typ, required: required, isCustomized: isCustomized}
		if defaultValue.Valid {
			v := defaultValue.String
			lr.defaultValue = &v
		}
		if options.Valid {
			v := options.String
			lr.options = &v
		}
		live[key] = lr
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate drift rows: %w", err)
	}

	warnings := 0
	fatals := 0
	missing := 0
	for _, spec := range specs {
		for _, field := range spec.Fields {
			key := strings.ToLower(spec.APIName) + "." + strings.ToLower(field.APIName)
			if skipFields[key] {
				continue
			}
			lr, ok := live[key]
			if !ok {
				missing++
				log.Printf("   missing: %s.%s (no live _System_Field row)", spec.APIName, field.APIName)
				continue
			}
			diffs := compareFieldAgainstSpec(field, lr)
			if len(diffs) == 0 {
				continue
			}
			isFatal := !lr.isCustomized && isStructuralDrift(field, lr)
			if isFatal {
				fatals += len(diffs)
				log.Printf("❌ [FATAL] spec drift: %s.%s", spec.APIName, field.APIName)
			} else {
				warnings += len(diffs)
				log.Printf("⚠️  spec drift: %s.%s", spec.APIName, field.APIName)
			}
			for _, d := range diffs {
				log.Printf("      %s", d)
			}
		}
	}
	if missing > 0 {
		if strictMissing {
			fatals += missing
			log.Printf("❌ [FATAL] %s: %d spec-declared field(s) have no live row — pack apply likely failed or was skipped", specName, missing)
		} else {
			log.Printf("ℹ️  %s: %d spec-declared field(s) have no live row — pack apply creates them; soft-deleted rows require manual reconciliation", specName, missing)
		}
	}
	if fatals == 0 && warnings == 0 {
		log.Printf("✅ %s matches live _System_Field (no drift)", specName)
	} else {
		log.Printf("⚠️  %s: %d fatal + %d warning drift(s) detected", specName, fatals, warnings)
	}
	if fatals > 0 {
		return fmt.Errorf("%s: %d structural drift(s) (Type or Options mismatch) — pack apply likely failed or was skipped", specName, fatals)
	}
	return nil
}

func tableTypeWhereClause(tableTypes []constants.TableType) (string, []interface{}) {
	if len(tableTypes) == 0 {
		tableTypes = []constants.TableType{constants.TableTypeStandardObject}
	}
	args := make([]interface{}, 0, len(tableTypes))
	for _, tableType := range tableTypes {
		args = append(args, string(tableType))
	}
	if len(tableTypes) == 1 {
		return fmt.Sprintf("o.`%s` = ?", constants.FieldSysObject_TableType), args
	}
	placeholders := make([]string, len(tableTypes))
	for i := range placeholders {
		placeholders[i] = "?"
	}
	return fmt.Sprintf("o.`%s` IN (%s)", constants.FieldSysObject_TableType, strings.Join(placeholders, ",")), args
}

// isStructuralDrift returns true if the drift involves Type or Options
// values — these indicate a pack apply failure on platform-owned rows,
// not a legitimate admin choice. Callers must gate customized rows
// before escalating.
func isStructuralDrift(spec fieldSpec, live liveField) bool {
	if !strings.EqualFold(spec.Type, live.typ) {
		return true
	}
	if strings.EqualFold(spec.Type, string(constants.FieldTypePicklist)) && len(spec.Options) > 0 {
		if live.options == nil || *live.options == "" || *live.options == "[]" || *live.options == "null" {
			return true
		}
		if !picklistOptionsMatch(spec.Options, *live.options) {
			return true
		}
	}
	return false
}

// picklistOptionsMatch compares the full user-visible PicklistOption
// structure — Value, Label, and Tags — between spec and live.
// Comparing only Value was the legacy shape; adding Tags in the
// previous pass closed the tenant-overlay classification gap; adding
// Label here closes the remaining asymmetry. Label flows into
// describe responses (sobject_field_helpers.go emits opt.Label) and
// renders in the frontend, so a live divergence is user-observable
// and must be treated as drift, not silent tolerance. Apply writes
// every field on fSpec.Options unchanged — Verify must compare every
// field Apply writes.
func picklistOptionsMatch(specOptions models.PicklistOptions, liveJSON string) bool {
	var liveOptions models.PicklistOptions
	if err := json.Unmarshal([]byte(liveJSON), &liveOptions); err != nil {
		return false
	}
	if len(specOptions) != len(liveOptions) {
		return false
	}
	specByValue := indexPicklistByValue(specOptions)
	liveByValue := indexPicklistByValue(liveOptions)
	for value, specOpt := range specByValue {
		liveOpt, ok := liveByValue[value]
		if !ok {
			return false
		}
		if specOpt.Label != liveOpt.Label {
			return false
		}
		if !stringSliceSetEqual(specOpt.Tags, liveOpt.Tags) {
			return false
		}
	}
	return true
}

func indexPicklistByValue(opts models.PicklistOptions) map[string]models.PicklistOption {
	byValue := make(map[string]models.PicklistOption, len(opts))
	for _, o := range opts {
		byValue[o.Value] = o
	}
	return byValue
}

// stringSliceSetEqual compares two tag slices as unordered sets.
// nil and empty slice are treated as equivalent (the "no tags" state).
func stringSliceSetEqual(a, b []string) bool {
	if len(a) != len(b) {
		return false
	}
	if len(a) == 0 {
		return true
	}
	aCopy := append([]string(nil), a...)
	bCopy := append([]string(nil), b...)
	sort.Strings(aCopy)
	sort.Strings(bCopy)
	for i := range aCopy {
		if aCopy[i] != bCopy[i] {
			return false
		}
	}
	return true
}

func compareFieldAgainstSpec(spec fieldSpec, live liveField) []string {
	var diffs []string

	if !strings.EqualFold(spec.Type, live.typ) {
		diffs = append(diffs, fmt.Sprintf("Type: spec=%q live=%q", spec.Type, live.typ))
	}
	if spec.Required != live.required {
		diffs = append(diffs, fmt.Sprintf("Required: spec=%t live=%t", spec.Required, live.required))
	}
	specDefault, specHasDefault := packspec.CoerceSpecDefault(spec.Default)
	if !defaultValuesMatch(specDefault, specHasDefault, live.defaultValue) {
		diffs = append(diffs, fmt.Sprintf("DefaultValue: spec=%s live=%s",
			specDefaultDisplay(specDefault, specHasDefault),
			nullStringDisplay(live.defaultValue)))
	}
	if strings.EqualFold(spec.Type, string(constants.FieldTypePicklist)) {
		if diff := comparePicklistOptions(spec.Options, live.options); diff != "" {
			diffs = append(diffs, diff)
		}
	}
	return diffs
}

func defaultValuesMatch(specDefault string, specHasDefault bool, liveDefault *string) bool {
	if !specHasDefault {
		return liveDefault == nil || *liveDefault == ""
	}
	if liveDefault == nil {
		return false
	}
	return specDefault == *liveDefault
}

func specDefaultDisplay(value string, hasDefault bool) string {
	if !hasDefault {
		return "<unset>"
	}
	return fmt.Sprintf("%q", value)
}

func nullStringDisplay(s *string) string {
	if s == nil {
		return "NULL"
	}
	return fmt.Sprintf("%q", *s)
}

func comparePicklistOptions(specOpts models.PicklistOptions, liveJSON *string) string {
	var liveOpts models.PicklistOptions
	if liveJSON != nil && *liveJSON != "" {
		_ = json.Unmarshal([]byte(*liveJSON), &liveOpts)
	}
	specValues := sortedValues(specOpts)
	liveValues := sortedValues(liveOpts)
	if strings.Join(specValues, ",") != strings.Join(liveValues, ",") {
		return fmt.Sprintf("Options: spec=[%s] live=[%s]",
			strings.Join(specValues, ","), strings.Join(liveValues, ","))
	}
	// Values match; now check per-value Label and Tags so every
	// user-visible field on PicklistOption is symmetric between
	// Apply (writes) and Verify (reads).
	if diff := comparePicklistPerValueFields(specOpts, liveOpts); diff != "" {
		return diff
	}
	return ""
}

// comparePicklistPerValueFields returns the first per-value field
// mismatch (Label or Tags) it finds, or "" if every value's
// user-visible fields match. Called only after value-set equality
// is established, so map lookups into liveByValue are safe.
func comparePicklistPerValueFields(specOpts, liveOpts models.PicklistOptions) string {
	liveByValue := indexPicklistByValue(liveOpts)
	for _, specOpt := range specOpts {
		liveOpt := liveByValue[specOpt.Value]
		if specOpt.Label != liveOpt.Label {
			return fmt.Sprintf("Label[%s]: spec=%q live=%q",
				specOpt.Value, specOpt.Label, liveOpt.Label)
		}
		if !stringSliceSetEqual(specOpt.Tags, liveOpt.Tags) {
			return fmt.Sprintf("Tags[%s]: spec=%s live=%s",
				specOpt.Value, formatTagSet(specOpt.Tags), formatTagSet(liveOpt.Tags))
		}
	}
	return ""
}

func formatTagSet(tags []string) string {
	if len(tags) == 0 {
		return "[]"
	}
	sorted := append([]string(nil), tags...)
	sort.Strings(sorted)
	return "[" + strings.Join(sorted, ",") + "]"
}

func sortedValues(opts models.PicklistOptions) []string {
	vals := make([]string, 0, len(opts))
	for _, o := range opts {
		vals = append(vals, o.Value)
	}
	sort.Strings(vals)
	return vals
}