pengzhan.dev/server/services/db.js

const fs = require('fs');
const path = require('path');
const os = require('os');
const { DatabaseSync } = require('node:sqlite');

class DatabaseService {
  constructor() {
    this.db = null;
    this.dbPath = null;
    this.isEphemeral = false;
  }

  init(contentDir) {
    const dataDir = path.join(contentDir, 'data');
    if (!fs.existsSync(dataDir)) {
      fs.mkdirSync(dataDir, { recursive: true });
    }

    const isStaging = process.env.NODE_ENV === 'test' || process.env.APP_ENV === 'staging' || process.env.DB_EPHEMERAL === 'true';
    const prodDbPath = path.join(dataDir, 'site.db');

    if (isStaging) {
      this.isEphemeral = true;
      const ephemeralName = `site.staging-${process.pid}-${Date.now()}.db`;
      this.dbPath = path.join(os.tmpdir(), ephemeralName);

      // Copy existing production DB if present so staging has realistic base state
      if (fs.existsSync(prodDbPath)) {
        try {
          fs.copyFileSync(prodDbPath, this.dbPath);
        } catch (_) {}
      }

      // Automatically clean up ephemeral DB on process exit
      const cleanup = () => {
        try {
          if (this.db) {
            this.db.close();
            this.db = null;
          }
          if (fs.existsSync(this.dbPath)) {
            fs.unlinkSync(this.dbPath);
          }
        } catch (_) {}
      };
      process.once('exit', cleanup);
      process.once('SIGINT', () => { cleanup(); process.exit(0); });
      process.once('SIGTERM', () => { cleanup(); process.exit(0); });

      this.db = new DatabaseSync(this.dbPath);
      console.log(`[DB] Ephemeral Staging SQLite database initialized at ${this.dbPath}`);
    } else {
      this.dbPath = prodDbPath;
      this.db = new DatabaseSync(this.dbPath);
    }

    // Initialize unified tables
    this.db.exec(`
      -- Posts storage & metadata
      CREATE TABLE IF NOT EXISTS posts (
        slug TEXT NOT NULL,
        lang TEXT NOT NULL,
        title TEXT NOT NULL,
        date TEXT NOT NULL,
        author TEXT,
        category TEXT,
        tags TEXT,
        excerpt TEXT,
        raw_content TEXT,
        cover TEXT,
        is_default INTEGER DEFAULT 1,
        PRIMARY KEY (slug, lang)
      );

      -- Full Text Search virtual table
      CREATE VIRTUAL TABLE IF NOT EXISTS posts_fts USING fts5(
        slug UNINDEXED,
        lang UNINDEXED,
        title,
        category,
        tags,
        excerpt,
        content,
        tokenize='porter unicode61'
      );

      -- Search query analytics & history
      CREATE TABLE IF NOT EXISTS search_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        query TEXT NOT NULL,
        result_count INTEGER DEFAULT 0,
        searched_at TEXT NOT NULL
      );

      -- Search query recommendations (curated + dynamic)
      CREATE TABLE IF NOT EXISTS search_recommendations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        label TEXT NOT NULL,
        query TEXT NOT NULL UNIQUE,
        clicks INTEGER DEFAULT 0,
        is_pinned INTEGER DEFAULT 0
      );

      -- Comments & Moderation Queue
      CREATE TABLE IF NOT EXISTS comments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        post_slug TEXT NOT NULL,
        author_name TEXT NOT NULL,
        author_email TEXT,
        author_website TEXT,
        body TEXT NOT NULL,
        created_at TEXT NOT NULL,
        approved INTEGER DEFAULT 0, -- 0: Pending moderation, 1: Approved, -1: Rejected/Spam
        ip TEXT
      );
      CREATE INDEX IF NOT EXISTS idx_comments_slug ON comments(post_slug, approved);
      CREATE INDEX IF NOT EXISTS idx_comments_email ON comments(author_email);
    `);

    // Auto-seed comments if comments table is newly created and comments.json exists
    try {
      const count = this.db.prepare('SELECT COUNT(*) as c FROM comments').get().c;
      const seedFile = path.join(dataDir, 'comments.json');
      if (count === 0 && fs.existsSync(seedFile)) {
        const seedData = JSON.parse(fs.readFileSync(seedFile, 'utf8'));
        const insertStmt = this.db.prepare(`
          INSERT INTO comments (id, post_slug, author_name, author_email, author_website, body, created_at, approved, ip)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        `);
        this.db.exec('BEGIN');
        for (const c of seedData) {
          insertStmt.run(
            c.id,
            c.post_slug,
            c.author_name,
            c.author_email,
            c.author_website,
            c.body,
            c.created_at,
            c.approved,
            c.ip || ''
          );
        }
        this.db.exec('COMMIT');
        console.log(`[DB] Seeded ${seedData.length} comments from ${seedFile}`);
      }
    } catch (err) {
      console.warn('[DB] Warning: Failed to auto-seed comments:', err.message);
    }

    console.log(`[DB] Unified SQLite database initialized at ${this.dbPath}`);
  }

