import React, { useState, useEffect } from 'react';
import { Box, Paper, Typography, Button, Alert, TextField, Chip, Autocomplete, Checkbox, FormControlLabel, Tooltip, CircularProgress, Stack, Divider, ToggleButtonGroup, ToggleButton, IconButton, Slider } from '@mui/material';
import UploadFileIcon from '@mui/icons-material/UploadFile';
import DescriptionIcon from '@mui/icons-material/Description';
import SaveIcon from '@mui/icons-material/Save';
import FileDownloadIcon from '@mui/icons-material/FileDownload';
import DeleteOutlineIcon from '@mui/icons-material/DeleteOutline';
import CheckCircleOutlineIcon from '@mui/icons-material/CheckCircleOutline';
import StorageIcon from '@mui/icons-material/Storage';
import PersonOutlineIcon from '@mui/icons-material/PersonOutline';
import ViewListIcon from '@mui/icons-material/ViewList';
import ZoomInIcon from '@mui/icons-material/ZoomIn';
import ZoomOutIcon from '@mui/icons-material/ZoomOut';
import EditNoteIcon from '@mui/icons-material/EditNote';
import * as XLSX from 'xlsx';
import { GetCustomizeBillFormatGetMultipleValues } from '../../../../../group-service/apis/customizebillformat';
import { MappingService } from './MappingService.js';
import { parseExcelBufferWithExcelJs } from './excelRenderUtils';

/** Decode 0-based row from mapping excelCell (string "A8" or object { start: "A8", end: "B8" }). */
function decodeRowFromMapping(mapping) {
    const cell = mapping?.excelCell;
    if (!cell) return null;
    if (typeof cell === 'string') {
        try {
            return XLSX.utils.decode_cell(cell).r;
        } catch (_) {
            const rowPart = cell.replace(/\D/g, '');
            return rowPart ? parseInt(rowPart, 10) - 1 : null;
        }
    }
    if (typeof cell === 'object' && cell.start) {
        const rowPart = String(cell.start).replace(/\D/g, '');
        return rowPart ? parseInt(rowPart, 10) - 1 : null;
    }
    return null;
}

/** Build layout metadata from mappings and sheet size (no hardcoded row indices). */
function buildLayoutMetadata(mappings, totalRows, totalCols) {
    const bodyRows = mappings.filter((m) => m.section === 'BODY').map(decodeRowFromMapping).filter((r) => r != null);
    const footerRows = mappings.filter((m) => m.section === 'FOOTER').map(decodeRowFromMapping).filter((r) => r != null);
    const bodyStartRow = bodyRows.length > 0 ? Math.min(...bodyRows) : undefined;
    const footerStartRow = footerRows.length > 0 ? Math.max(...footerRows) : undefined;
    const templateBodyRowCount =
        bodyRows.length > 0 ? Math.max(...bodyRows) - Math.min(...bodyRows) + 1 : 1;
    return {
        bodyStartRow: bodyStartRow ?? 0,
        footerStartRow: footerStartRow ?? Math.max(0, totalRows - 1),
        templateBodyRowCount,
    };
}

