package com.njcn.gather.aireport.testreport.component;

import com.njcn.common.pojo.exception.BusinessException;
import com.njcn.gather.aireport.testreport.pojo.constant.TestReportConst;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.util.LinkedHashMap;
import java.util.Map;

class TestReportLedgerExcelParserTest {

    @Test
    void shouldParseValidLedgerWorkbook() throws Exception {
        TestReportLedgerExcelParser parser = new TestReportLedgerExcelParser();
        MockMultipartFile summaryFile = new MockMultipartFile("files", TestReportConst.LEDGER_SUMMARY_FILE_NAME,
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", buildWorkbookBytes(true));
        Map<String, MultipartFile> attachments = new LinkedHashMap<String, MultipartFile>();
        attachments.put("point-a.xlsx", new MockMultipartFile("files", "point-a.xlsx", "application/octet-stream", new byte[]{1}));
        attachments.put("point-a.docx", new MockMultipartFile("files", "point-a.docx", "application/octet-stream", new byte[]{1}));

        TestReportLedgerExcelParser.ParsedLedger ledger = parser.parse(summaryFile, attachments);

        Assertions.assertEquals(1, ledger.getPoints().size());
        Assertions.assertEquals(1, ledger.getDevices().size());
        Assertions.assertEquals(1, ledger.getClients().size());
        Assertions.assertEquals("点位A", ledger.getPoints().get(0).getPointName());
    }

    @Test
    void shouldAllowSummaryFileNameWithSurroundingSpaces() throws Exception {
        TestReportLedgerExcelParser parser = new TestReportLedgerExcelParser();
        MockMultipartFile summaryFile = new MockMultipartFile("files", " " + TestReportConst.LEDGER_SUMMARY_FILE_NAME + " ",
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", buildWorkbookBytes(true));
        Map<String, MultipartFile> attachments = new LinkedHashMap<String, MultipartFile>();
        attachments.put("point-a.xlsx", new MockMultipartFile("files", "point-a.xlsx", "application/octet-stream", new byte[]{1}));
        attachments.put("point-a.docx", new MockMultipartFile("files", "point-a.docx", "application/octet-stream", new byte[]{1}));

        TestReportLedgerExcelParser.ParsedLedger ledger = parser.parse(summaryFile, attachments);

        Assertions.assertEquals(TestReportConst.LEDGER_SUMMARY_FILE_NAME, ledger.getSummaryFileName());
        Assertions.assertEquals(1, ledger.getPoints().size());
    }

    @Test
    void shouldRejectMissingPointSheet() throws Exception {
        TestReportLedgerExcelParser parser = new TestReportLedgerExcelParser();
        MockMultipartFile summaryFile = new MockMultipartFile("files", TestReportConst.LEDGER_SUMMARY_FILE_NAME,
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", buildWorkbookBytes(false));

        BusinessException exception = Assertions.assertThrows(BusinessException.class,
                () -> parser.parse(summaryFile, new LinkedHashMap<String, MultipartFile>()));

        Assertions.assertTrue(exception.getMessage().contains("监测点信息"));
    }

    @Test
    void shouldRejectDuplicatePointBySubstationAndPointName() throws Exception {
        TestReportLedgerExcelParser parser = new TestReportLedgerExcelParser();
        MockMultipartFile summaryFile = new MockMultipartFile("files", TestReportConst.LEDGER_SUMMARY_FILE_NAME,
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", buildDuplicatePointWorkbookBytes());
        Map<String, MultipartFile> attachments = new LinkedHashMap<String, MultipartFile>();
        attachments.put("point-a.xlsx", new MockMultipartFile("files", "point-a.xlsx", "application/octet-stream", new byte[]{1}));
        attachments.put("point-a.docx", new MockMultipartFile("files", "point-a.docx", "application/octet-stream", new byte[]{1}));
        attachments.put("point-b.xlsx", new MockMultipartFile("files", "point-b.xlsx", "application/octet-stream", new byte[]{1}));
        attachments.put("point-b.docx", new MockMultipartFile("files", "point-b.docx", "application/octet-stream", new byte[]{1}));

        BusinessException exception = Assertions.assertThrows(BusinessException.class,
                () -> parser.parse(summaryFile, attachments));

        Assertions.assertTrue(exception.getMessage().contains("重复点位"));
    }

    @Test
    void shouldAllowMissingOptionalSheets() throws Exception {
        TestReportLedgerExcelParser parser = new TestReportLedgerExcelParser();
        MockMultipartFile summaryFile = new MockMultipartFile("files", TestReportConst.LEDGER_SUMMARY_FILE_NAME,
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                buildWorkbookBytes(true, false, false, false));
        Map<String, MultipartFile> attachments = new LinkedHashMap<String, MultipartFile>();
        attachments.put("point-a.xlsx", new MockMultipartFile("files", "point-a.xlsx", "application/octet-stream", new byte[]{1}));
        attachments.put("point-a.docx", new MockMultipartFile("files", "point-a.docx", "application/octet-stream", new byte[]{1}));

        TestReportLedgerExcelParser.ParsedLedger ledger = parser.parse(summaryFile, attachments);

        Assertions.assertEquals(1, ledger.getPoints().size());
        Assertions.assertTrue(ledger.getDevices().isEmpty());
        Assertions.assertTrue(ledger.getClients().isEmpty());
        Assertions.assertTrue(ledger.getMissingSheets().isEmpty());
    }

    @Test
    void shouldAllowBlankOptionalSheetContent() throws Exception {
        TestReportLedgerExcelParser parser = new TestReportLedgerExcelParser();
        MockMultipartFile summaryFile = new MockMultipartFile("files", TestReportConst.LEDGER_SUMMARY_FILE_NAME,
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", buildWorkbookWithBlankOptionalRows());
        Map<String, MultipartFile> attachments = new LinkedHashMap<String, MultipartFile>();
        attachments.put("point-a.xlsx", new MockMultipartFile("files", "point-a.xlsx", "application/octet-stream", new byte[]{1}));
        attachments.put("point-a.docx", new MockMultipartFile("files", "point-a.docx", "application/octet-stream", new byte[]{1}));

        TestReportLedgerExcelParser.ParsedLedger ledger = parser.parse(summaryFile, attachments);

        Assertions.assertEquals(1, ledger.getPoints().size());
        Assertions.assertTrue(ledger.getDevices().isEmpty());
        Assertions.assertTrue(ledger.getClients().isEmpty());
    }

    private byte[] buildWorkbookBytes(boolean includePointSheet) throws Exception {
        return buildWorkbookBytes(includePointSheet, true, true, true);
    }

    private byte[] buildWorkbookBytes(boolean includePointSheet, boolean includeGuideSheet,
                                      boolean includeDeviceSheet, boolean includeClientSheet) throws Exception {
        try (XSSFWorkbook workbook = new XSSFWorkbook(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            if (includeGuideSheet) {
                workbook.createSheet(TestReportConst.LEDGER_GUIDE_SHEET_NAME);
            }
            if (includePointSheet) {
                XSSFSheet pointSheet = workbook.createSheet(TestReportConst.LEDGER_POINT_SHEET_NAME);
                pointSheet.createRow(0).createCell(0).setCellValue("分组号");
                pointSheet.getRow(0).createCell(1).setCellValue("变电站");
                pointSheet.getRow(0).createCell(2).setCellValue("监测点名称");
                pointSheet.getRow(0).createCell(3).setCellValue("检测设备编号");
                pointSheet.getRow(0).createCell(4).setCellValue("委托单位名称");
                pointSheet.getRow(0).createCell(5).setCellValue("Excel附件名称");
                pointSheet.getRow(0).createCell(6).setCellValue("Word附件名称");
                pointSheet.createRow(1).createCell(0).setCellValue("1");
                pointSheet.getRow(1).createCell(1).setCellValue("站点A");
                pointSheet.getRow(1).createCell(2).setCellValue("点位A");
                pointSheet.getRow(1).createCell(3).setCellValue(includeDeviceSheet ? "DEV-001" : "");
                pointSheet.getRow(1).createCell(4).setCellValue(includeClientSheet ? "单位A" : "");
                pointSheet.getRow(1).createCell(5).setCellValue("point-a.xlsx");
                pointSheet.getRow(1).createCell(6).setCellValue("point-a.docx");
            }
            if (includeDeviceSheet) {
                XSSFSheet deviceSheet = workbook.createSheet(TestReportConst.LEDGER_DEVICE_SHEET_NAME);
                deviceSheet.createRow(0).createCell(0).setCellValue("设备型号");
                deviceSheet.getRow(0).createCell(1).setCellValue("设备编号");
                deviceSheet.getRow(0).createCell(2).setCellValue("标准有效期");
                deviceSheet.getRow(0).createCell(3).setCellValue("设备状态");
                deviceSheet.getRow(0).createCell(4).setCellValue("标准报告文件名");
                deviceSheet.createRow(1).createCell(0).setCellValue("型号A");
                deviceSheet.getRow(1).createCell(1).setCellValue("DEV-001");
                deviceSheet.getRow(1).createCell(2).setCellValue("2026-07-01");
                deviceSheet.getRow(1).createCell(3).setCellValue("在运");
                deviceSheet.getRow(1).createCell(4).setCellValue("report-a.pdf");
            }
            if (includeClientSheet) {
                XSSFSheet clientSheet = workbook.createSheet(TestReportConst.LEDGER_CLIENT_SHEET_NAME);
                clientSheet.createRow(0).createCell(0).setCellValue("单位名称");
                clientSheet.getRow(0).createCell(1).setCellValue("联系人");
                clientSheet.getRow(0).createCell(2).setCellValue("联系方式");
                clientSheet.getRow(0).createCell(3).setCellValue("地址");
                clientSheet.createRow(1).createCell(0).setCellValue("单位A");
                clientSheet.getRow(1).createCell(1).setCellValue("张三");
                clientSheet.getRow(1).createCell(2).setCellValue("13800000000");
                clientSheet.getRow(1).createCell(3).setCellValue("南京");
            }
            workbook.write(outputStream);
            return outputStream.toByteArray();
        }
    }

    private byte[] buildDuplicatePointWorkbookBytes() throws Exception {
        try (XSSFWorkbook workbook = new XSSFWorkbook(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            workbook.createSheet(TestReportConst.LEDGER_GUIDE_SHEET_NAME);
            XSSFSheet pointSheet = workbook.createSheet(TestReportConst.LEDGER_POINT_SHEET_NAME);
            pointSheet.createRow(0).createCell(0).setCellValue("分组号");
            pointSheet.getRow(0).createCell(1).setCellValue("变电站");
            pointSheet.getRow(0).createCell(2).setCellValue("监测点名称");
            pointSheet.getRow(0).createCell(3).setCellValue("检测设备编号");
            pointSheet.getRow(0).createCell(4).setCellValue("委托单位名称");
            pointSheet.getRow(0).createCell(5).setCellValue("Excel附件名称");
            pointSheet.getRow(0).createCell(6).setCellValue("Word附件名称");
            pointSheet.createRow(1).createCell(0).setCellValue("1");
            pointSheet.getRow(1).createCell(1).setCellValue("站点A");
            pointSheet.getRow(1).createCell(2).setCellValue("点位A");
            pointSheet.getRow(1).createCell(3).setCellValue("DEV-001");
            pointSheet.getRow(1).createCell(4).setCellValue("单位A");
            pointSheet.getRow(1).createCell(5).setCellValue("point-a.xlsx");
            pointSheet.getRow(1).createCell(6).setCellValue("point-a.docx");
            pointSheet.createRow(2).createCell(0).setCellValue("2");
            pointSheet.getRow(2).createCell(1).setCellValue("站点A");
            pointSheet.getRow(2).createCell(2).setCellValue("点位A");
            pointSheet.getRow(2).createCell(3).setCellValue("DEV-002");
            pointSheet.getRow(2).createCell(4).setCellValue("单位B");
            pointSheet.getRow(2).createCell(5).setCellValue("point-b.xlsx");
            pointSheet.getRow(2).createCell(6).setCellValue("point-b.docx");
            workbook.write(outputStream);
            return outputStream.toByteArray();
        }
    }

    private byte[] buildWorkbookWithBlankOptionalRows() throws Exception {
        try (XSSFWorkbook workbook = new XSSFWorkbook(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            XSSFSheet pointSheet = workbook.createSheet(TestReportConst.LEDGER_POINT_SHEET_NAME);
            pointSheet.createRow(0).createCell(0).setCellValue("分组号");
            pointSheet.getRow(0).createCell(1).setCellValue("变电站");
            pointSheet.getRow(0).createCell(2).setCellValue("监测点名称");
            pointSheet.getRow(0).createCell(3).setCellValue("检测设备编号");
            pointSheet.getRow(0).createCell(4).setCellValue("委托单位名称");
            pointSheet.getRow(0).createCell(5).setCellValue("Excel附件名称");
            pointSheet.getRow(0).createCell(6).setCellValue("Word附件名称");
            pointSheet.createRow(1).createCell(0).setCellValue("1");
            pointSheet.getRow(1).createCell(1).setCellValue("站点A");
            pointSheet.getRow(1).createCell(2).setCellValue("点位A");
            pointSheet.getRow(1).createCell(5).setCellValue("point-a.xlsx");
            pointSheet.getRow(1).createCell(6).setCellValue("point-a.docx");

            XSSFSheet deviceSheet = workbook.createSheet(TestReportConst.LEDGER_DEVICE_SHEET_NAME);
            deviceSheet.createRow(0).createCell(0).setCellValue("设备型号");
            deviceSheet.getRow(0).createCell(1).setCellValue("设备编号");
            deviceSheet.getRow(0).createCell(2).setCellValue("标准有效期");
            deviceSheet.getRow(0).createCell(3).setCellValue("设备状态");
            deviceSheet.getRow(0).createCell(4).setCellValue("标准报告文件名");
            deviceSheet.createRow(1).createCell(0).setCellValue("型号A");

            XSSFSheet clientSheet = workbook.createSheet(TestReportConst.LEDGER_CLIENT_SHEET_NAME);
            clientSheet.createRow(0).createCell(0).setCellValue("单位名称");
            clientSheet.getRow(0).createCell(1).setCellValue("联系人");
            clientSheet.getRow(0).createCell(2).setCellValue("联系方式");
            clientSheet.getRow(0).createCell(3).setCellValue("地址");
            clientSheet.createRow(1).createCell(1).setCellValue("张三");

            workbook.write(outputStream);
            return outputStream.toByteArray();
        }
    }
}
