1package store23import (4 "database/sql"5 "fmt"6 "time"7)89// CategoryStat holds aggregate statistics for a single game category.10type CategoryStat struct {11 GameType string12 TotalAttempts int13 TotalVictories int14 TotalAbandoned int15 TimesDaily int16 DailyVictories int17 PreferredMode string18}1920// ModeStat holds per-mode victory counts for XP computation.21type ModeStat struct {22 GameType string23 Mode string24 Victories int25 DailyVictories int26 WeeklyBonusXP int27}2829// GetCategoryStats returns aggregate stats per game category from the30// category_stats view.31func (s *Store) GetCategoryStats() ([]CategoryStat, error) {32 rows, err := s.db.Query(33 `SELECT game_type, total_attempts, total_victories, total_abandoned,34 times_daily, daily_victories, preferred_mode35 FROM category_stats`,36 )37 if err != nil {38 return nil, fmt.Errorf("querying category_stats: %w", err)39 }40 defer rows.Close()4142 var stats []CategoryStat43 for rows.Next() {44 var cs CategoryStat45 var preferredMode sql.NullString46 if err := rows.Scan(47 &cs.GameType, &cs.TotalAttempts, &cs.TotalVictories,48 &cs.TotalAbandoned, &cs.TimesDaily, &cs.DailyVictories,49 &preferredMode,50 ); err != nil {51 return nil, fmt.Errorf("scanning category_stats row: %w", err)52 }53 if preferredMode.Valid {54 cs.PreferredMode = preferredMode.String55 }56 stats = append(stats, cs)57 }58 return stats, rows.Err()59}6061// GetModeStats returns per-mode victory counts from the mode_stats view.62func (s *Store) GetModeStats() ([]ModeStat, error) {63 rows, err := s.db.Query(64 `SELECT game_type, mode, victories, daily_victories65 FROM mode_stats`,66 )67 if err != nil {68 return nil, fmt.Errorf("querying mode_stats: %w", err)69 }70 defer rows.Close()7172 var stats []ModeStat73 for rows.Next() {74 var ms ModeStat75 if err := rows.Scan(76 &ms.GameType, &ms.Mode, &ms.Victories, &ms.DailyVictories,77 ); err != nil {78 return nil, fmt.Errorf("scanning mode_stats row: %w", err)79 }80 stats = append(stats, ms)81 }82 if err := rows.Err(); err != nil {83 return nil, err84 }8586 stats, err = s.applyWeeklyBonusXP(stats)87 if err != nil {88 return nil, err89 }90 return stats, nil91}9293// GetDailyStreakDates returns distinct completion dates for daily games,94// ordered most recent first. Dates are in local time with time set to midnight.95func (s *Store) GetDailyStreakDates() ([]time.Time, error) {96 rows, err := s.db.Query(97 `SELECT DISTINCT run_date98 FROM games99 WHERE run_kind = ?100 AND status = 'completed'101 AND run_date IS NOT NULL102 ORDER BY run_date DESC`,103 string(RunKindDaily),104 )105 if err != nil {106 return nil, fmt.Errorf("querying daily streak dates: %w", err)107 }108 defer rows.Close()109110 var dates []time.Time111 for rows.Next() {112 var dateValue sql.NullTime113 if err := rows.Scan(&dateValue); err != nil {114 return nil, fmt.Errorf("scanning daily streak date: %w", err)115 }116 if !dateValue.Valid {117 continue118 }119 t := dateValue.Time.In(time.Local)120 dates = append(dates, time.Date(t.Year(), t.Month(), t.Day(), 0, 0, 0, 0, time.Local))121 }122 return dates, rows.Err()123}124125// GetCompletedWeeklyGauntlets returns the number of ISO weeks whose highest126// completed weekly index is 99.127func (s *Store) GetCompletedWeeklyGauntlets() (int, error) {128 rows, err := s.db.Query(129 `SELECT week_year, week_number, MAX(week_index)130 FROM games131 WHERE status = 'completed'132 AND run_kind = ?133 AND week_year IS NOT NULL134 AND week_number IS NOT NULL135 GROUP BY week_year, week_number`,136 string(RunKindWeekly),137 )138 if err != nil {139 return 0, fmt.Errorf("querying completed weekly gauntlets: %w", err)140 }141 defer rows.Close()142 completed := 0143 for rows.Next() {144 var year, week, highest int145 if err := rows.Scan(&year, &week, &highest); err != nil {146 return 0, fmt.Errorf("scanning weekly gauntlet row: %w", err)147 }148 if highest >= 99 {149 completed++150 }151 }152 return completed, rows.Err()153}154155// GetCurrentWeeklyHighestCompletedIndex returns the highest completed weekly156// index for the provided ISO week-year.157func (s *Store) GetCurrentWeeklyHighestCompletedIndex(year, week int) (int, error) {158 var highest sql.NullInt64159 err := s.db.QueryRow(160 `SELECT MAX(week_index)161 FROM games162 WHERE status = 'completed'163 AND run_kind = ?164 AND week_year = ?165 AND week_number = ?`,166 string(RunKindWeekly), year, week,167 ).Scan(&highest)168 if err != nil {169 return 0, fmt.Errorf("querying current weekly highest index: %w", err)170 }171 if !highest.Valid {172 return 0, nil173 }174 return int(highest.Int64), nil175}176177func (s *Store) applyWeeklyBonusXP(stats []ModeStat) ([]ModeStat, error) {178 rows, err := s.db.Query(179 `SELECT week_index, game_type, mode180 FROM games181 WHERE status = 'completed'182 AND run_kind = ?`,183 string(RunKindWeekly),184 )185 if err != nil {186 return nil, fmt.Errorf("querying completed weekly games: %w", err)187 }188 defer rows.Close()189190 type key struct {191 gameType string192 mode string193 }194195 indexByKey := make(map[key]int, len(stats))196 for i := range stats {197 indexByKey[key{gameType: stats[i].GameType, mode: stats[i].Mode}] = i198 }199200 for rows.Next() {201 var weekIndex int202 var gameType string203 var mode string204 if err := rows.Scan(&weekIndex, &gameType, &mode); err != nil {205 return nil, fmt.Errorf("scanning completed weekly game: %w", err)206 }207208 k := key{gameType: gameType, mode: mode}209 i, ok := indexByKey[k]210 if !ok {211 stats = append(stats, ModeStat{212 GameType: gameType,213 Mode: mode,214 Victories: 1,215 })216 i = len(stats) - 1217 indexByKey[k] = i218 }219 if weekIndex >= 10 {220 stats[i].WeeklyBonusXP += weekIndex / 10221 }222 }223224 if err := rows.Err(); err != nil {225 return nil, fmt.Errorf("reading completed weekly games: %w", err)226 }227 return stats, nil228}