export default function ExcelMappingComponent({
    customerCode: selectedCustomerCode,
    userCode: selectedUserCode,
    branchCode: selectedBranchCode,
    divCode,
    setFormvalueObj,
}) {
    const [uploadedFile, setUploadedFile] = useState(null);
    const [excelData, setExcelData] = useState([]);
    const [selectedCell, setSelectedCell] = useState(null);
    const [mappings, setMappings] = useState([]);
    const [mappingMode, setMappingMode] = useState('HEADER');
    const [draft, setDraft] = useState({ source: '', fieldKey: '', attribute: '', readOnly: false, userDataType: 'TEXT' });

    const USER_DATA_TYPES = [
        { value: 'TEXT', label: 'Text' },
        { value: 'NUMBER', label: 'Number' },
        { value: 'DATE', label: 'Date' }
    ];
    const [showMappingPanel, setShowMappingPanel] = useState(false);
    const [mergeInfo, setMergeInfo] = useState([]);
    const [cellStyles, setCellStyles] = useState([]);
    const [sheetDims, setSheetDims] = useState({ colWidths: [], rowHeights: [] });
    const [anchoredImages, setAnchoredImages] = useState([]);
    const [imageCalib, setImageCalib] = useState({ sx: 1, sy: 1 });
    const [imageOffset, setImageOffset] = useState({ dx: 0, dy: 0 });
    const [dbAttributes, setDbAttributes] = useState([]);
    const [loadingDbFields, setLoadingDbFields] = useState(false);
    const [dbFieldsError, setDbFieldsError] = useState(null);
    const [savingFormat, setSavingFormat] = useState(false);
    const [tableZoom, setTableZoom] = useState(1);
    const [existingFormat, setExistingFormat] = useState(null);
    const [isEditMode, setIsEditMode] = useState(false);
    const [loadingExisting, setLoadingExisting] = useState(false);
    const [cellValidations, setCellValidations] = useState([]);
    const [rawExcelData, setRawExcelData] = useState([]);
    const [validationRuleCount, setValidationRuleCount] = useState(0);
    const [validationCountBefore, setValidationCountBefore] = useState(0);
    const [fileReplaceNotice, setFileReplaceNotice] = useState(null);
    const replaceUploadInputRef = React.useRef(null);
    const previewTableRef = React.useRef(null);
    const previewViewportRef = React.useRef(null);

    const showLoader = (loadType) => {
        if (setFormvalueObj) {
            setFormvalueObj((pre) => ({ ...pre, loader: { ...pre?.loader, open: true, loadType: loadType || 'Processing...' } }));
        }
    };
    const hideLoader = () => {
        if (setFormvalueObj) {
            setFormvalueObj((pre) => ({ ...pre, loader: { ...pre?.loader, open: false, loadType: '' } }));
        }
    };
    const showError = (msg, type = 'error') => {
        if (setFormvalueObj) {
            setFormvalueObj((pre) => ({ ...pre, error: { ...pre?.error, Msg: msg, Type: type, errorOpen: true } }));
        }
    };
    const showCatchError = (err) => {
        if (setFormvalueObj) {
            setFormvalueObj((pre) => ({ ...pre, catchError: { ...pre?.catchError, msg: err, open: true } }));
        }
    };

    useEffect(() => {
        let cancelled = false;
        const fetchDbFields = async () => {
            setLoadingDbFields(true);
            setDbFieldsError(null);
            showLoader('Loading database fields...');
            try {
                const response = await GetCustomizeBillFormatGetMultipleValues(divCode);
                if (cancelled) return;

                const hasError = response?.data?.Error != null && response?.data?.Error?.Status_Message != null;
                if (hasError) {
                    const msg = response?.data?.Error?.Status_Message ?? 'API returned an error';
                    setDbAttributes([]);
                    setDbFieldsError(msg);
                    hideLoader();
                    showError(msg);
                    return;
                }

                const items = response?.data?.MultipleValuesDataItems;
                if (!Array.isArray(items) || items.length === 0) {
                    setDbAttributes([]);
                    hideLoader();
                    return;
                }

                const arr = items
                    .map((data) => {
                        const columnName = data?.Column_Name;
                        const columnValue = data?.Column_Value;
                        if (columnName == null || String(columnName).trim() === '' || columnValue == null) return null;
                        return {
                            label: String(columnName).replace(/_/g, ' ').replace(/\b\w/g, (l) => l.toUpperCase()),
                            value: columnValue
                        };
                    })
                    .filter(Boolean);
                setDbAttributes(arr);
                hideLoader();
            } catch (error) {
                if (!cancelled) {
                    setDbAttributes([]);
                    const msg = error?.message ?? 'Failed to load database fields';
                    setDbFieldsError(msg);
                    hideLoader();
                    showCatchError(error);
                }
            } finally {
                if (!cancelled) setLoadingDbFields(false);
            }
        };
        fetchDbFields();
        return () => { cancelled = true; };
    }, []);

    const clearEditor = () => {
        setUploadedFile(null);
        setExcelData([]);
        setMappings([]);
        setShowMappingPanel(false);
        setIsEditMode(false);
        setSelectedCell(null);
        setMergeInfo([]);
        setCellStyles([]);
        setSheetDims({ colWidths: [], rowHeights: [] });
        setAnchoredImages([]);
        setCellValidations([]);
        setRawExcelData([]);
        setValidationRuleCount(0);
        setValidationCountBefore(0);
        setFileReplaceNotice(null);
        setDraft({ source: '', fieldKey: '', attribute: '', readOnly: false, userDataType: 'TEXT' });
    };

    const applyParsedExcel = (parsed) => {
        const normalizedData = parsed.data;
        const stylesGrid = parsed.styles;

        if (!normalizedData.length || !normalizedData[0]?.length) {
            alert('Excel file is empty or invalid');
            setExcelData([['Empty File']]);
            setMergeInfo([]);
            setCellStyles([]);
            setCellValidations([]);
            setRawExcelData([]);
            setValidationRuleCount(0);
            return false;
        }
        const merges = parsed.merges || [];
        const colWidths = parsed.colWidths || [];
        const rowHeights = parsed.rowHeights || [];
        const images = parsed.images || [];
        const validations = parsed.validations || [];
        const ruleCount = MappingService.countValidationRules(validations);

        setExcelData(normalizedData);
        setRawExcelData(parsed.raw || []);
        setMergeInfo(merges);
        setCellStyles(stylesGrid);
        setSheetDims({ colWidths, rowHeights });
        setAnchoredImages(images);
        setCellValidations(validations);
        setValidationRuleCount(ruleCount);
        return true;
    };

    const downloadCurrentTemplate = () => {
        try {
            if (uploadedFile) {
                MappingService.downloadFile(uploadedFile);
                return;
            }
            if (existingFormat?.templateData) {
                const file = MappingService.base64ToFile(
                    existingFormat.templateData,
                    existingFormat.templateName || 'template.xlsx'
                );
                MappingService.downloadFile(file);
                return;
            }
            showError('No template file available to download.');
        } catch (err) {
            showCatchError(err);
        }
    };

    const refreshExistingFormat = async (custCode, brnCode) => {
        if (!custCode || !brnCode) {
            setExistingFormat(null);
            return;
        }
        setLoadingExisting(true);
        try {
            const result = await MappingService.getCustomerMapping(custCode, brnCode, divCode);
            if (result.success && result.data?.templateData) {
                setExistingFormat(result.data);
            } else {
                setExistingFormat(null);
            }
        } catch {
            setExistingFormat(null);
        } finally {
            setLoadingExisting(false);
        }
    };

    useEffect(() => {
        clearEditor();
        setExistingFormat(null);
        if (!selectedCustomerCode || !selectedBranchCode) return undefined;

        let cancelled = false;
        const checkExisting = async () => {
            setLoadingExisting(true);
            try {
                const result = await MappingService.getCustomerMapping(selectedCustomerCode, selectedBranchCode, divCode);
                if (cancelled) return;
                if (result.success && result.data?.templateData) {
                    setExistingFormat(result.data);
                } else {
                    setExistingFormat(null);
                }
            } catch {
                if (!cancelled) setExistingFormat(null);
            } finally {
                if (!cancelled) setLoadingExisting(false);
            }
        };
        checkExisting();
        return () => { cancelled = true; };
    }, [selectedCustomerCode, selectedBranchCode]);

    const loadExistingFormat = async () => {
        if (!existingFormat?.templateData) {
            showError('No active format found for this customer and branch.');
            return;
        }

        showLoader('Loading existing format...');
        try {
            const file = MappingService.base64ToFile(
                existingFormat.templateData,
                existingFormat.templateName || 'template.xlsx'
            );
            const arrayBuffer = await file.arrayBuffer();
            const parsed = await parseExcelBufferWithExcelJs(arrayBuffer);
            const ok = applyParsedExcel(parsed);
            if (!ok) {
                hideLoader();
                return;
            }

            const config = existingFormat.mappingConfig;
            const restoredMappings = Array.isArray(config?.mappings) ? config.mappings : [];
            const beforeCount = MappingService.countValidationRules(parsed.validations);
            setUploadedFile(file);
            setMappings(restoredMappings);
            setValidationCountBefore(beforeCount);
            setFileReplaceNotice(null);
            setIsEditMode(true);
            setShowMappingPanel(true);
            hideLoader();
        } catch (error) {
            hideLoader();
            showCatchError(error);
        }
    };

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

    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;
    };

    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 getMergeStartForCell = (row, col) =>
        mergeInfo.find(
            (m) =>
                row >= m.startRow &&
                row <= m.endRow &&
                col >= m.startCol &&
                col <= m.endCol
        );

    const getCellAddress = (row, col) => {
        return `${String.fromCharCode(65 + col)}${row + 1}`;
    };

    const isCellMapped = (r, c) => {
        const address = getCellAddress(r, c);
        return mappings.some(mapping => mapping.excelCell === address);
    };

    const highlightCell = (cellAddress) => {
        // Convert cell address like "B3" to row and col
        const col = cellAddress.charCodeAt(0) - 65; // A=0, B=1, etc.
        const row = parseInt(cellAddress.substring(1)) - 1; // 3 -> row 2 (0-indexed)

        setSelectedCell({ row, col, address: cellAddress });

        // Scroll to the cell (optional enhancement)
        const cellElement = document.querySelector(`[data-cell="${cellAddress}"]`);
        if (cellElement) {
            cellElement.scrollIntoView({ behavior: 'smooth', block: 'center' });
        }
    };

    const handleCellSelect = (row, col) => {
        const merge = getMergeStartForCell(row, col);
        if (merge) {
            setSelectedCell({
                row: merge.startRow,
                col: merge.startCol,
                address: getCellAddress(merge.startRow, merge.startCol),
                merge,
            });
        } else {
            setSelectedCell({ row, col, address: getCellAddress(row, col) });
        }
        setDraft({ source: '', fieldKey: '', attribute: '', readOnly: false, userDataType: 'TEXT' });
    };

    const handleUpload = (e) => {
        const file = e.target.files[0];
        if (!file) return;

        if (!file.name.match(/\.(xlsx|xls)$/)) {
            alert('Please upload a valid Excel file (.xlsx or .xls)');
            return;
        }

        setUploadedFile(file);
        setShowMappingPanel(true);
        setIsEditMode(false);

        const reader = new FileReader();
        reader.onload = async (evt) => {
            try {
                const parsed = await parseExcelBufferWithExcelJs(evt.target.result);
                applyParsedExcel(parsed);
            } catch (error) {
                console.error('Error reading Excel file:', error);
                alert('Failed to read Excel file: ' + error.message);
                setExcelData([['Error Loading File']]);
                setAnchoredImages([]);
            }
        };

        reader.readAsArrayBuffer(file);
        e.target.value = '';
    };

    const handleReplaceUpload = async (e) => {
        const file = e.target.files?.[0];
        e.target.value = '';
        if (!file) return;

        if (!file.name.match(/\.(xlsx|xls)$/i)) {
            showError('Please upload a valid Excel file (.xlsx or .xls)');
            return;
        }

        if (file.name.match(/\.xls$/i) && !file.name.match(/\.xlsx$/i)) {
            setFileReplaceNotice({
                severity: 'warning',
                message: 'Legacy .xls format may not preserve data validation rules. Prefer saving as .xlsx in Excel.',
            });
        }

        showLoader('Loading replaced Excel file...');
        try {
            const arrayBuffer = await file.arrayBuffer();
            const parsed = await parseExcelBufferWithExcelJs(arrayBuffer);
            const ok = applyParsedExcel(parsed);
            if (!ok) {
                hideLoader();
                return;
            }

            setUploadedFile(file);
            setIsEditMode(true);
            setShowMappingPanel(true);

            const afterCount = MappingService.countValidationRules(parsed.validations);
            if (validationCountBefore > 0 && afterCount === 0) {
                setFileReplaceNotice({
                    severity: 'warning',
                    message: 'No data validation rules detected in the re-uploaded file. Save as .xlsx in Excel to keep dropdown/range rules.',
                });
            } else {
                setFileReplaceNotice({
                    severity: 'success',
                    message: `Excel file replaced. ${afterCount} validation rule(s) detected. Review mappings if cell positions changed.`,
                });
            }
            hideLoader();
        } catch (error) {
            hideLoader();
            showCatchError(error);
        }
    };

    const saveMapping = () => {
        if (!selectedCell || !draft.source) {
            alert('Please select a cell and specify source');
            return;
        }

        const mapping = {
            section: mappingMode,
            source: draft.source,
            excelCell: selectedCell.address,
            readOnly: draft.source === 'DB' ? !!draft.readOnly : false,
            cellValue: excelData[selectedCell.row]?.[selectedCell.col] || '',
            ...(draft.source === 'DB'
                ? {
                    type: 'TEXT',
                    attribute: draft.attribute,
                    fieldKey: draft.attribute.toLowerCase()
                }
                : {
                    type: draft.userDataType || 'TEXT',
                    fieldKey: `User_${selectedCell.address}`
                }
            )
        };

        if (mappingMode === 'BODY' && draft.source === 'USER') {
            mapping.repeat = 'VERTICAL';
        }

        // Check for existing mapping
        const existingIndex = mappings.findIndex(
            m => m.section === mappingMode && m.excelCell === selectedCell.address
        );

        if (existingIndex >= 0) {
            const updatedMappings = [...mappings];
            updatedMappings[existingIndex] = mapping;
            setMappings(updatedMappings);
        } else {
            setMappings(prev => [...prev, mapping]);
        }

        setSelectedCell(null);
        setDraft({ source: '', fieldKey: '', attribute: '', readOnly: false, userDataType: 'TEXT' });
    };

    const deleteMapping = (index) => {
        setMappings(prev => prev.filter((_, i) => i !== index));
    };

    const exportMapping = () => {
        const mappingData = {
            template: uploadedFile?.name,
            uploadedAt: new Date().toISOString(),
            mappings,
            metadata: {
                totalRows: excelData.length,
                totalCols: excelData[0]?.length || 0
            }
        };

        const blob = new Blob([JSON.stringify(mappingData, null, 2)], { type: 'application/json' });
        const url = URL.createObjectURL(blob);
        const a = document.createElement('a');
        a.href = url;
        a.download = `excel-mapping-${Date.now()}.json`;
        a.click();
        URL.revokeObjectURL(url);
    };

    const saveFormat = async () => {
        if (!uploadedFile) {
            showError('Please upload an Excel file first.');
            return;
        }
        if (mappings.length === 0) {
            showError('Please create at least one mapping before saving.');
            return;
        }

        const totalRows = excelData.length;
        const totalCols = excelData[0]?.length || 0;
        const layout = buildLayoutMetadata(mappings, totalRows, totalCols);
        const mappingData = {
            mappings,
            metadata: {
                template: uploadedFile.name,
                totalRows,
                totalCols,
                createdAt: new Date().toISOString(),
                ...layout
            }
        };
        const validation = MappingService.validateMapping(mappingData);
        if (!validation.isValid) {
            showError(`Validation failed: ${validation.errors.join(', ')}`);
            return;
        }

        const customerCode = selectedCustomerCode;
        const userCode = selectedUserCode;
        const branchCode = selectedBranchCode;
        if (!customerCode) {
            showError('Please select a customer before saving the format.');
            return;
        }
        if (!userCode) {
            showError('User code is required. Please ensure you are logged in.');
            return;
        }
        if (!branchCode) {
            showError('Please select a branch before saving the format.');
            return;
        }

        setSavingFormat(true);
        showLoader(isEditMode ? 'Saving as new version...' : 'Saving format...');
        try {
            const result = await MappingService.saveTemplateMapping(
                customerCode,
                uploadedFile,
                mappingData,
                userCode,
                branchCode,
                divCode
            );
            hideLoader();
            if (result.success) {
                const apiMessage = isEditMode
                    ? 'Template saved as new version'
                    : (result.message || 'Template Saved Successfully');

                if (isEditMode) {
                    setIsEditMode(false);
                    await refreshExistingFormat(customerCode, branchCode);
                }

                if (setFormvalueObj) {
                    setFormvalueObj((pre) => ({
                        ...pre,
                        error: {
                            ...pre?.error,
                            Msg: apiMessage,
                            Type: 'success',
                            errorOpen: true,
                            templateSavedSuccess: true,
                        },
                    }));
                }
            } else {
                showError(result.message ?? 'Failed to save format');
            }
        } catch (error) {
            hideLoader();
            showCatchError(error);
        } finally {
            setSavingFormat(false);
        }
    };

    const sectionHeaderSx = { backgroundColor: '#603d23', color: '#fff', fontWeight: 600, px: 1.5, py: 0.5 };
    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 - 24) / baseWidth);
        setTableZoom(target);
    };

    useEffect(() => {
        const tableEl = previewTableRef.current;
        if (!tableEl || !excelData?.length) return;
        const fallbackColWidth = 110;
        const fallbackRowHeight = 20;
        const maxCols = Math.max(...excelData.map((r) => (Array.isArray(r) ? r.length : 0)), 0);
        const expectedW = Array.from({ length: maxCols }, (_, c) => (sheetDims?.colWidths?.[c] || fallbackColWidth)).reduce((a, b) => a + b, 0);
        const expectedH = Array.from({ length: excelData.length }, (_, r) => (sheetDims?.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, sheetDims, 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]);

    return (
        <Box sx={{ display: "flex", flexDirection: 'column', gap: 2, p: 2, minHeight: 0, overflow: 'hidden' }}>
            <Divider><Chip label="Excel Template Mapping" sx={sectionHeaderSx} /></Divider>

            {loadingExisting && (
                <Alert severity="info" icon={<CircularProgress size={18} />}>
                    Checking for existing format...
                </Alert>
            )}

            {!loadingExisting && existingFormat && !uploadedFile && (
                <Alert
                    severity="info"
                    action={
                        <Stack direction="row" spacing={1}>
                            <Button
                                color="inherit"
                                size="small"
                                startIcon={<FileDownloadIcon />}
                                onClick={downloadCurrentTemplate}
                                sx={{ fontWeight: 600 }}
                            >
                                Download
                            </Button>
                            <Button
                                color="inherit"
                                size="small"
                                startIcon={<EditNoteIcon />}
                                onClick={loadExistingFormat}
                                sx={{ fontWeight: 600 }}
                            >
                                Load for editing
                            </Button>
                        </Stack>
                    }
                >
                    Active format found: <strong>{existingFormat.templateName || 'Template'}</strong>
                    {existingFormat.version != null ? ` (v${existingFormat.version})` : ''}
                </Alert>
            )}

            {!loadingExisting && isEditMode && uploadedFile && (
                <Alert severity="warning" sx={{ py: 0.5 }}>
                    Editing existing format
                    {existingFormat?.version != null ? ` (based on v${existingFormat.version})` : ''}
                    — save will create a new version.
                    {' '}To change <strong>formulas</strong> or <strong>data validation</strong>, download the template, edit in <strong>Excel</strong>, save as <strong>.xlsx</strong>, then use <strong>Replace Excel file</strong>.
                    Mappings are kept — review them if cells moved. Validation rules come from the Excel file you re-upload.
                    {validationRuleCount > 0 ? ` (${validationRuleCount} validation rule(s) in current file.)` : ''}
                </Alert>
            )}

            {fileReplaceNotice && (
                <Alert severity={fileReplaceNotice.severity} onClose={() => setFileReplaceNotice(null)}>
                    {fileReplaceNotice.message}
                </Alert>
            )}

            <Box sx={{ display: "flex", gap: 2, flex: 1, minHeight: 400, overflow: 'hidden' }}>
                <Box sx={{ flex: 1, display: 'flex', flexDirection: 'column', minWidth: 0 }}>
                    {!uploadedFile && (
                        <Paper
                            elevation={2}
                            sx={{
                                p: 4,
                                textAlign: 'center',
                                border: '2px dashed #603d23',
                                backgroundColor: '#f5f5f5',
                                borderRadius: 2,
                                transition: 'background-color 0.2s',
                                '&:hover': { backgroundColor: '#eeeeee' }
                            }}
                        >
                            <UploadFileIcon sx={{ fontSize: 48, color: '#603d23', mb: 1 }} />
                            <Typography variant="subtitle1" sx={{ color: '#424242', mb: 2, fontWeight: 500 }}>
                                Upload Excel invoice template to map cells to database or user fields
                            </Typography>
                            <input type="file" accept=".xlsx,.xls" onChange={handleUpload} style={{ display: 'none' }} id="excel-upload" />
                            <label htmlFor="excel-upload">
                                <Button variant="contained" component="span" startIcon={<UploadFileIcon />} sx={{ backgroundColor: '#603d23', '&:hover': { backgroundColor: '#4a2f1a' } }}>
                                    Choose File
                                </Button>
                            </label>
                        </Paper>
                    )}

                    {uploadedFile && (
                        <Paper elevation={2} sx={{ p: 1.5, mb: 1.5, display: 'flex', justifyContent: 'space-between', alignItems: 'center', flexWrap: 'wrap', gap: 1 }}>
                            <Chip icon={<DescriptionIcon />} label={uploadedFile.name} variant="outlined" sx={{ fontWeight: 600 }} />
                            <Stack direction="row" spacing={1} flexWrap="wrap" useFlexGap>
                                <Button size="small" startIcon={<FileDownloadIcon />} variant="outlined" onClick={downloadCurrentTemplate}>
                                    Download
                                </Button>
                                {isEditMode && (
                                    <>
                                        <input
                                            ref={replaceUploadInputRef}
                                            type="file"
                                            accept=".xlsx,.xls"
                                            onChange={handleReplaceUpload}
                                            style={{ display: 'none' }}
                                            id="excel-replace-upload"
                                        />
                                        <Button
                                            size="small"
                                            variant="outlined"
                                            startIcon={<UploadFileIcon />}
                                            onClick={() => replaceUploadInputRef.current?.click()}
                                        >
                                            Replace Excel file
                                        </Button>
                                    </>
                                )}
                                <Button size="small" startIcon={savingFormat ? <CircularProgress size={16} /> : <SaveIcon />} onClick={saveFormat} disabled={savingFormat || mappings.length === 0} variant="contained" sx={{ backgroundColor: '#603d23', '&:hover': { backgroundColor: '#4a2f1a' } }}>{savingFormat ? 'Saving...' : (isEditMode ? 'Save as New Version' : 'Save format')}</Button>
                                <Button size="small" startIcon={<DeleteOutlineIcon />} variant="outlined" color="error" onClick={clearEditor}>Clear</Button>
                            </Stack>
                        </Paper>
                    )}

                    {uploadedFile && (
                        <Paper elevation={2} sx={{ flex: 1, overflow: 'hidden', borderRadius: 1, backgroundColor: '#fff', border: '1px solid #e0e0e0', display: 'flex', flexDirection: 'column', minHeight: 0 }}>
                            <Box sx={{ p: 0.5, borderBottom: '1px solid #e0e0e0', backgroundColor: '#fafafa', display: 'flex', justifyContent: 'space-between', alignItems: 'center', gap: 1, flexWrap: 'wrap' }}>
                                <Typography variant="caption" sx={{ color: '#666', fontWeight: 600 }}>Excel Preview — Click a cell to map it</Typography>
                                <Stack direction="row" spacing={1} alignItems="center">
                                    <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: 120 }}
                                    />
                                    <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: 44, textAlign: 'right' }}>
                                        {Math.round(tableZoom * 100)}%
                                    </Typography>
                                </Stack>
                            </Box>
                            <Box
                                ref={previewViewportRef}
                                sx={{
                                    p: 1,
                                    overflow: 'auto',
                                    flex: 1,
                                    minHeight: 280,
                                    maxHeight: '72vh',
                                }}
                            >
                                {excelData.length > 0 && (
                                    /* Keep preview anchored from left while zooming; viewport handles inner scrolling */
                                    <Box sx={{ width: 'max-content', margin: 0, transform: `scale(${tableZoom})`, transformOrigin: 'top left', position: 'relative' }}>
                                    <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 r = m.startRow; r <= m.endRow; r++) {
                                                        for (let c = m.startCol; c <= m.endCol; c++) {
                                                            if (r === m.startRow && c === m.startCol) continue;
                                                            covered.add(`${r}-${c}`);
                                                        }
                                                    }
                                                });

                                                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;

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

                                                            const merge = mergeMap.get(`${r}-${c}`);
                                                            const css = cellStyles?.[r]?.[c] || null;
                                                            const isSelected = selectedCell?.row === r && selectedCell?.col === c;

                                                            const style = {
                                                                width: sheetDims?.colWidths?.[c] || fallbackColWidth,
                                                                maxWidth: sheetDims?.colWidths?.[c] || fallbackColWidth,
                                                                minWidth: sheetDims?.colWidths?.[c] || fallbackColWidth,
                                                                padding: '3px 8px',
                                                                boxSizing: 'border-box',
                                                                cursor: 'pointer',
                                                                userSelect: 'none',
                                                                verticalAlign: 'middle',
                                                                fontSize: '12px',
                                                                ...(css && typeof css === 'object' ? css : {}),
                                                            };
                                                            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;
                                                            }

                                                            // Add visual indicator for mapped cells
                                                            if (isCellMapped(r, c)) {
                                                                style.backgroundColor = '#c8e6c9';
                                                                style.border = '2px solid #4caf50';
                                                            }

                                                            const rawCell = rawExcelData?.[r]?.[c];
                                                            const formulaText = rawCell && typeof rawCell === 'object' && rawCell.formula
                                                                ? String(rawCell.formula)
                                                                : null;
                                                            const validationRule = cellValidations?.[r]?.[c];
                                                            if (validationRule && !isCellMapped(r, c)) {
                                                                style.boxShadow = style.boxShadow || 'inset 0 0 0 1px #90caf9';
                                                            }
                                                            const cellTitle = formulaText
                                                                ? `Formula: ${formulaText}`
                                                                : validationRule?.type
                                                                    ? `Validation: ${validationRule.type}${validationRule.operator ? ` (${validationRule.operator})` : ''}`
                                                                    : undefined;

                                                            // Add selection highlight
                                                            if (isSelected) {
                                                                style.outline = '2px solid #603d23';
                                                                style.outlineOffset = '-2px';
                                                                style.boxShadow = 'inset 0 0 0 1px #603d23';
                                                            }

                                                            return (
                                                                <td
                                                                    key={`${r}-${c}`}
                                                                    rowSpan={merge?.rowspan}
                                                                    colSpan={merge?.colspan}
                                                                    style={style}
                                                                    onClick={() => handleCellSelect(r, c)}
                                                                    data-cell={getCellAddress(r, c)}
                                                                    title={cellTitle}
                                                                >
                                                                    <span>{value}</span>
                                                                    {isCellMapped(r, c) ? (
                                                                        <CheckCircleOutlineIcon
                                                                            component="span"
                                                                            sx={{ fontSize: 14, color: '#2e7d32', ml: 0.5, verticalAlign: 'middle' }}
                                                                        />
                                                                    ) : null}
                                                                </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}
                                    </Box>
                                )}
                            </Box>
                        </Paper>
                    )}
                </Box>

                {showMappingPanel && (
                    <Paper elevation={2} sx={{ width: 380, flexShrink: 0, overflow: 'hidden', display: 'flex', flexDirection: 'column', borderRadius: 2, border: '1px solid #e0e0e0' }}>
                        <Box sx={{ p: 1.5, backgroundColor: '#603d23', color: '#fff' }}>
                            <Typography variant="subtitle2" sx={{ fontWeight: 600, display: 'flex', alignItems: 'center', gap: 0.5 }}>
                                <ViewListIcon fontSize="small" /> Mapping steps
                            </Typography>
                            <Typography variant="caption" sx={{ opacity: 0.9, display: 'block', mt: 0.5 }}>
                                Select section → Click cell → Choose source → Configure field → Save
                            </Typography>
                        </Box>

                        <Box sx={{ p: 2, flex: 1, overflow: 'auto', backgroundColor: '#fafafa' }}>
                            <Typography variant="subtitle2" sx={{ color: '#424242', fontWeight: 600, mb: 1.5 }}>Section</Typography>
                            <ToggleButtonGroup
                                value={mappingMode}
                                exclusive
                                onChange={(e, val) => val != null && (setMappingMode(val), setSelectedCell(null), setDraft({ source: '', fieldKey: '', attribute: '', readOnly: false, userDataType: 'TEXT' }))}
                                size="small"
                                fullWidth
                                sx={{ mb: 2, '& .MuiToggleButtonGroup-grouped': { textTransform: 'none' } }}
                            >
                                <ToggleButton value="HEADER">Header</ToggleButton>
                                <ToggleButton value="BODY">Body</ToggleButton>
                                <ToggleButton value="FOOTER">Footer</ToggleButton>
                            </ToggleButtonGroup>

                            {selectedCell && (
                                <Chip icon={<CheckCircleOutlineIcon />} label={`Selected: ${selectedCell.address}`} size="small" color="success" sx={{ mb: 2 }} />
                            )}

                            {dbFieldsError && (
                                <Alert severity="error" onClose={() => setDbFieldsError(null)} sx={{ mb: 2 }}>
                                    Database fields: {dbFieldsError}
                                </Alert>
                            )}
                            {selectedCell && (
                                <>
                                    <Divider sx={{ my: 2 }}><Typography variant="caption" color="text.secondary">Data source</Typography></Divider>
                                    <Stack direction="row" spacing={1} sx={{ mb: 2 }}>
                                        <Button
                                            variant={draft.source === 'DB' ? 'contained' : 'outlined'}
                                            size="small"
                                            startIcon={<StorageIcon />}
                                            onClick={() => setDraft(prev => ({ ...prev, source: 'DB', fieldKey: '', attribute: '' }))}
                                            disabled={loadingDbFields || !!dbFieldsError || dbAttributes.length === 0}
                                            sx={{ flex: 1, textTransform: 'none', ...(draft.source === 'DB' && { backgroundColor: '#603d23', '&:hover': { backgroundColor: '#4a2f1a' } }) }}
                                        >
                                            Database
                                        </Button>
                                        <Button
                                            variant={draft.source === 'USER' ? 'contained' : 'outlined'}
                                            size="small"
                                            startIcon={<PersonOutlineIcon />}
                                            onClick={() => setDraft(prev => ({ ...prev, source: 'USER', fieldKey: '', attribute: '', readOnly: false, userDataType: prev.userDataType || 'TEXT' }))}
                                            sx={{ flex: 1, textTransform: 'none', ...(draft.source === 'USER' && { backgroundColor: '#603d23', '&:hover': { backgroundColor: '#4a2f1a' } }) }}
                                        >
                                            User input
                                        </Button>
                                    </Stack>
                                </>
                            )}

                            {/* Field Configuration */}
                            {selectedCell && draft.source && (
                                <Box sx={{ mb: 3 }}>
                                    {draft.source === 'DB' && (
                                        <Box>
                                            <Autocomplete
                                                disablePortal
                                                id="database-field"
                                                size="small"
                                                options={dbAttributes}
                                                value={dbAttributes.find(option => option.value === draft.attribute) || null}
                                                onChange={(event, newValue) => setDraft(prev => ({ ...prev, attribute: newValue?.value || '' }))}
                                                getOptionLabel={(option) => option?.label || ''}
                                                renderInput={(params) => (
                                                    <TextField
                                                        {...params}
                                                        label={
                                                            loadingDbFields
                                                                ? 'Loading database fields...'
                                                                : dbAttributes.length === 0
                                                                    ? 'No database fields (from API)'
                                                                    : 'Select database field'
                                                        }
                                                        fullWidth
                                                        sx={{ mb: 2 }}
                                                        InputProps={{
                                                            ...params.InputProps,
                                                            endAdornment: (
                                                                <>
                                                                    {loadingDbFields ? <CircularProgress color="inherit" size={20} /> : null}
                                                                    {params.InputProps.endAdornment}
                                                                </>
                                                            )
                                                        }}
                                                    />
                                                )}
                                                disabled={loadingDbFields || dbAttributes.length === 0}
                                            />

                                            <FormControlLabel
                                                control={
                                                    <Checkbox
                                                        checked={draft.readOnly}
                                                        onChange={(e) => setDraft(prev => ({ ...prev, readOnly: e.target.checked }))}
                                                        size="small"
                                                    />
                                                }
                                                label="Read Only"
                                                sx={{ mb: 2 }}
                                            />
                                        </Box>
                                    )}

                                    {draft.source === 'USER' && (
                                        <Box>
                                            <Autocomplete
                                                size="small"
                                                options={USER_DATA_TYPES}
                                                getOptionLabel={(opt) => opt.label}
                                                value={USER_DATA_TYPES.find(t => t.value === (draft.userDataType || 'TEXT')) || USER_DATA_TYPES[0]}
                                                onChange={(e, newValue) => setDraft(prev => ({ ...prev, userDataType: newValue?.value || 'TEXT' }))}
                                                renderInput={(params) => <TextField {...params} label="Data type (for validation)" />}
                                                sx={{ mb: 2 }}
                                            />

                                        </Box>
                                    )}
                                </Box>
                            )}

                            {/* Save mapping */}
                            {selectedCell && draft.source && (
                                <Stack direction="row" spacing={1} sx={{ mt: 2 }}>
                                    <Button variant="contained" size="small" startIcon={<SaveIcon />} onClick={saveMapping} disabled={!draft.source || (draft.source === 'DB' ? !draft.attribute : false)} sx={{ flex: 1, backgroundColor: '#2e7d32', '&:hover': { backgroundColor: '#1b5e20' } }}>Save mapping</Button>
                                    <Button variant="outlined" size="small" onClick={() => { setSelectedCell(null); setDraft({ source: '', fieldKey: '', attribute: '', readOnly: false, userDataType: 'TEXT' }); }}>Clear</Button>
                                </Stack>
                            )}


                            {mappings.length > 0 && (
                                <>
                                    <Divider sx={{ my: 2 }}><Typography variant="caption" color="text.secondary">{mappingMode} mappings</Typography></Divider>
                                    <Box sx={{ display: 'flex', flexWrap: 'wrap', gap: 0.75 }}>
                                        {mappings.filter(m => m.section === mappingMode).map((mapping, idx) => {
                                            const globalIndex = mappings.findIndex(m => m === mapping);
                                            return (
                                                <Tooltip key={`${mapping.excelCell}-${globalIndex}`} title={<Box sx={{ p: 1 }}><Typography variant="caption">Section: {mapping.section} · Source: {mapping.source} · Read only: {mapping.readOnly ? 'Yes' : 'No'}</Typography></Box>} arrow placement="top">
                                                    <Chip
                                                        size="small"
                                                        label={`${mapping.excelCell}: ${mapping.source === 'DB' ? (dbAttributes.find(attr => attr.value === mapping.attribute)?.label || mapping.attribute) : `${mapping.fieldKey} (${mapping.type})`}`}
                                                        onDelete={() => deleteMapping(globalIndex)}
                                                        onClick={() => highlightCell(mapping.excelCell)}
                                                        variant="outlined"
                                                        clickable
                                                        sx={{ fontSize: '0.7rem', borderColor: '#603d23', color: '#603d23', '&:hover': { backgroundColor: 'rgba(96,61,35,0.08)' } }}
                                                    />
                                                </Tooltip>
                                            );
                                        })}
                                    </Box>
                                </>
                            )}

                            {/* <Divider sx={{ my: 2 }} />   //Vinay 
                            <Typography variant="caption" color="text.secondary" sx={{ display: 'block', mb: 0.5 }}>Config preview</Typography>
                            <Box sx={{ background: '#f5f5f5', p: 1.5, borderRadius: 1, maxHeight: 100, overflow: 'auto', fontSize: '0.7rem', fontFamily: 'monospace' }}>
                                {JSON.stringify({ mappings }, null, 2)}
                            </Box> */}
                        </Box>
                    </Paper>
                )}
            </Box>
        </Box>
    );
}