// ── Execute SQL & Display Results ────────────────────────────────────
function qEsc(v) {
    return String(v == null ? '' : v)
        .replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;')
        .replace(/"/g, '&quot;').replace(/'/g, '&#39;');
}

let lastQueryResult = null;     // { columns, rows } for export
let queryPage = 1;              // current preview page (1-based)
let queryPerPage = 100;         // 0 = no paging (uses the server's 1000-row cap)
let explaining = false;         // last run was an EXPLAIN plan
let _execAbort = null;          // in-flight execute request (for sequencing)

async function executeSql() {
    const sql = document.getElementById('sqlEditor').value.trim();
    const resContainer = document.getElementById('resultsContainer');
    if (!sql) {
        resContainer.innerHTML = '<div class="text-muted small py-2">No SQL to execute.</div>';
        return false;
    }
    // Cancel any in-flight request so out-of-order responses can't render stale rows.
    if (_execAbort) _execAbort.abort();
    const ctrl = new AbortController();
    _execAbort = ctrl;
    resContainer.innerHTML = '<div class="text-center py-3"><div class="spinner-border spinner-border-sm"></div></div>';
    try {
        const body = { sql };
        if (!explaining && queryPerPage > 0) { body.page = queryPage; body.perPage = queryPerPage; }
        const res = await fetch(`${baseUrl}/project/${projectSlug}/query/execute`, {
            method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify(body), signal: ctrl.signal
        });
        const data = await res.json();
        if (ctrl.signal.aborted) return false;   // a newer request superseded us
        if (data.success) { renderResults(data); return true; }
        resContainer.innerHTML = `<div class="alert alert-danger small">${qEsc(data.message)}</div>`;
        return false;
    } catch (err) {
        if (err && err.name === 'AbortError') return false;
        resContainer.innerHTML = '<div class="alert alert-danger small">Execution failed.</div>';
        return false;
    } finally {
        if (_execAbort === ctrl) _execAbort = null;
    }
}

function renderResults(data) {
    const { columns, rows, time, count } = data;
    const container = document.getElementById('resultsContainer');
    if (!columns || columns.length === 0) {
        lastQueryResult = null;
        container.innerHTML = '<div class="text-muted small py-2">Query ran successfully — no rows returned.</div>';
        return;
    }
    lastQueryResult = { columns, rows };

    let meta = explaining ? 'Query plan' : `${count} row(s) in ${time}s`;
    if (data.truncated) meta += ` — showing first ${count}`;

    // Toolbar: meta + paging (when applicable) + export/explain actions.
    let bar = '<div class="d-flex flex-wrap align-items-center justify-content-between gap-2 mb-1">';
    bar += `<span class="small text-muted">${qEsc(meta)}</span>`;
    bar += '<div class="d-flex flex-wrap align-items-center gap-1">';
    if (!explaining && data.perPage > 0) {
        bar += '<div class="btn-group btn-group-sm me-1" role="group">';
        bar += `<button type="button" class="btn btn-outline-secondary py-0 px-2" id="qResPrev" ${data.page <= 1 ? 'disabled' : ''}><i class="bi bi-chevron-left"></i></button>`;
        bar += `<span class="btn btn-outline-secondary py-0 px-2 disabled">Page ${data.page}</span>`;
        bar += `<button type="button" class="btn btn-outline-secondary py-0 px-2" id="qResNext" ${data.hasMore ? '' : 'disabled'}><i class="bi bi-chevron-right"></i></button>`;
        bar += '</div>';
    }
    if (!explaining) {
        bar += '<select class="form-select form-select-sm py-0 me-1" id="qResPerPage" style="width:auto;" title="Rows per page">';
        [25, 50, 100, 250].forEach(n => bar += `<option value="${n}" ${queryPerPage === n ? 'selected' : ''}>${n}/page</option>`);
        bar += `<option value="0" ${queryPerPage === 0 ? 'selected' : ''}>All</option>`;
        bar += '</select>';
        bar += '<button type="button" class="btn btn-sm btn-outline-secondary py-0 px-2" id="qResExplain" title="Show the database query plan"><i class="bi bi-diagram-2 me-1"></i>Explain</button>';
        bar += '<div class="btn-group btn-group-sm">';
        bar += '<button type="button" class="btn btn-outline-secondary py-0 px-2" id="qResCsv"><i class="bi bi-filetype-csv me-1"></i>CSV</button>';
        bar += '<button type="button" class="btn btn-outline-secondary py-0 px-2" id="qResJson">JSON</button>';
        bar += '<button type="button" class="btn btn-outline-secondary py-0 px-2" id="qResXls">Excel</button>';
        bar += '</div>';
    } else {
        bar += '<button type="button" class="btn btn-sm btn-outline-secondary py-0 px-2" id="qResBack"><i class="bi bi-arrow-left me-1"></i>Back to results</button>';
    }
    bar += '</div></div>';

    // Data grid (every cell escaped — DB values are untrusted).
    let html = bar + '<div class="table-responsive"><table class="table table-sm table-bordered small"><thead><tr>';
    columns.forEach(col => html += `<th>${qEsc(col)}</th>`);
    html += '</tr></thead><tbody>';
    rows.forEach(row => {
        html += '<tr>';
        columns.forEach(col => html += `<td>${qEsc(row[col])}</td>`);
        html += '</tr>';
    });
    html += '</tbody></table></div>';
    container.innerHTML = html;

    // Wire the toolbar.
    container.querySelector('#qResPrev')?.addEventListener('click', () => { queryPage = Math.max(1, queryPage - 1); executeSql(); });
    container.querySelector('#qResNext')?.addEventListener('click', () => { queryPage += 1; executeSql(); });
    container.querySelector('#qResPerPage')?.addEventListener('change', (e) => { queryPerPage = parseInt(e.target.value, 10) || 0; queryPage = 1; executeSql(); });
    container.querySelector('#qResCsv')?.addEventListener('click', exportResultsCsv);
    container.querySelector('#qResJson')?.addEventListener('click', exportResultsJson);
    container.querySelector('#qResXls')?.addEventListener('click', exportResultsExcel);
    container.querySelector('#qResExplain')?.addEventListener('click', () => { explaining = true; explainSql(); });
    container.querySelector('#qResBack')?.addEventListener('click', () => { explaining = false; executeSql(); });
}

// EXPLAIN the current SQL (the read-only guard permits EXPLAIN).
async function explainSql() {
    const sql = document.getElementById('sqlEditor').value.trim();
    const container = document.getElementById('resultsContainer');
    if (!sql) { explaining = false; return; }
    container.innerHTML = '<div class="text-center py-3"><div class="spinner-border spinner-border-sm"></div></div>';
    try {
        const res = await fetch(`${baseUrl}/project/${projectSlug}/query/execute`, {
            method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ sql: 'EXPLAIN ' + sql })
        });
        const data = await res.json();
        if (data.success) { renderResults(data); }
        else { explaining = false; container.innerHTML = `<div class="alert alert-danger small">${qEsc(data.message)}</div>`; }
    } catch (err) { explaining = false; container.innerHTML = '<div class="alert alert-danger small">Explain failed.</div>'; }
}

