"/**
 * Optimized script for generating formatted PDF reports based on inputs from the "Main" sheet.
 * Enhancements:
 * 1. Reduced number of Spreadsheet API calls by batching data operations.
 * 2. Improved filtering efficiency using JavaScript array methods.
 * 3. Enhanced error handling and logging for better debugging.
 * 4. Improved code readability and maintainability.
 * 5. Added AM/PM format to all date-time displays.
 * 6. Included email recipient in log notes when emails are sent.
 */
function generateFreePDFReport() {
  const generationTime = new Date(); // Capture generationTime once at the start
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = ss.getSheetByName("Main");
  const logSheet = ss.getSheetByName("Log(s)");

  if (!mainSheet) {
    Logger.log(`Main sheet "Main" not found.`);
    return;
  }

  if (!logSheet) {
    Logger.log(`Log sheet "Log(s)" not found.`);
    mainSheet.getRange("H2").setValue(`Log sheet "Log(s)" not found.`);
    return;
  }

  // Set Column B in "Log(s)" sheet to plain text to prevent formatting issues
  logSheet.getRange("B4:B").setNumberFormat("@");

  // Fetch inputs from Main sheet in a single batch operation
  const mainRange = mainSheet.getRange("H2:K7");
  const mainValues = mainRange.getValues(); // H2:K7 spans multiple rows

  // Assign fetched values
  const reportType = mainSheet.getRange("I3").getValue().toString().trim();
  const startDate = mainSheet.getRange("I4").getValue();
  const endDate = mainSheet.getRange("K4").getValue();
  const sendEmailFlag = mainSheet.getRange("I5").getValue().toString().trim().toLowerCase();
  const emailRecipient = mainSheet.getRange("J5").getValue().toString().trim();
  const assetClassFilter = mainSheet.getRange("I7").getValue().toString().trim();
  const brokerFilter = mainSheet.getRange("J7").getValue().toString().trim();
  const userFilter = mainSheet.getRange("K7").getValue().toString().trim();

  // Validate report type
  const validReportTypes = ["PnL", "PF", "Trade Log"];
  if (!validReportTypes.includes(reportType)) {
    Logger.log(`Invalid report type selected: ${reportType}.`);
    mainSheet.getRange("H2").setValue("Invalid report type selected.");
    return;
  }

  // Generate report based on type
  if (reportType === "PnL") {
    generatePnLReport(
      ss,
      mainSheet,
      startDate,
      endDate,
      sendEmailFlag,
      emailRecipient,
      assetClassFilter,
      brokerFilter,
      userFilter,
      reportType,
      generationTime // Pass generationTime
    );
  }

  // Future implementations for "PF", "Trade Log" can be added here

  // Capture end time and calculate duration
  const endTime = new Date();
  const timeTakenSeconds = ((endTime - generationTime) / 1000).toFixed(2); // Use generationTime for duration

  // Display the time taken in the Main sheet
  mainSheet.getRange("H2").setValue(`Report generated in ${timeTakenSeconds} seconds`);

  // Update cell A2 in "Log(s)" sheet with the end time
  const formattedEndTime = Utilities.formatDate(endTime, Session.getScriptTimeZone(), "dd-MMM-yyyy hh:mm:ss a");
  logSheet.getRange("A2").setValue(`Report last updated at: ${formattedEndTime}`);
}

/**
 * Generates the PnL Report based on the provided filters and inputs.
 */
