package com.bcxin.backend.domain.signature.service.impls;

import cn.hutool.core.date.DateUtil;
import cn.hutool.http.HttpRequest;
import cn.hutool.http.HttpResponse;
import cn.hutool.http.HttpUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bcxin.backend.core.utils.ExceptionUtils;
import com.bcxin.backend.domain.models.SignatureQueuesDTO;
import com.bcxin.backend.domain.repositories.SignatureQueuesRepository;
import com.bcxin.backend.domain.services.impls.ConvertServiceImpl;
import com.bcxin.backend.domain.signature.service.QzSignatureStrategy;
import com.bcxin.backend.domain.signature.service.impls.vos.CertificateVo;
import com.bcxin.backend.domain.syncs.dtos.SharedConvertDto;
import io.jsonwebtoken.lang.Collections;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.Instant;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Service
public class BeiJingInSignatureToPDFCompare implements QzSignatureStrategy {
    private static final Logger logger  = LoggerFactory.getLogger(BeiJingInSignatureToPDFCompare.class);

    private final SignatureQueuesRepository signatureQueuesRepository;
    @Value("${myapps.domain.url}")
    String urlHead;
    @Value("${myapps.storage.root}")
    String rootPath;
    @Value("${myapps.signature.tempPDF}")
    String tempPDF;
    @Value("${myapps.signature.mobanPDF}")
    String mobanPDF;
    @Value("${myapps.signature.cgiApi}")
    String cgiApi;
    @Autowired
    private ConvertServiceImpl convertService;

    @Autowired
    @Qualifier("primaryJdbcTemplate")
    protected JdbcTemplate jdbcTemplate;

    public BeiJingInSignatureToPDFCompare(SignatureQueuesRepository signatureQueuesRepository) {
        this.signatureQueuesRepository = signatureQueuesRepository;
    }

    /**
     * 该证书里面的数据为已核验且实名认证过的数据
     *
     * todo:
     *  当用户的认证通过之后, 需要将人员的基本信息覆盖到证书信息表同时修改签章状态, 以触发重新认证
     */
    @Override
    public void cerSignatureQueues() {
        String sql =
                "select id,item_securityname,item_documentid,item_idaddress,item_certificateno,item_certificatefrom," +
                        " item_certificatedate,item_headphoto,created,item_signatured_processed_status " +
                        " from tlk_certificate where ITEM_signatured_processed_status IN (0) and " +
                        " ITEM_CERTIFICATETYPE='1' and ITEM_isCertified != '0' " +
                        " order by item_certificateDate desc, ITEM_signatured_time desc limit 300";
        List<CertificateVo> list = jdbcTemplate.query(sql, new RowMapper<CertificateVo>() {
            @Override
            public CertificateVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                return CertificateVo.create(
                        rs.getString("id"),
                        rs.getString("item_securityname"),
                        rs.getString("item_documentid"),
                        rs.getString("item_idaddress"),
                        rs.getString("item_certificateno"),
                        rs.getString("item_certificatefrom"),
                        rs.getString("item_certificatedate"),
                        rs.getString("item_headphoto"),
                        rs.getString("created"),
                        rs.getString("item_signatured_processed_status")
                );
            }
        });

