/**
 * Graph Database — SQLite for the Graph Agent architecture (P0 + P2)
 *
 * 表结构：
 *   system_agents      Agent 本体注册表（TEXT PRIMARY KEY = system/agents/ 下的目录名），
 *                      origin 分 builtin（仓库自带）/ created（create-agent 生成）两类
 *   company_agents     公司 ⇄ 本体的**雇佣关系**（28 批次 4）
 *   org_graph_nodes    公司组织图节点（company 对某个 system_agent 的引用 + 角色名）
 *   org_graph_edges    公司组织图**执行流**边（P2 起：condition + edge_kind，与 work_graph_edges 同构）
 *
 * 为什么 company_agents 不能复用 org_graph_nodes（28 §4.2 / §5.3）：
 *   后者是**执行流图**的节点（带 reports_to_node_id / 出入边 / authored_by），
 *   而 P3B 规定 work 层本体被雇时**不写** org_graph_nodes
 *   （判据见 company-service.decideOrgNodeRecording：图基础设施本体、work graph 骨架
 *   点名的本体、成型图上不在图里的角色，三类都只补 system_agents 不进 org 图）。
 *   也就是说 org_graph_nodes 天生覆盖不全「这家公司到底雇了谁」——拿它当成员表，
 *   查出来的名册会**少人**，而且少的正是跑得最多的那批 work 层本体。
 *   雇佣关系因此独立成表，org_graph_nodes 继续只管执行流。
 *
 * P2（27-p2-org-graph.md §3.2）的三处改造：
 *   1. org_graph_nodes 追加 status（软删除，供 fire 用）+ reports_to_node_id（汇报关系降级为节点属性）
 *   2. 全量唯一索引 idx_org_graph_nodes_company_role → 部分唯一索引 ..._active
 *      （fire 成 status='removed' 之后，同角色能重新 hire）
 *   3. org_graph_edges 从「汇报关系」重建为「执行流边」
 *
 * P3（27-p3-p4-work-graph-console.md §四.D.2.2，T-P3）的两处改造：
 *   1. org_graph_edges 追加可空 fail_reason —— 一个节点可以有多条 fail 出边，
 *      靠边上的 fail_reason 做细分路由（精确匹配优先，无匹配退到 fail_reason IS NULL 的兜底边）。
 *   2. 唯一索引 idx_org_graph_edges_dedupe_active 的键必须把 fail_reason 一起算进去。
 *      （R3-F2）原本以为「多条 fail 边分别指向不同 to_node_id，本来就不撞」——不成立：
 *      D.2.2 允许同一对 from/to 上并存「精确 fail 边 + 兜底边」（例：verify 失败因
 *      input_underspecified 退回 plan，其余未归类的 fail 也退回 plan），
 *      旧键把这两条视为同一条，insertEdge 的 ON CONFLICT DO NOTHING 会静默吞掉兜底边。
 *   注：原 R-16「归因保持 org node 粒度封装、org 层回退只退到 org node」的假设已被 **R-17.1 推翻**——
 *   跨层地址不落在边上（定义层写不出运行时的 work node），而是走 graph_run_nodes.fail_node_ref，
 *   理由见 graph-engine.js 的「跨层节点地址（R-17.1）」一节。本文件的 DDL 不因此改动。
 *
 * 本文件所有 DDL 都是幂等的，getDb() 可重复调用 / 进程可反复重启。
 */

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;

/** 表是否已有某列（SQLite 的 ALTER TABLE ADD COLUMN 对已存在的列会直接报错） */
function hasColumn(database, table, column) {
  return database.pragma(`table_info(${table})`).some((c) => c.name === column);
}

/**
 * 存量库迁移。新建库走上面的 CREATE TABLE 就已经是目标形态，这里全部是 no-op。
 */
