import { ColumnDef } from "@tanstack/react-table"
import ExcelJS from "exceljs"
import { saveAs } from "file-saver"

export async function exportToExcel<TData>(
  data: TData[],
  columns: ColumnDef<TData, any>[],
  filename: string = "export.xlsx"
) {
  const workbook = new ExcelJS.Workbook()
  const worksheet = workbook.addWorksheet("Data")

  // Filter out utility columns and resolve headers
  const exportableColumns = columns.filter(
    (col) => 
      col.id !== "select" && 
      col.id !== "actions" && 
      // @ts-ignore - simple check for now
      (typeof col.header === "string" || typeof col.accessorKey === "string")
  )

  // Setup columns
  worksheet.columns = exportableColumns.map((col) => {
    let headerText = ""
    if (typeof col.header === "string") {
      headerText = col.header
    } else if (typeof col.accessorKey === "string") {
      headerText = col.accessorKey
    }

    return {
      header: headerText,
      key: (col.accessorKey as string) || col.id,
      width: 20, 
    }
  })

  // Add rows
  data.forEach((row) => {
    const rowData: any = {}
    exportableColumns.forEach((col) => {
      const key = (col.accessorKey as string) || col.id
      if (key) {
        const value = getValueByPath(row, key)
        rowData[key] = value
      }
    })
    worksheet.addRow(rowData)
  })

  worksheet.getRow(1).font = { bold: true }

  const buffer = await workbook.xlsx.writeBuffer()
  const blob = new Blob([buffer], {
    type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
  })
  saveAs(blob, filename)
}

function getValueByPath(obj: any, path: string) {
  return path.split('.').reduce((acc, part) => acc && acc[part], obj)
}

interface CreateTemplateOptions<TData> {
  columns: ColumnDef<TData, any>[]
  filename?: string
  includeColumns?: { key: string; header: string }[]
  excludeColumns?: string[] // accessorKey or id to exclude
}

export async function createExcelTemplate<TData>({
  columns,
  filename = "template.xlsx",
  includeColumns = [],
  excludeColumns = [],
}: CreateTemplateOptions<TData>) {
  const workbook = new ExcelJS.Workbook()
  const worksheet = workbook.addWorksheet("Template")

  // 1. Filter columns from definition
  const baseColumns = columns.filter((col) => {
    const key = (col.accessorKey as string) || col.id
    
    // Skip system columns
    if (col.id === "select" || col.id === "actions") return false
    
    // Skip excluded columns
    if (excludeColumns.includes(key!) || (col.id && excludeColumns.includes(col.id))) return false

    return true
  })

  // 2. Map to ExcelJS columns
  const excelColumns = baseColumns.map((col) => {
    let headerText = ""
    if (typeof col.header === "string") {
      headerText = col.header
    } else if (typeof col.accessorKey === "string") {
      headerText = col.accessorKey
    }
    
    return {
      header: headerText,
      key: (col.accessorKey as string) || col.id,
      width: 20
    }
  })

  // 3. Add extra included columns
  includeColumns.forEach((col) => {
    excelColumns.push({
      header: col.header,
      key: col.key,
      width: 20
    })
  })

  worksheet.columns = excelColumns

  // Style Header
  const headerRow = worksheet.getRow(1)
  headerRow.font = { bold: true }
  headerRow.fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFD3D3D3' } // Light Gray
  }

  // Add Data Validation or Comments if needed (future expansion)

  const buffer = await workbook.xlsx.writeBuffer()
  const blob = new Blob([buffer], {
    type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
  })
  saveAs(blob, filename)
}