package gudusoft.gsqlparser.stmt;


import gudusoft.gsqlparser.*;
import gudusoft.gsqlparser.compiler.TFrame;
import gudusoft.gsqlparser.compiler.TVariable;
import gudusoft.gsqlparser.nodes.*;
import gudusoft.gsqlparser.nodes.mssql.TProcedureOption;
import gudusoft.gsqlparser.sqlenv.TSQLFunction;
import gudusoft.gsqlparser.sqlenv.TSQLTable;
import gudusoft.gsqlparser.stmt.db2.TDb2ReturnStmt;
import gudusoft.gsqlparser.stmt.mssql.TMssqlBlock;
import gudusoft.gsqlparser.stmt.mssql.TMssqlReturn;

/**
 * Create function.
 *
 * Supported database:
 *
 * <ul>
 *     <li>BigQuery</li>
 * </ul>
 */
public class TCreateFunctionStmt extends TRoutine{

    private TMssqlBlock block = null;
    private TMssqlReturn returnStmt = null;
    private TPTNodeList <TProcedureOption> procedureOptions;
    private TObjectName returnTableVaraible = null;
    private TTableElementList returnTableDefinitions = null;
    private TTypeName returnDataType = null;
    private TObjectName externalName = null;
    private int functionType = TBaseType.uftScalar;

    //  private TObjectName outerLabelName = null;
    private TConstant objfile;
    private TConstant linkSymbol;

    private String className;
    private String resourceType;//jar, file
    private String resourceURI;//
    private EFunctionReturnsType returnsType = EFunctionReturnsType.frtScalar;

    //private TTypeName returnDataType = null;
    private int returnMode = TBaseType.function_return_datatype;

    private TExpression sqlExpression;//bigquery
    private String sharedLibraryName;
    private TConstant functionDefinition;
    private TConstant procedureLanguage;


    public void setProcedureOptions(TPTNodeList<TProcedureOption> procedureOptions) {
        this.procedureOptions = procedureOptions;
    }

    public TPTNodeList<TProcedureOption> getProcedureOptions() {
        return procedureOptions;
    }


     // TGSqlParser newParser ;
//    static int gCount;
//    static {
//        //newParser = new TGSqlParser(EDbVendor.dbvpostgresql);
//        gCount = 0;
//    }
    public TCreateFunctionStmt (EDbVendor dbvendor){
        super(dbvendor);
        sqlstatementtype = ESqlStatementType.sstcreatefunction ;
       // newParser = new TGSqlParser(EDbVendor.dbvpostgresql);
    }

    private TObjectName functionName = null;
    @Override
    public TObjectName getStoredProcedureName(){
        return functionName;
    }
    /**
     * The name that you give to the function that you are declaring or defining.
     * @return
     */
    public TObjectName getFunctionName() {
        return functionName;
    }

    public EFunctionReturnsType getReturnsType() {
        return returnsType;
    }

    public int getReturnMode() {
        return returnMode;
    }

    /**
     *
     * @return statements in create function
     */
    public TMssqlBlock getBlock() {
        return block;
    }

    /**
     *
     * @return this is the only return statement in create function.
     */
    public TMssqlReturn getReturnStmt() {
        return returnStmt;
    }

    public TObjectName getReturnTableVaraible() {
        return returnTableVaraible;
    }

    /**
     * when {@link #getReturnsType()} == {@link EFunctionReturnsType#frtMultiStatementTableValue}
     * or {@link EFunctionReturnsType#frtCLRTableValue}
     * returns this table_type_definition
     *
     * @return table_type_definition
     */
    public TTableElementList getReturnTableDefinitions() {
        return returnTableDefinitions;
    }

    /**
     * SQL Server CLR function body: the <code>assembly.class.method</code> name
     * that follows <code>AS EXTERNAL NAME</code>. Null for every function that
     * has a SQL body.
     *
     * @return the external method name, or null
     * @since 4.1.9
     */
    public TObjectName getExternalName() {
        return externalName;
    }
    public TTypeName getReturnDataType() {
        return returnDataType;
    }

    /**
     * this is used for backward compatibility  of .NET version TMssqlCreateFunction.functiontype
     * please use {@link #returnMode} in java version
     * @return
     */
    public int getFunctionType() {
        int ret = this.functionType;
        if (this.returnMode == TBaseType.function_return_table_variable){
            ret = TBaseType.uftMultiStatementTableValued;
        }else if (this.returnMode == TBaseType.function_return_table){
            ret = TBaseType.uftInlineTableValued;
        }
        return ret;
    }

    public TConstant getObjfile() {
        return objfile;
    }
    public TConstant getLinkSymbol() {
        return linkSymbol;
    }
    public String getClassName() {
        return className;
    }
    public String getResourceType() {
        return resourceType;
    }
    public String getResourceURI() {
        return resourceURI;
    }

