package com.erp.report.service.impl;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.erp.report.dto.*;
import com.erp.report.repository.*;
import com.erp.report.service.ReportService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 报表统计服务实现
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class ReportServiceImpl implements ReportService {

    private final OrderRepository orderRepository;
    private final StockRepository stockRepository;
    private final PullRecordRepository pullRecordRepository;
    private final PurchaseOrderRepository purchaseOrderRepository;

    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");

    // ==================== 订单统计 ====================

    @Override
    public OrderReportResponse getOrderReport(OrderReportRequest request) {
        log.debug("获取订单统计, groupBy={}, platform={}, shopId={}, startDate={}, endDate={}",
                request.getGroupBy(), request.getPlatform(), request.getShopId(),
                request.getStartDate(), request.getEndDate());

        LocalDateTime startDateTime = parseStartDateTime(request.getStartDate());
        LocalDateTime endDateTime = parseEndDateTime(request.getEndDate());

        List<OrderReportResponse.OrderStatItem> items;
        long total;
        int pageSize = request.getPageSize() != null ? request.getPageSize() : 20;

        if ("day".equals(request.getGroupBy()) || "month".equals(request.getGroupBy())) {
            String dateFormat = "day".equals(request.getGroupBy()) ? "%Y-%m-%d" : "%Y-%m";
            List<Map<String, Object>> rawData = orderRepository.selectGroupByDate(
                    request.getPlatform(),
                    request.getShopId(),
                    startDateTime,
                    endDateTime,
                    dateFormat,
                    request.getTrend() != null ? request.getTrend() : "desc"
            );
            items = rawData.stream()
                    .map(this::mapToOrderStatItemByDate)
                    .collect(Collectors.toList());
            total = items.size();
        } else if ("platform".equals(request.getGroupBy())) {
            List<Map<String, Object>> rawData = orderRepository.selectGroupByPlatform(
                    request.getPlatform(),
                    request.getShopId(),
                    startDateTime,
                    endDateTime,
                    request.getTrend() != null ? request.getTrend() : "desc"
            );
            items = rawData.stream()
                    .map(this::mapToOrderStatItemByPlatform)
                    .collect(Collectors.toList());
            total = items.size();
        } else {
            // shop
            List<Map<String, Object>> rawData = orderRepository.selectGroupByShop(
                    request.getPlatform(),
                    request.getShopId(),
                    startDateTime,
                    endDateTime,
                    request.getTrend() != null ? request.getTrend() : "desc"
            );
            items = rawData.stream()
                    .map(this::mapToOrderStatItemByShop)
                    .collect(Collectors.toList());
            total = items.size();
        }

        // 分页
        int page = request.getPage() != null ? request.getPage() : 1;
        int fromIndex = (page - 1) * pageSize;
        int toIndex = Math.min(fromIndex + pageSize, items.size());
        List<OrderReportResponse.OrderStatItem> pagedItems = fromIndex < items.size()
                ? items.subList(fromIndex, toIndex)
                : Collections.emptyList();

        return OrderReportResponse.builder()
                .groupBy(request.getGroupBy())
                .list(pagedItems)
                .pagination(OrderReportResponse.PaginationInfo.builder()
                        .currentPage(page)
                        .perPage(pageSize)
                        .total(total)
                        .lastPage((int) Math.ceil((double) total / pageSize))
                        .build())
                .build();
    }

    private OrderReportResponse.OrderStatItem mapToOrderStatItemByDate(Map<String, Object> row) {
        String groupBy = "";
        // determine if it's day or month based on the date string length
        String dateKey = row.get("dateKey") != null ? row.get("dateKey").toString() : "";
        if (dateKey.length() == 7) {
            groupBy = "month";
        } else {
            groupBy = "day";
        }
        
        return OrderReportResponse.OrderStatItem.builder()
                .date(dateKey)
                .month(groupBy.equals("month") ? dateKey : null)
                .orderCount(toLong(row.get("orderCount")))
                .totalAmount(toBigDecimal(row.get("totalAmount")))
                .goodsAmount(toBigDecimal(row.get("goodsAmount")))
                .freight(toBigDecimal(row.get("freight")))
                .build();
    }

    private OrderReportResponse.OrderStatItem mapToOrderStatItemByPlatform(Map<String, Object> row) {
        return OrderReportResponse.OrderStatItem.builder()
                .platform(row.get("platform") != null ? row.get("platform").toString() : null)
                .orderCount(toLong(row.get("orderCount")))
                .totalAmount(toBigDecimal(row.get("totalAmount")))
                .goodsAmount(toBigDecimal(row.get("goodsAmount")))
                .freight(toBigDecimal(row.get("freight")))
                .completedCount(toLong(row.get("completedCount")))
                .cancelledCount(toLong(row.get("cancelledCount")))
                .build();
    }

    private OrderReportResponse.OrderStatItem mapToOrderStatItemByShop(Map<String, Object> row) {
        return OrderReportResponse.OrderStatItem.builder()
                .shopId(toLong(row.get("shop_id")))
                .shopName(row.get("shop_name") != null ? row.get("shop_name").toString() : null)
                .platform(row.get("platform") != null ? row.get("platform").toString() : null)
                .orderCount(toLong(row.get("orderCount")))
                .totalAmount(toBigDecimal(row.get("totalAmount")))
                .goodsAmount(toBigDecimal(row.get("goodsAmount")))
                .freight(toBigDecimal(row.get("freight")))
                .completedCount(toLong(row.get("completedCount")))
                .cancelledCount(toLong(row.get("cancelledCount")))
                .build();
    }

    // ==================== 销售统计 ====================

    @Override
    public SalesReportResponse getSalesReport(SalesReportRequest request) {
        log.debug("获取销售统计, period={}, platform={}, shopId={}, startDate={}, endDate={}",
                request.getPeriod(), request.getPlatform(), request.getShopId(),
                request.getStartDate(), request.getEndDate());

        String startDate = request.getStartDate() != null ? request.getStartDate() 
                : LocalDate.now().minusDays(30).format(DATE_FORMATTER);
        String endDate = request.getEndDate() != null ? request.getEndDate() 
                : LocalDate.now().format(DATE_FORMATTER);

        LocalDateTime startDateTime = LocalDateTime.parse(startDate + " 00:00:00", 
                DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        LocalDateTime endDateTime = LocalDateTime.parse(endDate + " 23:59:59",
                DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

        String dateFormat = switch (request.getPeriod()) {
            case "week" -> "%x-W%v";
            case "month" -> "%Y-%m";
            default -> "%Y-%m-%d";
        };

        String trend = request.getTrend() != null ? request.getTrend() : "asc";

        // 汇总
        Map<String, Object> summaryRaw = orderRepository.selectSalesSummary(
                request.getPlatform(), request.getShopId(), startDateTime, endDateTime);
        SalesReportResponse.SalesSummary summary = SalesReportResponse.SalesSummary.builder()
                .totalOrders(toLong(summaryRaw.get("totalOrders")))
                .totalSales(toBigDecimal(summaryRaw.get("totalSales")))
                .avgOrderAmount(toBigDecimal(summaryRaw.get("avgOrderAmount")))
                .completedCount(toLong(summaryRaw.get("completedCount")))
                .build();

        // 列表
        List<Map<String, Object>> rawData = orderRepository.selectSalesByDate(
                request.getPlatform(), request.getShopId(), startDateTime, endDateTime,
                dateFormat, trend);
        
        List<SalesReportResponse.SalesStatItem> items = rawData.stream()
                .map(row -> {
                    String periodKey = row.get("dateKey") != null ? row.get("dateKey").toString() : "";
                    return SalesReportResponse.SalesStatItem.builder()
                            .date("day".equals(request.getPeriod()) ? periodKey : null)
                            .week("week".equals(request.getPeriod()) ? periodKey : null)
                            .month("month".equals(request.getPeriod()) ? periodKey : null)
                            .orderCount(toLong(row.get("orderCount")))
                            .salesAmount(toBigDecimal(row.get("salesAmount")))
                            .goodsAmount(toBigDecimal(row.get("goodsAmount")))
                            .avgOrderAmount(toBigDecimal(row.get("avgOrderAmount")))
                            .completedCount(toLong(row.get("completedCount")))
                            .build();
                })
                .collect(Collectors.toList());

        // 分页
        int page = request.getPage() != null ? request.getPage() : 1;
        int pageSize = request.getPageSize() != null ? request.getPageSize() : 30;
        long total = items.size();
        int fromIndex = (page - 1) * pageSize;
        int toIndex = Math.min(fromIndex + pageSize, items.size());
        List<SalesReportResponse.SalesStatItem> pagedItems = fromIndex < items.size()
                ? items.subList(fromIndex, toIndex)
                : Collections.emptyList();

        return SalesReportResponse.builder()
                .period(request.getPeriod())
                .summary(summary)
                .list(pagedItems)
                .pagination(SalesReportResponse.PaginationInfo.builder()
                        .currentPage(page)
                        .perPage(pageSize)
                        .total(total)
                        .lastPage((int) Math.ceil((double) total / pageSize))
                        .build())
                .build();
    }

    // ==================== 库存统计 ====================

    @Override
    @Cacheable(value = "stockReport", key = "#request.type + '-' + #request.warehouseId + '-' + #request.slowDays")
    public StockReportResponse getStockReport(StockReportRequest request) {
        log.debug("获取库存统计, type={}, warehouseId={}, slowDays={}",
                request.getType(), request.getWarehouseId(), request.getSlowDays());

        if ("overview".equals(request.getType())) {
            return getStockOverview(request);
        } else if ("slow_moving".equals(request.getType())) {
            return getSlowMovingStocks(request);
        } else if ("turnover".equals(request.getType())) {
            return getTurnoverStocks(request);
        }

        throw new IllegalArgumentException("未知的统计类型: " + request.getType());
    }

    private StockReportResponse getStockOverview(StockReportRequest request) {
        Map<String, Object> raw = stockRepository.selectStockOverview(request.getWarehouseId());
        
        String cutoffDate = LocalDate.now().minusDays(request.getSlowDays() != null ? request.getSlowDays() : 30)
                .format(DATE_FORMATTER);
        
        // 呆滞库存数量（独立查询）
        List<Map<String, Object>> slowMoving = stockRepository.selectSlowMovingStocks(
                request.getWarehouseId(), cutoffDate, "desc");
        long slowMovingCount = slowMoving.size();

        return StockReportResponse.builder()
                .type("overview")
                .totalStocks(toLong(raw.get("totalStocks")))
                .totalQuantity(toLong(raw.get("totalQuantity")))
                .totalAvailable(toLong(raw.get("totalAvailable")))
                .lowStockCount(toLong(raw.get("lowStockCount")))
                .outOfStockCount(toLong(raw.get("outOfStockCount")))
                .slowMovingCount(slowMovingCount)
                .build();
    }

    private StockReportResponse getSlowMovingStocks(StockReportRequest request) {
        int slowDays = request.getSlowDays() != null ? request.getSlowDays() : 30;
        String cutoffDate = LocalDate.now().minusDays(slowDays).format(DATE_FORMATTER);
        String trend = request.getTrend() != null ? request.getTrend() : "desc";

        List<Map<String, Object>> rawData = stockRepository.selectSlowMovingStocks(
                request.getWarehouseId(), cutoffDate, trend);

        List<StockReportResponse.StockStatItem> items = rawData.stream()
                .map(row -> {
                    LocalDateTime lastMoveAt = row.get("last_move_at") != null 
                            ? toLocalDateTime(row.get("last_move_at")) : null;
                    Integer daysSinceMove = lastMoveAt != null 
                            ? (int) java.time.temporal.ChronoUnit.DAYS.between(lastMoveAt.toLocalDate(), LocalDate.now())
                            : null;
                    return StockReportResponse.StockStatItem.builder()
                            .skuCode(row.get("sku_code") != null ? row.get("sku_code").toString() : null)
                            .skuName(row.get("sku_name") != null ? row.get("sku_name").toString() : null)
                            .warehouseId(toLong(row.get("warehouse_id")))
                            .warehouseName(row.get("warehouse_name") != null ? row.get("warehouse_name").toString() : null)
                            .quantity(toInteger(row.get("quantity")))
                            .available(toInteger(row.get("available")))
                            .lastMoveAt(lastMoveAt)
                            .daysSinceMove(daysSinceMove)
                            .isSlow(daysSinceMove != null && daysSinceMove >= slowDays)
                            .build();
                })
                .collect(Collectors.toList());

        int page = request.getPage() != null ? request.getPage() : 1;
        int pageSize = request.getPageSize() != null ? request.getPageSize() : 20;
        long total = items.size();
        int fromIndex = (page - 1) * pageSize;
        int toIndex = Math.min(fromIndex + pageSize, items.size());
        List<StockReportResponse.StockStatItem> pagedItems = fromIndex < items.size()
                ? items.subList(fromIndex, toIndex)
                : Collections.emptyList();

        return StockReportResponse.builder()
                .type("slow_moving")
                .slowDays(slowDays)
                .list(pagedItems)
                .pagination(StockReportResponse.PaginationInfo.builder()
                        .currentPage(page)
                        .perPage(pageSize)
                        .total(total)
                        .lastPage((int) Math.ceil((double) total / pageSize))
                        .build())
                .build();
    }

    private StockReportResponse getTurnoverStocks(StockReportRequest request) {
        String cutoffDate = LocalDate.now().minusDays(30).format(DATE_FORMATTER);
        String trend = request.getTrend() != null ? request.getTrend() : "desc";

        // 30天出库汇总
        List<Map<String, Object>> outboundData = stockRepository.selectOutboundSummary(cutoffDate);
        Map<String, Integer> outboundMap = outboundData.stream()
                .collect(Collectors.toMap(
                        row -> row.get("sku_code") != null ? row.get("sku_code").toString() : "",
                        row -> toInteger(row.get("total_out")),
                        (a, b) -> a
                ));

        // 库存列表
        List<Map<String, Object>> rawData = stockRepository.selectTurnoverStocks(
                request.getWarehouseId(), trend);

        List<StockReportResponse.StockStatItem> items = rawData.stream()
                .map(row -> {
                    String skuCode = row.get("sku_code") != null ? row.get("sku_code").toString() : "";
                    int out30d = outboundMap.getOrDefault(skuCode, 0);
                    int quantity = toInteger(row.get("quantity"));
                    BigDecimal turnoverRate = quantity > 0
                            ? BigDecimal.valueOf(out30d).divide(BigDecimal.valueOf(quantity), 2, RoundingMode.HALF_UP)
                            : BigDecimal.ZERO;
                    BigDecimal turnoverDays = turnoverRate.compareTo(BigDecimal.ZERO) > 0
                            ? BigDecimal.valueOf(30).divide(turnoverRate, 1, RoundingMode.HALF_UP)
                            : null;

                    return StockReportResponse.StockStatItem.builder()
                            .skuCode(skuCode)
                            .skuName(row.get("sku_name") != null ? row.get("sku_name").toString() : null)
                            .warehouseId(toLong(row.get("warehouse_id")))
                            .warehouseName(row.get("warehouse_name") != null ? row.get("warehouse_name").toString() : null)
                            .quantity(quantity)
                            .available(toInteger(row.get("available")))
                            .out30d(out30d)
                            .turnoverRate(turnoverRate)
                            .turnoverDays(turnoverDays)
                            .build();
                })
                .collect(Collectors.toList());

        // 整体周转率
        Map<String, Object> overallRaw = stockRepository.selectOverallTurnover(
                request.getWarehouseId(), cutoffDate);
        long totalOut = toLong(overallRaw.get("totalOut"));
        long totalStock = toLong(overallRaw.get("totalStock"));
        BigDecimal overallTurnover = totalStock > 0
                ? BigDecimal.valueOf(totalOut).divide(BigDecimal.valueOf(totalStock), 2, RoundingMode.HALF_UP)
                : BigDecimal.ZERO;

        int page = request.getPage() != null ? request.getPage() : 1;
        int pageSize = request.getPageSize() != null ? request.getPageSize() : 20;
        long total = items.size();
        int fromIndex = (page - 1) * pageSize;
        int toIndex = Math.min(fromIndex + pageSize, items.size());
        List<StockReportResponse.StockStatItem> pagedItems = fromIndex < items.size()
                ? items.subList(fromIndex, toIndex)
                : Collections.emptyList();

        return StockReportResponse.builder()
                .type("turnover")
                .period(30)
                .overallTurnoverRate(overallTurnover)
                .list(pagedItems)
                .pagination(StockReportResponse.PaginationInfo.builder()
                        .currentPage(page)
                        .perPage(pageSize)
                        .total(total)
                        .lastPage((int) Math.ceil((double) total / pageSize))
                        .build())
                .build();
    }

    // ==================== API调用统计 ====================

    @Override
    public ApiUsageReportResponse getApiUsageReport(ApiUsageReportRequest request) {
        log.debug("获取API调用统计, period={}, platform={}, startDate={}, endDate={}",
                request.getPeriod(), request.getPlatform(), request.getStartDate(), request.getEndDate());

        String startDate = request.getStartDate() != null ? request.getStartDate()
                : LocalDate.now().minusDays(30).format(DATE_FORMATTER);
        String endDate = request.getEndDate() != null ? request.getEndDate()
                : LocalDate.now().format(DATE_FORMATTER);
        String trend = request.getTrend() != null ? request.getTrend() : "desc";

        List<Map<String, Object>> rawData;
        if ("day".equals(request.getPeriod())) {
            rawData = pullRecordRepository.selectGroupByDate(
                    request.getPlatform(), startDate + " 00:00:00", endDate + " 23:59:59", trend);
        } else if ("platform".equals(request.getPeriod())) {
            rawData = pullRecordRepository.selectGroupByPlatform(
                    request.getPlatform(), startDate + " 00:00:00", endDate + " 23:59:59", trend);
        } else {
            rawData = pullRecordRepository.selectGroupByPullType(
                    request.getPlatform(), startDate + " 00:00:00", endDate + " 23:59:59", trend);
        }

        List<ApiUsageReportResponse.ApiUsageStatItem> items = rawData.stream()
                .map(row -> ApiUsageReportResponse.ApiUsageStatItem.builder()
                        .date(row.get("dateKey") != null && "day".equals(request.getPeriod()) 
                                ? row.get("dateKey").toString() : null)
                        .platformType(row.get("platform_type") != null ? row.get("platform_type").toString() : null)
                        .pullType(row.get("pull_type") != null ? row.get("pull_type").toString() : null)
                        .pullCount(toLong(row.get("pullCount")))
                        .totalOrders(toLong(row.get("totalOrders")))
                        .successCount(toLong(row.get("successCount")))
                        .failedCount(toLong(row.get("failedCount")))
                        .successRate(toBigDecimal(row.get("successRate")))
                        .build())
                .collect(Collectors.toList());

        int page = request.getPage() != null ? request.getPage() : 1;
        int pageSize = request.getPageSize() != null ? request.getPageSize() : 30;
        long total = items.size();
        int fromIndex = (page - 1) * pageSize;
        int toIndex = Math.min(fromIndex + pageSize, items.size());
        List<ApiUsageReportResponse.ApiUsageStatItem> pagedItems = fromIndex < items.size()
                ? items.subList(fromIndex, toIndex)
                : Collections.emptyList();

        return ApiUsageReportResponse.builder()
                .period(request.getPeriod())
                .list(pagedItems)
                .pagination(ApiUsageReportResponse.PaginationInfo.builder()
                        .currentPage(page)
                        .perPage(pageSize)
                        .total(total)
                        .lastPage((int) Math.ceil((double) total / pageSize))
                        .build())
                .build();
    }

    // ==================== 采购报表 ====================

    @Override
    public PurchaseReportResponse getPurchaseReport(PurchaseReportRequest request) {
        log.debug("获取采购报表, platform={}, shopId={}, warehouseId={}, status={}",
                request.getPlatform(), request.getShopId(), request.getWarehouseId(), request.getStatus());

        String startDate = request.getStartDate() != null ? request.getStartDate()
                : LocalDate.now().minusDays(30).format(DATE_FORMATTER);
        String endDate = request.getEndDate() != null ? request.getEndDate()
                : LocalDate.now().format(DATE_FORMATTER);
        String trend = request.getTrend() != null ? request.getTrend() : "desc";

        // 汇总
        Map<String, Object> summaryRaw = purchaseOrderRepository.selectPurchaseSummary(
                request.getPlatform(), request.getShopId(), request.getWarehouseId(),
                startDate + " 00:00:00", endDate + " 23:59:59", request.getStatus());
        PurchaseReportResponse.PurchaseSummary summary = PurchaseReportResponse.PurchaseSummary.builder()
                .totalCount(toLong(summaryRaw.get("totalCount")))
                .completedCount(toLong(summaryRaw.get("completedCount")))
                .underReviewCount(toLong(summaryRaw.get("underReviewCount")))
                .approvedCount(toLong(summaryRaw.get("approvedCount")))
                .rejectedCount(toLong(summaryRaw.get("rejectedCount")))
                .totalAmount(toBigDecimal(summaryRaw.get("totalAmount")))
                .totalFreight(toBigDecimal(summaryRaw.get("totalFreight")))
                .completedAmount(toBigDecimal(summaryRaw.get("completedAmount")))
                .build();

        // 列表
        List<Map<String, Object>> rawData = purchaseOrderRepository.selectPurchaseList(
                request.getPlatform(), request.getShopId(), request.getWarehouseId(),
                startDate + " 00:00:00", endDate + " 23:59:59", request.getStatus(), trend);

        List<PurchaseReportResponse.PurchaseStatItem> items = rawData.stream()
                .map(row -> PurchaseReportResponse.PurchaseStatItem.builder()
                        .id(toLong(row.get("id")))
                        .poNo(row.get("po_no") != null ? row.get("po_no").toString() : null)
                        .warehouseId(toLong(row.get("warehouse_id")))
                        .warehouseName(row.get("warehouse_name") != null ? row.get("warehouse_name").toString() : null)
                        .supplierId(toLong(row.get("supplier_id")))
                        .supplierName(row.get("supplier_name") != null ? row.get("supplier_name").toString() : null)
                        .platform(row.get("platform") != null ? row.get("platform").toString() : null)
                        .brandId(toLong(row.get("brand_id")))
                        .brandName(row.get("brand_name") != null ? row.get("brand_name").toString() : null)
                        .expectedArrival(row.get("expected_arrival") != null ? toLocalDate(row.get("expected_arrival")) : null)
                        .shippingMethod(row.get("shipping_method") != null ? row.get("shipping_method").toString() : null)
                        .totalAmount(toBigDecimal(row.get("total_amount")))
                        .freight(toBigDecimal(row.get("freight")))
                        .status(row.get("status") != null ? row.get("status").toString() : null)
                        .statusLabel(getPurchaseStatusLabel(row.get("status") != null ? row.get("status").toString() : null))
                        .createdAt(toLocalDateTime(row.get("created_at")))
                        .build())
                .collect(Collectors.toList());

        int page = request.getPage() != null ? request.getPage() : 1;
        int pageSize = request.getPageSize() != null ? request.getPageSize() : 20;
        long total = items.size();
        int fromIndex = (page - 1) * pageSize;
        int toIndex = Math.min(fromIndex + pageSize, items.size());
        List<PurchaseReportResponse.PurchaseStatItem> pagedItems = fromIndex < items.size()
                ? items.subList(fromIndex, toIndex)
                : Collections.emptyList();

        return PurchaseReportResponse.builder()
                .summary(summary)
                .list(pagedItems)
                .pagination(PurchaseReportResponse.PaginationInfo.builder()
                        .currentPage(page)
                        .perPage(pageSize)
                        .total(total)
                        .lastPage((int) Math.ceil((double) total / pageSize))
                        .build())
                .build();
    }

    // ==================== 资金报表 ====================

    @Override
    public FinanceReportResponse getFinanceReport(FinanceReportRequest request) {
        log.debug("获取资金报表, platform={}, shopId={}, startDate={}, endDate={}",
                request.getPlatform(), request.getShopId(), request.getStartDate(), request.getEndDate());

        String startDate = request.getStartDate() != null ? request.getStartDate()
                : LocalDate.now().minusDays(30).format(DATE_FORMATTER);
        String endDate = request.getEndDate() != null ? request.getEndDate()
                : LocalDate.now().format(DATE_FORMATTER);
        String trend = request.getTrend() != null ? request.getTrend() : "asc";

        // 收入汇总
        Map<String, Object> incomeSummary = orderRepository.selectIncomeSummary(
                request.getPlatform(), request.getShopId(),
                LocalDateTime.parse(startDate + " 00:00:00", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")),
                LocalDateTime.parse(endDate + " 23:59:59", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));

        // 支出汇总
        Map<String, Object> expenseSummary = purchaseOrderRepository.selectExpenseSummary(
                request.getPlatform(), request.getShopId(),
                startDate + " 00:00:00", endDate + " 23:59:59");

        BigDecimal totalIncome = toBigDecimal(incomeSummary.get("totalIncome"));
        BigDecimal totalExpense = toBigDecimal(expenseSummary.get("totalExpense"));
        BigDecimal netProfit = totalIncome.subtract(totalExpense).setScale(2, RoundingMode.HALF_UP);

        FinanceReportResponse.FinanceSummary summary = FinanceReportResponse.FinanceSummary.builder()
                .totalIncome(totalIncome)
                .totalExpense(totalExpense)
                .netProfit(netProfit)
                .orderCount(toLong(incomeSummary.get("orderCount")))
                .poCount(toLong(expenseSummary.get("poCount")))
                .freightIncome(toBigDecimal(incomeSummary.get("freightIncome")))
                .freightExpense(toBigDecimal(expenseSummary.get("freightExpense")))
                .build();

        // 按日期的收入
        List<Map<String, Object>> incomeByDate = orderRepository.selectIncomeByDate(
                request.getPlatform(), request.getShopId(),
                LocalDateTime.parse(startDate + " 00:00:00", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")),
                LocalDateTime.parse(endDate + " 23:59:59", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")),
                trend);

        // 按日期的支出
        List<Map<String, Object>> expenseByDate = purchaseOrderRepository.selectExpenseByDate(
                request.getPlatform(), request.getShopId(),
                startDate + " 00:00:00", endDate + " 23:59:59", trend);

        Map<String, BigDecimal> incomeMap = incomeByDate.stream()
                .collect(Collectors.toMap(
                        row -> row.get("dateKey").toString(),
                        row -> toBigDecimal(row.get("income")),
                        (a, b) -> a
                ));
        Map<String, Long> incomeOrderMap = incomeByDate.stream()
                .collect(Collectors.toMap(
                        row -> row.get("dateKey").toString(),
                        row -> toLong(row.get("orderCount")),
                        (a, b) -> a
                ));
        Map<String, BigDecimal> expenseMap = expenseByDate.stream()
                .collect(Collectors.toMap(
                        row -> row.get("dateKey").toString(),
                        row -> toBigDecimal(row.get("expense")),
                        (a, b) -> a
                ));
        Map<String, Long> expenseOrderMap = expenseByDate.stream()
                .collect(Collectors.toMap(
                        row -> row.get("dateKey").toString(),
                        row -> toLong(row.get("poCount")),
                        (a, b) -> a
                ));

        // 合并日期
        Set<String> allDates = new TreeSet<>();
        allDates.addAll(incomeMap.keySet());
        allDates.addAll(expenseMap.keySet());
        if ("desc".equals(trend)) {
            allDates = new TreeSet<>(Comparator.reverseOrder());
            allDates.addAll(incomeMap.keySet());
            allDates.addAll(expenseMap.keySet());
        }

        List<FinanceReportResponse.FinanceStatItem> items = allDates.stream()
                .map(date -> {
                    BigDecimal income = incomeMap.getOrDefault(date, BigDecimal.ZERO);
                    BigDecimal expense = expenseMap.getOrDefault(date, BigDecimal.ZERO);
                    return FinanceReportResponse.FinanceStatItem.builder()
                            .date(date)
                            .income(income)
                            .expense(expense)
                            .netProfit(income.subtract(expense).setScale(2, RoundingMode.HALF_UP))
                            .orderCount(incomeOrderMap.getOrDefault(date, 0L))
                            .poCount(expenseOrderMap.getOrDefault(date, 0L))
                            .build();
                })
                .collect(Collectors.toList());

        // 分页
        int page = request.getPage() != null ? request.getPage() : 1;
        int pageSize = request.getPageSize() != null ? request.getPageSize() : 30;
        long total = items.size();
        int fromIndex = (page - 1) * pageSize;
        int toIndex = Math.min(fromIndex + pageSize, items.size());
        List<FinanceReportResponse.FinanceStatItem> pagedItems = fromIndex < items.size()
                ? items.subList(fromIndex, toIndex)
                : Collections.emptyList();

        return FinanceReportResponse.builder()
                .summary(summary)
                .list(pagedItems)
                .pagination(FinanceReportResponse.PaginationInfo.builder()
                        .currentPage(page)
                        .perPage(pageSize)
                        .total(total)
                        .lastPage((int) Math.ceil((double) total / pageSize))
                        .build())
                .build();
    }

    // ==================== 工具方法 ====================

    private LocalDateTime parseStartDateTime(String date) {
        if (!StringUtils.hasText(date)) {
            return null;
        }
        return LocalDateTime.parse(date + " 00:00:00", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
    }

    private LocalDateTime parseEndDateTime(String date) {
        if (!StringUtils.hasText(date)) {
            return null;
        }
        return LocalDateTime.parse(date + " 23:59:59", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
    }

    private LocalDate toLocalDate(Object obj) {
        if (obj == null) return null;
        if (obj instanceof LocalDate) return (LocalDate) obj;
        if (obj instanceof java.sql.Date) return ((java.sql.Date) obj).toLocalDate();
        if (obj instanceof String) return LocalDate.parse(obj.toString(), DATE_FORMATTER);
        return null;
    }

    private LocalDateTime toLocalDateTime(Object obj) {
        if (obj == null) return null;
        if (obj instanceof LocalDateTime) return (LocalDateTime) obj;
        if (obj instanceof java.sql.Timestamp) return ((java.sql.Timestamp) obj).toLocalDateTime();
        if (obj instanceof java.util.Date) return LocalDateTime.ofInstant(((java.util.Date) obj).toInstant(), java.time.ZoneId.systemDefault());
        return null;
    }

    private Long toLong(Object obj) {
        if (obj == null) return 0L;
        if (obj instanceof Long) return (Long) obj;
        if (obj instanceof Number) return ((Number) obj).longValue();
        try {
            return Long.parseLong(obj.toString());
        } catch (NumberFormatException e) {
            return 0L;
        }
    }

    private Integer toInteger(Object obj) {
        if (obj == null) return 0;
        if (obj instanceof Integer) return (Integer) obj;
        if (obj instanceof Number) return ((Number) obj).intValue();
        try {
            return Integer.parseInt(obj.toString());
        } catch (NumberFormatException e) {
            return 0;
        }
    }

    private BigDecimal toBigDecimal(Object obj) {
        if (obj == null) return BigDecimal.ZERO;
        if (obj instanceof BigDecimal) return (BigDecimal) obj;
        if (obj instanceof Number) return BigDecimal.valueOf(((Number) obj).doubleValue());
        try {
            return new BigDecimal(obj.toString());
        } catch (NumberFormatException e) {
            return BigDecimal.ZERO;
        }
    }

    private String getPurchaseStatusLabel(String status) {
        if (status == null) return null;
        return switch (status) {
            case "draft" -> "草稿";
            case "under_review" -> "待审核";
            case "approved" -> "已审核";
            case "rejected" -> "已驳回";
            case "completed" -> "已完成";
            default -> status;
        };
    }
}
