package com.zanxiang.game.data.serve.service.impl;

import com.github.sd4324530.jtuple.Tuple2;
import com.zanxiang.game.data.serve.component.DataPowerComponent;
import com.zanxiang.game.data.serve.pojo.dto.OverallSummaryDTO;
import com.zanxiang.game.data.serve.pojo.vo.OverallSummaryAmountLineDataVO;
import com.zanxiang.game.data.serve.pojo.vo.OverallSummaryCostLineDataVO;
import com.zanxiang.game.data.serve.pojo.vo.OverallSummaryUserLineDataVO;
import com.zanxiang.game.data.serve.pojo.vo.OverallSummaryVO;
import com.zanxiang.game.data.serve.service.IOverallSummaryService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Condition;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Criteria;
import org.nutz.dao.sql.Sql;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.temporal.ChronoUnit;
import java.util.*;

/**
 * @author tianhua
 * @time 2023/7/4
 * @Description 整体概况逻辑处理
 **/
@Service
@Slf4j
public class OverallSummaryServiceImpl implements IOverallSummaryService {

    @Autowired
    private Dao dao;
    @Autowired
    private DataPowerComponent dataPowerComponent;

    /**
     * 整体概况总计数据部分
     *
     * @param dto 前端传递的查询条件实体
     * @return 返回给前端的整体概况总计数据实体
     */
    @Override
    public OverallSummaryVO getOverallSummaryTotalData(OverallSummaryDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = dto.getPitcherId() == null ? poerInfo.first : Collections.singletonList(dto.getPitcherId());
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : Collections.singletonList(dto.getGameId());

        //创建返回给前端的对象
        OverallSummaryVO overallSummaryVO = new OverallSummaryVO();

        //默认查询‘ZX_ONE’数据
        if (StringUtils.isBlank(dto.getSourceSystem())) {
            dto.setSourceSystem("ZX_ONE");
        }

        //计算总计条件
        Condition totalCri = myCondition(userIds, gameIds, dto, true, false, -1);
        //计算今日数据条件
        Condition todayCri = myCondition(userIds, gameIds, dto, true, false, 1);
        //计算昨日数据条件
        Condition yesterdayCri = myCondition(userIds, gameIds, dto, true, false, 0);
        //计算7日数据条件
        Condition d7Cri = myCondition(userIds, gameIds, dto, true, false, 7);
        //计算30日数据条件
        Condition d30Cri = myCondition(userIds, gameIds, dto, true, false, 30);

        //计算游戏数量相关数据
        //查询总投游戏数量
        overallSummaryVO.setTotalGameCount(getResultBySql(2, null, myCondition(userIds, gameIds, dto, false, false, -1)));
        //查询今日在投游戏数量
        overallSummaryVO.setTodayGameCount(getResultBySql(2, null, myCondition(userIds, gameIds, dto, false, false, 1)));
        //查询昨日在投游戏数量
        overallSummaryVO.setYesterdayGameCount(getResultBySql(2, null, myCondition(userIds, gameIds, dto, false, false, 0)));

        //计算玩家数量相关数据
        //查询玩家总计数
        overallSummaryVO.setTotalPlayers(getResultBySql(1, "today_user_count-ads_overall_summary_user", totalCri));
        //查询今日新增玩家总计数
        overallSummaryVO.setTodayPlayerCount(getResultBySql(1, "today_user_count-ads_overall_summary_user", todayCri));
        //查询昨日新增玩家总计数
        overallSummaryVO.setYesterdayPlayerCount(getResultBySql(1, "today_user_count-ads_overall_summary_user", yesterdayCri));

        //计算渠道数量相关数据
        //查询渠道总计数
        overallSummaryVO.setTotalAgentCount(getResultBySql(1, "today_agent_count-ads_overall_summary_agent", totalCri));
        //查询今日新增渠道
        overallSummaryVO.setTodayAgentCount(getResultBySql(1, "today_agent_count-ads_overall_summary_agent", todayCri));
        //查询昨日新增渠道
        overallSummaryVO.setYesterdayAgentCount(getResultBySql(1, "today_agent_count-ads_overall_summary_agent", yesterdayCri));

        //计算充值相关数据
        //分source_system判断
        if (dto.getSourceSystem().startsWith("BG_")) {
            //查询充值总计（买量）
            overallSummaryVO.setTotalAmount(getResultBySql("today_buy_amount-ads_overall_summary_amount", totalCri));
            //查询今日账面（买量）
            overallSummaryVO.setTodayAmount(getResultBySql("today_buy_amount-ads_overall_summary_amount", todayCri));
            //查询昨日账面（买量）
            overallSummaryVO.setYesterdayAmount(getResultBySql("today_buy_amount-ads_overall_summary_amount", yesterdayCri));
            //查询今日新用户充值
            overallSummaryVO.setTodayNewPlayerAmount(getResultBySql("today_new_user_amount-ads_overall_summary_amount", todayCri));
            //查询昨日新用户充值（昨日的首日新用户充值）
            overallSummaryVO.setYesterdayNewPlayerAmount(getResultBySql("today_first_new_user_amount-ads_overall_summary_amount", yesterdayCri));

            //查询充值总计（自然量）
            overallSummaryVO.setTotalAmountForBG(getResultBySql("today_nature_amount-ads_overall_summary_amount", totalCri));
            //查询今日账面（自然量）
            overallSummaryVO.setTodayAmountForBG(getResultBySql("today_nature_amount-ads_overall_summary_amount", todayCri));
            //查询昨日账面（自然量）
            overallSummaryVO.setYesterdayAmountForBG(getResultBySql("today_nature_amount-ads_overall_summary_amount", yesterdayCri));
        } else {
            //查询充值总计
            overallSummaryVO.setTotalAmount(getResultBySql("today_amount-ads_overall_summary_amount", totalCri));
            //查询今日账面
            overallSummaryVO.setTodayAmount(getResultBySql("today_amount-ads_overall_summary_amount", todayCri));
            //查询昨日账面
            overallSummaryVO.setYesterdayAmount(getResultBySql("today_amount-ads_overall_summary_amount", yesterdayCri));
            //查询今日新用户充值
            overallSummaryVO.setTodayNewPlayerAmount(getResultBySql("today_new_user_amount-ads_overall_summary_amount", todayCri));
            //查询昨日新用户充值（昨日的首日新用户充值）
            overallSummaryVO.setYesterdayNewPlayerAmount(getResultBySql("today_first_new_user_amount-ads_overall_summary_amount", yesterdayCri));
        }

        //计算消耗相关数据
        //查询消耗总计
        overallSummaryVO.setTotalCost(getResultBySql("today_cost-ads_overall_summary_cost", totalCri));
        //查询今日消耗
        overallSummaryVO.setTodayTotalCost(getResultBySql("today_cost-ads_overall_summary_cost", todayCri));
        //查询昨日消耗
        overallSummaryVO.setYesterdayTotalCost(getResultBySql("today_cost-ads_overall_summary_cost", yesterdayCri));

        //计算Roi相关数据
        //计算总数据的首日Roi
        //计算所有的首日新用户充值
        BigDecimal totalFirstNewUserAmount = getResultBySql("today_first_new_user_amount-ads_overall_summary_amount", totalCri);
        overallSummaryVO.setTotalFirstRoi(overallSummaryVO.getTotalCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                totalFirstNewUserAmount.divide(overallSummaryVO.getTotalCost(), 4, RoundingMode.HALF_UP).multiply(BigDecimal.valueOf(100)));
        //计算今日Roi
        //计算当天首日新用户充值
        BigDecimal todayFirstNewUserAmount = getResultBySql("today_first_new_user_amount-ads_overall_summary_amount", todayCri);
        overallSummaryVO.setFirstRoi(overallSummaryVO.getTodayTotalCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                todayFirstNewUserAmount.divide(overallSummaryVO.getTodayTotalCost(), 4, RoundingMode.HALF_UP).multiply(BigDecimal.valueOf(100)));
        //计算总回本数据
        overallSummaryVO.setTotalRoi(overallSummaryVO.getTotalCost().compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                overallSummaryVO.getTotalAmount().divide(overallSummaryVO.getTotalCost(), 4, RoundingMode.HALF_UP).multiply(BigDecimal.valueOf(100)));
        //布谷数据不同处理
        if (dto.getSourceSystem().startsWith("BG_")) {
            //计算7日Roi
            //计算7日账面充值和消耗(布谷只算买量)
            BigDecimal d7Amount = getResultBySql("today_buy_amount-ads_overall_summary_amount", d7Cri);
            BigDecimal d7Cost = getResultBySql("today_cost-ads_overall_summary_cost", d7Cri);
            overallSummaryVO.setD7TotalRoi(d7Cost.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                    d7Amount.divide(d7Cost, 4, RoundingMode.HALF_UP).multiply(BigDecimal.valueOf(100)));
            //计算30日Roi
            //计算7日账面充值和消耗（布谷只算买量）
            BigDecimal d30Amount = getResultBySql("today_buy_amount-ads_overall_summary_amount", d30Cri);
            BigDecimal d30Cost = getResultBySql("today_cost-ads_overall_summary_cost", d30Cri);
            overallSummaryVO.setD30TotalRoi(d30Cost.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                    d30Amount.divide(d30Cost, 4, RoundingMode.HALF_UP).multiply(BigDecimal.valueOf(100)));
        } else {
            //计算7日Roi
            //计算7日账面充值和消耗
            BigDecimal d7Amount = getResultBySql("today_amount-ads_overall_summary_amount", d7Cri);
            BigDecimal d7Cost = getResultBySql("today_cost-ads_overall_summary_cost", d7Cri);
            overallSummaryVO.setD7TotalRoi(d7Cost.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                    d7Amount.divide(d7Cost, 4, RoundingMode.HALF_UP).multiply(BigDecimal.valueOf(100)));
            //计算30日Roi
            //计算7日账面充值和消耗
            BigDecimal d30Amount = getResultBySql("today_amount-ads_overall_summary_amount", d30Cri);
            BigDecimal d30Cost = getResultBySql("today_cost-ads_overall_summary_cost", d30Cri);
            overallSummaryVO.setD30TotalRoi(d30Cost.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                    d30Amount.divide(d30Cost, 4, RoundingMode.HALF_UP).multiply(BigDecimal.valueOf(100)));
        }

        return overallSummaryVO;
    }

