
var innovaphone = innovaphone || {};
innovaphone.SqlConsole = innovaphone.SqlConsole || function (style, app, path) {
    this.createNode("div", style);
    const svgPlay = "<path d=\'M5,16.23V3.77a1.12,1.12,0,0,1,1.74-.93l8.73,5.81a1.13,1.13,0,0,1,.06,1.83L6.84,17.12A1.12,1.12,0,0,1,5,16.23Z'/>";
    const svgFormat = "<path d=\'M16,5H13V4h3Zm0,2H13V8h3Zm0,5H4v1H16Zm0,3H4v1H16ZM12.1,6,8,3.22V5H4V7H8V8.77Z'/>";
    const svgTable = "<path d=\'M3,3H7V7H3ZM8,3h4V7H8Zm5,0h4V7H13ZM3,8H7v4H3ZM8,8h4v4H8Zm5,0h4v4H13ZM3,13H7v4H3Zm5,0h4v4H8Zm5,0h4v4H13Z'/>";

    if (!path) path = "sql-console";
    var panel = this.add(new innovaphone.ui1.Div("position: absolute; left:0px; right:0px; top:0px; bottom:0px; display:flex; flex-direction:column"));
    var historyDiv = panel.add(new innovaphone.ui1.Div("position:relative; width:100%; height:120px; min-height:100px; flex-grow:2; overflow:auto; padding:2px; box-sizing:border-box; border:0; border-bottom:1px solid black"));
    var input = panel.add(new innovaphone.ui1.Node("textarea", "position:relative; width:100%; min-height:120px; flex-shrink:0; flex-grow:10; overflow:auto; padding:2px; resize:none; box-sizing:border-box; border:0; border-bottom:1px solid black")).addEvent("keydown", function (e) {
        if (e.keyCode == 13 && e.ctrlKey) {
            onplay();
            e.preventDefault();
        }
    });
    var control = panel.add(new innovaphone.ui1.Div("position:relative; width:100%; display:flex; flex-direction:row; border:0; border-bottom:1px solid black"));
    var play = control.add(new innovaphone.ui1.SvgInline("position:relative; width:20px; height:20px; margin:5px; fill:black; cursor:pointer", "0 0 20 20", svgPlay)).addEvent("click", onplay);
    var format = control.add(new innovaphone.ui1.SvgInline("position:relative; width:20px; height:20px; margin:5px; fill:black; cursor:pointer", "0 0 20 20", svgFormat)).addEvent("click", onformat);
    var tableCtrl = control.add(new innovaphone.ui1.SvgInline("position:relative; width:20px; height:20px; margin:5px; fill:black; cursor:pointer", "0 0 20 20", svgTable)).addEvent("click", ontable);
    var output = panel.add(new innovaphone.ui1.Div("position:relative; box-sizing:border-box; width:100%; flex-grow:20; overflow:auto"));
    var outTable = false;
    var history = [];

    Save.prototype = innovaphone.ui1.nodePrototype;
    var storage = localStorage.getItem(path);
    var restore = [];
    if (storage) restore = JSON.parse(storage);
    if (restore?.length) {
        restore.forEach((e) => {
            if (e.sql) history.push(historyDiv.add(new Save(e.sql)));
        });
        setTimeout(() => historyDiv.setScrollBottom(0), 100);
    }
    else {
        history.push(historyDiv.add(new Save("SELECT * FROM pg_catalog.pg_tables where schemaname = 'public'")));
    }

    function onplay() {
        output.clear();
        var table = null;
        var thead = null;
        var tbody = null;
        var found = history.find((e) => e.text == input.container.value);
        if (found) {
            history.splice(history.indexOf(found), 1);
            historyDiv.rem(found);
            found.close();
        }
        history.push(historyDiv.add(new Save(input.container.value)));
        setTimeout(() => historyDiv.setScrollBottom(0), 100);

        if (outTable) {
            table = output.add(new innovaphone.ui1.Node("table", "position:relative"));
        }

        app.sendSrcMore({ mt: "SqlConsoleExec", api: "com.innovaphone.sqlconsole", query: input.container.value }, function (msg) {
            if (msg.mt == "SqlConsoleRow") {
                if (table) {
                    if (!thead) {
                        thead = table.add(new innovaphone.ui1.Node("thead", "text-align:left"));
                        const trHead = thead.add(new innovaphone.ui1.Node("tr"));
                        Object.keys(msg.result).forEach(key => {
                            trHead.add(new innovaphone.ui1.Node("th", "", key));
                        });
                        tbody = table.add(new innovaphone.ui1.Node("tbody"));
                    }

                    const tr = tbody.add(new innovaphone.ui1.Node("tr"));
                    Object.values(msg.result).forEach(value => {
                        tr.add(new innovaphone.ui1.Node("td", "", value));
                    });
                }
                else {
                    output.add(new innovaphone.ui1.Div("position:relative; font-family:monospace; white-space:nowrap", JSON.stringify(msg.result) + ","));
                }
            }
            else if (msg.mt == "SqlConsoleExecResult") {
                output.add(new innovaphone.ui1.Div("position:relative; font-family:monospace", msg.error ? "Error: " + msg.error : "Total time: " + msg.total + "ms"));
            }
        });
        input.container.value = null;
        store();
    }

    function onformat() {
        input.container.value = formatSQL(input.container.value);
    }

    function ontable() {
        if (outTable) {
            outTable = false;
            tableCtrl.container.style.fill = "black";
        }
        else {
            outTable = true;
            tableCtrl.container.style.fill = "red";
        }
    }

    function formatSQL(query, indentLevel = 0) {
        const indent = ' '.repeat(4 * indentLevel);
        const nextIndent = ' '.repeat(4 * (indentLevel + 1));

        // Cleanup white spaces and prepare for formatting
        query = query.trim().replace(/\s+/g, ' ');

        // Process each character in the query
        let formattedQuery = '';
        let depth = 0;
        let inQuotes = false;
        let buffer = '';

        for (let i = 0; i < query.length; i++) {
            const char = query[i];
            const prevChar = query[i - 1];

            // Toggle quote state
            if (char === "'" && prevChar !== '\\') {
                inQuotes = !inQuotes;
            }

            // If in quotes, continue to add characters to buffer
            if (inQuotes) {
                buffer += char;
                continue;
            }

            // Handle open and close parentheses
            if (char === '(') {
                depth++;
                if (depth === 1) {
                    // Starting a new subquery
                    formattedQuery += buffer.trim() + char;
                    buffer = '';
                    continue;
                }
            }
            else if (char === ')') {
                depth--;
                if (depth === 0) {
                    // Closing a subquery, process its contents
                    formattedQuery += formatSQL(buffer.trim(), indentLevel + 1) + char;
                    buffer = '';
                    continue;
                }
            }

            buffer += char;

            // When not in a subquery or quotes, check for commas and keywords
            if (depth === 0) {
                const isKeyword = /\b(SELECT|FROM|WHERE|GROUP BY|ORDER BY)\b/gi.test(buffer);
                if (char === ',' || char === ';' || i === query.length - 1 || isKeyword) {
                    formattedQuery += formatLine(buffer.trim(), indent, nextIndent);
                    buffer = '';
                }
            }
        }

        // Add any remaining text
        if (buffer) {
            formattedQuery += formatLine(buffer.trim(), indent, nextIndent);
        }

        return formattedQuery.trim();

        function formatLine(line, indent, nextIndent) {
            return line
                .replace(/\b(SELECT|FROM|WHERE|GROUP BY|ORDER BY)\b/gi, `\n${indent}$1\n${nextIndent}`)
                .replace(/,\s*/g, `,\n${nextIndent}`);
        }
    }

    function store() {
        var save = [];
        history.forEach((e) => { save.push({ sql: e.text }) });
        localStorage.setItem(path, JSON.stringify(save));
    }

    function Save(text) {
        var that = this;
        this.createNode("div", "position:relative; display:flex");
        var rem = this.add(new innovaphone.ui1.Div("position:relative; margin:8px; width:12px; height:12px; flex-shrink:0; background-color:red"));
        rem.addEvent("click", onrem);
        var save = this.add(new innovaphone.ui1.Node("pre", "position:relative; cursor:pointer; margin:2px; font-size:16px; background-color:white", text));
        save.addEvent("click", onselect);
        this.text = text;
        this.close = () => {
            rem.remEvent(onrem);
            save.remEvent(onselect);
        };

        function onrem() {
            history.splice(history.indexOf(that), 1);
            historyDiv.rem(that);
            store();
        }

        function onselect() {
            input.container.value = text;
        }
    }
}
innovaphone.SqlConsole.prototype = innovaphone.ui1.nodePrototype;
