/*
 * The MIT License (MIT)
 *
 * Copyright (c) 2014-2022 abel533@gmail.com
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 */

package com.github.pagehelper.parser;

import com.github.pagehelper.JSqlParser;
import com.github.pagehelper.PageException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
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.select.*;

import java.util.*;

/**
 * 将sqlserver查询语句转换为分页语句<br>
 * 注意事项：<br>
 * <ol>
 * <li>请先保证你的SQL可以执行</li>
 * <li>sql中最好直接包含order by，可以自动从sql提取</li>
 * <li>如果没有order by，可以通过入参提供，但是需要自己保证正确</li>
 * <li>如果sql有order by，可以通过orderby参数覆盖sql中的order by</li>
 * <li>order by的列名不能使用别名</li>
 * <li>表和列使用别名的时候不要使用单引号(')</li>
 * </ol>
 * 该类设计为一个独立的工具类，依赖jsqlparser,可以独立使用
 *
 * @author liuzh
 */
public class SqlServerParser {
    //开始行号
    public static final    String     START_ROW                = String.valueOf(Long.MIN_VALUE);
    //结束行号
    public static final    String     PAGE_SIZE                = String.valueOf(Long.MAX_VALUE);
    //外层包装表
    protected static final String     WRAP_TABLE               = "WRAP_OUTER_TABLE";
    //表别名名字
    protected static final String     PAGE_TABLE_NAME          = "PAGE_TABLE_ALIAS";
    //protected
    public static final    Alias      PAGE_TABLE_ALIAS         = new Alias(PAGE_TABLE_NAME);
    //行号
    protected static final String     PAGE_ROW_NUMBER          = "PAGE_ROW_NUMBER";
    //行号列
    protected static final Column     PAGE_ROW_NUMBER_COLUMN   = new Column(PAGE_ROW_NUMBER);
    //TOP 100 PERCENT
    protected static final Top        TOP100_PERCENT;
    //别名前缀
    protected static final String     PAGE_COLUMN_ALIAS_PREFIX = "ROW_ALIAS_";
    private final          JSqlParser jSqlParser;

    //静态方法处理
    static {
        TOP100_PERCENT = new Top();
        TOP100_PERCENT.setExpression(new LongValue(100));
        TOP100_PERCENT.setPercentage(true);
    }

    public SqlServerParser() {
        this.jSqlParser = JSqlParser.DEFAULT;
    }

    public SqlServerParser(JSqlParser jSqlParser) {
        this.jSqlParser = jSqlParser;
    }

    /**
     * 转换为分页语句
     *
     * @param sql
     * @return
     */
    public String convertToPageSql(String sql) {
        return convertToPageSql(sql, null, null);
    }

    /**
     * 转换为分页语句
     *
     * @param sql
     * @param offset
     * @param limit
     * @return
     */
    public String convertToPageSql(String sql, Integer offset, Integer limit) {
        //解析SQL
        Statement stmt;
        try {
            stmt = jSqlParser.parse(sql);
        } catch (Throwable e) {
            throw new PageException("不支持该SQL转换为分页查询!", e);
        }
        if (!(stmt instanceof Select)) {
            throw new PageException("分页语句必须是Select查询!");
        }
        //获取分页查询的select
        Select pageSelect = getPageSelect((Select) stmt);
        String pageSql = pageSelect.toString();
        //缓存移到外面了，所以不替换参数
        if (offset != null) {
            pageSql = pageSql.replace(START_ROW, String.valueOf(offset));
        }
        if (limit != null) {
            pageSql = pageSql.replace(PAGE_SIZE, String.valueOf(limit));
        }
        return pageSql;
    }

