//src\components\ReportSearchBox\SearchBox2.tsx
"use client";

import Grid from "@mui/material/Grid";
import Card from "@mui/material/Card";
import CardContent from "@mui/material/CardContent";
import Typography from "@mui/material/Typography";
import React, { useCallback, useMemo, useState } from "react";
import { useTranslation } from "react-i18next";
import TextField from "@mui/material/TextField";
import FormControl from "@mui/material/FormControl";
import InputLabel from "@mui/material/InputLabel";
import Select, { SelectChangeEvent } from "@mui/material/Select";
import MenuItem from "@mui/material/MenuItem";
import CardActions from "@mui/material/CardActions";
import Button from "@mui/material/Button";
import RestartAlt from "@mui/icons-material/RestartAlt";
import Search from "@mui/icons-material/Search";
import dayjs from "dayjs";
import "dayjs/locale/zh-hk";
import { DatePicker } from "@mui/x-date-pickers/DatePicker";
import { LocalizationProvider } from "@mui/x-date-pickers/LocalizationProvider";
import { AdapterDayjs } from "@mui/x-date-pickers/AdapterDayjs";
import { Box } from "@mui/material";
import * as XLSX from 'xlsx-js-style';
//import { DownloadReportButton } from '../LateStartReportGen/DownloadReportButton';

interface BaseCriterion<T extends string> {
  label: string;
  label2?: string;
  paramName: T;
  paramName2?: T;
}

interface TextCriterion<T extends string> extends BaseCriterion<T> {
  type: "text";
}

interface SelectCriterion<T extends string> extends BaseCriterion<T> {
  type: "select";
  options: string[];
}

interface DateRangeCriterion<T extends string> extends BaseCriterion<T> {
  type: "dateRange";
}

export type Criterion<T extends string> =
  | TextCriterion<T>
  | SelectCriterion<T>
  | DateRangeCriterion<T>;

interface Props<T extends string> {
  criteria: Criterion<T>[];
  onSearch: (inputs: Record<T, string>) => void;
  onReset?: () => void;
}

