// ── Initialization & Event Listeners ────────────────────────────────

document.addEventListener('DOMContentLoaded', () => {
    // Theme-aware, keyboard-accessible guidance for tabs and query actions.
    const queryTooltipMap = [
        ['#btnAddFieldRow', 'Add another output field or expression to the visual query grid.'],
        ['#btnSyncToDesigner', 'Parse the SQL editor and synchronize supported changes back to the visual designer.'],
        ['#btnAddWhereTab', 'Add a named WHERE-condition variant to this query.'],
        ['#btnPreviewWhere', 'Preview the current WHERE condition before saving it.'],
        ['#btnSaveWhereTab', 'Save this named WHERE condition.'],
        ['#btnSaveQueryConfirm', 'Save the current query using the entered name.'],
        ['#btnSaveJoin', 'Create this join between the selected table fields.'],
    ];
    queryTooltipMap.forEach(([selector, title]) => {
        const element = document.querySelector(selector);
        if (element && !element.getAttribute('title')) element.setAttribute('title', title);
    });
    document.querySelectorAll('#queryWorkspace [title], #queryTableList [title], #savedQueriesList [title]').forEach((element) => {
        // Do not overwrite Bootstrap behaviors such as data-bs-toggle="tab".
        // Tooltips are initialized explicitly below, so the data attribute is
        // unnecessary and would make the SQL/Results/WHERE tabs inert.
        element.setAttribute('data-bs-container', 'body');
        element.setAttribute('data-bs-custom-class', 'query-tooltip');
        if (window.bootstrap?.Tooltip) {
            // getOrCreateInstance never throws "more than one instance"; dispose
            // first so the tooltip picks up the options set just above.
            window.bootstrap.Tooltip.getInstance(element)?.dispose();
            window.bootstrap.Tooltip.getOrCreateInstance(element, { container: 'body', trigger: 'hover focus' });
        }
    });

    // Compact table finder for projects with large schemas.
    const tableSearch = document.getElementById('queryTableSearch');
    const tableCount = document.getElementById('queryTableCount');
    const tableEmpty = document.getElementById('queryTableSearchEmpty');
    const tableItems = Array.from(document.querySelectorAll('#queryTableList li'));
    const filterTables = () => {
        const term = (tableSearch?.value || '').trim().toLocaleLowerCase();
        let visible = 0;
        tableItems.forEach((item) => {
            const matches = !term || (item.dataset.table || item.textContent || '').toLocaleLowerCase().includes(term);
            item.hidden = !matches;
            if (matches) visible += 1;
        });
        if (tableCount) tableCount.textContent = term ? `${visible}/${tableItems.length}` : String(tableItems.length);
        if (tableEmpty) tableEmpty.hidden = visible !== 0;
    };
    tableSearch?.addEventListener('input', filterTables);

    // Saved queries remain docked at the bottom and open only when requested.
    const savedQueriesToggle = document.getElementById('savedQueriesToggle');
    const savedQueriesPanel = document.getElementById('savedQueriesPanel');
    savedQueriesToggle?.addEventListener('click', () => {
        const expanded = savedQueriesToggle.getAttribute('aria-expanded') === 'true';
        savedQueriesToggle.setAttribute('aria-expanded', String(!expanded));
        if (savedQueriesPanel) savedQueriesPanel.hidden = expanded;
    });

    // Mobile toggle
    const btnToggle = document.getElementById('btnToggleQueryList');
    const leftPanel = document.querySelector('.left-panel');
    if (btnToggle && leftPanel) {
        btnToggle.addEventListener('click', () => {
            leftPanel.classList.toggle('collapsed');
            btnToggle.innerHTML = leftPanel.classList.contains('collapsed')
                ? '<i class="bi bi-list"></i> Tables'
                : '<i class="bi bi-x-lg"></i> Hide';
        });
    }

    // Left panel click – load table preset
    document.querySelectorAll('#queryTableList li').forEach(li => {
        li.addEventListener('click', () => {
            const tableName = li.dataset.table;
            if (!tableName) return;
            document.querySelectorAll('#queryTableList li').forEach(l => l.classList.remove('active'));
            li.classList.add('active');
            const container = document.getElementById('diagramContainer');
            container.querySelectorAll('.diagram-table-card').forEach(c => c.remove());
            selectedTables = [];
            joins = [];
            document.getElementById('joinArrowsSvg').innerHTML = '';
            if (tablePresets[tableName]) {
                loadQueryState(tablePresets[tableName]);
            } else {
                addTableToDiagram(tableName, true);
                updateJoinTip();
            }
            const dropMsg = document.getElementById('diagramDropZone');
            if (dropMsg) dropMsg.style.display = 'none';
            currentTableName = tableName;
        });
        li.addEventListener('dragstart', (e) => {
            const tableName = li.dataset.table;
            if (tableName) {
                e.dataTransfer.setData('text/plain', tableName);
                e.dataTransfer.effectAllowed = 'copy';
            }
        });
    });

    // Drop zone for adding tables
    const dropZone = document.getElementById('diagramContainer');
    dropZone?.addEventListener('dragover', (e) => e.preventDefault());
    dropZone?.addEventListener('drop', (e) => {
        e.preventDefault();
        const tableName = e.dataTransfer.getData('text/plain').trim();
        if (tableName && !selectedTables.includes(tableName)) {
            addTableToDiagram(tableName, true);
            updateJoinTip();
            const dropMsg = document.getElementById('diagramDropZone');
            if (dropMsg) dropMsg.style.display = 'none';
        }
    });

    // Designer grid: individual row inputs self-wire (debounced) in designer.js.
    // Only the output checkbox isn't self-wired, so catch it here (delegated).
    const designerGrid = document.getElementById('designerGrid');
    designerGrid?.addEventListener('change', (e) => {
        if (!e.target.matches('.output-check')) return;
        scheduleGenerateSql();
        if (typeof autoSaveCurrentQueryState === 'function') autoSaveCurrentQueryState();
    });

    // DISTINCT / row-limit options regenerate the SQL and auto-save.
    ['queryDistinct', 'queryLimit'].forEach((id) => {
        document.getElementById(id)?.addEventListener('input', () => { generateSql(); if (typeof autoSaveCurrentQueryState === 'function') autoSaveCurrentQueryState(); });
        document.getElementById(id)?.addEventListener('change', () => { generateSql(); if (typeof autoSaveCurrentQueryState === 'function') autoSaveCurrentQueryState(); });
    });

    // Join modal save button (create or, when editing, replace by id).
    document.getElementById('btnSaveJoin')?.addEventListener('click', () => {
        const leftTable = document.getElementById('joinLeftTable').value;
        const leftField = document.getElementById('joinLeftField').value;
        const rightTable = document.getElementById('joinRightTable').value;
        const rightField = document.getElementById('joinRightField').value;
        const joinType = document.getElementById('joinTypeSelect').value;
        const editId = document.getElementById('joinEditId')?.value || '';
        joins = joins.filter(j => j.id !== editId && !(j.leftTable === leftTable && j.leftField === leftField && j.rightTable === rightTable && j.rightField === rightField));
        joins.push({ id: editId || ('join_' + Date.now()), type: joinType, leftTable, leftField, rightTable, rightField });
        drawJoinArrows();
        generateSql();
        if (typeof autoSaveCurrentQueryState === 'function') autoSaveCurrentQueryState();
        bootstrap.Modal.getInstance(document.getElementById('joinModal')).hide();
    });

    // Delete from within the (edit) join modal.
    document.getElementById('btnDeleteJoin')?.addEventListener('click', () => {
        const id = document.getElementById('joinEditId')?.value || '';
        if (id) { joins = joins.filter(j => j.id !== id); drawJoinArrows(); generateSql(); if (typeof autoSaveCurrentQueryState === 'function') autoSaveCurrentQueryState(); }
        bootstrap.Modal.getInstance(document.getElementById('joinModal'))?.hide();
    });

    // Double‑click a join arrow to EDIT it (change type, or delete).
    document.getElementById('joinArrowsSvg')?.addEventListener('dblclick', (e) => {
        const path = e.target.closest('path');
        if (!path || !path.dataset.joinId) return;
        const join = joins.find(j => j.id === path.dataset.joinId);
        if (!join) return;
        document.getElementById('joinEditId').value = join.id;
        document.getElementById('joinLeftTable').value = join.leftTable;
        document.getElementById('joinLeftField').value = join.leftField;
        document.getElementById('joinRightTable').value = join.rightTable;
        document.getElementById('joinRightField').value = join.rightField;
        document.getElementById('joinTypeSelect').value = join.type;
        document.getElementById('joinInfoLeft').textContent = `${join.leftTable}.${join.leftField}`;
        document.getElementById('joinInfoRight').textContent = `${join.rightTable}.${join.rightField}`;
        document.getElementById('joinModalTitle').textContent = 'Edit JOIN';
        document.getElementById('btnDeleteJoin').classList.remove('d-none');
        new bootstrap.Modal(document.getElementById('joinModal')).show();
    });

    // Load saved query
    document.querySelectorAll('#savedQueriesList li').forEach(li => {
        li.addEventListener('click', async () => {
            const queryName = li.dataset.query;
            if (!queryName) return;
            try {
                const res = await fetch(`${baseUrl}/project/${projectSlug}/query/load/${queryName}`);
                const data = await res.json();
                if (data.success) loadQueryState(data.query);
                else Swal.fire('Error', data.message, 'error');
            } catch (err) { Swal.fire('Error', 'Failed to load query.', 'error'); }
        });
    });

    // Delete saved query
    document.querySelectorAll('.delete-query-btn').forEach(btn => {
        btn.addEventListener('click', (e) => {
            e.stopPropagation();
            const queryName = btn.dataset.query;
            Swal.fire({ title: 'Delete query?', text: `"${queryName}" will be removed.`, icon: 'warning', showCancelButton: true, confirmButtonColor: '#dc3545', confirmButtonText: 'Delete' })
                .then(async (result) => {
                    if (result.isConfirmed) {
                        try {
                            const res = await fetch(`${baseUrl}/project/${projectSlug}/query/delete/${queryName}`, { method: 'POST' });
                            const data = await res.json();
                            if (data.success) Swal.fire('Deleted', data.message, 'success').then(() => location.reload());
                            else Swal.fire('Error', data.message, 'error');
                        } catch (err) { Swal.fire('Error', 'Failed to delete.', 'error'); }
                    }
                });
        });
    });

    // Sync SQL to Designer
    document.getElementById('btnSyncToDesigner')?.addEventListener('click', async () => {
        const sql = document.getElementById('sqlEditor').value.trim();
        if (!sql) return;
        try {
            const res = await fetch(`${baseUrl}/project/${projectSlug}/query/parse-sql`, {
                method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ sql })
            });
            const data = await res.json();
            if (!data.success) { Swal.fire('Error', data.message, 'error'); return; }
            const query = data.query;
            if (!query.fields || query.fields.length === 0) {
                Swal.fire({ title: 'Synced', text: 'Tables and joins updated. Fields preserved (wildcard *).', icon: 'info', timer: 2000 });
                query.fields = [];
            }
            const positions = [];
            document.querySelectorAll('.diagram-table-card').forEach(card => {
                positions.push({ table: card.dataset.table, left: parseInt(card.style.left), top: parseInt(card.style.top) });
            });
            query.positions = positions;
            await updateDesignerFromSql(query);
            if (query.fields && query.fields.length > 0) Swal.fire({ title: 'Synced', text: 'Designer updated from SQL.', icon: 'success', timer: 1500 });
        } catch (err) { Swal.fire('Error', 'Could not parse the SQL.', 'error'); }
    });

    // Auto‑sync when leaving SQL tab
    document.querySelector('[data-bs-target="#sqlPane"]')?.addEventListener('hide.bs.tab', async () => {
        const sql = document.getElementById('sqlEditor').value.trim();
        if (!sql) return;
        try {
            const res = await fetch(`${baseUrl}/project/${projectSlug}/query/parse-sql`, {
                method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ sql })
            });
            const data = await res.json();
            if (data.success && data.query) {
                const query = data.query;
                if (!query.fields || query.fields.length === 0) query.fields = [];
                const positions = [];
                document.querySelectorAll('.diagram-table-card').forEach(card => {
                    positions.push({ table: card.dataset.table, left: parseInt(card.style.left), top: parseInt(card.style.top) });
                });
                query.positions = positions;
                await updateDesignerFromSql(query);
            }
        } catch (err) { /* leaving the tab; a parse failure here is non-critical */ }
    });

    // Refresh Results with the current SQL whenever the Results tab is opened,
    // so manual edits in the SQL editor are always reflected.
    document.querySelector('[data-bs-target="#resultsPane"]')?.addEventListener('shown.bs.tab', () => {
        if (typeof autoExecuteSql === 'function') autoExecuteSql();
    });

    // Add field row button
    document.getElementById('btnAddFieldRow')?.addEventListener('click', addFieldRow);
    // Save query modal button
    document.getElementById('btnSaveQuery')?.addEventListener('click', () => new bootstrap.Modal(document.getElementById('saveQueryModal')).show());
    // Save as database View
    document.getElementById('btnSaveAsView')?.addEventListener('click', () => { if (typeof saveAsView === 'function') saveAsView(); });
    // Save confirmation
    document.getElementById('btnSaveQueryConfirm')?.addEventListener('click', saveQueryAs);
    // WHERE tab buttons
    document.getElementById('btnAddWhereTab')?.addEventListener('click', () => openWhereTabModal(-1));
    document.getElementById('btnSaveWhereTab')?.addEventListener('click', saveWhereTab);
    document.getElementById('btnPreviewWhere')?.addEventListener('click', previewWhere);
});

