package gudusoft.gsqlparser.parser;

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TBaseType;
import gudusoft.gsqlparser.TCustomLexer;
import gudusoft.gsqlparser.TCustomParser;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.TLexerSnowflake;
import gudusoft.gsqlparser.TParserSnowflake;
import gudusoft.gsqlparser.TSourceToken;
import gudusoft.gsqlparser.TSourceTokenList;
import gudusoft.gsqlparser.TStatementList;
import gudusoft.gsqlparser.TSyntaxError;
import gudusoft.gsqlparser.EFindSqlStateType;
import gudusoft.gsqlparser.ETokenType;
import gudusoft.gsqlparser.ETokenStatus;
import gudusoft.gsqlparser.ESqlStatementType;
import gudusoft.gsqlparser.EErrorType;
import gudusoft.gsqlparser.stmt.TUnknownSqlStatement;
import gudusoft.gsqlparser.stmt.TCommonStoredProcedureSqlStatement;
import gudusoft.gsqlparser.stmt.oracle.TSqlplusCmdStatement;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreatePackage;
import gudusoft.gsqlparser.stmt.snowflake.TCreateTaskStmt;
import gudusoft.gsqlparser.sqlcmds.ISqlCmds;
import gudusoft.gsqlparser.sqlcmds.SqlCmdsFactory;
import gudusoft.gsqlparser.compiler.TContext;
import gudusoft.gsqlparser.sqlenv.TSQLEnv;
import gudusoft.gsqlparser.compiler.TGlobalScope;
import gudusoft.gsqlparser.compiler.TFrame;
import gudusoft.gsqlparser.resolver.TSQLResolver;
import gudusoft.gsqlparser.TLog;
import gudusoft.gsqlparser.compiler.TASTEvaluator;
import gudusoft.gsqlparser.stmt.TRoutine;
import gudusoft.gsqlparser.util.TSnowflakeParameterChecker;

import java.io.BufferedReader;
import java.util.ArrayList;
import java.util.List;
import java.util.Stack;

import static gudusoft.gsqlparser.ESqlStatementType.*;

/**
 * Snowflake database SQL parser implementation.
 *
 * <p>This parser handles Snowflake-specific SQL syntax including:
 * <ul>
 *   <li>Snowflake stored procedures (SQL and JavaScript)</li>
 *   <li>Snowflake-specific functions (FLATTEN, PIVOT, UNPIVOT, etc.)</li>
 *   <li>Snowflake tasks and streams</li>
 *   <li>Snowflake semi-structured data handling (VARIANT, ARRAY, OBJECT)</li>
 *   <li>Special token handling (AT, LEFT/RIGHT joins, DATE/TIME functions)</li>
 *   <li>Transaction control (BEGIN TRANSACTION, COMMIT, ROLLBACK)</li>
 * </ul>
 *
 * <p><b>Design Notes:</b>
 * <ul>
 *   <li>Extends {@link AbstractSqlParser} using the template method pattern</li>
 *   <li>Uses {@link TLexerSnowflake} for tokenization</li>
 *   <li>Uses {@link TParserSnowflake} for parsing</li>
 *   <li>Delimiter character: ';' for SQL statements</li>
 * </ul>
 *
 * <p><b>Usage Example:</b>
 * <pre>
 * // Get Snowflake parser from factory
 * SqlParser parser = SqlParserFactory.get(EDbVendor.dbvsnowflake);
 *
 * // Build context
 * ParserContext context = new ParserContext.Builder(EDbVendor.dbvsnowflake)
 *     .sqlText("SELECT * FROM customers WHERE region = 'US'")
 *     .build();
 *
 * // Parse
 * SqlParseResult result = parser.parse(context);
 *
 * // Access statements
 * TStatementList statements = result.getSqlStatements();
 * </pre>
 *
 * @see SqlParser
 * @see AbstractSqlParser
 * @see TLexerSnowflake
 * @see TParserSnowflake
 * @since 3.2.0.0
 */
public class SnowflakeSqlParser extends AbstractSqlParser {

    /**
     * Construct Snowflake SQL parser.
     * <p>
     * Configures the parser for Snowflake database with default delimiter (;).
     * <p>
     * Following the original TGSqlParser pattern, the lexer and parser are
     * created once in the constructor and reused for all parsing operations.
     */
    public SnowflakeSqlParser() {
        super(EDbVendor.dbvsnowflake);
        this.delimiterChar = ';';
        this.defaultDelimiterStr = ";";

        // Create lexer once - will be reused for all parsing operations
        this.flexer = new TLexerSnowflake();
        this.flexer.delimiterchar = this.delimiterChar;
        this.flexer.defaultDelimiterStr = this.defaultDelimiterStr;

        // Set parent's lexer reference for shared tokenization logic
        this.lexer = this.flexer;

        // Create parser once - will be reused for all parsing operations
        this.fparser = new TParserSnowflake(null);
        this.fparser.lexer = this.flexer;
    }

    // ========== Parser Components ==========

    /** The Snowflake lexer used for tokenization */
    public TLexerSnowflake flexer;

    /** SQL parser (for Snowflake statements) */
    private TParserSnowflake fparser;

    /** Current statement being built during extraction */
    private TCustomSqlStatement gcurrentsqlstatement;

    // Stored procedure parsing state tracking
    private enum stored_procedure_type {
        procedure, function, package_spec, package_body, block_with_declare,
        block_with_begin, create_trigger, create_library, others
    }

    private enum stored_procedure_status {
        start, is_as, body, bodyend, end
    }

    private static final int stored_procedure_nested_level = 50;

    // Note: Global context and frame stack fields inherited from AbstractSqlParser:
    // - protected TContext globalContext
    // - protected TSQLEnv sqlEnv
    // - protected Stack<TFrame> frameStack
    // - protected TFrame globalFrame

    // ========== AbstractSqlParser Abstract Methods Implementation ==========

    /**
     * Return the Snowflake lexer instance.
     */
    @Override
    protected TCustomLexer getLexer(ParserContext context) {
        return this.flexer;
    }

    /**
     * Return the Snowflake SQL parser instance with updated token list.
     */
    @Override
    protected TCustomParser getParser(ParserContext context, TSourceTokenList tokens) {
        this.fparser.sourcetokenlist = tokens;
        this.fparser.reclassifyStagePathKeywords();
        return this.fparser;
    }

    /**
     * Snowflake does not use a secondary parser (unlike Oracle with PL/SQL).
     */
    @Override
    protected TCustomParser getSecondaryParser(ParserContext context, TSourceTokenList tokens) {
        return null;
    }

    /**
     * Call Snowflake-specific tokenization logic.
     * <p>
     * Delegates to dosnowflakesqltexttotokenlist which handles Snowflake's
     * specific keyword recognition and token generation.
     */
    @Override
    protected void tokenizeVendorSql() {
        dosnowflakesqltexttotokenlist();
    }

    /**
     * Setup Snowflake parser for raw statement extraction.
     * <p>
     * Snowflake uses a single parser, so we inject sqlcmds and update
     * the token list for the main parser only.
     */
    @Override
    protected void setupVendorParsersForExtraction() {
        // Inject sqlcmds into parser (required for make_stmt)
        this.fparser.sqlcmds = this.sqlcmds;

        // Update token list for parser
        this.fparser.sourcetokenlist = this.sourcetokenlist;
    }

