package de.avatic.taricdb.service;

import java.io.IOException;
import java.util.*;
import java.util.stream.Collectors;

import de.avatic.taricdb.helper.workbook.converters.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;

import de.avatic.taricdb.helper.file.InMemFile;
import de.avatic.taricdb.helper.stats.HeapStat;
import de.avatic.taricdb.helper.workbook.ExcelFileType;
import de.avatic.taricdb.helper.workbook.ExcelRow;
import de.avatic.taricdb.helper.workbook.ExcelTable;
import de.avatic.taricdb.helper.workbook.ExcelTableType;
import de.avatic.taricdb.helper.workbook.wrapper.AppliedMeasureConditionWrapper;
import de.avatic.taricdb.helper.workbook.wrapper.AppliedMeasureWrapper;
import de.avatic.taricdb.helper.workbook.wrapper.DeclarableCodesWrapper;
import de.avatic.taricdb.helper.workbook.wrapper.GeoGroupMembershipWrapper;
import de.avatic.taricdb.helper.workbook.wrapper.ImportWrapper;
import de.avatic.taricdb.helper.workbook.wrapper.MeasureExclusionWrapper;
import de.avatic.taricdb.helper.workbook.wrapper.MeasureFootnoteWrapper;
import de.avatic.taricdb.helper.workbook.wrapper.NomenclatureDescEnWrapper;
import de.avatic.taricdb.helper.workbook.wrapper.NomenclatureDescFrWrapper;
import de.avatic.taricdb.helper.workbook.wrapper.NomenclatureFootnoteWrapper;
import de.avatic.taricdb.helper.zip.Unzipper;
import de.avatic.taricdb.model.AdditionalCode;
import de.avatic.taricdb.model.Certificate;
import de.avatic.taricdb.model.CertificateType;
import de.avatic.taricdb.model.ConditionType;
import de.avatic.taricdb.model.Footnote;
import de.avatic.taricdb.model.Geo;
import de.avatic.taricdb.model.GeoGroup;
import de.avatic.taricdb.model.Import;
import de.avatic.taricdb.model.LegalBase;
import de.avatic.taricdb.model.Measure;
import de.avatic.taricdb.model.MeasureAction;
import de.avatic.taricdb.model.MeasureSeries;
import de.avatic.taricdb.model.MonetaryUnit;
import de.avatic.taricdb.model.Nomenclature;
import de.avatic.taricdb.model.Unit;
import de.avatic.taricdb.repository.AdditionalCodeRepository;
import de.avatic.taricdb.repository.CertificateRepository;
import de.avatic.taricdb.repository.CertificateTypeRepository;
import de.avatic.taricdb.repository.ConditionTypeRepository;
import de.avatic.taricdb.repository.FootnoteRepository;
import de.avatic.taricdb.repository.GeoGroupRepository;
import de.avatic.taricdb.repository.GeoRepository;
import de.avatic.taricdb.repository.ImportRepository;
import de.avatic.taricdb.repository.LegalBaseRepository;
import de.avatic.taricdb.repository.MeasureActionRepository;
import de.avatic.taricdb.repository.MeasureRepository;
import de.avatic.taricdb.repository.MeasureSeriesRepository;
import de.avatic.taricdb.repository.MonetaryUnitRepository;
import de.avatic.taricdb.repository.NomenclatureRepository;
import de.avatic.taricdb.repository.UnitRepository;

@Service
public class SetupService {

	Logger log = LoggerFactory.getLogger(SetupService.class);

	Map<ExcelFileType, InMemFile> files;

	Map<ExcelTableType, ExcelTable> tables;

	int recordTotalCount;

	int recordCurrentCount;

	@Autowired
	SetupStatusService statusService;

	@Autowired
	Unzipper unzipper;

	@Autowired
	FootnoteRepository footnoteRepository;

	@Autowired
	private MeasureRepository measureRepository;

	@Autowired
	private MeasureSeriesRepository measureSeriesRepository;

	@Autowired
	private MeasureActionRepository measureActionRepository;

	@Autowired
	private ReferenceStorageService referenceStorage;

	@Autowired
	private ConditionTypeRepository conditionTypeRepository;

	@Autowired
	private UnitRepository unitRepository;

	@Autowired
	private MonetaryUnitRepository monetaryUnitRepository;

	@Autowired
	private CertificateRepository certificateRepository;

	@Autowired
	private CertificateTypeRepository certificateTypeRepository;

	@Autowired
	private LegalBaseRepository legalBaseRepository;

