app/static/script.js

let sessionId,
  editor,
  currentQuestions = [],
  currentQuestionIndex = 0,
  isSchemaVisible,
  isTableVisible,
  isHintVisible,
  isSolutionVisible;

async function init() {
  await new Promise((r) =>
    document.readyState === "complete"
      ? r()
      : window.addEventListener("load", r)
  );
  const sqlEditor = document.getElementById("sqlEditor");
  if (!sqlEditor) throw new Error("SQL Editor element not found");
  await new Promise((r) => setTimeout(r, 100));
  if (typeof ace === "undefined")
    throw new Error("Ace Editor library not loaded");
  editor = ace.edit("sqlEditor");
  editor.setTheme("ace/theme/monokai");
  editor.session.setMode("ace/mode/sql");
  editor.setOptions({ enableBasicAutocompletion: true, fontSize: "12px" });
  editor.setValue("SELECT * FROM customers;");
  const response = await fetch("/api/session", { method: "POST" });
  if (!response.ok) throw new Error("Failed to create session");
  sessionId = (await response.json()).session_id;
  const domainResponse = await fetch("/api/databases");
  if (!domainResponse.ok) throw new Error("Failed to fetch databases");
  const domains = (await domainResponse.json()).databases;
  const domainSelect = document.getElementById("domainSelect");
  domainSelect.innerHTML = '<option value="">Select Database</option>';
  domains.forEach((domain) => {
    const option = document.createElement("option");
    option.value = domain;
    option.textContent = domain.charAt(0).toUpperCase() + domain.slice(1);
    domainSelect.appendChild(option);
  });
  document
    .getElementById("loadSchemaBtn")
    .addEventListener("click", loadDomain);
  document
    .getElementById("showSchemaBtn")
    .addEventListener("click", showSchema);
  document.getElementById("showTableBtn").addEventListener("click", showTable);
  document
    .getElementById("difficultySelect")
    .addEventListener("change", loadQuestions);
  document.getElementById("hintBtn").addEventListener("click", toggleHint);
  document
    .getElementById("solutionBtn")
    .addEventListener("click", toggleSolution);
  document.getElementById("prevBtn").addEventListener("click", prevQuestion);
  document.getElementById("nextBtn").addEventListener("click", nextQuestion);
  document.getElementById("runQueryBtn").addEventListener("click", runQuery);
}

async function loadDomain() {
  const domain = document.getElementById("domainSelect").value;
  if (!domain) {
    showError("Please select a database.");
    return;
  }
  const loadBtn = document.getElementById("loadSchemaBtn");
  loadBtn.disabled = true;
  loadBtn.textContent = "Loading...";
  try {
    const response = await fetch(`/api/load-schema/${domain}`, {
      method: "POST",
      headers: { "session-id": sessionId },
    });
    if (!response.ok)
      throw new Error(
        (await response.json()).detail || "Failed to load database"
      );
    await response.json();
    document.getElementById("difficultySelect").disabled = false;
    document.getElementById("showSchemaBtn").classList.remove("hidden");
    document.getElementById("showTableBtn").classList.remove("hidden");
    document.getElementById("schemaInfo").classList.add("hidden");
    document.getElementById("questionDetails").innerHTML = "";
    document.getElementById("hintBtn").style.display = "block";
    document.getElementById("solutionBtn").style.display = "block";
    currentQuestions = [];
    currentQuestionIndex = 0;
  } catch (e) {
    showError(`Failed to load database: ${e.message}.`);
  } finally {
    loadBtn.disabled = false;
    loadBtn.textContent = "Load Database";
  }
}

async function showSchema() {
  const domain = document.getElementById("domainSelect").value;
  if (!domain) return;
  const schemaInfo = document.getElementById("schemaInfo");
  if (isTableVisible) {
    isTableVisible = false;
    schemaInfo.classList.add("hidden");
  }
  schemaInfo.classList.toggle("hidden");
  isSchemaVisible = !schemaInfo.classList.contains("hidden");
  if (isSchemaVisible) {
    const schemaResponse = await fetch(`/api/schema/${domain}`, {
      headers: { "session-id": sessionId },
    });
    if (!schemaResponse.ok)
      throw new Error(
        (await schemaResponse.json()).detail || "Failed to load schema"
      );
    const schema = (await schemaResponse.json()).schema;
    let schemaHtml = '<div class="grid grid-cols-1 md:grid-cols-2 gap-4">';
    for (const [table, columns] of Object.entries(schema)) {
      schemaHtml += `<div class="bg-gray-100 p-2 rounded"><h3 class="font-semibold">${table}</h3><ul class="list-disc ml-4">`;
      columns.forEach(
        (col) => (schemaHtml += `<li>${col.name} (${col.type})</li>`)
      );
      schemaHtml += "</ul></div>";
    }
    schemaInfo.innerHTML = schemaHtml;
  }
}