  // --- Posts & FTS5 Synchronization ---
  syncPosts(postsList) {
    if (!this.db) return;

    try {
      this.db.exec('DELETE FROM posts; DELETE FROM posts_fts;');

      const insertPost = this.db.prepare(`
        INSERT INTO posts (slug, lang, title, date, author, category, tags, excerpt, raw_content, cover, is_default)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `);

      const insertFts = this.db.prepare(`
        INSERT INTO posts_fts (slug, lang, title, category, tags, excerpt, content)
        VALUES (?, ?, ?, ?, ?, ?, ?)
      `);

      for (const post of postsList) {
        const tagsStr = (post.tags || []).join(' ');
        const dateStr = post.date instanceof Date ? post.date.toISOString() : String(post.date);

        insertPost.run(
          post.slug,
          post.lang || 'en',
          post.title || '',
          dateStr,
          post.author || '',
          post.category || '',
          tagsStr,
          post.excerpt || '',
          post.rawContent || '',
          post.cover || '',
          post.isDefault ? 1 : 0
        );

        insertFts.run(
          post.slug,
          post.lang || 'en',
          post.title || '',
          post.category || '',
          tagsStr,
          post.excerpt || '',
          post.rawContent || ''
        );
      }
      console.log(`[DB] Synced ${postsList.length} post variants to SQLite & FTS5.`);
    } catch (err) {
      console.error('[DB] Error syncing posts to SQLite:', err);
    }
  }

  // --- Search Query Logging & Recommendations in DB ---
  logSearch(query, resultCount = 0) {
    if (!this.db || !query || !query.trim()) return;
    try {
      const trimmed = query.trim().slice(0, 150);
      const stmt = this.db.prepare(`
        INSERT INTO search_logs (query, result_count, searched_at)
        VALUES (?, ?, ?)
      `);
      stmt.run(trimmed, resultCount, new Date().toISOString());

      // Update recommendations clicks or add to recommendations if valid
      const recStmt = this.db.prepare(`
        INSERT INTO search_recommendations (label, query, clicks)
        VALUES (?, ?, 1)
        ON CONFLICT(query) DO UPDATE SET clicks = clicks + 1
      `);
      recStmt.run(trimmed, trimmed);
    } catch (err) {
      console.error('[DB] Error logging search:', err);
    }
  }

  getTopSearchRecommendations(limit = 6) {
    if (!this.db) return [];
    try {
      const stmt = this.db.prepare(`
        SELECT label, query, clicks, is_pinned
        FROM search_recommendations
        ORDER BY is_pinned DESC, clicks DESC
        LIMIT ?
      `);
      return stmt.all(limit);
    } catch (err) {
      console.error('[DB] Error fetching recommendations:', err);
      return [];
    }
  }

  // Aggregate genuine popular searches by frequency where result_count > 0
  getTopSearchQueries(limit = 6) {
    if (!this.db) return [];
    try {
      const stmt = this.db.prepare(`
        SELECT query, COUNT(*) as frequency
        FROM search_logs
        WHERE result_count > 0 AND length(trim(query)) >= 2
        GROUP BY lower(trim(query))
        ORDER BY frequency DESC
        LIMIT ?
      `);
      return stmt.all(limit).map(r => ({
        label: r.query,
        query: r.query,
        frequency: r.frequency
      }));
    } catch (err) {
      console.error('[DB] Error fetching top search queries:', err);
      return [];
    }
  }

  // Get total & pending comment counts per post slug
  getPostCommentStats() {
    if (!this.db) return new Map();
    try {
      const stmt = this.db.prepare(`
        SELECT post_slug,
               COUNT(*) as total,
               SUM(CASE WHEN approved = 0 THEN 1 ELSE 0 END) as pending
        FROM comments
        GROUP BY post_slug
      `);
      const rows = stmt.all();
      const map = new Map();
      for (const row of rows) {
        map.set(row.post_slug, {
          total: row.total || 0,
          pending: row.pending || 0
        });
      }
      return map;
    } catch (err) {
      console.error('[DB] Error fetching post comment stats:', err);
      return new Map();
    }
  }

