package com.walker.jdbc.dao;

import com.walker.common.SplitPageInfo;
import com.walker.db.DatabaseType;
import com.walker.db.Sorts;
import com.walker.db.page.GenericPager;
import com.walker.infrastructure.utils.StringUtils;
import com.walker.jdbc.Constants;
import com.walker.jdbc.SqlAndParameters;
import org.apache.commons.lang3.ArrayUtils;

import java.util.HashMap;
import java.util.Map;

/**
 * 分页工具类
 *
 * @author 时克英
 */
public class PaginationHelper {
    /**
     * 数据库类型
     */
    private String type;

    /**
     * 获取数据库类型
     *
     * @return 数据库类型
     * @author 时克英
     */
    public String getType() {
        return type;
    }

    /**
     * 设置数据库类型
     *
     * @param type 数据库类型
     * @author 时克英
     */
    public void setType(String type) {
        this.type = type;
    }

    public String getOrderBySql(String sql, Sorts.Sort sort){
        if(sort == null){
            return sql;
        }
        return new StringBuilder(sql).append(Constants.SQL_ORDER_BY)
                .append(sort.getField()).append(StringUtils.CHAR_SPACE).append(sort.getValue()).toString();
    }

    public String getSqlPagingQuery(String sql, Sorts.Sort sort){
//        if(sort != null){
//            sql = new StringBuilder(sql).append(Constants.SQL_ORDER_BY)
//                    .append(sort.getField()).append(StringUtils.CHAR_SPACE).append(sort.getValue()).toString();
//        }
        sql = this.getOrderBySql(sql, sort);
        if (DatabaseType.NAME_ORACLE.equalsIgnoreCase(type) || DatabaseType.NAME_DAMENG.equalsIgnoreCase(type)) {
            return  "select * from (select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= ?) where rownum_ >= ?";
        } else if(DatabaseType.NAME_MYSQL.equalsIgnoreCase(type)
                || DatabaseType.NAME_SQLITE.equalsIgnoreCase(type)){
            return sql + " limit ?, ?";
        } else if(DatabaseType.NAME_POSTGRES.equalsIgnoreCase(type)){
            return sql + " limit ? offset ?";
        } else {
            return sql + " limit ?, ?";
        }
    }

    /**
     * 返回带分页信息的SQL信息对象。
     * <pre>
     *     1）使用GenericPager重写分页查询。
     *     2）后续都是使用该方法，废弃:SplitPageInfo
     * </pre>
     * @param sql 原始SQL语句
     * @param parameters 参数集合
     * @param pager 分页对象，由调用方传递
     * @return
     * @auth 时克英
     * @date 2022-09-18
     * @date 2023-07-24 oracle分页索引值+1
     */
    public SqlAndParameters<Map<String, Object>> getSqlPagingQuery(String sql
            , Map<String, Object> parameters, GenericPager<?> pager, Sorts.Sort sort){
        sql = this.getOrderBySql(sql, sort);
        Map<String, Object> temp = new HashMap<>(4);
        temp.putAll(parameters);
        String pageSql = null;
        if (DatabaseType.NAME_ORACLE.equalsIgnoreCase(type) || DatabaseType.NAME_DAMENG.equalsIgnoreCase(type)) {
            if(pager.getPageIndex() == 1){
                pageSql = "select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= :endRow_";
                temp.put("endRow_", pager.getEndRowIndexPage()+1);
            } else {
                pageSql = "select * from (select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= :endRow_) where rownum_ >= :startRow_";
                temp.put("startRow_", pager.getFirstRowIndexInPage()+1);
                temp.put("endRow_", pager.getEndRowIndexPage()+1);
            }
        } else if (DatabaseType.NAME_MYSQL.equalsIgnoreCase(type)
                || DatabaseType.NAME_SQLITE.equalsIgnoreCase(type)){
            if(pager.getPageIndex() == 1){
                pageSql = sql + " limit :pageSize_";
                temp.put("pageSize_", pager.getPageSize());
            } else {
                pageSql = sql + " limit :startRow_, :pageSize_";
                temp.put("startRow_", pager.getFirstRowIndexInPage());
                temp.put("pageSize_", pager.getPageSize());
            }
        } else if(DatabaseType.NAME_POSTGRES.equalsIgnoreCase(type)){
            // 2023-10-08，TBase数据库（基于PG）
            if(pager.getPageIndex() == 1){
                pageSql = sql + " limit :pageSize_";
                temp.put("pageSize_", pager.getPageSize());
            } else {
                pageSql = sql + " limit :pageSize_ offset :startRow_";
                temp.put("pageSize_", pager.getPageSize());
                temp.put("startRow_", pager.getFirstRowIndexInPage());
            }
        } else {
            System.out.println("type = " + type);
            if(pager.getPageIndex() == 1){
                pageSql = sql + " limit :pageSize_";
                temp.put("pageSize_", pager.getPageSize());
            } else {
                pageSql = sql + " limit :startRow_, :pageSize_";
                temp.put("startRow_", pager.getFirstRowIndexInPage());
                temp.put("pageSize_", pager.getPageSize());
            }
        }
        return new SqlAndParameters<>(pageSql, temp);
    }