async function showTable() {
  const domain = document.getElementById("domainSelect").value;
  if (!domain) return;
  const schemaInfo = document.getElementById("schemaInfo");
  if (isSchemaVisible) {
    isSchemaVisible = false;
    schemaInfo.classList.add("hidden");
  }
  schemaInfo.classList.toggle("hidden");
  isTableVisible = !schemaInfo.classList.contains("hidden");
  if (isTableVisible) {
    const sampleResponse = await fetch(`/api/sample-data/${domain}`, {
      headers: { "session-id": sessionId },
    });
    if (!sampleResponse.ok)
      throw new Error(
        (await sampleResponse.json()).detail || "Failed to load sample data"
      );
    const sampleData = (await sampleResponse.json()).sample_data;
    let tableHtml = '<div class="grid grid-cols-1 gap-4">';
    for (const [table, data] of Object.entries(sampleData)) {
      tableHtml += `<div class="bg-gray-50 p-2 rounded"><h4 class="font-semibold">${table}</h4>`;
      tableHtml += '<table class="w-full mt-2"><tr>';
      data.columns.forEach(
        (col) => (tableHtml += `<th class="border p-1">${col}</th>`)
      );
      tableHtml += "</tr>";
      data.rows.forEach((row) => {
        tableHtml += "<tr>";
        data.columns.forEach(
          (col) =>
            (tableHtml += `<td class="border p-1">${row[col] || "NULL"}</td>`)
        );
        tableHtml += "</tr>";
      });
      tableHtml += "</table></div>";
    }
    schemaInfo.innerHTML = tableHtml;
  }
}

async function loadQuestions() {
  const domain = document.getElementById("domainSelect").value;
  const difficulty = document.getElementById("difficultySelect").value;
  if (!domain) {
    document.getElementById("difficultySelect").value = "";
    alert("Please select and load a database first");
    return;
  }
  if (!difficulty) {
    document.getElementById("questionDetails").innerHTML = "";
    document.getElementById("hintBtn").style.display = "none";
    document.getElementById("solutionBtn").style.display = "none";
    document.getElementById("navButtons").style.display = "none";
    currentQuestions = [];
    currentQuestionIndex = 0;
    return;
  }
  const questionResponse = await fetch(
    `/api/questions/${domain}?difficulty=${difficulty}`
  );
  if (!questionResponse.ok)
    throw new Error(
      (await questionResponse.json()).detail || "Failed to load questions"
    );
  currentQuestions = await questionResponse.json();
  if (currentQuestions.length > 0) {
    currentQuestionIndex = 0;
    updateQuestionDisplay();
    document.getElementById("hintBtn").style.display = "block";
    document.getElementById("solutionBtn").style.display = "block";
    document.getElementById("navButtons").style.display = "flex";
  } else {
    document.getElementById("questionDetails").innerHTML =
      "<p>No questions available for this difficulty.</p>";
    document.getElementById("hintBtn").style.display = "none";
    document.getElementById("solutionBtn").style.display = "none";
    document.getElementById("navButtons").style.display = "none";
    currentQuestions = [];
    currentQuestionIndex = 0;
  }
}

function updateQuestionDisplay() {
  const questionDetails = document.getElementById("questionDetails");
  if (
    currentQuestions.length &&
    currentQuestionIndex >= 0 &&
    currentQuestionIndex < currentQuestions.length
  ) {
    const question = currentQuestions[currentQuestionIndex];
    questionDetails.innerHTML = `<p id="questionText"><strong>Practice Question:</strong> ${
      question.description || "No question available."
    }</p>`;
  } else {
    questionDetails.innerHTML =
      '<p id="questionText">No questions available.</p>';
  }
}

