// Package autonumber implements metadata-driven identifier assignment for NexusCRM.
package autonumber

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"log/slog"
	"strings"
	"sync"
	"time"
)

// MetadataSource is the production MetadataProvider backing the engine at
// runtime. It materializes Rule rows from `_System_Field` WHERE
// `AutoNumberFormat IS NOT NULL AND AutoNumberFormat != ”` and exposes
// two views of the cache:
//
//  1. Rule-lookup by fieldID (implements MetadataProvider for the engine).
//  2. Per-object fieldID listing for persistence call-sites (LookupByObject).
//
// The cache is refreshed on-demand via Refresh(). Concurrent reads are
// lock-free (atomic load of the immutable snapshot); writers (Refresh)
// serialize via an internal mutex.
type MetadataSource struct {
	db     *sql.DB
	logger *slog.Logger

	mu   sync.Mutex
	snap *metadataSnapshot
}

// metadataSnapshot is the immutable value held by the atomic pointer.
type metadataSnapshot struct {
	// byFieldID — keyed on _System_Field.Id.
	byFieldID map[string]Rule
	// byObject — keyed on strings.ToLower(_System_Object.ApiName) so the
	// LookupByObject call-site can be case-insensitive (Round-6 V1-Codex-P1).
	// Values are FieldMeta entries used by persistence callers that need
	// the (fieldID, fieldAPIName) tuple without knowing which fields an
	// object has autonumber on.
	byObject map[string][]FieldMeta
}

// FieldMeta is the minimal (fieldID, fieldAPIName) pair persistence callers
// need to iterate over autonumber-configured fields for an object.
type FieldMeta struct {
	FieldID      string
	FieldAPIName string
}

// NewMetadataSource constructs a MetadataSource backed by the given *sql.DB.
// The caller MUST invoke Refresh() (or RefreshWithBackoff) before passing
// this into the engine — an empty snapshot is fail-closed (LookupByObject
// returns nil) so persistence callers will silently skip autonumber
// assignment. Round-5 Codex V2-C2: there is no "lazy refresh on miss";
// the caller owns init-time success.
func NewMetadataSource(db *sql.DB, logger *slog.Logger) *MetadataSource {
	if logger == nil {
		logger = slog.Default()
	}
	return &MetadataSource{
		db:     db,
		logger: logger,
		snap: &metadataSnapshot{
			byFieldID: map[string]Rule{},
			byObject:  map[string][]FieldMeta{},
		},
	}
}

// Refresh reloads the cache from `_System_Field`. Idempotent; safe to call
// concurrently — only one Refresh body runs at a time. Returns an error
// only if the SQL query fails; unparseable rows are NOT dropped here (the
// engine's NewEngine quarantines them at init).
func (m *MetadataSource) Refresh(ctx context.Context) error {
	m.mu.Lock()
	defer m.mu.Unlock()

	const q = "SELECT f.Id, o.ApiName, f.ApiName, " +
		"f.auto_number_format, " +
		"COALESCE(f.auto_number_scope, 'global'), " +
		"COALESCE(f.auto_number_start, 1), " +
		"COALESCE(f.auto_number_token_refs, '') " +
		"FROM `_System_Field` f " +
		"JOIN `_System_Object` o ON o.Id = f.ObjectId " +
		"WHERE f.auto_number_format IS NOT NULL AND f.auto_number_format != '' " +
		"AND (f.IsDeleted = 0 OR f.IsDeleted IS NULL)"

	rows, err := m.db.QueryContext(ctx, q)
	if err != nil {
		return fmt.Errorf("autonumber metadata source: query: %w", err)
	}
	defer rows.Close()

	byFieldID := map[string]Rule{}
	byObject := map[string][]FieldMeta{}

	for rows.Next() {
		var (
			fieldID, objectAPI, fieldAPI, format, scope string
			start                                       int64
			tokenRefsJSON                               string
		)
		if err := rows.Scan(&fieldID, &objectAPI, &fieldAPI, &format, &scope, &start, &tokenRefsJSON); err != nil {
			return fmt.Errorf("autonumber metadata source: scan: %w", err)
		}

		rule := Rule{
			FieldID:       fieldID,
			ObjectAPIName: objectAPI,
			FieldAPIName:  fieldAPI,
			Format:        format,
			Scope:         scope,
			Start:         start,
			DateSrc:       "assigned_at",
		}
		if strings.TrimSpace(tokenRefsJSON) != "" {
			var refs []CrossRecordRef
			if err := json.Unmarshal([]byte(tokenRefsJSON), &refs); err != nil {
				m.logger.Warn("autonumber metadata source: malformed AutoNumberTokenRefs; skipping ref decode",
					"fieldID", fieldID, "err", err)
			} else {
				rule.TokenRefs = refs
			}
		}

		byFieldID[fieldID] = rule
		// Round-6 V1-Codex-P1: index byObject by lower-cased object API name
		// so LookupByObject is robust against callers that pass "account"
		// when snapshot stores "Account". Previously this was a silent miss
		// and the INSERT would skip autonumber assignment entirely.
		objectKey := strings.ToLower(objectAPI)
		byObject[objectKey] = append(byObject[objectKey], FieldMeta{
			FieldID:      fieldID,
			FieldAPIName: fieldAPI,
		})
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("autonumber metadata source: iterate: %w", err)
	}

	m.snap = &metadataSnapshot{
		byFieldID: byFieldID,
		byObject:  byObject,
	}
	return nil
}

