package com.java110.fee.cmd.fee;

import com.alibaba.fastjson.JSONObject;
import com.java110.core.annotation.Java110Cmd;
import com.java110.core.context.ICmdDataFlowContext;
import com.java110.core.event.cmd.Cmd;
import com.java110.core.event.cmd.CmdEvent;
import com.java110.dto.community.CommunityDto;
import com.java110.dto.fee.FeeConfigDto;
import com.java110.dto.room.RoomDto;
import com.java110.intf.community.ICommunityInnerServiceSMO;
import com.java110.intf.community.IRoomInnerServiceSMO;
import com.java110.intf.fee.IFeeConfigInnerServiceSMO;
import com.java110.intf.fee.IReportFeeInnerServiceSMO;
import com.java110.utils.exception.CmdException;
import com.java110.utils.util.Assert;
import com.java110.utils.util.BeanConvertUtil;
import com.java110.utils.util.DateUtil;
import com.java110.vo.ResultVo;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.util.*;
import java.util.stream.Collectors;

@Java110Cmd(serviceCode = "fee.exportPropertyFeeExcelCmd")
public class ExportPropertyFeeExcelCmd extends Cmd {

    @Autowired
    private IReportFeeInnerServiceSMO reportFeeInnerServiceSMOImpl;

    @Autowired
    private ICommunityInnerServiceSMO communityInnerServiceSMOImpl;

    @Autowired
    private IRoomInnerServiceSMO roomInnerServiceSMOImpl;

    @Autowired
    private IFeeConfigInnerServiceSMO feeConfigInnerServiceSMOImpl;

    @Override
    public void validate(CmdEvent event, ICmdDataFlowContext context, JSONObject reqJson) throws CmdException, ParseException {
        Assert.hasKey(reqJson, "communityId", "请求报文中未包含小区编号");
    }

    @Override
    public void doCmd(CmdEvent event, ICmdDataFlowContext context, JSONObject reqJson) throws CmdException, ParseException {
        // 设置年份范围：2020年至当前年份
        int startYear = 2020;
        int currentYear = DateUtil.getYear();
        int endYear = currentYear;

        String communityId = reqJson.getString("communityId");
        
        // 获取小区信息
        CommunityDto communityDto = new CommunityDto();
        communityDto.setCommunityId(communityId);
        List<CommunityDto> communityDtos = communityInnerServiceSMOImpl.queryCommunitys(communityDto);
        Assert.notEmpty(communityDtos, "未查询到小区信息");

        // 查询房屋数据（不分页，获取全部）
        RoomDto roomDto = new RoomDto();
        roomDto.setCommunityId(communityId);
        roomDto.setFloorNum(reqJson.containsKey("floorId") && !reqJson.getString("floorId").equals("") ? reqJson.getString("floorId") : null);
        roomDto.setOwnerName(reqJson.containsKey("ownerName") && !reqJson.getString("ownerName").equals("") ? reqJson.getString("ownerName") : null);
        // 取消分页限制
        roomDto.setRow(Integer.MAX_VALUE);
        roomDto.setPage(1);
        List<Map> rooms = roomInnerServiceSMOImpl.queryRoomsAsReport(roomDto);
        
        if (rooms.isEmpty()) {
            ResultVo resultVo = new ResultVo("未查询到数据");
            ResponseEntity<String> responseEntity = new ResponseEntity<>(resultVo.toString(), HttpStatus.OK);
            context.setResponseEntity(responseEntity);
            return;
        }

        // 获取费用配置
        FeeConfigDto feeConfigDto = new FeeConfigDto();
        feeConfigDto.setCommunityId(communityId);
        if (reqJson.containsKey("feeTypeCd") && !reqJson.getString("feeTypeCd").equals("")) {
            feeConfigDto.setFeeTypeCd(reqJson.getString("feeTypeCd"));
        } else {
            feeConfigDto.setFeeTypeCds(new String[]{"630000001"});
        }
        if (reqJson.containsKey("importFee") && !reqJson.getString("importFee").equals("")) {
            feeConfigDto.setFeeNameEq(reqJson.getString("importFee"));
        } else {
            feeConfigDto.setFeeTypeCds(new String[]{"630000001"});
        }
        List<FeeConfigDto> originalDtos = feeConfigInnerServiceSMOImpl.queryFeeConfigs(feeConfigDto);

        // 处理费用配置：去重、排序
        List<FeeConfigDto> feeConfigDtos = 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());

