package gudusoft.gsqlparser.sqlcmds;

import gudusoft.gsqlparser.*;
import gudusoft.gsqlparser.stmt.*;

/**
 * Abstract base class for vendor-specific SQL command resolvers.
 * Provides common functionality and the TSqlCmd/TSqlCmdList infrastructure
 * that all vendor implementations share.
 *
 * @since 3.1.0.9
 */
public abstract class AbstractSqlCmds implements ISqlCmds {

    protected EDbVendor vendor;
    protected volatile TSqlCmdList sqlCmdList;
    protected final Object initLock = new Object();
    protected volatile boolean initialized = false;

    /**
     * Temporary field for tracking statement type during issql() processing.
     * Used by both findcte() and issql() methods.
     */
    protected ESqlStatementType gnewsqlstatementtype = ESqlStatementType.sstinvalid;

    /**
     * Constructor for vendor-specific implementation.
     * @param vendor Database vendor this resolver handles
     */
    protected AbstractSqlCmds(EDbVendor vendor) {
        this.vendor = vendor;
    }

    @Override
    public EDbVendor getVendor() {
        return vendor;
    }


    @Override
    public TSqlCmdList getSqlCmdList() {
        ensureInitialized();
        return sqlCmdList;
    }

    /**
     * Ensures command list is initialized using double-checked locking.
     * This provides thread-safe lazy initialization.
     */
    protected void ensureInitialized() {
        if (!initialized) {
            synchronized (initLock) {
                if (!initialized) {
                    sqlCmdList = new TSqlCmdList();
                    initializeCommands();
                    initialized = true;
                }
            }
        }
    }

    /**
     * Initialize vendor-specific commands.
     * Subclasses must implement this to populate sqlCmdList.
     */
    protected abstract void initializeCommands();

    /**
     * Helper method to add a command to the list.
     * Simplifies command initialization in subclasses.
     *
     * @param token1 First token (keyword)
     * @param token2 Second token pattern
     * @param token3 Third token pattern
     * @param token4 Fourth token pattern
     * @param token5 Fifth token pattern
     * @param token6 Sixth token pattern
     * @param token7 Seventh token pattern
     * @param token8 Eighth token pattern
     * @param stmtType Statement type
     */
    protected void addCmd(int token1, String token2, String token3, String token4,
                         String token5, String token6, String token7, String token8,
                         ESqlStatementType stmtType) {
        TSqlCmd cmd = new TSqlCmd();
        cmd.token1 = token1;
        cmd.token2 = token2;
        cmd.token3 = token3;
        cmd.token4 = token4;
        cmd.token5 = token5;
        cmd.token6 = token6;
        cmd.token7 = token7;
        cmd.token8 = token8;
        cmd.sqlstatementtype = stmtType;

        // Handle vendor-specific reserved words (token codes > TBaseType.rrw_abort)
        // These need their string representation set explicitly
        if (token1 > TBaseType.rrw_abort) {
            String tokenStr = getToken1Str(token1);
            if (tokenStr != null && !tokenStr.isEmpty()) {
                cmd.token1Str = tokenStr;
            }
        }

        sqlCmdList.add(cmd);
    }

    /**
     * Replace the statement type of an inherited registration with the same
     * token pattern, in place. Subclasses use this when they must change what
     * a head maps to: a plain re-registration would only append a duplicate
     * that loses the first-registered tie in finddbcmd, and re-ordering the
     * registrations would break the token1 grouping the lookup relies on.
     * Falls back to a normal append when no matching entry exists.
     */
    protected void overrideCmd(int token1, String token2, String token3, ESqlStatementType stmtType) {
        for (int i = 0; i < sqlCmdList.size(); i++) {
            TSqlCmd cmd = (TSqlCmd) sqlCmdList.get(i);
            if ((cmd.token1 == token1)
                    && wordMatches(cmd.token2, token2)
                    && wordMatches(cmd.token3, token3)
                    && isBlankWord(cmd.token4) && isBlankWord(cmd.token5)
                    && isBlankWord(cmd.token6) && isBlankWord(cmd.token7)) {
                cmd.sqlstatementtype = stmtType;
                return;
            }
        }
        addCmd(token1, token2, token3, "", "", "", "", "", stmtType);
    }

