366 lines
12 KiB
JavaScript
366 lines
12 KiB
JavaScript
const form = document.getElementById("query-form");
|
|
const resultsPanel = document.getElementById("results");
|
|
const errorPanel = document.getElementById("error");
|
|
const warningEl = document.getElementById("warning");
|
|
const summaryEl = document.getElementById("summary");
|
|
const generatedSqlEl = document.getElementById("generated-sql");
|
|
const sanitizedBlock = document.getElementById("sanitized-block");
|
|
const sanitizedSqlEl = document.getElementById("sanitized-sql");
|
|
const rowCountEl = document.getElementById("row-count");
|
|
const tableWrapper = document.getElementById("table-wrapper");
|
|
const tableEl = document.getElementById("result-table");
|
|
const tableHeadEl = document.getElementById("result-thead");
|
|
const tableBodyEl = document.getElementById("result-tbody");
|
|
const errorMessageEl = document.getElementById("error-message");
|
|
const feedbackPanel = document.getElementById("feedback-panel");
|
|
const feedbackCorrectBtn = document.getElementById("feedback-correct");
|
|
const feedbackIncorrectBtn = document.getElementById("feedback-incorrect");
|
|
const feedbackStatus = document.getElementById("feedback-status");
|
|
const resetBtn = document.getElementById("reset-form");
|
|
const exportWrapper = document.getElementById("export-wrapper");
|
|
const exportBtn = document.getElementById("export-results");
|
|
const reportBtn = document.getElementById("export-report");
|
|
|
|
let latestResult = null;
|
|
|
|
function resetPanels() {
|
|
resultsPanel.hidden = true;
|
|
errorPanel.hidden = true;
|
|
warningEl.hidden = true;
|
|
warningEl.textContent = "";
|
|
summaryEl.textContent = "";
|
|
generatedSqlEl.textContent = "";
|
|
sanitizedBlock.hidden = true;
|
|
sanitizedSqlEl.textContent = "";
|
|
rowCountEl.textContent = "";
|
|
tableEl.hidden = true;
|
|
tableWrapper.hidden = true;
|
|
tableHeadEl.innerHTML = "";
|
|
tableBodyEl.innerHTML = "";
|
|
errorMessageEl.textContent = "";
|
|
feedbackPanel.hidden = true;
|
|
feedbackStatus.hidden = true;
|
|
feedbackStatus.textContent = "";
|
|
exportWrapper.hidden = true;
|
|
exportBtn.disabled = true;
|
|
if (reportBtn) {
|
|
reportBtn.disabled = true;
|
|
}
|
|
latestResult = null;
|
|
}
|
|
|
|
function renderTable(columns, rows) {
|
|
if (!columns || !rows || rows.length === 0) {
|
|
tableEl.hidden = true;
|
|
tableWrapper.hidden = true;
|
|
return false;
|
|
}
|
|
|
|
tableHeadEl.innerHTML = "";
|
|
tableBodyEl.innerHTML = "";
|
|
|
|
const headerRow = document.createElement("tr");
|
|
columns.forEach((col) => {
|
|
const th = document.createElement("th");
|
|
th.textContent = col;
|
|
headerRow.appendChild(th);
|
|
});
|
|
tableHeadEl.appendChild(headerRow);
|
|
|
|
rows.forEach((row) => {
|
|
const tr = document.createElement("tr");
|
|
columns.forEach((col) => {
|
|
const td = document.createElement("td");
|
|
const value = row[col];
|
|
td.textContent = value === null || value === undefined ? "" : String(value);
|
|
tr.appendChild(td);
|
|
});
|
|
tableBodyEl.appendChild(tr);
|
|
});
|
|
|
|
tableEl.hidden = false;
|
|
tableWrapper.hidden = false;
|
|
return true;
|
|
}
|
|
|
|
function toCsv(columns, rows) {
|
|
const escapeCell = (value) => {
|
|
if (value === null || value === undefined) {
|
|
return "";
|
|
}
|
|
const stringValue = String(value);
|
|
if (/[",\n]/.test(stringValue)) {
|
|
return `"${stringValue.replace(/"/g, '""')}"`;
|
|
}
|
|
return stringValue;
|
|
};
|
|
|
|
const header = columns.map(escapeCell).join(",");
|
|
const lines = rows.map((row) => columns.map((col) => escapeCell(row[col])).join(","));
|
|
return [header, ...lines].join("\n");
|
|
}
|
|
|
|
function downloadCsv(filename, columns, rows) {
|
|
const csvContent = toCsv(columns, rows);
|
|
const blob = new Blob([csvContent], { type: "text/csv;charset=utf-8;" });
|
|
const url = URL.createObjectURL(blob);
|
|
const link = document.createElement("a");
|
|
link.href = url;
|
|
link.download = filename;
|
|
document.body.appendChild(link);
|
|
link.click();
|
|
document.body.removeChild(link);
|
|
URL.revokeObjectURL(url);
|
|
}
|
|
|
|
form.addEventListener("submit", async (event) => {
|
|
event.preventDefault();
|
|
resetPanels();
|
|
|
|
const question = document.getElementById("question").value.trim();
|
|
const tablesValue = document.getElementById("tables").value.trim();
|
|
const execute = document.getElementById("execute").checked;
|
|
const maxRows = parseInt(document.getElementById("max-rows").value, 10) || 500;
|
|
|
|
if (!question) {
|
|
errorPanel.hidden = false;
|
|
errorMessageEl.textContent = "Please enter a question.";
|
|
return;
|
|
}
|
|
|
|
const payload = {
|
|
question,
|
|
tables: tablesValue ? tablesValue.split(",").map((s) => s.trim()).filter(Boolean) : undefined,
|
|
execute,
|
|
max_rows: maxRows,
|
|
feedback: null,
|
|
};
|
|
|
|
try {
|
|
const response = await fetch("/api/query", {
|
|
method: "POST",
|
|
headers: { "Content-Type": "application/json" },
|
|
body: JSON.stringify(payload),
|
|
});
|
|
|
|
if (!response.ok) {
|
|
const errorData = await response.json().catch(() => ({}));
|
|
throw new Error(errorData.detail || `Request failed with status ${response.status}`);
|
|
}
|
|
|
|
const data = await response.json();
|
|
|
|
summaryEl.textContent = data.summary || "(No summary provided)";
|
|
generatedSqlEl.textContent = data.sql || "";
|
|
|
|
if (data.llm_warning) {
|
|
warningEl.textContent = data.llm_warning;
|
|
warningEl.hidden = false;
|
|
}
|
|
|
|
if (data.sanitized_sql) {
|
|
sanitizedSqlEl.textContent = data.sanitized_sql;
|
|
sanitizedBlock.hidden = false;
|
|
}
|
|
|
|
if (typeof data.row_count === "number") {
|
|
rowCountEl.textContent = `Rows returned: ${data.row_count}`;
|
|
}
|
|
|
|
const hasRows = renderTable(data.columns, data.rows);
|
|
|
|
latestResult = {
|
|
question,
|
|
sql: data.sql || "",
|
|
summary: data.summary || "",
|
|
sanitized_sql: data.sanitized_sql || null,
|
|
columns: Array.isArray(data.columns) ? data.columns : [],
|
|
rows: Array.isArray(data.rows) ? data.rows : [],
|
|
warning: data.llm_warning || null,
|
|
rowCount: typeof data.row_count === "number" ? data.row_count : null,
|
|
};
|
|
feedbackPanel.hidden = false;
|
|
feedbackStatus.hidden = true;
|
|
feedbackStatus.textContent = "";
|
|
|
|
const canExportTable = hasRows && latestResult.rows.length > 0;
|
|
|
|
if (reportBtn) {
|
|
exportWrapper.hidden = false;
|
|
reportBtn.disabled = false;
|
|
exportBtn.disabled = !canExportTable;
|
|
} else if (canExportTable) {
|
|
exportWrapper.hidden = false;
|
|
exportBtn.disabled = false;
|
|
}
|
|
|
|
resultsPanel.hidden = false;
|
|
} catch (error) {
|
|
errorPanel.hidden = false;
|
|
errorMessageEl.textContent = error.message || String(error);
|
|
}
|
|
});
|
|
|
|
resetBtn.addEventListener("click", () => {
|
|
form.reset();
|
|
document.getElementById("tables").value = document.getElementById("tables").defaultValue;
|
|
document.getElementById("max-rows").value = document.getElementById("max-rows").defaultValue;
|
|
document.getElementById("execute").checked = document.getElementById("execute").defaultChecked;
|
|
resetPanels();
|
|
});
|
|
|
|
exportBtn.addEventListener("click", () => {
|
|
if (!latestResult || !latestResult.columns || latestResult.rows.length === 0) {
|
|
return;
|
|
}
|
|
const timestamp = new Date().toISOString().replace(/[:.]/g, "-");
|
|
const filename = `sql-agent-results-${timestamp}.csv`;
|
|
downloadCsv(filename, latestResult.columns, latestResult.rows);
|
|
});
|
|
|
|
function escapeHtml(value) {
|
|
if (value === null || value === undefined) {
|
|
return "";
|
|
}
|
|
return String(value)
|
|
.replace(/&/g, "&")
|
|
.replace(/</g, "<")
|
|
.replace(/>/g, ">")
|
|
.replace(/"/g, """)
|
|
.replace(/'/g, "'");
|
|
}
|
|
|
|
function buildReportHtml(result) {
|
|
const timestamp = new Date().toLocaleString();
|
|
const rowsHtml = result.rows
|
|
.map(
|
|
(row) =>
|
|
`<tr>${result.columns
|
|
.map((col) => `<td>${escapeHtml(row[col])}</td>`)
|
|
.join("")}</tr>`
|
|
)
|
|
.join("");
|
|
|
|
const tableSection =
|
|
result.columns.length && result.rows.length
|
|
? `<table><thead><tr>${result.columns
|
|
.map((col) => `<th>${escapeHtml(col)}</th>`)
|
|
.join("")}</tr></thead><tbody>${rowsHtml}</tbody></table>`
|
|
: `<p>No row data was returned.</p>`;
|
|
|
|
const sanitizedSection = result.sanitized_sql
|
|
? `<h3>Sanitized SQL</h3><pre>${escapeHtml(result.sanitized_sql)}</pre>`
|
|
: "";
|
|
|
|
const warningSection = result.warning
|
|
? `<div class="warning">${escapeHtml(result.warning)}</div>`
|
|
: "";
|
|
|
|
return `<!DOCTYPE html>
|
|
<html lang="en">
|
|
<head>
|
|
<meta charset="utf-8" />
|
|
<title>SQL Agent Report</title>
|
|
<style>
|
|
body { font-family: 'Segoe UI', sans-serif; margin: 2rem auto; max-width: 960px; color: #0f172a; }
|
|
h1, h2, h3 { color: #1e3a8a; }
|
|
.meta { margin-bottom: 1.5rem; }
|
|
.meta dt { font-weight: 600; }
|
|
.meta dd { margin: 0 0 0.75rem 0; }
|
|
pre { background: #0f172a; color: #f8fafc; padding: 1rem; border-radius: 8px; overflow-x: auto; }
|
|
table { width: 100%; border-collapse: collapse; margin-top: 1.5rem; }
|
|
th, td { border: 1px solid #e2e8f0; padding: 0.75rem; text-align: left; }
|
|
th { background: #e2e8f0; }
|
|
.warning { background: #fef3c7; border-left: 4px solid #f59e0b; padding: 0.75rem 1rem; border-radius: 8px; margin-bottom: 1.5rem; }
|
|
footer { margin-top: 2rem; font-size: 0.85rem; color: #475569; }
|
|
</style>
|
|
</head>
|
|
<body>
|
|
<h1>SQL Agent Report</h1>
|
|
<dl class="meta">
|
|
<dt>Generated</dt>
|
|
<dd>${escapeHtml(timestamp)}</dd>
|
|
<dt>Question</dt>
|
|
<dd>${escapeHtml(result.question)}</dd>
|
|
<dt>Summary</dt>
|
|
<dd>${escapeHtml(result.summary || '(No summary provided)')}</dd>
|
|
<dt>Rows Returned</dt>
|
|
<dd>${result.rowCount !== null ? escapeHtml(result.rowCount) : 'n/a'}</dd>
|
|
</dl>
|
|
${warningSection}
|
|
<h2>Generated SQL</h2>
|
|
<pre>${escapeHtml(result.sql)}</pre>
|
|
${sanitizedSection}
|
|
<h2>Result Preview</h2>
|
|
${tableSection}
|
|
<footer>Report generated by the SugarScale SQL Agent UI.</footer>
|
|
</body>
|
|
</html>`;
|
|
}
|
|
|
|
function downloadReport(filename, html) {
|
|
const blob = new Blob([html], { type: "text/html;charset=utf-8;" });
|
|
const url = URL.createObjectURL(blob);
|
|
const link = document.createElement("a");
|
|
link.href = url;
|
|
link.download = filename;
|
|
document.body.appendChild(link);
|
|
link.click();
|
|
document.body.removeChild(link);
|
|
URL.revokeObjectURL(url);
|
|
}
|
|
|
|
if (reportBtn) {
|
|
reportBtn.addEventListener("click", () => {
|
|
if (!latestResult) {
|
|
return;
|
|
}
|
|
const timestamp = new Date().toISOString().replace(/[:.]/g, "-");
|
|
const filename = `sql-agent-report-${timestamp}.html`;
|
|
const html = buildReportHtml(latestResult);
|
|
downloadReport(filename, html);
|
|
});
|
|
}
|
|
|
|
async function sendFeedback(tag) {
|
|
if (!latestResult) {
|
|
return;
|
|
}
|
|
|
|
const payload = {
|
|
question: latestResult.question,
|
|
sql: latestResult.sql,
|
|
summary: latestResult.summary,
|
|
sanitized_sql: latestResult.sanitized_sql,
|
|
feedback: tag,
|
|
};
|
|
|
|
feedbackStatus.hidden = false;
|
|
feedbackStatus.textContent = "Sending feedback...";
|
|
feedbackCorrectBtn.disabled = true;
|
|
feedbackIncorrectBtn.disabled = true;
|
|
|
|
try {
|
|
const response = await fetch("/api/feedback", {
|
|
method: "POST",
|
|
headers: { "Content-Type": "application/json" },
|
|
body: JSON.stringify(payload),
|
|
});
|
|
|
|
if (!response.ok) {
|
|
const errorData = await response.json().catch(() => ({}));
|
|
throw new Error(errorData.detail || `Feedback request failed with status ${response.status}`);
|
|
}
|
|
|
|
feedbackStatus.textContent = tag === "correct" ? "Marked as correct." : "Marked as needs work.";
|
|
} catch (error) {
|
|
feedbackStatus.textContent = error.message || String(error);
|
|
} finally {
|
|
feedbackCorrectBtn.disabled = false;
|
|
feedbackIncorrectBtn.disabled = false;
|
|
}
|
|
}
|
|
|
|
feedbackCorrectBtn.addEventListener("click", () => sendFeedback("correct"));
|
|
feedbackIncorrectBtn.addEventListener("click", () => sendFeedback("incorrect"));
|