package common.service.impl;


import common.model.*;
import common.repository.*;
import common.service.DmpIncomeService;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.joda.time.DateTime;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StopWatch;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import util.ResultModel;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@Service
public class DmpIncomeServiceImpl implements DmpIncomeService {

	Logger logger = LoggerFactory.getLogger(DmpIncomeServiceImpl.class);

	@Autowired
	DmpIncomeRepository dmpIncomeRepository;

	@Autowired
	ContractRepository contractRepository;

	@Autowired
	TradeTypeRepsitory tradeTypeRepsitory;

	@Autowired
	SalesRepository salesRepository;

	@Autowired
	PackageBaseRepository packageBaseRepository;

	@Autowired
	BarrioCityRepository barrioCityRepository;

	@Autowired
	ContractBodyRepository contractBodyRepository;


	public static final Map<String, String> SHEET_NAMES = new HashMap();
	private static final Map<String, String> CONTRACT_TYPE_NAME = new HashMap();
	private static final Map<String, String> BUSINESS_TYPE_NAME = new HashMap();
	private static final Map<String, String> SETAGREEMENT_TYPE_NAME = new HashMap();


	static {
		SHEET_NAMES.put("tkio", "TrackingIO");
		SHEET_NAMES.put("dmp", "DMP");
		SHEET_NAMES.put("fake", "防作弊卫士");
		SHEET_NAMES.put("adi", "ADI");
		SHEET_NAMES.put("ads", "ADS");
		SHEET_NAMES.put("abtest", "ABTEST");
		SHEET_NAMES.put("cas", "CAS");
		CONTRACT_TYPE_NAME.put("首次签约", "0");
		CONTRACT_TYPE_NAME.put("续约", "1");
		CONTRACT_TYPE_NAME.put("补充协议", "2");
		BUSINESS_TYPE_NAME.put("VIP", "1");//业务类型 1 VIP 2 共管
		BUSINESS_TYPE_NAME.put("共管", "2");
		BUSINESS_TYPE_NAME.put("前置机", "3");
		SETAGREEMENT_TYPE_NAME.put("普通协议", "1");//协议类型 1 普通协议 2 框架协议
		SETAGREEMENT_TYPE_NAME.put("框架协议", "2");
	}


	@Override
	public List<DmpIncome> listByCode(String contractCode) {
		List<DmpIncome> dmpIncomes = dmpIncomeRepository.findByContractCode(contractCode);
		return dmpIncomes;
	}

	@Override
	public List<DmpIncome> listByDs(String start, String end) {
		List<DmpIncome> dmpIncomes = dmpIncomeRepository.findByContractDs(start, end);
		if (dmpIncomes.isEmpty()) {
			return dmpIncomes;
		}
		List<String> codes = dmpIncomes.stream().map(v -> v.getContractCode()).collect(Collectors.toList());
		List<Object[]> contracts = contractRepository.findByDmpContractCode(codes);
		Map<String, Object[]> names = contracts.stream().collect(Collectors.toMap(v -> v[0] + "", v -> v, (v1, v2) -> v1));
		Map<String, String> cBodyMap = contractBodyRepository.findAllDis()
				.stream().collect(Collectors.toMap(ContractBody::getCode, ContractBody::getName, (v1, v2) -> v1));
		for (DmpIncome dmpIncome : dmpIncomes) {
			Object[] nameItem = names.get(dmpIncome.getContractCode());
			//my_body_name, customer_body, customer_short, business_type, agreement_type
			if (nameItem != null) {
				dmpIncome.setMyBodyName(nameItem[2] + "");
				dmpIncome.setCustomerBody(nameItem[3] + "");
				dmpIncome.setCustomerShort(nameItem[4] + "");
				dmpIncome.setBusinessType(nameItem[5] + "");
				dmpIncome.setAgreementType(nameItem[6] + "");
				if (StringUtils.isEmpty(dmpIncome.getMyBodyName()) || dmpIncome.getMyBodyName().equals("null")) {
					dmpIncome.setMyBodyName(cBodyMap.get(nameItem[1]));
				}
			}
		}
		return dmpIncomes;
	}