    /**
     * 获取一个外层包装的TOP查询
     *
     * @param select
     * @return
     */
    protected Select getPageSelect(Select select) {
        SelectBody selectBody = select.getSelectBody();
        if (selectBody instanceof SetOperationList) {
            selectBody = wrapSetOperationList((SetOperationList) selectBody);
        }
        //这里的selectBody一定是PlainSelect
        if (((PlainSelect) selectBody).getTop() != null) {
            throw new PageException("被分页的语句已经包含了Top，不能再通过分页插件进行分页查询!");
        }
        //获取查询列
        List<SelectItem> selectItems = getSelectItems((PlainSelect) selectBody);
        //对一层的SQL增加ROW_NUMBER()
        List<SelectItem> autoItems = new ArrayList<SelectItem>();
        SelectItem orderByColumn = addRowNumber((PlainSelect) selectBody, autoItems);
        //加入自动生成列
        ((PlainSelect) selectBody).addSelectItems(autoItems.toArray(new SelectItem[autoItems.size()]));
        //处理子语句中的order by
        processSelectBody(selectBody, 0);

        //中层子查询
        PlainSelect innerSelectBody = new PlainSelect();
        //PAGE_ROW_NUMBER
        innerSelectBody.addSelectItems(orderByColumn);
        innerSelectBody.addSelectItems(selectItems.toArray(new SelectItem[selectItems.size()]));
        //将原始查询作为内层子查询
        SubSelect fromInnerItem = new SubSelect();
        fromInnerItem.setSelectBody(selectBody);
        fromInnerItem.setAlias(PAGE_TABLE_ALIAS);
        innerSelectBody.setFromItem(fromInnerItem);

        //新建一个select
        Select newSelect = new Select();
        PlainSelect newSelectBody = new PlainSelect();
        //设置top
        Top top = new Top();
        top.setExpression(new LongValue(Long.MAX_VALUE));
        newSelectBody.setTop(top);
        //设置order by
        List<OrderByElement> orderByElements = new ArrayList<OrderByElement>();
        OrderByElement orderByElement = new OrderByElement();
        orderByElement.setExpression(PAGE_ROW_NUMBER_COLUMN);
        orderByElements.add(orderByElement);
        newSelectBody.setOrderByElements(orderByElements);
        //设置where
        GreaterThan greaterThan = new GreaterThan();
        greaterThan.setLeftExpression(PAGE_ROW_NUMBER_COLUMN);
        greaterThan.setRightExpression(new LongValue(Long.MIN_VALUE));
        newSelectBody.setWhere(greaterThan);
        //设置selectItems
        newSelectBody.setSelectItems(selectItems);
        //设置fromIterm
        SubSelect fromItem = new SubSelect();
        fromItem.setSelectBody(innerSelectBody); //中层子查询
        fromItem.setAlias(PAGE_TABLE_ALIAS);
        newSelectBody.setFromItem(fromItem);

        newSelect.setSelectBody(newSelectBody);
        if (isNotEmptyList(select.getWithItemsList())) {
            newSelect.setWithItemsList(select.getWithItemsList());
        }
        return newSelect;
    }

    /**
     * 包装SetOperationList
     *
     * @param setOperationList
     * @return
     */
    protected SelectBody wrapSetOperationList(SetOperationList setOperationList) {
        //获取最后一个plainSelect
        SelectBody setSelectBody = setOperationList.getSelects().get(setOperationList.getSelects().size() - 1);
        if (!(setSelectBody instanceof PlainSelect)) {
            throw new PageException("目前无法处理该SQL，您可以将该SQL发送给abel533@gmail.com协助作者解决!");
        }
        PlainSelect plainSelect = (PlainSelect) setSelectBody;
        PlainSelect selectBody = new PlainSelect();
        List<SelectItem> selectItems = getSelectItems(plainSelect);
        selectBody.setSelectItems(selectItems);

        //设置fromIterm
        SubSelect fromItem = new SubSelect();
        fromItem.setSelectBody(setOperationList);
        fromItem.setAlias(new Alias(WRAP_TABLE));
        selectBody.setFromItem(fromItem);
        //order by
        if (isNotEmptyList(plainSelect.getOrderByElements())) {
            selectBody.setOrderByElements(plainSelect.getOrderByElements());
            plainSelect.setOrderByElements(null);
        }
        return selectBody;
    }