    private static boolean isBlankWord(String w) {
        return (w == null) || w.trim().isEmpty();
    }

    private static boolean wordMatches(String stored, String requested) {
        if (isBlankWord(requested)) return isBlankWord(stored);
        return (stored != null) && stored.trim().equalsIgnoreCase(requested.trim());
    }

    /**
     * Get the string representation for vendor-specific token codes.
     * Subclasses should override this method to provide mappings for
     * vendor-specific reserved words (tokens > TBaseType.rrw_abort).
     *
     * @param token1 Token code
     * @return String representation of the token, or null if not applicable
     */
    protected String getToken1Str(int token1) {
        // Default implementation returns null
        // Subclasses override to provide vendor-specific mappings
        return null;
    }

    /**
     * Overloaded helper for simpler commands.
     */
    protected void addCmd(int token1, ESqlStatementType stmtType) {
        addCmd(token1, "", "", "", "", "", "", "", stmtType);
    }

    protected void addCmd(int token1, String token2, ESqlStatementType stmtType) {
        addCmd(token1, token2, "", "", "", "", "", "", stmtType);
    }

    protected void addCmd(int token1, String token2, String token3, ESqlStatementType stmtType) {
        addCmd(token1, token2, token3, "", "", "", "", "", stmtType);
    }

    protected void addCmd(int token1, String token2, String token3, String token4, ESqlStatementType stmtType) {
        addCmd(token1, token2, token3, token4, "", "", "", "", stmtType);
    }

    protected void addCmd(int token1, String token2, String token3, String token4, String token5, ESqlStatementType stmtType) {
        addCmd(token1, token2, token3, token4, token5, "", "", "", stmtType);
    }

    protected void addCmd(int token1, String token2, String token3, String token4, String token5, String token6, ESqlStatementType stmtType) {
        addCmd(token1, token2, token3, token4, token5, token6, "", "", stmtType);
    }

    protected void addCmd(int token1, String token2, String token3, String token4, String token5, String token6, String token7, ESqlStatementType stmtType) {
        addCmd(token1, token2, token3, token4, token5, token6, token7, "", stmtType);
    }

