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.TLexerPostgresql;
import gudusoft.gsqlparser.TParserPostgresql;
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.oracle.TSqlplusCmdStatement;
import gudusoft.gsqlparser.stmt.TUnknownSqlStatement;
import gudusoft.gsqlparser.sqlcmds.ISqlCmds;
import gudusoft.gsqlparser.sqlcmds.SqlCmdsFactory;
import gudusoft.gsqlparser.stmt.TCommonBlock;
import gudusoft.gsqlparser.stmt.TRoutine;
import gudusoft.gsqlparser.compiler.TContext;
import gudusoft.gsqlparser.sqlenv.TSQLEnv;
import gudusoft.gsqlparser.compiler.TGlobalScope;
import gudusoft.gsqlparser.compiler.TFrame;
import gudusoft.gsqlparser.ETokenStatus;

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

/**
 * PostgreSQL database SQL parser implementation.
 *
 * <p>This parser handles PostgreSQL-specific SQL syntax including:
 * <ul>
 *   <li>PL/pgSQL blocks (functions, procedures, triggers)</li>
 *   <li>Dollar quoting ($$...$$)</li>
 *   <li>PostgreSQL-specific DML/DDL</li>
 *   <li>Special operators and functions</li>
 *   <li>Special token handling (%ROWTYPE, %TYPE, etc.)</li>
 * </ul>
 *
 * <p><b>Design Notes:</b>
 * <ul>
 *   <li>Extends {@link AbstractSqlParser}</li>
 *   <li>Can directly instantiate: {@link TLexerPostgresql}, {@link TParserPostgresqlSql}</li>
 *   <li>Uses single parser (no secondary parser like Oracle's PL/SQL)</li>
 *   <li>Delimiter character: ';' for SQL statements</li>
 * </ul>
 *
 * <p><b>Usage Example:</b>
 * <pre>
 * // Get PostgreSQL parser from factory
 * SqlParser parser = SqlParserFactory.get(EDbVendor.dbvpostgresql);
 *
 * // Build context
 * ParserContext context = new ParserContext.Builder(EDbVendor.dbvpostgresql)
 *     .sqlText("SELECT * FROM employees WHERE dept_id = 10")
 *     .build();
 *
 * // Parse
 * SqlParseResult result = parser.parse(context);
 *
 * // Access statements
 * TStatementList statements = result.getSqlStatements();
 * </pre>
 *
 * @see SqlParser
 * @see AbstractSqlParser
 * @see TLexerPostgresql
 * @see TParserPostgresql
 * @since 3.2.0.0
 */
public class PostgreSqlParser extends AbstractSqlParser {

    // ========== Lexer and Parser Instances ==========
    // Created once in constructor, reused for all parsing operations

    /** The PostgreSQL lexer used for tokenization (public for TGSqlParser.getFlexer()) */
    public TLexerPostgresql flexer;
    private TParserPostgresql fparser;

    // ========== State Variables ==========
    // NOTE: The following fields moved to AbstractSqlParser (inherited):
    //   - sourcetokenlist (TSourceTokenList)
    //   - sqlstatements (TStatementList)
    //   - parserContext (ParserContext)
    //   - sqlcmds (ISqlCmds) - to be added when PostgreSQL raw extraction is refactored
    //   - globalContext (TContext)
    //   - sqlEnv (TSQLEnv)
    //   - frameStack (Stack<TFrame>)
    //   - globalFrame (TFrame)

    // ========== State Variables for Tokenization ==========
    private boolean insqlpluscmd;
    private boolean isvalidplace;
    private boolean waitingreturnforsemicolon;
    private boolean waitingreturnforfloatdiv;
    private boolean continuesqlplusatnewline;

    // ========== Constructor ==========

