package com.youchain.basicdata.service.impl;

import cn.idev.excel.read.metadata.ReadSheet;
import com.youchain.basicdata.domain.BillType;
import com.youchain.basicdata.domain.Item;
import com.youchain.basicdata.repository.*;
import com.youchain.basicdata.service.*;
import com.youchain.businessdata.domain.Asn;
import com.youchain.businessdata.domain.AsnDetail;
import com.youchain.businessdata.inputJson.imports.BaseImport;
import com.youchain.businessdata.inputJson.imports.InvoicePackingImport;
import com.youchain.businessdata.repository.*;
import com.youchain.businessdata.service.*;
import com.youchain.config.FileProperties;
import com.youchain.exception.BadRequestException;
import com.youchain.modules.system.domain.Dept;
import com.youchain.utils.*;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.sql.Timestamp;
import java.util.*;
import java.util.stream.Collectors;

@Service
@RequiredArgsConstructor
@Slf4j
public class ImportAsnServiceImpl implements ImportAsnService {

    private final AsnRepository asnRepository;

    private final AsnDetailRepository asnDetailRepository;

    private final BillTypeRepository billTypeRepository;

    private final ItemService itemService;

    private final AsnService asnService;

    private final CodeUtils codeUtils;

    private final FileProperties properties;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void importAsn(MultipartFile file, String templateType) {
//        String suffix = FileUtil.getExtensionName(file.getOriginalFilename());
//        String type = FileUtil.getFileType(suffix);
//        FileUtil.upload(file, properties.getPath().getPath() + type + File.separator);
        // 验证模板类型是否正确
        List<ReadSheet> readSheets = isValidTemplateType(file, templateType);

        // 根据模板类型进行不同的处理
        switch (templateType) {
            case "template1":
                baseImportTemplate(file, readSheets);
                break;
            case "template2":
                bigItemTemplate(file, readSheets);
                break;
            case "template3":
                invoicePackingTemplate(file, readSheets);
                break;
            default:
                throw new BadRequestException("不支持的模板类型: " + templateType);
        }
    }

    // 处理标准模板
    @Transactional
    public void baseImportTemplate(MultipartFile file, List<ReadSheet> readSheets) {
        // 读取sheet数据
        List<BaseImport> dataList = FastExcelUtil.readExcelData(file, BaseImport.class, readSheets.get(0).getSheetNo(), 1);

        //批量导入
        importAsnData(dataList);
    }

    //处理大物模板
    private void bigItemTemplate(MultipartFile file, List<ReadSheet> readSheets) {
        // 获取发票号
        FastExcelCellListener reader = new FastExcelCellListener()
                .addTargetCell(6, 55); // invoiceNo
        FastExcelUtil.readExcelCellValues(file, reader, readSheets.get(0).getSheetNo());
        String invoiceNo = reader.getCellValue(6, 55);

        // 读取两个sheet的数据
        List<BaseImport> sheet1Data = FastExcelUtil.readExcelData(file, BaseImport.class, readSheets.get(0).getSheetNo(), 21);
        List<BaseImport> sheet2Data = FastExcelUtil.readExcelData(file, BaseImport.class, readSheets.get(1).getSheetNo(), 21);

        //大物数据处理
        List<BaseImport> dataList = bigItemDataProcess(invoiceNo, sheet1Data, sheet2Data);

        //批量导入数据
        importAsnData(dataList);

    }

    // 处理发票箱单模板
    private void invoicePackingTemplate(MultipartFile file, List<ReadSheet> readSheets) {
        // 读取第二个sheet的invoiceNo号
        FastExcelCellListener reader = new FastExcelCellListener().addTargetCell(3, 11); // invoiceNo
        FastExcelUtil.readExcelCellValues(file, reader, readSheets.get(1).getSheetNo());//
        String invoiceNo = reader.getCellValue(3, 11);

        // 读取sheet数据
        List<InvoicePackingImport> sheet1Data = FastExcelUtil.readExcelData(file, InvoicePackingImport.class, readSheets.get(0).getSheetNo(), 24);

        // 读取sheet数据
        List<InvoicePackingImport> sheet2Data = FastExcelUtil.readExcelData(file, InvoicePackingImport.class, readSheets.get(1).getSheetNo(), 5);

        //发票箱单数据处理
        List<BaseImport> dataList = invoicePackingDataProcess(invoiceNo, sheet1Data, sheet2Data);

        //批量导入
        importAsnData(dataList);
    }