    /**
     * 获取查询列
     *
     * @param plainSelect
     * @return
     */
    protected List<SelectItem> getSelectItems(PlainSelect plainSelect) {
        //设置selectItems
        List<SelectItem> selectItems = new ArrayList<SelectItem>();
        for (SelectItem selectItem : plainSelect.getSelectItems()) {
            //别名需要特殊处理
            if (selectItem instanceof SelectExpressionItem) {
                SelectExpressionItem selectExpressionItem = (SelectExpressionItem) selectItem;
                if (selectExpressionItem.getAlias() != null) {
                    //直接使用别名
                    Column column = new Column(selectExpressionItem.getAlias().getName());
                    SelectExpressionItem expressionItem = new SelectExpressionItem(column);
                    selectItems.add(expressionItem);
                } else if (selectExpressionItem.getExpression() instanceof Column) {
                    Column column = (Column) selectExpressionItem.getExpression();
                    SelectExpressionItem item = null;
                    if (column.getTable() != null) {
                        Column newColumn = new Column(column.getColumnName());
                        item = new SelectExpressionItem(newColumn);
                        selectItems.add(item);
                    } else {
                        selectItems.add(selectItem);
                    }
                } else {
                    selectItems.add(selectItem);
                }
            } else if (selectItem instanceof AllTableColumns) {
                selectItems.add(new AllColumns());
            } else {
                selectItems.add(selectItem);
            }
        }
        // SELECT *, 1 AS alias FROM TEST
        // 应该为
        // SELECT * FROM (SELECT *, 1 AS alias FROM TEST)
        // 不应该为
        // SELECT *, alias FROM (SELECT *, 1 AS alias FROM TEST)
        for (SelectItem selectItem : selectItems) {
            if (selectItem instanceof AllColumns) {
                return Collections.singletonList(selectItem);
            }
        }
        return selectItems;
    }

    /**
     * 获取 ROW_NUMBER() 列
     *
     * @param plainSelect 原查询
     * @param autoItems   自动生成的查询列
     * @return ROW_NUMBER() 列
     */
    protected SelectItem addRowNumber(PlainSelect plainSelect, List<SelectItem> autoItems) {
        //增加ROW_NUMBER()
        StringBuilder orderByBuilder = new StringBuilder();
        orderByBuilder.append("ROW_NUMBER() OVER (");
        if (isNotEmptyList(plainSelect.getOrderByElements())) {
            orderByBuilder.append(PlainSelect.orderByToString(
                    getOrderByElements(plainSelect, autoItems)).substring(1));
            //清空排序列表
            plainSelect.setOrderByElements(null);
        } else {
            orderByBuilder.append("ORDER BY RAND()");
        }
        orderByBuilder.append(") ");
        orderByBuilder.append(PAGE_ROW_NUMBER);
        return new SelectExpressionItem(new Column(orderByBuilder.toString()));
    }

    /**
     * 处理selectBody去除Order by
     *
     * @param selectBody
     */
    protected void processSelectBody(SelectBody selectBody, int level) {
        if (selectBody != null) {
            if (selectBody instanceof PlainSelect) {
                processPlainSelect((PlainSelect) selectBody, level + 1);
            } else if (selectBody instanceof WithItem) {
                WithItem withItem = (WithItem) selectBody;
                if (withItem.getSubSelect() != null) {
                    processSelectBody(withItem.getSubSelect().getSelectBody(), level + 1);
                }
            } else {
                SetOperationList operationList = (SetOperationList) selectBody;
                if (operationList.getSelects() != null && operationList.getSelects().size() > 0) {
                    List<SelectBody> plainSelects = operationList.getSelects();
                    for (SelectBody plainSelect : plainSelects) {
                        processSelectBody(plainSelect, level + 1);
                    }
                }
            }
        }
    }