    /**
     * Call Snowflake-specific raw statement extraction logic.
     * <p>
     * Delegates to dosnowflakegetrawsqlstatements which handles Snowflake's
     * statement delimiters and stored procedure boundaries.
     */
    @Override
    protected void extractVendorRawStatements(SqlParseResult.Builder builder) {
        int errorCount = dosnowflakegetrawsqlstatements(builder);
        // Error count is tracked internally; errors are already added to syntaxErrors list

        // Expand dollar string and single-quoted procedure bodies into token lists
        expandDollarString();

        // Set the extracted statements in the builder
        builder.sqlStatements(this.sqlstatements);
    }

    /**
     * Expand dollar-delimited and single-quoted string literals in Snowflake procedure/function bodies.
     * <p>
     * For CREATE PROCEDURE/FUNCTION statements with LANGUAGE SQL, this method:
     * 1. Finds string literals (starting with $$ or ') that follow AS keyword
     * 2. Extracts and tokenizes the SQL code inside the quotes
     * 3. Replaces the single string token with expanded tokens for proper parsing
     * <p>
     * This is essential for Snowflake's syntax: AS '...' or AS $$...$$
     */
    private void expandDollarString() {
        TSourceToken st;
        TCustomSqlStatement sql;
        ArrayList<TSourceToken> dollarTokens = new ArrayList<>();
        boolean isSQLLanguage = true;

        // Iterate all create procedure and create function, other sql statement just skipped
        for (int i = 0; i < sqlstatements.size(); i++) {
            sql = sqlstatements.get(i);
            if (!((sql.sqlstatementtype == ESqlStatementType.sstcreateprocedure) ||
                  (sql.sqlstatementtype == ESqlStatementType.sstcreatefunction))) continue;

            isSQLLanguage = true;
            for (int j = 0; j < sql.sourcetokenlist.size(); j++) {
                st = sql.sourcetokenlist.get(j);

                if (sql.sqlstatementtype == ESqlStatementType.sstcreateprocedure) {
                    if (st.tokencode == TBaseType.rrw_snowflake_language) {
                        TSourceToken lang = st.nextSolidToken();
                        if ((lang != null) && (!lang.toString().equalsIgnoreCase("sql"))) {
                            isSQLLanguage = false;
                        }
                    }
                }

                if (!isSQLLanguage) break;

                if (st.tokencode == TBaseType.sconst) {
                    if (st.toString().startsWith("$$")) {
                        dollarTokens.add(st);
                    } else if (st.toString().startsWith("'")) {
                        // https://docs.snowflake.com/en/sql-reference/sql/create-procedure
                        // string literal delimiter can be $ or '
                        if (st.prevSolidToken().tokencode == TBaseType.rrw_as) {
                            dollarTokens.add(st);
                        }
                    }
                }
            }//check tokens

            for (int m = dollarTokens.size() - 1; m >= 0; m--) {
                // Token Expansion:
                // For each identified string literal:
                // Extracts the content between the quotes
                // Tokenizes the extracted SQL code
                // Verifies it's a valid code block (starts with DECLARE or BEGIN)
                // Replaces the original quoted string with expanded tokens in the source token list

                st = dollarTokens.get(m);

                // Create a new parser to tokenize the procedure body
                // Use TGSqlParser for internal tokenization (simpler than using SnowflakeSqlParser for this snippet)
                gudusoft.gsqlparser.TGSqlParser parser = new gudusoft.gsqlparser.TGSqlParser(this.vendor);

                // Extract the body content from the string literal
                // For procedure bodies, we need special handling for single-quoted strings:
                // - Remove outer quotes
                // - Unescape '' to ' (SQL standard)
                // - Preserve backslash-quote-quote patterns: \'' -> \'
                //   (backslash + escaped quote should become backslash + single quote for re-parsing)
                // See mantisbt issue 4298 for details
                String tokenStr = st.toString();
                String bodyContent;
                if (tokenStr.startsWith("$$")) {
                    // Dollar-quoted string: just strip the $$ delimiters
                    bodyContent = TBaseType.getStringInsideLiteral(tokenStr);
                } else if (tokenStr.startsWith("'")) {
                    // Single-quoted string: custom unescaping
                    // Do NOT use getStringInsideLiteral() as it incorrectly handles \'
                    bodyContent = tokenStr.substring(1, tokenStr.length() - 1);
                    // Replace \'' with placeholder, unescape '', then restore \'
                    // \'' in original means backslash + escaped quote -> value \'
                    // In re-parsed body, \' is the escape sequence for this
                    bodyContent = bodyContent.replace("\\''", "\u0000BSQQ\u0000");
                    bodyContent = bodyContent.replace("''", "'");
                    bodyContent = bodyContent.replace("\u0000BSQQ\u0000", "\\'");
                } else {
                    bodyContent = tokenStr;
                }
                parser.sqltext = bodyContent;

                TSourceToken startQuote = new TSourceToken(st.toString().substring(0, 1));
                startQuote.tokencode = TBaseType.lexspace; // Set as space, can be ignored during parsing, but preserved in toString()
                TSourceToken endQuote = new TSourceToken(st.toString().substring(0, 1));
                endQuote.tokencode = TBaseType.lexspace;

                // use getrawsqlstatements() instead of tokenizeSqltext() to get the source token list because
                // some token will be transformed to other token, which will be processed in dosnowflakegetrawsqlstatements()
                parser.getrawsqlstatements();

                TSourceToken st2;
                boolean isValidBlock = false;
                for (int k = 0; k < parser.sourcetokenlist.size(); k++) {
                    st2 = parser.sourcetokenlist.get(k);
                    if (st2.isnonsolidtoken()) continue;
                    if ((st2.tokencode == TBaseType.rrw_declare) || (st2.tokencode == TBaseType.rrw_begin)) {
                        isValidBlock = true;
                    }
                    break;
                }

                if (isValidBlock) {
                    TSourceToken semiColon = null;
                    st.tokenstatus = ETokenStatus.tsdeleted;
                    int startPosOfThisSQL = sql.getStartToken().posinlist;

                    sql.sourcetokenlist.add((st.posinlist++) - startPosOfThisSQL, startQuote); // Add opening quote
                    for (int k = 0; k < parser.sourcetokenlist.size(); k++) {
                        st2 = parser.sourcetokenlist.get(k);
                        if (st2.tokencode == ';') {
                            semiColon = st2;
                            TSourceToken prevSolidToken = st2.prevSolidToken();
                            if ((prevSolidToken != null) && (prevSolidToken.tokencode == TBaseType.rrw_begin)) {
                                // begin;  => begin transaction;
                                prevSolidToken.tokencode = TBaseType.rrw_snowflake_begin_transaction;
                            }
                        }
                        if ((st2.tokencode == TBaseType.rrw_snowflake_work) || (st2.tokencode == TBaseType.rrw_snowflake_transaction)) {
                            // begin work;  => begin transaction;
                            TSourceToken prevSolidToken = st2.prevSolidToken();
                            if ((prevSolidToken != null) && (prevSolidToken.tokencode == TBaseType.rrw_begin)) {
                                // begin;  => begin transaction;
                                prevSolidToken.tokencode = TBaseType.rrw_snowflake_begin_transaction;
                            }
                        }
                        sql.sourcetokenlist.add((st.posinlist++) - startPosOfThisSQL, st2);
                    }
                    if (semiColon != null) {
                        if (semiColon.prevSolidToken().tokencode == TBaseType.rrw_end) {
                            // Set as space, can be ignored during parsing, but preserved in toString()
                            semiColon.tokencode = TBaseType.lexspace;
                        }
                    }

                    sql.sourcetokenlist.add((st.posinlist++) - startPosOfThisSQL, endQuote); // Add closing quote
                    TBaseType.resetTokenChain(sql.sourcetokenlist, 0); // Reset token chain to ensure new tokens are accessible in toString()
                }
            }

            dollarTokens.clear();
        }//statement
    }

