package cn.com.ty.lift.common.sql;

import lombok.Data;
import lombok.ToString;
import lombok.experimental.Accessors;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.expression.operators.relational.NotEqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * the rules of illegal sql.
 * @author wcz
 * @since 2020/02/14
 */
@Slf4j
public class IllegalRule {
    // the list of where exp parse from the sql to verify.
    private List<Expression> wheres        = new ArrayList<>();
    // the list of index info parse from the sql to verify.
    private List<IndexInfo>  indexInfos    = new ArrayList<>();
    // the verify info parse from sql, include WHERE & TABLE & JOINS.
    private List<VerifyInfo> verifyInfos   = new ArrayList<>();
    // the verify message.
    private List<String>     verifyMessage = new ArrayList<>();
    private DatabaseMetaData databaseMetaData;
    private String           catalog;
    private String           schema;

    /**
     * the constructor with a original sql.
     *
     * @param originalSql the original sql string.
     */
    public IllegalRule(DatabaseMetaData databaseMetaData, String catalog, String schema, String originalSql) {
        this.databaseMetaData = databaseMetaData;
        this.catalog = catalog;
        this.schema = schema;
        // parse original sql
        parseSql(originalSql);
    }

    private void addMessage(String message, Object... values){
        if(message.contains("%s") && null != values && values.length > 0){
            verifyMessage.add(String.format(message, values));
        }else {
            verifyMessage.add(message);
        }
    }
    /**
     * verify no use WHERE.
     *
     * @param where a where {@link Expression}
     */
    private int whereNoUse(Expression where) {
        if (SqlUtils.isNull(where)) {
            addMessage("illegal sql. not have WHERE condition.");
            return 0;
        }
        return 1;
    }

    /**
     * verify use OR expression in WHERE.
     *
     * @param where a where {@link Expression}
     */
    private int whereHasOr(Expression where) {
        if (where instanceof OrExpression) {
            OrExpression or = (OrExpression) where;
            addMessage("illegal use [%s] in WHERE, will cause discard the index. sql: %s", or.getStringExpression(), or.toString());
            return 0;
        }
        return 1;
    }

    /**
     * verify use NotEqualsTo expression in WHERE.
     *
     * @param where a where {@link Expression}
     */
    private int whereHasNotEqualsTo(Expression where) {
        if (where instanceof NotEqualsTo) {
            NotEqualsTo notEqualsTo = (NotEqualsTo) where;
            addMessage("illegal use [%s] in WHERE, will cause discard the index. sql: %s", notEqualsTo.getStringExpression(), notEqualsTo.toString());
            return 0;
        }
        return 1;
    }

    /**
     * verify use binary expression in WHERE.
     * 1. NOT binary expression
     * 2. the left of binary is FUNCTION.
     * 3. the right of binary is SubSelect.
     *
     * @param where a where {@link Expression}
     */
    private int whereHasNotBinary(Expression where) {
        if (where instanceof BinaryExpression) {
            BinaryExpression binary = (BinaryExpression) where;
            if (binary.isNot()) {
                addMessage("illegal use [%s] in WHERE, will cause discard the index. Sql: %s", binary.getStringExpression(), binary.toString());
                return 0;
            }
            Expression leftExpression = binary.getLeftExpression();
            if (leftExpression instanceof Function) {
                Function function = (Function) leftExpression;
                addMessage("illegal use db function in WHERE, will cause discard the index. sql: %s", function.toString());
                return 0;
            }
            Expression rightExpression = binary.getRightExpression();
            if (rightExpression instanceof SubSelect) {
                SubSelect subSelect = (SubSelect) rightExpression;
                addMessage("illegal use subSelect in WHERE, will cause discard the index. sql: %s", subSelect.toString());
                return 0;
            }
        }
        return 1;
    }

    /**
     * verify use IN expression and the rightItem of IN is SubSelect in WHERE.
     *
     * @param where a where {@link Expression}
     */
    private int whereHasInSubSelect(Expression where) {
        if (where instanceof InExpression) {
            InExpression in = (InExpression) where;
            ItemsList rightItemsList = in.getRightItemsList();
            if (rightItemsList instanceof SubSelect) {
                SubSelect subSelect = (SubSelect) rightItemsList;
                addMessage("illegal use IN (subSelect) in WHERE, Replacing IN with EXISTS(subSelect) is better. sql: %s", subSelect.toString());
                return 0;
            }
        }
        return 1;
    }

