const fs = require('fs');
const path = require('path');
const XLSX = require('xlsx');
require('dotenv').config();
const source = process.env.MASTER_EXCEL || 'Audit_Firm_ERP_Master_UAT_VAPT_Checklist.xlsx';
const output = process.env.UPDATED_EXCEL || 'results/Audit_Firm_ERP_Master_UAT_VAPT_Checklist_v2_2_Results.xlsx';
const mergedPath = 'results/merged-results.json';
if (!fs.existsSync(mergedPath)) {
  console.error('Run npm run report:json first.'); process.exit(1);
}
const merged = JSON.parse(fs.readFileSync(mergedPath,'utf8'));
const wb = XLSX.readFile(source);
const byId = {};
for (const v of merged) {
  byId[v.sourceId] = byId[v.sourceId] || [];
  byId[v.sourceId].push(v);
}
for (const sheet of wb.SheetNames) {
  if (!sheet.startsWith('UAT_') && !sheet.startsWith('VAPT_')) continue;
  const ws = wb.Sheets[sheet];
  const data = XLSX.utils.sheet_to_json(ws, {header:1, blankrows:false});
  if (data.length < 2) continue;
  const headers = data[1];
  let statusCol = headers.indexOf('Status');
  let remarksCol = headers.indexOf('Remarks');
  if (statusCol < 0) { statusCol = headers.length; headers.push('Status'); }
  if (remarksCol < 0) { remarksCol = headers.length; headers.push('Remarks'); }
  for (let r=2; r<data.length; r++) {
    const id = data[r][0];
    if (!id || !byId[id]) continue;
    const vars = byId[id];
    const failed = vars.filter(v => ['failed','timedOut'].includes(v.result));
    const passed = vars.filter(v => v.result === 'passed');
    const manual = vars.filter(v => v.result === 'manual');
    const notRun = vars.filter(v => v.result === 'not_run');
    let status = 'Not Run';
    if (failed.length) status = 'Fail';
    else if (notRun.length && passed.length) status = 'Partial';
    else if (manual.length === vars.length) status = 'Manual';
    else if (passed.length === vars.length || passed.length + manual.length === vars.length) status = 'Pass';
    data[r][statusCol] = status;
    data[r][remarksCol] = `v2.2 variants: ${passed.length} pass, ${failed.length} fail, ${manual.length} manual, ${notRun.length} not run.`;
  }
  wb.Sheets[sheet] = XLSX.utils.aoa_to_sheet(data);
}
fs.mkdirSync(path.dirname(output), {recursive:true});
XLSX.writeFile(wb, output);
console.log(`Updated Excel written: ${output}`);