WORKING CODES IN INSTANTIATED PROCESS

import React, { useRef, useState, useEffect } from 'react'
import { HotTable } from '@handsontable/react'
import 'handsontable/dist/handsontable.full.min.css'
import Handsontable from 'handsontable'
import {
  registerControl,
  RegisterControlProps,
  RegisteredControlProperties,
} from '../hoc/registerControl'
import { CustomContentRenderer } from './utils/customButtonRenderer'
import { hyperformulaInstance, sheetId } from './utils/hyperformulaConfig'
import { reformatDate, reformatCurrency } from './utils/formatters'
import { SpreadsheetProperties } from '@components/features/FormBuilder/FormBuilderFieldProperties/Spreadsheet/types'
import { registerAllCellTypes } from 'handsontable/cellTypes'
import { registerAllPlugins } from 'handsontable/plugins'
import { useFormBuilderWorksheetGrid } from '../../hooks/useFormBuilderWorksheetGrid'
import { createSpreadsheetDefaultValue } from '../../FormBuilderFieldProperties/Spreadsheet/constants'
import { useFormBuilder } from '../../hooks/useFormBuilder'
import { useFormService } from '../../hooks/useFormService'

registerAllCellTypes()
registerAllPlugins()

type Properties = RegisteredControlProperties &
  SpreadsheetProperties & {
    externalVariables?: {
      [key: string]: {
        propertyName?: string
        defaultValue?: number
      }
    }
  }

