'use strict';

/**
 * Fabius Threat Registry — SQLite Database Layer
 * 
 * Uses better-sqlite3 for synchronous, fast SQLite access.
 * All data is stored locally in the user's Fabius data directory.
 */

const path = require('path');
const fs = require('fs');

let Database;
try {
  Database = require('better-sqlite3');
} catch {
  // Fallback: will be handled at runtime
  Database = null;
}

class FabiusDB {
  /**
   * @param {string} dbPath - Path to SQLite database file
   */
  constructor(dbPath) {
    if (!Database) {
      throw new Error('better-sqlite3 is required. Run: npm install better-sqlite3');
    }

    // Ensure directory exists
    const dir = path.dirname(dbPath);
    if (!fs.existsSync(dir)) {
      fs.mkdirSync(dir, { recursive: true });
    }

    this.db = new Database(dbPath);
    this.db.pragma('journal_mode = WAL');
    this.db.pragma('foreign_keys = ON');

    this._initSchema();
  }

  _initSchema() {
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS threats (
        id            TEXT PRIMARY KEY,
        name          TEXT NOT NULL,
        description   TEXT,
        severity      TEXT CHECK(severity IN ('critical','high','medium','low','info')) DEFAULT 'medium',
        status        TEXT CHECK(status IN ('detected','verified','mitigated','archived')) DEFAULT 'detected',
        cve           TEXT,
        ecosystems    TEXT DEFAULT '["npm"]',
        attack_vector TEXT,
        source_name   TEXT,
        source_url    TEXT,
        source_trusted INTEGER DEFAULT 0,
        raw_advisory  TEXT,
        discovered_at TEXT NOT NULL,
        verified_at   TEXT,
        created_at    TEXT DEFAULT (datetime('now')),
        updated_at    TEXT DEFAULT (datetime('now'))
      );

      CREATE TABLE IF NOT EXISTS threat_packages (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        threat_id   TEXT NOT NULL REFERENCES threats(id) ON DELETE CASCADE,
        name        TEXT NOT NULL,
        versions    TEXT DEFAULT '[]',
        ecosystem   TEXT DEFAULT 'npm',
        pkg_type    TEXT DEFAULT 'compromised'
      );

      CREATE TABLE IF NOT EXISTS detections (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        threat_id   TEXT NOT NULL REFERENCES threats(id) ON DELETE CASCADE,
        code        TEXT NOT NULL,
        patterns    TEXT DEFAULT '[]',
        created_at  TEXT DEFAULT (datetime('now')),
        version     INTEGER DEFAULT 1
      );

      CREATE TABLE IF NOT EXISTS workflows (
        id             INTEGER PRIMARY KEY AUTOINCREMENT,
        threat_id      TEXT NOT NULL REFERENCES threats(id) ON DELETE CASCADE,
        filename       TEXT NOT NULL,
        content        TEXT NOT NULL,
        workflow_type  TEXT DEFAULT 'scan',
        distributed_to TEXT DEFAULT '[]',
        created_at     TEXT DEFAULT (datetime('now')),
        version        INTEGER DEFAULT 1
      );

      CREATE TABLE IF NOT EXISTS repositories (
        id          TEXT PRIMARY KEY,
        name        TEXT NOT NULL,
        path        TEXT NOT NULL UNIQUE,
        machine_id  TEXT,
        ecosystem   TEXT DEFAULT 'npm',
        last_scan   TEXT,
        scan_status TEXT DEFAULT 'unknown',
        watch_enabled INTEGER DEFAULT 0,
        created_at  TEXT DEFAULT (datetime('now'))
      );

      CREATE TABLE IF NOT EXISTS machines (
        id          TEXT PRIMARY KEY,
        name        TEXT NOT NULL,
        hostname    TEXT,
        os          TEXT,
        last_seen   TEXT DEFAULT (datetime('now'))
      );

      CREATE TABLE IF NOT EXISTS scans (
        id           INTEGER PRIMARY KEY AUTOINCREMENT,
        repo_id      TEXT REFERENCES repositories(id) ON DELETE SET NULL,
        threat_id    TEXT REFERENCES threats(id) ON DELETE SET NULL,
        trigger_type TEXT CHECK(trigger_type IN ('auto','manual','schedule')) DEFAULT 'manual',
        status       TEXT CHECK(status IN ('running','completed','failed')) DEFAULT 'running',
        findings     TEXT DEFAULT '[]',
        summary      TEXT,
        started_at   TEXT DEFAULT (datetime('now')),
        completed_at TEXT
      );

      CREATE TABLE IF NOT EXISTS remediations (
        id           INTEGER PRIMARY KEY AUTOINCREMENT,
        scan_id      INTEGER REFERENCES scans(id) ON DELETE SET NULL,
        threat_id    TEXT REFERENCES threats(id) ON DELETE SET NULL,
        repo_id      TEXT REFERENCES repositories(id) ON DELETE SET NULL,
        action       TEXT NOT NULL,
        patches      TEXT DEFAULT '[]',
        status       TEXT CHECK(status IN ('proposed','approved','applied','rejected','rolled_back')) DEFAULT 'proposed',
        auto_applied INTEGER DEFAULT 0,
        proposed_at  TEXT DEFAULT (datetime('now')),
        applied_at   TEXT,
        applied_by   TEXT
      );

      CREATE TABLE IF NOT EXISTS sources (
        id            TEXT PRIMARY KEY,
        name          TEXT NOT NULL,
        source_type   TEXT DEFAULT 'rss',
        url           TEXT NOT NULL,
        trusted       INTEGER DEFAULT 0,
        poll_interval INTEGER DEFAULT 3600,
        enabled       INTEGER DEFAULT 1,
        ecosystem_filter TEXT DEFAULT '[]',
        api_key_ref   TEXT,
        last_poll     TEXT,
        last_error    TEXT,
        created_at    TEXT DEFAULT (datetime('now'))
      );

      CREATE TABLE IF NOT EXISTS audit_log (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        action      TEXT NOT NULL,
        entity_type TEXT,
        entity_id   TEXT,
        details     TEXT,
        created_at  TEXT DEFAULT (datetime('now'))
      );

      CREATE INDEX IF NOT EXISTS idx_threats_status ON threats(status);
      CREATE INDEX IF NOT EXISTS idx_threats_severity ON threats(severity);
      CREATE INDEX IF NOT EXISTS idx_scans_repo ON scans(repo_id);
      CREATE INDEX IF NOT EXISTS idx_scans_status ON scans(status);
      CREATE INDEX IF NOT EXISTS idx_remediations_status ON remediations(status);
      CREATE INDEX IF NOT EXISTS idx_audit_entity ON audit_log(entity_type, entity_id);
    `);
  }

  // ─── Threats ───────────────────────────────────────────────────────────

  addThreat(threat) {
    const stmt = this.db.prepare(`
      INSERT OR REPLACE INTO threats (id, name, description, severity, status, cve, ecosystems, 
        attack_vector, source_name, source_url, source_trusted, raw_advisory, discovered_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))
    `);

    stmt.run(
      threat.id, threat.name, threat.description || '', threat.severity || 'medium',
      threat.status || 'detected', threat.cve || null,
      JSON.stringify(threat.ecosystems || ['npm']),
      threat.attackVector || null, threat.sourceName || null,
      threat.sourceUrl || null, threat.sourceTrusted ? 1 : 0,
      threat.rawAdvisory || null, threat.discoveredAt || new Date().toISOString()
    );

    // Add affected packages
    if (threat.affectedPackages && threat.affectedPackages.length) {
      const pkgStmt = this.db.prepare(`
        INSERT INTO threat_packages (threat_id, name, versions, ecosystem, pkg_type)
        VALUES (?, ?, ?, ?, ?)
      `);
      for (const pkg of threat.affectedPackages) {
        pkgStmt.run(
          threat.id, pkg.name,
          JSON.stringify(pkg.versions || []),
          pkg.ecosystem || 'npm',
          pkg.type || 'compromised'
        );
      }
    }

    this.audit('threat_added', 'threat', threat.id, { name: threat.name, severity: threat.severity });
    return threat.id;
  }

  getThreat(id) {
    const threat = this.db.prepare('SELECT * FROM threats WHERE id = ?').get(id);
    if (!threat) return null;

    threat.ecosystems = JSON.parse(threat.ecosystems || '[]');
    threat.affectedPackages = this.db.prepare('SELECT * FROM threat_packages WHERE threat_id = ?').all(id);
    threat.affectedPackages.forEach(p => p.versions = JSON.parse(p.versions || '[]'));
    threat.detections = this.db.prepare('SELECT * FROM detections WHERE threat_id = ? ORDER BY version DESC').all(id);
    threat.workflows = this.db.prepare('SELECT * FROM workflows WHERE threat_id = ? ORDER BY version DESC').all(id);

    return threat;
  }

  listThreats(filter = {}) {
    let sql = 'SELECT * FROM threats WHERE 1=1';
    const params = [];

    if (filter.status) {
      sql += ' AND status = ?';
      params.push(filter.status);
    }
    if (filter.severity) {
      sql += ' AND severity = ?';
      params.push(filter.severity);
    }
    if (filter.search) {
      sql += ' AND (name LIKE ? OR description LIKE ?)';
      params.push(`%${filter.search}%`, `%${filter.search}%`);
    }

    sql += ' ORDER BY created_at DESC';

    if (filter.limit) {
      sql += ' LIMIT ?';
      params.push(filter.limit);
    }

    const threats = this.db.prepare(sql).all(...params);
    threats.forEach(t => t.ecosystems = JSON.parse(t.ecosystems || '[]'));
    return threats;
  }

  verifyThreat(id) {
    this.db.prepare(`UPDATE threats SET status = 'verified', verified_at = datetime('now'), updated_at = datetime('now') WHERE id = ?`).run(id);
    this.audit('threat_verified', 'threat', id);
  }

  updateThreatStatus(id, status) {
    this.db.prepare(`UPDATE threats SET status = ?, updated_at = datetime('now') WHERE id = ?`).run(status, id);
    this.audit('threat_status_changed', 'threat', id, { status });
  }

  threatExists(id) {
    return !!this.db.prepare('SELECT 1 FROM threats WHERE id = ?').get(id);
  }

  findThreatByPackage(packageName) {
    const pkgs = this.db.prepare('SELECT threat_id FROM threat_packages WHERE name = ?').all(packageName);
    return pkgs.map(p => this.getThreat(p.threat_id)).filter(Boolean);
  }

  // ─── Detections ────────────────────────────────────────────────────────

  addDetection(threatId, code, patterns = []) {
    const maxVersion = this.db.prepare('SELECT MAX(version) as v FROM detections WHERE threat_id = ?').get(threatId);
    const version = (maxVersion?.v || 0) + 1;

    const result = this.db.prepare(`
      INSERT INTO detections (threat_id, code, patterns, version) VALUES (?, ?, ?, ?)
    `).run(threatId, code, JSON.stringify(patterns), version);

    this.audit('detection_added', 'detection', result.lastInsertRowid.toString(), { threatId, version });
    return result.lastInsertRowid;
  }

  getLatestDetections() {
    return this.db.prepare(`
      SELECT d.*, t.name as threat_name, t.severity as threat_severity
      FROM detections d
      JOIN threats t ON d.threat_id = t.id
      WHERE t.status IN ('detected', 'verified')
      AND d.version = (SELECT MAX(version) FROM detections WHERE threat_id = d.threat_id)
      ORDER BY t.severity DESC
    `).all();
  }

  // ─── Workflows ─────────────────────────────────────────────────────────

  addWorkflow(threatId, filename, content, type = 'scan') {
    const maxVersion = this.db.prepare('SELECT MAX(version) as v FROM workflows WHERE threat_id = ? AND workflow_type = ?').get(threatId, type);
    const version = (maxVersion?.v || 0) + 1;

    const result = this.db.prepare(`
      INSERT INTO workflows (threat_id, filename, content, workflow_type, version) VALUES (?, ?, ?, ?, ?)
    `).run(threatId, filename, content, type, version);

    this.audit('workflow_added', 'workflow', result.lastInsertRowid.toString(), { threatId, filename, type });
    return result.lastInsertRowid;
  }

  getWorkflowsForThreat(threatId) {
    return this.db.prepare('SELECT * FROM workflows WHERE threat_id = ? ORDER BY version DESC').all(threatId);
  }

  getAllLatestWorkflows() {
    return this.db.prepare(`
      SELECT w.*, t.name as threat_name
      FROM workflows w
      JOIN threats t ON w.threat_id = t.id
      WHERE w.version = (SELECT MAX(version) FROM workflows WHERE threat_id = w.threat_id AND workflow_type = w.workflow_type)
      ORDER BY w.created_at DESC
    `).all();
  }

  markWorkflowDistributed(workflowId, repoId) {
    const wf = this.db.prepare('SELECT distributed_to FROM workflows WHERE id = ?').get(workflowId);
    if (!wf) return;
    const list = JSON.parse(wf.distributed_to || '[]');
    if (!list.includes(repoId)) {
      list.push(repoId);
      this.db.prepare('UPDATE workflows SET distributed_to = ? WHERE id = ?').run(JSON.stringify(list), workflowId);
    }
  }

  // ─── Repositories ──────────────────────────────────────────────────────

  addRepo(repo) {
    this.db.prepare(`
      INSERT OR REPLACE INTO repositories (id, name, path, machine_id, ecosystem) VALUES (?, ?, ?, ?, ?)
    `).run(repo.id, repo.name, repo.path, repo.machineId || null, repo.ecosystem || 'npm');

    this.audit('repo_added', 'repository', repo.id, { name: repo.name, path: repo.path });
    return repo.id;
  }

  getRepo(id) {
    return this.db.prepare('SELECT * FROM repositories WHERE id = ?').get(id);
  }

  getRepoByPath(repoPath) {
    return this.db.prepare('SELECT * FROM repositories WHERE path = ?').get(repoPath);
  }

  listRepos(machineId = null) {
    if (machineId) {
      return this.db.prepare('SELECT * FROM repositories WHERE machine_id = ? ORDER BY name').all(machineId);
    }
    return this.db.prepare('SELECT * FROM repositories ORDER BY name').all();
  }

  removeRepo(id) {
    this.db.prepare('DELETE FROM repositories WHERE id = ?').run(id);
    this.audit('repo_removed', 'repository', id);
  }

  updateRepoScan(id, status, findings) {
    this.db.prepare(`
      UPDATE repositories SET last_scan = datetime('now'), scan_status = ? WHERE id = ?
    `).run(status, id);
  }

  // ─── Scans ─────────────────────────────────────────────────────────────

  startScan(repoId, threatId = null, triggerType = 'manual') {
    const result = this.db.prepare(`
      INSERT INTO scans (repo_id, threat_id, trigger_type, status) VALUES (?, ?, ?, 'running')
    `).run(repoId, threatId, triggerType);

    this.audit('scan_started', 'scan', result.lastInsertRowid.toString(), { repoId, threatId, triggerType });
    return result.lastInsertRowid;
  }

  completeScan(scanId, findings, summary = '') {
    this.db.prepare(`
      UPDATE scans SET status = 'completed', findings = ?, summary = ?, completed_at = datetime('now') WHERE id = ?
    `).run(JSON.stringify(findings), summary, scanId);

    this.audit('scan_completed', 'scan', scanId.toString(), { findingCount: findings.length });
  }

  failScan(scanId, error) {
    this.db.prepare(`
      UPDATE scans SET status = 'failed', summary = ?, completed_at = datetime('now') WHERE id = ?
    `).run(error, scanId);
  }

  getScan(id) {
    const scan = this.db.prepare('SELECT * FROM scans WHERE id = ?').get(id);
    if (scan) scan.findings = JSON.parse(scan.findings || '[]');
    return scan;
  }

  listScans(filter = {}) {
    let sql = 'SELECT * FROM scans WHERE 1=1';
    const params = [];

    if (filter.repoId) {
      sql += ' AND repo_id = ?';
      params.push(filter.repoId);
    }
    if (filter.status) {
      sql += ' AND status = ?';
      params.push(filter.status);
    }

    sql += ' ORDER BY started_at DESC';
    if (filter.limit) {
      sql += ' LIMIT ?';
      params.push(filter.limit);
    }

    const scans = this.db.prepare(sql).all(...params);
    scans.forEach(s => s.findings = JSON.parse(s.findings || '[]'));
    return scans;
  }

  // ─── Remediations ─────────────────────────────────────────────────────

  proposeRemediation(scanId, threatId, repoId, action, patches = []) {
    const result = this.db.prepare(`
      INSERT INTO remediations (scan_id, threat_id, repo_id, action, patches) VALUES (?, ?, ?, ?, ?)
    `).run(scanId, threatId, repoId, action, JSON.stringify(patches));

    this.audit('remediation_proposed', 'remediation', result.lastInsertRowid.toString(), { action });
    return result.lastInsertRowid;
  }

  approveRemediation(id) {
    this.db.prepare(`UPDATE remediations SET status = 'approved' WHERE id = ?`).run(id);
    this.audit('remediation_approved', 'remediation', id.toString());
  }

  applyRemediation(id, appliedBy = 'user') {
    this.db.prepare(`
      UPDATE remediations SET status = 'applied', applied_at = datetime('now'), applied_by = ? WHERE id = ?
    `).run(appliedBy, id);
    this.audit('remediation_applied', 'remediation', id.toString(), { appliedBy });
  }

  rejectRemediation(id) {
    this.db.prepare(`UPDATE remediations SET status = 'rejected' WHERE id = ?`).run(id);
    this.audit('remediation_rejected', 'remediation', id.toString());
  }

  getRemediation(id) {
    const rem = this.db.prepare('SELECT * FROM remediations WHERE id = ?').get(id);
    if (rem) rem.patches = JSON.parse(rem.patches || '[]');
    return rem;
  }

  listRemediations(filter = {}) {
    let sql = 'SELECT * FROM remediations WHERE 1=1';
    const params = [];
    if (filter.status) { sql += ' AND status = ?'; params.push(filter.status); }
    if (filter.repoId) { sql += ' AND repo_id = ?'; params.push(filter.repoId); }
    sql += ' ORDER BY proposed_at DESC';
    if (filter.limit) { sql += ' LIMIT ?'; params.push(filter.limit); }

    const rems = this.db.prepare(sql).all(...params);
    rems.forEach(r => r.patches = JSON.parse(r.patches || '[]'));
    return rems;
  }

  // ─── Sources ──────────────────────────────────────────────────────────

  addSource(source) {
    this.db.prepare(`
      INSERT OR REPLACE INTO sources (id, name, source_type, url, trusted, poll_interval, enabled, ecosystem_filter, api_key_ref)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      source.id, source.name, source.type || 'rss', source.url,
      source.trusted ? 1 : 0, source.pollInterval || 3600,
      source.enabled !== false ? 1 : 0,
      JSON.stringify(source.ecosystemFilter || []),
      source.apiKeyRef || null
    );
    this.audit('source_added', 'source', source.id, { name: source.name });
  }

  listSources(enabledOnly = false) {
    const sql = enabledOnly
      ? 'SELECT * FROM sources WHERE enabled = 1 ORDER BY trusted DESC, name'
      : 'SELECT * FROM sources ORDER BY trusted DESC, name';
    const sources = this.db.prepare(sql).all();
    sources.forEach(s => s.ecosystemFilter = JSON.parse(s.ecosystem_filter || '[]'));
    return sources;
  }

  updateSourcePoll(id, error = null) {
    this.db.prepare(`UPDATE sources SET last_poll = datetime('now'), last_error = ? WHERE id = ?`).run(error, id);
  }

  // ─── Machines ─────────────────────────────────────────────────────────

  registerMachine(machine) {
    this.db.prepare(`
      INSERT OR REPLACE INTO machines (id, name, hostname, os, last_seen) VALUES (?, ?, ?, ?, datetime('now'))
    `).run(machine.id, machine.name, machine.hostname || '', machine.os || process.platform);
  }

  getMachine(id) {
    return this.db.prepare('SELECT * FROM machines WHERE id = ?').get(id);
  }

  // ─── Audit Log ────────────────────────────────────────────────────────

  audit(action, entityType, entityId, details = null) {
    this.db.prepare(`
      INSERT INTO audit_log (action, entity_type, entity_id, details) VALUES (?, ?, ?, ?)
    `).run(action, entityType, entityId, details ? JSON.stringify(details) : null);
  }

  getAuditLog(filter = {}) {
    let sql = 'SELECT * FROM audit_log WHERE 1=1';
    const params = [];
    if (filter.entityType) { sql += ' AND entity_type = ?'; params.push(filter.entityType); }
    if (filter.entityId) { sql += ' AND entity_id = ?'; params.push(filter.entityId); }
    sql += ' ORDER BY created_at DESC';
    if (filter.limit) { sql += ' LIMIT ?'; params.push(filter.limit); }
    return this.db.prepare(sql).all(...params);
  }

  // ─── Stats ────────────────────────────────────────────────────────────

  getStats() {
    return {
      threats: {
        total: this.db.prepare('SELECT COUNT(*) as c FROM threats').get().c,
        active: this.db.prepare("SELECT COUNT(*) as c FROM threats WHERE status IN ('detected','verified')").get().c,
        critical: this.db.prepare("SELECT COUNT(*) as c FROM threats WHERE severity = 'critical'").get().c,
      },
      repos: {
        total: this.db.prepare('SELECT COUNT(*) as c FROM repositories').get().c,
        clean: this.db.prepare("SELECT COUNT(*) as c FROM repositories WHERE scan_status = 'clean'").get().c,
      },
      scans: {
        total: this.db.prepare('SELECT COUNT(*) as c FROM scans').get().c,
        today: this.db.prepare("SELECT COUNT(*) as c FROM scans WHERE started_at >= date('now')").get().c,
      },
      remediations: {
        pending: this.db.prepare("SELECT COUNT(*) as c FROM remediations WHERE status = 'proposed'").get().c,
        applied: this.db.prepare("SELECT COUNT(*) as c FROM remediations WHERE status = 'applied'").get().c,
      },
    };
  }

  // ─── Lifecycle ────────────────────────────────────────────────────────

  close() {
    if (this.db) {
      this.db.close();
      this.db = null;
    }
  }
}

module.exports = FabiusDB;
