package com.product.data.center.entity;

import com.product.common.lang.StringUtils;
import com.product.core.entity.DataTableEntity;
import com.product.core.exception.BaseException;
import com.product.data.center.config.ErrorCode;
import com.product.data.center.utils.BatchUtils;
import com.product.data.center.utils.CallBackReturnValue;
import com.product.datasource.config.DataBaseType;

import java.util.Arrays;
import java.util.HashSet;
import java.util.Set;

/**
 * 查询实体类
 *
 * @author cheng
 * @date 2022年07月14日10:26:22
 */
public class QueryDataConfigEntity {
    /**
     * 查询表名
     */
    private String queryTable;
    /**
     * 需要查询的字段集合
     */
    private Set<String> queryFieldSet;
    /**
     * 自动递增主键字段
     */
    private String autoIncrementPrimaryField;
    /**
     * 查询条件
     * 支持占位符
     */
    private String queryFilter;
    /**
     * 占位符参数
     */
    private Object[] params;
    /**
     * 数据库类型
     */
    private DataBaseType dbt;

    /**
     * @param queryTable
     * @param queryFilter
     * @param params
     */
    public QueryDataConfigEntity(String queryTable, String queryFilter, String autoIncrementPrimaryField, Object[] params) {
        this.queryTable = queryTable;
        this.queryFilter = queryFilter;
        this.params = params;
        this.autoIncrementPrimaryField = autoIncrementPrimaryField;
    }

    /**
     * @param queryTable
     * @param queryFilter
     */
    public QueryDataConfigEntity(String queryTable, String queryFilter, String autoIncrementPrimaryField) {
        this.queryTable = queryTable;
        this.queryFilter = queryFilter;
        this.autoIncrementPrimaryField = autoIncrementPrimaryField;
    }

    /**
     * @param queryTable
     * @param queryFieldSet
     * @param queryFilter
     * @param params
     */
    public QueryDataConfigEntity(String queryTable, Set<String> queryFieldSet, String autoIncrementPrimaryField, String queryFilter, Object[] params) {
        this.queryTable = queryTable;
        this.queryFieldSet = queryFieldSet;
        this.queryFilter = queryFilter;
        this.params = params;
        this.autoIncrementPrimaryField = autoIncrementPrimaryField;
    }

    /**
     * 添加查询字段
     *
     * @param fieldName
     */
    public void addQueryField(String... fieldName) {
        if (fieldName != null) {
            if (this.queryFieldSet == null) {
                this.queryFieldSet = new HashSet<>();
            }
            this.queryFieldSet.addAll(Arrays.asList(fieldName));
        }
    }

    /**
     * 删除查询字段
     *
     * @param fieldName
     */
    public void removeQueryField(String... fieldName) {
        this.querySqlSuccess = false;
        if (fieldName != null && this.queryFieldSet != null && this.queryFieldSet.size() > 0) {
            for (String field : fieldName) {
                this.queryFieldSet.remove(field);
            }
        }
    }


    public String getQueryTable() {
        return queryTable;
    }

    public void setQueryTable(String queryTable) {
        this.querySqlSuccess = false;
        this.queryTable = queryTable;
    }

    public Set<String> getQueryFieldSet() {
        return queryFieldSet;
    }

    public void setQueryFieldSet(Set<String> queryFieldSet) {
        this.querySqlSuccess = false;
        this.queryFieldSet = queryFieldSet;
    }

    public String getQueryFilter() {
        return queryFilter;
    }

    public void setQueryFilter(String queryFilter) {
        this.querySqlSuccess = false;
        this.queryFilter = queryFilter;
    }

    public Object[] getParams() {
        return params;
    }

    public void setParams(Object[] params) {
        this.params = params;
    }

    public DataBaseType getDbt() {
        return dbt;
    }

    public void setDbt(DataBaseType dbt) {
        this.querySqlSuccess = false;
        this.dbt = dbt;
    }

    public String getAutoIncrementPrimaryField() {
        return autoIncrementPrimaryField;
    }

    public void setAutoIncrementPrimaryField(String autoIncrementPrimaryField) {
        this.autoIncrementPrimaryField = autoIncrementPrimaryField;
    }

    /**
     * 查询sql状态 是否初始化模板完成
     */
    private boolean querySqlSuccess = false;

    private String sqlTemplate;

