excelLogger.js

const fs = require("fs");
const XLSX = require("xlsx");
const path = require("path");

const filePath = path.join(__dirname, "log-data.xlsx");

function sanitizeSheetName(name) {
  return name.replace(/[^a-zA-Z0-9]/g, "_").substring(0, 31);
}

function loadOrCreateWorkbook() {
  if (fs.existsSync(filePath)) {
    try {
      return XLSX.readFile(filePath);
    } catch (e) {
      console.error("Failed to read Excel file, recreating:", e.message);
      fs.unlinkSync(filePath);
      return XLSX.utils.book_new();
    }
  } else {
    return XLSX.utils.book_new();
  }
}

function appendLogEntry(entry) {
  const wb = loadOrCreateWorkbook();
  const userId = sanitizeSheetName(entry.userId || "unknown");
  const sheetName = `user_${userId}`;

  let logs = [["Timestamp", "Type", "Details"]];

  if (wb.SheetNames.includes(sheetName)) {
    const ws = wb.Sheets[sheetName];
    logs = XLSX.utils.sheet_to_json(ws, { header: 1 });
  }

  logs.push([
    new Date().toISOString(),
    entry.type || "unspecified",
    JSON.stringify(entry.data)
  ]);

  const newSheet = XLSX.utils.aoa_to_sheet(logs);
  wb.Sheets[sheetName] = newSheet;

  if (!wb.SheetNames.includes(sheetName)) {
    wb.SheetNames.push(sheetName);
  }

  XLSX.writeFile(wb, filePath);
}

module.exports = { appendLogEntry };