function migrate(database) {
  // 1. org_graph_nodes 追加两列
  if (!hasColumn(database, 'org_graph_nodes', 'status')) {
    database.exec(`ALTER TABLE org_graph_nodes ADD COLUMN status TEXT NOT NULL DEFAULT 'active'`);
  }
  if (!hasColumn(database, 'org_graph_nodes', 'reports_to_node_id')) {
    // 默认值必须是 NULL，SQLite 才允许 ADD COLUMN 带 REFERENCES
    database.exec(
      `ALTER TABLE org_graph_nodes ADD COLUMN reports_to_node_id INTEGER REFERENCES org_graph_nodes(id)`
    );
  }

  // 2. P1 建的全量唯一索引挡住了「fire 后重新 hire 同角色」，换成只约束 active 行的部分索引
  //    注意 graph-service.js 的 ensureOrgGraphNodeIndex() 也必须同步改成建这个部分索引，
  //    否则这里 DROP 掉的索引会被它重新建出来。
  database.exec(`
    DROP INDEX IF EXISTS idx_org_graph_nodes_company_role;
    CREATE UNIQUE INDEX IF NOT EXISTS idx_org_graph_nodes_company_role_active
      ON org_graph_nodes(company_id, role_name) WHERE status = 'active';
  `);

  // 3. 老的 org_graph_edges（relation_type 汇报关系）从建表至今零数据，直接 DROP 重建，无迁移负担
  if (hasColumn(database, 'org_graph_edges', 'relation_type')) {
    database.exec(`DROP TABLE org_graph_edges`);
    createEdgesTable(database);
  }

  // 4. P3 T-P3：condition='fail' 的细分路由键（27-p3 §四.D.2.2）。
  //    可空，旧行为 NULL——NULL 正好就是「兜底边」语义，存量数据不受影响。
  if (!hasColumn(database, 'org_graph_edges', 'fail_reason')) {
    database.exec(`ALTER TABLE org_graph_edges ADD COLUMN fail_reason TEXT`);
  }

  // 4b. P3B W7-fix：节点的来源。
  //     判「这家公司的 org graph 成型了没有」原先看的是**边数**，理由是
  //     「节点 hire() 自己就会造，拿节点当信号会废掉『连雇两个人第二个不入图』」。
  //     但真跑里 orggraph-planning 裁出过一张**零条边的单节点图**，边数代理当场失效：
  //     三条判据全落空 → workgraph-planning 现雇的 work 层本体被写进 org_graph_nodes，
  //     下一次下达任务时它会被 advanceOne 当成 org 节点执行。
  //     所以把「谁写的」显式记下来，不再靠边数猜。
  //     'planner' = org-graph-service.writeGraph（规划 agent 提交的整张图）
  //     'hire'    = company-service.hire 顺手记的一行
  //     NULL      = 本列之前的存量行，判定时回落到边数代理，存量行为不变
  if (!hasColumn(database, 'org_graph_nodes', 'authored_by')) {
    database.exec(`ALTER TABLE org_graph_nodes ADD COLUMN authored_by TEXT`);
  }

  // 5. P3 R3-F2：去重键补上 fail_reason，否则同一对 from/to 上的「精确 fail 边 + 兜底边」
  //    只能活一条（insertEdge 用 ON CONFLICT DO NOTHING，被吞的那条不报错）。
  //
  //    键里写 COALESCE(fail_reason,'') 而不是裸列：SQLite 的 UNIQUE 索引里多个 NULL 互不相等，
  //    裸列会让「两条都是兜底边（fail_reason 都是 NULL）的完全重复边」不再被去重——
  //    修一个吞边反而丢掉去重能力。COALESCE 把 NULL 折成 ''，NULL 之间照旧互相冲突。
  //    org-graph-service.insertEdge 的 ON CONFLICT 目标必须原样带上这个表达式和 WHERE。
  //
  //    幂等：存量库上是 P2 的四列索引（键更窄 = 约束更严），先 DROP 再按新定义建；
  //    新键是旧键的超集，约束只会更松，已有数据一定建得起来。第二遍跑时 sqlite_master
  //    里的定义已含 fail_reason，跳过 DROP，CREATE 走 IF NOT EXISTS，不重建索引。
  const dedupeIdx = database
    .prepare(`SELECT sql FROM sqlite_master WHERE type = 'index' AND name = ?`)
    .get('idx_org_graph_edges_dedupe_active');
  if (dedupeIdx && !/fail_reason/.test(dedupeIdx.sql || '')) {
    database.exec(`DROP INDEX idx_org_graph_edges_dedupe_active`);
  }
  database.exec(`
    CREATE UNIQUE INDEX IF NOT EXISTS idx_org_graph_edges_dedupe_active
      ON org_graph_edges(company_id, from_node_id, to_node_id, condition, COALESCE(fail_reason, ''))
      WHERE status = 'active';
  `);

  // 6. 28 批次 1：`source_kind` 的三分（global_agent / template / company_hired）收成
  //    `origin` 二分。`system/templates/` 已整体并入 `system/agents/`，
  //    「本体住在哪个目录」不再是一个可区分的维度，剩下的唯一区别是**谁造的**：
  //      builtin = 仓库自带的本体（原 global_agent + template）
  //      created = 走 create-agent / hire 生成的（原 company_hired）
  //
  //    带常量 DEFAULT 的 ADD COLUMN 是 SQLite 允许的少数几种 ALTER 之一，
  //    存量行会立刻被填成 'builtin'，所以不存在「加了列还没回填」的中间态。
  //    **CHECK 加不上**：SQLite 只能靠整表重建改约束，而这张表被 org_graph_nodes /
  //    work_graph_nodes 的外键引着，为一条 CHECK 重建表不划算。取值域由写入侧
  //    （graph-seed-service / graph-service，全仓只有这两处写 system_agents）
  //    和新建库的 CREATE TABLE 一起兜。
  if (!hasColumn(database, 'system_agents', 'origin')) {
    database.exec(`ALTER TABLE system_agents ADD COLUMN origin TEXT NOT NULL DEFAULT 'builtin'`);
  }
  // 回填放在 if 外面，且写成「只改需要改的行」：ALTER 与 UPDATE 是两条语句，
  // 中间挂掉的话 company_hired 行会停在默认的 'builtin' 上，下次 migrate 必须能补上。
  // WHERE 里的 origin <> 'created' 让第二遍变成零行更新（幂等）。
  database.exec(`
    UPDATE system_agents SET origin = 'created'
     WHERE source_kind = 'company_hired' AND origin <> 'created'
  `);

  // 7. 28 批次 4：company_agents 的存量回填。
  backfillCompanyAgents(database);
}