function generatePnLReport(
  ss,
  mainSheet,
  startDate,
  endDate,
  sendEmailFlag,
  emailRecipient,
  assetClassFilter,
  brokerFilter,
  userFilter,
  reportType,
  generationTime // Accept generationTime as a parameter
) {
  const PNL_SHEET_NAME = "PnL";
  const PNL_I_SHEET_NAME = "PnL_i";
  const REPORT_SHEET_NAME = "Report";
  const LOG_SHEET_NAME = "Log(s)";

  // Retrieve and increment the unique report number
  const reportNumber = getNextReportNumber(ss, LOG_SHEET_NAME);
  const formattedReportNumber = Utilities.formatString("%05d", reportNumber);

  // Use the passed generationTime instead of creating a new one
  const pdfName = `${reportType}_Report_${formattedReportNumber}_${Utilities.formatDate(
    generationTime,
    Session.getScriptTimeZone(),
    "dd-MMM-yyyy_hh:mm:ss a"
  )}.pdf`;

  // Report Drive folder
  const folderName = "Trading Reports";
  const folder = getExistingFolder(folderName);

  if (!folder) {
    Logger.log(`Folder "${folderName}" not found. Please create it in Google Drive.`);
    mainSheet.getRange("H2").setValue(`Folder "${folderName}" not found.`);
    return;
  }

  // Create or clear the Report sheet
  let reportSheet = ss.getSheetByName(REPORT_SHEET_NAME);
  if (reportSheet) {
    reportSheet.clear();
  } else {
    reportSheet = ss.insertSheet(REPORT_SHEET_NAME);
  }

  // Insert Report Header
  insertReportHeader(reportSheet, formattedReportNumber, generationTime); // Use generationTime

  // Apply filters
  const filters = {
    assetClass: assetClassFilter,
    broker: brokerFilter,
    user: userFilter,
    startDate,
    endDate,
  };

  // Append PnL sections
  appendPnLSection(ss, reportSheet, PNL_SHEET_NAME, "Delivery Trades", filters);
  appendPnLSection(ss, reportSheet, PNL_I_SHEET_NAME, "Intraday Trades", filters, true);

  // Apply formatting
  formatReportSheet(reportSheet);

  // Export to PDF
  const pdfBlob = exportSheetAsPDF(
    ss,
    REPORT_SHEET_NAME,
    pdfName,
    formattedReportNumber,
    generationTime
  ); // Pass generationTime

  if (pdfBlob) {
    // Save PDF to Drive
    const pdfFile = folder.createFile(pdfBlob);
    Logger.log(`PDF report generated: ${pdfFile.getUrl()}`);

    // Email the PDF if required
    if (sendEmailFlag === "yes" && emailRecipient) {
      sendEmailWithReport(
        pdfFile,
        formattedReportNumber,
        generationTime,
        emailRecipient,
        reportType
      ); // Use generationTime
    }

    // Log the report generation
    logReportGeneration(
      ss,
      LOG_SHEET_NAME,
      formattedReportNumber,
      reportType,
      sendEmailFlag,
      assetClassFilter,
      brokerFilter,
      userFilter,
      emailRecipient,
      generationTime
    ); // Use generationTime

    // Delete the Report sheet to clean up
    ss.deleteSheet(reportSheet);
  } else {
    Logger.log("PDF blob was not created.");
    mainSheet.getRange("H2").setValue("Failed to generate PDF.");
  }
}

/**
 * Retrieves an existing folder by name. If it doesn't exist, returns null.
 * @param {string} folderName - The name of the folder.
 * @return {Folder|null} - The Google Drive folder or null if not found.
 */
function getExistingFolder(folderName) {
  const folders = DriveApp.getFoldersByName(folderName);
  return folders.hasNext() ? folders.next() : null;
}

/**
 * Retrieves the next unique report number from the Log(s) sheet and increments it.
 * @param {Spreadsheet} ss - The active spreadsheet.
 * @param {string} logSheetName - The name of the Log(s) sheet.
 * @return {number} - The unique report number.
 */
function getNextReportNumber(ss, logSheetName) {
  const logSheet = ss.getSheetByName(logSheetName);
  if (!logSheet) {
    Logger.log(`Log sheet "${logSheetName}" not found. Please create it with headers in row 4: Sl, Date time, Note.`);
    throw new Error(`Log sheet "${logSheetName}" not found.`);
  }

  const lastRow = logSheet.getLastRow();
  let nextReportNumber = 1;

  if (lastRow >= 4) { // Assuming data starts at row 4
    const lastReportNumber = logSheet.getRange(lastRow, 1).getValue();
    if (!isNaN(lastReportNumber) && lastReportNumber >= 1) {
      nextReportNumber = lastReportNumber + 1;
    }
  }

  return nextReportNumber;
}

