package com.cpop.oam.web;

import com.alibaba.fastjson.JSONArray;
import com.cpop.api.cloudDb.core.dto.CloudStoreActiveDto;
import com.cpop.api.cloudDb.handler.CloudStoreHandler;
import com.cpop.common.utils.StringUtils;
import com.cpop.common.utils.bean.BeanUtils;
import com.cpop.core.utils.SpringUtils;
import com.cpop.jambox.business.entity.*;
import com.cpop.jambox.business.service.*;
import com.cpop.oam.business.entity.Clue;
import com.cpop.oam.business.entity.Task;
import com.cpop.oam.business.entity.TaskWorkOrder;
import com.cpop.oam.business.mapper.TaskWorkOrderMapper;
import com.cpop.oam.business.service.ClueService;
import com.cpop.oam.business.service.TaskService;
import com.cpop.oam.business.service.TaskWorkOrderService;
import com.cpop.system.business.entity.Store;
import com.cpop.system.business.entity.StoreSign;
import com.cpop.system.business.entity.WxPayScore;
import com.cpop.system.business.mapper.StoreMapper;
import com.cpop.system.business.service.StoreService;
import com.cpop.system.business.service.StoreSignService;
import com.cpop.system.business.service.WxPayScoreService;
import com.mybatisflex.core.datasource.DataSourceKey;
import com.mybatisflex.core.query.QueryWrapper;
import com.mybatisflex.core.row.Db;
import com.mybatisflex.core.row.DbChain;
import com.mybatisflex.core.row.Row;
import com.mybatisflex.core.row.RowUtil;
import com.mybatisflex.core.update.UpdateChain;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.transaction.annotation.Transactional;

import java.time.Duration;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import static com.cpop.jambox.business.entity.table.StoreExtendTableDef.STORE_EXTEND;
import static com.cpop.oam.business.entity.table.TaskTableDef.TASK;
import static com.cpop.oam.business.entity.table.TaskWorkOrderTableDef.TASK_WORK_ORDER;
import static com.cpop.system.business.entity.table.StoreSignTableDef.STORE_SIGN;
import static com.cpop.system.business.entity.table.StoreTableDef.STORE;
import static com.mybatisflex.core.query.QueryMethods.count;
import static com.mybatisflex.core.query.QueryMethods.max;

/**
 * @author DB
 * @version 1.0.0
 * @since 2024-01-16 17:17
 */
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@ActiveProfiles(profiles = {"prod", "core", "pay"})
public class CpopDataSyncTests {

    /**
     * 同步校区缺失数据
     * @author DB
     * @since 2024/1/16
     */
    @Test
    public void syncStoreInfo(){
        List<Row> rowList;
        try {
            DataSourceKey.use("jambox");
            rowList = DbChain.table("t_mechanism_info")
                    .select("tmi.store_id as storeCloudId")
                    .from("t_mechanism_info").as("tmi")
                    .leftJoin("t_signContract_mechanism").as("tsm").on("tsm.store_id = tmi.store_id")
                    .where("tmi.deleted = 1")
                    .and("DATEDIFF(\n" +
                            "\tDATE_FORMAT( tsm.end_time, '%Y-%m-%d' ),\n" +
                            "\tDATE_FORMAT( tmi.creation_time, '%Y-%m-%d' ))>= 300")
                    .list();
        } finally {
            DataSourceKey.clear();
        }
        //获取所有校区
        Map<String, String> storeMap = SpringUtils.getBean(StoreExtendService.class).list().stream().collect(Collectors.toMap(StoreExtend::getStoreCloudId, StoreExtend::getStoreId));
        rowList.forEach(item -> {
            item.set("id", storeMap.get(item.getString("storeCloudId")));
            item.set("have_active", true);
        });
        List<Store> storeList = RowUtil.toEntityList(rowList, Store.class).stream().filter(item -> StringUtils.isNotBlank(item.getId())).collect(Collectors.toList());
        SpringUtils.getBean(StoreService.class).updateBatch(storeList);
    }