// RefreshWithBackoff retries Refresh with exponential backoff until
// success or maxAttempts is exhausted. Returns the last error on
// failure. Each attempt waits `baseDelay * 2^(i-1)` before the i'th
// retry (i=1 for the first retry, not the first attempt).
//
// Round-5 Codex V2-C2: engine init MUST fail loudly when the
// autonumber metadata snapshot cannot be populated. The previous
// non-fatal log-and-continue branch left the engine in a permanently
// empty state, which LookupByObject reports as "no autonumber on this
// object" — so every subsequent INSERT silently skipped autonumber
// assignment. Callers at init time should invoke this and propagate
// the error so the pod fails its readiness probe / init crashes.
func (m *MetadataSource) RefreshWithBackoff(ctx context.Context, maxAttempts int, baseDelay time.Duration) error {
	if maxAttempts < 1 {
		maxAttempts = 1
	}
	if baseDelay <= 0 {
		baseDelay = 500 * time.Millisecond
	}
	var lastErr error
	for attempt := 1; attempt <= maxAttempts; attempt++ {
		err := m.Refresh(ctx)
		if err == nil {
			return nil
		}
		lastErr = err
		if attempt == maxAttempts {
			break
		}
		sleep := baseDelay << (attempt - 1) // base, 2×, 4×, ...
		m.logger.Warn("autonumber metadata refresh failed; retrying",
			"attempt", attempt, "maxAttempts", maxAttempts,
			"sleep", sleep, "err", err)
		select {
		case <-ctx.Done():
			return fmt.Errorf("autonumber metadata refresh cancelled after %d attempt(s): %w",
				attempt, ctx.Err())
		case <-time.After(sleep):
		}
	}
	return fmt.Errorf("autonumber metadata refresh failed after %d attempt(s): %w",
		maxAttempts, lastErr)
}

// GetRule implements MetadataProvider.GetRule.
func (m *MetadataSource) GetRule(fieldID string) *Rule {
	m.mu.Lock()
	snap := m.snap
	m.mu.Unlock()
	if r, ok := snap.byFieldID[fieldID]; ok {
		copy := r
		return &copy
	}
	return nil
}

// ListRules implements MetadataProvider.ListRules.
func (m *MetadataSource) ListRules() []Rule {
	m.mu.Lock()
	snap := m.snap
	m.mu.Unlock()
	out := make([]Rule, 0, len(snap.byFieldID))
	for _, r := range snap.byFieldID {
		out = append(out, r)
	}
	return out
}

// InvalidateObject forces the snapshot to be rebuilt from the DB so
// a subsequent LookupByObject / GetRule sees changes made by
// MetadataService (e.g. a new autonumber field created via
// `field_create.go` or `field_batch.go`).
//
// Round-5 Codex V1-P2 / V2-H3: the admin create-field path writes
// `_System_Field.auto_number_format` via MetadataRepository.
// ConfigureAutoNumberField but never nudged MetadataSource, so the
// engine's snapshot remained stale until the next process restart. In
// the intervening window every INSERT on the object skipped autonumber
// assignment (LookupByObject returned nil).
//
// The current implementation does a full Refresh — simpler and small
// enough at 132 rows today. A surgical per-object update can replace
// this when steady-state row count grows; the method signature is
// stable (objectAPIName hint included so callers don't change).
func (m *MetadataSource) InvalidateObject(ctx context.Context, objectAPIName string) error {
	return m.Refresh(ctx)
}

// LookupByObject returns the (fieldID, fieldAPIName) pairs for every
// autonumber-configured field on the given object. The lookup is
// case-insensitive on object API name: snapshot keys are lower-cased at
// Refresh time and the query is lower-cased here (Round-6 V1-Codex-P1).
// Returns nil for unknown objects.
func (m *MetadataSource) LookupByObject(objectAPIName string) []FieldMeta {
	m.mu.Lock()
	snap := m.snap
	m.mu.Unlock()
	if out, ok := snap.byObject[strings.ToLower(objectAPIName)]; ok {
		return append([]FieldMeta(nil), out...)
	}
	return nil
}