package com.zbooksoft.gdmis.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ruili.wcp.data.entity.config.ImportColumn;
import com.ruili.wcp.data.entity.config.ImportTemplate;
import com.ruili.wcp.engine.form.IFormData;
import com.ruili.wcp.service.config.ImportColumnService;
import com.ruili.wcp.service.config.ImportTemplateService;
import com.ruili.wcp.web.common.LicenseException;
import com.ruili.wcp.web.model.AjaxResponse;
import com.ruili.wcp.web.model.ErrorInfo;
import com.zbooksoft.gdmis.common.BoxEnum;
import com.zbooksoft.gdmis.service.CatSwajxxService;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.authz.annotation.RequiresUser;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import javax.crypto.spec.PSource;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

import static com.ruili.wcp.web.controller.general.DataImportController.getExcelValue;

/**
 * @Description:导入Excel文件
 * @Author: zhai
 * @Date: 2024/9/11
 **/
@Controller
@RequestMapping("/importExcel")
public class ImportExcelController {

    @Autowired
    ImportTemplateService importTemplateService;
    @Autowired
    ImportColumnService importColumnService;
    @Autowired
    CatSwajxxService catSwajxxService;
    //    @Autowired
//    CatSwzkxxService catSwzkxxService;
//    @Autowired
//    CatSwyxxxService catSwyxxxService;
    @Autowired
    IFormData iform;
    @Autowired
    JdbcTemplate jdbcTemplate;
    private static final Logger logger = LoggerFactory.getLogger(ImportExcelController.class);


    /**
     * 导入页面
     *
     * @param type 0:从转孔基本信息进入 1:从钻孔图幅进入
     * @return
     */
    @RequestMapping({"/importIndex"})
    @RequiresUser
    public ModelAndView importIndex(Integer type) {
        ModelAndView mv = new ModelAndView("gh/excel/importExcel");
        return mv;
    }

    /**
     * @param file 0 导入转孔信息  1 导入图幅信息 2 导入实物案卷信息
     * @return
     * @throws LicenseException
     */

    @RequestMapping({"/importExcel"})
    @ResponseBody
    @RequiresUser
    public Object importExcel(@RequestParam(value = "file", required = false) MultipartFile file) throws LicenseException {
        try {
            readExcelBySwyp(file);
            return new AjaxResponse(true);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return new AjaxResponse(new ErrorInfo("导入失败"), false);
        }
    }


    private void readExcelBySwyp(MultipartFile file) throws IOException {
        List<Long> importTemplateIds = Arrays.asList(1954734982477950977L, 1954736942581067778L);

        String originalFilename = file.getOriginalFilename();

        InputStream inputStream = file.getInputStream();
        if (originalFilename.matches("^.+\\.(?i)(xls)$")) {
            //创建工作簿对象
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
            HashMap<String, Object> map = readProject2003(hssfWorkbook);
            readExcel2003(hssfWorkbook, map);

        }
        if (originalFilename.matches("^.+\\.(?i)(xlsx)$")) {
            //创建工作簿对象
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
            for (int i = 0; i < 2; i++) {
                HashMap<String, Object> map = null;
                readExcel2007(xssfWorkbook, i, importTemplateIds.get(i), map, 0);
            }
        }

    }

    //Excel2003 xls格式
    private HashMap<String, Object> readProject2003(HSSFWorkbook hssfWorkbook) {
        HashMap<String, Object> map = new HashMap<>();
        //读取第i个工作表
        HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
        //项目名称：1:25万南沙幅海域区域地质调查
        String xmmc = getValue(sheet, 6, 3);
        map.put("XMMC", xmmc);

        String xmbh = getValue(sheet, 7, 3);
        map.put("XMBH", xmbh);

        String selectProject = "SELECT ID FROM CAT_FILE_AJJXX WHERE XMKYQMC = '" + xmmc + "' AND XMKYQBH = '" + xmbh + "'";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(selectProject);
        if (mapList.size() > 0) {
            String ajId = mapList.get(0).get("ID").toString();
            map.put("AJ_ID", ajId);
        }
        String dcchc = getValue(sheet, 8, 3);
        map.put("DCCHC", dcchc);

        String dchy = getValue(sheet, 9, 3);
        map.put("DCHY", dchy);

        String dcqu = getValue(sheet, 10, 3);
        map.put("DCQU", dcqu);

        String dcsj = getValue(sheet, 11, 3);
        map.put("DCSJ", dcsj);

        String jyr = getValue(sheet, 12, 3);
        map.put("JYR", jyr);

        String syr = getValue(sheet, 13, 3);
        map.put("SYR", syr);

        String syrq = getValue(sheet, 14, 3);
        map.put("SYRQ", syrq);

        return map;

    }