    /**
     * Perform full parsing of statements with syntax checking.
     * <p>
     * This method orchestrates the parsing of all statements.
     */
    @Override
    protected TStatementList performParsing(ParserContext context,
                                           TCustomParser parser,
                                           TCustomParser secondaryParser,
                                           TSourceTokenList tokens,
                                           TStatementList rawStatements) {
        // Store references
        this.fparser = (TParserSnowflake) parser;
        this.sourcetokenlist = tokens;
        this.parserContext = context;

        // Use the raw statements passed from AbstractSqlParser.parse()
        this.sqlstatements = rawStatements;

        // Initialize statement parsing infrastructure
        this.sqlcmds = SqlCmdsFactory.get(vendor);

        // Inject sqlcmds into parser (required for make_stmt and other methods)
        this.fparser.sqlcmds = this.sqlcmds;

        // Initialize global context for semantic analysis
        initializeGlobalContext();

        // Parse each statement with exception handling for robustness
        for (int i = 0; i < sqlstatements.size(); i++) {
            TCustomSqlStatement stmt = sqlstatements.getRawSql(i);

            try {
                stmt.setFrameStack(frameStack);

                // Parse the statement
                int parseResult = stmt.parsestatement(null, false, context.isOnlyNeedRawParseTree());

                // Handle error recovery for CREATE TABLE/INDEX
                boolean doRecover = TBaseType.ENABLE_ERROR_RECOVER_IN_CREATE_TABLE;
                if (doRecover && ((parseResult != 0) || (stmt.getErrorCount() > 0))) {
                    handleCreateTableErrorRecovery(stmt);
                }

                // Collect syntax errors
                if ((parseResult != 0) || (stmt.getErrorCount() > 0)) {
                    copyErrorsFromStatement(stmt);
                }

            } catch (Exception ex) {
                // Use inherited exception handler from AbstractSqlParser
                // This provides consistent error handling across all database parsers
                handleStatementParsingException(stmt, i, ex);
                continue;
            }
        }

        // Clean up frame stack
        if (globalFrame != null) {
            globalFrame.popMeFromStack(frameStack);
        }

        return this.sqlstatements;
    }

    // Note: initializeGlobalContext() inherited from AbstractSqlParser
    // Note: No override of afterStatementParsed() needed - default (no-op) is appropriate for Snowflake

    /**
     * Handle error recovery for CREATE TABLE/INDEX statements.
     */
    private void handleCreateTableErrorRecovery(TCustomSqlStatement stmt) {
        if (((stmt.sqlstatementtype == ESqlStatementType.sstcreatetable)
                || (stmt.sqlstatementtype == ESqlStatementType.sstcreateindex))
                && (!TBaseType.c_createTableStrictParsing)) {

            int nested = 0;
            boolean isIgnore = false, isFoundIgnoreToken = false;
            TSourceToken firstIgnoreToken = null;

            for (int k = 0; k < stmt.sourcetokenlist.size(); k++) {
                TSourceToken st = stmt.sourcetokenlist.get(k);
                if (isIgnore) {
                    if (st.issolidtoken() && (st.tokencode != ';')) {
                        isFoundIgnoreToken = true;
                        if (firstIgnoreToken == null) {
                            firstIgnoreToken = st;
                        }
                    }
                    if (st.tokencode != ';') {
                        st.tokencode = TBaseType.sqlpluscmd;
                    }
                    continue;
                }
                if (st.tokencode == (int) ')') {
                    nested--;
                    if (nested == 0) {
                        boolean isSelect = false;
                        TSourceToken st1 = st.searchToken(TBaseType.rrw_as, 1);
                        if (st1 != null) {
                            TSourceToken st2 = st.searchToken((int) '(', 2);
                            if (st2 != null) {
                                TSourceToken st3 = st.searchToken(TBaseType.rrw_select, 3);
                                isSelect = (st3 != null);
                            }
                        }
                        if (!isSelect) isIgnore = true;
                    }
                } else if (st.tokencode == (int) '(') {
                    nested++;
                }
            }

            if (isFoundIgnoreToken) {
                stmt.clearError();
                stmt.parsestatement(null, false);
            }
        }
    }

    /**
     * Perform Snowflake-specific semantic analysis using TSQLResolver.
     */
    @Override
    protected void performSemanticAnalysis(ParserContext context, TStatementList statements) {
        if (TBaseType.isEnableResolver() && getSyntaxErrors().isEmpty()) {
            TSQLResolver resolver = new TSQLResolver(globalContext, statements);
            resolver.resolve();
        }
    }

    /**
     * Perform interpretation/evaluation on parsed statements.
     */
    @Override
    protected void performInterpreter(ParserContext context, TStatementList statements) {
        if (TBaseType.ENABLE_INTERPRETER && getSyntaxErrors().isEmpty()) {
            TLog.clearLogs();
            TGlobalScope interpreterScope = new TGlobalScope(sqlEnv);
            TLog.enableInterpreterLogOnly();
            TASTEvaluator astEvaluator = new TASTEvaluator(statements, interpreterScope);
            astEvaluator.eval();
        }
    }

    // ========== Snowflake-Specific Tokenization ==========