function prevQuestion() {
  if (currentQuestions.length && currentQuestionIndex > 0) {
    currentQuestionIndex--;
    updateQuestionDisplay();
    updateHintSolutionDisplay();
  }
}
function nextQuestion() {
  if (
    currentQuestions.length &&
    currentQuestionIndex < currentQuestions.length - 1
  ) {
    currentQuestionIndex++;
    updateQuestionDisplay();
    updateHintSolutionDisplay();
  }
}

function updateHintSolutionDisplay() {
  if (isHintVisible) toggleHint();
  if (isSolutionVisible) toggleSolution();
}

function toggleHint() {
  const question = currentQuestions[currentQuestionIndex];
  const hintBtn = document.getElementById("hintBtn");
  const questionDetails = document.getElementById("questionDetails");
  if (isSolutionVisible) toggleSolution();
  if (question && question.hint) {
    if (hintBtn.textContent === "Show Hint") {
      questionDetails.innerHTML += `<p><strong>Hint:</strong> ${question.hint}</p>`;
      hintBtn.textContent = "Hide Hint";
      isHintVisible = true;
    } else {
      questionDetails.innerHTML = questionDetails.innerHTML.replace(
        `<p><strong>Hint:</strong> ${question.hint}</p>`,
        ""
      );
      hintBtn.textContent = "Show Hint";
      isHintVisible = false;
    }
  } else {
    if (hintBtn.textContent === "Show Hint") {
      questionDetails.innerHTML +=
        '<p class="text-black">No hint available.</p>';
      hintBtn.textContent = "Hide Hint";
      isHintVisible = true;
    } else {
      questionDetails.innerHTML = questionDetails.innerHTML.replace(
        '<p class="text-black">No hint available.</p>',
        ""
      );
      hintBtn.textContent = "Show Hint";
      isHintVisible = false;
    }
  }
}

function toggleSolution() {
  const question = currentQuestions[currentQuestionIndex];
  const solutionBtn = document.getElementById("solutionBtn");
  const questionDetails = document.getElementById("questionDetails");
  if (isHintVisible) toggleHint();
  if (question && question.expected_sql) {
    if (solutionBtn.textContent === "Show Solution") {
      questionDetails.innerHTML += `<p><strong>Solution:</strong> <code>${question.expected_sql}</code></p>`;
      solutionBtn.textContent = "Hide Solution";
      isSolutionVisible = true;
    } else {
      questionDetails.innerHTML = questionDetails.innerHTML.replace(
        `<p><strong>Solution:</strong> <code>${question.expected_sql}</code></p>`,
        ""
      );
      solutionBtn.textContent = "Show Solution";
      isSolutionVisible = false;
    }
  } else {
    if (solutionBtn.textContent === "Show Solution") {
      questionDetails.innerHTML +=
        '<p class="text-black">No solution available.</p>';
      solutionBtn.textContent = "Hide Solution";
      isSolutionVisible = true;
    } else {
      questionDetails.innerHTML = questionDetails.innerHTML.replace(
        '<p class="text-black">No solution available.</p>',
        ""
      );
      solutionBtn.textContent = "Show Solution";
      isSolutionVisible = false;
    }
  }
}

