package com.theorchard.javaowsassetbulkscript.logic;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FilenameUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class LabelExcelLoader {
	
	public static Map<String, String> loadLabelMapping(File excelFile) throws Exception {
		Map<String, String> labelMapping = new HashMap<>();
		Workbook wb = WorkbookFactory.create(excelFile);
		
		Sheet sheet = wb.getSheetAt(0);
		
		for (int i = sheet.getFirstRowNum() +1; i < sheet.getLastRowNum() + 1; ++i) {
			Cell ftCell = sheet.getRow(i).getCell(0);
			Cell orcdCell = sheet.getRow(i).getCell(1);
			
			Double ftVal = ftCell.getNumericCellValue();
			Double orcdVal = orcdCell.getNumericCellValue();

			labelMapping.put(""+Math.round(ftVal), ""+Math.round(orcdVal));
		}
		
		return labelMapping;
	}

    public static List<DeliveredProduct> getExpectedProductList(File labelExcel) throws Exception {

		List<DeliveredProduct> products = new ArrayList<>();

		Workbook wb = WorkbookFactory.create(new FileInputStream(labelExcel));

		products.addAll(findAudioProducts(wb.getSheet("Main"), FilenameUtils.getBaseName(labelExcel.getName())));
		products.addAll(findAudioProducts(wb.getSheet("Classical"), FilenameUtils.getBaseName(labelExcel.getName())));

		return products;
    }

	private static List<DeliveredProduct> findAudioProducts(Sheet sheet, String labelSourceFolder) throws Exception {
		List<DeliveredProduct> products = new ArrayList<>();

		Map<String, DeliveredProduct> productMap = new HashMap<>();
		for (int i = sheet.getFirstRowNum() +1; i < sheet.getLastRowNum()+1; ++i) {

			Cell folderCell = sheet.getRow(i).getCell(26);
			Cell fileNameCell = sheet.getRow(i).getCell(29);
			Cell upcCell = sheet.getRow(i).getCell(25);
			Cell isrcCell = sheet.getRow(i).getCell(44);
			Cell setCell = sheet.getRow(i).getCell(30);
			Cell posCell = sheet.getRow(i).getCell(31);
			Cell md5Cell = sheet.getRow(i).getCell(69);
			Cell productCodeCell = sheet.getRow(i).getCell(27);

			String folder = folderCell.getStringCellValue();
			String asset = fileNameCell.getStringCellValue();
            String isrc = isrcCell.getStringCellValue();
            String md5 = md5Cell.getStringCellValue();
            int pos = Integer.parseInt(posCell.getStringCellValue());
            int set = Integer.parseInt(setCell.getStringCellValue());
            String productCode = productCodeCell.getStringCellValue().trim();

			DeliveredProduct product = null;
            if (productMap.containsKey(folder)) {
                product = productMap.get(folder);
            } else {
				if (asset.endsWith(".flac") || asset.endsWith(".wav") || asset.endsWith(".mp3") || asset.endsWith(".mov") || asset.endsWith(".mp4")) {

					String upc = upcCell.getStringCellValue();

					String labelId = labelSourceFolder.substring(0, labelSourceFolder.indexOf("_"));

					product = new DeliveredProduct(folder, labelId, upc);

					product.setCoverAsset(labelSourceFolder + "/" + folder + "/" +folder + ".jpg");

					product.setBookletAsset(labelSourceFolder + "/" + folder + "/" + folder + ".pdf");
					
					product.setProductCode(productCode);

					productMap.put(folder, product);
				}
            }
            if (asset.endsWith(".flac") || asset.endsWith(".wav") || asset.endsWith(".mp3") || asset.endsWith(".mov") || asset.endsWith(".mp4"))
            {
                product.addTrack(set, pos, isrc, labelSourceFolder + "/" + folder + "/" + asset, md5);
            }
		}

		products.addAll(productMap.values());

		return products;
	}
}