client/src/hooks/useSpreadsheet.ts - develop - kan64/spreadsheet-lab

client/src/hooks/useSpreadsheet.ts
1 104 строки40 KB
Anton Kravchenkov
feat: fix
30 мар 2026, 23:14
30 мар 2026, 23:14b5f409e
import { useReducer, useCallback, useRef } from 'react';
import type {
  SpreadsheetState,
  SpreadsheetAction,
  SpreadsheetFile,
  SheetData,
  CellData,
  CellRange,
} from '../types/types.ts';
import { DEFAULT_COL_WIDTH, DEFAULT_ROW_HEIGHT } from '../types/types.ts';
import { cellId, getRangeCells, normalizeRange, letterToCol, colToLetter, parseCellDate, formatDateForCell } from '../utils/cellUtils';
import { computeCell } from '../utils/formulaEngine';

function createSheet(name: string): SheetData {
  return {
    id: crypto.randomUUID ? crypto.randomUUID() : `${Date.now()}-${Math.random()}`,
    name,
    cells: {},
    columnWidths: {},
    rowHeights: {},
    mergedCells: [],
    frozenRows: 0,
    frozenCols: 0,
  };
}

function getActiveSheet(state: SpreadsheetState): SheetData {
  return state.sheets.find((s) => s.id === state.activeSheetId)!;
}

/** Расширяет frozenRows, чтобы не разрезать объединённые ячейки */
function expandFrozenRowsForMerged(rows: number, mergedCells: CellRange[]): number {
  let r = rows;
  let changed = true;
  while (changed) {
    changed = false;
    for (const m of mergedCells ?? []) {
      const sr = m.start.row;
      const er = m.end.row;
      if (sr < r && er >= r) {
        r = Math.max(r, er + 1);
        changed = true;
      }
    }
  }
  return r;
}

/** Расширяет frozenCols, чтобы не разрезать объединённые ячейки */
function expandFrozenColsForMerged(cols: number, mergedCells: CellRange[]): number {
  let c = cols;
  let changed = true;
  while (changed) {
    changed = false;
    for (const m of mergedCells ?? []) {
      const sc = m.start.col;
      const ec = m.end.col;
      if (sc < c && ec >= c) {
        c = Math.max(c, ec + 1);
        changed = true;
      }
    }
  }
  return c;
}

/** Расширяет диапазон, чтобы включить все объединённые ячейки, пересекающиеся с ним */
function expandRangeWithMergedCells(range: CellRange, mergedCells: CellRange[]): CellRange {
  if (!mergedCells?.length) return range;
  let sr = range.start.row, sc = range.start.col;
  let er = range.end.row, ec = range.end.col;
  let changed = true;
  while (changed) {
    changed = false;
    for (const m of mergedCells) {
      const nm = normalizeRange(m);
      if (nm.start.row <= er && nm.end.row >= sr && nm.start.col <= ec && nm.end.col >= sc) {
        const nsr = Math.min(sr, nm.start.row);
        const nsc = Math.min(sc, nm.start.col);
        const ner = Math.max(er, nm.end.row);
        const nec = Math.max(ec, nm.end.col);
        if (nsr !== sr || nsc !== sc || ner !== er || nec !== ec) {
          sr = nsr; sc = nsc; er = ner; ec = nec;
          changed = true;
        }
      }
    }
  }
  return { start: { row: sr, col: sc }, end: { row: er, col: ec } };
}

function updateActiveSheet(
  state: SpreadsheetState,
  updater: (sheet: SheetData) => SheetData
): SpreadsheetState {
  return {
    ...state,
    sheets: state.sheets.map((s) =>
      s.id === state.activeSheetId ? updater(s) : s
    ),
  };
}

function updateSheetById(
  state: SpreadsheetState,
  sheetId: string,
  updater: (sheet: SheetData) => SheetData
): SpreadsheetState {
  return {
    ...state,
    sheets: state.sheets.map((s) =>
      s.id === sheetId ? updater(s) : s
    ),
  };
}

function recomputeAll(sheet: SheetData): SheetData {
  const getCellData = (id: string): CellData | undefined => sheet.cells[id];
  const newCells = { ...sheet.cells };

  for (const [key, cell] of Object.entries(newCells)) {
    if (cell.formula || cell.value) {
      const computed = computeCell(cell, getCellData);
      newCells[key] = { ...cell, computedValue: computed };
    }
  }

  return { ...sheet, cells: newCells };
}