    /**
     * 处理批量导入
     */
    private void importAsnData(List<BaseImport> dataList) {
        // TODO: 实现批量导入逻辑
        log.info("处理批量导入，数据条数: {}", dataList.size());

        //获取文件中所有的托盘号
        List<String> cusCodes = dataList.stream().map(BaseImport::getCNo).collect(Collectors.toList());
        cusCodes = cusCodes.stream().filter(Objects::nonNull).distinct().collect(Collectors.toList());
        //验证托盘号
        validateAsn(cusCodes);

        //获取文件中所有的品番
//        List<String> itemCodes = dataList.stream().map(BaseImport::getPartNo).collect(Collectors.toList());
        List<String> itemCodes = dataList.stream()
                .map(item -> item.getPartNo().replace("-", ""))
                .collect(Collectors.toList());
        itemCodes = itemCodes.stream().filter(Objects::nonNull).distinct().collect(Collectors.toList());
        //验证品番
        Map<String, Item> exitItemMap = validateItem(itemCodes);


        //单据类型
        BillType billType = billTypeRepository.findByName(BaseStatus.XDRK);
        Dept dept = UserUtils.getDept();
        Map<String, Asn> asnMap = new HashMap<>();
        List<Asn> insertToAsn = new ArrayList<>();//批量新增ASN
        List<AsnDetail> insertToAsnDetail = new ArrayList<>();//批量新增asnDetail
        for (BaseImport data : dataList) {
            String relatedBill1 = data.getInvoiceNo();//发票号
            String propC2 = data.getBoi();//税别
            String po = data.getPoNo();//订单号
            String itemCode = data.getPartNo();//品番
            String remark = data.getDescription();//描述
            String orderQty = data.getQty().replace(",","");//数量,去掉逗号格式

            String code = data.getCNo();//托盘号
            //品番
            Item item = exitItemMap.get(itemCode.replace("-", ""));

            Asn asn;
            if (asnMap.containsKey(code)) {
                asn = asnMap.get(code);
                asn.setOrderQuantity(asn.getOrderQuantity() + Double.parseDouble(orderQty));
            } else {
                asn = Asn.builder()
                        .relatedBill1(relatedBill1)
                        .code(codeUtils.getCode_yyMMdd(billType.getPrefix() + '-', 3))
                        .cusCode(code)
                        .status(BizStatus.OPEN)
                        .orderDate(new Timestamp(new Date().getTime()))
                        .orderQuantity(Double.parseDouble(orderQty))
                        .billType(billType)
                        .receivedQuantity(0d)
                        .putawayQuantity(0d)
                        .build();
                asnMap.put(code, asn);
                insertToAsn.add(asn);
            }

            AsnDetail asnDetail = AsnDetail.builder()
                    .propC2(propC2)
                    .po(po)
                    .item(item)
                    .remark(remark)
                    .orderQty(Double.parseDouble(orderQty))
                    .asn(asn)
                    .status(BizStatus.OPEN)
                    .receivedQty(0d)
                    .moveQty(0d)
                    .putQty(0d)
                    .weight(0d)
                    .volume(0d)
                    .dept(dept)
                    .build();
            insertToAsnDetail.add(asnDetail);
        }
        if (!insertToAsn.isEmpty()) {
            asnRepository.saveAll(insertToAsn);
        }
        if (!insertToAsnDetail.isEmpty()) {
            asnDetailRepository.saveAll(insertToAsnDetail);
        }
    }

    //验证品番
    private Map<String, Item> validateItem(List<String> itemCodes) {
        Map<String, Item> exitItemMap = itemService.queryByItemCodesToMap(itemCodes);
        if (exitItemMap.isEmpty()) {
            throw new BadRequestException(itemCodes + "品番不存在或已失效");
        }

        List<String> existingItemCodes = new ArrayList<>(exitItemMap.keySet());

        // 获取两个集合的非交集说明品番不存在或失效，直接提示
        List<String> difference = SmartStringUtil.getDifference(itemCodes, existingItemCodes);

        if (CollectionUtils.isNotEmpty(difference)) {
            throw new BadRequestException(difference + "品番不存在或已失效");
        }
        return exitItemMap;
    }