    /**
     * 同步课卡模板
     * @author DB
     * @since 2024/1/17
     */
    @Test
    public void syncCardTemplate(){
        List<Row> rowList;
        try {
            DataSourceKey.use("jambox");
            rowList = DbChain.table("t_card_template")
                    .select("tct.template_id as oldTemplateId", "tct.brand_id as brandCloudId", "tct.store_id as storeCloudId", "tct.open_status as status", "tct.name", "tct.day as validDay",
                            "tct.end as endDate", "tct.price", "tct.week_appointment", "tct.day_appointment", "tct.free_day as bufferDay", "tct.number as classNumber",
                            "tct.type as scopeUse", "tct.time_limit", "tct.is_suda", "tct.pay_type", "tct.template_qr_code as qrCode", "tct.is_member", "tct.stage_num", "tct.deposit", "tct.buffer_days",
                            "tct.is_drainage", "tct.is_ecpp_activity", "tct.ecpp_activity_code", "tct.ecpp_activity_desc")
                    .select("(SELECT jwpp.plan_id from j_wechat_pay_plan as jwpp WHERE jwpp.template_id = tct.template_id AND jwpp.is_month = 0 AND jwpp.deleted = 1 LIMIT 1 ) as oncePlanId")
                    .select("(SELECT jwpp.plan_id from j_wechat_pay_plan as jwpp WHERE jwpp.template_id = tct.template_id AND jwpp.is_month = 1 AND jwpp.deleted = 1 LIMIT 1 ) as monthPlanId")
                    .select("creation_time as createTime", "last_modification_date as updateTime")
                    .from("t_card_template").as("tct")
                    .where("tct.deleted = 1")
                    .list();
        } finally {
            DataSourceKey.clear();
        }
        //获取所有计划
        Map<String, WxPayScore> wxPayScoreMap = SpringUtils.getBean(WxPayScoreService.class).list().stream().collect(Collectors.toMap(WxPayScore::getOutPlanId, item -> item));
        Map<String, String> brandMap = SpringUtils.getBean(BrandExtendService.class).list().stream().collect(Collectors.toMap(BrandExtend::getBrandCloudId, BrandExtend::getBrandId));
        Map<String, String> storeMap = SpringUtils.getBean(StoreExtendService.class).list().stream().collect(Collectors.toMap(StoreExtend::getStoreCloudId, StoreExtend::getStoreId));
        List<CardTemplateExtend> cardTemplateExtends = new ArrayList<>();
        rowList.forEach(item -> {
            //模板类型
            item.set("templateType", changeTimeLimitToTemplateType(item.getString("timeLimit")));
            if (item.getBoolean("isSuda")){
                CardTemplateExtend cardTemplateExtend = new CardTemplateExtend();
                cardTemplateExtend.setTemplateId(item.getString("oldTemplateId"))
                        .setPayType(3)
                        .setExtendParamOne(item.getString("isEcppActivity"))
                        .setExtendParamTwo(item.getString("ecppActivityCode"))
                        .setExtendParamThree(item.getString("ecppActivityDesc"));
                cardTemplateExtends.add(cardTemplateExtend);
            }
            if (StringUtils.isNotBlank(item.getString("brandCloudId"))){
                item.set("brandId",brandMap.get(item.getString("brandCloudId")));
            }
            if (StringUtils.isNotBlank(item.getString("storeCloudId"))){
                item.set("storeId",storeMap.get(item.getString("storeCloudId")));
            }
            if (StringUtils.isNotBlank(item.getString("payType"))){
                List<String> payTypeList = Arrays.stream(item.getString("payType").split(",")).collect(Collectors.toList());
                payTypeList.forEach(inner -> {
                    changePayType(inner, cardTemplateExtends, item, wxPayScoreMap);
                });
            } else {
                CardTemplateExtend cardTemplateExtend = new CardTemplateExtend();
                cardTemplateExtend.setTemplateId(item.getString("oldTemplateId"))
                        .setPayType(4);
                cardTemplateExtends.add(cardTemplateExtend);
            }
        });
        List<CardTemplate> entityList = RowUtil.toEntityList(rowList, CardTemplate.class);
        SpringUtils.getBean(CardTemplateService.class).saveBatch(entityList);
        Map<String, String> collect = entityList.stream().collect(Collectors.toMap(CardTemplate::getOldTemplateId, CardTemplate::getId));
        LocalDateTime now = LocalDateTime.now();
        cardTemplateExtends.forEach(item->{
            item.setTemplateId(collect.get(item.getTemplateId()));
            item.setCreateTime(now);
            item.setUpdateTime(now);
        });
        SpringUtils.getBean(CardTemplateExtendService.class).saveBatch(cardTemplateExtends);
    }