function SearchBox<T extends string>({
  criteria,
  onSearch,
  onReset,
}: Props<T>) {
  const { t } = useTranslation("common");
  const defaultInputs = useMemo(
    () =>
      criteria.reduce<Record<T, string>>(
        (acc, c) => {
          return { ...acc, [c.paramName]: c.type === "select" ? "All" : "" };
        },
        {} as Record<T, string>,
      ),
    [criteria],
  );
  const [inputs, setInputs] = useState(defaultInputs);

  const makeInputChangeHandler = useCallback(
    (paramName: T): React.ChangeEventHandler<HTMLInputElement> => {
      return (e) => {
        setInputs((i) => ({ ...i, [paramName]: e.target.value }));
      };
    },
    [],
  );

  const makeSelectChangeHandler = useCallback((paramName: T) => {
    return (e: SelectChangeEvent) => {
      setInputs((i) => ({ ...i, [paramName]: e.target.value }));
    };
  }, []);

  const makeDateChangeHandler = useCallback((paramName: T) => {
    return (e: any) => {
      setInputs((i) => ({ ...i, [paramName]: dayjs(e).format("YYYY-MM-DD") }));
    };
  }, []);

  const makeDateToChangeHandler = useCallback((paramName: T) => {
    return (e: any) => {
      setInputs((i) => ({
        ...i,
        [paramName + "To"]: dayjs(e).format("YYYY-MM-DD"),
      }));
    };
  }, []);

  interface CellValue {
    v: number | string; // Value of the cell
    t: 'n' | 's'; // Type of the cell value: 'n' for number, 's' for string
    s?: XLSX.CellStyle; // Optional style for the cell
  }

  const handleReset = () => {
    setInputs(defaultInputs);
    onReset?.();
  };

  const handleSearch = () => {
    onSearch(inputs);
    
  };

  // Function to merge cells from A2:B2 to A14:B14
function mergeCells(worksheet: XLSX.WorkSheet) {
  // Ensure the 'merges' array exists in the worksheet
  if (!worksheet['!merges']) worksheet['!merges'] = [];

  // Loop through rows 2 to 14 (0-indexed + 1)
  for (let row = 1; row <= 13; row++) {
    // Define the range for current row to merge A and B columns
    const mergeRange = {
      s: { c: 0, r: row }, // Start cell (Column A)
      e: { c: 1, r: row }  // End cell (Column B)
    };
    // Add the range to the 'merges' array in the worksheet
    worksheet['!merges'].push(mergeRange);
    // Apply center alignment to the merged cell
    const mergedCellRef = XLSX.utils.encode_cell({ c: 0, r: row });
    if (!worksheet[mergedCellRef]) {
      worksheet[mergedCellRef] = {}; // Create the cell if it doesn't exist
    }
    worksheet[mergedCellRef].s = {
      alignment: { horizontal: "left", wrapText: true }
    };
  }
}

// Processing and inserting table data with calculations
function processDataAndInsert(worksheet: XLSX.WorkSheet, startRow:number, data:(string|number)[][]) {
  data.forEach((row, rowIndex) => {
    const r = startRow + rowIndex;

    // Direct assignments for columns A-F as strings
    const stringCols = ['A', 'B', 'C', 'D', 'E', 'F'];
    stringCols.forEach((col, index) => {
      const cellRef = col + r;
      worksheet[cellRef] = { v: row[index], t: 's' }; // Force type as string
    });

    // Assignments for columns G-O as numbers
    const numberCols = ['G', 'H', 'I', 'K', 'N'];
    const colIndices = [6, 7, 8, 9, 10]; // Indices in the data array corresponding to G, H, I, K, N
    numberCols.forEach((col, index) => {
      const cellRef = col + r;
      worksheet[cellRef] = { v: row[colIndices[index]], t: 'n' }; // Force type as number
    });

    // Calculations for columns J, L, M, O
    const h = row[6] as number;
    const i = row[7] as number;
    const k = row[9] as number;
    const n = row[10] as number;

    // Column J: H - I
    worksheet['J' + r] = { v: h - i, t: 'n' };

    // Column L: IF(H<I, H-K, I-K)
    worksheet['L' + r] = { v: h < i ? h - k : i - k, t: 'n' };

    // Column M: K / I
    worksheet['M' + r] = { v: k / i, t: 'n' };

    // Column O: K - N
    worksheet['O' + r] = { v: k - n, t: 'n' };
  });
  // Calculate sums for specified columns and insert them in the next row after the last data row
  const sumRow = startRow + data.length;

  // // Define the range of cells to merge 
  // const mergeRangeA1 = {
  //   s: { c: 0, r: sumRow-1}, // Start cell
  //   e: { c: 5, r: sumRow-1}  // End cell
  // };
  // // Add the range to the 'merges' array in the worksheet if it doesn't exist
  // if (!worksheet['!merges']) worksheet['!merges'] = [];
  // worksheet['!merges'].push(mergeRangeA1);
  // // Apply center alignment to the merged cell


  const sumCols = ['G', 'H', 'I', 'J', 'K', 'L', 'N', 'O'];
  sumCols.forEach(col => {
    const cellRefs = data.map((_, index) => col + (startRow + index));
    const formula = `=SUM(${cellRefs.join(',')})`;
    worksheet[col + sumRow] = { f: formula, t: 'n', s: {
      border: {
        top: {style: 'thin', color: {auto: 1}},
        bottom: {style: 'double', color: {auto: 1}}
      }
    }};
  });
  XLSX.utils.sheet_add_aoa(worksheet, [['Sub-total']], { origin: { c: 0, r: (sumRow-1) } });

//   const mergedCellRefA1 = XLSX.utils.encode_cell({ c: 0, r: sumRow-1});
//   if (!worksheet[mergedCellRefA1]) {
//     worksheet[mergedCellRefA1] = {}; // Create the cell if it doesn't exist
//   }
// // Apply right alignment, center vertical alignment, wrap text, and border styles to the 'Sub-total' cell
// worksheet[mergedCellRefA1].s = {
//     alignment: {horizontal: "right", vertical: "center", wrapText: true},
//     border: {
//         top: {style: 'thin', color: {auto: 1}},
//         bottom: {style: 'double', color: {auto: 1}}
//     }
// };
// Define the range of cells to merge for 'Sub-total'
const mergeRangeSubTotal = {
  s: { c: 0, r: sumRow-1}, // Start at column A
  e: { c: 5, r: sumRow-1}  // End at column F
};
// // Add the range to the 'merges' array in the worksheet if it doesn't exist
// if (!worksheet['!merges']) worksheet['!merges'] = [];
// worksheet['!merges'].push(mergeRangeSubTotal);

// Update styles for the merged cell range where 'Sub-total' is located
const mergedCellRefSubTotal = XLSX.utils.encode_cell({ c: 0, r: sumRow-1 });
if (!worksheet[mergedCellRefSubTotal]) {
  worksheet[mergedCellRefSubTotal] = {}; // Create the cell if it doesn't exist
}
worksheet[mergedCellRefSubTotal].s = {
  alignment: {horizontal: "right", vertical: "center", wrapText: true},
  border: {
      top: {style: 'thin', color: {auto: 1}},
      bottom: {style: 'double', color: {auto: 1}}}
};
// Add the range to the 'merges' array in the worksheet if it doesn't exist
if (!worksheet['!merges']) worksheet['!merges'] = [];
worksheet['!merges'].push(mergeRangeSubTotal)


const mergedCellRefM1 = XLSX.utils.encode_cell({ c: 12, r: sumRow});
if (!worksheet[mergedCellRefM1]) {
  worksheet[mergedCellRefM1] = {}; // Create the cell if it doesn't exist
}
worksheet[mergedCellRefM1].s = {
  alignment: {horizontal: "right", vertical: "center", wrapText: true},
  border: {
      top: {style: 'thin', color: {auto: 1}},
      bottom: {style: 'double', color: {auto: 1}}
  }
};

}
const firstTableData = [ 
  ['Code1', 'PJName1', 'Client1','Team1','2011/01/01','2011/02/01','625','500','350','350','171'], // Row 1
  ['Code2', 'PJName2', 'Client2','Team2','2011/03/01','2011/04/01','1000','800','565','565','565'],// Row 2
  ['Code2', 'PJName2', 'Client2','Team2','2011/03/01','2011/04/01','1000','800','565','565','565'],// Row 3
  // ... more rows as needed
];

  const handleDownload = async () => {
    //setIsLoading(true);

    try {
      const response = await fetch('/temp/EX01_Financial Status Report.xlsx', {
        headers: {
          'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        },
      });
      if (!response.ok) throw new Error('Network response was not ok.');

      const data = await response.blob();
      const reader = new FileReader();
      reader.onload = (e) => {
        if (e.target && e.target.result) {
          const ab = e.target.result as ArrayBuffer;
          const workbook = XLSX.read(ab, { type: 'array' });
          const firstSheetName = workbook.SheetNames[0];
          const worksheet = workbook.Sheets[firstSheetName];
          
          // Add the current date to cell C2
          const cellAddress = 'C2';
          const date = new Date().toISOString().split('T')[0]; // Format YYYY-MM-DD
          const formattedDate = date.replace(/-/g, '/'); // Change format to YYYY/MM/DD
          XLSX.utils.sheet_add_aoa(worksheet, [[formattedDate]], { origin: cellAddress });

        mergeCells(worksheet);

          // Style for cell A1: Font size 16 and bold
        if (worksheet['A1']) {
          worksheet['A1'].s = {
            font: {bold: true,sz: 16,},alignment: { horizontal: 'center' }  // Font size 16 //name: 'Times New Roman' // Specify font
          };
        }

          // Apply styles from A2 A3 A5 (bold)
        ['A2', 'A3', 'A5','A14'].forEach(cell => {
          if (worksheet[cell]) {
            worksheet[cell].s = { font: { bold: true },alignment: { horizontal: 'left' } };
          }
        });

          // Apply styles from A2 A3 A5 (bold)
        ['A6', 'A7', 'A8','A9','A10','A11','A12'].forEach(cell => {
          if (worksheet[cell]) {
            worksheet[cell].s = { font: { bold: false },alignment: { horizontal: 'left' } };
          }
        });

        // Formatting from A15 to O15
        // Apply styles from A6 to K6 (bold, bottom border, center alignment)
        for (let col = 0; col < 15; col++) { // Columns A to O
          const cellRef = XLSX.utils.encode_col(col) + '15';
          if (worksheet[cellRef]) {
            worksheet[cellRef].s = {
              font: { bold: true },
              alignment: { horizontal: 'center' },
              border: {
                bottom: { style: 'thin', color: { auto: 1 } }
              }
            };
          }
        }

        // Find the last row of the first table
        let lastRowOfFirstTable = 16; // Starting row for data in the first table
        while (worksheet[XLSX.utils.encode_cell({ c: 0, r: lastRowOfFirstTable })]) {
          lastRowOfFirstTable++;
        }
        // Insert the first data form into the worksheet at the desired location
        //XLSX.utils.sheet_add_aoa(worksheet, firstTableData, { origin: { c: 0, r: lastRowOfFirstTable } });
        // Assuming worksheet is already defined, and we start inserting from row 16
processDataAndInsert(worksheet, 16, firstTableData);
        // Update lastRowOfFirstTable to account for the new data
        lastRowOfFirstTable += firstTableData.length;
        // Now insert the text that goes between the two tables
        
        // Calculate the maximum length of content in each column and set column width
        const colWidths: number[] = [];

        // Start with a base width for each column (optional, but can help with columns that have no data)
        // Check if worksheet['!ref'] is defined to prevent errors
        const maxCol = worksheet['!ref'] ? worksheet['!ref'].split(':')[1].charCodeAt(0) - 'A'.charCodeAt(0) + 1 : 0;
        for (let col = 0; col < maxCol; col++) {
          colWidths[col] = 10; // Default base width
        }

        const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1, defval: "", blankrows: true }) as (string | number)[][];

        // Skip the first row in the jsonData
        for (let row = 1; row < jsonData.length; row++) {
          jsonData[row].forEach((cell, index) => {
            // Only process if the cell is not null/undefined
            if (cell) {
              const valueLength = cell.toString().length;
              colWidths[index] = Math.max(colWidths[index] || 0, valueLength);
            }
          });
        }

        // Check if worksheet exists before setting '!cols'
        if (worksheet) {
          worksheet['!cols'] = colWidths.map((width) => ({ wch: width + 2 })); // +2 for a little extra padding
        }

        // Format filename with date
        const today = new Date().toISOString().split('T')[0].replace(/-/g, '_'); // Get current date and format as YYYY_MM_DD
        const filename = `EX01_Financial_Status_Report_${today}.xlsx`; // Append formatted date to the filename

          // Convert workbook back to XLSX file
          XLSX.writeFile(workbook, filename);
        } else {
          throw new Error('Failed to load file');
        }
      };
      reader.readAsArrayBuffer(data);
    } catch (error) {
      console.error('Error downloading the file: ', error);
    }

    //setIsLoading(false);
  };
  return (
    <Card>
      <CardContent sx={{ display: "flex", flexDirection: "column", gap: 1 }}>
        <Typography variant="overline">{t("Search Criteria")}</Typography>
        <Grid container spacing={2} columns={{ xs: 6, sm: 12 }}>
          {criteria.map((c) => {
            return (
              <Grid key={c.paramName} item xs={6}>
                {c.type === "text" && (
                  <TextField
                    label={c.label}
                    fullWidth
                    onChange={makeInputChangeHandler(c.paramName)}
                    value={inputs[c.paramName]}
                  />
                )}
                {c.type === "select" && (
                  <FormControl fullWidth>
                    <InputLabel>{c.label}</InputLabel>
                    <Select
                      label={c.label}
                      onChange={makeSelectChangeHandler(c.paramName)}
                      value={inputs[c.paramName]}
                    >
                      <MenuItem value={"All"}>{t("All")}</MenuItem>
                      {c.options.map((option, index) => (
                        <MenuItem key={`${option}-${index}`} value={option}>
                          {option}
                        </MenuItem>
                      ))}
                    </Select>
                  </FormControl>
                )}
                {c.type === "dateRange" && (
                  <LocalizationProvider
                    dateAdapter={AdapterDayjs}
                    // TODO: Should maybe use a custom adapterLocale here to support YYYY-MM-DD
                    adapterLocale="zh-hk"
                  >
                    <Box display="flex">
                      <FormControl fullWidth>
                        <DatePicker
                          label={c.label}
                          onChange={makeDateChangeHandler(c.paramName)}
                          value={inputs[c.paramName] ? dayjs(inputs[c.paramName]) : null}
                        />
                      </FormControl>
                      <Box
                        display="flex"
                        alignItems="center"
                        justifyContent="center"
                        marginInline={2}
                      >
                        {"-"}
                      </Box>
                      <FormControl fullWidth>
                        <DatePicker
                          label={c.label2}
                          onChange={makeDateToChangeHandler(c.paramName)}
                          value={inputs[c.paramName.concat("To") as T] ? dayjs(inputs[c.paramName.concat("To") as T]) : null}
                        />
                      </FormControl>
                    </Box>
                  </LocalizationProvider>
                )}
              </Grid>
            );
          })}
        </Grid>
          <CardActions sx={{ justifyContent: "flex-end" }}>
            <Button
              variant="text"
              startIcon={<RestartAlt />}
              onClick={handleReset}
            >
              {t("Reset")}
            </Button>
            <Button
              variant="outlined"
              startIcon={<Search />}
              onClick={handleDownload}
            >
              {t("Download")}
            </Button>
          </CardActions>
      </CardContent>
    </Card>
  );
}

export default SearchBox;