    /**
     * Find command in the list starting from a specific token.
     * This is the core search algorithm used by all vendors.
     *
     * @param pcst Starting token
     * @param cmdList Command list to search
     * @return Found command or null
     */
    protected TSqlCmd finddbcmd(TSourceToken pcst, TSqlCmdList cmdList) {
        if (pcst == null || cmdList == null) return null;
        if (pcst.tokentype != ETokenType.ttkeyword) return null;

        int startIndex = cmdList.getStartIndex(pcst.tokencode);
        if (startIndex == -1) return null;

        TSqlCmd bestMatch = null;
        int maxTokensMatched = 0;

        for (int i = startIndex; i < cmdList.size(); i++) {
            TSqlCmd cmd = (TSqlCmd) cmdList.get(i);

            // First token must match
            if (cmd.token1 != pcst.tokencode) {
                // If we've already found some matches, we can break since commands are grouped by first token
                if (maxTokensMatched > 0) break;
                continue;
            }

            int tokensMatched = 1;
            TSourceToken lcst = pcst;
            boolean matches = true;

            // Check token2
            if (!cmd.token2.isEmpty() && !cmd.token2.equals(" ")) {
                lcst = lcst.nextSolidToken();
                if (!tokenMatches(lcst, cmd.token2)) {
                    matches = false;
                } else {
                    tokensMatched = 2;
                }
            }

            // Check token3
            if (matches && !cmd.token3.isEmpty() && !cmd.token3.equals(" ")) {
                lcst = lcst.nextSolidToken();
                if (!tokenMatches(lcst, cmd.token3)) {
                    matches = false;
                } else {
                    tokensMatched = 3;
                }
            }

            // Check token4
            if (matches && !cmd.token4.isEmpty() && !cmd.token4.equals(" ")) {
                lcst = lcst.nextSolidToken();
                if (!tokenMatches(lcst, cmd.token4)) {
                    matches = false;
                } else {
                    tokensMatched = 4;
                }
            }

            // Check token5
            if (matches && !cmd.token5.isEmpty() && !cmd.token5.equals(" ")) {
                lcst = lcst.nextSolidToken();
                if (!tokenMatches(lcst, cmd.token5)) {
                    matches = false;
                } else {
                    tokensMatched = 5;
                }
            }

            // Check token6
            if (matches && !cmd.token6.isEmpty() && !cmd.token6.equals(" ")) {
                lcst = lcst.nextSolidToken();
                if (!tokenMatches(lcst, cmd.token6)) {
                    matches = false;
                } else {
                    tokensMatched = 6;
                }
            }

            // Check token7
            if (matches && !cmd.token7.isEmpty() && !cmd.token7.equals(" ")) {
                lcst = lcst.nextSolidToken();
                if (!tokenMatches(lcst, cmd.token7)) {
                    matches = false;
                } else {
                    tokensMatched = 7;
                }
            }

            // Check token8
            if (matches && !cmd.token8.isEmpty() && !cmd.token8.equals(" ")) {
                lcst = lcst.nextSolidToken();
                if (!tokenMatches(lcst, cmd.token8)) {
                    matches = false;
                } else {
                    tokensMatched = 8;
                }
            }

            // If this command matches and has more tokens than our current best match, update it
            if (matches && tokensMatched > maxTokensMatched) {
                bestMatch = cmd;
                maxTokensMatched = tokensMatched;

                // If this command ends with an empty token (exact match), we can return immediately
                // as no longer match is possible
                if ((tokensMatched == 2 && cmd.token3.isEmpty()) ||
                    (tokensMatched == 3 && cmd.token4.isEmpty()) ||
                    (tokensMatched == 4 && cmd.token5.isEmpty()) ||
                    (tokensMatched == 5 && cmd.token6.isEmpty()) ||
                    (tokensMatched == 6 && cmd.token7.isEmpty()) ||
                    (tokensMatched == 7 && cmd.token8.isEmpty()) ||
                    (tokensMatched == 8)) {
                    return bestMatch;
                }
            }
        }

        return bestMatch;
    }

    /**
     * Check if a token matches a pattern string.
     * Pattern can be "*" (any), or a literal string.
     */
    private boolean tokenMatches(TSourceToken token, String pattern) {
        if (token == null) return false;
        if (pattern.equals("*")) return true;

        // Check literal match (case-insensitive)
        return token.toString().equalsIgnoreCase(pattern);
    }

    @Override
    public ESqlStatementType getStatementTypeForToken(TSourceToken token) {
        ensureInitialized();
        TSqlCmd cmd = finddbcmd(token, sqlCmdList);
        return (cmd != null) ? cmd.sqlstatementtype : ESqlStatementType.sstinvalid;
    }

    /**
     * Detect Common Table Expression (CTE) for all database vendors.
     * Handles WITH clauses before DELETE, INSERT, SELECT, UPDATE, MERGE.
     *
     * This is a shared method used by all vendor implementations since CTE
     * syntax is similar across vendors (standardized by SQL:1999).
     *
     * @param ptoken The WITH token
     * @return Statement object for the CTE query, or null if not a valid CTE
     */
    protected TCustomSqlStatement findcte(TSourceToken ptoken) {
        TCustomSqlStatement ret = null;
        TSourceToken lctoken = null;
        int lcnested = 0, k, j;
        boolean inXmlNamespaces = false;
        boolean isXmlNamespaces = false;

        int lcpos = ptoken.posinlist;
        TSourceTokenList lcsourcetokenlist = ptoken.container;

        ret = findCteByStructure(ptoken);
        if (ret != null) return ret;

        for (int i = lcpos + 1; i < lcsourcetokenlist.size(); i++) {
            lctoken = lcsourcetokenlist.get(i);

            // Handle XML namespaces (for SQL Server compatibility)
            if (lctoken.tokencode == TBaseType.rrw_xmlnamespaces) {
                inXmlNamespaces = true;
                lcnested = 0;
                continue;
            }

            if (inXmlNamespaces) {
                if (lctoken.tokentype == ETokenType.ttleftparenthesis) lcnested++;
                if (lctoken.tokentype == ETokenType.ttrightparenthesis) {
                    lcnested--;
                    if (lcnested == 0) {
                        inXmlNamespaces = false;
                        isXmlNamespaces = true;
                    }
                }
                continue;
            }

            // Look for AS keyword or after XML namespaces
            if ((lctoken.tokencode == TBaseType.rrw_as) || isXmlNamespaces) {
                lcnested = 0;
                int startPos = i + 1;
                if (isXmlNamespaces) startPos = i;

                for (j = startPos; j < lcsourcetokenlist.size(); j++) {
                    lctoken = lcsourcetokenlist.get(j);
                    if (lctoken.isnonsolidtoken()) continue;
                    if (lctoken.tokentype == ETokenType.ttleftparenthesis) lcnested++;
                    if (lctoken.tokentype == ETokenType.ttrightparenthesis) lcnested--;

                    if (lcnested == 0) {
                        ret = createCteMainStatement(lctoken);
                        if (ret != null) break;
                    }
                }

                // Mark CTE tokens as ignored for raw statement processing
                if (ret != null) {
                    for (k = lcpos + 1; k <= j; k++) {
                        lcsourcetokenlist.get(k).tokenstatus = ETokenStatus.tsignoredbygetrawstatement;
                    }
                    break;
                }
            }
        }

        return ret;
    }