    /**
     * 整体概况折线图数据部分
     *
     * @param dto 前端传递的查询条件实体
     * @return 返回给前端的整体概况折线图数据实体
     */
    @Override
    public Map<String, List> getOverallSummaryLineData(OverallSummaryDTO dto) {
        Tuple2<List<Long>, List<Long>> poerInfo = dataPowerComponent.getPowerInfo(dto.getSourceSystem());
        List<Long> userIds = dto.getPitcherId() == null ? poerInfo.first : Collections.singletonList(dto.getPitcherId());
        List<Long> gameIds = dto.getGameId() == null ? poerInfo.second : Collections.singletonList(dto.getGameId());

        //根据条件获取数据
        Condition lineCon = myCondition(userIds, gameIds, dto, true, true, -1);

        //查询新增用户数据
        Sql userSql = Sqls.create(userLineSql() + lineCon);
        //自定义回显对象
        userSql.setCallback(Sqls.callback.entities());
        userSql.setEntity(dao.getEntity(OverallSummaryUserLineDataVO.class));
        //执行sql得到结果
        dao.execute(userSql);
        List<OverallSummaryUserLineDataVO> userLineList = userSql.getList(OverallSummaryUserLineDataVO.class);

        //查询消耗折线数据
        Sql costSql = Sqls.create(costLineSql() + lineCon);
        //自定义回显对象 结果自动添加到一个List中 List<Entity>
        costSql.setCallback(Sqls.callback.entities());
        costSql.setEntity(dao.getEntity(OverallSummaryCostLineDataVO.class));
        //执行自定义sql语句
        dao.execute(costSql);
        List<OverallSummaryCostLineDataVO> costLineList = costSql.getList(OverallSummaryCostLineDataVO.class);

        //查询充值、买量账面、自然量账面数据
        Sql amountSql = Sqls.create(amountLineSql() + lineCon);
        //自定义回显对象
        amountSql.setCallback(Sqls.callback.entities());
        amountSql.setEntity(dao.getEntity(OverallSummaryAmountLineDataVO.class));
        //执行sql得到结果
        dao.execute(amountSql);
        List<OverallSummaryAmountLineDataVO> amountLineList = amountSql.getList(OverallSummaryAmountLineDataVO.class);

        //计算首日Roi
        //记录每个dt的cost
        Map<LocalDate, BigDecimal> costMap = new HashMap<>();
        for (OverallSummaryCostLineDataVO costLineDataVO : costLineList) {
            costMap.put(costLineDataVO.getDt(), costLineDataVO.getCost());
        }
        //根据首日新用户充值计算roi
        for (OverallSummaryAmountLineDataVO amountLineDataVO : amountLineList) {
            //得到当天的消耗
            BigDecimal cost = costMap.get(amountLineDataVO.getDt());
            amountLineDataVO.setFirstRoi(cost == null || cost.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO :
                    amountLineDataVO.getTodayFirstNewUserAmount().divide(cost, 4, RoundingMode.HALF_UP).multiply(BigDecimal.valueOf(100)));
        }

        Map<String, List> resMap = new HashMap<>(3);
        resMap.put("costLineList", checkAllDateData("costList", costLineList, dto));
        resMap.put("amountLineList", checkAllDateData("amountList", amountLineList, dto));
        resMap.put("userLineList", checkAllDateData("userList", userLineList, dto));

        return resMap;
    }