    /**
     * 获取查询sql
     *
     * @param currentPage 当前页数
     * @param pageSize    每页条数
     * @return
     */
    public String getQuerySql(int currentPage, int pageSize) throws BaseException {
        if (!this.querySqlSuccess) {
            this.initQuerySqlTemplate();
        }
        int first = -1;
        int second = -1;
        if (DataBaseType.ORACLE.equals(this.dbt)) {
            first = currentPage * pageSize;
            second = (currentPage - 1) * pageSize;
        } else if (DataBaseType.MYSQL.equals(this.dbt)) {
            if (currentPage == 1) {
                first = 0;
            } else {
                first = (currentPage - 1) * pageSize;
            }
            second = pageSize;
        } else if (DataBaseType.SQLSERVER.equals(this.dbt)) {
            first = pageSize;
            second = (currentPage - 1) * pageSize;
        } else if (DataBaseType.INFORMIX.equals(this.dbt)) {
            first = (currentPage - 1) * pageSize;
            second = pageSize;
        } else if (DataBaseType.PSQL.equals(this.dbt)) {
            first = pageSize;
            second = pageSize * (currentPage - 1);
        } else {
            throw new BaseException(ErrorCode.NONSUPPORT_DATABASE_TYPE);
        }

        return sqlTemplate.replace("<<~1~>>", first + "").replace("<<~2~>>", second + "");
    }


    public String getQueryTotalCountSql() {
        StringBuilder sql = new StringBuilder("SELECT ");
        sql.append("count(").append(this.autoIncrementPrimaryField).append(") as total_count FROM ");
        sql.append(this.queryTable);
        if (!StringUtils.isEmpty(this.queryFilter)) {
            sql.append(" WHERE ( ").append(this.queryFilter).append(" ) ");
        }
        return sql.toString();
    }

    /**
     * 初始化查询sql
     */
    private void initQuerySqlTemplate() {
        if (this.queryFieldSet == null || this.queryFieldSet.size() <= 0) {
            throw new BaseException(ErrorCode.BATCH_EXECUTE_QUERY_FIELD_CAN_NOT_EMPTY);
        }
        StringBuilder queryTemplate = new StringBuilder("SELECT ");
        if (DataBaseType.INFORMIX.equals(this.dbt)) {
            queryTemplate.append(" SKIP <<~1~>> FIRST <<~2~>> ");
        } else if (DataBaseType.SQLSERVER.equals(this.dbt)) {
//            queryTemplate.append(" TOP <<~1~>> ");
            // TODO: 2022/7/25 暂未实现sqlserver查询
        }

        String[] fields = this.queryFieldSet.stream().toArray(String[]::new);
        for (int i = 0; i < fields.length; i++) {
            if (i > 0) {
                queryTemplate.append(" , ");
            }
            queryTemplate.append(BatchUtils.getFieldName(fields[i], this.dbt));
        }
        if (!this.queryFieldSet.contains(autoIncrementPrimaryField)) {
            queryTemplate.append(",").append(BatchUtils.getFieldName(this.autoIncrementPrimaryField, this.dbt));
        }
        //ORACLE 特殊查询出序号用于分页
        if (DataBaseType.ORACLE.equals(this.dbt)) {
            queryTemplate.append("");
        }
        queryTemplate.append(" FROM ").append(this.queryTable);
        //是否拼接了WHERE 关键字
        boolean isWhere = false;
        if (!StringUtils.isEmpty(this.queryFilter)) {
            isWhere = true;
            queryTemplate.append(" WHERE ").append("(").append(this.queryFilter).append(") ");
        }
        if (DataBaseType.ORACLE.equals(this.dbt)) {

            queryTemplate.append(" order by ").append(BatchUtils.getFieldName(this.autoIncrementPrimaryField, this.dbt)).append(" asc ");
            queryTemplate.insert(0, "SELECT aa.*,ROWNUM as row_num  FROM ( ");

            queryTemplate.append(" ) aa ");

            queryTemplate.append(" WHERE  ( ").append(" ROWNUM<=<<~1~>> ) ");

            queryTemplate.insert(0, " SELECT * FROM (");
            queryTemplate.append(" ) A where (row_num> <<~2~>> )");
        } else {

            queryTemplate.append(" order by ").append(BatchUtils.getFieldName(this.autoIncrementPrimaryField, this.dbt)).append(" asc ");
        }
        if (this.dbt.equals(DataBaseType.MYSQL)) {
            queryTemplate.append(" limit <<~1~>>,<<~2~>>");
        }
        this.sqlTemplate = queryTemplate.toString();
        this.querySqlSuccess = true;
    }

    public static void main(String[] args) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT *,ROWNNUM from TABLE where 1=1");
        sql.append(" order by xxx");
        sql.insert(0, " SELECT *,ROWNUM as row_num  FROM (");
        sql.append(" ) A ");
//        sql.append(") a ORDER BY XXX");
        sql.insert(0, " SELECT * FROM (");
        sql.append(") A ");
        System.out.println(sql);
    }
}