import * as XLSX from 'xlsx';

export async function generateExcel(
  content: string,
  toolName: string
): Promise<Buffer> {
  const wb = XLSX.utils.book_new();

  // Parse markdown tables if present
  const lines = content.split('\n');
  const tableRows: string[][] = [];
  const textRows: string[][] = [];
  let inTable = false;

  for (const line of lines) {
    const trimmed = line.trim();
    if (trimmed.startsWith('|') && trimmed.endsWith('|')) {
      // Skip separator rows
      if (trimmed.match(/^\|[\s-:|]+\|$/)) continue;
      inTable = true;
      const cells = trimmed
        .split('|')
        .slice(1, -1)
        .map((c) => c.trim());
      tableRows.push(cells);
    } else {
      if (inTable) inTable = false;
      if (trimmed) {
        textRows.push([trimmed]);
      }
    }
  }

  // Create table sheet if tables found
  if (tableRows.length > 0) {
    const ws = XLSX.utils.aoa_to_sheet(tableRows);
    XLSX.utils.book_append_sheet(wb, ws, 'Framework');
  }

  // Create content sheet
  const title = toolName.replace(/-/g, ' ').replace(/\b\w/g, (c) => c.toUpperCase());
  const contentRows = [
    [`NGO Toolkit Lab — ${title}`],
    [''],
    ...textRows,
    [''],
    ['Generated by NGO Toolkit Lab'],
  ];
  const contentWs = XLSX.utils.aoa_to_sheet(contentRows);
  XLSX.utils.book_append_sheet(wb, contentWs, 'Full Content');

  const buf = XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });
  return Buffer.from(buf);
}