    /**
     * Detect a CTE query by structurally walking over the whole CTE list of the
     * WITH clause instead of scanning for the first statement keyword that sits
     * at parenthesis depth zero.
     *
     * <p>The keyword scan cannot see a main query that is wrapped in its own
     * parentheses — <code>WITH c AS (SELECT ...) (SELECT ...)</code> — because
     * that SELECT never appears at depth zero. It then keeps retrying from every
     * later AS token, which either finds nothing (the WITH clause is reported as
     * a tokenizer error) or latches onto a keyword inside the CTE body and builds
     * the wrong statement class.
     *
     * <p>Vendors that keep their own copy of the legacy scan call this method
     * first and fall back to that copy when it returns null.
     *
     * @param withToken the WITH token that opens the clause
     * @return the statement for the main query, or null when the CTE list does
     *         not have the standard shape or the main query does not start with
     *         a keyword that may follow a CTE list
     */
    protected TCustomSqlStatement findCteByStructure(TSourceToken withToken) {
        TSourceToken mainToken = findMainQueryTokenAfterCteList(withToken);
        if (mainToken == null) return null;

        TCustomSqlStatement ret = createCteMainStatement(mainToken);
        if (ret == null) return null;

        TSourceTokenList list = withToken.container;
        for (int k = withToken.posinlist + 1; k <= mainToken.posinlist; k++) {
            list.get(k).tokenstatus = ETokenStatus.tsignoredbygetrawstatement;
        }
        return ret;
    }