    public void setSqlExpression(TExpression sqlExpression) {
        this.sqlExpression = sqlExpression;
    }
    public TExpression getSqlExpression() {
        return sqlExpression;
    }
    public String getSharedLibraryName() {
        return sharedLibraryName;
    }
    public TConstant getFunctionDefinition() {
        return functionDefinition;
    }
    public TConstant getProcedureLanguage() {
        return procedureLanguage;
    }


    private void redshiftFunctionDefinition(TCustomSqlStatement psql,TCreateFunctionSqlNode createFunctionNode){
        language = TRoutine.LANGUAGE_UNKNOWN;
        if ((createFunctionNode.getFunctionBody() != null)&&(getProcedureLanguage().toString().equalsIgnoreCase("sql"))){
            language = TRoutine.LANGUAGE_SQL;
            String bodyStr  = createFunctionNode.getFunctionBody().getStartToken().getQuotedString();//.trim();
            if (createFunctionNode.getFunctionBody().getStartToken().toString().startsWith("'")){
                bodyStr = bodyStr.replaceAll("''","'");
            }


            //System.out.println(bodyStr);
            int testLen = 9;
            if (bodyStr.trim().length() < testLen) testLen = bodyStr.trim().length();

            String prefixStr = bodyStr.trim().substring(0,testLen).toLowerCase(java.util.Locale.ROOT);
            boolean isExpression = true;
            TGSqlParser newParser = new TGSqlParser(this.dbvendor);

            // Keep only the column padding (stringBlock first arg 0); the line
            // offset is applied after parsing so it is never capped at 1000.
            int bodyDeltaLine = (int)createFunctionNode.getFunctionBody().getStartToken().lineNo - 1;
            newParser.sqltext =
                    TBaseType.stringBlock(
                            0,
                            (int)createFunctionNode.getFunctionBody().getStartToken().columnNo + createFunctionNode.getFunctionBody().getStartToken().getQuoteSymbolLength()-1
                    )
                    + bodyStr;

            newParser.setFrameStack(getFrameStack());
            int iRet = newParser.parse();
            TReparseCoordinateShifter.shift(newParser, bodyDeltaLine, 0);
            if ((iRet == 0)&&(newParser.getSqlstatements().size() >0)){
//                this.blockBody = new TBlockSqlNode();
//                this.blockBody.setParsed(true);
//                this.blockBody.getBodyStatements().add(newParser.getSqlstatements().get(0));

               this.getBodyStatements().add(newParser.getSqlstatements().get(0));
            }else {
                for(int j=0;j<newParser.getErrorCount();j++){
                    this.parseerrormessagehandle(newParser.getSyntaxErrors().get(j));
                }
            }
        }

    }

