bagg-app/db.js

'use strict';

const Database = require('better-sqlite3');
const crypto = require('crypto');
const path = require('path');

const DB_PATH = process.env.DATABASE_PATH || path.join(__dirname, 'data', 'bagg.sqlite');

// Ensure data dir exists
const fs = require('fs');
fs.mkdirSync(path.dirname(DB_PATH), { recursive: true });

const db = new Database(DB_PATH);

db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

// ── Schema ────────────────────────────────────────────────────────────────────

db.exec(`
CREATE TABLE IF NOT EXISTS users (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  username      TEXT    NOT NULL UNIQUE,
  password_hash TEXT    NOT NULL,
  salt          TEXT    NOT NULL,
  created_at    INTEGER NOT NULL DEFAULT (unixepoch()),
  updated_at    INTEGER NOT NULL DEFAULT (unixepoch())
);

CREATE TABLE IF NOT EXISTS web_sessions (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  token      TEXT    NOT NULL UNIQUE,
  user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  expires_at INTEGER NOT NULL,
  created_at INTEGER NOT NULL DEFAULT (unixepoch())
);

CREATE TABLE IF NOT EXISTS oauth_clients (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  client_id     TEXT    NOT NULL UNIQUE,
  client_secret TEXT    NOT NULL,
  created_at    INTEGER NOT NULL DEFAULT (unixepoch())
);

CREATE TABLE IF NOT EXISTS oauth_tokens (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  access_token  TEXT    NOT NULL UNIQUE,
  refresh_token TEXT    UNIQUE,
  user_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  expires_at    INTEGER NOT NULL,
  created_at    INTEGER NOT NULL DEFAULT (unixepoch())
);

CREATE TABLE IF NOT EXISTS entries (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id         INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  url             TEXT    NOT NULL,
  hashed_url      TEXT    NOT NULL,
  title           TEXT,
  content         TEXT,
  domain_name     TEXT,
  preview_picture TEXT,
  language        TEXT,
  reading_time    INTEGER NOT NULL DEFAULT 0,
  is_archived     INTEGER NOT NULL DEFAULT 0,
  is_starred      INTEGER NOT NULL DEFAULT 0,
  published_at    INTEGER,
  archived_at     INTEGER,
  starred_at      INTEGER,
  created_at      INTEGER NOT NULL DEFAULT (unixepoch()),
  updated_at      INTEGER NOT NULL DEFAULT (unixepoch())
);

CREATE INDEX IF NOT EXISTS idx_entries_user_id     ON entries(user_id);
CREATE INDEX IF NOT EXISTS idx_entries_is_archived ON entries(user_id, is_archived);
CREATE INDEX IF NOT EXISTS idx_entries_is_starred  ON entries(user_id, is_starred);
CREATE INDEX IF NOT EXISTS idx_entries_hashed_url  ON entries(user_id, hashed_url);

CREATE TABLE IF NOT EXISTS tags (
  id      INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  label   TEXT    NOT NULL,
  slug    TEXT    NOT NULL,
  UNIQUE(user_id, slug)
);

CREATE TABLE IF NOT EXISTS entries_tags (
  entry_id INTEGER NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
  tag_id   INTEGER NOT NULL REFERENCES tags(id)    ON DELETE CASCADE,
  PRIMARY KEY (entry_id, tag_id)
);
`);

// ── Seed: create default user + client from env ───────────────────────────────

function hashPassword(password, salt) {
  return crypto.scryptSync(password, salt, 64).toString('hex');
}

function verifyPassword(password, hash, salt) {
  return crypto.timingSafeEqual(
    Buffer.from(hashPassword(password, salt), 'hex'),
    Buffer.from(hash, 'hex')
  );
}

function randomToken() {
  return crypto.randomBytes(32).toString('hex');
}

function slugify(str) {
  return str.toLowerCase().trim().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '');
}

// Seed admin user if ADMIN_USERNAME/ADMIN_PASSWORD set and no users exist
const adminUsername = process.env.ADMIN_USERNAME;
const adminPassword = process.env.ADMIN_PASSWORD;

if (adminUsername && adminPassword) {
  const existing = db.prepare('SELECT id FROM users WHERE username = ?').get(adminUsername);
  if (!existing) {
    const salt = crypto.randomBytes(16).toString('hex');
    const hash = hashPassword(adminPassword, salt);
    db.prepare('INSERT OR IGNORE INTO users (username, password_hash, salt) VALUES (?, ?, ?)')
      .run(adminUsername, hash, salt);
    console.log(`[db] Created admin user: ${adminUsername}`);
  }
}

// Seed default OAuth2 client
const clientId     = process.env.ADMIN_CLIENT_ID     || 'bagg_ios';
const clientSecret = process.env.ADMIN_CLIENT_SECRET || 'bagg_secret';
db.prepare('INSERT OR IGNORE INTO oauth_clients (client_id, client_secret) VALUES (?, ?)')
  .run(clientId, clientSecret);

