// ── SQL editor enhancer: CodeMirror + schema-aware autocomplete ───────
// Upgrades the plain #sqlEditor textarea to CodeMirror with SQL highlighting
// and column-aware completion drawn from the query's selected tables. Falls
// back silently to the plain textarea if the bundle isn't available. The
// underlying textarea stays in sync (cm.save on every change) so every existing
// reader of #sqlEditor.value keeps working.
(function () {
    let cm = null;

    const KEYWORDS = ('SELECT FROM WHERE GROUP BY ORDER BY HAVING LIMIT OFFSET JOIN LEFT RIGHT INNER OUTER FULL ON AS ' +
        'AND OR NOT IN LIKE BETWEEN IS NULL DISTINCT UNION ALL EXISTS CASE WHEN THEN ELSE END ASC DESC ' +
        'COUNT SUM AVG MIN MAX COALESCE CONCAT NOW CURDATE').split(/\s+/);

    // alias/table → real table map from FROM/JOIN in the current text.
    function refs(text) {
        const map = {};
        const re = /\b(?:from|join)\s+`?([a-z_][a-z0-9_]*)`?(?:\s+(?:as\s+)?`?([a-z_][a-z0-9_]*)`?)?/gi;
        let m;
        while ((m = re.exec(text))) {
            map[m[1].toLowerCase()] = m[1];
            if (m[2] && !/^(where|on|inner|left|right|outer|full|join|group|order|having|limit|union|as)$/i.test(m[2])) {
                map[m[2].toLowerCase()] = m[1];
            }
        }
        return map;
    }

    function columnsOf(table) {
        const cols = (typeof tableFieldCache !== 'undefined' && tableFieldCache[table]) || [];
        return cols.map(c => c.name || c.COLUMN_NAME || '').filter(Boolean);
    }

    function allTables() {
        return (typeof selectedTables !== 'undefined' ? selectedTables : []).slice();
    }

    function sqlHint(cmInst) {
        const cur = cmInst.getCursor();
        const line = cmInst.getLine(cur.line).slice(0, cur.ch);
        const refMap = refs(cmInst.getValue());

        // Dot-completion: alias. / table. → that table's columns.
        const dot = /([a-z_][a-z0-9_]*)\.([a-z0-9_]*)$/i.exec(line);
        if (dot) {
            const table = refMap[dot[1].toLowerCase()] || dot[1];
            const pref = dot[2].toLowerCase();
            const list = columnsOf(table).filter(c => c.toLowerCase().indexOf(pref) === 0);
            if (!list.length) return null;
            return { list, from: { line: cur.line, ch: cur.ch - dot[2].length }, to: cur };
        }

        const tok = cmInst.getTokenAt(cur);
        const start = /[\w]/.test(tok.string || '') ? tok.start : cur.ch;
        const word = cmInst.getRange({ line: cur.line, ch: start }, cur);
        if (!word) return null;
        const lw = word.toLowerCase();

        const tables = allTables();
        const usedTables = Object.values(refMap);
        const colSet = {};
        (usedTables.length ? usedTables : tables).forEach(t => columnsOf(t).forEach(c => { colSet[c.toLowerCase()] = c; }));

        const seen = {}, list = [];
        tables.forEach(t => { const x = t.toLowerCase(); if (x.indexOf(lw) === 0 && x !== lw && !seen[x]) { seen[x] = 1; list.push(t); } });
        Object.values(colSet).forEach(c => { const x = c.toLowerCase(); if (x.indexOf(lw) === 0 && x !== lw && !seen[x]) { seen[x] = 1; list.push(c); } });
        KEYWORDS.forEach(k => { const x = k.toLowerCase(); if (x.indexOf(lw) === 0 && x !== lw && !seen['k' + x]) { seen['k' + x] = 1; list.push(k); } });
        return list.length ? { list: list.slice(0, 40), from: { line: cur.line, ch: start }, to: cur } : null;
    }

    function mount() {
        if (cm) { cm.refresh(); return; }
        const ta = document.getElementById('sqlEditor');
        const CM = window.__PPCM || window.CodeMirror;
        if (!ta || !CM || typeof CM.fromTextArea !== 'function') return; // graceful fallback
        try {
            cm = CM.fromTextArea(ta, {
                mode: 'text/x-sql', lineNumbers: true, lineWrapping: true, viewportMargin: Infinity,
                matchBrackets: true, autoCloseBrackets: true,
                hintOptions: { hint: sqlHint, completeSingle: false },
                extraKeys: { 'Ctrl-Space': c => c.showHint(), 'Cmd-Space': c => c.showHint() },
            });
        } catch (e) { return; }
        cm.getWrapperElement().classList.add('sqled-viewcm');
        cm.setSize('100%', 260);
        cm.on('change', () => cm.save()); // keep the hidden textarea current for all readers
        let t = null;
        cm.on('inputRead', (c, chg) => {
            if (c.state.completionActive) return;
            const s = (chg.text && chg.text[0]) || '';
            if (s !== '.' && !/[\w]/.test(s)) return;
            clearTimeout(t);
            t = setTimeout(() => { if (!c.state.completionActive) c.showHint(); }, 150);
        });
        window.__querySqlCm = cm;
    }

    // Writers of the SQL editor must go through this so CM stays authoritative.
    window.setSqlEditorValue = function (v) {
        if (cm) { cm.setValue(v || ''); }
        else { const ta = document.getElementById('sqlEditor'); if (ta) ta.value = v || ''; }
    };

    document.addEventListener('DOMContentLoaded', () => {
        // Mount when the SQL tab is first shown (a hidden pane sizes CM to 0).
        document.querySelector('[data-bs-target="#sqlPane"]')?.addEventListener('shown.bs.tab', mount);
    });
})();