    private void postgresqlFunctionDefinition(TCustomSqlStatement psql,TCreateFunctionSqlNode createFunctionNode){
        language =  TRoutine.LANGUAGE_UNKNOWN;
        if ((createFunctionNode.getFunctionBody() != null)&&(getProcedureLanguage()!=null)&&(getProcedureLanguage().toString() !=null)) {
            if (getProcedureLanguage().toString().equalsIgnoreCase("sql")) language = TRoutine.LANGUAGE_SQL;
            else if (getProcedureLanguage().toString().equalsIgnoreCase("plpgsql")) language = TRoutine.LANGUAGE_PLPGSQL;
            else if (getProcedureLanguage().toString().equalsIgnoreCase("'plpgsql'")) language = TRoutine.LANGUAGE_PLPGSQL;

            if ((language == TRoutine.LANGUAGE_SQL)||(language == TRoutine.LANGUAGE_PLPGSQL))
            {

                String bodyStr = createFunctionNode.getFunctionBody().getStartToken().getQuotedString();//.trim();
                // System.out.println(bodyStr);
                //long lineNo = createFunctionNode.getFunctionBody().getStartToken().lineNo ;
                // CREATE OR REPLACE FUNCTION testspg__getString (varchar) RETURNS varchar as ' DECLARE inString alias for $1; begin return ''bob''; end; ' LANGUAGE plpgsql
                // escaped quotes in string literals
                // mantisbt/view.php?id=1331
                if (createFunctionNode.getFunctionBody().getStartToken().toString().startsWith("'")) {
                    bodyStr = bodyStr.replaceAll("''", "'");
                }


                // System.out.println(bodyStr);
                //String prefixStr = bodyStr.trim().substring(0, (bodyStr.trim().length() < 9 ? bodyStr.trim().length() : 9)).toLowerCase();
                //String suffixStr = bodyStr.trim().length() <= 7 ? bodyStr.trim() : bodyStr.trim().substring(bodyStr.trim().length() - 7);
                                
                String bodyStrTrim = bodyStr.trim();
                int bodyStringLength = bodyStrTrim.length();
                String prefixStr = bodyStringLength <= 7 ? bodyStrTrim.toLowerCase(java.util.Locale.ROOT) : bodyStrTrim.substring(0, 7).toLowerCase(java.util.Locale.ROOT);
                String suffixStr = bodyStringLength <= 7 ? prefixStr : bodyStrTrim.substring(bodyStringLength - 7).toLowerCase(java.util.Locale.ROOT);
                                
                boolean isSQLBlock = true;
                TGSqlParser newParser = new TGSqlParser(EDbVendor.dbvpostgresql);
                // Keep only the column padding (stringBlock first arg 0); the
                // line offset is applied after parsing so it is never capped at
                // 1000. The marker line "plpgsql_function_delimiter\n" occupies
                // parser line 1, so the body starts on parser line 2 and the
                // delta there is (lineNo - 2); without the marker it is
                // (lineNo - 1).
                int bodyDeltaLine;
                if ((prefixStr.startsWith("declare")) || (prefixStr.startsWith("begin")) || (prefixStr.startsWith("<<"))
                        // || (((suffixStr.toLowerCase().endsWith("end")) || (suffixStr.toLowerCase().endsWith("end;")))&&((!prefixStr.startsWith("select"))))
                                                   || (((suffixStr.endsWith("end")) || (suffixStr.endsWith("end;")))&&((!prefixStr.startsWith("select"))))
                ) {
                    //bodyStr.replaceAll("''","'");
                    //System.out.println(bodyStr);

                    bodyDeltaLine = (int) createFunctionNode.getFunctionBody().getStartToken().lineNo - 2;
                    newParser.sqltext = "plpgsql_function_delimiter\n"
                            + TBaseType.stringBlock(0, (int) createFunctionNode.getFunctionBody().getStartToken().columnNo + createFunctionNode.getFunctionBody().getStartToken().getQuoteSymbolLength() - 1)
                            + bodyStr;
                } else {
                    bodyDeltaLine = (int) createFunctionNode.getFunctionBody().getStartToken().lineNo - 1;
                    newParser.sqltext =
                            TBaseType.stringBlock(
                                    0,
                                    (int) createFunctionNode.getFunctionBody().getStartToken().columnNo + createFunctionNode.getFunctionBody().getStartToken().getQuoteSymbolLength() - 1)
                                    + bodyStr;
                    isSQLBlock = false;
                }

                newParser.setFrameStack(getFrameStack());
                // we only need a raw parse tree
                // newParser.setOnlyNeedRawParseTree(true);
                int iRet = newParser.parse();
                TReparseCoordinateShifter.shift(newParser, bodyDeltaLine, 0);
                if ((iRet == 0) && (newParser.getSqlstatements().size() > 0)) {
                    if (isSQLBlock) {
                        TCommonBlock lcBlock = (TCommonBlock) newParser.getSqlstatements().get(0);
                        this.blockBody = lcBlock.getBlockBody();
                        this.blockBody.setParent(this);


//                        this.setOuterLabelName(lcBlock.getLabelName());
//                        for(int i=0;i<lcBlock.getDeclareStatements().size();i++){
//                            this.getTopStatement().getSymbolTable().push( new TSymbolTableItem(TObjectName.ttobjVariable,this,lcBlock.getDeclareStatements().get(i)));
//                            this.getDeclareStatements().add(lcBlock.getDeclareStatements().get(i));
//                        }
//                        for(int i=0;i<lcBlock.getBodyStatements().size();i++){
//                            lcBlock.getBodyStatements().get(i).setAlreadyAddToParent(false);
//                            lcBlock.getBodyStatements().get(i).setParentStmt(this);
//                            //commonBlock.getBodyStatements().get(i).doParseStatement(this);
//                            this.getBodyStatements().add(lcBlock.getBodyStatements().get(i));
//                        }
//                        if (lcBlock.getExceptionClause() != null){
//                            this.setExceptionClause(lcBlock.getExceptionClause());
//                        }
//                        for(int i=0;i<lcBlock.getDeclareStatements().size();i++){
//                            this.getTopStatement().getSymbolTable().pop();
//                        }
                    }else{
                        this.getBodyStatements().add(newParser.getSqlstatements().get(0));
//                        TStatementListSqlNode lcStmts = new TStatementListSqlNode();
//                        TStatementSqlNode lcSqlNode = new TStatementSqlNode();
//                        lcSqlNode.setSqlNode(newParser.getSqlstatements().get(0).rootNode);
//                        lcStmts.addStatementSqlNode(lcSqlNode);
//                        this.blockBody = new TBlockSqlNode();
//                        this.blockBody.init(lcStmts);
                    }
                } else {
                        for (int j = 0; j < newParser.getErrorCount(); j++) {
                            this.parseerrormessagehandle(newParser.getSyntaxErrors().get(j));
                        }
                }

                }
            }

    }