/**
 * Inserts the report header including the report number and barcode into the Report sheet.
 * Ensures barcode is placed in a separate cell to avoid overlap.
 * @param {Sheet} reportSheet - The report sheet.
 * @param {string} reportNumber - The unique 5-digit report number.
 * @param {Date} generationTime - The consistent generation time.
 */
function insertReportHeader(reportSheet, reportNumber, generationTime) { // Use generationTime
  // Insert Report Title and Generation Time
  const headerValues = [
    [`Trading Report - Report #${reportNumber}`],
    [`Generated at: ${Utilities.formatDate(
      generationTime,
      Session.getScriptTimeZone(),
      "dd-MMM-yyyy hh:mm:ss a"
    )}`], // Correct format
    ['']
  ];
  reportSheet.getRange(1, 1, headerValues.length, 1).setValues(headerValues);

  // Apply formatting to the title and generation time
  reportSheet.getRange("A1").setFontWeight("bold").setFontSize(16);
  reportSheet.getRange("A2").setFontSize(12);

  // Insert Barcode Image using QuickChart
  const barcodeURL = `https://quickchart.io/barcode?text=${reportNumber}&format=png&type=code128&height=50`;

  try {
    const response = UrlFetchApp.fetch(barcodeURL);
    const barcodeBlob = response.getBlob().setName(`Barcode_Report_${reportNumber}.png`);
    reportSheet.insertImage(barcodeBlob, 7, 1); // Column G (7), Row 1
  } catch (error) {
    Logger.log(`Error fetching barcode image: ${error}`);
  }

  // Add a blank row after the barcode
  reportSheet.appendRow(['']);
}

/**
 * Appends a PnL section (Delivery or Intraday) to the Report sheet, including data, formatting, and totals.
 * @param {Spreadsheet} ss - The active spreadsheet.
 * @param {Sheet} reportSheet - The report sheet.
 * @param {string} sheetName - The name of the PnL sheet ("PnL" or "PnL_i").
 * @param {string} sectionTitle - The title of the section ("Delivery Trades" or "Intraday Trades").
 * @param {Object} filters - Filters for Asset Class, Broker, User, Start Date, End Date.
 * @param {boolean} newPage - If true, starts the section on a new page.
 */
function appendPnLSection(
  ss,
  reportSheet,
  sheetName,
  sectionTitle,
  filters,
  newPage = false
) {
  const pnlSheet = ss.getSheetByName(sheetName);
  if (!pnlSheet) {
    Logger.log(`Sheet "${sheetName}" not found.`);
    return;
  }

  // Apply filters and fetch data
  const { data, headers, columnIndices } = getFilteredData(pnlSheet, filters);

  Logger.log(`${sectionTitle} Sheet filtered row count: ${data.length}`);

  if (data.length > 0) {
    if (newPage) {
      // Insert a page break by adding blank rows
      const blankRows = 5; // Adjust as needed
      const blankRowData = Array(headers.length).fill('');
      for (let i = 0; i < blankRows; i++) {
        reportSheet.appendRow(blankRowData);
      }
    }

    // Append section title
    const currentLastRow = reportSheet.getLastRow() + 1;
    reportSheet.appendRow([`${sectionTitle} - ${sheetName}`]);
    reportSheet.getRange(currentLastRow, 1).setFontWeight("bold").setFontSize(14);

    // Append headers
    reportSheet.appendRow(headers);
    reportSheet.getRange(currentLastRow + 1, 1, 1, headers.length).setFontWeight("bold").setBackground("#d3d3d3");

    // Append data
    const dataStartRow = reportSheet.getLastRow() + 1;
    const dataRange = reportSheet.getRange(dataStartRow, 1, data.length, headers.length);
    dataRange.setValues(data);

    // Apply number formats where necessary
    applyNumberFormats(dataRange, columnIndices);

    // Insert Total Row
    insertTotalRowAfter(reportSheet, dataStartRow, data.length, columnIndices, headers.length);
  }
}

/**
 * Retrieves and filters data from a sheet based on provided filters.
 * Updates the existing "% P/L (Abs.)" column.
 * @param {Sheet} sheet - The sheet to retrieve data from.
 * @param {Object} filters - Filters for Asset Class, Broker, User, Start Date, End Date.
 * @return {Object} - An object containing data, headers, and column indices to sum.
 */