        // 创建Excel
        try (SXSSFWorkbook workbook = new SXSSFWorkbook(100); // 内存中保留100行，其余写入临时文件
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {

            // 创建工作表
            Sheet sheet = workbook.createSheet("物业费报表_" + communityDtos.get(0).getName());
            
            // 创建表头样式
            CellStyle headerStyle = createHeaderStyle(workbook);
            // 创建内容样式
            CellStyle contentStyle = createContentStyle(workbook);

            // 生成表头
            int headerRowIndex = createExcelHeader(sheet, headerStyle, feeConfigDtos, startYear, endYear);

            // 填充数据
            fillExcelData(sheet, contentStyle, rooms, feeConfigDtos, startYear, endYear, headerRowIndex);

            // 调整列宽
            autoSizeColumns(sheet, 0, 15 + 2 * feeConfigDtos.size() + 3 * (endYear - startYear + 1));

            // 写入输出流
            workbook.write(out);
            workbook.dispose(); // 清理临时文件

            // 构建响应
            byte[] bytes = out.toByteArray();
            HttpHeaders headers = createExcelHeaders("物业费报表_" + DateUtil.getNow("yyyy-MM-dd") + ".xlsx");
            ResponseEntity<byte[]> responseEntity = new ResponseEntity<>(bytes, headers, HttpStatus.OK);
            context.setResponseEntity(responseEntity);

        } catch (IOException e) {
            throw new CmdException("导出Excel失败：" + e.getMessage());
        }
    }

    /**
     * 创建表头样式
     */
    private CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 设置边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        // 设置居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置背景色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置字体
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    /**
     * 创建内容样式
     */
    private CellStyle createContentStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 设置边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        // 设置居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    /**
     * 创建Excel表头
     */
    private int createExcelHeader(Sheet sheet, CellStyle headerStyle, List<FeeConfigDto> feeConfigDtos, int startYear, int endYear) {
        int rowIndex = 0;
        int feeCount = feeConfigDtos.size();
        int yearCount = endYear - startYear + 1;

        // 第一行表头
        Row row1 = sheet.createRow(rowIndex++);
        int colIndex = 0;
        
        // 基础信息列
        String[] baseHeaders = {"序号", "物业类型", "楼栋号/弄", "门号", "室号", "门室号", "产证地址", "收费面积（m²）", "购房人姓名"};
        for (String header : baseHeaders) {
            createCell(row1, colIndex++, header, headerStyle);
        }
        
        // 费用配置列
        for (int i = 0; i < feeCount; i++) {
            createCell(row1, colIndex++, feeConfigDtos.get(i).getFeeName(), headerStyle);
        }
        
        // 其他固定列
        createCell(row1, colIndex++, "设备运作费", headerStyle);
        for (int i = 0; i < feeCount; i++) {
            createCell(row1, colIndex++, feeConfigDtos.get(i).getFeeName(), headerStyle);
        }
        createCell(row1, colIndex++, "年应收款", headerStyle);
        createCell(row1, colIndex++, "合计(" + startYear + "年1月-至今)", headerStyle);
        createCell(row1, colIndex++, "历年实收", headerStyle);
        createCell(row1, colIndex++, "操作", headerStyle);

        // 第二行表头
        Row row2 = sheet.createRow(rowIndex++);
        colIndex = 0;
        
        // 跳过基础信息列
        colIndex += baseHeaders.length + feeCount + 1 + feeCount + 1;
        
        // 统计信息列
        String[] statsHeaders = {"应收", "实收", "代收", startYear + "年", (startYear + 1) + "年", "待收月数", "待收金额", "代收区间"};
        for (String header : statsHeaders) {
            createCell(row2, colIndex++, header, headerStyle);
        }
        
        // 年份列
        for (int i = startYear; i <= endYear; i++) {
            createCell(row2, colIndex++, String.valueOf(i), headerStyle);
        }
        
        createCell(row2, colIndex++, "打折金额汇总", headerStyle);

        // 第三行表头
        Row row3 = sheet.createRow(rowIndex++);
        colIndex = 0;
        
        // 跳过前面的列
        colIndex += baseHeaders.length + feeCount + 1 + feeCount + 1 + statsHeaders.length;
        
        // 年份详情列
        for (int i = startYear; i <= endYear; i++) {
            createCell(row3, colIndex++, "已收月数", headerStyle);
            createCell(row3, colIndex++, "已收区间", headerStyle);
            createCell(row3, colIndex++, "未收区间", headerStyle);
        }

        // 合并单元格
        // 这里可以根据需要添加单元格合并逻辑

        return rowIndex;
    }

