package com.liangjian.dataplatform.member.service.impl;

import com.liangjian.dataplatform.member.dao.fourthly.MemDataDictoryRepository;
import com.liangjian.dataplatform.member.entity.po.secondary.*;
import com.liangjian.dataplatform.member.entity.vo.response.BirdGirdTableResponse2;
import com.liangjian.dataplatform.member.jobUtil.EsUtils;
import com.liangjian.dataplatform.member.service.IMemberListService;
import com.liangjian.dataplatform.member.util.StringUtil;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.lucene.search.join.ScoreMode;
import org.elasticsearch.action.search.SearchRequest;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.client.RequestOptions;
import org.elasticsearch.client.RestHighLevelClient;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.NestedQueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.index.reindex.ScrollableHitSource;
import org.elasticsearch.search.SearchHit;
import org.elasticsearch.search.SearchHits;
import org.elasticsearch.search.builder.SearchSourceBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.elasticsearch.core.ElasticsearchRestTemplate;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.util.*;

@Service
public class MemberListService implements IMemberListService {

    //链接查询es
    public static EsUtils esUtils = new EsUtils();

    //sql查询es _xpack/sql?format=json
    public static String Host = "http://192.168.50.41:9200/";

    @Autowired
    private MemDataDictoryRepository memDataDictoryRepository;


    @Autowired
    private ElasticsearchRestTemplate elasticsearchTemplate;
//    @Autowired
//    private EntityManager entityManager;