	@Autowired
	private GeoRepository geoRepository;

	@Autowired
	private GeoGroupRepository geoGroupRepository;

	@Autowired
	private NomenclatureRepository nomenclatureRepository;

	@Autowired
	private AdditionalCodeRepository additionalCodeRepository;

	@Autowired
	private ImportRepository importRepository;

	@Autowired
	private AsyncSetupDutiesService asyncSetupDutiesService;

	@Async
	public void run(byte[] zipFile) throws IOException {

		System.out.println("Execute method in setup service. " + Thread.currentThread().getName());

		tables = new HashMap<>();
		files = new HashMap<>();

		// step 1 - unzip file
		statusService.setTotalRecordCount(0);
		unzipping(zipFile);
		collectRecords();

		// step 2 - process tables
		footnotes();

		measureSeries();
		measureFootnotes();
		measures();
		measureActions();

		monetaryUnit();
		unit();

		certificateType();
		certificate();

		conditionType();

		legalBase();

		geo();
		geoGroupMembership();
		geoGroup();

		nomenclatureFootnote();
		declarableNomenclature();
		nomenclatureFr();
		nomenclatureEn();
		nomenclatureDe();

		additionalCodes();

		measureExclusions();
		measureConditions();

        legalBase2();

		appliedMeasure();

		importCodes();

		log.info("Setup completed");
		statusService.setStatus(statusService.getRecordTotalCount(), "setup finished.", false, "OK");

	}

