OFApp/server/db.js

import Database from 'better-sqlite3';
import { mkdirSync, existsSync } from 'fs';
import { dirname } from 'path';

const DB_PATH = process.env.DB_PATH || './data/db/ofapp.db';

const dir = dirname(DB_PATH);
if (!existsSync(dir)) {
  mkdirSync(dir, { recursive: true });
}

const db = new Database(DB_PATH);

db.pragma('journal_mode = WAL');

db.exec(`
  CREATE TABLE IF NOT EXISTS auth_config (
    user_id TEXT,
    cookie TEXT,
    x_bc TEXT,
    app_token TEXT,
    x_of_rev TEXT,
    user_agent TEXT
  );

  CREATE TABLE IF NOT EXISTS download_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT,
    post_id TEXT,
    media_id TEXT,
    media_type TEXT,
    filename TEXT,
    downloaded_at TEXT
  );

  CREATE TABLE IF NOT EXISTS download_cursors (
    user_id TEXT UNIQUE,
    cursor TEXT,
    posts_downloaded INTEGER
  );

  CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT
  );

  CREATE TABLE IF NOT EXISTS media_files (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    folder TEXT NOT NULL,
    filename TEXT NOT NULL,
    type TEXT NOT NULL,
    size INTEGER NOT NULL,
    modified REAL NOT NULL,
    posted_at TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    UNIQUE(folder, filename)
  );

  CREATE INDEX IF NOT EXISTS idx_media_folder ON media_files(folder);
  CREATE INDEX IF NOT EXISTS idx_media_type ON media_files(type);
  CREATE INDEX IF NOT EXISTS idx_media_modified ON media_files(modified);
  CREATE INDEX IF NOT EXISTS idx_media_posted_at ON media_files(posted_at);
`);

// Migration: add posted_at column if missing
const cols = db.prepare("PRAGMA table_info(download_history)").all().map((c) => c.name);
if (!cols.includes('posted_at')) {
  db.exec('ALTER TABLE download_history ADD COLUMN posted_at TEXT');
}

export function getAuthConfig() {
  const row = db.prepare('SELECT * FROM auth_config LIMIT 1').get();
  return row || null;
}

export function saveAuthConfig(config) {
  const del = db.prepare('DELETE FROM auth_config');
  const ins = db.prepare(
    'INSERT INTO auth_config (user_id, cookie, x_bc, app_token, x_of_rev, user_agent) VALUES (?, ?, ?, ?, ?, ?)'
  );

  const upsert = db.transaction((c) => {
    del.run();
    ins.run(c.user_id, c.cookie, c.x_bc, c.app_token, c.x_of_rev, c.user_agent);
  });

  upsert(config);
}

export function isMediaDownloaded(mediaId) {
  const row = db.prepare('SELECT 1 FROM download_history WHERE media_id = ? LIMIT 1').get(String(mediaId));
  return !!row;
}

export function recordDownload(userId, postId, mediaId, mediaType, filename, postedAt) {
  db.prepare(
    'INSERT INTO download_history (user_id, post_id, media_id, media_type, filename, downloaded_at, posted_at) VALUES (?, ?, ?, ?, ?, ?, ?)'
  ).run(String(userId), String(postId), String(mediaId), mediaType, filename, new Date().toISOString(), postedAt || null);
}

export function getDownloadHistory(userId) {
  return db.prepare('SELECT * FROM download_history WHERE user_id = ? ORDER BY downloaded_at DESC').all(String(userId));
}

export function saveCursor(userId, cursor, postsDownloaded) {
  db.prepare(
    'INSERT INTO download_cursors (user_id, cursor, posts_downloaded) VALUES (?, ?, ?) ON CONFLICT(user_id) DO UPDATE SET cursor = excluded.cursor, posts_downloaded = excluded.posts_downloaded'
  ).run(String(userId), cursor, postsDownloaded);
}

export function getCursor(userId) {
  return db.prepare('SELECT cursor, posts_downloaded FROM download_cursors WHERE user_id = ?').get(String(userId)) || null;
}

export function clearCursor(userId) {
  db.prepare('DELETE FROM download_cursors WHERE user_id = ?').run(String(userId));
}