    private Integer changeTimeLimitToTemplateType(String timeLimit) {
        switch (timeLimit) {
            case "课时卡":
                return 0;
            case "时限卡":
                return 1;
            default:
                return 2;
        }
    }

    private void changePayType(String payType,List<CardTemplateExtend> cardTemplateExtends,Row row,Map<String, WxPayScore> wxPayScoreMap) {
        CardTemplateExtend cardTemplateExtend = new CardTemplateExtend();
        switch (payType) {
            //先学后付次付
            case "1":
                if (StringUtils.isNotBlank(row.getString("oncePlanId"))){
                    WxPayScore oncePlan = wxPayScoreMap.get(row.getString("oncePlanId"));
                    if (oncePlan != null){
                        cardTemplateExtend.setTemplateId(row.getString("oldTemplateId"))
                                .setExtendId(oncePlan.getId())
                                .setPayType(1);
                        cardTemplateExtends.add(cardTemplateExtend);
                    }
                }
                break;
            //旧放心学合约
            case "2":
                cardTemplateExtend.setTemplateId(row.getString("oldTemplateId"))
                        .setPayType(2)
                        .setExtendParamOne(row.getString("stageNum"))
                        .setExtendParamTwo(row.getString("deposit"))
                        .setExtendParamThree(row.getString("bufferDays"));
                cardTemplateExtends.add(cardTemplateExtend);
                break;
            //先学后付月付
            case "5":
                if (StringUtils.isNotBlank(row.getString("monthPlanId"))){
                    WxPayScore monthPlan = wxPayScoreMap.get(row.getString("monthPlanId"));
                    if (monthPlan != null){
                        cardTemplateExtend.setTemplateId(row.getString("oldTemplateId"))
                                .setExtendId(monthPlan.getId())
                                .setPayType(5);
                        cardTemplateExtends.add(cardTemplateExtend);
                    }
                }
                break;
            default:
                break;
        }
    }

