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, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;")
    .replace(/'/g, "&#39;");
}

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"));