async function runQuery() {
  const runBtn = document.getElementById("runQueryBtn");
  const resultsDiv = document.getElementById("results");
  let resultMessage =
    document.getElementById("resultMessage") || document.createElement("span");
  if (!resultMessage.id) {
    resultMessage.id = "resultMessage";
    runBtn.parentNode.appendChild(resultMessage);
  }
  runBtn.disabled = true;
  runBtn.textContent = "Running...";
  resultMessage.textContent = "";
  try {
    if (!editor) throw new Error("Editor not initialized. Refresh the page.");
    let query = editor.getValue().trim().toLowerCase();
    if (!query) throw new Error("Please enter a query.");
    const domain = document.getElementById("domainSelect").value;
    if (!domain) throw new Error("Please load a database first.");
    const schemaResponse = await fetch(`/api/schema/${domain}`, {
      headers: { "session-id": sessionId },
    });
    if (!schemaResponse.ok)
      throw new Error("Failed to load schema for table validation");
    const schema = (await schemaResponse.json()).schema;
    const validTables = Object.keys(schema).map((t) => t.toLowerCase());
    const tableNames = extractTableNames(query);
    if (tableNames.some((table) => !validTables.includes(table)))
      throw new Error(
        `Invalid table name. Use only: ${validTables.join(", ")}`
      );
    const response = await fetch("/api/run-query", {
      method: "POST",
      headers: { "Content-Type": "application/json", "session-id": sessionId },
      body: JSON.stringify({ query }),
    });
    if (!response.ok) {
      const errorText = await response.text();
      throw new Error(errorText || "Server error occurred.");
    }
    const result = await response.json();
    if (result.columns) {
      let html = `<table class="w-full border-collapse"><tr>${result.columns
        .map((col) => `<th class="border p-2">${col}</th>`)
        .join("")}</tr>`;
      html += result.rows
        .map(
          (row) =>
            `<tr>${result.columns
              .map((col) => `<td class="border p-2">${row[col] || "NULL"}</td>`)
              .join("")}</tr>`
        )
        .join("");
      html += "</table>";
      resultsDiv.innerHTML = html;
    } else {
      resultsDiv.innerHTML = "<p>No results</p>";
    }
    if (
      currentQuestions.length &&
      currentQuestionIndex >= 0 &&
      currentQuestionIndex < currentQuestions.length
    )
      await validateQuery(query, result, resultsDiv);
    else {
      resultMessage.textContent = "Select a question first";
      resultMessage.className = "text-red-500 ml-4";
    }
  } catch (e) {
    resultsDiv.innerHTML = "";
    resultMessage.textContent = e.message.includes("Internal Server Error")
      ? "Server error: Please check the query or try again later."
      : e.message;
    resultMessage.className = "text-red-500 ml-4";
  } finally {
    runBtn.disabled = false;
    runBtn.textContent = "Run";
  }
}

function extractTableNames(query) {
  const tables = new Set();
  const tokens = query.replace(/(\s+)/g, " ").split(" ");
  let inSubquery = false,
    inOpenQuery = false,
    inValues = false;

  for (let i = 0; i < tokens.length; i++) {
    const token = tokens[i].toLowerCase();
    if (token === "(" && !inSubquery && !inValues) {
      if (i > 0 && tokens[i - 1].toLowerCase() === "values") inValues = true;
      else inSubquery = true;
    }
    if (token === ")" && (inSubquery || inValues)) {
      if (
        inValues &&
        i + 1 < tokens.length &&
        tokens[i + 1].toLowerCase() === "as"
      )
        inValues = false;
      else if (inSubquery) inSubquery = false;
    }
    if (token === "openquery" && i + 1 < tokens.length && tokens[i + 1] === "(")
      inOpenQuery = true;
    if (token === ")" && inOpenQuery) inOpenQuery = false;
    if (inOpenQuery) continue;

    if (
      [
        "from",
        "join",
        "update",
        "delete",
        "insert",
        "into",
        "using",
        "apply",
        "pivot",
        "table",
      ].includes(token)
    ) {
      let nextToken = tokens[i + 1]
        ? tokens[i + 1].replace(/[,;)]/g, "").toLowerCase()
        : "";
      if (
        nextToken &&
        ![
          "select",
          "where",
          "on",
          "order",
          "group",
          "having",
          "as",
          "(",
        ].includes(nextToken)
      ) {
        if (i + 2 < tokens.length && tokens[i + 2].toLowerCase() === "as")
          nextToken = nextToken;
        else if (
          !["left", "right", "inner", "outer", "cross", "full"].includes(
            nextToken
          )
        )
          tables.add(nextToken);
      }
      i++;
    } else if (
      token === "merge" &&
      i + 1 < tokens.length &&
      tokens[i + 1].toLowerCase() === "into"
    ) {
      let nextToken = tokens[i + 2]
        ? tokens[i + 2].replace(/[,;)]/g, "").toLowerCase()
        : "";
      if (nextToken && !["using", "select", "where"].includes(nextToken))
        tables.add(nextToken);
      i += 2;
      while (i + 1 < tokens.length && tokens[i + 1].toLowerCase() !== "using")
        i++;
      if (i + 2 < tokens.length) {
        nextToken = tokens[i + 2].replace(/[,;)]/g, "").toLowerCase();
        if (nextToken && !["select", "where"].includes(nextToken))
          tables.add(nextToken);
      }
    } else if (
      token === "select" &&
      i + 1 < tokens.length &&
      tokens[i + 1].toLowerCase() === "into"
    ) {
      let nextToken = tokens[i + 2]
        ? tokens[i + 2].replace(/[,;)]/g, "").toLowerCase()
        : "";
      if (nextToken && !["from", "select"].includes(nextToken))
        tables.add(nextToken);
      i += 2;
      while (i + 1 < tokens.length && tokens[i + 1].toLowerCase() !== "from")
        i++;
      if (i + 2 < tokens.length) {
        nextToken = tokens[i + 2].replace(/[,;)]/g, "").toLowerCase();
        if (nextToken && !["where", "join"].includes(nextToken))
          tables.add(nextToken);
      }
    } else if (token === "with") {
      let cteStart = i + 1;
      while (i + 1 < tokens.length && tokens[i + 1].toLowerCase() !== "as") i++;
      if (i + 2 < tokens.length && tokens[i + 2] === "(") {
        let bracketCount = 1,
          subqueryStart = i + 2;
        while (i + 1 < tokens.length && bracketCount > 0) {
          i++;
          if (tokens[i] === "(") bracketCount++;
          if (tokens[i] === ")") bracketCount--;
        }
        const subquery = tokens.slice(subqueryStart, i).join(" ");
        tables.add(
          ...extractTableNames(subquery).filter((t) => !tables.has(t))
        );
      }
    } else if (
      token === "values" &&
      i + 1 < tokens.length &&
      tokens[i + 1] === "("
    ) {
      let aliasStart = i + 1;
      while (i + 1 < tokens.length && tokens[i + 1] !== "as") i++;
      if (i + 2 < tokens.length) {
        let alias = tokens[i + 2].replace(/[,;)]/g, "").toLowerCase();
        if (alias) tables.add(alias);
      }
    } else if (["exists", "in"].includes(token)) {
      let subqueryStart = i + 1;
      while (i + 1 < tokens.length && tokens[i + 1] !== ")") i++;
      if (i > subqueryStart) {
        const subquery = tokens.slice(subqueryStart, i + 1).join(" ");
        tables.add(
          ...extractTableNames(subquery).filter((t) => !tables.has(t))
        );
      }
    }
  }
  return Array.from(tables);
}