export function getPostDateByFilename(filename) {
  const row = db.prepare('SELECT posted_at FROM download_history WHERE filename = ? LIMIT 1').get(filename);
  return row?.posted_at || null;
}

export function getSetting(key) {
  const row = db.prepare('SELECT value FROM settings WHERE key = ?').get(key);
  return row ? row.value : null;
}

export function setSetting(key, value) {
  db.prepare(
    'INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value'
  ).run(key, value);
}

export function getAllSettings() {
  const rows = db.prepare('SELECT key, value FROM settings').all();
  const obj = {};
  for (const row of rows) obj[row.key] = row.value;
  return obj;
}

export function getDownloadStats() {
  return db.prepare(
    'SELECT user_id, COUNT(*) as file_count, MAX(downloaded_at) as last_download FROM download_history GROUP BY user_id'
  ).all();
}

// --- media_files helpers ---

const upsertMediaStmt = db.prepare(`
  INSERT INTO media_files (folder, filename, type, size, modified, posted_at)
  VALUES (?, ?, ?, ?, ?, ?)
  ON CONFLICT(folder, filename) DO UPDATE SET
    size = excluded.size,
    modified = excluded.modified,
    posted_at = COALESCE(excluded.posted_at, media_files.posted_at)
`);

export function upsertMediaFile(folder, filename, type, size, modified, postedAt) {
  upsertMediaStmt.run(folder, filename, type, size, modified, postedAt || null);
}

export const upsertMediaFileBatch = db.transaction((files) => {
  for (const f of files) {
    upsertMediaStmt.run(f.folder, f.filename, f.type, f.size, f.modified, f.postedAt || null);
  }
});

export function removeMediaFile(folder, filename) {
  db.prepare('DELETE FROM media_files WHERE folder = ? AND filename = ?').run(folder, filename);
}

export function getMediaFolders() {
  return db.prepare(`
    SELECT folder AS name,
           COUNT(*) AS total,
           SUM(CASE WHEN type = 'image' THEN 1 ELSE 0 END) AS images,
           SUM(CASE WHEN type = 'video' THEN 1 ELSE 0 END) AS videos
    FROM media_files
    GROUP BY folder
    ORDER BY folder
  `).all();
}

export function getMediaFiles({ folder, folders, type, sort, offset, limit }) {
  const conditions = [];
  const params = [];

  if (folder) {
    conditions.push('folder = ?');
    params.push(folder);
  } else if (folders && folders.length > 0) {
    conditions.push(`folder IN (${folders.map(() => '?').join(',')})`);
    params.push(...folders);
  }

  if (type && type !== 'all') {
    conditions.push('type = ?');
    params.push(type);
  }

  const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

  const countRow = db.prepare(`SELECT COUNT(*) AS total FROM media_files ${where}`).get(...params);
  const total = countRow.total;

  let orderBy;
  if (sort === 'shuffle') {
    orderBy = 'ORDER BY RANDOM()';
  } else {
    // 'latest' — prefer posted_at, fall back to modified
    orderBy = 'ORDER BY COALESCE(posted_at, datetime(modified / 1000, \'unixepoch\')) DESC';
  }

  const rows = db.prepare(`
    SELECT folder, filename, type, size, modified, posted_at
    FROM media_files
    ${where}
    ${orderBy}
    LIMIT ? OFFSET ?
  `).all(...params, limit || 50, offset || 0);

  return { total, rows };
}

export function getAllIndexedFolders() {
  return db.prepare('SELECT DISTINCT folder FROM media_files').all().map(r => r.folder);
}

export function removeStaleFiles(folder, existingFilenames) {
  const rows = db.prepare('SELECT filename FROM media_files WHERE folder = ?').all(folder);
  const existing = new Set(existingFilenames);
  const toDelete = rows.filter(r => !existing.has(r.filename));
  if (toDelete.length > 0) {
    const del = db.prepare('DELETE FROM media_files WHERE folder = ? AND filename = ?');
    const batch = db.transaction((files) => {
      for (const f of files) del.run(folder, f.filename);
    });
    batch(toDelete);
  }
  return toDelete.length;
}

export function getMediaFileCount() {
  return db.prepare('SELECT COUNT(*) AS count FROM media_files').get().count;
}