/*
 * Copyright 2019-2023 The Polypheny Project
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.polypheny.db.sql.language.advise;


import com.google.common.collect.ImmutableList;
import com.google.common.collect.Lists;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.Locale;
import java.util.Objects;
import java.util.Set;
import java.util.TreeSet;
import org.apache.calcite.avatica.util.Casing;
import org.polypheny.db.algebra.constant.MonikerType;
import org.polypheny.db.languages.NodeParseException;
import org.polypheny.db.languages.Parser;
import org.polypheny.db.languages.Parser.ParserConfig;
import org.polypheny.db.languages.ParserPos;
import org.polypheny.db.runtime.PolyphenyDbContextException;
import org.polypheny.db.runtime.PolyphenyDbException;
import org.polypheny.db.sql.language.SqlIdentifier;
import org.polypheny.db.sql.language.SqlNode;
import org.polypheny.db.sql.language.SqlSelect;
import org.polypheny.db.sql.language.SqlUtil;
import org.polypheny.db.sql.language.parser.SqlAbstractParserImpl;
import org.polypheny.db.sql.language.parser.SqlParser;
import org.polypheny.db.sql.language.validate.SqlValidatorWithHints;
import org.polypheny.db.util.Advisor;
import org.polypheny.db.util.CoreUtil;
import org.polypheny.db.util.Moniker;
import org.polypheny.db.util.MonikerImpl;
import org.polypheny.db.util.SourceStringReader;
import org.polypheny.db.util.Util;
import org.polypheny.db.util.trace.PolyphenyDbTrace;
import org.slf4j.Logger;


/**
 * An assistant which offers hints and corrections to a partially-formed SQL statement. It is used in the SQL editor user-interface.
 */
public class SqlAdvisor implements Advisor {

    public static final Logger LOGGER = PolyphenyDbTrace.PARSER_LOGGER;
    private static final String HINT_TOKEN = "_suggest_";
    private static final String UPPER_HINT_TOKEN = HINT_TOKEN.toUpperCase( Locale.ROOT );

    // Flags indicating precision/scale combinations
    private final SqlValidatorWithHints validator;
    private final ParserConfig parserConfig;

    // Cache for getPreferredCasing
    private String prevWord;
    private Casing prevPreferredCasing;

    // Reserved words cache
    private Set<String> reservedWordsSet;
    private List<String> reservedWordsList;


    /**
     * Creates a SqlAdvisor with a validator instance and given parser configuration
     *
     * @param validator Validator
     * @param parserConfig parser config
     */
    public SqlAdvisor( SqlValidatorWithHints validator, ParserConfig parserConfig ) {
        this.validator = validator;
        this.parserConfig = parserConfig;
    }


    private char quoteStart() {
        return parserConfig.quoting().string.charAt( 0 );
    }


    private char quoteEnd() {
        char quote = quoteStart();
        return quote == '[' ? ']' : quote;
    }


    /**
     * Gets completion hints for a partially completed or syntactically incorrect sql statement with cursor pointing to the position where completion hints are requested.
     *
     * Writes into <code>replaced[0]</code> the string that is being replaced. Includes the cursor and the preceding identifier. For example, if <code>sql</code> is "select abc^de from t",
     * sets <code>replaced[0]</code> to "abc". If the cursor is in the middle of whitespace, the replaced string is empty. The replaced string is never null.
     *
     * @param sql A partial or syntactically incorrect sql statement for which to retrieve completion hints
     * @param cursor to indicate the 0-based cursor position in the query at
     * @param replaced String which is being replaced (output)
     * @return completion hints
     */
    public List<Moniker> getCompletionHints( String sql, int cursor, String[] replaced ) {
        // search backward starting from current position to find a "word"
        int wordStart = cursor;
        boolean quoted = false;
        while ( wordStart > 0 && Character.isJavaIdentifierPart( sql.charAt( wordStart - 1 ) ) ) {
            --wordStart;
        }
        if ( (wordStart > 0) && (sql.charAt( wordStart - 1 ) == quoteStart()) ) {
            quoted = true;
            --wordStart;
        }

        if ( wordStart < 0 ) {
            return Collections.emptyList();
        }

        // Search forwards to the end of the word we should remove. Eat up trailing double-quote, if any
        int wordEnd = cursor;
        while ( wordEnd < sql.length() && Character.isJavaIdentifierPart( sql.charAt( wordEnd ) ) ) {
            ++wordEnd;
        }
        if ( quoted && (wordEnd < sql.length()) && (sql.charAt( wordEnd ) == quoteEnd()) ) {
            ++wordEnd;
        }

        // remove the partially composed identifier from the sql statement - otherwise we get a parser exception
        String word = replaced[0] = sql.substring( wordStart, cursor );
        if ( wordStart < wordEnd ) {
            sql = sql.substring( 0, wordStart ) + sql.substring( wordEnd );
        }

        final List<Moniker> completionHints = getCompletionHints0( sql, wordStart );

        if ( quoted ) {
            word = word.substring( 1 );
        }

        if ( word.isEmpty() ) {
            return completionHints;
        }

        // If cursor was part of the way through a word, only include hints which start with that word in the result.
        final List<Moniker> result = new ArrayList<>();
        Casing preferredCasing = getPreferredCasing( word );

        boolean ignoreCase = preferredCasing != Casing.UNCHANGED;
        for ( Moniker hint : completionHints ) {
            List<String> names = hint.getFullyQualifiedNames();
            // For now we treat only simple cases where the added name is the last
            // See [POLYPHENYDB-2439] Smart complete for SqlAdvisor
            String cname = Util.last( names );
            if ( cname.regionMatches( ignoreCase, 0, word, 0, word.length() ) ) {
                result.add( hint );
            }
        }

        return result;
    }