    private void snowflakeFunctionDefinition(TCustomSqlStatement psql,TCreateFunctionSqlNode createFunctionNode){
        language = TRoutine.LANGUAGE_UNKNOWN;
        if ((createFunctionNode.getFunctionBody() != null)
                &&((getProcedureLanguage()==null)||((getProcedureLanguage()!=null)&&(getProcedureLanguage().toString().equalsIgnoreCase("SQL"))))){
                language = TRoutine.LANGUAGE_SQL;
                String bodyStr  = createFunctionNode.getFunctionBody().getStartToken().getQuotedString();//.trim();
                // CREATE OR REPLACE FUNCTION testspg__getString (varchar) RETURNS varchar as ' DECLARE inString alias for $1; begin return ''bob''; end; ' LANGUAGE plpgsql
                // escaped quotes in string literals
                // mantisbt/view.php?id=1331
                if (createFunctionNode.getFunctionBody().getStartToken().toString().startsWith("'")){
                    // Unescape C-style backslash escapes (\' -> ') before SQL-standard escapes
                    // Snowflake supports both \' and '' for escaping single quotes in string literals
                    bodyStr = bodyStr.replace("\\'", "'");
                    bodyStr = bodyStr.replaceAll("''","'");
                }

                //System.out.println(bodyStr);
                String trimmedBody = bodyStr.trim();

                // Skip re-parsing for non-SQL bodies (e.g., SPCS URL paths like '/echo')
                if (trimmedBody.length() > 0 && !Character.isLetterOrDigit(trimmedBody.charAt(0))
                        && trimmedBody.charAt(0) != '(' && trimmedBody.charAt(0) != '-'
                        && trimmedBody.charAt(0) != '+' && trimmedBody.charAt(0) != '\'') {
                    return;
                }

                int testLen = 9;
                if (trimmedBody.length() < testLen) testLen = trimmedBody.length();
                String prefixStr = trimmedBody.substring(0,testLen).toLowerCase(java.util.Locale.ROOT);
                boolean isExpression = true;
                TGSqlParser newParser = new TGSqlParser(this.dbvendor);
                // Keep only the column padding (stringBlock first arg 0); the
                // line offset is applied after parsing so it is never capped at
                // 1000. The marker line "pseudo_expr_sign\n" occupies parser
                // line 1, so the body starts on parser line 2 and the delta
                // there is (lineNo - 2); without the marker it is (lineNo - 1).
                int bodyDeltaLine;
                if ((prefixStr.startsWith("select"))||(prefixStr.startsWith("insert"))||(prefixStr.startsWith("delete"))||(prefixStr.startsWith("update"))){
                    bodyDeltaLine = (int)createFunctionNode.getFunctionBody().getStartToken().lineNo - 1;
                    newParser.sqltext =
                            TBaseType.stringBlock(
                                    0,
                                    (int)createFunctionNode.getFunctionBody().getStartToken().columnNo + createFunctionNode.getFunctionBody().getStartToken().getQuoteSymbolLength()-1)
                                + bodyStr;

                    isExpression = false;
                }else{
                    bodyDeltaLine = (int)createFunctionNode.getFunctionBody().getStartToken().lineNo - 2;
                    newParser.sqltext = "pseudo_expr_sign\n"+
                             TBaseType.stringBlock(0
                            ,(int)createFunctionNode.getFunctionBody().getStartToken().columnNo+ createFunctionNode.getFunctionBody().getStartToken().getQuoteSymbolLength()-1)
                            +bodyStr;
                }

                newParser.setFrameStack(getFrameStack());
                int iRet = newParser.parse();
                TReparseCoordinateShifter.shift(newParser, bodyDeltaLine, 0);
                if ((iRet == 0)&&(newParser.getSqlstatements().size() >0)){
                    TCustomSqlStatement lcBodyStmt = newParser.getSqlstatements().get(0);
                    if (!isExpression){
                        // A single-quoted body holds the same thing a $$...$$ body does, so
                        // expose it the same way: getBlockBody() must not depend on which
                        // delimiter the author used (MantisBT 4723). The $$ branch in
                        // doParseStatement() assigns createFunctionNode.getBlcok(); there is no
                        // grammar block node here, so wrap the re-parsed statement in one.
                        //
                        // ORDER MATTERS. TBlockSqlStatement.getBodyStatements() returns the
                        // fallback bodyStatements list only WHILE blockBody is null, and
                        // delegates to blockBody.getBodyStatements() once it is set. Filling the
                        // block before publishing it keeps getBodyStatements() returning the body
                        // throughout - assigning blockBody first would make it answer from an
                        // empty list and silently drop the lineage that already worked.
                        TBlockSqlNode lcBlock = new TBlockSqlNode();
                        // Constructing the node directly skips the node factory, which would
                        // otherwise stamp these. Without them the block reports dbvgeneric and
                        // node type 0, so a consumer that switches on either mis-identifies the
                        // one node this fix exists to expose.
                        lcBlock.setGsqlparser(this.getGsqlparser());
                        lcBlock.setNodeType(ENodeType.T_BlockSqlNode.getId());
                        lcBodyStmt.setParent(lcBlock);
                        lcBlock.getBodyStatements().add(lcBodyStmt);
                        lcBlock.setParsed(true);
                        lcBlock.setParent(this);
                        // A synthetic node starts with no token boundaries, which leaves
                        // toString() null and getLineNo() -1 while the $$ block reports the body
                        // text and its real position. Take the boundaries from the re-parsed body
                        // statement - TReparseCoordinateShifter.shift() has already moved it onto
                        // the outer statement's coordinates, so these positions line up with the
                        // original SQL rather than with the throwaway parser's text.
                        lcBlock.setStartToken(lcBodyStmt);
                        lcBlock.setEndToken(lcBodyStmt);
                        this.blockBody = lcBlock;
                    } else {
                        // An expression body (AS 'a + b') is re-parsed behind a synthetic
                        // pseudo_expr_sign statement. That wrapper is not a routine block, so it
                        // stays out of blockBody and is reachable through getBodyStatements().
                        this.getBodyStatements().add(lcBodyStmt);
                    }
                }else {
                    for(int j=0;j<newParser.getErrorCount();j++){
                        this.parseerrormessagehandle(newParser.getSyntaxErrors().get(j));
                    }
                }
        }

    }

