const Excel = require('exceljs'),
  utils = require('./utils');

async function exportUsageDetail(data, res) {
  const options = {
    stream: res,    // write to server response
    useStyles: true,
    useSharedStrings: false
  };
  const workbook = new Excel.stream.xlsx.WorkbookWriter(options);
  // const workbook = new Excel.Workbook();

  const headers = [
    {}, {}, {},
    { header: 'Created Date', key: 'createdDate', width: 15 },
    { header: 'Job Id', key: 'jobId', width: 15 },
    { header: 'Sprayed Ha', key: 'sprayedHa', width: 15 },
    { header: 'Sprayed Acr', key: 'sprayedAcre', width: 15 }
  ];
  const center = () => ({ alignment: { vertical: 'middle', horizontal: 'center' } });

  const bold = () => ({ font: { bold: true } })

  const boldCenter = () => ({ ...bold(), ...center() })

  const thinBorder = () => ({
    top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' }
  });

  let custJobs, cell;
  for (let i = 0; i < data.length; i++) {
    custJobs = data[i];

    const worksheet = workbook.addWorksheet(utils.normalizeName(custJobs.name));
    worksheet.columns = headers;

    worksheet.mergeCells('A1:B1');
    cell = worksheet.getCell('A1');
    cell.value = "Customer Information";
    cell.style = boldCenter();

    // Fill Customer Info
    fillCustInfo();

    // Fill spray data table cells    
    for (const [rIdx, job] of custJobs.jobs.entries()) {
      fillSprayData(job, rIdx + 2);
    }

    // Make the Total cells
    worksheet.mergeCells(custJobs.jobs.length + 2, 4, custJobs.jobs.length + 2, 5);
    cell = worksheet.getCell(custJobs.jobs.length + 2, 4)
    cell.value = "Total"
    cell.style = boldCenter();

    cell = worksheet.getCell(custJobs.jobs.length + 2, 6)
    cell.value = { formula: `SUM(F2:F${custJobs.jobs.length + 1})` };
    cell.style = boldCenter();

    cell = worksheet.getCell(custJobs.jobs.length + 2, 7)
    cell.value = { formula: `SUM(G2:G${custJobs.jobs.length + 1})` };
    cell.style = boldCenter();

    // Format the first row and Customer Info
    worksheet.getRow(1).height = 18;
    worksheet.getColumn(1).style = boldCenter();
    worksheet.getColumn(1).width = 12;
    worksheet.getColumn(2).width = 40;
    worksheet.getColumn(3).width = 6;

    // Format number-value cells
    [4, 5, 6, 7].forEach(c => {
      worksheet.getCell(1, c).style = boldCenter();
      if ([6, 7].includes(c)) {
        worksheet.getColumn(c).numFmt = '#,##0.0';
      }
    });
    // Format spray data table cells
    for (let i = 1; i <= custJobs.jobs.length + 2; i++) {
      for (let j = 4; j <= 7; j++) {
        cell = worksheet.getCell(i, j)
        cell.border = thinBorder();
      }
    }

    worksheet.commit();

    function fillSprayData(data, rIdx) {
      worksheet.getCell(rIdx, 4).value = data.createdAt;
      worksheet.getCell(rIdx, 5).value = data.jobId;
      worksheet.getCell(rIdx, 6).value = Number(data.totalSpray.toFixed(1));
      worksheet.getCell(rIdx, 7).value = Number(utils.haToAcre(data.totalSpray).toFixed(1));
    }

    function fillCustInfo() {
      let row = 2;
      for (let p in custJobs) {
        if (p !== 'jobs') {
          cell = worksheet.getCell(row, 1);
          cell.value = utils.capitalize(p);
          cell.style = boldCenter();

          worksheet.getCell(row, 2).value = custJobs[p];
          row++;
        }
      }
    }
  }

  await workbook.commit();
  // await workbook.xlsx.writeFile('usageDetail.xlsx');
}


module.exports = {
  exportUsageDetail
}