speedboard/db.js

import Database from 'better-sqlite3';
import { join, dirname } from 'path';
import { fileURLToPath } from 'url';

const __dirname = dirname(fileURLToPath(import.meta.url));
const DB_PATH = process.env.DB_PATH || join(__dirname, 'speedboard.db');

let db;

export function getDb() {
  if (!db) {
    db = new Database(DB_PATH);
    db.pragma('journal_mode = WAL');
    db.pragma('foreign_keys = ON');
    initSchema();
  }
  return db;
}

function migrateSchema() {
  // Add columns introduced after initial schema — safe to run on existing DBs
  const add = (col, type) => { try { db.exec(`ALTER TABLE jobs ADD COLUMN ${col} ${type}`); } catch {} };
  add('headers_json', 'TEXT');
  add('ssl_json',     'TEXT');
}

function initSchema() {
  db.exec(`
    CREATE TABLE IF NOT EXISTS jobs (
      id TEXT PRIMARY KEY,
      url TEXT NOT NULL,
      browser TEXT NOT NULL DEFAULT 'chrome',
      mobile INTEGER NOT NULL DEFAULT 0,
      runs INTEGER NOT NULL DEFAULT 3,
      status TEXT NOT NULL DEFAULT 'queued',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      started_at TEXT,
      finished_at TEXT,
      error_msg TEXT,

      -- Core Web Vitals (ms / score)
      lcp REAL,
      cls REAL,
      tbt REAL,
      fcp REAL,
      ttfb REAL,
      max_potential_fid REAL,

      -- Visual metrics
      speed_index REAL,
      first_visual_change REAL,
      last_visual_change REAL,
      visual_complete_85 REAL,
      perceptual_speed_index REAL,

      -- Navigation timings (ms)
      page_load_time REAL,
      fully_loaded REAL,
      dom_content_loaded REAL,
      dom_interactive REAL,
      front_end_time REAL,
      back_end_time REAL,
      time_to_first_byte REAL,

      -- Coach scores (0-100)
      score_overall REAL,
      score_performance REAL,
      score_accessibility REAL,
      score_bestpractice REAL,
      score_privacy REAL,

      -- Resource sizes (bytes)
      transfer_total REAL,
      transfer_html REAL,
      transfer_js REAL,
      transfer_css REAL,
      transfer_image REAL,
      transfer_font REAL,
      transfer_other REAL,

      -- Request counts
      requests_total INTEGER,
      requests_js INTEGER,
      requests_css INTEGER,
      requests_image INTEGER,
      requests_font INTEGER,

      -- Third-party
      third_party_requests INTEGER,
      third_party_transfer REAL,

      -- Accessibility (axe)
      axe_critical INTEGER,
      axe_serious INTEGER,
      axe_moderate INTEGER,
      axe_minor INTEGER,

      -- CPU
      long_tasks_count INTEGER,
      long_tasks_duration REAL,

      -- CO2
      co2_per_page_view REAL,
      co2_total REAL,
      co2_first_party REAL,
      co2_third_party REAL,

      -- Raw JSON paths for drilling down
      report_folder TEXT
    );

    CREATE INDEX IF NOT EXISTS idx_jobs_url ON jobs(url);
    CREATE INDEX IF NOT EXISTS idx_jobs_created ON jobs(created_at DESC);
    CREATE INDEX IF NOT EXISTS idx_jobs_status ON jobs(status);
  `);
  migrateSchema();
}

export function createJob(id, url, browser, mobile, runs) {
  const stmt = getDb().prepare(`
    INSERT INTO jobs (id, url, browser, mobile, runs, status, created_at)
    VALUES (?, ?, ?, ?, ?, 'queued', datetime('now'))
  `);
  stmt.run(id, url, browser, mobile ? 1 : 0, runs);
}

export function updateJobStatus(id, status, extras = {}) {
  const db = getDb();
  const fields = ['status = ?'];
  const values = [status];

  if (status === 'running') {
    fields.push('started_at = datetime(\'now\')');
  }
  if (status === 'done' || status === 'error') {
    fields.push('finished_at = datetime(\'now\')');
  }
  if (extras.error_msg !== undefined) {
    fields.push('error_msg = ?');
    values.push(extras.error_msg);
  }
  if (extras.report_folder !== undefined) {
    fields.push('report_folder = ?');
    values.push(extras.report_folder);
  }

  values.push(id);
  db.prepare(`UPDATE jobs SET ${fields.join(', ')} WHERE id = ?`).run(...values);
}

export function updateJobMetrics(id, metrics) {
  const db = getDb();
  const keys = Object.keys(metrics);
  if (keys.length === 0) return;
  const sets = keys.map(k => `${k} = ?`).join(', ');
  const values = keys.map(k => metrics[k]);
  values.push(id);
  db.prepare(`UPDATE jobs SET ${sets} WHERE id = ?`).run(...values);
}

export function getJob(id) {
  return getDb().prepare('SELECT * FROM jobs WHERE id = ?').get(id);
}

export function getHistory(limit = 100) {
  return getDb().prepare(`
    SELECT id, url, browser, mobile, runs, status, created_at, finished_at,
           lcp, fcp, tbt, speed_index, score_overall, score_performance,
           transfer_total, requests_total
    FROM jobs
    ORDER BY created_at DESC
    LIMIT ?
  `).all(limit);
}

export function getSiteHistory(url, limit = 20) {
  return getDb().prepare(`
    SELECT * FROM jobs
    WHERE url = ? AND status = 'done'
    ORDER BY created_at DESC
    LIMIT ?
  `).all(url, limit);
}

export function getDistinctUrls() {
  return getDb().prepare(`
    SELECT url, COUNT(*) as count, MAX(created_at) as last_tested,
           (SELECT status FROM jobs j2 WHERE j2.url = jobs.url ORDER BY created_at DESC LIMIT 1) as last_status
    FROM jobs
    GROUP BY url
    ORDER BY MAX(created_at) DESC
  `).all();
}