db.ts

// SQLite access to Apple Notes NoteStore.sqlite.
// Reads the gzip-compressed protobuf blobs and note metadata.

import { Database } from "jsr:@db/sqlite";
import { gunzipSync } from "node:zlib";

const HOME = Deno.env.get("HOME")!;
const NOTESTORE_PATH =
  `${HOME}/Library/Group Containers/group.com.apple.notes/NoteStore.sqlite`;

// Apple epoch: 2001-01-01T00:00:00Z
const APPLE_EPOCH_S = Date.UTC(2001, 0, 1) / 1000;

function appleToISO(seconds: number | null): string {
  if (seconds == null) return "";
  return new Date((seconds + APPLE_EPOCH_S) * 1000).toISOString();
}

export function openNoteStore(readonly = true): Database {
  return new Database(NOTESTORE_PATH, { readonly });
}

/** Read + decompress the protobuf blob for a note (by Z_PK). */
export function readNoteData(db: Database, zpk: number): Uint8Array {
  const row = db.prepare(
    "SELECT ZDATA FROM ZICNOTEDATA WHERE ZNOTE = ?",
  ).get(zpk) as { ZDATA: Uint8Array } | undefined;

  if (!row) throw new Error(`No note data row for Z_PK=${zpk}`);
  if (!row.ZDATA) throw new Error(`ZDATA is null for Z_PK=${zpk} (locked/encrypted?)`);
  return new Uint8Array(gunzipSync(row.ZDATA));
}

/** Parse a CoreData URI or plain number to Z_PK. */
export function resolveId(input: string): number {
  const m = input.match(/\/p(\d+)$/);
  if (m) return Number(m[1]);
  const n = Number(input);
  if (!isNaN(n) && n > 0) return n;
  throw new Error(`Cannot resolve note ID from: ${input}`);
}

export interface NoteMeta {
  zpk: number;
  identifier: string;
  title: string;
  folder: string;
  created: string;
  modified: string;
}

export function getNoteMeta(db: Database, zpk: number): NoteMeta {
  // Column names depend on macOS / Core Data model version.
  // These match macOS 15 (Sequoia) / 26 (Tahoe).
  const row = db.prepare(`
    SELECT
      n.Z_PK,
      n.ZIDENTIFIER,
      n.ZTITLE1 AS title,
      n.ZCREATIONDATE3 AS created,
      n.ZMODIFICATIONDATE1 AS modified,
      f.ZTITLE2 AS folder
    FROM ZICCLOUDSYNCINGOBJECT n
    LEFT JOIN ZICCLOUDSYNCINGOBJECT f ON n.ZFOLDER = f.Z_PK
    WHERE n.Z_PK = ?
  `).get(zpk) as Record<string, unknown> | undefined;

  if (!row) throw new Error(`Note not found: Z_PK=${zpk}`);

  return {
    zpk,
    identifier: (row.ZIDENTIFIER ?? "") as string,
    title: (row.title ?? "") as string,
    folder: (row.folder ?? "") as string,
    created: appleToISO(row.created as number | null),
    modified: appleToISO(row.modified as number | null),
  };
}

export interface AttachmentMeta {
  identifier: string;
  type: string;
  zpk: number;
}

export function getAttachments(
  db: Database,
  zpk: number,
): AttachmentMeta[] {
  // ICAttachment rows use ZNOTE, ICInlineAttachment rows use ZNOTE1
  const rows = db.prepare(`
    SELECT Z_PK, ZIDENTIFIER, ZTYPEUTI
    FROM ZICCLOUDSYNCINGOBJECT
    WHERE (ZNOTE = ? OR ZNOTE1 = ?)
      AND ZIDENTIFIER IS NOT NULL
  `).all(zpk, zpk) as Record<string, unknown>[];

  return rows.map((row) => ({
    identifier: (row.ZIDENTIFIER ?? "") as string,
    type: (row.ZTYPEUTI ?? "") as string,
    zpk: row.Z_PK as number,
  }));
}