package com.liquidnet.service.platform.controller.A_fskfsfs;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.liquidnet.common.cache.redis.util.RedisDataSourceUtil;
import com.liquidnet.commons.lang.util.DateUtil;
import com.liquidnet.service.base.ResponseDto;
import com.liquidnet.service.kylin.constant.KylinRedisConst;
import com.liquidnet.service.kylin.dto.param.platformFskfsfs.FskfsfsAddressExcelVo;
import com.liquidnet.service.kylin.dto.vo.mongo.KylinOrderRefundEntitiesVo;
import com.liquidnet.service.kylin.dto.vo.mongo.KylinOrderRefundPicVo;
import com.liquidnet.service.kylin.dto.vo.mongo.KylinOrderTicketEntitiesVo;
import com.liquidnet.service.kylin.dto.vo.mongo.KylinOrderTicketVo;
import com.liquidnet.service.kylin.dto.vo.returns.KylinOrderRefundsVo;
import com.liquidnet.service.kylin.entity.KylinOrderRefunds;
import com.liquidnet.service.kylin.entity.KylinOrderRefundEntities;
import com.liquidnet.service.kylin.entity.KylinOrderRefundPic;
import com.liquidnet.service.kylin.entity.KylinOrderTickets;
import com.liquidnet.service.kylin.mapper.KylinOrderRefundsMapper;
import com.liquidnet.service.kylin.mapper.KylinOrderRefundsEntitiesMapper;
import com.liquidnet.service.kylin.mapper.KylinOrderRefundPicMapper;
import com.liquidnet.service.kylin.mapper.KylinOrderTicketsMapper;
import com.mysql.cj.jdbc.result.ResultSetImpl;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.bson.Document;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;

/**
 * 临时数据处理
 *
 * @author jiangxiulong
 */
@Api(tags = "临时数据处理")
@RestController
@RequestMapping("fskfsfs")
@Slf4j
public class JxlDataKylinOrderController {

    @Value("${spring.datasource.url}")
    private String SQL_URL;
    @Value("${spring.datasource.username}")
    private String SQL_USER;
    @Value("${spring.datasource.password}")
    private String SQL_PWD;

    @Autowired
    private KylinOrderRefundsMapper kylinOrderRefundsMapper;
    @Autowired
    private KylinOrderRefundsEntitiesMapper kylinOrderRefundsEntitiesMapper;
    @Autowired
    private KylinOrderRefundPicMapper kylinOrderRefundPicMapper;
    @Autowired
    private KylinOrderTicketsMapper kylinOrderTicketsMapper;
    @Autowired
    private MongoTemplate mongoTemplate;
    @Autowired
    private RedisDataSourceUtil redisDataSourceUtil;