function spreadsheetReducer(
  state: SpreadsheetState,
  action: SpreadsheetAction
): SpreadsheetState {
  switch (action.type) {
    case 'SET_CELL_VALUE': {
      const id = cellId(action.row, action.col);
      const existing = getActiveSheet(state).cells[id];
      const isFormula = action.value.startsWith('=');
      const newCell: CellData = {
        value: isFormula ? '' : action.value,
        formula: isFormula ? action.value.slice(1) : undefined,
        style: existing?.style,
        link: existing?.link,
      };

      let newState = updateActiveSheet(state, (sheet) => ({
        ...sheet,
        cells: { ...sheet.cells, [id]: newCell },
      }));

      newState = updateActiveSheet(newState, (sheet) => recomputeAll(sheet));
      return { ...newState, isEditing: false, editValue: '' };
    }

    case 'SET_CELL_STYLE': {
      const positions = getRangeCells(normalizeRange(action.range));
      return updateActiveSheet(state, (sheet) => {
        const newCells = { ...sheet.cells };
        for (const pos of positions) {
          const id = cellId(pos.row, pos.col);
          const existing = newCells[id] || { value: '' };
          newCells[id] = {
            ...existing,
            style: { ...existing.style, ...action.style },
          };
        }
        return { ...sheet, cells: newCells };
      });
    }

    case 'SELECT_CELL': {
      const sheet = getActiveSheet(state);
      const range = { start: action.position, end: action.position };
      const expanded = expandRangeWithMergedCells(range, sheet.mergedCells ?? []);
      return {
        ...state,
        selectedCell: action.position,
        selectedRange: expanded,
        isEditing: false,
        editValue: '',
      };
    }

    case 'SELECT_RANGE': {
      const sheet = getActiveSheet(state);
      const normalized = normalizeRange(action.range);
      const expanded = expandRangeWithMergedCells(normalized, sheet.mergedCells ?? []);
      return {
        ...state,
        selectedCell: expanded.start,
        selectedRange: expanded,
      };
    }

    case 'START_EDITING': {
      const sheet = getActiveSheet(state);
      const cell = state.selectedCell;
      if (!cell) return state;
      const id = cellId(cell.row, cell.col);
      const cellData = sheet.cells[id];
      const editVal =
        action.value !== undefined
          ? action.value
          : cellData?.formula
            ? `=${cellData.formula}`
            : cellData?.value || '';
      return { ...state, isEditing: true, editValue: editVal };
    }

    case 'UPDATE_EDIT_VALUE':
      return { ...state, editValue: action.value };

    case 'STOP_EDITING': {
      if (!action.save || !state.selectedCell) {
        return { ...state, isEditing: false, editValue: '' };
      }
      const id = cellId(state.selectedCell.row, state.selectedCell.col);
      const existingCell = getActiveSheet(state).cells[id];
      const editVal = action.finalValue ?? state.editValue;
      const isFormula = editVal.startsWith('=');
      const newCell: CellData = {
        value: isFormula ? '' : editVal,
        formula: isFormula ? editVal.slice(1) : undefined,
        style: existingCell?.style,
        link: existingCell?.link,
        richText: isFormula ? undefined : action.richText,
      };

      let newState = updateActiveSheet(state, (sheet) => ({
        ...sheet,
        cells: { ...sheet.cells, [id]: newCell },
      }));

      newState = updateActiveSheet(newState, (sheet) => recomputeAll(sheet));
      return { ...newState, isEditing: false, editValue: '' };
    }

    case 'SET_COLUMN_WIDTH':
      return updateActiveSheet(state, (sheet) => ({
        ...sheet,
        columnWidths: { ...sheet.columnWidths, [action.col]: action.width },
      }));

    case 'SET_COLUMNS_WIDTH': {
      return updateActiveSheet(state, (sheet) => {
        const columnWidths = { ...sheet.columnWidths };
        for (const col of action.cols) {
          columnWidths[col] = action.width;
        }
        return { ...sheet, columnWidths };
      });
    }

    case 'SET_ROW_HEIGHT':
      return updateActiveSheet(state, (sheet) => ({
        ...sheet,
        rowHeights: { ...sheet.rowHeights, [action.row]: action.height },
      }));

    case 'SET_FROZEN_ROWS': {
      const sheet = getActiveSheet(state);
      const expanded = expandFrozenRowsForMerged(Math.max(0, action.count), sheet.mergedCells ?? []);
      return updateActiveSheet(state, (s) => ({ ...s, frozenRows: expanded }));
    }

    case 'SET_FROZEN_COLS': {
      const sheet = getActiveSheet(state);
      const expanded = expandFrozenColsForMerged(Math.max(0, action.count), sheet.mergedCells ?? []);
      return updateActiveSheet(state, (s) => ({ ...s, frozenCols: expanded }));
    }

    case 'ADD_SHEET': {
      const newSheet = createSheet(`Лист ${state.sheets.length + 1}`);
      return {
        ...state,
        sheets: [...state.sheets, newSheet],
        activeSheetId: newSheet.id,
        selectedCell: { row: 0, col: 0 },
        selectedRange: { start: { row: 0, col: 0 }, end: { row: 0, col: 0 } },
      };
    }

    case 'REMOVE_SHEET': {
      if (state.sheets.length <= 1) return state;
      const newSheets = state.sheets.filter((s) => s.id !== action.sheetId);
      const newActiveId =
        state.activeSheetId === action.sheetId
          ? newSheets[0].id
          : state.activeSheetId;
      return { ...state, sheets: newSheets, activeSheetId: newActiveId };
    }

    case 'RENAME_SHEET':
      return {
        ...state,
        sheets: state.sheets.map((s) =>
          s.id === action.sheetId ? { ...s, name: action.name } : s
        ),
      };

    case 'SET_ACTIVE_SHEET':
      return {
        ...state,
        activeSheetId: action.sheetId,
        selectedCell: { row: 0, col: 0 },
        selectedRange: { start: { row: 0, col: 0 }, end: { row: 0, col: 0 } },
        isEditing: false,
        editValue: '',
      };

    case 'REORDER_SHEETS': {
      const sheets = [...state.sheets];
      const [moved] = sheets.splice(action.fromIndex, 1);
      sheets.splice(action.toIndex, 0, moved);
      return { ...state, sheets };
    }

    case 'COPY': {
      if (!state.selectedRange) return state;
      const sheet = getActiveSheet(state);
      const range = normalizeRange(state.selectedRange);
      const copiedCells: Record<string, CellData> = {};
      const positions = getRangeCells(range);
      for (const pos of positions) {
        const id = cellId(pos.row, pos.col);
        if (sheet.cells[id]) {
          copiedCells[id] = { ...sheet.cells[id] };
        }
      }
      // Сохраняем объединённые ячейки, полностью попадающие в скопированный диапазон
      const copiedMerged = (sheet.mergedCells ?? []).filter(
        (m) =>
          m.start.row >= range.start.row && m.end.row <= range.end.row &&
          m.start.col >= range.start.col && m.end.col <= range.end.col,
      );
      return {
        ...state,
        clipboard: { range, cells: copiedCells, mergedCells: copiedMerged, isCut: action.isCut },
      };
    }

    case 'PASTE': {
      if (!state.clipboard || !state.selectedCell) return state;
      const { range: srcRange, cells: srcCells, isCut } = state.clipboard;
      if (!srcRange) return state;
      const dest = state.selectedCell;
      const rowOffset = dest.row - srcRange.start.row;
      const colOffset = dest.col - srcRange.start.col;

      let newState = updateActiveSheet(state, (sheet) => {
        const newCells = { ...sheet.cells };

        if (isCut) {
          for (const key of Object.keys(srcCells)) {
            delete newCells[key];
          }
        }

        const positions = getRangeCells(srcRange);
        for (const pos of positions) {
          const srcId = cellId(pos.row, pos.col);
          const destId = cellId(pos.row + rowOffset, pos.col + colOffset);
          if (srcCells[srcId]) {
            newCells[destId] = { ...srcCells[srcId] };
          }
        }

        return { ...sheet, cells: newCells };
      });

      newState = updateActiveSheet(newState, (sheet) => recomputeAll(sheet));
      newState = { ...newState, clipboard: null };
      return newState;
    }

    case 'CLEAR_CLIPBOARD':
      return { ...state, clipboard: null };

    case 'CLEAR_SELECTION':
      return { ...state, selectedCell: null, selectedRange: null };

    case 'CLEAR_SELECTION_AND_CLIPBOARD':
      return {
        ...state,
        selectedCell: null,
        selectedRange: null,
        clipboard: null,
      };

    case 'DELETE_CELL_CONTENT': {
      if (!state.selectedRange) return state;
      const range = normalizeRange(state.selectedRange);
      const positions = getRangeCells(range);
      let newState = updateActiveSheet(state, (sheet) => {
        const newCells = { ...sheet.cells };
        for (const pos of positions) {
          const id = cellId(pos.row, pos.col);
          if (newCells[id]) {
            newCells[id] = { ...newCells[id], value: '', formula: undefined, computedValue: '', link: undefined, richText: undefined };
          }
        }
        return { ...sheet, cells: newCells };
      });
      newState = updateActiveSheet(newState, (sheet) => recomputeAll(sheet));
      return newState;
    }

    case 'INSERT_ROW': {
      return updateActiveSheet(state, (sheet) => {
        const newCells: Record<string, CellData> = {};
        for (const [key, cell] of Object.entries(sheet.cells)) {
          const match = key.match(/^([A-Z]+)(\d+)$/);
          if (match) {
            const row = parseInt(match[2], 10) - 1;
            if (row >= action.row) {
              const newKey = `${match[1]}${row + 2}`;
              newCells[newKey] = cell;
            } else {
              newCells[key] = cell;
            }
          }
        }
        const newMergedCells = (sheet.mergedCells ?? []).map((m) => {
          const start = m.start.row >= action.row ? { ...m.start, row: m.start.row + 1 } : m.start;
          const end = m.end.row >= action.row ? { ...m.end, row: m.end.row + 1 } : m.end;
          return { start, end };
        });
        const newRowHeights: Record<number, number> = {};
        for (const [k, v] of Object.entries(sheet.rowHeights ?? {})) {
          const row = Number(k);
          newRowHeights[row >= action.row ? row + 1 : row] = v;
        }
        return { ...sheet, cells: newCells, mergedCells: newMergedCells, rowHeights: newRowHeights };
      });
    }

    case 'INSERT_COLUMN': {
      return updateActiveSheet(state, (sheet) => {
        const newCells: Record<string, CellData> = {};
        for (const [key, cell] of Object.entries(sheet.cells)) {
          const match = key.match(/^([A-Z]+)(\d+)$/);
          if (match) {
            const col = letterToCol(match[1]);
            const row = parseInt(match[2], 10) - 1;
            if (col >= action.col) {
              const newKey = `${colToLetter(col + 1)}${row + 1}`;
              newCells[newKey] = cell;
            } else {
              newCells[key] = cell;
            }
          }
        }
        const newMergedCells = (sheet.mergedCells ?? []).map((m) => {
          const start = m.start.col >= action.col ? { ...m.start, col: m.start.col + 1 } : m.start;
          const end = m.end.col >= action.col ? { ...m.end, col: m.end.col + 1 } : m.end;
          return { start, end };
        });
        const newColumnWidths: Record<number, number> = {};
        for (const [k, v] of Object.entries(sheet.columnWidths ?? {})) {
          const col = Number(k);
          newColumnWidths[col >= action.col ? col + 1 : col] = v;
        }
        return { ...sheet, cells: newCells, mergedCells: newMergedCells, columnWidths: newColumnWidths };
      });
    }

    // Вставка скопированных строк выше или ниже целевой строки
    case 'PASTE_ROWS': {
      if (!state.clipboard?.range) return state;
      const srcRange = normalizeRange(state.clipboard.range);
      const numRows = srcRange.end.row - srcRange.start.row + 1;
      const insertAt = action.direction === 'above' ? action.row : action.row + 1;

      let newState = updateActiveSheet(state, (sheet) => {
        // Сдвигаем существующие ячейки вниз на numRows начиная с insertAt
        const newCells: Record<string, CellData> = {};
        for (const [key, cell] of Object.entries(sheet.cells)) {
          const match = key.match(/^([A-Z]+)(\d+)$/);
          if (match) {
            const row = parseInt(match[2], 10) - 1;
            if (row >= insertAt) {
              newCells[`${match[1]}${row + numRows + 1}`] = cell;
            } else {
              newCells[key] = cell;
            }
          }
        }

        // Вставляем скопированные ячейки на новые позиции
        const srcCells = state.clipboard!.cells;
        for (let r = srcRange.start.row; r <= srcRange.end.row; r++) {
          for (let c = srcRange.start.col; c <= srcRange.end.col; c++) {
            const srcId = cellId(r, c);
            if (srcCells[srcId]) {
              const destRow = insertAt + (r - srcRange.start.row);
              newCells[cellId(destRow, c)] = { ...srcCells[srcId] };
            }
          }
        }

        // Сдвигаем существующие объединённые ячейки вниз
        const shiftedMerged = (sheet.mergedCells ?? []).map((m) => ({
          start: m.start.row >= insertAt ? { ...m.start, row: m.start.row + numRows } : m.start,
          end: m.end.row >= insertAt ? { ...m.end, row: m.end.row + numRows } : m.end,
        }));

        // Восстанавливаем объединённые ячейки из буфера обмена со сдвигом на новую позицию
        const pastedMerged = (state.clipboard!.mergedCells ?? []).map((m) => ({
          start: { row: insertAt + (m.start.row - srcRange.start.row), col: m.start.col },
          end: { row: insertAt + (m.end.row - srcRange.start.row), col: m.end.col },
        }));

        const newMergedCells = [...shiftedMerged, ...pastedMerged];

        // Сдвигаем кастомные высоты строк
        const newRowHeights: Record<number, number> = {};
        for (const [k, v] of Object.entries(sheet.rowHeights ?? {})) {
          const row = Number(k);
          newRowHeights[row >= insertAt ? row + numRows : row] = v;
        }

        return { ...sheet, cells: newCells, mergedCells: newMergedCells, rowHeights: newRowHeights };
      });

      newState = updateActiveSheet(newState, (s) => recomputeAll(s));
      return { ...newState, clipboard: null, selectedRange: null };
    }

    case 'DELETE_ROW': {
      return updateActiveSheet(state, (sheet) => {
        const newCells: Record<string, CellData> = {};
        for (const [key, cell] of Object.entries(sheet.cells)) {
          const match = key.match(/^([A-Z]+)(\d+)$/);
          if (match) {
            const row = parseInt(match[2], 10) - 1;
            if (row === action.row) continue;
            if (row > action.row) {
              const newKey = `${match[1]}${row}`;
              newCells[newKey] = cell;
            } else {
              newCells[key] = cell;
            }
          }
        }
        const newMergedCells = (sheet.mergedCells ?? [])
          .filter((m) => {
            const sr = m.start.row;
            const er = m.end.row;
            if (sr === action.row && er === action.row) return false;
            if (sr === action.row) return false;
            return true;
          })
          .map((m) => {
            const sr = m.start.row;
            const er = m.end.row;
            if (er < action.row) return m;
            if (sr > action.row) {
              return { start: { ...m.start, row: sr - 1 }, end: { ...m.end, row: er - 1 } };
            }
            return { start: m.start, end: { ...m.end, row: er - 1 } };
          })
          .filter((m) => m.start.row !== m.end.row || m.start.col !== m.end.col);
        const newRowHeights: Record<number, number> = {};
        for (const [k, v] of Object.entries(sheet.rowHeights ?? {})) {
          const row = Number(k);
          if (row === action.row) continue;
          newRowHeights[row > action.row ? row - 1 : row] = v;
        }
        return { ...sheet, cells: newCells, mergedCells: newMergedCells, rowHeights: newRowHeights };
      });
    }

    case 'DELETE_COLUMN': {
      return updateActiveSheet(state, (sheet) => {
        const newCells: Record<string, CellData> = {};
        for (const [key, cell] of Object.entries(sheet.cells)) {
          const match = key.match(/^([A-Z]+)(\d+)$/);
          if (match) {
            const col = letterToCol(match[1]);
            const row = parseInt(match[2], 10) - 1;
            if (col === action.col) continue;
            if (col > action.col) {
              const newKey = `${colToLetter(col - 1)}${row + 1}`;
              newCells[newKey] = cell;
            } else {
              newCells[key] = cell;
            }
          }
        }
        const newMergedCells = (sheet.mergedCells ?? [])
          .filter((m) => {
            const sc = m.start.col;
            const ec = m.end.col;
            if (sc === action.col && ec === action.col) return false;
            if (sc === action.col) return false;
            return true;
          })
          .map((m) => {
            const sc = m.start.col;
            const ec = m.end.col;
            if (ec < action.col) return m;
            if (sc > action.col) {
              return { start: { ...m.start, col: sc - 1 }, end: { ...m.end, col: ec - 1 } };
            }
            return { start: m.start, end: { ...m.end, col: ec - 1 } };
          })
          .filter((m) => m.start.row !== m.end.row || m.start.col !== m.end.col);
        const newColumnWidths: Record<number, number> = {};
        for (const [k, v] of Object.entries(sheet.columnWidths ?? {})) {
          const col = Number(k);
          if (col === action.col) continue;
          newColumnWidths[col > action.col ? col - 1 : col] = v;
        }
        return { ...sheet, cells: newCells, mergedCells: newMergedCells, columnWidths: newColumnWidths };
      });
    }

    case 'MERGE_CELLS': {
      const range = normalizeRange(action.range);
      if (range.start.row === range.end.row && range.start.col === range.end.col) {
        return state;
      }
      return updateActiveSheet(state, (sheet) => {
        // Remove any existing merges that overlap with the new range
        const filtered = sheet.mergedCells.filter((m) => {
          const nm = normalizeRange(m);
          return (
            nm.end.row < range.start.row ||
            nm.start.row > range.end.row ||
            nm.end.col < range.start.col ||
            nm.start.col > range.end.col
          );
        });
        // Move all content to the top-left cell, clear others
        const originId = cellId(range.start.row, range.start.col);
        const newCells = { ...sheet.cells };
        const originData = newCells[originId];
        for (let r = range.start.row; r <= range.end.row; r++) {
          for (let c = range.start.col; c <= range.end.col; c++) {
            if (r === range.start.row && c === range.start.col) continue;
            const id = cellId(r, c);
            if (newCells[id] && !originData?.value) {
              // Copy first non-empty value to origin if origin is empty
              const v = newCells[id];
              if (v.value || v.formula) {
                newCells[originId] = {
                  ...newCells[originId] || { value: '' },
                  value: v.value,
                  formula: v.formula,
                  computedValue: v.computedValue,
                };
              }
            }
            delete newCells[id];
          }
        }
        return {
          ...sheet,
          cells: newCells,
          mergedCells: [...filtered, range],
        };
      });
    }

    case 'UNMERGE_CELLS': {
      const range = normalizeRange(action.range);
      return updateActiveSheet(state, (sheet) => {
        const filtered = sheet.mergedCells.filter((m) => {
          const nm = normalizeRange(m);
          return !(
            nm.start.row === range.start.row &&
            nm.start.col === range.start.col &&
            nm.end.row === range.end.row &&
            nm.end.col === range.end.col
          );
        });
        if (filtered.length === sheet.mergedCells.length) {
          // Also remove any merge that overlaps with selection
          const newFiltered = sheet.mergedCells.filter((m) => {
            const nm = normalizeRange(m);
            return (
              nm.end.row < range.start.row ||
              nm.start.row > range.end.row ||
              nm.end.col < range.start.col ||
              nm.start.col > range.end.col
            );
          });
          return { ...sheet, mergedCells: newFiltered };
        }
        return { ...sheet, mergedCells: filtered };
      });
    }

    case 'SET_CELL_LINK': {
      const id = cellId(action.row, action.col);
      return updateActiveSheet(state, (sheet) => {
        const existing = sheet.cells[id] || { value: '' };
        const newCell: CellData = {
          ...existing,
          link: action.url,
        };
        if (action.text !== undefined) {
          newCell.value = action.text;
        }
        if (action.url && !newCell.value) {
          newCell.value = action.url;
        }
        return { ...sheet, cells: { ...sheet.cells, [id]: newCell } };
      });
    }

    case 'RESTORE_STATE':
      return action.state;

    case 'APPLY_REMOTE_CELL': {
      const tabId = action.tabId === 'default' ? (state.sheets[0]?.id ?? 'default') : action.tabId;
      const sheet = state.sheets.find((s) => s.id === tabId);
      if (!sheet) return state;
      const id = cellId(action.row, action.col);
      const existing = sheet.cells[id];
      const isFormula = action.value?.startsWith('=');
      const newCell: CellData = {
        value: isFormula ? '' : (action.value ?? ''),
        formula: isFormula ? action.value.slice(1) : undefined,
        style: action.style === null ? undefined : (action.style !== undefined ? action.style : existing?.style),
        richText: action.richText !== undefined ? action.richText : existing?.richText,
        link: existing?.link,
      };
      let newState = updateSheetById(state, tabId, (s) => ({
        ...s,
        cells: { ...s.cells, [id]: newCell },
      }));
      return updateSheetById(newState, tabId, (s) => recomputeAll(s));
    }

    case 'APPLY_REMOTE_LAYOUT': {
      const layout = action.layout;
      const tabLayouts = layout.tabLayouts ?? {};
      let newState = state;
      for (const sheet of state.sheets) {
        const tl = tabLayouts[sheet.id] ?? (sheet.id === 'default' ? (layout as { mergedCells?: CellRange[]; columnWidths?: Record<string, number>; rowHeights?: Record<string, number>; frozenRows?: number; frozenCols?: number }) : null);
        if (!tl) continue;
        const mergedCells: { start: { row: number; col: number }; end: { row: number; col: number } }[] =
          (tl.mergedCells ?? []).map((m: { start: { row: number; col: number }; end: { row: number; col: number } }) => ({
            start: m.start,
            end: m.end,
          }));
        const columnWidths: Record<number, number> = {};
        if (tl.columnWidths) {
          for (const [k, v] of Object.entries(tl.columnWidths)) {
            columnWidths[Number(k)] = v as number;
          }
        }
        const rowHeights: Record<number, number> = {};
        if (tl.rowHeights) {
          for (const [k, v] of Object.entries(tl.rowHeights)) {
            rowHeights[Number(k)] = v as number;
          }
        }
        newState = updateSheetById(newState, sheet.id, (s) => ({
          ...s,
          mergedCells,
          columnWidths: Object.keys(columnWidths).length ? columnWidths : s.columnWidths,
          rowHeights: Object.keys(rowHeights).length ? rowHeights : s.rowHeights,
          frozenRows: tl.frozenRows ?? s.frozenRows,
          frozenCols: tl.frozenCols ?? s.frozenCols,
        }));
      }
      return newState;
    }

    case 'APPLY_REMOTE_INSERT_ROW': {
      const tabId = action.tabId === 'default' ? (state.sheets[0]?.id ?? 'default') : action.tabId;
      if (!state.sheets.find((s) => s.id === tabId)) return state;
      return updateSheetById(state, tabId, (s) => {
        const newCells: Record<string, CellData> = {};
        for (const [key, cell] of Object.entries(s.cells)) {
          const match = key.match(/^([A-Z]+)(\d+)$/);
          if (match) {
            const row = parseInt(match[2], 10) - 1;
            if (row >= action.row) {
              const newKey = `${match[1]}${row + 2}`;
              newCells[newKey] = cell;
            } else {
              newCells[key] = cell;
            }
          }
        }
        const newMergedCells = (s.mergedCells ?? []).map((m) => {
          const start = m.start.row >= action.row ? { ...m.start, row: m.start.row + 1 } : m.start;
          const end = m.end.row >= action.row ? { ...m.end, row: m.end.row + 1 } : m.end;
          return { start, end };
        });
        const newRowHeights: Record<number, number> = {};
        for (const [k, v] of Object.entries(s.rowHeights ?? {})) {
          const row = Number(k);
          newRowHeights[row >= action.row ? row + 1 : row] = v;
        }
        return { ...s, cells: newCells, mergedCells: newMergedCells, rowHeights: newRowHeights };
      });
    }

    case 'APPLY_REMOTE_PASTE_ROWS': {
      const tabId = action.tabId === 'default' ? (state.sheets[0]?.id ?? 'default') : action.tabId;
      if (!state.sheets.find((s) => s.id === tabId)) return state;
      return updateSheetById(state, tabId, (s) => {
        const insertAt = action.insertAt;
        const numRows = action.numRows;
        const newCells: Record<string, CellData> = {};
        for (const [key, cell] of Object.entries(s.cells)) {
          const match = key.match(/^([A-Z]+)(\d+)$/);
          if (match) {
            const row = parseInt(match[2], 10) - 1;
            if (row >= insertAt) {
              const newKey = `${match[1]}${row + numRows + 1}`;
              newCells[newKey] = cell;
            } else {
              newCells[key] = cell;
            }
          }
        }
        for (const c of action.cells) {
          const key = cellId(c.rowIndex, c.colIndex);
          newCells[key] = { value: c.value, style: c.style ?? undefined, richText: c.richText };
        }
        const newMergedCells = (s.mergedCells ?? []).map((m) => {
          const start = m.start.row >= insertAt ? { ...m.start, row: m.start.row + numRows } : m.start;
          const end = m.end.row >= insertAt ? { ...m.end, row: m.end.row + numRows } : m.end;
          return { start, end };
        });
        const newRowHeights: Record<number, number> = {};
        for (const [k, v] of Object.entries(s.rowHeights ?? {})) {
          const row = Number(k);
          newRowHeights[row >= insertAt ? row + numRows : row] = v;
        }
        return { ...s, cells: newCells, mergedCells: newMergedCells, rowHeights: newRowHeights };
      });
    }

    case 'APPLY_REMOTE_DELETE_ROW': {
      const tabId = action.tabId === 'default' ? (state.sheets[0]?.id ?? 'default') : action.tabId;
      if (!state.sheets.find((s) => s.id === tabId)) return state;
      return updateSheetById(state, tabId, (s) => {
        const newCells: Record<string, CellData> = {};
        for (const [key, cell] of Object.entries(s.cells)) {
          const match = key.match(/^([A-Z]+)(\d+)$/);
          if (match) {
            const row = parseInt(match[2], 10) - 1;
            if (row === action.row) continue;
            if (row > action.row) {
              const newKey = `${match[1]}${row}`;
              newCells[newKey] = cell;
            } else {
              newCells[key] = cell;
            }
          }
        }
        const newMergedCells = (s.mergedCells ?? [])
          .filter((m) => m.start.row !== action.row)
          .map((m) => {
            const sr = m.start.row;
            const er = m.end.row;
            if (er < action.row) return m;
            if (sr > action.row) {
              return { start: { ...m.start, row: sr - 1 }, end: { ...m.end, row: er - 1 } };
            }
            return { start: m.start, end: { ...m.end, row: er - 1 } };
          })
          .filter((m) => m.start.row !== m.end.row || m.start.col !== m.end.col);
        const newRowHeights: Record<number, number> = {};
        for (const [k, v] of Object.entries(s.rowHeights ?? {})) {
          const row = Number(k);
          if (row === action.row) continue;
          newRowHeights[row > action.row ? row - 1 : row] = v;
        }
        return { ...s, cells: newCells, mergedCells: newMergedCells, rowHeights: newRowHeights };
      });
    }

    case 'APPLY_REMOTE_INSERT_COLUMN': {
      const tabId = action.tabId === 'default' ? (state.sheets[0]?.id ?? 'default') : action.tabId;
      if (!state.sheets.find((s) => s.id === tabId)) return state;
      return updateSheetById(state, tabId, (s) => {
        const newCells: Record<string, CellData> = {};
        for (const [key, cell] of Object.entries(s.cells)) {
          const match = key.match(/^([A-Z]+)(\d+)$/);
          if (match) {
            const col = letterToCol(match[1]);
            const row = parseInt(match[2], 10) - 1;
            if (col >= action.col) {
              const newKey = `${colToLetter(col + 1)}${row + 1}`;
              newCells[newKey] = cell;
            } else {
              newCells[key] = cell;
            }
          }
        }
        const newMergedCells = (s.mergedCells ?? []).map((m) => {
          const start = m.start.col >= action.col ? { ...m.start, col: m.start.col + 1 } : m.start;
          const end = m.end.col >= action.col ? { ...m.end, col: m.end.col + 1 } : m.end;
          return { start, end };
        });
        const newColumnWidths: Record<number, number> = {};
        for (const [k, v] of Object.entries(s.columnWidths ?? {})) {
          const col = Number(k);
          newColumnWidths[col >= action.col ? col + 1 : col] = v;
        }
        return { ...s, cells: newCells, mergedCells: newMergedCells, columnWidths: newColumnWidths };
      });
    }

    case 'FILL_DATES': {
      const sheet = getActiveSheet(state);
      const srcId = cellId(action.sourceRow, action.sourceCol);
      const srcCell = sheet.cells[srcId];
      const srcVal = srcCell?.computedValue ?? srcCell?.value ?? '';
      const baseDate = parseCellDate(srcVal);
      if (!baseDate) return state;
      const range = normalizeRange(action.fillRange);
      const positions = getRangeCells(range);
      const srcIdx = positions.findIndex((p) => p.row === action.sourceRow && p.col === action.sourceCol);
      if (srcIdx < 0) return state;
      const newCells = { ...sheet.cells };
      const srcStyle = srcCell?.style;
      for (let i = 0; i < positions.length; i++) {
        const pos = positions[i];
        const daysToAdd = i - srcIdx;
        const d = new Date(baseDate);
        d.setDate(d.getDate() + daysToAdd);
        const id = cellId(pos.row, pos.col);
        const existing = newCells[id] || { value: '' };
        newCells[id] = {
          ...existing,
          value: formatDateForCell(d),
          formula: undefined,
          style: srcStyle ? { ...srcStyle } : existing.style,
        };
      }
      let newState = updateActiveSheet(state, (s) => ({ ...s, cells: newCells }));
      return updateActiveSheet(newState, (s) => recomputeAll(s));
    }

    case 'LOAD_CELLS_RANGE': {
      const tabId = action.tabId === 'default' ? (state.sheets[0]?.id ?? 'default') : action.tabId;
      const sheet = state.sheets.find((s) => s.id === tabId);
      if (!sheet) return state;
      const newCells = { ...sheet.cells };
      for (const c of action.cells) {
        const id = cellId(c.rowIndex, c.colIndex);
        const isFormula = c.value?.startsWith('=');
        newCells[id] = {
          value: isFormula ? '' : (c.value ?? ''),
          formula: isFormula ? c.value.slice(1) : undefined,
          style: c.style,
          richText: c.richText,
        };
      }
      let newState = updateSheetById(state, tabId, (s) => ({ ...s, cells: newCells }));
      return updateSheetById(newState, tabId, (s) => recomputeAll(s));
    }

    case 'APPLY_REMOTE_DELETE_COLUMN': {
      const tabId = action.tabId === 'default' ? (state.sheets[0]?.id ?? 'default') : action.tabId;
      if (!state.sheets.find((s) => s.id === tabId)) return state;
      return updateSheetById(state, tabId, (s) => {
        const newCells: Record<string, CellData> = {};
        for (const [key, cell] of Object.entries(s.cells)) {
          const match = key.match(/^([A-Z]+)(\d+)$/);
          if (match) {
            const col = letterToCol(match[1]);
            const row = parseInt(match[2], 10) - 1;
            if (col === action.col) continue;
            if (col > action.col) {
              const newKey = `${colToLetter(col - 1)}${row + 1}`;
              newCells[newKey] = cell;
            } else {
              newCells[key] = cell;
            }
          }
        }
        const newMergedCells = (s.mergedCells ?? [])
          .filter((m) => m.start.col !== action.col)
          .map((m) => {
            const sc = m.start.col;
            const ec = m.end.col;
            if (ec < action.col) return m;
            if (sc > action.col) {
              return { start: { ...m.start, col: sc - 1 }, end: { ...m.end, col: ec - 1 } };
            }
            return { start: m.start, end: { ...m.end, col: ec - 1 } };
          })
          .filter((m) => m.start.row !== m.end.row || m.start.col !== m.end.col);
        const newColumnWidths: Record<number, number> = {};
        for (const [k, v] of Object.entries(s.columnWidths ?? {})) {
          const col = Number(k);
          if (col === action.col) continue;
          newColumnWidths[col > action.col ? col - 1 : col] = v;
        }
        return { ...s, cells: newCells, mergedCells: newMergedCells, columnWidths: newColumnWidths };
      });
    }

    default:
      return state;
  }
}