function getFilteredData(sheet, filters) {
  const headerRow = 4;
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();

  if (lastRow < headerRow + 1) { // No data
    return { data: [], headers: [], columnIndices: [] };
  }

  const dataRange = sheet.getRange(headerRow + 1, 1, lastRow - headerRow, lastColumn);
  const data = dataRange.getValues();

  const headers = sheet.getRange(headerRow, 1, 1, lastColumn).getValues()[0];

  // Identify relevant column indices based on headers (case-insensitive)
  const headerMap = headers.reduce((map, header, index) => {
    if (header) { // Ensure header is not null or undefined
      map[header.toString().toLowerCase()] = index;
    }
    return map;
  }, {});

  const dateHeaderName = sheet.getName() === "PnL" ? "sell date" : "trade date";
  const dateColumnIndex = headerMap[dateHeaderName];
  const assetClassIndex = headerMap["asset class"];
  const brokerIndex = headerMap["broker"];
  const userIndex = headerMap["user"];
  const netPnLIndex = headerMap["net pnl (after fees)"];
  const valueBuyIndex = headerMap["value @ buy"];
  const percentPnLIndex = headerMap["% p/l (abs.)"];

  if (
    dateColumnIndex === undefined ||
    assetClassIndex === undefined ||
    brokerIndex === undefined ||
    userIndex === undefined ||
    netPnLIndex === undefined ||
    valueBuyIndex === undefined ||
    percentPnLIndex === undefined
  ) {
    throw new Error(`One or more required columns not found in sheet "${sheet.getName()}".`);
  }

  // Filter data based on criteria
  const filteredData = data.filter(row => {
    const date = row[dateColumnIndex];
    if (!(date instanceof Date) || isNaN(date)) return false;
    if (filters.startDate && new Date(date) < new Date(filters.startDate)) return false;
    if (filters.endDate && new Date(date) > new Date(filters.endDate)) return false;

    if (filters.assetClass !== "ALL" && row[assetClassIndex] !== filters.assetClass) return false;
    if (filters.broker !== "ALL" && row[brokerIndex] !== filters.broker) return false;
    if (filters.user !== "ALL" && row[userIndex] !== filters.user) return false;

    return true;
  });

  // Update "% P/L (Abs.)" column
  filteredData.forEach(row => {
    const netPnL = row[netPnLIndex];
    const valueBuy = row[valueBuyIndex];
    row[percentPnLIndex] = valueBuy !== 0 ? ((netPnL / valueBuy) * 100).toFixed(2) : "0.00";
  });

  // Identify columns to sum
  const columnsToSum = ["value @ buy", "value @ sell", "gross pnp", "fees", "net pnl (after fees)", "% p/l (abs.)"];
  const columnIndices = columnsToSum
    .map(col => headerMap[col.toLowerCase()])
    .filter(index => index !== undefined)
    .map(index => index + 1); // Convert to 1-based

  return { data: filteredData, headers, columnIndices };
}

/**
 * Applies number formats to the specified range based on column indices.
 * @param {Range} range - The range to apply formats to.
 * @param {Array<number>} columnIndices - The 1-based indices of columns that require number formatting.
 */
function applyNumberFormats(range, columnIndices) {
  const numRows = range.getNumRows();
  const numCols = range.getNumColumns();
  const formats = range.getNumberFormats();

  columnIndices.forEach(colIndex => {
    if (colIndex <= numCols) {
      for (let i = 0; i < numRows; i++) {
        formats[i][colIndex - 1] = "#,##0.00"; // Customize format as needed
      }
    }
  });

  range.setNumberFormats(formats);
}

/**
 * Converts a column index (1-based) to its corresponding letter (e.g., 1 -> A).
 * @param {number} column - The 1-based column index.
 * @return {string} - The corresponding column letter.
 */
function columnToLetter(column) {
  let temp;
  let letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = Math.floor((column - temp - 1) / 26);
  }
  return letter;
}