    private String getValue(HSSFSheet sheet, int rowIndex, int cellIndex) {
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            Cell cell = row.getCell(cellIndex);
            if (cell != null) {
                String excelValue = getExcelValue(cell) == null ? "" : getExcelValue(cell).toString();
                return subString(excelValue);
            }
        }
        return null;
    }

    private String subString(String cellValue) {
        if (cellValue != null && cellValue.contains("：")) {
            int index = cellValue.indexOf("：");
            return cellValue.substring(index + 1).trim();
        }
        return cellValue;
    }


    //Excel2003 xls格式
    private void readExcel2003(HSSFWorkbook hssfWorkbook, Map<String, Object> mapData) {

        ImportTemplate importTemplate = importTemplateService.getByIdImportTemplate(1966070853328244738L);
        QueryWrapper<ImportColumn> query = new QueryWrapper();
        query.eq("import_template_id", importTemplate.getImportTemplateId());
        List<ImportColumn> columnList = importColumnService.list(query);
        List<String> excelColumns = new ArrayList();

        //读取第i个工作表
        HSSFSheet sheet = hssfWorkbook.getSheetAt(1);
        //获取最后一行的num，即总行数。此处从6行开始
        int maxRow = sheet.getLastRowNum();
        Row row = sheet.getRow(3);
//        Row row = sheet.getRow(4);
        int totalCells = row.getPhysicalNumberOfCells();
        for (int j = 0; j < totalCells; ++j) {
            Cell cell = row.getCell(j);
            String cellValue = getExcelValue(cell) == null ? "" : getExcelValue(cell).toString();

            if (StringUtils.isNotEmpty(cellValue) && "站位位置".equals(cellValue)) {
                Row rowJd = sheet.getRow(4);
                Cell cellJd = rowJd.getCell(j);
                String excelValueJd = getExcelValue(cellJd);
                excelColumns.add(excelValueJd);

                Cell cellWd = rowJd.getCell(j + 1);
                String excelValueWd = getExcelValue(cellWd);
                excelColumns.add(excelValueWd);
            } else if (StringUtils.isNotEmpty(cellValue)) {
                excelColumns.add(cellValue.toString());
            }
        }
        for (int j = 6; j <= maxRow; j++) {
            Row rowValue = sheet.getRow(j);
            Map<String, Object> map = new HashMap();
            map.putAll(mapData);
            for (int z = 0; z < columnList.size(); z++) {
                ImportColumn column = columnList.get(z);
                int columnIndex = excelColumns.indexOf(column.getExcelColunm());
                if (columnIndex >= 0) {
                    Cell cell = rowValue.getCell(columnIndex);
                    Object cellValue = getExcelValue(cell);
                    map.put(column.getColumnName(), cellValue);
                }
            }
            iform.insert(importTemplate.getTableId(), map);
        }

    }

    // Excel2007 xlsx
    private void readExcel2007(XSSFWorkbook xssfWorkbook, int sheetAt, Long importTemplateId, Map<String, Object> mapData, int rowNum) {
        ImportTemplate importTemplate = importTemplateService.getByIdImportTemplate(importTemplateId);
        QueryWrapper<ImportColumn> query = new QueryWrapper();
        query.eq("import_template_id", importTemplate.getImportTemplateId());
        List<ImportColumn> columnList = importColumnService.list(query);
        List<String> excelColumns = new ArrayList();
        int dhIndex = 0;
        //读取第i个工作表
        XSSFSheet sheet = xssfWorkbook.getSheetAt(sheetAt);
        //获取最后一行的num，即总行数。此处从0开始
        int maxRow = sheet.getLastRowNum();
        Row row = sheet.getRow(0);
        int totalCells = row.getPhysicalNumberOfCells();
        for (int j = 0; j < totalCells; ++j) {
            Cell cell = row.getCell(j);
            Object cellValue = getExcelValue(cell);
            if (sheetAt == 1 && "案卷档号".equals(cellValue.toString())) {
                dhIndex = j;
            }
            excelColumns.add(cellValue.toString());
        }
        for (int j = 1; j <= maxRow; j++) {
            try {
                Row rowValue = sheet.getRow(j);
                Map<String, Object> map = new HashMap();
                if (sheetAt == 1) {
                    Cell cell = rowValue.getCell(dhIndex);
                    Object cellValue = getExcelValue(cell);
                    String sql = "select id from cat_file_ajjxx where dh='" + cellValue + "'";
                    Long id = jdbcTemplate.queryForObject(sql, Long.class);
                    map.put("AJ_ID", id);
                }
                for (int z = 0; z < columnList.size(); z++) {
                    ImportColumn column = columnList.get(z);
                    if ("2".equals(column.getLinkedData())) {
                        String linkedType = column.getLinkedType();
                        String excelColunm = column.getExcelColunm();
                        int columnIndex = excelColumns.indexOf(excelColunm);
                        if (columnIndex >= 0) {
                            Cell cell = rowValue.getCell(columnIndex);
                            Object cellValue = getExcelValue(cell);
                            String[] split = linkedType.split("\\|");
                            String tableName = split[0];
                            String columnName = split[1];
                            String valueName = split[2];
                            String sql = "select " + valueName + " from " + tableName + " where " + columnName + "='" + cellValue + "'";
                            String columnValue = jdbcTemplate.queryForObject(sql, String.class);
                            map.put(column.getColumnName(), columnValue);
                        }
                    } else {
                        int columnIndex = excelColumns.indexOf(column.getExcelColunm());
                        if (columnIndex >= 0) {
                            Cell cell = rowValue.getCell(columnIndex);
                            Object cellValue = getExcelValue(cell);
                            if ("工作时间".equals(column.getColumnName().toString())) {
                                String string = cellValue.toString();
                                boolean contains = string.contains("-");
                                if (contains) {
                                    String[] split = string.split("-");
                                    map.put("QSSJ", split[0]);
                                    map.put("ZZSJ", split[1]);
                                } else {
                                    map.put("QSSJ", string);
                                }
                            } else {
                                map.put(column.getColumnName(), cellValue);
                            }
                        }
                    }
                }
                iform.insert(importTemplate.getTableId(), map);
            } catch (Exception e) {
                logger.info(e.getMessage(), e);
            }
        }
    }

    /**
     * 关联盒号关联
     *
     * @param
     * @return
     */
    @RequestMapping({"/packingIndex"})
    @RequiresUser
    public ModelAndView packingIndex() {
        ModelAndView mv = new ModelAndView("gh/excel/packingExcel");
        return mv;
    }

    /**
     * @param file 0 导入转孔信息  1 导入图幅信息 2 导入实物案卷信息
     * @return
     * @throws LicenseException
     */

    @RequestMapping({"/packingExcel"})
    @ResponseBody
    @RequiresUser
    public Object packingExcel(@RequestParam(value = "file", required = false) MultipartFile file) throws LicenseException {
        try {
            relationPacking(file);
            return new AjaxResponse(true);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return new AjaxResponse(new ErrorInfo("导入失败"), false);
        }
    }

    private void relationPacking(MultipartFile file) throws IOException {

        String originalFilename = file.getOriginalFilename();

        InputStream inputStream = file.getInputStream();
        if (originalFilename.matches("^.+\\.(?i)(xls)$")) {
            //创建工作簿对象
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
            //读取第i个工作表
            HSSFSheet sheet = hssfWorkbook.getSheetAt(0);

            int maxRow = sheet.getLastRowNum();

            for (int j = 1; j <= maxRow; j++) {
                Row rowValue = sheet.getRow(j);
                Cell boxNumberCell = rowValue.getCell(0);
                Object boxNumber = getExcelValue(boxNumberCell);

                Cell storageLocationCell = rowValue.getCell(1);
                Object storageLocation = getExcelValue(storageLocationCell);

                //04 1 10 1 02 06
                //ZO1 A10左 02列 06层
                String update = "update BUS_PACKING_MANAGE set STORAGE_LOCATION ='" + storageLocation + "' where BOX_NUMBER = '" + boxNumber + "'";
                jdbcTemplate.update(update);
            }

        }
        if (originalFilename.matches("^.+\\.(?i)(xlsx)$")) {
            //创建工作簿对象
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);

            XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
            //获取最后一行的num，即总行数。此处从0开始
            int maxRow = sheet.getLastRowNum();
            Row row = sheet.getRow(0);
            for (int j = 1; j <= maxRow; j++) {
                Row rowValue = sheet.getRow(j);
                Cell boxNumberCell = rowValue.getCell(0);
                Object boxNumber = getExcelValue(boxNumberCell);

                Cell storageLocationCell = rowValue.getCell(1);
                Object storageLocationCode = getExcelValue(storageLocationCell);

                String storageLocation = parseStorageLocationWithEnum(storageLocationCode == null ? "" : storageLocationCode.toString());
                String update = "update BUS_PACKING_MANAGE set STORAGE_LOCATION_CODE = '" + storageLocationCode + "', STORAGE_LOCATION ='" + storageLocation + "' where BOX_NUMBER = '" + boxNumber + "'";
                jdbcTemplate.update(update);
            }
        }

    }

    public static String parseStorageLocationWithEnum(String code) {
        if (code == null || code.length() != 10) {
            throw new IllegalArgumentException("Invalid code format, expected 10 characters");
        }

        // 提取各部分
        String zoneCode = code.substring(0, 2);
        String zoneName = code.substring(2, 3);
        String rackCode = code.substring(3, 5);
        String sideCode = code.substring(5, 6);
        String column = code.substring(6, 8);
        String layer = code.substring(8, 10);

        // 使用枚举获取区域名称
        String zoneStr = BoxEnum.getNameByCode(zoneCode);

        // 处理方向映射
        String sideStr = "1".equals(sideCode) ? "左" : "右";

        // 根据数字获取对应字母
        String letter = BoxEnum.RackLetter.getLetterByNumber(zoneName); // 返回 "A"

        // 构造架号
        String rackStr = letter + rackCode;

        // 组合最终结果
        return String.format("%s-%s%s-%s列-%s层", zoneStr, rackStr, sideStr, column, layer);
    }

    public static String generateStorageLocationCode(String location) {
        // 示例输入: Z01-A01左-08列-02层
        String[] parts = location.split("-");

        String zoneStr = parts[0];           // Z01
        String rackAndSide = parts[1];       // A01左

        // 区域代码 (例如 Z01 -> 04)
        String zoneCode = BoxEnum.getCodeByName(zoneStr);

        // 架号中的字母与数字 (例如 A01 -> A=1, 01)
        String rackLetter = rackAndSide.substring(0, 1);   // A
        String rackNumber = rackAndSide.substring(1, 3); // 01
        String sideStr = rackAndSide.substring(3); // 左 or 右

        // 获取字母对应的数字 (例如 A -> 1)
        // 根据数字获取对应字母
        String rackLetterNum = BoxEnum.RackLetter.getNumberByLetter(rackLetter); // 返回 "A"


        // 方向代码 (左 -> 1, 右 -> 2)
        String sideCode = "左".equals(sideStr) ? "1" : "0";


        // 拼接最终结果
        return zoneCode + rackLetterNum + rackNumber + sideCode;
    }

    public static void main(String[] args) {
//        String s = parseStorageLocationWithEnum("0411010206");
        String locationNumber = "Z01-A01左-08列-02层";
        //0410110802
        String s = generateStorageLocationCode(locationNumber);
        System.out.println(s);
    }
}