import React, { useCallback, useEffect, useRef, useState } from 'react';
import { __ } from '@wordpress/i18n';
import { isEmpty } from 'ramda';
import { createUniver, LocaleType, mergeLocales } from '@univerjs/presets';
import { UniverSheetsCorePreset } from '@univerjs/preset-sheets-core';
import UniverPresetSheetsCoreEnUS from '@univerjs/preset-sheets-core/locales/en-US';
import '@univerjs/preset-sheets-core/lib/index.css';

// store
import { useStoreValue } from '../../../../../../store';

// tools
import { xlsxToWorkbookData } from '../../../../../BandoEdit/components/BandoEditFormStep3Excel/xlsxToWorkbookData';

const TAG_RE = /^\{\{gepafin_field:([^|]+)\|([^}]+)}}$/;

const ElementSettingSpreadsheet = ({ value, name, setDataFn, context }) => {
    const callFormFields = useStoreValue('callFormFields');
    const [tagTooltip, setTagTooltip] = useState(null);
    const containerRef = useRef(null);
    const univerRef = useRef(null);
    const univerAPIRef = useRef(null);
    const formFieldsRef = useRef([]);
    const isRestoringRef = useRef(false);
    const mousePos = useRef({ x: 0, y: 0 });
    const fileInputRef = useRef(null);
    const taggedCellsRef = useRef(new Set());
    const saveTimerRef = useRef(null);
    const setDataFnRef = useRef(setDataFn);
    const gepafinMetaRef = useRef({ inputOnlyMode: false, editableCells: [], validationCells: [] });
    const contextRef = useRef(context);
    const addSheetsFileInputRef = useRef(null);
    const [inputOnlyActive, setInputOnlyActive] = useState(false);

    useEffect(() => { setDataFnRef.current = setDataFn; }, [setDataFn]);

    const insertFieldTag = (ids, type, field) => {
        const api = univerAPIRef.current;
        if (!api) return;
        const range = api.getActiveWorkbook()?.getActiveSheet()?.getActiveRange();
        if (!range) return;
        const displayValue = type === 'value' ? (field.placeholder || '') : field.label;
        range.setValues([[{
            v: displayValue,
            f: `{{gepafin_field:${ids.join(',')}|${type}}}`
        }]]);
        range.setBackground('#dbeafe');
    };

    const getActiveCellKey = () => {
        const api = univerAPIRef.current;
        if (!api) return null;
        const range = api.getActiveWorkbook()?.getActiveSheet()?.getActiveRange();
        if (!range) return null;
        return `${range.getRow()}:${range.getColumn()}`;
    };

    const saveWithMeta = useCallback(() => {
        const wb = univerAPIRef.current?.getActiveWorkbook();
        if (wb) {
            const raw = wb.save();
            const meta = gepafinMetaRef.current;
            // inputOnlyMode is derived: active whenever any editable cells are defined
            setDataFnRef.current(name, {
                ...raw,
                gepafin: { ...meta, inputOnlyMode: meta.editableCells.length > 0 }
            });
        }
    }, [name]);

    const toggleEditableCell = useCallback(() => {
        const key = getActiveCellKey();
        if (!key) return;
        const meta = gepafinMetaRef.current;
        const api = univerAPIRef.current;
        const sheet = api?.getActiveWorkbook()?.getActiveSheet();
        const [r, c] = key.split(':').map(Number);

        if (meta.editableCells.includes(key)) {
            meta.editableCells = meta.editableCells.filter(k => k !== key);
            sheet?.getRange(r, c, 1, 1).setBackground('');
        } else {
            meta.editableCells = [...meta.editableCells, key];
            sheet?.getRange(r, c, 1, 1).setBackground('#dcfce7');
        }
        setInputOnlyActive(meta.editableCells.length > 0);
        saveWithMeta();
    }, [saveWithMeta]);

    const toggleValidationCell = useCallback(() => {
        const key = getActiveCellKey();
        if (!key) return;
        const meta = gepafinMetaRef.current;
        const api = univerAPIRef.current;
        const sheet = api?.getActiveWorkbook()?.getActiveSheet();
        const [r, c] = key.split(':').map(Number);

        const existing = meta.validationCells.find(vc => (vc.key ?? vc) === key);
        if (existing) {
            meta.validationCells = meta.validationCells.filter(vc => (vc.key ?? vc) !== key);
            sheet?.getRange(r, c, 1, 1).setBackground('');
        } else {
            // eslint-disable-next-line no-alert
            const errorMessage = window.prompt(
                __('Messaggio di errore di validazione (lascia vuoto per il default):', 'gepafin'),
                ''
            ) ?? '';
            meta.validationCells = [...meta.validationCells, { key, errorMessage: errorMessage || 'Validazione foglio di calcolo fallita' }];
            sheet?.getRange(r, c, 1, 1).setBackground('#fef3c7');
        }
        saveWithMeta();
    }, [saveWithMeta]);

    const initializeUniver = useCallback((workbookData) => {
        if (univerRef.current) {
            univerRef.current.dispose();
            univerRef.current = null;
            univerAPIRef.current = null;
        }

        if (!containerRef.current) return;

        // Initialise gepafin metadata from workbook
        gepafinMetaRef.current = workbookData?.gepafin
            ? { inputOnlyMode: false, editableCells: [], validationCells: [], ...workbookData.gepafin }
            : { inputOnlyMode: false, editableCells: [], validationCells: [] };
        setInputOnlyActive(gepafinMetaRef.current.editableCells.length > 0);

        const { univer, univerAPI } = createUniver({
            locale: LocaleType.EN_US,
            locales: {
                [LocaleType.EN_US]: mergeLocales(UniverPresetSheetsCoreEnUS),
            },
            presets: [
                UniverSheetsCorePreset({
                    container: containerRef.current,
                }),
            ],
        });

        univerAPI.createWorkbook(workbookData || { name: 'Sheet' });
        univerRef.current = univer;
        univerAPIRef.current = univerAPI;

        // Initialise tagged-cell tracking from the loaded workbook data
        const initialTagged = new Set();
        if (workbookData?.sheets) {
            const sid = workbookData.sheetOrder?.[0];
            const sd = sid ? workbookData.sheets[sid] : Object.values(workbookData.sheets)[0];
            if (sd?.cellData) {
                Object.entries(sd.cellData).forEach(([r, row]) =>
                    Object.entries(row).forEach(([c, cell]) => {
                        if (typeof cell.f === 'string' && TAG_RE.test(cell.f))
                            initialTagged.add(`${r}:${c}`);
                    })
                );
            }
        }
        taggedCellsRef.current = initialTagged;

        // Re-apply gepafin cell background markers after a short delay (Univer needs to settle)
        setTimeout(() => {
            const wb = univerAPIRef.current?.getActiveWorkbook();
            const sheet = wb?.getActiveSheet();
            if (!sheet) return;
            const meta = gepafinMetaRef.current;
            meta.editableCells.forEach((key) => {
                const [r, c] = key.split(':').map(Number);
                sheet.getRange(r, c, 1, 1).setBackground('#dcfce7');
            });
            meta.validationCells.forEach((vc) => {
                const key = vc.key ?? vc;
                const [r, c] = key.split(':').map(Number);
                sheet.getRange(r, c, 1, 1).setBackground('#fef3c7');
            });
        }, 300);

        // Restore tag cells whose display value was overwritten by raw tag on blur;
        // also clear background for cells whose tag formula was removed.
        const restoreTagCells = () => {
            if (isRestoringRef.current) return;
            const wb = univerAPIRef.current?.getActiveWorkbook();
            if (!wb) return;
            const sheet = wb.getActiveSheet();
            if (!sheet) return;
            const saved = wb.save();
            if (!saved?.sheets) return;
            const sheetId = saved.sheetOrder?.[0];
            const sheetData = sheetId ? saved.sheets[sheetId] : Object.values(saved.sheets)[0];
            if (!sheetData?.cellData) return;

            // Build set of cells that currently have a tag formula
            const currentlyTagged = new Set();
            Object.entries(sheetData.cellData).forEach(([r, row]) =>
                Object.entries(row).forEach(([c, cell]) => {
                    if (typeof cell.f === 'string' && TAG_RE.test(cell.f))
                        currentlyTagged.add(`${r}:${c}`);
                })
            );

            // Cells that lost their tag formula → need background reset
            const cellsToDecolor = [];
            for (const key of taggedCellsRef.current) {
                if (!currentlyTagged.has(key)) {
                    const [r, c] = key.split(':').map(Number);
                    cellsToDecolor.push({ r, c });
                }
            }

            // Cells whose displayed value was overwritten by raw formula text → restore
            const restorations = [];
            Object.entries(sheetData.cellData).forEach(([r, row]) => {
                Object.entries(row).forEach(([c, cell]) => {
                    const match = typeof cell.v === 'string' && cell.v.match(TAG_RE);
                    if (match) {
                        const tagIds = match[1].split(',');
                        const type = match[2];
                        const field = formFieldsRef.current.find(f =>
                            f.ids.some(id => tagIds.includes(String(id)))
                        );
                        const displayValue = type === 'value'
                            ? (field?.placeholder || '')
                            : (field?.label || match[1]);
                        restorations.push({ r: +r, c: +c, v: displayValue, f: cell.v });
                    }
                });
            });

            if (restorations.length === 0 && cellsToDecolor.length === 0) {
                taggedCellsRef.current = currentlyTagged;
                return;
            }
            isRestoringRef.current = true;
            restorations.forEach(({ r, c, v, f }) => {
                sheet.getRange(r, c, 1, 1).setValues([[{ v, f }]]);
            });
            cellsToDecolor.forEach(({ r, c }) => {
                sheet.getRange(r, c, 1, 1).setBackground('');
            });
            isRestoringRef.current = false;
            taggedCellsRef.current = currentlyTagged;
        };

        let restoreTimer;

        // Auto-save on every command; schedule tag restoration
        univerAPI.addEvent(univerAPI.Event.BeforeCommandExecute, (event) => {
            clearTimeout(saveTimerRef.current);
            saveTimerRef.current = setTimeout(() => {
                const wb = univerAPIRef.current?.getActiveWorkbook();
                if (wb) {
                    const raw = wb.save();
                    const m = gepafinMetaRef.current;
                    setDataFnRef.current(name, { ...raw, gepafin: { ...m, inputOnlyMode: m.editableCells.length > 0 } });
                }
            }, 300);
            clearTimeout(restoreTimer);
            restoreTimer = setTimeout(restoreTagCells, 80);
        });

        // Context menu: "Inserisci variabile GEPAFIN" — only for evaluation form builder (context="call")
        if (contextRef.current === 'call') {
            const fields = formFieldsRef.current;
            if (fields.length > 0) {
                const submenu = univerAPI.createSubmenu({
                    id: 'gepafin-insert-variable',
                    title: 'Inserisci variabile GEPAFIN',
                });

                fields.forEach((field, index) => {
                    if (index > 0) submenu.addSeparator();

                    const menuKey = field.ids[0];
                    const countLabel = field.ids.length > 1 ? ` [${field.ids.length}]` : '';

                    submenu.addSubmenu(univerAPI.createMenu({
                        id: `gepafin-field-${menuKey}-label`,
                        title: `${field.label}${countLabel} (etichetta)`,
                        action: () => insertFieldTag(field.ids, 'label', field),
                    }));

                    submenu.addSubmenu(univerAPI.createMenu({
                        id: `gepafin-field-${menuKey}-value`,
                        title: `${field.label}${countLabel} (valore)`,
                        action: () => insertFieldTag(field.ids, 'value', field),
                    }));
                });

                submenu.appendTo(['contextMenu.mainArea', 'contextMenu.others']);
            }
        }

        // Context menu: input-only mode management — only for regular form builder (context="application")
        if (contextRef.current === 'application') {
            univerAPI.createMenu({
                id: 'gepafin-toggle-editable-cell',
                title: 'Modalità inserimento: segna/rimuovi cella modificabile',
                action: toggleEditableCell,
            }).appendTo(['contextMenu.mainArea', 'contextMenu.others']);

            univerAPI.createMenu({
                id: 'gepafin-toggle-validation-cell',
                title: 'Modalità inserimento: segna/rimuovi cella di validazione',
                action: toggleValidationCell,
            }).appendTo(['contextMenu.mainArea', 'contextMenu.others']);
        }

        // Tooltip on hover over tagged cells — read formula field to detect tag
        univerAPI.addEvent(univerAPI.Event.CellHover, (params) => {
            const { row, column, worksheet } = params;
            const formula = worksheet?.getRange(row, column)?.getFormulas()?.[0]?.[0];
            if (typeof formula === 'string' && formula.startsWith('{{gepafin_field:')) {
                const match = formula.match(/\{\{gepafin_field:([^|]+)\|([^}]+)\}\}/);
                if (match) {
                    const tagIds = match[1].split(',');
                    const field = formFieldsRef.current.find(f => f.ids.some(fid => tagIds.includes(fid)));
                    const countSuffix = tagIds.length > 1 ? ` — ${tagIds.length} moduli` : '';
                    setTagTooltip({ label: `${field ? field.label : match[1]} (${match[2]})${countSuffix}` });
                }
            } else {
                setTagTooltip(null);
            }
        });

    }, [toggleEditableCell, toggleValidationCell]); // refs are stable

    // Keep formFieldsRef in sync with store value; rebuild Univer (and its context menu)
    // once fields arrive, because the menu is constructed at init time from formFieldsRef.current.
    useEffect(() => {
        console.log('callFormFields', callFormFields)
        formFieldsRef.current = callFormFields;
        if (callFormFields.length > 0 && univerAPIRef.current) {
            const currentWorkbook = univerAPIRef.current.getActiveWorkbook()?.save() ?? null;
            // Preserve gepafin metadata across reinit
            const withMeta = currentWorkbook
                ? { ...currentWorkbook, gepafin: gepafinMetaRef.current }
                : null;
            initializeUniver(withMeta);
        }
    }, [callFormFields, initializeUniver]);

    // Initialize Univer on mount
    useEffect(() => {
        const workbook = value && !isEmpty(value) && value.sheets ? value : null;
        initializeUniver(workbook);

        return () => {
            clearTimeout(saveTimerRef.current);
            if (univerRef.current) {
                univerRef.current.dispose();
                univerRef.current = null;
                univerAPIRef.current = null;
            }
        };
    }, []); // run once on mount

    // Flush workbook data to parent immediately when the user clicks outside the spreadsheet.
    useEffect(() => {
        const container = containerRef.current;
        if (!container) return;
        const flushOnExternalClick = (e) => {
            if (container.contains(e.target)) return;
            clearTimeout(saveTimerRef.current);
            const wb = univerAPIRef.current?.getActiveWorkbook();
            if (wb) {
                const raw = wb.save();
                const m = gepafinMetaRef.current;
                setDataFnRef.current(name, { ...raw, gepafin: { ...m, inputOnlyMode: m.editableCells.length > 0 } });
            }
        };
        document.addEventListener('click', flushOnExternalClick, true);
        return () => document.removeEventListener('click', flushOnExternalClick, true);
    }, [name]);

    // Prevent page scroll while hovering over the spreadsheet
    useEffect(() => {
        const el = containerRef.current;
        if (!el) return;
        const handler = (e) => { e.stopPropagation(); e.preventDefault(); };
        el.addEventListener('wheel', handler, { passive: false });
        return () => el.removeEventListener('wheel', handler);
    }, []);

    const handleImport = (e) => {
        const file = e.target.files?.[0];
        if (!file) return;
        const reader = new FileReader();
        reader.onload = (ev) => {
            const data = xlsxToWorkbookData(ev.target.result);
            initializeUniver(data);
        };
        reader.readAsArrayBuffer(file);
        e.target.value = '';
    };

    const handleImportAsSheets = (e) => {
        const file = e.target.files?.[0];
        if (!file) return;
        const reader = new FileReader();
        reader.onload = (ev) => {
            const imported = xlsxToWorkbookData(ev.target.result);
            const wb = univerAPIRef.current?.getActiveWorkbook();
            if (!wb) return;
            const current = wb.save();
            const meta = gepafinMetaRef.current;
            const existingIds = new Set(current.sheetOrder || []);
            const mergedSheets = { ...(current.sheets || {}) };
            const mergedOrder = [...(current.sheetOrder || [])];
            (imported.sheetOrder || []).forEach((importedId) => {
                let newId = importedId;
                let counter = 0;
                while (existingIds.has(newId)) {
                    counter++;
                    newId = `${importedId}_${counter}`;
                }
                existingIds.add(newId);
                mergedSheets[newId] = { ...imported.sheets[importedId], id: newId };
                mergedOrder.push(newId);
            });
            initializeUniver({ ...current, sheets: mergedSheets, sheetOrder: mergedOrder, gepafin: meta });
        };
        reader.readAsArrayBuffer(file);
        e.target.value = '';
    };

    const handleSave = () => {
        const wb = univerAPIRef.current?.getActiveWorkbook();
        if (wb) {
            const raw = wb.save();
            const m = gepafinMetaRef.current;
            setDataFn(name, { ...raw, gepafin: { ...m, inputOnlyMode: m.editableCells.length > 0 } });
        }
    };

    return (
        <div style={{ width: '100%' }}>
            {context === 'application' && <div className="formElementSettings__fieldDescription" style={{ marginBottom: '12px' }}>
                <p><strong>{__('Celle modificabili (verde)', 'gepafin')}</strong> — {__('Clicca destro su una cella e scegli "Segna/rimuovi cella modificabile" per definire quali celle il beneficiario potrà compilare. La modalità inserimento dati si attiva automaticamente non appena viene definita almeno una cella modificabile.', 'gepafin')}</p>
                <p style={{ marginTop: '8px' }}><strong>{__('Celle di validazione (ambra)', 'gepafin')}</strong> — {__('Segna una cella come "cella di validazione" e inserisci una formula che restituisce TRUE/FALSE. Se il risultato non è vero al momento dell\'invio, il campo viene considerato non valido. Esempi di formule utili:', 'gepafin')}</p>
                <ul style={{ marginTop: '4px', paddingLeft: '20px' }}>
                    <li><code>=NOT(ISBLANK(A1))</code> — {__('cella A1 obbligatoria', 'gepafin')}</li>
                    <li><code>=AND(A1&gt;0, A1&lt;100)</code> — {__('valore numerico tra 0 e 100', 'gepafin')}</li>
                    <li><code>=LEN(TRIM(B2))&gt;0</code> — {__('testo non vuoto in B2', 'gepafin')}</li>
                </ul>
            </div>}
            <div style={{ marginBottom: '8px', display: 'flex', gap: '8px', justifyContent: 'flex-end' }}>
                <input
                    ref={fileInputRef}
                    type="file"
                    accept=".xlsx,.xls,.csv,.ods"
                    style={{ display: 'none' }}
                    onChange={handleImport}
                />
                <input
                    ref={addSheetsFileInputRef}
                    type="file"
                    accept=".xlsx,.xls,.csv,.ods"
                    style={{ display: 'none' }}
                    onChange={handleImportAsSheets}
                />
                <button
                    type="button"
                    className="p-button p-button-outlined p-button-sm"
                    disabled={inputOnlyActive}
                    onClick={() => fileInputRef.current?.click()}
                >
                    {__('Importa foglio', 'gepafin')}
                </button>
                <button
                    type="button"
                    className="p-button p-button-outlined p-button-sm"
                    disabled={inputOnlyActive}
                    onClick={() => addSheetsFileInputRef.current?.click()}
                >
                    {__('Aggiungi fogli', 'gepafin')}
                </button>
                <button
                    type="button"
                    className="p-button p-button-sm"
                    onClick={handleSave}
                >
                    {__('Salva template', 'gepafin')}
                </button>
            </div>
            <div
                ref={containerRef}
                style={{ width: '100%', height: '500px' }}
                onMouseMove={(e) => { mousePos.current = { x: e.clientX, y: e.clientY }; }}
                onMouseLeave={() => setTagTooltip(null)}
            />
            {tagTooltip && (
                <div style={{
                    position: 'fixed',
                    top: mousePos.current.y - 36,
                    left: mousePos.current.x + 12,
                    background: '#1e293b',
                    color: '#fff',
                    padding: '4px 8px',
                    borderRadius: '4px',
                    fontSize: '12px',
                    pointerEvents: 'none',
                    zIndex: 9999,
                    whiteSpace: 'nowrap',
                }}>
                    {tagTooltip.label}
                </div>
            )}
        </div>
    );
};

export default ElementSettingSpreadsheet;
