package org.jeecg.modules.demo.contract.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import io.swagger.v3.oas.annotations.tags.Tag;
import jakarta.annotation.PostConstruct;
import lombok.extern.slf4j.Slf4j;
import org.jeecg.config.firewall.SqlInjection.impl.RobinJdbcConfig;
import org.jeecg.modules.demo.contract.entity.Contract;
import org.jeecg.modules.demo.contract.entity.SemanticWord;
import org.jeecg.modules.demo.contract.service.IContractService;
import org.jeecg.modules.demo.contract.service.ISemanticWordService;
import org.jeecg.modules.message.handle.impl.EmailSendMsgHandle;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * JEECG-Boot 定时发送邮件任务
 */
@Tag(name="邮件")
@RestController
@RequestMapping("/email")
@Slf4j
public class EmailScheduledTask {

    @Autowired
    @Qualifier("robinJdbcTemplate") // 指定特定的JdbcTemplate
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private EmailSendMsgHandle emailSendMsgHandle;

    @Autowired
    private ISemanticWordService semanticWordService;

    @Autowired
    private IContractService contractService;

    @Scheduled(initialDelay = 6000, fixedRate = 1800000)
    public void sendTimedEmail() {

        QueryWrapper<SemanticWord> semanticWordQueryWrapper = new QueryWrapper<>();
        semanticWordQueryWrapper.in("status", new Object[]{4,5,6,7});
        List<SemanticWord> list = semanticWordService.list(semanticWordQueryWrapper);

        for (SemanticWord semanticWord : list) {
            Contract byId = contractService.getById(semanticWord.getContractId());
            if (byId == null) {
                return;
            } else {
                String contractId = byId.getContractCode();
                String sql = "select count(1) from d_event_list where contract_name = '" + contractId + "' and (keyword_name like concat('%','" + semanticWord.getWord() + "','%'))";
                Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(sql);
                if (stringObjectMap != null) {
                    if (stringObjectMap.get("count(1)") != null && !stringObjectMap.get("count(1)").toString().equals("0")) {
                        // 1. 定义预编译SQL模板（用?占位符代替变量）
                        String sqlV2 = "SELECT\n" +
                                "    IFNULL(t2.highlight_count / t1.total_count, 0) AS ratio,\n" +
                                "    t1.total_count,\n" +
                                "    t2.highlight_count\n" +
                                "FROM\n" +
                                "    (SELECT COUNT(*) AS total_count\n" +
                                "     FROM d_keyword_list kl\n" +
                                "     WHERE kl.is_active = 1\n" +
                                "       AND kl.sType = 'rank'\n" +
                                "       AND kl.ContractId = ?\n" +  // 占位符1：contractId
                                "       AND kl.sDomain <> 'HC_audit'\n" +
                                "       AND kl.sBookMarkName LIKE CONCAT('%', ?, '%')\n" +  // 占位符2：semanticWord
                                "       AND EXISTS (\n" +
                                "           SELECT 1\n" +
                                "           FROM d_event_list el\n" +
                                "           WHERE el.contract_name = ?\n" +  // 占位符3：contractId
                                "             AND el.keyword_name LIKE CONCAT('%', ?, '%')\n" +  // 占位符4：semanticWord
                                "       )) AS t1,\n" +
                                "    (" +
                                "SELECT COUNT(DISTINCT el.keyword_name) AS highlight_count\n" +
                                "FROM d_event_list el\n" +
                                "INNER JOIN d_keyword_list kl \n" +
                                "    ON el.keyword_name = kl.sBookMarkName\n" +
                                "WHERE el.contract_name = ?\n" +
                                "  AND el.keyword_name LIKE CONCAT('%', ? ,'%')\n" +
                                "  AND kl.is_active = 1\n" +
                                "  AND kl.ContractId = ?\n" +
                                "  AND kl.sDomain <> 'HC_audit'\n" +
                                "  AND (el.keyword_name, el.event_time) IN (\n" +
                                "    SELECT keyword_name, MAX(event_time)\n" +
                                "    FROM d_event_list\n" +
                                "    WHERE contract_name = ?\n" +
                                "      AND keyword_name LIKE CONCAT('%', ? ,'%')\n" +
                                "    GROUP BY keyword_name\n" +
                                "  )\n" +
                                "  AND el.type_info = 'appear') as t2";
                        Map<String, Object> semMap = jdbcTemplate.queryForMap(
                                sqlV2,
                                // 按占位符顺序传入参数（注意顺序和?一一对应）
                                contractId,
                                semanticWord.getWord(),
                                contractId,
                                semanticWord.getWord(),
                                contractId,
                                semanticWord.getWord(),
                                contractId,
                                contractId,
                                semanticWord.getWord()

                        );
                        String[] es_receiver = new String[]{"2156125618@qq.com"};
//                        String[] es_receiver = new String[]{"182004222@qq.com","lospace@163.com","2156125618@qq.com","coldspring@163.com"};
                        BigDecimal acceptindicator = new BigDecimal(semMap.get("ratio").toString());
                        if (semanticWord.getCount() == null || semanticWord.getCount().equals("")){
                            if (semanticWord.getAcceptindicator()/100 <= acceptindicator.doubleValue()){//上词
                                semanticWord.setCount(acceptindicator.toString());
                                String es_title = semanticWord.getOutWord() + "首次上词提醒邮件";
                                String es_content = "语义词：" + semanticWord.getWord() + "；漏出词：" + semanticWord.getOutWord() +
                                        "，排名要求：" + (semanticWord.getRanking() < 4 ? "小于大于" + semanticWord.getRanking() + "名。" : "保展现不保排名。") +
                                        "达标率要求：" + semanticWord.getAcceptindicator() +
                                        "%，当前达标率：" + acceptindicator.doubleValue() * 100 + "%";
                                for (String s_receiver : es_receiver) {
                                    emailSendMsgHandle.sendMsg(s_receiver ,es_title ,es_content);
                                }
                                semanticWord.setStatus("5");
                                semanticWordService.updateById(semanticWord);
                            }else {//掉词
                                semanticWord.setCount(acceptindicator.toString());
                                String es_title = semanticWord.getOutWord() + "掉词提醒邮件";
                                String es_content = "语义词：" + semanticWord.getWord() + "；漏出词：" + semanticWord.getOutWord() +
                                        "，排名要求：" + (semanticWord.getRanking() < 4 ? "小于等于" + semanticWord.getRanking() + "名。" : "保展现不保排名。") +
                                        "达标率要求：" + semanticWord.getAcceptindicator() +
                                        "%，当前达标率：" + acceptindicator.doubleValue() * 100 + "%";
                                for (String s_receiver : es_receiver) {
                                    emailSendMsgHandle.sendMsg(s_receiver ,es_title ,es_content);
                                }
                                semanticWord.setStatus("6");
                                semanticWordService.updateById(semanticWord);
                            }

                        }//掉词时上词了
                        else if (semanticWord.getStatus().equals("6") && semanticWord.getAcceptindicator() <= acceptindicator.doubleValue() * 100){
                            semanticWord.setCount(acceptindicator.toString());
                            String es_title = semanticWord.getOutWord() + "再次上词提醒邮件";
                            String es_content = "语义词：" + semanticWord.getWord() + "；漏出词：" + semanticWord.getOutWord() +
                                    "，排名要求：" + (semanticWord.getRanking() < 4 ? "小于等于" + semanticWord.getRanking() + "名。" : "保展现不保排名。") +
                                    "达标率要求：" + semanticWord.getAcceptindicator() +
                                    "%，当前达标率：" + acceptindicator.doubleValue() * 100 + "%";
                            for (String s_receiver : es_receiver) {
                                emailSendMsgHandle.sendMsg(s_receiver ,es_title ,es_content);
                            }
                            semanticWord.setStatus("5");
                            semanticWordService.updateById(semanticWord);
                        }//上词后掉词
                        else if (semanticWord.getStatus().equals("5") && semanticWord.getAcceptindicator() > acceptindicator.doubleValue() * 100){
                            semanticWord.setCount(acceptindicator.toString());
                            String es_title = semanticWord.getOutWord() + "掉词提醒邮件";
                            String es_content = "语义词：" + semanticWord.getWord() + "；漏出词：" + semanticWord.getOutWord() +
                                    "，排名要求：" + (semanticWord.getRanking() < 4 ? "小于等于" + semanticWord.getRanking() + "名。" : "保展现不保排名。") +
                                    "达标率要求：" + semanticWord.getAcceptindicator() + "%，当前达标率：" + acceptindicator.doubleValue() * 100 + "%";
                            for (String s_receiver : es_receiver) {
                                emailSendMsgHandle.sendMsg(s_receiver ,es_title ,es_content);
                            }
                            semanticWord.setStatus("6");
                            semanticWordService.updateById(semanticWord);
                        }

                    }
                }
            }
        }
        int i = 1;

//        String es_receiver = "2156125618@qq.com";
//        String es_title = "jeecg测试邮件";
//        String es_content = "测试内容";
//        emailSendMsgHandle.sendMsg(es_receiver ,es_title ,es_content );
    }
//
//    //                            String[] es_receiver = new String[]{"182004222@qq.com","lospace@163.com","2156125618@qq.com"};
//    String es_title = semMap.get("target_name") + (Integer.parseInt(semMap.get("iResultRank").toString()) <= semanticWord.getRanking()? (stringObjectMap.get("count(1)").toString().equals("1")?"首次上词提醒邮件" : "再次上词提醒邮件") : "掉词提醒邮件");
//    String es_content = "语义词：" + semMap.get("keyword_name").toString() + "；漏出词：" + semMap.get("target_name") + (Integer.parseInt(semMap.get("iResultRank").toString()) <= semanticWord.getRanking()? (stringObjectMap.get("count(1)").toString().equals("1")?"首次上词" : "再次上词") : "掉词了") +"，当前排名：" + semMap.get("iResultRank") + "。";
//                        for (String s_receiver : es_receiver) {
//        emailSendMsgHandle.sendMsg(s_receiver ,es_title ,es_content);
//    }
//
//                        semanticWord.setNowNo(semMap.get("iResultRank").toString());
//                        semanticWord.setStatus(Integer.parseInt(semMap.get("iResultRank").toString()) <= semanticWord.getRanking()?"5" : "6");
//
//                        semanticWordService.updateById(semanticWord);

}