// 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
}

type ruleQueryer interface {
	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
}

const metadataRulesQuery = "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)"

// ReadRules reads the authoritative autonumber metadata from the caller's
// executor. Catalog uses this with a transaction-bound executor so autonumber
// facts cannot escape the revision snapshot. Unlike the legacy cache refresh,
// malformed token references fail closed instead of being silently discarded.
func ReadRules(ctx context.Context, exec ruleQueryer) ([]Rule, error) {
	if exec == nil {
		return nil, fmt.Errorf("autonumber metadata source: executor is required")
	}
	rows, err := exec.QueryContext(ctx, metadataRulesQuery)
	if err != nil {
		return nil, fmt.Errorf("autonumber metadata source: query: %w", err)
	}
	defer rows.Close()
	rules := make([]Rule, 0)
	for rows.Next() {
		rule, _, err := scanMetadataRule(rows)
		if err != nil {
			return nil, err
		}
		rules = append(rules, rule)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("autonumber metadata source: iterate: %w", err)
	}
	return rules, nil
}

func scanMetadataRule(rows *sql.Rows) (Rule, string, error) {
	var (
		rule          Rule
		tokenRefsJSON string
	)
	if err := rows.Scan(&rule.FieldID, &rule.ObjectAPIName, &rule.FieldAPIName, &rule.Format,
		&rule.Scope, &rule.Start, &tokenRefsJSON); err != nil {
		return Rule{}, "", fmt.Errorf("autonumber metadata source: scan: %w", err)
	}
	rule.DateSrc = "assigned_at"
	if strings.TrimSpace(tokenRefsJSON) != "" {
		if err := json.Unmarshal([]byte(tokenRefsJSON), &rule.TokenRefs); err != nil {
			return rule, tokenRefsJSON, fmt.Errorf("autonumber metadata source: decode token refs for %q: %w", rule.FieldID, err)
		}
	}
	return rule, tokenRefsJSON, nil
}

// 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()

	rows, err := m.db.QueryContext(ctx, metadataRulesQuery)
	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() {
		rule, tokenRefsJSON, scanErr := scanMetadataRule(rows)
		if scanErr != nil {
			if strings.TrimSpace(tokenRefsJSON) != "" {
				m.logger.Warn("autonumber metadata source: malformed AutoNumberTokenRefs; skipping ref decode",
					"fieldID", rule.FieldID, "err", scanErr)
				rule.TokenRefs = nil
			} else {
				return scanErr
			}
		}

		byFieldID[rule.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 the snapshot stores canonical API casing. Previously this was a silent miss
		// and the INSERT would skip autonumber assignment entirely.
		objectKey := strings.ToLower(rule.ObjectAPIName)
		byObject[objectKey] = append(byObject[objectKey], FieldMeta{
			FieldID:      rule.FieldID,
			FieldAPIName: rule.FieldAPIName,
		})
	}
	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 the published cache-backed MetadataProvider contract.
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
}

// GetRuleContext is the context-capable form used by the independent Runtime
// abstraction. The process-local cache cannot fail, so it delegates to the
// compatibility method without inventing an error path.
func (m *MetadataSource) GetRuleContext(_ context.Context, fieldID string) (*Rule, error) {
	return m.GetRule(fieldID), nil
}

// ListRules implements the published cache-backed MetadataProvider contract.
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
}

// ListRulesContext is the context-capable initialization and maintenance form.
func (m *MetadataSource) ListRulesContext(_ context.Context) ([]Rule, error) {
	return m.ListRules(), nil
}

// 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
}

// LookupByObjectContext is the context-capable Runtime form. The cache-backed
// source delegates to its established lookup; a request-bound provider may
// instead fail closed when no Catalog view is bound.
func (m *MetadataSource) LookupByObjectContext(_ context.Context, objectAPIName string) ([]FieldMeta, error) {
	return m.LookupByObject(objectAPIName), nil
}