// ── Designer Grid (field selection, SQL generation) ───────────────────
let designerSettingsCache = {};

// Group By / aggregate options for the designer grid. Values map 1:1 to what
// the server SQL builder understands (see QueryController::generateSql).
const GROUP_BY_OPTIONS = [
    ['', 'No'],
    ['Group By', 'Group By'],
    ['COUNT', 'Count'],
    ['COUNT DISTINCT', 'Count Distinct'],
    ['SUM', 'Sum'],
    ['AVG', 'Average'],
    ['MIN', 'Min'],
    ['MAX', 'Max'],
];
function groupByOptionsHtml(selected) {
    const sel = selected === 'Group By (aggregate)' ? 'Group By' : (selected || ''); // migrate legacy value
    return GROUP_BY_OPTIONS.map(([v, l]) => `<option value="${v}"${v === sel ? ' selected' : ''}>${l}</option>`).join('');
}

function updateDesignerFromDiagram() {
    const tbody = document.getElementById('designerRows');
    let hasField = false;

    // Snapshot current advanced settings from existing rows into the cache BEFORE
    // clearing the grid (otherwise there's nothing to read).
    document.querySelectorAll('#designerRows tr').forEach(row => {
        const fnEl = row.querySelector('.field-name');
        // The canonical identity is always the qualified `table.field` (kept in
        // data-qualified); the visible value may be the short field name.
        const fieldName = (fnEl.dataset.qualified || fnEl.value).trim();
        if (fieldName) {
            designerSettingsCache[fieldName] = {
                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()
            };
        }
    });
    tbody.innerHTML = '';

    document.querySelectorAll('#diagramContainer .field-check:checked').forEach(cb => {
        hasField = true;
        const table = cb.dataset.table;
        const field = cb.dataset.field;
        const fieldName = `${table}.${field}`;
        // Show the short field name when a single table is in play; qualify it
        // only when there are multiple tables (joins), where it's needed to
        // disambiguate. The full `table.field` is always kept in data-qualified.
        const displayName = selectedTables.length > 1 ? fieldName : field;
        const saved = designerSettingsCache[fieldName] || {};
        const row = document.createElement('tr');
        row.innerHTML = `
            <td><input type="checkbox" class="output-check" checked></td>
            <td><input type="text" class="form-control form-control-sm field-name" value="${displayName}" data-qualified="${fieldName}" title="${fieldName}" readonly></td>
            <td><input type="text" class="form-control form-control-sm field-alias" value="${saved.alias || ''}" placeholder="Alias"></td>
            <td><select class="form-select form-select-sm sort-type">
                <option value="">None</option>
                <option value="Ascending" ${saved.sortType === 'Ascending' ? 'selected' : ''}>Ascending</option>
                <option value="Descending" ${saved.sortType === 'Descending' ? 'selected' : ''}>Descending</option>
            </select></td>
            <td><input type="number" class="form-control form-control-sm sort-order" value="${saved.sortOrder || ''}" placeholder="#" min="1" style="width:60px;"></td>
            <td><select class="form-select form-select-sm group-by">${groupByOptionsHtml(saved.groupBy)}</select></td>
            <td><input type="text" class="form-control form-control-sm criteria" value="${saved.criteria || ''}" placeholder="e.g. > 100"></td>
            <td><button class="btn btn-sm btn-outline-danger remove-row">&times;</button></td>
        `;
        tbody.appendChild(row);
        row.querySelector('.remove-row').addEventListener('click', () => { row.remove(); generateSql(); });

        const aliasInput = row.querySelector('.field-alias');
        const sortTypeSelect = row.querySelector('.sort-type');
        const sortOrderInput = row.querySelector('.sort-order');
        const groupBySelect = row.querySelector('.group-by');
        const criteriaInput = row.querySelector('.criteria');

        const updateCache = () => {
            designerSettingsCache[fieldName] = {
                alias: aliasInput.value.trim(),
                sortType: sortTypeSelect.value,
                sortOrder: sortOrderInput.value,
                groupBy: groupBySelect.value,
                criteria: criteriaInput.value.trim()
            };
            scheduleGenerateSql();             // debounced SQL update (keystrokes)
            if (typeof autoSaveCurrentQueryState === 'function') {
                autoSaveCurrentQueryState();   // 🔥 INSTANT AUTO‑SAVE
            }
        };
        aliasInput.addEventListener('input', updateCache);
        sortTypeSelect.addEventListener('change', updateCache);
        sortOrderInput.addEventListener('input', updateCache);
        groupBySelect.addEventListener('change', updateCache);
        criteriaInput.addEventListener('input', updateCache);
    });
    if (!hasField) addFieldRow();
    generateSql();
}

