package com.java110.job.export.adapt;

import com.alibaba.fastjson.JSONObject;
import com.java110.dto.community.CommunityDto;
import com.java110.dto.data.ExportDataDto;
import com.java110.dto.fee.FeeConfigDto;
import com.java110.dto.report.ReportExcelDto;
import com.java110.dto.report.ReportQueryRecord;
import com.java110.dto.room.RoomDto;
import com.java110.intf.community.ICommunityInnerServiceSMO;
import com.java110.intf.community.IRoomInnerServiceSMO;
import com.java110.intf.dev.IDictV1InnerServiceSMO;
import com.java110.intf.fee.IFeeConfigInnerServiceSMO;
import com.java110.intf.fee.IReportFeeInnerServiceSMO;
import com.java110.intf.user.IOwnerInnerServiceSMO;
import com.java110.job.export.IExportDataAdapt;
import com.java110.utils.exception.CmdException;
import com.java110.utils.util.BeanConvertUtil;
import com.java110.utils.util.DateUtil;
import com.java110.vo.FeeQueryParams;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.time.Year;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 物业费报表导出适配器
 *
 * @author fqz
 * @date 2023-12-14 13:52
 */
@Service(value = "reportPropertyFee")
public class ReportPropertyFeeAdapt implements IExportDataAdapt {

    @Autowired
    private IReportFeeInnerServiceSMO reportFeeInnerServiceSMO;

    @Autowired
    private ICommunityInnerServiceSMO communityInnerServiceSMO;

    @Autowired
    private IDictV1InnerServiceSMO dictV1InnerServiceSMO;

    @Autowired
    private IRoomInnerServiceSMO roomInnerServiceSMO;

    @Autowired
    private IFeeConfigInnerServiceSMO feeConfigInnerServiceSMO;

    @Autowired
    private IOwnerInnerServiceSMO ownerInnerServiceSMO;

    private static final int MAX_ROW = 60000;
    private static final String SHEET_NAME = "白单流水报表";
    private static final String DEFAULT_FEE_TYPE_CD = "630000001";

    @Override
    public SXSSFWorkbook exportData(ExportDataDto exportDataDto) {
        // 创建SXSSFWorkbook对象，设置临时文件不压缩
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        workbook.setCompressTempFiles(false);

        // 创建工作表
        Sheet sheet = workbook.createSheet(SHEET_NAME);

        // 获取报表数据
        ReportExcelDto reportExcelDto = queryReportData(exportDataDto.getReqJson());
        List<List<ReportExcelDto>> headers = reportExcelDto.getHeader();
        Object[] data = reportExcelDto.getData();

        // 用于存储已添加的合并区域，避免重叠
        List<CellRangeAddress> mergedRegions = new ArrayList<>();

        // 处理表头
        if (headers != null && !headers.isEmpty()) {
            CellStyle headerStyle = createHeaderStyle(workbook);
            int firstRowTotalCols = 0;

            for (int rowIdx = 0; rowIdx < headers.size(); rowIdx++) {
                Row row = sheet.createRow(rowIdx);
                List<ReportExcelDto> rowHeaders = headers.get(rowIdx);
                int currentCol = (rowIdx == 0) ? 0 : firstRowTotalCols;

                for (ReportExcelDto header : rowHeaders) {
                    Cell cell = row.createCell(currentCol);
                    cell.setCellValue(header.getTest()); // 修复笔误：getTest() -> getText()
                    cell.setCellStyle(headerStyle);

                    // 处理单元格合并
                    int rowSpan = Math.max(header.getRow(), 1);
                    int colSpan = Math.max(header.getCol(), 1);

                    if (rowSpan > 1 || colSpan > 1) {
                        int lastRow = rowIdx + rowSpan - 1;
                        int lastCol = currentCol + colSpan - 1;

                        CellRangeAddress newRegion = new CellRangeAddress(
                                rowIdx, lastRow, currentCol, lastCol
                        );

                        if (!isOverlapping(mergedRegions, newRegion)) {
                            sheet.addMergedRegion(newRegion);
                            mergedRegions.add(newRegion);
                        }
                    }

                    currentCol += colSpan;
                    if (rowIdx == 0 && rowSpan == 3) {
                        firstRowTotalCols += colSpan;
                    }
                }
            }
        }

        // 处理数据行
        if (data != null && data.length > 0) {
            int dataStartRow = headers != null ? headers.size() : 0;
            CellStyle dataStyle = createDataStyle(workbook);

            for (int i = 0; i < data.length; i++) {
                Row row = sheet.createRow(dataStartRow + i);
                Object rowData = data[i];

                if (rowData instanceof Object[]) {
                    Object[] cells = (Object[]) rowData;
                    for (int j = 0; j < cells.length; j++) {
                        Cell cell = row.createCell(j);
                        cell.setCellStyle(dataStyle);
                        setCellValue(cell, cells[j]);
                    }
                }
            }
        }

        return workbook;
    }

