/**
 * Modified by
 * @author  Data Virtuality GmbH
 * @date    $Date$
 */
package org.teiid.translator.excel;

import com.github.pjfanning.xlsx.StreamingReader;
import org.apache.poi.ss.usermodel.*;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Iterator;
import java.util.Locale;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertTrue;

public class StreamingWorkbookTest {
  @BeforeAll
  public static void init() {
    Locale.setDefault(Locale.ENGLISH);
  }

  @Test
  public void testIterateSheets() throws Exception {
    try(
        InputStream is = new FileInputStream(new File("src/test/resources/sheets.xlsx"));
        Workbook workbook = StreamingReader.builder().open(is);
    ) {

      assertEquals(2, workbook.getNumberOfSheets());

      Sheet alpha = workbook.getSheetAt(0);
      Sheet zulu = workbook.getSheetAt(1);
      assertEquals("SheetAlpha", alpha.getSheetName());
      assertEquals("SheetZulu", zulu.getSheetName());

      Row rowA = alpha.rowIterator().next();
      Row rowZ = zulu.rowIterator().next();

      assertEquals("stuff", rowA.getCell(0).getStringCellValue());
      assertEquals("yeah", rowZ.getCell(0).getStringCellValue());
    }
  }

  @Test
  public void testHiddenCells() throws Exception {
    try(
        InputStream is = new FileInputStream(new File("src/test/resources/hidden.xlsx"));
        Workbook workbook = StreamingReader.builder().open(is)
    ) {
      assertEquals(3, workbook.getNumberOfSheets());
      Sheet sheet = workbook.getSheetAt(0);

      assertFalse(sheet.isColumnHidden(0), "Column 0 should not be hidden");
      assertTrue(sheet.isColumnHidden(1), "Column 1 should be hidden");
      assertFalse(sheet.isColumnHidden(2), "Column 2 should not be hidden");

      assertFalse(sheet.rowIterator().next().getZeroHeight(), "Row 0 should not be hidden");
      assertTrue(sheet.rowIterator().next().getZeroHeight(), "Row 1 should be hidden");
      assertFalse(sheet.rowIterator().next().getZeroHeight(), "Row 2 should not be hidden");
    }
  }

  @Test
  public void testHiddenSheets() throws Exception {
    try(
        InputStream is = new FileInputStream(new File("src/test/resources/hidden.xlsx"));
        Workbook workbook = StreamingReader.builder().open(is)
    ) {
      assertEquals(3, workbook.getNumberOfSheets());
      assertFalse(workbook.isSheetHidden(0));

      assertTrue(workbook.isSheetHidden(1));
      assertFalse(workbook.isSheetVeryHidden(1));

      assertFalse(workbook.isSheetHidden(2));
      assertTrue(workbook.isSheetVeryHidden(2));
    }
  }

  @Test
  public void testFormulaCells() throws Exception {
    try(
        InputStream is = new FileInputStream(new File("src/test/resources/formula_cell.xlsx"));
        Workbook workbook = StreamingReader.builder().open(is)
    ) {
      assertEquals(1, workbook.getNumberOfSheets());
      Sheet sheet = workbook.getSheetAt(0);

      Iterator<Row> rowIterator = sheet.rowIterator();
      rowIterator.next();
      rowIterator.next();
      Row row3 = rowIterator.next();
      Cell A3 = row3.getCell(0);

      assertEquals(CellType.FORMULA, A3.getCellType(), "Cell A3 should be of type formula");
      assertEquals(CellType.NUMERIC, A3.getCachedFormulaResultType(), "Cell A3's value should be of type numeric");
      assertEquals("SUM(A1:A2)", A3.getCellFormula(), "Wrong formula");
    }
  }
}
