import assert from "node:assert/strict";
import { readdir, readFile } from "node:fs/promises";
import { DatabaseSync } from "node:sqlite";
import test from "node:test";

const migrationDirectory = new URL("../../drizzle/", import.meta.url);

async function migratedDatabase() {
  const database = new DatabaseSync(":memory:");
  database.exec("PRAGMA foreign_keys = ON;");
  const files = (await readdir(migrationDirectory))
    .filter((file) => file.endsWith(".sql"))
    .sort();
  assert.ok(files.length > 0, "Expected at least one generated D1 migration");
  for (const file of files) {
    const sql = await readFile(new URL(file, migrationDirectory), "utf8");
    database.exec(sql.replaceAll("--> statement-breakpoint", ""));
  }
  return database;
}

test("creates every V1 persistence table and its lookup indexes", async () => {
  const database = await migratedDatabase();
  const tables = database
    .prepare("SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name")
    .all()
    .map(({ name }) => name);

  assert.deepEqual(
    tables.filter((name) => !name.startsWith("sqlite_")),
    [
      "audio_segments",
      "books",
      "chapters",
      "playback_progress",
      "rate_limits",
      "segments",
      "storage_ledger",
      "upload_sessions",
    ],
  );

  const indexes = database
    .prepare("SELECT name FROM sqlite_master WHERE type = 'index'")
    .all()
    .map(({ name }) => name);
  for (const required of [
    "books_recent_idx",
    "chapters_book_position_idx",
    "segments_chapter_position_idx",
    "audio_segments_lookup_idx",
    "storage_ledger_book_kind_idx",
    "upload_sessions_expiry_idx",
    "rate_limits_updated_idx",
  ]) {
    assert.ok(indexes.includes(required), `Missing index ${required}`);
  }
});

test("cascades every dependent record when a book is deleted", async () => {
  const database = await migratedDatabase();
  database.exec(`
    INSERT INTO books (id, title, author, format, source_object_key, source_bytes, status)
      VALUES ('book-1', '测试书', '作者', 'pdf', 'books/book-1/source.pdf', 100, 'ready');
    INSERT INTO chapters (id, book_id, title, position)
      VALUES ('chapter-1', 'book-1', '第一章', 0);
    INSERT INTO segments (id, chapter_id, position, text, language, content_hash)
      VALUES ('segment-1', 'chapter-1', 0, '正文。', 'zh-CN', 'hash');
    INSERT INTO audio_segments (id, book_id, segment_id, text_version, voice, object_key, status)
      VALUES ('audio-1', 'book-1', 'segment-1', 1, 'zh-CN-XiaoxiaoNeural', 'audio/book-1/a.mp3', 'ready');
    INSERT INTO playback_progress (book_id, chapter_id, segment_id, offset_ms, playback_rate)
      VALUES ('book-1', 'chapter-1', 'segment-1', 500, 1.25);
    INSERT INTO storage_ledger (object_key, kind, book_id, bytes)
      VALUES ('books/book-1/source.pdf', 'source', 'book-1', 100);
    DELETE FROM books WHERE id = 'book-1';
  `);

  for (const table of [
    "books",
    "chapters",
    "segments",
    "audio_segments",
    "playback_progress",
    "storage_ledger",
  ]) {
    const row = database.prepare(`SELECT COUNT(*) AS count FROM ${table}`).get();
    assert.equal(row.count, 0, `Expected ${table} to be empty after cascade`);
  }
});
