fiddle/db.js

import Database from 'better-sqlite3';
import { mkdirSync } from 'fs';

mkdirSync('data', { recursive: true });

const db = new Database('data/fiddles.db');
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

db.exec(`
  CREATE TABLE IF NOT EXISTS fiddles (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL DEFAULT 'Untitled',
    html TEXT NOT NULL DEFAULT '',
    css TEXT NOT NULL DEFAULT '',
    css_type TEXT NOT NULL DEFAULT 'css',
    js TEXT NOT NULL DEFAULT '',
    js_type TEXT NOT NULL DEFAULT 'javascript',
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
  )
`);

// Migration: add js_type column for existing databases
try {
  db.exec(`ALTER TABLE fiddles ADD COLUMN js_type TEXT NOT NULL DEFAULT 'javascript'`);
} catch (_) { /* column already exists */ }

// Migration: add listed column
try {
  db.exec(`ALTER TABLE fiddles ADD COLUMN listed INTEGER NOT NULL DEFAULT 1`);
} catch (_) { /* column already exists */ }

// Migration: add options column (JSON string for per-fiddle settings like tailwind)
try {
  db.exec(`ALTER TABLE fiddles ADD COLUMN options TEXT NOT NULL DEFAULT '{}'`);
} catch (_) { /* column already exists */ }

// Tags tables
db.exec(`
  CREATE TABLE IF NOT EXISTS tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE COLLATE NOCASE
  )
`);

db.exec(`
  CREATE TABLE IF NOT EXISTS fiddle_tags (
    fiddle_id TEXT NOT NULL REFERENCES fiddles(id) ON DELETE CASCADE,
    tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (fiddle_id, tag_id)
  )
`);

db.exec(`CREATE INDEX IF NOT EXISTS idx_fiddles_listed_updated ON fiddles(listed, updated_at DESC)`);

// Migration: add screenshot column
try {
  db.exec(`ALTER TABLE fiddles ADD COLUMN screenshot TEXT`);
} catch (_) { /* column already exists */ }

// Migration: add publishing columns
try {
  db.exec(`ALTER TABLE fiddles ADD COLUMN published_slug TEXT`);
} catch (_) { /* column already exists */ }
try {
  db.exec(`ALTER TABLE fiddles ADD COLUMN published_html TEXT`);
} catch (_) { /* column already exists */ }
db.exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_fiddles_published_slug ON fiddles(published_slug) WHERE published_slug IS NOT NULL`);

// Version history table
db.exec(`
  CREATE TABLE IF NOT EXISTS fiddle_versions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    fiddle_id TEXT NOT NULL REFERENCES fiddles(id) ON DELETE CASCADE,
    version INTEGER NOT NULL,
    html TEXT NOT NULL DEFAULT '',
    css TEXT NOT NULL DEFAULT '',
    css_type TEXT NOT NULL DEFAULT 'css',
    js TEXT NOT NULL DEFAULT '',
    js_type TEXT NOT NULL DEFAULT 'javascript',
    options TEXT NOT NULL DEFAULT '{}',
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
  )
`);
db.exec(`CREATE INDEX IF NOT EXISTS idx_versions_fiddle ON fiddle_versions(fiddle_id, version DESC)`);

// Collections tables
db.exec(`
  CREATE TABLE IF NOT EXISTS collections (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
  )
`);
db.exec(`
  CREATE TABLE IF NOT EXISTS collection_fiddles (
    collection_id TEXT NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
    fiddle_id TEXT NOT NULL REFERENCES fiddles(id) ON DELETE CASCADE,
    position INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (collection_id, fiddle_id)
  )
`);

// Presentation slides table
db.exec(`
  CREATE TABLE IF NOT EXISTS fiddle_slides (
    id TEXT PRIMARY KEY,
    fiddle_id TEXT NOT NULL,
    slide_order INTEGER NOT NULL,
    html TEXT DEFAULT '',
    css TEXT DEFAULT '',
    js TEXT DEFAULT '',
    notes TEXT DEFAULT '',
    FOREIGN KEY (fiddle_id) REFERENCES fiddles(id) ON DELETE CASCADE
  )
