package com.product.server.report.service;

import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.product.core.service.support.QueryFilterService;
import com.product.server.report.util.QuerySqlParseUtil;
import com.product.util.BaseUtil;
import com.product.util.CallBack;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.product.common.lang.StringUtils;
import com.product.core.cache.DataPoolCacheImpl;
import com.product.core.connection.ConnectionManager;
import com.product.core.dao.BaseDao;
import com.product.core.entity.DataTableEntity;
import com.product.core.entity.FieldMetaEntity;
import com.product.core.entity.FieldSetEntity;
import com.product.core.exception.BaseException;
import com.product.core.service.support.AbstractBaseService;
import com.product.core.spring.context.SpringMVCContextHolder;
import com.product.core.transfer.Transactional;
import com.product.server.report.config.CmnConst;
import com.product.server.report.service.idel.IReportDatasourceService;
import com.product.util.SystemParamReplace;

@Component
public class ReportDatasourceService extends AbstractBaseService implements IReportDatasourceService {

    @Autowired
    BaseDao baseDao;

    @Autowired
    QueryFilterService queryFilterService;
    /**
     * 报表数据源列表
     *
     * @param fse 分页加高级索引
     * @return
     */
    public DataTableEntity listDatasource(FieldSetEntity fse) {
        Integer cpage = fse.getInteger("cpage");
        Integer pageSize = fse.getInteger("pagesize");
        DataTableEntity dataTableEntity = null;
        String queryFilter = "";
        if (!BaseUtil.dataTableIsEmpty(fse.getSubDataTable("systemSeniorQueryString"))) {
            queryFilter =  queryFilterService.getQueryFilter(fse);
        }
        if (!StringUtils.isEmpty(cpage)) {
            dataTableEntity = baseDao.listTable(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG, queryFilter, null, null, null, pageSize, cpage);
        } else {
            dataTableEntity = baseDao.listTable(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG);
        }
        baseDao.loadPromptData(dataTableEntity);
        return dataTableEntity;
    }

    /**
     * 报表数据源详情
     *
     * @param uuid
     * @return
     */
    public FieldSetEntity findDatasource(String uuid) {
        return baseDao.getFieldSetEntity(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG, uuid, true);
    }

    /**
     * 数据源-保存
     * @param fse
     * @return
     */
    @Transactional
    @Override
    public String saveDatasource(FieldSetEntity fse) {
        String uuid;
        if (StringUtils.isEmpty(fse.getUUID())) {
            fse.setValue(CmnConst.CREATED_BY, SpringMVCContextHolder.getCurrentUserId());
            fse.setValue(CmnConst.CREATED_UTC_DATETIME, new Date());
            uuid = baseDao.add(fse);
        } else {
            fse.setValue(CmnConst.UPDATED_BY, SpringMVCContextHolder.getCurrentUserId());
            fse.setValue(CmnConst.UPDATED_UTC_DATETIME, new Date());
            DataTableEntity subDte = fse.getSubDataTable(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG_FIELD);
            if (!BaseUtil.dataTableIsEmpty(subDte)) {
                List<String> list = Lists.newArrayList();
                FieldSetEntity tempFse;
                for (int i = 0; i < subDte.getRows(); i++) {
                    tempFse = subDte.getFieldSetEntity(i);
                    uuid = tempFse.getUUID();
                    if (!StringUtils.isEmpty(uuid)) {
                        list.add(uuid);
                    }
                }
                String filter = BaseUtil.buildQuestionMarkFilter(CmnConst.UUID, list.size(), false);
                if (!StringUtils.isEmpty(filter)) {
                    filter += " and ";
                }
                filter += "datasource_uuid=?";
                list.add(fse.getUUID());
                baseDao.delete(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG_FIELD, filter, list.toArray());
            }
            baseDao.update(fse);
            uuid = fse.getUUID();
        }
        return uuid;
    }

    /**
     * 报表数据源-复制
     *
     * @param
     * @return
     */
    public FieldSetEntity copyDatasource(FieldSetEntity fse) {
        FieldSetEntity baseFse = baseDao.getFieldSet(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG, fse.getUUID(), true);
        DataTableEntity baseSubDte = baseFse.getSubDataTable(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG_FIELD);
        FieldSetEntity tempFse;
        for (int i = 0;i < baseSubDte.getRows();i++) {
            tempFse = baseSubDte.getFieldSetEntity(i);
            tempFse.remove(new String[]{CmnConst.UUID, CmnConst.ID});
        }
        baseFse.remove(new String[]{CmnConst.UUID, CmnConst.ID, CmnConst.UPDATED_UTC_DATETIME, CmnConst.UPDATED_BY});
        baseFse.setValue(CmnConst.REPORT_DATASOURCE_NAME, baseFse.getString(CmnConst.REPORT_DATASOURCE_NAME) + "_" + System.currentTimeMillis());
        return baseFse;
    }

