Tampermonkey UserScript - Enhanced Editor for SuiteQL Query Tool with Metadata Fetching

tm_suiteQLHelper.js

// ==UserScript==
// @name         SuiteQL Editor Enhanced
// @namespace    https://netsuite.com
// @version      0.0.5
// @description  Enhanced CodeMirror Editor for SuiteQL Query Tool with Metadata Fetching
// @author       Lavi Yatziv
// @match        https://*.app.netsuite.com/*
// @grant        none
// @run-at       document-start
// @require      https://cdn.jsdelivr.net/npm/codemirror@5.65.18/lib/codemirror.min.js
// @require      https://cdn.jsdelivr.net/npm/codemirror@5.65.18/mode/sql/sql.js
// @require      https://cdn.jsdelivr.net/npm/codemirror@5.65.18/addon/hint/show-hint.js
// @require      https://cdn.jsdelivr.net/npm/codemirror@5.65.18/addon/hint/sql-hint.js
// @require      https://cdn.jsdelivr.net/npm/codemirror@5.65.18/addon/lint/lint.js
// @require      https://cdn.jsdelivr.net/npm/codemirror@5.65.18/addon/lint/lint.js
// @require      https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/sql-wasm.js

/*
Todo:
add Builtin.* Functions
    BUILTIN.CF
    BUILTIN.CONSOLIDATE
    BUILTIN.CURRENCY
    BUILTIN.CURRENCY_CONVERT
    BUILTIN.DF
    BUILTIN.HIERARCHY
    BUILTIN.MNFILTER
    BUILTIN.NAMED_GROUP
    BUILTIN.PERIOD
    BUILTIN.RELATIVE_RANGES

*/

// ==/UserScript==