/**
 * 存量雇佣关系回填（28 批次 4-17）。
 *
 * 两个来源合起来才是完整名册，缺一不可：
 *   - org_graph_nodes 的 active 行 —— org 层雇的角色，但按 decideOrgNodeRecording
 *     的三条判据，work 层本体根本不在这张表里（这正是不能拿它当成员表的原因）；
 *   - `data/companies/<cid>/company.json` 的 `agents[]` —— hire() 一直在维护的那份名册，
 *     org / work 两层都在里面，是 work 层本体唯一的存量证据。
 *
 * 幂等靠 (company_id, role_name) 主键 + INSERT OR IGNORE：重复跑是零行变化。
 *
 * company.json 那一路**只收 system_agents 里已有的 id**：`system_agent_id` 是指向
 * system_agents 的外键（且 PRAGMA foreign_keys=ON），插一个没有本体行的角色会直接抛，
 * 而这里跑在 getDb() 里 —— 抛出去等于整个进程连不上库。少一行名册可以由 M2 迁移脚本
 * 补，连不上库不行。
 */
function backfillCompanyAgents(database) {
  database.exec(`
    INSERT OR IGNORE INTO company_agents (company_id, system_agent_id, role_name, status, hired_at)
    SELECT company_id, system_agent_id, role_name, 'active', created_at
      FROM org_graph_nodes
     WHERE status = 'active'
  `);

  const companiesDir = path.join(DATA_DIR, 'companies');
  if (!fs.existsSync(companiesDir)) return;

  const insert = database.prepare(`
    INSERT OR IGNORE INTO company_agents (company_id, system_agent_id, role_name, status, hired_at)
    SELECT @company_id, @role, @role, 'active', @hired_at
     WHERE EXISTS (SELECT 1 FROM system_agents WHERE id = @role)
  `);
  for (const entry of fs.readdirSync(companiesDir, { withFileTypes: true })) {
    if (!entry.isDirectory()) continue;
    let company;
    try {
      company = JSON.parse(fs.readFileSync(path.join(companiesDir, entry.name, 'company.json'), 'utf-8'));
    } catch {
      continue; // 没有 company.json / 内容坏掉的目录不是公司，跳过
    }
    for (const role of company.agents || []) {
      if (typeof role !== 'string' || !role) continue;
      // 存量里 role_name 与本体 id 恒相同（hire() 从来只用一个名字），所以两列都填 role
      insert.run({ company_id: entry.name, role, hired_at: company.createdAt || new Date().toISOString() });
    }
  }
}

function createEdgesTable(database) {
  database.exec(`
    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),
      condition        TEXT NOT NULL CHECK (condition IN ('success','fail')),
      fail_reason      TEXT,
      edge_kind        TEXT NOT NULL DEFAULT 'forward' CHECK (edge_kind IN ('forward','backward')),
      status           TEXT NOT NULL DEFAULT 'active',
      created_at       TEXT NOT 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 / legacy_path 是 28 批次 1 之后的**遗留列**：读侧已全部改走
      -- origin + 「路径恒为 system/agents/{id}」的推导，这两列只为存量库和还没改完的
      -- 批次（company_agents 表落地在批次 4）留一版兼容，写入侧仍按 origin 一一映射维护。
      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,
      -- 列排在最后是为了和存量库 ALTER 出来的列序一致（SQLite 的 ADD COLUMN 只能追加）
      origin           TEXT NOT NULL DEFAULT 'builtin' CHECK (origin IN ('builtin','created'))
    );

    -- 公司 ⇄ 本体的雇佣关系（28 §4.2）。公司不再持有 Agent 实体目录，
    -- 「这家公司雇了谁」的唯一真相源就是这张表。
    -- 主键取 (company_id, role_name) 而不是带上 system_agent_id：同一个本体可以在
    -- 一家公司里担两个角色（两行），但同一个角色名在一家公司里只能有一个人。
    CREATE TABLE IF NOT EXISTS company_agents (
      company_id      TEXT NOT NULL,
      system_agent_id TEXT NOT NULL REFERENCES system_agents(id),
      role_name       TEXT NOT NULL,
      status          TEXT NOT NULL DEFAULT 'active',
      hired_at        TEXT NOT NULL,
      PRIMARY KEY (company_id, role_name)
    );

    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,
      status             TEXT NOT NULL DEFAULT 'active',
      reports_to_node_id INTEGER REFERENCES org_graph_nodes(id),
      created_at         TEXT NOT NULL
    );
  `);

  createEdgesTable(db);
  migrate(db);

  return db;
}

module.exports = {
  getDb,
};