/**
 * Inserts a total row by summing specified columns after the data rows.
 * @param {Sheet} reportSheet - The report sheet.
 * @param {number} dataStartRow - The starting row number of the data (1-based).
 * @param {number} numRows - The number of data rows.
 * @param {Array<number>} columnIndices - The 1-based indices of the columns to sum.
 * @param {number} totalColumns - Total number of columns in the data.
 */
function insertTotalRowAfter(reportSheet, dataStartRow, numRows, columnIndices, totalColumns) {
  const totalRow = Array(totalColumns).fill('');

  totalRow[0] = "Total"; // Assuming first column is for labels

  columnIndices.forEach(colIndex => {
    if (colIndex <= totalColumns) {
      const colLetter = columnToLetter(colIndex);
      totalRow[colIndex - 1] = `=SUM(${colLetter}${dataStartRow}:${colLetter}${dataStartRow + numRows - 1})`;
    }
  });

  // Insert the total row
  reportSheet.appendRow(totalRow);

  const totalRowNumber = dataStartRow + numRows;

  // Apply formatting to the total row
  const totalRange = reportSheet.getRange(totalRowNumber, 1, 1, totalColumns);
  totalRange.setFontWeight("bold").setBackground("#d3d3d3").setNumberFormat("@"); // Set as needed
}

/**
 * Applies formatting to the Report sheet to make it systematic and visually appealing.
 * Sets column widths, text alignment, frozen rows, and alternating row colors.
 * @param {Sheet} reportSheet - The report sheet.
 */
function formatReportSheet(reportSheet) {
  // Adjust column widths to fit content
  reportSheet.autoResizeColumns(1, reportSheet.getLastColumn());

  // Set text alignment to left for all cells
  const range = reportSheet.getDataRange();
  range.setHorizontalAlignment("left");

  // Freeze the top rows where headers and barcode are placed
  reportSheet.setFrozenRows(5); // Adjust based on the number of header rows

  // Apply alternating row colors (Zebra Striping)
  const numRows = reportSheet.getLastRow();
  const numColumns = reportSheet.getLastColumn();

  if (numRows > 5) { // Adjust based on frozen rows
    const dataRange = reportSheet.getRange(6, 1, numRows - 5, numColumns);
    const backgrounds = [];

    for (let i = 0; i < (numRows - 5); i++) {
      const isEven = (i + 1) % 2 === 0;
      const rowColor = isEven ? "#f9f9f9" : "#ffffff";
      backgrounds.push(Array(numColumns).fill(rowColor));
    }

    dataRange.setBackgrounds(backgrounds);
  }
}

/**
 * Exports a specific sheet as a PDF blob, including report number and generation time.
 * @param {Spreadsheet} ss - The active spreadsheet.
 * @param {string} sheetName - The name of the sheet to export.
 * @param {string} pdfName - The desired PDF file name.
 * @param {string} reportNumber - The unique report number.
 * @param {Date} generationTime - The consistent generation time.
 * @return {Blob|null} - The PDF blob of the sheet or null if export fails.
 */
function exportSheetAsPDF(
  ss,
  sheetName,
  pdfName,
  reportNumber,
  generationTime
) { // Pass generationTime
  const sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    Logger.log(`Sheet "${sheetName}" not found.`);
    return null;
  }

  const sheetId = sheet.getSheetId();
  const url_base = ss.getUrl().replace(/edit$/, '');
  const exportUrl = `${url_base}export?` +
    `format=pdf&` +
    `size=letter&` +
    `portrait=true&` +
    `fitw=true&` +
    `sheetnames=false&` +
    `printtitle=false&` +
    `pagenumbers=true&` +
    `gridlines=false&` +
    `fzr=true&` + // Freeze rows
    `gid=${sheetId}&` +
    `horizontal_alignment=LEFT&` +
    `vertical_alignment=TOP&` +
    `top_margin=0.5&` +
    `bottom_margin=0.5&` +
    `left_margin=0.5&` +
    `right_margin=0.5&`;

  const options = {
    headers: { 'Authorization': `Bearer ${ScriptApp.getOAuthToken()}` },
    muteHttpExceptions: true
  };

  try {
    const response = UrlFetchApp.fetch(exportUrl, options);
    if (response.getResponseCode() !== 200) {
      Logger.log(`Failed to fetch PDF. Response Code: ${response.getResponseCode()}`);
      return null;
    }
    return response.getBlob().setName(pdfName);
  } catch (error) {
    Logger.log(`Error exporting sheet "${sheetName}" as PDF: ${error}`);
    return null;
  }
}