`);
db.exec(`CREATE INDEX IF NOT EXISTS idx_slides_fiddle ON fiddle_slides(fiddle_id, slide_order)`);

export const stmts = {
  insert: db.prepare(`
    INSERT INTO fiddles (id, title, html, css, css_type, js, js_type, listed, options)
    VALUES (@id, @title, @html, @css, @css_type, @js, @js_type, @listed, @options)
  `),
  get: db.prepare('SELECT * FROM fiddles WHERE id = ?'),
  update: db.prepare(`
    UPDATE fiddles SET title = @title, html = @html, css = @css,
      css_type = @css_type, js = @js, js_type = @js_type, listed = @listed,
      options = @options, updated_at = datetime('now')
    WHERE id = @id
  `),
  list: db.prepare('SELECT id, title, css_type, js_type, created_at, updated_at FROM fiddles ORDER BY updated_at DESC LIMIT 50'),

  // Tags
  getTagsForFiddle: db.prepare(`
    SELECT t.id, t.name FROM tags t
    JOIN fiddle_tags ft ON ft.tag_id = t.id
    WHERE ft.fiddle_id = ?
  `),
  insertTag: db.prepare('INSERT OR IGNORE INTO tags (name) VALUES (?)'),
  getTagByName: db.prepare('SELECT id, name FROM tags WHERE name = ? COLLATE NOCASE'),
  insertFiddleTag: db.prepare('INSERT OR IGNORE INTO fiddle_tags (fiddle_id, tag_id) VALUES (?, ?)'),
  deleteFiddleTags: db.prepare('DELETE FROM fiddle_tags WHERE fiddle_id = ?'),
  listTags: db.prepare(`
    SELECT t.id, t.name, COUNT(ft.fiddle_id) as count
    FROM tags t
    LEFT JOIN fiddle_tags ft ON ft.tag_id = t.id
    GROUP BY t.id
    HAVING count > 0
    ORDER BY count DESC
  `),

  // Versions
  insertVersion: db.prepare(`
    INSERT INTO fiddle_versions (fiddle_id, version, html, css, css_type, js, js_type, options)
    VALUES (@fiddle_id, @version, @html, @css, @css_type, @js, @js_type, @options)
  `),
  getMaxVersion: db.prepare('SELECT COALESCE(MAX(version), 0) as max_ver FROM fiddle_versions WHERE fiddle_id = ?'),
  listVersions: db.prepare('SELECT id, version, created_at FROM fiddle_versions WHERE fiddle_id = ? ORDER BY version DESC'),
  getVersion: db.prepare('SELECT * FROM fiddle_versions WHERE fiddle_id = ? AND version = ?'),
  deleteOldVersions: db.prepare(`
    DELETE FROM fiddle_versions WHERE fiddle_id = ? AND id NOT IN (
      SELECT id FROM fiddle_versions WHERE fiddle_id = ? ORDER BY version DESC LIMIT 50
    )
  `),

  // Screenshot
  updateScreenshot: db.prepare('UPDATE fiddles SET screenshot = ? WHERE id = ?'),

  // Publishing
  publishFiddle: db.prepare('UPDATE fiddles SET published_slug = @slug, published_html = @html WHERE id = @id'),
  getPublishedFiddle: db.prepare('SELECT id, title, published_html FROM fiddles WHERE published_slug = ?'),
  unpublishFiddle: db.prepare('UPDATE fiddles SET published_slug = NULL, published_html = NULL WHERE id = ?'),
  getPublishStatus: db.prepare('SELECT published_slug FROM fiddles WHERE id = ?'),

  // Collections
  insertCollection: db.prepare(`
    INSERT INTO collections (id, name, description) VALUES (@id, @name, @description)
  `),
  listCollections: db.prepare(`
    SELECT c.*, COUNT(cf.fiddle_id) as fiddle_count
    FROM collections c
    LEFT JOIN collection_fiddles cf ON cf.collection_id = c.id
    GROUP BY c.id
    ORDER BY c.updated_at DESC
  `),
  getCollection: db.prepare('SELECT * FROM collections WHERE id = ?'),
  updateCollection: db.prepare(`
    UPDATE collections SET name = @name, description = @description, updated_at = datetime('now') WHERE id = @id
  `),
  deleteCollection: db.prepare('DELETE FROM collections WHERE id = ?'),
  addFiddleToCollection: db.prepare(`
    INSERT OR IGNORE INTO collection_fiddles (collection_id, fiddle_id, position)
    VALUES (@collection_id, @fiddle_id, (SELECT COALESCE(MAX(position), 0) + 1 FROM collection_fiddles WHERE collection_id = @collection_id))
  `),
  removeFiddleFromCollection: db.prepare('DELETE FROM collection_fiddles WHERE collection_id = ? AND fiddle_id = ?'),
  getCollectionFiddles: db.prepare(`
    SELECT f.id, f.title, f.css_type, f.js_type, f.created_at, f.updated_at, f.screenshot,
      SUBSTR(f.html, 1, 200) as html_preview, SUBSTR(f.js, 1, 200) as js_preview
    FROM fiddles f
    JOIN collection_fiddles cf ON cf.fiddle_id = f.id
    WHERE cf.collection_id = ?
    ORDER BY cf.position
  `),
  getCollectionsForFiddle: db.prepare(`
    SELECT c.id, c.name FROM collections c
    JOIN collection_fiddles cf ON cf.collection_id = c.id
    WHERE cf.fiddle_id = ?
  `),

  // Slides
  insertSlide: db.prepare(`
    INSERT INTO fiddle_slides (id, fiddle_id, slide_order, html, css, js, notes)
    VALUES (@id, @fiddle_id, @slide_order, @html, @css, @js, @notes)
  `),
  listSlides: db.prepare('SELECT * FROM fiddle_slides WHERE fiddle_id = ? ORDER BY slide_order'),
  getSlide: db.prepare('SELECT * FROM fiddle_slides WHERE id = ?'),
  updateSlide: db.prepare(`
    UPDATE fiddle_slides SET html = @html, css = @css, js = @js, notes = @notes, slide_order = @slide_order WHERE id = @id
  `),
  deleteSlide: db.prepare('DELETE FROM fiddle_slides WHERE id = ?'),
  getMaxSlideOrder: db.prepare('SELECT COALESCE(MAX(slide_order), 0) as max_order FROM fiddle_slides WHERE fiddle_id = ?'),
};

/**
 * Upsert tags for a fiddle. Accepts an array of tag name strings.
 */
export function setFiddleTags(fiddleId, tagNames) {
  stmts.deleteFiddleTags.run(fiddleId);
  for (const name of tagNames) {
    const trimmed = name.trim();
    if (!trimmed) continue;
    stmts.insertTag.run(trimmed);
    const tag = stmts.getTagByName.get(trimmed);
    if (tag) stmts.insertFiddleTag.run(fiddleId, tag.id);
  }
}

/**
 * Snapshot current fiddle state as a version before overwriting.
 */
export function snapshotVersion(fiddleId) {
  const fiddle = stmts.get.get(fiddleId);
  if (!fiddle) return;
  const { max_ver } = stmts.getMaxVersion.get(fiddleId);
  const version = max_ver + 1;
  stmts.insertVersion.run({
    fiddle_id: fiddleId,
    version,
    html: fiddle.html,
    css: fiddle.css,
    css_type: fiddle.css_type,
    js: fiddle.js,
    js_type: fiddle.js_type,
    options: fiddle.options || '{}',
  });
  // Cap at 50 versions
  stmts.deleteOldVersions.run(fiddleId, fiddleId);
}

export default db;