package com.basic.security.utils;

import android.content.ContextWrapper;
import android.os.SystemClock;
import android.text.TextUtils;

import com.basic.security.base.BaseApplication;
import com.basic.security.manager.IdentityManager;
import com.basic.security.manager.OrgManager;
import com.basic.security.manager.PersonIdentityManager;
import com.basic.security.manager.PersonManager;
import com.basic.security.manager.TimeManager;
import com.basic.security.model.Identity;
import com.basic.security.model.ModelAdapter;
import com.basic.security.model.Org;
import com.basic.security.model.Person;
import com.basic.security.model.Time;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

public class ExcelUtil {
    static String cellData;
    static FormulaEvaluator formulaEvaluator;
    static Row row;
    static DecimalFormat decimalFormat = new DecimalFormat("###.#");

    private static String getCellAsString(int columnIndex) {
        String value = "";
        try {
            Cell cell = row.getCell(columnIndex);
            CellValue cellValue = formulaEvaluator.evaluate(cell);
            switch (cellValue.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    value = "" + cellValue.getBooleanValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    double numericValue = cellValue.getNumberValue();
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        double date = cellValue.getNumberValue();
                        SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yy");
                        value = formatter.format(HSSFDateUtil.getJavaDate(date));
                    } else {
                        value = "" + decimalFormat.format(numericValue);
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    value = "" + cellValue.getStringValue();
                    break;
                default:
            }
        } catch (NullPointerException e) {
//            ToastUtil.show("Null Pointer Exception in getCellAsString");
            e.printStackTrace();
        }
        return value;
    }

    public static void parseExcel(File excelFile, Map<String, File> photoFileMap) {
        FaceId.instance.initSdk(new ContextWrapper(BaseApplication.getApplication().activity).getFilesDir().getAbsolutePath());
        String rootOrgId = OrgManager.findRootOrg().getString(Org.id);
        if (excelFile != null) {
            try {
                FileInputStream inputStream = new FileInputStream(excelFile.getAbsolutePath());
                XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
                XSSFSheet sheet = workbook.getSheetAt(0);
                int rowCount = sheet.getPhysicalNumberOfRows();
                formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
                Map<String, ModelAdapter> namesOrgMap = new HashMap<>();
                for (int r = 1; r < rowCount; r++) {
                    try {
                        row = sheet.getRow(r);
                        int colCount = row.getPhysicalNumberOfCells();
                        String photoId = getCellAsString(0);
                        File photoFile = photoFileMap.get(photoId);
                        if (photoFile == null) {
                            continue;
                        }
                        photoFileMap.remove(photoId);
                        ModelAdapter savedPerson = PersonManager.savePicToDatabase(photoFile, rootOrgId);
                        if (savedPerson != null) {
                            ModelAdapter personOrg;
                            String personName = getCellAsString(1);
                            savedPerson.setString(Person.name, personName);
                            String schoolName = getCellAsString(2);
                            ModelAdapter schoolOrg = OrgManager.buildParentOrg(schoolName);
                            personOrg = schoolOrg;
                            String gradeName = getCellAsString(3);
                            if (!TextUtils.isEmpty(gradeName)) {
                                ModelAdapter gradeOrg = OrgManager.addChildOrg(schoolOrg, gradeName);
                                personOrg = gradeOrg;
                                String className = getCellAsString(4);
                                if (!TextUtils.isEmpty(className)) {
                                    personOrg = OrgManager.addChildOrg(gradeOrg, className);
                                }
                            }
                            savedPerson.setString(Person.org_id, personOrg.getString(Org.id));
                            savedPerson.setString(Person.org_ids, personOrg.getString(Org.ids));
                            String studentId = getCellAsString(5);
                            savedPerson.setString(Person.student_id, studentId);
                            String id_card = getCellAsString(6);
                            savedPerson.setString(Person.id_card_number, id_card);
                            String phone = getCellAsString(7);
                            savedPerson.setString(Person.phone, phone);
                            String identity = getCellAsString(8);
                            if (identity.contains(",")) {
                                String[] identityArray = identity.split(",");
                                for (String identityStr : identityArray) {
                                    if (!TextUtils.isEmpty(identityStr)) {
                                        ModelAdapter identityModel = IdentityManager.findIdentityByName(identityStr);
                                        PersonIdentityManager.save(savedPerson.getString(Person.id), identityModel.getString(Identity.id));
                                    }
                                }
                            } else {
                                if (!TextUtils.isEmpty(identity)) {
                                    ModelAdapter identityModel = IdentityManager.findIdentityByName(identity);
                                    PersonIdentityManager.save(savedPerson.getString(Person.id), identityModel.getString(Identity.id));
                                }
                            }
                            String signUpRule = getCellAsString(9);
                            savedPerson.setString(Person.sign_up_rule, signUpRule);
                            if ("有效时间内通行".equals(signUpRule)) {
                                boolean hasCheckedTimeRuleId = false;
                                String timeRuleName = getCellAsString(10);
                                if (!TextUtils.isEmpty(timeRuleName)) {
                                    ModelAdapter time = TimeManager.findByTimeRuleName(timeRuleName);
                                    if (time != null) {
                                        String checked_time_rule_id = time.getString(Time.id);
                                        if (!TextUtils.isEmpty(checked_time_rule_id)) {
                                            hasCheckedTimeRuleId = true;
                                            savedPerson.setString(Person.checked_time_rule_id, checked_time_rule_id);
                                        }
                                    }
                                }
                                if (!hasCheckedTimeRuleId) {
                                    ToastUtil.show("请添加名称为 " + timeRuleName + " 的时间规则");
                                }
                            }
                            savedPerson.setString(Person.sign_up_method, "批量导入");
                            savedPerson.setString(Person.all_device, "1");
                            PersonManager.save(savedPerson);
                            SocketUtil.rpcCallSavePerson(savedPerson);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                workbook.close();
                OrgManager.initTreeNodeList = false;
                OrgManager.initTreeNodeList();
                SocketUtil.rpcCallInitTreeNodeList();
            } catch (FileNotFoundException e) {
                ToastUtil.show("File Not Found");
            } catch (IOException e) {
                ToastUtil.show("IO Error reading input stream");
            }
        }
        for (File photoFile : photoFileMap.values()) {
            try {
                ModelAdapter person = PersonManager.savePicToDatabase(photoFile, rootOrgId);
                SocketUtil.rpcCallSavePerson(person);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        long begin = System.currentTimeMillis();
        while (!OrgManager.initTreeNodeList) {
            SystemClock.sleep(1000);
            if (System.currentTimeMillis() - begin > 5 * 1000) {
                break;
            }
        }
        ToastUtil.show("导入成功");
        BaseApplication.getApplication().activity.runOnUiThread(() -> BaseApplication.getApplication().activity.fragment_face_list.signed_up());
    }

    public static void main(String[] args) {
        String names = "aaaa,,,";
        String[] nameArray = names.split(",,,");
        System1.out.println("ExcelUtil.main " + names.split("|||").length);
//        ExcelUtil.parseExcel("E:\\workspace\\Security_excel\\app\\src\\main\\assets\\person.xlsx");
    }
}