package com.oguerreiro.resilient.activities.inputDataImporter.importers;

import java.io.Closeable;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;

public class GlobalPOIFileImporter implements Closeable {
  private static final int MAX_TRY_ROWS = 30;

  private GlobalPOIFileImporterCallback callback;
  private InputStream poiInputStream;
  private String password;

  public GlobalPOIFileImporter(InputStream poiInputStream, String password) {
    this.poiInputStream = poiInputStream;
    this.password = password;
  }

  public GlobalPOIFileImporter(File poiFile, String password) throws FileNotFoundException {
    this(new FileInputStream(poiFile), password);
  }

  public void importPOI(File poiFile, GlobalPOIFileImporterCallback callback)
      throws EncryptedDocumentException, IOException {
    this.callback = callback;
    this.loadPOIFile();
  }

  public void importPOI(GlobalPOIFileImporterCallback callback) throws EncryptedDocumentException, IOException {
    this.callback = callback;
    this.loadPOIFile();
  }

  private void loadPOIFile() throws EncryptedDocumentException, IOException {
    this.loadWorkbook();
  }

  private void loadWorkbook() throws EncryptedDocumentException, IOException {

    try (Workbook workbook = WorkbookFactory.create(this.poiInputStream, this.password)) {
      //First sheet have the year value
      Cell yearCell = workbook.getSheetAt(0).getRow(5).getCell(7);
      Integer year = this.getCellValueDouble(yearCell).intValue();
      this.callback.validateCallBack(year);

      //Loop all sheets			
      for (Sheet sheet : workbook) {
        //Ignore the intro sheet. No data there for me
        if (sheet.getSheetName().toUpperCase().equals("INTRO"))
          continue;

        this.loadSheet(sheet);
      }
    }
  }

  private void loadSheet(Sheet sheet) {
    try {
      this.printDataHeader(sheet.getSheetName());
    } catch (Throwable t) {
      //Ignore. Don't stop the import, because of logging errors
      t.printStackTrace();
    }

    //Loop all rows
    int maxTry = 0;
    int lastRowNumber = 0;
    for (Row row : sheet) {
      if ((row.getRowNum() - lastRowNumber) > MAX_TRY_ROWS || maxTry == MAX_TRY_ROWS) {
        //Stop reading. No more data rows
        break;
      }
      lastRowNumber = row.getRowNum();

      if (this.isRowData(row)) {
        maxTry = 0;
        if (this.hasData(row)) {
          this.loadRow(row);
        }
      } else {
        maxTry++;
      }
    }
  }

  private void loadRow(Row row) {
    String varCode = this.getCellValueString(row.getCell(1)); // B
    String varClass = this.removeLineBreaks(this.getCellValueString(row.getCell(3))); // D

    if (varClass == null || varClass.length() == 0) {
      //Check if this is a merged CELL. Get the value of the merged range
      varClass = this.getMergedCellValue(row.getCell(3));
    }
    String varClassCode = this.processClassCode(varClass);
    String varClassDescription = this.processClassDescription(varClass);

    String varUnit = this.getCellValueString(row.getCell(6)); // G
    String varUnitCode = this.processUnit(varUnit);

    String varOrganizationCode = this.getCellValueString(row.getCell(2)); // C

    String varValue = this.getCellValueString(row.getCell(7));  // H

    this.callback.callback(row.getSheet().getSheetName(), row.getRowNum(), varCode, varClassCode, varClassDescription,
        varUnit, varUnitCode, varValue, varOrganizationCode);
    this.printData(varCode, varClassCode, varUnit, varUnitCode, varValue, varOrganizationCode);
  }

  private String processClassCode(String varClass) {
    if (varClass == null) {
      return varClass;
    }

    String code = null;
    if (varClass.contains("[") && varClass.contains("]")) {
      //Extract code between [..]
      varClass = varClass.trim();
      String lastChar = varClass.substring(varClass.length() - 1, varClass.length());
      if (!lastChar.equals("]")) {
        //ERROR
      } else {
        int startCodeIndex = varClass.lastIndexOf("[");
        code = varClass.substring(startCodeIndex + 1, varClass.length() - 1);
      }
    }

    return code;
  }