function addFieldRow() {
    const tbody = document.getElementById('designerRows');
    const row = document.createElement('tr');
    row.innerHTML = `
        <td><input type="checkbox" class="output-check" checked></td>
        <td><input type="text" class="form-control form-control-sm field-name" placeholder="field or expression e.g. CONCAT(a,' ',b)"></td>
        <td><input type="text" class="form-control form-control-sm field-alias" placeholder="Alias"></td>
        <td><select class="form-select form-select-sm sort-type"><option value="">None</option><option value="Ascending">Ascending</option><option value="Descending">Descending</option></select></td>
        <td><input type="number" class="form-control form-control-sm sort-order" placeholder="#" min="1" style="width:60px;"></td>
        <td><select class="form-select form-select-sm group-by">${groupByOptionsHtml('')}</select></td>
        <td><input type="text" class="form-control form-control-sm criteria" placeholder="e.g. > 100"></td>
        <td><button class="btn btn-sm btn-outline-danger remove-row">&times;</button></td>
    `;
    tbody.appendChild(row);
    const fieldInput = row.querySelector('.field-name');
    const aliasInput = row.querySelector('.field-alias');
    const sortTypeSelect = row.querySelector('.sort-type');
    const sortOrderInput = row.querySelector('.sort-order');
    const groupBySelect = row.querySelector('.group-by');
    const criteriaInput = row.querySelector('.criteria');
    const removeBtn = row.querySelector('.remove-row');
    let currentField = '';

    const updateCache = () => {
        const newField = fieldInput.value.trim();
        if (newField && newField !== currentField) {
            if (currentField && designerSettingsCache[currentField]) delete designerSettingsCache[currentField];
            currentField = newField;
        }
        if (currentField) {
            designerSettingsCache[currentField] = {
                alias: aliasInput.value.trim(),
                sortType: sortTypeSelect.value,
                sortOrder: sortOrderInput.value,
                groupBy: groupBySelect.value,
                criteria: criteriaInput.value.trim()
            };
            scheduleGenerateSql();
            if (typeof autoSaveCurrentQueryState === 'function') {
                autoSaveCurrentQueryState();
            }
        }
    };
    fieldInput.addEventListener('input', updateCache);
    aliasInput.addEventListener('input', updateCache);
    sortTypeSelect.addEventListener('change', updateCache);
    sortOrderInput.addEventListener('input', updateCache);
    groupBySelect.addEventListener('change', updateCache);
    criteriaInput.addEventListener('input', updateCache);
    removeBtn.addEventListener('click', () => {
        if (currentField && designerSettingsCache[currentField]) delete designerSettingsCache[currentField];
        row.remove();
        generateSql();
        if (typeof autoSaveCurrentQueryState === 'function') autoSaveCurrentQueryState();
    });
}