    public List<Moniker> getCompletionHints0( String sql, int cursor ) {
        String simpleSql = simplifySql( sql, cursor );
        int idx = simpleSql.indexOf( HINT_TOKEN );
        if ( idx < 0 ) {
            return Collections.emptyList();
        }
        ParserPos pos = new ParserPos( 1, idx + 1 );
        return getCompletionHints( simpleSql, pos );
    }


    /**
     * Returns casing which is preferred for replacement.
     * For instance, {@code en => ename, EN => ENAME}.
     * When input has mixed case, {@code Casing.UNCHANGED} is returned.
     *
     * @param word input word
     * @return preferred casing when replacing input word
     */
    private Casing getPreferredCasing( String word ) {
        if ( word.equals( prevWord ) ) {
            return prevPreferredCasing;
        }
        boolean hasLower = false;
        boolean hasUpper = false;
        int i = 0;
        while ( i < word.length() && !(hasLower && hasUpper) ) {
            int codePoint = word.codePointAt( i );
            hasLower |= Character.isLowerCase( codePoint );
            hasUpper |= Character.isUpperCase( codePoint );
            i += Character.charCount( codePoint );
        }
        Casing preferredCasing;
        if ( hasUpper && !hasLower ) {
            preferredCasing = Casing.TO_UPPER;
        } else if ( !hasUpper && hasLower ) {
            preferredCasing = Casing.TO_LOWER;
        } else {
            preferredCasing = Casing.UNCHANGED;
        }
        prevWord = word;
        prevPreferredCasing = preferredCasing;
        return preferredCasing;
    }


    public String getReplacement( Moniker hint, String word ) {
        Casing preferredCasing = getPreferredCasing( word );
        boolean quoted = !word.isEmpty() && word.charAt( 0 ) == quoteStart();
        return getReplacement( hint, quoted, preferredCasing );
    }


    public String getReplacement( Moniker hint, boolean quoted, Casing preferredCasing ) {
        String name = Util.last( hint.getFullyQualifiedNames() );
        boolean isKeyword = hint.getType() == MonikerType.KEYWORD;
        // If replacement has mixed case, we need to quote it (or not depending on quotedCasing/unquotedCasing
        quoted &= !isKeyword;

        if ( !quoted && !isKeyword && getReservedAndKeyWordsSet().contains( name ) ) {
            quoted = true;
        }

        StringBuilder sb = new StringBuilder( name.length() + (quoted ? 2 : 0) );

        if ( !isKeyword && !Util.isValidJavaIdentifier( name ) ) {
            // needs quotes ==> quoted
            quoted = true;
        }
        String idToAppend = name;

        if ( !quoted ) {
            // id ==preferredCasing==> preferredId ==unquotedCasing==> recasedId
            // if recasedId matches id, then use preferredId
            String preferredId = applyCasing( name, preferredCasing );
            if ( isKeyword || matchesUnquoted( name, preferredId ) ) {
                idToAppend = preferredId;
            } else {
                // Check if we can use unquoted identifier as is: for instance, unquotedCasing==UNCHANGED
                quoted = !matchesUnquoted( name, idToAppend );
            }
        }
        if ( quoted ) {
            sb.append( quoteStart() );
        }
        sb.append( idToAppend );
        if ( quoted ) {
            sb.append( quoteEnd() );
        }

        return sb.toString();
    }


