npi-2026/scripts/parse-excel.mjs

import XLSX from 'xlsx';
import { readFileSync, writeFileSync } from 'fs';

const workbook = XLSX.read(readFileSync('../NPI - AGENT Instructions.xlsx'));

// Map sheet names to category enum values
const sheetToType = {
  'Agent I': 'I',
  'Agent IB': 'IB',
  'Agent II': 'II',
  'Agent III': 'III'
};

// Gate number mapping from stage text
function parseGateNumber(stageText) {
  if (!stageText) return null;
  const match = String(stageText).match(/^(\d+)/);
  return match ? parseInt(match[1], 10) : null;
}

// Escape SQL strings
function esc(str) {
  if (!str) return 'NULL';
  return "'" + String(str).replace(/'/g, "''").trim() + "'";
}

// Parse semicolon-separated role lists
function parseRoles(text) {
  if (!text) return [];
  return String(text).split(/[;,]/).map(r => r.trim()).filter(Boolean);
}

// ============================================================
// Parse Sheet1 for evidence/failure modes (Agent III reference)
// ============================================================
const sheet1 = XLSX.utils.sheet_to_json(workbook.Sheets['Sheet1'], { header: 1 });
// Build a map: gateNumber -> resource -> { evidence, failures }
const sheet1Data = {};
for (let i = 1; i < sheet1.length; i++) {
  const row = sheet1[i];
  if (!row || !row[0]) continue;
  const gateNum = parseGateNumber(row[0]);
  if (gateNum === null) continue;
  const resource = String(row[1] || '').trim();
  const evidence = String(row[4] || '').trim();
  const failures = String(row[5] || '').trim();
  if (!sheet1Data[gateNum]) sheet1Data[gateNum] = {};
  if (!sheet1Data[gateNum][resource]) {
    sheet1Data[gateNum][resource] = { evidence: [], failures: [] };
  }
  if (evidence) sheet1Data[gateNum][resource].evidence.push(evidence);
  if (failures) sheet1Data[gateNum][resource].failures.push(failures);
}

// ============================================================
// Generate gate_steps and gate_step_raci seed SQL
// ============================================================
let stepsSql = '-- Gate steps per agent type (auto-generated from Excel)\n\n';
let raciSql = '-- RACI assignments per gate step (auto-generated from Excel)\n\n';

let stepCounter = 0;

for (const [sheetName, agentType] of Object.entries(sheetToType)) {
  const sheet = workbook.Sheets[sheetName];
  if (!sheet) {
    console.warn(`Sheet "${sheetName}" not found, skipping`);
    continue;
  }

  const data = XLSX.utils.sheet_to_json(sheet, { header: 1 });

  stepsSql += `-- === ${sheetName} (${agentType}) ===\n`;
  raciSql += `-- === ${sheetName} (${agentType}) ===\n`;

  let sortOrder = 0;

  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    if (!row || !row[0] || !row[1]) continue;

    const gateNum = parseGateNumber(row[0]);
    if (gateNum === null) continue;

    const stepName = String(row[1]).trim();
    const description = String(row[2] || '').trim();
    const accountable = parseRoles(row[3]);
    const responsible = parseRoles(row[4]);
    const consulted = parseRoles(row[5]);
    const informed = parseRoles(row[6]);

    // Try to find evidence/failure from Sheet1
    let evidence = '';
    let failures = '';
    // Look through Sheet1 data for matching gate
    if (sheet1Data[gateNum]) {
      // Aggregate all evidence/failures for this gate
      const gateRef = sheet1Data[gateNum];
      const allEvidence = new Set();
      const allFailures = new Set();
      for (const res of Object.values(gateRef)) {
        res.evidence.forEach(e => allEvidence.add(e));
        res.failures.forEach(f => allFailures.add(f));
      }
      // Only attach to the first step per gate per agent type to avoid duplication
      if (sortOrder === 0 || parseGateNumber(data[i-1]?.[0]) !== gateNum) {
        // This is not a great heuristic but better than nothing
      }
    }

    stepCounter++;
    const stepId = stepCounter;

    stepsSql += `INSERT INTO gate_steps (id, gate_id, category, step_name, description, sort_order)\n`;
    stepsSql += `  VALUES (${stepId}, (SELECT id FROM gates WHERE gate_number = ${gateNum}), '${agentType}', ${esc(stepName)}, ${esc(description)}, ${sortOrder});\n`;

    // Generate RACI entries
    for (const role of accountable) {
      raciSql += `INSERT INTO gate_step_raci (gate_step_id, role_name, raci) VALUES (${stepId}, ${esc(role)}, 'A');\n`;
    }
    for (const role of responsible) {
      raciSql += `INSERT INTO gate_step_raci (gate_step_id, role_name, raci) VALUES (${stepId}, ${esc(role)}, 'R');\n`;
    }
    for (const role of consulted) {
      raciSql += `INSERT INTO gate_step_raci (gate_step_id, role_name, raci) VALUES (${stepId}, ${esc(role)}, 'C');\n`;
    }
    for (const role of informed) {
      raciSql += `INSERT INTO gate_step_raci (gate_step_id, role_name, raci) VALUES (${stepId}, ${esc(role)}, 'I');\n`;
    }

    raciSql += '\n';
    sortOrder++;
  }

  stepsSql += '\n';
  raciSql += '\n';
}

// Also generate evidence/failure modes seed from Sheet1
let evidenceSql = '-- Evidence requirements and failure modes from Sheet1 (reference data)\n';
evidenceSql += '-- Stored as comments for now; can be added to gate_steps table later\n\n';

for (let i = 1; i < sheet1.length; i++) {
  const row = sheet1[i];
  if (!row || !row[0]) continue;
  const gateNum = parseGateNumber(row[0]);
  if (gateNum === null) continue;
  const resource = String(row[1] || '').trim();
  const instructions = String(row[3] || '').trim();
  const evidence = String(row[4] || '').trim();
  const failures = String(row[5] || '').trim();

  if (instructions || evidence || failures) {
    evidenceSql += `-- G${gateNum} | ${resource}\n`;
    if (instructions) evidenceSql += `--   Instructions: ${instructions.substring(0, 200)}\n`;
    if (evidence) evidenceSql += `--   Evidence: ${evidence.substring(0, 200)}\n`;
    if (failures) evidenceSql += `--   Failure modes: ${failures.substring(0, 200)}\n`;
    evidenceSql += '\n';
  }
}

writeFileSync('../db/init/003_seed_steps.sql', stepsSql);
writeFileSync('../db/init/004_seed_raci.sql', raciSql);
writeFileSync('../db/init/005_seed_evidence_ref.sql', evidenceSql);

console.log(`Generated ${stepCounter} gate steps across all agent types`);
console.log('Files written:');
console.log('  db/init/003_seed_steps.sql');
console.log('  db/init/004_seed_raci.sql');
console.log('  db/init/005_seed_evidence_ref.sql');