    /**
     * Snowflake-specific tokenization logic.
     * <p>
     * Extracted from: TGSqlParser.dosnowflakesqltexttotokenlist() (lines 3289-3439)
     */
    private void dosnowflakesqltexttotokenlist() {

        boolean insqlpluscmd = false;
        boolean isvalidplace = true;
        boolean waitingreturnforfloatdiv = false;
        boolean waitingreturnforsemicolon = false;
        boolean continuesqlplusatnewline = false;

        TSourceToken lct = null, prevst = null;

        TSourceToken asourcetoken, lcprevst;
        int yychar;

        asourcetoken = getanewsourcetoken();
        if (asourcetoken == null) return;
        yychar = asourcetoken.tokencode;

        while (yychar > 0) {
            sourcetokenlist.add(asourcetoken);
            switch (yychar) {
                case TBaseType.cmtdoublehyphen:
                case TBaseType.cmtslashstar:
                case TBaseType.lexspace: {
                    if (insqlpluscmd) {
                        asourcetoken.insqlpluscmd = true;
                    }
                    break;
                }
                case TBaseType.lexnewline: {
                    if (insqlpluscmd) {
                        insqlpluscmd = false;
                        isvalidplace = true;

                        if (continuesqlplusatnewline) {
                            insqlpluscmd = true;
                            isvalidplace = false;
                            asourcetoken.insqlpluscmd = true;
                        }
                    }

                    if (waitingreturnforsemicolon) {
                        isvalidplace = true;
                    }
                    if (waitingreturnforfloatdiv) {
                        isvalidplace = true;
                        lct.tokencode = TBaseType.sqlpluscmd;
                        if (lct.tokentype != ETokenType.ttslash) {
                            lct.tokentype = ETokenType.ttsqlpluscmd;
                        }
                    }
                    flexer.insqlpluscmd = insqlpluscmd;
                    break;
                } //case newline
                default: {
                    //solid tokentext
                    continuesqlplusatnewline = false;
                    waitingreturnforsemicolon = false;
                    waitingreturnforfloatdiv = false;
                    if (insqlpluscmd) {
                        asourcetoken.insqlpluscmd = true;
                        if (asourcetoken.getAstext().equalsIgnoreCase("-")) {
                            continuesqlplusatnewline = true;
                        }
                    } else {
                        if (asourcetoken.tokentype == ETokenType.ttsemicolon) {
                            waitingreturnforsemicolon = true;
                        }
                        if ((asourcetoken.tokentype == ETokenType.ttslash)
                                // and (isvalidplace or sourcetokenlist.TokenBeforeCurToken(#10,false,false,false)) then
                                && (isvalidplace || (IsValidPlaceForDivToSqlplusCmd(sourcetokenlist, asourcetoken.posinlist)))) {
                            lct = asourcetoken;
                            waitingreturnforfloatdiv = true;
                        }
                        if ((isvalidplace) && isvalidsqlpluscmdInPostgresql(asourcetoken.toString())) {
                            asourcetoken.tokencode = TBaseType.sqlpluscmd;
                            if (asourcetoken.tokentype != ETokenType.ttslash) {
                                asourcetoken.tokentype = ETokenType.ttsqlpluscmd;
                            }
                            insqlpluscmd = true;
                            flexer.insqlpluscmd = insqlpluscmd;
                        }
                    }
                    isvalidplace = false;

                    // the inner keyword tokentext should be convert to TBaseType.ident when
                    // next solid tokentext is not join

                    if (prevst != null) {
                        if (prevst.tokencode == TBaseType.rrw_inner)//flexer.getkeywordvalue("INNER"))
                        {
                            if (asourcetoken.tokencode != flexer.getkeywordvalue("JOIN")
                                    && asourcetoken.tokencode != flexer.getkeywordvalue("DIRECTED")) {
                                prevst.tokencode = TBaseType.ident;
                            }
                        }


                        if ((prevst.tokencode == TBaseType.rrw_not)
                                && (asourcetoken.tokencode == flexer.getkeywordvalue("DEFERRABLE"))) {
                            prevst.tokencode = flexer.getkeywordvalue("NOT_DEFERRABLE");
                        }

                    }

                    if (asourcetoken.tokencode == TBaseType.rrw_inner) {
                        prevst = asourcetoken;
                    } else if (asourcetoken.tokencode == TBaseType.rrw_not) {
                        prevst = asourcetoken;
                    } else {
                        prevst = null;
                    }


                }
            }

            //flexer.yylexwrap(asourcetoken);
            asourcetoken = getanewsourcetoken();
            if (asourcetoken != null) {
                yychar = asourcetoken.tokencode;
            } else {
                yychar = 0;

                if (waitingreturnforfloatdiv) { // / at the end of line treat as sqlplus command
                    //isvalidplace = true;
                    lct.tokencode = TBaseType.sqlpluscmd;
                    if (lct.tokentype != ETokenType.ttslash) {
                        lct.tokentype = ETokenType.ttsqlpluscmd;
                    }
                }

            }

            if ((yychar == 0) && (prevst != null)) {
                if (prevst.tokencode == TBaseType.rrw_inner)// flexer.getkeywordvalue("RW_INNER"))
                {
                    prevst.tokencode = TBaseType.ident;
                }
            }


        } // while


    }

    // ========== Snowflake-Specific Raw Statement Extraction ==========

