import React, { useEffect, useRef, useState, forwardRef, useImperativeHandle } from "react";
import jspreadsheet from "jspreadsheet-ce";
import "jspreadsheet-ce/dist/jspreadsheet.css";
import * as XLSX from "xlsx";
import { Alert, Box, Button, CircularProgress, Dialog, DialogActions, DialogContent, DialogTitle, IconButton, Slider, Snackbar, Stack, Typography } from '@mui/material';
import { buildAndGetValues, isFormulaError, formatComputedValue, createFormulaEngine } from './formulaEngine';
import { base64ToArrayBuffer, parseExcelBufferWithExcelJs } from './excelRenderUtils';
import SaveIcon from '@mui/icons-material/Save';
import ArrowBackIcon from '@mui/icons-material/ArrowBack';
import ZoomInIcon from '@mui/icons-material/ZoomIn';
import ZoomOutIcon from '@mui/icons-material/ZoomOut';
import PrintIcon from '@mui/icons-material/Print';
import DownloadIcon from '@mui/icons-material/Download';
import CheckCircleOutlineIcon from '@mui/icons-material/CheckCircleOutline';
import ErrorOutlineIcon from '@mui/icons-material/ErrorOutline';

/* ================= GLOBAL CSS (IMPORTANT) ================= */
const style = document.createElement("style");
style.innerHTML = `
/* Remove default gridlines completely */
.jexcel > tbody > tr > td {
  border: none !important;
}

/* Highlight editable cells */
.jexcel .editable-cell {
  background-color: #ffffcc !important;
  border: 1px solid #1976d2 !important;
  cursor: pointer !important;
}

/* Highlight readonly cells */
.jexcel .readonly-cell {
  background-color: #f8f9fa !important;
  color: #666 !important;
  cursor: not-allowed !important;
}

/* Highlight computed cells */
.jexcel .computed-cell {
  background-color: #e3f2fd !important;
  color: #0d47a1 !important;
  font-weight: 600 !important;
}
`;
document.head.appendChild(style);
/* ========================================================== */

/** Fallback when ws['!ref'] is missing: compute range from cell keys (same idea as Excel Mapping). */
function getSheetRange(ws) {
    if (!ws) return { s: { r: 0, c: 0 }, e: { r: 0, c: 0 } };
    if (ws['!ref']) {
        try {
            return XLSX.utils.decode_range(ws['!ref']);
        } catch (_) { }
    }
    let minR = 0, minC = 0, maxR = 0, maxC = 0;
    const re = /^([A-Z]+)(\d+)$/i;
    for (const key of Object.keys(ws)) {
        if (key.startsWith('!')) continue;
        const m = key.match(re);
        if (m) {
            const colStr = m[1].toUpperCase();
            let c = 0;
            for (let i = 0; i < colStr.length; i++) c = c * 26 + (colStr.charCodeAt(i) - 64);
            const r = parseInt(m[2], 10) - 1;
            c--;
            if (r < minR) minR = r;
            if (r > maxR) maxR = r;
            if (c < minC) minC = c;
            if (c > maxC) maxC = c;
        }
    }
    return { s: { r: minR, c: minC }, e: { r: maxR, c: maxC } };
}

/** Get body/footer row indices from mapping metadata or infer from mappings (no hardcoding). */
function getBodyFooterFromMapping(mappings, metadata, totalRows) {
    if (metadata && Number.isInteger(metadata.bodyStartRow) && Number.isInteger(metadata.footerStartRow)) {
        return {
            bodyStartRow: metadata.bodyStartRow,
            footerStartRow: metadata.footerStartRow,
            templateBodyRowCount: metadata.templateBodyRowCount ?? 1,
        };
    }
    const bodyRows = (mappings || [])
        .filter((m) => m.section === 'BODY')
        .map((m) => {
            if (typeof m.excelCell === 'string') {
                try {
                    return XLSX.utils.decode_cell(m.excelCell).r;
                } catch (_) {
                    const rowPart = String(m.excelCell).replace(/\D/g, '');
                    return rowPart ? parseInt(rowPart, 10) - 1 : null;
                }
            }
            if (typeof m.excelCell === 'object' && m.excelCell?.start) {
                const rowPart = String(m.excelCell.start).replace(/\D/g, '');
                return rowPart ? parseInt(rowPart, 10) - 1 : null;
            }
            return null;
        })
        .filter((r) => r != null);
    const footerRows = (mappings || [])
        .filter((m) => m.section === 'FOOTER')
        .map((m) => {
            if (typeof m.excelCell === 'string') {
                try {
                    return XLSX.utils.decode_cell(m.excelCell).r;
                } catch (_) {
                    const rowPart = String(m.excelCell).replace(/\D/g, '');
                    return rowPart ? parseInt(rowPart, 10) - 1 : null;
                }
            }
            if (typeof m.excelCell === 'object' && m.excelCell?.end) {
                const rowPart = String(m.excelCell.end).replace(/\D/g, '');
                return rowPart ? parseInt(rowPart, 10) - 1 : null;
            }
            return null;
        })
        .filter((r) => r != null);
    const bodyStart = bodyRows.length > 0 ? Math.min(...bodyRows) : 0;
    const bodyEnd = bodyRows.length > 0 ? Math.max(...bodyRows) : 0;
    return {
        bodyStartRow: bodyStart,
        footerStartRow: footerRows.length > 0 ? Math.max(...footerRows) : Math.max(0, (totalRows || 1) - 1),
        templateBodyRowCount: bodyRows.length > 0 ? bodyEnd - bodyStart + 1 : 1,
    };
}

/** Add rowOffset to every 1-based row reference in formula that is >= oneBasedThreshold. */
function adjustFormulaRowRefs(formulaStr, rowOffset, oneBasedThreshold, preserveAbsoluteRow = false) {
    if (!formulaStr || typeof formulaStr !== 'string') return formulaStr;
    let formula = formulaStr.trim();
    if (formula.startsWith('=')) formula = formula.slice(1);
    const oneBased = oneBasedThreshold != null ? oneBasedThreshold : 1;
    const rowRefRegex = /(\$?[A-Z]{1,3})(\$?)(\d+)/gi;
    const adjusted = formula.replace(rowRefRegex, (_, col, rowAbs, rowNum) => {
        const r = parseInt(rowNum, 10);
        if (preserveAbsoluteRow && rowAbs === '$') return col + rowAbs + rowNum;
        if (r >= oneBased) return col + (rowAbs || '') + (r + rowOffset);
        return col + (rowAbs || '') + rowNum;
    });
    return formulaStr.trimStart().startsWith('=') ? '=' + adjusted : adjusted;
}

/** Copy a row and adjust formula row references so they refer to the new row (for inserted body rows). */
function copyRowWithFormulaOffset(row, rowOffset, bodyStartRow0Based) {
    if (!Array.isArray(row)) return row;
    const oneBasedBodyStart = bodyStartRow0Based + 1;
    return row.map((cell) => {
        if (cell == null) return cell;
        if (typeof cell === 'object' && typeof cell.formula === 'string' && cell.formula.trim()) {
            const newFormula = adjustFormulaRowRefs(cell.formula, rowOffset, oneBasedBodyStart, true);
            const withEquals = newFormula.startsWith('=') ? newFormula : '=' + newFormula;
            return { ...cell, formula: withEquals, value: cell.value };
        }
        return typeof cell === 'object' ? { ...cell } : cell;
    });
}

