path: root/lib/export-to-excel.ts

// lib/utils/export-to-excel.ts

import ExcelJS from 'exceljs'

interface ExportToExcelOptions {
  filename?: string
  sheetName?: string
  headers?: string[]
  dateFormat?: string
  autoFilter?: boolean
  freezeHeader?: boolean
}

/**
 * 데이터 배열을 Excel 파일로 내보내기 (ExcelJS 사용)
 * @param data - 내보낼 데이터 배열
 * @param options - 내보내기 옵션
 */
export async function exportDataToExcel(
  data: Record<string, any>[],
  options: ExportToExcelOptions = {}
) {
  const {
    filename = 'export',
    sheetName = 'Sheet1',
    headers,
    dateFormat = 'yyyy-mm-dd',
    autoFilter = true,
    freezeHeader = true
  } = options

  try {
    // 데이터가 없으면 반환
    if (!data || data.length === 0) {
      console.warn('No data to export')
      return false
    }

    // 워크북 생성
    const workbook = new ExcelJS.Workbook()
    workbook.creator = 'TBE System'
    workbook.created = new Date()
    
    // 워크시트 추가
    const worksheet = workbook.addWorksheet(sheetName, {
      properties: {
        defaultRowHeight: 20
      }
    })

    // 헤더 처리
    const finalHeaders = headers || Object.keys(data[0])
    
    // 컬럼 정의
    const columns = finalHeaders.map(header => ({
      header,
      key: header,
      width: Math.min(
        Math.max(
          header.length,
          ...data.map(row => {
            const value = row[header]
            if (value === null || value === undefined) return 0
            return String(value).length
          })
        ) + 2,
        50
      )
    }))
    
    worksheet.columns = columns

    // 데이터 추가
    data.forEach(row => {
      const rowData: Record<string, any> = {}
      
      finalHeaders.forEach(header => {
        const value = row[header]
        
        // null/undefined 처리
        if (value === null || value === undefined) {
          rowData[header] = ''
        }
        // Date 객체 처리
        else if (value instanceof Date) {
          rowData[header] = value
        }
        // boolean 처리
        else if (typeof value === 'boolean') {
          rowData[header] = value ? 'Yes' : 'No'
        }
        // 숫자 처리
        else if (typeof value === 'number') {
          rowData[header] = value
        }
        // 기타 (문자열 등)
        else {
          rowData[header] = String(value)
        }
      })
      
      worksheet.addRow(rowData)
    })

    // 헤더 스타일링
    const headerRow = worksheet.getRow(1)
    headerRow.font = { bold: true }
    headerRow.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE0E0E0' }
    }
    headerRow.alignment = { vertical: 'middle', horizontal: 'center' }
    headerRow.height = 25

    // 헤더 테두리
    headerRow.eachCell((cell) => {
      cell.border = {
        top: { style: 'thin' },
        left: { style: 'thin' },
        bottom: { style: 'thin' },
        right: { style: 'thin' }
      }
    })

    // 데이터 행 스타일링
    worksheet.eachRow((row, rowNumber) => {
      if (rowNumber > 1) {
        row.alignment = { vertical: 'middle' }
        row.eachCell((cell) => {
          cell.border = {
            top: { style: 'thin' },
            left: { style: 'thin' },
            bottom: { style: 'thin' },
            right: { style: 'thin' }
          }
        })
      }
    })

    // 자동 필터 추가
    if (autoFilter) {
      worksheet.autoFilter = {
        from: { row: 1, column: 1 },
        to: { row: data.length + 1, column: columns.length }
      }
    }

    // 헤더 고정
    if (freezeHeader) {
      worksheet.views = [
        { state: 'frozen', ySplit: 1 }
      ]
    }

    // 날짜 포맷 적용
    worksheet.eachRow((row, rowNumber) => {
      if (rowNumber > 1) {
        row.eachCell((cell, colNumber) => {
          const header = finalHeaders[colNumber - 1]
          const value = data[rowNumber - 2][header]
          
          if (value instanceof Date) {
            cell.numFmt = dateFormat === 'yyyy-mm-dd' 
              ? 'yyyy-mm-dd' 
              : 'mm/dd/yyyy'
          }
          // 숫자 포맷 (천단위 구분)
          else if (typeof value === 'number' && header.toLowerCase().includes('quantity')) {
            cell.numFmt = '#,##0'
          }
        })
      }
    })

    // 파일 다운로드
    const buffer = await workbook.xlsx.writeBuffer()
    const blob = new Blob([buffer], { 
      type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' 
    })
    
    const url = URL.createObjectURL(blob)
    const link = document.createElement('a')
    const timestamp = new Date().toISOString().slice(0, 10)
    const finalFilename = `${filename}_${timestamp}.xlsx`
    
    link.href = url
    link.download = finalFilename
    link.style.display = 'none'
    
    document.body.appendChild(link)
    link.click()
    document.body.removeChild(link)
    
    URL.revokeObjectURL(url)

    return true
  } catch (error) {
    console.error('Excel export error:', error)
    throw new Error('Failed to export Excel file')
  }
}