    @Override
    public String index(FiltersData data) {
        int start = 0;
        String filterStr = null;
        String query = null;
        Map<String, Object> ret = new HashMap<String, Object>();
        Map<String, List<FiltersUtils>> map = new HashMap<String, List<FiltersUtils>>();
        List<String> phonesSearch = new ArrayList<String>(1);
        if (data != null) {
            List<FiltersUtils> filters = data.getFilters();
            if (filters != null && filters.size() != 0) {
                for (FiltersUtils filter : filters) {
                    String fieId = filter.getField();
                    String value = filter.getValue();
                    if ("MemUsualPhone".equals(fieId.trim()) && null != value && !"".equals(value.trim())) {
                        phonesSearch.add(value);
                        String[] encryption = getPhoneEncrypt(phonesSearch, true);
                        value = encryption[0];
                        filter.setValue(value);
                    }

                    if (fieId.indexOf(".") > 0 && null != value && !value.equals("null")) {
                        List<FiltersUtils> list = null;
                        map.put(fieId, list);
                        if (list == null) {
                            list = new ArrayList<FiltersUtils>();
                        }
                        List<Map<String, Object>> rulesList = filter.getRules();
                        if (null != rulesList && rulesList.size() > 0) {
                            Map<String, Object> rulesMap = rulesList.get(0);
                            if (!StringUtil.isEmpty(rulesMap.get("value").toString())) {
                                list.add(filter);
                            }
                        }
                        map.put(fieId, list);
                    }

                }
            }
            filterStr = getFilterString(filters);
            if (data.getPageIndex() == 1) {
                start = 0;
            } else {
                start = ((data.getPageIndex() - 1) * data.getPageSize()) + 1;
            }
            query = "select * from mem_memberbase_p where 1=1 " + filterStr + "  order by RegisterTime desc   limit " + data.getPageSize() + "";

        }
        String sql = EsUtils.query(query, "_sql/translate?format=json", true);
        ret.put("items", new ArrayList<Map<String, Object>>());
        String from = "\"from\":" + start + ",";
        StringBuffer sb = new StringBuffer(sql);
        sql = sb.insert(1, from).toString();


        int sourceIndex = sql.indexOf("\"_source\":{");
        int sourceIndexEnd = sql.indexOf("},", sourceIndex);
        String sourcestr = sql.substring(sourceIndex, sourceIndexEnd + 1);
        sql = sql.replace(sourcestr, "\"_source\":{\"includes\":[\"*\"], \"excludes\": []}");

        int fieldIndex = sql.indexOf("\"docvalue_fields\":[");
        int fieldIndexEnd = sql.indexOf("],", fieldIndex);

        String firstHalf = sql.substring(0, fieldIndex);
        String Half = sql.substring(fieldIndex + (fieldIndexEnd - fieldIndex + 2), sql.length());
        sql = firstHalf + Half;

        if (map.size() > 0) {
            for (String key : map.keySet()) {
                List<FiltersUtils> filters = map.get(key);
                if (null == filters || filters.size() == 0) {
                    continue;
                }
                String[] values = new String[filters.size()];
                for (int i = 0; i < filters.size(); i++) {
                    FiltersUtils filter = filters.get(i);
                    List<Map<String, Object>> rules = filter.getRules();
                    if (null == rules || rules.size() == 0) {
                        String value = filter.getValue();
                        if (!StringUtil.isEmpty(value)) {
                            values[i] = value;
                        }
                        continue;
                    } else {
                        String value = rules.get(0).get("value").toString();
                        if (!StringUtil.isEmpty(value)) {
                            values[i] = value;
                        }
                    }
                }

                StringBuilder nestMinMatch = new StringBuilder(5);
                nestMinMatch.append("{ 'bool': { 'should': [");
                for (int i = 0; i < values.length; i++) {
                    var valuestr = "[" + String.join(",", values[0]) + "]";
                    String newKey = key.replace('.', ',');
                    String[] keytoken = newKey.split(",");
                    String path = keytoken[0];
                    String tnest = "{\"nested\":{\"query\":{\"bool\":{\"must\":[{\"term\":{\"Terminals.TerminalCode\":{\"value\":\"6\"}}}]}},\"path\":\"Terminals\",\"ignore_unmapped\":false,\"score_mode\":\"none\",\"boost\":1.0}}";
                    tnest = tnest.replace(":\"6\"", ":\"" + values[0] + "\"");
                    tnest = tnest.replace("Terminals.TerminalCode", key);
                    tnest = tnest.replace("Terminals", path);
                    nestMinMatch.append(tnest);
                }
//                nestMinMatch = nestMinMatch.remove(nestMinMatch.length()-1,1);
                nestMinMatch.substring(nestMinMatch.length() - 1);
                nestMinMatch.append("],'minimum_should_match': " + values.length + "");
                nestMinMatch.append("}}");

                String snest = "{\"nested\":{\"query\":{\"term\":{\"Terminals.MemSourcePlatforms\":{\"value\":\"2\",\"boost\":1.0}}},\"path\":\"Terminals\",\"ignore_unmapped\":false,\"score_mode\":\"none\",\"boost\":1.0}}";
                String sValue = "";
                List<Map<String, Object>> rules = null;
                if (null != filters && filters.size() > 0) {
                    rules = filters.get(0).getRules();
                }

                if (rules != null && rules.size() > 0) {
                    sValue = rules.get(0).get("value").toString();
                } else {
                    sValue = filters.get(0).getValue();
                }

                sql = sql.replace(snest.replace("Terminals.MemSourcePlatforms", key).replace("\"2\"", "\"" + sValue + "\""), nestMinMatch.toString());
            }
        }

        String str = EsUtils.query(sql, "mem_memberbase_p/_search?format=json", false);

        net.sf.json.JSONObject jsonObject = JSONObject.fromObject(str);
        JSONArray jsonArray = JSONObject.fromObject(jsonObject.getString("hits")).getJSONArray("hits");
        List<Map<String, Object>> items = new ArrayList<Map<String, Object>>();
        for (int i = 0; i < jsonArray.size(); i++) {
            Map<String, Object> hitsMap = (Map<String, Object>) jsonArray.get(i);
            Map<String, Object> sourceMap = (Map<String, Object>) hitsMap.get("_source");
            sourceMap.put("_id", hitsMap.get("_id"));
            items.add(sourceMap);
        }
        Object[] object = jsonArray.toArray();
        List<Object> lisat = Arrays.asList(object);
        List<String> phones = new ArrayList<String>(items.size());
        for (Map<String, Object> o : items) {
            Map<String, Object> maps = (Map<String, Object>) o;
            if (null != maps.get("MemUsualPhone") && maps.get("MemUsualPhone").toString().length() == 32) {
                phones.add(maps.get("MemUsualPhone").toString());
            } else {
                phones.add("");
            }
        }
        String[] phones1 = getPhoneEncrypt(phones, false);
        for (int i = 0; i < items.size(); i++) {
            Map<String, Object> maps = (Map<String, Object>) items.get(i);
            if (null != maps.get("MemUsualPhone") && maps.get("MemUsualPhone").toString().length() == 32) {
                maps.put("MemUsualPhoneCr", maps.get("MemUsualPhone"));
                String phoneStars = phones1[i];
                if (phoneStars != null && phoneStars.length() > 8) {
                    String replaced = phoneStars.substring(3, 7);
                    phoneStars = phoneStars.replace(replaced, "****");
                }
                maps.put("MemUsualPhone", phoneStars);
                maps.put("MemUsualPhoneOr", phones1[i]);
            }
//            Object MemUsualPhone = source.get("MemUsualPhone");
//            source.put("MemUsualPhone","");
        }
        //总数
        long totalcount = 0;
        sql = "select count(1) from mem_memberbase_p where 1=1 " + filterStr + "";
        String dataTotale = EsUtils.query(sql, "_sql?format=json", true);
        JSONObject totalJson = JSONObject.fromObject(dataTotale);
        JSONArray rowsArray = totalJson.getJSONArray("rows");
        List<Integer> listRow = (List<Integer>) rowsArray.get(0);

        ret.put("items", items);
        ret.put("totalCount", listRow.get(0));
        String json = JSONObject.fromObject(ret).toString();
        return json;
    }