    private boolean matchesUnquoted( String name, String idToAppend ) {
        String recasedId = applyCasing( idToAppend, parserConfig.unquotedCasing() );
        return recasedId.regionMatches( !parserConfig.caseSensitive(), 0, name, 0, name.length() );
    }


    private String applyCasing( String value, Casing casing ) {
        return CoreUtil.strip( value, null, null, null, casing );
    }


    /**
     * Gets completion hints for a syntactically correct sql statement with dummy SqlIdentifier
     *
     * @param sql A syntactically correct sql statement for which to retrieve completion hints
     * @param pos to indicate the line and column position in the query at which completion hints need to be retrieved. For example, "select a.ename, b.deptno from sales.emp a join sales.dept b "on a.deptno=b.deptno where empno=1"; setting pos to 'Line 1, Column 17' returns all the possible column names that can be selected
     * from sales.dept table setting pos to 'Line 1, Column 31' returns all the possible table names in 'sales' schema
     * @return A list of hints ({@link Moniker}) that can fill in at the indicated position
     */
    public List<Moniker> getCompletionHints( String sql, ParserPos pos ) {
        // First try the statement they gave us. If this fails, just return
        // the tokens which were expected at the failure point.
        List<Moniker> hintList = new ArrayList<>();
        SqlNode sqlNode = tryParse( sql, hintList );
        if ( sqlNode == null ) {
            return hintList;
        }

        // Now construct a statement which is bound to fail. (Character 7 BEL is not legal in any SQL statement.)
        final int x = pos.getColumnNum() - 1;
        sql = sql.substring( 0, x ) + " \07" + sql.substring( x );
        tryParse( sql, hintList );

        final Moniker star = new MonikerImpl( ImmutableList.of( "*" ), MonikerType.KEYWORD );
        String hintToken = parserConfig.unquotedCasing() == Casing.TO_UPPER ? UPPER_HINT_TOKEN : HINT_TOKEN;
        if ( hintList.contains( star ) && !isSelectListItem( sqlNode, pos, hintToken ) ) {
            hintList.remove( star );
        }

        // Add the identifiers which are expected at the point of interest.
        try {
            validator.validateSql( sqlNode );
        } catch ( Exception e ) {
            // mask any exception that is thrown during the validation, i.e. try to continue even if the sql is invalid. we are doing a best effort here to try to come up with the requested completion hints
            Util.swallow( e, LOGGER );
        }
        final List<Moniker> validatorHints = validator.lookupHints( sqlNode, pos );
        hintList.addAll( validatorHints );
        return hintList;
    }


    private static boolean isSelectListItem( SqlNode root, final ParserPos pos, String hintToken ) {
        List<SqlNode> nodes = SqlUtil.getAncestry(
                root,
                input -> input instanceof SqlIdentifier && ((SqlIdentifier) input).names.contains( hintToken ),
                input -> Objects.requireNonNull( input ).getPos().startsAt( pos ) );
        assert nodes.get( 0 ) == root;
        nodes = Lists.reverse( nodes );
        return nodes.size() > 2
                && nodes.get( 2 ) instanceof SqlSelect
                && nodes.get( 1 ) == ((SqlSelect) nodes.get( 2 )).getSqlSelectList();
    }


    /**
     * Tries to parse a SQL statement.
     *
     * If succeeds, returns the parse tree node; if fails, populates the list of hints and returns null.
     *
     * @param sql SQL statement
     * @param hintList List of hints suggesting allowable tokens at the point of failure
     * @return Parse tree if succeeded, null if parse failed
     */
    private SqlNode tryParse( String sql, List<Moniker> hintList ) {
        try {
            return parseQuery( sql );
        } catch ( NodeParseException e ) {
            for ( String tokenName : e.getExpectedTokenNames() ) {
                // Only add tokens which are keywords, like '"BY"'; ignore symbols such as '<Identifier>'.
                if ( tokenName.startsWith( "\"" ) && tokenName.endsWith( "\"" ) ) {
                    hintList.add(
                            new MonikerImpl( tokenName.substring( 1, tokenName.length() - 1 ), MonikerType.KEYWORD ) );
                }
            }
            return null;
        } catch ( PolyphenyDbException e ) {
            Util.swallow( e, null );
            return null;
        }
    }


