package com.bonus.common.core.utils;

import com.alibaba.fastjson2.JSONObject;
import com.bonus.common.core.constant.BusinessConstants;
import com.bonus.common.core.exception.ServiceException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author cwchen
 * excel文件导入-包含图片
 */
@Slf4j
public class ImportExcelUtils {
    public static List<JSONObject> readExcel(MultipartFile file, Class<?> mClass, String uploadPath) throws Exception {
        InputStream inputStream = null;
        try {
            String fileName = file.getOriginalFilename();
            log.info("OriginalFilename:{}", fileName);
            if (!StringUtils.endsWithAny(fileName, BusinessConstants.XLS, BusinessConstants.XLSX)) {
                throw new ServiceException("不支持excel以外的文件导入！");
            }
            List<JSONObject> list = new ArrayList<>();
            inputStream = file.getInputStream();
            String className = mClass.getSimpleName();
            log.info("className:{}", className);
            //根据指定的文件输入流导入Excel从而产生Workbook对象
            Workbook workbook = null;
            Sheet sheet = null;
            Map<String, PictureData> mapData = null;
            if (null != fileName && fileName.endsWith(BusinessConstants.XLS)) {
                workbook = new HSSFWorkbook(inputStream);
                //获取Excel文档中的第一个表单
                sheet = workbook.getSheetAt(0);
                if (!checkModal(sheet, className)) {
                    throw new ServiceException("模板错误，请重新选择模板！");
                }
                mapData = getPicturesXls((HSSFSheet) sheet, className);
            }
            if (null != fileName && fileName.endsWith(BusinessConstants.XLSX)) {
                workbook = new XSSFWorkbook(inputStream);
                //获取Excel文档中的第一个表单
                sheet = workbook.getSheetAt(0);
                if (!checkModal(sheet, className)) {
                    throw new ServiceException("模板错误，请重新选择模板！");
                }
                mapData = getPicturesXlsx((XSSFSheet) sheet, className);
            }
            List<Map<String, Object>> filenames = new ArrayList<>();
            if (Objects.equals(className, BusinessConstants.PERSON_IMPORT_VO)) {
                log.info("开始读取并写入图片");
                filenames = writeImg(mapData, className, uploadPath);
                log.info("图片:{}，写入完成！", filenames);
            }
            int sheetCount = 0;
            if (workbook != null) {
                workbook.getNumberOfSheets();
            }
            log.info("Sheet(表单)数量：{}", sheetCount);
            log.info("filenames:{}", filenames);
            //获得最后一条记录得的行号，从0开始
            int totalRowNum = 0;
            if (sheet != null) {
                totalRowNum = sheet.getLastRowNum();
            }
            log.info("总记录数:{}", (totalRowNum + 1));
            list = createBean(sheet, mClass, filenames);
            return list;
        } finally {
            if (inputStream != null) {
                inputStream.close();
            }
        }
    }

    private static boolean checkModal(Sheet sheet, String className) {
        int colNum = sheet.getRow(0).getLastCellNum();
        if (Objects.equals(className, BusinessConstants.PERSON_IMPORT_VO)) {
            return colNum == 6;
        }
        return false;
    }

    private static List<JSONObject> createBean(Sheet sheet, Class<?> mClass, List<Map<String, Object>> filename) throws Exception {
        if (sheet == null || sheet.getLastRowNum() < 0) {
            return null;
        }
        List<JSONObject> list = new ArrayList<>();
        // 总列数
        int last = sheet.getRow(0).getLastCellNum();
        log.info("列数:{}", last);
        try {
            for (Row row : sheet) {
                if (row == null) {
                    continue;
                }
                // 第一行是标题栏
                if (Objects.equals(mClass.getSimpleName(), BusinessConstants.PERSON_IMPORT_VO)) {
                    if (row.getRowNum() < 2) {
                        continue;
                    }
                }
                boolean isBlankRow = true;
                for (Cell c : row) {
                    if (c.getCellType() != CellType.BLANK) {
                        isBlankRow = false;
                        break;
                    }
                }
                if (isBlankRow) {
                    continue;
                }
                JSONObject obj = new JSONObject();
                obj.put("rowNo", row.getRowNum() + 1);
                if (Objects.equals(mClass.getSimpleName(), BusinessConstants.PERSON_IMPORT_VO)) {
                    setExcelToString(5, row);
                    obj = setPersonObjData(row, obj, filename);
                }
                list.add(obj);
            }
        } catch (IllegalStateException e) {
            e.printStackTrace();
            throw new ServiceException("模板中含有单元格数据格式不正确");
        }
        return list;
    }