    /**
     * Snowflake-specific raw statement extraction logic.
     * <p>
     * Extracted from: TGSqlParser.dosnowflakegetrawsqlstatements() (lines 8646-9388)
     */
    private int dosnowflakegetrawsqlstatements(SqlParseResult.Builder builder) {
        int waitingEnd = 0;
        boolean foundEnd = false;

        int waitingEnds[] = new int[stored_procedure_nested_level];
        stored_procedure_type sptype[] = new stored_procedure_type[stored_procedure_nested_level];
        stored_procedure_status procedure_status[] = new stored_procedure_status[stored_procedure_nested_level];
        boolean endBySlashOnly = true;
        int nestedProcedures = 0, nestedParenthesis = 0;
        boolean inDollarBody = false;

        if (TBaseType.assigned(sqlstatements)) sqlstatements.clear();
        if (!TBaseType.assigned(sourcetokenlist)) return -1;

        gcurrentsqlstatement = null;
        EFindSqlStateType gst = EFindSqlStateType.stnormal;
        TSourceToken lcprevsolidtoken = null, ast = null;

        for (int i = 0; i < sourcetokenlist.size(); i++) {

            if ((ast != null) && (ast.issolidtoken()))
                lcprevsolidtoken = ast;

            ast = sourcetokenlist.get(i);
            sourcetokenlist.curpos = i;

            if ((ast.tokencode == TBaseType.rrw_right) || (ast.tokencode == TBaseType.rrw_left)) {
                TSourceToken stLparen = ast.searchToken('(', 1);
                if (stLparen != null) {   //match (
                    ast.tokencode = TBaseType.ident;
                }
                TSourceToken stNextToken = ast.nextSolidToken();
                if ((stNextToken != null) && ((stNextToken.tokencode == TBaseType.rrw_join) || (stNextToken.tokencode == TBaseType.rrw_outer))) {
                    if (ast.tokencode == TBaseType.rrw_left) {
                        ast.tokencode = TBaseType.rrw_snowflake_left_join;
                    } else {
                        ast.tokencode = TBaseType.rrw_snowflake_right_join;
                    }
                }
            } else if (ast.tokencode == TBaseType.rrw_snowflake_at) {
                TSourceToken stLparen = ast.searchToken('(', 1);
                if (stLparen != null) {   //match (
                    ast.tokencode = TBaseType.rrw_snowflake_at_before_parenthesis;
                }
            } else if (ast.tokencode == TBaseType.rrw_snowflake_changes) {
                TSourceToken stLparen = ast.searchToken('(', 1);
                if (stLparen != null) {   //changes (
                    ast.tokencode = TBaseType.rrw_snowflake_changes_parenthesis;
                }
            } else if (ast.tokencode == TBaseType.rrw_date) {
                TSourceToken stLparen = ast.searchToken('(', 1);
                if (stLparen != null) {   //date (
                    ast.tokencode = TBaseType.rrw_snowflake_date;
                } else {
                    stLparen = ast.searchToken('.', 1);
                    if (stLparen != null) {   //date (
                        ast.tokencode = TBaseType.ident;
                    }
                }
            } else if (ast.tokencode == TBaseType.rrw_time) {
                TSourceToken stLparen = ast.searchToken('(', 1);
                if (stLparen != null) {   //date (
                    ast.tokencode = TBaseType.rrw_snowflake_time;
                } else {
                    stLparen = ast.searchToken('.', 1);
                    if (stLparen != null) {   //date (
                        ast.tokencode = TBaseType.ident;
                    }
                }
            } else if (ast.tokencode == TBaseType.rrw_char) {
                TSourceToken stLparen = ast.searchToken('(', 1);
                if (stLparen != null) {   //date (
                    ast.tokencode = TBaseType.rrw_snowflake_char;
                } else {
                    stLparen = ast.searchToken('.', 1);
                    if (stLparen != null) {   //date (
                        ast.tokencode = TBaseType.ident;
                    }
                }
            } else if (ast.tokencode == TBaseType.rrw_snowflake_window) {
                TSourceToken stAs = ast.searchToken(TBaseType.rrw_as, 2);
                if (stAs != null) {   //date (
                    ast.tokencode = TBaseType.rrw_snowflake_window_as;
                } else {
                }
            } else if ((ast.tokencode == TBaseType.rrw_snowflake_pivot) || (ast.tokencode == TBaseType.rrw_snowflake_unpivot)) {
                // For UNPIVOT, search range 3 to look past INCLUDE/EXCLUDE NULLS before (
                int searchRange = (ast.tokencode == TBaseType.rrw_snowflake_unpivot) ? 3 : 1;
                TSourceToken stLparen = ast.searchToken('(', searchRange);
                if (stLparen != null) {   //pivot (, unpivot (, unpivot include nulls (

                } else {
                    ast.tokencode = TBaseType.ident;
                }
            } else if (ast.tokencode == TBaseType.rrw_snowflake_flatten) {
                TSourceToken stLeftParens = ast.searchToken('(', 1);
                if (stLeftParens != null) {   //flatten (

                } else {
                    ast.tokencode = TBaseType.ident; // change it to an identifier, can be used as db object name.
                }
            } else if (ast.tokencode == TBaseType.rrw_snowflake_offset) {
                TSourceToken stFrom = ast.searchToken(TBaseType.rrw_from, -ast.posinlist, TBaseType.rrw_select, true);
                if (stFrom == null) {
                    // FORM keyword before OFFSET is not found, then offset must be a column name,
                    // just like this: SELECT column1 offset FROM table2
                    ast.tokencode = TBaseType.ident; // change it to an identifier, can be used as db object name.
                }
            } else if (ast.tokencode == TBaseType.rrw_replace) {
                TSourceToken stStar = ast.prevSolidToken();
                if (stStar.tokencode == '*') {
                    ast.tokencode = TBaseType.rrw_snowflake_replace_after_star;
                }
            } else if (ast.tokencode == TBaseType.rrw_snowflake_transaction) {
                TSourceToken stBegin = ast.prevSolidToken();
                if ((stBegin != null) && (stBegin.tokencode == TBaseType.rrw_begin)) {
                    stBegin.tokencode = TBaseType.rrw_snowflake_begin_transaction;
                }
            } else if (ast.tokencode == TBaseType.rrw_begin) {
                // begin;
                // begin work;
                // begin transaction;
                TSourceToken stNext = ast.nextSolidToken();
                if ((stNext != null) && ((stNext.tokencode == ';')
                        || (stNext.tokencode == TBaseType.rrw_snowflake_work) || (stNext.tokencode == TBaseType.rrw_snowflake_transaction))
                ) {
                    ast.tokencode = TBaseType.rrw_snowflake_begin_transaction;
                }
            } else if ((ast.tokencode == TBaseType.rrw_snowflake_top) || (ast.tokencode == TBaseType.rrw_text) || (ast.tokencode == TBaseType.rrw_snowflake_default)) {
                TSourceToken stPeriod = ast.nextSolidToken();
                if ((stPeriod != null) && (stPeriod.tokencode == '.')) {
                    ast.tokencode = TBaseType.ident;
                }
            } else if (ast.tokencode == TBaseType.rrw_snowflake_limit) {
                TSourceToken stPrev = ast.prevSolidToken();
                if ((stPrev != null) && (stPrev.tokencode == ',')) {
                    ast.tokencode = TBaseType.ident;
                }
            } else if (ast.tokencode == TBaseType.ident) {
                // check whether it is a snowflake parameter name
                // 这个调用可能会有性能问题，因为每个ident都会调用一次
                if (TSnowflakeParameterChecker.isSnowflakeParameter(ast.toString())) {
                    ast.tokencode = TBaseType.rrw_snowflake_parameter_name;
                }
            }


            switch (gst) {
                case sterror: {
                    if (ast.tokentype == ETokenType.ttsemicolon) {
                        gcurrentsqlstatement.sourcetokenlist.add(ast);
                        onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                        gst = EFindSqlStateType.stnormal;
                    } else {
                        gcurrentsqlstatement.sourcetokenlist.add(ast);
                    }
                    break;
                } //sterror

                case stnormal: {
                    if ((ast.tokencode == TBaseType.cmtdoublehyphen)
                            || (ast.tokencode == TBaseType.cmtslashstar)
                            || (ast.tokencode == TBaseType.lexspace)
                            || (ast.tokencode == TBaseType.lexnewline)
                            || (ast.tokentype == ETokenType.ttsemicolon)) {
                        if (gcurrentsqlstatement != null) {
                            gcurrentsqlstatement.sourcetokenlist.add(ast);
                        }

                        if ((lcprevsolidtoken != null) && (ast.tokentype == ETokenType.ttsemicolon)) {
                            if (lcprevsolidtoken.tokentype == ETokenType.ttsemicolon) {
                                // ;;;; continuous semicolon,treat it as comment
                                ast.tokentype = ETokenType.ttsimplecomment;
                                ast.tokencode = TBaseType.cmtdoublehyphen;
                            }
                        }

                        continue;
                    }

                    if (ast.tokencode == TBaseType.sqlpluscmd) {
                        gst = EFindSqlStateType.stsqlplus;
                        gcurrentsqlstatement = new TSqlplusCmdStatement(vendor);
                        gcurrentsqlstatement.sourcetokenlist.add(ast);
                        continue;
                    }

                    // find a tokentext to start sql or plsql mode
                    gcurrentsqlstatement = sqlcmds.issql(ast, gst, gcurrentsqlstatement);

                    if (gcurrentsqlstatement != null) {
                        // WITH...AS PROCEDURE uses stsql mode (not ststoredprocedure)
                        // because the $$ body is handled by TRoutine's dollar delimiter logic in stsql mode,
                        // and the ststoredprocedure state machine misinterprets the early AS token in "WITH name AS PROCEDURE"
                        boolean isWithProcedure = (gcurrentsqlstatement.sqlstatementtype == ESqlStatementType.sstcreateprocedure)
                                && (ast.tokencode == TBaseType.rrw_with);

                        if (gcurrentsqlstatement.issnowflakeplsql() && !isWithProcedure) {
                            nestedProcedures = 0;
                            gst = EFindSqlStateType.ststoredprocedure;
                            gcurrentsqlstatement.sourcetokenlist.add(ast);

                            switch (gcurrentsqlstatement.sqlstatementtype) {
                                case sstplsql_createprocedure:
                                case sstcreateprocedure:
                                    sptype[nestedProcedures] = stored_procedure_type.procedure;
                                    break;
                                case sstplsql_createfunction:
                                    sptype[nestedProcedures] = stored_procedure_type.function;
                                    break;
                                case sstplsql_createpackage:
                                    sptype[nestedProcedures] = stored_procedure_type.package_spec;
                                    if (ast.searchToken(TBaseType.rrw_body, 5) != null) {
                                        sptype[nestedProcedures] = stored_procedure_type.package_body;
                                    }
                                    break;
                                case sst_plsql_block:
                                    sptype[nestedProcedures] = stored_procedure_type.block_with_declare;
                                    if (ast.tokencode == TBaseType.rrw_begin) {
                                        sptype[nestedProcedures] = stored_procedure_type.block_with_begin;
                                    }
                                    break;
                                case sstplsql_createtrigger:
                                    sptype[nestedProcedures] = stored_procedure_type.create_trigger;
                                    break;
                                case sstoraclecreatelibrary:
                                    sptype[nestedProcedures] = stored_procedure_type.create_library;
                                    break;
                                case sstplsql_createtype_placeholder:
                                    gst = EFindSqlStateType.stsql;
                                    break;
                                default:
                                    sptype[nestedProcedures] = stored_procedure_type.others;
                                    break;
                            }

                            if (sptype[0] == stored_procedure_type.block_with_declare) {
                                // sd
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.is_as;
                            } else if (sptype[0] == stored_procedure_type.block_with_begin) {
                                // sb
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.body;
                            } else if (sptype[0] == stored_procedure_type.procedure) {
                                // ss
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.start;
                            } else if (sptype[0] == stored_procedure_type.function) {
                                // ss
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.start;
                            } else if (sptype[0] == stored_procedure_type.package_spec) {
                                // ss
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.start;
                            } else if (sptype[0] == stored_procedure_type.package_body) {
                                // ss
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.start;
                            } else if (sptype[0] == stored_procedure_type.create_trigger) {
                                // ss
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.start;
                                //procedure_status[0] = stored_procedure_status.body;
                            } else if (sptype[0] == stored_procedure_type.create_library) {
                                // ss
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.bodyend;
                            } else {
                                // so
                                endBySlashOnly = true;
                                procedure_status[0] = stored_procedure_status.bodyend;
                            }
                            //foundEnd = false;
                            if ((ast.tokencode == TBaseType.rrw_begin)
                                    || (ast.tokencode == TBaseType.rrw_package)
                                    //||(ast.tokencode == TBaseType.rrw_procedure)
                                    || (ast.searchToken(TBaseType.rrw_package, 4) != null)
                            ) {
                                //waitingEnd = 1;
                                waitingEnds[nestedProcedures] = 1;
                            }

                        } else {
                            gst = EFindSqlStateType.stsql;
                            if (isWithProcedure) {
                                // Mark WITH and first AS tokens so parser skips them
                                // This avoids S/R conflict with CTE (WITH name AS SELECT)
                                // Parser skips tokens with tsignorebyyacc status (TCustomParser line 420)
                                ast.tokenstatus = ETokenStatus.tsignorebyyacc;
                                // Also mark the first AS (WITH name(...) AS PROCEDURE) so parser sees IDENT (...) PROCEDURE...
                                TSourceToken nameToken = ast.nextSolidToken();
                                if (nameToken != null) {
                                    TSourceToken afterName = nameToken.nextSolidToken();
                                    // Skip past parenthesized parameter list if present
                                    if (afterName != null && afterName.tokentype == ETokenType.ttleftparenthesis) {
                                        int parenDepth = 1;
                                        TSourceToken t = afterName.nextSolidToken();
                                        while (t != null && parenDepth > 0) {
                                            if (t.tokentype == ETokenType.ttleftparenthesis) parenDepth++;
                                            else if (t.tokentype == ETokenType.ttrightparenthesis) parenDepth--;
                                            if (parenDepth > 0) t = t.nextSolidToken();
                                        }
                                        if (t != null) afterName = t.nextSolidToken();
                                        else afterName = null;
                                    }
                                    if (afterName != null && afterName.tokencode == TBaseType.rrw_as) {
                                        afterName.tokenstatus = ETokenStatus.tsignorebyyacc;
                                    }
                                }
                            }
                            gcurrentsqlstatement.sourcetokenlist.add(ast);
                        }
                    } else {
                        //error tokentext found

                        this.syntaxErrors.add(new TSyntaxError(ast.getAstext(), ast.lineNo, (ast.columnNo < 0 ? 0 : ast.columnNo)
                                , "Error when tokenlize", EErrorType.spwarning, TBaseType.MSG_WARNING_ERROR_WHEN_TOKENIZE, null, ast.posinlist));

                        ast.tokentype = ETokenType.tttokenlizererrortoken;
                        gst = EFindSqlStateType.sterror;

                        gcurrentsqlstatement = new TUnknownSqlStatement(vendor);
                        gcurrentsqlstatement.sqlstatementtype = ESqlStatementType.sstinvalid;
                        gcurrentsqlstatement.sourcetokenlist.add(ast);

                    }

                    break;
                } // stnormal

                case stsqlplus: {
                    if (ast.insqlpluscmd) {
                        gcurrentsqlstatement.sourcetokenlist.add(ast);
                    } else {
                        gst = EFindSqlStateType.stnormal; //this tokentext must be newline,
                        gcurrentsqlstatement.sourcetokenlist.add(ast); // so add it here
                        onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                    }

                    break;
                }//case stsqlplus

                case stsql: {
                    if (gcurrentsqlstatement instanceof TRoutine) {
                        if (isDollarFunctionDelimiter(ast.tokencode, this.vendor)) {
                            if (inDollarBody) {
                                inDollarBody = false;
                            } else {
                                inDollarBody = true;
                            }
                        }

                        if (inDollarBody) {
                            gcurrentsqlstatement.sourcetokenlist.add(ast);
                            continue;
                        }

                        // Handle inline scripting body (AS DECLARE ... BEGIN ... END or AS BEGIN ... END)
                        // for CREATE FUNCTION with LANGUAGE SQL
                        if (ast.tokencode == TBaseType.rrw_as) {
                            TSourceToken tmpNext = ast.nextSolidToken();
                            if ((tmpNext != null) && ((tmpNext.tokencode == TBaseType.rrw_begin) || (tmpNext.tokencode == TBaseType.rrw_declare))) {
                                gst = EFindSqlStateType.ststoredprocedure;
                                nestedProcedures = 0;
                                if (tmpNext.tokencode == TBaseType.rrw_begin) {
                                    procedure_status[nestedProcedures] = stored_procedure_status.body;
                                } else {
                                    procedure_status[nestedProcedures] = stored_procedure_status.is_as;
                                }
                                waitingEnds[nestedProcedures] = 0;
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                continue;
                            }
                        }
                    } else if (gcurrentsqlstatement instanceof TCreateTaskStmt) {
                        if (ast.tokencode == TBaseType.rrw_as) {
                            TSourceToken tmpNext = ast.nextSolidToken();
                            if ((tmpNext != null) && (tmpNext.tokencode == TBaseType.rrw_begin)) {
                                // begin ... end block in create task statement, mantisbt/view.php?id=3531

                                gst = EFindSqlStateType.ststoredprocedure;
                                nestedProcedures = 0;
                                procedure_status[nestedProcedures] = stored_procedure_status.body;
                                waitingEnds[nestedProcedures] = 0;
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                continue;
                            }
                        }
                    }

                    if (ast.tokentype == ETokenType.ttsemicolon) {
                        gst = EFindSqlStateType.stnormal;
                        gcurrentsqlstatement.sourcetokenlist.add(ast);
                        gcurrentsqlstatement.semicolonended = ast;
                        onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                        continue;
                    }

                    if (sourcetokenlist.sqlplusaftercurtoken()) //most probaly is / cmd
                    {
                        gst = EFindSqlStateType.stnormal;
                        gcurrentsqlstatement.sourcetokenlist.add(ast);
                        onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                        continue;
                    }
                    gcurrentsqlstatement.sourcetokenlist.add(ast);
                    break;
                }//case stsql

                case ststoredprocedure: {
                    if (procedure_status[nestedProcedures] != stored_procedure_status.bodyend) {
                        gcurrentsqlstatement.sourcetokenlist.add(ast);
                    }

                    switch (procedure_status[nestedProcedures]) {
                        case start:
                            if ((ast.tokencode == TBaseType.rrw_as) || (ast.tokencode == TBaseType.rrw_is)) {
                                // s1
                                if (sptype[nestedProcedures] != stored_procedure_type.create_trigger) {
                                    if ((sptype[0] == stored_procedure_type.package_spec) && (nestedProcedures > 0)) {
                                        //when it's a package specification, only top level accept as/is
                                    } else {
                                        procedure_status[nestedProcedures] = stored_procedure_status.is_as;
                                        if (ast.searchToken("language", 1) != null) {
                                            // if as language is used in create function, then switch state to stored_procedure_status.body directly.
//                                        CREATE OR REPLACE FUNCTION THING.addressparse(p_addressline1 VARCHAR2) RETURN VARCHAR2 AUTHID DEFINER
//                                        as Language JAVA NAME 'AddressParser.parse(java.lang.String) return java.lang.String';
//                                        /
                                            if (nestedProcedures == 0) {
                                                //  procedure_status[nestedProcedures] = stored_procedure_status.bodyend;
                                                gst = EFindSqlStateType.stsql;
                                            } else {
                                                procedure_status[nestedProcedures] = stored_procedure_status.body;
                                                nestedProcedures--;
                                                //if (nestedProcedures > 0){ nestedProcedures--;}
                                            }

                                        }
                                    }
                                }
                            } else if (ast.tokencode == TBaseType.rrw_begin) {
                                // s4
                                if (sptype[nestedProcedures] == stored_procedure_type.create_trigger)
                                    waitingEnds[nestedProcedures]++;

                                if (nestedProcedures > 0) {
                                    nestedProcedures--;
                                }
                                procedure_status[nestedProcedures] = stored_procedure_status.body;
                            } else if (ast.tokencode == TBaseType.rrw_end) {
                                //s10
                                if ((nestedProcedures > 0) && (waitingEnds[nestedProcedures - 1] == 1)
                                        && ((sptype[nestedProcedures - 1] == stored_procedure_type.package_body)
                                        || (sptype[nestedProcedures - 1] == stored_procedure_type.package_spec))) {
                                    nestedProcedures--;
                                    procedure_status[nestedProcedures] = stored_procedure_status.bodyend;
                                }
                            } else if ((ast.tokencode == TBaseType.rrw_procedure) || (ast.tokencode == TBaseType.rrw_function)) {
                                //s3
                                if ((nestedProcedures > 0) && (waitingEnds[nestedProcedures] == 0)
                                        && (procedure_status[nestedProcedures - 1] == stored_procedure_status.is_as)) {
                                    nestedProcedures--;
                                    nestedProcedures++;
                                    waitingEnds[nestedProcedures] = 0;
                                    procedure_status[nestedProcedures] = stored_procedure_status.start;
                                }
                            } else if ((sptype[nestedProcedures] == stored_procedure_type.create_trigger) && (ast.tokencode == TBaseType.rrw_declare)) {
                                procedure_status[nestedProcedures] = stored_procedure_status.is_as;
                            } else if ((sptype[nestedProcedures] == stored_procedure_type.create_trigger) && (ast.tokentype == ETokenType.ttslash) && (ast.tokencode == TBaseType.sqlpluscmd)) {
                                // TPlsqlStatementParse(asqlstatement).TerminatorToken := ast;
                                ast.tokenstatus = ETokenStatus.tsignorebyyacc;
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);

                                //make / a sqlplus cmd
                                gcurrentsqlstatement = new TSqlplusCmdStatement(vendor);
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                            } else if ((sptype[nestedProcedures] == stored_procedure_type.create_trigger)) {
                                if (ast.tokencode == TBaseType.rrw_trigger) {
                                    TSourceToken compoundSt = ast.searchToken(TBaseType.rrw_oracle_compound, -1);
                                    if (compoundSt != null) {
                                        //it's trigger with compound trigger block
                                        procedure_status[nestedProcedures] = stored_procedure_status.body;
                                        waitingEnds[nestedProcedures]++;
                                    }
                                }
                            } else if ((sptype[nestedProcedures] == stored_procedure_type.function) && (ast.tokencode == TBaseType.rrw_teradata_using)) {
                                if ((ast.searchToken("aggregate", -1) != null) || (ast.searchToken("pipelined", -1) != null)) {
                                    if (nestedProcedures == 0) {
                                        //  procedure_status[nestedProcedures] = stored_procedure_status.bodyend;
                                        gst = EFindSqlStateType.stsql;
                                    } else {
                                        procedure_status[nestedProcedures] = stored_procedure_status.body;
                                        nestedProcedures--;
                                    }
                                }

                            } else {
                                //other tokens, do nothing
                                if (ast.tokencode == TBaseType.rrw_snowflake_language) {
                                    // check next token which is the language used by this stored procedure
                                    TSourceToken nextSt = ast.nextSolidToken();
                                    if (nextSt != null) {
                                        if (gcurrentsqlstatement instanceof TRoutine) {  // can be TCreateProcedureStmt or TCreateFunctionStmt
                                            TRoutine p = (TRoutine) gcurrentsqlstatement;
                                            p.setRoutineLanguage(nextSt.toString());
                                            //System.out.println("Find snowflake procedure language: "+p.getRoutineLanguage());
                                            if (p.getRoutineLanguage().toString().equalsIgnoreCase("javascript")) {
                                                // procedure 中出现 javascript， 则碰到 semicolon 可能是整个 procedure 语句解释，因此可以设为 stsql 状态
                                                // 但因为 $$body$$ 已经被解析为分离的token，因此需要在 stsql 中忽略这些$$body$$中的token
                                                gst = EFindSqlStateType.stsql;
                                            }
                                        }
                                    }
                                }
                            }
                            break;
                        case is_as:
                            if ((ast.tokencode == TBaseType.rrw_procedure) || (ast.tokencode == TBaseType.rrw_function)) {
                                // s2
                                nestedProcedures++;
                                waitingEnds[nestedProcedures] = 0;
                                procedure_status[nestedProcedures] = stored_procedure_status.start;

                                if (nestedProcedures > stored_procedure_nested_level - 1) {
                                    gst = EFindSqlStateType.sterror;
                                    nestedProcedures--;
                                }

                            } else if (ast.tokencode == TBaseType.rrw_begin) {
                                // s5
                                if ((nestedProcedures == 0) &&
                                        ((sptype[nestedProcedures] == stored_procedure_type.package_body)
                                                || (sptype[nestedProcedures] == stored_procedure_type.package_spec))) {
                                    //top level package or package body's BEGIN keyword already count,
                                    // so don't increase waitingEnds[nestedProcedures] here

                                } else {
                                    waitingEnds[nestedProcedures]++;
                                }
                                procedure_status[nestedProcedures] = stored_procedure_status.body;
                            } else if (ast.tokencode == TBaseType.rrw_end) {
                                // s6
                                if ((nestedProcedures == 0) && (waitingEnds[nestedProcedures] == 1) &&
                                        ((sptype[nestedProcedures] == stored_procedure_type.package_body) || (sptype[nestedProcedures] == stored_procedure_type.package_spec))) {
                                    procedure_status[nestedProcedures] = stored_procedure_status.bodyend;
                                    waitingEnds[nestedProcedures]--;
                                } else {
                                    waitingEnds[nestedProcedures]--;
                                }
                            } else if (ast.tokencode == TBaseType.rrw_case) {
//                            if (ast.searchToken(TBaseType.rrw_end,-1) == null){
//                                //this is not case after END
//                             waitingEnds[nestedProcedures]++;
//                            }
                                if (ast.searchToken(';', 1) == null) {
                                    //this is not case before ;
                                    waitingEnds[nestedProcedures]++;
                                }
                            } else {
                                //other tokens, do nothing
                            }
                            break;
                        case body:
                            if ((ast.tokencode == TBaseType.rrw_begin)) {
                                waitingEnds[nestedProcedures]++;
                            } else if (ast.tokencode == TBaseType.rrw_if) {
                                if (ast.searchToken(TBaseType.rrw_snowflake_exists, 1) != null) {
                                    //drop table if exists SANDBOX.ANALYSIS_CONTENT.TABLEAU_DATES;
                                    // don't need END for the above if exists clause
                                } else if (ast.searchToken(';', 2) == null) {
                                    //this is not if before ;

                                    // 2015-02-27, change 1 to 2 make it able to detect label name after case
                                    // like this: END CASE l1;
                                    waitingEnds[nestedProcedures]++;
                                }
                            } else if (ast.tokencode == TBaseType.rrw_for) {
                                if (ast.searchToken(';', 2) == null) {
                                    //this is not for before ;

                                    // 2015-02-27, change 1 to 2 make it able to detect label name after case
                                    // like this: END CASE l1;
                                    waitingEnds[nestedProcedures]++;
                                }
                            } else if (ast.tokencode == TBaseType.rrw_case) {
//                            if (ast.searchToken(TBaseType.rrw_end,-1) == null){
//                                //this is not case after END
//                             waitingEnds[nestedProcedures]++;
//                            }
                                if (ast.searchToken(';', 2) == null) {
                                    //this is not case before ;
                                    if (ast.searchToken(TBaseType.rrw_end, -1) == null) {
                                        waitingEnds[nestedProcedures]++;
                                    }
                                }
                            } else if (ast.tokencode == TBaseType.rrw_loop) {
                                if (!((ast.searchToken(TBaseType.rrw_end, -1) != null)
                                        && (ast.searchToken(';', 2) != null))) {
                                    // exclude loop like this:
                                    // end loop [labelname];
                                    waitingEnds[nestedProcedures]++;
                                }

//                            if (ast.searchToken(TBaseType.rrw_end,-1) == null){
//                                //this is not loop after END
//                             waitingEnds[nestedProcedures]++;
////                            }
////                            if (ast.searchToken(';',2) == null){
////                                //this is no loop before ;
////                             waitingEnds[nestedProcedures]++;
//                            } else if (ast.searchToken(TBaseType.rrw_null,1) != null){
//                                // mantis bug tracking system:   #65
//                                waitingEnds[nestedProcedures]++;
//                            }
                            } else if (ast.tokencode == TBaseType.rrw_end) {
                                //foundEnd = true;
                                waitingEnds[nestedProcedures]--;
                                //if (waitingEnd < 0) { waitingEnd = 0;}
                                if (waitingEnds[nestedProcedures] == 0) {
                                    if (nestedProcedures == 0) {
                                        // s7
                                        procedure_status[nestedProcedures] = stored_procedure_status.bodyend;
                                    } else {
                                        // s71
                                        nestedProcedures--;
                                        procedure_status[nestedProcedures] = stored_procedure_status.is_as;
                                    }
                                }
                            } else if ((waitingEnds[nestedProcedures] == 0) && (ast.tokentype == ETokenType.ttslash) && (ast.tokencode == TBaseType.sqlpluscmd)) //and (prevst.NewlineIsLastTokenInTailerToken)) then
                            {
                                //sql ref: c:\prg\gsqlparser\Test\TestCases\oracle\createtrigger.sql, line 53
                                ast.tokenstatus = ETokenStatus.tsignorebyyacc;
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);

                                //make / a sqlplus cmd
                                gcurrentsqlstatement = new TSqlplusCmdStatement(vendor);
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                            }
                            break;
                        case bodyend:
                            if ((ast.tokentype == ETokenType.ttslash) && (ast.tokencode == TBaseType.sqlpluscmd)) //and (prevst.NewlineIsLastTokenInTailerToken)) then
                            {
                                // TPlsqlStatementParse(asqlstatement).TerminatorToken := ast;
                                ast.tokenstatus = ETokenStatus.tsignorebyyacc;
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);

                                //make / a sqlplus cmd
                                gcurrentsqlstatement = new TSqlplusCmdStatement(vendor);
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                            } else if ((ast.tokentype == ETokenType.ttperiod) && (sourcetokenlist.returnaftercurtoken(false)) && (sourcetokenlist.returnbeforecurtoken(false))) {    // single dot at a seperate line
                                ast.tokenstatus = ETokenStatus.tsignorebyyacc;
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);

                                //make ttperiod a sqlplus cmd
                                gcurrentsqlstatement = new TSqlplusCmdStatement(vendor);
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_package, 1) != null) && (!endBySlashOnly)) {
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_procedure, 1) != null) && (!endBySlashOnly)) {
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_function, 1) != null) && (!endBySlashOnly)) {
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_create, 1) != null) && (ast.searchToken(TBaseType.rrw_package, 4) != null) && (!endBySlashOnly)) {
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_create, 1) != null) && (ast.searchToken(TBaseType.rrw_library, 4) != null) && (!endBySlashOnly)) {
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_alter, 1) != null) && (ast.searchToken(TBaseType.rrw_trigger, 2) != null) && (!endBySlashOnly)) {
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_select, 1) != null) && (!endBySlashOnly)) {
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_commit, 1) != null) && (!endBySlashOnly)) {
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_grant, 1) != null) &&
                                    (ast.searchToken(TBaseType.rrw_execute, 2) != null) && (!endBySlashOnly)) {
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                            } else if ((gcurrentsqlstatement instanceof TCreateTaskStmt) && (ast.tokencode == ';')) {
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, false, builder);
                            } else {
                                gcurrentsqlstatement.sourcetokenlist.add(ast);
                            }
                            break;
                        case end:
                            break;
                        default:
                            break;
                    }


                    if (ast.tokencode == TBaseType.sqlpluscmd) {
                        //change tokencode back to keyword or TBaseType.ident, because sqlplus cmd
                        //in a sql statement(almost is plsql block) is not really a sqlplus cmd
                        int m = flexer.getkeywordvalue(ast.getAstext());
                        if (m != 0) {
                            ast.tokencode = m;
                        } else if (ast.tokentype == ETokenType.ttslash) {
                            ast.tokencode = '/';
                        } else {
                            ast.tokencode = TBaseType.ident;
                        }
                    }

                    final int wrapped_keyword_max_pos = 20;
                    if ((ast.tokencode == TBaseType.rrw_wrapped) && (ast.posinlist - gcurrentsqlstatement.sourcetokenlist.get(0).posinlist < wrapped_keyword_max_pos)) {
                        if (gcurrentsqlstatement instanceof TCommonStoredProcedureSqlStatement) {
                            ((TCommonStoredProcedureSqlStatement) gcurrentsqlstatement).setWrapped(true);
                        }

                        if (gcurrentsqlstatement instanceof TPlsqlCreatePackage) {
                            if (ast.prevSolidToken() != null) {
                                ((TPlsqlCreatePackage) gcurrentsqlstatement).setPackageName(fparser.getNf().createObjectNameWithPart(ast.prevSolidToken()));
                            }
                        }
                    }

                    break;
                } //ststoredprocedure
            } //switch
        }//for

        //last statement
        if ((gcurrentsqlstatement != null) &&
                ((gst == EFindSqlStateType.stsqlplus) || (gst == EFindSqlStateType.stsql) || (gst == EFindSqlStateType.ststoredprocedure) ||
                        (gst == EFindSqlStateType.sterror))) {
            onRawStatementComplete(parserContext, gcurrentsqlstatement, fparser, null, sqlstatements, true, builder);
        }

        return syntaxErrors.size();
    }

    // ========== Helper Methods ==========

    /**
     * Check if a position is valid for treating division operator as SQL*Plus command.
     */
    private boolean IsValidPlaceForDivToSqlplusCmd(TSourceTokenList pstlist, int pPos) {
        boolean ret = false;

        if ((pPos <= 0) || (pPos > pstlist.size() - 1)) return ret;
        //tokentext directly before div must be ttreturn without space appending it
        TSourceToken lcst = pstlist.get(pPos - 1);
        if (lcst.tokencode == TBaseType.lexnewline) {
            String astext = lcst.getAstext();
            ret = (astext.length() > 0 && astext.charAt(astext.length() - 1) == '\n');
        }

        return ret;
    }

    /**
     * Placeholder function for PostgreSQL-style SQL*Plus commands.
     * Always returns false for Snowflake (not applicable).
     */
    private boolean isvalidsqlpluscmdInPostgresql(String astr) {
        return false;
    }

    // Note: isDollarFunctionDelimiter() is now inherited from AbstractSqlParser
    // The parent implementation handles all PostgreSQL-family databases including Snowflake

    @Override
    public String toString() {
        return "SnowflakeSqlParser{vendor=" + vendor + "}";
    }
}