Skip to content
Shell
8.6 KB
Raw Blame
/**
 * Database operations for Reddit Research Agent
 */

import Database from 'better-sqlite3';
import { randomUUID } from 'crypto';
import { SCHEMA } from './schema.js';
import type { GoldmineScore } from '../types.js';

let db: Database.Database | null = null;

/**
 * Initialize database connection
 */
export function initDatabase(dbPath = './reddit-research.db'): Database.Database {
  if (db) return db;

  db = new Database(dbPath);
  db.pragma('journal_mode = WAL');
  db.exec(SCHEMA);

  return db;
}

/**
 * Get database instance
 */
export function getDb(): Database.Database {
  if (!db) {
    throw new Error('Database not initialized. Call initDatabase() first.');
  }
  return db;
}

/**
 * Close database connection
 */
export function closeDatabase(): void {
  if (db) {
    db.close();
    db = null;
  }
}

// ==================== Thread Operations ====================

/**
 * Save scanned threads to database
 */
export function saveThreads(threads: GoldmineScore[]): number {
  const database = getDb();
  const stmt = database.prepare(`
    INSERT OR REPLACE INTO threads
    (id, url, subreddit, title, score, num_comments, upvote_ratio, goldmine_score, scanned_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);

  let count = 0;
  const now = new Date().toISOString();

  for (const thread of threads) {
    const id = thread.url.split('/comments/')[1]?.split('/')[0] || randomUUID();
    stmt.run(
      id,
      thread.url,
      thread.subreddit,
      thread.title,
      thread.score,
      thread.numComments,
      thread.upvoteRatio,
      thread.goldmineScore,
      now
    );
    count++;
  }

  return count;
}

/**
 * Get unanalyzed threads sorted by goldmine score
 */
export function getUnanalyzedThreads(limit = 10): Array<{ id: string; url: string; goldmineScore: number }> {
  const database = getDb();
  return database.prepare(`
    SELECT id, url, goldmine_score as goldmineScore
    FROM threads
    WHERE analyzed = 0
    ORDER BY goldmine_score DESC
    LIMIT ?
  `).all(limit) as Array<{ id: string; url: string; goldmineScore: number }>;
}

/**
 * Mark thread as analyzed
 */
export function markThreadAnalyzed(threadId: string): void {
  const database = getDb();
  database.prepare('UPDATE threads SET analyzed = 1 WHERE id = ?').run(threadId);
}

/**
 * Get thread by ID
 */
export function getThread(threadId: string): GoldmineScore | null {
  const database = getDb();
  const row = database.prepare('SELECT * FROM threads WHERE id = ?').get(threadId) as any;
  if (!row) return null;

  return {
    url: row.url,
    title: row.title,
    score: row.score,
    numComments: row.num_comments,
    upvoteRatio: row.upvote_ratio,
    goldmineScore: row.goldmine_score,
    subreddit: row.subreddit,
  };
}

// ==================== Insight Operations ====================

/**
 * Save market insights
 */
export function saveInsight(
  threadId: string,
  type: string,
  content: string,
  metadata?: Record<string, unknown>,
  confidence?: string
): string {
  const database = getDb();
  const id = randomUUID();

  database.prepare(`
    INSERT INTO insights (id, thread_id, type, content, metadata, confidence, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `).run(
    id,
    threadId,
    type,
    content,
    metadata ? JSON.stringify(metadata) : null,
    confidence || null,
    new Date().toISOString()
  );

  return id;
}

/**
 * Get insights by type
 */
export function getInsightsByType(type: string, limit = 50): Array<{
  id: string;
  threadId: string;
  content: string;
  metadata: Record<string, unknown> | null;
  confidence: string | null;
  createdAt: string;
}> {
  const database = getDb();
  const rows = database.prepare(`
    SELECT id, thread_id as threadId, content, metadata, confidence, created_at as createdAt
    FROM insights
    WHERE type = ?
    ORDER BY created_at DESC
    LIMIT ?
  `).all(type, limit) as any[];

  return rows.map(row => ({
    ...row,
    metadata: row.metadata ? JSON.parse(row.metadata) : null,
  }));
}

/**
 * Get all insights for a thread
 */
export function getThreadInsights(threadId: string): Array<{
  type: string;
  content: string;
  metadata: Record<string, unknown> | null;
}> {
  const database = getDb();
  const rows = database.prepare(`
    SELECT type, content, metadata
    FROM insights
    WHERE thread_id = ?
    ORDER BY type, created_at
  `).all(threadId) as any[];

  return rows.map(row => ({
    type: row.type,
    content: row.content,
    metadata: row.metadata ? JSON.parse(row.metadata) : null,
  }));
}

// ==================== Agent Run Operations ====================

/**
 * Start a new agent run
 */
export function startAgentRun(): number {
  const database = getDb();
  const result = database.prepare(`
    INSERT INTO agent_runs (started_at, status)
    VALUES (?, 'running')
  `).run(new Date().toISOString());

  return result.lastInsertRowid as number;
}

/**
 * Complete an agent run
 */
export function completeAgentRun(
  runId: number,
  stats: { threadsScanned: number; insightsFound: number; costUsd: number }
): void {
  const database = getDb();
  database.prepare(`
    UPDATE agent_runs
    SET completed_at = ?, status = 'completed',
        threads_scanned = ?, insights_found = ?, cost_usd = ?
    WHERE id = ?
  `).run(
    new Date().toISOString(),
    stats.threadsScanned,
    stats.insightsFound,
    stats.costUsd,
    runId
  );
}

/**
 * Fail an agent run
 */
export function failAgentRun(runId: number, error: string): void {
  const database = getDb();
  database.prepare(`
    UPDATE agent_runs
    SET completed_at = ?, status = 'failed', error_message = ?
    WHERE id = ?
  `).run(new Date().toISOString(), error, runId);
}

/**
 * Get recent agent runs
 */
export function getRecentRuns(limit = 10): Array<{
  id: number;
  startedAt: string;
  completedAt: string | null;
  status: string;
  threadsScanned: number;
  insightsFound: number;
  costUsd: number;
}> {
  const database = getDb();
  return database.prepare(`
    SELECT id, started_at as startedAt, completed_at as completedAt,
           status, threads_scanned as threadsScanned,
           insights_found as insightsFound, cost_usd as costUsd
    FROM agent_runs
    ORDER BY id DESC
    LIMIT ?
  `).all(limit) as any[];
}

// ==================== Tracked Sources ====================

/**
 * Add a subreddit to track
 */
export function addTrackedSource(subreddit: string, query?: string): void {
  const database = getDb();
  database.prepare(`
    INSERT OR IGNORE INTO tracked_sources (subreddit, query, created_at)
    VALUES (?, ?, ?)
  `).run(subreddit, query || null, new Date().toISOString());
}

/**
 * Get all tracked sources
 */
export function getTrackedSources(): Array<{
  id: number;
  subreddit: string;
  query: string | null;
  enabled: boolean;
  lastScanned: string | null;
}> {
  const database = getDb();
  return database.prepare(`
    SELECT id, subreddit, query, enabled, last_scanned as lastScanned
    FROM tracked_sources
    WHERE enabled = 1
  `).all() as any[];
}

/**
 * Update last scanned time for a source
 */
export function updateSourceScanned(subreddit: string, query?: string): void {
  const database = getDb();
  if (query) {
    database.prepare(`
      UPDATE tracked_sources SET last_scanned = ? WHERE subreddit = ? AND query = ?
    `).run(new Date().toISOString(), subreddit, query);
  } else {
    database.prepare(`
      UPDATE tracked_sources SET last_scanned = ? WHERE subreddit = ? AND query IS NULL
    `).run(new Date().toISOString(), subreddit);
  }
}

// ==================== Stats ====================

/**
 * Get database statistics
 */
export function getStats(): {
  totalThreads: number;
  analyzedThreads: number;
  totalInsights: number;
  insightsByType: Record<string, number>;
  topSubreddits: Array<{ subreddit: string; count: number }>;
} {
  const database = getDb();

  const totalThreads = (database.prepare('SELECT COUNT(*) as count FROM threads').get() as any).count;
  const analyzedThreads = (database.prepare('SELECT COUNT(*) as count FROM threads WHERE analyzed = 1').get() as any).count;
  const totalInsights = (database.prepare('SELECT COUNT(*) as count FROM insights').get() as any).count;

  const insightsByTypeRows = database.prepare(`
    SELECT type, COUNT(*) as count FROM insights GROUP BY type
  `).all() as any[];
  const insightsByType: Record<string, number> = {};
  for (const row of insightsByTypeRows) {
    insightsByType[row.type] = row.count;
  }

  const topSubreddits = database.prepare(`
    SELECT subreddit, COUNT(*) as count
    FROM threads
    GROUP BY subreddit
    ORDER BY count DESC
    LIMIT 10
  `).all() as any[];

  return {
    totalThreads,
    analyzedThreads,
    totalInsights,
    insightsByType,
    topSubreddits,
  };
}