    /**
     * 填充Excel数据
     */
    private void fillExcelData(Sheet sheet, CellStyle contentStyle, List<Map> rooms, 
                              List<FeeConfigDto> feeConfigDtos, int startYear, int endYear, int startRowIndex) {
        int rowIndex = startRowIndex;
        int feeCount = feeConfigDtos.size();
        int yearCount = endYear - startYear + 1;

        for (int i = 0; i < rooms.size(); i++) {
            Map room = rooms.get(i);
            Row row = sheet.createRow(rowIndex++);
            int colIndex = 0;
            
            // 基础信息
            createCell(row, colIndex++, String.valueOf(i + 1), contentStyle); // 序号
            createCell(row, colIndex++, room.get("property_type") != null ? room.get("property_type").toString() : "", contentStyle);
            createCell(row, colIndex++, room.get("floor_num") != null ? room.get("floor_num").toString() : "", contentStyle);
            createCell(row, colIndex++, room.get("unit_num") != null ? room.get("unit_num").toString() : "", contentStyle);
            createCell(row, colIndex++, room.get("room_num") != null ? room.get("room_num").toString() : "", contentStyle);
            createCell(row, colIndex++, (room.get("unit_num") != null ? room.get("unit_num").toString() : "") + "-" + 
                    (room.get("room_num") != null ? room.get("room_num").toString() : ""), contentStyle);
            createCell(row, colIndex++, room.get("property_address") != null ? room.get("property_address").toString() : "", contentStyle);
            createCell(row, colIndex++, room.get("room_area") != null ? room.get("room_area").toString() : "", contentStyle);
            createCell(row, colIndex++, room.get("name") != null ? room.get("name").toString() : "", contentStyle);
            
            // 费用配置信息
            for (FeeConfigDto config : feeConfigDtos) {
                createCell(row, colIndex++, config.getSquarePrice() != null ? config.getSquarePrice() : "", contentStyle);
            }
            
            // 设备运作费
            createCell(row, colIndex++, "", contentStyle);
            
            // 费用计算
            BigDecimal roomArea = room.get("room_area") != null ? (BigDecimal) room.get("room_area") : BigDecimal.ZERO;
            for (FeeConfigDto config : feeConfigDtos) {
                double price = config.getSquarePrice() != null ? Double.parseDouble(config.getSquarePrice()) : 0;
                double total = price * roomArea.doubleValue();
                createCell(row, colIndex++, String.valueOf(doublequ2(total)), contentStyle);
            }
            
            // 年应收款
            double year应收 = feeConfigDtos.stream()
                    .mapToDouble(config -> {
                        double price = config.getSquarePrice() != null ? Double.parseDouble(config.getSquarePrice()) : 0;
                        return price * roomArea.doubleValue() * 12;
                    })
                    .sum();
            createCell(row, colIndex++, String.valueOf(doublequ2(year应收)), contentStyle);
            
            // 获取费用数据
            HashMap<Object, Object> params = new HashMap<>();
            params.put("payObjId", room.get("room_id"));
            List<Map> feeData = reportFeeInnerServiceSMOImpl.onceRoomFee(params);
            
            // 计算合计信息
            double allMoney = 0;
            double allPayMoney = 0;
            int monthCount = 0;
            int noPayMoney = 0;
            double discountMoney = 0;
            
            for (Map fee : feeData) {
                if (fee.containsKey("统计维度") && !fee.get("统计维度").toString().contains("总计")) {
                    allMoney += ((BigDecimal) fee.get("应收金额")).doubleValue();
                    allPayMoney += ((BigDecimal) fee.get("实收金额")).doubleValue();
                    monthCount += ((BigDecimal) fee.get("未收月数")).intValue();
                    noPayMoney += ((BigDecimal) fee.get("未收金额")).intValue();
                    discountMoney += ((BigDecimal) fee.get("折扣金额")).doubleValue();
                }
            }
            
            // 填充合计信息
            createCell(row, colIndex++, String.valueOf(doublequ2(allMoney)), contentStyle); // 合计
            createCell(row, colIndex++, String.valueOf(doublequ2(allPayMoney)), contentStyle); // 历年实收
            createCell(row, colIndex++, "", contentStyle); // 操作
            
            // 填充统计信息
            createCell(row, colIndex++, String.valueOf(doublequ2(allMoney)), contentStyle); // 应收
            createCell(row, colIndex++, String.valueOf(doublequ2(allPayMoney)), contentStyle); // 实收
            createCell(row, colIndex++, "", contentStyle); // 代收
            createCell(row, colIndex++, "", contentStyle); // 2020年
            createCell(row, colIndex++, "", contentStyle); // 2021年
            createCell(row, colIndex++, String.valueOf(monthCount), contentStyle); // 待收月数
            createCell(row, colIndex++, String.valueOf(noPayMoney), contentStyle); // 待收金额
            createCell(row, colIndex++, "", contentStyle); // 代收区间
            
            // 填充年份数据
            for (int year = startYear; year <= endYear; year++) {
                // 年份总数据（占位）
                createCell(row, colIndex++, "", contentStyle);
                
                // 已收月数、已收区间、未收区间
                String receivedMonths = "0";
                String receivedRange = "";
                String unpaidRange = "";
                
                for (Map fee : feeData) {
                    if (fee.get("统计维度").equals(year + "年")) {
                        receivedMonths = ((BigDecimal) fee.get("已收月数")).intValue() + "";
                        receivedRange = fee.get("已收区间") != null ? fee.get("已收区间").toString() : "";
                        unpaidRange = fee.get("未收区间") != null ? fee.get("未收区间").toString() : "";
                        break;
                    }
                }
                
                createCell(row, colIndex++, receivedMonths, contentStyle);
                createCell(row, colIndex++, receivedRange, contentStyle);
                createCell(row, colIndex++, unpaidRange, contentStyle);
            }
            
            // 打折金额汇总
            createCell(row, colIndex++, String.valueOf(doublequ2(discountMoney)), contentStyle);
        }
    }

