leetcode/apps/cli/db.ts

/**
 * Local LeetCode problem cache backed by SQLite at
 * ~/.local/share/leetcode/data.db (XDG_DATA_HOME honored).
 *
 * One `problems` table, two freshness rules:
 *   - the full problem index (id / title / slug / difficulty / paid flag) is
 *     refreshed from leetcode.com at most once per 24h (`meta.index_fetched_at`);
 *   - descriptions (HTML from GraphQL `question.content`) are fetched once per
 *     slug and kept forever. Only the campaign problems linked from README.md
 *     are ever fetched, and paid-only problems are skipped because GraphQL
 *     returns null content for them.
 *
 * A `solutions` table tracks the lifecycle of each work/ file by LC number:
 * `created_at` (backfilled from the file's birthtime on every picker run),
 * `last_tested` and `last_submitted` (stamped on exit 0 of leetcode-cli).
 * The test picker sorts by last_tested, the submit picker by created_at, and
 * submit hides anything with a last_submitted.
 *
 * Library: side-effect-free on import — nothing is opened or fetched until
 * problemDb() is called. Index upserts never touch the description column, so
 * a daily refresh cannot evict cached statements.
 */
import { Database } from "bun:sqlite";
import { mkdirSync } from "node:fs";
import { homedir } from "node:os";
import { dirname, join } from "node:path";

export interface Problem {
  id: number;
  title: string;
  slug: string;
  difficulty: "Easy" | "Medium" | "Hard";
  paidOnly: boolean;
}

/** Lifecycle of one work/ solution, keyed by LC number. ISO timestamps. */
export interface SolutionActivity {
  createdAt: string;
  lastTested: string | null;
  lastSubmitted: string | null;
}

const DIFFICULTY = ["", "Easy", "Medium", "Hard"] as const;
const INDEX_TTL_MS = 24 * 60 * 60 * 1000;

export const DB_PATH = join(
  process.env.XDG_DATA_HOME ?? join(homedir(), ".local", "share"),
  "leetcode",
  "data.db",
);

// ── README parsing ───────────────────────────────────────────────

