package com.rj.controller;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.rj.entity.LbDailyUserTrade;
import com.rj.entity.LbDailyUserTradeReport;
import com.rj.service.ILbDailyUserTradeReportService;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.Parameter;
import io.swagger.v3.oas.annotations.tags.Tag;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.usermodel.ComparisonOperator;
import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
import org.apache.poi.ss.usermodel.FontFormatting;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.awt.Color;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;

/**
 * <p>
 * 当天用户交易报表 前端控制器
 * </p>
 */
@RestController
@RequestMapping("/api/lbDailyUserTradeReport")
@Tag(name = "当天用户交易报表", description = "当天用户交易报表相关接口")
public class LbDailyUserTradeReportController {

    @Autowired
    private ILbDailyUserTradeReportService lbDailyUserTradeReportService;

    @PostMapping("/add")
    @Operation(summary = "新增报表记录", description = "新增当天用户交易报表记录")
    public ResponseEntity<Map<String, Object>> add(@RequestBody LbDailyUserTradeReport tradeReport) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (tradeReport.getId() == null || tradeReport.getId().trim().isEmpty()) {
                tradeReport.setId(UUID.randomUUID().toString());
            }
            if (tradeReport.getYestodayBuyAmt() == null) {
                tradeReport.setYestodayBuyAmt(BigDecimal.ZERO);
            }
            if (tradeReport.getDailySellAmt() == null) {
                tradeReport.setDailySellAmt(BigDecimal.ZERO);
            }
            if (tradeReport.getDailyBuyAmt() == null) {
                tradeReport.setDailyBuyAmt(BigDecimal.ZERO);
            }
            if (tradeReport.getServiceAmt() == null) {
                tradeReport.setServiceAmt(BigDecimal.ZERO);
            }
            if (tradeReport.getDikouAmt() == null) {
                tradeReport.setDikouAmt(BigDecimal.ZERO);
            }
            if (tradeReport.getDiffAmt() == null) {
                tradeReport.setDiffAmt(BigDecimal.ZERO);
            }
            tradeReport.setActualReceiptsPayments(tradeReport.getDiffAmt().subtract(tradeReport.getServiceAmt()));
            if (tradeReport.getReportDate() == null) {
                tradeReport.setReportDate(LocalDateTime.now());
            }
            tradeReport.setCreatedAt(LocalDateTime.now());
            tradeReport.setUpdatedAt(LocalDateTime.now());