const SpreadsheetControl: React.FC<RegisterControlProps<Properties>> = (
  props
) => {
  const hotTableRef = useRef<any>(null)
  const containerRef = useRef<HTMLDivElement>(null)

  const { rows = 1, columns = 1 } = props.properties || {}

  const DEFAULT_COLUMN_WIDTH = 100
  const DEFAULT_ROW_HEIGHT = 25

  const defaultMeta = {
    data: createSpreadsheetDefaultValue(rows, columns),
    cellFormats: {} as { [key: string]: 'date' | 'currency' | undefined },
    mergeCellsConfig: [],
    formulas: [],
  }

  const meta = props.properties?.meta || defaultMeta
  const { actions: formBuilderWorksheetActions } = useFormBuilderWorksheetGrid()
  const { actions, data: formBuilderData } = useFormBuilder()
  const { watch } = useFormService()
  const formData = watch()

  if (!actions || !actions.getAllFields) {
  }

  const [data, setData] = useState(
    () => props?.value ?? JSON.parse(JSON.stringify(meta.data))
  )
  const [cellFormats, setCellFormats] = useState(meta.cellFormats)
  const [mergeCellsConfig, setMergeCellsConfig] = useState(
    meta.mergeCellsConfig
  )

  const [count, setCount] = useState(0)

  const [formulas, setFormulas] = useState<
    { row: number; col: number; formula: string }[]
  >(meta.formulas || [])

  useEffect(() => {
    if (props.runtime && props.properties?.meta) {
      const {
        data: metaData,
        formulas: metaFormulas = [],

        mergeCellsConfig: metaMergeCells,
      } = props.properties.meta

      setData(props.value ?? metaData)
      setFormulas(metaFormulas)
      setMergeCellsConfig(metaMergeCells)

      metaFormulas.forEach(({ row, col, formula }) => {
        try {
          hyperformulaInstance.setCellContents({ sheet: sheetId, row, col }, [
            [formula],
          ])
        } catch (error) {}
      })

      hyperformulaInstance.rebuildAndRecalculate()
    }
  }, [props.runtime, props.properties?.meta])

  console.log('Meta:', props)

  // useEffect(() => {
  //   if (
  //     !props.externalVariables ||
  //     Object.keys(props.externalVariables).length === 0
  //   ) {
  //     return
  //   }

  //   const existingExpressions = new Set(
  //     hyperformulaInstance.listNamedExpressions()
  //   )

  //   Object.entries(props.externalVariables).forEach(([key, property]) => {
  //     const typedProperty = property as {
  //       propertyName?: string
  //       defaultValue?: number
  //     }
  //     const variableName = typedProperty.propertyName || key
  //     const value = Number(typedProperty.defaultValue || 0)

  //     if (!existingExpressions.has(variableName)) {
  //       try {
  //         hyperformulaInstance.addNamedExpression(variableName, value, sheetId)
  //         existingExpressions.add(variableName)
  //       } catch (error) {
  //         console.error(
  //           `Failed to add named expression: ${variableName}`,
  //           error
  //         )
  //       }
  //     } else {
  //       try {
  //         hyperformulaInstance.changeNamedExpression(
  //           variableName,
  //           value,
  //           sheetId
  //         )
  //       } catch (error) {
  //         console.error(
  //           `Failed to update named expression: ${variableName}`,
  //           error
  //         )
  //       }
  //     }
  //   })

  //   try {
  //     hyperformulaInstance.rebuildAndRecalculate()
  //     syncSpreadsheetWithEngine()
  //   } catch (error) {
  //     console.error('Error recalculating HyperFormula instance:', error)
  //   }
  // }, [props.externalVariables])
  // console.log('External Variable:', props.externalVariables)

  const syncSpreadsheetWithEngine = () => {
    if (hotTableRef.current?.hotInstance && typeof sheetId === 'number') {
      const updatedData = hyperformulaInstance.getSheetValues(sheetId)
      console.log('UpdatedData:', updatedData)
      try {
        hotTableRef.current.hotInstance.loadData(updatedData)
        updateFieldProperties({ data: updatedData })
      } catch (error) {}
    }
  }

  // useEffect(() => {
  //   if (formBuilderData) {
  //     const existingExpressions = hyperformulaInstance.listNamedExpressions()

  //     Object.entries(formBuilderData).forEach(([key, value]) => {
  //       const numericValue = Number(value) || 0

  //       if (!existingExpressions.includes(key)) {
  //         hyperformulaInstance.addNamedExpression(key, numericValue)
  //       } else {
  //         hyperformulaInstance.changeNamedExpression(key, numericValue)
  //       }
  //     })

  //     try {
  //       hyperformulaInstance.rebuildAndRecalculate()

  //       if (sheetId !== undefined) {
  //         const recalculatedData = hyperformulaInstance.getSheetValues(sheetId)
  //         setData(recalculatedData)
  //         updateFieldProperties({ data: recalculatedData })
  //       } else {
  //       }
  //     } catch (error) {}
  //   }
  // }, [formBuilderData])

  // useEffect(() => {
  //   if (formBuilderData?.fields) {
  //     const existingExpressions = new Set(
  //       hyperformulaInstance.listNamedExpressions()
  //     )

  //     formBuilderData.fields.forEach((field) => {
  //       const propertyName = field.properties?.propertyName
  //       const defaultValue = Number(field.properties?.defaultValue || 0)

  //       if (propertyName && !existingExpressions.has(propertyName)) {
  //         try {
  //           hyperformulaInstance.addNamedExpression(propertyName, defaultValue)
  //           existingExpressions.add(propertyName)
  //         } catch (error) {}
  //       } else if (propertyName) {
  //         try {
  //           hyperformulaInstance.changeNamedExpression(
  //             propertyName,
  //             defaultValue
  //           )
  //         } catch (error) {}
  //       }
  //     })

  //     try {
  //       hyperformulaInstance.rebuildAndRecalculate()
  //     } catch (error) {}
  //   }
  // }, [formBuilderData?.fields])

  // useEffect(() => {
  //   const handler = (changes: any) => {
  //     console.log('changes', changes)
  //     hyperformulaInstance.rebuildAndRecalculate()
  //   }

  //   hyperformulaInstance.on('valuesUpdated', handler)

  //   return () => {
  //     hyperformulaInstance.off('valuesUpdated', handler)
  //   }
  // }, [])

  useEffect(() => {
    if (formData && Object.keys(formData).length > 0) {
      console.log('Form Data:', formData)
      const existingExpressions = hyperformulaInstance.listNamedExpressions()

      Object.entries(formData).forEach(([key, value]) => {
        if (!existingExpressions.includes(`${key}`)) {
          hyperformulaInstance.addNamedExpression(`${key}`, Number(value) || 0)
        } else {
          hyperformulaInstance.changeNamedExpression(
            `${key}`,
            Number(value) || 0
          )
        }
        hyperformulaInstance.getNamedExpressionValue(key)
        console.log(
          `get named ${key}`,
          hyperformulaInstance.getNamedExpressionValue(key)
        )

        // hyperformulaInstance.setCellContents(
        //   { sheet: sheetId, row: 0, col: 0 },
        //   [[Number(value) || 0]]
        // )
      })

      hyperformulaInstance.rebuildAndRecalculate()
      // setCount((prev) => prev + 1)
      try {
        // setTimeout(() => {
        //   hyperformulaInstance.rebuildAndRecalculate()
        // }, 100)
      } catch (error) {}
    }
  }, [formData])

  useEffect(() => {
    const adjustedData = adjustDataDimensions(data, rows, columns)
    if (JSON.stringify(data) !== JSON.stringify(adjustedData)) {
      setData(adjustedData)
    }
  }, [rows, columns])

  const adjustDataDimensions = (
    currentData: any[][],
    rows: number,
    columns: number
  ) => {
    const newData = currentData.map((row) => [...row])
    if (newData.length < rows) {
      for (let i = newData.length; i < rows; i++) {
        newData.push(Array(columns).fill(null))
      }
    } else if (newData.length > rows) {
      newData.length = rows
    }
    newData.forEach((row) => {
      if (row.length < columns) {
        row.push(...Array(columns - row.length).fill(null))
      } else if (row.length > columns) {
        row.length = columns
      }
    })
    return newData
  }

  const handleAfterChange = (changes: any, source: string) => {
    if (!changes || source === 'loadData') return

    const validSheetId = sheetId ?? 0
    const updatedFormulas = [...formulas]

    changes.forEach(([row, col, oldValue, newValue]: any) => {
      if (
        newValue &&
        typeof newValue === 'string' &&
        newValue.startsWith('=')
      ) {
        try {
          updatedFormulas.push({ row, col, formula: newValue })
          setFormulas(updatedFormulas)

          hyperformulaInstance.setCellContents(
            { sheet: validSheetId, row, col },
            [[newValue]]
          )
        } catch (error) {}
      }
    })
    const updatedData = hotTableRef.current?.hotInstance?.getData?.()

    setData(updatedData)

    if (props.onChange) {
      props.onChange(updatedData)
    }
    try {
      hyperformulaInstance.rebuildAndRecalculate()
      // setData(hyperformulaInstance.getSheetValues)
      // console.log(
      //   'Get Sheet Value:',
      //   hyperformulaInstance.getSheetValues(sheetId!)
      // )
    } catch (error) {}
  }

  console.log('hotable', hotTableRef.current?.hotInstance?.getData?.())
  // const handleAfterChange = (changes: any, source: string) => {
  //   if (!changes || source === 'loadData') return

  //   const validSheetId = sheetId ?? 0
  //   const updatedFormulas = [...formulas]

  //   changes.forEach(([row, col, oldValue, newValue]: any) => {
  //     if (
  //       newValue &&
  //       typeof newValue === 'string' &&
  //       newValue.startsWith('=')
  //     ) {
  //       try {
  //         updatedFormulas.push({ row, col, formula: newValue })
  //         setFormulas(updatedFormulas)

  //         // hyperformulaInstance.setCellContents(
  //         //   { sheet: validSheetId, row, col },
  //         //   [[newValue]]
  //         // )
  //       } catch (error) {
  //         console.error('Error setting cell contents:', error)
  //       }
  //     }
  //   })

  //   try {
  //     hyperformulaInstance.rebuildAndRecalculate()

  //     const updatedData = hotTableRef.current?.hotInstance?.getData?.()

  //     setData(updatedData)

  //     if (props.onChange) {
  //       props.onChange(updatedData)
  //     }
  //   } catch (error) {
  //     console.error('Error rebuilding and recalculating formulas:', error)
  //   }
  // }

  console.log('hotable', hotTableRef.current?.hotInstance?.getData?.())

  const handleMergeCells = () => {
    const hotInstance = hotTableRef.current?.hotInstance
    const selected = hotInstance?.getSelected()
    if (selected) {
      const [startRow, startCol, endRow, endCol] = selected[0]
      const newMerge = {
        row: startRow,
        col: startCol,
        rowspan: endRow - startRow + 1,
        colspan: endCol - startCol + 1,
      }

      const updatedMergeCellsConfig = [...mergeCellsConfig, newMerge]
      hotInstance.updateSettings({ mergeCells: updatedMergeCellsConfig })
      setMergeCellsConfig(updatedMergeCellsConfig)

      updateFieldProperties({
        data,
        cellFormats,
        mergeCellsConfig: updatedMergeCellsConfig,
      })
    }
  }

  const handleUnmergeCells = () => {
    const hotInstance = hotTableRef.current?.hotInstance
    const selected = hotInstance?.getSelected()
    if (selected) {
      const [startRow, startCol] = selected[0]
      const mergeCellsPlugin = hotInstance.getPlugin('mergeCells')
      mergeCellsPlugin.unmerge(startRow, startCol)

      const updatedMergeCellsConfig = mergeCellsConfig.filter(
        (cell) => cell.row !== startRow || cell.col !== startCol
      )
      hotInstance.updateSettings({ mergeCells: updatedMergeCellsConfig })
      setMergeCellsConfig(updatedMergeCellsConfig)

      updateFieldProperties({
        data,
        cellFormats,
        mergeCellsConfig: updatedMergeCellsConfig,
      })
    }
  }

  const handleSetAsDate = () => {
    const selected = hotTableRef.current?.hotInstance.getSelected()
    if (selected) {
      const [row, col] = selected[0]
      const newFormats = { ...cellFormats, [`${row}-${col}`]: 'date' as const }
      setCellFormats(newFormats)
      updateFieldProperties({ data, cellFormats: newFormats, mergeCellsConfig })
    }
  }

  const handleSetAsCurrency = () => {
    const selected = hotTableRef.current?.hotInstance.getSelected()
    if (selected) {
      const [row, col] = selected[0]
      const newFormats = {
        ...cellFormats,
        [`${row}-${col}`]: 'currency' as const,
      }
      setCellFormats(newFormats)
      updateFieldProperties({ data, cellFormats: newFormats, mergeCellsConfig })
    }
  }

  const updateFieldProperties = (updatedMeta: any) => {
    formBuilderWorksheetActions?.setFieldConfigProperty(props.config?.id!, {
      meta: {
        data,
        formulas,
        mergeCellsConfig,
        ...updatedMeta,
      },
    })
  }

  const cellsHandler = (row: number, col: number) => {
    const cellProperties = {} as Handsontable.CellProperties
    cellProperties.width = DEFAULT_COLUMN_WIDTH
    cellProperties.height = DEFAULT_ROW_HEIGHT

    const format = cellFormats?.[`${row}-${col}`]

    if (format === 'date') {
      cellProperties.renderer = (
        instance,
        td,
        row,
        col,
        prop,
        value,
        cellProperties
      ) =>
        CustomContentRenderer(instance, td, row, col, () => {
          const span = document.createElement('span')
          span.innerText = reformatDate(
            instance.getDataAtCell(row, col) as string
          )
          return span
        })
    } else if (format === 'currency') {
      cellProperties.renderer = (
        instance,
        td,
        row,
        col,
        prop,
        value,
        cellProperties
      ) =>
        CustomContentRenderer(instance, td, row, col, () => {
          const span = document.createElement('span')
          span.innerText = reformatCurrency(
            instance.getDataAtCell(row, col) as string
          )
          return span
        })
    }

    return cellProperties
  }

  return (
    <div
      ref={containerRef}
      className="h-[400px] w-1/2 resize-x overflow-auto border md:w-full"
      style={{ minWidth: '50%', maxWidth: '100%', resize: 'horizontal' }}
      onMouseDown={(e) => e.stopPropagation()}
    >
      <HotTable
        id="mySpreadsheet"
        data={data}
        colHeaders={true}
        rowHeaders={true}
        width="100%"
        height="100%"
        selectionMode="multiple"
        copyPaste={true}
        contextMenu={{
          items: {
            row_above: {},
            row_below: {},
            col_left: {},
            col_right: {},
            remove_row: {},
            remove_col: {},
            clear_column: {},
            mergeCells: { name: 'Merge Cells', callback: handleMergeCells },
            unmergeCells: {
              name: 'Unmerge Cells',
              callback: handleUnmergeCells,
            },
            set_as_date: { name: 'Set as Date', callback: handleSetAsDate },
            set_as_currency: {
              name: 'Set as Currency',
              callback: handleSetAsCurrency,
            },
          },
        }}
        ref={hotTableRef}
        afterChange={handleAfterChange}
        persistentState={true}
        licenseKey="non-commercial-and-evaluation"
        manualColumnResize={false}
        manualRowResize={false}
        autoColumnSize={false}
        autoRowSize={false}
        stretchH="all"
        mergeCells={mergeCellsConfig}
        formulas={{ engine: hyperformulaInstance }}
        cells={cellsHandler}
        colWidths={DEFAULT_COLUMN_WIDTH}
        rowHeights={DEFAULT_ROW_HEIGHT}
      />
    </div>
  )
}

export default registerControl<Properties>(SpreadsheetControl, {
  noDisplayLabel: true,
})