    @GetMapping("KO001")
    @ApiOperation("某演出快递票批量申请未退快递费bug临时数据处理")
    @ApiImplicitParams({
            @ApiImplicitParam(type = "query", dataType = "String", name = "performancesId", value = "演出id", required = true),
    })
    public ResponseDto KO001(
            @RequestParam("performancesId") String performancesId
    ) {
        try {
            String sql = "select aa.created_at, aa.order_tickets_id, aa.price, bb.price_actual,bb.number,count(1) as ssrefund_number, bb.price_express as b_price_express, aa.price_express, type, aa.order_refunds_id, bb.order_code\n" +
                    "from kylin_order_refunds as aa\n" +
                    "         join kylin_order_tickets as bb on aa.order_tickets_id = bb.order_tickets_id\n" +
                    "where bb.performance_title = '2024武汉草莓音乐节'\n" +
                    "  and bb.get_ticket_type = 'express'\n" +
                    "  and aa.price_express != bb.price_express\n" +
                    "  and aa.status = 0\n" +
                    "group by bb.order_code, bb.number\n" +
                    "HAVING\n" +
                    "        COUNT(1) = bb.number;";

            Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER, SQL_PWD);
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSetImpl row = (ResultSetImpl) preparedStatement.executeQuery();
            while (row.next()) {
                String order_tickets_id = row.getString("order_tickets_id");
                String order_refunds_id = row.getString("order_refunds_id");
                BigDecimal b_price_express = row.getBigDecimal("b_price_express");

                // mysql
                KylinOrderRefunds aNew = KylinOrderRefunds.getNew();
                aNew.setPriceExpress(b_price_express);
                kylinOrderRefundsMapper.update(aNew,
                        Wrappers.lambdaUpdate(KylinOrderRefunds.class)
                                .eq(KylinOrderRefunds::getOrderRefundsId, order_refunds_id)
                );
                // mongodb
                mongoTemplate.getCollection(KylinOrderRefundsVo.class.getSimpleName()).updateOne(
                        Query.query(Criteria.where("orderRefundsId").is(order_refunds_id)).getQueryObject(),
                        new Document("$set", new Document("priceExpress", b_price_express))
                );
                // redis
                redisDataSourceUtil.getRedisKylinUtil().del(KylinRedisConst.ORDER_REFUND_BY_ORDER_ID + order_tickets_id);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ResponseDto.success();
    }

    @PostMapping("KO002")
    @ApiOperation("修改快递票订单收货地址")
    @ApiImplicitParams({
            @ApiImplicitParam(type = "form", dataType = "File", name = "file", value = "文件", required = true)
    })
    public ResponseDto KO002(
            @RequestParam MultipartFile file
    ) {
        try {
            EasyExcel.read("/Users/jiangxiulong/Desktop" + File.separator + file.getOriginalFilename(), FskfsfsAddressExcelVo.class, new PageReadListener<FskfsfsAddressExcelVo>(dataList -> {
                for (FskfsfsAddressExcelVo demoData : dataList) {
                    String orderCode = demoData.getOrderCode();
                    String userName = demoData.getUserName();
                    String phone = demoData.getPhone();
                    String address = demoData.getAddress();
                    log.info("orderCode:{}，userName:{}，phone:{}，address:{}，", orderCode, userName, phone, address);

                    if (null == orderCode) {
                        continue;
                    }
                    // 获取订单 id
                    KylinOrderTickets kylinOrderTickets = kylinOrderTicketsMapper.selectOne(
                            Wrappers.lambdaQuery(KylinOrderTickets.class)
                                    .eq(KylinOrderTickets::getOrderCode, orderCode)
                    );
                    String orderTicketsId = kylinOrderTickets.getOrderTicketsId();
                    if (null == userName || userName.isEmpty()) {
                        userName = kylinOrderTickets.getExpressContacts();
                    }
                    if (null == phone || phone.isEmpty()) {
                        phone = kylinOrderTickets.getExpressPhone();
                    }
                    if (null == address || address.isEmpty()) {
                        address = kylinOrderTickets.getExpressAddress();
                    }

                    // mysql
                    KylinOrderTickets aNew = KylinOrderTickets.getNew();
                    aNew.setExpressContacts(userName);
                    aNew.setExpressPhone(phone);
                    aNew.setExpressAddress(address);
                    kylinOrderTicketsMapper.update(aNew,
                            Wrappers.lambdaUpdate(KylinOrderTickets.class)
                                    .eq(KylinOrderTickets::getOrderTicketsId, orderTicketsId)
                    );

                    // mongodb
                    mongoTemplate.getCollection(KylinOrderTicketVo.class.getSimpleName()).updateOne(
                            Query.query(Criteria.where("orderTicketsId").is(orderTicketsId)).getQueryObject(),
                            new Document("$set", new Document("expressContacts", userName)
                                    .append("expressAddress", address)
                                    .append("expressPhone", phone)
                            )
                    );

                    //redis
                    redisDataSourceUtil.getRedisKylinUtil().del(KylinRedisConst.ORDER + orderTicketsId);
                }
            })).sheet().doRead();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ResponseDto.success();
    }

    @GetMapping("KO003")
    @ApiOperation("修复订单支付状态 - 系统故障导致用户支付成功但订单状态未更新")
    public ResponseDto KO003() {
        try {
            log.info("开始批量修复订单支付状态");

            // 第一步：查询所有需要修复的订单（支付成功但订单状态未更新的）
            log.info("第一步：查询所有需要修复的订单");
            String findOrdersSql = "SELECT kot.order_tickets_id, kot.pay_code, kot.order_code, kts.status, kot.user_id " +
                                  "FROM kylin_order_tickets as kot " +
                                  "JOIN dragon_orders as do ON do.code = kot.pay_code " +
                                  "JOIN kylin_order_ticket_status as kts ON kts.order_id = kot.order_tickets_id " +
                                  "WHERE 1 = 1 " +
                                  "AND (kts.status = 0 OR kts.status = 2) " +
//                                  "AND kts.status = 2 " +
//                                  "AND kts.status = 1 " +
//                                  "AND kot.order_code = '582528024908148736162994054' " +
                                  "AND kot.performance_title = '2025中山草莓音乐节' " +
                                  "AND do.status = 1";

            Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER, SQL_PWD);
            PreparedStatement stmt = connection.prepareStatement(findOrdersSql);
            ResultSetImpl rs = (ResultSetImpl) stmt.executeQuery();
            
            int totalCount = 0;
            int successCount = 0;
            int failCount = 0;
            
            // 第二步：逐个修复订单
            log.info("第二步：开始逐个修复订单");
            while (rs.next()) {
                totalCount++;
                String orderTicketsId = rs.getString("order_tickets_id");
                String orderCode = rs.getString("order_code");
                String userId = rs.getString("user_id");

                try {
                    log.info("正在修复订单 {} - orderCode: {}, orderTicketsId: {}", totalCount, orderCode, orderTicketsId);

                    // 第二步补充：检查是否有退款申请记录，有退款申请的不处理
                    String checkRefundSql = "SELECT COUNT(1) as refund_count FROM kylin_order_refunds WHERE order_tickets_id = ?";
                    PreparedStatement refundStmt = connection.prepareStatement(checkRefundSql);
                    refundStmt.setString(1, orderTicketsId);
                    ResultSetImpl refundRs = (ResultSetImpl) refundStmt.executeQuery();

                    int refundCount = 0;
                    if (refundRs.next()) {
                        refundCount = refundRs.getInt("refund_count");
                    }

                    if (refundCount > 0) {
                        log.warn("订单存在退款申请记录，跳过修复 - orderCode: {}, 退款记录数: {}", orderCode, refundCount);
                        continue;
                    }

                    // 获取支付信息
                    String verifySql = "SELECT do.payment_id, do.payment_type, do.payment_at " +
                                      "FROM kylin_order_tickets as kot " +
                                      "JOIN dragon_orders as do ON do.code = kot.pay_code " +
                                      "WHERE kot.order_code = ? AND do.status = 1";

                    PreparedStatement payStmt = connection.prepareStatement(verifySql);
                    payStmt.setString(1, orderCode);
                    ResultSetImpl payRs = (ResultSetImpl) payStmt.executeQuery();

                    String paymentId = null;
                    String paymentType = null;
                    String paymentAt = null;

                    if (payRs.next()) {
                        paymentId = payRs.getString("payment_id");
                        paymentType = payRs.getString("payment_type");
                        paymentAt = payRs.getString("payment_at");
                    }

                    // 第三步：更新MySQL中的订单相关表
                    // 更新订单主表 kylin_order_tickets
                    KylinOrderTickets orderUpdate = KylinOrderTickets.getNew();
                    orderUpdate.setPaymentType(paymentType);
                    orderUpdate.setPaymentId(paymentId);
                    orderUpdate.setTimePay(paymentAt);
                    kylinOrderTicketsMapper.update(orderUpdate,
                            Wrappers.lambdaUpdate(KylinOrderTickets.class)
                                    .eq(KylinOrderTickets::getOrderTicketsId, orderTicketsId)
                    );

                    // 更新订单状态表 kylin_order_ticket_status
                    String updateStatusSql = "UPDATE kylin_order_ticket_status SET status = 1, pay_status = 1 WHERE order_id = ?";
                    PreparedStatement statusStmt = connection.prepareStatement(updateStatusSql);
                    statusStmt.setString(1, orderTicketsId);
                    statusStmt.executeUpdate();

                    // 更新订单票实体表 kylin_order_ticket_entities
                    String updateEntitiesSql = "UPDATE kylin_order_ticket_entities SET is_payment = 1 WHERE order_id = ?";
                    PreparedStatement entitiesStmt = connection.prepareStatement(updateEntitiesSql);
                    entitiesStmt.setString(1, orderTicketsId);
                    entitiesStmt.executeUpdate();

                    // 第四步：更新MongoDB中的订单相关集合
                    // 更新订单主视图 KylinOrderTicketVo
                    Document orderUpdateDoc = new Document("$set", new Document()
                            .append("paymentType", paymentType)
                            .append("paymentId", paymentId)
                            .append("timePay", paymentAt)
                            .append("status", 1)
                            .append("payStatus", 1)
                    );

                    mongoTemplate.getCollection(KylinOrderTicketVo.class.getSimpleName()).updateOne(
                            Query.query(Criteria.where("orderTicketsId").is(orderTicketsId)).getQueryObject(),
                            orderUpdateDoc
                    );

                    // 更新订单票实体视图 KylinOrderTicketEntitiesVo
                    Document entitiesUpdateDoc = new Document("$set", new Document("isPayment", 1));
                    mongoTemplate.getCollection("KylinOrderTicketEntitiesVo").updateMany(
                            Query.query(Criteria.where("orderId").is(orderTicketsId)).getQueryObject(),
                            entitiesUpdateDoc
                    );

                    // 第五步：清理Redis缓存
                    redisDataSourceUtil.getRedisKylinUtil().del(KylinRedisConst.ORDER + orderTicketsId);
                    redisDataSourceUtil.getRedisKylinUtil().del(KylinRedisConst.ORDER_LIST + userId);

                    successCount++;
                    log.info("订单修复成功 - orderCode: {}", orderCode);

                } catch (Exception e) {
                    failCount++;
                    log.error("订单修复失败 - orderCode: {}, error: ", orderCode, e);
                }
            }
            
            connection.close();
            
            log.info("批量修复完成 - 总数: {}, 成功: {}, 失败: {}", totalCount, successCount, failCount);
            return ResponseDto.success(String.format("批量修复完成！总数: %d, 成功: %d, 失败: %d", totalCount, successCount, failCount));

        } catch (Exception e) {
            log.error("批量修复订单支付状态失败, error: ", e);
            return ResponseDto.failure("批量修复失败: " + e.getMessage());
        }
    }

    @GetMapping("KO004")
    @ApiOperation("恢复订单功能(未测试) - 将已退款的订单恢复为待付款状态，延长支付时间到24小时")
    @ApiImplicitParams({
            @ApiImplicitParam(type = "query", dataType = "String", name = "orderCode", value = "订单编号", required = true)
    })
    public ResponseDto KO004(@RequestParam("orderCode") String orderCode) {
        try {
            log.info("开始恢复订单 - orderCode: {}", orderCode);

            // 第一步：查询订单信息并验证
            log.info("第一步：查询订单信息并验证");
            KylinOrderTickets orderTickets = kylinOrderTicketsMapper.selectOne(
                    Wrappers.lambdaQuery(KylinOrderTickets.class)
                            .eq(KylinOrderTickets::getOrderCode, orderCode)
            );

            if (orderTickets == null) {
                log.error("订单不存在 - orderCode: {}", orderCode);
                return ResponseDto.failure("订单不存在");
            }

            String orderTicketsId = orderTickets.getOrderTicketsId();
            String userId = orderTickets.getUserId();
            log.info("找到订单 - orderTicketsId: {}", orderTicketsId);

            // 第二步：检查订单当前状态，只有已退款的订单才能恢复
            String checkStatusSql = "SELECT status, pay_status FROM kylin_order_ticket_status WHERE order_id = ?";
            Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER, SQL_PWD);
            PreparedStatement checkStmt = connection.prepareStatement(checkStatusSql);
            checkStmt.setString(1, orderTicketsId);
            ResultSetImpl statusRs = (ResultSetImpl) checkStmt.executeQuery();
            
            Integer currentStatus = null;
            Integer currentPayStatus = null;
            if (statusRs.next()) {
                currentStatus = statusRs.getInt("status");
                currentPayStatus = statusRs.getInt("pay_status");
            }
            
            log.info("找到订单状态 - orderTicketsId: {}, 当前status: {}, 当前pay_status: {}", 
                    orderTicketsId, currentStatus, currentPayStatus);

            // 检查订单状态，只有已退款的订单(status=4)才能恢复
            if (currentStatus == null || currentStatus != 4) {
                log.warn("订单状态不是已退款，无法恢复 - orderCode: {}, status: {}", orderCode, currentStatus);
                connection.close();
                return ResponseDto.failure("只有已退款的订单才能恢复，当前订单状态: " + currentStatus);
            }

            // 第三步：检查是否有退款记录
            log.info("第三步：检查退款记录");
            String checkRefundSql = "SELECT COUNT(1) as refund_count FROM kylin_order_refunds WHERE order_tickets_id = ?";
            PreparedStatement refundStmt = connection.prepareStatement(checkRefundSql);
            refundStmt.setString(1, orderTicketsId);
            ResultSetImpl refundRs = (ResultSetImpl) refundStmt.executeQuery();
            
            int refundCount = 0;
            if (refundRs.next()) {
                refundCount = refundRs.getInt("refund_count");
            }
            
            if (refundCount == 0) {
                log.error("订单没有退款记录，无法恢复 - orderCode: {}", orderCode);
                connection.close();
                return ResponseDto.failure("订单没有退款记录，无法恢复");
            }

            // 第四步：恢复订单状态为待付款
            log.info("第四步：恢复订单状态为待付款");
            
            // 更新订单主表 - 清空支付相关信息，重新设置创建时间和支付倒计时
            java.time.LocalDateTime now = java.time.LocalDateTime.now();
            KylinOrderTickets orderUpdate = KylinOrderTickets.getNew();
            orderUpdate.setPaymentType(null);
            orderUpdate.setPaymentId(null);
            orderUpdate.setTimePay(null);
            orderUpdate.setPayCode(""); // 清空支付码，需要重新生成
            orderUpdate.setPayCountdownMinute(1440); // 24小时 = 1440分钟
            orderUpdate.setCreatedAt(now); // 重新设置创建时间
            orderUpdate.setUpdatedAt(now);
            kylinOrderTicketsMapper.update(orderUpdate,
                    Wrappers.lambdaUpdate(KylinOrderTickets.class)
                            .eq(KylinOrderTickets::getOrderTicketsId, orderTicketsId)
            );

            // 更新订单状态表 - 改为待付款状态
            String updateStatusSql = "UPDATE kylin_order_ticket_status SET status = 0, pay_status = 0, updated_at = ? WHERE order_id = ?";
            PreparedStatement statusStmt = connection.prepareStatement(updateStatusSql);
            statusStmt.setObject(1, now);
            statusStmt.setString(2, orderTicketsId);
            statusStmt.executeUpdate();

            // 更新订单票实体表 - 改为未支付状态
            String updateEntitiesSql = "UPDATE kylin_order_ticket_entities SET is_payment = 0, updated_at = ? WHERE order_id = ?";
            PreparedStatement entitiesStmt = connection.prepareStatement(updateEntitiesSql);
            entitiesStmt.setObject(1, now);
            entitiesStmt.setString(2, orderTicketsId);
            entitiesStmt.executeUpdate();

            connection.close();

            // 第五步：更新MongoDB中的订单相关集合
            log.info("第五步：更新MongoDB中的订单相关集合");
            
            // 计算新的过期时间（24小时后）
            String overdueAt = now.plusMinutes(1440).format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
            String createdAtStr = now.format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
            
            // 更新订单主视图 KylinOrderTicketVo
            Document orderUpdateDoc = new Document("$set", new Document()
                    .append("paymentType", null)
                    .append("paymentId", null)
                    .append("timePay", null)
                    .append("payCode", "")
                    .append("status", 0)
                    .append("payStatus", 0)
                    .append("payCountdownMinute", 1440)
                    .append("overdueAt", overdueAt)
                    .append("createdAt", createdAtStr)
                    .append("updatedAt", createdAtStr)
                    .append("changeDate", now)
            );
            
            mongoTemplate.getCollection(KylinOrderTicketVo.class.getSimpleName()).updateOne(
                    Query.query(Criteria.where("orderTicketsId").is(orderTicketsId)).getQueryObject(),
                    orderUpdateDoc
            );

            // 更新订单票实体视图 KylinOrderTicketEntitiesVo
            Document entitiesUpdateDoc = new Document("$set", new Document()
                    .append("isPayment", 0)
                    .append("updatedAt", createdAtStr)
                    .append("changeDate", now)
            );
            mongoTemplate.getCollection("KylinOrderTicketEntitiesVo").updateMany(
                    Query.query(Criteria.where("orderId").is(orderTicketsId)).getQueryObject(),
                    entitiesUpdateDoc
            );

            // 第六步：清理Redis缓存
            log.info("第六步：清理Redis缓存");
            redisDataSourceUtil.getRedisKylinUtil().del(KylinRedisConst.ORDER + orderTicketsId);
            redisDataSourceUtil.getRedisKylinUtil().del(KylinRedisConst.ORDER_LIST + userId);

            // 第七步：重新发送支付倒计时队列（24小时）
            log.info("第七步：重新发送支付倒计时队列");
            // 注意：这里需要调用QueueUtils的sendMsgByRedisGoblinStock方法
            // queueUtils.sendMsgByRedisGoblinStock(orderTicketsId, now, "TICKET", 1440);
            // 由于没有直接注入QueueUtils，这里先记录日志，实际使用时需要注入
            log.info("需要重新发送支付倒计时队列 - orderTicketsId: {}, 倒计时: 1440分钟", orderTicketsId);

            log.info("订单恢复完成 - orderCode: {}, 新的支付时间: 24小时", orderCode);
            return ResponseDto.success(String.format("订单恢复成功！订单编号: %s, 支付时间已延长至24小时", orderCode));

        } catch (Exception e) {
            log.error("恢复订单失败 - orderCode: {}, error: ", orderCode, e);
            return ResponseDto.failure("恢复订单失败: " + e.getMessage());
        }
    }

