package connector_events

import (
	"context"
	"database/sql"
	"time"
)

type ClaimPressureSnapshot struct {
	InstalledPackID      string
	QueueClasses         []string
	ClaimableCount       int
	PendingCount         int
	FailedDueCount       int
	StaleProcessingCount int
	ProcessingCount      int
	OldestClaimableAt    *time.Time
	OldestClaimableAge   time.Duration
}

func (s *Service) ClaimPressure(ctx context.Context, installedPackID string, queueClasses []string) (ClaimPressureSnapshot, error) {
	snapshot := ClaimPressureSnapshot{
		InstalledPackID: installedPackID,
		QueueClasses:    append([]string(nil), queueClasses...),
	}
	queueClassSQL, queueClassArgs, err := queueClassFilterSQL("e", queueClasses)
	if err != nil {
		return snapshot, err
	}
	now := s.now()
	staleCutoff := now.Add(-StaleProcessingSeconds * time.Second)

	// NCRM #3139 (Problem A): the claimable CASE arms embed the NextAttemptAt-gated
	// ordering guard six times; each embed carries its own (now, now) binds, so the
	// SQL and arg slice are built together to stay positionally aligned. The
	// pressure count must agree with what ClaimNext will actually claim (a newer
	// sibling becomes claimable once its head is backing off), so these embeds use
	// the SAME gated predicate as the claim SELECT.
	gPending, bPending := buildClaimOrderingGuard(now)
	gFailedDue, bFailedDue := buildClaimOrderingGuard(now)
	gStale, bStale := buildClaimOrderingGuard(now)
	gOldestPending, bOldestPending := buildClaimOrderingGuard(now)
	gOldestFailed, bOldestFailed := buildClaimOrderingGuard(now)
	gOldestProc, bOldestProc := buildClaimOrderingGuard(now)

	pressureArgs := make([]interface{}, 0, 18)
	pressureArgs = append(pressureArgs, now) // pending NextAttemptAt
	pressureArgs = append(pressureArgs, bPending...)
	pressureArgs = append(pressureArgs, now) // failed_due NextAttemptAt
	pressureArgs = append(pressureArgs, bFailedDue...)
	pressureArgs = append(pressureArgs, staleCutoff) // stale LastModifiedDate
	pressureArgs = append(pressureArgs, bStale...)
	pressureArgs = append(pressureArgs, now) // oldest pending NextAttemptAt
	pressureArgs = append(pressureArgs, bOldestPending...)
	pressureArgs = append(pressureArgs, now) // oldest failed NextAttemptAt
	pressureArgs = append(pressureArgs, bOldestFailed...)
	pressureArgs = append(pressureArgs, staleCutoff) // oldest processing LastModifiedDate
	pressureArgs = append(pressureArgs, bOldestProc...)
	pressureArgs = append(pressureArgs, installedPackID)
	pressureArgs = append(pressureArgs, queueClassArgs...)

	var oldest sql.NullTime
	err = s.db.QueryRowContext(ctx, `
SELECT
  COALESCE(SUM(CASE WHEN e.Status = 'pending' AND (e.NextAttemptAt IS NULL OR e.NextAttemptAt <= ?) AND (`+gPending+`) THEN 1 ELSE 0 END), 0) AS pending_count,
  COALESCE(SUM(CASE WHEN e.Status = 'failed' AND (e.NextAttemptAt IS NULL OR e.NextAttemptAt <= ?) AND (`+gFailedDue+`) THEN 1 ELSE 0 END), 0) AS failed_due_count,
  COALESCE(SUM(CASE WHEN e.Status = 'processing' AND e.LastModifiedDate < ? AND (`+gStale+`) THEN 1 ELSE 0 END), 0) AS stale_processing_count,
  COALESCE(SUM(CASE WHEN e.Status = 'processing' THEN 1 ELSE 0 END), 0) AS processing_count,
  MIN(CASE
        WHEN (e.Status = 'pending' AND (e.NextAttemptAt IS NULL OR e.NextAttemptAt <= ?) AND (`+gOldestPending+`))
          OR (e.Status = 'failed' AND (e.NextAttemptAt IS NULL OR e.NextAttemptAt <= ?) AND (`+gOldestFailed+`))
          OR (e.Status = 'processing' AND e.LastModifiedDate < ? AND (`+gOldestProc+`))
        THEN e.CreatedDate
      END) AS oldest_claimable_at
  FROM _System_ConnectorEvent e
 WHERE e.InstalledPackId = ?
   AND e.Status IN ('pending', 'failed', 'processing')`+queueClassSQL,
		pressureArgs...,
	).Scan(
		&snapshot.PendingCount,
		&snapshot.FailedDueCount,
		&snapshot.StaleProcessingCount,
		&snapshot.ProcessingCount,
		&oldest,
	)
	if err != nil {
		return snapshot, err
	}
	snapshot.ClaimableCount = snapshot.PendingCount + snapshot.FailedDueCount + snapshot.StaleProcessingCount
	if oldest.Valid {
		t := oldest.Time
		snapshot.OldestClaimableAt = &t
		if age := now.Sub(t); age > 0 {
			snapshot.OldestClaimableAge = age
		}
	}
	return snapshot, nil
}

// ProcessingPressure returns the cheap capacity inputs the claim limiter needs:
// total processing rows, plus the stale subset by age. The stale count is only a
// soft reclaim signal. It intentionally does not embed the expensive
// claim-ordering NOT EXISTS guard, so SQLClaimLimiter must not treat stale rows
// as free fresh-claim capacity. Strict reclaimability is decided later by
// ClaimNext/ReclaimStaleNext under the full ordering predicate.
func (s *Service) ProcessingPressure(ctx context.Context, installedPackID string, queueClasses []string) (ClaimPressureSnapshot, error) {
	now := s.now()
	staleCutoff := now.Add(-StaleProcessingSeconds * time.Second)
	snapshot := ClaimPressureSnapshot{
		InstalledPackID: installedPackID,
		QueueClasses:    append([]string(nil), queueClasses...),
	}
	queueClassSQL, queueClassArgs, err := queueClassFilterSQL("e", queueClasses)
	if err != nil {
		return snapshot, err
	}
	// Keep this probe cheap: it runs before every claim budget decision and must
	// not embed the claim-ordering NOT EXISTS guard. Strict ordering is enforced by
	// ClaimNext/ReclaimStaleNext when a row is actually claimed. The stale count is
	// only a soft signal that lets the limiter attempt bounded stale reclaim.
	procArgs := []interface{}{staleCutoff, installedPackID}
	procArgs = append(procArgs, queueClassArgs...)
	err = s.db.QueryRowContext(ctx, `
SELECT
  COUNT(*),
  COALESCE(SUM(CASE WHEN e.LastModifiedDate < ? THEN 1 ELSE 0 END), 0)
  FROM _System_ConnectorEvent e
 WHERE e.InstalledPackId = ?
   AND e.Status = 'processing'`+queueClassSQL, procArgs...).Scan(&snapshot.ProcessingCount, &snapshot.StaleProcessingCount)
	if err != nil {
		return ClaimPressureSnapshot{}, err
	}
	return snapshot, nil
}