    /**
     * 报表数据源删除
     *
     * @param uuid
     * @return
     */
    @Transactional
    public boolean deleteDatasource(String uuid) {
        return baseDao.delete(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG, "uuid=?", new Object[]{uuid});
    }

    /**
     * sql解析与验证
     *
     * @param fse
     * @return
     */
    public DataTableEntity sqlVerify(FieldSetEntity fse) throws SQLException {
        String sqlText = fse.getString(CmnConst.SQL_TEXT);
//        String rep = "(\\{\\$.+\\$\\})|(\\{\\^.+\\^\\})|(\\{\\&.+\\&\\})";

//		sqlText = sqlText.replaceAll("\\[\\[.+\\]\\]", "").replaceAll("(\\{\\$.+\\$\\})|(\\{\\^.+\\^\\})|(\\{\\&.+\\&\\})", "null");

//        sqlText = sqlText.replace("\n", "  ");//替换为空格
//        sqlText = sqlText.replaceAll("\\{%((?!(\\{%)).+)%\\}", "");
        sqlText = SystemParamReplace.systemParamsReplace(sqlText);
        sqlText+=" limit 1 ";
        DataTableEntity dataTableEntity = getColumnNames(sqlText);
        String relatedTable = dataTableEntity.getFieldSetEntity(0).getString(CmnConst.TABLE_NAME);
        Map<String, String> map = Maps.newHashMap();
        if (!BaseUtil.strIsNull(relatedTable)) {
            FieldSetEntity tableInfo = baseDao.getFieldSetEntityByFilter(CmnConst.PRODUCT_SYS_DATAMODEL_TABLE, "table_name = ?", new Object[]{relatedTable}, true);
            if (tableInfo != null) {
                DataTableEntity fieldData = tableInfo.getSubDataTable(CmnConst.PRODUCT_SYS_DATAMODEL_FIELD);
                for (int i = 0, length = fieldData.getRows(); i < length; i++) {
                    FieldSetEntity subField = fieldData.getFieldSetEntity(i);
                    String fieldName = subField.getString("field_name");
                    String fieldDescription = subField.getString("field_description");
                    map.put(fieldName, fieldDescription);
                }
            }
        }
        Map<String, List<String>> fieldTypeMap = Maps.newHashMap();
        fieldTypeMap.put(CmnConst.ATTR_DATA_TYPE_NUM, Arrays.asList(CmnConst.FIELD_TYPE_NUM_STR.split(",")));
        fieldTypeMap.put(CmnConst.ATTR_DATA_TYPE_DATE, Arrays.asList(CmnConst.FIELD_TYPE_DATE_STR.split(",")));
        String fieldType;
        for (int i = 0; i < dataTableEntity.getRows(); i++) {
            FieldSetEntity fieldSetEntity = dataTableEntity.getFieldSetEntity(i);
            fieldSetEntity.setTableName(CmnConst.PRODUCT_SYS_DATAMODEL_FIELD);
            fieldSetEntity.setValue(CmnConst.FIELD_TEXT, map.get(fieldSetEntity.getString(CmnConst.FIELD_NAME)));
            fieldType = fieldSetEntity.getString(CmnConst.FIELD_FORMAT);
            mid:
            for (Map.Entry<String, List<String>> entry : fieldTypeMap.entrySet()) {
                for (String singleFieldType : entry.getValue()) {
                    if (singleFieldType.equalsIgnoreCase(fieldType)) {
                        fieldSetEntity.setValue(CmnConst.FIELD_FORMAT, entry.getKey());
                        break mid;
                    }
                }
            }
            fieldType = fieldSetEntity.getString(CmnConst.FIELD_FORMAT);
            if (!CmnConst.ATTR_DATA_TYPE_NUM.equalsIgnoreCase(fieldType) && !CmnConst.ATTR_DATA_TYPE_DATE.equalsIgnoreCase(fieldType)) {
                fieldSetEntity.setValue(CmnConst.FIELD_FORMAT, CmnConst.ATTR_DATA_TYPE_STRING);
            }
        }
        //data存放表名
        dataTableEntity.setMeta(dataTableEntity.getFieldSetEntity(0).getMeta());

        // 获取原有信息 2021年12月24日 10:51:38 6c
        DataTableEntity subDte = baseDao.listTable(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG_FIELD, "datasource_uuid=?", new Object[]{fse.getUUID()});
        FieldSetEntity subFse;
        FieldSetEntity tempFse;
        for (int j = 0; j < dataTableEntity.getRows(); j++) {
            tempFse = dataTableEntity.getFieldSetEntity(j);
            for (int i = 0; i < subDte.getRows(); i++) {
                subFse = subDte.getFieldSetEntity(i);
                if (subFse.getString(CmnConst.FIELD_NAME).equals(tempFse.getString(CmnConst.FIELD_NAME))) {
                    tempFse.setValue(CmnConst.FIELD_TEXT, subFse.getString(CmnConst.FIELD_TEXT));
                    tempFse.setValue(CmnConst.FIELD_PROMPT, subFse.getString(CmnConst.FIELD_PROMPT));
                    tempFse.setValue(CmnConst.TABLE_NAME, subFse.getString(CmnConst.TABLE_NAME));
                    tempFse.setValue(CmnConst.FIELD_FORMAT, subFse.getString(CmnConst.FIELD_FORMAT));
                    tempFse.setValue(CmnConst.SEARCH_TYPE, subFse.getInteger(CmnConst.SEARCH_TYPE));
                    tempFse.setValue(CmnConst.LOGICAL_OPERATOR, subFse.getString(CmnConst.LOGICAL_OPERATOR));
                    tempFse.setValue(CmnConst.LOGICAL_VALUE, subFse.getString(CmnConst.LOGICAL_VALUE));
                    break;
                }
            }
        }

        return dataTableEntity;
    }


