package com.diagbot.api;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.diagbot.dto.ConceptDTO;
import com.diagbot.dto.RelationDTO;
import com.diagbot.entity.LibraryDetail;
import com.diagbot.entity.LibraryInfo;
import com.diagbot.entity.Medical;
import com.diagbot.entity.Relation;
import com.diagbot.facade.*;
import com.diagbot.vo.AmendTermVo;
import com.diagbot.vo.TermVo;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ImportExcel {

    private static ConceptFacade conceptFacade;

    private static LibraryInfoFacade libraryinfoFacade;

    private static LibraryDetailFacade libraryDetailFacade;

    private static MedicalFacade medicalFacade;

    private static RelationFacade relationFacade;

    private static String[] catelist = {"症状","查体","化验","辅检","诊断","药品","其他史","治疗方案"};

    public static void setConceptFacade(ConceptFacade conceptFacade) {
        ImportExcel.conceptFacade = conceptFacade;
    }

    public static void setLibraryinfoFacade(LibraryInfoFacade libraryinfoFacade) {
        ImportExcel.libraryinfoFacade = libraryinfoFacade;
    }

    public static void setLibraryDetailFacade(LibraryDetailFacade libraryDetailFacade) {
        ImportExcel.libraryDetailFacade = libraryDetailFacade;
    }

    public static void setMedicalFacade(MedicalFacade medicalFacade) {
        ImportExcel.medicalFacade = medicalFacade;
    }

    public static void setRelationFacade(RelationFacade relationFacade) {
        ImportExcel.relationFacade = relationFacade;
    }


    /**
     * 从文件批量导入术语信息
     *
     * @param file
     * @return ConceptDTO
     */
    public static ConceptDTO importBatch(MultipartFile file) {
        ConceptDTO conceptDTO = new ConceptDTO();
        List<String> messages = new ArrayList<>();

        InputStream inputStream = null;
        Workbook wb = null;

        AmendTermVo amendTermVo;
        TermVo termVo;
        Medical medical;
        List<Medical> medlist;
        RelationDTO relationDTO;
        List<RelationDTO> rellist;

        try {
            if (!file.isEmpty()) {
                inputStream = file.getInputStream();

                if (inputStream.available() > 5120000) {
                    messages.add("术语文件最大只支持5MB！");
                } else {
                    String fileName = file.getOriginalFilename();
                    if (fileName.lastIndexOf(".") != -1) {
                        String type = fileName.substring(fileName.lastIndexOf("."));
                        if (type.equals(".xls")) {
                            wb = new HSSFWorkbook(inputStream);
                        } else if (type.equals(".xlsx")) {
                            wb = new XSSFWorkbook(inputStream);
                        }
                        if (wb != null) {

                            CleanupDB(fileName);

                            Sheet sheet = wb.getSheetAt(0);
                            int count = 0;
                            String group, category, std_name, name, prop, prop1, minagestr, maxagestr;
                            int diag_id = 0;
                            int treat_id = 0;
                            String diag = "";
                            String treat = "";
                            String diag_type = "";
                            String treat_type = "";
                            String gender = "";
                            String dept = "";
                            String note = "";
                            int minage = 0;
                            int maxage = 0;
                            String grp_type = "";
                            String cate_type = "";
                            String std_type = "";

                            if (fileName.indexOf(catelist[7]) == -1) {
                                for (Row row : sheet) {
                                    count++;

                                    if (row != null) {
                                        if (count == 1) {
                                            grp_type = row.getCell(0).toString();
                                            if (medicalFacade.getTypeInfo(grp_type) == null) {
                                                System.out.println("术语类型-'" + grp_type + "'不存在!");
                                                break;
                                            }
                                            cate_type = row.getCell(1).toString();
                                            if (medicalFacade.getTypeInfo(cate_type) == null) {
                                                System.out.println("术语类型-'" + cate_type + "'不存在!");
                                                break;
                                            }
                                            std_type = row.getCell(2).toString().replace("标准名称", "");
                                            if (medicalFacade.getTypeInfo(std_type) == null) {
                                                System.out.println("术语类型-'" + std_type + "'不存在!");
                                                break;
                                            }
                                        }

                                        if (count > 1) {

                                            group = (row.getCell(0) == null) ? "" : row.getCell(0).toString().trim();
                                            category = (row.getCell(1) == null) ? "" : row.getCell(1).toString().trim();
                                            std_name = (row.getCell(2) == null) ? "" : row.getCell(2).toString().trim();
                                            name = (row.getCell(3) == null) ? "" : row.getCell(3).toString().trim();
                                            prop = (row.getCell(4) == null) ? "" : row.getCell(4).toString().trim();
                                            prop1 = (row.getCell(5) == null) ? "" : row.getCell(5).toString().trim();

                                            System.out.println(group + "\t" + category + "\t" + std_name + "\t" +
                                                    name + "\t" + prop + "\t" + prop1);

                                            if (std_type.equals("症状")) {
                                                gender = (row.getCell(8) == null) ? "" : row.getCell(8).toString();
                                                gender = (gender.indexOf(".") == -1) ? gender : gender.substring(0, gender.indexOf("."));
                                                minagestr = (row.getCell(9) == null) ? "0" : row.getCell(9).toString();
                                                minage = Integer.valueOf((minagestr.indexOf(".") == -1) ?
                                                        minagestr : minagestr.substring(0, minagestr.indexOf(".")));
                                                maxagestr = (row.getCell(10) == null) ? "0" : row.getCell(10).toString();
                                                maxage = Integer.valueOf((maxagestr.indexOf(".") == -1) ?
                                                        maxagestr : maxagestr.substring(0, maxagestr.indexOf(".")));

//                                            System.out.println(gender + "\t" + minage + "\t" + maxage);
                                            } else if (std_type.equals("诊断")) {
                                                dept = (row.getCell(7) == null) ? "" : row.getCell(7).toString();
                                            } else if (std_type.equals("药品")) {
                                                note = (row.getCell(7) == null) ? "" : row.getCell(7).toString();
                                            }


                                            // 设置实体
                                            if (std_name.trim().length() > 0 && name.trim().length() > 0) {
                                                amendTermVo = new AmendTermVo();
                                                amendTermVo.setConcept(std_name);
                                                amendTermVo.setNewterm(name);
                                                amendTermVo.setOldterm("");
                                                amendTermVo.setNewtype(std_type);

                                                medical = new Medical();
                                                medical.setName(name);
                                                medical.setStdName(std_name);
                                                medical.setCateName(category);
                                                if (std_type.equals("症状")) {
                                                    medical.setBodypart(prop);
                                                    medical.setSubBodypart(prop1);
                                                    medical.setGender(gender);
                                                    medical.setMinAge(minage);
                                                    medical.setMaxAge(maxage);
                                                    medical.setGrp(group);
                                                } else if (std_type.equals("诊断")) {
                                                    medical.setDept(dept);
                                                } else if (std_type.equals("药品")) {
                                                    medical.setNote(note);
                                                    String grpname = (group.length() > 0) ? group : "";
                                                    String catename = (category.length() > 0) ? ("(" + category + ")") : "";
                                                    medical.setFunction(grpname + catename);
                                                }
                                                medlist = new ArrayList<>();
                                                medlist.add(medical);
                                                amendTermVo.setMedicalInfo(medlist);
//                                                amendTermVo.setMedicalInfo(medical);

                                                conceptFacade.upsertConceptInfo(amendTermVo);
                                                System.out.println(name + ":\t 已入库.");
                                            }

                                            if (category.length() > 0) {
                                                // 设置套餐
                                                amendTermVo = new AmendTermVo();
                                                amendTermVo.setConcept(category);
                                                amendTermVo.setNewterm(category);
                                                amendTermVo.setOldterm("");
                                                amendTermVo.setNewtype(cate_type);

                                                medical = new Medical();
                                                medical.setName(category);
                                                medical.setStdName(category);
                                                medical.setCateName(group);
                                                medical.setCode("");
                                                medical.setNote("");
                                                medlist = new ArrayList<>();
                                                medlist.add(medical);
                                                amendTermVo.setMedicalInfo(medlist);
//                                                amendTermVo.setMedicalInfo(medical);

                                                conceptFacade.upsertConceptInfo(amendTermVo);

                                                if (!std_name.equals(category)) {
                                                    relationDTO = new RelationDTO();
                                                    relationDTO.setStartId(conceptFacade.getConceptId(std_name, std_type));
                                                    relationDTO.setStartName(std_name);
                                                    relationDTO.setRelationId(1);
                                                    relationDTO.setEndId(conceptFacade.getConceptId(category, cate_type));
                                                    relationDTO.setEndName(category);
                                                    rellist = new ArrayList<>();
                                                    rellist.add(relationDTO);
                                                    amendTermVo.setRelations(rellist);

                                                    relationFacade.upsertRelationInfo(amendTermVo);
                                                }

                                                System.out.println(category + ":\t 已入库.");
                                            }

                                            if (group.length() > 0) {
                                                // 设置大类
                                                amendTermVo = new AmendTermVo();
                                                amendTermVo.setConcept(group);
                                                amendTermVo.setNewterm(group);
                                                amendTermVo.setOldterm("");
                                                amendTermVo.setNewtype(grp_type);

                                                medical = new Medical();
                                                medical.setName(group);
                                                medical.setStdName(group);
                                                medical.setCateName("");
                                                medical.setCode("");
                                                medical.setNote("");
                                                medlist = new ArrayList<>();
                                                medlist.add(medical);
                                                amendTermVo.setMedicalInfo(medlist);
//                                                amendTermVo.setMedicalInfo(medical);

                                                conceptFacade.upsertConceptInfo(amendTermVo);

                                                if (!category.equals(group)) {
                                                    relationDTO = new RelationDTO();
                                                    relationDTO.setStartId(conceptFacade.getConceptId(category, cate_type));
                                                    relationDTO.setStartName(category);
                                                    relationDTO.setRelationId(1);
                                                    relationDTO.setEndId(conceptFacade.getConceptId(group, grp_type));
                                                    relationDTO.setEndName(group);
                                                    rellist = new ArrayList<>();
                                                    rellist.add(relationDTO);
                                                    amendTermVo.setRelations(rellist);

                                                    relationFacade.upsertRelationInfo(amendTermVo);
                                                }

                                                System.out.println(group + ":\t 已入库.");
                                            }

//                                        if (count >= 20) break;
                                        }
                                    }
                                    System.out.println(count + " 条记录添加入库.");
                                }
                            }
                            else {
                                for (Row row : sheet) {
                                    count++;

                                    if (row != null) {
                                        if (count == 1) {
                                            diag = row.getCell(0).toString().trim();
                                            treat = row.getCell(3).toString().trim();
                                            diag_type = "诊断";
                                            treat_type = "药品";
                                        }
                                        else {
                                            diag = row.getCell(0).toString().trim();
                                            treat = row.getCell(3).toString().trim();

                                            if (diag.length() > 0) {
                                                diag_id = conceptFacade.getConceptId(diag, diag_type);
                                                treat_id = conceptFacade.getConceptId(treat, treat_type);

                                                if (diag_id > 0 && treat_id > 0) {
                                                    amendTermVo = new AmendTermVo();

                                                    relationDTO = new RelationDTO();
                                                    relationDTO.setStartId(diag_id);
                                                    relationDTO.setStartName(diag);
                                                    relationDTO.setRelationId(11);
                                                    relationDTO.setEndId(treat_id);
                                                    relationDTO.setEndName(treat);
                                                    rellist = new ArrayList<>();
                                                    rellist.add(relationDTO);
                                                    amendTermVo.setRelations(rellist);

                                                    relationFacade.upsertRelationInfo(amendTermVo);

                                                    System.out.println(diag + "\t治疗\t" + treat + ":\t 已入库.");
//                                                    break;
                                                }
                                            }
                                        }
                                    }
                                }
                            }

                        } else {
                            messages.add("非excel文件无法解析！");
                        }
                    } else {
                        messages.add("未知文件无法解析！");
                    }
                }
            } else {
                messages.add("无文件上传！");
            }
        }
        catch (IOException ioe) {
            ioe.printStackTrace();
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
        finally {
            conceptDTO.setMessage(messages);
            try {

                if (inputStream != null) {
                    inputStream.close();
                }
                if (wb != null) {
                    wb.close();
                }
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }

        return conceptDTO;
    }

    /**
     * 批量导入数据前，删除相关表
     *
     * @param filename
     *
     */
    public static void CleanupDB(String filename) {
        String cate = "";
        List<Integer> Ids;

        try {
            for (String item:catelist) {
                if (filename.indexOf(item) >= 0) {
                    cate = item;
                    break;
                }
            }

            if (cate.length() > 0 && !cate.equals(catelist[7])) {
                System.out.println(cate);

                // 从 LibraryInfo 中找出所有需要删除的Id
                QueryWrapper<LibraryInfo> wrapper = new QueryWrapper<>();
                wrapper.likeRight("type", cate);
                List<LibraryInfo> libinfolist = libraryinfoFacade.list(wrapper);

                // 删除术语信息
                Ids = new ArrayList<>();
                for (int i=0; i<libinfolist.size(); i++) {
                    Ids.add(libinfolist.get(i).getId().intValue());
//                    break;
                }

                if (Ids.size() > 0) {
                    libraryinfoFacade.removeByIds(Ids);
                }

                // 删除术语详细信息
                List<LibraryDetail> limbodetails = libraryDetailFacade.getLimboItems();
                Ids = new ArrayList<>();
                for (int i=0; i<limbodetails.size(); i++) {
                    Ids.add(limbodetails.get(i).getId().intValue());
//                    break;
                }

                if (Ids.size() > 0) {
                    libraryDetailFacade.removeByIds(Ids);
                }
//            libraryDetailFacade.deleteDetail();

                // 删除医学记录
                List<Medical> limbomed = medicalFacade.getLimboItems();
                Ids = new ArrayList<>();
                for (int i=0; i<limbomed.size(); i++) {
                    Ids.add(limbomed.get(i).getId().intValue());
//                    break;
                }

                if (Ids.size() > 0) {
                    medicalFacade.removeByIds(Ids);
                }

                // 删除概念
                // 找出所有需要删除的概念
                List<ConceptDTO> limboIds = conceptFacade.getLimboItems();
                Ids = new ArrayList<>();
                for (int i=0; i<limboIds.size(); i++) {
                    Ids.add(limboIds.get(i).getId());
                }

                if (Ids.size() > 0) {
                    conceptFacade.removeByIds(Ids);
                }

                // 删除关系
                List<Relation> limboRelation = relationFacade.getLimboRelation();
                Ids = new ArrayList<>();
                for (int i=0; i<limboRelation.size(); i++) {
                    Ids.add(limboRelation.get(i).getId());
                }

                if (Ids.size() > 0) {
                    relationFacade.removeByIds(Ids);
                }
            }
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}