async function validateQuery(query, runResult, resultsDiv) {
  const question = currentQuestions[currentQuestionIndex];
  if (!question || !question.expected_sql) {
    showError("No question or expected SQL available for validation.");
    return;
  }
  const response = await fetch("/api/validate", {
    method: "POST",
    headers: { "Content-Type": "application/json", "session-id": sessionId },
    body: JSON.stringify({
      user_query: query,
      expected_query: question.expected_sql,
    }),
  });
  if (!response.ok)
    throw new Error((await response.json()).detail || "Failed to validate");
  const result = await response.json();
  const questionText = document.getElementById("questionText");
  const resultMessage = document.getElementById("resultMessage");
  if (result.valid) {
    questionText.classList.remove("text-red-500");
    questionText.classList.add("text-green-500");
    resultMessage.textContent = "Correct answer!";
    resultMessage.className = "text-green-500 ml-4";
    if (runResult.columns) {
      let html = `<table class="w-full border-collapse"><tr>${runResult.columns
        .map((col) => `<th class="border p-2">${col}</th>`)
        .join("")}</tr>`;
      html += runResult.rows
        .map(
          (row) =>
            `<tr>${runResult.columns
              .map((col) => `<td class="border p-2">${row[col] || "NULL"}</td>`)
              .join("")}</tr>`
        )
        .join("");
      html += "</table>";
      resultsDiv.innerHTML = html;
    } else {
      resultsDiv.innerHTML = "<p>No results</p>";
    }
  } else {
    questionText.classList.remove("text-green-500");
    questionText.classList.add("text-red-500");
    resultMessage.textContent = "Incorrect answer!";
    resultMessage.className = "text-red-500 ml-4";
    resultsDiv.innerHTML = "";
  }
}

function showError(message) {
  let errorMessage =
    document.getElementById("errorMessage") || document.createElement("span");
  if (!errorMessage.id) {
    errorMessage.id = "errorMessage";
    document.getElementById("runQueryBtn").parentNode.appendChild(errorMessage);
  }
  errorMessage.textContent = message;
  errorMessage.className = "text-red-500 mt-2";
}

window.onload = init;