    /**
     * 人员数据赋值
     *
     * @param row
     * @param obj
     * @param files
     * @return JSONObject
     * @author cwchen
     * @date 2024/7/17 10:01
     */
    public static JSONObject setPersonObjData(Row row, JSONObject obj, List<Map<String, Object>> files) {
        obj.put("rowNum", row.getRowNum() + 1);
        if (row.getCell(BusinessConstants.CELL_1) != null) {
            // 姓名
            obj.put("name", row.getCell(BusinessConstants.CELL_1).getStringCellValue());
        }
        if (row.getCell(BusinessConstants.CELL_2) != null) {
            // 性别
            obj.put("sex", row.getCell(BusinessConstants.CELL_2).getStringCellValue());
        }
        if (row.getCell(BusinessConstants.CELL_3) != null) {
            // 身份证号
            obj.put("idCard", row.getCell(BusinessConstants.CELL_3).getStringCellValue());
        }
        if (row.getCell(BusinessConstants.CELL_4) != null) {
            // 电话
            obj.put("phone", row.getCell(BusinessConstants.CELL_4).getStringCellValue());
        }
        obj = setFileObj(row, obj, files);
        return obj;
    }

    public static JSONObject setFileObj(Row row, JSONObject obj, List<Map<String, Object>> files) {
        // 人脸照片
        String fileName = "file" + "_" + row.getRowNum() + "_1";
        for (Map<String, Object> map : files) {
            for (String keyName : map.keySet()) {
                if (Objects.equals(fileName, keyName)) {
                    obj.put("file", map.get(keyName));
                }
            }
        }
        return obj;
    }

    /**
     * 获取图片和位置 (xlsx)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, PictureData> getPicturesXlsx(XSSFSheet sheet, String className) throws IOException {
        Map<String, PictureData> map = new HashMap<>(16);
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();

                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    Row row = sheet.getRow(marker.getRow());
                    if (row == null) {
                        continue;
                    }
                    String keyName = null;
                    if (Objects.equals(className, BusinessConstants.PERSON_IMPORT_VO)) {
                        keyName = setKey(picture, row, BusinessConstants.PERSON_IMPORT_VO);
                    } else {
                        keyName = "";
                    }
                    map.put(keyName, picture.getPictureData());
                }
            }
        }
        return map;
    }

    /**
     * 获取图片和位置 (xls)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, PictureData> getPicturesXls(HSSFSheet sheet, String className) throws IOException {
        int i = 0;
        Map<String, PictureData> map = new HashMap<String, PictureData>(16);
        if (sheet.getDrawingPatriarch() != null) {
            List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
            for (HSSFShape shape : list) {
                if (shape instanceof HSSFPicture) {
                    HSSFPicture picture = (HSSFPicture) shape;
                    int pictureIndex = ((HSSFPicture) shape).getPictureIndex();
                    if (pictureIndex != -1) {
                        HSSFClientAnchor cAnchor = picture.getClientAnchor();
                        Row row = sheet.getRow(cAnchor.getRow1());
                        if (row == null) {
                            continue;
                        }
                        String keyName = null;
                        if (cAnchor.getCol1() == 5) {
                            keyName = "file" + "_" + cAnchor.getRow1() + "_1";
                        }
                        map.put(keyName, picture.getPictureData());
                    }
                }
            }
        }
        return map;
    }

    /**
     * 图片写出
     */
    public static List<Map<String, Object>> writeImg(Map<String, PictureData> mapData, String className, String uploadPath) throws IOException {
        if (mapData == null || mapData.size() == 0) {
            return new ArrayList<>();
        }
        Object[] keyArr = mapData.keySet().toArray();
        ArrayList<Map<String, Object>> filename = new ArrayList<>();
        for (int i = 0; i < mapData.size(); i++) {
            Map<String, Object> map = new HashMap<>(16);
            // 获取图片流
            PictureData pic = mapData.get(keyArr[i]);
            // 获取图片索引
            String picName = keyArr[i].toString();
            byte[] data = pic.getData();
            MultipartFile multipartFile = BytesToMultipartFileUtil.encodeToMultipartFile(data, uploadPath);
            map.put(picName + "", multipartFile);
            filename.add(map);
        }
        return filename;
    }


    private static String setKey(XSSFPicture picture, Row row, String className) {
        if (row == null) {
            return null;
        }
        ;
        String result = picture.getPreferredSize().toString();
        String pattern = "\\<xdr\\:col\\>[1-9]\\d*\\<\\/xdr\\:col\\>";
        Pattern r = Pattern.compile(pattern);
        String col = "";
        Matcher m = r.matcher(result);
        if (m.find()) {
            col = m.group(0);
        } else {
            System.out.println("NO MATCH");
        }

        if (Objects.equals(className, BusinessConstants.PERSON_IMPORT_VO)) {
            switch (col) {
                case "<xdr:col>5</xdr:col>":
                    return "file" + "_" + row.getRowNum() + "_1";
                default:
                    return null;
            }
        } else {
            return "";
        }

    }

    private static void setExcelToString(int j, Row row) {

        for (int i = 0; i < j; i++) {
            if (row.getCell(i) != null) {
                row.getCell(i).setCellType(CellType.STRING);
            }
        }
    }
}