  cleanTestSearchData() {
    if (!this.db) return;
    try {
      this.db.exec(`
        DELETE FROM search_logs WHERE query LIKE '%container image%' OR query LIKE '%tag:k8s "container image"%';
        DELETE FROM search_recommendations WHERE query LIKE '%container image%';
      `);
    } catch (_) {}
  }

  // --- Comments & WordPress-Grade Moderation Workflow ---
  isAuthorWhitelisted(email) {
    if (!this.db || !email) return false;
    try {
      const stmt = this.db.prepare(`
        SELECT COUNT(*) as count FROM comments 
        WHERE author_email = ? AND approved = 1
      `);
      const res = stmt.get(email.toLowerCase().trim());
      return res && res.count > 0;
    } catch (err) {
      return false;
    }
  }

  addComment({ postSlug, authorName, authorEmail, authorWebsite, body, honeypot, ip }) {
    if (!this.db) throw new Error('DB not initialized');

    // Honeypot spam check
    if (honeypot && honeypot.trim().length > 0) {
      console.warn('[DB] Honeypot triggered, dropping spam.');
      return { id: -1, status: 'spam' };
    }

    const cleanName = String(authorName).trim().slice(0, 80);
    const cleanEmail = authorEmail ? String(authorEmail).toLowerCase().trim().slice(0, 120) : '';
    const cleanWebsite = authorWebsite ? String(authorWebsite).trim().slice(0, 200) : '';
    const cleanBody = String(body).trim().slice(0, 5000);
    const createdAt = new Date().toISOString();

    // WordPress-parity rule: If author's email was previously approved, auto-approve new comment!
    const autoApprove = cleanEmail ? this.isAuthorWhitelisted(cleanEmail) : false;
    const approvedStatus = autoApprove ? 1 : 0; // 0: pending moderation

    const stmt = this.db.prepare(`
      INSERT INTO comments (post_slug, author_name, author_email, author_website, body, created_at, approved, ip)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(postSlug, cleanName, cleanEmail, cleanWebsite, cleanBody, createdAt, approvedStatus, ip || '');

    return {
      post_slug: postSlug,
      author_name: cleanName,
      author_email: cleanEmail,
      author_website: cleanWebsite,
      body: cleanBody,
      created_at: createdAt,
      approved: approvedStatus,
      status: approvedStatus === 1 ? 'approved' : 'pending'
    };
  }

  getApprovedCommentsForPost(postSlug) {
    if (!this.db) return [];
    try {
      const stmt = this.db.prepare(`
        SELECT id, post_slug, author_name, author_website, body, created_at
        FROM comments
        WHERE post_slug = ? AND approved = 1
        ORDER BY id ASC
      `);
      return stmt.all(postSlug);
    } catch (err) {
      console.error('[DB] Error fetching comments:', err);
      return [];
    }
  }

  getCommentCounts() {
    if (!this.db) return { all: 0, pending: 0, approved: 0, spam: 0 };
    try {
      const all = this.db.prepare('SELECT COUNT(*) as c FROM comments').get().c;
      const pending = this.db.prepare('SELECT COUNT(*) as c FROM comments WHERE approved = 0').get().c;
      const approved = this.db.prepare('SELECT COUNT(*) as c FROM comments WHERE approved = 1').get().c;
      const spam = this.db.prepare('SELECT COUNT(*) as c FROM comments WHERE approved = -1').get().c;
      return { all, pending, approved, spam };
    } catch (err) {
      console.error('[DB] Error fetching comment counts:', err);
      return { all: 0, pending: 0, approved: 0, spam: 0 };
    }
  }

  getPaginatedComments({ status = 'all', q = '', page = 1, limit = 15 } = {}) {
    if (!this.db) return { comments: [], total: 0, page: 1, totalPages: 1 };
    try {
      let whereClauses = [];
      let params = [];

      if (status === 'pending') {
        whereClauses.push('approved = 0');
      } else if (status === 'approved') {
        whereClauses.push('approved = 1');
      } else if (status === 'spam') {
        whereClauses.push('approved = -1');
      }

      if (q && q.trim()) {
        whereClauses.push('(author_name LIKE ? OR author_email LIKE ? OR author_website LIKE ? OR body LIKE ? OR post_slug LIKE ? OR ip LIKE ?)');
        const searchPattern = `%${q.trim()}%`;
        params.push(searchPattern, searchPattern, searchPattern, searchPattern, searchPattern, searchPattern);
      }

      const whereSql = whereClauses.length > 0 ? `WHERE ${whereClauses.join(' AND ')}` : '';
      
      const countStmt = this.db.prepare(`SELECT COUNT(*) as count FROM comments ${whereSql}`);
      const total = countStmt.get(...params).count;

      const pageNum = Math.max(1, parseInt(page, 10) || 1);
      const pageSize = Math.max(1, parseInt(limit, 10) || 15);
      const totalPages = Math.ceil(total / pageSize) || 1;
      const offset = (pageNum - 1) * pageSize;

      const dataStmt = this.db.prepare(`
        SELECT id, post_slug, author_name, author_email, author_website, body, created_at, approved, ip
        FROM comments
        ${whereSql}
        ORDER BY id DESC
        LIMIT ? OFFSET ?
      `);

      const comments = dataStmt.all(...params, pageSize, offset);
      return { comments, total, page: pageNum, totalPages, limit: pageSize };
    } catch (err) {
      console.error('[DB] Error in getPaginatedComments:', err);
      return { comments: [], total: 0, page: 1, totalPages: 1 };
    }
  }

  getAllComments(filterApproved = null) {
    if (!this.db) return [];
    try {
      if (filterApproved !== null) {
        const stmt = this.db.prepare(`
          SELECT id, post_slug, author_name, author_email, author_website, body, created_at, approved, ip
          FROM comments
          WHERE approved = ?
          ORDER BY id DESC
        `);
        return stmt.all(filterApproved);
      }
      const stmt = this.db.prepare(`
        SELECT id, post_slug, author_name, author_email, author_website, body, created_at, approved, ip
        FROM comments
        ORDER BY id DESC
      `);
      return stmt.all();
    } catch (err) {
      console.error('[DB] Error fetching all comments:', err);
      return [];
    }
  }

  updateCommentStatus(commentId, newStatus) {
    if (!this.db) return false;
    try {
      const stmt = this.db.prepare(`UPDATE comments SET approved = ? WHERE id = ?`);
      stmt.run(newStatus, commentId);
      return true;
    } catch (err) {
      console.error('[DB] Error updating comment status:', err);
      return false;
    }
  }

  deleteComment(commentId) {
    if (!this.db) return false;
    try {
      const stmt = this.db.prepare(`DELETE FROM comments WHERE id = ?`);
      stmt.run(commentId);
      return true;
    } catch (err) {
      console.error('[DB] Error deleting comment:', err);
      return false;
    }
  }

  batchUpdateCommentStatus(ids, newStatus) {
    if (!this.db || !Array.isArray(ids) || ids.length === 0) return 0;
    try {
      const placeholders = ids.map(() => '?').join(',');
      const stmt = this.db.prepare(`UPDATE comments SET approved = ? WHERE id IN (${placeholders})`);
      const info = stmt.run(newStatus, ...ids);
      return info.changes;
    } catch (err) {
      console.error('[DB] Error batch updating comments:', err);
      return 0;
    }
  }

  batchDeleteComments(ids) {
    if (!this.db || !Array.isArray(ids) || ids.length === 0) return 0;
    try {
      const placeholders = ids.map(() => '?').join(',');
      const stmt = this.db.prepare(`DELETE FROM comments WHERE id IN (${placeholders})`);
      const info = stmt.run(...ids);
      return info.changes;
    } catch (err) {
      console.error('[DB] Error batch deleting comments:', err);
      return 0;
    }
  }

  // --- Admin Dashboard Stats & Logs ---
  getAdminStats() {
    if (!this.db) {
      return { totalPosts: 0, pendingComments: 0, approvedComments: 0, totalSearches: 0 };
    }
    try {
      const postsCount = this.db.prepare('SELECT COUNT(*) as count FROM posts').get()?.count || 0;
      const pendingCount = this.db.prepare('SELECT COUNT(*) as count FROM comments WHERE approved = 0').get()?.count || 0;
      const approvedCount = this.db.prepare('SELECT COUNT(*) as count FROM comments WHERE approved = 1').get()?.count || 0;
      const searchCount = this.db.prepare('SELECT COUNT(*) as count FROM search_logs').get()?.count || 0;

      return {
        totalPosts: postsCount,
        pendingComments: pendingCount,
        approvedComments: approvedCount,
        totalSearches: searchCount
      };
    } catch (err) {
      console.error('[DB] Error getting admin stats:', err);
      return { totalPosts: 0, pendingComments: 0, approvedComments: 0, totalSearches: 0 };
    }
  }

  getAllSearchLogs(limit = 100) {
    if (!this.db) return [];
    try {
      const stmt = this.db.prepare(`
        SELECT id, query, result_count, searched_at
        FROM search_logs
        ORDER BY id DESC
        LIMIT ?
      `);
      return stmt.all(limit);
    } catch (err) {
      console.error('[DB] Error fetching search logs:', err);
      return [];
    }
  }
}

module.exports = new DatabaseService();