(() => {
  "use strict";
  const accountId = document.location.hostname.split(".")[0];
  const serviceUrl = `https://${accountId}.app.netsuite.com`;

  // Cache for metadata to avoid repeated fetches
  const metadataCache = {};

  const loadStyles = (url) => {
    let link = document.createElement("link");
    link.type = "text/css";
    link.rel = "stylesheet";
    link.href = url;
    document.head.appendChild(link);
  };

  loadStyles(
    "https://cdn.jsdelivr.net/npm/codemirror@5.65.18/lib/codemirror.css",
  );
  loadStyles(
    "https://cdn.jsdelivr.net/npm/codemirror@5.65.18/theme/dracula.css",
  );
  loadStyles(
    "https://cdn.jsdelivr.net/npm/codemirror@5.65.18/addon/hint/show-hint.css",
  );
  loadStyles(
    "https://cdn.jsdelivr.net/npm/codemirror@5.65.18/addon/lint/lint.css",
  );

  async function sqlLinter(text, options) {
    //return new Promise((resolve) => {
    const errors = [];
    const lines = text.split("\n");

    const stack = []; // For tracking open/close brackets
    const brackets = { "(": ")", "[": "]", "{": "}" };
    const closingBrackets = new Set([")", "]", "}"]);

    let inString = false;
    let stringChar = null;
    let lastNonEmptyLine = -1;
    const selectRegex = /\bSELECT\b([\s\S]+?)\bFROM\b/gi; // Capture SELECT clause up to FROM

    for (let lineNumber = 0; lineNumber < lines.length; lineNumber++) {
      let line = lines[lineNumber].trim();

      if (line.length > 0) lastNonEmptyLine = lineNumber;

      for (let i = 0; i < line.length; i++) {
        let char = line[i];

        // Detect unescaped quotes (string handling)
        if (
          (char === "'" || char === '"') &&
          (!inString || stringChar === char)
        ) {
          if (inString && line[i - 1] !== "\\") {
            inString = false;
            stringChar = null;
          } else if (!inString) {
            inString = true;
            stringChar = char;
          }
        }

        // Track brackets
        if (!inString) {
          if (brackets[char]) {
            stack.push({ char, line: lineNumber, col: i });
          } else if (closingBrackets.has(char)) {
            if (
              stack.length === 0 ||
              brackets[stack[stack.length - 1].char] !== char
            ) {
              errors.push({
                message: `Unmatched closing bracket '${char}'`,
                severity: "error",
                from: CodeMirror.Pos(lineNumber, i),
                to: CodeMirror.Pos(lineNumber, i + 1),
              });
            } else {
              stack.pop();
            }
          }
        }
      }
    }

    // Unmatched opening brackets
    while (stack.length > 0) {
      let { char, line, col } = stack.pop();
      errors.push({
        message: `Unmatched opening bracket '${char}'`,
        severity: "error",
        from: CodeMirror.Pos(line, col),
        to: CodeMirror.Pos(line, col + 1),
      });
    }

    // Missing semicolon check (only on last non-empty line)
    if (
      lastNonEmptyLine !== -1 &&
      !lines[lastNonEmptyLine].trim().endsWith(";")
    ) {
      errors.push({
        message: "SQL statement should end with a semicolon.",
        severity: "warning",
        from: CodeMirror.Pos(lastNonEmptyLine, lines[lastNonEmptyLine].length),
        to: CodeMirror.Pos(lastNonEmptyLine, lines[lastNonEmptyLine].length),
      });
    }

    // Detect missing commas in SELECT clause
    let match;
    while ((match = selectRegex.exec(text)) !== null) {
      let selectClause = match[1].trim();
      let fromIndex = match.index + match[0].lastIndexOf("FROM");
      let selectIndex = match.index + match[0].indexOf("SELECT");
      
      // Split the SELECT clause by spaces, ignoring aliases (AS keyword) or SELECT directives
      let columns = selectClause
          .split(/\n+/)
          .map(trim)
          .filter(col => col.match(new RegExp(/(\w+.\d+){1}/g)) === null)

      for (let i = 0; i < columns.length; i++) {
        let word = columns[i].trim();
        const isLast = i + 1 === columns.length;
        if (!word || word.toUpperCase() === "AS") continue;
        if (isLast) {
          break;
        }
        if (!/([a-zA-Z0-9_. ]*\,)/.test(word)) {
          // Check if we have two consecutive identifiers without a comma
          // if (previousWasColumn && /^[a-zA-Z0-9_]+$/.test(word)) {
          errors.push({
            message:
              "Possible missing comma between column names in SELECT clause.",
            severity: "error",
            from: CodeMirror.Pos(
              text.substring(0, selectIndex).split("\n").length - 1,
              0,
            ),
            to: CodeMirror.Pos(
              text.substring(0, fromIndex).split("\n").length - 1,
              selectClause.length,
            ),
          });
          break;
        }
      }
    }

    // Return errors
    return errors;
    //});
  }

  document.addEventListener("DOMContentLoaded", async () => {
    let textarea = document.querySelector("textarea#query"); // document.getElementById("query");
    if (!textarea) return;

    let editorContainer = document.createElement("div");
    textarea.parentNode.insertBefore(editorContainer, textarea);
    textarea.style.display = "none";

    // Fetch metadata for autocomplete
    async function fetchMetadata(recordType) {
      try {
        console.log("Fetching metadata for:", recordType);
        const response = await fetch(
          `${serviceUrl}/app/recordscatalog/rcendpoint.nl?action=getRecordTypeDetail&data=${encodeURIComponent(JSON.stringify({ scriptId: recordType, detailType: "SS_ANAL" }))}`,
        );

        if (!response.ok) {
          console.error("Failed to fetch metadata:", response.status);
          return [];
        }

        const json = await response.json();
        if (json.error) {
          console.error("API returned error:", json.error);
          return [];
        }

        if (json.data && json.data.fields) {
          return json.data.fields.map((field) => field.id);
        } else {
          console.warn("Unexpected API response format:", json);
          return [];
        }
      } catch (error) {
        console.error("Error fetching metadata:", error);
        return [];
      }
    }

    // Store original SQL hint function
    const originalSqlHint = CodeMirror.hint.sql;

    // Custom SQL-hint function that incorporates NetSuite metadata
    async function customSqlHint(editor) {
      console.log("customSqlHint", { editor });
      const cursor = editor.getCursor();
      const token = editor.getTokenAt(cursor);
      const tokenString = token.string.trim();
      const lineUntilCursor = editor
        .getLine(cursor.line)
        .substring(0, cursor.ch);

      // Try to find the table name using regex
      const fromMatch = lineUntilCursor.match(/FROM\s+(\w+)/i);
      let recordType = fromMatch
        ? fromMatch[1]
        : lineUntilCursor.trim().replace(".", "");
      console.log({ lineUntilCursor, fromMatch, recordType });

      // If we're typing after a dot, look for the table alias
      const dotMatch = lineUntilCursor.match(/(\w+)\.\s*\w*$/);
      if (dotMatch) {
        const alias = dotMatch[1];

        // Try to find what table this alias refers to
        const fullText = editor.getValue();
        const aliasMatch = fullText.match(
          new RegExp(`FROM\\s+(\\w+)(?:\\s+as)?\\s+${alias}`, "i"),
        );
        if (aliasMatch) {
          recordType = aliasMatch[1];
        }
      }

      if (!recordType || !dotMatch) {
        // Use default CodeMirror SQL hints if we can't determine the table
        // or we're not in a context where we need field names
        // IMPORTANT: Use the stored original function to prevent recursion
        return originalSqlHint(editor);
      }

      try {
        let fields;

        if (metadataCache[recordType]) {
          fields = metadataCache[recordType];
        } else {
          fields = await fetchMetadata(recordType);
          metadataCache[recordType] = fields;
        }

        // Filter fields based on what's being typed
        let filteredFields = fields;
        if (tokenString && token.type !== "whitespace") {
          filteredFields = fields.filter((field) =>
            field.toLowerCase().includes(tokenString.toLowerCase()),
          );
        }

        return {
          list: filteredFields.length ? filteredFields : fields,
          from: CodeMirror.Pos(cursor.line, token.start + 1),
          to: CodeMirror.Pos(cursor.line, token.end),
        };
      } catch (error) {
        console.error("Error with hints:", error);
        // Fallback to default SQL hints
        return originalSqlHint(editor);
      }
    }

    // Store the original hint before overriding
    const originalSqlHintRef = CodeMirror.hint.sql;

    // Override with our custom hint function
    CodeMirror.hint.sql = customSqlHint;

    // register SQL Linter
    CodeMirror.registerHelper("lint", "sql", sqlLinter);

    // fetch tables here?
    // OR a dynamic typeahead
    // console.log(temp.getFields().map(fieldId => {
    // const tempField = temp.getField(fieldId)
    // if (tempField && tempField.label !== "" && tempField.isMandatory){
    // return tempField.id
    // }
    // }).filter(Boolean))
    // tables schema
    // CodeMirror.commands.autocomplete = function(cm) {
    //    CodeMirror.showHint(cm, CodeMirror.hint.sql, {
    //        tables: {
    //            "table1": [ "col_A", "col_B", "col_C" ],
    //            "table2": [ "other_columns1", "other_columns2" ]
    //        }
    //    } );
    //}

    let editor = CodeMirror(editorContainer, {
      value: textarea.value,
      mode: "sql",
      theme: "dracula",
      lineNumbers: true,
      extraKeys: { "Ctrl-Space": "autocomplete" },
      hintOptions: {
        completeSingle: false,
        tables: {},
      },
      gutters: ["CodeMirror-lint-markers"],
      lint: {
        // getAnnotations: sqlLinter,
        // async: true,
        lintOnChange: true, // Set to false if you want to manually trigger linting
        highlightLines: true,
        selfContain: false, // Keeps tooltips inside editor bounds
      },
    });

    editorContainer.firstChild.style.height = "60vh";

    editor.on("change", () => {
      textarea.value = editor.getValue();
    });

    // Observe external changes to the textarea
    const observer = new MutationObserver(() => {
      if (textarea.value !== editor.getValue()) {
        editor.setValue(textarea.value);
      }
    });

    observer.observe(textarea, {
      childList: true,
      subtree: true,
      characterData: true,
      attributes: true,
    });

    // Toggle Button
    let toggleBtn = document.createElement("button");
    toggleBtn.innerText = "Toggle Editor";
    toggleBtn.style.cssText = "margin-top: 10px; display: block;";
    textarea.parentNode.insertBefore(toggleBtn, textarea);

    toggleBtn.addEventListener("click", () => {
      if (editorContainer.style.display === "none") {
        editorContainer.style.display = "block";
      } else {
        editorContainer.style.display = "none";
      }
    });

    // Auto-format SQL function
    function formatSQL() {
      let sql = editor.getValue();

      // Basic SQL formatting
      sql = sql
        // Add line breaks for common SQL keywords
        .replace(/\s*SELECT\s+/gi, "\nSELECT ")
        .replace(/\s*FROM\s+/gi, "\nFROM ")
        .replace(/\s*WHERE\s+/gi, "\nWHERE ")
        .replace(/\s*ORDER BY\s+/gi, "\nORDER BY ")
        .replace(/\s*GROUP BY\s+/gi, "\nGROUP BY ")
        .replace(/\s*HAVING\s+/gi, "\nHAVING ")
        .replace(/\s*INNER JOIN\s+/gi, "\nINNER JOIN ")
        .replace(/\s*LEFT JOIN\s+/gi, "\nLEFT JOIN ")
        .replace(/\s*RIGHT JOIN\s+/gi, "\nRIGHT JOIN ")
        .replace(/\s*FULL JOIN\s+/gi, "\nFULL JOIN ")
        .replace(/\s*JOIN\s+/gi, "\nJOIN ")
        // Add indentation for clauses
        .replace(/\n(FROM|WHERE|ORDER BY|GROUP BY|HAVING)/gi, "\n  $1")
        // Add commas at the end of lines where needed
        .replace(/,\s*/g, ",\n  ")
        .trim();

      editor.setValue(sql);
    }

    let formatBtn = document.createElement("button");
    formatBtn.innerText = "Format SQL";
    formatBtn.style.cssText = "margin-top: 10px; display: block;";
    textarea.parentNode.insertBefore(formatBtn, textarea);
    formatBtn.addEventListener("click", formatSQL);
  });
})();