    private TSelectSqlStatement sqlQuery;

    public TSelectSqlStatement getSqlQuery() {
        return sqlQuery;
    }

    public int doParseStatement(TCustomSqlStatement psql) {
        if (rootNode == null) return -1;
        // A failed parse can leave a TParseErrorSqlNode (or a node from a
        // previous statement) in rootNode; never cast blindly.
        if (!(rootNode instanceof TCreateFunctionSqlNode)) return -1;
        TCreateFunctionSqlNode createFunctionNode = (TCreateFunctionSqlNode)rootNode;
        if (dbvendor == EDbVendor.dbvpostgresql){
            if (super.doParseStatement(psql) != 0) return -1;
        }else
            super.doParseStatement(psql);

        TFrame currentFrame = new TFrame(this.stmtScope);
        currentFrame.pushMeToStack(getFrameStack());

        functionName = createFunctionNode.getFunctionName();

        if (getSqlEnv() != null) {
            getSqlEnv().addFunction(functionName,true);

            // move to TDatabaseObjectResolver

//            if (getSqlEnv().getDefaultCatalogName() != null){
//                if (functionName.getDatabaseToken() == null){
//                    functionName.setDatabaseToken(new TSourceToken(getSqlEnv().getDefaultCatalogName()));
//                }
//            }
        }

        // sql server
        procedureOptions = createFunctionNode.getProcedureOptions();
        //end sql server

        if (createFunctionNode.getProcedureLanguage() != null){
            // language name is retrieved through parser
            procedureLanguage = createFunctionNode.getProcedureLanguage();
            setRoutineBodyInConstant(procedureLanguage);
            setRoutineLanguage(procedureLanguage.toString());
        }else if (getRoutineLanguage() != null){
            // language name is retrieved during TGsqlParser.dopostgresqlgetrawsqlstatements()
        }

//        procedureLanguage = createFunctionNode.getProcedureLanguage();
//        if (procedureLanguage != null){
//            setRoutineBodyInConstant(procedureLanguage);
//            setRoutineLanguage(procedureLanguage.toString());
//        }
//
//        // postgresql
//        procedureLanguage = createFunctionNode.getProcedureLanguage();
//        if (getRoutineLanguage() == null){
//            // not already set during TGsqlParser.dopostgresqlgetrawsqlstatements()
//            // then we set it here
//            if (procedureLanguage != null){
//                setRoutineLanguage(procedureLanguage.toString());
//            }
//        }

        //outerLabelName = createFunctionNode.getLabelName();
        objfile = createFunctionNode.getObjfile();
        linkSymbol = createFunctionNode.getLinkSymbol();
        this.className = createFunctionNode.getClassName();
        this.resourceType = createFunctionNode.getResourceType();
        this.resourceURI = createFunctionNode.getResourceURI();


        if (createFunctionNode.getReturnDataType() != null){
            this.returnMode = TBaseType.function_return_datatype;
            this.returnDataType = createFunctionNode.getReturnDataType();
        }else if (createFunctionNode.getReturnTable() != null){
            TDummy dmy = createFunctionNode.getReturnTable();
            this.returnMode = TBaseType.function_return_table;
            if (dmy.list1 instanceof TTableElementList){ // hana includes TParameterDeclarationList type which is not this type
                this.returnTableDefinitions = (TTableElementList)dmy.list1;
                this.returnTableDefinitions.doParse(this,ESqlClause.unknown);
            }
        }
        // end of postgresql

        this.setParameterDeclarations(createFunctionNode.getParameters());

        // sql server
        if (createFunctionNode.getReturnDataType() != null){
            this.returnMode = TBaseType.function_return_datatype;
            this.returnDataType = createFunctionNode.getReturnDataType();
            this.returnsType = EFunctionReturnsType.frtScalar;
        }else if (createFunctionNode.getReturnTable() != null){
            TDummy dmy = createFunctionNode.getReturnTable();
            if (dmy.node1 != null){
                this.returnsType = EFunctionReturnsType.frtMultiStatementTableValue;
                this.returnMode = TBaseType.function_return_table_variable;
                this.returnTableVaraible = (TObjectName)dmy.node1;
                this.returnTableVaraible.setObjectType(TObjectName.ttobjVariable);
                this.returnTableDefinitions = (TTableElementList)dmy.list1;
                this.returnTableDefinitions.doParse(this,ESqlClause.unknown);

                if (getSqlEnv() != null)  {
                    TSQLTable returnTable = getSqlEnv().addTable(this.returnTableVaraible.toString(),true);
                    //TSQLFunction functionTable = getSqlEnv().searchFunction(functionName.toString());
                    TSQLFunction functionTable = getSqlEnv().searchFunction(functionName);

                    if (functionTable == null){
                        TBaseType.log(String.format("Table function: <%s> is not found in SQL Evn", functionName.toString()),TLog.WARNING,functionName);
                        //System.out.println("Function not found:"+functionName.toString());
                        //System.out.println(getSqlEnv().toString());
                    }

                    for(TTableElement column: this.returnTableDefinitions){
                        if(column.getColumnDefinition()!=null && column.getColumnDefinition().getColumnName()!=null){
                            if (returnTable != null) {
                                // addTable returns null when enableGetMetadataFromDDL is off
                                returnTable.addColumn(column.getColumnDefinition().getColumnName().toString());
                            }
                            if (functionTable != null) {
                                functionTable.addReturnColumn(column.getColumnDefinition().getColumnName().toString());
                            }
                        }
                    }
                }
            }else if ((createFunctionNode.getExternalName() != null)
                    && (dmy.list1 instanceof TTableElementList)){
                // CLR table-valued function: RETURNS TABLE (column_list) with no
                // table variable and no SQL body, the rows come from EXTERNAL NAME.
                // The EXTERNAL NAME is what distinguishes this from the other
                // dialects that also hang a list off RETURNS TABLE.
                this.returnsType = EFunctionReturnsType.frtCLRTableValue;
                this.returnMode = TBaseType.function_return_table;
                this.returnTableDefinitions = (TTableElementList)dmy.list1;
                this.returnTableDefinitions.doParse(this,ESqlClause.unknown);

                if (getSqlEnv() != null)  {
                    TSQLFunction functionTable = getSqlEnv().searchFunction(functionName);

                    if (functionTable == null){
                        TBaseType.log(String.format("Table function: <%s> is not found in SQL Evn", functionName.toString()),TLog.WARNING,functionName);
                    }else{
                        for(TTableElement column: this.returnTableDefinitions){
                            if(column.getColumnDefinition()!=null && column.getColumnDefinition().getColumnName()!=null){
                                functionTable.addReturnColumn(column.getColumnDefinition().getColumnName().toString());
                            }
                        }
                    }
                }
            }else{
                this.returnMode = TBaseType.function_return_table;
                this.returnsType = EFunctionReturnsType.frtInlineTableValue;
            }
        }

        // The CLR method name is not a database object, so it is exposed through
        // getExternalName() but deliberately kept out of acceptChildren() — the
        // resolver must not try to bind assembly.class.method to a table column.
        if (createFunctionNode.getExternalName() != null){
            this.externalName = createFunctionNode.getExternalName();
        }
        // end sql server

        // push parameterDeclarations into symbolTable
        if (this.getParameterDeclarations() != null){
            for(int i=0;i< this.getParameterDeclarations().size();i++){
                this.getTopStatement().getSymbolTable().push( new TSymbolTableItem(TObjectName.ttobjParameter,this, this.getParameterDeclarations().getParameterDeclarationItem(i)));
                TParameterDeclaration parameterDeclaration = this.getParameterDeclarations().getParameterDeclarationItem(i);
                if (parameterDeclaration.getParameterName() != null){
                    this.stmtScope.addSymbol(new TVariable(parameterDeclaration.getParameterName(),parameterDeclaration,functionName));
                }
            }
        }


        switch (this.dbvendor){
            case dbvsnowflake:
                if (createFunctionNode.getBlcok() != null){
                    // $$ body $$ 在这里处理
                    createFunctionNode.getBlcok().doParse(this,ESqlClause.unknown);
                    this.blockBody = createFunctionNode.getBlcok();
                }else  {
                    // 'body' 在这里处理
                    snowflakeFunctionDefinition(psql,createFunctionNode);
                }

                break;
            case dbvpostgresql:
            case dbvgreenplum:
            case dbvredshift:
                if (createFunctionNode.getFunctionBody() != null){
                    // function body only inside '' will be processed here
                    postgresqlFunctionDefinition(psql,createFunctionNode);
                }else{
                    // function body only inside $$ will be processed here

                    if (createFunctionNode.getBlcok() != null){
                        createFunctionNode.getBlcok().doParse(this,ESqlClause.unknown);
                        this.blockBody = createFunctionNode.getBlcok();
                    }else  if (createFunctionNode.getStmt() != null){
                        createFunctionNode.getStmt().doParse(this, ESqlClause.unknown);
                        this.getBodyStatements().add(createFunctionNode.getStmt().getStmt());
                    }else {
                        // there is no function body is language is not in sql or plsql,
                        // such as LANGUAGE plpython3u
                    }
                }
                break;
//            case dbvgreenplum:
//                postgresqlFunctionDefinition(psql,createFunctionNode);
//                break;
            case dbvmssql:
                if (createFunctionNode.getBlcok() != null){
                    block = new TMssqlBlock(this.dbvendor);
                    block.rootNode = createFunctionNode.getBlcok();
                    block.doParseStatement(this);
                    this.getBodyStatements().add(block);
                }

                if (createFunctionNode.getReturnSqlNode() != null){
                    returnStmt = new TMssqlReturn(this.dbvendor);
                    returnStmt.rootNode = createFunctionNode.getReturnSqlNode();
                    returnStmt.doParseStatement(this);
                    this.getBodyStatements().add(returnStmt);
                }

                break;
            case dbvmysql:
                if (createFunctionNode.getStmt() != null){
                    createFunctionNode.getStmt().doParse(this,ESqlClause.unknown);
                    this.getBodyStatements().add(createFunctionNode.getStmt().getStmt());
                }
                else if (createFunctionNode.getBlcok() != null){
                    createFunctionNode.getBlcok().getStmts().doParse(this,ESqlClause.unknown);

                    for(int i=0;i<createFunctionNode.getBlcok().getStmts().size();i++){
                        this.getBodyStatements().add(createFunctionNode.getBlcok().getStmts().getStatementSqlNode(i).getStmt());
                    }
                }
                break;
            case dbvbigquery:
                if (createFunctionNode.getSqlQuery() != null){
                    sqlQuery = new TSelectSqlStatement(this.dbvendor);
                    sqlQuery.rootNode = createFunctionNode.getSqlQuery();
                    sqlQuery.doParseStatement(this);
                    this.returnMode = TBaseType.function_return_datatype;
                    this.returnDataType = createFunctionNode.getReturnDataType();
                    this.returnsType = EFunctionReturnsType.frtInlineTableValue;
                    this.getBodyStatements().add(sqlQuery);
                }
                break;
            case dbvdb2:
                TCompoundSqlNode compoundSqlNode = createFunctionNode.getCompoundSql();
               // TReturnSqlNode returnSqlNode = createFunctionNode.getReturnSqlNode();

                if (compoundSqlNode != null){
                    if (compoundSqlNode.getDeclareStmts() != null){
                        compoundSqlNode.getDeclareStmts().doParse(this,ESqlClause.unknown);

                        // push variable declare into symbolTable, and add to declareStatements
                        for(int i=0;i<compoundSqlNode.getDeclareStmts().size();i++){
                            this.getTopStatement().getSymbolTable().push( new TSymbolTableItem(TObjectName.ttobjVariable,this,compoundSqlNode.getDeclareStmts().getStatementSqlNode(i).getStmt() ));
                            this.getDeclareStatements().add(compoundSqlNode.getDeclareStmts().getStatementSqlNode(i).getStmt());
                        }
                    }

                    if (compoundSqlNode.getStmts() != null){
                        compoundSqlNode.getStmts().doParse(this,ESqlClause.unknown);

                        for(int i= 0; i<compoundSqlNode.getStmts().size();i++){
                            this.getBodyStatements().add(compoundSqlNode.getStmts().getStatementSqlNode(i).getStmt());
                        }
                    }

                    if (compoundSqlNode.getDeclareStmts() != null){
                        // pop variable declare from symbolTable
                        for(int i=0;i<compoundSqlNode.getDeclareStmts().size();i++){
                            this.getTopStatement().getSymbolTable().pop();
                        }
                    }
                }else if (createFunctionNode.getReturnSqlNode() != null){
                    returnStmt = new TMssqlReturn(this.dbvendor);
                    returnStmt.rootNode = createFunctionNode.getReturnSqlNode();
                    returnStmt.doParseStatement(this);
                    this.getBodyStatements().add(returnStmt);
                }

                break;
            default:
                if (createFunctionNode.getStmt() != null){
                    createFunctionNode.getStmt().doParse(this, ESqlClause.unknown);
                    this.getBodyStatements().add(createFunctionNode.getStmt().getStmt());
//                    TStatementListSqlNode lcStmts = new TStatementListSqlNode();
//                    lcStmts.addStatementSqlNode(createFunctionNode.getStmt());
//                    this.blockBody = new TBlockSqlNode();
//                    this.blockBody.init(lcStmts);
                }
                else if (createFunctionNode.getBlcok() != null){
                    createFunctionNode.getBlcok().doParse(this,ESqlClause.unknown);
                    //createFunctionNode.getBlcok().getStmts().doParse(this,ESqlClause.unknown);
//
//                    for(int i=0;i<createFunctionNode.getBlcok().getStmts().size();i++){
//                        this.getBodyStatements().add(createFunctionNode.getBlcok().getStmts().getStatementSqlNode(i).getStmt());
//                    }
                    this.blockBody = createFunctionNode.getBlcok();
                }
                break;
        }




        // pop parameterDeclarations from symbolTable
        if (this.getParameterDeclarations() != null){
            for(int i=0;i< this.getParameterDeclarations().size();i++){
                this.getTopStatement().getSymbolTable().pop();
            }
        }

        if (createFunctionNode.getSharedLibraryName() != null){
            sharedLibraryName = createFunctionNode.getSharedLibraryName().toString();
        }

        functionDefinition  = createFunctionNode.getFunctionDefinition();
        if (functionDefinition != null){
            setRoutineBodyInConstant(functionDefinition);
            setRoutineBody(functionDefinition.toString());
        }

        this.sqlExpression = createFunctionNode.getSqlExpression();
        if (this.sqlExpression != null){
            this.sqlExpression.doParse(this,ESqlClause.unknown);
        }

        // StarRocks-specific fields
        this.starrocksGlobal = createFunctionNode.isStarrocksGlobal();
        this.starrocksAggregate = createFunctionNode.isStarrocksAggregate();
        this.starrocksTableFunction = createFunctionNode.isStarrocksTableFunction();
        this.starrocksOrReplace = createFunctionNode.isStarrocksOrReplace();
        this.starrocksIntermediateType = createFunctionNode.getStarrocksIntermediateType();
        this.starrocksProperties = createFunctionNode.getStarrocksProperties();

        //endlabelName = createFunctionNode.getEndlabelName();
        currentFrame.popMeFromStack(getFrameStack());

        return 0;
    }