    /**
     * 同步校区签约
     * @author DB
     * @since 2024/1/18
     */
    @Test
    public void syncOldStoreSignData() {
        /*List<Row> rowList;
        try {
            DataSourceKey.use("jambox");
            rowList = DbChain.table("OAM_data_sign")
                    .select("ods.sign_status", "tmi.mechanism as storeName", "ods.store_id as storeCloudId", "tbi.brand_id as brandCloudId", "ods.sign_time as signCreateTime", "ods.end_time as expireDate", "ods.activation_time as renewDate",
                            "ods.sign_name as signStaffId", "ods.cre_time as createDate", "ods.address as storeAddr", "ods.charge_name as personCharge","tmi.charge_phone as phone", "tmi.X as longitude", "tmi.Y as latitude", "tmi.test as isTest",
                            "tmi.reg_addr as licenseAddr", "str_to_date(tmi.reg_date,'%Y年%m月%d') as licenseDate", "tmi.reg_Name as licenseName",
                            "tmi.ness_license_id as licenseCode", "tmi.corp_name as licenseUserName", "tmi.reg_pic as licensePicUrl")
                    .from("OAM_data_sign").as("ods")
                    .leftJoin("t_mechanism_info").as("tmi").on("tmi.mechanism_id = ods.mechanism_id")
                    .leftJoin("t_brand_info").as("tbi").on("tbi.id = ods.brand_id")
                    .and("ods.store_id is not null")
                    .list();
        } finally {
            DataSourceKey.clear();
        }
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        //获取所有校区
        Map<String, String> storeIdMap = Db.selectListByQuery(STORE.getTableName(), QueryWrapper.create().select(STORE.ID, STORE_EXTEND.STORE_CLOUD_ID)
                        .leftJoin(STORE_EXTEND).on(STORE_EXTEND.STORE_ID.eq(STORE.ID)))
                .stream().collect(Collectors.toMap(item -> item.getString("storeCloudId"), item -> item.getString("id")));
        //过滤出已签约的数据
        List<Row> waitSignStore = rowList.stream().filter(item -> !StringUtils.equals(item.getString("signStatus"), "待签约")).collect(Collectors.toList());
        List<StoreSign> storeSigns = new ArrayList<>();
        waitSignStore.forEach(item -> {
            StoreSign storeSign = new StoreSign();
            storeSign.setSignStaffId(item.getString("signStaffId"));
            storeSign.setStoreId(storeIdMap.get(item.getString("storeCloudId")));
            storeSign.setExpireDate(item.getDate("expireDate").toInstant().atZone(ZoneId.systemDefault()).toLocalDate());
            if (item.getString("signCreateTime") != null){
                if (item.getString("signCreateTime").length() < 11) {
                    storeSign.setCreateTime(LocalDateTime.parse(item.getString("signCreateTime") + " 00:00:00", dateTimeFormatter));
                } else {
                    storeSign.setCreateTime(LocalDateTime.parse(item.getString("signCreateTime"), dateTimeFormatter));
                }
            }
            storeSigns.add(storeSign);
        });*/

        SpringUtils.getBean(StoreService.class).updateChain()
                .set(STORE.SIGN_ID, null)
                .update();
        List<StoreSign> list = SpringUtils.getBean(StoreSignService.class).list();
        Db.executeBatch(list.stream().filter(item->StringUtils.isNotBlank(item.getStoreId())).collect(Collectors.toList()), StoreMapper.class, (mapper, data) -> {
            UpdateChain.of(mapper).set(STORE.SIGN_ID, data.getId())
                    .where(STORE.ID.eq(data.getStoreId()))
                    .update();
        });

    }

    @Test
    public void removeExtendSignData(){
        StoreSignService storeSignService = SpringUtils.getBean(StoreSignService.class);
        List<StoreSign> list = storeSignService.queryChain()
                .select(max(STORE_SIGN.ID).as(StoreSign::getId))
                .groupBy(STORE_SIGN.STORE_ID)
                .having(count(STORE_SIGN.STORE_ID).gt(1)).list();
        storeSignService.removeByIds(list.stream().map(StoreSign::getId).collect(Collectors.toList()));
    }

    @Test
    public void checkSpecialCharacter(){
        String input = "排课000\uD83C\uDF3F\uD83C\uDF8B\uD83C\uDF15\uD83C\uDF44\uD83D\uDC1A";
        //特殊字符过滤
        String pattern = "[^a-zA-Z0-9]";
        System.out.println(input.replaceAll(pattern, ""));
    }

    /**
     * 同步校区签约数据
     */
    @Test
    public void syncStoreSignData(){
        List<StoreSign> list = SpringUtils.getBean(StoreSignService.class).list();
        List<Store> stores = new ArrayList<>();
        list.forEach(item->{
            Store store = new Store();
            store.setId(item.getStoreId())
                    .setSignId(item.getId());
            stores.add(store);
        });
        SpringUtils.getBean(StoreService.class).updateBatch(stores);
    }

    /**
     * 删除没有云校区的数据
     * @author DB
     * @since 2024/1/23
     */
    @Test
    public void removeNotHaveCloudStore() {
        StoreService storeService = SpringUtils.getBean(StoreService.class);
        List<Store> storeList = storeService.queryChain()
                .select(STORE.ID)
                .leftJoin(STORE_EXTEND).on(STORE_EXTEND.STORE_ID.eq(STORE.ID))
                .where(STORE_EXTEND.STORE_CLOUD_ID.isNull())
                .list();
        List<String> collect = storeList.stream().map(Store::getId).collect(Collectors.toList());
        storeService.removeByIds(collect);
    }

