apps/backend/src/database/index.ts

/**
 * Database initialization
 *
 * Primary: Prisma/PostgreSQL when DATABASE_URL is set
 * Fallback: sql.js (SQLite, in-memory) for legacy synchronous consumers
 */

import fs from 'fs';
import path from 'path';
import initSqlJs from 'sql.js';
import { prisma, checkPrismaConnection } from './prisma.js';

type SqliteDatabase = import('sql.js').Database;

// Legacy interface for backward compatibility (synchronous API)
export interface DatabaseStatement<P = any[], R = any> {
  all: (...params: P extends any[] ? P : any[]) => R[];
  get: (...params: P extends any[] ? P : any[]) => R | undefined;
  run: (...params: P extends any[] ? P : any[]) => { changes: number; lastInsertRowid: number | bigint };
  free: () => void;
}

export interface Database {
  prepare: <P = any[], R = any>(sql: string) => DatabaseStatement<P, R>;
  run: (sql: string, params?: any[]) => { changes: number; lastInsertRowid: number | bigint };
  close: () => void;
}

let isInitialized = false;
let sqliteDb: SqliteDatabase | null = null;
let sqliteReady = false;
let prismaReady = false;

const legacyTableBootstrap = `
CREATE TABLE IF NOT EXISTS security_search_templates (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  query TEXT NOT NULL,
  severity TEXT NOT NULL,
  timeframe TEXT NOT NULL,
  sources TEXT NOT NULL,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS security_search_history (
  id TEXT PRIMARY KEY,
  query TEXT NOT NULL,
  severity TEXT NOT NULL,
  timeframe TEXT NOT NULL,
  sources TEXT NOT NULL,
  results_count INTEGER NOT NULL,
  latency_ms INTEGER NOT NULL,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS security_activity_events (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  category TEXT NOT NULL,
  severity TEXT NOT NULL,
  source TEXT NOT NULL,
  rule TEXT,
  channel TEXT NOT NULL,
  payload TEXT,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP,
  acknowledged INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS widget_permissions (
  widget_id TEXT,
  resource_type TEXT NOT NULL,
  access_level TEXT NOT NULL,
  override INTEGER DEFAULT 0,
  PRIMARY KEY (widget_id, resource_type)
);

CREATE TABLE IF NOT EXISTS vector_documents (
  id TEXT PRIMARY KEY,
  content TEXT NOT NULL,
  embedding TEXT,
  metadata TEXT,
  namespace TEXT DEFAULT 'default',
  userId TEXT DEFAULT 'system',
  orgId TEXT DEFAULT 'default',
  createdAt TEXT DEFAULT CURRENT_TIMESTAMP,
  updatedAt TEXT DEFAULT CURRENT_TIMESTAMP
);`;

/**
 * Initialize database connection(s)
 * - Connect Prisma when DATABASE_URL is present
 * - Always prepare a lightweight SQLite (sql.js) fallback for legacy sync consumers
 */