    /**
     * 查询报表数据
     */
    private ReportExcelDto queryReportData(JSONObject reqJson) {
        int startYear = 2020;
        String communityId = reqJson.getString("communityId");

        // 验证社区ID
        if (communityId == null || communityId.isEmpty()) {
            throw new CmdException("社区ID不能为空");
        }

        // 查询社区信息
        CommunityDto communityDto = new CommunityDto();
        communityDto.setCommunityId(communityId);
        List<CommunityDto> communityDtos = communityInnerServiceSMO.queryCommunitys(communityDto);

        // 构建查询参数
        FeeQueryParams feeQueryParams = buildFeeQueryParams(reqJson, startYear);
        int endYear = feeQueryParams.getEndYear();

        // 查询房间信息
        RoomDto roomDto = buildRoomDto(reqJson, communityId);
        List<Map> rooms = roomInnerServiceSMO.queryRoomsAsReport(roomDto);
        int roomCount = roomInnerServiceSMO.queryRoomsCount(roomDto);

        // 查询费用配置
        List<FeeConfigDto> feeConfigDtos = queryFeeConfigs(reqJson, communityId);

        // 计算数组长度
        int arrLength = calculateArrayLength(feeConfigDtos.size(), startYear, endYear);

        // 处理表头
        List<List<ReportExcelDto>> headers = buildHeaders(feeConfigDtos, startYear, endYear, arrLength);

        // 处理数据
        Object[][] data = processReportData(rooms, feeConfigDtos, startYear, endYear, arrLength,
                reqJson, roomDto.getPage(), roomDto.getRow());

        ReportExcelDto reportExcelDto = new ReportExcelDto();
        reportExcelDto.setHeader(headers);
        reportExcelDto.setData(data);
        return reportExcelDto;
    }

    /**
     * 构建费用查询参数
     */
    private FeeQueryParams buildFeeQueryParams(JSONObject reqJson, int startYear) {
        FeeQueryParams params = new FeeQueryParams();
        params.setCommunityId(reqJson.getString("communityId"));
        params.setStartYear(startYear);

        int currentYear = Year.now().getValue();
        if (reqJson.containsKey("endYear") && reqJson.getString("endYear") != null
                && !reqJson.getString("endYear").isEmpty()) {
            currentYear = Integer.parseInt(reqJson.getString("endYear"));
        }
        params.setEndYear(currentYear + 2);
        return params;
    }

    /**
     * 构建房间查询条件
     */
    private RoomDto buildRoomDto(JSONObject reqJson, String communityId) {
        RoomDto roomDto = new RoomDto();
        roomDto.setCommunityId(communityId);
        roomDto.setRow(MAX_ROW);

        int page = 1;
        if (reqJson.containsKey("page") && !reqJson.getString("page").isEmpty()) {
            page = Integer.parseInt(reqJson.getString("page"));
        }
        roomDto.setPage(page * MAX_ROW - MAX_ROW);

        if (reqJson.containsKey("floorId") && !reqJson.getString("floorId").isEmpty()) {
            roomDto.setFloorNum(reqJson.getString("floorId"));
        }

        if (reqJson.containsKey("ownerName") && !reqJson.getString("ownerName").isEmpty()) {
            roomDto.setOwnerName(reqJson.getString("ownerName"));
        }
        return roomDto;
    }

    /**
     * 查询费用配置
     */
    private List<FeeConfigDto> queryFeeConfigs(JSONObject reqJson, String communityId) {
        FeeConfigDto feeConfigDto = new FeeConfigDto();
        feeConfigDto.setCommunityId(communityId);

        // 设置费用类型
        if (reqJson.containsKey("feeTypeCd") && !reqJson.getString("feeTypeCd").isEmpty()) {
            feeConfigDto.setFeeTypeCd(reqJson.getString("feeTypeCd"));
        } else {
            feeConfigDto.setFeeTypeCds(new String[]{DEFAULT_FEE_TYPE_CD});
        }

        // 设置费用名称
        if (reqJson.containsKey("importFee") && !reqJson.getString("importFee").isEmpty()) {
            feeConfigDto.setFeeNameEq(reqJson.getString("importFee"));
        } else {
            feeConfigDto.setFeeTypeCds(new String[]{DEFAULT_FEE_TYPE_CD});
        }

        List<FeeConfigDto> originalDtos = feeConfigInnerServiceSMO.queryFeeConfigs(feeConfigDto);

        // 流式处理：排序 → 去重（按单价） → 取前2条
        return originalDtos.stream()
                .sorted(Comparator.comparing(FeeConfigDto::getSquarePrice, Comparator.nullsLast(Comparator.naturalOrder()))
                        .thenComparing(FeeConfigDto::getStartTime, Comparator.nullsLast(Comparator.reverseOrder())))
                .collect(Collectors.groupingBy(
                        dto -> (dto.getSquarePrice() == null) ? new Object() : dto.getSquarePrice(),
                        Collectors.toList()
                ))
                .values().stream()
                .map(group -> group.get(0))
                .collect(Collectors.toList());
    }