    @Test
    public void syncTaskOrderReceivingTime(){
        List<Task> list = SpringUtils.getBean(TaskService.class).queryChain()
                .leftJoin(TASK_WORK_ORDER).on(TASK_WORK_ORDER.TASK_ID.eq(TASK.ID))
                .where(TASK.TASK_TYPE.eq(2))
                .where(TASK_WORK_ORDER.CREATE_TIME.ge(LocalDateTime.of(2024, 1, 11, 0, 0, 0)))
                .list();
        List<TaskWorkOrder> taskWorkOrders = new ArrayList<>();
        list.forEach(item->{
            if (item.getTaskReceiptTime() != null){
                TaskWorkOrder taskWorkOrder = new TaskWorkOrder();
                Long minutes = Duration.between(item.getCreateTime(), item.getTaskReceiptTime()).toMinutes();
                taskWorkOrder.setTaskId(item.getId())
                        .setReceivingTime(minutes.intValue());
                taskWorkOrders.add(taskWorkOrder);
            }
        });
        Db.executeBatch(taskWorkOrders, TaskWorkOrderMapper.class, (mapper, data) -> {
            UpdateChain.of(mapper)
                    .set(TASK_WORK_ORDER.RECEIVING_TIME, data.getReceivingTime())
                    .where(TASK_WORK_ORDER.TASK_ID.eq(data.getTaskId()))
                    .update();
        });
    }

    @Test
    public void syncClueData(){
        //查询已有线索
        ClueService clueService = SpringUtils.getBean(ClueService.class);
        List<Clue> clueList = clueService.list();
        //查询校区
        List<Store> storeList = SpringUtils.getBean(StoreService.class).list(QueryWrapper.create()
                .where(STORE.ID.notIn(clueList.stream().map(Clue::getStoreId).collect(Collectors.toList()))));
        //设置校区信息
        List<StoreExtend> storeExtends = SpringUtils.getBean(StoreExtendService.class).list(
                QueryWrapper.create().where(STORE_EXTEND.STORE_ID.in(storeList.stream().map(Store::getId).collect(Collectors.toList()))));
        //查询旧库信息
        List<Row> rowList;
        try {
            DataSourceKey.use("jambox");
            rowList = DbChain.table("t_mechanism_info")
                    .select("city","store_id as storeCloudId")
                    .from("t_mechanism_info")
                    .in("store_id",storeExtends.stream().map(StoreExtend::getStoreCloudId).collect(Collectors.toSet()))
                    .list();
        } finally {
            DataSourceKey.clear();
        }
        Map<String, String> collect = storeExtends.stream().collect(Collectors.toMap(StoreExtend::getStoreCloudId, StoreExtend::getStoreId));
        ArrayList<Clue> clues = new ArrayList<>();
        rowList.forEach(item->{
            String storeId = collect.get(item.getString("storeCloudId"));
            if (StringUtils.isNotBlank(storeId)) {
                Clue clue = new Clue();
                clue.setCity(item.getString("city"))
                        .setStoreId(storeId);
                clues.add(clue);
            }
        });
        SpringUtils.getBean(ClueService.class).saveBatch(clues);
    }

    /**
     * 导入校区活跃数
     * @author DB
     * @since 2024/4/12
     */
    @Test
    @Transactional(rollbackFor = Exception.class)
    public void importStoreActiveData(){
        LocalDate localDate = LocalDate.of(2024, 4, 4);
        for (int i = 0; i < 8; i++) {
            LocalDate date = localDate.plusDays(i);
            List<CloudStoreActiveDto> storeActiveData = SpringUtils.getBean(CloudStoreHandler.class).getStoreActiveData(date.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
            List<StoreActive> storeActives = BeanUtils.mapToList(storeActiveData, StoreActive.class);
            if (!storeActives.isEmpty()) {
                storeActives.forEach(item -> {
                    item.setActiveDate(date);
                });
                SpringUtils.getBean(StoreActiveService.class).saveBatch(storeActives);
            }
        }
    }
}