export function useSpreadsheet(file?: SpreadsheetFile) {
  const initialSheet = file?.sheets?.[0] ?? createSheet('Лист 1');
  const initialState: SpreadsheetState = {
    sheets: file?.sheets ?? [initialSheet],
    activeSheetId: file?.activeSheetId ?? initialSheet.id,
    selectedCell: { row: 0, col: 0 },
    selectedRange: { start: { row: 0, col: 0 }, end: { row: 0, col: 0 } },
    isEditing: false,
    editValue: '',
    clipboard: null,
  };

  const [state, dispatch] = useReducer(spreadsheetReducer, initialState);

  const historyRef = useRef<SpreadsheetState[]>([initialState]);
  const historyIndexRef = useRef(0);

  const dispatchWithHistory = useCallback(
    (action: SpreadsheetAction) => {
      const nonHistoryActions = [
        'SELECT_CELL',
        'SELECT_RANGE',
        'START_EDITING',
        'UPDATE_EDIT_VALUE',
        'SET_ACTIVE_SHEET',
        'APPLY_REMOTE_CELL',
        'APPLY_REMOTE_LAYOUT',
        'APPLY_REMOTE_INSERT_ROW',
        'APPLY_REMOTE_PASTE_ROWS',
        'APPLY_REMOTE_DELETE_ROW',
        'APPLY_REMOTE_INSERT_COLUMN',
        'APPLY_REMOTE_DELETE_COLUMN',
        'LOAD_CELLS_RANGE',
      ];

      dispatch(action);

      if (!nonHistoryActions.includes(action.type)) {
        // We need to compute the new state to store in history
        const newState = spreadsheetReducer(
          historyRef.current[historyIndexRef.current] ?? initialState,
          action
        );
        const newHistory = historyRef.current.slice(0, historyIndexRef.current + 1);
        newHistory.push(newState);
        if (newHistory.length > 50) newHistory.shift();
        historyRef.current = newHistory;
        historyIndexRef.current = newHistory.length - 1;
      }
    },
    // eslint-disable-next-line react-hooks/exhaustive-deps
    []
  );

  const undo = useCallback(() => {
    if (historyIndexRef.current > 0) {
      historyIndexRef.current--;
      dispatch({
        type: 'RESTORE_STATE',
        state: historyRef.current[historyIndexRef.current],
      });
    }
  }, []);

  const redo = useCallback(() => {
    if (historyIndexRef.current < historyRef.current.length - 1) {
      historyIndexRef.current++;
      dispatch({
        type: 'RESTORE_STATE',
        state: historyRef.current[historyIndexRef.current],
      });
    }
  }, []);

  const activeSheet = state.sheets.find((s) => s.id === state.activeSheetId)!;

  const getColumnWidth = useCallback(
    (col: number) => activeSheet.columnWidths[col] ?? DEFAULT_COL_WIDTH,
    [activeSheet.columnWidths]
  );

  const getRowHeight = useCallback(
    (row: number) => activeSheet.rowHeights[row] ?? DEFAULT_ROW_HEIGHT,
    [activeSheet.rowHeights]
  );

  return {
    state,
    activeSheet,
    dispatch: dispatchWithHistory,
    undo,
    redo,
    getColumnWidth,
    getRowHeight,
  };
}