OpsLog/internal/qso/stats.go

package qso

import (
	"context"
	"database/sql"
	"fmt"
	"sort"
	"strings"
	"time"
)

// Statistics over the whole logbook.
//
// Everything is aggregated IN GO from one lean scan rather than with SQL GROUP
// BYs. Two reasons: the date maths (year / month / hour of day) would need
// dialect-specific functions — strftime() on SQLite vs YEAR()/HOUR() on MySQL —
// which is exactly the kind of thing that silently works on one backend and
// breaks on the other; and a single pass over a few columns of a 30k-row log is
// a few tens of milliseconds, so the complexity buys nothing.

// Bucket is one labelled count (mode, band, operator, entity…).
type Bucket struct {
	Key   string `json:"key"`
	Count int    `json:"count"`
}

// Gap is a stretch with no QSO at all — the off-air periods. In a contest these
// are the expensive minutes: they are where the score went.
type Gap struct {
	Start   string `json:"start"` // RFC3339 — the last QSO before the silence
	End     string `json:"end"`   // the first QSO after it
	Minutes int    `json:"minutes"`
}

// ContestRun is one contest the operator actually took part in, discovered FROM
// THE LOG (a CONTEST_ID plus the year it ran) rather than from a static list — so
// the picker only ever offers contests you really entered, and never an empty one.
type ContestRun struct {
	ID    string `json:"id"`
	Year  int    `json:"year"`
	Count int    `json:"count"`
	Start string `json:"start"` // first QSO, RFC3339
	End   string `json:"end"`   // last QSO
}