    public void accept(TParseTreeVisitor v){
        v.preVisit(this);
        v.postVisit(this);
    }

    public void acceptChildren(TParseTreeVisitor v){
        v.preVisit(this);
        if (this.getFunctionName() != null) this.getFunctionName().acceptChildren(v);
        if (getParameterDeclarations() != null) getParameterDeclarations().acceptChildren(v);
        if (blockBody != null){
            blockBody.acceptChildren(v);
        }else if (getBodyStatements().size() > 0){
            getBodyStatements().acceptChildren(v);
        }
        // Visit sqlExpression for BigQuery functions where body is an expression (e.g., AS ((SELECT ...)))
        // This ensures UNNEST tables and column references inside the expression are collected
        if (sqlExpression != null) {
            sqlExpression.acceptChildren(v);
        }
        if (returnStmt != null) returnStmt.acceptChildren(v);
        v.postVisit(this);
    }


    public void setFunctionName(TObjectName functionName) {
        this.functionName = functionName;
    }

    public void setBlock(TMssqlBlock block) {
        this.block = block;
    }
    public void setReturnStmt(TMssqlReturn returnStmt) {
        this.returnStmt = returnStmt;
    }
    public void setReturnMode(int returnMode) {
        this.returnMode = returnMode;
    }
    public void setReturnTableVaraible(TObjectName returnTableVaraible) {
        this.returnTableVaraible = returnTableVaraible;
    }
    public void setReturnTableDefinitions(TTableElementList returnTableDefinitions) {
        this.returnTableDefinitions = returnTableDefinitions;
    }
    public void setReturnDataType(TTypeName returnDataType) {
        this.returnDataType = returnDataType;
    }
    public void setFunctionType(int functionType) {
        this.functionType = functionType;
    }

    // StarRocks-specific fields
    private boolean starrocksGlobal = false;
    private boolean starrocksAggregate = false;
    private boolean starrocksTableFunction = false;
    private boolean starrocksOrReplace = false;
    private TTypeName starrocksIntermediateType = null;
    private TPTNodeList starrocksProperties = null;

    public boolean isStarrocksGlobal() { return starrocksGlobal; }
    public boolean isStarrocksAggregate() { return starrocksAggregate; }
    public boolean isStarrocksTableFunction() { return starrocksTableFunction; }
    public boolean isStarrocksOrReplace() { return starrocksOrReplace; }
    public TTypeName getStarrocksIntermediateType() { return starrocksIntermediateType; }
    public TPTNodeList getStarrocksProperties() { return starrocksProperties; }

}