1package store23import (4 "database/sql"5 "path/filepath"6 "testing"7 "time"89 "github.com/FelineStateMachine/puzzletea/weekly"10)1112func openTestStore(t *testing.T) *Store {13 t.Helper()14 s, err := Open(filepath.Join(t.TempDir(), "test.db"))15 if err != nil {16 t.Fatal(err)17 }18 t.Cleanup(func() { s.Close() })19 return s20}2122func newTestRecord(name string) *GameRecord {23 return &GameRecord{24 Name: name,25 GameType: "Sudoku",26 Mode: "Easy",27 InitialState: `{"grid":"..."}`,28 SaveState: `{"grid":"..."}`,29 Status: StatusNew,30 RunKind: RunKindNormal,31 }32}3334func newDailyTestRecord(name string, date time.Time) *GameRecord {35 rec := newTestRecord(name)36 day := dayOnly(date)37 rec.RunKind = RunKindDaily38 rec.RunDate = &day39 return rec40}4142func newWeeklyTestRecord(name string, year, weekNumber, index int) *GameRecord {43 rec := newTestRecord(name)44 rec.RunKind = RunKindWeekly45 rec.WeekYear = year46 rec.WeekNumber = weekNumber47 rec.WeekIndex = index48 return rec49}5051func TestOpen(t *testing.T) {52 t.Run("creates directory and database", func(t *testing.T) {53 dir := t.TempDir()54 dbPath := filepath.Join(dir, "sub", "nested", "test.db")55 s, err := Open(dbPath)56 if err != nil {57 t.Fatal(err)58 }59 defer s.Close()60 })6162 t.Run("opens existing database", func(t *testing.T) {63 dir := t.TempDir()64 dbPath := filepath.Join(dir, "test.db")6566 s1, err := Open(dbPath)67 if err != nil {68 t.Fatal(err)69 }70 rec := newTestRecord("alpha")71 if err := s1.CreateGame(rec); err != nil {72 t.Fatal(err)73 }74 s1.Close()7576 s2, err := Open(dbPath)77 if err != nil {78 t.Fatal(err)79 }80 defer s2.Close()8182 got, err := s2.GetGameByName("alpha")83 if err != nil {84 t.Fatal(err)85 }86 if got == nil {87 t.Fatal("expected record to persist across reopen")88 }89 })9091 t.Run("records current schema version", func(t *testing.T) {92 s := openTestStore(t)9394 version, err := schemaVersion(s.db)95 if err != nil {96 t.Fatal(err)97 }98 if got, want := version, currentSchemaVersion; got != want {99 t.Fatalf("schema version = %d, want %d", got, want)100 }101 })102103 t.Run("upgrades databases missing run metadata and backfills it", func(t *testing.T) {104 dbPath := filepath.Join(t.TempDir(), "legacy.db")105106 raw, err := sql.Open("sqlite", dbPath)107 if err != nil {108 t.Fatal(err)109 }110 t.Cleanup(func() { raw.Close() })111112 _, err = raw.Exec(`113CREATE TABLE games (114 id INTEGER PRIMARY KEY AUTOINCREMENT,115 name TEXT NOT NULL UNIQUE,116 game_type TEXT NOT NULL,117 mode TEXT NOT NULL,118 initial_state TEXT NOT NULL,119 save_state TEXT,120 status TEXT NOT NULL DEFAULT 'new'121 CHECK(status IN ('new','in_progress','completed','abandoned')),122 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,123 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,124 completed_at DATETIME125);`)126 if err != nil {127 t.Fatal(err)128 }129130 _, err = raw.Exec(`131INSERT INTO games (name, game_type, mode, initial_state, save_state, status)132VALUES (?, ?, ?, ?, ?, ?)`,133 "Daily Feb 16 26 - amber-fox",134 "Sudoku",135 "Easy",136 `{"grid":"initial"}`,137 `{"grid":"save"}`,138 string(StatusNew),139 )140 if err != nil {141 t.Fatal(err)142 }143 if err := raw.Close(); err != nil {144 t.Fatal(err)145 }146147 s, err := Open(dbPath)148 if err != nil {149 t.Fatal(err)150 }151 defer s.Close()152153 version, err := schemaVersion(s.db)154 if err != nil {155 t.Fatal(err)156 }157 if got, want := version, currentSchemaVersion; got != want {158 t.Fatalf("schema version = %d, want %d", got, want)159 }160161 rec, err := s.GetGameByName("Daily Feb 16 26 - amber-fox")162 if err != nil {163 t.Fatal(err)164 }165 if rec == nil {166 t.Fatal("expected migrated record")167 }168 if got, want := rec.GameID, "sudoku"; got != want {169 t.Fatalf("GameID = %q, want %q", got, want)170 }171 if got, want := rec.ModeID, "easy"; got != want {172 t.Fatalf("ModeID = %q, want %q", got, want)173 }174 if got, want := rec.VariantID, "easy"; got != want {175 t.Fatalf("VariantID = %q, want %q", got, want)176 }177 if got, want := rec.Variant, "Easy"; got != want {178 t.Fatalf("Variant = %q, want %q", got, want)179 }180 if got, want := rec.RunKind, RunKindDaily; got != want {181 t.Fatalf("RunKind = %q, want %q", got, want)182 }183 if rec.RunDate == nil {184 t.Fatal("expected RunDate to be backfilled")185 }186187 if _, err := s.GetCategoryStats(); err != nil {188 t.Fatalf("GetCategoryStats() error after migration: %v", err)189 }190 })191}192193func TestCreateGame(t *testing.T) {194 t.Run("sets ID", func(t *testing.T) {195 s := openTestStore(t)196 rec := newTestRecord("alpha")197 if err := s.CreateGame(rec); err != nil {198 t.Fatal(err)199 }200 if rec.ID <= 0 {201 t.Fatalf("expected positive ID, got %d", rec.ID)202 }203 })204205 t.Run("sequential IDs", func(t *testing.T) {206 s := openTestStore(t)207 r1 := newTestRecord("alpha")208 r2 := newTestRecord("beta")209 if err := s.CreateGame(r1); err != nil {210 t.Fatal(err)211 }212 if err := s.CreateGame(r2); err != nil {213 t.Fatal(err)214 }215 if r2.ID <= r1.ID {216 t.Fatalf("expected r2.ID (%d) > r1.ID (%d)", r2.ID, r1.ID)217 }218 })219220 t.Run("preserves fields", func(t *testing.T) {221 s := openTestStore(t)222 rec := &GameRecord{223 Name: "gamma",224 GameType: "Nonogram",225 Mode: "Hard 10x10",226 InitialState: `{"init":true}`,227 SaveState: `{"save":true}`,228 Status: StatusNew,229 RunKind: RunKindNormal,230 }231 targetElo := 1200232 actualElo := 1185233 rec.TargetDifficultyElo = &targetElo234 rec.ActualDifficultyElo = &actualElo235 rec.DifficultyConfidence = "high"236 if err := s.CreateGame(rec); err != nil {237 t.Fatal(err)238 }239240 got, err := s.GetGameByName("gamma")241 if err != nil {242 t.Fatal(err)243 }244 if got == nil {245 t.Fatal("expected record")246 }247 if got.Name != "gamma" {248 t.Errorf("Name = %q, want %q", got.Name, "gamma")249 }250 if got.GameType != "Nonogram" {251 t.Errorf("GameType = %q, want %q", got.GameType, "Nonogram")252 }253 if got.Mode != "Hard 10x10" {254 t.Errorf("Mode = %q, want %q", got.Mode, "Hard 10x10")255 }256 if got.Variant != "Hard 10x10" {257 t.Errorf("Variant = %q, want %q", got.Variant, "Hard 10x10")258 }259 if got.VariantID != "hard 10x10" {260 t.Errorf("VariantID = %q, want %q", got.VariantID, "hard 10x10")261 }262 if got.InitialState != `{"init":true}` {263 t.Errorf("InitialState = %q, want %q", got.InitialState, `{"init":true}`)264 }265 if got.SaveState != `{"save":true}` {266 t.Errorf("SaveState = %q, want %q", got.SaveState, `{"save":true}`)267 }268 if got.TargetDifficultyElo == nil || *got.TargetDifficultyElo != targetElo {269 t.Fatalf("TargetDifficultyElo = %v, want %d", got.TargetDifficultyElo, targetElo)270 }271 if got.ActualDifficultyElo == nil || *got.ActualDifficultyElo != actualElo {272 t.Fatalf("ActualDifficultyElo = %v, want %d", got.ActualDifficultyElo, actualElo)273 }274 if got.DifficultyConfidence != "high" {275 t.Fatalf("DifficultyConfidence = %q, want high", got.DifficultyConfidence)276 }277 if got.Status != StatusNew {278 t.Errorf("Status = %q, want %q", got.Status, StatusNew)279 }280 })281282 t.Run("rejects duplicate name", func(t *testing.T) {283 s := openTestStore(t)284 r1 := newTestRecord("dup")285 if err := s.CreateGame(r1); err != nil {286 t.Fatal(err)287 }288 r2 := newTestRecord("dup")289 if err := s.CreateGame(r2); err == nil {290 t.Fatal("expected error for duplicate name")291 }292 })293294 t.Run("does not infer deterministic metadata from name", func(t *testing.T) {295 s := openTestStore(t)296 rec := newTestRecord("Daily Feb 16 26 - amber-fox")297 if err := s.CreateGame(rec); err != nil {298 t.Fatal(err)299 }300301 got, err := s.GetGameByName(rec.Name)302 if err != nil {303 t.Fatal(err)304 }305 if got == nil {306 t.Fatal("expected record")307 }308 if got.RunKind != RunKindNormal {309 t.Fatalf("RunKind = %q, want %q", got.RunKind, RunKindNormal)310 }311 if got.RunDate != nil {312 t.Fatalf("RunDate = %v, want nil", got.RunDate)313 }314 if got.SeedText != "" {315 t.Fatalf("SeedText = %q, want empty", got.SeedText)316 }317 if got.WeekYear != 0 || got.WeekNumber != 0 || got.WeekIndex != 0 {318 t.Fatalf("weekly metadata = (%d, %d, %d), want zero values", got.WeekYear, got.WeekNumber, got.WeekIndex)319 }320 })321}322323func TestUpdateSaveState(t *testing.T) {324 t.Run("updates save state", func(t *testing.T) {325 s := openTestStore(t)326 rec := newTestRecord("alpha")327 if err := s.CreateGame(rec); err != nil {328 t.Fatal(err)329 }330331 if err := s.UpdateSaveState(rec.ID, `{"new":"state"}`); err != nil {332 t.Fatal(err)333 }334335 got, err := s.GetGameByName("alpha")336 if err != nil {337 t.Fatal(err)338 }339 if got.SaveState != `{"new":"state"}` {340 t.Errorf("SaveState = %q, want %q", got.SaveState, `{"new":"state"}`)341 }342 })343344 t.Run("advances updated_at", func(t *testing.T) {345 s := openTestStore(t)346 rec := newTestRecord("alpha")347 if err := s.CreateGame(rec); err != nil {348 t.Fatal(err)349 }350351 before, _ := s.GetGameByName("alpha")352 time.Sleep(10 * time.Millisecond)353354 if err := s.UpdateSaveState(rec.ID, `{"v2":true}`); err != nil {355 t.Fatal(err)356 }357358 after, _ := s.GetGameByName("alpha")359 if !after.UpdatedAt.After(before.UpdatedAt) {360 t.Errorf("UpdatedAt not advanced: before=%v, after=%v", before.UpdatedAt, after.UpdatedAt)361 }362 })363}364365func TestUpdateStatus(t *testing.T) {366 t.Run("new to in_progress", func(t *testing.T) {367 s := openTestStore(t)368 rec := newTestRecord("alpha")369 if err := s.CreateGame(rec); err != nil {370 t.Fatal(err)371 }372 if err := s.UpdateStatus(rec.ID, StatusInProgress); err != nil {373 t.Fatal(err)374 }375376 got, _ := s.GetGameByName("alpha")377 if got.Status != StatusInProgress {378 t.Errorf("Status = %q, want %q", got.Status, StatusInProgress)379 }380 if got.CompletedAt != nil {381 t.Error("CompletedAt should be nil for in_progress")382 }383 })384385 t.Run("in_progress to completed sets completed_at", func(t *testing.T) {386 s := openTestStore(t)387 rec := newTestRecord("alpha")388 if err := s.CreateGame(rec); err != nil {389 t.Fatal(err)390 }391 if err := s.UpdateStatus(rec.ID, StatusCompleted); err != nil {392 t.Fatal(err)393 }394395 got, _ := s.GetGameByName("alpha")396 if got.Status != StatusCompleted {397 t.Errorf("Status = %q, want %q", got.Status, StatusCompleted)398 }399 if got.CompletedAt == nil {400 t.Fatal("CompletedAt should be set for completed")401 }402 if time.Since(*got.CompletedAt) > 5*time.Second {403 t.Errorf("CompletedAt too old: %v", *got.CompletedAt)404 }405 })406407 t.Run("in_progress to abandoned", func(t *testing.T) {408 s := openTestStore(t)409 rec := newTestRecord("alpha")410 if err := s.CreateGame(rec); err != nil {411 t.Fatal(err)412 }413 if err := s.UpdateStatus(rec.ID, StatusAbandoned); err != nil {414 t.Fatal(err)415 }416417 // GetGameByName excludes abandoned, so query directly.418 all, _ := s.ListAllGames()419 if len(all) != 1 {420 t.Fatalf("expected 1 game, got %d", len(all))421 }422 if all[0].Status != StatusAbandoned {423 t.Errorf("Status = %q, want %q", all[0].Status, StatusAbandoned)424 }425 if all[0].CompletedAt != nil {426 t.Error("CompletedAt should be nil for abandoned")427 }428 })429}430431func TestNameExists(t *testing.T) {432 t.Run("returns true for existing name", func(t *testing.T) {433 s := openTestStore(t)434 rec := newTestRecord("alpha")435 if err := s.CreateGame(rec); err != nil {436 t.Fatal(err)437 }438439 exists, err := s.NameExists("alpha")440 if err != nil {441 t.Fatal(err)442 }443 if !exists {444 t.Error("expected true for existing name")445 }446 })447448 t.Run("returns false for missing name", func(t *testing.T) {449 s := openTestStore(t)450451 exists, err := s.NameExists("nope")452 if err != nil {453 t.Fatal(err)454 }455 if exists {456 t.Error("expected false for missing name")457 }458 })459460 t.Run("includes abandoned games", func(t *testing.T) {461 s := openTestStore(t)462 rec := newTestRecord("alpha")463 if err := s.CreateGame(rec); err != nil {464 t.Fatal(err)465 }466 if err := s.UpdateStatus(rec.ID, StatusAbandoned); err != nil {467 t.Fatal(err)468 }469470 exists, err := s.NameExists("alpha")471 if err != nil {472 t.Fatal(err)473 }474 if !exists {475 t.Error("NameExists should include abandoned games")476 }477 })478479 t.Run("empty database", func(t *testing.T) {480 s := openTestStore(t)481482 exists, err := s.NameExists("anything")483 if err != nil {484 t.Fatal(err)485 }486 if exists {487 t.Error("expected false for empty database")488 }489 })490}491492func TestListGames(t *testing.T) {493 t.Run("excludes abandoned", func(t *testing.T) {494 s := openTestStore(t)495 for _, name := range []string{"a", "b", "c"} {496 rec := newTestRecord(name)497 if err := s.CreateGame(rec); err != nil {498 t.Fatal(err)499 }500 if name == "b" {501 if err := s.UpdateStatus(rec.ID, StatusAbandoned); err != nil {502 t.Fatal(err)503 }504 }505 }506507 games, err := s.ListGames()508 if err != nil {509 t.Fatal(err)510 }511 if len(games) != 2 {512 t.Fatalf("expected 2 games, got %d", len(games))513 }514 for _, g := range games {515 if g.Status == StatusAbandoned {516 t.Error("ListGames should not include abandoned games")517 }518 }519 })520521 t.Run("ordered by updated_at DESC", func(t *testing.T) {522 s := openTestStore(t)523 for _, name := range []string{"first", "second", "third"} {524 rec := newTestRecord(name)525 if err := s.CreateGame(rec); err != nil {526 t.Fatal(err)527 }528 time.Sleep(20 * time.Millisecond)529 }530 // Update "first" to make it most recent.531 first, _ := s.GetGameByName("first")532 time.Sleep(20 * time.Millisecond)533 if err := s.UpdateSaveState(first.ID, `{"updated":true}`); err != nil {534 t.Fatal(err)535 }536537 games, err := s.ListGames()538 if err != nil {539 t.Fatal(err)540 }541 if len(games) < 3 {542 t.Fatalf("expected 3 games, got %d", len(games))543 }544 if games[0].Name != "first" {545 t.Errorf("expected first game to be 'first', got %q", games[0].Name)546 }547 })548549 t.Run("empty database returns empty slice", func(t *testing.T) {550 s := openTestStore(t)551552 games, err := s.ListGames()553 if err != nil {554 t.Fatal(err)555 }556 if len(games) != 0 {557 t.Errorf("expected empty slice, got %d games", len(games))558 }559 })560561 t.Run("includes all non-abandoned statuses", func(t *testing.T) {562 s := openTestStore(t)563 for _, name := range []string{"new-game", "progress-game", "done-game"} {564 rec := newTestRecord(name)565 if err := s.CreateGame(rec); err != nil {566 t.Fatal(err)567 }568 }569 pg, _ := s.GetGameByName("progress-game")570 if err := s.UpdateStatus(pg.ID, StatusInProgress); err != nil {571 t.Fatal(err)572 }573 dg, _ := s.GetGameByName("done-game")574 if err := s.UpdateStatus(dg.ID, StatusCompleted); err != nil {575 t.Fatal(err)576 }577578 games, err := s.ListGames()579 if err != nil {580 t.Fatal(err)581 }582 if len(games) != 3 {583 t.Fatalf("expected 3 games, got %d", len(games))584 }585 })586}587588func TestListAllGames(t *testing.T) {589 t.Run("includes abandoned", func(t *testing.T) {590 s := openTestStore(t)591 for _, name := range []string{"a", "b", "c"} {592 rec := newTestRecord(name)593 if err := s.CreateGame(rec); err != nil {594 t.Fatal(err)595 }596 if name == "b" {597 if err := s.UpdateStatus(rec.ID, StatusAbandoned); err != nil {598 t.Fatal(err)599 }600 }601 }602603 games, err := s.ListAllGames()604 if err != nil {605 t.Fatal(err)606 }607 if len(games) != 3 {608 t.Fatalf("expected 3 games, got %d", len(games))609 }610 })611}612613func TestGetGameByName(t *testing.T) {614 t.Run("finds by name", func(t *testing.T) {615 s := openTestStore(t)616 rec := newTestRecord("alpha")617 if err := s.CreateGame(rec); err != nil {618 t.Fatal(err)619 }620621 got, err := s.GetGameByName("alpha")622 if err != nil {623 t.Fatal(err)624 }625 if got == nil {626 t.Fatal("expected record")627 }628 if got.Name != "alpha" {629 t.Errorf("Name = %q, want %q", got.Name, "alpha")630 }631 })632633 t.Run("excludes abandoned", func(t *testing.T) {634 s := openTestStore(t)635 rec := newTestRecord("alpha")636 if err := s.CreateGame(rec); err != nil {637 t.Fatal(err)638 }639 if err := s.UpdateStatus(rec.ID, StatusAbandoned); err != nil {640 t.Fatal(err)641 }642643 got, err := s.GetGameByName("alpha")644 if err != nil {645 t.Fatal(err)646 }647 if got != nil {648 t.Error("expected nil for abandoned game")649 }650 })651652 t.Run("returns nil for missing name", func(t *testing.T) {653 s := openTestStore(t)654655 got, err := s.GetGameByName("nope")656 if err != nil {657 t.Fatal(err)658 }659 if got != nil {660 t.Error("expected nil for missing name")661 }662 })663664 t.Run("preserves completed_at", func(t *testing.T) {665 s := openTestStore(t)666 rec := newTestRecord("alpha")667 if err := s.CreateGame(rec); err != nil {668 t.Fatal(err)669 }670 if err := s.UpdateStatus(rec.ID, StatusCompleted); err != nil {671 t.Fatal(err)672 }673674 got, _ := s.GetGameByName("alpha")675 if got.CompletedAt == nil {676 t.Fatal("expected CompletedAt to be set")677 }678 })679}680681func TestGetDeterministicGame(t *testing.T) {682 s := openTestStore(t)683 rec := newWeeklyTestRecord("Week 01-2026 - #01", 2026, 1, 1)684 if err := s.CreateGame(rec); err != nil {685 t.Fatal(err)686 }687 if err := s.UpdateStatus(rec.ID, StatusAbandoned); err != nil {688 t.Fatal(err)689 }690691 got, err := s.GetDeterministicGame(rec.Name)692 if err != nil {693 t.Fatal(err)694 }695 if got == nil {696 t.Fatal("expected deterministic record")697 }698 if got.Status != StatusAbandoned {699 t.Fatalf("status = %q, want %q", got.Status, StatusAbandoned)700 }701}702703func TestWeeklyQueries(t *testing.T) {704 s := openTestStore(t)705706 records := []*GameRecord{707 newWeeklyTestRecord(weekly.Name(2026, 1, 1), 2026, 1, 1),708 newWeeklyTestRecord(weekly.Name(2026, 1, 2), 2026, 1, 2),709 newWeeklyTestRecord(weekly.Name(2026, 2, 1), 2026, 2, 1),710 newTestRecord("Week 1-2026 - #01"),711 }712 for _, rec := range records {713 if err := s.CreateGame(rec); err != nil {714 t.Fatal(err)715 }716 }717718 got, err := s.GetWeeklyGame(2026, 1, 2)719 if err != nil {720 t.Fatal(err)721 }722 if got == nil || got.Name != weekly.Name(2026, 1, 2) {723 t.Fatalf("GetWeeklyGame() = %#v, want %q", got, weekly.Name(2026, 1, 2))724 }725726 weeklyGames, err := s.ListWeeklyGames(2026, 1)727 if err != nil {728 t.Fatal(err)729 }730 if len(weeklyGames) != 2 {731 t.Fatalf("ListWeeklyGames() len = %d, want 2", len(weeklyGames))732 }733 for _, rec := range weeklyGames {734 info, ok := weekly.ParseName(rec.Name)735 if !ok {736 t.Fatalf("ListWeeklyGames() included non-canonical name %q", rec.Name)737 }738 if info.Year != 2026 || info.Week != 1 {739 t.Fatalf("ListWeeklyGames() included wrong week %#v", info)740 }741 }742}743744func TestFullLifecycle(t *testing.T) {745 s := openTestStore(t)746747 rec := newTestRecord("lifecycle")748 if err := s.CreateGame(rec); err != nil {749 t.Fatal(err)750 }751 if rec.Status != StatusNew {752 t.Errorf("initial status = %q, want %q", rec.Status, StatusNew)753 }754755 if err := s.UpdateStatus(rec.ID, StatusInProgress); err != nil {756 t.Fatal(err)757 }758759 if err := s.UpdateSaveState(rec.ID, `{"progress":"halfway"}`); err != nil {760 t.Fatal(err)761 }762763 mid, _ := s.GetGameByName("lifecycle")764 if mid.Status != StatusInProgress {765 t.Errorf("mid-game status = %q, want %q", mid.Status, StatusInProgress)766 }767 if mid.SaveState != `{"progress":"halfway"}` {768 t.Errorf("mid-game SaveState = %q", mid.SaveState)769 }770771 if err := s.UpdateStatus(rec.ID, StatusCompleted); err != nil {772 t.Fatal(err)773 }774775 done, _ := s.GetGameByName("lifecycle")776 if done.Status != StatusCompleted {777 t.Errorf("final status = %q, want %q", done.Status, StatusCompleted)778 }779 if done.CompletedAt == nil {780 t.Error("expected CompletedAt to be set")781 }782783 games, _ := s.ListGames()784 found := false785 for _, g := range games {786 if g.Name == "lifecycle" {787 found = true788 }789 }790 if !found {791 t.Error("completed game should appear in ListGames")792 }793}