    /**
     * Construct PostgreSQL SQL parser.
     * <p>
     * Configures the parser for PostgreSQL database with default delimiter: semicolon (;)
     * <p>
     * Following the original TGSqlParser pattern, the lexer and parser are
     * created once in the constructor and reused for all parsing operations.
     */
    public PostgreSqlParser() {
        super(EDbVendor.dbvpostgresql);

        // Set delimiter character
        this.delimiterChar = ';';
        this.defaultDelimiterStr = ";";

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

        // CRITICAL: Set lexer for inherited getanewsourcetoken() method
        this.lexer = this.flexer;

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

        // NOTE: sourcetokenlist and sqlstatements are initialized in AbstractSqlParser constructor
    }

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

    /**
     * Return the PostgreSQL lexer instance.
     * <p>
     * The lexer is created once in the constructor and reused for all
     * parsing operations. This method simply returns the existing instance,
     * matching the original TGSqlParser pattern where the lexer is created
     * once and reset before each use.
     *
     * @param context parser context (not used, lexer already created)
     * @return the PostgreSQL lexer instance created in constructor
     */
    @Override
    protected TCustomLexer getLexer(ParserContext context) {
        // Return existing lexer instance (created in constructor)
        return this.flexer;
    }

    /**
     * Return the PostgreSQL SQL parser instance with updated token list.
     * <p>
     * The parser is created once in the constructor and reused for all
     * parsing operations. This method updates the token list and returns
     * the existing instance, matching the original TGSqlParser pattern.
     *
     * @param context parser context (not used, parser already created)
     * @param tokens source token list to parse
     * @return the PostgreSQL SQL parser instance created in constructor
     */
    @Override
    protected TCustomParser getParser(ParserContext context, TSourceTokenList tokens) {
        // Update token list for reused parser instance
        this.fparser.sourcetokenlist = tokens;
        return this.fparser;
    }

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

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

    /**
     * Call PostgreSQL-specific raw statement extraction logic.
     * <p>
     * Delegates to dopostgresqlgetrawsqlstatements which handles PostgreSQL's
     * statement delimiters (semicolon for SQL, $$ for PL/pgSQL functions).
     */
    @Override
    protected void extractVendorRawStatements(SqlParseResult.Builder builder) {
        dopostgresqlgetrawsqlstatements(builder);
    }

    /**
     * Perform full parsing of statements with syntax checking.
     * <p>
     * This method orchestrates the parsing of all statements.
     *
     * <p><b>Important:</b> This method does NOT extract raw statements - they are
     * passed in as a parameter already extracted by {@link #extractRawStatements}.
     *
     * @param context parser context
     * @param parser main SQL parser (TParserPostgresql)
     * @param secondaryParser not used for PostgreSQL
     * @param tokens source token list
     * @param rawStatements raw statements already extracted (never null)
     * @return list of fully parsed statements with AST built
     */
    @Override
    protected TStatementList performParsing(ParserContext context,
                                           TCustomParser parser,
                                           TCustomParser secondaryParser,
                                           TSourceTokenList tokens,
                                           TStatementList rawStatements) {
        // Store references (fparser is already set, don't reassign final variable)
        this.sourcetokenlist = tokens;
        this.parserContext = context;

        // Use the raw statements passed from AbstractSqlParser.parse()
        // (already extracted - DO NOT re-extract to avoid duplication)
        this.sqlstatements = rawStatements;

        // Initialize global context for statement parsing
        initializeGlobalContext();

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

            // Set frame stack for the statement (needed for parsing)
            stmt.setFrameStack(frameStack);

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

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

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

        return this.sqlstatements;
    }

    // ========== PostgreSQL-Specific Tokenization ==========

    /**
     * Perform PostgreSQL-specific tokenization.
     * <p>
     * Extracted from TGSqlParser.dopostgresqltexttotokenlist() (lines 3093-3287)
     */
    private void dopostgresqltexttotokenlist() {
        // Initialize state machine
        insqlpluscmd = false;
        isvalidplace = true;
        waitingreturnforfloatdiv = false;
        waitingreturnforsemicolon = false;
        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;
                }

