src/lib/excel-export.ts

import * as XLSX from 'xlsx';
import { toast } from './custom-toast';

interface ExportOptions {
  filename: string;
  sheetName?: string;
  headerStyle?: any;
  cellStyles?: { [key: string]: any };
  columnWidths?: { [key: string]: number };
}

/**
 * Export data to Excel with advanced formatting options
 * @param data The data to export
 * @param options Export options including filename and formatting
 */
export const exportToExcel = (data: any[], options: ExportOptions) => {
  console.log("exportToExcel function called with", { 
    dataLength: data?.length,
    options 
  });

  if (!data || data.length === 0) {
    console.error("No data to export");
    toast.error("No data to export");
    return;
  }

  try {
    // Set default options
    const sheetName = options.sheetName || "Report";
    console.log(`Creating worksheet with sheetName: ${sheetName}`);
    
    // Create a worksheet
    const worksheet = XLSX.utils.json_to_sheet(data);
    console.log("Worksheet created successfully");
    
    // Apply column widths if provided
    if (options.columnWidths) {
      console.log("Applying column widths");
      worksheet['!cols'] = Object.entries(options.columnWidths).map(([key, width]) => ({
        wch: width
      }));
    }
    
    // Create a workbook
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);
    console.log("Workbook created and worksheet appended");
    
    // Generate Excel file
    console.log(`Writing Excel file with filename: ${options.filename}.xlsx`);
    XLSX.writeFile(workbook, `${options.filename}.xlsx`);
    console.log("Excel file written successfully");
    
    toast.success(`${options.filename} exported successfully`);
    return true;
  } catch (error) {
    console.error('Excel export error:', error);
    toast.error("Failed to export data");
    return false;
  }
};

/**
 * Export multiple datasets to Excel with each dataset in a separate sheet
 * @param datasets Object containing multiple datasets, with keys as sheet names
 * @param filename Filename for the exported Excel file
 */
export const exportMultipleSheets = (datasets: { [key: string]: any[] }, filename: string) => {
  if (!datasets || Object.keys(datasets).length === 0) {
    toast.error("No data to export");
    return;
  }

  try {
    // Create a workbook
    const workbook = XLSX.utils.book_new();
    
    // Add each dataset as a separate sheet
    Object.entries(datasets).forEach(([sheetName, data]) => {
      if (data && data.length > 0) {
        const worksheet = XLSX.utils.json_to_sheet(data);
        XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);
      }
    });
    
    // Generate Excel file
    XLSX.writeFile(workbook, `${filename}.xlsx`);
    
    toast.success(`${filename} exported successfully`);
    return true;
  } catch (error) {
    console.error('Multi-sheet Excel export error:', error);
    toast.error("Failed to export data");
    return false;
  }
};

/**
 * Format data for Excel export 
 * @param data Raw data that might need formatting
 * @param formatOptions Formatting options for specific fields
 */
export const formatDataForExport = (data: any[], formatOptions: { [key: string]: (value: any) => any } = {}) => {
  return data.map(item => {
    const formattedItem = { ...item };
    
    Object.entries(formatOptions).forEach(([key, formatter]) => {
      if (item[key] !== undefined) {
        formattedItem[key] = formatter(item[key]);
      }
    });
    
    return formattedItem;
  });
};

/**
 * Convert chart data to a simplified format for Excel export
 * @param chartData Data used for charts (may contain complex objects)
 */
export const prepareChartDataForExport = (chartData: any[]) => {
  // Filter out non-serializable properties and format data
  return chartData.map(item => {
    const exportableItem: any = {};
    
    Object.entries(item).forEach(([key, value]) => {
      // Skip complex objects like React refs, DOM elements, or functions
      if (
        typeof value !== 'function' &&
        !(value instanceof Element) &&
        !key.startsWith('_') &&
        key !== 'color' // Skip color property which is used only for UI
      ) {
        exportableItem[key] = value;
      }
    });
    
    return exportableItem;
  });
};