// ── User queries ──────────────────────────────────────────────────────────────

function getUserByUsername(username) {
  return db.prepare('SELECT * FROM users WHERE username = ?').get(username);
}

function getUserById(id) {
  return db.prepare('SELECT id, username, created_at FROM users WHERE id = ?').get(id);
}

// ── Web session queries ───────────────────────────────────────────────────────

function createWebSession(userId) {
  const token = randomToken();
  const expiresAt = Math.floor(Date.now() / 1000) + 60 * 60 * 24 * 30; // 30 days
  db.prepare('INSERT INTO web_sessions (token, user_id, expires_at) VALUES (?, ?, ?)')
    .run(token, userId, expiresAt);
  return token;
}

function getWebSession(token) {
  const now = Math.floor(Date.now() / 1000);
  return db.prepare(
    'SELECT * FROM web_sessions WHERE token = ? AND expires_at > ?'
  ).get(token, now);
}

function deleteWebSession(token) {
  db.prepare('DELETE FROM web_sessions WHERE token = ?').run(token);
}

// ── OAuth client queries ──────────────────────────────────────────────────────

function getOAuthClient(clientId, clientSecret) {
  return db.prepare(
    'SELECT * FROM oauth_clients WHERE client_id = ? AND client_secret = ?'
  ).get(clientId, clientSecret);
}

// ── OAuth token queries ───────────────────────────────────────────────────────

function createOAuthToken(accessToken, refreshToken, userId) {
  const expiresAt = Math.floor(Date.now() / 1000) + 3600;
  db.prepare(
    'INSERT INTO oauth_tokens (access_token, refresh_token, user_id, expires_at) VALUES (?, ?, ?, ?)'
  ).run(accessToken, refreshToken, userId, expiresAt);
}

function getTokenByAccessToken(token) {
  const now = Math.floor(Date.now() / 1000);
  return db.prepare(
    'SELECT * FROM oauth_tokens WHERE access_token = ? AND expires_at > ?'
  ).get(token, now);
}

function getTokenByRefreshToken(token) {
  return db.prepare(
    'SELECT * FROM oauth_tokens WHERE refresh_token = ?'
  ).get(token);
}

function rotateOAuthToken(oldId, newAccess, newRefresh) {
  const expiresAt = Math.floor(Date.now() / 1000) + 3600;
  db.prepare('DELETE FROM oauth_tokens WHERE id = ?').run(oldId);
  db.prepare(
    'INSERT INTO oauth_tokens (access_token, refresh_token, user_id, expires_at) SELECT ?, ?, user_id, ? FROM oauth_tokens WHERE id = ?'
  ).run(newAccess, newRefresh, expiresAt, oldId);
  // oldId is gone, re-insert with the user_id from the deleted row -- use a different approach
}

// Fix: keep user_id available before deletion
function rotateOAuthTokenFix(oldToken) {
  const expiresAt = Math.floor(Date.now() / 1000) + 3600;
  const newAccess  = randomToken();
  const newRefresh = randomToken();
  db.prepare('DELETE FROM oauth_tokens WHERE id = ?').run(oldToken.id);
  db.prepare(
    'INSERT INTO oauth_tokens (access_token, refresh_token, user_id, expires_at) VALUES (?, ?, ?, ?)'
  ).run(newAccess, newRefresh, oldToken.user_id, expiresAt);
  return { access_token: newAccess, refresh_token: newRefresh };
}

// ── Entry queries ─────────────────────────────────────────────────────────────

function hashUrl(url) {
  return crypto.createHash('sha1').update(url).digest('hex');
}

function extractDomain(url) {
  try { return new URL(url).hostname.replace(/^www\./, ''); } catch { return null; }
}

const GET_ENTRIES = db.prepare(`
  SELECT * FROM entries
  WHERE user_id = :userId
    AND (:archive IS NULL OR is_archived = :archive)
    AND (:starred IS NULL OR is_starred = :starred)
  ORDER BY
    CASE WHEN :sort = 'created'  AND :order = 'desc' THEN created_at END DESC,
    CASE WHEN :sort = 'created'  AND :order = 'asc'  THEN created_at END ASC,
    CASE WHEN :sort = 'updated'  AND :order = 'desc' THEN updated_at END DESC,
    CASE WHEN :sort = 'updated'  AND :order = 'asc'  THEN updated_at END ASC,
    CASE WHEN :sort = 'id'       AND :order = 'desc' THEN id END DESC,
    CASE WHEN :sort = 'id'       AND :order = 'asc'  THEN id END ASC,
    created_at DESC
  LIMIT :limit OFFSET :offset
`);

const COUNT_ENTRIES = db.prepare(`
  SELECT COUNT(*) as count FROM entries
  WHERE user_id = :userId
    AND (:archive IS NULL OR is_archived = :archive)
    AND (:starred IS NULL OR is_starred = :starred)
`);