	@Override
	public HSSFWorkbook exportIncomeList(String startDate, String endDate, String bodyCode, String serchName) {
		List<DmpIncome> dmpIncomes = this.listByDs(startDate, endDate);
		dmpIncomes = dmpIncomes.stream()
				.filter(v -> StringUtils.isEmpty(bodyCode) || "all".equals(bodyCode) || bodyCode.equals(v.getMyBodyName()))
				.filter(v -> StringUtils.isEmpty(serchName) || (v.getContractCode().contains(serchName) || v.getCustomerBody().contains(serchName) || v.getMyBodyName().contains(serchName)))
				.collect(Collectors.toList());

		DecimalFormat df = new DecimalFormat("##,##0.00");
		//创建工作薄对象
		HSSFWorkbook workbook = new HSSFWorkbook();//这里也可以设置sheet的Name
		//创建工作表对象
		HSSFSheet sheet = workbook.createSheet();
		//创建工作表的行
		HSSFRow row = sheet.createRow(0);

		//表头
		String[] title = "我方签约主体,收入月份,签约方,客户简称,合同编号,业务类型,结算周期,系统结算,按月结算,税率,确认收入".split(",");
		for (int i = 0; i < title.length; i++) {
			row.createCell(i).setCellValue(title[i]);
		}

		String[] bussinesName = new String[]{"", "VIP", "共管", "前置机"};
		for (int j = 0; j < dmpIncomes.size(); j++) {
			HSSFRow rowBody = sheet.createRow(j + 1);
			DmpIncome income = dmpIncomes.get(j);
			String bussinesTypeName = income.getBusinessType() == null ? "" : bussinesName[Integer.parseInt(income.getBusinessType())];
			String[] dataItem = new String[]{
					income.getMyBodyName(), income.getIncomeMonth(), income.getCustomerBody(),
					income.getCustomerShort(), income.getContractCode(), bussinesTypeName,
					income.getPeriod(), income.getSysSettlement(), income.getMonthSettlement(),
					income.getTaxRate(), income.getConfirmSettlement()
			};

			for (int w = 0; w < dataItem.length; w++) {
				rowBody.createCell(w).setCellValue(dataItem[w]);
			}
		}
		return workbook;
	}

	@Override
	public DmpIncome update(DmpIncome dmpIncome) {

		if (dmpIncome.getId() == null) {
			return null;
		}

		DmpIncome dbItem = dmpIncomeRepository.findOne(dmpIncome.getId());
		dbItem.setIncomeMonth(dmpIncome.getIncomeMonth());
		dbItem.setPeriod(dmpIncome.getPeriod());
		dbItem.setSysSettlement(dmpIncome.getSysSettlement());
		dbItem.setMonthSettlement(dmpIncome.getMonthSettlement());
		dbItem.setTaxRate(dmpIncome.getTaxRate());
		dbItem.setConfirmSettlement(dmpIncome.getConfirmSettlement());
		dmpIncomeRepository.save(dbItem);
		return dbItem;
	}

	@Override
	public Long delete(DmpIncome dmpIncome) {
		if (dmpIncome.getId() == null) {
			return null;
		}
		dmpIncomeRepository.delete(dmpIncome.getId());
		return dmpIncome.getId();
	}