    //验证ASN单号
    private void validateAsn(List<String> cusCodes) {
        Map<String, Asn> exitAsnMap = asnService.queryByCusCodesToMap(cusCodes);
        List<String> existCusCodes = new ArrayList<>(exitAsnMap.keySet());
        // 获取两个集合的交集说明ASN重复导入了，直接提示
        List<String> difference = SmartStringUtil.getIntersection(cusCodes, existCusCodes);
        if (CollectionUtils.isNotEmpty(difference)) {
            //去重difference
            difference = difference.stream().filter(Objects::nonNull).distinct().collect(Collectors.toList());
            throw new BadRequestException(difference + "托盘号已导入，请勿重复导入");
        }
    }

    //验证模板是否使用正确
    private List<ReadSheet> isValidTemplateType(MultipartFile file, String templateType) {
        // 根据模板类型进行不同的处理
        if ("template1".equals(templateType)) {
            List<ReadSheet> readSheets = FastExcelUtil.readSheets(file);
            if (readSheets.size() != 1) {
                throw new BadRequestException("标准导入模板中只能包含1个sheet目录");
            }
            List<String> requiredColumns = Arrays.asList("INVOICE NO.", "BOI", "PO&LN NO.", "PART NO.", "DESCRIPTION", "Q'TY \n" + "(PCS)", "C/NO.");
            List<String> headers = FastExcelUtil.readHeadContent(file, readSheets.get(0).getSheetNo(), 0);
            if (!SmartStringUtil.containsAllIgnoreCase(requiredColumns, headers)) {
                throw new BadRequestException("标准导入模板不正确,请确认模板信息");
            }
            return readSheets;
        } else if ("template2".equals(templateType)) {
            List<ReadSheet> readSheets = FastExcelUtil.readSheets(file);
            if (readSheets.size() != 2) {
                throw new BadRequestException("大物导入模板中只能包含2个sheet目录");
            }
            List<String> requiredColumns = Arrays.asList("BOI", "PO&LN NO.", "PART NO.", "DESCRIPTION", "Q'TY \n" + "(PCS)");
            List<String> sheet1Headers = FastExcelUtil.readHeadContent(file, readSheets.get(0).getSheetNo(), 20);
            if (!SmartStringUtil.containsAllIgnoreCase(requiredColumns, sheet1Headers)) {
                throw new BadRequestException("大物第1个Sheet目录模板不正确,请确认模板信息");
            }

            List<String> requiredColumns2 = Collections.singletonList("C/NO.");
            List<String> sheet2Headers = FastExcelUtil.readHeadContent(file, readSheets.get(1).getSheetNo(), 20);
            if (!SmartStringUtil.containsAllIgnoreCase(requiredColumns2, sheet2Headers)) {
                throw new BadRequestException("大物第2个Sheet目录模板不正确,请确认模板信息");
            }
            return readSheets;
        } else if ("template3".equals(templateType)) {
            List<ReadSheet> readSheets = FastExcelUtil.readSheets(file);
            if (readSheets.size() != 2) {
                throw new BadRequestException("发票箱单模板中只能包含2个sheet目录");
            }
            List<String> requiredColumns = Collections.singletonList("(BOI)");
            List<String> headers = FastExcelUtil.readHeadContent(file, readSheets.get(0).getSheetNo(), 23);
            if (!SmartStringUtil.containsAllIgnoreCase(requiredColumns, headers)) {
                throw new BadRequestException("发票箱单第1个Sheet目录模板不正确,请确认模板信息");
            }

            List<String> requiredColumns2 = Arrays.asList("A-PROS\n" + "CASE NO", "KMT NO. PO&LN NO.", "PART NO.", "DESCRIPTION", "Q'TY");
            List<String> headers2 = FastExcelUtil.readHeadContent(file, readSheets.get(1).getSheetNo(), 4);
            if (!SmartStringUtil.containsAllIgnoreCase(requiredColumns2, headers2)) {
                throw new BadRequestException("发票箱单第2个Sheet目录模板不正确,请确认模板信息");
            }

            return readSheets;
        } else {
            throw new BadRequestException("模板类型错误");
        }
    }