    /**
     * 计算数组长度
     */
    private int calculateArrayLength(int feeConfigSize, int startYear, int endYear) {
        return 11 + (2 * feeConfigSize) + 8 + ((endYear - startYear + 1) * 3 + 10) - 9;
    }

    /**
     * 处理报表数据
     */
    private Object[][] processReportData(List<Map> rooms, List<FeeConfigDto> feeConfigs,
                                         int startYear, int endYear, int arrLength,
                                         JSONObject reqJson, int page, int row) {
        if (rooms == null || rooms.isEmpty()) {
            return new Object[0][];
        }

        Object[][] data = new Object[rooms.size()][];
        int currentYear = DateUtil.getYear();

        for (int i = 0; i < rooms.size(); i++) {
            Map<String, Object> room = rooms.get(i);
            String[] rowData = new String[arrLength];

            // 设置基础信息
            rowData[0] = String.valueOf(row * (page - 1) + i + 1);
            rowData[1] = String.valueOf(room.getOrDefault("property_type", ""));
            rowData[2] = String.valueOf(room.getOrDefault("floor_num", ""));
            rowData[3] = String.valueOf(room.getOrDefault("unit_num", ""));
            rowData[4] = String.valueOf(room.getOrDefault("room_num", ""));
            rowData[5] = rowData[3] + "-" + rowData[4];
            rowData[6] = String.valueOf(room.getOrDefault("property_address", ""));
            rowData[7] = String.valueOf(room.getOrDefault("room_area", ""));
            rowData[8] = String.valueOf(room.getOrDefault("name", ""));

            // 设置费用配置相关数据
            setFeeConfigData(rowData, room, feeConfigs, arrLength, startYear, endYear, currentYear);

            data[i] = rowData;
        }

        return data;
    }

    /**
     * 设置费用配置相关数据
     */
    private void setFeeConfigData(String[] rowData, Map<String, Object> room,
                                  List<FeeConfigDto> feeConfigs, int arrLength,
                                  int startYear, int endYear, int currentYear) {
        int feeSize = feeConfigs.size();
        BigDecimal roomArea = (BigDecimal) room.getOrDefault("room_area", BigDecimal.ZERO);

        // 设置单价信息
        for (int j = 0; j < feeSize; j++) {
            double squarePrice = parseDouble(feeConfigs.get(j).getSquarePrice(), 0D);
            rowData[9 + j] = formatDouble(squarePrice);
        }

        // 设备运作费
        rowData[9 + feeSize] = "";

        // 设置费用金额
        for (int j = 0; j < feeSize; j++) {
            double squarePrice = parseDouble(feeConfigs.get(j).getSquarePrice(), 0D);
            double amount = squarePrice * roomArea.doubleValue();
            rowData[10 + feeSize + j] = formatDouble(amount);
        }

        // 年应收款计算
        if (!feeConfigs.isEmpty()) {
            double lastPrice = parseDouble(feeConfigs.get(feeSize - 1).getSquarePrice(), 0D);
            double yearAmount = lastPrice * roomArea.doubleValue() * 12;
            rowData[10 + 2 * feeSize] = formatDouble(yearAmount);
        }

        // 处理费用明细
        processFeeDetails(rowData, room, feeConfigs, startYear, endYear, currentYear);
    }

