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;
    
    /** Redis key前缀，用于存储上次处理的最大行数 */
    private static final String LAST_PROCESSED_ROW_KEY_PREFIX = "tendoc:last_row:";
    
    /**
     * 测试回调接口是否可访问（用于调试）
     */
    @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;
            
            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, 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;
            
            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, 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());
        }
    }
    
    /**
     * 根据单号填充物流链接 - 读取表格数据，根据单号查询订单系统中的物流链接，并填充到表格
     * 优化：记录上次处理的最大行数，每次从最大行数-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");
            
            // 可选参数：指定列位置
            Integer orderNoColumn = params.get("orderNoColumn") != null ? 
                Integer.valueOf(params.get("orderNoColumn").toString()) : null; // 单号列索引（从0开始）
            Integer logisticsLinkColumn = params.get("logisticsLinkColumn") != null ? 
                Integer.valueOf(params.get("logisticsLinkColumn").toString()) : null; // 物流链接列索引（从0开始）
            Integer headerRow = params.get("headerRow") != null ? 
                Integer.valueOf(params.get("headerRow").toString()) : 1; // 表头所在行（默认第1行，从1开始）
            
            // 可选参数：是否强制从指定行开始（如果为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()) : null;
            
            if (accessToken == null || fileId == null || sheetId == null) {
                return AjaxResult.error("accessToken、fileId和sheetId不能为空");
            }
            
            // 生成Redis key，用于存储该文件的工作表的上次处理最大行数
            String redisKey = LAST_PROCESSED_ROW_KEY_PREFIX + fileId + ":" + sheetId;
            
            // 获取上次处理的最大行数
            Integer lastMaxRow = null;
            if (!forceStart && redisCache.hasKey(redisKey)) {
                Object cacheObj = redisCache.getCacheObject(redisKey);
                if (cacheObj != null) {
                    lastMaxRow = Integer.valueOf(cacheObj.toString());
                }
            }
            
            // 计算起始行：从上次最大行数-100开始，或者从强制指定的行开始，或者从表头下一行开始
            int startRow;
            if (forceStartRow != null) {
                startRow = forceStartRow;
            } else if (lastMaxRow != null && lastMaxRow > headerRow) {
                startRow = Math.max(headerRow + 1, lastMaxRow - 100); // 从最大行数-100开始，但至少是表头下一行
            } else {
                startRow = headerRow + 1; // 默认从表头下一行开始
            }
            
            // 计算读取范围：从起始行开始，读取足够多的行（假设每次最多处理200行）
            int endRow = startRow + 200; // 每次最多读取200行
            String startColumn = "A";
            String endColumn = "Z";
            String range = String.format("%s%d:%s%d", startColumn, startRow, endColumn, endRow);
            
            log.info("开始填充物流链接 - 文件ID: {}, 工作表ID: {}, 起始行: {}, 结束行: {}, 上次最大行: {}", 
                fileId, sheetId, startRow, endRow, lastMaxRow);
            
            // 读取表格数据（先读取表头行用于识别列位置）
            String headerRange = String.format("%s%d:%s%d", startColumn, headerRow, endColumn, headerRow);
            JSONObject headerData = tencentDocService.readSheetData(accessToken, fileId, sheetId, headerRange);
            JSONArray headerValues = headerData.getJSONArray("values");
            
            if (headerValues == null || headerValues.isEmpty()) {
                return AjaxResult.error("无法读取表头，请检查headerRow参数");
            }
            
            // 自动识别列位置（如果未指定）
            if (orderNoColumn == null || logisticsLinkColumn == null) {
                JSONArray headerRowData = headerValues.getJSONArray(0);
                if (headerRowData == null || headerRowData.isEmpty()) {
                    return AjaxResult.error("无法识别表头，请手动指定列位置");
                }
                
                // 查找单号列和物流链接列
                for (int i = 0; i < headerRowData.size(); i++) {
                    String cellValue = headerRowData.getString(i);
                    if (cellValue != null) {
                        String cellValueLower = cellValue.toLowerCase().trim();
                        if (orderNoColumn == null && (cellValueLower.contains("单号") || cellValueLower.contains("order"))) {
                            orderNoColumn = i;
                        }
                        if (logisticsLinkColumn == null && (cellValueLower.contains("物流链接") || 
                            (cellValueLower.contains("物流") && cellValueLower.contains("链接")))) {
                            logisticsLinkColumn = i;
                        }
                    }
                }
                
                if (orderNoColumn == null) {
                    return AjaxResult.error("无法找到单号列，请手动指定orderNoColumn参数");
                }
                if (logisticsLinkColumn == null) {
                    return AjaxResult.error("无法找到物流链接列，请手动指定logisticsLinkColumn参数");
                }
            }
            
            // 读取数据行
            JSONObject sheetData = tencentDocService.readSheetData(accessToken, fileId, sheetId, range);
            JSONArray values = sheetData.getJSONArray("values");
            
            if (values == null || values.isEmpty()) {
                log.info("指定范围内没有数据，可能已处理完毕");
                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", "指定范围内没有数据");
                return AjaxResult.success("没有需要处理的数据", result);
            }
            
            // 处理数据行
            int filledCount = 0;
            int skippedCount = 0;
            int errorCount = 0;
            int currentMaxRow = startRow - 1; // 记录当前处理的最大行号（Excel行号，从1开始）
            
            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;
                currentMaxRow = Math.max(currentMaxRow, excelRow);
                
                // 获取单号
                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()) {
                    skippedCount++; // 已有物流链接，跳过
                    continue;
                }
                
                try {
                    // 根据单号查询订单
                    JDOrder order = jdOrderService.selectJDOrderByRemark(orderNo);
                    
                    if (order != null && order.getLogisticsLink() != null && !order.getLogisticsLink().trim().isEmpty()) {
                        String logisticsLink = order.getLogisticsLink().trim();
                        
                        // 构建更新请求
                        JSONObject update = new JSONObject();
                        update.put("row", excelRow);
                        update.put("column", logisticsLinkColumn);
                        update.put("orderNo", orderNo);
                        update.put("logisticsLink", logisticsLink);
                        updates.add(update);
                        
                        filledCount++;
                        log.info("找到订单物流链接 - 单号: {}, 物流链接: {}, 行号: {}", orderNo, logisticsLink, excelRow);
                    } else {
                        errorCount++;
                        log.warn("未找到订单或物流链接为空 - 单号: {}, 行号: {}", orderNo, excelRow);
                    }
                } catch (Exception e) {
                    errorCount++;
                    log.error("处理订单失败 - 单号: {}, 行号: {}", orderNo, excelRow, e);
                }
            }
            
            // 批量更新表格
            if (!updates.isEmpty()) {
                // 将更新按行分组，批量写入
                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);
                }
                
                // 批量写入（每行单独写入，因为腾讯文档API可能不支持批量更新不同行）
                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");
                        
                        // 计算列字母（A, B, C...）
                        String columnLetter = getColumnLetter(logisticsLinkColumn);
                        String cellRange = columnLetter + row;
                        
                        // 构建写入数据（二维数组格式）
                        JSONArray writeValues = new JSONArray();
                        JSONArray writeRow = new JSONArray();
                        writeRow.add(logisticsLink);
                        writeValues.add(writeRow);
                        
                        // 写入单个单元格
                        tencentDocService.writeSheetData(accessToken, fileId, sheetId, cellRange, writeValues);
                        successUpdates++;
                        
                        log.info("成功写入物流链接 - 单元格: {}, 单号: {}, 物流链接: {}", cellRange, update.getString("orderNo"), logisticsLink);
                    } catch (Exception e) {
                        log.error("写入物流链接失败 - 行: {}", entry.getKey(), e);
                        errorCount++;
                    }
                    
                    // 添加延迟，避免API调用频率过高
                    try {
                        Thread.sleep(100); // 100ms延迟
                    } catch (InterruptedException e) {
                        Thread.currentThread().interrupt();
                    }
                }
                
                log.info("批量填充物流链接完成 - 成功: {}, 跳过: {}, 错误: {}", successUpdates, skippedCount, errorCount);
            }
            
            // 更新Redis中记录的最大行数（如果本次处理了数据）
            if (currentMaxRow >= startRow) {
                redisCache.setCacheObject(redisKey, currentMaxRow, 30, TimeUnit.DAYS);
                log.info("更新上次处理的最大行数 - 文件ID: {}, 工作表ID: {}, 最大行: {}", fileId, sheetId, currentMaxRow);
            }
            
            JSONObject result = new JSONObject();
            result.put("startRow", startRow);
            result.put("endRow", endRow);
            result.put("currentMaxRow", currentMaxRow);
            result.put("lastMaxRow", lastMaxRow);
            result.put("filledCount", filledCount);
            result.put("skippedCount", skippedCount);
            result.put("errorCount", errorCount);
            result.put("orderNoColumn", orderNoColumn);
            result.put("logisticsLinkColumn", logisticsLinkColumn);
            result.put("message", String.format("处理完成：成功填充 %d 条，跳过 %d 条，错误 %d 条，最大行号: %d", 
                filledCount, skippedCount, errorCount, currentMaxRow));
            
            return AjaxResult.success("填充物流链接完成", result);
        } catch (Exception e) {
            log.error("填充物流链接失败", e);
            return AjaxResult.error("填充物流链接失败: " + e.getMessage());
        }
    }
    
    /**
     * 将列索引转换为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();
    }
}