	@Override
	public ResultModel uploadFile(MultipartFile file, String platform, User loginAccount) {

		Workbook workbook = getWorkbook(file);
		if (workbook == null) {
			return ResultModel.ERROR("获取上传文件错误");
		}
		int rowNumber = checkSheetTitle(workbook, platform + "_income");
		if (rowNumber == -1) {
			return ResultModel.ERROR("模板表头错误");
		} else if (rowNumber <= 1) {
			return ResultModel.ERROR("文件为空");
		}

		Sheet sheet = workbook.getSheetAt(0);
		DecimalFormat df = new DecimalFormat("##,##0.00");
		StringBuffer erroMessage = new StringBuffer();
		StopWatch stopWatch = new StopWatch();
		stopWatch.start();
		Map<String, String> incomeMap = new HashMap<>();
		List<DmpIncome> dmpIncomeList = Stream.iterate(1, n -> n + 1).limit(rowNumber - 1).map(index -> {
			DmpIncome income = saveDmpIncomeItem(sheet, index, loginAccount);
			String key = income.getIncomeMonth() + income.getContractCode();
			if (incomeMap.containsKey(key)) {
				return null;
			}
			incomeMap.put(key, "1");
			return income;
		}).filter(in -> in != null).collect(Collectors.toList());

		if (dmpIncomeList.isEmpty()) {
			return ResultModel.OK();
		}
		ExecutorService executorService = Executors.newFixedThreadPool(30);
		final int[] indexLine = {0};
		CompletableFuture[] futures = dmpIncomeList.stream().map(income ->
				CompletableFuture.runAsync(
						() -> {

							DmpIncome one = dmpIncomeRepository.findByCodeMonth(income.getContractCode(), income.getIncomeMonth());
							if (one != null) {
								// 同一合同同一月份不重复保存
								return;
							}
							indexLine[0] = income.getIndex();
							if ("erro".equals(income.getIncomeMonth())) {
								erroMessage.append(income.getIndex()).append(" 行 收入月份错误").append(";\n");
							} else {
								income.setConfirmSettlement(new BigDecimal(income.getConfirmSettlement()).setScale(2, BigDecimal.ROUND_HALF_UP).toString());
								dmpIncomeRepository.save(income);
							}
						}, executorService)
						.exceptionally((t) -> {
							erroMessage.append(indexLine[0]).append(" 行 错误").append(t.getMessage()).append(";\n");
							logger.error("the line " + indexLine[0], t);
							return null;
						})
		).toArray(size -> new CompletableFuture[size]);
		CompletableFuture.allOf(futures).join();
		executorService.shutdownNow();
		stopWatch.stop();
		logger.info("dmp income upload {} line data  use all {}s ", rowNumber - 1, stopWatch.getTotalTimeSeconds());
		if (erroMessage.length() > 0) {
			return ResultModel.ERROR(erroMessage.toString());
		}
		return ResultModel.OK();
	}