    @Override
    public BirdGirdTableResponse2 getMemberList(MemberDataFilter memberDataFilter) throws IOException {
        RestHighLevelClient client = elasticsearchTemplate.getClient();

        // 页码
        Integer pageIndex = 0;
        // 每页记录数
        Integer pageSize = 10;
        if (memberDataFilter.getPageIndex() != null) {
            pageIndex = memberDataFilter.getPageIndex();
        }
        if (memberDataFilter.getPageSize() != null) {
            pageSize = memberDataFilter.getPageSize();
        }

        // 标签
        List<Tags> tags = memberDataFilter.getSelectedTags();
        // 标签筛选条件
        BoolQueryBuilder boolQueryBuilderTags = QueryBuilders.boolQuery();
        if (tags != null) {
            tags.forEach(tag -> {
                boolQueryBuilderTags.should(QueryBuilders.nestedQuery("Tags", QueryBuilders.matchQuery("Tags.TagId", tag.getTagId()), ScoreMode.None));
            });
        }

        // 月份
        String selectedMonth = memberDataFilter.getSelectedMonth();
        // 月份筛选
        NestedQueryBuilder queryBuilderMonth;
        if (selectedMonth != null) {
            queryBuilderMonth = QueryBuilders.nestedQuery("Tags", QueryBuilders.matchQuery("Tags.TagName", selectedMonth.replaceAll("-", "")), ScoreMode.None);

        } else {
            queryBuilderMonth = QueryBuilders.nestedQuery("Tags", QueryBuilders.matchAllQuery(), ScoreMode.None);

        }
        // 平台筛选
        List<Terminal> terminals = memberDataFilter.getSelectedTerminal();
        // 平台筛选条件
        BoolQueryBuilder boolQueryBuilderTerminal = QueryBuilders.boolQuery();
        if (terminals != null) {
            terminals.forEach(terminal -> {
                boolQueryBuilderTerminal.should(QueryBuilders.nestedQuery("Tags", QueryBuilders.nestedQuery("Tags.OriginTypeRepur", QueryBuilders.matchQuery("Tags.OriginTypeRepur.OriginType", terminal.getTerminalId()), ScoreMode.None), ScoreMode.None));
            });
        }

        // 合并筛选条件
        BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
        if (tags != null) {
            boolQueryBuilder.must(boolQueryBuilderTags);
        }
        if (selectedMonth != null) {
            boolQueryBuilder.must(queryBuilderMonth);
        }
        if (terminals != null) {
            boolQueryBuilder.must(boolQueryBuilderTerminal);
        }
        // 创建查询器
        SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
        // 返回字段
        String[] includeField = new String[]{"MemUsualPhone", "MemberNickName", "MemberId","MemberGender", "PatientAge", "Tags.RN_isBeforeCustomer", "Tags.RM_isBeforeCustomer", "Tags.F", "Tags.M"};
        searchSourceBuilder.size(pageSize).from((pageIndex -1 ) * pageSize).query(boolQueryBuilder).fetchSource(includeField, null);
        // 查询
        SearchRequest searchRequest = new SearchRequest("mem_memberbase_p").source(searchSourceBuilder);
        // 查询结果
        SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);