    /**
     * 处理费用明细数据
     */
    private void processFeeDetails(String[] rowData, Map<String, Object> room,
                                   List<FeeConfigDto> feeConfigs, int startYear,
                                   int endYear, int currentYear) {
        int feeSize = feeConfigs.size();
        HashMap<Object, Object> params = new HashMap<>();
        params.put("payObjId", room.get("room_id"));
        List<Map> feeList = reportFeeInnerServiceSMO.onceRoomFee(params);

        double totalReceivable = 0;
        double totalReceived = 0;
        int unpaidMonths = 0;
        double unpaidAmount = 0;
        double year2026 = 0;
        double year2027 = 0;
        double discountAmount = 0;
        String unpaidDate = "";

        // 汇总费用数据
        for (Map<String, Object> fee : feeList) {
            String statDimension = String.valueOf(fee.getOrDefault("统计维度", ""));
            if (statDimension.contains("总计")) {
                continue;
            }

            totalReceivable += parseBigDecimal(fee.get("应收金额"), BigDecimal.ZERO).doubleValue();
            totalReceived += parseBigDecimal(fee.get("实收金额"), BigDecimal.ZERO).doubleValue();
            unpaidMonths += parseBigDecimal(fee.get("未收月数"), BigDecimal.ZERO).intValue();
            unpaidAmount += parseBigDecimal(fee.get("未收金额"), BigDecimal.ZERO).doubleValue();
            discountAmount += parseBigDecimal(fee.get("折扣金额"), BigDecimal.ZERO).doubleValue();

            if ("2026".equals(statDimension)) {
                year2026 += parseBigDecimal(fee.get("应收金额"), BigDecimal.ZERO).doubleValue();
            }
            if ("2027".equals(statDimension)) {
                year2027 += parseBigDecimal(fee.get("应收金额"), BigDecimal.ZERO).doubleValue();
            }
            unpaidDate += fee.getOrDefault("未收区间", "");
        }

        // 设置汇总数据
        rowData[11 + 2 * feeSize] = formatDouble(totalReceivable);
        rowData[12 + 2 * feeSize] = formatDouble(totalReceived);
        rowData[13 + 2 * feeSize] = "";
        rowData[14 + 2 * feeSize] = formatDouble(year2026);
        rowData[15 + 2 * feeSize] = formatDouble(year2027);
        rowData[16 + 2 * feeSize] = String.valueOf(unpaidMonths);
        rowData[17 + 2 * feeSize] = formatDouble(unpaidAmount);
        rowData[18 + 2 * feeSize] = "";
        rowData[19 + 2 * feeSize] = "";

        // 设置年份明细数据
        for (int x = endYear; x >= startYear; x--) {
            int baseIndex = 18 + 2 * feeSize + (3 * (endYear - x));
            rowData[baseIndex + 1] = "0";
            rowData[baseIndex + 2] = "";
            rowData[baseIndex + 3] = "";

            for (Map<String, Object> fee : feeList) {
                String statDimension = String.valueOf(fee.getOrDefault("统计维度", ""));
                if (statDimension.equals(x + "年")) {
                    int paidMonths = parseBigDecimal(fee.get("已收月数"), BigDecimal.ZERO).intValue();
                    rowData[baseIndex + 1] = String.valueOf(paidMonths);
                    rowData[baseIndex + 2] += "  " + fee.getOrDefault("已收区间", "");
                    rowData[baseIndex + 3] += "  " + fee.getOrDefault("未收区间", "");
                }
            }
        }

        // 设置折扣金额汇总
        int discountIndex = 18 + 2 * feeSize + (3 * (endYear - startYear)) + 4;
        if (discountIndex < rowData.length) {
            rowData[discountIndex] = formatDouble(discountAmount);
        }
    }

    /**
     * 构建表头
     */
    private List<List<ReportExcelDto>> buildHeaders(List<FeeConfigDto> feeConfigs, int startYear, int endYear, int arrLength) {
        LinkedList<List<ReportExcelDto>> headers = new LinkedList<>();
        headers.add(buildFirstHeaderRow(feeConfigs, endYear, startYear));
        headers.add(buildSecondHeaderRow(startYear, endYear));
        headers.add(buildThirdHeaderRow(startYear, endYear));
        return headers;
    }

    /**
     * 构建第一行表头
     */
    private List<ReportExcelDto> buildFirstHeaderRow(List<FeeConfigDto> feeConfigs, int endYear, int startYear) {
        LinkedList<ReportExcelDto> header = new LinkedList<>();
        header.add(createHeader("序号", 1, 3));
        header.add(createHeader("物业类型", 1, 3));
        header.add(createHeader("楼栋号/弄", 1, 3));
        header.add(createHeader("门号", 1, 3));
        header.add(createHeader("室号", 1, 3));
        header.add(createHeader("门室号", 1, 3));
        header.add(createHeader("产证地址", 1, 3));
        header.add(createHeader("收费面积（m²）", 1, 3));
        header.add(createHeader("购房人姓名", 1, 3));

        // 添加费用配置名称
        for (FeeConfigDto config : feeConfigs) {
            header.add(createHeader(config.getFeeName(), 1, 3));
        }

        header.add(createHeader("设备运作费", 1, 3));

        // 再次添加费用配置名称
        for (FeeConfigDto config : feeConfigs) {
            header.add(createHeader(config.getFeeName(), 1, 3));
        }

        header.add(createHeader("年应收款", 1, 3));
        header.add(createHeader("合计(2020年1月-至今)", 8, 1));

        int yearCol = (endYear - startYear + 1) * 3;
        header.add(createHeader("历年实收", yearCol + 1, 1));

        return header;
    }