	@Override
	public ResultModel contractUploadFile(MultipartFile file, String platform, User loginAccount) {
		Workbook workbook = getWorkbook(file);
		if (workbook == null) {
			return ResultModel.ERROR("获取上传文件错误");
		}
		Sheet sheet = workbook.getSheetAt(0);
		String sheetName = sheet.getSheetName();
		if (!sheetName.equalsIgnoreCase(SHEET_NAMES.get(platform))) {
			return ResultModel.ERROR("请上传对应项目模板处理的数据");
		}

		int rowNumber = checkSheetTitle(workbook, platform);
		if (rowNumber == -1) {
			return ResultModel.ERROR("模板表头错误");
		} else if (rowNumber <= 1) {
			return ResultModel.ERROR("文件为空");
		}

		List<Contract> allContracts = new ArrayList<>();
		Map<String, Long> tradTypeMap = tradeTypeRepsitory.findAll()
				.stream().collect(Collectors.toMap(TradeType::getName, TradeType::getId));
		Map<String, Long> salseMap = salesRepository.findSaleByStatus(0)
				.stream().collect(Collectors.toMap(Sales::getName, Sales::getId));
		Map<Long, String> barrioCityMap = barrioCityRepository.findAll()
				.stream().collect(Collectors.toMap(BarrioCity::getId, BarrioCity::getName));
		Map<String, String> cBodyMap = contractBodyRepository.findAllDis()
				.stream().collect(Collectors.toMap(ContractBody::getName, ContractBody::getCode, (v1, v2) -> v1));


		for (int i = 1; i < rowNumber; i++) {
			Row rowItem = sheet.getRow(i);
			if (rowItem == null) {
				break;
			}
			Contract contract = new Contract();
			contract.setPlatform(platform);
			contract.setProduct(platform);
			contract.setStatus("normal");
			contract.setType("main");
			contract.setCreateAccount(loginAccount.getId());
			contract.setCreateName(loginAccount.getName());

			contract.setDs(DateTime.now().toString("yyyy-MM-dd"));

			String sysErro = null;
			try {
				//填充数据
				fillDataByRow(contract, rowItem);
			} catch (Exception e) {
				logger.error("the line " + i, e);
				sysErro = e.getMessage();
			}

			if (sysErro != null) {
				return erroValueTip(i, sysErro);
			}

			//校验数据有效性
			if ("erro".equals(contract.getStartDate()) || "erro".equals(contract.getEndDate())) {
				return erroValueTip(i, "开始时间或结束时间");
			}
			if (!tradTypeMap.containsKey(contract.getTradeName())) {
				return erroValueTip(i, "行业分类");
			} else {
				contract.setTradeType(Integer.parseInt(tradTypeMap.get(contract.getTradeName()) + ""));
			}
			if (!cBodyMap.containsKey(contract.getMyBodyName())) {
				return erroValueTip(i, "我方签约主体");
			} else {
				contract.setMyBodyCode(cBodyMap.get(contract.getMyBodyName()));
			}

			if (StringUtils.isEmpty(contract.getCustomerBody())) {
				return erroValueTip(i, "客户签约主体");
			}

			if (!barrioCityMap.containsKey(contract.getBarrioId())) {
				return erroValueTip(i, "行政区域");
			} else {
				contract.setBarrioName(barrioCityMap.get(contract.getBarrioId()));
			}

			if (!salseMap.containsKey(contract.getSaleName())) {
				return erroValueTip(i, "签约销售");
			} else {
				contract.setSale(salseMap.get(contract.getSaleName()));
			}

            /*if (StringUtils.isEmpty(contract.getEmail())) {
                return erroValueTip(i, "客户主账号");
            }*/

			if (StringUtils.isEmpty(contract.getContractCode())) {
				return erroValueTip(i, "合同编号");
			} else {
				String[] codeArr = contract.getContractCode().split("-");
				if (codeArr.length == 4) {
					contract.setCodeNum(Integer.parseInt(codeArr[3]));
				}
			}

			if (!CONTRACT_TYPE_NAME.containsKey(contract.getContractType()) && !"续签".equals(contract.getContractType())) {
				return erroValueTip(i, "签约类型");
			} else {
				contract.setContractType(CONTRACT_TYPE_NAME.get(contract.getContractType()));
				if ("续签".equals(contract.getContractType())) {
					contract.setContractType("1");
				}
			}

			if (!BUSINESS_TYPE_NAME.containsKey(contract.getBusinessTypeName())) {
				return erroValueTip(i, "业务类型");
			} else {
				contract.setBusinessType(Integer.parseInt(BUSINESS_TYPE_NAME.get(contract.getBusinessTypeName())));
			}

			if (!SETAGREEMENT_TYPE_NAME.containsKey(contract.getAgreementTypeName())) {
				return erroValueTip(i, "协议类型");
			} else {
				contract.setAgreementType(Integer.parseInt(SETAGREEMENT_TYPE_NAME.get(contract.getAgreementTypeName())));
			}

			allContracts.add(contract);
		}

		if (allContracts.isEmpty()) {
			return ResultModel.ERROR("上传内容为空");
		}

		//allContracts.stream().map(v->v.getRelationCode()).collect(Collectors.toList())

		CompletableFuture.runAsync(() -> {
			StopWatch stopWatch = new StopWatch();
			stopWatch.start();
			ExecutorService executorService = Executors.newFixedThreadPool(35);
			CompletableFuture[] futures = allContracts.stream()
					.map(contract -> CompletableFuture.runAsync(() -> {
								if (!StringUtils.isEmpty(contract.getRelationCode())) {
									List<Long> idList = contractRepository.findIdByCode(contract.getRelationCode());
									if (!idList.isEmpty()) {
										contract.setRelationContract(idList.get(0));
									} else {
										contract.setRelationContract(-1L);
									}
								} else {
									contract.setRelationContract(-1L);
								}
								if (contract.getRelationContract() < 0) {
									contract.setRelationCode(null);
								}

								//删除已经重复的记录
								contractRepository.deleteByCode(contract.getContractCode(), platform);
								contract.setCreateTime(new Date());
								contractRepository.save(contract);

							}, executorService).exceptionally((t) -> null)

					).toArray(size -> new CompletableFuture[size]);

			CompletableFuture.allOf(futures).join();
			executorService.shutdownNow();
			stopWatch.stop();
			logger.info("{} contract upload {} line data  use all {}s ", platform, allContracts.size(), stopWatch.getTotalTimeSeconds());
		});
		return ResultModel.OK();
	}

