package com.ruoyi.web.controller.jarvis;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.ruoyi.common.annotation.Anonymous;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.redis.RedisCache;
import com.ruoyi.jarvis.domain.JDOrder;
import com.ruoyi.jarvis.service.ITencentDocService;
import com.ruoyi.jarvis.service.IJDOrderService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.http.MediaType;

import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

/**
 * 腾讯文档控制器
 *
 * @author system
 */
@RestController
@RequestMapping("/jarvis/tendoc")
public class TencentDocController extends BaseController {

    private static final Logger log = LoggerFactory.getLogger(TencentDocController.class);

    @Autowired
    private ITencentDocService tencentDocService;

    @Autowired
    private IJDOrderService jdOrderService;

    @Autowired
    private RedisCache redisCache;

    @Autowired
    private com.ruoyi.jarvis.service.ITencentDocTokenService tencentDocTokenService;

    @Autowired
    private com.ruoyi.jarvis.config.TencentDocConfig tencentDocConfig;

    @Autowired
    private com.ruoyi.jarvis.mapper.TencentDocOperationLogMapper operationLogMapper;

    @Autowired
    private com.ruoyi.jarvis.service.ITencentDocBatchPushService batchPushService;

    @Autowired
    private com.ruoyi.jarvis.service.ITencentDocDelayedPushService delayedPushService;

    /** Redis key前缀，用于存储上次处理的最大行数 */
    private static final String LAST_PROCESSED_ROW_KEY_PREFIX = "tendoc:last_row:";

    /** Redis key前缀，用于分布式锁 */
    private static final String TENCENT_DOC_LOCK_KEY = "tendoc:write:lock:";

    /**
     * 测试回调接口是否可访问（用于调试）
     */
    @Anonymous
    @GetMapping("/test-callback")
    public AjaxResult testCallback() {
        return AjaxResult.success("回调接口测试成功，路由配置正确");
    }

    /**
     * 获取授权URL（用于手动授权，获取token后通过setToken接口保存）
     */
    @GetMapping("/authUrl")
    public AjaxResult getAuthUrl() {
        try {
            String authUrl = tencentDocService.getAuthUrl();
            return AjaxResult.success("获取授权URL成功", authUrl);
        } catch (Exception e) {
            log.error("获取授权URL失败", e);
            return AjaxResult.error("获取授权URL失败: " + e.getMessage());
        }
    }

    /**
     * 手动设置访问令牌（用于首次授权后保存token）
     */
    @PostMapping("/setToken")
    public AjaxResult setToken(@RequestBody Map<String, Object> params) {
        try {
            String accessToken = (String) params.get("accessToken");
            String refreshToken = (String) params.get("refreshToken");
            Integer expiresIn = params.get("expiresIn") != null ?
                Integer.valueOf(params.get("expiresIn").toString()) : 7200;

            if (accessToken == null || accessToken.trim().isEmpty()) {
                return AjaxResult.error("accessToken不能为空");
            }

            // 通过反射调用setToken方法
            if (tencentDocTokenService instanceof com.ruoyi.jarvis.service.impl.TencentDocTokenServiceImpl) {
                ((com.ruoyi.jarvis.service.impl.TencentDocTokenServiceImpl) tencentDocTokenService)
                    .setToken(accessToken, refreshToken, expiresIn);
                return AjaxResult.success("访问令牌已保存");
            } else {
                return AjaxResult.error("Token服务类型不支持");
            }
        } catch (Exception e) {
            log.error("设置访问令牌失败", e);
            return AjaxResult.error("设置访问令牌失败: " + e.getMessage());
        }
    }

    /**
     * 测试获取用户信息接口
     */
    @GetMapping("/testUserInfo")
    public AjaxResult testUserInfo() {
        try {
            String token = tencentDocTokenService.getValidAccessToken();
            JSONObject userInfo = tencentDocService.getUserInfo(token);
            log.info("获取用户信息成功: {}", userInfo);
            return AjaxResult.success("获取用户信息成功", userInfo);
        } catch (Exception e) {
            log.error("测试获取用户信息失败", e);
            return AjaxResult.error("测试失败: " + e.getMessage());
        }
    }

    /**
     * 获取当前token状态
     */
    @GetMapping("/tokenStatus")
    public AjaxResult getTokenStatus() {
        try {
            String token;
            try {
                token = tencentDocTokenService.getValidAccessToken();
                return AjaxResult.success("访问令牌有效", new JSONObject().fluentPut("hasToken", true)
                    .fluentPut("token", token.substring(0, Math.min(20, token.length())) + "..."));
            } catch (Exception e) {
                return AjaxResult.success("访问令牌无效", new JSONObject().fluentPut("hasToken", false)
                    .fluentPut("message", e.getMessage()));
            }
        } catch (Exception e) {
            log.error("获取token状态失败", e);
            return AjaxResult.error("获取token状态失败: " + e.getMessage());
        }
    }

    /**
     * OAuth回调 - 通过授权码获取访问令牌
     * 根据腾讯文档官方文档：https://docs.qq.com/open/document/app/oauth2/authorize.html
     * 用户授权成功后，腾讯文档会重定向到此回调地址，并携带code和state参数
     * 返回HTML页面，自动关闭窗口并通知父窗口
     */
    @Anonymous
    @GetMapping(value = "/oauth/callback", produces = MediaType.TEXT_HTML_VALUE)
    public String oauthCallback(@RequestParam(value = "code", required = false) String code,
                                @RequestParam(value = "state", required = false) String state,
                                @RequestParam(value = "error", required = false) String error,
                                @RequestParam(value = "error_description", required = false) String errorDescription) {
        try {
            // 处理授权错误
            if (error != null) {
                log.error("腾讯文档授权失败 - error: {}, error_description: {}", error, errorDescription);
                String errorMsg = errorDescription != null ? errorDescription : error;
                return generateCallbackHtml(false, "授权失败: " + errorMsg, null);
            }

            // 验证授权码
            if (code == null || code.trim().isEmpty()) {
                log.error("授权码为空");
                return generateCallbackHtml(false, "授权码不能为空", null);
            }

            log.info("收到腾讯文档授权回调 - code: {}, state: {}", code, state);

            // 使用授权码换取access_token
            JSONObject tokenInfo = tencentDocService.getAccessTokenByCode(code);

            // 验证返回的token信息
            if (tokenInfo == null || !tokenInfo.containsKey("access_token")) {
                log.error("获取访问令牌失败 - 响应数据: {}", tokenInfo);
                return generateCallbackHtml(false, "获取访问令牌失败，响应数据格式不正确", null);
            }

            String accessToken = tokenInfo.getString("access_token");
            String refreshToken = tokenInfo.getString("refresh_token");
            Integer expiresIn = tokenInfo.getIntValue("expires_in");

            log.info("成功获取访问令牌 - access_token: {}", accessToken);

            // 自动保存token到后端
            try {
                if (tencentDocTokenService instanceof com.ruoyi.jarvis.service.impl.TencentDocTokenServiceImpl) {
                    ((com.ruoyi.jarvis.service.impl.TencentDocTokenServiceImpl) tencentDocTokenService)
                        .setToken(accessToken, refreshToken, expiresIn);
                    log.info("访问令牌已自动保存到后端缓存");
                }
            } catch (Exception e) {
                log.error("保存访问令牌失败", e);
                return generateCallbackHtml(false, "保存访问令牌失败: " + e.getMessage(), null);
            }

            return generateCallbackHtml(true, "授权成功，访问令牌已自动保存", null);
        } catch (Exception e) {
            log.error("OAuth回调处理失败", e);
            return generateCallbackHtml(false, "授权失败: " + e.getMessage(), null);
        }
    }

    /**
     * 生成回调HTML页面
     */
    private String generateCallbackHtml(boolean success, String message, Object data) {
        StringBuilder html = new StringBuilder();
        html.append("<!DOCTYPE html>");
        html.append("<html lang='zh-CN'>");
        html.append("<head>");
        html.append("<meta charset='UTF-8'>");
        html.append("<meta name='viewport' content='width=device-width, initial-scale=1.0'>");
        html.append("<title>腾讯文档授权").append(success ? "成功" : "失败").append("</title>");
        html.append("<style>");
        html.append("body { font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif; ");
        html.append("display: flex; justify-content: center; align-items: center; min-height: 100vh; margin: 0; ");
        html.append("background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); }");
        html.append(".container { background: white; padding: 40px; border-radius: 10px; box-shadow: 0 10px 40px rgba(0,0,0,0.2); text-align: center; max-width: 400px; }");
        html.append(".icon { font-size: 64px; margin-bottom: 20px; }");
        html.append(".success { color: #52c41a; }");
        html.append(".error { color: #ff4d4f; }");
        html.append(".message { font-size: 16px; color: #333; margin-bottom: 20px; line-height: 1.6; }");
        html.append("</style>");
        html.append("</head>");
        html.append("<body>");
        html.append("<div class='container'>");
        if (success) {
            html.append("<div class='icon success'>✓</div>");
            html.append("<h2 style='color: #52c41a; margin-bottom: 10px;'>授权成功</h2>");
        } else {
            html.append("<div class='icon error'>✗</div>");
            html.append("<h2 style='color: #ff4d4f; margin-bottom: 10px;'>授权失败</h2>");
        }
        html.append("<div class='message'>").append(message).append("</div>");
        html.append("<p style='color: #999; font-size: 14px;'>窗口将在3秒后自动关闭...</p>");
        html.append("</div>");
        html.append("<script>");
        html.append("// 通知父窗口授权结果");
        html.append("if (window.opener) {");
        html.append("  window.opener.postMessage({");
        html.append("    type: 'tendoc_oauth_callback',");
        html.append("    success: ").append(success).append(",");
        html.append("    message: '").append(message.replace("'", "\\'").replace("\n", "\\n").replace("\r", "\\r")).append("'");
        html.append("  }, '*');");
        html.append("}");
        html.append("// 3秒后自动关闭窗口");
        html.append("setTimeout(function() {");
        html.append("  if (window.opener) {");
        html.append("    window.close();");
        html.append("  } else {");
        html.append("    window.location.href = '/';");
        html.append("  }");
        html.append("}, 3000);");
        html.append("</script>");
        html.append("</body>");
        html.append("</html>");
        return html.toString();
    }

    /**
     * 刷新访问令牌
     */
    @PostMapping("/refreshToken")
    public AjaxResult refreshToken(@RequestBody Map<String, String> params) {
        try {
            String refreshToken = params.get("refreshToken");
            if (refreshToken == null || refreshToken.isEmpty()) {
                return AjaxResult.error("refreshToken不能为空");
            }

            JSONObject tokenInfo = tencentDocService.refreshAccessToken(refreshToken);
            return AjaxResult.success("刷新令牌成功", tokenInfo);
        } catch (Exception e) {
            log.error("刷新访问令牌失败", e);
            return AjaxResult.error("刷新令牌失败: " + e.getMessage());
        }
    }

    /**
     * 将订单物流信息上传到腾讯文档表格
     */
    @PostMapping("/uploadLogistics")
    public AjaxResult uploadLogistics(@RequestBody Map<String, Object> params) {
        try {
            String accessToken = (String) params.get("accessToken");
            String fileId = (String) params.get("fileId");
            String sheetId = (String) params.get("sheetId");

            if (accessToken == null || fileId == null || sheetId == null) {
                return AjaxResult.error("accessToken、fileId和sheetId不能为空");
            }

            // 获取订单ID列表
            @SuppressWarnings("unchecked")
            List<Long> orderIds = (List<Long>) params.get("orderIds");

            if (orderIds == null || orderIds.isEmpty()) {
                return AjaxResult.error("订单ID列表不能为空");
            }

            // 查询订单信息
            List<JDOrder> orders = new java.util.ArrayList<>();
            for (Long orderId : orderIds) {
                JDOrder order = jdOrderService.selectJDOrderById(orderId);
                if (order != null) {
                    orders.add(order);
                }
            }

            if (orders.isEmpty()) {
                return AjaxResult.error("未找到有效的订单信息");
            }

            JSONObject result = tencentDocService.uploadLogisticsToSheet(accessToken, fileId, sheetId, orders);
            return AjaxResult.success("上传物流信息成功", result);
        } catch (Exception e) {
            log.error("上传物流信息失败", e);
            return AjaxResult.error("上传物流信息失败: " + e.getMessage());
        }
    }


    /**
     * 将单个订单的物流信息追加到表格
     */
    @PostMapping("/appendLogistics")
    public AjaxResult appendLogistics(@RequestBody Map<String, Object> params) {
        try {
            String accessToken = (String) params.get("accessToken");
            String fileId = (String) params.get("fileId");
            String sheetId = (String) params.get("sheetId");
            Long orderId = params.get("orderId") != null ? Long.valueOf(params.get("orderId").toString()) : null;
            // 起始行号，默认为3
            Integer startRow = params.get("startRow") != null ? Integer.valueOf(params.get("startRow").toString()) : 3;

            if (accessToken == null || fileId == null || sheetId == null || orderId == null) {
                return AjaxResult.error("accessToken、fileId、sheetId和orderId不能为空");
            }

            // 查询订单信息
            JDOrder order = jdOrderService.selectJDOrderById(orderId);
            if (order == null) {
                return AjaxResult.error("未找到订单信息");
            }

            JSONObject result = tencentDocService.appendLogisticsToSheet(accessToken, fileId, sheetId, startRow, order);
            return AjaxResult.success("追加物流信息成功", result);
        } catch (Exception e) {
            log.error("追加物流信息失败", e);
            return AjaxResult.error("追加物流信息失败: " + e.getMessage());
        }
    }

    /**
     * 读取表格数据
     */
    @GetMapping("/readSheet")
    public AjaxResult readSheet(@RequestParam("accessToken") String accessToken,
                                @RequestParam("fileId") String fileId,
                                @RequestParam("sheetId") String sheetId,
                                @RequestParam(value = "range", defaultValue = "A1:Z100") String range) {
        try {
            JSONObject result = tencentDocService.readSheetData(accessToken, fileId, sheetId, range);
            return AjaxResult.success("读取表格数据成功", result);
        } catch (Exception e) {
            log.error("读取表格数据失败", e);
            return AjaxResult.error("读取表格数据失败: " + e.getMessage());
        }
    }

    /**
     * 获取文件信息
     */
    @GetMapping("/fileInfo")
    public AjaxResult getFileInfo(@RequestParam("accessToken") String accessToken,
                                  @RequestParam("fileId") String fileId) {
        try {
            JSONObject result = tencentDocService.getFileInfo(accessToken, fileId);
            return AjaxResult.success("获取文件信息成功", result);
        } catch (Exception e) {
            log.error("获取文件信息失败", e);
            return AjaxResult.error("获取文件信息失败: " + e.getMessage());
        }
    }

