/**
 * Graph Database — SQLite for the Graph Agent architecture (P0)
 *
 * 表结构：
 *   system_agents     全局 / 模板 / 公司专属 agent 元数据（TEXT PRIMARY KEY = 目录名）
 *   org_graph_nodes    公司组织图节点（company 对某个 system_agent 的引用 + 角色名）
 *   org_graph_edges    公司组织图边（节点间的关系）
 *
 * P0 阶段：只加不改，不涉及任何现有执行路径。
 */

const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');

const DATA_DIR = path.resolve(__dirname, '../../data');
const DB_PATH = process.env.GRAPH_DB_PATH || path.join(DATA_DIR, 'graph.sqlite');

let db = null;

function getDb() {
  if (db) return db;

  if (!fs.existsSync(DATA_DIR)) {
    fs.mkdirSync(DATA_DIR, { recursive: true });
  }

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

  db.exec(`
    CREATE TABLE IF NOT EXISTS system_agents (
      id               TEXT PRIMARY KEY,
      name             TEXT NOT NULL,
      scope            TEXT NOT NULL CHECK (scope IN ('public','company')),
      owner_company_id TEXT,
      source_kind      TEXT NOT NULL CHECK (source_kind IN ('global_agent','template','company_hired')),
      legacy_path      TEXT NOT NULL,
      status           TEXT NOT NULL DEFAULT 'active',
      benchmark_score  REAL,
      created_at       TEXT NOT NULL,
      updated_at       TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS org_graph_nodes (
      id               INTEGER PRIMARY KEY AUTOINCREMENT,
      company_id       TEXT NOT NULL,
      system_agent_id  TEXT NOT NULL REFERENCES system_agents(id),
      role_name        TEXT NOT NULL,
      created_at       TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS org_graph_edges (
      id               INTEGER PRIMARY KEY AUTOINCREMENT,
      company_id       TEXT NOT NULL,
      from_node_id     INTEGER NOT NULL REFERENCES org_graph_nodes(id),
      to_node_id       INTEGER NOT NULL REFERENCES org_graph_nodes(id),
      relation_type    TEXT NOT NULL
    );
  `);

  return db;
}

module.exports = {
  getDb,
};