    /**
     * 处理PlainSelect类型的selectBody
     *
     * @param plainSelect
     */
    protected void processPlainSelect(PlainSelect plainSelect, int level) {
        if (level > 1) {
            if (isNotEmptyList(plainSelect.getOrderByElements())) {
                if (plainSelect.getTop() == null) {
                    plainSelect.setTop(TOP100_PERCENT);
                }
            }
        }
        if (plainSelect.getFromItem() != null) {
            processFromItem(plainSelect.getFromItem(), level + 1);
        }
        if (plainSelect.getJoins() != null && plainSelect.getJoins().size() > 0) {
            List<Join> joins = plainSelect.getJoins();
            for (Join join : joins) {
                if (join.getRightItem() != null) {
                    processFromItem(join.getRightItem(), level + 1);
                }
            }
        }
    }

    /**
     * 处理子查询
     *
     * @param fromItem
     */
    protected void processFromItem(FromItem fromItem, int level) {
        if (fromItem instanceof SubJoin) {
            SubJoin subJoin = (SubJoin) fromItem;
            if (subJoin.getJoinList() != null && subJoin.getJoinList().size() > 0) {
                for (Join join : subJoin.getJoinList()) {
                    if (join.getRightItem() != null) {
                        processFromItem(join.getRightItem(), level + 1);
                    }
                }
            }
            if (subJoin.getLeft() != null) {
                processFromItem(subJoin.getLeft(), level + 1);
            }
        } else if (fromItem instanceof SubSelect) {
            SubSelect subSelect = (SubSelect) fromItem;
            if (subSelect.getSelectBody() != null) {
                processSelectBody(subSelect.getSelectBody(), level + 1);
            }
        } else if (fromItem instanceof ValuesList) {

        } else if (fromItem instanceof LateralSubSelect) {
            LateralSubSelect lateralSubSelect = (LateralSubSelect) fromItem;
            if (lateralSubSelect.getSubSelect() != null) {
                SubSelect subSelect = lateralSubSelect.getSubSelect();
                if (subSelect.getSelectBody() != null) {
                    processSelectBody(subSelect.getSelectBody(), level + 1);
                }
            }
        }
        //Table时不用处理
    }

    /**
     * List不空
     *
     * @param list
     * @return
     */
    public boolean isNotEmptyList(List<?> list) {
        if (list == null || list.size() == 0) {
            return false;
        }
        return true;
    }

    /**
     * 复制 OrderByElement
     *
     * @param orig  原 OrderByElement
     * @param alias 新 OrderByElement 的排序要素
     * @return 复制的新 OrderByElement
     */
    protected OrderByElement cloneOrderByElement(OrderByElement orig, String alias) {
        return cloneOrderByElement(orig, new Column(alias));
    }

    /**
     * 复制 OrderByElement
     *
     * @param orig       原 OrderByElement
     * @param expression 新 OrderByElement 的排序要素
     * @return 复制的新 OrderByElement
     */
    protected OrderByElement cloneOrderByElement(OrderByElement orig, Expression expression) {
        OrderByElement element = new OrderByElement();
        element.setAsc(orig.isAsc());
        element.setAscDescPresent(orig.isAscDescPresent());
        element.setNullOrdering(orig.getNullOrdering());
        element.setExpression(expression);
        return element;
    }

