let XLSXInstance: any = null
const getXLSX = async () => {
  if (!XLSXInstance) {
    XLSXInstance = await import('xlsx')
  }
  return XLSXInstance
}

/**
 * 导出Excel文件
 * @param {Array} data - 导出的数据
 * @param {String} filename - 文件名
 * @param {String} sheetName - 工作表名称
 */
export const exportToExcel = async (data:any, filename:string = 'export.xlsx', sheetName:string = 'Sheet1') => {
  const XLSX = await getXLSX()
  const wb = XLSX.utils.book_new()
  const ws = XLSX.utils.json_to_sheet(data)
  XLSX.utils.book_append_sheet(wb, ws, sheetName)
  XLSX.writeFile(wb, filename)
}

/**
 * 导出多工作表Excel文件
 * @param {Object} sheetData - 包含多个工作表数据的对象 {sheetName1: data1, sheetName2: data2}
 * @param {String} filename - 文件名
 */
export const exportMultiSheetExcel = async (sheetData:any, filename:string = 'export.xlsx') => {
  const XLSX = await getXLSX()
  const wb = XLSX.utils.book_new()
  Object.keys(sheetData).forEach(sheetName => {
    const ws = XLSX.utils.json_to_sheet(sheetData[sheetName])
    XLSX.utils.book_append_sheet(wb, ws, sheetName)
  })
  XLSX.writeFile(wb, filename)
}

/**
 * 自定义表头的Excel导出
 * @param {Array} data - 数据
 * @param {Array} headers - 表头配置 [{key: 'name', title: '姓名', width: 20}, ...]
 * @param {String} filename - 文件名
 * @param {String} sheetName - 工作表名称
 */
export const exportWithCustomHeaders = async (data:any, headers:any, filename:string = 'export.xlsx', sheetName:string = 'Sheet1') => {
  const XLSX = await getXLSX()
  const exportData = data.map(item => {
    const row = {}
    headers.forEach(header => {
      row[header.title] = item[header.key]
    })
    return row
  })
  const wb = XLSX.utils.book_new()
  const ws = XLSX.utils.json_to_sheet(exportData)
  
  // 设置列宽
  const colWidths = headers.map(header => ({ wch: header.width || 15 }))
  ws['!cols'] = colWidths
  
  XLSX.utils.book_append_sheet(wb, ws, sheetName)
  XLSX.writeFile(wb, filename)
}


/**
 * 高级对象数组处理 - 支持展开模式
 * @param {Array} data - 数据
 * @param {Array} headers - 表头配置
 *   普通字段: {key: 'name', title: '姓名'}
 *   对象数组展开: {
 *     key: 'projects',
 *     title: '项目',
 *     arrayType: 'object',
 *     expand: true, // 展开模式
 *     fieldMappings: { // 字段映射
 *       name: '项目名称',
 *       role: '项目角色'
 *     }
 *   }
 * @param {String} filename - 文件名
 * @param {String} sheetName - 工作表名称
 */
export const exportWithExpandedObjectArrays = async (data:any, headers:any, filename:string = 'export.xlsx', sheetName:string = 'Sheet1') => {
  const XLSX = await getXLSX()
  let processedData = [...data]
  let processedHeaders = [...headers]

  const expandFields = headers.filter(header =>
    header.arrayType === 'object' && header.expand
  )

  if (expandFields.length > 0) {
    expandFields.forEach(field => {
      const maxLength = Math.max(...data.map(item => {
        const arr = item[field.key]
        return Array.isArray(arr) ? arr.length : 0
      }))

      processedHeaders = processedHeaders.filter(h => h.key !== field.key)

      for (let i = 0; i < maxLength; i++) {
        const baseKey = `${field.key}[${i}]`
        const baseTitle = `${field.title}[${i + 1}]`

        if (field.fieldMappings) {
          Object.keys(field.fieldMappings).forEach(sourceField => {
            processedHeaders.push({
              key: `${baseKey}.${sourceField}`,
              title: `${baseTitle} - ${field.fieldMappings[sourceField]}`
            })
          })
        } else {
          processedHeaders.push({
            key: `${baseKey}`,
            title: baseTitle
          })
        }
      }
    })

    processedData = data.map(item => {
      const newItem = { ...item }

      expandFields.forEach(field => {
        const arr = item[field.key]
        if (Array.isArray(arr)) {
          for (let i = 0; i < arr.length; i++) {
            const obj = arr[i]
            if (typeof obj === 'object' && obj !== null) {
              if (field.fieldMappings) {
                Object.keys(field.fieldMappings).forEach(sourceField => {
                  newItem[`${field.key}[${i}].${sourceField}`] = obj[sourceField]
                })
              } else {
                Object.keys(obj).forEach(sourceField => {
                  newItem[`${field.key}[${i}].${sourceField}`] = obj[sourceField]
                })
              }
            }
          }
        }
      })

      return newItem
    })
  }

  const exportData = processedData.map(item => {
    const row = {}

    processedHeaders.forEach(header => {
      if (header.arrayType === 'object' && !header.expand) {
        const value = item[header.key]
        if (Array.isArray(value)) {
          if (header.extractFields && header.extractFields.length > 0) {
            const extractedValues = value.map(obj => {
              if (typeof obj === 'object' && obj !== null) {
                return header.extractFields.map(field => obj[field]).join(' ')
              }
              return obj
            })
            row[header.title] = extractedValues.join(header.joinWith || ', ')
          } else {
            const extractedValues = value.map(obj => {
              if (typeof obj === 'object' && obj !== null) {
                return Object.keys(obj).map(key => `${key}: ${obj[key]}`).join(' ')
              }
              return obj
            })
            row[header.title] = extractedValues.join(header.joinWith || ', ')
          }
        } else {
          row[header.title] = value
        }
      } else if (!header.expand) {
        const value = item[header.key]
        if (Array.isArray(value)) {
          row[header.title] = value.join(header.joinWith || ', ')
        } else {
          row[header.title] = value
        }
      } else {
        row[header.title] = item[header.key]
      }
    })

    return row
  })

  const wb = XLSX.utils.book_new()
  const ws = XLSX.utils.json_to_sheet(exportData)
  XLSX.utils.book_append_sheet(wb, ws, sheetName)
  XLSX.writeFile(wb, filename)
}