	private int checkSheetTitle(Workbook workbook, String platform) {
		String sheetTitle = null;
		String code = "\t";
		if ("dmp".equals(platform)) {
			//dmp 合同
			sheetTitle = "我方签约主体\t客户签约主体\t客户简称\t第三方签约主体\t行政区域\t隶属集团\t行业分类\t合同开始日期\t合同结束日期\t签约销售\t客户主账号\t合同编号\t签约类型\t业务类型\t协议类型\t合同金额\t关联合同编号";
		} else if ("dmp_income".equals(platform)) {
			//dmp 收入
			sheetTitle = "合同编号\t收入月份\t结算周期\t系统结算\t按月结算\t税率\t确认收入";
		}
		if (sheetTitle == null) {
			return -1;
		}

		Sheet sheet = workbook.getSheetAt(0);
		Row row = sheet.getRow(0);
		int sheLength = sheetTitle.split("\t").length;
		StringBuffer titleUp = new StringBuffer();
		for (int i = 0; i < sheLength; i++) {
			if (i > 0) titleUp.append(code);
			titleUp.append(row.getCell(i));
		}
		if (!titleUp.toString().equals(sheetTitle)) {
			return -1;
		}

		//总行数
		sheLength = sheet.getLastRowNum() + 1;
		return sheLength;
	}

	private Workbook getWorkbook(MultipartFile excelfile) {
		InputStream stream = null;
		Workbook workbook = null;
		try {
			stream = excelfile.getInputStream();
			workbook = WorkbookFactory.create(stream);
		} catch (Exception e) {
			logger.error("", e);
		} finally {
			if (stream != null) {
				try {
					stream.close();
				} catch (IOException e) {
					logger.error("", e);
				}
			}
		}

		return workbook;
	}

	private DmpIncome saveDmpIncomeItem(Sheet sheet, int index, User user) {
		//logger.info(" line index {}", index);
		Row rowItem = sheet.getRow(index);
		//合同编号	收入月份	结算周期	系统结算	按月结算	税率	确认收入
		DmpIncome income = new DmpIncome();
		income.setIndex(index);
		income.setContractCode(getCellStringValue(rowItem, 0));
		income.setIncomeMonth(getCellDateValue(rowItem, 1, "yyyy-MM"));
		income.setPeriod(getCellStringValue(rowItem, 2));
		income.setSysSettlement(getCellStringValue(rowItem, 3));
		income.setMonthSettlement(getCellStringValue(rowItem, 4));
		income.setTaxRate(getCellStringValue(rowItem, 5));
		income.setConfirmSettlement(getCellStringValue(rowItem, 6));
		// 时间 上传人
		income.setCreateTime(new Date());
		income.setUploadUser(user.getName());
		if (StringUtils.isEmpty(income.getContractCode())) {
			return null;
		}
		Double drate = StringUtils.isEmpty(income.getTaxRate()) ? 0 : Double.parseDouble(income.getTaxRate());
		drate = new BigDecimal(drate * 100).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
		income.setTaxRate(drate + "%");
		if (StringUtils.isEmpty(income.getSysSettlement())) {
			income.setSysSettlement("0");
		}

		return income;

	}