    /**
     * 自定义查询条件
     *
     * @param dto           前端传递的查询参数实体
     * @param needGameId    是否需要拼接gameId查询条件（整体概况游戏表不需要gameId条件）
     * @param needLocalDate 是否拼接日期查询条件(折线图需要日期条件，总计不需要)
     * @param queryDays     拼接需要查询数据的天数条件（给今日昨日、首日Roi、7日Roi以及30日Roi使用）
     * @return Condition 查询条件
     */
    private Condition myCondition(List<Long> userIds, List<Long> gameIds, OverallSummaryDTO dto, Boolean needGameId, Boolean needLocalDate, Integer queryDays) {

        Criteria cri = Cnd.cri();
        //默认查询条件
        if (gameIds != null && needGameId) {
            //拼接游戏查询条件
            cri.where().andInList("game_id", gameIds);
        }
        if (dto.getAgentId() != null) {
            //拼接渠道查询条件
            cri.where().andInList("agent_id", Arrays.asList(dto.getAgentId()));
        }
        if (dto.getAccountId() != null) {
            //拼接广告账户查询条件
            cri.where().andEquals("account_id", dto.getAccountId());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            //拼接查询 sdk 来源条件
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        //前端传递 pitcherId 根据 pitcherId 查询
        if (userIds != null) {
            //拼接投手查询条件
            cri.where().andInList("pitcher_id", userIds);
        }

        //根据参数拼接条件
        switch (queryDays) {
            //前一天时间
            case 0 -> cri.where().andEquals("dt", LocalDate.now().minusDays(1));
            //当天时间
            case 1 -> cri.where().andEquals("dt", LocalDate.now());
            //7天时间
            case 7 -> cri.where().andBetween("dt", LocalDate.now().minusDays(7), LocalDate.now());
            //30天时间
            case 30 -> cri.where().andBetween("dt", LocalDate.now().minusDays(30), LocalDate.now());
            default -> {
            }
        }

        //折线数据需要拼接时间查询条件
        if (needLocalDate) {
            //如果传入时间区间参数为空 设置默认值
            if (dto.getBeginDate() == null || dto.getEndDate() == null) {
                dto.setBeginDate(LocalDate.now().minusDays(30));
                dto.setEndDate(LocalDate.now());
            }
            //添加日期条件
            cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
            //按日期分组
            cri.getGroupBy().groupBy("dt");
            //按日期排序 升序
            cri.getOrderBy().asc("dt");
        }

        return cri;
    }

    /**
     * 检查查询的时间内是否都有数据，没有则赋值默认值0
     *
     * @param type 需要判断list的类型：costList -> 消耗折线数据;
     *             amountList -> 充值相关折线数据;
     *             userList -> 新增用户折线数据
     * @param list 需要判断的list
     * @param dto  前端传递的查询参数实体
     * @return 补全后的list
     */
    private List checkAllDateData(String type, List list, OverallSummaryDTO dto) {
        //先判断是否缺少数据 不少数据直接返回
        if ((list.size() - 1) == dto.getBeginDate().until(dto.getEndDate(), ChronoUnit.DAYS)) {
            return list;
        }
        //创建一个Map用来存储日期数据
        Map<LocalDate, Integer> map = new HashMap<>();
        //添加日期数据
        LocalDate tempDate = dto.getBeginDate();
        while (!tempDate.isAfter(dto.getEndDate())) {
            map.put(tempDate, 1);
            tempDate = tempDate.plusDays(1);
        }
        //不同类型list
        if ("costList".equals(type)) {
            //循环遍历list 查询vo里的日期参数
            for (Object vo : list) {
                Integer value = map.get(((OverallSummaryCostLineDataVO) vo).getDt());
                if (value != null) {
                    map.put(((OverallSummaryCostLineDataVO) vo).getDt(), ++value);
                }
            }
        } else if ("amountList".equals(type)) {
            //循环遍历list 查询vo里的日期参数
            for (Object vo : list) {
                Integer value = map.get(((OverallSummaryAmountLineDataVO) vo).getDt());
                if (value != null) {
                    map.put(((OverallSummaryAmountLineDataVO) vo).getDt(), ++value);
                }
            }
        } else if ("userList".equals(type)) {
            //循环遍历list 查询vo里的日期参数
            for (Object vo : list) {
                Integer value = map.get(((OverallSummaryUserLineDataVO) vo).getDt());
                if (value != null) {
                    map.put(((OverallSummaryUserLineDataVO) vo).getDt(), ++value);
                }
            }
        }
        //遍历map数据 value为 1 的表示 list中没有该日期的实体
        for (Map.Entry<LocalDate, Integer> entry : map.entrySet()) {
            if (entry.getValue() == 1) {
                //不同类型list
                if ("costList".equals(type)) {
                    //添加默认数据
                    list.add(OverallSummaryCostLineDataVO.builder()
                            .dt(entry.getKey())
                            .cost(BigDecimal.ZERO)
                            .build());
                } else if ("amountList".equals(type)) {
                    //添加默认数据
                    list.add(OverallSummaryAmountLineDataVO.builder()
                            .dt(entry.getKey())
                            .amount(BigDecimal.ZERO)
                            .buyAmount(BigDecimal.ZERO)
                            .natureAmount(BigDecimal.ZERO)
                            .todayFirstNewUserAmount(BigDecimal.ZERO)
                            .firstRoi(BigDecimal.ZERO)
                            .build());
                } else if ("userList".equals(type)) {
                    //添加默认数据
                    list.add(OverallSummaryUserLineDataVO.builder()
                            .dt(entry.getKey())
                            .playerAccount(0L)
                            .build());
                }
            }
        }

        //排序 lambda表达式 按日期升序排序
        if ("costList".equals(type)) {
            list.sort((vo1, vo2) -> ((OverallSummaryCostLineDataVO) vo1).getDt()
                    .isBefore(((OverallSummaryCostLineDataVO) vo2).getDt()) ? -1 : 1);
        } else if ("amountList".equals(type)) {
            list.sort((vo1, vo2) -> ((OverallSummaryAmountLineDataVO) vo1).getDt()
                    .isBefore(((OverallSummaryAmountLineDataVO) vo2).getDt()) ? -1 : 1);
        } else if ("userList".equals(type)) {
            list.sort((vo1, vo2) -> ((OverallSummaryUserLineDataVO) vo1).getDt()
                    .isBefore(((OverallSummaryUserLineDataVO) vo2).getDt()) ? -1 : 1);
        }

        return list;
    }

    /**
     * 共用的查询方法
     *
     * @param type        查询的字段类型 {1 -> 查询其他表long类型数据 ; 2 -> 查询游戏在投数量表}
     * @param queryString 参数字符串 格式："xxx-xxx" 前面代表查询的字段 后面代表查询的表名
     * @param cri         查询条件
     * @return 查询结果 Long
     */
    private Long getResultBySql(int type, String queryString, Condition cri) {
        //创建sql
        Sql sql;
        if (type == 1) {
            sql = Sqls.create(sumByItemsAndTablesSql() + cri);
            //设置查询字段及表名
            sql.vars().set("item", queryString.split("-")[0]);
            sql.vars().set("tableName", queryString.split("-")[1]);
        } else {
            sql = Sqls.create(gameCountSql() + cri);
        }
        //设置回传对象
        sql.setCallback(Sqls.callback.longValue());
        //执行sql
        dao.execute(sql);
        //返回结果
        return sql.getLong();
    }

    /**
     * 共用的查询方法
     *
     * @param queryString 参数字符串 格式："xxx-xxx" 前面代表查询的字段 后面代表查询的表名
     * @param cri         查询条件
     * @return 查询结果 BigDecimal
     */
    private BigDecimal getResultBySql(String queryString, Condition cri) {
        //创建sql
        Sql sql = Sqls.create(sumByItemsAndTablesSql() + cri);
        //设置查询字段及表名
        sql.vars().set("item", queryString.split("-")[0]);
        sql.vars().set("tableName", queryString.split("-")[1]);
        //设置回传对象
        sql.setCallback(Sqls.callback.doubleValue());
        //执行sql
        dao.execute(sql);
        //返回结果
        return BigDecimal.valueOf(sql.getDouble()).setScale(2, RoundingMode.HALF_UP);
    }

    /**
     * 共用的gameCount sql语句
     *
     * @return String
     */
    private String gameCountSql() {
        return """
                SELECT
                    COUNT(DISTINCT game_id)
                FROM
                    ads_overall_summary_game
                """;
    }

    /**
     * 共用计算总计sql
     */
    private String sumByItemsAndTablesSql() {
        return """
                SELECT
                    SUM($item)
                FROM
                    $tableName
                """;
    }

    /**
     * 消耗折线数据sql
     */
    private String costLineSql() {
        return """
                SELECT
                    dt,
                    SUM(today_cost) cost
                FROM
                    ads_overall_summary_cost
                """;
    }

    /**
     * 充值、买量账面、自然量折线数据sql
     */
    private String amountLineSql() {
        return """
                SELECT
                    dt,
                    SUM(today_amount) amount,
                    SUM(today_buy_amount) buy_amount,
                    SUM(today_nature_amount) nature_amount,
                    SUM(today_first_new_user_amount) today_first_new_user_amount
                FROM
                    ads_overall_summary_amount
                """;
    }

    /**
     * 新增用户折线数据sql
     */
    private String userLineSql() {
        return """
                SELECT
                    dt,
                    SUM(today_user_count) player_account
                FROM
                    ads_overall_summary_user
                """;
    }

}