    /**
     * 创建单元格并设置值和样式
     */
    private void createCell(Row row, int column, String value, CellStyle style) {
        Cell cell = row.createCell(column);
        cell.setCellValue(value);
        cell.setCellStyle(style);
    }

    /**
     * 自动调整列宽
     */
    private void autoSizeColumns(Sheet sheet, int startCol, int endCol) {
        for (int i = startCol; i <= endCol; i++) {
            sheet.autoSizeColumn(i);
            // 适当调整自动列宽，避免过窄
            int width = sheet.getColumnWidth(i) + 256;
            sheet.setColumnWidth(i, width > 65535 ? 65535 : width);
        }
    }

    /**
     * 创建Excel响应头
     */
    private HttpHeaders createExcelHeaders(String fileName) {
        HttpHeaders headers = new HttpHeaders();
        try {
            // 设置文件名编码
            String encodedFileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
            headers.setContentDispositionFormData("attachment", encodedFileName);
        } catch (Exception e) {
            headers.setContentDispositionFormData("attachment", fileName);
        }
        headers.setContentType(org.springframework.http.MediaType.APPLICATION_OCTET_STREAM);
        return headers;
    }

    /**
     * 保留两位小数
     */
    public double doublequ2(double num) {
        return (int) (num * 100) / 100.0;
    }
}