        // 结果保存到List<Map<String, Object>>
        SearchHits hits = searchResponse.getHits();
        List<Map<String, Object>> resultHashMap = new ArrayList<>(pageSize);


        for (SearchHit hit : hits) {
            System.out.println(hit.getSourceAsMap());
        }


        hits.forEach(hit -> {
            Map<String, Object> sourceAsMap = hit.getSourceAsMap();
            if (sourceAsMap.containsKey("Tags")) {
                ArrayList<HashMap<String, String>> RFMList = (ArrayList<HashMap<String, String>>) sourceAsMap.get("Tags");
                HashMap<String, String> RFM = RFMList.get(0);
                sourceAsMap.remove("Tags");
                sourceAsMap.put("M", RFM.get("M"));
                sourceAsMap.put("F", RFM.get("F"));
                sourceAsMap.put("RN_isBeforeCustomer", RFM.get("RN_isBeforeCustomer"));
                sourceAsMap.put("RM_isBeforeCustomer", RFM.get("RM_isBeforeCustomer"));
            }

            resultHashMap.add(sourceAsMap);
        });

        return new BirdGirdTableResponse2(resultHashMap, searchResponse.getHits().getTotalHits());
    }


    // 加密
    public String[] getPhoneEncrypt(List<String> memPhones, boolean sign) {
        int len = memPhones.size();
        String[] phones = new String[len];
        StringBuilder sb = new StringBuilder();
        sb.append("select ");

        for (int i = 0; i < len; i++) {
            String number = memPhones.get(i);
            if (!StringUtil.isEmpty(number)) {
                Map<String, Object> map = null;
                if (sign) {// 加密
                    map = memDataDictoryRepository.desEncryptFixKey(number);
                } else {// 解密
                    map = memDataDictoryRepository.desDecryptFixKey(number);
                }
                String phone = (String) map.get("phone");
                phones[i] = phone;
            }
        }
        return phones;
    }


    public String getFilterString(List<FiltersUtils> filterList) {
        String queryCondition = "";
        if (filterList != null) {
            for (FiltersUtils item : filterList) {
                if (item != null && (!StringUtil.isEmpty(item.getValue()) || (item.getRules() != null && item.getRules().size() > 0))) {
                    String val = item.getValue();
                    String field = item.getField();
                    String operate = item.getOperate();
                    switch (operate) {
                        case "equal":
                            queryCondition += " and " + field + "=  '" + val + "'";
                            break;
                        case "notequal":
                            queryCondition += " and " + field + "!=  " + val;

                            break;
                        case "less":
                            queryCondition += " and " + field + "< " + val;

                            break;
                        case "lessorequal":
                            queryCondition += " and " + field + "<= " + val;

                            break;
                        case "greater":
                            queryCondition += " and " + field + "> " + val;

                            break;
                        case "greateroreeuqal":
                            queryCondition += " and " + field + ">= " + val;

                            break;
                        case "contains":
                            queryCondition += " and " + field + " like '%" + val + "%'";

                            break;
                        case "startswith":
                            queryCondition += " and " + field + " like '" + val + "%'";

                            break;
                        case "endswith":
                            queryCondition += " and " + field + " like '%" + val + "'";

                            break;
                        case "or":
                            var innerCondition = "1 = 1";
                            List<Map<String, Object>> rulesMap = item.getRules();
                            for (Map<String, Object> stringObjectMap : rulesMap) {
                                String rulesField = (String) stringObjectMap.get("field");
                                String rulesValue = (String) stringObjectMap.get("value");
                                innerCondition += " or " + rulesField + " = '" + rulesValue + "'";
                            }

                            innerCondition = innerCondition.replace("1 = 1 or", "");
                            queryCondition += " and (" + innerCondition + ")";

                            break;
                        case "nestand":
                            var innerConditionNest = "1 = 1";
                            List<Map<String, Object>> rules = item.getRules();
                            for (Map<String, Object> stringObjectMap : rules) {
                                String rulesField = (String) stringObjectMap.get("field");
                                String rulesvalue = (String) stringObjectMap.get("value");
                                innerConditionNest += " and " + rulesField + " = '" + rulesvalue + "'";
                            }
                            innerConditionNest = innerConditionNest.replace("1 = 1 and", "");
                            queryCondition += " and (" + innerConditionNest + ")";

                            break;
                    }
                }
            }
//            Console.WriteLine(queryCondition);
        }
        return queryCondition;
    }
}