export async function initializeDatabase(): Promise<void> {
  if (isInitialized) return;

  // 1) Try Prisma/Postgres first
  try {
    const prismaOk = await checkPrismaConnection();
    if (prismaOk) {
      prismaReady = true;
      // Ensure required legacy tables exist for raw queries
      await prisma.$executeRawUnsafe(`
        CREATE TABLE IF NOT EXISTS "security_search_templates" (
          "id" TEXT PRIMARY KEY,
          "name" TEXT NOT NULL,
          "description" TEXT NOT NULL,
          "query" TEXT NOT NULL,
          "severity" TEXT NOT NULL,
          "timeframe" TEXT NOT NULL,
          "sources" JSONB NOT NULL DEFAULT '[]'::jsonb,
          "created_at" TIMESTAMPTZ DEFAULT NOW()
        );
      `);
      await prisma.$executeRawUnsafe(`
        CREATE TABLE IF NOT EXISTS "security_search_history" (
          "id" TEXT PRIMARY KEY,
          "query" TEXT NOT NULL,
          "severity" TEXT NOT NULL,
          "timeframe" TEXT NOT NULL,
          "sources" JSONB NOT NULL DEFAULT '[]'::jsonb,
          "results_count" INTEGER NOT NULL,
          "latency_ms" INTEGER NOT NULL,
          "created_at" TIMESTAMPTZ DEFAULT NOW()
        );
      `);
      await prisma.$executeRawUnsafe(`
        CREATE TABLE IF NOT EXISTS "security_activity_events" (
          "id" TEXT PRIMARY KEY,
          "title" TEXT NOT NULL,
          "description" TEXT NOT NULL,
          "category" TEXT NOT NULL,
          "severity" TEXT NOT NULL,
          "source" TEXT NOT NULL,
          "rule" TEXT,
          "channel" TEXT NOT NULL,
          "payload" JSONB,
          "created_at" TIMESTAMPTZ DEFAULT NOW(),
          "acknowledged" BOOLEAN DEFAULT FALSE
        );
      `);
      await prisma.$executeRawUnsafe(`
        CREATE TABLE IF NOT EXISTS "widget_permissions" (
          "widget_id" TEXT,
          "resource_type" TEXT NOT NULL,
          "access_level" TEXT NOT NULL,
          "override" BOOLEAN DEFAULT FALSE,
          CONSTRAINT widget_permissions_pk PRIMARY KEY ("widget_id", "resource_type")
        );
      `);
      await prisma.$executeRawUnsafe(`
        CREATE TABLE IF NOT EXISTS "vector_documents" (
          "id" TEXT PRIMARY KEY,
          "content" TEXT NOT NULL,
          "embedding" JSONB,
          "metadata" JSONB,
          "namespace" TEXT DEFAULT 'default',
          "userId" TEXT DEFAULT 'system',
          "orgId" TEXT DEFAULT 'default',
          "createdAt" TIMESTAMPTZ DEFAULT NOW(),
          "updatedAt" TIMESTAMPTZ DEFAULT NOW()
        );
      `);
      console.log('✅ Prisma database connected');
    }
  } catch (error) {
    console.warn('⚠️ Prisma connection failed, continuing with SQLite fallback', error);
  }

  // 2) Always prepare SQLite (sql.js) fallback for synchronous consumers
  try {
    const SQL = await initSqlJs();
    sqliteDb = new SQL.Database();

    // Initialize SQLite with inlined schema (avoids fs/path issues in Docker/Bundled envs)
    const fallbackSchema = `
-- Memory (CMA) tables
CREATE TABLE IF NOT EXISTS memory_entities (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  org_id          TEXT NOT NULL,
  user_id         TEXT,
  entity_type     TEXT NOT NULL,
  content         TEXT NOT NULL,
  importance      INTEGER NOT NULL DEFAULT 3,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS memory_relations (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  org_id          TEXT NOT NULL,
  source_id       INTEGER NOT NULL REFERENCES memory_entities(id),
  target_id       INTEGER NOT NULL REFERENCES memory_entities(id),
  relation_type   TEXT NOT NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS memory_tags (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  entity_id       INTEGER NOT NULL REFERENCES memory_entities(id),
  tag             TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_memory_entities_org ON memory_entities(org_id);
CREATE INDEX IF NOT EXISTS idx_memory_entities_user ON memory_entities(user_id);
CREATE INDEX IF NOT EXISTS idx_memory_tags_entity ON memory_tags(entity_id);
CREATE INDEX IF NOT EXISTS idx_memory_tags_tag ON memory_tags(tag);

-- SRAG tables
CREATE TABLE IF NOT EXISTS raw_documents (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  org_id        TEXT NOT NULL,
  source_type   TEXT NOT NULL,
  source_path   TEXT NOT NULL,
  content       TEXT NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS structured_facts (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  org_id        TEXT NOT NULL,
  doc_id        INTEGER REFERENCES raw_documents(id),
  fact_type     TEXT NOT NULL,
  json_payload  TEXT NOT NULL,
  occurred_at   DATETIME,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_raw_documents_org ON raw_documents(org_id);
CREATE INDEX IF NOT EXISTS idx_structured_facts_org ON structured_facts(org_id);
CREATE INDEX IF NOT EXISTS idx_structured_facts_type ON structured_facts(fact_type);

-- Evolution Agent tables
CREATE TABLE IF NOT EXISTS agent_prompts (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  agent_id      TEXT NOT NULL,
  version       INTEGER NOT NULL,
  prompt_text   TEXT NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by    TEXT NOT NULL DEFAULT 'evolution-agent'
);

CREATE TABLE IF NOT EXISTS agent_runs (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  agent_id        TEXT NOT NULL,
  prompt_version  INTEGER NOT NULL,
  input_summary   TEXT,
  output_summary  TEXT,
  kpi_name        TEXT,
  kpi_delta       REAL,
  run_context     TEXT,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_agent_prompts_agent ON agent_prompts(agent_id, version);
CREATE INDEX IF NOT EXISTS idx_agent_runs_agent ON agent_runs(agent_id);

-- PAL tables
CREATE TABLE IF NOT EXISTS pal_user_profiles (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id       TEXT NOT NULL,
  org_id        TEXT NOT NULL,
  preference_tone TEXT NOT NULL DEFAULT 'neutral',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS pal_focus_windows (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id       TEXT NOT NULL,
  org_id        TEXT NOT NULL,
  weekday       INTEGER NOT NULL,
  start_hour    INTEGER NOT NULL,
  end_hour      INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS pal_events (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id       TEXT NOT NULL,
  org_id        TEXT NOT NULL,
  event_type    TEXT NOT NULL,
  payload       TEXT NOT NULL,
  detected_stress_level TEXT,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_pal_profiles_user ON pal_user_profiles(user_id, org_id);
CREATE INDEX IF NOT EXISTS idx_pal_focus_windows_user ON pal_focus_windows(user_id);
CREATE INDEX IF NOT EXISTS idx_pal_events_user ON pal_events(user_id, org_id);

-- Security Intelligence tables
CREATE TABLE IF NOT EXISTS security_search_templates (
  id            TEXT PRIMARY KEY,
  name          TEXT NOT NULL,
  description   TEXT NOT NULL,
  query         TEXT NOT NULL,
  severity      TEXT NOT NULL DEFAULT 'all',
  timeframe     TEXT NOT NULL DEFAULT '24h',
  sources       TEXT NOT NULL DEFAULT '[]',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS security_search_history (
  id             TEXT PRIMARY KEY,
  query          TEXT NOT NULL,
  severity       TEXT NOT NULL,
  timeframe      TEXT NOT NULL,
  sources        TEXT NOT NULL,
  results_count  INTEGER NOT NULL DEFAULT 0,
  latency_ms     INTEGER NOT NULL DEFAULT 0,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_security_search_history_created ON security_search_history(created_at DESC);

CREATE TABLE IF NOT EXISTS security_activity_events (
  id             TEXT PRIMARY KEY,
  title          TEXT NOT NULL,
  description    TEXT NOT NULL,
  category       TEXT NOT NULL,
  severity       TEXT NOT NULL,
  source         TEXT NOT NULL,
  rule           TEXT,
  channel        TEXT NOT NULL DEFAULT 'SSE',
  payload        TEXT,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  acknowledged   INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_security_activity_events_created ON security_activity_events(created_at DESC);

CREATE TABLE IF NOT EXISTS widget_permissions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  widget_id TEXT NOT NULL,
  resource_type TEXT NOT NULL,
  access_level TEXT NOT NULL CHECK (access_level IN ('none', 'read', 'write')),
  override BOOLEAN DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(widget_id, resource_type)
);

-- COGNITIVE MEMORY LAYER
CREATE TABLE IF NOT EXISTS mcp_query_patterns (
  id TEXT PRIMARY KEY,
  widget_id TEXT NOT NULL,
  query_type TEXT NOT NULL,
  query_signature TEXT NOT NULL,
  source_used TEXT NOT NULL,
  latency_ms INTEGER NOT NULL,
  result_size INTEGER,
  success BOOLEAN NOT NULL,
  user_context TEXT,
  timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_query_patterns_widget 
  ON mcp_query_patterns(widget_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_query_patterns_signature 
  ON mcp_query_patterns(query_signature);

CREATE TABLE IF NOT EXISTS mcp_failure_memory (
  id TEXT PRIMARY KEY,
  source_name TEXT NOT NULL,
  error_type TEXT NOT NULL,
  error_message TEXT,
  error_context TEXT,
  recovery_action TEXT,
  recovery_success BOOLEAN,
  recovery_time_ms INTEGER,
  occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_failure_memory_source 
  ON mcp_failure_memory(source_name, occurred_at DESC);

CREATE TABLE IF NOT EXISTS mcp_source_health (
  id TEXT PRIMARY KEY,
  source_name TEXT NOT NULL,
  health_score REAL NOT NULL,
  latency_p50 REAL,
  latency_p95 REAL,
  latency_p99 REAL,
  success_rate REAL NOT NULL,
  request_count INTEGER NOT NULL,
  error_count INTEGER NOT NULL,
  timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_source_health_source 
  ON mcp_source_health(source_name, timestamp DESC);

CREATE TABLE IF NOT EXISTS mcp_decision_log (
  id TEXT PRIMARY KEY,
  query_intent TEXT NOT NULL,
  selected_source TEXT NOT NULL,
  decision_confidence REAL NOT NULL,
  actual_latency_ms INTEGER,
  was_optimal BOOLEAN,
  timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS mcp_widget_patterns (
  id TEXT PRIMARY KEY,
  widget_id TEXT NOT NULL,
  pattern_type TEXT NOT NULL,
  pattern_data TEXT NOT NULL,
  occurrence_count INTEGER NOT NULL DEFAULT 1,
  confidence REAL NOT NULL,
  last_seen DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_widget_patterns_widget 
  ON mcp_widget_patterns(widget_id, confidence DESC);

-- PROJECT MEMORY LAYER
CREATE TABLE IF NOT EXISTS project_lifecycle_events (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  event_type    TEXT NOT NULL,
  status        TEXT NOT NULL,
  details       TEXT,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS project_features (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  name          TEXT NOT NULL,
  description   TEXT,
  status        TEXT NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS vector_documents (
  id            TEXT PRIMARY KEY,
  content       TEXT NOT NULL,
  embedding     TEXT,
  metadata      TEXT,
  namespace     TEXT DEFAULT 'default',
  "userId"      TEXT,
  "orgId"       TEXT,
  "createdAt"   DATETIME DEFAULT CURRENT_TIMESTAMP,
  "updatedAt"   DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_vector_documents_namespace ON vector_documents(namespace);
`;

    sqliteDb.run(fallbackSchema);
    sqliteDb.run(legacyTableBootstrap);
    sqliteReady = true;
  } catch (error) {
    console.error('❌ Failed to initialize SQLite fallback', error);
  }

  isInitialized = prismaReady || sqliteReady;
}

