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 mode_id TEXT NOT NULL DEFAULT '',22 mode TEXT NOT NULL,23 initial_state TEXT NOT NULL,24 save_state TEXT,25 status TEXT NOT NULL DEFAULT 'new'26 CHECK(status IN ('new','in_progress','completed','abandoned')),27 run_kind TEXT NOT NULL DEFAULT 'normal'28 CHECK(run_kind IN ('normal','daily','weekly','seeded')),29 run_date DATE,30 week_year INTEGER,31 week_number INTEGER,32 week_index INTEGER,33 seed_text TEXT,34 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,35 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,36 completed_at DATETIME37);`3839const createCategoryStatsViewSQL = `40CREATE VIEW IF NOT EXISTS category_stats AS41SELECT42 game_type,43 COUNT(*) AS total_attempts,44 COUNT(*) FILTER (WHERE status = 'completed') AS total_victories,45 COUNT(*) FILTER (WHERE status = 'abandoned') AS total_abandoned,46 COUNT(*) FILTER (WHERE run_kind = 'daily') AS times_daily,47 COUNT(*) FILTER (WHERE status = 'completed'48 AND run_kind = 'daily') AS daily_victories,49 (SELECT mode FROM games g250 WHERE g2.game_type = games.game_type51 AND g2.status = 'completed'52 GROUP BY mode53 ORDER BY COUNT(*) DESC, mode ASC54 LIMIT 1) AS preferred_mode55FROM games56GROUP BY game_type;`5758const createModeStatsViewSQL = `59CREATE VIEW IF NOT EXISTS mode_stats AS60SELECT61 game_type,62 mode,63 COUNT(*) FILTER (WHERE status = 'completed') AS victories,64 COUNT(*) FILTER (WHERE status = 'completed'65 AND run_kind = 'daily') AS daily_victories66FROM games67GROUP BY game_type, mode;`6869type Store struct {70 db *sql.DB71}7273const gameSelectColumns = `id, name, game_id, game_type, mode_id, mode, initial_state, save_state,74 status, run_kind, run_date, week_year, week_number, week_index, seed_text,75 created_at, updated_at, completed_at`7677type rowScanner interface {78 Scan(dest ...any) error79}8081func scanGameRecord(scanner rowScanner) (GameRecord, error) {82 var g GameRecord83 var runDate sql.NullTime84 var weekYear sql.NullInt6485 var weekNumber sql.NullInt6486 var weekIndex sql.NullInt6487 var seedText sql.NullString88 var completedAt sql.NullTime89 if err := scanner.Scan(90 &g.ID, &g.Name, &g.GameID, &g.GameType, &g.ModeID, &g.Mode,91 &g.InitialState, &g.SaveState, &g.Status,92 &g.RunKind, &runDate, &weekYear, &weekNumber, &weekIndex, &seedText,93 &g.CreatedAt, &g.UpdatedAt, &completedAt,94 ); err != nil {95 return GameRecord{}, err96 }97 if runDate.Valid {98 t := runDate.Time99 g.RunDate = &t100 }101 if weekYear.Valid {102 g.WeekYear = int(weekYear.Int64)103 }104 if weekNumber.Valid {105 g.WeekNumber = int(weekNumber.Int64)106 }107 if weekIndex.Valid {108 g.WeekIndex = int(weekIndex.Int64)109 }110 if seedText.Valid {111 g.SeedText = seedText.String112 }113 if completedAt.Valid {114 g.CompletedAt = &completedAt.Time115 }116 return g, nil117}118119func (s *Store) listGamesByQuery(query string, args ...any) ([]GameRecord, error) {120 rows, err := s.db.Query(query, args...)121 if err != nil {122 return nil, fmt.Errorf("querying games: %w", err)123 }124 defer rows.Close()125126 var games []GameRecord127 for rows.Next() {128 g, err := scanGameRecord(rows)129 if err != nil {130 return nil, fmt.Errorf("scanning game row: %w", err)131 }132 games = append(games, g)133 }134 return games, rows.Err()135}136137func (s *Store) getGameByQuery(query string, args ...any) (*GameRecord, error) {138 g, err := scanGameRecord(s.db.QueryRow(query, args...))139 if errors.Is(err, sql.ErrNoRows) {140 return nil, nil141 }142 if err != nil {143 return nil, err144 }145 return &g, nil146}147148// Open creates the directory if needed, opens the database, creates the table,149// and enables WAL mode.150func Open(dbPath string) (*Store, error) {151 dir := filepath.Dir(dbPath)152 if err := os.MkdirAll(dir, 0o755); err != nil {153 return nil, fmt.Errorf("creating db directory: %w", err)154 }155156 db, err := sql.Open("sqlite", dbPath)157 if err != nil {158 return nil, fmt.Errorf("opening database: %w", err)159 }160161 if _, err := db.Exec("PRAGMA journal_mode=WAL"); err != nil {162 db.Close()163 return nil, fmt.Errorf("enabling WAL mode: %w", err)164 }165166 if err := runMigrations(db); err != nil {167 db.Close()168 return nil, err169 }170171 return &Store{db: db}, nil172}173174// CreateGame inserts a new game record and sets rec.ID.175func (s *Store) CreateGame(rec *GameRecord) error {176 if rec.GameID == "" {177 rec.GameID = CanonicalGameID(rec.GameType)178 }179 if rec.ModeID == "" {180 rec.ModeID = CanonicalModeID(rec.Mode)181 }182 if rec.RunKind == "" {183 rec.RunKind = RunKindNormal184 }185186 result, err := s.db.Exec(187 `INSERT INTO games188 (name, game_id, game_type, mode_id, mode, initial_state, save_state, status,189 run_kind, run_date, week_year, week_number, week_index, seed_text)190 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,191 rec.Name, rec.GameID, rec.GameType, rec.ModeID, rec.Mode,192 rec.InitialState, rec.SaveState, rec.Status, string(rec.RunKind), rec.RunDate,193 nullableInt(rec.WeekYear), nullableInt(rec.WeekNumber), nullableInt(rec.WeekIndex), nullableString(rec.SeedText),194 )195 if err != nil {196 return fmt.Errorf("inserting game: %w", err)197 }198 id, err := result.LastInsertId()199 if err != nil {200 return fmt.Errorf("getting last insert id: %w", err)201 }202 rec.ID = id203 return nil204}205206// UpdateSaveState updates save_state and updated_at for the given game.207func (s *Store) UpdateSaveState(id int64, saveState string) error {208 _, err := s.db.Exec(209 `UPDATE games SET save_state = ?, updated_at = ? WHERE id = ?`,210 saveState, time.Now().UTC(), id,211 )212 return err213}214215// UpdateStatus updates the status (and completed_at if completed) for the given game.216func (s *Store) UpdateStatus(id int64, status GameStatus) error {217 if status == StatusCompleted {218 now := time.Now().UTC()219 _, err := s.db.Exec(220 `UPDATE games SET status = ?, completed_at = ?, updated_at = ? WHERE id = ?`,221 status, now, now, id,222 )223 return err224 }225 _, err := s.db.Exec(226 `UPDATE games SET status = ?, updated_at = ? WHERE id = ?`,227 status, time.Now().UTC(), id,228 )229 return err230}231232// NameExists checks whether a game with the given name already exists.233func (s *Store) NameExists(name string) (bool, error) {234 var count int235 err := s.db.QueryRow(`SELECT COUNT(*) FROM games WHERE name = ?`, name).Scan(&count)236 if err != nil {237 return false, err238 }239 return count > 0, nil240}241242// ListGames returns all non-abandoned games ordered by most recently updated.243func (s *Store) ListGames() ([]GameRecord, error) {244 return s.listGamesByQuery(245 fmt.Sprintf(`SELECT %s246 FROM games247 WHERE status != ?248 ORDER BY updated_at DESC`, gameSelectColumns),249 StatusAbandoned,250 )251}252253// GetDailyGame looks up a daily game by name, including abandoned ones.254// Daily puzzles should always be resumable regardless of status.255// Returns nil, nil if no matching game is found.256func (s *Store) GetDailyGame(name string) (*GameRecord, error) {257 return s.GetDeterministicGame(name)258}259260// GetDeterministicGame looks up an exact deterministic game name, including261// abandoned rows.262func (s *Store) GetDeterministicGame(name string) (*GameRecord, error) {263 g, err := s.getGameByQuery(264 fmt.Sprintf(`SELECT %s265 FROM games266 WHERE name = ?`, gameSelectColumns),267 name,268 )269 if err != nil {270 return nil, fmt.Errorf("querying deterministic game: %w", err)271 }272 return g, nil273}274275// GetWeeklyGame looks up a weekly game by canonical week and index.276func (s *Store) GetWeeklyGame(year, weekNumber, index int) (*GameRecord, error) {277 g, err := s.getGameByQuery(278 fmt.Sprintf(`SELECT %s279 FROM games280 WHERE run_kind = ?281 AND week_year = ?282 AND week_number = ?283 AND week_index = ?`, gameSelectColumns),284 string(RunKindWeekly), year, weekNumber, index,285 )286 if err != nil {287 return nil, fmt.Errorf("querying weekly game: %w", err)288 }289 return g, nil290}291292// ListWeeklyGames returns all records matching the canonical weekly prefix for293// the provided ISO week-year, including abandoned rows.294func (s *Store) ListWeeklyGames(year, weekNumber int) ([]GameRecord, error) {295 games, err := s.listGamesByQuery(296 fmt.Sprintf(`SELECT %s297 FROM games298 WHERE run_kind = ?299 AND week_year = ?300 AND week_number = ?301 ORDER BY name DESC`, gameSelectColumns),302 string(RunKindWeekly), year, weekNumber,303 )304 if err != nil {305 return nil, fmt.Errorf("listing weekly games: %w", err)306 }307 return games, nil308}309310// GetGameByName looks up a single non-abandoned game by its unique name.311// Returns nil, nil if no matching game is found.312func (s *Store) GetGameByName(name string) (*GameRecord, error) {313 g, err := s.getGameByQuery(314 fmt.Sprintf(`SELECT %s315 FROM games316 WHERE name = ? AND status != ?`, gameSelectColumns),317 name, StatusAbandoned,318 )319 if err != nil {320 return nil, fmt.Errorf("querying game by name: %w", err)321 }322 return g, nil323}324325// ListAllGames returns all games (including abandoned) ordered by most recently updated.326func (s *Store) ListAllGames() ([]GameRecord, error) {327 return s.listGamesByQuery(328 fmt.Sprintf(`SELECT %s329 FROM games330 ORDER BY updated_at DESC`, gameSelectColumns),331 )332}333334func (s *Store) Close() error {335 return s.db.Close()336}