    /**
     * verify check NULL in WHERE.
     *
     * @param where a where {@link Expression}
     */
    private int whereCheckNull(Expression where) {
        String lower = where.toString().toLowerCase();
        boolean checkNull = lower.contains("is null") || lower.contains("is not null");
        if (checkNull) {
            addMessage("illegal check null in WHERE, will cause discard the index. sql: %s", where);
            return 0;
        }
        return 1;
    }

    /**
     * verify whether use index on the column.
     *
     * @param indexInfo include catalog & schema & tableName & columnName to Specify a unique index.
     */
    private int columnNoUseIndex(IndexInfo indexInfo, Set<String> indexInfoCacheSet) {
        String fullyQualifiedColumn = indexInfo.fullyQualifiedColumn();
        if(indexInfo.fullyColumn() && indexInfoCacheSet.contains(fullyQualifiedColumn)){
            return 1;
        }
        //get index info from the connection.
        try (ResultSet resultSet = databaseMetaData.getIndexInfo(indexInfo.catalog, indexInfo.schema, indexInfo.tableName, false, true)){
            int indexCount = 0;
            while (resultSet.next()) {
                //索引中的列序列号等于1，才有效, index: 8, label: ORDINAL_POSITION
                if (resultSet.getShort("ORDINAL_POSITION") == 1) {
                    //在索引中的列名列 index：9, label: COLUMN_NAME
                    IndexInfo index = new IndexInfo();
                    //TABLE_CAT
                    index.setCatalog(indexInfo.catalog);
                    //TABLE_SCHEM
                    index.setSchema(indexInfo.schema);
                    //TABLE_NAME
                    index.setTableName(indexInfo.tableName);
                    //COLUMN_NAME
                    index.setColumnName(resultSet.getString("COLUMN_NAME"));
                    indexInfoCacheSet.add(index.fullyQualifiedColumn());
                    indexCount ++;
                }
            }
            if (indexCount > 6) {
                addMessage("Too many indexes (%s > 6) use on table (%s).", indexCount, indexInfo.fullyQualifiedTable());
            }
            log.info("Cache index: {}", indexInfoCacheSet.size());
        } catch (SQLException e) {
            log.error("get index info from connection fail.");
        }
        boolean useIndex = indexInfoCacheSet.contains(fullyQualifiedColumn);
        if (!useIndex) {
            addMessage("Index not use on %s. Example sql: CREATE [UNIQUE|CLUSTERED] INDEX idx_%s ON %s(%s)", fullyQualifiedColumn, indexInfo.columnName, indexInfo.tableName, indexInfo.columnName);
            return 0;
        }
        return 1;
    }
    /**
     * parse original sql to WHERE & TABLE & JOIN TABLES.
     */
    private void parseSql(String originalSql) {
        try {
            log.info("Parsing original sql.");
            if(null == originalSql || originalSql.trim().isEmpty()){
                addMessage("The original sql is null or empty.");
                return;
            }
            Statement statement = CCJSqlParserUtil.parse(originalSql);
            //select <SelectItem> from <FromItem> where <Expression>
            if (isSelect(statement)) {
                //SELECT t.* FROM (SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 WHERE ...) t WHERE t....
                SelectBody selectBody = ((Select) statement).getSelectBody();
                if(isPlainSelect(selectBody)){
                    parseSelect((PlainSelect) selectBody);
                }
            } else if (isUpdate(statement)) {
                //UPDATE table SET column1 = v1 WHERE column2 = v2
                verifyInfos.add(new VerifyInfo().parse((Update) statement));
            } else if (isDelete(statement)) {
                //DELETE FROM table WHERE column = v
                verifyInfos.add(new VerifyInfo().parse((Delete) statement));
            }
        } catch (Exception e) {
            verifyInfos.clear();
            addMessage("Parse original sql fail. %s", e.getMessage());
        }
    }

    /**
     * parse plain select to WHERE & TABLE & JOIN TABLES.
     *
     * @param plainSelect a plain select.
     */
    private void parseSelect(PlainSelect plainSelect) {
        verifyInfos.add(new VerifyInfo().parse(plainSelect));
        FromItem fromItem = plainSelect.getFromItem();
        //if is SubSelect, recursion parse select.
        if (isSubSelect(fromItem)) {
            SelectBody selectBody = ((SubSelect) fromItem).getSelectBody();
            if (isPlainSelect(selectBody)) {
                parseSelect((PlainSelect) selectBody);
            }
        }
    }

