package middleware

import (
	"context"
	"database/sql"

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/prometheus/client_golang/prometheus"
	"github.com/prometheus/client_golang/prometheus/promauto"
)

// Outbox backlog gauges expose the burst-resolution after-save outbox backlog.
//
// These are the class-A stability SLIs for the shared-pool saturation failure
// mode (see issue #3295): when the foreground write path and the async outbox
// worker contend for the single shared pool, the outbox stops draining and the
// oldest pending row ages. The existing ops endpoint only exposes per-status
// counts at 10-minute polling granularity; oldestPendingAge does not exist there
// at all, so a burst is invisible until it has already caused damage.
//
// The gauges are refreshed by the API runtime's bounded hourly observer, so
// unauthenticated Prometheus scrapes stay DB-free.
var (
	// outboxQueueDepth is the number of outbox rows in each Status bucket.
	outboxQueueDepth = promauto.NewGaugeVec(
		prometheus.GaugeOpts{
			Name: "nexuscrm_outbox_queue_depth",
			Help: "Number of after-save outbox rows per Status (pending/processing/processed/failed).",
		},
		[]string{"state"},
	)

	// outboxOldestPendingAgeSeconds is the age in seconds of the oldest
	// still-dispatchable (pending) outbox row. Zero when there are no pending
	// rows. This is the load-bearing burst signal: a rising value means the
	// outbox is not draining.
	outboxOldestPendingAgeSeconds = promauto.NewGauge(
		prometheus.GaugeOpts{
			Name: "nexuscrm_outbox_oldest_pending_age_seconds",
			Help: "Age in seconds of the oldest still-pending outbox row (0 when none pending).",
		},
	)
)

// outboxBacklogStates is the fixed set of Status buckets reported as gauge
// children. Listing them explicitly keeps the gauge cardinality bounded and
// ensures a bucket that drops to zero is published as 0 rather than going stale.
var outboxBacklogStates = []string{
	string(constants.OutboxStatusPending),
	"processing", // transient status; not a typed constant (see outbox_repository.go).
	string(constants.OutboxStatusProcessed),
	string(constants.OutboxStatusFailed),
}

// outboxBacklogStats is the parsed result of one backlog sample.
type outboxBacklogStats struct {
	// depthByState holds the row count per Status bucket. Missing buckets are
	// treated as zero.
	depthByState map[string]int64
	// oldestPendingAgeSeconds is the age of the oldest pending row, or 0 when
	// there are no pending rows.
	oldestPendingAgeSeconds float64
}

// queryOutboxBacklogStats reads depth-by-status and oldest-pending-age from the
// _System_OutboxEvent table with two bounded aggregate statements.
//
// It reuses the same grouped-count shape as the ops sentry-metrics handler
// (SELECT Status, COUNT(*) ... GROUP BY Status) and additionally computes
// MIN(CreatedDate) over the pending rows so the age is derived in the database
// (TIMESTAMPDIFF(SECOND, MIN(CreatedDate), NOW())), avoiding clock skew between
// the app and the DB.
//
// The query is read-only and carries no row writes; callers supply a bounded
// context so it can never hold a connection indefinitely.
func queryOutboxBacklogStats(ctx context.Context, db *sql.DB) (outboxBacklogStats, error) {
	stats := outboxBacklogStats{depthByState: map[string]int64{}}
	if db == nil {
		return stats, sql.ErrConnDone
	}

	// Depth by Status — mirrors the ops handler's grouped count over the same
	// system table.
	depthRows, err := db.QueryContext(ctx,
		"SELECT `Status`, COUNT(*) FROM `"+constants.TableOutboxEvent+"` GROUP BY `Status`")
	if err != nil {
		return stats, err
	}
	var scanErr error
	func() {
		defer depthRows.Close()
		for depthRows.Next() {
			var status string
			var count int64
			if err := depthRows.Scan(&status, &count); err != nil {
				scanErr = err
				return
			}
			stats.depthByState[status] = count
		}
	}()
	if scanErr != nil {
		return stats, scanErr
	}
	if err := depthRows.Err(); err != nil {
		return stats, err
	}

	// Oldest pending age — derived in the DB so it does not depend on app/DB
	// clock agreement. NULL (no pending rows) maps to 0.
	var ageSeconds sql.NullFloat64
	err = db.QueryRowContext(ctx,
		"SELECT TIMESTAMPDIFF(SECOND, MIN(`"+constants.FieldSysOutboxEvent_CreatedDate+"`), NOW()) "+
			"FROM `"+constants.TableOutboxEvent+"` WHERE `"+constants.FieldSysOutboxEvent_Status+"` = ?",
		string(constants.OutboxStatusPending),
	).Scan(&ageSeconds)
	if err != nil {
		return stats, err
	}
	if ageSeconds.Valid && ageSeconds.Float64 > 0 {
		stats.oldestPendingAgeSeconds = ageSeconds.Float64
	}

	return stats, nil
}

// applyOutboxBacklogStats publishes a sampled snapshot to the gauges. Every
// known Status bucket is set (to 0 if absent) so a bucket that empties is
// reported as 0 rather than retaining a stale value.
func applyOutboxBacklogStats(stats outboxBacklogStats) {
	for _, state := range outboxBacklogStates {
		outboxQueueDepth.WithLabelValues(state).Set(float64(stats.depthByState[state]))
	}
	outboxOldestPendingAgeSeconds.Set(stats.oldestPendingAgeSeconds)
}

// InitializeOutboxBacklogMetrics creates every fixed-label time series without
// reading the database. The API observer's stale metric distinguishes these
// initial zero values from a successful exact snapshot.
func InitializeOutboxBacklogMetrics() {
	applyOutboxBacklogStats(outboxBacklogStats{depthByState: map[string]int64{}})
}

// RefreshOutboxBacklogStats performs one exact, read-only backlog observation.
// It publishes only a complete successful snapshot, so callers can retain the
// last-good Prometheus values when any query fails.
func RefreshOutboxBacklogStats(ctx context.Context, db *sql.DB) error {
	stats, err := queryOutboxBacklogStats(ctx, db)
	if err != nil {
		return err
	}
	applyOutboxBacklogStats(stats)
	return nil
}