	private void additionalCodes() {
		log.info("Convert additional code");
		statusService.setStatus(recordCurrentCount, "Convert additional code", true, null);
		Collection<AdditionalCode> m = new AdditionalCodeConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.ADDITIONAL_CODE));
		recordCurrentCount += tables.get(ExcelTableType.ADDITIONAL_CODE).size();
		referenceStorage.addAll(toList(additionalCodeRepository.saveAll(m)), AdditionalCode.class, Integer.class);

	}

	private void measureConditions() {
		log.info("Convert measure condition");
		statusService.setStatus(recordCurrentCount, "Convert measure condition", true, null);
		Collection<AppliedMeasureConditionWrapper> m = new AppliedMeasureConditionConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.APPLIED_MEASURE_CONDITION));
		recordCurrentCount += tables.get(ExcelTableType.APPLIED_MEASURE_CONDITION).size();
		// do not save, just put in storage, because its member of the import aggregate
		// root.
		

		referenceStorage.addAll(toList(m), AppliedMeasureConditionWrapper.class, Integer.class);
	}

	private void measureExclusions() {
		log.info("Convert measure exclusions");
		statusService.setStatus(recordCurrentCount, "Convert measure exclusions", true, null);
		Collection<MeasureExclusionWrapper> m = new MeasureExclusionConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.MEASURE_EXCLUSION));
		recordCurrentCount += tables.get(ExcelTableType.MEASURE_EXCLUSION).size();
		// do not save, just put in storage, because its member of the import aggregate
		// root.

		
		referenceStorage.addAll(toList(m), MeasureExclusionWrapper.class, Integer.class);
	}

	private void importCodes() {
		log.info("Convert duties import");
		statusService.setStatus(recordCurrentCount, "Convert import duties", true, null);

		Collection<Import> m = convertImportCodes(tables.get(ExcelTableType.APPLIED_MEASURES));
		
		log.info("save duties import");
		int totalProcessed = 0;
		

		for (Collection<Import> chunk : toChunks(m, 1000)) {
			referenceStorage.addAll(toList(importRepository.saveAll(chunk)), Import.class, Integer.class);
			totalProcessed += chunk.size();
			statusService.setStatus(statusService.getRecordTotalCount(), "Save import duties ... " + Math.round((float) (totalProcessed * 100) / m.size()) + "%", true, null);
		}

	}

	private Collection<Import> convertImportCodes(ExcelTable table) {

		Collection<ImportWrapper> imports = new ArrayList<>();
		int count = 0;

		for (ExcelRow row : table.getRows()) {
			count++;
			if (row.index() == 0) {
				continue;
			}

			if (count % 10000 == 0) {
				int percentage = Math.round((float) (count * 100) / table.getRows().size());
				log.info("import duties processed: " + percentage + "% - " + count + " of " + table.getRows().size()
						+ " " + HeapStat.getHeapUtilization(), true, null);
				statusService.setStatus(recordCurrentCount, "Convert import duties ... " + percentage + "%",
						true, null);
			}

			String hsCode = row.getAs("Goods code", String.class);
			String origin = row.getAs("Origin code", String.class);
			String addCode = row.getAs("Add code", String.class);

			ImportWrapper im = imports.stream().filter(
					i -> i.getHsCode().equals(hsCode) && i.getOrigin().equals(origin) && i.getAddCode().equals(addCode))
					.findFirst().orElse(null);

			if (im == null) {
				ImportWrapper imw = new ImportWrapper(hsCode, origin, addCode);
				imports.add(imw);

				// do this stuff async
				asyncSetupDutiesService.setupDuties(imw);

			}
		}

		return imports.stream().map(ImportWrapper::getImport).filter(Objects::nonNull).collect(Collectors.toSet());
	}


    private void legalBase2() {
        log.info("Convert legal base from applied measure");
        statusService.setStatus(recordCurrentCount, "Convert legal base (2)", true, null);
        Collection<LegalBase> m = new LegalBase2Converter(referenceStorage)
                .convert(tables.get(ExcelTableType.APPLIED_MEASURES));

        log.info("Convert legal base (found {} additional legal bases)", m.size());

        referenceStorage.addAll(toList(legalBaseRepository.saveAll(m)), LegalBase.class, Integer.class);

    }

	private void appliedMeasure() {
		log.info("Convert applied measures");
		statusService.setStatus(recordCurrentCount, "Convert applied measures", true, null);
		Collection<AppliedMeasureWrapper> m = new AppliedMeasureConverter(referenceStorage, statusService,
				recordCurrentCount).convert(tables.get(ExcelTableType.APPLIED_MEASURES));
		// recordCurrentCount += tables.get(ExcelTableType.APPLIED_MEASURES).size();
		// do not save, just put in storage, because its member of the import aggregate
		// root.
		referenceStorage.addAll(toList(m), AppliedMeasureWrapper.class, Integer.class);
	}

	private void nomenclatureFootnote() {
		log.info("Convert nominclature footnotes");
		statusService.setStatus(recordCurrentCount, "Convert Nomenclature Footnotes", true, null);
		Collection<NomenclatureFootnoteWrapper> m = new NomenclatureFootnoteConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.NOMENCLATURE_FOOTNOTE));
		recordCurrentCount += tables.get(ExcelTableType.NOMENCLATURE_FOOTNOTE).size();
		// do not save, just put in storage, because its member of the nomenclature
		// aggregate root.
		referenceStorage.addAll(toList(m), NomenclatureFootnoteWrapper.class, Integer.class);
	}

	private void declarableNomenclature() {
		log.info("Convert declarable codes");
		statusService.setStatus(recordCurrentCount, "Convert declarable codes", true, null);
		Collection<DeclarableCodesWrapper> m = new DeclarableCodesConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.NOMENCLATURE_DECLAREABLE));
		recordCurrentCount += tables.get(ExcelTableType.NOMENCLATURE_DECLAREABLE).size();
		// do not save, just put in storage, because its member of the nomenclature
		// aggregate root.
		referenceStorage.addAll(toList(m), DeclarableCodesWrapper.class, Integer.class);
	}

	private void nomenclatureEn() {

		log.info("Convert nomenclature EN");
		statusService.setStatus(recordCurrentCount, "Convert Nomenclature EN", true, null);
		Collection<NomenclatureDescEnWrapper> m = new NomenclatureEnConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.NOMENCLATURE_EN));
		// do not save, just put in storage, because its member of the nomenclature
		// aggregate root.
		recordCurrentCount += tables.get(ExcelTableType.NOMENCLATURE_EN).size();
		referenceStorage.addAll(toList(m), NomenclatureDescEnWrapper.class, Integer.class);
	}

	private void nomenclatureFr() {
		log.info("Convert nomenclature FR");
		statusService.setStatus(recordCurrentCount, "Convert Nomenclature FR", true, null);
		Collection<NomenclatureDescFrWrapper> m = new NomenclatureFrConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.NOMENCLATURE_FR));
		// do not save, just put in storage, because its member of the nomenclature
		// aggregate root.
		recordCurrentCount += tables.get(ExcelTableType.NOMENCLATURE_FR).size();
		referenceStorage.addAll(toList(m), NomenclatureDescFrWrapper.class, Integer.class);
	}

	private void nomenclatureDe() {
		log.info("Convert nomenclature DE");
		statusService.setStatus(recordCurrentCount, "Convert Nomenclature DE", true, null);
		Collection<Nomenclature> m = new NomenclatureDeConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.NOMENCLATURE_DE));
		recordCurrentCount += tables.get(ExcelTableType.NOMENCLATURE_DE).size();
		referenceStorage.addAll(toList(nomenclatureRepository.saveAll(m)), Nomenclature.class, Integer.class);
	}

	private void geoGroupMembership() {
		log.info("Convert geo group membership");
		statusService.setStatus(recordCurrentCount, "Convert geo group membership", true, null);
		Collection<GeoGroupMembershipWrapper> m = new GeoGroupMembershipConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.GEO_MEMBERSHIP));
		recordCurrentCount += tables.get(ExcelTableType.GEO_MEMBERSHIP).size();
		// do not save, just put in storage, because its member of the GeoGroup
		// aggregate root.
		referenceStorage.addAll(toList(m), GeoGroupMembershipWrapper.class, Integer.class);
	}

	private void geoGroup() {
		log.info("Convert geo group");
		statusService.setStatus(recordCurrentCount, "Convert geo group", true, null);
		Collection<GeoGroup> m = new GeoGroupConverter(referenceStorage).convert(tables.get(ExcelTableType.GEO_GROUP));
		recordCurrentCount += tables.get(ExcelTableType.GEO_GROUP).size();
		referenceStorage.addAll(toList(geoGroupRepository.saveAll(m)), GeoGroup.class, Integer.class);
	}

	private void geo() {
		log.info("Convert geo");
		statusService.setStatus(recordCurrentCount, "Convert geo", true, null);
		Collection<Geo> m = new GeoConverter(referenceStorage).convert(tables.get(ExcelTableType.GEO_GROUP));
		recordCurrentCount += tables.get(ExcelTableType.GEO_GROUP).size();
		referenceStorage.addAll(toList(geoRepository.saveAll(m)), Geo.class, Integer.class);
	}

	private void legalBase() {
		log.info("Convert legal base");
		statusService.setStatus(recordCurrentCount, "Convert legal base", true, null);
		Collection<LegalBase> m = new LegalBaseConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.LEGAL_BASE));
		recordCurrentCount += tables.get(ExcelTableType.LEGAL_BASE).size();
		referenceStorage.addAll(toList(legalBaseRepository.saveAll(m)), LegalBase.class, Integer.class);

	}

	private void conditionType() {
		log.info("Convert condition type");
		statusService.setStatus(recordCurrentCount, "Convert condition type", true, null);
		Collection<ConditionType> m = new ConditionTypeConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.CONDITION_TYPE));
		recordCurrentCount += tables.get(ExcelTableType.CONDITION_TYPE).size();
		referenceStorage.addAll(toList(conditionTypeRepository.saveAll(m)), ConditionType.class, Integer.class);
	}

	private void certificateType() {
		log.info("Convert certificate type");
		statusService.setStatus(recordCurrentCount, "Convert certificate type", true, null);
		Collection<CertificateType> m = new CertificateTypeConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.CERTIFICATE_TYPE));
		recordCurrentCount += tables.get(ExcelTableType.CERTIFICATE_TYPE).size();
		referenceStorage.addAll(toList(certificateTypeRepository.saveAll(m)), CertificateType.class, Integer.class);
	}

	private void certificate() {
		log.info("Convert certificate");
		statusService.setStatus(recordCurrentCount, "Convert certificate", true, null);
		Collection<Certificate> m = new CertificateConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.CERTIFICATE));
		recordCurrentCount += tables.get(ExcelTableType.CERTIFICATE).size();
		referenceStorage.addAll(toList(certificateRepository.saveAll(m)), Certificate.class, Integer.class);
	}

	private void unit() {
		log.info("Convert unit");
		statusService.setStatus(recordCurrentCount, "Convert unit", true, null);
		Collection<Unit> m = new UnitConverter(referenceStorage).convert(tables.get(ExcelTableType.UNIT));
		recordCurrentCount += tables.get(ExcelTableType.UNIT).size();
		referenceStorage.addAll(toList(unitRepository.saveAll(m)), Unit.class, Integer.class);
	}

	private void monetaryUnit() {
		log.info("Convert monentary unit");
		statusService.setStatus(recordCurrentCount, "Convert monetary unit", true, null);
		Collection<MonetaryUnit> m = new MonetaryUnitConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.MONETARY_UNIT));
		recordCurrentCount += tables.get(ExcelTableType.MONETARY_UNIT).size();
		referenceStorage.addAll(toList(monetaryUnitRepository.saveAll(m)), MonetaryUnit.class, Integer.class);
	}

	private void measureActions() {
		log.info("Convert measure action");
		statusService.setStatus(recordCurrentCount, "Convert measure action", true, null);
		Collection<MeasureAction> m = new MeasureActionConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.MEASURE_ACTION));
		recordCurrentCount += tables.get(ExcelTableType.MEASURE_ACTION).size();
		referenceStorage.addAll(toList(measureActionRepository.saveAll(m)), MeasureAction.class, Integer.class);
	}

	private void measureFootnotes() {
		log.info("Convert measure footnotes");
		statusService.setStatus(recordCurrentCount, "Convert measure footnotes", true, null);
		Collection<MeasureFootnoteWrapper> m = new MeasureFootnoteConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.MEASURE_FOOTNOTES));
		recordCurrentCount += tables.get(ExcelTableType.MEASURE_FOOTNOTES).size();
		// do not save, just put in storage, because its member of the Measure aggregate
		// root.
		referenceStorage.addAll(toList(m), MeasureFootnoteWrapper.class, Integer.class);
	}

	private void measures() {
		log.info("Convert measure");
		statusService.setStatus(recordCurrentCount, "Convert measure", true, null);
		Collection<Measure> m = new MeasureConverter(referenceStorage).convert(tables.get(ExcelTableType.MEASURE));
		recordCurrentCount += tables.get(ExcelTableType.MEASURE).size();
		referenceStorage.addAll(toList(measureRepository.saveAll(m)), Measure.class, Integer.class);
	}

	private void measureSeries() {
		log.info("Convert measure series");
		statusService.setStatus(recordCurrentCount, "Convert measure series", true, null);
		Collection<MeasureSeries> m = new MeasureSeriesConverter(referenceStorage)
				.convert(tables.get(ExcelTableType.MEASURE_SERIES));
		recordCurrentCount += tables.get(ExcelTableType.MEASURE_SERIES).size();
		referenceStorage.addAll(toList(measureSeriesRepository.saveAll(m)), MeasureSeries.class, Integer.class);
	}

	private void footnotes() {
		log.info("Convert footnotes");
		statusService.setStatus(recordCurrentCount, "Convert Footnotes", true, null);
		Collection<Footnote> f = new FootnoteConverter(referenceStorage).convert(tables.get(ExcelTableType.FOOTNOTES));
		recordCurrentCount += tables.get(ExcelTableType.FOOTNOTES).size();
		referenceStorage.addAll(toList(footnoteRepository.saveAll(f)), Footnote.class, Integer.class);
	}

	private List<Object> toList(Iterable<?> iterable) {
		List<Object> list = new ArrayList<>();
		iterable.forEach(list::add);
		return list;
	}

	private void unzipping(byte[] zipFile) throws IOException {
		unzipper.unzip(zipFile).forEach((file) -> files.put(ExcelFileType.fromFileName(file.getName()), file));
	}

	private <T> Collection<Collection<T>> toChunks(Collection<T> data, int chunkSize) {

		int itemsInChunk = 0;
		List<Collection<T>> chunks = new ArrayList<>();
		Collection<T> chunk = new ArrayList<>();

		for (T item : data) {

			chunk.add(item);
			itemsInChunk++;

			if (itemsInChunk == chunkSize) {
				chunks.add(chunk);
				chunk = new ArrayList<>();
				itemsInChunk = 0;
			}
		}

		return chunks;
	}

	private void collectRecords() throws IOException {
		int foundRecords = 0;

		for (ExcelTableType tableType : ExcelTableType.values()) {
			InMemFile file = files.get(tableType.getFileType());

			log.info("Searching sheet: " + tableType.name());
			statusService.setStatus(0, "collecting records (" + foundRecords + ") ... " + tableType.name(), true, null);

			if (file == null) {
				statusService.setStatus(0, "File missing in ZIP: " + tableType.getFileType().getFileName(), false,
						"ERR");
				throw new IOException("File missing in ZIP: " + tableType.getFileType());
			}

			ExcelTable table = new ExcelTable(tableType, file);

			if (table.size() == -1) {
				statusService.setStatus(0,
						"Sheet missing " + tableType + " in file " + tableType.getFileType().getFileName(), false,
						"ERR");
				throw new IOException("Sheet is missing: " + tableType);
			}

			foundRecords += table.size();

			tables.put(tableType, table);
		}

		statusService.setTotalRecordCount(foundRecords);
		statusService.setStatus(0, "record counting finished.", true, null);
	}

}