    /**
     * 获取新的排序列表
     *
     * @param plainSelect 原始查询
     * @param autoItems   生成的新查询要素
     * @return 新的排序列表
     */
    protected List<OrderByElement> getOrderByElements(PlainSelect plainSelect,
                                                      List<SelectItem> autoItems) {
        List<OrderByElement> orderByElements = plainSelect.getOrderByElements();
        ListIterator<OrderByElement> iterator = orderByElements.listIterator();
        OrderByElement orderByElement;

        // 非 `*` 且 非 `t.*` 查询列集合
        Map<String, SelectExpressionItem> selectMap = new HashMap<String, SelectExpressionItem>();
        // 别名集合
        Set<String> aliases = new HashSet<String>();
        // 是否包含 `*` 查询列
        boolean allColumns = false;
        // `t.*` 查询列的表名集合
        Set<String> allColumnsTables = new HashSet<String>();

        for (SelectItem item : plainSelect.getSelectItems()) {
            if (item instanceof SelectExpressionItem) {
                SelectExpressionItem expItem = (SelectExpressionItem) item;
                selectMap.put(expItem.getExpression().toString(), expItem);

                Alias alias = expItem.getAlias();
                if (alias != null) {
                    aliases.add(alias.getName());
                }

            } else if (item instanceof AllColumns) {
                allColumns = true;

            } else if (item instanceof AllTableColumns) {
                allColumnsTables.add(((AllTableColumns) item).getTable().getName());
            }
        }

        // 开始遍历 OrderByElement 列表
        int aliasNo = 1;
        while (iterator.hasNext()) {
            orderByElement = iterator.next();
            Expression expression = orderByElement.getExpression();
            SelectExpressionItem selectExpressionItem = selectMap.get(expression.toString());
            if (selectExpressionItem != null) { // OrderByElement 在查询列表中
                Alias alias = selectExpressionItem.getAlias();
                if (alias != null) { // 查询列含有别名时用查询列别名
                    iterator.set(cloneOrderByElement(orderByElement, alias.getName()));

                } else { // 查询列不包含别名
                    if (expression instanceof Column) {
                        // 查询列为普通列，这时因为列在嵌套查询外时名称中不包含表名，故去除排序列的表名引用
                        // 例（仅为解释此处逻辑，不代表最终分页结果）：
                        // SELECT TEST.A FROM TEST ORDER BY TEST.A
                        // -->
                        // SELECT A FROM (SELECT TEST.A FROM TEST) ORDER BY A
                        ((Column) expression).setTable(null);

                    } else {
                        // 查询列不为普通列时（例如函数列）不支持分页
                        // 此种情况比较难预测，简单的增加新列容易产生不可预料的结果
                        // 而为列增加别名是非常简单的，故此要求排序复杂列必须使用别名
                        throw new PageException("列 \"" + expression + "\" 需要定义别名");
                    }
                }

            } else { // OrderByElement 不在查询列表中，需要自动生成一个查询列
                if (expression instanceof Column) { // OrderByElement 为普通列
                    Table table = ((Column) expression).getTable();
                    if (table == null) { // 表名为空
                        if (allColumns ||
                                (allColumnsTables.size() == 1 && plainSelect.getJoins() == null) ||
                                aliases.contains(((Column) expression).getColumnName())) {
                            // 包含`*`查询列 或者 只有一个 `t.*`列且为单表查询 或者 其实排序列是一个别名
                            // 此时排序列其实已经包含在查询列表中了，不需做任何操作
                            continue;
                        }

                    } else { //表名不为空
                        String tableName = table.getName();
                        if (allColumns || allColumnsTables.contains(tableName)) {
                            // 包含`*`查询列 或者 包含特定的`t.*`列
                            // 此时排序列其实已经包含在查询列表中了，只需去除排序列的表名引
                            ((Column) expression).setTable(null);
                            continue;
                        }
                    }
                }

                // 将排序列加入查询列中
                String aliasName = PAGE_COLUMN_ALIAS_PREFIX + aliasNo++;

                SelectExpressionItem item = new SelectExpressionItem();
                item.setExpression(expression);
                item.setAlias(new Alias(aliasName));
                autoItems.add(item);

                iterator.set(cloneOrderByElement(orderByElement, aliasName));
            }
        }

        return orderByElements;
    }
}