function currentExportName(ext) {
    const t = (typeof currentTableName !== 'undefined' && currentTableName) ? currentTableName : 'results';
    return `query-${t}.${ext}`;
}
function download(name, blob) {
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url; a.download = name;
    document.body.appendChild(a); a.click(); a.remove();
    setTimeout(() => URL.revokeObjectURL(url), 1500);
}

function exportResultsCsv() {
    if (!lastQueryResult) return;
    const { columns, rows } = lastQueryResult;
    const cell = (v) => {
        let s = String(v == null ? '' : v);
        if (/^[=+\-@\t\r]/.test(s)) s = "'" + s;               // formula-injection guard
        return /[",\n\r]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s;
    };
    const lines = [columns.map(cell).join(',')];
    rows.forEach(r => lines.push(columns.map(c => cell(r[c])).join(',')));
    download(currentExportName('csv'), new Blob(['﻿' + lines.join('\r\n')], { type: 'text/csv;charset=utf-8' }));
}

function exportResultsJson() {
    if (!lastQueryResult) return;
    const { columns, rows } = lastQueryResult;
    const out = rows.map(r => { const o = {}; columns.forEach(c => o[c] = r[c]); return o; });
    download(currentExportName('json'), new Blob([JSON.stringify(out, null, 2)], { type: 'application/json' }));
}

// "Excel" = an HTML table with an .xls extension + Excel MIME. Excel opens it
// natively; no heavy XLSX library needed.
function exportResultsExcel() {
    if (!lastQueryResult) return;
    const { columns, rows } = lastQueryResult;
    let t = '<table border="1"><thead><tr>';
    columns.forEach(c => t += `<th>${qEsc(c)}</th>`);
    t += '</tr></thead><tbody>';
    rows.forEach(r => { t += '<tr>'; columns.forEach(c => t += `<td>${qEsc(r[c])}</td>`); t += '</tr>'; });
    t += '</tbody></table>';
    const doc = `<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"><head><meta charset="utf-8"></head><body>${t}</body></html>`;
    download(currentExportName('xls'), new Blob(['﻿' + doc], { type: 'application/vnd.ms-excel' }));
}

// Auto‑execute the current SQL without a click (resets to page 1).
async function autoExecuteSql() {
    const sql = document.getElementById('sqlEditor').value.trim();
    if (!sql) return;
    explaining = false;
    queryPage = 1;
    await executeSql();
}

window.executeSql = executeSql;
window.autoExecuteSql = autoExecuteSql;