    /**
     * 获取分页查询参数对象。
     * @param sql
     * @param parameters
     * @param pager
     * @return
     * @date 2023-07-24 oracle分页索引值+1
     */
    public SqlAndParameters<Object[]> getSqlPagingQuery(String sql, Object[] parameters, GenericPager<?> pager, Sorts.Sort sort) {
        sql = this.getOrderBySql(sql, sort);
        Object[] tempArray = null;
        String pageSql = null;
        if (DatabaseType.NAME_ORACLE.equalsIgnoreCase(type) || DatabaseType.NAME_DAMENG.equalsIgnoreCase(type)) {
            if(pager.getPageIndex() == 1){
                pageSql = "select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= ?";
                tempArray = ArrayUtils.add(parameters, pager.getEndRowIndexPage()+1);
            } else {
                pageSql = "select * from (select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= ?) where rownum_ >= ?";
                tempArray = ArrayUtils.add(parameters, pager.getEndRowIndexPage()+1);
                tempArray = ArrayUtils.add(tempArray, pager.getFirstRowIndexInPage()+1);
            }
        } else if (DatabaseType.NAME_MYSQL.equalsIgnoreCase(type)
                || DatabaseType.NAME_SQLITE.equalsIgnoreCase(type)){
            if(pager.getPageIndex() == 1){
                pageSql = sql + " limit ?";
//                tempArray = ArrayUtils.add(parameters, pager.getEndRowIndexPage());
                tempArray = ArrayUtils.add(parameters, pager.getPageSize());
            } else {
                pageSql = sql + " limit ?, ?";
//                tempArray = ArrayUtils.add(parameters, pager.getFirstRowIndexInPage() - 1);
//                tempArray = ArrayUtils.add(tempArray, pager.getEndRowIndexPage());
                tempArray = ArrayUtils.add(parameters, pager.getFirstRowIndexInPage());
                tempArray = ArrayUtils.add(tempArray, pager.getPageSize());
            }
        } else if(DatabaseType.NAME_POSTGRES.equalsIgnoreCase(type)){
            // 2023-10-08，TBase数据库（基于PG）
            if(pager.getPageIndex() == 1){
                pageSql = sql + " limit ?";
                tempArray = ArrayUtils.add(parameters, pager.getPageSize());
            } else {
                pageSql = sql + " limit ? offset ?";
                tempArray = ArrayUtils.add(parameters, pager.getPageSize());
                tempArray = ArrayUtils.add(tempArray, pager.getFirstRowIndexInPage());
            }
        } else {
            System.out.println("getSqlPagingQuery,不支持的分页类型:" + type);
            if(pager.getPageIndex() == 1){
                pageSql = sql + " limit ?";
                tempArray = ArrayUtils.add(parameters, pager.getPageSize());
            } else {
                pageSql = sql + " limit ?,?";
                tempArray = ArrayUtils.add(parameters, pager.getFirstRowIndexInPage());
                tempArray = ArrayUtils.add(tempArray, pager.getPageSize());
            }
//            throw new PersistenceException("不支持的分页类型:" + type);
        }
        return new SqlAndParameters<>(pageSql, tempArray);
    }