            boolean success = lbDailyUserTradeReportService.save(tradeReport);
            if (success) {
                result.put("success", true);
                result.put("message", "新增成功");
                result.put("data", tradeReport);
                return ResponseEntity.ok(result);
            }
            result.put("success", false);
            result.put("message", "新增失败");
            return ResponseEntity.badRequest().body(result);
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "新增异常：" + e.getMessage());
            return ResponseEntity.internalServerError().body(result);
        }
    }

    @GetMapping("/get/{id}")
    @Operation(summary = "根据ID查询", description = "根据ID查询当天用户交易报表记录")
    public ResponseEntity<Map<String, Object>> getById(
            @Parameter(description = "主键ID", required = true) @PathVariable String id) {
        Map<String, Object> result = new HashMap<>();
        try {
            LbDailyUserTradeReport data = lbDailyUserTradeReportService.getById(id);
            if (data == null) {
                result.put("success", false);
                result.put("message", "记录不存在");
                return ResponseEntity.badRequest().body(result);
            }
            result.put("success", true);
            result.put("message", "查询成功");
            result.put("data", data);
            return ResponseEntity.ok(result);
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "查询异常：" + e.getMessage());
            return ResponseEntity.internalServerError().body(result);
        }
    }

    @GetMapping("/list")
    @Operation(summary = "分页条件查询", description = "分页查询当天用户交易报表记录")
    public ResponseEntity<Map<String, Object>> list(
            @Parameter(description = "页码", example = "1") @RequestParam(defaultValue = "1") Integer current,
            @Parameter(description = "每页大小", example = "10") @RequestParam(defaultValue = "10") Integer size,
            @Parameter(description = "租户ID") @RequestParam(required = false) String tenantId,
            @Parameter(description = "用户ID") @RequestParam(required = false) String userId,
            @Parameter(description = "数据类型") @RequestParam(required = false) String dataType,
            @Parameter(description = "昵称（模糊查询）") @RequestParam(required = false) String nickname,
            @Parameter(description = "报表开始时间，格式：yyyy-MM-dd HH:mm:ss")
            @RequestParam(required = false) String reportStartTime,
            @Parameter(description = "报表结束时间，格式：yyyy-MM-dd HH:mm:ss")
            @RequestParam(required = false) String reportEndTime,
            @Parameter(description = "创建开始时间，格式：yyyy-MM-dd HH:mm:ss")
            @RequestParam(required = false) String createdStartTime,
            @Parameter(description = "创建结束时间，格式：yyyy-MM-dd HH:mm:ss")
            @RequestParam(required = false) String createdEndTime) {
        Map<String, Object> result = new HashMap<>();
        try {
            LambdaQueryWrapper<LbDailyUserTradeReport> queryWrapper = new LambdaQueryWrapper<>();
            if (tenantId != null && !tenantId.trim().isEmpty()) {
                queryWrapper.eq(LbDailyUserTradeReport::getTenantId, tenantId.trim());
            }
            if (userId != null && !userId.trim().isEmpty()) {
                queryWrapper.eq(LbDailyUserTradeReport::getUserId, userId.trim());
            }
            if (dataType != null && !dataType.trim().isEmpty()) {
                queryWrapper.eq(LbDailyUserTradeReport::getDataType, dataType.trim());
            }
            if (nickname != null && !nickname.trim().isEmpty()) {
                queryWrapper.like(LbDailyUserTradeReport::getNickname, nickname.trim());
            }

            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            if (reportStartTime != null && !reportStartTime.trim().isEmpty()) {
                try {
                    queryWrapper.ge(LbDailyUserTradeReport::getReportDate, LocalDateTime.parse(reportStartTime.trim(), formatter));
                } catch (Exception e) {
                    result.put("success", false);
                    result.put("message", "报表开始时间格式错误，请使用 yyyy-MM-dd HH:mm:ss");
                    return ResponseEntity.badRequest().body(result);
                }
            }
            if (reportEndTime != null && !reportEndTime.trim().isEmpty()) {
                try {
                    queryWrapper.le(LbDailyUserTradeReport::getReportDate, LocalDateTime.parse(reportEndTime.trim(), formatter));
                } catch (Exception e) {
                    result.put("success", false);
                    result.put("message", "报表结束时间格式错误，请使用 yyyy-MM-dd HH:mm:ss");
                    return ResponseEntity.badRequest().body(result);
                }
            }
            if (createdStartTime != null && !createdStartTime.trim().isEmpty()) {
                try {
                    queryWrapper.ge(LbDailyUserTradeReport::getCreatedAt, LocalDateTime.parse(createdStartTime.trim(), formatter));
                } catch (Exception e) {
                    result.put("success", false);
                    result.put("message", "创建开始时间格式错误，请使用 yyyy-MM-dd HH:mm:ss");
                    return ResponseEntity.badRequest().body(result);
                }
            }
            if (createdEndTime != null && !createdEndTime.trim().isEmpty()) {
                try {
                    queryWrapper.le(LbDailyUserTradeReport::getCreatedAt, LocalDateTime.parse(createdEndTime.trim(), formatter));
                } catch (Exception e) {
                    result.put("success", false);
                    result.put("message", "创建结束时间格式错误，请使用 yyyy-MM-dd HH:mm:ss");
                    return ResponseEntity.badRequest().body(result);
                }
            }

            queryWrapper.orderByDesc(LbDailyUserTradeReport::getDiffAmt)
                    .orderByDesc(LbDailyUserTradeReport::getCreatedAt);
            Page<LbDailyUserTradeReport> page = lbDailyUserTradeReportService.page(new Page<>(current, size), queryWrapper);

            result.put("success", true);
            result.put("message", "查询成功");
            result.put("data", page.getRecords());
            result.put("total", page.getTotal());
            result.put("current", page.getCurrent());
            result.put("size", page.getSize());
            result.put("pages", page.getPages());
            return ResponseEntity.ok(result);
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "查询异常：" + e.getMessage());
            return ResponseEntity.internalServerError().body(result);
        }
    }

    @GetMapping("/list/daily-user-trade/by-user-id-and-date-range")
    @Operation(summary = "按用户ID和日期范围分页查询交易明细", description = "根据开始日期、结束日期和用户ID，从lb_daily_user_trade分页查询交易数据")
    public ResponseEntity<Map<String, Object>> listDailyUserTradeByUserIdAndDateRange(
            @Parameter(description = "页码", example = "1") @RequestParam(defaultValue = "1") Integer current,
            @Parameter(description = "每页大小", example = "10") @RequestParam(defaultValue = "10") Integer size,
            @Parameter(description = "开始日期，格式：yyyy-MM-dd", required = true) @RequestParam String startDate,
            @Parameter(description = "结束日期，格式：yyyy-MM-dd", required = true) @RequestParam String endDate,
            @Parameter(description = "用户ID", required = true) @RequestParam String userId) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (userId == null || userId.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "userId不能为空");
                return ResponseEntity.badRequest().body(result);
            }
            if (startDate == null || startDate.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "startDate不能为空");
                return ResponseEntity.badRequest().body(result);
            }
            if (endDate == null || endDate.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "endDate不能为空");
                return ResponseEntity.badRequest().body(result);
            }

            LocalDate parsedStartDate;
            LocalDate parsedEndDate;
            try {
                parsedStartDate = LocalDate.parse(startDate.trim());
            } catch (Exception e) {
                result.put("success", false);
                result.put("message", "startDate格式错误，请使用 yyyy-MM-dd");
                return ResponseEntity.badRequest().body(result);
            }
            try {
                parsedEndDate = LocalDate.parse(endDate.trim());
            } catch (Exception e) {
                result.put("success", false);
                result.put("message", "endDate格式错误，请使用 yyyy-MM-dd");
                return ResponseEntity.badRequest().body(result);
            }
            if (parsedEndDate.isBefore(parsedStartDate)) {
                result.put("success", false);
                result.put("message", "endDate不能早于startDate");
                return ResponseEntity.badRequest().body(result);
            }

            Page<LbDailyUserTrade> page = lbDailyUserTradeReportService.pageDailyUserTradeByUserIdAndDateRange(
                    parsedStartDate, parsedEndDate, userId.trim(), current, size);

            result.put("success", true);
            result.put("message", "查询成功");
            result.put("data", page.getRecords());
            result.put("total", page.getTotal());
            result.put("current", page.getCurrent());
            result.put("size", page.getSize());
            result.put("pages", page.getPages());
            return ResponseEntity.ok(result);
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "查询异常：" + e.getMessage());
            return ResponseEntity.internalServerError().body(result);
        }
    }

    @GetMapping("/list/daily-user-trade/by-parent-id-of-user-and-date-range")
    @Operation(summary = "按用户ID和日期范围分页查询父节点的交易明细",
            description = "根据用户ID查询lb_department_user的parent_id，再按user_id=parent_id从lb_daily_user_trade分页查询指定日期范围交易数据")
    public ResponseEntity<Map<String, Object>> listDailyUserTradeByParentIdOfUserAndDateRange(
            @Parameter(description = "页码", example = "1") @RequestParam(defaultValue = "1") Integer current,
            @Parameter(description = "每页大小", example = "10") @RequestParam(defaultValue = "10") Integer size,
            @Parameter(description = "开始日期，格式：yyyy-MM-dd", required = true) @RequestParam String startDate,
            @Parameter(description = "结束日期，格式：yyyy-MM-dd", required = true) @RequestParam String endDate,
            @Parameter(description = "用户ID", required = true) @RequestParam String userId) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (userId == null || userId.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "userId不能为空");
                return ResponseEntity.badRequest().body(result);
            }
            if (startDate == null || startDate.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "startDate不能为空");
                return ResponseEntity.badRequest().body(result);
            }
            if (endDate == null || endDate.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "endDate不能为空");
                return ResponseEntity.badRequest().body(result);
            }

            LocalDate parsedStartDate;
            LocalDate parsedEndDate;
            try {
                parsedStartDate = LocalDate.parse(startDate.trim());
            } catch (Exception e) {
                result.put("success", false);
                result.put("message", "startDate格式错误，请使用 yyyy-MM-dd");
                return ResponseEntity.badRequest().body(result);
            }
            try {
                parsedEndDate = LocalDate.parse(endDate.trim());
            } catch (Exception e) {
                result.put("success", false);
                result.put("message", "endDate格式错误，请使用 yyyy-MM-dd");
                return ResponseEntity.badRequest().body(result);
            }
            if (parsedEndDate.isBefore(parsedStartDate)) {
                result.put("success", false);
                result.put("message", "endDate不能早于startDate");
                return ResponseEntity.badRequest().body(result);
            }

            Page<LbDailyUserTrade> page = lbDailyUserTradeReportService.pageDailyUserTradeByParentIdOfUserAndDateRange(
                    parsedStartDate, parsedEndDate, userId.trim(), current, size);

            result.put("success", true);
            result.put("message", "查询成功");
            result.put("data", page.getRecords());
            result.put("total", page.getTotal());
            result.put("current", page.getCurrent());
            result.put("size", page.getSize());
            result.put("pages", page.getPages());
            return ResponseEntity.ok(result);
        } catch (IllegalArgumentException e) {
            result.put("success", false);
            result.put("message", e.getMessage());
            return ResponseEntity.badRequest().body(result);
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "查询异常：" + e.getMessage());
            return ResponseEntity.internalServerError().body(result);
        }
    }

    @PutMapping("/update")
    @Operation(summary = "修改报表记录", description = "根据ID修改当天用户交易报表记录")
    public ResponseEntity<Map<String, Object>> update(@RequestBody LbDailyUserTradeReport tradeReport) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (tradeReport.getId() == null || tradeReport.getId().trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "ID不能为空");
                return ResponseEntity.badRequest().body(result);
            }
            tradeReport.setUpdatedAt(LocalDateTime.now());
            boolean success = lbDailyUserTradeReportService.updateById(tradeReport);
            if (success) {
                result.put("success", true);
                result.put("message", "修改成功");
                result.put("data", tradeReport);
                return ResponseEntity.ok(result);
            }
            result.put("success", false);
            result.put("message", "修改失败");
            return ResponseEntity.badRequest().body(result);
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "修改异常：" + e.getMessage());
            return ResponseEntity.internalServerError().body(result);
        }
    }

    @DeleteMapping("/delete/{id}")
    @Operation(summary = "删除报表记录", description = "根据ID删除当天用户交易报表记录")
    public ResponseEntity<Map<String, Object>> delete(
            @Parameter(description = "主键ID", required = true) @PathVariable String id) {
        Map<String, Object> result = new HashMap<>();
        try {
            boolean success = lbDailyUserTradeReportService.removeById(id);
            if (success) {
                result.put("success", true);
                result.put("message", "删除成功");
                return ResponseEntity.ok(result);
            }
            result.put("success", false);
            result.put("message", "删除失败");
            return ResponseEntity.badRequest().body(result);
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "删除异常：" + e.getMessage());
            return ResponseEntity.internalServerError().body(result);
        }
    }

    @DeleteMapping("/delete/by-date-and-tenant")
    @Operation(summary = "按日期和租户删除报表", description = "根据报表日期和租户ID删除当天用户交易报表记录")
    public ResponseEntity<Map<String, Object>> deleteByDateAndTenant(
            @Parameter(description = "报表日期，格式：yyyy-MM-dd", required = true)
            @RequestParam String reportDate,
            @Parameter(description = "租户ID", required = true)
            @RequestParam String tenantId) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (tenantId == null || tenantId.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "tenantId不能为空");
                return ResponseEntity.badRequest().body(result);
            }
            if (reportDate == null || reportDate.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "reportDate不能为空");
                return ResponseEntity.badRequest().body(result);
            }

            LocalDate parsedReportDate;
            try {
                parsedReportDate = LocalDate.parse(reportDate.trim());
            } catch (Exception e) {
                result.put("success", false);
                result.put("message", "报表日期格式错误，请使用 yyyy-MM-dd");
                return ResponseEntity.badRequest().body(result);
            }

            LocalDateTime start = parsedReportDate.atStartOfDay();
            LocalDateTime end = parsedReportDate.plusDays(1).atStartOfDay();

            LambdaQueryWrapper<LbDailyUserTradeReport> queryWrapper = new LambdaQueryWrapper<>();
            queryWrapper.eq(LbDailyUserTradeReport::getTenantId, tenantId.trim())
                    .ge(LbDailyUserTradeReport::getReportDate, start)
                    .lt(LbDailyUserTradeReport::getReportDate, end);

            long deleteCount = lbDailyUserTradeReportService.count(queryWrapper);
            if (deleteCount == 0) {
                result.put("success", true);
                result.put("message", "未匹配到可删除记录");
                result.put("deleteCount", 0);
                return ResponseEntity.ok(result);
            }

            boolean success = lbDailyUserTradeReportService.remove(queryWrapper);
            if (success) {
                result.put("success", true);
                result.put("message", "删除成功");
                result.put("deleteCount", deleteCount);
                return ResponseEntity.ok(result);
            }
            result.put("success", false);
            result.put("message", "删除失败");
            return ResponseEntity.badRequest().body(result);
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "删除异常：" + e.getMessage());
            return ResponseEntity.internalServerError().body(result);
        }
    }

    @GetMapping("/list/yestoday-buy-positive-and-daily-sell-zero")
    @Operation(summary = "按日期范围查询昨日买入大于0且当日卖出为0的报表", description = "根据开始日期、结束日期和租户ID查询 reportDate 范围内 yestodayBuyAmt > 0 且 dailySellAmt = 0 的记录")
    public ResponseEntity<Map<String, Object>> listByDateRangeWithBuyPositiveAndSellZero(
            @Parameter(description = "开始日期，格式：yyyy-MM-dd", required = true)
            @RequestParam String startDate,
            @Parameter(description = "结束日期，格式：yyyy-MM-dd", required = true)
            @RequestParam String endDate,
            @Parameter(description = "租户ID", required = true)
            @RequestParam String tenantId) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (tenantId == null || tenantId.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "tenantId不能为空");
                return ResponseEntity.badRequest().body(result);
            }
            if (startDate == null || startDate.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "startDate不能为空");
                return ResponseEntity.badRequest().body(result);
            }
            if (endDate == null || endDate.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "endDate不能为空");
                return ResponseEntity.badRequest().body(result);
            }

            LocalDate parsedStartDate;
            LocalDate parsedEndDate;
            try {
                parsedStartDate = LocalDate.parse(startDate.trim());
            } catch (Exception e) {
                result.put("success", false);
                result.put("message", "startDate格式错误，请使用 yyyy-MM-dd");
                return ResponseEntity.badRequest().body(result);
            }
            try {
                parsedEndDate = LocalDate.parse(endDate.trim());
            } catch (Exception e) {
                result.put("success", false);
                result.put("message", "endDate格式错误，请使用 yyyy-MM-dd");
                return ResponseEntity.badRequest().body(result);
            }

            if (parsedEndDate.isBefore(parsedStartDate)) {
                result.put("success", false);
                result.put("message", "endDate不能早于startDate");
                return ResponseEntity.badRequest().body(result);
            }

            LocalDateTime start = parsedStartDate.atStartOfDay();
            LocalDateTime end = parsedEndDate.atTime(23, 59, 59);

            LambdaQueryWrapper<LbDailyUserTradeReport> queryWrapper = new LambdaQueryWrapper<>();
            queryWrapper.eq(LbDailyUserTradeReport::getTenantId, tenantId.trim())
                    .ge(LbDailyUserTradeReport::getReportDate, start)
                    .le(LbDailyUserTradeReport::getReportDate, end)
                    .gt(LbDailyUserTradeReport::getYestodayBuyAmt, BigDecimal.ZERO)
                    .eq(LbDailyUserTradeReport::getDailySellAmt, BigDecimal.ZERO)
                    .orderByDesc(LbDailyUserTradeReport::getReportDate)
                    .orderByDesc(LbDailyUserTradeReport::getCreatedAt);

            List<LbDailyUserTradeReport> rows = lbDailyUserTradeReportService.list(queryWrapper);
            result.put("success", true);
            result.put("message", "查询成功");
            result.put("data", rows);
            result.put("total", rows.size());
            return ResponseEntity.ok(result);
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "查询异常：" + e.getMessage());
            return ResponseEntity.internalServerError().body(result);
        }
    }

    @GetMapping("/stats/user-trade-with-team/by-date-range-and-tenant")
    @Operation(summary = "按日期范围统计本人及递归下级交易金额", description = "先查 lb_daily_user_trade_report（日期+租户，排除 report_sum）圈定 user_id；再查 lb_department_user（同租户）用 parent_id（父 user_id）建上下级；对 yestoday_buy_amt、daily_sell_amt、daily_buy_amt、diff_amt 分别汇总 own*/team*/total*（team 为递归下级、不含本人）。仅返回报表范围内有数据的用户。")
    public ResponseEntity<Map<String, Object>> statsUserTradeWithTeamByDateRangeAndTenant(
            @Parameter(description = "开始日期，格式：yyyy-MM-dd", required = true)
            @RequestParam String startDate,
            @Parameter(description = "结束日期，格式：yyyy-MM-dd", required = true)
            @RequestParam String endDate,
            @Parameter(description = "租户ID", required = true)
            @RequestParam String tenantId) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (tenantId == null || tenantId.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "tenantId不能为空");
                return ResponseEntity.badRequest().body(result);
            }
            if (startDate == null || startDate.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "startDate不能为空");
                return ResponseEntity.badRequest().body(result);
            }
            if (endDate == null || endDate.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "endDate不能为空");
                return ResponseEntity.badRequest().body(result);
            }

            LocalDate parsedStartDate;
            LocalDate parsedEndDate;
            try {
                parsedStartDate = LocalDate.parse(startDate.trim());
            } catch (Exception e) {
                result.put("success", false);
                result.put("message", "startDate格式错误，请使用 yyyy-MM-dd");
                return ResponseEntity.badRequest().body(result);
            }
            try {
                parsedEndDate = LocalDate.parse(endDate.trim());
            } catch (Exception e) {
                result.put("success", false);
                result.put("message", "endDate格式错误，请使用 yyyy-MM-dd");
                return ResponseEntity.badRequest().body(result);
            }

            Map<String, Object> serviceResult = lbDailyUserTradeReportService
                    .listUserTradeAmountWithTeamByDateRangeAndTenant(parsedStartDate, parsedEndDate, tenantId.trim());
            Boolean success = (Boolean) serviceResult.get("success");
            if (Boolean.FALSE.equals(success)) {
                return ResponseEntity.badRequest().body(serviceResult);
            }
            return ResponseEntity.ok(serviceResult);
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "统计异常：" + e.getMessage());
            return ResponseEntity.internalServerError().body(result);
        }
    }

    @PostMapping("/calculate-report-sum/by-date-and-tenant")
    @Operation(summary = "按日期和租户汇总报表", description = "根据报表日期和租户ID汇总报表字段并新增一条 report_sum 记录")
    public ResponseEntity<Map<String, Object>> calculateReportSumByDateAndTenant(
            @Parameter(description = "报表日期，格式：yyyy-MM-dd", required = true)
            @RequestParam String reportDate,
            @Parameter(description = "租户ID", required = true)
            @RequestParam String tenantId) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (tenantId == null || tenantId.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "tenantId不能为空");
                return ResponseEntity.badRequest().body(result);
            }
            if (reportDate == null || reportDate.trim().isEmpty()) {
                result.put("success", false);
                result.put("message", "reportDate不能为空");
                return ResponseEntity.badRequest().body(result);
            }

            LocalDate parsedReportDate;
            try {
                parsedReportDate = LocalDate.parse(reportDate.trim());
            } catch (Exception e) {
                result.put("success", false);
                result.put("message", "报表日期格式错误，请使用 yyyy-MM-dd");
                return ResponseEntity.badRequest().body(result);
            }

            Map<String, Object> serviceResult = lbDailyUserTradeReportService
                    .calculateReportSumByDateAndTenant(parsedReportDate, tenantId.trim());
            Boolean success = (Boolean) serviceResult.get("success");
            if (Boolean.TRUE.equals(success)) {
                return ResponseEntity.ok(serviceResult);
            }
            return ResponseEntity.badRequest().body(serviceResult);
        } catch (Exception e) {
            result.put("success", false);
            result.put("message", "汇总异常：" + e.getMessage());
            return ResponseEntity.internalServerError().body(result);
        }
    }

    @GetMapping("/export/by-date-and-tenant")
    @Operation(summary = "按日期和租户导出报表Excel", description = "导出 reportDate 指定日期、tenantId 指定租户的报表数据到 Excel")
    public void exportByDateAndTenant(
            @Parameter(description = "报表日期，格式：yyyy-MM-dd", required = true)
            @RequestParam String reportDate,
            @Parameter(description = "租户ID", required = true)
            @RequestParam String tenantId,
            HttpServletResponse response) {
        try {
            if (tenantId == null || tenantId.trim().isEmpty()) {
                response.sendError(HttpServletResponse.SC_BAD_REQUEST, "tenantId不能为空");
                return;
            }
            if (reportDate == null || reportDate.trim().isEmpty()) {
                response.sendError(HttpServletResponse.SC_BAD_REQUEST, "reportDate不能为空");
                return;
            }

            LocalDate parsedReportDate;
            try {
                parsedReportDate = LocalDate.parse(reportDate.trim());
            } catch (Exception e) {
                response.sendError(HttpServletResponse.SC_BAD_REQUEST, "报表日期格式错误，请使用 yyyy-MM-dd");
                return;
            }

            LocalDateTime start = parsedReportDate.atStartOfDay();
            LocalDateTime end = parsedReportDate.plusDays(1).atStartOfDay();

            LambdaQueryWrapper<LbDailyUserTradeReport> queryWrapper = new LambdaQueryWrapper<>();
            queryWrapper.eq(LbDailyUserTradeReport::getTenantId, tenantId.trim())
                    .ge(LbDailyUserTradeReport::getReportDate, start)
                    .lt(LbDailyUserTradeReport::getReportDate, end)
                    .orderByDesc(LbDailyUserTradeReport::getCreatedAt);

            List<LbDailyUserTradeReport> rows = lbDailyUserTradeReportService.list(queryWrapper);
            rows.sort(
                    Comparator
                            .comparingInt((LbDailyUserTradeReport o) -> "report_sum".equals(o.getDataType()) ? 1 : 0)
                            .thenComparing((a, b) -> {
                                BigDecimal da = a.getDiffAmt() == null ? BigDecimal.ZERO : a.getDiffAmt();
                                BigDecimal db = b.getDiffAmt() == null ? BigDecimal.ZERO : b.getDiffAmt();
                                return db.compareTo(da);
                            })
            );

            try (Workbook workbook = new XSSFWorkbook()) {
                Sheet sheet = workbook.createSheet("report");

                DateTimeFormatter mmdd = DateTimeFormatter.ofPattern("MM-dd");
                String d0 = parsedReportDate.format(mmdd);
                String d1 = parsedReportDate.minusDays(1).format(mmdd);

                float rowHeightFactor = 1.3f;
                sheet.setDefaultRowHeightInPoints(sheet.getDefaultRowHeightInPoints() * rowHeightFactor);

                DataFormat dataFormat = workbook.createDataFormat();

                CellStyle headerStyle = workbook.createCellStyle();
                Font headerFont = workbook.createFont();
                headerFont.setBold(true);
                headerFont.setColor(IndexedColors.DARK_BLUE.getIndex());
                headerStyle.setFont(headerFont);
                headerStyle.setAlignment(HorizontalAlignment.CENTER);
                headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                headerStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
                headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                headerStyle.setBorderTop(BorderStyle.THIN);
                headerStyle.setBorderBottom(BorderStyle.THIN);
                headerStyle.setBorderLeft(BorderStyle.THIN);
                headerStyle.setBorderRight(BorderStyle.THIN);

                CellStyle textStyle = workbook.createCellStyle();
                textStyle.setAlignment(HorizontalAlignment.LEFT);
                textStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                textStyle.setBorderTop(BorderStyle.THIN);
                textStyle.setBorderBottom(BorderStyle.THIN);
                textStyle.setBorderLeft(BorderStyle.THIN);
                textStyle.setBorderRight(BorderStyle.THIN);

                CellStyle zebraTextStyle = workbook.createCellStyle();
                zebraTextStyle.cloneStyleFrom(textStyle);
                zebraTextStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

                CellStyle intStyle = workbook.createCellStyle();
                intStyle.setAlignment(HorizontalAlignment.RIGHT);
                intStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                intStyle.setDataFormat(dataFormat.getFormat("#,##0"));
                intStyle.setBorderTop(BorderStyle.THIN);
                intStyle.setBorderBottom(BorderStyle.THIN);
                intStyle.setBorderLeft(BorderStyle.THIN);
                intStyle.setBorderRight(BorderStyle.THIN);

                CellStyle zebraIntStyle = workbook.createCellStyle();
                zebraIntStyle.cloneStyleFrom(intStyle);
                zebraIntStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

                // 更淡的斑马纹底色（接近白色）
                XSSFColor zebraFill = new XSSFColor(new Color(242, 244, 248), null);
                ((XSSFCellStyle) zebraTextStyle).setFillForegroundColor(zebraFill);
                ((XSSFCellStyle) zebraIntStyle).setFillForegroundColor(zebraFill);

                CellStyle summaryTextStyle = workbook.createCellStyle();
                summaryTextStyle.cloneStyleFrom(textStyle);
                summaryTextStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
                summaryTextStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                Font summaryFont = workbook.createFont();
                summaryFont.setBold(true);
                summaryTextStyle.setFont(summaryFont);
                summaryTextStyle.setBorderTop(BorderStyle.THIN);

                CellStyle summaryIntStyle = workbook.createCellStyle();
                summaryIntStyle.cloneStyleFrom(intStyle);
                summaryIntStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
                summaryIntStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                summaryIntStyle.setFont(summaryFont);
                summaryIntStyle.setBorderTop(BorderStyle.THIN);

                int r = 0;
                Row header = sheet.createRow(r++);
                header.setHeightInPoints(20 * rowHeightFactor);
                createHeaderCell(header, 0, "昵称", headerStyle);
                createHeaderCell(header, 1, d1 + "买货", headerStyle);
                createHeaderCell(header, 2, d0 + "卖货", headerStyle);
                createHeaderCell(header, 3, d0 + "买货", headerStyle);
                createHeaderCell(header, 4, "服务费", headerStyle);
                createHeaderCell(header, 5, "差额", headerStyle);
                createHeaderCell(header, 6, "应收应付", headerStyle);
                createHeaderCell(header, 7, "抵扣金额", headerStyle);

                sheet.createFreezePane(0, 1);
                sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, 7));

                for (LbDailyUserTradeReport item : rows) {
                    Row row = sheet.createRow(r++);
                    row.setHeightInPoints(row.getHeightInPoints() * rowHeightFactor);
                    boolean isSummary = "report_sum".equals(item.getDataType());
                    boolean zebra = (r % 2 == 0);

                    CellStyle tStyle = isSummary ? summaryTextStyle : (zebra ? zebraTextStyle : textStyle);
                    CellStyle nStyle = isSummary ? summaryIntStyle : (zebra ? zebraIntStyle : intStyle);

                    createTextCell(row, 0, item.getNickname() == null ? "" : item.getNickname(), tStyle);
                    createIntCell(row, 1, roundedInt(item.getYestodayBuyAmt()), nStyle);
                    createIntCell(row, 2, roundedInt(item.getDailySellAmt()), nStyle);
                    createIntCell(row, 3, roundedInt(item.getDailyBuyAmt()), nStyle);
                    createIntCell(row, 4, roundedInt(item.getServiceAmt()), nStyle);
                    createIntCell(row, 5, roundedInt(item.getDiffAmt()), nStyle);
                    createIntCell(row, 6, roundedInt(item.getActualReceiptsPayments()), nStyle);
                    createIntCell(row, 7, roundedInt(item.getDikouAmt()), nStyle);
                }

                if (r > 1) {
                    SheetConditionalFormatting scf = sheet.getSheetConditionalFormatting();
                    ConditionalFormattingRule negative = scf.createConditionalFormattingRule(ComparisonOperator.LT, "0");
                    FontFormatting ff = negative.createFontFormatting();
                    ff.setFontColorIndex(IndexedColors.DARK_RED.getIndex());
                    ff.setFontStyle(false, false);
                    scf.addConditionalFormatting(new CellRangeAddress[]{new CellRangeAddress(1, r - 1, 5, 5)}, negative);
                }

                for (int i = 0; i <= 7; i++) {
                    sheet.autoSizeColumn(i);
                    int currentWidth = sheet.getColumnWidth(i);
                    int widened = (int) Math.min(255 * 256, Math.round(currentWidth * 1.5));
                    sheet.setColumnWidth(i, widened);
                }

                String filename = "当日汇总报表" + parsedReportDate.format(DateTimeFormatter.ofPattern("yyyyMMdd")) + ".xlsx";
                String encoded = URLEncoder.encode(filename, StandardCharsets.UTF_8).replaceAll("\\+", "%20");

                response.setCharacterEncoding(StandardCharsets.UTF_8.name());
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename*=UTF-8''" + encoded);

                try (ServletOutputStream os = response.getOutputStream()) {
                    workbook.write(os);
                    os.flush();
                }
            }
        } catch (Exception e) {
            try {
                response.reset();
                response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                response.setCharacterEncoding(StandardCharsets.UTF_8.name());
                response.setContentType("text/plain;charset=UTF-8");
                response.getWriter().write("导出异常：" + e.getMessage());
            } catch (Exception ignored) {
                // ignore
            }
        }
    }

    private static long roundedInt(BigDecimal value) {
        if (value == null) {
            return 0L;
        }
        return value.setScale(0, RoundingMode.HALF_UP).longValue();
    }

    private static void createHeaderCell(Row row, int col, String text, CellStyle style) {
        Cell cell = row.createCell(col);
        cell.setCellValue(text);
        cell.setCellStyle(style);
    }

    private static void createTextCell(Row row, int col, String text, CellStyle style) {
        Cell cell = row.createCell(col);
        cell.setCellValue(text);
        cell.setCellStyle(style);
    }

    private static void createIntCell(Row row, int col, long value, CellStyle style) {
        Cell cell = row.createCell(col);
        cell.setCellValue((double) value);
        cell.setCellStyle(style);
    }
}