    /**
     * parse a join to WHERE & INDEX
     */
    private void parseJoin(VerifyInfo verifyInfo) {
        Expression on = verifyInfo.getWhere();
        Table table = verifyInfo.getTable();
        List<Table> joinTables = verifyInfo.getJoinTables();
        wheres.add(on);
        if (!isBinary(on)) {
            return;
        }
        BinaryExpression binary = (BinaryExpression) on;
        //获得左边表达式
        Expression leftExp = binary.getLeftExpression();
        //获得右边表达式
        Expression rightExp = binary.getRightExpression();
        wheres.add(rightExp);
        if (isBinary(leftExp)) {
            verifyInfo.setWhere(leftExp);
            parseJoin(verifyInfo);
        } else if (isColumn(leftExp) && isColumn(rightExp)) {
            // the alias of primary
            Alias alias = table.getAlias();
            Column leftCol = (Column) leftExp;
            String leftColumn = leftCol.getColumnName();
            Table leftTable = leftCol.getTable();
            Column rightCol = (Column) rightExp;
            String rightColumn = rightCol.getColumnName();
            Table rightTable = rightCol.getTable();
            // if left or right belong to the main table, that verify the index info.
            // if the alias or the table name equals.
            if (SqlUtils.notNull(leftTable, rightTable)) {
                String leftTableName = leftTable.getName();
                String rightTableName = rightTable.getName();
                if (SqlUtils.notNull(alias) && Objects.equals(leftTableName, alias.getName())) {
                    parseTable(table, leftColumn);
                } else if (Objects.equals(leftTableName, table.getName())) {
                    parseTable(table, leftColumn);
                } else if (SqlUtils.notNull(alias) && Objects.equals(rightTableName, alias.getName())) {
                    parseTable(table, rightColumn);
                } else if (Objects.equals(rightTableName, table.getName())) {
                    parseTable(table, rightColumn);
                } else if (verifyInfo.hasJoinTables()) {
                    // if left and right both don't belong to main table, find in the join tables.
                    addMessage("Join column does not contain primary table (%s). sql: %s", table, binary);
                    // the column is belong to the join tables.
                    for (Table joinTable : joinTables) {
                        Alias joinTableAlias = joinTable.getAlias();
                        if (SqlUtils.notNull(joinTableAlias) && Objects.equals(joinTableAlias.getName(), leftTableName)) {
                            parseTable(joinTable, leftColumn);
                        } else if (SqlUtils.notNull(joinTableAlias) && Objects.equals(joinTableAlias.getName(), rightTableName)) {
                            parseTable(joinTable, rightColumn);
                        } else if (Objects.equals(joinTable.getName(), leftTableName)) {
                            parseTable(joinTable, leftColumn);
                        } else if (Objects.equals(joinTable.getName(), rightTableName)) {
                            parseTable(joinTable, rightColumn);
                        }
                    }
                }
            }
        }
    }

    /**
     * parse where exp to WHERE & INDEX.
     */
    private void parseWhere(VerifyInfo verifyInfo) {
        Expression where = verifyInfo.getWhere();
        Table table = verifyInfo.getTable();
        List<Table> joinTables = verifyInfo.getJoinTables();
        wheres.add(where);
        if (!isBinary(where)) {
            return;
        }
        BinaryExpression binary = (BinaryExpression) where;
        //获得右边表达式
        Expression rightExp = binary.getRightExpression();
        wheres.add(rightExp);
        //获得左边表达式
        Expression leftExp = binary.getLeftExpression();
        if (isBinary(leftExp)) {
            verifyInfo.setWhere(leftExp);
            parseWhere(verifyInfo);
        } else if (isColumn(leftExp)) {
            Column leftCol = (Column) leftExp;
            Table leftTable = leftCol.getTable();
            Alias alias = table.getAlias();
            String leftColumn = leftCol.getColumnName();
            // with table alias
            if (SqlUtils.notNull(leftTable, alias) && Objects.equals(leftTable.getName(), alias.getName())) {
                parseTable(table, leftColumn);
                // single table select without alias
            } else if (SqlUtils.isNull(leftTable, alias)) {
                parseTable(table, leftColumn);
            } else if (verifyInfo.hasJoinTables()) {
                // the column is belong to join tables.
                addMessage("Column that are not primary table (%s) in WHERE. sql: %s", table, binary);
                for (Table joinTable : joinTables) {
                    Alias joinTableAlias = joinTable.getAlias();
                    if (SqlUtils.notNull(joinTableAlias) && Objects.equals(joinTableAlias.getName(), leftTable.getName())) {
                        parseTable(joinTable, leftColumn);
                    } else if (Objects.equals(joinTable.getName(), leftTable.getName())) {
                        parseTable(joinTable, leftColumn);
                    }
                }
            }
        }
    }