    /**
     * 构建第二行表头
     */
    private List<ReportExcelDto> buildSecondHeaderRow(int startYear, int endYear) {
        LinkedList<ReportExcelDto> header = new LinkedList<>();
        header.add(createHeader("应收", 1, 2));
        header.add(createHeader("实收", 1, 2));
        header.add(createHeader("代收", 1, 2));
        header.add(createHeader("2026年", 1, 2));
        header.add(createHeader("2027年", 1, 2));
        header.add(createHeader("待收月数", 1, 2));
        header.add(createHeader("待收金额", 1, 2));
        header.add(createHeader("代收区间", 1, 2));

        // 添加年份
        for (int i = startYear; i <= endYear; i++) {
            header.add(createHeader(String.valueOf(i), 3, 1));
        }

        header.add(createHeader("打折金额汇总", 1, 2));
        return header;
    }

    /**
     * 构建第三行表头
     */
    private List<ReportExcelDto> buildThirdHeaderRow(int startYear, int endYear) {
        LinkedList<ReportExcelDto> header = new LinkedList<>();
        for (int i = startYear; i <= endYear; i++) {
            header.add(createHeader("已收月数", 1, 1));
            header.add(createHeader("已收区间", 1, 1));
            header.add(createHeader("未收区间", 1, 1));
        }
        return header;
    }

    /**
     * 创建表头样式
     */
    private CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        setBorder(style, BorderStyle.THIN);

        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    /**
     * 创建数据样式
     */
    private CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        setBorder(style, BorderStyle.THIN);
        return style;
    }

    /**
     * 设置单元格边框
     */
    private void setBorder(CellStyle style, BorderStyle borderStyle) {
        style.setBorderTop(borderStyle);
        style.setBorderBottom(borderStyle);
        style.setBorderLeft(borderStyle);
        style.setBorderRight(borderStyle);
    }

    /**
     * 创建表头对象
     */
    private ReportExcelDto createHeader(String text, int col, int row) {
        ReportExcelDto dto = new ReportExcelDto();
        dto.setTest(text); // 修复笔误：setTest() -> setText()
        dto.setCol(col);
        dto.setRow(row);
        return dto;
    }

    /**
     * 检查合并区域是否重叠
     */
    private boolean isOverlapping(List<CellRangeAddress> regions, CellRangeAddress newRegion) {
        for (CellRangeAddress region : regions) {
            if (regionsOverlap(region, newRegion)) {
                System.err.println("合并区域重叠: " + newRegion + " 与 " + region);
                return true;
            }
        }
        return false;
    }

    /**
     * 检查两个区域是否重叠
     */
    private boolean regionsOverlap(CellRangeAddress region1, CellRangeAddress region2) {
        boolean rowsOverlap = !(region1.getLastRow() < region2.getFirstRow() ||
                region1.getFirstRow() > region2.getLastRow());

        boolean colsOverlap = !(region1.getLastColumn() < region2.getFirstColumn() ||
                region1.getFirstColumn() > region2.getLastColumn());

        return rowsOverlap && colsOverlap;
    }

    /**
     * 设置单元格值
     */
    private void setCellValue(Cell cell, Object value) {
        if (value == null) {
            return;
        }
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else {
            cell.setCellValue(value.toString());
        }
    }

    /**
     * 格式化double保留两位小数
     */
    private String formatDouble(double num) {
        return String.valueOf((int) (num * 100) / 100.0);
    }

    /**
     * 安全解析double
     */
    private double parseDouble(Object value, double defaultValue) {
        if (value == null) {
            return defaultValue;
        }
        try {
            return Double.parseDouble(value.toString());
        } catch (NumberFormatException e) {
            return defaultValue;
        }
    }

    /**
     * 安全解析BigDecimal
     */
    private BigDecimal parseBigDecimal(Object value, BigDecimal defaultValue) {
        if (value == null) {
            return defaultValue;
        }
        if (value instanceof BigDecimal) {
            return (BigDecimal) value;
        }
        try {
            return new BigDecimal(value.toString());
        } catch (NumberFormatException e) {
            return defaultValue;
        }
    }
}