    public static Set<String> getCustomColumnName(String sql) {
        Set<String> result = Sets.newHashSet();
        CallBack<Matcher> getColumn = param -> {
            Matcher m = param[0];
            while (m.find()) {
                result.add(m.group(2));
            }
        };
        String[] reps = {"(\\{\\^)([\\w]+)(\\^\\})", "(\\{\\$)([\\w]+)(\\$\\})", "(\\{\\&)([\\w]+)(\\&\\})"};
        for (String rep : reps) {
            getColumn.method(Pattern.compile(rep).matcher(sql));
        }
        return result;
    }

    /**
     * 获取表中所有字段名称  不正确-------------------
     *
     * @param sql 表名
     * @return
     * @throws BaseException show full columns from tableName 通过表名获取数据库元数据
     */
    public DataTableEntity getColumnNames(String sql) throws SQLException {
        DataTableEntity dataTableEntity = new DataTableEntity();
        Set<String> customColumnName = getCustomColumnName(sql);
        sql = sql.replaceAll("(\\{\\$((?!(\\{\\$)).)+\\$\\})|(\\{\\^((?!(\\{\\^)).)+\\^\\})|(\\{\\&((?!(\\{\\&)).)+\\&\\})", "null");
        List<String> ternaryOperatorSuitList = BaseUtil.getSuitContent(sql, "\\{@((?!(\\{@)|(@\\})|(\\[\\[)|(\\]\\])).)+@\\}");
        String executeContent;
        String resultValue;
        for (String ternaryOperator : ternaryOperatorSuitList) {
            executeContent = ternaryOperator.substring(2, ternaryOperator.length() - 2);
            try {
                resultValue = BaseUtil.executeExpression(executeContent).toString();
                sql = sql.replace(ternaryOperator, resultValue);
            } catch (Exception e) {
                SpringMVCContextHolder.getSystemLogger().error(e);
                sql = sql.replace(ternaryOperator, "");
            }
        }
        sql = sql.replaceAll("\\[\\[((?!\\[).)+\\]\\]", "")
                .replaceAll("and\\s+1\\s*=\\s*1", "")
                .replaceAll("\\{=.+=\\}", "")
                .replaceAll("(\\t*\\n){2,}", "\n");
        sql = QuerySqlParseUtil.parseSplitTableSql(baseDao, sql, true);
        SpringMVCContextHolder.getSystemLogger().info(sql);
        //与数据库的连接
        try (Connection con = ConnectionManager.getConnection();
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery()) {
            //结果集元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            Set<String> fieldNameSet = Sets.newHashSet();
            for (int i = 1; i <= size; i++) {
                FieldSetEntity fieldSetEntity = new FieldSetEntity();
                FieldMetaEntity f = new FieldMetaEntity();
                f.setTableName(new Object[]{CmnConst.PRODUCT_SYS_DATAMODEL_FIELD});
                fieldSetEntity.setMeta(f);
                fieldSetEntity.setValue(CmnConst.TABLE_NAME, rsmd.getTableName(i));//获取表名
                //字段名或别名
                fieldSetEntity.setValue(CmnConst.FIELD_NAME, rsmd.getColumnLabel(i));
                //字段类型
                fieldSetEntity.setValue(CmnConst.FIELD_FORMAT, "string");
                // 查询类型
                fieldSetEntity.setValue(CmnConst.SEARCH_TYPE, -1);
                //获取指定列的指定列大小
//            fieldSetEntity.setValue(CmnConst.FIELD_LENGTH, rsmd.getPrecision(i));
                dataTableEntity.addFieldSetEntity(fieldSetEntity);
                fieldNameSet.add(fieldSetEntity.getString(CmnConst.FIELD_NAME));
            }
            for (String columnName : customColumnName) {
                if (fieldNameSet.contains(columnName)) {
                    continue;
                }
                FieldSetEntity fs = new FieldSetEntity();
                fs.setTableName(CmnConst.PRODUCT_SYS_DATAMODEL_FIELD);
                fs.setValue(CmnConst.FIELD_NAME, columnName);
                fs.setValue(CmnConst.FIELD_FORMAT, "string");
                fs.setValue(CmnConst.SEARCH_TYPE, -1);
                dataTableEntity.addFieldSetEntity(fs);
            }
            return dataTableEntity;
        }
    }

