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;

    /** 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, "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, "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, "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(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(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(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(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(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(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(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(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(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(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(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(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(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(fileId, sheetId, "WRITE_SINGLE", thirdPartyOrderNo, targetRow, logisticsLink, 
                    "FAILED", "无法获取用户数据");
                return AjaxResult.error("无法获取用户数据");
            }
            String openId = userData.getString("openID");
            if (openId == null || openId.isEmpty()) {
                logOperation(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(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);
                // 不影响主流程，继续返回成功
            }
            
            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(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 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.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
     */
    @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");
                }
            }

            // 从参数或配置中获取文档信息
            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");
            }
            
            log.info("同步物流配置 - fileId: {}, sheetId: {}, 配置起始行: {}, 表头行: {}", 
                fileId, sheetId, 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 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 (arrangedColumn == null && 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 (arrangedColumn == null) {
                log.warn("未找到'是否安排'列，将跳过该字段的更新");
            }
            if (markColumn == null) {
                log.warn("未找到'标记'列，将跳过该字段的更新");
            }
            if (phoneColumn == null) {
                log.warn("未找到'下单电话'列，将跳过该字段的更新");
            }

            log.info("列位置识别完成 - 单号: {}, 物流单号: {}, 是否安排: {}, 标记: {}, 下单电话: {}",
                orderNoColumn, logisticsLinkColumn, 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;

            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(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 successUpdates = 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 (arrangedColumn != null) {
                            requests.add(buildUpdateCellRequest(sheetId, row - 1, arrangedColumn, "2", false));
                        }

                        // 4. 更新"标记"列（如果存在）
                        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);
                        successUpdates++;

                        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(fileId, sheetId, "BATCH_SYNC", expectedOrderNo, row, logisticsLink, 
                            "SUCCESS", null);
                    } catch (Exception e) {
                        log.error("写入数据失败 - 行: {}", entry.getKey(), e);
                        errorCount++;
                        
                        // 记录失败日志
                        String orderNo = entry.getValue().getString("orderNo");
                        String logisticsLink = entry.getValue().getString("logisticsLink");
                        logOperation(fileId, sheetId, "BATCH_SYNC", orderNo, entry.getKey(), logisticsLink, 
                            "FAILED", "写入异常: " + e.getMessage());
                    }

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

                log.info("批量填充物流链接完成 - 成功: {}, 跳过: {}, 错误: {}", successUpdates, skippedCount, errorCount);
            }

            // 根据填充结果决定下次的起始位置
            int currentMaxRow = endRow;
            int nextStartRow;
            String nextSyncHint;
            
            if (filledCount > 0) {
                // 有数据填充成功，说明当前范围有有效数据，继续往前推进
                // 下次从 endRow - 100 开始（回溯100行，防止遗漏）
                nextStartRow = Math.max(effectiveStartRow, currentMaxRow - 100);
                nextSyncHint = String.format("下次将从第 %d 行开始（本次填充 %d 条，回溯100行防止遗漏）", 
                    nextStartRow, filledCount);
                
                // 记录进度到 endRow，持续往前
                redisCache.setCacheObject(redisKey, currentMaxRow, 30, TimeUnit.DAYS);
                log.info("本次填充成功 {} 条，更新进度到第 {} 行，下次从第 {} 行开始", 
                    filledCount, currentMaxRow, nextStartRow);
            } else {
                // 没有数据填充，说明这个范围都是空行或已处理的数据
                if (lastMaxRow != null && currentMaxRow > (lastMaxRow + 300)) {
                    // 如果跳跃太大（>300行），可能已经超出数据区域，回到配置起始行重新扫描
                    nextStartRow = effectiveStartRow;
                    nextSyncHint = String.format("下次将从第 %d 行重新开始（本次范围 %d-%d 无新数据，重新扫描）", 
                        nextStartRow, startRow, endRow);
                    
                    // 不更新 Redis 进度，保持原有进度
                    log.info("本次无数据填充，且跳跃过大，下次将从配置起始行 {} 重新开始", effectiveStartRow);
                } else {
                    // 跳跃不大，继续往前推进，跳过更多行（+200行）
                    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("本次无数据填充，更新进度到第 {} 行，下次从第 {} 行继续", 
                        currentMaxRow, nextStartRow);
                }
            }
            
            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);

            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();
    }
}

