<?php

namespace dokuwiki\plugin\sqlite;

class Tools
{
    /**
     * Split sql queries on semicolons, unless when semicolons are quoted
     * or inside BEGIN...END blocks (triggers)
     *
     * Usually you don't need this. It's only really needed if you need individual results for
     * multiple queries. For example in the admin interface.
     *
     * @param string $sql
     * @return string[] sql queries
     */
    public static function SQLstring2array($sql)
    {
        $statements = [];
        $len = strlen($sql);

        // State machine to "parse" sql into single statements
        $in_str = false;      // inside a string literal
        $in_com = false;      // inside a -- comment
        $in_block_com = false; // inside a /* */ comment
        $trigger_depth = 0;   // depth of trigger BEGIN...END blocks
        $case_depth = 0;      // depth of CASE...END blocks (to not confuse with trigger END)
        $statement = '';

        for ($i = 0; $i < $len; $i++) {
            $char = $sql[$i];
            $next = $i < ($len - 1) ? $sql[$i + 1] : '';

            // in block comment /* */? ignore everything until */
            if ($in_block_com) {
                if ($char == '*' && $next == '/') {
                    $in_block_com = false;
                    $i++; // skip the /
                }
                continue;
            }

            // in line comment? ignore everything until line end
            if ($in_com) {
                if ($char == "\n") {
                    $in_com = false;
                    // Add newline to statement to preserve formatting
                    $statement .= $char;
                }
                continue;
            }

            // handle strings
            if ($in_str) {
                if ($char == "'") {
                    if ($next == "'") {
                        // current char is an escape for the next
                        $statement .= $char . $next;
                        $i++;
                        continue;
                    } else {
                        // end of string
                        $statement .= $char;
                        $in_str = false;
                        continue;
                    }
                }
                // still in string
                $statement .= $char;
                continue;
            }

            // new block comment /* ?
            if ($char == '/' && $next == '*') {
                $in_block_com = true;
                $i++; // skip the *
                continue;
            }

            // new line comment? (anywhere, not just at line start)
            if ($char == '-' && $next == '-') {
                $in_com = true;
                $i++; // skip the second -
                continue;
            }

            // new string?
            if ($char == "'") {
                $in_str = true;
                $statement .= $char;
                continue;
            }

            // Check for CASE keyword (to track CASE...END blocks)
            if (strtoupper(substr($sql, $i, 4)) == 'CASE' &&
                ($i + 4 >= $len || !preg_match('/[A-Za-z0-9_]/', $sql[$i + 4]))) {
                $case_depth++;
                $statement .= substr($sql, $i, 4);
                $i += 3; // skip 'ASE', loop will advance past 'C'
                continue;
            }

            // Check for BEGIN keyword (for triggers)
            // Look for BEGIN followed by whitespace or newline
            if (strtoupper(substr($sql, $i, 5)) == 'BEGIN' &&
                ($i + 5 >= $len || preg_match('/\s/', $sql[$i + 5]))) {
                // Check if this is part of a CREATE TRIGGER
                $trimmed = strtoupper(trim($statement));
                if (strpos($trimmed, 'CREATE TRIGGER') !== false ||
                    strpos($trimmed, 'CREATE TEMP TRIGGER') !== false ||
                    strpos($trimmed, 'CREATE TEMPORARY TRIGGER') !== false) {
                    $trigger_depth++;
                }
                $statement .= substr($sql, $i, 5);
                $i += 4; // skip 'EGIN', loop will advance past 'B'
                continue;
            }

            // Check for END keyword
            if (strtoupper(substr($sql, $i, 3)) == 'END' &&
                ($i + 3 >= $len || !preg_match('/[A-Za-z0-9_]/', $sql[$i + 3]))) {
                $statement .= substr($sql, $i, 3);
                $i += 2; // skip 'ND', loop will advance past 'E'

                // Decrement case_depth first, then trigger_depth
                if ($case_depth > 0) {
                    $case_depth--;
                } elseif ($trigger_depth > 0) {
                    $trigger_depth--;
                }
                continue;
            }

            // the real delimiter - but NOT inside trigger BEGIN...END blocks
            if ($char == ';' && $trigger_depth == 0) {
                $statements[] = trim($statement);
                $statement = '';
                continue;
            }

            // some standard query stuff
            $statement .= $char;
        }
        if (trim($statement)) $statements[] = trim($statement);

        return array_filter($statements); // remove empty statements
    }
}
