Pete/internal/storage/roster.go

package storage

import (
	"database/sql"
	"encoding/json"
	"log/slog"
)

// RosterEntry is one adventurer's currently-true state, as of the last snapshot
// gogobee pushed. Not an event — nothing here is a thing that *happened*.
type RosterEntry struct {
	Token      string `json:"token"`
	Name       string `json:"name"`
	Level      int    `json:"level"`
	ClassRace  string `json:"class_race"`
	Status     string `json:"status"` // "expedition" | "idle"
	Zone       string `json:"zone,omitempty"`
	Region     string `json:"region,omitempty"`
	Day        int    `json:"day,omitempty"`
	IdleHours  int    `json:"idle_hours,omitempty"`
	SnapshotAt int64  `json:"snapshot_at"`
	// Detail is the public expanded sheet (stats + gear), carried as raw JSON so
	// the board path never has to model or touch it — only the detail page decodes
	// it. Empty when a snapshot predates the detail push.
	Detail json.RawMessage `json:"detail,omitempty"`
}

// ReplaceRoster swaps the whole board for a new snapshot, in one transaction.
//
// Replace — never merge. A player who dropped out of gogobee's snapshot (deleted
// character, or a fresh `!news optout`) must vanish from the board, and an
// upsert would leave them standing there forever. The transaction means a reader
// mid-swap sees the old board or the new one, never a half-empty realm.
func ReplaceRoster(entries []RosterEntry, snapshotAt int64) error {
	tx, err := Get().Begin()
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	if _, err := tx.Exec(`DELETE FROM adventure_roster`); err != nil {
		return err
	}
	stmt, err := tx.Prepare(`
		INSERT INTO adventure_roster
		    (token, name, level, class_race, status, zone, region, day, idle_hours, snapshot_at, detail_json)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`)
	if err != nil {
		return err
	}
	defer stmt.Close()

	for _, e := range entries {
		var detail any // NULL when absent, so the column reads as "no detail", not "{}"
		if len(e.Detail) > 0 {
			detail = string(e.Detail)
		}
		if _, err := stmt.Exec(e.Token, e.Name, e.Level, e.ClassRace, e.Status,
			e.Zone, e.Region, e.Day, e.IdleHours, snapshotAt, detail); err != nil {
			return err
		}
	}
	// Stamp the snapshot even when it carried zero adventurers — that is a
	// legitimate state (quiet realm) and must not read as "gogobee went away".
	if _, err := tx.Exec(`
		INSERT INTO adventure_roster_meta (id, snapshot_at) VALUES (1, ?)
		ON CONFLICT(id) DO UPDATE SET snapshot_at = excluded.snapshot_at`, snapshotAt); err != nil {
		return err
	}
	return tx.Commit()
}

// LoadRoster returns the current board: everyone on an expedition first (most
// recently departed at the top of that group), then the idle, longest-idle last.
// Also returns the snapshot time so the caller can decide whether the wire has
// gone quiet — see rosterStale.
func LoadRoster() ([]RosterEntry, int64, error) {
	rows, err := Get().Query(`
		SELECT token, name, level, COALESCE(class_race, ''), status,
		       COALESCE(zone, ''), COALESCE(region, ''), day, idle_hours, snapshot_at
		  FROM adventure_roster
		 ORDER BY status = 'expedition' DESC, day DESC, idle_hours ASC, level DESC, name ASC`)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var out []RosterEntry
	for rows.Next() {
		var e RosterEntry
		if err := rows.Scan(&e.Token, &e.Name, &e.Level, &e.ClassRace, &e.Status,
			&e.Zone, &e.Region, &e.Day, &e.IdleHours, &e.SnapshotAt); err != nil {
			return nil, 0, err
		}
		out = append(out, e)
	}
	if err := rows.Err(); err != nil {
		return nil, 0, err
	}
	return out, RosterSnapshotAt(), nil
}

// RosterEntryByToken looks up one adventurer by their roster token. The bool is
// false when no such token is on the current board — which is exactly the check
// the storefront needs: a buyer may only order a hit on a mark the live board is
// actually showing, never a stale or guessed token.
func RosterEntryByToken(token string) (RosterEntry, bool, error) {
	var e RosterEntry
	var detail sql.NullString
	err := Get().QueryRow(`
		SELECT token, name, level, COALESCE(class_race, ''), status,
		       COALESCE(zone, ''), COALESCE(region, ''), day, idle_hours, snapshot_at, detail_json
		  FROM adventure_roster WHERE token = ?`, token).Scan(
		&e.Token, &e.Name, &e.Level, &e.ClassRace, &e.Status,
		&e.Zone, &e.Region, &e.Day, &e.IdleHours, &e.SnapshotAt, &detail)
	if err == sql.ErrNoRows {
		return RosterEntry{}, false, nil
	}
	if err != nil {
		return RosterEntry{}, false, err
	}
	if detail.Valid && detail.String != "" {
		e.Detail = json.RawMessage(detail.String)
	}
	return e, true, nil
}

// RosterSnapshotAt reports when the board was last refreshed, 0 if gogobee has
// never pushed one. Read from the meta row, not the entries, so a snapshot that
// legitimately carried nobody still counts as a snapshot.
func RosterSnapshotAt() int64 {
	var at sql.NullInt64
	err := Get().QueryRow(`SELECT snapshot_at FROM adventure_roster_meta WHERE id = 1`).Scan(&at)
	if err == sql.ErrNoRows {
		return 0
	}
	if err != nil {
		slog.Error("RosterSnapshotAt query failed", "err", err)
		return 0
	}
	return at.Int64
}