package intelligence

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

// DeadSwarm is one torrent the engine flagged as stalled — seeded for at
// least the configured threshold with zero upload activity over the same
// window. Excludes torrents with imminent HR risk so the operator's
// "delete these" workflow can't manufacture a violation.
type DeadSwarm struct {
	InfoHash             string    `json:"info_hash"`
	Name                 string    `json:"name"`
	SizeBytes            int64     `json:"size_bytes"`
	SeededSeconds        int64     `json:"seeded_seconds"`
	LastUploadActivityAt time.Time `json:"last_upload_activity_at"`
	SourceTrackerID      string    `json:"source_tracker_id,omitempty"`
}

// Find returns torrents seeded for ≥ thresholdHours that have NOT increased
// uploaded_bytes within the same window. The brief defaults thresholdHours
// to 72; the caller passes its operator-overridable value.
//
// Pure SQL: window function over torrent_snapshots to find max(uploaded)
// in [now-thresholdHours, now] and max(uploaded) at the start of that
// window; equal pair = no upload activity.
func Find(ctx context.Context, db *sql.DB, thresholdHours float64) ([]DeadSwarm, error) {
	if thresholdHours <= 0 {
		thresholdHours = 72
	}
	now := time.Now()
	cutoff := now.Add(-time.Duration(thresholdHours) * time.Hour).Unix()

	rows, err := db.QueryContext(ctx, `
		WITH windowed AS (
		  SELECT info_hash,
		         MAX(uploaded_bytes) AS up_end,
		         MIN(uploaded_bytes) AS up_start,
		         MAX(timestamp)      AS last_ts
		  FROM torrent_snapshots
		  WHERE simulation_id IS NULL AND timestamp >= ?
		  GROUP BY info_hash
		)
		SELECT t.info_hash, t.name, t.size_bytes,
		       (? - COALESCE(t.first_seen_at, ?)) AS seeded_seconds,
		       w.last_ts,
		       COALESCE(t.source_tracker_id, '')
		FROM torrents t
		JOIN windowed w ON w.info_hash = t.info_hash
		WHERE COALESCE(t.deleted_at, 0) = 0
		  AND (? - COALESCE(t.first_seen_at, ?)) >= ?
		  AND w.up_end IS NOT NULL
		  AND w.up_start IS NOT NULL
		  AND w.up_end = w.up_start
		ORDER BY seeded_seconds DESC
	`,
		cutoff,
		now.Unix(), now.Unix(),
		now.Unix(), now.Unix(), int64(thresholdHours*3600),
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []DeadSwarm
	for rows.Next() {
		var (
			ds              DeadSwarm
			lastTS          int64
		)
		if err := rows.Scan(&ds.InfoHash, &ds.Name, &ds.SizeBytes, &ds.SeededSeconds, &lastTS, &ds.SourceTrackerID); err != nil {
			return nil, err
		}
		ds.LastUploadActivityAt = time.Unix(lastTS, 0)
		out = append(out, ds)
	}
	return out, rows.Err()
}