package intelligence

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

// Velocity is one tracker's ratio rate-of-change over a window.
type Velocity struct {
	TrackerID    string    `json:"tracker_id"`
	WindowStart  time.Time `json:"window_start"`
	WindowEnd    time.Time `json:"window_end"`
	StartRatio   float64   `json:"start_ratio"`
	EndRatio     float64   `json:"end_ratio"`
	SlopePerDay  float64   `json:"slope_per_day"`
	SampleCount  int       `json:"sample_count"`
	Trend        string    `json:"trend"` // "rising" | "falling" | "flat"
}

// ComputeAll returns the velocity per tracker over the given window. The
// computation is intentionally simple: (last - first) / days. A regression
// would be more sophisticated but the operator's mental model is the
// straight-line slope, and the snapshot cadence (one row every 5-10
// minutes) makes the simple form ~indistinguishable from a regression at
// single-operator scale.
func ComputeAll(ctx context.Context, db *sql.DB, window time.Duration) ([]Velocity, error) {
	if window <= 0 {
		window = 24 * time.Hour
	}
	cutoff := time.Now().Add(-window).Unix()

	rows, err := db.QueryContext(ctx, `
		WITH first_in_window AS (
		  SELECT tracker_id, real_ratio, timestamp,
		         ROW_NUMBER() OVER (PARTITION BY tracker_id ORDER BY timestamp ASC) AS rn
		  FROM ratio_snapshots
		  WHERE simulation_id IS NULL AND timestamp >= ?
		),
		last_in_window AS (
		  SELECT tracker_id, real_ratio, timestamp,
		         ROW_NUMBER() OVER (PARTITION BY tracker_id ORDER BY timestamp DESC) AS rn
		  FROM ratio_snapshots
		  WHERE simulation_id IS NULL AND timestamp >= ?
		),
		counts AS (
		  SELECT tracker_id, COUNT(*) AS n
		  FROM ratio_snapshots
		  WHERE simulation_id IS NULL AND timestamp >= ?
		  GROUP BY tracker_id
		)
		SELECT f.tracker_id, f.real_ratio AS start_r, f.timestamp AS start_t,
		       l.real_ratio AS end_r, l.timestamp AS end_t, c.n
		FROM first_in_window f
		JOIN last_in_window l USING(tracker_id)
		JOIN counts c USING(tracker_id)
		WHERE f.rn = 1 AND l.rn = 1
	`, cutoff, cutoff, cutoff)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []Velocity
	for rows.Next() {
		var (
			id                       string
			startR, endR             sql.NullFloat64
			startT, endT             int64
			n                        int
		)
		if err := rows.Scan(&id, &startR, &startT, &endR, &endT, &n); err != nil {
			return nil, err
		}
		if !startR.Valid || !endR.Valid {
			continue
		}
		v := Velocity{
			TrackerID:   id,
			WindowStart: time.Unix(startT, 0),
			WindowEnd:   time.Unix(endT, 0),
			StartRatio:  startR.Float64,
			EndRatio:    endR.Float64,
			SampleCount: n,
		}
		dur := v.WindowEnd.Sub(v.WindowStart).Hours()
		if dur > 0 {
			v.SlopePerDay = (v.EndRatio - v.StartRatio) / (dur / 24.0)
		}
		switch {
		case v.SlopePerDay > 0.01:
			v.Trend = "rising"
		case v.SlopePerDay < -0.01:
			v.Trend = "falling"
		default:
			v.Trend = "flat"
		}
		out = append(out, v)
	}
	return out, rows.Err()
}