// ContestRuns lists every (contest, year) pair present in the logbook, most
// recent first.
func (r *Repo) ContestRuns(ctx context.Context) ([]ContestRun, error) {
	rows, err := r.db.QueryContext(ctx,
		`SELECT contest_id, qso_date FROM qso WHERE contest_id IS NOT NULL AND contest_id <> ''`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	type key struct {
		id   string
		year int
	}
	agg := map[key]*ContestRun{}
	for rows.Next() {
		var id, dateStr sql.NullString
		if err := rows.Scan(&id, &dateStr); err != nil {
			return nil, err
		}
		cid := strings.ToUpper(strings.TrimSpace(id.String))
		if cid == "" {
			continue
		}
		t := parseTimeLoose(dateStr.String).UTC()
		if t.IsZero() {
			continue
		}
		k := key{cid, t.Year()}
		c, ok := agg[k]
		if !ok {
			c = &ContestRun{ID: cid, Year: t.Year(), Start: t.Format(time.RFC3339), End: t.Format(time.RFC3339)}
			agg[k] = c
		}
		c.Count++
		if t.Format(time.RFC3339) < c.Start {
			c.Start = t.Format(time.RFC3339)
		}
		if t.Format(time.RFC3339) > c.End {
			c.End = t.Format(time.RFC3339)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	out := make([]ContestRun, 0, len(agg))
	for _, c := range agg {
		out = append(out, *c)
	}
	sort.Slice(out, func(i, j int) bool {
		if out[i].Year != out[j].Year {
			return out[i].Year > out[j].Year // most recent first
		}
		return out[i].ID < out[j].ID
	})
	return out, nil
}

// gapThreshold is the silence that counts as "off the air". Short enough to catch
// a real break, long enough not to flag the normal pause between two QSOs.
const gapThreshold = 30 * time.Minute

// rateMaxHours caps the per-hour rate timeline. A contest weekend is ~48 h, so a
// week is generous. This is a READABILITY limit, not a memory one: at 30 days the
// chart is 720 hourly bars, each about a pixel wide with an unreadable label — it
// looks broken, which is exactly how it first shipped. Past this the UI says
// "period too long for an hourly chart" instead of drawing mush.
const rateMaxHours = 7 * 24

// Stats is the whole dashboard payload.
type Stats struct {
	// Headline figures.
	Total       int    `json:"total"`
	UniqueCalls int    `json:"unique_calls"`
	Entities    int    `json:"entities"`   // distinct DXCC entities
	Continents  int    `json:"continents"` // distinct continents
	FirstQSO    string `json:"first_qso"`  // RFC3339, "" when the log is empty
	LastQSO     string `json:"last_qso"`

	// Confirmations (of Total).
	ConfirmedLoTW int `json:"confirmed_lotw"`
	ConfirmedEQSL int `json:"confirmed_eqsl"`
	ConfirmedQSL  int `json:"confirmed_qsl"`
	ConfirmedAny  int `json:"confirmed_any"`

	// Breakdowns, each sorted most → least (bands keep frequency order).
	ByMode      []Bucket `json:"by_mode"`
	ByBand      []Bucket `json:"by_band"`
	ByOperator  []Bucket `json:"by_operator"`
	ByStation   []Bucket `json:"by_station"` // station_callsign (the call put on the air)
	ByContinent []Bucket `json:"by_continent"`
	TopEntities []Bucket `json:"top_entities"`
	ByYear      []Bucket `json:"by_year"`  // chronological
	ByMonth     []Bucket `json:"by_month"` // "YYYY-MM", chronological

	// ── Period / contest metrics ──
	// Meaningful only over a WINDOW: "12 QSO/h" across seventeen years says
	// nothing, but across a contest weekend it is the score. The window is the
	// requested [from,to] when given, else the span of the log.
	WindowStart   string   `json:"window_start"`
	WindowEnd     string   `json:"window_end"`
	WindowHours   float64  `json:"window_hours"`
	AvgPerHour   float64 `json:"avg_per_hour"`   // QSOs ÷ window hours (breaks included — the honest rate)
	AvgPerActive float64 `json:"avg_per_active"` // QSOs ÷ ON-AIR hours

	// On-air and off-air are a TIME BUDGET and must add up to the window:
	//     OnAirMinutes + OffAirMinutes == window
	// The first version counted "clock hours containing at least one QSO" as on-air,
	// so a single QSO at 08:05 booked the whole 08:00 hour. On a 45 h contest that
	// gave 39 h on air AND 16 h 43 off air — 56 h inside a 45 h window. Two numbers
	// measured on incompatible bases can't be compared, and the operator rightly
	// didn't believe either of them.
	OnAirMinutes  int `json:"on_air_minutes"`
	OffAirMinutes int `json:"off_air_minutes"`

	PeakHourKey   string   `json:"peak_hour_key"` // best clock hour (kept for reference)
	PeakHourCount int      `json:"peak_hour_count"`
	Best60        int      `json:"best_60"` // best ROLLING 60 min — the number contesters quote
	Gaps          []Gap    `json:"gaps"`    // the silences that make up OffAirMinutes, longest first
	Rate          []Bucket `json:"rate"`    // QSO per clock hour across the window ("MM-DD HH")

	// The contest RATE SHEET: hour by hour, who made the QSOs.
	// RateOps are the operators, busiest first — that fixed order is also the
	// colour/legend order, so an operator keeps their hue across the whole page.
	// RateByOp[h][o] is operator o's count in hour h; rows align 1:1 with Rate, so
	// the per-operator numbers always sum to the hour's total.
	RateOps  []string `json:"rate_ops"`
	RateByOp [][]int  `json:"rate_by_op"`
}

// entry is one dated QSO with the operator who made it — the pair the contest
// rate sheet needs. A bare timestamp can tell you HOW MANY, never BY WHOM.
type entry struct {
	t  time.Time
	op string
}

// bandOrder sorts bands by frequency (160m → 70cm) rather than alphabetically,
// so the band chart reads like a band plan instead of a jumble.
var bandOrder = map[string]int{
	"2190m": 1, "630m": 2, "160m": 3, "80m": 4, "60m": 5, "40m": 6, "30m": 7,
	"20m": 8, "17m": 9, "15m": 10, "12m": 11, "10m": 12, "6m": 13, "4m": 14,
	"2m": 15, "1.25m": 16, "70cm": 17, "23cm": 18, "13cm": 19,
}

// yes reports whether an ADIF confirmation flag means "confirmed".
func yes(s string) bool {
	switch strings.ToUpper(strings.TrimSpace(s)) {
	case "Y", "V": // V = verified (LoTW)
		return true
	}
	return false
}

// Stats scans the logbook once and returns every breakdown the dashboard needs,
// restricted to [from, to] (a zero time means "no bound", so a zero/zero pair is
// the whole log).
//
// The window is applied HERE, in Go, on the parsed timestamp — not as a SQL
// WHERE. qso_date is a text column whose format differs between the two backends,
// so a string comparison would quietly select the wrong rows on one of them. We
// already parse every date in this pass; filtering on the parsed value is both
// correct and free.
// contestID (with an optional year, 0 = any) narrows the log to one contest. When
// it is set and no explicit window is given, the window becomes the contest's own
// span — so rate, best-hour and off-air figures are computed over the contest
// itself without the operator having to look its dates up.
func (r *Repo) Stats(ctx context.Context, from, to time.Time, contestID string, year int) (Stats, error) {
	var s Stats
	contestID = strings.ToUpper(strings.TrimSpace(contestID))

	rows, err := r.db.QueryContext(ctx, `
		SELECT callsign, qso_date, band, mode, cont, country, dxcc,
		       operator, station_callsign, lotw_rcvd, eqsl_rcvd, qsl_rcvd, contest_id
		FROM qso`)
	if err != nil {
		return s, err
	}
	defer rows.Close()

	var (
		calls      = map[string]struct{}{}
		entities   = map[int]struct{}{}
		modeC      = map[string]int{}
		bandC      = map[string]int{}
		opC        = map[string]int{}
		stationC   = map[string]int{}
		contC      = map[string]int{}
		entityC    = map[string]int{}
		yearC      = map[string]int{}
		monthC      = map[string]int{}
		times       []entry     // every dated QSO (+ its operator), for the rate / gap maths
		first, last time.Time
	)

	for rows.Next() {
		var (
			call, band, mode, cont, country     sql.NullString
			oper, station                        sql.NullString
			lotw, eqsl, paper                    sql.NullString
			dxcc                                 sql.NullInt64
			dateStr, contestID2                  sql.NullString
		)
		if err := rows.Scan(&call, &dateStr, &band, &mode, &cont, &country, &dxcc,
			&oper, &station, &lotw, &eqsl, &paper, &contestID2); err != nil {
			return s, err
		}

		// Contest filter first — same reasoning as the window below: a QSO that
		// isn't in this contest must not reach ANY bucket.
		if contestID != "" && strings.ToUpper(strings.TrimSpace(contestID2.String)) != contestID {
			continue
		}

		// Window first: a QSO outside the period must not reach ANY bucket. Doing
		// this after the counting (the obvious mistake) would leave the mode/band/
		// operator charts showing the whole log while only the trend was filtered.
		// parseTimeLoose is the repo's existing convention for qso_date — it copes
		// with what each backend hands back (SQLite ISO string, MySQL DATETIME).
		t := parseTimeLoose(dateStr.String).UTC()
		dated := !t.IsZero()
		if year > 0 && (!dated || t.Year() != year) {
			continue
		}
		if !from.IsZero() && (!dated || t.Before(from)) {
			continue
		}
		if !to.IsZero() && (!dated || t.After(to)) {
			continue
		}

		s.Total++

		if c := strings.ToUpper(strings.TrimSpace(call.String)); c != "" {
			calls[c] = struct{}{}
		}
		if dxcc.Valid && dxcc.Int64 > 0 {
			entities[int(dxcc.Int64)] = struct{}{}
		}
		if m := strings.ToUpper(strings.TrimSpace(mode.String)); m != "" {
			modeC[m]++
		}
		if b := strings.ToLower(strings.TrimSpace(band.String)); b != "" {
			bandC[b]++
		}
		// An empty OPERATOR means "the station owner logged it himself" — bucket
		// it explicitly rather than dropping the QSO from the operator chart.
		op := strings.ToUpper(strings.TrimSpace(oper.String))
		if op == "" {
			op = "—"
		}
		opC[op]++
		if st := strings.ToUpper(strings.TrimSpace(station.String)); st != "" {
			stationC[st]++
		}
		if c := strings.ToUpper(strings.TrimSpace(cont.String)); c != "" {
			contC[c]++
		}
		if c := strings.TrimSpace(country.String); c != "" {
			entityC[c]++
		}

		cl, el, pl := yes(lotw.String), yes(eqsl.String), yes(paper.String)
		if cl {
			s.ConfirmedLoTW++
		}
		if el {
			s.ConfirmedEQSL++
		}
		if pl {
			s.ConfirmedQSL++
		}
		if cl || el || pl {
			s.ConfirmedAny++
		}

		// An undated QSO still counts in the mode/band/operator totals above, but
		// it can't be placed on a time axis — leave it out of the trend rather than
		// parking it at year zero.
		if !dated {
			continue
		}
		if first.IsZero() || t.Before(first) {
			first = t
		}
		if last.IsZero() || t.After(last) {
			last = t
		}
		yearC[t.Format("2006")]++
		monthC[t.Format("2006-01")]++
		times = append(times, entry{t: t, op: op})
	}
	if err := rows.Err(); err != nil {
		return s, err
	}

	s.UniqueCalls = len(calls)
	s.Entities = len(entities)
	s.Continents = len(contC)
	if !first.IsZero() {
		s.FirstQSO = first.UTC().Format(time.RFC3339)
		s.LastQSO = last.UTC().Format(time.RFC3339)
	}

	s.ByMode = topBuckets(modeC, 0)
	s.ByOperator = topBuckets(opC, 0)
	s.ByStation = topBuckets(stationC, 0)
	s.ByContinent = topBuckets(contC, 0)
	s.TopEntities = topBuckets(entityC, 15)

	// Bands read in band-plan order, not by count — the shape of the chart IS
	// the band plan, and re-sorting it by size would destroy that.
	s.ByBand = sortedBuckets(bandC, func(a, b string) bool {
		oa, ob := bandOrder[a], bandOrder[b]
		if oa == 0 {
			oa = 99
		}
		if ob == 0 {
			ob = 99
		}
		if oa != ob {
			return oa < ob
		}
		return a < b
	})
	// The time axis must be CONTINUOUS. Emitting only the months that have QSOs
	// would place, say, 2012-08 next to 2022-01 as if they were consecutive — the
	// chart would invent activity that never happened. A gap in the log is real
	// information: it belongs on the chart as zeros.
	s.ByYear = fillYears(yearC, first, last)
	s.ByMonth = fillMonths(monthC, first, last)

	s.periodMetrics(times, from, to, first, last)
	s.ensureNonNil()

	return s, nil
}

// ensureNonNil replaces every nil slice with an empty one.
//
// This is NOT cosmetic. A nil Go slice marshals to JSON `null`, not `[]`, and the
// UI then calls .length / .map on null — a TypeError that unmounts the whole React
// tree and leaves a WHITE SCREEN. It bites exactly in the innocent cases: a contest
// with no break ≥ 30 min (Gaps nil), or a window too long for the hourly chart
// (Rate nil). The awards code carries the same guard for the same reason.
func (s *Stats) ensureNonNil() {
	if s.ByMode == nil {
		s.ByMode = []Bucket{}
	}
	if s.ByBand == nil {
		s.ByBand = []Bucket{}
	}
	if s.ByOperator == nil {
		s.ByOperator = []Bucket{}
	}
	if s.ByStation == nil {
		s.ByStation = []Bucket{}
	}
	if s.ByContinent == nil {
		s.ByContinent = []Bucket{}
	}
	if s.TopEntities == nil {
		s.TopEntities = []Bucket{}
	}
	if s.ByYear == nil {
		s.ByYear = []Bucket{}
	}
	if s.ByMonth == nil {
		s.ByMonth = []Bucket{}
	}
	if s.Rate == nil {
		s.Rate = []Bucket{}
	}
	if s.Gaps == nil {
		s.Gaps = []Gap{}
	}
	if s.RateOps == nil {
		s.RateOps = []string{}
	}
	if s.RateByOp == nil {
		s.RateByOp = [][]int{}
	}
}

// periodMetrics derives the rate / off-air figures that make a contest window
// readable. The window is the caller's [from,to] when given, else the span of the
// log itself.
//
// Two rates are reported on purpose, because a single one always flatters:
//   • AvgPerHour  = QSOs ÷ the WHOLE window — breaks included. The honest number.
//   • AvgPerActive = QSOs ÷ the hours actually operated. Flatters, but tells you
//     how fast you go when you ARE at the radio.
// Quoting only the second is how an 8-hour effort gets sold as a 48-hour score.
func (s *Stats) periodMetrics(times []entry, from, to, first, last time.Time) {
	if len(times) == 0 {
		return
	}
	sort.Slice(times, func(i, j int) bool { return times[i].t.Before(times[j].t) })

	winStart, winEnd := from, to
	if winStart.IsZero() {
		winStart = first
	}
	if winEnd.IsZero() {
		winEnd = last
	}
	if !winEnd.After(winStart) {
		return
	}
	s.WindowStart = winStart.Format(time.RFC3339)
	s.WindowEnd = winEnd.Format(time.RFC3339)
	s.WindowHours = winEnd.Sub(winStart).Hours()
	if s.WindowHours > 0 {
		s.AvgPerHour = float64(len(times)) / s.WindowHours
	}

	// Clock-hour buckets — for the rate chart and the best clock hour only. NOT for
	// "hours on air": a single QSO at 08:05 would book the whole 08:00 hour.
	hourly := map[string]int{}
	for _, e := range times {
		hourly[e.t.Format("2006-01-02 15")]++
	}
	for k, v := range hourly {
		if v > s.PeakHourCount || (v == s.PeakHourCount && k < s.PeakHourKey) {
			s.PeakHourKey, s.PeakHourCount = k, v
		}
	}

	// Best ROLLING 60 minutes — not the best clock hour. A run straddling 13:45–
	// 14:45 is invisible to clock-hour bucketing, and it's the figure contesters
	// actually quote. Two pointers over the sorted times: O(n).
	lo := 0
	for hi := range times {
		for times[hi].t.Sub(times[lo].t) >= time.Hour {
			lo++
		}
		if n := hi - lo + 1; n > s.Best60 {
			s.Best60 = n
		}
	}

	// Off-air is a TIME BUDGET, and it has to close on the window:
	//     OnAirMinutes + OffAirMinutes == window
	// So every silence ≥ 30 min counts — including the lead-in before the first QSO
	// and the tail after the last, when an explicit window was asked for. Skipping
	// those (the first version did) makes "on air" and "off air" sum to more than
	// the window, and then neither number is believable.
	addGap := func(a, b time.Time) {
		d := b.Sub(a)
		if d < gapThreshold {
			return
		}
		s.OffAirMinutes += int(d.Minutes())
		s.Gaps = append(s.Gaps, Gap{
			Start:   a.Format(time.RFC3339),
			End:     b.Format(time.RFC3339),
			Minutes: int(d.Minutes()),
		})
	}
	addGap(winStart, times[0].t)      // lead-in
	for i := 1; i < len(times); i++ { // the silences between QSOs
		addGap(times[i-1].t, times[i].t)
	}
	addGap(times[len(times)-1].t, winEnd) // tail

	s.OnAirMinutes = int(winEnd.Sub(winStart).Minutes()) - s.OffAirMinutes
	if s.OnAirMinutes < 0 {
		s.OnAirMinutes = 0
	}
	if s.OnAirMinutes > 0 {
		s.AvgPerActive = float64(len(times)) / (float64(s.OnAirMinutes) / 60)
	}
	sort.Slice(s.Gaps, func(i, j int) bool { return s.Gaps[i].Minutes > s.Gaps[j].Minutes })
	if len(s.Gaps) > 10 {
		s.Gaps = s.Gaps[:10] // the long ones are the story; the tail is noise
	}

	// Per-hour rate timeline + the RATE SHEET (who made those QSOs, hour by hour).
	// Every hour of the window, zeros included, so the silences read as silences.
	if s.WindowHours > rateMaxHours {
		return
	}

	// Operators, busiest first. That order is fixed and reused as the colour/legend
	// order, so an operator keeps the same hue everywhere on the page — a chart that
	// repaints its series when the filter changes is a chart nobody can trust.
	opTotals := map[string]int{}
	for _, e := range times {
		opTotals[e.op]++
	}
	s.RateOps = make([]string, 0, len(opTotals))
	for op := range opTotals {
		s.RateOps = append(s.RateOps, op)
	}
	sort.Slice(s.RateOps, func(i, j int) bool {
		a, b := s.RateOps[i], s.RateOps[j]
		if opTotals[a] != opTotals[b] {
			return opTotals[a] > opTotals[b]
		}
		return a < b
	})
	// Never invent a 9th colour: past 8 operators the tail folds into "Other", which
	// is honest and still sums correctly.
	const maxOps = 8
	folded := false
	if len(s.RateOps) > maxOps {
		s.RateOps = append(s.RateOps[:maxOps:maxOps], otherOp)
		folded = true
	}
	opIdx := map[string]int{}
	for i, op := range s.RateOps {
		opIdx[op] = i
	}
	slotFor := func(op string) int {
		if i, ok := opIdx[op]; ok {
			return i
		}
		if folded {
			return len(s.RateOps) - 1 // the "Other" bucket
		}
		return -1
	}

	// hourOps[hourKey][slot] — built from the same `times` as `hourly`, so the
	// per-operator numbers ALWAYS sum to the hour's total. Deriving them separately
	// is how a rate sheet ends up not adding up to its own total row.
	hourOps := map[string][]int{}
	for _, e := range times {
		k := e.t.Format("2006-01-02 15")
		row, ok := hourOps[k]
		if !ok {
			row = make([]int, len(s.RateOps))
			hourOps[k] = row
		}
		if i := slotFor(e.op); i >= 0 {
			row[i]++
		}
	}

	cur := winStart.Truncate(time.Hour)
	end := winEnd.Truncate(time.Hour)
	for !cur.After(end) {
		k := cur.Format("2006-01-02 15")
		s.Rate = append(s.Rate, Bucket{Key: cur.Format("01-02 15"), Count: hourly[k]})
		row := hourOps[k]
		if row == nil {
			row = make([]int, len(s.RateOps)) // a silent hour is zeros, not a missing row
		}
		s.RateByOp = append(s.RateByOp, row)
		cur = cur.Add(time.Hour)
	}
}

// otherOp is where operators past the 8th are folded. Generating a 9th colour is
// never the answer: under colour-blindness it is indistinguishable from one of the
// existing eight.
const otherOp = "Other"

// topBuckets sorts a count map most → least (ties alphabetical) and optionally
// keeps only the top n.
func topBuckets(m map[string]int, n int) []Bucket {
	out := make([]Bucket, 0, len(m))
	for k, v := range m {
		out = append(out, Bucket{Key: k, Count: v})
	}
	sort.Slice(out, func(i, j int) bool {
		if out[i].Count != out[j].Count {
			return out[i].Count > out[j].Count
		}
		return out[i].Key < out[j].Key
	})
	if n > 0 && len(out) > n {
		out = out[:n]
	}
	return out
}

// sortedBuckets keeps a caller-defined key order (band plan, chronology).
func sortedBuckets(m map[string]int, less func(a, b string) bool) []Bucket {
	keys := make([]string, 0, len(m))
	for k := range m {
		keys = append(keys, k)
	}
	sort.Slice(keys, func(i, j int) bool { return less(keys[i], keys[j]) })
	out := make([]Bucket, 0, len(keys))
	for _, k := range keys {
		out = append(out, Bucket{Key: k, Count: m[k]})
	}
	return out
}


// fillMonths emits EVERY month between the first and last QSO — zeros included —
// so the trend line's x-axis is real time rather than "months that happen to have
// data". A quiet decade must read as a decade at zero, not vanish.
func fillMonths(m map[string]int, first, last time.Time) []Bucket {
	if first.IsZero() {
		return nil
	}
	var out []Bucket
	cur := time.Date(first.Year(), first.Month(), 1, 0, 0, 0, 0, time.UTC)
	end := time.Date(last.Year(), last.Month(), 1, 0, 0, 0, 0, time.UTC)
	for !cur.After(end) {
		k := cur.Format("2006-01")
		out = append(out, Bucket{Key: k, Count: m[k]})
		cur = cur.AddDate(0, 1, 0)
	}
	return out
}

// fillYears does the same for the yearly view.
func fillYears(m map[string]int, first, last time.Time) []Bucket {
	if first.IsZero() {
		return nil
	}
	var out []Bucket
	for y := first.Year(); y <= last.Year(); y++ {
		k := fmt.Sprintf("%04d", y)
		out = append(out, Bucket{Key: k, Count: m[k]})
	}
	return out
}