    /**
     * 根据数据源配置，查询出数据
     *
     * @param dfs 数据源
     * @return
     */
    public DataTableEntity getDataSourceData(FieldSetEntity dfs) {
        if (dfs == null) {
            return null;
        }
        String sql = dfs.getString("sql_text");
        sql = SystemParamReplace.systemParamsReplace(sql);
        DataTableEntity dt = null;
        if (dfs.getBoolean("is_page")) {
            dt = baseDao.listTable(sql, new String[]{}, dfs.getInteger("page_size"), 1);
        } else {
            dt = baseDao.listTable(sql, new String[]{});
        }
        return dt;
    }

    /**
     * 根据数据源配置，查询出数据
     *
     * @param uuid 数据源uuid
     * @return
     */
    public DataTableEntity getDataSourceData(String uuid) {
        if (StringUtils.isEmpty(uuid)) {
            return null;
        }
        //获取数据源
        DataTableEntity datasources = DataPoolCacheImpl.getInstance().getCacheData("数据源配置", new String[]{uuid});
        if (datasources != null && datasources.getRows() > 0) {
            return getDataSourceData(datasources.getFieldSetEntity(0));
        }
        return null;
    }

    public DataTableEntity getDictNames() {
        return baseDao.listTable("SELECT dict_name FROM product_sys_dict GROUP BY dict_name", new Object[]{});
    }

    /**
     * 根据数据源获取所有字段信息
     *
     * @param uuid
     * @return
     */
    public DataTableEntity getDataReouceOfField(String uuid) {
        return baseDao.listTable(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG_FIELD, "datasource_uuid=?", new Object[]{uuid});
    }

    /**
     * 获取所有参照
     *
     * @return
     */
    public List<String> getDictAndPrompt() {
        List<String> reference_name = new ArrayList<String>();
        StringBuffer query_sql = new StringBuffer();
        query_sql.append(" select dict_name from( ");
        query_sql.append(" select CONCAT('《',dict_name,'》')dict_name FROM product_sys_dict GROUP BY dict_name ");
        query_sql.append(" UNION ALL ");
        query_sql.append(" select prompt_name FROM product_sys_prompt ");
        query_sql.append(" )a ");
        DataTableEntity dt = baseDao.listTable(query_sql.toString(), new Object[]{});
        if (!BaseUtil.dataTableIsEmpty(dt)) {
            for (int i = 0; i < dt.getRows(); i++) {
                reference_name.add(dt.getFieldSetEntity(i).getString("dict_name"));
            }
        }
        return reference_name;
    }
}