    /**
     * 组装分页查询语句及查询参数
     *
     * @param sql           查询语句
     * @param parameters    查询参数
     * @param splitPageInfo 分页对象
     * @return 查询语句及查询参数
     */
    @Deprecated
    public SqlAndParameters<Map<String, Object>> getPaginationSql(String sql, Map<String, Object> parameters, SplitPageInfo splitPageInfo) {
        Map<String, Object> temp = new HashMap<>();
        temp.putAll(parameters);
        if (DatabaseType.NAME_ORACLE.equalsIgnoreCase(type) || DatabaseType.NAME_DAMENG.equalsIgnoreCase(type)) {
            if (splitPageInfo.getCurrentPage() != 1) {
//                String pSql =
//                        "select *\n" +
//                                "  from (select row_.*, rownum rownum_\n" +
//                                "          from (" + sql + ") row_\n" +
//                                "         where rownum <= :endRow_)\n" +
//                                " where rownum_ >= :startRow_";
                String pSql = "select * from (select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= :endRow_) where rownum_ >= :startRow_";
                temp.put("startRow_", splitPageInfo.getPageRowBegin());
                temp.put("endRow_", splitPageInfo.getPageRowEnd());

                return new SqlAndParameters<>(pSql, temp);
            } else {
//                String pSql =
//                        "select row_.*, rownum rownum_\n" +
//                                "  from (" + sql + ") row_\n" +
//                                " where rownum <= :endRow_";
                String pSql = "select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= :endRow_";
                temp.put("endRow_", splitPageInfo.getPageRowEnd());

                return new SqlAndParameters<>(pSql, temp);
            }
        } else if (DatabaseType.NAME_MYSQL.equalsIgnoreCase(type)
                || DatabaseType.NAME_SQLITE.equalsIgnoreCase(type)) {
            if (splitPageInfo.getCurrentPage() != 1 && splitPageInfo.getCurrentPage() != 0) {
                String pSql = sql + " limit :startRow_, :pageSize_";
                temp.put("startRow_", splitPageInfo.getPageRowBegin() - 1);
                temp.put("pageSize_", splitPageInfo.getPageSize());
                return new SqlAndParameters<>(pSql, temp);
            } else {
                String pSql = sql + " limit :pageSize_";
                temp.put("pageSize_", splitPageInfo.getPageSize());
                return new SqlAndParameters<>(pSql, temp);
            }
        } else {
//            throw new PersistenceException("不支持的分页类型！");
            System.out.println("type = " + type);
            if (splitPageInfo.getCurrentPage() != 1 && splitPageInfo.getCurrentPage() != 0) {
                String pSql = sql + " limit :startRow_, :pageSize_";
                temp.put("startRow_", splitPageInfo.getPageRowBegin() - 1);
                temp.put("pageSize_", splitPageInfo.getPageSize());
                return new SqlAndParameters<>(pSql, temp);
            } else {
                String pSql = sql + " limit :pageSize_";
                temp.put("pageSize_", splitPageInfo.getPageSize());
                return new SqlAndParameters<>(pSql, temp);
            }
        }

    }

    /**
     * 组装分页查询语句及查询参数
     *
     * @param sql           查询语句
     * @param parameters    查询参数
     * @param splitPageInfo 分页对象
     * @return 分页查询语句及查询参数
     * @author 李志慧
     */
    @Deprecated
    public SqlAndParameters<Object[]> getPaginationSql(String sql, Object[] parameters, SplitPageInfo splitPageInfo) {
        if (DatabaseType.NAME_ORACLE.equalsIgnoreCase(type) || DatabaseType.NAME_DAMENG.equalsIgnoreCase(type)) {
            if (splitPageInfo.getCurrentPage() != 1) {
//                String pSql =
//                        "select *\n" +
//                                "  from (select row_.*, rownum rownum_\n" +
//                                "          from (" + sql + ") row_\n" +
//                                "         where rownum <= ?)\n" +
//                                " where rownum_ >= ?";
                String pSql = "select * from (select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= ?) where rownum_ >= ?";
                Object[] tempArray = ArrayUtils.add(parameters, splitPageInfo.getPageRowEnd());
                tempArray = ArrayUtils.add(tempArray, splitPageInfo.getPageRowBegin());

                return new SqlAndParameters<>(pSql, tempArray);
            } else {
//                String pSql =
//                        "select row_.*, rownum rownum_\n" +
//                                "  from (" + sql + ") row_\n" +
//                                " where rownum <= ?";
                String pSql = "select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= ?";
                Object[] tempArray = ArrayUtils.add(parameters, splitPageInfo.getPageRowEnd());

                return new SqlAndParameters<>(pSql, tempArray);
            }
        } else if (DatabaseType.NAME_MYSQL.equalsIgnoreCase(type)) {
            if (splitPageInfo.getCurrentPage() != 1 && splitPageInfo.getCurrentPage() != 0) {
                String pSql = sql + " limit ?, ?";

                Object[] tempArray = ArrayUtils.add(parameters, splitPageInfo.getPageRowBegin() - 1);
                tempArray = ArrayUtils.add(tempArray, splitPageInfo.getPageRowEnd());

                return new SqlAndParameters<>(pSql, tempArray);
            } else {
                String pSql = sql + " limit ?";
                Object[] tempArray = ArrayUtils.add(parameters, splitPageInfo.getPageRowEnd());
                return new SqlAndParameters<>(pSql, tempArray);
            }
        } else {
            throw new PersistenceException("不支持的分页类型！");
        }
    }
}