                default: {
                    // Solid token
                    continuesqlplusatnewline = false;
                    waitingreturnforsemicolon = false;
                    waitingreturnforfloatdiv = false;

                    if (insqlpluscmd) {
                        asourcetoken.insqlpluscmd = true;
                        if (asourcetoken.toString().equalsIgnoreCase("-")) {
                            continuesqlplusatnewline = true;
                        }
                    } else {
                        if (asourcetoken.tokentype == ETokenType.ttsemicolon) {
                            waitingreturnforsemicolon = true;
                        }
                        if ((asourcetoken.tokentype == ETokenType.ttslash)
                                && (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;

                    // PostgreSQL-specific keyword handling
                    if (prevst != null) {
                        if (prevst.tokencode == TBaseType.rrw_inner) {
                            if (asourcetoken.tokencode != flexer.getkeywordvalue("JOIN")) {
                                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;
                    }

                    // Additional PostgreSQL transformations
                    if ((asourcetoken.tokencode == flexer.getkeywordvalue("DIRECT_LOAD"))
                            || (asourcetoken.tokencode == flexer.getkeywordvalue("ALL"))) {
                        lcprevst = getprevsolidtoken(asourcetoken);
                        if (lcprevst != null) {
                            if (lcprevst.tokencode == TBaseType.rrw_for)
                                lcprevst.tokencode = TBaseType.rw_for1;
                        }
                    }

                    if (asourcetoken.tokencode == TBaseType.rrw_dense_rank) {
                        TSourceToken stKeep = asourcetoken.searchToken(TBaseType.rrw_keep, -2);
                        if (stKeep != null) {
                            stKeep.tokencode = TBaseType.rrw_keep_before_dense_rank;
                        }
                    }

                    if ((asourcetoken.tokencode == TBaseType.rrw_postgresql_rowtype)
                            || (asourcetoken.tokencode == TBaseType.rrw_postgresql_type)) {
                        TSourceToken stPercent = asourcetoken.searchToken('%', -1);
                        if (stPercent != null) {
                            stPercent.tokencode = TBaseType.rowtype_operator;
                        }
                    }

                    if (asourcetoken.tokencode == TBaseType.JSON_EXIST) {
                        TSourceToken stPercent = asourcetoken.searchToken('=', -1);
                        if (stPercent != null) {
                            asourcetoken.tokencode = TBaseType.ident;
                        }
                    }

                    if (asourcetoken.tokencode == TBaseType.rrw_update) {
                        TSourceToken stDo = asourcetoken.searchToken(TBaseType.rrw_do, -1);
                        if (stDo != null) {
                            asourcetoken.tokencode = TBaseType.rrw_postgresql_do_update;
                        }
                    }

                    break;
                }
            }

            // Get next token
            asourcetoken = getanewsourcetoken();
            if (asourcetoken != null) {
                yychar = asourcetoken.tokencode;
            } else {
                yychar = 0;

                if (waitingreturnforfloatdiv) {
                    lct.tokencode = TBaseType.sqlpluscmd;
                    if (lct.tokentype != ETokenType.ttslash) {
                        lct.tokentype = ETokenType.ttsqlpluscmd;
                    }
                }
            }

            if ((yychar == 0) && (prevst != null)) {
                if (prevst.tokencode == TBaseType.rrw_inner) {
                    prevst.tokencode = TBaseType.ident;
                }
            }
        }
    }

    /**
     * Get next source token from the lexer.
     * <p>
     * This method wraps the lexer's yylexwrap() call.
     *
     * @return next source token, or null if end of input
     */

    /**
     * Check if token represents a valid SQL*Plus-like command in PostgreSQL.
     *
     * @param tokenText token text to check
     * @return true if valid SQL*Plus command
     */
    private boolean isvalidsqlpluscmdInPostgresql(String tokenText) {
        // PostgreSQL supports psql meta-commands like \d, \dt, etc.
        // For now, keep compatible with original implementation
        return false;
    }

    /**
     * Determine if forward slash should be treated as SQL*Plus command delimiter.
     *
     * @param pstlist token list
     * @param pPos position of '/' token
     * @return true if '/' should be SQL*Plus command
     */
    private boolean isValidPlaceForDivToSqlplusCmd(TSourceTokenList pstlist, int pPos) {
        boolean ret = false;

        if ((pPos <= 0) || (pPos > pstlist.size() - 1)) return ret;

        TSourceToken lcst = pstlist.get(pPos - 1);
        if (lcst.tokentype != ETokenType.ttreturn) {
            return ret;
        }

        if (!(lcst.getAstext().charAt(lcst.getAstext().length() - 1) == ' ')) {
            ret = true;
        }

        return ret;
    }

    /**
     * Get previous non-whitespace token.
     *
     * @param ptoken current token
     * @return previous solid token, or null
     */
    private TSourceToken getprevsolidtoken(TSourceToken ptoken) {
        TSourceToken ret = null;
        TSourceTokenList lctokenlist = ptoken.container;

        if (lctokenlist != null) {
            if ((ptoken.posinlist > 0) && (lctokenlist.size() > ptoken.posinlist - 1)) {
                if (!(
                        (lctokenlist.get(ptoken.posinlist - 1).tokentype == ETokenType.ttwhitespace)
                        || (lctokenlist.get(ptoken.posinlist - 1).tokentype == ETokenType.ttreturn)
                        || (lctokenlist.get(ptoken.posinlist - 1).tokentype == ETokenType.ttsimplecomment)
                        || (lctokenlist.get(ptoken.posinlist - 1).tokentype == ETokenType.ttbracketedcomment)
                )) {
                    ret = lctokenlist.get(ptoken.posinlist - 1);
                } else {
                    ret = lctokenlist.nextsolidtoken(ptoken.posinlist - 1, -1, false);
                }
            }
        }
        return ret;
    }

    // ========== PostgreSQL-Specific Raw Statement Extraction ==========

    /**
     * Extract raw PostgreSQL SQL statements from tokenized source.
     * <p>
     * Extracted from TGSqlParser.dopostgresqlgetrawsqlstatements() (lines 8051-8492)
     *
     * @param builder the result builder to populate with raw statements
     */
    private void dopostgresqlgetrawsqlstatements(SqlParseResult.Builder builder) {
        int waitingEnd = 0;
        boolean foundEnd = false, enterDeclare = false;
        boolean isSinglePLBlock = false;

        if (TBaseType.assigned(sqlstatements)) sqlstatements.clear();
        if (!TBaseType.assigned(sourcetokenlist)) {
            // No tokens available - populate builder with empty results and return
            builder.sqlStatements(this.sqlstatements);
            builder.errorCode(1);
            builder.errorMessage("No source token list available");
            return;
        }

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

        if (isSinglePLBlock) {
            gcurrentsqlstatement = new TCommonBlock(EDbVendor.dbvpostgresql);
        }

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

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

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

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

            // Token transformations during raw statement extraction
            performRawStatementTokenTransformations(ast);

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

                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) {
                            appendToken(gcurrentsqlstatement, ast);
                        }

                        if ((lcprevsolidtoken != null) && (ast.tokentype == ETokenType.ttsemicolon)) {
                            if (lcprevsolidtoken.tokentype == ETokenType.ttsemicolon) {
                                ast.tokentype = ETokenType.ttsimplecomment;
                                ast.tokencode = TBaseType.cmtdoublehyphen;
                            }
                        }

                        continue;
                    }

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

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

                    if (gcurrentsqlstatement != null) {
                        enterDeclare = false;
                        if (gcurrentsqlstatement.ispgplsql()) {
                            gst = EFindSqlStateType.ststoredprocedure;
                            appendToken(gcurrentsqlstatement, ast);
                            foundEnd = false;
                            if ((ast.tokencode == TBaseType.rrw_begin)
                                    || (ast.tokencode == TBaseType.rrw_package)
                                    || (ast.searchToken(TBaseType.rrw_package, 4) != null)) {
                                waitingEnd = 1;
                            } else if (ast.tokencode == TBaseType.rrw_declare) {
                                enterDeclare = true;
                            }
                        } else {
                            gst = EFindSqlStateType.stsql;
                            appendToken(gcurrentsqlstatement, ast);
                        }
                    } else {
                        // Error token found
                        this.syntaxErrors.add(new TSyntaxError(ast.getAstext(), ast.lineNo,
                                (ast.columnNo < 0 ? 0 : ast.columnNo),
                                "Error when tokenize", 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;
                        appendToken(gcurrentsqlstatement, ast);
                    }

                    break;
                }

                case stsqlplus: {
                    if (ast.insqlpluscmd) {
                        appendToken(gcurrentsqlstatement, ast);
                    } else {
                        gst = EFindSqlStateType.stnormal;
                        appendToken(gcurrentsqlstatement, ast);
                        onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, null, this.sqlstatements, false, builder);
                    }

                    break;
                }

                case stsql: {
                    if (ast.tokentype == ETokenType.ttsemicolon) {
                        gst = EFindSqlStateType.stnormal;
                        appendToken(gcurrentsqlstatement, ast);
                        gcurrentsqlstatement.semicolonended = ast;
                        onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, null, this.sqlstatements, false, builder);
                        continue;
                    }

                    if (sourcetokenlist.sqlplusaftercurtoken()) {
                        gst = EFindSqlStateType.stnormal;
                        appendToken(gcurrentsqlstatement, ast);
                        onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, null, this.sqlstatements, false, builder);
                        continue;
                    }

