/**
 * The collection: items that were lost and the notes visitors leave on
 * them. One SQLite file under FORGE_APP_DATA; production and each preview
 * get their own, so a preview starts from production's data and never
 * writes back to it.
 */

import { Database } from "bun:sqlite";
import { mkdirSync } from "node:fs";
import { join } from "node:path";

const dataDir = process.env.FORGE_APP_DATA ?? "./data";
mkdirSync(dataDir, { recursive: true });
export const db = new Database(join(dataDir, "app.db"));
db.exec("PRAGMA journal_mode = WAL");

// An earlier version of this app kept a flat note table with no item to
// belong to. `CREATE TABLE IF NOT EXISTS` below would leave that shape in
// place forever, so a table missing item_id is moved aside once, keeping
// its rows rather than losing them.
const existingNoteColumns = db.query<{ name: string }, []>("PRAGMA table_info(note)").all();
if (existingNoteColumns.length > 0 && !existingNoteColumns.some((c) => c.name === "item_id")) {
  db.exec("ALTER TABLE note RENAME TO note_legacy");
}

db.exec(
  `CREATE TABLE IF NOT EXISTS item (
    id INTEGER PRIMARY KEY,
    description TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
  )`,
);
db.exec(
  `CREATE TABLE IF NOT EXISTS note (
    id INTEGER PRIMARY KEY,
    item_id INTEGER NOT NULL REFERENCES item(id),
    kind TEXT NOT NULL CHECK (kind IN ('sighting', 'match')),
    body TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
  )`,
);

export type Item = { id: number; description: string; created_at: string };
export type ItemWithCount = Item & { note_count: number };
export type NoteKind = "sighting" | "match";
export type Note = { id: number; item_id: number; kind: NoteKind; body: string; created_at: string };

export function createItem(description: string): number {
  const row = db
    .query<{ id: number }, [string]>("INSERT INTO item (description) VALUES (?1) RETURNING id")
    .get(description);
  return row!.id;
}

export function getItem(id: number): Item | undefined {
  return db.query<Item, [number]>("SELECT id, description, created_at FROM item WHERE id = ?1").get(id) ?? undefined;
}

export function listItems(): ItemWithCount[] {
  return db
    .query<
      ItemWithCount,
      []
    >(`SELECT item.id, item.description, item.created_at,
              (SELECT count(*) FROM note WHERE note.item_id = item.id) AS note_count
       FROM item ORDER BY item.id DESC LIMIT 100`)
    .all();
}

export function listNotes(itemId: number): Note[] {
  return db
    .query<Note, [number]>("SELECT id, item_id, kind, body, created_at FROM note WHERE item_id = ?1 ORDER BY id ASC")
    .all(itemId);
}

export function addNote(itemId: number, kind: NoteKind, body: string): void {
  db.query("INSERT INTO note (item_id, kind, body) VALUES (?1, ?2, ?3)").run(itemId, kind, body);
}