// ── Central Save & Auto‑save (projectSave event) ─────────────────────
// This accumulates states for ALL tables, not just the active one.
document.addEventListener('projectSave', (e) => {
    if (e.detail.module !== 'query') return;

    // Capture current state into allQueryStates (global cache defined in core.js)
    const fields = [];
    document.querySelectorAll('#designerRows tr').forEach(row => {
        fields.push({
            output: row.querySelector('.output-check').checked,
            field: queryRowField(row),
            alias: row.querySelector('.field-alias').value.trim(),
            sortType: row.querySelector('.sort-type').value,
            sortOrder: row.querySelector('.sort-order').value,
            groupBy: row.querySelector('.group-by').value,
            criteria: row.querySelector('.criteria').value.trim()
        });
    });
    const positions = [];
    document.querySelectorAll('.diagram-table-card').forEach(card => {
        positions.push({ table: card.dataset.table, left: parseInt(card.style.left), top: parseInt(card.style.top) });
    });

    // Update the cache for the current table
    allQueryStates[currentTableName] = {
        fields,
        tables: selectedTables,
        joins,
        sql: document.getElementById('sqlEditor').value,
        whereTabs,
        whereTabsShowAll,
        distinct: queryDistinct(),
        limit: queryLimit(),
        positions
    };

    // Send the entire cache as byTable
    e.detail.payload = {
        byTable: allQueryStates,
        activeTable: currentTableName
    };

    // Also update local tablePresets for immediate restore (optional)
    if (currentTableName) {
        tablePresets[currentTableName] = {
            fields, tables: selectedTables, joins,
            sql: document.getElementById('sqlEditor').value,
            whereTabs, positions
        };
    }
});

// Restore last active table if saved preset exists
if (typeof currentTableName !== 'undefined' && currentTableName && typeof tablePresets !== 'undefined' && tablePresets[currentTableName]) {
    setTimeout(() => {
        loadQueryState(tablePresets[currentTableName]);
        document.querySelectorAll('#queryTableList li').forEach(li => {
            if (li.dataset.table === currentTableName) li.classList.add('active');
        });
    }, 300);
}