    /**
     * Build the statement object for the main query that follows a WITH clause,
     * based on the keyword that opens it.
     *
     * @param lctoken first keyword of the main query (SELECT, INSERT, ...)
     * @return the statement for that main query, or null if the token does not
     *         open a statement that may follow a CTE list
     */
    private TCustomSqlStatement createCteMainStatement(TSourceToken lctoken) {
        TSourceTokenList lcsourcetokenlist = lctoken.container;
        TCustomSqlStatement ret = null;
        int k;

        if (lctoken.tokencode == TBaseType.rrw_delete) {
            ret = new TDeleteSqlStatement(vendor);
            ret.isctequery = true;
            gnewsqlstatementtype = ESqlStatementType.sstdelete;
            return ret;
        }

        if (lctoken.tokencode == TBaseType.rrw_merge) {
            ret = new TMergeSqlStatement(vendor);
            ret.isctequery = true;
            gnewsqlstatementtype = ESqlStatementType.sstmerge;
            return ret;
        }

        if ((lctoken.tokencode == TBaseType.rrw_insert) || (lctoken.tokencode == TBaseType.rrw_replace)) {
            ret = new TInsertSqlStatement(vendor);
            ret.isctequery = true;
            gnewsqlstatementtype = (lctoken.tokencode == TBaseType.rrw_replace) ? ESqlStatementType.sstmysqlreplace : ESqlStatementType.sstinsert;
            ret.sqlstatementtype = gnewsqlstatementtype;
            ret.dummytag = 1; // select stmt in insert is permitted

            for (k = lctoken.posinlist + 1; k < lcsourcetokenlist.size(); k++) {
                if (lcsourcetokenlist.get(k).isnonsolidtoken()) continue;
                if (lcsourcetokenlist.get(k).tokencode == TBaseType.rrw_values) break;
                if (lcsourcetokenlist.get(k).tokencode == TBaseType.rrw_go) break;
                if (lcsourcetokenlist.get(k).tokentype == ETokenType.ttsemicolon) break;
                if (lcsourcetokenlist.get(k).tokencode == TBaseType.rrw_select) break;
                if (lcsourcetokenlist.get(k).tokencode == TBaseType.rrw_teradata_sel) break;
                if (lcsourcetokenlist.get(k).tokencode == TBaseType.rrw_execute) break;
                if (lcsourcetokenlist.get(k).tokencode == TBaseType.rrw_exec) break;
            }
            if (k > lcsourcetokenlist.size() - 1)
                k = lcsourcetokenlist.size() - 1;

            for (int m = lctoken.posinlist + 1; m <= k; m++) {
                lcsourcetokenlist.get(m).tokenstatus = ETokenStatus.tsignoredbygetrawstatement;
            }

            return ret;
        }

        if ((lctoken.tokencode == TBaseType.rrw_values) && (vendor == EDbVendor.dbvpostgresql || vendor == EDbVendor.dbvmysql)) {
            ret = new TSelectSqlStatement(vendor);
            ret.isctequery = true;
            gnewsqlstatementtype = ESqlStatementType.sstselect;
            return ret;
        }

        // MySQL TABLE statement (TABLE t1 is equivalent to SELECT * FROM t1)
        if ((lctoken.tokencode == TBaseType.rrw_table) && (vendor == EDbVendor.dbvmysql)) {
            ret = new TSelectSqlStatement(vendor);
            ret.isctequery = true;
            gnewsqlstatementtype = ESqlStatementType.sstselect;
            return ret;
        }

        if (lctoken.tokencode == TBaseType.rrw_select) {
            ret = new TSelectSqlStatement(vendor);
            ret.isctequery = true;
            gnewsqlstatementtype = ESqlStatementType.sstselect;
            return ret;
        }

        // Teradata SEL keyword (abbreviation for SELECT)
        if (lctoken.tokencode == TBaseType.rrw_teradata_sel) {
            ret = new TSelectSqlStatement(vendor);
            ret.isctequery = true;
            gnewsqlstatementtype = ESqlStatementType.sstselect;
            return ret;
        }

        if (lctoken.tokencode == TBaseType.rrw_update) {
            ret = new TUpdateSqlStatement(vendor);
            ret.isctequery = true;
            ret.dummytag = 1; // means set clause in update is not found yet
            gnewsqlstatementtype = ESqlStatementType.sstupdate;
            return ret;
        }

        if ((vendor == EDbVendor.dbvhive) && (lctoken.tokencode == TBaseType.rrw_from)) {
            TSourceToken cmdToken = lctoken.searchToken(TBaseType.rrw_insert, 3);
            if (cmdToken != null) {
                ret = new TInsertSqlStatement(vendor);
                ret.isctequery = true;
                gnewsqlstatementtype = ESqlStatementType.sstinsert;
            } else {
                ret = new TSelectSqlStatement(vendor);
                ret.isctequery = true;
                gnewsqlstatementtype = ESqlStatementType.ssthiveFromQuery;
            }
            return ret;
        }

        // BigQuery: CTE followed by FROM pipe syntax (WITH ... FROM t |> ...)
        if ((vendor == EDbVendor.dbvbigquery) && (lctoken.tokencode == TBaseType.rrw_from)) {
            ret = new TUnknownSqlStatement(vendor);
            ret.isctequery = true;
            ret.sqlstatementtype = ESqlStatementType.sstselect;
            gnewsqlstatementtype = ESqlStatementType.sstselect;
            return ret;
        }

        // DuckDB: CTE followed by FROM-first syntax (WITH ... FROM t)
        if ((vendor == EDbVendor.dbvduckdb) && (lctoken.tokencode == TBaseType.rrw_from)) {
            ret = new TSelectSqlStatement(vendor);
            ret.isctequery = true;
            gnewsqlstatementtype = ESqlStatementType.sstselect;
            return ret;
        }

        return null;
    }