/**
 * Get synchronous legacy database (sql.js)
 * Falls back to an in-memory stub if initialization failed.
 */
export function getDatabase(): Database {
  if (!sqliteReady || !sqliteDb) {
    // Provide a harmless stub to avoid runtime crashes
    return {
      prepare: () => ({
        all: () => [],
        get: () => undefined,
        run: () => ({ changes: 0, lastInsertRowid: 0 }),
        free: () => undefined,
      }),
      run: () => ({ changes: 0, lastInsertRowid: 0 }),
      close: () => undefined,
    };
  }

  return {
    prepare: <P = any[], R = any>(sql: string): DatabaseStatement<P, R> => {
      const stmt = sqliteDb!.prepare(sql);
      return {
        all: (...params: any[]) => {
          stmt.bind(params);
          const rows: any[] = [];
          while (stmt.step()) {
            rows.push(stmt.getAsObject());
          }
          stmt.reset();
          return rows as R[];
        },
        get: (...params: any[]) => {
          stmt.bind(params);
          const hasRow = stmt.step();
          const row = hasRow ? stmt.getAsObject() : undefined;
          stmt.reset();
          return row as R | undefined;
        },
        run: (...params: any[]) => {
          stmt.bind(params);
          stmt.step();
          const info = { changes: sqliteDb!.getRowsModified(), lastInsertRowid: sqliteDb!.getRowsModified() };
          stmt.reset();
          return info;
        },
        free: () => stmt.free(),
      };
    },
    run: (sql: string, params?: any[]) => {
      sqliteDb!.run(sql, params);
      return { changes: sqliteDb!.getRowsModified(), lastInsertRowid: sqliteDb!.getRowsModified() };
    },
    close: () => sqliteDb!.close(),
  };
}

/**
 * Get raw sql.js Database for memory systems that need direct exec() access
 * This is needed for CognitiveMemory, PatternMemory, FailureMemory
 */
export function getSqlJsDatabase(): SqliteDatabase | null {
  return sqliteDb;
}

export async function closeDatabase(): Promise<void> {
  if (sqliteDb) {
    sqliteDb.close();
  }
  if (prismaReady) {
    await prisma.$disconnect();
  }
  isInitialized = false;
  sqliteReady = false;
  prismaReady = false;
}

export function isPrismaReady(): boolean {
  return prismaReady;
}

export function isSqliteReady(): boolean {
  return sqliteReady;
}

// Re-export prisma for convenience
export { prisma };