1// Package store provides persistent storage for saved games.2package store34import (5 "database/sql"6 "errors"7 "fmt"8 "os"9 "path/filepath"10 "time"1112 _ "modernc.org/sqlite"13)1415const createTableSQL = `16CREATE TABLE IF NOT EXISTS games (17 id INTEGER PRIMARY KEY AUTOINCREMENT,18 name TEXT NOT NULL UNIQUE,19 game_id TEXT NOT NULL DEFAULT '',20 game_type TEXT NOT NULL,21 variant_id TEXT NOT NULL DEFAULT '',22 variant TEXT NOT NULL DEFAULT '',23 mode_id TEXT NOT NULL DEFAULT '',24 mode TEXT NOT NULL,25 initial_state TEXT NOT NULL,26 save_state TEXT,27 status TEXT NOT NULL DEFAULT 'new'28 CHECK(status IN ('new','in_progress','completed','abandoned')),29 run_kind TEXT NOT NULL DEFAULT 'normal'30 CHECK(run_kind IN ('normal','daily','weekly','seeded')),31 run_date DATE,32 week_year INTEGER,33 week_number INTEGER,34 week_index INTEGER,35 seed_text TEXT,36 target_difficulty_elo INTEGER,37 actual_difficulty_elo INTEGER,38 difficulty_confidence TEXT,39 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,40 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,41 completed_at DATETIME42);`4344const createCategoryStatsViewSQL = `45CREATE VIEW IF NOT EXISTS category_stats AS46SELECT47 game_type,48 COUNT(*) AS total_attempts,49 COUNT(*) FILTER (WHERE status = 'completed') AS total_victories,50 COUNT(*) FILTER (WHERE status = 'abandoned') AS total_abandoned,51 COUNT(*) FILTER (WHERE run_kind = 'daily') AS times_daily,52 COUNT(*) FILTER (WHERE status = 'completed'53 AND run_kind = 'daily') AS daily_victories,54 (SELECT COALESCE(NULLIF(variant, ''), mode) FROM games g255 WHERE g2.game_type = games.game_type56 AND g2.status = 'completed'57 GROUP BY COALESCE(NULLIF(variant, ''), mode)58 ORDER BY COUNT(*) DESC, COALESCE(NULLIF(variant, ''), mode) ASC59 LIMIT 1) AS preferred_mode60FROM games61GROUP BY game_type;`6263const createModeStatsViewSQL = `64CREATE VIEW IF NOT EXISTS mode_stats AS65SELECT66 game_type,67 COALESCE(NULLIF(variant, ''), mode) AS mode,68 COUNT(*) FILTER (WHERE status = 'completed') AS victories,69 COUNT(*) FILTER (WHERE status = 'completed'70 AND run_kind = 'daily') AS daily_victories,71 CAST(ROUND(AVG(COALESCE(actual_difficulty_elo, target_difficulty_elo))72 FILTER (WHERE status = 'completed'73 AND COALESCE(actual_difficulty_elo, target_difficulty_elo) IS NOT NULL)) AS INTEGER) AS difficulty_elo74FROM games75GROUP BY game_type, COALESCE(NULLIF(variant, ''), mode);`7677type Store struct {78 db *sql.DB79}8081const gameSelectColumns = `id, name, game_id, game_type, variant_id, variant, mode_id, mode, initial_state, save_state,82 status, run_kind, run_date, week_year, week_number, week_index, seed_text,83 target_difficulty_elo, actual_difficulty_elo, difficulty_confidence,84 created_at, updated_at, completed_at`8586type rowScanner interface {87 Scan(dest ...any) error88}8990func scanGameRecord(scanner rowScanner) (GameRecord, error) {91 var g GameRecord92 var runDate sql.NullTime93 var weekYear sql.NullInt6494 var weekNumber sql.NullInt6495 var weekIndex sql.NullInt6496 var seedText sql.NullString97 var targetDifficultyElo sql.NullInt6498 var actualDifficultyElo sql.NullInt6499 var difficultyConfidence sql.NullString100 var completedAt sql.NullTime101 if err := scanner.Scan(102 &g.ID, &g.Name, &g.GameID, &g.GameType, &g.VariantID, &g.Variant, &g.ModeID, &g.Mode,103 &g.InitialState, &g.SaveState, &g.Status,104 &g.RunKind, &runDate, &weekYear, &weekNumber, &weekIndex, &seedText,105 &targetDifficultyElo, &actualDifficultyElo, &difficultyConfidence,106 &g.CreatedAt, &g.UpdatedAt, &completedAt,107 ); err != nil {108 return GameRecord{}, err109 }110 if runDate.Valid {111 t := runDate.Time112 g.RunDate = &t113 }114 if weekYear.Valid {115 g.WeekYear = int(weekYear.Int64)116 }117 if weekNumber.Valid {118 g.WeekNumber = int(weekNumber.Int64)119 }120 if weekIndex.Valid {121 g.WeekIndex = int(weekIndex.Int64)122 }123 if seedText.Valid {124 g.SeedText = seedText.String125 }126 if targetDifficultyElo.Valid {127 v := int(targetDifficultyElo.Int64)128 g.TargetDifficultyElo = &v129 }130 if actualDifficultyElo.Valid {131 v := int(actualDifficultyElo.Int64)132 g.ActualDifficultyElo = &v133 }134 if difficultyConfidence.Valid {135 g.DifficultyConfidence = difficultyConfidence.String136 }137 if completedAt.Valid {138 g.CompletedAt = &completedAt.Time139 }140 return g, nil141}142143func (s *Store) listGamesByQuery(query string, args ...any) ([]GameRecord, error) {144 rows, err := s.db.Query(query, args...)145 if err != nil {146 return nil, fmt.Errorf("querying games: %w", err)147 }148 defer rows.Close()149150 var games []GameRecord151 for rows.Next() {152 g, err := scanGameRecord(rows)153 if err != nil {154 return nil, fmt.Errorf("scanning game row: %w", err)155 }156 games = append(games, g)157 }158 return games, rows.Err()159}160161func (s *Store) getGameByQuery(query string, args ...any) (*GameRecord, error) {162 g, err := scanGameRecord(s.db.QueryRow(query, args...))163 if errors.Is(err, sql.ErrNoRows) {164 return nil, nil165 }166 if err != nil {167 return nil, err168 }169 return &g, nil170}171172// Open creates the directory if needed, opens the database, creates the table,173// and enables WAL mode.174func Open(dbPath string) (*Store, error) {175 dir := filepath.Dir(dbPath)176 if err := os.MkdirAll(dir, 0o755); err != nil {177 return nil, fmt.Errorf("creating db directory: %w", err)178 }179180 db, err := sql.Open("sqlite", dbPath)181 if err != nil {182 return nil, fmt.Errorf("opening database: %w", err)183 }184185 if _, err := db.Exec("PRAGMA journal_mode=WAL"); err != nil {186 db.Close()187 return nil, fmt.Errorf("enabling WAL mode: %w", err)188 }189190 if err := runMigrations(db); err != nil {191 db.Close()192 return nil, err193 }194195 return &Store{db: db}, nil196}197198// CreateGame inserts a new game record and sets rec.ID.199func (s *Store) CreateGame(rec *GameRecord) error {200 if rec.GameID == "" {201 rec.GameID = CanonicalGameID(rec.GameType)202 }203 if rec.ModeID == "" {204 rec.ModeID = CanonicalModeID(rec.Mode)205 }206 if rec.Variant == "" {207 rec.Variant = rec.Mode208 }209 if rec.VariantID == "" {210 rec.VariantID = CanonicalModeID(rec.Variant)211 }212 if rec.RunKind == "" {213 rec.RunKind = RunKindNormal214 }215216 result, err := s.db.Exec(217 `INSERT INTO games218 (name, game_id, game_type, variant_id, variant, mode_id, mode, initial_state, save_state, status,219 run_kind, run_date, week_year, week_number, week_index, seed_text,220 target_difficulty_elo, actual_difficulty_elo, difficulty_confidence)221 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,222 rec.Name, rec.GameID, rec.GameType, rec.VariantID, rec.Variant, rec.ModeID, rec.Mode,223 rec.InitialState, rec.SaveState, rec.Status, string(rec.RunKind), rec.RunDate,224 nullableInt(rec.WeekYear), nullableInt(rec.WeekNumber), nullableInt(rec.WeekIndex), nullableString(rec.SeedText),225 nullableIntPtr(rec.TargetDifficultyElo), nullableIntPtr(rec.ActualDifficultyElo), nullableString(rec.DifficultyConfidence),226 )227 if err != nil {228 return fmt.Errorf("inserting game: %w", err)229 }230 id, err := result.LastInsertId()231 if err != nil {232 return fmt.Errorf("getting last insert id: %w", err)233 }234 rec.ID = id235 return nil236}237238// UpdateSaveState updates save_state and updated_at for the given game.239func (s *Store) UpdateSaveState(id int64, saveState string) error {240 _, err := s.db.Exec(241 `UPDATE games SET save_state = ?, updated_at = ? WHERE id = ?`,242 saveState, time.Now().UTC(), id,243 )244 return err245}246247// UpdateStatus updates the status (and completed_at if completed) for the given game.248func (s *Store) UpdateStatus(id int64, status GameStatus) error {249 if status == StatusCompleted {250 now := time.Now().UTC()251 _, err := s.db.Exec(252 `UPDATE games SET status = ?, completed_at = ?, updated_at = ? WHERE id = ?`,253 status, now, now, id,254 )255 return err256 }257 _, err := s.db.Exec(258 `UPDATE games SET status = ?, updated_at = ? WHERE id = ?`,259 status, time.Now().UTC(), id,260 )261 return err262}263264// NameExists checks whether a game with the given name already exists.265func (s *Store) NameExists(name string) (bool, error) {266 var count int267 err := s.db.QueryRow(`SELECT COUNT(*) FROM games WHERE name = ?`, name).Scan(&count)268 if err != nil {269 return false, err270 }271 return count > 0, nil272}273274// ListGames returns all non-abandoned games ordered by most recently updated.275func (s *Store) ListGames() ([]GameRecord, error) {276 return s.listGamesByQuery(277 fmt.Sprintf(`SELECT %s278 FROM games279 WHERE status != ?280 ORDER BY updated_at DESC`, gameSelectColumns),281 StatusAbandoned,282 )283}284285// GetDailyGame looks up a daily game by name, including abandoned ones.286// Daily puzzles should always be resumable regardless of status.287// Returns nil, nil if no matching game is found.288func (s *Store) GetDailyGame(name string) (*GameRecord, error) {289 return s.GetDeterministicGame(name)290}291292// GetDeterministicGame looks up an exact deterministic game name, including293// abandoned rows.294func (s *Store) GetDeterministicGame(name string) (*GameRecord, error) {295 g, err := s.getGameByQuery(296 fmt.Sprintf(`SELECT %s297 FROM games298 WHERE name = ?`, gameSelectColumns),299 name,300 )301 if err != nil {302 return nil, fmt.Errorf("querying deterministic game: %w", err)303 }304 return g, nil305}306307// GetWeeklyGame looks up a weekly game by canonical week and index.308func (s *Store) GetWeeklyGame(year, weekNumber, index int) (*GameRecord, error) {309 g, err := s.getGameByQuery(310 fmt.Sprintf(`SELECT %s311 FROM games312 WHERE run_kind = ?313 AND week_year = ?314 AND week_number = ?315 AND week_index = ?`, gameSelectColumns),316 string(RunKindWeekly), year, weekNumber, index,317 )318 if err != nil {319 return nil, fmt.Errorf("querying weekly game: %w", err)320 }321 return g, nil322}323324// ListWeeklyGames returns all records matching the canonical weekly prefix for325// the provided ISO week-year, including abandoned rows.326func (s *Store) ListWeeklyGames(year, weekNumber int) ([]GameRecord, error) {327 games, err := s.listGamesByQuery(328 fmt.Sprintf(`SELECT %s329 FROM games330 WHERE run_kind = ?331 AND week_year = ?332 AND week_number = ?333 ORDER BY name DESC`, gameSelectColumns),334 string(RunKindWeekly), year, weekNumber,335 )336 if err != nil {337 return nil, fmt.Errorf("listing weekly games: %w", err)338 }339 return games, nil340}341342// GetGameByName looks up a single non-abandoned game by its unique name.343// Returns nil, nil if no matching game is found.344func (s *Store) GetGameByName(name string) (*GameRecord, error) {345 g, err := s.getGameByQuery(346 fmt.Sprintf(`SELECT %s347 FROM games348 WHERE name = ? AND status != ?`, gameSelectColumns),349 name, StatusAbandoned,350 )351 if err != nil {352 return nil, fmt.Errorf("querying game by name: %w", err)353 }354 return g, nil355}356357// ListAllGames returns all games (including abandoned) ordered by most recently updated.358func (s *Store) ListAllGames() ([]GameRecord, error) {359 return s.listGamesByQuery(360 fmt.Sprintf(`SELECT %s361 FROM games362 ORDER BY updated_at DESC`, gameSelectColumns),363 )364}365366func (s *Store) Close() error {367 return s.db.Close()368}