  private String processClassDescription(String varClass) {
    if (varClass == null) {
      return varClass;
    }

    String description = varClass;
    if (varClass.contains("[")) {
      //Extract code between [..]
      int startCodeIndex = varClass.lastIndexOf("[");
      description = varClass.substring(0, startCodeIndex + 1);
    }

    return description;
  }

  private String processUnit(String varUnit) {
    if (varUnit == null) {
      return varUnit;
    }

    String code = null;
    code = StringUtils.stripAccents(varUnit.trim());	//Uppercase && Trim && Strip accents

    String finalCode = "";
    for (char ch : code.toCharArray()) {
      if ((ch > 'a' && ch < 'z') || (ch > 'A' && ch < 'Z') || (ch > '0' && ch < '9') || ch == '%' || ch == '€') {
        finalCode += ch;
      } else {
        finalCode += '_';
      }
    }

    return finalCode;
  }

  private void printDataHeader(String sheetName) {
    System.out.printf(
        "---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------%n");
    System.out.printf("-- %s --%n", sheetName);
    System.out.printf(
        "---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------%n");
    System.out.printf("| %-10s | %-20s | %-25s | %-25s | %10s | %-35s |", "CODE", "CLASS CODE", "UNIT", "UNIT CODE",
        "VALUE", "OWNER");
    System.out.printf(
        "---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------%n");
  }

  private void printData(String varCode, String varClass, String varUnit, String varUnitCode, String varValue,
      String varOrganizationCode) {
    if (varClass == null)
      varClass = "";
    System.out.printf("| %-10s | %-20s | %-25s | %-25s | %10s | %-35s |", varCode, varClass, varUnit, varUnitCode,
        varValue, varOrganizationCode);
  }

  private boolean isRowData(Row row) {
    String varCode = this.getCellValueString(row.getCell(1)); //B

    if (varCode != null && varCode.length() > 0) {
      return true;
    }

    return false;
  }

  private boolean hasData(Row row) {
    String varCode = this.getCellValueString(row.getCell(1));               // B
    String varOrganizationCode = this.getCellValueString(row.getCell(2));   // C
    String varClass = this.getCellValueString(row.getCell(3));   // D
    String varUnit = this.getCellValueString(row.getCell(6));     // G
    String varValue = this.getCellValueString(row.getCell(7));     // H

    //Check if it has data to load
    if (hasValue(varCode) && hasValue(varOrganizationCode) && hasValue(varValue) && hasValue(varUnit)) {
      return true;
    }

    return false;
  }

  private boolean hasValue(String value) {
    return (value != null) && (value.length() > 0);
  }

  private String removeLineBreaks(String value) {
    if (value == null)
      return null;

    return value.replace("\n", "").replace("\r", "");
  }

  private String getCellValueString(Cell cell) {
    String value;
    if (cell == null)
      return null;

    try {
      value = cell.getStringCellValue().trim();
    } catch (Exception e) {
      //Try numeric
      double valueDouble = cell.getNumericCellValue();
      value = String.valueOf(valueDouble);
    }

    return value;
  }

  private Double getCellValueDouble(Cell cell) {
    try {
      return cell.getNumericCellValue();
    } catch (Throwable t) {
      return null;
    }
  }

  public String getMergedCellValue(Cell cell) {
    CellRangeAddress range = getMergedRegionForCell(cell);
    if (range != null) {
      Sheet s = cell.getRow().getSheet();
      Cell valueCell = s.getRow(range.getFirstRow()).getCell(range.getFirstColumn());
      return this.getCellValueString(valueCell);
    }

    return null;
  }

  public CellRangeAddress getMergedRegionForCell(Cell c) {
    Sheet s = c.getRow().getSheet();
    for (CellRangeAddress mergedRegion : s.getMergedRegions()) {
      if (mergedRegion.isInRange(c.getRowIndex(), c.getColumnIndex())) {
        // This region contains the cell in question
        return mergedRegion;
      }
    }
    // Not in any
    return null;
  }

  @Override
  public void close() throws IOException {
    // TODO Auto-generated method stub

  }

}