    /**
     * parse joins exp to to WHERE & INDEX
     */
    private void parseJoins(VerifyInfo verifyInfo) {
        //允许执行join，验证join是否使用索引等等
        if (verifyInfo.hasJoins()) {
            for (Join join : verifyInfo.getJoins()) {
                verifyInfo.setWhere(join.getOnExpression());
                parseJoin(verifyInfo);
            }
        }
    }

    private void parseOrderBy(VerifyInfo verifyInfo) {
        List<OrderByElement> orderByElements = verifyInfo.getOrderByElements();
        Table table = verifyInfo.getTable();
        List<Table> joinTables = verifyInfo.getJoinTables();
        if (SqlUtils.notEmpty(orderByElements)) {
            for (OrderByElement orderByElement : orderByElements) {
                Expression orderBy = orderByElement.getExpression();
                if (!isColumn(orderBy)) {
                    continue;
                }
                Column orderByCol = (Column) orderBy;
                String orderByColumnName = orderByCol.getColumnName();
                Table orderByTable = orderByCol.getTable();
                Alias alias = table.getAlias();
                if (SqlUtils.notNull(orderByTable, alias) && Objects.equals(orderByTable.getName(), alias.getName())) {
                    parseTable(table, orderByColumnName);
                } else if (SqlUtils.isNull(orderByTable, alias)) {
                    parseTable(table, orderByColumnName);
                } else if (verifyInfo.hasJoinTables()) {
                    // the column is belong to join tables.
                    addMessage("Column that are not primary table (%s) in ORDER BY. sql: %s", table, orderByElement);
                    for (Table joinTable : joinTables) {
                        Alias joinTableAlias = joinTable.getAlias();
                        if (SqlUtils.notNull(joinTableAlias) && Objects.equals(joinTableAlias.getName(), orderByTable.getName())) {
                            parseTable(joinTable, orderByColumnName);
                        } else if (Objects.equals(joinTable.getName(), orderByTable.getName())) {
                            parseTable(joinTable, orderByColumnName);
                        }
                    }
                }
            }
        }
    }

    /**
     * parse table and it's column to get index.
     *
     * @param table      a table.
     * @param columnName the column belong to the table.
     */
    private void parseTable(Table table, String columnName) {
        String tableInfo = table.getName();
        log.info("table.name: {}", tableInfo);
        //table : db.tableName
        IndexInfo indexInfo = new IndexInfo();
        String[] tableArray = tableInfo.split("\\.");
        if (tableArray.length == 1) {
            indexInfo.setTableName(tableArray[0]);
        } else {
            indexInfo.setTableName(tableArray[1]);
        }
        indexInfo.setCatalog(catalog);
        indexInfo.setSchema(schema);
        indexInfo.setColumnName(columnName);
        indexInfos.add(indexInfo);
    }
    /**
     * verify the where exp & index info parse from the sql. return finally.
     */
    public boolean verify(Set<String> indexInfoCacheSet) {
        if (verifyInfos.isEmpty()) {
            return false;
        }
        log.info("Parsing all expressions of sql.");
        for (VerifyInfo verifyInfo : verifyInfos) {
            if (verifyInfo.hasTable()) {
                log.info("===============================");
                parseWhere(verifyInfo);
                parseJoins(verifyInfo);
                parseOrderBy(verifyInfo);
            } else {
                wheres.add(verifyInfo.getWhere());
            }
        }

        int whereTotal = wheres.size();
        int indexTotal = indexInfos.size();

        log.info("parse expression complete, WHERE: {}, INDEX: {}.", whereTotal, indexTotal);
        int verifyCount = 0;
        if(!wheres.isEmpty()){
            log.info("Verifying all WHERE expressions.");
            for (int i = 0; i < whereTotal; i++) {
                Expression where = wheres.get(i);
                log.info("{}. where: {}", (i + 1), where);
                int whereNoUse = whereNoUse(where);
                verifyCount += whereNoUse;
                if (whereNoUse > 0) {
                    verifyCount += whereHasOr(where);
                    verifyCount += whereHasNotEqualsTo(where);
                    verifyCount += whereHasNotBinary(where);
                    verifyCount += whereHasInSubSelect(where);
                    verifyCount += whereCheckNull(where);
                }
            }
        }
        if(!indexInfos.isEmpty()){
            log.info("Verifying all INDEX on column.");
            for (int i = 0; i < indexTotal; i++) {
                IndexInfo indexInfo = indexInfos.get(i);
                log.info("{}. indexInfo: {}", (i + 1), indexInfo.fullyQualifiedColumn());
                verifyCount += columnNoUseIndex(indexInfo, indexInfoCacheSet);
            }
        }
        return verifyCount == whereTotal * 6 + indexTotal;
    }