	private String getCellDateValue(Row row, int index) {
		return getCellDateValue(row, index, null);
	}

	private String getCellDateValue(Row row, int index, String fm) {
		Cell cell = row.getCell(index);
		if (cell == null) {
			return "erro";
		}

		String dfmate = cell.getCellStyle().getDataFormatString();
		if (!"yyyy/mm;@".equals(dfmate) && !"m/d/yy".equals(dfmate) && !"yy/m/d".equals(dfmate) && !"mm/dd/yy".equals(dfmate) && !"dd-mmm-yy".equals(dfmate) && !"yyyy/m/d".equals(dfmate) && !"yyyy/m/d;@".equals(dfmate) && !"yyyy\"年\"m\"月\";@".equals(dfmate)) {
			return "erro";
		}

		if (fm == null) {
			fm = "yyyy-MM-dd";
		}

		if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
			// 用于转化为日期格式
			Date d = cell.getDateCellValue();
			DateFormat formater = new SimpleDateFormat(fm);
			return formater.format(d);
		}
		return cell.toString();
	}

	private String getCellStringValue(Row row, int index) {
		Cell cell = row.getCell(index);
		return cell == null ? "" : cell.toString().trim();
	}

	private String getCellStringValue(Row row, int index, String defaulValue) {
		Cell cell = row.getCell(index);
		return cell == null ? defaulValue : cell.toString();
	}

	private void fillDataByRow(Contract contract, Row rowItem) {
		//我方签约主体   客户签约主体	客户简称
		contract.setMyBodyName(getCellStringValue(rowItem, 0));
		contract.setCustomerBody(getCellStringValue(rowItem, 1));
		contract.setCustomerShort(getCellStringValue(rowItem, 2));
		// 第三方签约主体	行政区域	隶属集团	行业分类
		contract.setCustomerThird(getCellStringValue(rowItem, 3));
		contract.setBarrioId(new BigDecimal(getCellStringValue(rowItem, 4, "0")).longValue());
		contract.setBelongGroup(getCellStringValue(rowItem, 5));
		contract.setTradeName(getCellStringValue(rowItem, 6));
		// 合同开始日期 合同结束日期 签约销售	 客户主账号
		contract.setStartDate(getCellDateValue(rowItem, 7));
		contract.setEndDate(getCellDateValue(rowItem, 8));
		contract.setSaleName(getCellStringValue(rowItem, 9));
		contract.setEmail(getCellStringValue(rowItem, 10));
		// 合同编号  签约类型	业务类型	协议类型	合同金额	关联合同编号
		contract.setContractCode(getCellStringValue(rowItem, 11));
		contract.setContractType(getCellStringValue(rowItem, 12));
		contract.setBusinessTypeName(getCellStringValue(rowItem, 13));
		contract.setAgreementTypeName(getCellStringValue(rowItem, 14));
		String money = getCellStringValue(rowItem, 15, "0");
		if (money == null || StringUtils.isEmpty(money.trim())) {
			money = "0";
		}
		contract.setMoney(Double.parseDouble(money));
		contract.setRelationCode(getCellStringValue(rowItem, 16));
	}

	private ResultModel erroValueTip(int line, String title) {
		return ResultModel.ERROR("第" + line + "行,[" + title + "]错误");
	}


}