import sqlite3 from 'sqlite3';
import { open, Database } from 'sqlite';

let db: Database | null = null;

export async function getDbConnection() {
  if (!db) {
    db = await open({
      filename: './data/factory.db',
      driver: sqlite3.Database
    });
    await initDb();
  }
  return db;
}

async function initDb() {
  if (!db) return;
  
  // جدول المشاريع والأسلحة
  await db.exec(`
    CREATE TABLE IF NOT EXISTS projects (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT,
      type TEXT,
      current_code TEXT,
      last_score INTEGER,
      status TEXT DEFAULT 'dev'
    );
  `);

  // جدول المراجع (Knowledge Base)
  await db.exec(`
    CREATE TABLE IF NOT EXISTS malware_refs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      family_name TEXT,
      technique TEXT,
      detection_rules TEXT
    );
  `);

  // سجل الحلقة (Feedback Loop)
  await db.exec(`
    CREATE TABLE IF NOT EXISTS loop_logs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      project_id INTEGER,
      iteration INTEGER,
      criticism TEXT,
      score INTEGER,
      timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
    );
  `);
}