    @GetMapping("KO005")
    @ApiOperation("修复订单支付状态 - admin显示已付款 用户显示取消中")
    public ResponseDto KO005() {
        try {
            log.info("开始批量修复订单支付状态");

            // 第一步：查询所有需要修复的订单（支付成功但订单状态未更新的）
            log.info("第一步：查询所有需要修复的订单");
            String findOrdersSql = "SELECT kot.order_tickets_id, kot.pay_code, kot.order_code, kts.status, kot.user_id " +
                    "FROM kylin_order_tickets as kot " +
                    "JOIN dragon_orders as do ON do.code = kot.pay_code " +
                    "JOIN kylin_order_ticket_status as kts ON kts.order_id = kot.order_tickets_id " +
                    "WHERE 1 = 1 " +
                    "AND kts.status = 1 " +
//                    "AND kot.order_code like '%0331891531%' " +
                    "AND kot.performance_title = '2025珠海草莓音乐节' " +
                    "AND do.status = 1";

            Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER, SQL_PWD);
            PreparedStatement stmt = connection.prepareStatement(findOrdersSql);
            ResultSetImpl rs = (ResultSetImpl) stmt.executeQuery();

            int totalCount = 0;
            int successCount = 0;
            int skipCount = 0; // 跳过的订单数（有退款申请的）
            int failCount = 0;
            
            // 错误记录列表
            java.util.List<String> errorList = new java.util.ArrayList<>();

            // 第二步：逐个修复订单
            log.info("第二步：开始逐个修复订单");
            while (rs.next()) {
                totalCount++;
                String orderTicketsId = rs.getString("order_tickets_id");
                String orderCode = rs.getString("order_code");
                String userId = rs.getString("user_id");

                try {
                    log.info("正在修复订单 {} - orderCode: {}, orderTicketsId: {}", totalCount, orderCode, orderTicketsId);

                    // 第二步补充：检查是否有退款申请记录，有退款申请的不处理
                    String checkRefundSql = "SELECT COUNT(1) as refund_count FROM kylin_order_refunds WHERE order_tickets_id = ?";
                    PreparedStatement refundStmt = connection.prepareStatement(checkRefundSql);
                    refundStmt.setString(1, orderTicketsId);
                    ResultSetImpl refundRs = (ResultSetImpl) refundStmt.executeQuery();

                    int refundCount = 0;
                    if (refundRs.next()) {
                        refundCount = refundRs.getInt("refund_count");
                    }

                    if (refundCount > 0) {
                        skipCount++;
                        String errorMsg = String.format("订单存在退款申请记录，跳过修复 - orderCode: %s, 退款记录数: %d", orderCode, refundCount);
                        log.warn(errorMsg);
                        errorList.add("SKIP_REFUND: " + errorMsg);
                        continue;
                    }

                    // 第三步：查询MongoDB中的订单状态是否为 1 如果为 1 更新 继续redis 如果不为 1 记录错误跳过
                    log.info("第三步：检查MongoDB中的订单状态");

                    // 先查询MongoDB中的订单状态
                    Query mongoQuery = Query.query(Criteria.where("orderTicketsId").is(orderTicketsId));
                    KylinOrderTicketVo mongoOrder = mongoTemplate.findOne(mongoQuery, KylinOrderTicketVo.class, KylinOrderTicketVo.class.getSimpleName());

                    if (mongoOrder == null) {
                        failCount++;
                        String errorMsg = String.format("MongoDB中未找到订单，跳过修复 - orderCode: %s, orderTicketsId: %s", orderCode, orderTicketsId);
                        log.error(errorMsg);
                        errorList.add("MONGO_NOT_FOUND: " + errorMsg);
                        continue;
                    }

                    // 检查MongoDB中的订单状态是否为1（已支付）
                    if (mongoOrder.getStatus() == null || mongoOrder.getStatus() != 1) {
                        // 获取支付信息
                        String verifySql = "SELECT do.payment_id, do.payment_type, do.payment_at " +
                                "FROM kylin_order_tickets as kot " +
                                "JOIN dragon_orders as do ON do.code = kot.pay_code " +
                                "WHERE kot.order_code = ? AND do.status = 1";

                        PreparedStatement payStmt = connection.prepareStatement(verifySql);
                        payStmt.setString(1, orderCode);
                        ResultSetImpl payRs = (ResultSetImpl) payStmt.executeQuery();

                        String paymentId = null;
                        String paymentType = null;
                        String paymentAt = null;

                        if (payRs.next()) {
                            paymentId = payRs.getString("payment_id");
                            paymentType = payRs.getString("payment_type");
                            paymentAt = payRs.getString("payment_at");
                        }
                        // MongoDB状态正确，更新订单状态（确保状态一致）
                        Document orderUpdateDoc = new Document("$set", new Document()
                                .append("paymentType", paymentType)
                                .append("paymentId", paymentId)
                                .append("timePay", paymentAt)
                                .append("status", 1)
                                .append("payStatus", 1)
                        );

                        mongoTemplate.getCollection(KylinOrderTicketVo.class.getSimpleName()).updateOne(
                                Query.query(Criteria.where("orderTicketsId").is(orderTicketsId)).getQueryObject(),
                                orderUpdateDoc
                        );

                        log.info("MongoDB订单状态更新完成 - orderCode: {}", orderCode);

//                        failCount++;
//                        String errorMsg = String.format("MongoDB中订单状态不为1，跳过修复 - orderCode: %s, MongoDB状态: %s", orderCode, mongoOrder.getStatus());
//                        log.error(errorMsg);
//                        errorList.add("MONGO_STATUS_ERROR: " + errorMsg);
//                        continue;
                    }

                    // 第四步：清理Redis缓存
                    redisDataSourceUtil.getRedisKylinUtil().del(KylinRedisConst.ORDER + orderTicketsId);
                    redisDataSourceUtil.getRedisKylinUtil().del(KylinRedisConst.ORDER_LIST + userId);

                    successCount++;
                    log.info("订单修复成功 - orderCode: {}", orderCode);

                } catch (Exception e) {
                    failCount++;
                    String errorMsg = String.format("订单修复异常失败 - orderCode: %s, error: %s", orderCode, e.getMessage());
                    log.error(errorMsg, e);
                    errorList.add("EXCEPTION: " + errorMsg);
                }
            }

            connection.close();

            // 打印详细的错误信息
            if (!errorList.isEmpty()) {
                log.error("=== 详细错误信息 ===");
                for (int i = 0; i < errorList.size(); i++) {
                    log.error("错误 {}: {}", i + 1, errorList.get(i));
                }
                log.error("=== 错误信息结束 ===");
            }

            // 验证计数是否正确
            int calculatedTotal = successCount + skipCount + failCount;
            if (calculatedTotal != totalCount) {
                log.warn("计数验证失败！总数: {}, 计算总数: {} (成功: {} + 跳过: {} + 失败: {})", 
                        totalCount, calculatedTotal, successCount, skipCount, failCount);
            }

            log.info("批量修复完成 - 总数: {}, 成功: {}, 跳过: {}, 失败: {}", totalCount, successCount, skipCount, failCount);
            
            String resultMsg = String.format("批量修复完成！总数: %d, 成功: %d, 跳过: %d, 失败: %d", 
                    totalCount, successCount, skipCount, failCount);
            
            if (!errorList.isEmpty()) {
                resultMsg += "\n详细错误信息:\n" + String.join("\n", errorList);
            }
            
            return ResponseDto.success(resultMsg);

        } catch (Exception e) {
            log.error("批量修复订单支付状态失败, error: ", e);
            return ResponseDto.failure("批量修复失败: " + e.getMessage());
        }
    }

    @GetMapping("KO006")
    @ApiOperation("修复退款 Mysql 数据-MQ 内存满")
    @ApiImplicitParams({
            @ApiImplicitParam(type = "query", dataType = "Boolean", name = "autoFix", value = "是否自动修复（true=修复，false=仅检查）", required = false)
    })
    public ResponseDto KO006(
            @RequestParam(value = "autoFix", required = false, defaultValue = "false") Boolean autoFix
    ) {
        try {
            log.info("开始批量查询退款信息，自动修复模式: {}", autoFix);

            // 第一步：查询所有需要检查的订单（所有支付成功或者退款中的订单）
            log.info("第一步：查询所有需要检查的订单");
            String findOrdersSql = "SELECT\n" +
                    "    kot.order_tickets_id,\n" +
                    "    kot.pay_code,\n" +
                    "    kot.order_code,\n" +
                    "    kts.status,\n" +
                    "    kot.user_id\n" +
                    "FROM kylin_order_tickets AS kot\n" +
                    "         JOIN dragon_orders AS do\n" +
                    "              ON do.code = kot.pay_code\n" +
                    "         JOIN kylin_order_ticket_status AS kts\n" +
                    "              ON kts.order_id = kot.order_tickets_id\n" +
                    "WHERE 1 = 1\n" +
                    "  AND (kts.status = 1 OR kts.status = 3)\n" +
//                    "AND kot.order_code = '582523623766241280872835556'\n" +
                    "  AND kot.performance_title = '2025中山草莓音乐节'\n" +
                    "  AND do.status = 1;";

            Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER, SQL_PWD);
            PreparedStatement stmt = connection.prepareStatement(findOrdersSql);
            ResultSetImpl rs = (ResultSetImpl) stmt.executeQuery();

            int totalCount = 0;
            int hasRedisNoMysqlCount = 0; // Redis有但MySQL没有的订单数
            int fixedCount = 0;  // 成功修复的订单数
            int failCount = 0;   // 修复失败的订单数

            // 记录缺失数据的订单信息
            java.util.List<String> missingDataList = new java.util.ArrayList<>();
            java.util.List<String> fixedDataList = new java.util.ArrayList<>();
            java.util.List<String> failedDataList = new java.util.ArrayList<>();

            // 第二步：逐个检查订单
            log.info("第二步：开始逐个检查订单");
            while (rs.next()) {
                totalCount++;
                String orderTicketsId = rs.getString("order_tickets_id");
                String orderCode = rs.getString("order_code");
                String userId = rs.getString("user_id");

                try {
                    log.info("正在检查订单 {} - orderCode: {}, orderTicketsId: {}", totalCount, orderCode, orderTicketsId);

                    // 第二步：从 Redis 获取退款记录（使用 key 拼接）
                    Object redisObj = redisDataSourceUtil.getRedisKylinUtil().get(KylinRedisConst.ORDER_REFUND_BY_ORDER_ID + orderTicketsId);
                    List<KylinOrderRefundsVo> redisRefundList = null;

                    if (redisObj != null) {
                        redisRefundList = (List<KylinOrderRefundsVo>) redisObj;
                        log.info("Redis中找到退款记录 - orderCode: {}, 记录数: {}", orderCode, redisRefundList.size());
                    }

                    // 第三步：从 MySQL 查询退款记录
                    String checkRefundSql = "SELECT COUNT(1) as refund_count FROM kylin_order_refunds WHERE order_tickets_id = ?";
                    PreparedStatement refundStmt = connection.prepareStatement(checkRefundSql);
                    refundStmt.setString(1, orderTicketsId);
                    ResultSetImpl refundRs = (ResultSetImpl) refundStmt.executeQuery();

                    int mysqlRefundCount = 0;
                    if (refundRs.next()) {
                        mysqlRefundCount = refundRs.getInt("refund_count");
                    }
                    log.info("MySQL中退款记录数 - orderCode: {}, 记录数: {}", orderCode, mysqlRefundCount);

                    // 第四步：对比 Redis 和 MySQL 的数据
                    if (redisRefundList != null && redisRefundList.size() > 0 && mysqlRefundCount == 0) {
                        hasRedisNoMysqlCount++;

                        // 构建详细的缺失数据信息
                        StringBuilder missingInfo = new StringBuilder();
                        missingInfo.append("\n========================================\n");
                        missingInfo.append(String.format("订单编号: %s\n", orderCode));
                        missingInfo.append(String.format("订单ID: %s\n", orderTicketsId));
                        missingInfo.append(String.format("用户ID: %s\n", userId));
                        missingInfo.append(String.format("Redis退款记录数: %d\n", redisRefundList.size()));
                        missingInfo.append("Redis退款详情:\n");

                        for (int i = 0; i < redisRefundList.size(); i++) {
                            KylinOrderRefundsVo refundVo = redisRefundList.get(i);
                            missingInfo.append(String.format("  [%d] 退款ID: %s\n", i + 1, refundVo.getOrderRefundsId()));
                            missingInfo.append(String.format("      订单票ID: %s\n", refundVo.getOrderTicketsId()));
                            missingInfo.append(String.format("      退款状态: %s\n", refundVo.getStatus()));
                            missingInfo.append(String.format("      退款类型: %s\n", refundVo.getType()));
                            missingInfo.append(String.format("      退款金额: %s\n", refundVo.getPrice()));
                            missingInfo.append(String.format("      快递费: %s\n", refundVo.getPriceExpress()));
                            missingInfo.append(String.format("      创建时间: %s\n", refundVo.getCreatedAt()));

                            if (refundVo.getOrderRefundEntitiesVoList() != null && !refundVo.getOrderRefundEntitiesVoList().isEmpty()) {
                                missingInfo.append(String.format("      退款实体数: %d\n", refundVo.getOrderRefundEntitiesVoList().size()));
                            }
                        }
                        missingInfo.append("========================================\n");

                        String missingInfoStr = missingInfo.toString();
                        missingDataList.add(missingInfoStr);
                        log.warn("发现数据缺失！{}", missingInfoStr);

                        // 第五步：如果开启自动修复，则进行修复操作
                        if (autoFix) {
                            log.info("开始修复订单 - orderCode: {}, 需要修复 {} 条退款记录", orderCode, redisRefundList.size());

                            boolean allSuccess = true;
                            StringBuilder fixInfo = new StringBuilder();
                            fixInfo.append(String.format("订单编号: %s, 订单ID: %s\n", orderCode, orderTicketsId));

                            for (KylinOrderRefundsVo refundVo : redisRefundList) {
                                try {
                                    // 插入 kylin_order_refunds 主表
                                    KylinOrderRefunds kylinOrderRefunds = new KylinOrderRefunds();
                                    kylinOrderRefunds.setOrderRefundsId(refundVo.getOrderRefundsId());
                                    kylinOrderRefunds.setOrderTicketsId(refundVo.getOrderTicketsId());
                                    kylinOrderRefunds.setOrderRefundCode(refundVo.getOrderRefundCode());
                                    kylinOrderRefunds.setPrice(refundVo.getPrice());
                                    kylinOrderRefunds.setPriceExpress(refundVo.getPriceExpress());
                                    kylinOrderRefunds.setStatus(refundVo.getStatus());
                                    kylinOrderRefunds.setType(refundVo.getType());
                                    kylinOrderRefunds.setCreatedAt(refundVo.getCreatedAt());
                                    kylinOrderRefunds.setApplicantId(refundVo.getApplicantId());
                                    kylinOrderRefunds.setApplicantName(refundVo.getApplicantName());
                                    kylinOrderRefunds.setApplicantAt(refundVo.getApplicantAt());

                                    kylinOrderRefundsMapper.insert(kylinOrderRefunds);
                                    log.info("成功插入退款主表 - orderRefundsId: {}", refundVo.getOrderRefundsId());

                                    // 时间格式化器
                                    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

                                    // 从 MongoDB 查询退款实体列表
                                    List<KylinOrderRefundEntitiesVo> mongoEntitiesList = mongoTemplate.find(
                                            Query.query(Criteria.where("orderRefundsId").is(refundVo.getOrderRefundsId())),
                                            KylinOrderRefundEntitiesVo.class,
                                            KylinOrderRefundEntitiesVo.class.getSimpleName()
                                    );

                                    if (!mongoEntitiesList.isEmpty()) {
                                        for (KylinOrderRefundEntitiesVo entityVo : mongoEntitiesList) {
                                            KylinOrderRefundEntities entity = new KylinOrderRefundEntities();
                                            entity.setOrderRefundsEntitiesId(entityVo.getOrderRefundsEntitiesId());
                                            entity.setOrderRefundsId(entityVo.getOrderRefundsId());
                                            entity.setOrderTicketEntitiesId(entityVo.getOrderTicketEntitiesId());
                                            entity.setRefundPrice(entityVo.getRefundPrice());

                                            // 安全解析时间
                                            if (entityVo.getCreatedAt() != null && !entityVo.getCreatedAt().isEmpty()) {
                                                try {
                                                    entity.setCreatedAt(LocalDateTime.parse(entityVo.getCreatedAt(), formatter));
                                                } catch (Exception e) {
                                                    log.warn("解析 createdAt 失败: {}", entityVo.getCreatedAt());
                                                }
                                            }

                                            kylinOrderRefundsEntitiesMapper.insert(entity);
                                            log.info("成功插入退款实体 - orderRefundEntitiesId: {}", entity.getOrderTicketEntitiesId());
                                        }
                                    }

                                    // 从 MongoDB 查询退款图片列表
                                    List<KylinOrderRefundPicVo> mongoPicsList = mongoTemplate.find(
                                            Query.query(Criteria.where("orderRefundsId").is(refundVo.getOrderRefundsId())),
                                            KylinOrderRefundPicVo.class,
                                            KylinOrderRefundPicVo.class.getSimpleName()
                                    );

                                    if (!mongoPicsList.isEmpty()) {
                                        for (KylinOrderRefundPicVo picVo : mongoPicsList) {
                                            KylinOrderRefundPic pic = new KylinOrderRefundPic();
                                            pic.setRefundPicId(picVo.getRefundPicId());
                                            pic.setOrderRefundsId(picVo.getOrderRefundsId());
                                            pic.setPicUrl(picVo.getPicUrl());

                                            // 安全解析时间
                                            if (picVo.getCreatedAt() != null && !picVo.getCreatedAt().isEmpty()) {
                                                try {
                                                    pic.setCreatedAt(LocalDateTime.parse(picVo.getCreatedAt(), formatter));
                                                } catch (Exception e) {
                                                    log.warn("解析 createdAt 失败: {}", picVo.getCreatedAt());
                                                }
                                            }

                                            kylinOrderRefundPicMapper.insert(pic);
                                            log.info("成功插入退款图片 - orderRefundPicId: {}", pic.getRefundPicId());
                                        }
                                    }

                                    // 更新订单状态表 kylin_order_ticket_status - 将状态设为 3（退款中）
                                    String updateStatusSql = "UPDATE kylin_order_ticket_status SET status = 3, updated_at = ? WHERE order_id = ?";
                                    PreparedStatement updateStatusStmt = connection.prepareStatement(updateStatusSql);
                                    updateStatusStmt.setObject(1, LocalDateTime.now());
                                    updateStatusStmt.setString(2, orderTicketsId);
                                    int statusUpdated = updateStatusStmt.executeUpdate();
                                    log.info("成功更新订单状态为退款中 - orderTicketsId: {}, 影响行数: {}", orderTicketsId, statusUpdated);

                                    // 更新订单实体表 kylin_order_ticket_entities - 根据退款实体将支付状态设为 2（退款中）
                                    if (!mongoEntitiesList.isEmpty()) {
                                        for (KylinOrderRefundEntitiesVo entityVo : mongoEntitiesList) {
                                            String updateEntitiesSql = "UPDATE kylin_order_ticket_entities SET is_payment = 2, updated_at = ? WHERE order_ticket_entities_id = ?";
                                            PreparedStatement updateEntitiesStmt = connection.prepareStatement(updateEntitiesSql);
                                            updateEntitiesStmt.setObject(1, LocalDateTime.now());
                                            updateEntitiesStmt.setString(2, entityVo.getOrderTicketEntitiesId());
                                            int entitiesUpdated = updateEntitiesStmt.executeUpdate();
                                            log.info("成功更新订单实体为退款中 - orderTicketEntitiesId: {}, 影响行数: {}",
                                                    entityVo.getOrderTicketEntitiesId(), entitiesUpdated);
                                        }
                                    }

//                                    // 更新 MongoDB 中的订单状态
//                                    Document orderStatusUpdateDoc = new Document("$set", new Document()
//                                            .append("status", 3)
//                                            .append("updatedAt", DateUtil.getNowTime())
//                                    );
//                                    mongoTemplate.getCollection(KylinOrderTicketVo.class.getSimpleName()).updateOne(
//                                            Query.query(Criteria.where("orderTicketsId").is(orderTicketsId)).getQueryObject(),
//                                            orderStatusUpdateDoc
//                                    );
//                                    log.info("成功更新MongoDB订单状态为退款中 - orderTicketsId: {}", orderTicketsId);
//
//                                    // 更新 MongoDB 中的订单实体状态
//                                    if (!mongoEntitiesList.isEmpty()) {
//                                        for (KylinOrderRefundEntitiesVo entityVo : mongoEntitiesList) {
//                                            Document entitiesStatusUpdateDoc = new Document("$set", new Document()
//                                                    .append("isPayment", 2)
//                                                    .append("updatedAt", DateUtil.getNowTime())
//                                            );
//                                            mongoTemplate.getCollection(KylinOrderTicketEntitiesVo.class.getSimpleName()).updateOne(
//                                                    Query.query(Criteria.where("orderTicketEntitiesId").is(entityVo.getOrderTicketEntitiesId())).getQueryObject(),
//                                                    entitiesStatusUpdateDoc
//                                            );
//                                            log.info("成功更新MongoDB订单实体为退款中 - orderTicketEntitiesId: {}", entityVo.getOrderTicketEntitiesId());
//                                        }
//                                    }

                                    fixInfo.append(String.format("  ✓ 退款ID: %s, 状态: %s, 金额: %s\n",
                                            refundVo.getOrderRefundsId(), refundVo.getStatus(), refundVo.getPrice()));

                                } catch (Exception e) {
                                    allSuccess = false;
                                    fixInfo.append(String.format("  ✗ 退款ID: %s 修复失败: %s\n",
                                            refundVo.getOrderRefundsId(), e.getMessage()));
                                    log.error("修复退款记录失败 - orderRefundsId: {}, error: ", refundVo.getOrderRefundsId(), e);
                                }
                            }

                            if (allSuccess) {
                                fixedCount++;
                                fixedDataList.add("✓ " + fixInfo.toString());
                                log.info("订单修复成功 - orderCode: {}", orderCode);
                            } else {
                                failCount++;
                                failedDataList.add("✗ " + fixInfo.toString());
                                log.error("订单修复部分失败 - orderCode: {}", orderCode);
                            }
                        }
                    }

                } catch (Exception e) {
                    log.error("检查订单失败 - orderCode: {}, error: ", orderCode, e);
                    if (autoFix) {
                        failCount++;
                        failedDataList.add(String.format("✗ 订单: %s, 错误: %s\n", orderCode, e.getMessage()));
                    }
                }
            }

            connection.close();

            // 第六步：生成汇总报告
            StringBuilder summaryReport = new StringBuilder();
            summaryReport.append("\n\n========================================\n");
            if (autoFix) {
                summaryReport.append("           数据修复汇总报告\n");
            } else {
                summaryReport.append("           数据检查汇总报告\n");
            }
            summaryReport.append("========================================\n");
            summaryReport.append(String.format("检查订单总数: %d\n", totalCount));
            summaryReport.append(String.format("Redis有但MySQL缺失的订单数: %d\n", hasRedisNoMysqlCount));

            if (autoFix) {
                summaryReport.append(String.format("成功修复: %d\n", fixedCount));
                summaryReport.append(String.format("修复失败: %d\n", failCount));
            }
            summaryReport.append("========================================\n");

            if (!missingDataList.isEmpty()) {
                summaryReport.append("\n缺失数据详情:\n");
                for (String missingInfo : missingDataList) {
                    summaryReport.append(missingInfo);
                }
            }

            if (autoFix) {
                if (!fixedDataList.isEmpty()) {
                    summaryReport.append("\n✓ 成功修复的订单:\n");
                    for (String info : fixedDataList) {
                        summaryReport.append(info).append("\n");
                    }
                }

                if (!failedDataList.isEmpty()) {
                    summaryReport.append("\n✗ 修复失败的订单:\n");
                    for (String info : failedDataList) {
                        summaryReport.append(info).append("\n");
                    }
                }
            } else {
                if (hasRedisNoMysqlCount > 0) {
                    summaryReport.append("\n提示：如需自动修复，请添加参数 autoFix=true\n");
                } else {
                    summaryReport.append("\n恭喜！没有发现数据缺失的订单。\n");
                }
            }

            summaryReport.append("\n========================================\n");

            String reportStr = summaryReport.toString();
            log.info(reportStr);

            if (autoFix) {
                log.info("批量修复完成 - 总数: {}, 缺失: {}, 成功: {}, 失败: {}",
                        totalCount, hasRedisNoMysqlCount, fixedCount, failCount);
                return ResponseDto.success(String.format("批量修复完成！\n总订单数: %d\n缺失数据订单: %d\n成功修复: %d\n修复失败: %d\n\n%s",
                        totalCount, hasRedisNoMysqlCount, fixedCount, failCount, reportStr));
            } else {
                log.info("批量检查完成 - 总数: {}, Redis有但MySQL缺失: {}", totalCount, hasRedisNoMysqlCount);
                return ResponseDto.success(String.format("批量检查完成！\n总订单数: %d\nRedis有但MySQL缺失: %d\n\n%s",
                        totalCount, hasRedisNoMysqlCount, reportStr));
            }

        } catch (Exception e) {
            log.error("批量检查订单退款数据失败, error: ", e);
            return ResponseDto.failure("批量检查失败: " + e.getMessage());
        }
    }

}