/**
 * Sends an email with the PDF report attached, including the unique report number and generation time.
 * @param {Blob} pdfFile - The PDF file blob to attach.
 * @param {string} reportNumber - The unique report number.
 * @param {Date} generationTime - The consistent generation time.
 * @param {string} emailRecipient - The recipient's email address.
 * @param {string} reportType - The type of report generated.
 */
function sendEmailWithReport(
  pdfFile,
  reportNumber,
  generationTime,
  emailRecipient,
  reportType
) { // Use generationTime
  const subject = `${reportType} Report - Report #${reportNumber}`;
  const reportGenerationTime = Utilities.formatDate(
    generationTime,
    Session.getScriptTimeZone(),
    "dd-MMM-yyyy hh:mm:ss a"
  ); // Correct format with AM/PM
  const body = `Please find attached the latest ${reportType} Report (Report #${reportNumber}) generated on ${reportGenerationTime}.\n\nRegards,\nNeeraj`;

  try {
    MailApp.sendEmail({
      to: emailRecipient,
      subject: subject,
      body: body,
      attachments: [pdfFile]
    });
    Logger.log("PDF report emailed to recipients.");
  } catch (error) {
    Logger.log(`Error sending email: ${error}`);
  }
}

/**
 * Logs the report generation with report number, type, email status, and applied filters in the "Log(s)" sheet.
 * @param {Spreadsheet} ss - The active spreadsheet.
 * @param {string} logSheetName - The name of the Log(s) sheet.
 * @param {string} reportNumber - The unique report number.
 * @param {string} reportType - The type of report generated.
 * @param {string} sendEmailFlag - "yes" or "no" indicating if email was sent.
 * @param {string} assetClassFilter - The Asset Class filter applied.
 * @param {string} brokerFilter - The Broker filter applied.
 * @param {string} userFilter - The User filter applied.
 * @param {string} emailRecipient - The recipient's email address (added for logging).
 * @param {Date} generationTime - The consistent generation time.
 */
function logReportGeneration(
  ss,
  logSheetName,
  reportNumber,
  reportType,
  sendEmailFlag,
  assetClassFilter,
  brokerFilter,
  userFilter,
  emailRecipient,
  generationTime
) { // Use generationTime
  const logSheet = ss.getSheetByName(logSheetName);
  if (!logSheet) {
    Logger.log(`Log sheet "${logSheetName}" not found. Please create it with headers in row 4: Sl, Date time, Note.`);
    return;
  }

  const mailStatus = sendEmailFlag === "yes" ? "Mail_Sent" : "Mail_Void";

  // Include emailRecipient in the note if email was sent
  const note = sendEmailFlag === "yes"
    ? `${reportType} Report generated with Report Number ${reportNumber}_${mailStatus}_${assetClassFilter} | ${brokerFilter} | ${userFilter}_${emailRecipient}`
    : `${reportType} Report generated with Report Number ${reportNumber}_${mailStatus}_${assetClassFilter} | ${brokerFilter} | ${userFilter}`;

  // Find the next empty row starting from row 4
  const lastRow = logSheet.getLastRow();
  const nextRow = lastRow >= 4 ? lastRow + 1 : 4;

  // Format the generationTime as a string
  const formattedGenerationTime = Utilities.formatDate(
    generationTime,
    Session.getScriptTimeZone(),
    "dd-MMM-yyyy hh:mm:ss a"
  ); // Ensures AM/PM is correctly formatted

  const logData = [
    [reportNumber, formattedGenerationTime, note, '', '', '', '']
  ];

  // Insert the formatted string directly
  logSheet.getRange(nextRow, 1, 1, 7).setValues(logData);

  // No need to setNumberFormat since we're inserting a formatted string
}"

ye code cell I3 se kon kon se input le rahah h "Main" sheet se