/**
 * Date 객체를 Excel 형식으로 포맷팅
 */
function formatDateForExcel(date: Date, format: string): string {
  const year = date.getFullYear()
  const month = String(date.getMonth() + 1).padStart(2, '0')
  const day = String(date.getDate()).padStart(2, '0')
  
  switch (format) {
    case 'yyyy-mm-dd':
      return `${year}-${month}-${day}`
    case 'dd/mm/yyyy':
      return `${day}/${month}/${year}`
    case 'mm/dd/yyyy':
      return `${month}/${day}/${year}`
    default:
      return date.toLocaleDateString()
  }
}

/**
 * CSV 파일로 내보내기 (대안 옵션)
 */
export function exportDataToCSV(
  data: Record<string, any>[],
  filename: string = 'export'
) {
  try {
    if (!data || data.length === 0) {
      console.warn('No data to export')
      return
    }

    // 헤더 추출
    const headers = Object.keys(data[0])
    
    // CSV 문자열 생성
    let csvContent = headers.join(',') + '\n'
    
    data.forEach(row => {
      const values = headers.map(header => {
        const value = row[header]
        
        // null/undefined 처리
        if (value === null || value === undefined) return ''
        
        // 콤마나 줄바꿈이 있으면 따옴표로 감싸기
        const stringValue = String(value)
        if (stringValue.includes(',') || stringValue.includes('\n')) {
          return `"${stringValue.replace(/"/g, '""')}"`
        }
        
        return stringValue
      })
      
      csvContent += values.join(',') + '\n'
    })

    // BOM 추가 (Excel에서 UTF-8 인식)
    const BOM = '\uFEFF'
    const blob = new Blob([BOM + csvContent], { type: 'text/csv;charset=utf-8;' })
    
    // 다운로드 링크 생성
    const link = document.createElement('a')
    const url = URL.createObjectURL(blob)
    
    link.setAttribute('href', url)
    link.setAttribute('download', `${filename}_${new Date().toISOString().slice(0, 10)}.csv`)
    link.style.visibility = 'hidden'
    
    document.body.appendChild(link)
    link.click()
    document.body.removeChild(link)
    
    return true
  } catch (error) {
    console.error('CSV export error:', error)
    throw new Error('Failed to export CSV file')
  }
}

/**
 * 간단한 데이터 내보내기 헬퍼
 * Excel이 안되면 CSV로 fallback
 */
export async function exportData(
  data: Record<string, any>[],
  options: ExportToExcelOptions & { format?: 'excel' | 'csv' } = {}
) {
  const { format = 'excel', ...exportOptions } = options
  
  try {
    if (format === 'csv') {
      return exportDataToCSV(data, exportOptions.filename)
    } else {
      return exportDataToExcel(data, exportOptions)
    }
  } catch (error) {
    console.error(`Failed to export as ${format}, trying CSV as fallback`)
    
    // Excel 실패 시 CSV로 시도
    if (format === 'excel') {
      try {
        return exportDataToCSV(data, exportOptions.filename)
      } catch (csvError) {
        console.error('Both Excel and CSV export failed')
        throw csvError
      }
    }
    
    throw error
  }
}