function getEntries(userId, { page = 1, perPage = 30, archive, starred, sort = 'created', order = 'desc' } = {}) {
  const archiveVal = archive !== undefined ? (archive === '1' || archive === 1 ? 1 : 0) : null;
  const starredVal = starred !== undefined ? (starred === '1' || starred === 1 ? 1 : 0) : null;
  const params = {
    userId,
    archive: archiveVal,
    starred: starredVal,
    sort,
    order,
    limit: +perPage,
    offset: (+page - 1) * +perPage
  };
  const items = GET_ENTRIES.all(params);
  const { count } = COUNT_ENTRIES.get(params);
  return { items, total: count };
}

function getEntryById(id, userId) {
  return db.prepare('SELECT * FROM entries WHERE id = ? AND user_id = ?').get(id, userId);
}

function entryExistsByUrl(url, userId) {
  const hash = hashUrl(url);
  return db.prepare('SELECT id FROM entries WHERE hashed_url = ? AND user_id = ?').get(hash, userId);
}

function createEntry(userId, { url, title, content, is_archived = 0, is_starred = 0, tags }) {
  const domain = extractDomain(url);
  const hash = hashUrl(url);
  const now = Math.floor(Date.now() / 1000);
  const result = db.prepare(`
    INSERT INTO entries (user_id, url, hashed_url, title, content, domain_name, is_archived, is_starred, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `).run(userId, url, hash, title || null, content || null, domain, +!!is_archived, +!!is_starred, now, now);
  return getEntryById(result.lastInsertRowid, userId);
}

function updateEntry(id, userId, fields) {
  const entry = getEntryById(id, userId);
  if (!entry) return null;

  const now = Math.floor(Date.now() / 1000);
  const updates = [];
  const values = [];

  if (fields.title    !== undefined) { updates.push('title = ?');       values.push(fields.title); }
  if (fields.content  !== undefined) { updates.push('content = ?');     values.push(fields.content); }
  if (fields.language !== undefined) { updates.push('language = ?');    values.push(fields.language); }
  if (fields.is_archived !== undefined) {
    updates.push('is_archived = ?');
    values.push(+!!fields.is_archived);
    updates.push('archived_at = ?');
    values.push(fields.is_archived ? now : null);
  }
  if (fields.is_starred !== undefined) {
    updates.push('is_starred = ?');
    values.push(+!!fields.is_starred);
    updates.push('starred_at = ?');
    values.push(fields.is_starred ? now : null);
  }
  if (updates.length === 0) return entry;

  updates.push('updated_at = ?');
  values.push(now);
  values.push(id, userId);

  db.prepare(`UPDATE entries SET ${updates.join(', ')} WHERE id = ? AND user_id = ?`).run(...values);
  return getEntryById(id, userId);
}

function deleteEntry(id, userId) {
  return db.prepare('DELETE FROM entries WHERE id = ? AND user_id = ?').run(id, userId).changes > 0;
}

// ── Tag queries ───────────────────────────────────────────────────────────────

function getTagsForEntry(entryId) {
  return db.prepare(`
    SELECT t.* FROM tags t
    JOIN entries_tags et ON et.tag_id = t.id
    WHERE et.entry_id = ?
  `).all(entryId);
}

function getAllTags(userId) {
  return db.prepare('SELECT * FROM tags WHERE user_id = ? ORDER BY label').all(userId);
}

function getOrCreateTag(userId, label) {
  const slug = slugify(label);
  const existing = db.prepare('SELECT * FROM tags WHERE user_id = ? AND slug = ?').get(userId, slug);
  if (existing) return existing;
  const result = db.prepare('INSERT INTO tags (user_id, label, slug) VALUES (?, ?, ?)').run(userId, label, slug);
  return { id: result.lastInsertRowid, user_id: userId, label, slug };
}

function addTagToEntry(entryId, tagId) {
  db.prepare('INSERT OR IGNORE INTO entries_tags (entry_id, tag_id) VALUES (?, ?)').run(entryId, tagId);
}

function removeTagFromEntry(entryId, tagId) {
  return db.prepare('DELETE FROM entries_tags WHERE entry_id = ? AND tag_id = ?').run(entryId, tagId).changes > 0;
}

// ── Exports ───────────────────────────────────────────────────────────────────

module.exports = {
  hashPassword,
  verifyPassword,
  randomToken,
  getUserByUsername,
  getUserById,
  createWebSession,
  getWebSession,
  deleteWebSession,
  getOAuthClient,
  createOAuthToken,
  getTokenByAccessToken,
  getTokenByRefreshToken,
  rotateOAuthTokenFix,
  getEntries,
  getEntryById,
  entryExistsByUrl,
  createEntry,
  updateEntry,
  deleteEntry,
  getTagsForEntry,
  getAllTags,
  getOrCreateTag,
  addTagToEntry,
  removeTagFromEntry,
  hashUrl,
};