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 DifficultyElo *int27 WeeklyBonusXP int28}2930// GetCategoryStats returns aggregate stats per game category from the31// category_stats view.32func (s *Store) GetCategoryStats() ([]CategoryStat, error) {33 rows, err := s.db.Query(34 `SELECT game_type, total_attempts, total_victories, total_abandoned,35 times_daily, daily_victories, preferred_mode36 FROM category_stats`,37 )38 if err != nil {39 return nil, fmt.Errorf("querying category_stats: %w", err)40 }41 defer rows.Close()4243 var stats []CategoryStat44 for rows.Next() {45 var cs CategoryStat46 var preferredMode sql.NullString47 if err := rows.Scan(48 &cs.GameType, &cs.TotalAttempts, &cs.TotalVictories,49 &cs.TotalAbandoned, &cs.TimesDaily, &cs.DailyVictories,50 &preferredMode,51 ); err != nil {52 return nil, fmt.Errorf("scanning category_stats row: %w", err)53 }54 if preferredMode.Valid {55 cs.PreferredMode = preferredMode.String56 }57 stats = append(stats, cs)58 }59 return stats, rows.Err()60}6162// GetModeStats returns per-mode victory counts from the mode_stats view.63func (s *Store) GetModeStats() ([]ModeStat, error) {64 rows, err := s.db.Query(65 `SELECT game_type, mode, victories, daily_victories, difficulty_elo66 FROM mode_stats`,67 )68 if err != nil {69 return nil, fmt.Errorf("querying mode_stats: %w", err)70 }71 defer rows.Close()7273 var stats []ModeStat74 for rows.Next() {75 var ms ModeStat76 var difficultyElo sql.NullInt6477 if err := rows.Scan(78 &ms.GameType, &ms.Mode, &ms.Victories, &ms.DailyVictories,79 &difficultyElo,80 ); err != nil {81 return nil, fmt.Errorf("scanning mode_stats row: %w", err)82 }83 if difficultyElo.Valid {84 v := int(difficultyElo.Int64)85 ms.DifficultyElo = &v86 }87 stats = append(stats, ms)88 }89 if err := rows.Err(); err != nil {90 return nil, err91 }9293 stats, err = s.applyWeeklyBonusXP(stats)94 if err != nil {95 return nil, err96 }97 return stats, nil98}99100// GetDailyStreakDates returns distinct completion dates for daily games,101// ordered most recent first. Dates are in local time with time set to midnight.102func (s *Store) GetDailyStreakDates() ([]time.Time, error) {103 rows, err := s.db.Query(104 `SELECT DISTINCT run_date105 FROM games106 WHERE run_kind = ?107 AND status = 'completed'108 AND run_date IS NOT NULL109 ORDER BY run_date DESC`,110 string(RunKindDaily),111 )112 if err != nil {113 return nil, fmt.Errorf("querying daily streak dates: %w", err)114 }115 defer rows.Close()116117 var dates []time.Time118 for rows.Next() {119 var dateValue sql.NullTime120 if err := rows.Scan(&dateValue); err != nil {121 return nil, fmt.Errorf("scanning daily streak date: %w", err)122 }123 if !dateValue.Valid {124 continue125 }126 t := dateValue.Time.In(time.Local)127 dates = append(dates, time.Date(t.Year(), t.Month(), t.Day(), 0, 0, 0, 0, time.Local))128 }129 return dates, rows.Err()130}131132// GetCompletedWeeklyGauntlets returns the number of ISO weeks whose highest133// completed weekly index is 99.134func (s *Store) GetCompletedWeeklyGauntlets() (int, error) {135 rows, err := s.db.Query(136 `SELECT week_year, week_number, MAX(week_index)137 FROM games138 WHERE status = 'completed'139 AND run_kind = ?140 AND week_year IS NOT NULL141 AND week_number IS NOT NULL142 GROUP BY week_year, week_number`,143 string(RunKindWeekly),144 )145 if err != nil {146 return 0, fmt.Errorf("querying completed weekly gauntlets: %w", err)147 }148 defer rows.Close()149 completed := 0150 for rows.Next() {151 var year, week, highest int152 if err := rows.Scan(&year, &week, &highest); err != nil {153 return 0, fmt.Errorf("scanning weekly gauntlet row: %w", err)154 }155 if highest >= 99 {156 completed++157 }158 }159 return completed, rows.Err()160}161162// GetCurrentWeeklyHighestCompletedIndex returns the highest completed weekly163// index for the provided ISO week-year.164func (s *Store) GetCurrentWeeklyHighestCompletedIndex(year, week int) (int, error) {165 var highest sql.NullInt64166 err := s.db.QueryRow(167 `SELECT MAX(week_index)168 FROM games169 WHERE status = 'completed'170 AND run_kind = ?171 AND week_year = ?172 AND week_number = ?`,173 string(RunKindWeekly), year, week,174 ).Scan(&highest)175 if err != nil {176 return 0, fmt.Errorf("querying current weekly highest index: %w", err)177 }178 if !highest.Valid {179 return 0, nil180 }181 return int(highest.Int64), nil182}183184func (s *Store) applyWeeklyBonusXP(stats []ModeStat) ([]ModeStat, error) {185 rows, err := s.db.Query(186 `SELECT week_index, game_type, mode187 FROM games188 WHERE status = 'completed'189 AND run_kind = ?`,190 string(RunKindWeekly),191 )192 if err != nil {193 return nil, fmt.Errorf("querying completed weekly games: %w", err)194 }195 defer rows.Close()196197 type key struct {198 gameType string199 mode string200 }201202 indexByKey := make(map[key]int, len(stats))203 for i := range stats {204 indexByKey[key{gameType: stats[i].GameType, mode: stats[i].Mode}] = i205 }206207 for rows.Next() {208 var weekIndex int209 var gameType string210 var mode string211 if err := rows.Scan(&weekIndex, &gameType, &mode); err != nil {212 return nil, fmt.Errorf("scanning completed weekly game: %w", err)213 }214215 k := key{gameType: gameType, mode: mode}216 i, ok := indexByKey[k]217 if !ok {218 stats = append(stats, ModeStat{219 GameType: gameType,220 Mode: mode,221 Victories: 1,222 })223 i = len(stats) - 1224 indexByKey[k] = i225 }226 if weekIndex >= 10 {227 stats[i].WeeklyBonusXP += weekIndex / 10228 }229 }230231 if err := rows.Err(); err != nil {232 return nil, fmt.Errorf("reading completed weekly games: %w", err)233 }234 return stats, nil235}