    /**
     * Gets the fully qualified name for a {@link SqlIdentifier} at a given position of a sql statement.
     *
     * @param sql A syntactically correct sql statement for which to retrieve a fully qualified SQL identifier name
     * @param cursor to indicate the 0-based cursor position in the query that represents a SQL identifier for which its fully qualified name is to be returned.
     * @return a {@link Moniker} that contains the fully qualified name of the specified SQL identifier, returns null if none is found or the SQL statement is invalid.
     */
    public Moniker getQualifiedName( String sql, int cursor ) {
        SqlNode sqlNode;
        try {
            sqlNode = parseQuery( sql );
            validator.validateSql( sqlNode );
        } catch ( Exception e ) {
            return null;
        }
        ParserPos pos = new ParserPos( 1, cursor + 1 );
        try {
            return validator.lookupQualifiedName( sqlNode, pos );
        } catch ( PolyphenyDbContextException | AssertionError e ) {
            return null;
        }
    }


    /**
     * Attempts to complete and validate a given partially completed sql statement, and returns whether it is valid.
     *
     * @param sql A partial or syntactically incorrect sql statement to validate
     * @return whether SQL statement is valid
     */
    public boolean isValid( String sql ) {
        SqlSimpleParser simpleParser = new SqlSimpleParser( HINT_TOKEN, parserConfig );
        String simpleSql = simpleParser.simplifySql( sql );
        SqlNode sqlNode;
        try {
            sqlNode = parseQuery( simpleSql );
        } catch ( Exception e ) {
            // if the sql can't be parsed we wont' be able to validate it
            return false;
        }
        try {
            validator.validateSql( sqlNode );
        } catch ( Exception e ) {
            return false;
        }
        return true;
    }


    /**
     * Attempts to parse and validate a SQL statement. Throws the first exception encountered. The error message of this exception is to be displayed on the UI
     *
     * @param sql A user-input sql statement to be validated
     * @return a List of ValidateErrorInfo (null if sql is valid)
     */
    public List<ValidateErrorInfo> validate( String sql ) {
        SqlNode sqlNode;
        List<ValidateErrorInfo> errorList = new ArrayList<>();

        sqlNode = collectParserError( sql, errorList );
        if ( !errorList.isEmpty() ) {
            return errorList;
        }
        try {
            validator.validateSql( sqlNode );
        } catch ( PolyphenyDbContextException e ) {
            ValidateErrorInfo errInfo = new ValidateErrorInfo( e );

            // validator only returns 1 exception now
            errorList.add( errInfo );
            return errorList;
        } catch ( Exception e ) {
            ValidateErrorInfo errInfo =
                    new ValidateErrorInfo(
                            1,
                            1,
                            1,
                            sql.length(),
                            e.getMessage() );

            // parser only returns 1 exception now
            errorList.add( errInfo );
            return errorList;
        }
        return null;
    }


    /**
     * Turns a partially completed or syntactically incorrect sql statement into a simplified, valid one that can be passed into getCompletionHints()
     *
     * @param sql A partial or syntactically incorrect sql statement
     * @param cursor to indicate column position in the query at which completion hints need to be retrieved.
     * @return a completed, valid (and possibly simplified SQL statement
     */
    public String simplifySql( String sql, int cursor ) {
        SqlSimpleParser parser = new SqlSimpleParser( HINT_TOKEN, parserConfig );
        return parser.simplifySql( sql, cursor );
    }


    /**
     * Return an array of SQL reserved and keywords
     *
     * @return an of SQL reserved and keywords
     */
    public List<String> getReservedAndKeyWords() {
        ensureReservedAndKeyWords();
        return reservedWordsList;
    }


    private Set<String> getReservedAndKeyWordsSet() {
        ensureReservedAndKeyWords();
        return reservedWordsSet;
    }


    private void ensureReservedAndKeyWords() {
        if ( reservedWordsSet != null ) {
            return;
        }
        Collection<String> c = SqlAbstractParserImpl.getSql92ReservedWords();
        List<String> l = Arrays.asList( getParserMetadata().getJdbcKeywords().split( "," ) );
        List<String> al = new ArrayList<>();
        al.addAll( c );
        al.addAll( l );
        reservedWordsList = al;
        reservedWordsSet = new TreeSet<>( String.CASE_INSENSITIVE_ORDER );
        reservedWordsSet.addAll( reservedWordsList );
    }