    /**
     * 获取工作表列表
     */
    @GetMapping("/sheetList")
    public AjaxResult getSheetList(@RequestParam("accessToken") String accessToken,
                                   @RequestParam("fileId") String fileId) {
        try {
            JSONObject result = tencentDocService.getSheetList(accessToken, fileId);
            return AjaxResult.success("获取工作表列表成功", result);
        } catch (Exception e) {
            log.error("获取工作表列表失败", e);
            return AjaxResult.error("获取工作表列表失败: " + e.getMessage());
        }
    }

    /**
     * 自动发货 - 将指定订单的物流信息上传到腾讯文档并标记为已发货
     */
    @PostMapping("/autoShip")
    public AjaxResult autoShip(@RequestBody Map<String, Object> params) {
        try {
            String accessToken = (String) params.get("accessToken");
            String fileId = (String) params.get("fileId");
            String sheetId = (String) params.get("sheetId");
            Long orderId = params.get("orderId") != null ? Long.valueOf(params.get("orderId").toString()) : null;
            // 起始行号，默认为3
            Integer startRow = params.get("startRow") != null ? Integer.valueOf(params.get("startRow").toString()) : 3;

            if (accessToken == null || fileId == null || sheetId == null || orderId == null) {
                return AjaxResult.error("accessToken、fileId、sheetId和orderId不能为空");
            }

            // 查询订单信息
            JDOrder order = jdOrderService.selectJDOrderById(orderId);
            if (order == null) {
                return AjaxResult.error("未找到订单信息");
            }

            // 检查是否有物流链接
            if (order.getLogisticsLink() == null || order.getLogisticsLink().trim().isEmpty()) {
                return AjaxResult.error("订单缺少物流链接，无法自动发货");
            }

            // 上传物流信息到腾讯文档
            JSONObject uploadResult = tencentDocService.appendLogisticsToSheet(accessToken, fileId, sheetId, startRow, order);

            // 更新订单状态为已发货（可选）
            // order.setStatus("已发货");
            // jdOrderService.updateJDOrder(order);

            JSONObject result = new JSONObject();
            result.put("uploadResult", uploadResult);
            result.put("orderId", orderId);
            result.put("message", "物流信息已上传到腾讯文档，自动发货成功");

            return AjaxResult.success("自动发货成功", result);
        } catch (Exception e) {
            log.error("自动发货失败", e);
            return AjaxResult.error("自动发货失败: " + e.getMessage());
        }
    }