                    if (ast.tokencode == TBaseType.cmtdoublehyphen) {
                        if (ast.toString().trim().endsWith(TBaseType.sqlflow_stmt_delimiter_str)) {
                            gst = EFindSqlStateType.stnormal;
                            onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, null, this.sqlstatements, false, builder);
                            continue;
                        }
                    }

                    appendToken(gcurrentsqlstatement, ast);
                    break;
                }

                case ststoredprocedure: {
                    if (ast.tokencode == TBaseType.rrw_postgresql_function_delimiter) {
                        appendToken(gcurrentsqlstatement, ast);
                        gst = EFindSqlStateType.ststoredprocedurePgStartBody;
                        continue;
                    }

                    if (ast.tokencode == TBaseType.rrw_postgresql_language) {
                        TSourceToken nextSt = ast.nextSolidToken();
                        if (nextSt != null) {
                            if (gcurrentsqlstatement instanceof TRoutine) {
                                TRoutine p = (TRoutine) gcurrentsqlstatement;
                                p.setRoutineLanguage(nextSt.toString());
                            }
                        }
                    }

                    if ((ast.tokentype == ETokenType.ttsemicolon) && (waitingEnd == 0) && (!enterDeclare)) {
                        gst = EFindSqlStateType.stnormal;
                        appendToken(gcurrentsqlstatement, ast);
                        gcurrentsqlstatement.semicolonended = ast;
                        onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, null, this.sqlstatements, false, builder);
                        continue;
                    }

                    if (ast.tokencode == TBaseType.rrw_begin) {
                        waitingEnd++;
                        enterDeclare = false;
                    } else if (ast.tokencode == TBaseType.rrw_declare) {
                        enterDeclare = true;
                    } else if (ast.tokencode == TBaseType.rrw_if) {
                        if (ast.searchToken(TBaseType.rrw_end, -1) == null) {
                            waitingEnd++;
                        }
                    } else if (ast.tokencode == TBaseType.rrw_case) {
                        if (ast.searchToken(TBaseType.rrw_end, -1) == null) {
                            waitingEnd++;
                        }
                    } else if (ast.tokencode == TBaseType.rrw_loop) {
                        if (ast.searchToken(TBaseType.rrw_end, -1) == null) {
                            waitingEnd++;
                        }
                    } else if (ast.tokencode == TBaseType.rrw_end) {
                        foundEnd = true;
                        waitingEnd--;
                        if (waitingEnd < 0) {
                            waitingEnd = 0;
                        }
                    }

                    if ((ast.tokentype == ETokenType.ttslash) && (ast.tokencode == TBaseType.sqlpluscmd)) {
                        ast.tokenstatus = ETokenStatus.tsignorebyyacc;
                        gst = EFindSqlStateType.stnormal;
                        onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, null, this.sqlstatements, false, builder);

                        gcurrentsqlstatement = new TSqlplusCmdStatement(vendor);
                        appendToken(gcurrentsqlstatement, ast);
                        onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, null, this.sqlstatements, false, builder);
                    } else if ((ast.tokentype == ETokenType.ttperiod)
                            && (sourcetokenlist.returnaftercurtoken(false))
                            && (sourcetokenlist.returnbeforecurtoken(false))) {
                        ast.tokenstatus = ETokenStatus.tsignorebyyacc;
                        gst = EFindSqlStateType.stnormal;
                        onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, null, this.sqlstatements, false, builder);

                        gcurrentsqlstatement = new TSqlplusCmdStatement(vendor);
                        appendToken(gcurrentsqlstatement, ast);
                        onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, null, this.sqlstatements, false, builder);
                    } else {
                        appendToken(gcurrentsqlstatement, ast);
                        if ((ast.tokentype == ETokenType.ttsemicolon) && (waitingEnd == 0) && (foundEnd)) {
                            gst = EFindSqlStateType.stnormal;
                            onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, null, this.sqlstatements, false, builder);
                        }
                    }

                    if (ast.tokencode == TBaseType.sqlpluscmd) {
                        int m = flexer.getkeywordvalue(ast.getAstext());
                        if (m != 0) {
                            ast.tokencode = m;
                        } else {
                            ast.tokencode = TBaseType.ident;
                        }
                    }

                    if ((gst == EFindSqlStateType.ststoredprocedure) && (ast.tokencode == TBaseType.cmtdoublehyphen)) {
                        if (ast.toString().trim().endsWith(TBaseType.sqlflow_stmt_delimiter_str)) {
                            gst = EFindSqlStateType.stnormal;
                            onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, null, this.sqlstatements, false, builder);
                        }
                    }

                    break;
                }

                case ststoredprocedurePgStartBody: {
                    appendToken(gcurrentsqlstatement, ast);

                    if (ast.tokencode == TBaseType.rrw_postgresql_function_delimiter) {
                        if (gcurrentsqlstatement.sqlstatementtype == ESqlStatementType.sstDoExecuteBlock) {
                            gst = EFindSqlStateType.stnormal;
                            onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, null, this.sqlstatements, false, builder);
                            continue;
                        } else {
                            gst = EFindSqlStateType.ststoredprocedurePgEndBody;
                            continue;
                        }
                    }

                    break;
                }

                case ststoredprocedurePgEndBody: {
                    if (ast.tokentype == ETokenType.ttsemicolon) {
                        gst = EFindSqlStateType.stnormal;
                        appendToken(gcurrentsqlstatement, ast);
                        gcurrentsqlstatement.semicolonended = ast;
                        onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, null, this.sqlstatements, false, builder);
                        continue;
                    } else if (ast.tokencode == TBaseType.cmtdoublehyphen) {
                        if (ast.toString().trim().endsWith(TBaseType.sqlflow_stmt_delimiter_str)) {
                            gst = EFindSqlStateType.stnormal;
                            onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, null, this.sqlstatements, false, builder);
                            continue;
                        }
                    }

                    appendToken(gcurrentsqlstatement, ast);

                    if (ast.tokencode == TBaseType.rrw_postgresql_language) {
                        TSourceToken nextSt = ast.nextSolidToken();
                        if (nextSt != null) {
                            if (gcurrentsqlstatement instanceof TRoutine) {
                                TRoutine p = (TRoutine) gcurrentsqlstatement;
                                p.setRoutineLanguage(nextSt.toString());
                            }
                        }
                    }

                    break;
                }
            }
        }

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

        // Populate builder with results
        builder.sqlStatements(this.sqlstatements);
        builder.syntaxErrors(syntaxErrors instanceof ArrayList ?
                (ArrayList<TSyntaxError>) syntaxErrors : new ArrayList<>(syntaxErrors));
        builder.errorCode(syntaxErrors.isEmpty() ? 0 : syntaxErrors.size());
    }

    /**
     * Handle token transformations during raw statement extraction.
     *
     * @param ast current token being processed
     */
    private void performRawStatementTokenTransformations(TSourceToken ast) {
        if (ast.tokencode == TBaseType.JSON_EXIST) {
            TSourceToken stConstant = ast.searchToken(TBaseType.sconst, 1);
            if (stConstant == null) {
                ast.tokencode = TBaseType.ident;
            }
        } else if (ast.tokencode == TBaseType.rrw_postgresql_POSITION) {
            TSourceToken st1 = ast.nextSolidToken();
            if (st1 != null) {
                if (st1.tokencode == '(') {
                    ast.tokencode = TBaseType.rrw_postgresql_POSITION_FUNCTION;
                }
            }
        } else if (ast.tokencode == TBaseType.rrw_postgresql_ordinality) {
            TSourceToken lcprevst = getprevsolidtoken(ast);

            if (lcprevst != null) {
                if (lcprevst.tokencode == TBaseType.rrw_with) {
                    TSourceToken lcnextst = ast.nextSolidToken();
                    if ((lcnextst != null) && (lcnextst.tokencode == TBaseType.rrw_as)) {
                        // Don't change with to rrw_postgresql_with_lookahead
                    } else {
                        lcprevst.tokencode = TBaseType.rrw_postgresql_with_lookahead;
                    }
                }
            }
        } else if (ast.tokencode == TBaseType.rrw_postgresql_filter) {
            TSourceToken st1 = ast.nextSolidToken();
            if (st1 != null) {
                if (st1.tokencode != '(') {
                    ast.tokencode = TBaseType.ident;
                }
            }
        } else if (ast.tokencode == TBaseType.rrw_postgresql_jsonb) {
            TSourceToken st1 = ast.nextSolidToken();
            if (st1 != null) {
                if (st1.tokencode == '?') {
                    st1.tokencode = TBaseType.OP_JSONB_QUESTION;
                }
            }
        } else if (ast.tokencode == '?') {
            TSourceToken st1 = ast.nextSolidToken();
            if (st1 != null) {
                if (st1.tokencode == TBaseType.sconst) {
                    ast.tokencode = TBaseType.OP_JSONB_QUESTION;
                }
            }
        } else if (ast.tokencode == TBaseType.rrw_values) {
            TSourceToken stParen = ast.searchToken('(', 1);
            if (stParen != null) {
                TSourceToken stInsert = ast.searchToken(TBaseType.rrw_insert, -ast.posinlist);
                if (stInsert != null) {
                    TSourceToken stSemiColon = ast.searchToken(';', -ast.posinlist);
                    if ((stSemiColon != null) && (stSemiColon.posinlist > stInsert.posinlist)) {
                        // Don't treat values(1) as insert values
                    } else {
                        TSourceToken stFrom = ast.searchToken(TBaseType.rrw_from, -ast.posinlist);
                        if ((stFrom != null) && (stFrom.posinlist > stInsert.posinlist)) {
                            // Don't treat values after from keyword as an insert values
                        } else {
                            ast.tokencode = TBaseType.rrw_postgresql_insert_values;
                        }
                    }
                }
            }
        }
    }

    private void appendToken(TCustomSqlStatement statement, TSourceToken token) {
        if (statement == null || token == null) {
            return;
        }
        token.stmt = statement;
        statement.sourcetokenlist.add(token);
    }

    // Note: initializeGlobalContext() inherited from AbstractSqlParser

    /**
     * Override onRawStatementComplete to add PostgreSQL-specific processing.
     *
     * <p>This method handles special processing for stored procedures/functions
     * whose body is written in non-SQL languages (e.g., PL/Python, PL/Perl, PL/R).
     *
     * <p>For such routines, the tokens between dollar-quote delimiters ($$, $function$, etc.)
     * are marked as non-SQL content to prevent parsing errors.
     *
     * @param context parser context
     * @param statement the completed statement
     * @param mainParser main SQL parser
     * @param secondaryParser secondary parser (not used for PostgreSQL)
     * @param statementList list to add the statement to
     * @param isLastStatement whether this is the last statement
     * @param builder result builder for populating parse results
     */
    @Override
    protected void onRawStatementComplete(ParserContext context,
                                         TCustomSqlStatement statement,
                                         TCustomParser mainParser,
                                         TCustomParser secondaryParser,
                                         TStatementList statementList,
                                         boolean isLastStatement,
                                         SqlParseResult.Builder builder) {
        // Call parent implementation for standard processing
        super.onRawStatementComplete(context, statement, mainParser, secondaryParser, statementList, isLastStatement, builder);

        // PostgreSQL-specific: Handle stored procedures with non-SQL bodies
        // (e.g., PL/Python, PL/Perl, PL/R, PL/Java, PL/Tcl)
        if (statement instanceof TRoutine) {
            TRoutine routine = (TRoutine) statement;

            // Check if the routine body is NOT written in SQL/PLPGSQL
            if (!routine.isBodyInSQL()) {
                processNonSqlRoutineBody(routine);
            }
        }
    }

    /**
     * Process a routine whose body is written in a non-SQL language.
     *
     * <p>This method:
     * <ul>
     *   <li>Identifies the dollar-quote delimiters marking the routine body</li>
     *   <li>Marks all tokens between delimiters as non-SQL (sqlpluscmd type)</li>
     *   <li>Extracts and stores the complete routine body text</li>
     * </ul>
     *
     * <p>This prevents the parser from trying to parse Python, Perl, or other
     * language syntax as SQL, which would cause syntax errors.
     *
     * @param routine the routine statement to process
     */
    private void processNonSqlRoutineBody(TRoutine routine) {
        if (routine.sourcetokenlist == null || routine.sourcetokenlist.size() == 0) {
            return;
        }

        TSourceToken st;
        boolean inBody = false;
        StringBuilder routineBodyBuilder = new StringBuilder();

        // Scan through all tokens to find and mark the routine body
        for (int i = 0; i < routine.sourcetokenlist.size(); i++) {
            st = routine.sourcetokenlist.get(i);

            // Check if this is a dollar-quote delimiter
            if (isDollarFunctionDelimiter(st.tokencode, this.vendor)) {
                if (!inBody) {
                    // Start of body - record opening delimiter
                    inBody = true;
                    routineBodyBuilder.append(st.toString());
                } else {
                    // End of body - record closing delimiter
                    inBody = false;
                    routineBodyBuilder.append(st.toString());
                    break;
                }
                continue;
            }

            // If we're inside the body, mark token as non-SQL and collect its text
            if (inBody) {
                st.tokencode = TBaseType.sqlpluscmd;
                routineBodyBuilder.append(st.toString());
            }
        }

        // Store the complete routine body text
        routine.setRoutineBody(routineBodyBuilder.toString());
    }

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

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