    /**
     * Returns the underlying Parser metadata.
     *
     * To use a different parser (recognizing a different dialect of SQL), derived class should override.
     *
     * @return metadata
     */
    protected SqlAbstractParserImpl.Metadata getParserMetadata() {
        SqlAbstractParserImpl parserImpl = (SqlAbstractParserImpl) parserConfig.parserFactory().getParser( new SourceStringReader( "" ) );
        SqlParser parser = new SqlParser( parserImpl, parserConfig );
        return parser.getMetadata();
    }


    /**
     * Wrapper function to parse a SQL query (SELECT or VALUES, but not INSERT, UPDATE, DELETE, CREATE, DROP etc.), throwing a {@link NodeParseException} if the statement is not syntactically valid.
     *
     * @param sql SQL statement
     * @return parse tree
     * @throws NodeParseException if not syntactically valid
     */
    protected SqlNode parseQuery( String sql ) throws NodeParseException {
        SqlAbstractParserImpl parserImpl = (SqlAbstractParserImpl) parserConfig.parserFactory().getParser( new SourceStringReader( sql ) );
        Parser parser = new SqlParser( parserImpl, parserConfig );
        return (SqlNode) parser.parseStmt();
    }


    /**
     * Attempts to parse a SQL statement and adds to the errorList if any syntax error is found. This implementation uses {@link SqlParser}. Subclass can re-implement this with a different parser implementation
     *
     * @param sql A user-input sql statement to be parsed
     * @param errorList A {@link List} of error to be added to
     * @return {@link SqlNode } that is root of the parse tree, null if the sql is not valid
     */
    protected SqlNode collectParserError( String sql, List<ValidateErrorInfo> errorList ) {
        try {
            return parseQuery( sql );
        } catch ( NodeParseException e ) {
            ValidateErrorInfo errInfo = new ValidateErrorInfo( e.getPos(), e.getMessage() );

            // parser only returns 1 exception now
            errorList.add( errInfo );
            return null;
        }
    }


    /**
     * An inner class that represents error message text and position info of a validator or parser exception
     */
    public class ValidateErrorInfo {

        private int startLineNum;
        private int startColumnNum;
        private int endLineNum;
        private int endColumnNum;
        private String errorMsg;


        /**
         * Creates a new ValidateErrorInfo with the position coordinates and an error string.
         *
         * @param startLineNum Start line number
         * @param startColumnNum Start column number
         * @param endLineNum End line number
         * @param endColumnNum End column number
         * @param errorMsg Error message
         */
        public ValidateErrorInfo( int startLineNum, int startColumnNum, int endLineNum, int endColumnNum, String errorMsg ) {
            this.startLineNum = startLineNum;
            this.startColumnNum = startColumnNum;
            this.endLineNum = endLineNum;
            this.endColumnNum = endColumnNum;
            this.errorMsg = errorMsg;
        }


        /**
         * Creates a new ValidateErrorInfo with an PolyphenyDbContextException.
         *
         * @param e Exception
         */
        public ValidateErrorInfo( PolyphenyDbContextException e ) {
            this.startLineNum = e.getPosLine();
            this.startColumnNum = e.getPosColumn();
            this.endLineNum = e.getEndPosLine();
            this.endColumnNum = e.getEndPosColumn();
            this.errorMsg = e.getCause().getMessage();
        }


        /**
         * Creates a new ValidateErrorInfo with a SqlParserPos and an error string.
         *
         * @param pos Error position
         * @param errorMsg Error message
         */
        public ValidateErrorInfo( ParserPos pos, String errorMsg ) {
            this.startLineNum = pos.getLineNum();
            this.startColumnNum = pos.getColumnNum();
            this.endLineNum = pos.getEndLineNum();
            this.endColumnNum = pos.getEndColumnNum();
            this.errorMsg = errorMsg;
        }


        /**
         * @return 1-based starting line number
         */
        public int getStartLineNum() {
            return startLineNum;
        }


        /**
         * @return 1-based starting column number
         */
        public int getStartColumnNum() {
            return startColumnNum;
        }


        /**
         * @return 1-based end line number
         */
        public int getEndLineNum() {
            return endLineNum;
        }


        /**
         * @return 1-based end column number
         */
        public int getEndColumnNum() {
            return endColumnNum;
        }


        /**
         * @return error message
         */
        public String getMessage() {
            return errorMsg;
        }

    }

}