    /**
     * 直接填充单个订单的物流链接
     *
     * @param params 包含 thirdPartyOrderNo（第三方单号）和 logisticsLink（物流链接）
     * @return 填充结果
     */
    @PostMapping("/fillSingleLogistics")
    public AjaxResult fillSingleLogistics(@RequestBody Map<String, Object> params) {
        String thirdPartyOrderNo = null;
        String fileId = null;
        String sheetId = null;
        String lockKey = null;

        try {
            // 1. 获取参数
            thirdPartyOrderNo = (String) params.get("thirdPartyOrderNo");
            String logisticsLink = (String) params.get("logisticsLink");
            Boolean forceRePush = params.get("forceRePush") != null ? (Boolean) params.get("forceRePush") : false;

            if (thirdPartyOrderNo == null || thirdPartyOrderNo.isEmpty()) {
                return AjaxResult.error("第三方单号不能为空");
            }
            if (logisticsLink == null || logisticsLink.isEmpty()) {
                return AjaxResult.error("物流链接不能为空");
            }

            // 2. 检查订单是否已推送（防止重复推送）
            JDOrder order = jdOrderService.selectJDOrderByThirdPartyOrderNo(thirdPartyOrderNo);
            if (order == null) {
                logOperation(null, null, null, "WRITE_SINGLE", thirdPartyOrderNo, null, logisticsLink,
                    "FAILED", "订单不存在");
                return AjaxResult.error("订单不存在：" + thirdPartyOrderNo);
            }

            // 如果已推送且不是强制重推，则拒绝
            if (order.getTencentDocPushed() != null && order.getTencentDocPushed() == 1 && !forceRePush) {
                String pushTimeStr = order.getTencentDocPushTime() != null ?
                    new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(order.getTencentDocPushTime()) : "未知";
                logOperation(null, null, null, "WRITE_SINGLE", thirdPartyOrderNo, null, logisticsLink,
                    "SKIPPED", String.format("订单已推送，推送时间: %s", pushTimeStr));
                return AjaxResult.error(String.format("该订单已推送到腾讯文档（推送时间：%s），请勿重复操作！如需重新推送，请使用强制推送功能。", pushTimeStr));
            }

            // 3. 获取访问令牌
            String accessToken;
            try {
                accessToken = tencentDocTokenService.getValidAccessToken();
            } catch (Exception e) {
                logOperation(null, null, null, "WRITE_SINGLE", thirdPartyOrderNo, null, logisticsLink,
                    "FAILED", "访问令牌无效: " + e.getMessage());
                return AjaxResult.error("访问令牌无效，请先完成授权");
            }

            // 3. 从配置中读取文档信息
            final String CONFIG_KEY_PREFIX = "tencent:doc:auto:config:";
            fileId = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "fileId");
            sheetId = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "sheetId");
            Integer headerRow = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "headerRow");
            Integer configStartRow = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "startRow");

            if (fileId == null || fileId.isEmpty()) {
                fileId = tencentDocConfig.getFileId();
            }
            if (sheetId == null || sheetId.isEmpty()) {
                sheetId = tencentDocConfig.getSheetId();
            }
            if (headerRow == null) {
                headerRow = tencentDocConfig.getHeaderRow();
            }
            if (configStartRow == null) {
                configStartRow = tencentDocConfig.getStartRow();
            }

            if (fileId == null || fileId.isEmpty() || sheetId == null || sheetId.isEmpty()) {
                logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, null, logisticsLink,
                    "FAILED", "文档配置不完整");
                return AjaxResult.error("文档配置不完整，请先完成配置");
            }

            // 4. 获取分布式锁（针对该订单单号+文档+工作表）
            lockKey = TENCENT_DOC_LOCK_KEY + fileId + ":" + sheetId + ":" + thirdPartyOrderNo;

            // 检查锁是否已存在
            String existingLock = redisCache.getCacheObject(lockKey);
            boolean lockAcquired = (existingLock == null);

            if (!lockAcquired) {
                log.warn("获取锁失败，该订单正在被其他请求处理：{}", thirdPartyOrderNo);
                logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, null, logisticsLink,
                    "FAILED", "获取分布式锁失败，该订单正在处理中");
                return AjaxResult.error("该订单正在处理中，请稍后再试");
            }

            // 设置锁
            redisCache.setCacheObject(lockKey, "locked", 30, TimeUnit.SECONDS);
            log.info("✓ 获取分布式锁成功 - 单号: {}, lockKey: {}", thirdPartyOrderNo, lockKey);

            // 5. 读取表头（从配置的 headerRow 读取，与 startRow 独立）
            String headerRange = String.format("A%d:Z%d", headerRow, headerRow);

            JSONObject headerData = tencentDocService.readSheetData(accessToken, fileId, sheetId, headerRange);
            if (headerData == null || !headerData.containsKey("gridData")) {
                logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, null, logisticsLink,
                    "FAILED", "无法读取表头数据");
                return AjaxResult.error("无法读取表头数据");
            }

            // 6. 解析表头，找到"单号"和"物流单号"列
            JSONObject gridData = headerData.getJSONObject("gridData");
            JSONArray rows = gridData.getJSONArray("rows");
            if (rows == null || rows.isEmpty()) {
                logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, null, logisticsLink,
                    "FAILED", "表头数据为空");
                return AjaxResult.error("表头数据为空");
            }

            JSONObject headerRowData = rows.getJSONObject(0);
            JSONArray headerCells = headerRowData.getJSONArray("values");

            int orderNoColumn = -1;
            int logisticsColumn = -1;

            for (int i = 0; i < headerCells.size(); i++) {
                JSONObject cell = headerCells.getJSONObject(i);
                if (cell.containsKey("cellValue")) {
                    String cellText = cell.getJSONObject("cellValue").getString("text");
                    if (cellText != null) {
                        if (cellText.contains("单号")) {
                            orderNoColumn = i;
                        } else if (cellText.contains("物流")) {
                            logisticsColumn = i;
                        }
                    }
                }
            }

            if (orderNoColumn == -1 || logisticsColumn == -1) {
                logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, null, logisticsLink,
                    "FAILED", "未找到'单号'或'物流'列");
                return AjaxResult.error("未找到'单号'或'物流'列，请检查表头配置");
            }

            log.info("表头解析完成 - 单号列: {}, 物流列: {}", orderNoColumn, logisticsColumn);

            // 7. 读取数据区域，查找指定单号
            String dataRange = String.format("A%d:Z%d", configStartRow, configStartRow + 999);
            JSONObject data = tencentDocService.readSheetData(accessToken, fileId, sheetId, dataRange);

            if (data == null || !data.containsKey("gridData")) {
                logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, null, logisticsLink,
                    "FAILED", "无法读取数据区域");
                return AjaxResult.error("无法读取数据区域");
            }

            JSONObject dataGridData = data.getJSONObject("gridData");
            JSONArray dataRows = dataGridData.getJSONArray("rows");

            if (dataRows == null || dataRows.isEmpty()) {
                logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, null, logisticsLink,
                    "FAILED", "数据区域为空");
                return AjaxResult.error("数据区域为空");
            }

            // 8. 查找匹配的单号
            int targetRow = -1;
            for (int i = 0; i < dataRows.size(); i++) {
                JSONObject row = dataRows.getJSONObject(i);
                JSONArray cells = row.getJSONArray("values");

                if (cells != null && cells.size() > orderNoColumn) {
                    JSONObject orderNoCell = cells.getJSONObject(orderNoColumn);
                    if (orderNoCell.containsKey("cellValue")) {
                        String cellText = orderNoCell.getJSONObject("cellValue").getString("text");
                        if (thirdPartyOrderNo.equals(cellText)) {
                            targetRow = configStartRow + i;
                            break;
                        }
                    }
                }
            }

            if (targetRow == -1) {
                logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, null, logisticsLink,
                    "FAILED", "未找到单号");
                return AjaxResult.error("未找到单号: " + thirdPartyOrderNo);
            }

            log.info("找到单号 {} 在第 {} 行", thirdPartyOrderNo, targetRow);

            // 9. 再次读取该行数据，验证单号和物流链接列是否为空（防止并发覆盖）
            String verifyRange = String.format("A%d:Z%d", targetRow, targetRow);
            JSONObject verifyData = tencentDocService.readSheetData(accessToken, fileId, sheetId, verifyRange);

            if (verifyData == null || !verifyData.containsKey("gridData")) {
                logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, targetRow, logisticsLink,
                    "FAILED", "验证读取失败");
                return AjaxResult.error("验证读取失败");
            }

            JSONObject verifyGridData = verifyData.getJSONObject("gridData");
            JSONArray verifyRows = verifyGridData.getJSONArray("rows");

            if (verifyRows == null || verifyRows.isEmpty()) {
                logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, targetRow, logisticsLink,
                    "FAILED", "验证行数据为空");
                return AjaxResult.error("验证行数据为空");
            }

            JSONObject verifyRowData = verifyRows.getJSONObject(0);
            JSONArray verifyCells = verifyRowData.getJSONArray("values");

            // 验证单号是否仍然匹配
            if (verifyCells == null || verifyCells.size() <= orderNoColumn) {
                logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, targetRow, logisticsLink,
                    "SKIPPED", "验证时单号列为空，行已变化");
                return AjaxResult.error("验证失败：行数据已变化，单号列为空");
            }

            JSONObject verifyOrderNoCell = verifyCells.getJSONObject(orderNoColumn);
            String verifyOrderNo = null;
            if (verifyOrderNoCell.containsKey("cellValue")) {
                verifyOrderNo = verifyOrderNoCell.getJSONObject("cellValue").getString("text");
            }

            if (!thirdPartyOrderNo.equals(verifyOrderNo)) {
                logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, targetRow, logisticsLink,
                    "SKIPPED", String.format("验证失败：单号不匹配，期望 %s，实际 %s", thirdPartyOrderNo, verifyOrderNo));
                return AjaxResult.error("验证失败：单号不匹配，行数据已变化");
            }

            // 验证物流链接列是否为空
            if (verifyCells.size() > logisticsColumn) {
                JSONObject logisticsCell = verifyCells.getJSONObject(logisticsColumn);
                if (logisticsCell.containsKey("cellValue")) {
                    String existingLogistics = logisticsCell.getJSONObject("cellValue").getString("text");
                    if (existingLogistics != null && !existingLogistics.trim().isEmpty()) {
                        logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, targetRow, logisticsLink,
                            "SKIPPED", String.format("物流链接列已有值：%s", existingLogistics));
                        return AjaxResult.error(String.format("该订单物流链接已存在：%s", existingLogistics));
                    }
                }
            }

            log.info("✓ 验证通过 - 单号匹配且物流列为空，可以写入");

            // 10. 获取用户信息（获取 openId）
            JSONObject userInfo = com.ruoyi.jarvis.util.TencentDocApiUtil.getUserInfo(accessToken);
            JSONObject userData = userInfo.getJSONObject("data");
            if (userData == null) {
                logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, targetRow, logisticsLink,
                    "FAILED", "无法获取用户数据");
                return AjaxResult.error("无法获取用户数据");
            }
            String openId = userData.getString("openID");
            if (openId == null || openId.isEmpty()) {
                logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, targetRow, logisticsLink,
                    "FAILED", "无法获取Open-Id");
                return AjaxResult.error("无法获取Open-Id");
            }

            // 11. 写入物流链接
            com.ruoyi.jarvis.util.TencentDocApiUtil.writeSheetData(
                accessToken,
                tencentDocConfig.getAppId(),
                openId,
                fileId,
                sheetId,
                String.format("%s%d", getColumnLetter(logisticsColumn), targetRow),
                logisticsLink,
                tencentDocConfig.getApiBaseUrl()
            );

            // 12. 记录成功日志
            logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, targetRow, logisticsLink,
                "SUCCESS", null);

            log.info("✓ 物流链接写入成功 - 单号: {}, 行: {}, 链接: {}", thirdPartyOrderNo, targetRow, logisticsLink);

            // 13. 更新订单的推送状态（标记为已推送）
            try {
                order.setTencentDocPushed(1);
                order.setTencentDocPushTime(new java.util.Date());
                jdOrderService.updateJDOrder(order);
                log.info("✓ 订单推送状态已更新 - 单号: {}", thirdPartyOrderNo);
            } catch (Exception e) {
                log.error("更新订单推送状态失败，但写入腾讯文档已成功", e);
                // 不影响主流程，继续返回成功
            }

            // 14. 发送微信推送通知（手动执行）
            try {
                Map<String, Object> successLog = new java.util.HashMap<>();
                successLog.put("orderNo", thirdPartyOrderNo);
                successLog.put("row", targetRow);
                successLog.put("logisticsLink", logisticsLink);

                List<Map<String, Object>> successLogs = new java.util.ArrayList<>();
                successLogs.add(successLog);

                // 手动执行时，batchId为null
                sendWeChatNotification(successLogs, 1, 0, 0, null, fileId, sheetId, true);
            } catch (Exception e) {
                log.error("发送微信推送失败", e);
                // 不影响主流程，继续返回成功
            }

            JSONObject result = new JSONObject();
            result.put("thirdPartyOrderNo", thirdPartyOrderNo);
            result.put("logisticsLink", logisticsLink);
            result.put("row", targetRow);
            result.put("column", logisticsColumn);
            result.put("pushed", true);
            result.put("pushTime", order.getTencentDocPushTime());

            return AjaxResult.success("物流链接填充成功", result);

        } catch (Exception e) {
            log.error("填充物流链接失败", e);
            logOperation(null, fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, null, null,
                "FAILED", "异常: " + e.getMessage());
            return AjaxResult.error("填充物流链接失败: " + e.getMessage());
        } finally {
            // 释放分布式锁
            if (lockKey != null) {
                try {
                    redisCache.deleteObject(lockKey);
                    log.info("✓ 释放分布式锁 - lockKey: {}", lockKey);
                } catch (Exception e) {
                    log.error("释放分布式锁失败", e);
                }
            }
        }
    }

    /**
     * 记录操作日志到数据库
     */
    private void logOperation(String batchId, String fileId, String sheetId, String operationType,
                              String orderNo, Integer targetRow, String logisticsLink,
                              String status, String errorMessage) {
        try {
            com.ruoyi.jarvis.domain.TencentDocOperationLog log = new com.ruoyi.jarvis.domain.TencentDocOperationLog();
            log.setBatchId(batchId);
            log.setFileId(fileId);
            log.setSheetId(sheetId);
            log.setOperationType(operationType);
            log.setOrderNo(orderNo);
            log.setTargetRow(targetRow);
            log.setLogisticsLink(logisticsLink);
            log.setOperationStatus(status);
            log.setErrorMessage(errorMessage);
            log.setOperator(getUsername()); // 从BaseController继承的方法
            operationLogMapper.insertLog(log);
        } catch (Exception e) {
            TencentDocController.log.error("记录操作日志失败", e);
        }
    }

    /**
     * 查询操作日志列表
     */
    @GetMapping("/operationLogs")
    public AjaxResult getOperationLogs(com.ruoyi.jarvis.domain.TencentDocOperationLog query) {
        try {
            List<com.ruoyi.jarvis.domain.TencentDocOperationLog> list = operationLogMapper.selectLogList(query);
            return AjaxResult.success(list);
        } catch (Exception e) {
            log.error("查询操作日志失败", e);
            return AjaxResult.error("查询操作日志失败: " + e.getMessage());
        }
    }

    /**
     * 查询最近的操作日志
     */
    @GetMapping("/recentLogs")
    public AjaxResult getRecentLogs(@RequestParam(required = false) String fileId,
                                     @RequestParam(defaultValue = "50") int limit) {
        try {
            List<com.ruoyi.jarvis.domain.TencentDocOperationLog> list = operationLogMapper.selectRecentLogs(fileId, limit);
            return AjaxResult.success(list);
        } catch (Exception e) {
            log.error("查询最近操作日志失败", e);
            return AjaxResult.error("查询最近操作日志失败: " + e.getMessage());
        }
    }

    /**
     * 从文本中提取手机号码
     * 支持11位手机号码，可能包含空格、横线等分隔符
     *
     * @param text 文本信息（可以是备注、状态等字段）
     * @return 提取到的手机号码，如果没有则返回null
     */
    private String extractPhoneFromRemark(String text) {
        if (text == null || text.trim().isEmpty()) {
            log.debug("文本为空，无法提取手机号码");
            return null;
        }

        log.debug("原始文本: [{}]", text);

        // 移除所有空格、横线、括号等分隔符
        String cleanedText = text.replaceAll("[\\s\\-\\(\\)\\[\\]（）\\【\\】]", "");
        log.debug("清理后文本: [{}]", cleanedText);

        // 匹配11位手机号码（1开头的11位数字）
        java.util.regex.Pattern pattern = java.util.regex.Pattern.compile("1[3-9]\\d{9}");
        java.util.regex.Matcher matcher = pattern.matcher(cleanedText);

        if (matcher.find()) {
            String phone = matcher.group();
            log.info("成功提取手机号码: [{}] <- 原文本: [{}]", phone, text);
            return phone;
        }

        log.debug("未找到匹配的手机号码，文本: [{}]", text);
        return null;
    }

    /**
     * 批量同步物流链接 - 读取表格数据，根据单号查询订单系统中的物流链接，并填充到表格
     * 优化：记录上次处理的最大行数，每次从最大行数-100开始读取，避免重复处理历史数据
     * 自动获取和管理访问令牌，点击同步时自动刷新token
     */
    @Anonymous
    @PostMapping("/fillLogisticsByOrderNo")
    public AjaxResult fillLogisticsByOrderNo(@RequestBody Map<String, Object> params) {
        try {
            // 直接尝试刷新token（如果失败，说明需要首次授权）
            String accessToken;
            try {
                accessToken = tencentDocTokenService.refreshAccessToken();
                log.info("成功刷新访问令牌");
            } catch (Exception e) {
                log.error("刷新访问令牌失败", e);
                // 如果刷新失败，尝试获取缓存的token
                try {
                    accessToken = tencentDocTokenService.getValidAccessToken();
                } catch (Exception e2) {
                    return AjaxResult.error("访问令牌无效，请先完成授权。获取授权URL: GET /jarvis/tendoc/authUrl");
                }
            }

            // 从参数获取批次ID（如果是批量调用会传入）
            String batchId = params.get("batchId") != null ? String.valueOf(params.get("batchId")) : null;

            // 从参数或配置中获取文档信息
            String fileId = (String) params.get("fileId");
            String sheetId = (String) params.get("sheetId");

            // 如果前端没有传fileId/sheetId，则从配置中读取
            final String CONFIG_KEY_PREFIX = "tencent:doc:auto:config:";
            if (fileId == null || fileId.isEmpty()) {
                fileId = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "fileId");
                if (fileId == null || fileId.isEmpty()) {
                    fileId = tencentDocConfig.getFileId();
                }
            }
            if (sheetId == null || sheetId.isEmpty()) {
                sheetId = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "sheetId");
                if (sheetId == null || sheetId.isEmpty()) {
                    sheetId = tencentDocConfig.getSheetId();
                }
            }

            // 从配置中读取表头行和数据起始行
            Integer headerRow = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "headerRow");
            Integer configStartRow = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "startRow");

            if (headerRow == null) {
                headerRow = tencentDocConfig.getHeaderRow();
            }
            if (configStartRow == null) {
                configStartRow = tencentDocConfig.getStartRow();
            }

            // 可选参数：是否强制从指定行开始（如果为true，则忽略Redis记录的最大行数）
            Boolean forceStart = params.get("forceStart") != null ?
                Boolean.valueOf(params.get("forceStart").toString()) : false;
            Integer forceStartRow = params.get("forceStartRow") != null ?
                Integer.valueOf(params.get("forceStartRow").toString()) : configStartRow;

            // 新增参数：是否跳过已推送的订单（默认true，防止重复推送）
            Boolean skipPushedOrders = params.get("skipPushedOrders") != null ?
                Boolean.valueOf(params.get("skipPushedOrders").toString()) : true;

            if (accessToken == null || fileId == null || sheetId == null) {
                return AjaxResult.error("文档配置不完整，请先配置 fileId 和 sheetId");
            }

            // 如果batchId为空，创建一个新的批次ID和批量推送记录（用于日志记录）
            if (batchId == null || batchId.trim().isEmpty()) {
                // 计算起始行和结束行（用于创建批量推送记录）
                int estimatedStartRow = configStartRow != null ? configStartRow : (headerRow + 1);
                int estimatedEndRow = estimatedStartRow + 199;

                batchId = batchPushService.createBatchPushRecord(
                    fileId,
                    sheetId,
                    "MANUAL",  // 手动触发
                    "MANUAL_TRIGGER",  // 手动触发来源
                    estimatedStartRow,
                    estimatedEndRow
                );
                log.info("未提供batchId，自动创建新的批次ID和批量推送记录: {}", batchId);
            }

            log.info("同步物流配置 - fileId: {}, sheetId: {}, batchId: {}, 配置起始行: {}, 表头行: {}",
                fileId, sheetId, batchId, configStartRow, headerRow);

            // 生成Redis key，用于存储该文件的工作表的动态处理进度
            String redisKey = LAST_PROCESSED_ROW_KEY_PREFIX + fileId + ":" + sheetId;
            // 生成配置版本key，用于检测配置是否被重新设置
            String configVersionKey = "tencent:doc:sync:config_version:" + fileId + ":" + sheetId;
            String currentConfigVersion = configStartRow + "_" + headerRow; // 配置版本：起始行_表头行

            // 检查配置是否被更新（如果配置版本变化，则重置动态进度）
            String savedConfigVersion = redisCache.getCacheObject(configVersionKey);
            if (savedConfigVersion == null || !savedConfigVersion.equals(currentConfigVersion)) {
                // 配置已更新，清除旧的处理进度
                redisCache.deleteObject(redisKey);
                redisCache.setCacheObject(configVersionKey, currentConfigVersion, 180, TimeUnit.DAYS);
                log.info("检测到配置更新，重置同步进度 - 新配置版本: {}", currentConfigVersion);
            }

            // 获取动态处理进度（上次处理到的最大行数）
            Integer lastMaxRow = null;
            if (!forceStart && redisCache.hasKey(redisKey)) {
                Object cacheObj = redisCache.getCacheObject(redisKey);
                if (cacheObj != null) {
                    lastMaxRow = Integer.valueOf(cacheObj.toString());
                    log.info("读取到上次处理进度 - 最大行: {}", lastMaxRow);
                }
            }

            // 计算本次同步的起始行和结束行（带回溯机制）
            // 确保 configStartRow 不为 null
            int effectiveStartRow = configStartRow != null ? configStartRow : (headerRow + 1);

            int startRow;
            int endRow;

            if (forceStartRow != null) {
                // 强制指定行（手动指定）
                startRow = forceStartRow;
                endRow = startRow + 199; // 读取200行
                log.info("使用强制指定的起始行: {}, 结束行: {}", startRow, endRow);
            } else if (lastMaxRow != null && lastMaxRow >= effectiveStartRow) {
                // 计算回溯阈值：配置起始行 + 100
                int threshold = effectiveStartRow + 100;

                if (lastMaxRow <= (effectiveStartRow + 199)) {
                    // 进度较小（一批数据内），重新从配置起始行开始
                    startRow = effectiveStartRow;
                    endRow = effectiveStartRow + 199; // 读取200行
                    log.info("上次进度 {} 较小（≤{}），重新从第 {} 行开始，到第 {} 行",
                        lastMaxRow, effectiveStartRow + 199, startRow, endRow);
                } else if (lastMaxRow > threshold) {
                    // 进度较大（超过阈值）
                    // 起始行：往回退100行，但不能小于配置的起始行
                    startRow = Math.max(effectiveStartRow, lastMaxRow - 100);
                    // 结束行：从上次位置往前读100行
                    endRow = lastMaxRow + 100;
                    log.info("上次进度 {} 较大（>{}），回溯100行从第 {} 行开始，往前读100行到第 {} 行（范围：{} 行）",
                        lastMaxRow, threshold, startRow, endRow, endRow - startRow + 1);
                } else {
                    // 进度在阈值范围内，重新从配置起始行开始
                    startRow = effectiveStartRow;
                    endRow = effectiveStartRow + 199; // 读取200行
                    log.info("上次进度 {} 在阈值范围内（{}-{}），重新从第 {} 行开始，到第 {} 行",
                        lastMaxRow, effectiveStartRow + 200, threshold, startRow, endRow);
                }
            } else {
                // 首次同步或配置被重置，使用配置的起始行
                startRow = effectiveStartRow;
                endRow = effectiveStartRow + 199; // 读取200行
                log.info("首次同步或配置已重置，从配置的起始行开始: {}, 到第 {} 行", startRow, endRow);
            }

            log.info("开始填充物流链接 - 文件ID: {}, 工作表ID: {}, 起始行: {}, 结束行: {}, 上次最大行: {}",
                fileId, sheetId, startRow, endRow, lastMaxRow);

            // 读取表格数据（先读取表头行用于识别列位置）
            // 根据官方文档，使用 A1 表示法（Excel格式）
            // 参考：https://docs.qq.com/open/document/app/openapi/v3/sheet/get/get_range.html
            String headerRange = String.format("A%d:Z%d", headerRow, headerRow);  // 例如：A2:Z2
            log.info("读取表头 - 行号: {}, range: {}", headerRow, headerRange);

            JSONObject headerData = null;
            try {
                headerData = tencentDocService.readSheetData(accessToken, fileId, sheetId, headerRange);
                log.info("表头数据响应: {}", headerData != null ? headerData.toJSONString() : "null");
            } catch (Exception e) {
                log.error("读取表头失败", e);
                return AjaxResult.error("读取表头失败: " + e.getMessage());
            }

            if (headerData == null) {
                return AjaxResult.error("读取表头返回null，请检查Access Token是否有效或文档权限");
            }

            JSONArray headerValues = headerData.getJSONArray("values");

            if (headerValues == null || headerValues.isEmpty()) {
                log.error("表头数据中values数组为空，完整响应: {}", headerData.toJSONString());
                return AjaxResult.error("无法读取表头，请检查headerRow参数。API响应: " + headerData.toJSONString());
            }

            // 自动识别列位置（从表头中识别）
            Integer orderNoColumn = null;       // "单号"列
            Integer logisticsLinkColumn = null; // "物流单号"列
            Integer remarkColumn = null;        // "备注"列
            Integer arrangedColumn = null;      // "是否安排"列
            Integer markColumn = null;          // "标记"列
            Integer phoneColumn = null;         // "下单电话"列

            JSONArray headerRowData = headerValues.getJSONArray(0);
            if (headerRowData == null || headerRowData.isEmpty()) {
                return AjaxResult.error("无法识别表头，表头数据为空");
            }

            // 查找所有相关列
            log.info("开始识别表头列，共 {} 列", headerRowData.size());
            for (int i = 0; i < headerRowData.size(); i++) {
                String cellValue = headerRowData.getString(i);
                if (cellValue != null) {
                    String cellValueTrim = cellValue.trim();
                    log.debug("列 {} 内容: [{}]", i, cellValueTrim);

                    // 识别"单号"列
                    if (orderNoColumn == null && cellValueTrim.contains("单号")) {
                        orderNoColumn = i;
                        log.info("✓ 识别到 '单号' 列：第 {} 列（索引{}）", i + 1, i);
                    }

                    // 识别"物流单号"或"物流链接"列
                    if (logisticsLinkColumn == null && (cellValueTrim.contains("物流单号") || cellValueTrim.contains("物流链接"))) {
                        logisticsLinkColumn = i;
                        log.info("✓ 识别到 '物流单号' 列：第 {} 列（索引{}）", i + 1, i);
                    }

                    // 识别"备注"列（可选）
                    if (remarkColumn == null && cellValueTrim.contains("备注")) {
                        remarkColumn = i;
                        log.info("✓ 识别到 '备注' 列：第 {} 列（索引{}）", i + 1, i);
                    }

                    // 识别"是否安排"列（可选）
                    if (arrangedColumn == null && (cellValueTrim.contains("是否安排") || cellValueTrim.contains("安排"))) {
                        arrangedColumn = i;
                        log.info("✓ 识别到 '是否安排' 列：第 {} 列（索引{}）", i + 1, i);
                    }

                    // 识别"标记"列（可选）
                    if (markColumn == null && cellValueTrim.contains("标记")) {
                        markColumn = i;
                        log.info("✓ 识别到 '标记' 列：第 {} 列（索引{}）", i + 1, i);
                    }

                    // 识别"下单电话"列（可选）
                    if (phoneColumn == null && (cellValueTrim.contains("下单电话"))) {
                        phoneColumn = i;
                        log.info("✓ 识别到 '下单电话' 列：第 {} 列（索引{}），列名: [{}]", i + 1, i, cellValueTrim);
                    }
                }
            }
            log.info("表头列识别完成");

            // 检查必需的列是否都已识别
            if (orderNoColumn == null) {
                return AjaxResult.error("无法找到'单号'列，请检查表头是否包含'单号'字段");
            }
            if (logisticsLinkColumn == null) {
                return AjaxResult.error("无法找到'物流单号'或'物流链接'列，请检查表头");
            }

            // 提示可选列的识别情况
            if (remarkColumn == null) {
                log.warn("未找到'备注'列，将跳过该字段的更新");
            }
            if (arrangedColumn == null) {
                log.warn("未找到'是否安排'列，将跳过该字段的更新");
            }
            if (markColumn == null) {
                log.warn("未找到'标记'列，将跳过该字段的更新");
            }
            if (phoneColumn == null) {
                log.warn("未找到'下单电话'列，将跳过该字段的更新");
            }

            log.info("列位置识别完成 - 单号: {}, 物流单号: {}, 备注: {}, 是否安排: {}, 标记: {}, 下单电话: {}",
                orderNoColumn, logisticsLinkColumn, remarkColumn, arrangedColumn, markColumn, phoneColumn);

            // 读取数据行
            // 使用 A1 表示法（Excel格式）
            String range = String.format("A%d:Z%d", startRow, endRow);  // 例如：A3:Z203
            log.info("开始读取数据行 - 行号: {} ~ {}, range: {}", startRow, endRow, range);

            JSONObject sheetData = null;
            try {
                sheetData = tencentDocService.readSheetData(accessToken, fileId, sheetId, range);
                log.info("数据行读取成功，响应: {}", sheetData != null ? "有数据" : "null");
            } catch (Exception e) {
                log.error("读取数据行失败", e);
                return AjaxResult.error("读取数据行失败: " + e.getMessage());
            }

            if (sheetData == null) {
                return AjaxResult.error("读取数据行返回null");
            }

            JSONArray values = sheetData.getJSONArray("values");
            log.info("解析后的数据行数: {}", values != null ? values.size() : "null");

            if (values == null || values.isEmpty()) {
                log.warn("指定范围内没有数据，可能已处理完毕。range={}, sheetData keys={}",
                    range, sheetData.keySet());

                // 打印前10个键值对用于调试
                if (sheetData != null && !sheetData.isEmpty()) {
                    log.warn("sheetData内容预览: {}", sheetData.toJSONString().substring(0, Math.min(500, sheetData.toJSONString().length())));
                }

                JSONObject result = new JSONObject();
                result.put("startRow", startRow);
                result.put("endRow", endRow);
                result.put("lastMaxRow", lastMaxRow);
                result.put("filledCount", 0);
                result.put("skippedCount", 0);
                result.put("errorCount", 0);
                result.put("message", "指定范围内没有数据");
                result.put("range", range);
                result.put("hasSheetData", sheetData != null);
                result.put("sheetDataKeys", sheetData != null ? sheetData.keySet() : null);
                return AjaxResult.success("没有需要处理的数据", result);
            }

            log.info("成功读取 {} 行数据，开始处理...", values.size());

            // 处理数据行
            int filledCount = 0;
            int skippedCount = 0;
            int errorCount = 0;
            int successUpdates = 0; // 实际成功写入的数量
            int maxSuccessRow = 0; // 记录实际成功写入的最大行号

            // 收集同步成功的详细日志（用于微信推送）
            List<Map<String, Object>> successLogs = new java.util.ArrayList<>();

            JSONArray updates = new JSONArray(); // 存储需要更新的行和值

            for (int i = 0; i < values.size(); i++) {
                JSONArray row = values.getJSONArray(i);
                if (row == null || row.size() <= Math.max(orderNoColumn, logisticsLinkColumn)) {
                    continue; // 跳过空行或列数不足的行
                }

                // 计算实际的行号（Excel行号，从1开始）
                int excelRow = startRow + i;

                // 获取单号
                String orderNo = row.getString(orderNoColumn);
                if (orderNo == null || orderNo.trim().isEmpty()) {
                    skippedCount++;
                    continue; // 跳过空单号的行
                }

                orderNo = orderNo.trim();

                // 检查物流链接列是否已有值（可能是别人手动填写的）
                String existingLogisticsLink = row.getString(logisticsLinkColumn);
                if (existingLogisticsLink != null && !existingLogisticsLink.trim().isEmpty()) {
                    // 文档中已有物流链接，同步更新订单的推送状态
                    try {
                        JDOrder existingOrder = jdOrderService.selectJDOrderByThirdPartyOrderNo(orderNo);
                        if (existingOrder != null &&
                            (existingOrder.getTencentDocPushed() == null || existingOrder.getTencentDocPushed() == 0)) {
                            // 订单未标记为已推送，但文档中已有值，同步状态
                            existingOrder.setTencentDocPushed(1);
                            existingOrder.setTencentDocPushTime(new java.util.Date());
                            int syncResult = jdOrderService.updateJDOrder(existingOrder);
                            if (syncResult > 0) {
                                log.info("✓ 同步订单状态成功 - 单号: {}, 行号: {}, 原因: 文档中已有物流链接（可能手动填写）",
                                    orderNo, excelRow);
                            } else {
                                log.warn("⚠️ 同步订单状态返回0 - 单号: {}, 行号: {}, 可能未更新",
                                    orderNo, excelRow);
                            }

                            // 记录同步日志
                            logOperation(batchId, fileId, sheetId, "BATCH_SYNC", orderNo, excelRow, existingLogisticsLink,
                                "SKIPPED", "文档中已有物流链接，已同步订单状态");
                        }
                    } catch (Exception e) {
                        log.error("❌ 同步订单状态失败 - 单号: {}, 行号: {}", orderNo, excelRow, e);
                    }

                    skippedCount++; // 已有物流链接，跳过写入
                    continue;
                }

                try {
                    // 根据第三方单号查询订单
                    JDOrder order = jdOrderService.selectJDOrderByThirdPartyOrderNo(orderNo);

                    if (order == null) {
                        errorCount++;
                        log.warn("未找到订单 - 单号: {}, 行号: {}", orderNo, excelRow);
                        continue;
                    }

                    // 检查订单是否已推送（防止重复推送）
                    if (skipPushedOrders && order.getTencentDocPushed() != null && order.getTencentDocPushed() == 1) {
                        skippedCount++;
                        log.info("跳过已推送订单 - 单号: {}, 推送时间: {}, 行号: {}",
                            orderNo,
                            order.getTencentDocPushTime() != null ?
                                new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(order.getTencentDocPushTime()) : "未知",
                            excelRow);
                        continue;
                    }

                    if (order.getLogisticsLink() != null && !order.getLogisticsLink().trim().isEmpty()) {
                        String logisticsLink = order.getLogisticsLink().trim();

                        // 从status字段中提取手机号码
                        String phone = null;
                        if (phoneColumn != null) {
                            String statusValue = order.getStatus();
                            log.debug("准备从status字段提取手机号 - 单号: {}, status内容: [{}]", orderNo, statusValue);
                            phone = extractPhoneFromRemark(statusValue);
                            if (phone != null) {
                                log.info("✓ 从status字段提取手机号码 - 单号: {}, status: [{}], 手机号: {}", orderNo, statusValue, phone);
                            } else {
                                log.debug("未能从status字段提取手机号码 - 单号: {}, status: [{}]", orderNo, statusValue);
                            }
                        } else {
                            log.debug("phoneColumn为null，跳过手机号提取 - 单号: {}", orderNo);
                        }

                        // 构建更新请求
                        JSONObject update = new JSONObject();
                        update.put("row", excelRow);
                        update.put("column", logisticsLinkColumn);
                        update.put("orderNo", orderNo);
                        update.put("logisticsLink", logisticsLink);

                        // 如果找到手机号码，也添加到更新中
                        if (phone != null && phoneColumn != null) {
                            update.put("phone", phone);
                            update.put("phoneColumn", phoneColumn);
                        }

                        // 注意：不再保存order对象，写入成功后会重新查询以确保数据最新
                        updates.add(update);

                        filledCount++;
                        log.info("找到订单物流链接 - 单号: {}, 物流链接: {}, 手机号: {}, 行号: {}, 已推送: {}",
                            orderNo, logisticsLink, phone != null ? phone : "无", excelRow,
                            order.getTencentDocPushed() != null && order.getTencentDocPushed() == 1 ? "是" : "否");
                    } else {
                        errorCount++;
                        log.warn("订单物流链接为空 - 单号: {}, 行号: {}", orderNo, excelRow);
                    }
                } catch (Exception e) {
                    errorCount++;
                    log.error("处理订单失败 - 单号: {}, 行号: {}", orderNo, excelRow, e);
                }
            }

            // 批量更新表格
            if (!updates.isEmpty()) {
                // 获取今天的日期，格式：yyMMdd（如：251105）
                String today = new java.text.SimpleDateFormat("yyMMdd").format(new java.util.Date());

                // 将更新按行分组，批量写入
                Map<Integer, JSONObject> rowUpdates = new java.util.HashMap<>();
                for (int i = 0; i < updates.size(); i++) {
                    JSONObject update = updates.getJSONObject(i);
                    int row = update.getIntValue("row");
                    rowUpdates.put(row, update);
                }

                // 批量写入（每行单独写入，同时更新多个字段）
                // 在写入前，重新读取该行验证单号匹配和物流链接列为空
                // 重置成功计数（在循环外已定义）
                int currentBatchSuccessUpdates = 0;
                int currentBatchMaxSuccessRow = 0;
                for (Map.Entry<Integer, JSONObject> entry : rowUpdates.entrySet()) {
                    try {
                        int row = entry.getKey();
                        JSONObject update = entry.getValue();
                        String logisticsLink = update.getString("logisticsLink");
                        String expectedOrderNo = update.getString("orderNo");

                        // 重新读取该行数据，验证单号和物流链接列
                        String verifyRange = String.format("A%d:Z%d", row, row);
                        JSONObject verifyData = tencentDocService.readSheetData(accessToken, fileId, sheetId, verifyRange);

                        if (verifyData == null || !verifyData.containsKey("values")) {
                            log.warn("验证失败 - 无法读取行 {}", row);
                            errorCount++;
                            continue;
                        }

                        JSONArray verifyRows = verifyData.getJSONArray("values");
                        if (verifyRows == null || verifyRows.isEmpty()) {
                            log.warn("验证失败 - 行 {} 数据为空", row);
                            errorCount++;
                            continue;
                        }

                        JSONArray verifyRow = verifyRows.getJSONArray(0);
                        if (verifyRow == null || verifyRow.size() <= Math.max(orderNoColumn, logisticsLinkColumn)) {
                            log.warn("验证失败 - 行 {} 列数不足", row);
                            errorCount++;
                            continue;
                        }

                        // 验证1：单号是否匹配
                        String currentOrderNo = verifyRow.getString(orderNoColumn);
                        if (currentOrderNo == null || !currentOrderNo.trim().equals(expectedOrderNo)) {
                            log.warn("验证失败 - 行 {} 单号不匹配，预期: {}, 实际: {}", row, expectedOrderNo, currentOrderNo);
                            errorCount++;
                            continue;
                        }

                        // 验证2：物流链接列是否为空
                        String currentLogisticsLink = verifyRow.getString(logisticsLinkColumn);
                        if (currentLogisticsLink != null && !currentLogisticsLink.trim().isEmpty()) {
                            log.info("跳过写入 - 行 {} 单号 {} 的物流链接列已有值: {}", row, expectedOrderNo, currentLogisticsLink);
                            skippedCount++;
                            continue;
                        }

                        // 验证通过，执行写入
                        // 使用 batchUpdate 一次性更新多个字段
                        JSONArray requests = new JSONArray();

                        // 1. 更新物流单号（超链接类型）
                        requests.add(buildUpdateCellRequest(sheetId, row - 1, logisticsLinkColumn, logisticsLink, true));

                        // 2. 更新"下单电话"列（如果存在且提取到了手机号码）
                        String phone = update.getString("phone");
                        Integer phoneCol = update.getInteger("phoneColumn");
                        if (phone != null && phoneCol != null) {
                            requests.add(buildUpdateCellRequest(sheetId, row - 1, phoneCol, phone, false));
                            log.info("✓ 准备写入手机号码 - 单号: {}, 手机号: {}, 行: {}, 列: {}", expectedOrderNo, phone, row, phoneCol);
                        }

                        // 3. 更新"备注"列（如果存在，固定填写"有货"）
                        if (remarkColumn != null) {
                            requests.add(buildUpdateCellRequest(sheetId, row - 1, remarkColumn, "有货", false));
                            log.info("✓ 准备写入备注 - 单号: {}, 备注: 有货, 行: {}, 列: {}", expectedOrderNo, row, remarkColumn);
                        }

                        // 4. 更新"是否安排"列（如果存在，固定填写"2"）
                        if (arrangedColumn != null) {
                            requests.add(buildUpdateCellRequest(sheetId, row - 1, arrangedColumn, "2", false));
                            log.info("✓ 准备写入是否安排 - 单号: {}, 是否安排: 2, 行: {}, 列: {}", expectedOrderNo, row, arrangedColumn);
                        }

                        // 5. 更新"标记"列（如果存在）
                        if (markColumn != null) {
                            requests.add(buildUpdateCellRequest(sheetId, row - 1, markColumn, today, false));
                        }

                        // 构建完整的 batchUpdate 请求体
                        JSONObject batchUpdateBody = new JSONObject();
                        batchUpdateBody.put("requests", requests);

                        // 调用 batchUpdate API
                        tencentDocService.batchUpdate(accessToken, fileId, batchUpdateBody);
                        currentBatchSuccessUpdates++;
                        // 更新实际成功写入的最大行号
                        if (row > currentBatchMaxSuccessRow) {
                            currentBatchMaxSuccessRow = row;
                        }

                        String logMsg = String.format("✓ 写入成功 - 行: %d, 单号: %s, 物流链接: %s", row, expectedOrderNo, logisticsLink);
                        if (phone != null) {
                            logMsg += String.format(", 手机号: %s", phone);
                        }
                        log.info(logMsg);

                        // 更新订单的推送状态（重新查询订单，避免使用旧对象）
                        try {
                            JDOrder orderToUpdate = jdOrderService.selectJDOrderByThirdPartyOrderNo(expectedOrderNo);
                            if (orderToUpdate != null) {
                                orderToUpdate.setTencentDocPushed(1);
                                orderToUpdate.setTencentDocPushTime(new java.util.Date());
                                int updateResult = jdOrderService.updateJDOrder(orderToUpdate);
                                if (updateResult > 0) {
                                    log.info("✓ 订单推送状态已更新 - 单号: {}, updateResult: {}", expectedOrderNo, updateResult);
                                } else {
                                    log.warn("⚠️ 订单推送状态更新返回0 - 单号: {}, 可能未更新", expectedOrderNo);
                                }
                            } else {
                                log.warn("⚠️ 重新查询订单失败 - 单号: {}", expectedOrderNo);
                            }
                        } catch (Exception e) {
                            log.error("❌ 更新订单推送状态失败（但写入腾讯文档已成功） - 单号: {}", expectedOrderNo, e);
                            // 不影响主流程，继续执行
                        }

                        // 记录操作日志
                        logOperation(batchId, fileId, sheetId, "BATCH_SYNC", expectedOrderNo, row, logisticsLink,
                            "SUCCESS", null);

                        // 收集成功日志信息（用于微信推送）
                        Map<String, Object> successLog = new java.util.HashMap<>();
                        successLog.put("orderNo", expectedOrderNo);
                        successLog.put("row", row);
                        successLog.put("logisticsLink", logisticsLink);
                        if (phone != null) {
                            successLog.put("phone", phone);
                        }
                        successLogs.add(successLog);
                    } catch (Exception e) {
                        log.error("写入数据失败 - 行: {}", entry.getKey(), e);
                        errorCount++;

                        // 记录失败日志
                        String orderNo = entry.getValue().getString("orderNo");
                        String logisticsLink = entry.getValue().getString("logisticsLink");
                        logOperation(batchId, fileId, sheetId, "BATCH_SYNC", orderNo, entry.getKey(), logisticsLink,
                            "FAILED", "写入异常: " + e.getMessage());
                    }

                    // 添加延迟，避免API调用频率过高
                    try {
                        Thread.sleep(100); // 100ms延迟
                    } catch (InterruptedException e) {
                        Thread.currentThread().interrupt();
                    }
                }

                // 更新全局成功计数
                successUpdates = currentBatchSuccessUpdates;
                maxSuccessRow = currentBatchMaxSuccessRow;

                log.info("批量填充物流链接完成 - 成功: {}, 跳过: {}, 错误: {}, 最大成功行号: {}",
                    successUpdates, skippedCount, errorCount, maxSuccessRow > 0 ? maxSuccessRow : "无");
            }

            // 根据填充结果决定下次的起始位置
            // 使用实际成功写入的最大行号，如果没有成功写入，则使用 endRow
            int currentMaxRow = (maxSuccessRow > 0) ? maxSuccessRow : endRow;
            int nextStartRow;
            String nextSyncHint;

            // 使用 successUpdates（实际成功写入数量）而不是 filledCount（准备写入数量）
            if (successUpdates > 0) {
                // 有数据实际写入成功，说明当前范围有有效数据，继续往前推进
                // 下次从 currentMaxRow - 100 开始（回溯100行，防止遗漏）
                nextStartRow = Math.max(effectiveStartRow, currentMaxRow - 100);
                nextSyncHint = String.format("下次将从第 %d 行开始（本次实际写入成功 %d 条，最大行号: %d，回溯100行防止遗漏）",
                    nextStartRow, successUpdates, currentMaxRow);

                // 记录进度到实际成功写入的最大行号，持续往前
                redisCache.setCacheObject(redisKey, currentMaxRow, 30, TimeUnit.DAYS);
                log.info("========== 更新Redis进度 ==========");
                log.info("本次实际写入成功 {} 条，最大成功行号: {}，更新进度到第 {} 行，下次从第 {} 行开始",
                    successUpdates, maxSuccessRow, currentMaxRow, nextStartRow);
                log.info("Redis Key: {}", redisKey);
            } else {
                // 没有数据实际写入成功，说明这个范围都是空行或已处理的数据
                if (lastMaxRow != null && currentMaxRow > (lastMaxRow + 300)) {
                    // 如果跳跃太大（>300行），可能已经超出数据区域，回到配置起始行重新扫描
                    nextStartRow = effectiveStartRow;
                    nextSyncHint = String.format("下次将从第 %d 行重新开始（本次范围 %d-%d 无实际写入，重新扫描）",
                        nextStartRow, startRow, endRow);

                    // 不更新 Redis 进度，保持原有进度
                    log.info("本次无实际写入，且跳跃过大（{} > {} + 300），下次将从配置起始行 {} 重新开始",
                        currentMaxRow, lastMaxRow, effectiveStartRow);
                } else {
                    // 跳跃不大，继续往前推进
                    nextStartRow = Math.max(effectiveStartRow, currentMaxRow - 100);
                    nextSyncHint = String.format("下次将从第 %d 行开始（本次范围 %d-%d 无实际写入，继续往前）",
                        nextStartRow, startRow, endRow);

                    // 记录进度到 endRow（即使没有写入，也要记录扫描进度）
                    redisCache.setCacheObject(redisKey, currentMaxRow, 30, TimeUnit.DAYS);
                    log.info("========== 更新Redis进度 ==========");
                    log.info("本次无实际写入，更新扫描进度到第 {} 行，下次从第 {} 行继续",
                        currentMaxRow, nextStartRow);
                    log.info("Redis Key: {}", redisKey);
                }
            }

            JSONObject result = new JSONObject();
            result.put("startRow", startRow);
            result.put("endRow", endRow);
            result.put("currentMaxRow", currentMaxRow);
            result.put("lastMaxRow", lastMaxRow);
            result.put("nextStartRow", nextStartRow);
            result.put("filledCount", filledCount);
            result.put("skippedCount", skippedCount);
            result.put("errorCount", errorCount);
            result.put("orderNoColumn", orderNoColumn);
            result.put("logisticsLinkColumn", logisticsLinkColumn);
            result.put("skipPushedOrders", skipPushedOrders); // 是否跳过已推送订单

            // 构建详细的提示信息
            String message = String.format(
                "✓ 同步完成：成功填充 %d 条，跳过 %d 条，错误 %d 条\n" +
                "  本次范围：第 %d-%d 行\n" +
                "  当前进度：第 %d 行\n" +
                "  防重推送：%s\n" +
                "  %s",
                filledCount, skippedCount, errorCount,
                startRow, currentMaxRow,
                currentMaxRow,
                skipPushedOrders ? "已启用（跳过已推送订单）" : "已禁用",
                nextSyncHint
            );
            result.put("message", message);

            // 更新批量推送记录状态（batchId 应该总是存在，因为如果没有会创建）
            if (batchId != null && !batchId.trim().isEmpty()) {
                try {
                    String status = "SUCCESS";
                    // 使用实际成功写入数量 successUpdates 而不是 filledCount
                    if (errorCount > 0 && successUpdates == 0) {
                        status = "FAILED";
                    } else if (errorCount > 0) {
                        status = "PARTIAL_SUCCESS";
                    }
                    batchPushService.updateBatchPushRecord(batchId, status, successUpdates, skippedCount, errorCount,
                        message, null);
                    log.info("✓ 批量推送记录已更新 - batchId: {}, status: {}, 实际成功: {}, 跳过: {}, 错误: {}",
                        batchId, status, successUpdates, skippedCount, errorCount);
                } catch (Exception e) {
                    log.error("更新批量推送记录失败", e);
                    // 不影响主流程，继续执行
                }
            } else {
                log.warn("⚠️ batchId 为空，无法更新批量推送记录");
            }

            // 如果有成功记录，发送微信推送
            if (!successLogs.isEmpty()) {
                try {
                    // 传递batchId给推送方法，用于日志关联
                    sendWeChatNotificationWithBatchId(successLogs, filledCount, skippedCount, errorCount, batchId, fileId, sheetId, false);
                } catch (Exception e) {
                    log.error("发送微信推送失败", e);
                    // 不影响主流程，继续返回成功
                }
            }

            return AjaxResult.success("填充物流链接完成", result);
        } catch (Exception e) {
            log.error("填充物流链接失败", e);
            return AjaxResult.error("填充物流链接失败: " + e.getMessage());
        }
    }

    /**
     * 构建单个单元格的更新请求（用于 batchUpdate）- 普通文本
     *
     * @param sheetId 工作表ID
     * @param rowIndex 行索引（从0开始）
     * @param columnIndex 列索引（从0开始）
     * @param value 要写入的值
     * @return updateRangeRequest 对象
     */
    private JSONObject buildUpdateCellRequest(String sheetId, int rowIndex, int columnIndex, String value) {
        return buildUpdateCellRequest(sheetId, rowIndex, columnIndex, value, false);
    }

    /**
     * 构建单个单元格的更新请求（用于 batchUpdate）- 支持超链接
     *
     * @param sheetId 工作表ID
     * @param rowIndex 行索引（从0开始）
     * @param columnIndex 列索引（从0开始）
     * @param value 要写入的值（文本或链接URL）
     * @param isLink 是否为超链接类型
     * @return updateRangeRequest 对象
     */
    private JSONObject buildUpdateCellRequest(String sheetId, int rowIndex, int columnIndex, String value, boolean isLink) {
        // 构建 updateRangeRequest
        JSONObject updateRangeRequest = new JSONObject();
        updateRangeRequest.put("sheetId", sheetId);

        // 构建 gridData
        JSONObject gridData = new JSONObject();
        gridData.put("startRow", rowIndex);
        gridData.put("startColumn", columnIndex);

        // 构建 rows 数组
        JSONArray rows = new JSONArray();
        JSONObject rowData = new JSONObject();
        JSONArray cellValues = new JSONArray();

        // 构建单元格数据
        JSONObject cellData = new JSONObject();
        JSONObject cellValue = new JSONObject();

        if (isLink) {
            // 写入超链接类型（url 和 text 都设置为相同的值）
            JSONObject link = new JSONObject();
            link.put("url", value);
            link.put("text", value);
            cellValue.put("link", link);
        } else {
            // 写入普通文本
            cellValue.put("text", value);
        }

        cellData.put("cellValue", cellValue);
        cellValues.add(cellData);

        rowData.put("values", cellValues);
        rows.add(rowData);
        gridData.put("rows", rows);

        updateRangeRequest.put("gridData", gridData);

        // 包装为 request 对象
        JSONObject request = new JSONObject();
        request.put("updateRangeRequest", updateRangeRequest);

        return request;
    }

    /**
     * 将列索引转换为Excel列字母（0->A, 1->B, ..., 25->Z, 26->AA, ...）
     */
    private String getColumnLetter(int columnIndex) {
        StringBuilder sb = new StringBuilder();
        columnIndex++; // 转换为1-based（A=1, B=2, ...）
        while (columnIndex > 0) {
            columnIndex--;
            sb.insert(0, (char)('A' + (columnIndex % 26)));
            columnIndex /= 26;
        }
        return sb.toString();
    }

    /**
     * 获取批量推送记录列表（带操作日志）
     */
    @GetMapping("/batchPushRecords")
    public AjaxResult getBatchPushRecords(@RequestParam(required = false) String fileId,
                                           @RequestParam(required = false) String sheetId,
                                           @RequestParam(defaultValue = "10") Integer limit) {
        try {
            // 如果没有指定文件ID，使用配置的默认值
            if (fileId == null || fileId.trim().isEmpty()) {
                fileId = tencentDocConfig.getFileId();
            }
            if (sheetId == null || sheetId.trim().isEmpty()) {
                sheetId = tencentDocConfig.getSheetId();
            }

            List<com.ruoyi.jarvis.domain.TencentDocBatchPushRecord> records =
                batchPushService.getBatchPushRecordListWithLogs(fileId, sheetId, limit);

            return AjaxResult.success(records);
        } catch (Exception e) {
            log.error("查询批量推送记录失败", e);
            return AjaxResult.error("查询批量推送记录失败: " + e.getMessage());
        }
    }

    /**
     * 获取批量推送记录详情（包含操作日志）
     */
    @GetMapping("/batchPushRecord/{batchId}")
    public AjaxResult getBatchPushRecordDetail(@PathVariable String batchId) {
        try {
            com.ruoyi.jarvis.domain.TencentDocBatchPushRecord record =
                batchPushService.getBatchPushRecord(batchId);

            if (record == null) {
                return AjaxResult.error("未找到批次记录");
            }

            return AjaxResult.success(record);
        } catch (Exception e) {
            log.error("查询批量推送记录详情失败", e);
            return AjaxResult.error("查询批量推送记录详情失败: " + e.getMessage());
        }
    }

    /**
     * 获取推送状态和倒计时信息
     */
    @GetMapping("/pushStatus")
    public AjaxResult getPushStatus() {
        try {
            Map<String, Object> status = batchPushService.getPushStatusAndCountdown();

            // 添加最后一次成功的推送记录信息
            String fileId = tencentDocConfig.getFileId();
            String sheetId = tencentDocConfig.getSheetId();

            if (fileId != null && sheetId != null) {
                com.ruoyi.jarvis.domain.TencentDocBatchPushRecord lastSuccess =
                    batchPushService.getLastSuccessRecord(fileId, sheetId);
                status.put("lastSuccessRecord", lastSuccess);
            }

            return AjaxResult.success(status);
        } catch (Exception e) {
            log.error("查询推送状态失败", e);
            return AjaxResult.error("查询推送状态失败: " + e.getMessage());
        }
    }

    /**
     * 手动触发立即推送
     */
    @PostMapping("/triggerPushNow")
    public AjaxResult triggerPushNow() {
        try {
            delayedPushService.executePushNow();
            return AjaxResult.success("推送已触发");
        } catch (Exception e) {
            log.error("触发推送失败", e);
            return AjaxResult.error("触发推送失败: " + e.getMessage());
        }
    }

    /**
     * 取消待推送任务
     */
    @PostMapping("/cancelPendingPush")
    public AjaxResult cancelPendingPush() {
        try {
            delayedPushService.cancelPendingPush();
            return AjaxResult.success("待推送任务已取消");
        } catch (Exception e) {
            log.error("取消待推送任务失败", e);
            return AjaxResult.error("取消待推送任务失败: " + e.getMessage());
        }
    }

    /**
     * 反向同步第三方单号
     * 从腾讯文档的物流单号列读取链接，通过链接匹配本地订单，将腾讯文档的单号列值写入到订单的第三方单号字段
     *
     * @param params 包含 fileId, sheetId, startRow（起始行，默认850）
     * @return 同步结果
     */
    @PostMapping("/reverseSyncThirdPartyOrderNo")
    public AjaxResult reverseSyncThirdPartyOrderNo(@RequestBody Map<String, Object> params) {
        String batchId = java.util.UUID.randomUUID().toString().replace("-", "");

        try {
            // 获取访问令牌
            String accessToken;
            try {
                accessToken = tencentDocTokenService.refreshAccessToken();
                log.info("成功刷新访问令牌");
            } catch (Exception e) {
                log.error("刷新访问令牌失败", e);
                try {
                    accessToken = tencentDocTokenService.getValidAccessToken();
                } catch (Exception e2) {
                    return AjaxResult.error("访问令牌无效，请先完成授权。获取授权URL: GET /jarvis/tendoc/authUrl");
                }
            }

            // 从参数或配置中获取文档信息
            String fileId = (String) params.get("fileId");
            String sheetId = (String) params.get("sheetId");

            final String CONFIG_KEY_PREFIX = "tencent:doc:auto:config:";
            if (fileId == null || fileId.isEmpty()) {
                fileId = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "fileId");
                if (fileId == null || fileId.isEmpty()) {
                    fileId = tencentDocConfig.getFileId();
                }
            }
            if (sheetId == null || sheetId.isEmpty()) {
                sheetId = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "sheetId");
                if (sheetId == null || sheetId.isEmpty()) {
                    sheetId = tencentDocConfig.getSheetId();
                }
            }

            // 从配置中读取表头行
            Integer headerRow = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "headerRow");
            if (headerRow == null) {
                headerRow = tencentDocConfig.getHeaderRow();
            }

            // 起始行，默认850
            Integer startRow = params.get("startRow") != null ?
                Integer.valueOf(params.get("startRow").toString()) : 850;

            // 结束行，默认到2500行
            Integer endRow = params.get("endRow") != null ?
                Integer.valueOf(params.get("endRow").toString()) : 2500;

            // 记录接收到的参数
            log.info("接收到参数 - startRow: {}, endRow: {}, params: {}", startRow, endRow, params);

            // 如果 endRow 小于 startRow + 1000，可能是前端传错了，强制设置为 2500
            if (endRow < startRow + 1000) {
                log.warn("检测到 endRow ({}) 可能不正确，强制设置为 2500", endRow);
                endRow = 2500;
            }

            if (accessToken == null || fileId == null || sheetId == null) {
                return AjaxResult.error("文档配置不完整，请先配置 fileId 和 sheetId");
            }

            log.info("反向同步第三方单号开始 - fileId: {}, sheetId: {}, 起始行: {}, 结束行: {}",
                fileId, sheetId, startRow, endRow);

            // 读取表头，识别列位置
            String headerRange = String.format("A%d:Z%d", headerRow, headerRow);
            JSONObject headerData = tencentDocService.readSheetData(accessToken, fileId, sheetId, headerRange);

            if (headerData == null || !headerData.containsKey("values")) {
                return AjaxResult.error("读取表头失败");
            }

            JSONArray headerValues = headerData.getJSONArray("values");
            if (headerValues == null || headerValues.isEmpty()) {
                return AjaxResult.error("表头数据为空");
            }

            JSONArray headerRowData = headerValues.getJSONArray(0);
            if (headerRowData == null || headerRowData.isEmpty()) {
                return AjaxResult.error("无法识别表头");
            }

            // 识别列位置
            Integer orderNoColumn = null;        // "单号"列
            Integer logisticsLinkColumn = null;  // "物流单号"列

            for (int i = 0; i < headerRowData.size(); i++) {
                String cellValue = headerRowData.getString(i);
                if (cellValue != null) {
                    String cellValueTrim = cellValue.trim();

                    if (orderNoColumn == null && cellValueTrim.contains("单号") && !cellValueTrim.contains("物流")) {
                        orderNoColumn = i;
                        log.info("✓ 识别到 '单号' 列：第 {} 列（索引{}）", i + 1, i);
                    }

                    if (logisticsLinkColumn == null && (cellValueTrim.contains("物流单号") || cellValueTrim.contains("物流链接"))) {
                        logisticsLinkColumn = i;
                        log.info("✓ 识别到 '物流单号' 列：第 {} 列（索引{}）", i + 1, i);
                    }
                }
            }

            if (orderNoColumn == null || logisticsLinkColumn == null) {
                return AjaxResult.error("无法识别表头列，请确保表头包含'单号'和'物流单号'列");
            }

            // 统计结果
            int successCount = 0;
            int skippedCount = 0;
            int errorCount = 0;

            // 分批读取数据，每批200行（避免单次读取过多数据导致API限制）
            final int BATCH_SIZE = 200;
            int currentStartRow = startRow;
            int totalBatches = (int) Math.ceil((double)(endRow - startRow + 1) / BATCH_SIZE);
            int currentBatch = 0;

            log.info("开始分批处理，共 {} 批，每批 {} 行", totalBatches, BATCH_SIZE);

            while (currentStartRow <= endRow) {
                currentBatch++;
                int currentEndRow = Math.min(currentStartRow + BATCH_SIZE - 1, endRow);

                log.info("正在处理第 {}/{} 批：第 {} 行到第 {} 行", currentBatch, totalBatches, currentStartRow, currentEndRow);

                // 读取当前批次的数据行
                String dataRange = String.format("A%d:Z%d", currentStartRow, currentEndRow);
                log.info("读取数据范围: {}", dataRange);

                JSONObject dataResponse = null;
                try {
                    dataResponse = tencentDocService.readSheetData(accessToken, fileId, sheetId, dataRange);
                } catch (Exception e) {
                    log.error("读取第 {} 批数据失败（{} - {} 行）", currentBatch, currentStartRow, currentEndRow, e);
                    errorCount += (currentEndRow - currentStartRow + 1);
                    // 继续处理下一批
                    currentStartRow = currentEndRow + 1;
                    continue;
                }

                if (dataResponse == null || !dataResponse.containsKey("values")) {
                    log.warn("第 {} 批数据读取返回空（{} - {} 行），跳过", currentBatch, currentStartRow, currentEndRow);
                    currentStartRow = currentEndRow + 1;
                    continue;
                }

                JSONArray rows = dataResponse.getJSONArray("values");
                if (rows == null || rows.isEmpty()) {
                    log.info("第 {} 批数据为空（{} - {} 行），跳过", currentBatch, currentStartRow, currentEndRow);
                    currentStartRow = currentEndRow + 1;
                    continue;
                }

                log.info("第 {} 批读取到 {} 行数据", currentBatch, rows.size());

                // 处理当前批次的每一行
                for (int rowIndex = 0; rowIndex < rows.size(); rowIndex++) {
                    JSONArray row = rows.getJSONArray(rowIndex);
                    if (row == null || row.size() <= Math.max(orderNoColumn, logisticsLinkColumn)) {
                        skippedCount++;
                        continue;
                    }

                    int actualRow = currentStartRow + rowIndex;
                    // 确保不超过结束行
                    if (actualRow > endRow) {
                        break;
                    }

                    String logisticsLink = row.getString(logisticsLinkColumn);
                    String orderNoFromDoc = row.getString(orderNoColumn);

                    // 跳过物流链接为空的行
                    if (logisticsLink == null || logisticsLink.trim().isEmpty()) {
                        log.debug("跳过第 {} 行：物流链接为空", actualRow);
                        skippedCount++;
                        logOperation(batchId, fileId, sheetId, "REVERSE_SYNC", null, actualRow, null,
                            "SKIPPED", "物流链接为空");
                        continue;
                    }

                    // 跳过单号为空的行
                    if (orderNoFromDoc == null || orderNoFromDoc.trim().isEmpty()) {
                        log.debug("跳过第 {} 行：单号为空", actualRow);
                        skippedCount++;
                        logOperation(batchId, fileId, sheetId, "REVERSE_SYNC", null, actualRow, logisticsLink,
                            "SKIPPED", "单号为空");
                        continue;
                    }

                    // 清理物流链接（去除空格、换行符、中文等）
                    logisticsLink = cleanLogisticsLink(logisticsLink);

                    try {
                        // 通过物流链接查找订单
                        JDOrder order = jdOrderService.selectJDOrderByLogisticsLink(logisticsLink);

                        if (order == null) {
                            log.warn("未找到匹配的订单 - 行: {}, 物流链接: {}", actualRow, logisticsLink);
                            errorCount++;
                            logOperation(batchId, fileId, sheetId, "REVERSE_SYNC", null, actualRow, logisticsLink,
                                "FAILED", "未找到匹配的订单");
                            continue;
                        }

                        // 检查订单是否已有第三方单号（如果已有且与文档中的不同，跳过）
                        if (order.getThirdPartyOrderNo() != null && !order.getThirdPartyOrderNo().trim().isEmpty()) {
                            if (!order.getThirdPartyOrderNo().trim().equals(orderNoFromDoc.trim())) {
                                log.info("跳过第 {} 行：订单已有第三方单号且不同 - 现有: {}, 文档: {}",
                                    actualRow, order.getThirdPartyOrderNo(), orderNoFromDoc);
                                skippedCount++;
                                logOperation(batchId, fileId, sheetId, "REVERSE_SYNC", order.getRemark(), actualRow, logisticsLink,
                                    "SKIPPED", "订单已有第三方单号且不同");
                                continue;
                            }
                            // 如果相同，继续执行（清除物流链接）
                        }

                        // 更新订单的第三方单号
                        order.setThirdPartyOrderNo(orderNoFromDoc.trim());
                        int updateResult = jdOrderService.updateJDOrder(order);

                        if (updateResult <= 0) {
                            log.error("更新订单失败 - 行: {}, 订单ID: {}, 单号: {}",
                                actualRow, order.getId(), order.getRemark());
                            errorCount++;
                            logOperation(batchId, fileId, sheetId, "REVERSE_SYNC", order.getRemark(), actualRow, logisticsLink,
                                "FAILED", "更新订单失败");
                            continue;
                        }

                        log.info("✓ 更新订单成功 - 行: {}, 订单: {}, 第三方单号: {}",
                            actualRow, order.getRemark(), orderNoFromDoc);

                        successCount++;

                        // 记录成功日志
                        logOperation(batchId, fileId, sheetId, "REVERSE_SYNC", order.getRemark(), actualRow, logisticsLink,
                            "SUCCESS", String.format("已将单号 %s 写入订单的第三方单号字段", orderNoFromDoc));

                    } catch (Exception e) {
                        log.error("处理第 {} 行失败", actualRow, e);
                        errorCount++;
                        logOperation(batchId, fileId, sheetId, "REVERSE_SYNC", null, actualRow, logisticsLink,
                            "FAILED", "处理异常: " + e.getMessage());
                    }

                    // 添加延迟，避免API调用频率过高
                    try {
                        Thread.sleep(100);
                    } catch (InterruptedException e) {
                        Thread.currentThread().interrupt();
                    }
                }

                log.info("第 {}/{} 批处理完成，当前统计 - 成功: {}, 跳过: {}, 错误: {}",
                    currentBatch, totalBatches, successCount, skippedCount, errorCount);

                // 移动到下一批
                currentStartRow = currentEndRow + 1;

                // 批次之间的延迟，避免API调用频率过高
                if (currentStartRow <= endRow) {
                    try {
                        Thread.sleep(200); // 批次之间延迟200ms
                    } catch (InterruptedException e) {
                        Thread.currentThread().interrupt();
                    }
                }
            }

            JSONObject result = new JSONObject();
            result.put("batchId", batchId);
            result.put("startRow", startRow);
            result.put("endRow", endRow);
            result.put("successCount", successCount);
            result.put("skippedCount", skippedCount);
            result.put("errorCount", errorCount);

            String message = String.format(
                "✓ 反向同步完成：成功 %d 条，跳过 %d 条，错误 %d 条\n" +
                "  处理范围：第 %d-%d 行\n" +
                "  批次ID：%s",
                successCount, skippedCount, errorCount, startRow, endRow, batchId);
            result.put("message", message);

            log.info("反向同步第三方单号完成 - {}", message);

            return AjaxResult.success("反向同步完成", result);

        } catch (Exception e) {
            log.error("反向同步第三方单号失败", e);
            return AjaxResult.error("反向同步失败: " + e.getMessage());
        }
    }

    /**
     * 从腾讯文档拉取物流信息并对比更新本地订单
     * 读取腾讯文档中的单号和物流链接，与本地订单进行对比，如果不一样则覆盖写入本地
     *
     * @param params 包含 fileId, sheetId, startRow（起始行，默认850）, endRow（结束行，默认2500）
     * @return 同步结果
     */
    @PostMapping("/syncLogisticsFromTencentDoc")
    public AjaxResult syncLogisticsFromTencentDoc(@RequestBody Map<String, Object> params) {
        String batchId = java.util.UUID.randomUUID().toString().replace("-", "");

        try {
            // 获取访问令牌
            String accessToken;
            try {
                accessToken = tencentDocTokenService.refreshAccessToken();
                log.info("成功刷新访问令牌");
            } catch (Exception e) {
                log.error("刷新访问令牌失败", e);
                try {
                    accessToken = tencentDocTokenService.getValidAccessToken();
                } catch (Exception e2) {
                    return AjaxResult.error("访问令牌无效，请先完成授权。获取授权URL: GET /jarvis/tendoc/authUrl");
                }
            }

            // 从参数或配置中获取文档信息
            String fileId = (String) params.get("fileId");
            String sheetId = (String) params.get("sheetId");

            final String CONFIG_KEY_PREFIX = "tencent:doc:auto:config:";
            if (fileId == null || fileId.isEmpty()) {
                fileId = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "fileId");
                if (fileId == null || fileId.isEmpty()) {
                    fileId = tencentDocConfig.getFileId();
                }
            }
            if (sheetId == null || sheetId.isEmpty()) {
                sheetId = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "sheetId");
                if (sheetId == null || sheetId.isEmpty()) {
                    sheetId = tencentDocConfig.getSheetId();
                }
            }

            // 从配置中读取表头行
            Integer headerRow = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "headerRow");
            if (headerRow == null) {
                headerRow = tencentDocConfig.getHeaderRow();
            }

            // 起始行，默认850
            Integer startRow = params.get("startRow") != null ?
                Integer.valueOf(params.get("startRow").toString()) : 850;

            // 结束行，默认到2500行
            Integer endRow = params.get("endRow") != null ?
                Integer.valueOf(params.get("endRow").toString()) : 2500;

            if (accessToken == null || fileId == null || sheetId == null) {
                return AjaxResult.error("文档配置不完整，请先配置 fileId 和 sheetId");
            }

            log.info("从腾讯文档同步物流信息开始 - fileId: {}, sheetId: {}, 起始行: {}, 结束行: {}",
                fileId, sheetId, startRow, endRow);

            // 读取表头，识别列位置
            String headerRange = String.format("A%d:Z%d", headerRow, headerRow);
            JSONObject headerData = tencentDocService.readSheetData(accessToken, fileId, sheetId, headerRange);

            if (headerData == null || !headerData.containsKey("values")) {
                return AjaxResult.error("读取表头失败");
            }

            JSONArray headerValues = headerData.getJSONArray("values");
            if (headerValues == null || headerValues.isEmpty()) {
                return AjaxResult.error("表头数据为空");
            }

            JSONArray headerRowData = headerValues.getJSONArray(0);
            if (headerRowData == null || headerRowData.isEmpty()) {
                return AjaxResult.error("无法识别表头");
            }

            // 识别列位置
            Integer orderNoColumn = null;        // "单号"列
            Integer logisticsLinkColumn = null;  // "物流单号"或"物流链接"列

            for (int i = 0; i < headerRowData.size(); i++) {
                String cellValue = headerRowData.getString(i);
                if (cellValue != null) {
                    String cellValueTrim = cellValue.trim();

                    if (orderNoColumn == null && cellValueTrim.contains("单号") && !cellValueTrim.contains("物流")) {
                        orderNoColumn = i;
                        log.info("✓ 识别到 '单号' 列：第 {} 列（索引{}）", i + 1, i);
                    }

                    if (logisticsLinkColumn == null && (cellValueTrim.contains("物流单号") || cellValueTrim.contains("物流链接"))) {
                        logisticsLinkColumn = i;
                        log.info("✓ 识别到 '物流单号' 列：第 {} 列（索引{}）", i + 1, i);
                    }
                }
            }

            if (orderNoColumn == null || logisticsLinkColumn == null) {
                return AjaxResult.error("无法识别表头列，请确保表头包含'单号'和'物流单号'列");
            }

            // 统计结果
            int updatedCount = 0;      // 更新数量
            int skippedCount = 0;      // 跳过数量（物流链接相同或为空）
            int errorCount = 0;        // 错误数量
            java.util.List<String> updatedOrderNos = new java.util.ArrayList<>(); // 更新的单号列表

            // 分批读取数据，每批200行（避免单次读取过多数据导致API限制）
            final int BATCH_SIZE = 200;
            int currentStartRow = startRow;
            int totalBatches = (int) Math.ceil((double)(endRow - startRow + 1) / BATCH_SIZE);
            int currentBatch = 0;

            log.info("开始分批处理，共 {} 批，每批 {} 行", totalBatches, BATCH_SIZE);

            while (currentStartRow <= endRow) {
                currentBatch++;
                int currentEndRow = Math.min(currentStartRow + BATCH_SIZE - 1, endRow);

                log.info("正在处理第 {}/{} 批：第 {} 行到第 {} 行", currentBatch, totalBatches, currentStartRow, currentEndRow);

                // 读取当前批次的数据行
                String dataRange = String.format("A%d:Z%d", currentStartRow, currentEndRow);
                log.info("读取数据范围: {}", dataRange);

                JSONObject dataResponse = null;
                try {
                    dataResponse = tencentDocService.readSheetData(accessToken, fileId, sheetId, dataRange);
                } catch (Exception e) {
                    log.error("读取第 {} 批数据失败（{} - {} 行）", currentBatch, currentStartRow, currentEndRow, e);
                    errorCount += (currentEndRow - currentStartRow + 1);
                    // 继续处理下一批
                    currentStartRow = currentEndRow + 1;
                    continue;
                }

                if (dataResponse == null || !dataResponse.containsKey("values")) {
                    log.warn("第 {} 批数据读取返回空（{} - {} 行），跳过", currentBatch, currentStartRow, currentEndRow);
                    currentStartRow = currentEndRow + 1;
                    continue;
                }

                JSONArray rows = dataResponse.getJSONArray("values");
                if (rows == null || rows.isEmpty()) {
                    log.info("第 {} 批数据为空（{} - {} 行），跳过", currentBatch, currentStartRow, currentEndRow);
                    currentStartRow = currentEndRow + 1;
                    continue;
                }

                log.info("第 {} 批读取到 {} 行数据", currentBatch, rows.size());

                // 处理当前批次的每一行
                for (int rowIndex = 0; rowIndex < rows.size(); rowIndex++) {
                    JSONArray row = rows.getJSONArray(rowIndex);
                    if (row == null || row.size() <= Math.max(orderNoColumn, logisticsLinkColumn)) {
                        skippedCount++;
                        continue;
                    }

                    int actualRow = currentStartRow + rowIndex;
                    // 确保不超过结束行
                    if (actualRow > endRow) {
                        break;
                    }

                    String orderNoFromDoc = row.getString(orderNoColumn);
                    String logisticsLinkFromDoc = row.getString(logisticsLinkColumn);

                    // 跳过单号为空的行
                    if (orderNoFromDoc == null || orderNoFromDoc.trim().isEmpty()) {
                        log.debug("跳过第 {} 行：单号为空", actualRow);
                        skippedCount++;
                        logOperation(batchId, fileId, sheetId, "SYNC_LOGISTICS", null, actualRow, null,
                            "SKIPPED", "单号为空");
                        continue;
                    }

                    // 跳过物流链接为空的行
                    if (logisticsLinkFromDoc == null || logisticsLinkFromDoc.trim().isEmpty()) {
                        log.debug("跳过第 {} 行：物流链接为空", actualRow);
                        skippedCount++;
                        logOperation(batchId, fileId, sheetId, "SYNC_LOGISTICS", orderNoFromDoc, actualRow, null,
                            "SKIPPED", "物流链接为空");
                        continue;
                    }

                    // 清理物流链接（去除空格、换行符、中文等）
                    String cleanedLogisticsLink = cleanLogisticsLink(logisticsLinkFromDoc);

                    try {
                        // 通过第三方单号查找本地订单
                        JDOrder order = jdOrderService.selectJDOrderByThirdPartyOrderNo(orderNoFromDoc.trim());

                        if (order == null) {
                            // 如果通过第三方单号找不到，尝试通过内部单号（remark）查找
                            order = jdOrderService.selectJDOrderByRemark(orderNoFromDoc.trim());
                        }

                        if (order == null) {
                            log.warn("未找到匹配的订单 - 行: {}, 单号: {}", actualRow, orderNoFromDoc);
                            errorCount++;
                            logOperation(batchId, fileId, sheetId, "SYNC_LOGISTICS", orderNoFromDoc, actualRow, cleanedLogisticsLink,
                                "FAILED", "未找到匹配的订单");
                            continue;
                        }

                        // 对比物流链接
                        String localLogisticsLink = order.getLogisticsLink();
                        if (localLogisticsLink != null) {
                            localLogisticsLink = localLogisticsLink.trim();
                        }

                        // 如果本地物流链接为空，直接更新
                        if (localLogisticsLink == null || localLogisticsLink.isEmpty()) {
                            // 本地物流链接为空，直接更新
                            order.setLogisticsLink(cleanedLogisticsLink);
                            int updateResult = jdOrderService.updateJDOrder(order);

                            if (updateResult <= 0) {
                                log.error("更新订单失败 - 行: {}, 订单ID: {}, 单号: {}",
                                    actualRow, order.getId(), order.getRemark());
                                errorCount++;
                                logOperation(batchId, fileId, sheetId, "SYNC_LOGISTICS", order.getRemark(), actualRow, cleanedLogisticsLink,
                                    "FAILED", "更新订单失败");
                                continue;
                            }

                            log.info("✓ 更新订单物流链接成功（本地为空） - 行: {}, 订单: {}, 新物流: {}",
                                actualRow, order.getRemark(), cleanedLogisticsLink);

                            updatedCount++;
                            updatedOrderNos.add(order.getRemark() != null ? order.getRemark() : orderNoFromDoc);

                            // 记录成功日志
                            logOperation(batchId, fileId, sheetId, "SYNC_LOGISTICS", order.getRemark(), actualRow, cleanedLogisticsLink,
                                "SUCCESS", String.format("已更新物流链接：空 -> %s", cleanedLogisticsLink));
                            continue;
                        }

                        // 如果物流链接相同，跳过
                        if (cleanedLogisticsLink.equals(localLogisticsLink)) {
                            log.debug("跳过第 {} 行：物流链接相同 - 单号: {}, 物流: {}", actualRow, orderNoFromDoc, cleanedLogisticsLink);
                            skippedCount++;
                            logOperation(batchId, fileId, sheetId, "SYNC_LOGISTICS", order.getRemark(), actualRow, cleanedLogisticsLink,
                                "SKIPPED", "物流链接相同");
                            continue;
                        }

                        // 物流链接不同，更新本地订单
                        String oldLogisticsLink = localLogisticsLink != null ? localLogisticsLink : "空";
                        order.setLogisticsLink(cleanedLogisticsLink);
                        int updateResult = jdOrderService.updateJDOrder(order);

                        if (updateResult <= 0) {
                            log.error("更新订单失败 - 行: {}, 订单ID: {}, 单号: {}",
                                actualRow, order.getId(), order.getRemark());
                            errorCount++;
                            logOperation(batchId, fileId, sheetId, "SYNC_LOGISTICS", order.getRemark(), actualRow, cleanedLogisticsLink,
                                "FAILED", "更新订单失败");
                            continue;
                        }

                        log.info("✓ 更新订单物流链接成功 - 行: {}, 订单: {}, 旧物流: {}, 新物流: {}",
                            actualRow, order.getRemark(), oldLogisticsLink, cleanedLogisticsLink);

                        updatedCount++;
                        updatedOrderNos.add(order.getRemark() != null ? order.getRemark() : orderNoFromDoc);

                        // 记录成功日志
                        logOperation(batchId, fileId, sheetId, "SYNC_LOGISTICS", order.getRemark(), actualRow, cleanedLogisticsLink,
                            "SUCCESS", String.format("已更新物流链接：%s -> %s", oldLogisticsLink, cleanedLogisticsLink));

                    } catch (Exception e) {
                        log.error("处理第 {} 行失败", actualRow, e);
                        errorCount++;
                        logOperation(batchId, fileId, sheetId, "SYNC_LOGISTICS", orderNoFromDoc, actualRow, cleanedLogisticsLink,
                            "FAILED", "处理异常: " + e.getMessage());
                    }

                    // 添加延迟，避免API调用频率过高
                    try {
                        Thread.sleep(100);
                    } catch (InterruptedException e) {
                        Thread.currentThread().interrupt();
                    }
                }

                log.info("第 {}/{} 批处理完成，当前统计 - 更新: {}, 跳过: {}, 错误: {}",
                    currentBatch, totalBatches, updatedCount, skippedCount, errorCount);

                // 移动到下一批
                currentStartRow = currentEndRow + 1;

                // 批次之间的延迟，避免API调用频率过高
                if (currentStartRow <= endRow) {
                    try {
                        Thread.sleep(200); // 批次之间延迟200ms
                    } catch (InterruptedException e) {
                        Thread.currentThread().interrupt();
                    }
                }
            }

            JSONObject result = new JSONObject();
            result.put("batchId", batchId);
            result.put("startRow", startRow);
            result.put("endRow", endRow);
            result.put("updatedCount", updatedCount);
            result.put("skippedCount", skippedCount);
            result.put("errorCount", errorCount);
            result.put("updatedOrderNos", updatedOrderNos);

            String message = String.format(
                "✓ 物流信息同步完成：更新 %d 条，跳过 %d 条，错误 %d 条\n" +
                "  处理范围：第 %d-%d 行\n" +
                "  批次ID：%s",
                updatedCount, skippedCount, errorCount, startRow, endRow, batchId);
            result.put("message", message);

            log.info("从腾讯文档同步物流信息完成 - {}", message);

            // 如果有更新的订单，发送微信推送通知
            if (updatedCount > 0) {
                try {
                    sendLogisticsSyncNotification(updatedOrderNos, updatedCount, skippedCount, errorCount, batchId, fileId, sheetId);
                } catch (Exception e) {
                    log.error("发送微信推送通知失败", e);
                }
            }

            return AjaxResult.success("物流信息同步完成", result);

        } catch (Exception e) {
            log.error("从腾讯文档同步物流信息失败", e);
            return AjaxResult.error("同步失败: " + e.getMessage());
        }
    }

    /**
     * 发送物流信息同步的微信推送通知
     *
     * @param updatedOrderNos 更新的单号列表
     * @param updatedCount 更新数量
     * @param skippedCount 跳过数量
     * @param errorCount 错误数量
     * @param batchId 批次ID
     * @param fileId 文档ID
     * @param sheetId 工作表ID
     */
    private void sendLogisticsSyncNotification(java.util.List<String> updatedOrderNos, int updatedCount, int skippedCount, int errorCount, String batchId, String fileId, String sheetId) {
        try {
            log.info("========== 开始发送物流信息同步微信推送通知 ==========");
            log.info("更新: {} 条, 跳过: {} 条, 错误: {} 条", updatedCount, skippedCount, errorCount);
            log.info("批次ID: {}, 文档ID: {}, 工作表ID: {}", batchId, fileId, sheetId);

            // 微信推送服务配置
            String wxSendBaseUrl = "https://wxts.van333.cn";
            String pushToken = "super_token_b62190c26";
            String pushUrl = wxSendBaseUrl + "/wx/send/ty";

            // 构建推送内容
            StringBuilder content = new StringBuilder();
            content.append("【腾讯文档物流信息同步成功】\n\n");
            content.append(String.format("✓ 更新数量: %d 条\n", updatedCount));
            if (skippedCount > 0) {
                content.append(String.format("⊘ 跳过: %d 条\n", skippedCount));
            }
            if (errorCount > 0) {
                content.append(String.format("✗ 错误: %d 条\n", errorCount));
            }
            content.append("\n");

            if (!updatedOrderNos.isEmpty()) {
                content.append("【更新的单号列表】\n");
                // 最多显示30条单号，避免消息过长
                int maxDisplay = Math.min(30, updatedOrderNos.size());
                for (int i = 0; i < maxDisplay; i++) {
                    content.append(String.format("%d. %s\n", i + 1, updatedOrderNos.get(i)));
                }

                if (updatedOrderNos.size() > maxDisplay) {
                    content.append(String.format("\n... 还有 %d 个单号未显示", updatedOrderNos.size() - maxDisplay));
                }
            }

            // 构建请求体
            JSONObject requestBody = new JSONObject();
            requestBody.put("title", "腾讯文档物流信息同步成功");
            requestBody.put("text", content.toString());
            requestBody.put("vanToken", pushToken);
            requestBody.put("messageType", "TY");
            requestBody.put("touser", "LinPingFan,Hong");

            String jsonBody = requestBody.toJSONString();

            log.info("微信推送请求 - URL: {}", pushUrl);
            log.info("微信推送请求体: {}", jsonBody);

            // 发送POST请求
            String result = sendPostRequest(pushUrl, jsonBody, pushToken);

            log.info("========== 微信推送发送完成 ==========");
            log.info("推送URL: {}", pushUrl);
            log.info("推送响应: {}", result);

            // 记录微信推送操作日志
            String pushLogMessage = String.format("物流信息同步微信推送成功 - 更新: %d条, 跳过: %d条, 错误: %d条",
                updatedCount, skippedCount, errorCount);
            logOperation(batchId, fileId, sheetId, "WECHAT_PUSH", null, null, null,
                "SUCCESS", pushLogMessage);

        } catch (Exception e) {
            log.error("========== 发送物流信息同步微信推送失败 ==========", e);

            // 记录微信推送失败日志
            String pushLogMessage = String.format("物流信息同步微信推送失败 - 错误: %s", e.getMessage());
            logOperation(batchId, fileId, sheetId, "WECHAT_PUSH", null, null, null,
                "FAILED", pushLogMessage);

            // 不抛出异常，避免影响主流程
            log.warn("微信推送失败，但不影响主流程，继续执行");
        }
    }

    /**
     * 发送微信推送通知（同步成功日志）
     *
     * @param successLogs 成功日志列表
     * @param filledCount 成功填充数量
     * @param skippedCount 跳过数量
     * @param errorCount 错误数量
     */
    private void sendWeChatNotification(List<Map<String, Object>> successLogs, int filledCount, int skippedCount, int errorCount) {
        sendWeChatNotification(successLogs, filledCount, skippedCount, errorCount, true);
    }

    /**
     * 发送微信推送通知（同步成功日志）- 带batchId参数
     *
     * @param successLogs 成功日志列表
     * @param filledCount 成功填充数量
     * @param skippedCount 跳过数量
     * @param errorCount 错误数量
     * @param batchId 批次ID
     * @param fileId 文档ID
     * @param sheetId 工作表ID
     * @param isManual 是否为手动执行
     */
    private void sendWeChatNotificationWithBatchId(List<Map<String, Object>> successLogs, int filledCount, int skippedCount, int errorCount, String batchId, String fileId, String sheetId, boolean isManual) {
        sendWeChatNotification(successLogs, filledCount, skippedCount, errorCount, batchId, fileId, sheetId, isManual);
    }

    /**
     * 发送微信推送通知（同步成功日志）
     *
     * @param successLogs 成功日志列表
     * @param filledCount 成功填充数量
     * @param skippedCount 跳过数量
     * @param errorCount 错误数量
     * @param isManual 是否为手动执行
     */
    private void sendWeChatNotification(List<Map<String, Object>> successLogs, int filledCount, int skippedCount, int errorCount, boolean isManual) {
        // 从配置中读取文档信息
        final String CONFIG_KEY_PREFIX = "tencent:doc:auto:config:";
        String fileId = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "fileId");
        String sheetId = redisCache.getCacheObject(CONFIG_KEY_PREFIX + "sheetId");
        if (fileId == null || fileId.isEmpty()) {
            fileId = tencentDocConfig.getFileId();
        }
        if (sheetId == null || sheetId.isEmpty()) {
            sheetId = tencentDocConfig.getSheetId();
        }
        sendWeChatNotification(successLogs, filledCount, skippedCount, errorCount, null, fileId, sheetId, isManual);
    }

    /**
     * 发送微信推送通知（同步成功日志）
     *
     * @param successLogs 成功日志列表
     * @param filledCount 成功填充数量
     * @param skippedCount 跳过数量
     * @param errorCount 错误数量
     * @param batchId 批次ID（可为null）
     * @param fileId 文档ID
     * @param sheetId 工作表ID
     * @param isManual 是否为手动执行
     */
    private void sendWeChatNotification(List<Map<String, Object>> successLogs, int filledCount, int skippedCount, int errorCount, String batchId, String fileId, String sheetId, boolean isManual) {
        try {
            log.info("========== 开始发送微信推送通知 ==========");
            log.info("推送类型: {}, 成功: {} 条, 跳过: {} 条, 错误: {} 条",
                isManual ? "手动执行" : "批量同步", filledCount, skippedCount, errorCount);
            log.info("批次ID: {}, 文档ID: {}, 工作表ID: {}", batchId, fileId, sheetId);

            // 微信推送服务配置
            String wxSendBaseUrl = "https://wxts.van333.cn";
            String pushToken = "super_token_b62190c26";
            String pushUrl = wxSendBaseUrl + "/wx/send/ty";

            // 构建推送内容
            StringBuilder content = new StringBuilder();
            if (isManual) {
                content.append("【手动执行-腾讯文档同步成功】\n\n");
            } else {
                content.append("【批量同步-腾讯文档同步成功】\n\n");
            }
            content.append(String.format("✓ 成功填充: %d 条\n", filledCount));
            if (skippedCount > 0) {
                content.append(String.format("⊘ 跳过: %d 条\n", skippedCount));
            }
            if (errorCount > 0) {
                content.append(String.format("✗ 错误: %d 条\n", errorCount));
            }
            content.append("\n");

            if (!successLogs.isEmpty()) {
                content.append("【成功详情】\n");
                // 最多显示20条详细记录，避免消息过长
                int maxDisplay = Math.min(20, successLogs.size());
                for (int i = 0; i < maxDisplay; i++) {
                    Map<String, Object> log = successLogs.get(i);
                    String orderNo = (String) log.get("orderNo");
                    Integer row = (Integer) log.get("row");
                    String logisticsLink = (String) log.get("logisticsLink");
                    String phone = (String) log.get("phone");

                    content.append(String.format("%d. 单号: %s\n", i + 1, orderNo));
                    content.append(String.format("   行号: %d\n", row));
                    if (phone != null && !phone.isEmpty()) {
                        content.append(String.format("   电话: %s\n", phone));
                    }
                    content.append(String.format("   物流: %s\n", logisticsLink));
                    if (i < maxDisplay - 1) {
                        content.append("\n");
                    }
                }

                if (successLogs.size() > maxDisplay) {
                    content.append(String.format("\n... 还有 %d 条记录未显示", successLogs.size() - maxDisplay));
                }
            }

            // 构建请求体
            JSONObject requestBody = new JSONObject();
            requestBody.put("title", "腾讯文档同步成功");
            requestBody.put("text", content.toString());
            requestBody.put("vanToken", pushToken);
            requestBody.put("messageType", "TY");
            requestBody.put("touser","LinPingFan,Hong");
            // touser 可以为空，使用默认接收人

            String jsonBody = requestBody.toJSONString();

            log.info("微信推送请求 - URL: {}", pushUrl);
            log.info("微信推送请求体: {}", jsonBody);

            // 发送POST请求
            String result = sendPostRequest(pushUrl, jsonBody, pushToken);

            log.info("========== 微信推送发送完成 ==========");
            log.info("推送URL: {}", pushUrl);
            log.info("推送响应: {}", result);

            // 记录微信推送操作日志
            String pushLogMessage = String.format("微信推送成功 - 类型: %s, 成功: %d条, 跳过: %d条, 错误: %d条",
                isManual ? "手动执行" : "批量同步", filledCount, skippedCount, errorCount);
            logOperation(batchId, fileId, sheetId, "WECHAT_PUSH", null, null, null,
                "SUCCESS", pushLogMessage);

        } catch (Exception e) {
            log.error("========== 发送微信推送失败 ==========", e);

            // 记录微信推送失败日志
            String pushLogMessage = String.format("微信推送失败 - 类型: %s, 错误: %s",
                isManual ? "手动执行" : "批量同步", e.getMessage());
            logOperation(batchId, fileId, sheetId, "WECHAT_PUSH", null, null, null,
                "FAILED", pushLogMessage);

            // 不抛出异常，避免影响主流程
            log.warn("微信推送失败，但不影响主流程，继续执行");
        }
    }

    /**
     * 发送POST请求到微信推送服务
     *
     * @param url 请求URL
     * @param jsonBody JSON请求体
     * @param token 认证token
     * @return 响应结果
     */
    private String sendPostRequest(String url, String jsonBody, String token) {
        java.io.BufferedReader in = null;
        java.io.PrintWriter out = null;
        StringBuilder result = new StringBuilder();
        try {
            log.info(">>> 发送HTTP POST请求到微信推送服务");
            log.info(">>> 请求URL: {}", url);
            log.info(">>> 请求体长度: {} 字节", jsonBody.length());

            java.net.URL realUrl = new java.net.URL(url);
            java.net.URLConnection conn = realUrl.openConnection();

            // 设置请求头
            conn.setRequestProperty("accept", "*/*");
            conn.setRequestProperty("connection", "Keep-Alive");
            conn.setRequestProperty("user-agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64)");
            conn.setRequestProperty("Accept-Charset", "utf-8");
            conn.setRequestProperty("Content-Type", "application/json");
            conn.setRequestProperty("vanToken", token);

            log.info(">>> 请求头已设置 - vanToken: {}...", token != null && token.length() > 10 ? token.substring(0, 10) : token);

            conn.setDoOutput(true);
            conn.setDoInput(true);

            // 设置超时时间
            conn.setConnectTimeout(10000); // 10秒连接超时
            conn.setReadTimeout(10000); // 10秒读取超时

            log.info(">>> 开始发送请求体...");
            // 发送请求体
            out = new java.io.PrintWriter(conn.getOutputStream());
            out.print(jsonBody);
            out.flush();
            log.info(">>> 请求体已发送");

            log.info(">>> 开始读取响应...");
            // 读取响应
            in = new java.io.BufferedReader(new java.io.InputStreamReader(conn.getInputStream(), java.nio.charset.StandardCharsets.UTF_8));
            String line;
            while ((line = in.readLine()) != null) {
                result.append(line);
            }

            log.info(">>> HTTP请求成功 - URL: {}, 响应: {}", url, result.toString());
        } catch (java.net.ConnectException e) {
            log.error(">>> HTTP请求连接失败 - URL: {}", url, e);
            throw new RuntimeException("连接失败: " + e.getMessage(), e);
        } catch (java.net.SocketTimeoutException e) {
            log.error(">>> HTTP请求超时 - URL: {}", url, e);
            throw new RuntimeException("请求超时: " + e.getMessage(), e);
        } catch (java.io.IOException e) {
            log.error(">>> HTTP请求IO异常 - URL: {}", url, e);
            throw new RuntimeException("IO异常: " + e.getMessage(), e);
        } catch (Exception e) {
            log.error(">>> HTTP请求异常 - URL: {}", url, e);
            throw new RuntimeException("推送异常: " + e.getMessage(), e);
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
                if (in != null) {
                    in.close();
                }
            } catch (java.io.IOException ex) {
                log.error("关闭流异常", ex);
            }
        }
        return result.toString();
    }

    /**
     * 清理物流链接
     * 去除空格、换行符、制表符、中文等特殊字符，只保留URL有效字符
     *
     * @param logisticsLink 原始物流链接
     * @return 清理后的物流链接
     */
    private String cleanLogisticsLink(String logisticsLink) {
        if (logisticsLink == null) {
            return null;
        }

        // 去除首尾空格
        String cleaned = logisticsLink.trim();

        // 去除换行符、制表符等空白字符
        cleaned = cleaned.replaceAll("[\\r\\n\\t]", "");

        // 去除所有空格
        cleaned = cleaned.replaceAll("\\s+", "");

        // 提取URL（如果包含中文或其他非URL字符，尝试提取URL部分）
        // 匹配 http:// 或 https:// 开头的URL
        java.util.regex.Pattern urlPattern = java.util.regex.Pattern.compile("https?://[^\\s\\u4e00-\\u9fa5]+");
        java.util.regex.Matcher matcher = urlPattern.matcher(cleaned);

        if (matcher.find()) {
            cleaned = matcher.group();
        } else {
            // 如果没有找到完整URL，尝试提取 3.cn 相关的链接
            java.util.regex.Pattern shortUrlPattern = java.util.regex.Pattern.compile("https?://3\\.cn/[^\\s\\u4e00-\\u9fa5]+");
            java.util.regex.Matcher shortMatcher = shortUrlPattern.matcher(cleaned);
            if (shortMatcher.find()) {
                cleaned = shortMatcher.group();
            }
        }

        return cleaned.trim();
    }
}