/** Campaign problem slugs linked from README.md, in order, deduplicated. */
export function readmeSlugs(markdown: string): string[] {
  const slugs = new Set<string>();
  for (const m of markdown.matchAll(/https:\/\/leetcode\.com\/problems\/([\w-]+)\//g)) {
    slugs.add(m[1]!);
  }
  return [...slugs];
}

// ── remote fetches ───────────────────────────────────────────────

async function fetchIndex(): Promise<Problem[]> {
  const res = await fetch("https://leetcode.com/api/problems/all/", {
    headers: { "user-agent": "Mozilla/5.0" },
  });
  if (!res.ok) throw new Error(`GET /api/problems/all -> ${res.status} ${res.statusText}`);
  const data = (await res.json()) as {
    stat_status_pairs: Array<{
      stat: {
        frontend_question_id: number;
        question__title: string;
        question__title_slug: string;
      };
      difficulty: { level: 1 | 2 | 3 };
      paid_only: boolean;
    }>;
  };
  return data.stat_status_pairs
    .map((p) => ({
      id: p.stat.frontend_question_id,
      title: p.stat.question__title,
      slug: p.stat.question__title_slug,
      difficulty: DIFFICULTY[p.difficulty.level] as Problem["difficulty"],
      paidOnly: p.paid_only,
    }))
    .sort((a, b) => a.id - b.id);
}

/** HTML problem statement, or null when LeetCode withholds it (paid-only). */
export async function fetchDescription(slug: string): Promise<string | null> {
  const res = await fetch("https://leetcode.com/graphql", {
    method: "POST",
    headers: {
      "content-type": "application/json",
      "user-agent": "Mozilla/5.0",
      referer: `https://leetcode.com/problems/${slug}/`,
    },
    body: JSON.stringify({
      query: "query questionContent($titleSlug: String!) { question(titleSlug: $titleSlug) { content } }",
      variables: { titleSlug: slug },
    }),
  });
  if (!res.ok) throw new Error(`POST /graphql (${slug}) -> ${res.status} ${res.statusText}`);
  const data = (await res.json()) as { data?: { question?: { content?: string | null } | null } };
  return data.data?.question?.content ?? null;
}

// ── the cache ────────────────────────────────────────────────────

export function problemDb(path = DB_PATH) {
  mkdirSync(dirname(path), { recursive: true });
  const db = new Database(path);
  db.exec(`
    CREATE TABLE IF NOT EXISTS problems (
      id          INTEGER PRIMARY KEY,
      title       TEXT NOT NULL,
      slug        TEXT NOT NULL UNIQUE,
      difficulty  TEXT NOT NULL,
      paid_only   INTEGER NOT NULL,
      description TEXT
    );
    CREATE TABLE IF NOT EXISTS solutions (
      id             INTEGER PRIMARY KEY,
      created_at     TEXT NOT NULL,
      last_tested    TEXT,
      last_submitted TEXT
    );
    DROP TABLE IF EXISTS submissions;
    CREATE TABLE IF NOT EXISTS meta (
      key   TEXT PRIMARY KEY,
      value TEXT NOT NULL
    );
  `);

  const getMeta = db.query<{ value: string }, [string]>("SELECT value FROM meta WHERE key = ?1");
  const setMeta = db.query<never, [string, string]>(
    "INSERT INTO meta (key, value) VALUES (?1, ?2) ON CONFLICT(key) DO UPDATE SET value = excluded.value",
  );
  const upsert = db.query<never, [number, string, string, string, number]>(
    `INSERT INTO problems (id, title, slug, difficulty, paid_only)
     VALUES (?1, ?2, ?3, ?4, ?5)
     ON CONFLICT(id) DO UPDATE SET
       title = excluded.title, slug = excluded.slug,
       difficulty = excluded.difficulty, paid_only = excluded.paid_only`,
  );
  const descState = db.query<{ paid_only: number; described: number }, [string]>(
    "SELECT paid_only, description IS NOT NULL AS described FROM problems WHERE slug = ?1",
  );

  return {
    path,

    /** True when the index has never been fetched or is older than 24h. */
    indexStale(): boolean {
      const row = getMeta.get("index_fetched_at");
      return !row || Date.now() - Number(row.value) > INDEX_TTL_MS;
    },

    /** Pull the full index from leetcode.com and upsert it. Returns the count. */
    async refreshIndex(): Promise<number> {
      const problems = await fetchIndex();
      db.transaction(() => {
        for (const p of problems) upsert.run(p.id, p.title, p.slug, p.difficulty, p.paidOnly ? 1 : 0);
        setMeta.run("index_fetched_at", String(Date.now()));
      })();
      return problems.length;
    },

    /** Every cached problem, ordered by LC number. */
    all(): Problem[] {
      const rows = db
        .query<{ id: number; title: string; slug: string; difficulty: string; paid_only: number }, []>(
          "SELECT id, title, slug, difficulty, paid_only FROM problems ORDER BY id",
        )
        .all();
      return rows.map((r) => ({
        id: r.id,
        title: r.title,
        slug: r.slug,
        difficulty: r.difficulty as Problem["difficulty"],
        paidOnly: r.paid_only === 1,
      }));
    },

    /** Free (non-premium) slugs among `slugs` with no cached description yet. */
    missingDescriptions(slugs: string[]): string[] {
      return slugs.filter((slug) => {
        const row = descState.get(slug);
        return row !== null && !row.paid_only && !row.described;
      });
    },

    saveDescription(slug: string, html: string): void {
      db.query("UPDATE problems SET description = ?2 WHERE slug = ?1").run(slug, html);
    },

    /** Per-solution lifecycle rows keyed by LC number. */
    solutionActivity(): Map<number, SolutionActivity> {
      const rows = db
        .query<{ id: number; created_at: string; last_tested: string | null; last_submitted: string | null }, []>(
          "SELECT id, created_at, last_tested, last_submitted FROM solutions",
        )
        .all();
      return new Map(
        rows.map((r) => [r.id, { createdAt: r.created_at, lastTested: r.last_tested, lastSubmitted: r.last_submitted }]),
      );
    },

    /** Register a solution's creation time; existing rows are left alone. */
    ensureSolution(id: number, createdAt: string): void {
      db.query("INSERT INTO solutions (id, created_at) VALUES (?1, ?2) ON CONFLICT(id) DO NOTHING").run(id, createdAt);
    },

    markTested(id: number): void {
      db.query(
        `INSERT INTO solutions (id, created_at, last_tested) VALUES (?1, ?2, ?2)
         ON CONFLICT(id) DO UPDATE SET last_tested = excluded.last_tested`,
      ).run(id, new Date().toISOString());
    },

    markSubmitted(id: number): void {
      db.query(
        `INSERT INTO solutions (id, created_at, last_submitted) VALUES (?1, ?2, ?2)
         ON CONFLICT(id) DO UPDATE SET last_submitted = excluded.last_submitted`,
      ).run(id, new Date().toISOString());
    },

    close(): void {
      db.close();
    },
  };
}