Study/tests/arcadeRemovalMigration.test.ts

import { readFileSync } from "node:fs";
import path from "node:path";
import Database from "better-sqlite3";
import { afterEach, describe, expect, it } from "vitest";
import {
  applyCommittedMigrations,
  createDisposableDatabase,
  disposeDisposableDatabase,
  type DisposableDatabase,
} from "./helpers/testDatabase";

const REMOVAL_MIGRATION = "20260808100000_remove_arcade";
const databases: DisposableDatabase[] = [];

afterEach(() => {
  for (const database of databases.splice(0)) {
    disposeDisposableDatabase(database);
  }
});

describe("Arcade removal migration", () => {
  it("removes Arcade data while preserving the rest of the study database", () => {
    const database = createDisposableDatabase();
    databases.push(database);
    applyCommittedMigrations(database, { through: "20260807092000_add_progress_revisions" });

    const sqlite = new Database(database.databasePath);
    try {
      sqlite.pragma("foreign_keys = ON");
      sqlite.exec(`
        INSERT INTO "Class" ("id", "slug", "name")
          VALUES ('class-arcade-removal', 'arcade-removal', 'Preserved class');
        INSERT INTO "MaterialGroup" ("id", "classId", "name", "type") VALUES
          ('group-deck-arcade-removal', 'class-arcade-removal', 'Preserved deck group', 'DECK'),
          ('group-quiz-arcade-removal', 'class-arcade-removal', 'Preserved quiz group', 'QUIZ');
        INSERT INTO "Deck" ("id", "classId", "groupId", "name")
          VALUES ('deck-arcade-removal', 'class-arcade-removal', 'group-deck-arcade-removal', 'Preserved deck');
        INSERT INTO "Flashcard" ("id", "deckId", "front", "back")
          VALUES ('card-arcade-removal', 'deck-arcade-removal', 'Preserved front', 'Preserved back');
        INSERT INTO "QuizSet" ("id", "classId", "groupId", "name")
          VALUES ('quiz-arcade-removal', 'class-arcade-removal', 'group-quiz-arcade-removal', 'Preserved quiz');
        INSERT INTO "Question" ("id", "quizSetId", "type", "prompt", "rationale", "category")
          VALUES ('question-arcade-removal', 'quiz-arcade-removal', 'MULTIPLE_CHOICE', 'Preserved prompt', 'Preserved rationale', 'Preserved category');
        INSERT INTO "AnswerOption" ("id", "questionId", "text", "isCorrect")
          VALUES ('option-arcade-removal', 'question-arcade-removal', 'Preserved option', 1);
        INSERT INTO "StudyProgress" ("id", "contentType", "deckId", "mode", "orderJson", "updatedAt", "sessionId", "revision") VALUES
          ('deck-progress-arcade-removal', 'DECK', 'deck-arcade-removal', 'SEQUENTIAL', '["card-arcade-removal"]', CURRENT_TIMESTAMP, 'session-deck', 2),
          ('quiz-progress-arcade-removal', 'QUIZ', NULL, 'SEQUENTIAL', '["question-arcade-removal"]', CURRENT_TIMESTAMP, 'session-quiz', 3);
        UPDATE "StudyProgress"
        SET "quizSetId" = 'quiz-arcade-removal'
        WHERE "id" = 'quiz-progress-arcade-removal';
        INSERT INTO "ShareLink" ("id", "targetType", "groupId")
          VALUES ('share-arcade-removal', 'GROUP', 'group-quiz-arcade-removal');
        INSERT INTO "Setting" ("key", "value") VALUES
          ('llmInstructionsFlashcards', 'keep this'),
          ('llmInstructionsConnections', 'remove this'),
          ('llmInstructionsCrossword', 'remove this');
        INSERT INTO "StudyActivity" ("id", "type") VALUES
          ('activity-flashcard', 'FLASHCARD'),
          ('activity-question', 'QUIZ_QUESTION'),
          ('activity-group', 'ARCADE_GROUP'),
          ('activity-word', 'ARCADE_WORD');
        INSERT INTO "ArcadePack"
          ("id", "classId", "gameType", "name", "schemaVersion", "sourceJson", "normalizedJson", "validationReportJson", "updatedAt")
          VALUES ('arcade-pack', 'class-arcade-removal', 'connections', 'Removed pack', 1, '{}', '{}', '{}', CURRENT_TIMESTAMP);
        INSERT INTO "ArcadeAttempt"
          ("id", "arcadePackId", "mode", "score", "maxScore", "accuracy", "durationSeconds", "mistakes", "hintsUsed", "settingsJson", "resultsJson", "seed")
          VALUES ('arcade-attempt', 'arcade-pack', 'CLASSIC', 0, 400, 0, 0, 0, 0, '{}', '{}', 'seed');
      `);

      sqlite.exec(readFileSync(
        path.join(process.cwd(), "prisma", "migrations", REMOVAL_MIGRATION, "migration.sql"),
        "utf8"
      ));

      expect(sqlite.prepare('SELECT "name" FROM "Class" WHERE "id" = ?').get("class-arcade-removal")).toEqual({ name: "Preserved class" });
      expect(sqlite.prepare('SELECT "name" FROM "Deck" WHERE "id" = ?').get("deck-arcade-removal")).toEqual({ name: "Preserved deck" });
      expect(sqlite.prepare('SELECT "name" FROM "MaterialGroup" WHERE "id" = ?').get("group-deck-arcade-removal")).toEqual({ name: "Preserved deck group" });
      expect(sqlite.prepare('SELECT "name" FROM "QuizSet" WHERE "id" = ?').get("quiz-arcade-removal")).toEqual({ name: "Preserved quiz" });
      expect(sqlite.prepare('SELECT "text" FROM "AnswerOption" WHERE "id" = ?').get("option-arcade-removal")).toEqual({ text: "Preserved option" });
      expect(sqlite.prepare('SELECT COUNT(*) AS count FROM "StudyProgress"').get()).toEqual({ count: 2 });
      expect(sqlite.prepare('SELECT "targetType" FROM "ShareLink" WHERE "id" = ?').get("share-arcade-removal")).toEqual({ targetType: "GROUP" });
      expect(sqlite.prepare('SELECT "key" FROM "Setting" ORDER BY "key"').all()).toEqual([
        { key: "llmInstructionsFlashcards" },
      ]);
      expect(sqlite.prepare('SELECT "type" FROM "StudyActivity" ORDER BY "type"').all()).toEqual([
        { type: "FLASHCARD" },
        { type: "QUIZ_QUESTION" },
      ]);
      expect(sqlite.prepare('SELECT 1 FROM sqlite_master WHERE type = ? AND name = ?').get("table", "ArcadePack")).toBeUndefined();
      expect(sqlite.prepare('SELECT 1 FROM sqlite_master WHERE type = ? AND name = ?').get("table", "ArcadeAttempt")).toBeUndefined();
      expect(sqlite.pragma("foreign_key_check")).toEqual([]);
      expect(sqlite.pragma("integrity_check", { simple: true })).toBe("ok");
    } finally {
      sqlite.close();
    }
  });
});