    public void warn() {
        if (verifyMessage.isEmpty()) {
            log.warn("no any message.");
        } else {
            verifyMessage.forEach(m -> {
                log.warn("Warn: {}", m);
            });
        }
    }

    public void error() {
        if (verifyMessage.isEmpty()) {
            log.error("no any message.");
        } else {
            verifyMessage.forEach(m -> {
                log.error("Error: {}", m);
            });
        }
    }

    @Data
    @Accessors(chain = true)
    @ToString
    private class VerifyInfo {
        private Expression           where;
        private Table                table;
        private List<Join>           joins;
        private List<OrderByElement> orderByElements;
        // the list tables in the JOIN condition.
        private List<Table>          joinTables = new ArrayList<>();

        private void parseJoinTables() {
            if (hasJoins()) {
                for (Join join : joins) {
                    joinTables.add((Table) join.getRightItem());
                }
            }
        }

        private VerifyInfo parse(PlainSelect plainSelect) {
            this.setWhere(plainSelect.getWhere());
            this.setJoins(plainSelect.getJoins());
            this.setOrderByElements(plainSelect.getOrderByElements());
            FromItem fromItem = plainSelect.getFromItem();
            // if is Table
            if (fromItem instanceof Table) {
                this.setTable((Table) fromItem);
            }
            parseJoinTables();
            return this;
        }

        private VerifyInfo parse(Update update) {
            this.setWhere(update.getWhere());
            this.setJoins(update.getJoins());
            List<Table> tables = update.getTables();
            if (!tables.isEmpty()) {
                this.setTable(tables.get(0));
            }
            parseJoinTables();
            return this;
        }

        private VerifyInfo parse(Delete delete) {
            this.setWhere(delete.getWhere());
            this.setJoins(delete.getJoins());
            this.setTable(delete.getTable());
            parseJoinTables();
            return this;
        }

        private boolean hasWhere() {
            return null != where;
        }

        private boolean hasTable() {
            return null != table;
        }

        private boolean hasJoins() {
            return null != joins && !joins.isEmpty();
        }

        private boolean hasJoinTables() {
            return !joinTables.isEmpty();
        }
    }

    /**
     * the information of the index.
     */
    @Data
    @Accessors(chain = true)
    @ToString
    private class IndexInfo {
        private String catalog;
        private String schema;
        private String tableName;
        private String columnName;

        public boolean fullyColumn() {
            return SqlUtils.notNull(getTableName(), getColumnName());
        }

        public boolean fullyTable() {
            return SqlUtils.notNull(getTableName());
        }

        public String fullyQualifiedColumn() {
            return fullyColumn() ? String.format("%s.%s.%s.%s", fixCatalog(), fixSchema(), getTableName(), getColumnName()) : null;
        }

        public String fullyQualifiedTable() {
            return fullyTable() ? String.format("%s.%s.%s", fixCatalog(), fixSchema(), getTableName()) : null;
        }

        private String fixCatalog() {
            return SqlUtils.isNull(this.catalog) ? "<catalog>" : this.catalog;
        }

        private String fixSchema() {
            return SqlUtils.isNull(this.schema) ? "<schema>" : this.schema;
        }
    }

    private boolean isSelect(Statement statement) {
        return (statement instanceof Select);
    }

    private boolean isUpdate(Statement statement) {
        return (statement instanceof Update);
    }

    private boolean isDelete(Statement statement) {
        return (statement instanceof Delete);
    }

    private boolean isPlainSelect(Object object) {
        return (object instanceof PlainSelect);
    }

    private boolean isSubSelect(Object object) {
        return (object instanceof SubSelect);
    }

    private boolean isBinary(Expression expression) {
        return (expression instanceof BinaryExpression);
    }

    private boolean isColumn(Expression expression) {
        return (expression instanceof Column);
    }
}