        logger.error("获取到待签章的资格证数量:{}",list.size());
        if (!CollectionUtils.isEmpty(list)) {
            for (CertificateVo certificateVo : list) {
                String securityname = certificateVo.getItem_securityname();
                String documentid = certificateVo.getItem_documentid();
                String IdAddress = certificateVo.getItem_idaddress();
                String certificateno = certificateVo.getItem_certificateno();
                String certificatefrom = certificateVo.getItem_certificatefrom();

                //因为都是实名认证过了, 因此，无需检测身份证格式的有效性
                if (certificateVo.doCheck(urlHead)) {
                    String headphoto = certificateVo.getHeadPhoto();
                    String year = String.valueOf(certificateVo.getYearFromIdCard());
                    String month = String.valueOf(certificateVo.getMonthFromIdCard());
                    String day = String.valueOf(certificateVo.getDayFromIdCard());
                    String certificatedate = certificateVo.getCertificatedate();
                    String json = "{\"name\":\"" + securityname + "\",\"address\":\"" + IdAddress + "\",\"headphoto\":\"" + headphoto + "\",\"idcardno\":\"" + documentid + "\",\"year\":\"" + year + "\",\"month\":\"" + month + "\",\"day\":\"" + day + "\",\"certificateno\":\"" + certificateno + "\",\"certificatefrom\":\"" + certificatefrom + "\",\"isSignature\":\"1\",\"fzDate\":\"" + certificatedate + "\"}";
                    certificateVo.assignProcessJson(json);
                }
            }

            String moban = mobanPDF;
            for (CertificateVo.ProcessStatus status : CertificateVo.ProcessStatus.values()) {
                Collection<CertificateVo>
                        selectedCertificateVos
                        = list.stream().filter(ii -> ii.getProcessStatus() == status)
                        .collect(Collectors.toList());
                String executeSql = null;
                StringBuilder trace = new StringBuilder();
                trace.append(String.format("执行签章的status=%s;size=%s", status.getText(), selectedCertificateVos.size()));
                try {
                    if (!CollectionUtils.isEmpty(selectedCertificateVos)) {
                        executeSql = status.getSql();
                        String idParams =
                                selectedCertificateVos.stream()
                                        .map(ii -> String.format("'%s'", ii.getId()))
                                        .collect(Collectors.joining(","));

                        executeSql = String.format("%s where id in (%s)", executeSql, idParams);

                        trace.append(String.format("status=%s;sql=%s;", status, executeSql));
                        if (status == CertificateVo.ProcessStatus.SUCCESS) {
                            String insertSql = status.getInsertSql();
                            List<Object[]> params = new ArrayList<>();
                            //INSERT INTO obpm2_security.signature_queues (version,template_url,DATA,STATUS,business_id,createdTime,creator,certificatedate)V
                            for (CertificateVo vi : selectedCertificateVos) {
                                Object[] itemPs = new Object[]{
                                        "2.0",
                                        moban,
                                        vi.getProcessJson(),
                                        0,
                                        vi.getId(),
                                        Timestamp.from(Instant.now()),
                                        "",
                                        vi.getCertificatedate()
                                };
                                params.add(itemPs);
                            }

                            trace.append(String.format("insertSql=%s;", insertSql));
                            /**
                             * 针对数据是有效的情况, 插入数据库
                             */
                            this.jdbcTemplate.batchUpdate(insertSql, params);
                        }

                        /**
                         * 更改状态; 包括: 数据都是有效的情况
                         */
                        this.jdbcTemplate.batchUpdate(executeSql);
                    }
                } catch (Exception ex) {
                    logger.error("执行SQL({})发生异常", executeSql, ex);
                } finally {
                    logger.error("跟踪资格证执行情况:{}", trace);
                }
            }
        }
    }

    @Override
    public void cerPDF() {
        //后执行要创建签章中业务的逻辑
        createPDF();
    }

    /**
     * <b> 激活电子印章签验接口服务器完成签章 </b>
     * @author ZXF
     * @create 2022/11/21 0021 10:44
     * @version
     * @注意事项 </b>
     */
    private boolean activateXMPSignQuicklyExCGI() {
        StringBuilder sb = new StringBuilder();
        try {
            JSONObject json = new JSONObject();
            json.put("service", "wss://127.0.0.1:8800");
            json.put("sn", "2171101001009256");
            json.put("pin", "88888888");
            json.put("in", "/data/share/in");
            json.put("out", "/data/share/out");
            JSONArray arr = new JSONArray();
            JSONObject pos = new JSONObject();
            pos.put("centerX", 405);
            pos.put("centerY", 312);
            pos.put("page", 1);
            arr.add(pos);
            json.put("signOnPos", arr);
            String ret = HttpUtil.post(cgiApi, json.toJSONString());
            log.info("====> 电子签章定时任务开始.activateXMPSignQuicklyExCGI.ret：" + ret);
            if (StringUtils.isEmpty(ret)) {
                return false;
            }
            JSONObject result = JSON.parseObject(ret.replace("#", ""));
            if ("1".equals(String.valueOf(result.get("code")))) {
                return true;
            }
            return false;
        } catch (Exception e){
            log.info("====> 电子签章定时任务开始.activateXMPSignQuicklyExCGI message:"+e.getMessage());
        }
        return false;
    }

    /**
     * <b> 批量生成pdf文件 </b>
     * @author ZXF
     * @create 2022/10/31 0031 13:33
     * @version
     * @注意事项 </b>
     */
    private void createPDF() {
        //1.查询签章队列表需要签章的数据
        Page<SignatureQueuesDTO> pageQueues = signatureQueuesRepository.pageQueues(0, PageRequest.of(0, 100));
        List<SignatureQueuesDTO> queues = pageQueues.getContent();
        if (Collections.isEmpty(queues)) {
            log.info("====> 电子签章定时任务开始.createPDF.queues.无业务数据 ---------------------");
            return;
        }
        log.info("====> 电子签章定时任务开始.createPDF.queues.size()="+queues.size());
        for (SignatureQueuesDTO queuesDTO:queues) {
            accept(queuesDTO);
        }
    }

    private void accept(SignatureQueuesDTO queue) {
        try {
            log.info("====> 电子签章定时任务开始.createPDF.accept.单次业务开始："+queue.getId());
            Long a = System.currentTimeMillis();
            if(queue.getData().contains("b.photoUrl")||queue.getData().contains("uid")){
                signatureQueuesRepository.deleteById(queue.getId());
                String updateSql = "UPDATE tlk_certificate SET ITEM_SIGNATURED_PROCESSED_STATUS=\"3\",ITEM_LAST_SIGNATURED_PROCESSED_TIME=NOW(),ITEM_LAST_SIGNATURED_PROCESSED_RESULT=\"签章失败：证书信息不全（身份证头像）\" WHERE ID=\"" + queue.getBusiness_id() + "\";";
                jdbcTemplate.update(updateSql);
                log.info("====> 电子签章定时任务开始.createPDF.accept.单次业务头像信息不可用，end");
                return;
            }
            SignatureData data = JSONObject.parseObject(queue.getData(),SignatureData.class);
            if(StringUtils.isEmpty(data.getAddress()) || data.getAddress().contains(" ")){
                signatureQueuesRepository.deleteById(queue.getId());
                String updateSql = "UPDATE tlk_certificate SET ITEM_SIGNATURED_PROCESSED_STATUS=\"3\",ITEM_LAST_SIGNATURED_PROCESSED_TIME=NOW(),ITEM_LAST_SIGNATURED_PROCESSED_RESULT=\"签章失败：证书信息不全（身份证住址）\" WHERE ID=\"" + queue.getBusiness_id() + "\";";
                jdbcTemplate.update(updateSql);
                log.info("====> 电子签章定时任务开始.createPDF.accept.单次业务地址信息不可用，end");
                return;
            }
            log.info("====> 电子签章定时任务开始.createPDF.accept.单次业务：001："+queue.getData());
            //先将文件命名为.temp
            data.setUnSignaturePDF(tempPDF+"/"+System.currentTimeMillis()+"queueId_"+queue.getId()+".temp");
            String toDay = data.getFzDate();
            if(StringUtils.isEmpty(toDay)){
                toDay = DateUtil.today();
            }
            data.setZYear(toDay.split("-")[0]);
            data.setZMonth(toDay.split("-")[1]);
            data.setZDay(toDay.split("-")[2]);
            data.setHeadphoto(data.getHeadphoto().startsWith("http")?data.getHeadphoto():urlHead+data.getHeadphoto());
            log.info("====> 电子签章定时任务开始.createPDF.accept.单次业务：002");

            SharedConvertDto sharedConvertDto = new SharedConvertDto();
            sharedConvertDto.setTempUrl(queue.getTemplate_url());
            sharedConvertDto.setFormData(data);
            if(!doesFileExist(data.getHeadphoto())){
                log.info("====> 电子签章定时任务开始.createPDF.accept.Headphoto：002：fail：无头像信息 end");
                //失败
                signatureQueuesRepository.updateSignature(2,new Date(),"PDF生成失败：无头像信息",queue.getId());
                String updateSql = "UPDATE tlk_certificate SET ITEM_SIGNATURED_PROCESSED_STATUS=\"3\",ITEM_LAST_SIGNATURED_PROCESSED_TIME=NOW(),ITEM_LAST_SIGNATURED_PROCESSED_RESULT=\"签章失败：无头像信息\" WHERE ID=\"" + queue.getBusiness_id() + "\";";
                jdbcTemplate.update(updateSql);
                log.info("====> 电子签章定时任务开始.createPDF.accept.单次业务：002：fail：无头像信息 end");
                return;
            }
            //最终输出为.pdf文件，主要是为了避免pdf 0kb时就被剪切走
            String result = convertService.execConvertHtmltopdfForTemp(sharedConvertDto);
            log.info("====> 电子签章定时任务开始.createPDF.accept.单次业务：003:"+result);

            if(StringUtils.isNotEmpty(result)){
                //成功
                signatureQueuesRepository.updateSignature(3,new Date(),"PDF已生成待签章",queue.getId());
                log.info("====> 电子签章定时任务开始.createPDF.accept.单次业务：004:success");
            }else{
                //失败
                signatureQueuesRepository.updateSignature(2,new Date(),"PDF生成失败",queue.getId());
                log.info("====> 电子签章定时任务开始.createPDF.accept.单次业务：004:fail");
            }
            log.info("====> 电子签章定时任务开始.createPDF.accept.单次生成文件耗时："+(System.currentTimeMillis()-a));
        } catch (Exception e) {
            log.error("====> 电子签章定时任务开始.createPDF.accept.pdf生成异常开始："+e.getMessage());
            //数据异常，删除签章记录，同时更新业务表的状态
            signatureQueuesRepository.deleteById(queue.getId());
            log.error("====> 电子签章定时任务开始.createPDF.accept.pdf生成异常删除signature_queues："+queue.getId());
            String updateSql = "UPDATE tlk_certificate SET ITEM_SIGNATURED_PROCESSED_STATUS=\"3\",ITEM_LAST_SIGNATURED_PROCESSED_TIME=NOW(),ITEM_LAST_SIGNATURED_PROCESSED_RESULT=\"签章失败："+e.getMessage()+"\" WHERE ID=\"" + queue.getBusiness_id() + "\";";

            log.error("====> 电子签章定时任务开始.createPDF.accept.pdf生成异常删除tlk_certificate："+queue.getBusiness_id());
            jdbcTemplate.update(updateSql);
            log.error("====> 电子签章定时任务开始.createPDF.accept.pdf生成异常："+e.getMessage());
        }
    }

    public boolean doesFileExist(String urlString) {
        log.error("====> 电子签章定时任务开始.createPDF.accept.doesFileExist.urlString2："+urlString);
        HttpResponse response = HttpRequest.get(urlString).execute();
        if (response.getStatus() == 200) {
            return true;
        }
        return false;
    }

    public void delInPDF(){
        SharedConvertDto sharedConvertDto = new SharedConvertDto();
        sharedConvertDto.setTempUrl(tempPDF);
        convertService.delInPDF(sharedConvertDto);
    }

    @Override
    public void signQuicklyExCGI() {
        Long a = System.currentTimeMillis();
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("tempUrl", tempPDF);
        jsonObject.put("formData", 230);
        try {
            //到文件夹剪切一个文件到out目录
            SharedConvertDto sharedConvertDto = new SharedConvertDto();
            String tempUrl = jsonObject.getString("tempUrl");
            String formData = jsonObject.getString("formData");
            sharedConvertDto.setTempUrl(tempUrl);
            sharedConvertDto.setFormData(formData);
            new Thread(()->{
                try {
                    //先检索in目录存在的删除
                    convertService.delInPDF(sharedConvertDto);
                } catch(Exception e) {
                    log.error("====> 电子签章定时任务开始.signQuicklyExCGI签章前比对in文件删除："+ e.getMessage());
                }
            }).start();
            //剪切文件到in目录下
            convertService.existsPdfCopy(sharedConvertDto);
            //每次盖章前先清除0kb文件
            convertService.delInPDF(tempPDF);
            //先触发对需要签章目录下文件进行签章
            activateXMPSignQuicklyExCGI();
            new Thread(()->{
                try {
                    //先检索in目录存在的删除
                    convertService.delInPDF(sharedConvertDto);
                } catch(Exception e) {
                    log.error("====> 电子签章定时任务开始.signQuicklyExCGI签章后比对in文件删除："+ e.getMessage());
                }
            }).start();
            log.info("====> 电子签章定时任务开始.signQuicklyExCGI.签章接口耗时："+(System.currentTimeMillis()-a));
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void changeStatus() {
        //对长时间滞留在已生成pdf待签章状态的进行重置（status = 0）
        String updateSql = "update obpm2_security.signature_queues set status = '0' where status = '3' and TIMESTAMPDIFF(HOUR, last_processed_time, NOW()) > 3";
        jdbcTemplate.update(updateSql);
        //对上一个小时因无头像问题导致签章失败的记录签章状态进行重置（status = 0）
        signatureCompensate();
    }

    /**
     * <b> 对前一小时出现头像异常的签章记录做状态重置 </b>
     * @author ZXF
     * @create 2025/06/17 0017 15:10
     * @version
     * @注意事项 </b>
     */
    private void signatureCompensate() {
        Date now = new Date();
        Date oneHourAgo = DateUtil.offsetMinute(now, -30);
        Date twoHourAgo = DateUtil.offsetMinute(now, -150);
        signatureQueuesRepository.updateLastHousePhoto(oneHourAgo,twoHourAgo);
    }

    public void scan() {
        try {
            Long a = System.currentTimeMillis();
            String pdfFile = "/uploads/pdf/dianziqianzhang/out";
            SharedConvertDto sharedConvertDto = new SharedConvertDto();
            sharedConvertDto.setTempUrl(pdfFile);
            sharedConvertDto.setFormData(null);
            String result = convertService.existsScanSignature(sharedConvertDto);
            if (StringUtils.isEmpty(result)) {
                log.info("====> 电子签章定时任务开始.scan.result：空");
                return;
            }
            log.info("====> 电子签章定时任务开始.scan.批量上传ftp结束耗时：" + (System.currentTimeMillis() - a));
            JSONObject json = JSONObject.parseObject(result);
            Object success = json.get("success");
            Object fail = json.get("fail");
            List<String> failList = null;
            String successFile = "";
            String failFile = "";
            if (success != null){
                //111queueId_134147.pdf,111queueId_134148.pdf,111queueId_134149.pdf
                successFile = (String) success;
            }
            if (fail != null){
                failFile = (String) fail;
                if(StringUtils.isNotEmpty(failFile)){
                    String[] failS = failFile.replace(".pdf", "").split("queueId_")[1].split(",") ;
                    if(failS.length>0){
                        failList = Arrays.asList(failS);
                    }
                }
            }
            String fileNames = successFile+failFile;
            new Thread(()->{
                try {
                    //只要ftp有跑过的都要比对删除
                    convertService.delSetFtpFile(tempPDF,fileNames);
                } catch(Exception e) {
                    log.error("====> 电子签章定时任务开始.scan.ftp操作过的文件删除："+ e.getMessage());
                }
            }).start();

            if (StringUtils.isNotEmpty(successFile)) {
                String updateSql;
                //成功
                String[] successSplit = successFile.split(",");
                for (String string : successSplit) {
                    String successFilePath = pdfFile+"/"+string;
                    String id = string.split("queueId_")[1].replace(".pdf","");
                    String business_id = signatureQueuesRepository.getBuId(id);
                    updateSql = "UPDATE tlk_certificate SET ITEM_SIGNATURED_PROCESSED_STATUS=\"2\",LASTMODIFIED=NOW(),ITEM_LAST_SIGNATURED_PROCESSED_TIME=NOW(),ITEM_CERTIFICATEDATE=IFNULL(ITEM_CERTIFICATEDATE,NOW()),ITEM_LAST_SIGNATURED_PROCESSED_RESULT=\"完成\",ITEM_ATTACHMENT=\"" + successFilePath + "\" WHERE ID=\"" + business_id + "\";";
                    jdbcTemplate.update(updateSql);
                    signatureQueuesRepository.updateSignature(1, new Date(), "成功", id);
                }
            }
            if (failList != null){
                signatureQueuesRepository.updateSignatures(2, new Date(), "未成功签章", failList);
            }
            log.info("====> 电子签章定时任务开始.scan.50个PDF已签章文件存FTP耗时：" + (System.currentTimeMillis() - a));
        } catch (Exception e) {
            log.error("====> 电子签章定时任务开始.scan.updateSql error：" + ExceptionUtils.getStackMessage(e));
        }
    }

    @Data
    public static class SignatureData {
        private String name;
        private String address;
        private String headphoto;
        private String idcardno;
        private String year;
        private String month;
        private String day;
        private String certificateno;
        private String isSignature;
        //发证日期
        private String fzDate;
        private String zYear;
        private String zMonth;
        private String zDay;
        private String signatureImg;
        //未签章pdf存储路径
        private String unSignaturePDF;
        //已签章pdf扫描路径
        private String signaturePDF;
    }

    /**
     * Restful 接口返回的资源对象
     *
     */
    @Data
    public static class Resource {
        private int errcode;
        private String errmsg;
        private Object data;
    }
}