    /**
     * Walk over the whole CTE list of a WITH clause and return the first solid
     * token of the main query that follows it. Leading parentheses of a
     * parenthesized main query — WITH c AS (...) (SELECT ...) — are skipped so
     * the caller sees the statement keyword itself.
     *
     * <p>The walk only accepts the standard CTE-list shape
     * <code>[RECURSIVE] name [(col, ...)] AS [[NOT] MATERIALIZED] ( ... ) [, ...]</code>.
     * It returns null for anything else (XMLNAMESPACES, Oracle SEARCH/CYCLE
     * clauses, malformed input) so the caller can fall back to the legacy
     * keyword scan instead of guessing.
     *
     * @param withToken the WITH token that opens the clause
     * @return first solid token of the main query, or null when the CTE list
     *         does not have the expected shape
     */
    private TSourceToken findMainQueryTokenAfterCteList(TSourceToken withToken) {
        TSourceToken token = withToken.nextSolidToken();
        if (token == null) return null;

        // non-identifier-compare: RECURSIVE is a keyword that has no dedicated
        // token code in every dialect, so it is matched by its text.
        if ("RECURSIVE".equalsIgnoreCase(token.toString())) {
            token = token.nextSolidToken();
            if (token == null) return null;
        }

        while (true) {
            // CTE name
            if (!isPlainNameToken(token)) return null;
            token = token.nextSolidToken();
            if (token == null) return null;

            // optional column alias list
            if (token.tokentype == ETokenType.ttleftparenthesis) {
                token = skipParenthesizedGroup(token);
                if (token == null) return null;
            }

            if (token.tokencode != TBaseType.rrw_as) return null;
            token = token.nextSolidToken();
            if (token == null) return null;

            // optional [NOT] MATERIALIZED hint between AS and the CTE body
            while (token.tokentype != ETokenType.ttleftparenthesis) {
                // non-identifier-compare: materialization hint keywords
                String text = token.toString();
                if (!"NOT".equalsIgnoreCase(text) && !"MATERIALIZED".equalsIgnoreCase(text)) return null;
                token = token.nextSolidToken();
                if (token == null) return null;
            }

            // CTE body
            token = skipParenthesizedGroup(token);
            if (token == null) return null;

            if (token.tokencode != ',') break;

            token = token.nextSolidToken();
            if (token == null) return null;
        }

        // The main query may be wrapped in its own parentheses.
        while (token.tokentype == ETokenType.ttleftparenthesis) {
            token = token.nextSolidToken();
            if (token == null) return null;
        }

        return token;
    }

    /**
     * @return true when the token can be a CTE name
     */
    private boolean isPlainNameToken(TSourceToken token) {
        if (token.tokentype == ETokenType.ttleftparenthesis) return false;
        if (token.tokentype == ETokenType.ttrightparenthesis) return false;
        if (token.tokentype == ETokenType.ttsemicolon) return false;
        if (token.tokencode == ',') return false;
        return true;
    }

    /**
     * Skip a balanced parenthesized group.
     *
     * @param openToken the '(' token that opens the group
     * @return the first solid token after the matching ')', or null when the
     *         parentheses are unbalanced or nothing follows the group
     */
    private TSourceToken skipParenthesizedGroup(TSourceToken openToken) {
        TSourceTokenList list = openToken.container;
        int nested = 0;
        for (int i = openToken.posinlist; i < list.size(); i++) {
            TSourceToken t = list.get(i);
            if (t.isnonsolidtoken()) continue;
            if (t.tokentype == ETokenType.ttleftparenthesis) nested++;
            else if (t.tokentype == ETokenType.ttrightparenthesis) {
                nested--;
                if (nested == 0) return t.nextSolidToken();
                if (nested < 0) return null;
            }
        }
        return null;
    }
}