async function generateSql() {
    const fields = [];
    document.querySelectorAll('#designerRows tr').forEach(row => {
        const output = row.querySelector('.output-check').checked;
        const fnEl = row.querySelector('.field-name');
        const field = (fnEl.dataset.qualified || fnEl.value).trim();
        const alias = row.querySelector('.field-alias').value.trim();
        const sortType = row.querySelector('.sort-type').value;
        const sortOrder = row.querySelector('.sort-order').value;
        const groupBy = row.querySelector('.group-by').value;
        const criteria = row.querySelector('.criteria').value.trim();
        if (field && output) {
            fields.push({ output, field, alias, sortType, sortOrder, groupBy, criteria });
        }
    });
    const payload = { fields, tables: selectedTables, joins, whereTabs: [], distinct: queryDistinct(), limit: queryLimit() };
    // Sequence rapid regenerations so an older response can't overwrite a newer one.
    if (_genAbort) _genAbort.abort();
    const ctrl = new AbortController();
    _genAbort = ctrl;
    try {
        const res = await fetch(`${baseUrl}/project/${projectSlug}/query/generate-sql`, {
            method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify(payload), signal: ctrl.signal
        });
        const data = await res.json();
        if (ctrl.signal.aborted) return;
        if (data.success) {
            currentSql = data.sql;
            if (typeof setSqlEditorValue === 'function') setSqlEditorValue(currentSql);
            else document.getElementById('sqlEditor').value = currentSql;
            if (typeof autoExecuteSql === 'function') autoExecuteSql();
            // Note: autoSaveCurrentQueryState is already called in updateCache, no need to call again
        }
    } catch (err) { if (!err || err.name !== 'AbortError') console.error('SQL generation failed', err); }
    finally { if (_genAbort === ctrl) _genAbort = null; }
}

// Debounced trigger for the fast-firing designer inputs (keystrokes) — cuts the
// per-character generate-sql + execute round-trips down to one burst.
let _genAbort = null, _genDebounce = null;
function scheduleGenerateSql() { clearTimeout(_genDebounce); _genDebounce = setTimeout(() => generateSql(), 220); }

async function updateDesignerFromSql(query) {
    const container = document.getElementById('diagramContainer');
    container.querySelectorAll('.diagram-table-card').forEach(c => c.remove());
    selectedTables = [];
    joins = [];
    tableFieldCache = {};
    document.getElementById('joinArrowsSvg').innerHTML = '';
    for (const tableName of (query.tables || [])) {
        await addTableToDiagram(tableName, false, true);
        const card = container.querySelector(`.diagram-table-card[data-table="${tableName}"]`);
        if (card) {
            const pos = (query.positions || []).find(p => p.table === tableName);
            if (pos) { card.style.left = pos.left + 'px'; card.style.top = pos.top + 'px'; }
            if (query.fields && query.fields.length) {
                query.fields.forEach(f => {
                    const parts = f.field.split('.');
                    if (parts[0] === tableName) {
                        const cb = card.querySelector(`.field-check[data-field="${parts[1]}"]`);
                        if (cb) cb.checked = !!f.output;
                    }
                });
            }
        }
    }
    // Clear cache and rebuild from query.fields
    designerSettingsCache = {};
    if (query.fields && query.fields.length) {
        query.fields.forEach(f => {
            if (f.output) {
                designerSettingsCache[f.field] = {
                    alias: f.alias || '',
                    sortType: f.sortType || '',
                    sortOrder: f.sortOrder || '',
                    groupBy: f.groupBy || '',
                    criteria: f.criteria || ''
                };
            }
        });
    }
    joins = query.joins || [];       // set BEFORE regenerating SQL, or the rebuilt SQL drops the joins
    updateDesignerFromDiagram();     // this will recreate rows using the cache (and generateSql with joins)
    setTimeout(() => drawJoinArrows(), 50);
    if (currentTableName && tablePresets) {
        tablePresets[currentTableName] = {
            fields: query.fields || [],
            tables: query.tables || [],
            joins: query.joins || [],
            sql: document.getElementById('sqlEditor').value,
            whereTabs: whereTabs,
            positions: query.positions || []
        };
    }
    const dropMsg = document.getElementById('diagramDropZone');
    if (dropMsg) dropMsg.style.display = 'none';
}

window.generateSql = generateSql;
window.scheduleGenerateSql = scheduleGenerateSql;
window.updateDesignerFromDiagram = updateDesignerFromDiagram;
window.addFieldRow = addFieldRow;
window.updateDesignerFromSql = updateDesignerFromSql;