import ExcelJS from "exceljs"

interface ImportExcelOptions {
  file: File
  /**
   * Map Excel header names to data keys.
   * Example: { "Name": "name", "Age": "age" }
   */
  columnMapping?: Record<string, string>
  /**
   * Row offset to start reading data (0-based).
   * Default: 1 (assuming row 0 is header)
   */
  dataStartRow?: number
}

export interface ExcelImportResult<T = any> {
  data: T[]
  errors: string[]
}

/**
 * Generic function to read an Excel file and convert it to an array of objects.
 * Does NOT handle database insertion or validation logic.
 */
export async function importFromExcel<T = any>({
  file,
  columnMapping = {},
  dataStartRow = 1,
}: ImportExcelOptions): Promise<ExcelImportResult<T>> {
  const workbook = new ExcelJS.Workbook()
  const arrayBuffer = await file.arrayBuffer()
  
  try {
    await workbook.xlsx.load(arrayBuffer)
  } catch (error) {
     return { data: [], errors: ["Failed to parse Excel file. Please ensure it is a valid .xlsx file."] }
  }

  const worksheet = workbook.worksheets[0] // Read the first sheet
  const data: T[] = []
  const errors: string[] = []

  if (!worksheet) {
    return { data: [], errors: ["No worksheet found in the Excel file."] }
  }

  // 1. Read Header Row (assumed to be row 1 for mapping if no explicit mapping provided, 
  //    or we can use it to validate mapping)
  const headers: string[] = []
  const headerRow = worksheet.getRow(1)
  headerRow.eachCell((cell, colNumber) => {
    headers[colNumber] = String(cell.value).trim()
  })

  // 2. Iterate Data Rows
  worksheet.eachRow((row, rowNumber) => {
    if (rowNumber <= dataStartRow) return // Skip header/pre-header rows

    const rowData: any = {}
    let hasData = false

    row.eachCell((cell, colNumber) => {
      const headerText = headers[colNumber]
      if (!headerText) return

      // Determine the key to use for this column
      // Priority: Explicit mapping -> Header text as key
      const key = columnMapping[headerText] || headerText

      let cellValue = cell.value

      // Handle ExcelJS object values (e.g. formula results, hyperlinks)
      if (cellValue && typeof cellValue === 'object') {
        if ('result' in cellValue) {
             // Formula result
             cellValue = (cellValue as any).result
        } else if ('text' in cellValue) {
             // Hyperlink text
             cellValue = (cellValue as any).text
        } else if ('richText' in cellValue) {
             // Rich text
             cellValue = (cellValue as any).richText.map((t: any) => t.text).join('')
        }
      }
      
      if (cellValue !== null && cellValue !== undefined && String(cellValue).trim() !== '') {
          hasData = true
          rowData[key] = cellValue
      }
    })

    if (hasData) {
      data.push(rowData as T)
    }
  })

  return { data, errors }
}