const EmployeeFillMode = forwardRef(function EmployeeFillMode({ templateData, mappingData, initialData, onSave, onPrint, onDownloadExcel, onBack, triggerDownloadOnLoad }, ref) {
    const [rawParsedRows, setRawParsedRows] = useState([]);
    const [mergeInfo, setMergeInfo] = useState([]);
    const [editableCells, setEditableCells] = useState(new Set());
    const [formData, setFormData] = useState({});
    const [isLoading, setIsLoading] = useState(false);
    const [showSaveSuccessDialog, setShowSaveSuccessDialog] = useState(false);
    const [savedInvoicePayload, setSavedInvoicePayload] = useState(null);
    const [showLoader, setShowLoader] = useState(false);
    const [loaderMessage, setLoaderMessage] = useState('');
    const [excelDownloading, setExcelDownloading] = useState(false);
    const [pdfDownloading, setPdfDownloading] = useState(false);
    const [showMessageDialog, setShowMessageDialog] = useState(false);
    const [messageDialog, setMessageDialog] = useState({ type: 'error', message: '' });
    const [excelData, setExcelData] = useState([]);
    const [anchoredImages, setAnchoredImages] = useState([]);
    const [validationError, setValidationError] = useState('');
    const [tableZoom, setTableZoom] = useState(1);
    const [imageCalib, setImageCalib] = useState({ sx: 1, sy: 1 });
    const [imageOffset, setImageOffset] = useState({ dx: 0, dy: 0 });

    const didLogStyleDebugRef = useRef(false);
    const appliedInitialDataRef = useRef(false);
    const previewTableRef = useRef(null);
    const previewViewportRef = useRef(null);
    const triggeredDownloadRef = useRef(false);

    const normalizedMapping = React.useMemo(() => {
        if (!mappingData) return { mappings: [] };
        const obj = Array.isArray(mappingData) ? { mappings: mappingData } : mappingData;
        const mappings = Array.isArray(obj?.mappings) ? obj.mappings : [];
        return {
            ...obj,
            mappings: mappings.map((m) => ({
                ...m,
                editable: typeof m?.editable === 'boolean'
                    ? m.editable
                    : (typeof m?.readOnly === 'boolean'
                        ? !m.readOnly
                        : (m?.source === 'USER')),
            }))
        };
    }, [mappingData]);

    // Parse mapping data and identify editable cells
    useEffect(() => {
        if (!templateData || !mappingData) return;

        setIsLoading(true);
        let cancelled = false;
        (async () => {
            try {
                const parsed = await parseExcelBufferWithExcelJs(base64ToArrayBuffer(templateData));
                if (cancelled) return;
                const normalizedData = normalizeExcelData(parsed.data || []);
                setRawParsedRows(normalizedData);
                const { bodyStartRow, templateBodyRowCount } = getBodyFooterFromMapping(
                    normalizedMapping.mappings,
                    normalizedMapping.metadata,
                    normalizedData.length
                );
                const requiredBodyRows = Math.max(1, (initialData?.detailData?.length ?? 0) || 1);
                const bodyDataStartRow = bodyStartRow + templateBodyRowCount - 1;
                const rowsToInsert = Math.max(0, requiredBodyRows - 1);

                const editable = new Set();
                const initialFormData = {};

                const getVal = (row, key) => {
                    if (!row || key == null) return undefined;
                    if (Object.prototype.hasOwnProperty.call(row, key)) return row[key];
                    const u = String(key).toUpperCase();
                    if (Object.prototype.hasOwnProperty.call(row, u)) return row[u];
                    return undefined;
                };
                const headerRow = initialData?.headerData?.[0];
                const detailRows = initialData?.detailData ?? [];

                normalizedMapping.mappings.forEach(mapping => {
                    if (mapping.editable) {
                        let defaultValue = '';
                        const isUserInput = String(mapping?.source || '').toUpperCase() === 'USER';
                        if (mapping.section === 'BODY') {
                            if (Number.isInteger(mapping.columnIndex)) {
                                for (let i = 0; i < requiredBodyRows; i++) editable.add(`${bodyDataStartRow + i}-${mapping.columnIndex}`);
                                defaultValue = isUserInput ? (mapping.cellValue ?? '') : (getVal(detailRows[0], mapping.fieldKey) ?? '');
                            } else if (typeof mapping.excelCell === 'string') {
                                try {
                                    const decoded = XLSX.utils.decode_cell(mapping.excelCell);
                                    for (let i = 0; i < requiredBodyRows; i++) editable.add(`${bodyDataStartRow + i}-${decoded.c}`);
                                    defaultValue = isUserInput ? (mapping.cellValue ?? '') : (getVal(detailRows[0], mapping.fieldKey) ?? '');
                                } catch (_) {
                                    const col = mapping.excelCell.charCodeAt(0) - 65;
                                    const firstBodyRow = parseInt(mapping.excelCell.slice(1), 10) - 1;
                                    if (!Number.isNaN(firstBodyRow)) {
                                        for (let i = 0; i < requiredBodyRows; i++) editable.add(`${bodyDataStartRow + i}-${col}`);
                                        defaultValue = isUserInput ? (mapping.cellValue ?? '') : (getVal(detailRows[0], mapping.fieldKey) ?? '');
                                    }
                                }
                            }
                        } else {
                            defaultValue = isUserInput ? (mapping.cellValue ?? '') : (getVal(headerRow, mapping.fieldKey) ?? '');
                            if (typeof mapping.excelCell === 'object') {
                                for (let r = mapping.excelCell.start.charCodeAt(0) - 65; r <= mapping.excelCell.end.charCodeAt(0) - 65; r++) {
                                    for (let c = parseInt(mapping.excelCell.start.slice(1), 10) - 1; c <= parseInt(mapping.excelCell.end.slice(1), 10) - 1; c++) {
                                        const targetRow = c > bodyDataStartRow ? c + rowsToInsert : c;
                                        editable.add(`${targetRow}-${r}`);
                                    }
                                }
                            } else {
                                try {
                                    const decoded = XLSX.utils.decode_cell(mapping.excelCell);
                                    const targetRow = decoded.r > bodyDataStartRow ? decoded.r + rowsToInsert : decoded.r;
                                    editable.add(`${targetRow}-${decoded.c}`);
                                } catch (_) {
                                    const col = mapping.excelCell.charCodeAt(0) - 65;
                                    const row = parseInt(mapping.excelCell.slice(1), 10) - 1;
                                    const targetRow = row > bodyDataStartRow ? row + rowsToInsert : row;
                                    editable.add(`${targetRow}-${col}`);
                                }
                            }
                        }
                        initialFormData[mapping.fieldKey] = defaultValue !== null && defaultValue !== undefined ? String(defaultValue) : '';
                    }
                });

                setEditableCells(editable);
                setFormData(initialFormData);
                setIsLoading(false);
            } catch (_) {
                if (!cancelled) {
                    setRawParsedRows([]);
                    setEditableCells(new Set());
                    setFormData({});
                    setIsLoading(false);
                }
            }
        })();
        return () => { cancelled = true; };
    }, [templateData, mappingData, normalizedMapping, initialData]);

    // Render prefilled Excel as styled HTML table with editable overlays
    const [excelHtml, setExcelHtml] = useState('');
    const [colWidths, setColWidths] = useState([]);
    const [rowHeights, setRowHeights] = useState([]);
    const [cellStyles, setCellStyles] = useState({});
    const [cellValidations, setCellValidations] = useState([]);
    const [fullSheetData, setFullSheetData] = useState(null);
    const [computedExcelValues, setComputedExcelValues] = useState(null);
    const [sheetOrigin, setSheetOrigin] = useState({ r: 0, c: 0 });

    // Real-time formula calculation: when excelData changes (including after user edit), recompute all formulas
    useEffect(() => {
        if (!excelData || excelData.length === 0) {
            setComputedExcelValues(null);
            return;
        }
        const values = buildAndGetValues(excelData, sheetOrigin);
        setComputedExcelValues(values);
    }, [excelData, sheetOrigin]);

    useEffect(() => {
        if (!templateData) return;
        appliedInitialDataRef.current = false;
        let cancelled = false;
        (async () => {
            try {
                const parsed = await parseExcelBufferWithExcelJs(base64ToArrayBuffer(templateData));
                if (cancelled) return;
                setFullSheetData(null);
                setSheetOrigin(parsed.origin || { r: 0, c: 0 });
                setCellStyles(parsed.styles || []);
                setCellValidations(parsed.validations || []);
                setColWidths(parsed.colWidths || []);
                setRowHeights(parsed.rowHeights || []);
                setMergeInfo(parsed.merges || []);
                setAnchoredImages(parsed.images || []);
                const normalized = normalizeExcelData(parsed.raw || []);
                setExcelData(normalized.length ? normalized : (parsed.raw || []));
            } catch (e) {
                if (cancelled) return;
                console.error('Failed to decode prefilled Excel to rows (exceljs parse)', e);
                setExcelData([]);
                setAnchoredImages([]);
                setCellValidations([]);
            }
        })();
        return () => { cancelled = true; };
    }, [templateData]);

    // Apply initialData (HeaderData + DetailData from API); overlay saved invoice user data when present
    useEffect(() => {
        if (!initialData || !excelData?.length) return;
        if (appliedInitialDataRef.current) return;

        if (!normalizedMapping.mappings?.length) return;
        appliedInitialDataRef.current = true;

        const { bodyStartRow, templateBodyRowCount } = getBodyFooterFromMapping(
            normalizedMapping.mappings,
            normalizedMapping.metadata,
            excelData.length
        );
        const detailRows = initialData.detailData ?? [];
        const saved = initialData.savedInvoiceData;
        // Keep live editor layout tied to actual detail rows only.
        // savedInvoiceData.meta can reflect previously expanded snapshots and
        // must not drive another structural expansion in the current editor view.
        const requiredBodyRows = Math.max(1, detailRows.length || 1);
        // bodyStartRow from mapping is in sheet coordinates; convert to grid row index by subtracting sheet origin
        const originR = sheetOrigin?.r ?? 0;
        const gridBodyStartRow = bodyStartRow - originR;
        const bodyDataStartRow = gridBodyStartRow + templateBodyRowCount - 1;
        const rowsToInsert = Math.max(0, requiredBodyRows - 1);
        const templateBaseRowCount = Array.isArray(rawParsedRows) && rawParsedRows.length
            ? rawParsedRows.length
            : excelData.length;
        const expectedExpandedRowCount = templateBaseRowCount + rowsToInsert;

        let next = excelData.map((row) => (Array.isArray(row) ? [...row] : row));
        let nextMerges = [...mergeInfo];
        const shouldInsertBodyRows = requiredBodyRows > 1 && next.length < expectedExpandedRowCount;

        if (shouldInsertBodyRows) {
            for (let k = 1; k <= rowsToInsert; k++) {
                const sourceRow = next[bodyDataStartRow];
                const newRow = copyRowWithFormulaOffset(sourceRow, k, bodyDataStartRow);
                next.splice(bodyDataStartRow + k, 0, newRow);
            }
            const newMerges = [];
            mergeInfo.forEach((m) => {
                if (m.startRow === bodyDataStartRow && m.endRow === bodyDataStartRow) {
                    newMerges.push(m);
                    for (let k = 1; k <= rowsToInsert; k++) {
                        newMerges.push({
                            startRow: bodyDataStartRow + k,
                            startCol: m.startCol,
                            endRow: bodyDataStartRow + k,
                            endCol: m.endCol,
                        });
                    }
                } else if (m.startRow > bodyDataStartRow) {
                    newMerges.push({
                        startRow: m.startRow + rowsToInsert,
                        startCol: m.startCol,
                        endRow: m.endRow + rowsToInsert,
                        endCol: m.endCol,
                    });
                } else if (m.startRow <= bodyDataStartRow && m.endRow >= bodyDataStartRow && m.endRow > m.startRow) {
                    newMerges.push({
                        startRow: m.startRow,
                        startCol: m.startCol,
                        endRow: m.endRow + rowsToInsert,
                        endCol: m.endCol,
                    });
                } else {
                    newMerges.push(m);
                }
            });
            setMergeInfo(newMerges);
            setRowHeights((prev) => {
                const p = prev || [];
                const dataRowH = p[bodyDataStartRow];
                const nextHeights = [...p];
                for (let k = 1; k <= rowsToInsert; k++) nextHeights.splice(bodyDataStartRow + k, 0, dataRowH ?? 20);
                return nextHeights;
            });
            setCellStyles((prev) => {
                if (!prev || typeof prev !== 'object') return prev;
                const nextStyles = {};
                const rowIndices = Object.keys(prev).map(Number).sort((a, b) => a - b);
                for (const r of rowIndices) {
                    if (r > bodyDataStartRow) {
                        nextStyles[r + rowsToInsert] = prev[r];
                    } else {
                        nextStyles[r] = prev[r];
                    }
                }
                const sourceStyle = prev[bodyDataStartRow];
                for (let k = 1; k <= rowsToInsert; k++) {
                    if (sourceStyle && typeof sourceStyle === 'object') {
                        nextStyles[bodyDataStartRow + k] = {};
                        for (const col in sourceStyle) {
                            nextStyles[bodyDataStartRow + k][col] = sourceStyle[col];
                        }
                    }
                }
                return nextStyles;
            });
            setCellValidations((prev) => {
                if (!Array.isArray(prev) || prev.length === 0) return prev;
                const nextValidations = [...prev];
                const sourceValidationRow = Array.isArray(prev[bodyDataStartRow]) ? prev[bodyDataStartRow] : null;
                const oneBasedSourceRow = bodyDataStartRow + 1;
                for (let k = 1; k <= rowsToInsert; k++) {
                    const cloneRow = sourceValidationRow
                        ? sourceValidationRow.map((rule) => {
                            if (!rule || typeof rule !== 'object') return rule;
                            const out = { ...rule };
                            if (Array.isArray(rule.formulae)) {
                                out.formulae = rule.formulae.map((f) =>
                                    adjustFormulaRowRefs(String(f || ''), k, oneBasedSourceRow, true)
                                );
                            }
                            return out;
                        })
                        : null;
                    nextValidations.splice(bodyDataStartRow + k, 0, cloneRow);
                }
                return nextValidations;
            });

            for (let r = bodyDataStartRow + 1 + rowsToInsert; r < next.length; r++) {
                const row = next[r];
                if (!Array.isArray(row)) continue;
                const oneBasedThreshold = bodyDataStartRow + 1;
                for (let c = 0; c < row.length; c++) {
                    const cell = row[c];
                    if (cell && typeof cell === 'object' && typeof cell.formula === 'string' && cell.formula.trim()) {
                        const adjusted = adjustFormulaRowRefs(cell.formula, rowsToInsert, oneBasedThreshold, true);
                        const withEquals = adjusted.startsWith('=') ? adjusted : '=' + adjusted;
                        next[r][c] = { ...cell, formula: withEquals };
                    }
                }
            }
        }

        const getVal = (row, key) => {
            if (!row || key == null) return undefined;
            if (Object.prototype.hasOwnProperty.call(row, key)) return row[key];
            const u = String(key).toUpperCase();
            if (Object.prototype.hasOwnProperty.call(row, u)) return row[u];
            return undefined;
        };
        const setCellValuePreserveFormula = (grid, r, c, val) => {
            if (!grid[r] || grid[r][c] === undefined) return;
            const existing = grid[r][c];
            if (existing && typeof existing === 'object' && Object.prototype.hasOwnProperty.call(existing, 'formula')) {
                grid[r][c] = { ...existing, value: val };
            } else {
                grid[r][c] = val;
            }
        };
        const hasUsableValue = (val) => {
            if (val === undefined || val === null) return false;
            if (typeof val === 'string' && val.trim() === '') return false;
            return true;
        };
        const headerRow = initialData.headerData?.[0];

        // 1) Apply fresh DB/header values
        normalizedMapping.mappings.forEach((mapping) => {
            const isUserInput = String(mapping?.source || '').toUpperCase() === 'USER';
            if (mapping.editable && isUserInput) return;
            if (mapping.section === 'BODY') {
                if (Number.isInteger(mapping.columnIndex)) {
                    detailRows.forEach((dr, i) => {
                        const rowIdx = bodyDataStartRow + i;
                        if (rowIdx < next.length && next[rowIdx]) {
                            const val = getVal(dr, mapping.fieldKey);
                            if (hasUsableValue(val))
                                setCellValuePreserveFormula(next, rowIdx, mapping.columnIndex, val);
                        }
                    });
                } else if (typeof mapping.excelCell === 'string') {
                    try {
                        const decoded = XLSX.utils.decode_cell(mapping.excelCell);
                        const col = decoded.c;
                        detailRows.forEach((dr, i) => {
                            const rowIdx = bodyDataStartRow + i;
                            if (rowIdx < next.length && next[rowIdx] && next[rowIdx][col] !== undefined) {
                                const val = getVal(dr, mapping.fieldKey);
                                if (hasUsableValue(val)) setCellValuePreserveFormula(next, rowIdx, col, val);
                            }
                        });
                    } catch (_) { }
                }
            } else {
                const val = getVal(headerRow, mapping.fieldKey);
                if (!hasUsableValue(val)) return;
                if (typeof mapping.excelCell === 'object' && mapping.excelCell?.start) {
                    const sc = String(mapping.excelCell.start);
                    const colPart = sc.replace(/\d/g, '');
                    const rowPart = sc.replace(/\D/g, '');
                    const r = rowPart ? parseInt(rowPart, 10) - 1 : 0;
                    const targetRow = r > bodyDataStartRow ? r + rowsToInsert : r;
                    let c = 0;
                    if (colPart) {
                        for (let i = 0; i < colPart.length; i++) c = c * 26 + (colPart.toUpperCase().charCodeAt(i) - 64);
                        c--;
                    }
                    if (targetRow >= 0 && next[targetRow] && next[targetRow][c] !== undefined) setCellValuePreserveFormula(next, targetRow, c, val);
                } else if (typeof mapping.excelCell === 'string') {
                    try {
                        const decoded = XLSX.utils.decode_cell(mapping.excelCell);
                        const targetRow = decoded.r > bodyDataStartRow ? decoded.r + rowsToInsert : decoded.r;
                        if (next[targetRow] && next[targetRow][decoded.c] !== undefined)
                            setCellValuePreserveFormula(next, targetRow, decoded.c, val);
                    } catch (_) { }
                }
            }
        });

        // 2) Overlay saved user-entered values (from savedInvoiceData) on top of DB data, preserving formulas
        if (saved && Array.isArray(saved.mappedFields)) {
            saved.mappedFields.forEach((mf) => {
                const r = mf.rowIndex;
                const c = mf.colIndex;
                if (!Number.isInteger(r) || !Number.isInteger(c)) return;
                if (!next[r] || next[r][c] === undefined) return;
                const val = mf.value;
                if (!hasUsableValue(val)) return;
                // Guard against backend/default "0" leaking into blank USER-input cells.
                // If template cell is blank and saved USER value is 0, keep blank.
                const isUserInput = String(mf?.source || '').toUpperCase() === 'USER';
                if (isUserInput && (val === 0 || String(val).trim() === '0')) {
                    const existing = next[r]?.[c];
                    const existingPlain = (existing && typeof existing === 'object')
                        ? (Object.prototype.hasOwnProperty.call(existing, 'value') ? (existing.value ?? '') : '')
                        : (existing ?? '');
                    if (String(existingPlain).trim() === '') return;
                }
                setCellValuePreserveFormula(next, r, c, val);
            });
        }

        setExcelData(next);
    }, [initialData, excelData, normalizedMapping.mappings, mergeInfo, rawParsedRows]);

    // When opened from main page "Print Bill" dialog: show same preview and download dialog (no auto-download, same flow as Employee Fill)
    useEffect(() => {
        if (!triggerDownloadOnLoad || !excelData?.length || triggeredDownloadRef.current) return;
        const savedRows = initialData?.savedInvoiceData?.sheet?.rows;
        const savedRowCount = Array.isArray(savedRows) ? savedRows.length : 0;
        if (savedRowCount > 0 && excelData.length !== savedRowCount) return;
        triggeredDownloadRef.current = true;
        setShowSaveSuccessDialog(true);
    }, [triggerDownloadOnLoad, excelData, initialData?.savedInvoiceData?.sheet?.rows]);

    // NOTE: Inputs are rendered inline in the table. No overlay effect needed.

    const normalizeExcelData = (raw) => {
        const rows = raw.filter((r) => Array.isArray(r));
        const maxCols = Math.max(...rows.map((r) => r.length), 1);

        return rows.map((r) => {
            const row = [...r];
            while (row.length < maxCols) row.push("");
            return row;
        });
    };

    const colLetter = (c) => String.fromCharCode(65 + c);

    const isInsideMerge = (row, col) =>
        mergeInfo.some(
            (m) =>
                row >= m.startRow &&
                row <= m.endRow &&
                col >= m.startCol &&
                col <= m.endCol &&
                !(row === m.startRow && col === m.startCol)
        );

    const getMergeAtCell = (row, col) =>
        mergeInfo.find(
            (m) => m.startRow === row && m.startCol === col
        );

    const buildMergeCells = () => {
        const mergeCells = {};
        mergeInfo.forEach((m) => {
            const rowspan = m.endRow - m.startRow + 1;
            const colspan = m.endCol - m.startCol + 1;
            const cell = colLetter(m.startCol) + (m.startRow + 1);
            mergeCells[cell] = [rowspan, colspan];
        });
        return mergeCells;
    };

    // Overlay editable inputs on the rendered HTML table
    useEffect(() => {
        if (!excelHtml || !excelData.length) return;
        const tableEl = document.getElementById('excel-table');
        if (!tableEl) return;

        // Clear previous overlays
        tableEl.querySelectorAll('.editable-overlay').forEach(el => el.remove());

        const rows = tableEl.querySelectorAll('tr');
        excelData.forEach((row, rowIndex) => {
            const tr = rows[rowIndex];
            if (!tr) return;
            const cells = tr.querySelectorAll('td');
            row.forEach((cell, colIndex) => {
                const td = cells[colIndex];
                if (!td) return;
                const cellKey = `${rowIndex}-${colIndex}`;
                if (editableCells.has(cellKey)) {
                    const isFormula = typeof cell === 'object' && cell.formula;
                    const displayValue = getCellPlainValue(cell);
                    const input = document.createElement('input');
                    input.type = 'text';
                    input.value = displayValue || '';
                    input.className = 'editable-overlay';
                    Object.assign(input.style, {
                        position: 'absolute',
                        background: 'transparent',
                        border: 'none',
                        outline: 'none',
                        width: `${td.offsetWidth}px`,
                        height: `${td.offsetHeight}px`,
                        padding: '0',
                        margin: '0',
                        fontSize: window.getComputedStyle(td).fontSize,
                        fontFamily: window.getComputedStyle(td).fontFamily,
                        color: 'inherit',
                        textAlign: 'inherit',
                        verticalAlign: 'top',
                        boxSizing: 'border-box',
                    });
                    input.addEventListener('blur', (e) => handleCellChange(rowIndex, colIndex, e.target.value));
                    // Position overlay relative to td
                    const rect = td.getBoundingClientRect();
                    const tableRect = tableEl.getBoundingClientRect();
                    input.style.left = `${rect.left - tableRect.left}px`;
                    input.style.top = `${rect.top - tableRect.top}px`;
                    tableEl.style.position = 'relative';
                    tableEl.appendChild(input);
                }
            });
        });
    }, [excelHtml, excelData, editableCells]);

    // Parse A1-style cell ref to 0-based grid { r, c } (supports A1, $A$1, AA10, $D$10, ...)
    // Sheet origin: Excel row/col numbers are absolute; grid is cropped by worksheet dimensions.
    const parseA1Ref = (cellStr) => {
        if (!cellStr || typeof cellStr !== 'string') return null;
        const cleaned = cellStr.replace(/\$/g, '').trim();
        const m = cleaned.match(/^([A-Z]+)(\d+)$/i);
        if (!m) return null;
        const colLetters = m[1].toUpperCase();
        const row = parseInt(m[2], 10);
        if (Number.isNaN(row) || row < 1) return null;
        let c = 0;
        for (let i = 0; i < colLetters.length; i++) {
            c = c * 26 + (colLetters.charCodeAt(i) - 64);
        }
        const originR = sheetOrigin?.r ?? 0;
        const originC = sheetOrigin?.c ?? 0;
        return { r: (row - 1) - originR, c: (c - 1) - originC };
    };

    /** Coerce any value to number for formula use; strings like "123" or "1,234.56" become number. */
    const coerceToNumber = (v) => {
        if (v === null || v === undefined || v === '') return 0;
        if (typeof v === 'number' && !Number.isNaN(v)) return v;
        if (typeof v === 'string') {
            const s = v.trim().replace(/,/g, '');
            if (/^-?\d+(\.\d+)?$/.test(s)) {
                const n = Number(s);
                if (Number.isFinite(n)) return n;
            }
        }
        const n = Number(v);
        return Number.isFinite(n) ? n : 0;
    };

    const getCellValue = (context, r, c) => {
        if (!context || !Array.isArray(context[r])) return 0;
        const cell = context[r][c];
        if (cell === null || cell === undefined) return 0;
        return coerceToNumber(getCellPlainValue(cell));
    };

    // Formula evaluator for employee fill: SUM ranges, single-cell refs, and simple arithmetic (no HyperFormula).
    const evaluateFormula = (formula, context, currentRow = null) => {
        if (!formula || typeof formula !== 'string') return '';
        if (!context || !Array.isArray(context) || context.length === 0) return '';
        try {
            let f = String(formula).trim();
            if (!f) return '';
            if (f.startsWith('=')) f = f.slice(1).trim();
            if (!f) return '';

            const maxRow = context.length - 1;
            // Normalize whole-column ranges (e.g. D:D or $D:$D inside SUM) to bounded range
            f = f.replace(/(\$?[A-Z]{1,3})\s*:\s*\1\b/gi, (match, col) => {
                const colClean = String(col).replace(/\$/g, '');
                return `${colClean}1:${colClean}${maxRow + 1}`;
            });

            // Targeted pattern: SUM(D19:INDEX(D:D,ROW()-1))
            // Resolve INDEX(D:D,ROW()-1) to the cell just above currentRow in column D
            const sumIndexMatch = f.match(/^SUM\s*\(\s*([A-Z]+)(\d+)\s*:\s*INDEX\s*\(\s*([A-Z]+)\s*:\s*\3\s*,\s*ROW\s*\(\s*\)\s*-\s*1\s*\)\s*\)$/i);
            if (sumIndexMatch && currentRow !== null && Number.isInteger(currentRow)) {
                const startCol = sumIndexMatch[1].toUpperCase();
                const startRow = parseInt(sumIndexMatch[2], 10) - 1;
                const endCol = sumIndexMatch[3].toUpperCase();
                const startColIdx = startCol.charCodeAt(0) - 65;
                const endColIdx = endCol.charCodeAt(0) - 65;
                // One-time diagnostic for this pattern
                if (typeof window !== 'undefined' && !window._didLogIndexPattern) {
                    window._didLogIndexPattern = true;
                    // eslint-disable-next-line no-console
                    console.log('[EmployeeFillMode] INDEX pattern match:', { formula: f, currentRow, startCol, startRow, endCol, startColIdx, endColIdx });
                }
                if (startColIdx === endColIdx && startRow >= 0 && startRow < currentRow) {
                    let total = 0;
                    for (let r = startRow; r < currentRow; r++) {
                        total += getCellValue(context, r, startColIdx);
                    }
                    if (typeof window !== 'undefined' && !window._didLogIndexResult) {
                        window._didLogIndexResult = true;
                        // eslint-disable-next-line no-console
                        console.log('[EmployeeFillMode] INDEX pattern computed total:', total);
                    }
                    return total;
                }
            }

            // Minimal ROW() support: replace with current row index (1-based) during evaluation
            // We'll replace ROW() with a placeholder and resolve it later per cell if needed
            // For now, since we only support INDEX(D:D,ROW()-1), we'll resolve directly.
            f = f.replace(/\bROW\(\s*\)/gi, 'ROW_PLACEHOLDER');

            // Minimal INDEX(D:D,ROW()-1) support: replace with the last data row index (dynamic)
            // We'll replace INDEX(D:D,ROW()-1) with a concrete cell reference like D<lastRow>
            f = f.replace(/INDEX\(\s*(\$?[A-Z]{1,3})\s*:\s*\1\s*,\s*ROW_PLACEHOLDER\s*-\s*1\s*\)/gi,
                (_, col) => {
                    const colClean = String(col).replace(/\$/g, '');
                    // Use the last non-empty row in column D (or a safe default)
                    let lastRow = maxRow;
                    for (let r = maxRow; r >= 0; r--) {
                        const val = context[r] && context[r][colClean.charCodeAt(0) - 65];
                        if (val !== null && val !== undefined && val !== '') {
                            lastRow = r;
                            break;
                        }
                    }
                    return `${colClean}${lastRow + 1}`;
                });

            // Remove any remaining ROW_PLACEHOLDER (fallback to maxRow)
            f = f.replace(/ROW_PLACEHOLDER/g, String(maxRow + 1));

            // Single cell reference: =D10 or =$D$10 or =AA10
            const singleRefMatch = f.match(/^\s*(\$?[A-Z]+\$?\d+)\s*$/i);
            if (singleRefMatch) {
                const ref = singleRefMatch[1].replace(/\$/g, '');
                const p = parseA1Ref(ref);
                if (p) return getCellValue(context, p.r, p.c);
            }

            // SUM: allow flexible spacing and $ in refs (e.g. SUM(D10:D12), SUM($D$10:$D$12), multi-letter columns)
            const sumMatch = f.match(/SUM\s*\(\s*([A-Z$]+\d+)\s*:\s*([A-Z$]+\d+)\s*\)/i);
            if (sumMatch) {
                const startRef = sumMatch[1].replace(/\$/g, '');
                const endRef = sumMatch[2].replace(/\$/g, '');
                const p1 = parseA1Ref(startRef);
                const p2 = parseA1Ref(endRef);
                if (p1 && p2) {
                    let total = 0;
                    for (let r = p1.r; r <= p2.r; r++) {
                        for (let c = p1.c; c <= p2.c; c++) {
                            total += getCellValue(context, r, c);
                        }
                    }
                    return total;
                }
            }

            // Replace cell references ($D$10, D10, etc.) with numeric values
            const replaced = f.replace(/(\$?[A-Z]+)(\$?)(\d+)/gi, (_, col, _rowAbs, row) => {
                const parsed = parseA1Ref(col + row);
                if (!parsed) return 0;
                return getCellValue(context, parsed.r, parsed.c);
            });

            // Simple arithmetic fallback (skip if nothing left to eval)
            const trimmed = replaced.trim();
            if (!trimmed || trimmed === '') return '';
            const result = Function('"use strict"; return (' + trimmed + ')')();
            return result === undefined || result === null ? '' : result;
        } catch (e) {
            // Suppress repeated console noise for the known pattern SUM(D19:INDEX(D:D,ROW()-1))
            if (!formula.includes('INDEX(') || !formula.includes('ROW()')) {
                console.warn('Formula evaluation failed:', formula, e);
            }
            return '';
        }
    };

    const getMappingForCell = (rowIndex, colIndex) => {
        const totalRows = (excelData && excelData.length) ? excelData.length : rawParsedRows.length;
        const { bodyStartRow, templateBodyRowCount } = getBodyFooterFromMapping(
            normalizedMapping.mappings,
            normalizedMapping.metadata,
            totalRows
        );
        const bodyDataStartRow = bodyStartRow + templateBodyRowCount - 1;
        const requiredBodyRows = Math.max(1, (initialData?.detailData?.length ?? 0) || 1);
        const bodyDataEndRow = bodyDataStartRow + requiredBodyRows - 1;
        const rowsToInsert = Math.max(0, requiredBodyRows - 1);

        return normalizedMapping.mappings.find((m) => {
            if (!m?.editable) return false;
            const isBodyDataRow = rowIndex >= bodyDataStartRow && rowIndex <= bodyDataEndRow;
            if (m.section === 'BODY' && Number.isInteger(m.columnIndex)) {
                return isBodyDataRow && m.columnIndex === colIndex;
            }
            if (typeof m.excelCell === 'string') {
                try {
                    const decoded = XLSX.utils.decode_cell(m.excelCell);
                    if (m.section === 'BODY') return isBodyDataRow && decoded.c === colIndex;
                    // Header/footer: match original row or shifted row (after body insertion)
                    const matchRow = decoded.r === rowIndex || (decoded.r > bodyDataStartRow && decoded.r + rowsToInsert === rowIndex);
                    return decoded.c === colIndex && matchRow;
                } catch (_) {
                    const mapCol = m.excelCell.charCodeAt(0) - 65;
                    const mapRow = parseInt(m.excelCell.slice(1), 10) - 1;
                    if (m.section === 'BODY') return isBodyDataRow && mapCol === colIndex;
                    const matchRow = mapRow === rowIndex || (mapRow > bodyDataStartRow && mapRow + rowsToInsert === rowIndex);
                    return mapCol === colIndex && matchRow;
                }
            }
            return false;
        }) || null;
    };

    const validateInputByType = (value, type) => {
        const input = String(value ?? '');
        const t = String(type || 'TEXT').toUpperCase();
        if (t === 'NUMBER') {
            if (input === '' || /^-?\d*\.?\d*$/.test(input)) return { valid: true };
            return { valid: false, message: 'Only numeric values are allowed for this field.' };
        }
        if (t === 'DATE') {
            if (input === '' || /^[0-9A-Za-z/-]*$/.test(input)) return { valid: true };
            return { valid: false, message: 'Only date characters (A-Z, 0-9, /, -) are allowed for this field.' };
        }
        return { valid: true };
    };

    const isBlankValue = (v) => v === null || v === undefined || String(v).trim() === '';
    const parseNumberLike = (v) => {
        const s = String(v ?? '').trim().replace(/,/g, '');
        if (!s) return NaN;
        const n = Number(s);
        return Number.isFinite(n) ? n : NaN;
    };
    const compareByOperator = (left, op, a, b) => {
        switch (op) {
            case 'between': return left >= a && left <= b;
            case 'notbetween': return !(left >= a && left <= b);
            case 'equal': return left === a;
            case 'notequal': return left !== a;
            case 'greaterthan': return left > a;
            case 'lessthan': return left < a;
            case 'greaterthanorequal': return left >= a;
            case 'lessthanorequal': return left <= a;
            default: return true;
        }
    };
    const resolveValidationOperand = (expr, context, currentRow, currentCol) => {
        if (expr === null || expr === undefined) return null;
        const s = String(expr).trim();
        if (!s) return null;
        if (/^[-+]?\d+(\.\d+)?$/.test(s)) return Number(s);
        if ((s.startsWith('"') && s.endsWith('"')) || (s.startsWith("'") && s.endsWith("'"))) return s.slice(1, -1);
        const formula = s.startsWith('=') ? s : `=${s}`;
        return evaluateFormula(formula, context, currentRow);
    };
    const validateExcelRule = (rule, value, rowIndex, colIndex, contextRows, computedValues = null, formulaEngine = null) => {
        if (!rule || typeof rule !== 'object') return { valid: true };
        const type = String(rule.type || '').toLowerCase();
        // Custom formulas decide blank handling themselves (e.g. OR(COUNTA(...)=0,...)).
        // Always evaluate custom rules so SUM/ABS mismatches stay red when inputs are empty.
        if (rule.allowBlank && isBlankValue(value) && type !== 'custom') {
            return { valid: true };
        }
        const op = String(rule.operator || '').toLowerCase();
        const msg = rule.error || rule.prompt || 'Value does not satisfy Excel validation rule.';
        if (type === 'whole' || type === 'decimal') {
            const n = parseNumberLike(value);
            if (!Number.isFinite(n)) return { valid: false, message: msg };
            const a = parseNumberLike(resolveValidationOperand(rule.formulae?.[0], contextRows, rowIndex, colIndex));
            const b = parseNumberLike(resolveValidationOperand(rule.formulae?.[1], contextRows, rowIndex, colIndex));
            const ok = compareByOperator(n, op || 'between', a, b);
            return ok ? { valid: true } : { valid: false, message: msg };
        }
        if (type === 'textlength') {
            const len = String(value ?? '').length;
            const a = parseNumberLike(resolveValidationOperand(rule.formulae?.[0], contextRows, rowIndex, colIndex));
            const b = parseNumberLike(resolveValidationOperand(rule.formulae?.[1], contextRows, rowIndex, colIndex));
            const ok = compareByOperator(len, op || 'between', a, b);
            return ok ? { valid: true } : { valid: false, message: msg };
        }
        if (type === 'list') {
            const raw = String(rule.formulae?.[0] || '').trim();
            if (!raw) return { valid: true };
            if (raw.startsWith('"') && raw.endsWith('"')) {
                const allowed = raw.slice(1, -1).split(',').map((x) => x.trim());
                return allowed.includes(String(value ?? '').trim()) ? { valid: true } : { valid: false, message: msg };
            }
            return { valid: true };
        }
        if (type === 'date') {
            const t = new Date(String(value ?? '')).getTime();
            if (!Number.isFinite(t)) return { valid: false, message: msg };
            const a = new Date(String(resolveValidationOperand(rule.formulae?.[0], contextRows, rowIndex, colIndex) ?? '')).getTime();
            const b = new Date(String(resolveValidationOperand(rule.formulae?.[1], contextRows, rowIndex, colIndex) ?? '')).getTime();
            const ok = compareByOperator(t, op || 'between', a, b);
            return ok ? { valid: true } : { valid: false, message: msg };
        }
        if (type === 'custom') {
            const rawExpr = String(rule.formulae?.[0] || '').trim();
            if (!rawExpr) return { valid: true };
            const temp = contextRows.map((r) => (Array.isArray(r) ? [...r] : r));
            if (temp[rowIndex] && temp[rowIndex][colIndex] !== undefined) {
                const existing = temp[rowIndex][colIndex];
                temp[rowIndex][colIndex] = (existing && typeof existing === 'object')
                    ? { ...existing, value }
                    : value;
            }
            // Prefer shared HyperFormula engine (built once per sheet snapshot).
            if (formulaEngine) {
                const ok = formulaEngine.isValid(rawExpr, rowIndex, colIndex);
                return ok ? { valid: true } : { valid: false, message: msg };
            }
            // One-shot engine from temp grid (includes in-progress typed value).
            const eng = createFormulaEngine(temp, sheetOrigin);
            if (eng) {
                try {
                    const ok = eng.isValid(rawExpr, rowIndex, colIndex);
                    return ok ? { valid: true } : { valid: false, message: msg };
                } finally {
                    eng.destroy();
                }
            }
            return { valid: false, message: msg };
        }
        return { valid: true };
    };

    const formatDateAsDdMmmYyyy = (raw) => {
        const s = String(raw ?? '').trim();
        if (!s) return '';
        const months = ['JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'];
        const monthMap = months.reduce((acc, m, i) => { acc[m] = i + 1; return acc; }, {});
        const pad2 = (n) => String(n).padStart(2, '0');

        const fromParts = (dd, mm, yyyy) => {
            const d = Number(dd);
            const m = Number(mm);
            const y = Number(yyyy);
            if (!Number.isInteger(d) || !Number.isInteger(m) || !Number.isInteger(y)) return null;
            if (y < 1000 || y > 9999 || m < 1 || m > 12 || d < 1 || d > 31) return null;
            const dt = new Date(y, m - 1, d);
            if (dt.getFullYear() !== y || dt.getMonth() !== (m - 1) || dt.getDate() !== d) return null;
            return `${pad2(d)}-${months[m - 1]}-${y}`;
        };

        let m = s.match(/^(\d{1,2})[-/](\d{1,2})[-/](\d{4})$/); // DD-MM-YYYY
        if (m) return fromParts(m[1], m[2], m[3]);

        m = s.match(/^(\d{4})[-/](\d{1,2})[-/](\d{1,2})$/); // YYYY-MM-DD
        if (m) return fromParts(m[3], m[2], m[1]);

        m = s.match(/^(\d{1,2})[-/]([A-Za-z]{3})[-/](\d{4})$/); // DD-MMM-YYYY
        if (m) {
            const mon = String(m[2]).toUpperCase();
            const mm = monthMap[mon];
            if (!mm) return null;
            return fromParts(m[1], mm, m[3]);
        }

        const dt = new Date(s);
        if (!Number.isNaN(dt.getTime())) {
            return `${pad2(dt.getDate())}-${months[dt.getMonth()]}-${dt.getFullYear()}`;
        }
        return null;
    };

    const handleCellChange = (rowIndex, colIndex, value, finalize = false) => {
        const cellKey = `${rowIndex}-${colIndex}`;
        if (!editableCells.has(cellKey)) return;

        const mapping = getMappingForCell(rowIndex, colIndex);
        const validation = validateInputByType(value, mapping?.type);
        if (!validation.valid) {
            setValidationError(validation.message);
            return;
        }
        if (validationError) setValidationError('');

        let nextValue = value;
        if (String(mapping?.type || '').toUpperCase() === 'DATE' && finalize) {
            const formatted = formatDateAsDdMmmYyyy(value);
            if (value !== '' && !formatted) {
                setValidationError('Date must be in format DD-MMM-YYYY (example: 05-Jan-2026).');
                return;
            }
            nextValue = formatted;
        }

        const excelRule = cellValidations?.[rowIndex]?.[colIndex] || null;
        if (excelRule) {
            const excelValidation = validateExcelRule(
                excelRule,
                nextValue,
                rowIndex,
                colIndex,
                excelData || [],
                computedExcelValues
            );
            if (!excelValidation.valid) {
                setValidationError(excelValidation.message);
                // Do not block typing; keep value entry fluid and enforce on save.
            } else if (validationError) {
                setValidationError('');
            }
        }

        if (mapping) {
            setFormData(prev => ({ ...prev, [mapping.fieldKey]: nextValue }));
        }

        // Keep raw input so trailing zeros (e.g. 4.90) and partial decimals are preserved while typing.
        // Formula evaluation coerces to number at read time via getCellValue/coerceToNumber.
        const valueToStore = nextValue;

        // Update excelData so formula useEffect runs and recalculates (real-time)
        setExcelData(prev => {
            const updated = prev.map((r, ri) =>
                r.map((c, ci) => {
                    if (ri === rowIndex && ci === colIndex) {
                        const next = typeof c === 'object' ? { ...c, value: valueToStore } : valueToStore;
                        return next;
                    }
                    return c;
                })
            );
            return updated;
        });
    };

    const handleSave = async () => {
        // Excel validation rules (template): totals, lists, custom formulas, etc.
        if (invalidValidationCells.size > 0) {
            const first = [...invalidValidationCells][0];
            const [rStr, cStr] = first.split('-');
            const r = Number(rStr);
            const c = Number(cStr);
            const rule = cellValidations?.[r]?.[c];
            const invalidRows = [...invalidValidationCells]
                .map((k) => Number(String(k).split('-')[0]))
                .filter((row) => Number.isInteger(row))
                .sort((a, b) => a - b);
            const uniqueRows = [...new Set(invalidRows)];
            const rowLabel = uniqueRows.length
                ? `Row${uniqueRows.length > 1 ? 's' : ''} ${uniqueRows.map((row) => row + 1).join(', ')}`
                : `Cell ${XLSX.utils.encode_cell({ r, c })}`;
            const baseMsg = rule?.error || rule?.prompt || 'Validation condition is not satisfied.';
            const msg = `${rowLabel}: ${baseMsg}`;
            setValidationError(msg);
            return;
        }
        if (hasBlockingTypeErrors) {
            setValidationError('Fix invalid number or date fields before saving.');
            return;
        }
        setShowLoader(true);
        setLoaderMessage('Saving invoice...');
        try {
            const invoicePayload = buildInvoicePayload();
            const result = await onSave(invoicePayload);
            if (result?.success) {
                setSavedInvoicePayload(invoicePayload);
                setShowSaveSuccessDialog(true);
            } else if (result?.message) {
                setMessageDialog({ type: 'error', message: result.message });
                setShowMessageDialog(true);
            }
        } catch (e) {
            const errMsg = e?.message || 'Save failed';
            setMessageDialog({ type: 'error', message: errMsg });
            setShowMessageDialog(true);
        } finally {
            setShowLoader(false);
            setLoaderMessage('');
        }
    };

    const getCellPlainValue = (cell) => {
        if (cell === null || cell === undefined) return '';
        if (typeof cell !== 'object') return cell;
        if ('value' in cell) return cell.value ?? '';
        if ('result' in cell) return cell.result ?? '';
        if ('text' in cell) return cell.text ?? '';
        if (Array.isArray(cell.richText)) return cell.richText.map((p) => p?.text || '').join('');
        return '';
    };

    const resolveDisplayCellValue = (r, c, cell) => {
        const computed = computedExcelValues?.[r]?.[c];
        const hasComputed = computed !== undefined && computed !== null && computed !== '' && !isFormulaError(computed);
        if (hasComputed) return formatComputedValue(computed);
        return getCellPlainValue(cell);
    };
    const getValidationCellValue = (contextRows, r, c, computedValues = null) => {
        const computed = computedValues?.[r]?.[c];
        const hasComputed = computed !== undefined && computed !== null && computed !== '' && !isFormulaError(computed);
        if (hasComputed) return formatComputedValue(computed);
        return getCellPlainValue(contextRows?.[r]?.[c]);
    };
    const invalidValidationCells = React.useMemo(() => {
        const bad = new Set();
        const rows = excelData || [];
        const validations = cellValidations || [];
        // Build HyperFormula once per sheet snapshot for all custom validation rules.
        const engine = createFormulaEngine(rows, sheetOrigin);
        try {
            for (let r = 0; r < validations.length; r++) {
                const vrow = validations[r];
                if (!Array.isArray(vrow)) continue;
                for (let c = 0; c < vrow.length; c++) {
                    const rule = vrow[c];
                    if (!rule) continue;
                    const val = getValidationCellValue(rows, r, c, computedExcelValues);
                    const check = validateExcelRule(rule, val, r, c, rows, computedExcelValues, engine);
                    if (!check.valid) {
                        // Only mark cells that have the validation rule applied (e.g. O–S).
                        // Do not paint every formula reference (N, U) — those are often read-only totals.
                        bad.add(`${r}-${c}`);
                    }
                }
            }
        } finally {
            if (engine) engine.destroy();
        }
        return bad;
    }, [cellValidations, excelData, computedExcelValues, sheetOrigin]);

    /** Mapped NUMBER/DATE fields must be empty or fully valid before save (stricter than while typing). */
    const hasBlockingTypeErrors = React.useMemo(() => {
        const rows = excelData || [];
        for (const key of editableCells) {
            const [rStr, cStr] = String(key).split('-');
            const r = Number(rStr);
            const c = Number(cStr);
            if (!Number.isInteger(r) || !Number.isInteger(c)) continue;
            const mapping = getMappingForCell(r, c);
            if (!mapping) continue;
            const val = getCellPlainValue(rows?.[r]?.[c]);
            const t = String(mapping.type || 'TEXT').toUpperCase();
            if (t === 'NUMBER') {
                const s = String(val ?? '').trim().replace(/,/g, '');
                if (s !== '' && !/^-?\d+(\.\d+)?$/.test(s)) return true;
            }
            if (t === 'DATE') {
                const s = String(val ?? '').trim();
                if (s !== '' && !formatDateAsDdMmmYyyy(s)) return true;
            }
        }
        return false;
    }, [editableCells, excelData, normalizedMapping, initialData]);

    const buildInvoicePayload = () => {
        const sourceRows = (excelData && excelData.length) ? excelData : (rawParsedRows || []);
        const rows = sourceRows.map((row, r) =>
            (Array.isArray(row) ? row : []).map((cell, c) => resolveDisplayCellValue(r, c, cell))
        );
        const totalRows = sourceRows.length;
        const { bodyStartRow, templateBodyRowCount } = getBodyFooterFromMapping(
            normalizedMapping.mappings,
            normalizedMapping.metadata,
            totalRows
        );
        const bodyDataStartRow = bodyStartRow + templateBodyRowCount - 1;
        const requiredBodyRows = Math.max(1, (initialData?.detailData?.length ?? 0) || 1);
        const bodyDataEndRow = bodyDataStartRow + requiredBodyRows - 1;
        const bodyEndRow = bodyStartRow + templateBodyRowCount - 1;
        const templateRowCount = (rawParsedRows && rawParsedRows.length) ? rawParsedRows.length : totalRows;

        const decodeCell = (excelCell) => {
            if (typeof excelCell !== 'string') return null;
            try {
                const d = XLSX.utils.decode_cell(excelCell);
                return { r: d.r, c: d.c };
            } catch (_) {
                return null;
            }
        };

        const mappedFields = [];
        normalizedMapping.mappings.forEach((m) => {
            if (!m) return;
            if (m.section === 'BODY') {
                let targetCol = Number.isInteger(m.columnIndex) ? m.columnIndex : null;
                if (targetCol == null && typeof m.excelCell === 'string') {
                    const d = decodeCell(m.excelCell);
                    targetCol = d ? d.c : null;
                }
                if (targetCol == null) return;
                for (let r = bodyDataStartRow; r <= bodyDataEndRow; r++) {
                    const val = resolveDisplayCellValue(r, targetCol, sourceRows?.[r]?.[targetCol]);
                    mappedFields.push({
                        cell: XLSX.utils.encode_cell({ r, c: targetCol }),
                        rowIndex: r,
                        colIndex: targetCol,
                        section: m.section,
                        source: m.source,
                        fieldKey: m.fieldKey,
                        attribute: m.attribute,
                        type: m.type,
                        readOnly: !!m.readOnly,
                        value: val,
                    });
                }
            } else {
                if (typeof m.excelCell === 'string') {
                    const d = decodeCell(m.excelCell);
                    if (!d) return;
                    const val = resolveDisplayCellValue(d.r, d.c, sourceRows?.[d.r]?.[d.c]);
                    mappedFields.push({
                        cell: m.excelCell,
                        rowIndex: d.r,
                        colIndex: d.c,
                        section: m.section,
                        source: m.source,
                        fieldKey: m.fieldKey,
                        attribute: m.attribute,
                        type: m.type,
                        readOnly: !!m.readOnly,
                        value: val,
                    });
                }
            }
        });

        return {
            formData,
            mappedFields,
            sheet: {
                rows,
                merges: mergeInfo,
                meta: {
                    bodyEndRow,
                    templateRowCount,
                },
            },
            meta: {
                generatedAt: new Date().toISOString(),
                rowCount: sourceRows.length,
                colCount: sourceRows[0]?.length || 0,
                bodyDataStartRow,
                bodyDataEndRow,
            },
        };
    };

    const handlePrint = () => {
        onPrint?.();
    };

    const handleDownloadPDF = () => {
        const tableEl = previewTableRef.current;
        if (!tableEl) return;

        // Print the same visual block (table + anchored images), not table alone.
        const visualBlockEl = tableEl.parentElement || tableEl;
        const printNode = visualBlockEl.cloneNode(true);
        if (printNode && printNode.style) {
            // Ensure PDF uses 1:1 sheet size, independent of current UI zoom.
            printNode.style.zoom = '1';
            printNode.style.transform = 'none';
            printNode.style.transformOrigin = 'top left';
            printNode.style.margin = '0';
        }
        // Keep print preview identical to pre-edit mode:
        // remove UI-only cues and convert editable inputs to static text.
        if (printNode && printNode.querySelectorAll) {
            // Replace editable inputs with plain text so print layout matches historical preview.
            const inputs = printNode.querySelectorAll('input');
            inputs.forEach((input) => {
                const span = document.createElement('span');
                span.textContent = String(input.value ?? '');
                const parent = input.parentNode;
                if (parent) parent.replaceChild(span, input);
            });

            // Normalize any nested zoom wrappers copied from on-screen preview container.
            const zoomed = printNode.querySelectorAll('[style*="zoom"]');
            zoomed.forEach((el) => {
                if (el && el.style) el.style.zoom = '1';
            });

            const cells = printNode.querySelectorAll('td, th');
            cells.forEach((cell) => {
                const style = cell.style || {};
                const bg = String(style.backgroundColor || '').toLowerCase();
                const border = String(style.border || '').toLowerCase();
                const boxShadow = String(style.boxShadow || '').toLowerCase();
                const isEditableBlue = bg.includes('227, 242, 253') || bg.includes('#e3f2fd');
                const isValidationRed = bg.includes('255, 235, 238') || bg.includes('#ffebee') || boxShadow.includes('d32f2f');
                if (isEditableBlue || isValidationRed) {
                    style.backgroundColor = '#fff';
                }
                if (border.includes('1976d2') || border.includes('25, 118, 210')) {
                    style.border = '';
                }
                if (boxShadow) {
                    style.boxShadow = 'none';
                }
                style.outline = 'none';
            });
        }
        const tableHTML = printNode?.outerHTML || tableEl.outerHTML;
        const printTitle = `Invoice_${new Date().toISOString().slice(0, 10)}`;
        const htmlContent = `<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>${printTitle}</title>
  <style>
    * { box-sizing: border-box; }
    body { margin: 0; padding: 16px; background: #fff; font-family: Arial, sans-serif; }
    table { border-collapse: collapse; width: auto; }
    img { max-width: none; }
    /* Remove editable-cell indicators (blue highlight) for PDF */
    td[style*="1976d2"], td[style*="#1976d2"], td[style*="25, 118, 210"],
    th[style*="1976d2"], th[style*="#1976d2"], th[style*="25, 118, 210"] {
      background-color: #fff !important;
      outline: none !important;
      box-shadow: none !important;
    }
    table input {
      border: none !important;
      outline: none !important;
      box-shadow: none !important;
      background: transparent !important;
    }
    @media print {
      body { padding: 8px; }
      * { -webkit-print-color-adjust: exact; print-color-adjust: exact; }
      table { page-break-inside: auto; }
      tr { page-break-inside: avoid; page-break-after: auto; }
      td[style*="1976d2"], td[style*="#1976d2"], td[style*="25, 118, 210"],
      th[style*="1976d2"], th[style*="#1976d2"], th[style*="25, 118, 210"] {
        background-color: #fff !important;
        outline: none !important;
        box-shadow: none !important;
      }
    }
  </style>
</head>
<body>${tableHTML}</body>
</html>`;

        const w = window.open('', '_blank');
        if (!w) {
            alert('Please allow popups to download PDF, or use Ctrl+P / Cmd+P to print this page.');
            return;
        }
        w.document.write(htmlContent);
        w.document.close();
        w.focus();
        setTimeout(() => {
            w.print();
            w.onafterprint = () => w.close();
        }, 400);
    };

    /**
     * Build a 2D array from the visible HTML table (respects rowspan/colspan).
     * Exports exactly what the user sees, with no dependency on React state.
     */
    const getGridFromTable = (tableEl) => {
        if (!tableEl || !tableEl.querySelectorAll) return null;
        const trs = Array.from(tableEl.querySelectorAll('tbody tr'));
        if (!trs.length) return null;

        const grid = [];
        const occupied = [];

        for (let r = 0; r < trs.length; r++) {
            if (!grid[r]) grid[r] = [];
            if (!occupied[r]) occupied[r] = [];
            const cells = trs[r].querySelectorAll('td, th');
            let c = 0;
            for (const cell of cells) {
                while (occupied[r][c]) c++;
                const rowspan = Math.max(1, parseInt(cell.getAttribute('rowspan'), 10) || 1);
                const colspan = Math.max(1, parseInt(cell.getAttribute('colspan'), 10) || 1);
                const input = cell.querySelector('input');
                const text = input ? String(input.value || '') : (cell.textContent || '').trim();
                grid[r][c] = text;
                for (let rr = 0; rr < rowspan; rr++) {
                    if (!occupied[r + rr]) occupied[r + rr] = [];
                    for (let cc = 0; cc < colspan; cc++) occupied[r + rr][c + cc] = true;
                }
                c += colspan;
            }
        }

        const maxCols = grid.length ? Math.max(...grid.map((row) => row.length)) : 0;
        return grid.map((row) => {
            const out = [];
            for (let c = 0; c < maxCols; c++) out.push(row[c] !== undefined ? row[c] : '');
            return out;
        });
    };

    const downloadExcel = async () => {
        setExcelDownloading(true);
        try {
            const fileName = `Invoice_${new Date().toISOString().slice(0, 10)}.xlsx`;

            // Build full grid from state (excelData) so inserted body rows are always included
            const invoicePayload = buildInvoicePayload();
            let rows = Array.isArray(invoicePayload?.sheet?.rows) ? invoicePayload.sheet.rows : [];
            let merges = Array.isArray(invoicePayload?.sheet?.merges) && invoicePayload.sheet.merges.length
                ? invoicePayload.sheet.merges
                : (Array.isArray(mergeInfo) ? mergeInfo : []);

            // Fallback: use saved invoice data from initialData when state-derived rows are empty (e.g. trigger download before state flush)
            if (!rows.length && initialData?.savedInvoiceData?.sheet?.rows?.length) {
                rows = initialData.savedInvoiceData.sheet.rows;
                merges = Array.isArray(initialData.savedInvoiceData.sheet?.merges) ? initialData.savedInvoiceData.sheet.merges : merges;
            }
            if (!rows.length) {
                const tableEl = previewTableRef.current;
                rows = getGridFromTable(tableEl) || [];
            }
            if (!rows.length) return;

            const maxCols = rows.length ? Math.max(...rows.map((row) => (Array.isArray(row) ? row.length : 0))) : 0;
            const fullGrid = rows.map((row) => {
                const arr = Array.isArray(row) ? [...row] : [];
                while (arr.length < maxCols) arr.push('');
                return arr.map((v) => {
                    if (v === null || v === undefined) return '';
                    if (typeof v === 'number' && Number.isFinite(v)) return v;
                    if (typeof v === 'object' && v !== null && !Array.isArray(v) && 'value' in v) return v.value ?? '';
                    const s = String(v).trim();
                    if (/^-?\d+(\.\d+)?$/.test(s) && s.replace(/[-.]/g, '').length <= 11) return Number(s);
                    return s;
                });
            });
            let sheetMeta = invoicePayload?.sheet?.meta;
            if (!sheetMeta && initialData?.savedInvoiceData?.sheet?.meta) {
                sheetMeta = initialData.savedInvoiceData.sheet.meta;
            }
            const payload = {
                sheet: {
                    rows: fullGrid,
                    merges,
                    meta: sheetMeta ? { bodyEndRow: sheetMeta.bodyEndRow, templateRowCount: sheetMeta.templateRowCount } : undefined,
                },
            };

            // Prefer backend styled Excel (exact template styling) when template and API are available
            if (templateData && typeof onDownloadExcel === 'function') {
                try {
                    const result = await onDownloadExcel(payload);
                    if (result?.success) return;
                } catch (_) { /* fall through to fallback */ }
            }

            try {
                const ws = XLSX.utils.aoa_to_sheet(fullGrid);
                if (merges.length) {
                    ws['!merges'] = merges.map((m) => ({
                        s: { r: m.startRow, c: m.startCol },
                        e: { r: m.endRow, c: m.endCol },
                    }));
                }
                const wb = XLSX.utils.book_new();
                XLSX.utils.book_append_sheet(wb, ws, 'Invoice');
                XLSX.writeFile(wb, fileName);
            } catch (e) {
                console.error('Download Excel failed:', e);
            }
        } finally {
            setExcelDownloading(false);
        }
    };

    /** Same grid payload used by the styled export APIs. */
    const buildStyledPdfExportPayload = () => {
        const invoicePayload = buildInvoicePayload();
        let rows = Array.isArray(invoicePayload?.sheet?.rows) ? invoicePayload.sheet.rows : [];
        let merges = Array.isArray(invoicePayload?.sheet?.merges) && invoicePayload.sheet.merges.length
            ? invoicePayload.sheet.merges
            : (Array.isArray(mergeInfo) ? mergeInfo : []);

        if (!rows.length && initialData?.savedInvoiceData?.sheet?.rows?.length) {
            rows = initialData.savedInvoiceData.sheet.rows;
            merges = Array.isArray(initialData.savedInvoiceData.sheet?.merges) ? initialData.savedInvoiceData.sheet.merges : merges;
        }
        if (!rows.length) {
            const tableEl = previewTableRef.current;
            rows = getGridFromTable(tableEl) || [];
        }
        if (!rows.length) return null;

        const maxCols = rows.length ? Math.max(...rows.map((row) => (Array.isArray(row) ? row.length : 0))) : 0;
        const fullGrid = rows.map((row) => {
            const arr = Array.isArray(row) ? [...row] : [];
            while (arr.length < maxCols) arr.push('');
            return arr.map((v) => {
                if (v === null || v === undefined) return '';
                if (typeof v === 'number' && Number.isFinite(v)) return v;
                if (typeof v === 'object' && v !== null && !Array.isArray(v) && 'value' in v) return v.value ?? '';
                const s = String(v).trim();
                if (/^-?\d+(\.\d+)?$/.test(s) && s.replace(/[-.]/g, '').length <= 11) return Number(s);
                return s;
            });
        });
        let sheetMeta = invoicePayload?.sheet?.meta;
        if (!sheetMeta && initialData?.savedInvoiceData?.sheet?.meta) {
            sheetMeta = initialData.savedInvoiceData.sheet.meta;
        }
        return {
            sheet: {
                rows: fullGrid,
                merges,
                meta: sheetMeta ? { bodyEndRow: sheetMeta.bodyEndRow, templateRowCount: sheetMeta.templateRowCount } : undefined,
            },
        };
    };

    /** Keep PDF export on the browser print flow for this app. */
    const downloadPdf = async () => {
        setPdfDownloading(true);
        try {
            const payload = buildStyledPdfExportPayload();
            if (!payload) return;
            handleDownloadPDF();
        } finally {
            setPdfDownloading(false);
        }
    };

    useImperativeHandle(ref, () => ({ downloadExcel }), [excelData, mergeInfo, computedExcelValues]);

    const clampZoom = (z) => Math.max(0.5, Math.min(2, z));
    const changeZoomBy = (delta) => setTableZoom((prev) => clampZoom(Number((prev + delta).toFixed(2))));
    const fitTableToWidth = () => {
        const viewport = previewViewportRef.current;
        const table = previewTableRef.current;
        if (!viewport || !table) return;
        const baseWidth = table.scrollWidth || table.offsetWidth || 0;
        if (!baseWidth) return;
        const viewportWidth = viewport.clientWidth || 0;
        if (!viewportWidth) return;
        const target = clampZoom((viewportWidth - 8) / baseWidth);
        setTableZoom(target);
        // Keep fit mode aligned from left and remove any stale horizontal offset.
        requestAnimationFrame(() => {
            if (previewViewportRef.current) previewViewportRef.current.scrollLeft = 0;
        });
    };

    useEffect(() => {
        const tableEl = previewTableRef.current;
        const rows = (excelData && excelData.length) ? excelData : rawParsedRows;
        if (!tableEl || !rows?.length) return;
        const fallbackColWidth = 110;
        const fallbackRowHeight = 20;
        const maxCols = Math.max(...rows.map((r) => (Array.isArray(r) ? r.length : 0)), 0);
        const expectedW = Array.from({ length: maxCols }, (_, c) => (colWidths?.[c] || fallbackColWidth)).reduce((a, b) => a + b, 0);
        const expectedH = Array.from({ length: rows.length }, (_, r) => (rowHeights?.[r] || fallbackRowHeight)).reduce((a, b) => a + b, 0);
        if (!expectedW || !expectedH) return;
        // Use layout size, not transformed visual size, to avoid double-scaling images.
        const baseW = tableEl.offsetWidth || tableEl.scrollWidth || 0;
        const baseH = tableEl.offsetHeight || tableEl.scrollHeight || 0;
        const sx = baseW > 0 ? baseW / expectedW : 1;
        const sy = baseH > 0 ? baseH / expectedH : 1;
        setImageCalib({
            sx: Number.isFinite(sx) && sx > 0 ? sx : 1,
            sy: Number.isFinite(sy) && sy > 0 ? sy : 1,
        });
    }, [excelData, rawParsedRows, colWidths, rowHeights, mergeInfo]);

    useEffect(() => {
        const tableEl = previewTableRef.current;
        if (!tableEl || !anchoredImages?.length) return;
        const first = anchoredImages.find((img) => Number.isInteger(img?.anchor?.row) && Number.isInteger(img?.anchor?.col));
        if (!first) return;
        const cellRef = XLSX.utils.encode_cell({ r: first.anchor.row, c: first.anchor.col });
        const cellEl = tableEl.querySelector(`td[data-cell="${cellRef}"]`);
        if (!cellEl) return;
        const expectedX = (first.x * imageCalib.sx);
        const expectedY = (first.y * imageCalib.sy);
        const actualX = cellEl.offsetLeft + (first.anchor.offX || 0) * imageCalib.sx;
        const actualY = cellEl.offsetTop + (first.anchor.offY || 0) * imageCalib.sy;
        const dx = actualX - expectedX;
        const dy = actualY - expectedY;
        setImageOffset({
            dx: Number.isFinite(dx) ? dx : 0,
            dy: Number.isFinite(dy) ? dy : 0,
        });
    }, [anchoredImages, imageCalib, excelData, mergeInfo]);

    if (isLoading) {
        return (
            <Box sx={{ p: 3, textAlign: 'center' }}>
                <Typography>Loading invoice template...</Typography>
            </Box>
        );
    }

    return (
        <div className="employee-fill-mode" style={{ display: 'flex', flexDirection: 'column', height: '100%', minHeight: 0 }}>
            <Typography variant="h6" fontWeight={600} sx={{ flexShrink: 0, mb: 1.5 }}>
                Fill the Invoice Data
            </Typography>
            <Box sx={{ display: 'flex', alignItems: 'center', justifyContent: 'space-between', mb: 1, px: 0.5, gap: 1, flexWrap: 'wrap' }}>
                <Typography variant="caption" sx={{ color: 'text.secondary', fontWeight: 600 }}>
                    Zoom
                </Typography>
                <Stack direction="row" spacing={1} alignItems="center" sx={{ minWidth: 260 }}>
                    <IconButton size="small" onClick={() => changeZoomBy(-0.1)} aria-label="Zoom out">
                        <ZoomOutIcon fontSize="small" />
                    </IconButton>
                    <Slider
                        size="small"
                        min={50}
                        max={200}
                        step={10}
                        value={Math.round(tableZoom * 100)}
                        onChange={(_, value) => setTableZoom(clampZoom(Number(value) / 100))}
                        sx={{ width: 140 }}
                    />
                    <IconButton size="small" onClick={() => changeZoomBy(0.1)} aria-label="Zoom in">
                        <ZoomInIcon fontSize="small" />
                    </IconButton>
                    <Button size="small" variant="outlined" onClick={() => setTableZoom(1)}>100%</Button>
                    <Button size="small" variant="outlined" onClick={fitTableToWidth}>Fit</Button>
                    <Typography variant="caption" sx={{ minWidth: 48, textAlign: 'right' }}>
                        {Math.round(tableZoom * 100)}%
                    </Typography>
                </Stack>
            </Box>
            <div
                ref={previewViewportRef}
                className="excel-table-wrapper"
                style={{
                    overflow: 'auto',
                    maxHeight: '85vh',
                    minHeight: 400,
                    border: '1px solid #a0a0a0',
                    background: '#fff',
                    position: 'relative',
                    flex: '1 1 auto',
                    width: '100%',
                }}
            >
                {/* max-content + margin auto: center narrow sheets; wide sheets scroll from the left */}
                <div
                    style={{
                        width: 'max-content',
                        margin: '0 auto',
                        position: 'relative',
                        zoom: tableZoom,
                        transformOrigin: 'top left',
                    }}
                >
                    <table ref={previewTableRef} style={{ borderCollapse: 'collapse' }}>
                        <tbody>
                            {(() => {
                                const mergeMap = new Map();
                                const covered = new Set();

                                mergeInfo.forEach((m) => {
                                    mergeMap.set(`${m.startRow}-${m.startCol}`, {
                                        rowspan: m.endRow - m.startRow + 1,
                                        colspan: m.endCol - m.startCol + 1,
                                        startRow: m.startRow,
                                        startCol: m.startCol,
                                        endRow: m.endRow,
                                        endCol: m.endCol,
                                    });
                                    for (let rr = m.startRow; rr <= m.endRow; rr++) {
                                        for (let cc = m.startCol; cc <= m.endCol; cc++) {
                                            if (rr === m.startRow && cc === m.startCol) continue;
                                            covered.add(`${rr}-${cc}`);
                                        }
                                    }
                                });

                                const borderScore = (b) => {
                                    if (!b || typeof b !== 'string') return 0;
                                    const s = b.toLowerCase();
                                    let score = 1;
                                    if (s.includes('double')) score += 40;
                                    else if (s.includes('dashed') || s.includes('dotted')) score += 20;
                                    else score += 25;
                                    if (s.includes(' 3px ')) score += 30;
                                    else if (s.includes(' 2px ')) score += 20;
                                    else if (s.includes(' 1px ')) score += 10;
                                    if (!s.includes('#000000') && !s.includes(' black')) score += 5;
                                    return score;
                                };
                                const pickBestBorder = (arr) => arr.filter(Boolean).sort((a, b) => borderScore(b) - borderScore(a))[0] || null;
                                const getMergedOuterBorder = (m) => {
                                    if (!m) return null;
                                    const out = {};
                                    const anchor = cellStyles?.[m.startRow]?.[m.startCol] || null;
                                    const tops = [];
                                    const bottoms = [];
                                    for (let cc = m.startCol; cc <= m.endCol; cc++) {
                                        tops.push(cellStyles?.[m.startRow]?.[cc]?.borderTop);
                                        bottoms.push(cellStyles?.[m.endRow]?.[cc]?.borderBottom);
                                    }
                                    tops.push(anchor?.borderTop, anchor?.border);
                                    bottoms.push(anchor?.borderBottom, anchor?.border);
                                    out.borderTop = pickBestBorder(tops);
                                    out.borderBottom = pickBestBorder(bottoms);
                                    const lefts = [];
                                    const rights = [];
                                    for (let rr = m.startRow; rr <= m.endRow; rr++) {
                                        lefts.push(cellStyles?.[rr]?.[m.startCol]?.borderLeft);
                                        rights.push(cellStyles?.[rr]?.[m.endCol]?.borderRight);
                                    }
                                    lefts.push(anchor?.borderLeft, anchor?.border);
                                    rights.push(anchor?.borderRight, anchor?.border);
                                    out.borderLeft = pickBestBorder(lefts);
                                    out.borderRight = pickBestBorder(rights);
                                    return out;
                                };

                                const fallbackColWidth = 110;
                                const fallbackRowHeight = 20;
                                const tableRows = (excelData && excelData.length) ? excelData : rawParsedRows;

                                return tableRows.map((row, r) => (
                                    <tr key={r} style={{ height: rowHeights?.[r] || fallbackRowHeight }}>
                                        {row.map((cell, c) => {
                                            if (covered.has(`${r}-${c}`)) return null;

                                            const merge = mergeMap.get(`${r}-${c}`);
                                            const css = cellStyles?.[r]?.[c] || null;
                                            const isEditable = !triggerDownloadOnLoad && editableCells.has(`${r}-${c}`);
                                            const showEditHighlight = isEditable && !showSaveSuccessDialog;
                                            const inputMapping = isEditable ? getMappingForCell(r, c) : null;
                                            const isFormula = typeof cell === 'object' && cell && cell.formula;
                                            let displayValue = getCellPlainValue(cell);
                                            if (isFormula) {
                                                const formulaStr = cell.formula;
                                                const computed = computedExcelValues?.[r]?.[c];
                                                const hasComputed = computedExcelValues
                                                    && computed !== undefined
                                                    && computed !== null
                                                    && computed !== ''
                                                    && !isFormulaError(computed);

                                                if (hasComputed) {
                                                    displayValue = formatComputedValue(computed);
                                                } else {
                                                    // Use formula engine output only; when unavailable show engine error/text.
                                                    if (computed && typeof computed === 'object' && computed.value) {
                                                        displayValue = String(computed.value);
                                                    } else if (typeof computed === 'string' && computed.startsWith('#')) {
                                                        displayValue = computed;
                                                    } else {
                                                        displayValue = getCellPlainValue(cell);
                                                    }
                                                }
                                            }

                                            const style = {
                                                width: colWidths?.[c] || fallbackColWidth,
                                                maxWidth: colWidths?.[c] || fallbackColWidth,
                                                minWidth: colWidths?.[c] || fallbackColWidth,
                                                padding: '3px 8px',
                                                boxSizing: 'border-box',
                                                verticalAlign: 'middle',
                                                fontSize: '12px',
                                                ...(css && typeof css === 'object' ? css : {})
                                            };
                                            const hasTemplateValidationError = invalidValidationCells.has(`${r}-${c}`);
                                            if (hasTemplateValidationError) {
                                                style.backgroundColor = '#ffebee';
                                                style.boxShadow = 'inset 0 0 0 1px #d32f2f';
                                            }
                                            if (merge) {
                                                const mergedOuter = getMergedOuterBorder(merge);
                                                if (mergedOuter?.borderTop && !style.borderTop) style.borderTop = mergedOuter.borderTop;
                                                if (mergedOuter?.borderBottom && !style.borderBottom) style.borderBottom = mergedOuter.borderBottom;
                                                if (mergedOuter?.borderLeft && !style.borderLeft) style.borderLeft = mergedOuter.borderLeft;
                                                if (mergedOuter?.borderRight && !style.borderRight) style.borderRight = mergedOuter.borderRight;
                                            }

                                            if (showEditHighlight) {
                                                // Make editable cells look like admin mapping: light blue fill, blue border
                                                style.backgroundColor = hasTemplateValidationError ? '#ffebee' : '#e3f2fd';
                                                style.outline = hasTemplateValidationError
                                                    ? '1px solid #d32f2f'
                                                    : '2px solid #1976d2';
                                                style.outlineOffset = '-2px';
                                                style.cursor = 'pointer';
                                            }

                                            return (
                                                <td
                                                    key={`${r}-${c}`}
                                                    rowSpan={merge?.rowspan}
                                                    colSpan={merge?.colspan}
                                                    style={style}
                                                    data-cell={XLSX.utils.encode_cell({ r, c })}
                                                >
                                                    {isEditable ? (
                                                        <input
                                                            type="text"
                                                            value={displayValue ?? ""}
                                                            onChange={(e) => handleCellChange(r, c, e.target.value, false)}
                                                            onBlur={(e) => handleCellChange(r, c, e.target.value, true)}
                                                            onFocus={(e) => e.target.select()}
                                                            inputMode={String(inputMapping?.type || '').toUpperCase() === 'NUMBER' ? 'decimal' : undefined}
                                                            placeholder={String(inputMapping?.type || '').toUpperCase() === 'DATE' ? 'DD-MMM-YYYY' : undefined}
                                                            style={{
                                                                width: "100%",
                                                                border: "none",
                                                                background: "transparent",
                                                                padding: 0,
                                                                margin: 0,
                                                                outline: "none",
                                                                fontSize: "inherit",
                                                                fontFamily: "inherit",
                                                                color: "inherit",
                                                                textAlign: "inherit",
                                                                cursor: "pointer",
                                                            }}
                                                        />
                                                    ) : (
                                                        <span title={isFormula ? cell.formula : undefined}>
                                                            {displayValue !== undefined && displayValue !== null && displayValue !== '' ? displayValue : (displayValue === 0 ? 0 : '')}
                                                        </span>
                                                    )}
                                                </td>
                                            );
                                        })}
                                    </tr>
                                ));
                            })()}
                        </tbody>
                    </table>
                    {anchoredImages?.length > 0 ? (
                        <div style={{ position: 'absolute', inset: 0, pointerEvents: 'none', zIndex: 3 }}>
                            {anchoredImages.map((img) => (
                                <img
                                    key={img.id}
                                    src={img.src}
                                    alt=""
                                    style={{
                                        position: 'absolute',
                                        left: `${(img.x * imageCalib.sx) + imageOffset.dx}px`,
                                        top: `${(img.y * imageCalib.sy) + imageOffset.dy}px`,
                                        width: `${img.width * imageCalib.sx}px`,
                                        height: `${img.height * imageCalib.sy}px`,
                                        objectFit: 'fill',
                                        userSelect: 'none',
                                    }}
                                />
                            ))}
                        </div>
                    ) : null}
                </div>
            </div>

            <Box sx={{ flexShrink: 0, pt: 1.5, pb: 1, display: 'flex', justifyContent: 'center', gap: 1 }}>
                {onBack && (
                    <Button variant="outlined" startIcon={<ArrowBackIcon />} onClick={onBack}>
                        Back
                    </Button>
                )}
                <Button
                    variant="contained"
                    color="primary"
                    startIcon={<SaveIcon />}
                    onClick={handleSave}
                    disabled={
                        invalidValidationCells.size > 0 ||
                        hasBlockingTypeErrors
                    }
                >
                    Save
                </Button>
            </Box>
            <Snackbar
                open={Boolean(validationError)}
                autoHideDuration={8000}
                onClose={() => setValidationError('')}
                anchorOrigin={{ vertical: 'bottom', horizontal: 'center' }}
                sx={{ zIndex: 1600 }}
            >
                <Alert
                    onClose={() => setValidationError('')}
                    severity="error"
                    variant="filled"
                    elevation={6}
                    sx={{ alignItems: 'center', minWidth: { xs: '100%', sm: 360 } }}
                >
                    {validationError}
                </Alert>
            </Snackbar>

            {/* Loader dialog – only dialog used for save flow (zIndex so it appears above overlay) */}
            <Dialog open={showLoader} disableEscapeKeyDown aria-labelledby="loader-dialog" sx={{ zIndex: 1500 }}>
                <DialogContent>
                    <Stack spacing={1} justifyContent="center" alignItems="center" direction="column" className="ms-3 me-3 mt-2">
                        <CircularProgress disableShrink />
                        <div><b>{loaderMessage || 'Processing...'}</b></div>
                    </Stack>
                </DialogContent>
            </Dialog>

            {/* Message dialog (error) */}
            <Dialog open={showMessageDialog} onClose={() => setShowMessageDialog(false)} aria-labelledby="message-dialog" sx={{ zIndex: 1500 }}>
                <DialogContent style={{ backgroundColor: 'white' }}>
                    <Stack spacing={1} className="m-1" direction="row" justifyContent="center">
                        {messageDialog.type === 'success' ? <CheckCircleOutlineIcon color="success" /> : <ErrorOutlineIcon color="error" />}
                    </Stack>
                    <Stack spacing={1} className="m-1" direction="row" justifyContent="center">
                        <b>{messageDialog.message}</b>
                    </Stack>
                </DialogContent>
                <DialogActions style={{ backgroundColor: 'white' }}>
                    <Button variant="contained" color={messageDialog.type} onClick={() => setShowMessageDialog(false)}>OK</Button>
                </DialogActions>
            </Dialog>

            {showSaveSuccessDialog && (
                <Dialog open={showSaveSuccessDialog} onClose={() => { setShowSaveSuccessDialog(false); onBack?.(); }} maxWidth="sm" fullWidth sx={{ zIndex: 1500 }}>
                    <DialogTitle sx={{ textAlign: 'center' }}>{triggerDownloadOnLoad ? 'Download or Print Bill' : 'Invoice saved successfully'}</DialogTitle>
                    <DialogActions sx={{ justifyContent: 'center', flexWrap: 'wrap', gap: 1, p: 2 }}>
                        <Button variant="contained" color="success" startIcon={excelDownloading ? <CircularProgress size={20} color="inherit" /> : <DownloadIcon />} onClick={() => downloadExcel()} disabled={excelDownloading || pdfDownloading}>
                            {excelDownloading ? 'Downloading...' : 'Download Excel'}
                        </Button>
                        {/* <Button variant="contained" color="error" startIcon={pdfDownloading ? <CircularProgress size={20} color="inherit" /> : <PictureAsPdfIcon />} onClick={() => downloadPdf()} disabled={excelDownloading || pdfDownloading}>
                            {pdfDownloading ? 'Downloading...' : 'Download PDF'}
                        </Button> */}
                        <Button variant="contained" color="primary" startIcon={<PrintIcon />} onClick={() => handleDownloadPDF()} disabled={excelDownloading || pdfDownloading} title="Opens the invoice in a new tab; use Print → Save as PDF">
                            Print PDF
                        </Button>
                        <Button variant="contained" color="error" onClick={() => {
                            setShowSaveSuccessDialog(false);
                            onBack?.();
                        }}>
                            Close
                        </Button>
                    </DialogActions>
                </Dialog>
            )}
        </div>
    );
});

export default EmployeeFillMode;