    //大物数据处理
    private static List<BaseImport> bigItemDataProcess(String invoiceNo, List<BaseImport> sheet1, List<BaseImport> sheet2) {

        //处理sheet1数据
        List<BaseImport> sheet1Date = new ArrayList<>();
        for (BaseImport item : sheet1) {
            if (item.getBoi().toUpperCase().contains("TOTAL")) {
                break; // 不区分大小写 遇到TOTAL则停止添加
            }
            sheet1Date.add(item);
        }

        //处理sheet2数据
        List<BaseImport> sheet2Date = new ArrayList<>();

        // 用于记录上一个非 null 的 cNo 值
        String lastNonNullCNo = null;
        for (BaseImport item : sheet2) {
            String currentCNo = item.getCNo();

            // 先检查是否遇到终止标志
            if (currentCNo != null && currentCNo.toUpperCase().contains("TOTAL")) {
                break;
            }

            if (currentCNo == null) {
                // 处理空值：使用最近的非空值填充
                if (lastNonNullCNo != null) {
                    item.setCNo(lastNonNullCNo);
                }
            } else {
                // 更新最近的非空值记录
                lastNonNullCNo = currentCNo;
            }

            // 统一添加元素到列表（排除TOTAL情况）
            sheet2Date.add(item);
        }

        if (sheet1Date.size() != sheet2Date.size()) {
            throw new BadRequestException("大物模板中数据行数不一致");
        }

        List<BaseImport> dataList = new ArrayList<>();

        for (int i = 0; i < sheet1Date.size(); i++) {
            BaseImport t1 = sheet1Date.get(i);//sheet1中的列数据

            BaseImport t2 = sheet2Date.get(i);//sheet2中的列数据

            BaseImport baseImport = BaseImport.builder()
                    .invoiceNo(invoiceNo)
                    .boi(t1.getBoi())
                    .poNo(t1.getPoNo())
                    .partNo(t1.getPartNo())
                    .description(t1.getDescription())
                    .qty(t1.getQty())
                    .cNo(t2.getCNo())
                    .build();
            dataList.add(baseImport);

        }
        return dataList;
    }

    //发票箱单数据处理
    private static List<BaseImport> invoicePackingDataProcess(String invoiceNo, List<InvoicePackingImport> sheet1Date, List<InvoicePackingImport> sheet2Date) {
        //处理sheet1数据
        List<InvoicePackingImport> newSheet1Date = new ArrayList<>();
        for (InvoicePackingImport item : sheet1Date) {
            if (item.getBoi().toUpperCase().contains("TOTAL")) {
                break; // 不区分大小写 遇到TOTAL则停止添加
            }
            newSheet1Date.add(item);
        }

        //处理sheet2数据
        List<InvoicePackingImport> newSheet2Date = new ArrayList<>();
        for (InvoicePackingImport item : sheet2Date) {
            if (item.getPartNo().toUpperCase().contains("PACKAGES")) {
                break; // 不区分大小写 遇到TOTAL则停止添加
            }
            newSheet2Date.add(item);
        }

        if (newSheet1Date.size() != newSheet2Date.size()) {
            throw new BadRequestException("发票箱单模板中数据行数不一致");
        }

        List<BaseImport> dataList = new ArrayList<>();

        for (int i = 0; i < newSheet1Date.size(); i++) {
            InvoicePackingImport t1 = newSheet1Date.get(i);//sheet1中的列数据

            InvoicePackingImport t2 = newSheet2Date.get(i);//sheet2中的列数据

            BaseImport baseImport = BaseImport.builder()
                    .invoiceNo(invoiceNo)
                    .boi(t1.getBoi())
                    .poNo(t2.getPoNo())
                    .partNo(t2.getPartNo())
                    .description(t2.getDescription())
                    .qty(t2.getQty())
                    .cNo(t2.getCNo())
                    .build();
            dataList.add(baseImport);

        }
        return dataList;
    }
}
