content: /*
   *  Licensed to the Apache Software Foundation (ASF) under one
   *  or more contributor license agreements.  See the NOTICE file
   *  distributed with this work for additional information
   *  regarding copyright ownership.  The ASF licenses this file
   *  to you under the Apache License, Version 2.0 (the
   *  "License"); you may not use this file except in compliance
   *  with the License.  You may obtain a copy of the License at
   *
   *        http://www.apache.org/licenses/LICENSE-2.0
   *
   *  Unless required by applicable law or agreed to in writing,
   *  software distributed under the License is distributed on an
   *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
   *  KIND, either express or implied.  See the License for the
   *  specific language governing permissions and limitations
   *  under the License.
   */
  package org.apache.causeway.extensions.excel.applib.util;
  
  import java.io.IOException;
  import java.util.Arrays;
  import java.util.List;
  
  import org.apache.poi.ss.usermodel.Cell;
  import org.apache.poi.ss.usermodel.CellType;
  import org.apache.poi.ss.usermodel.Row;
  import org.apache.poi.xssf.usermodel.XSSFSheet;
  import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  import org.junit.jupiter.api.BeforeEach;
  import org.junit.jupiter.api.Test;
  
  import static org.assertj.core.api.Assertions.assertThat;
  import static org.junit.jupiter.api.Assertions.assertThrows;
  import static org.junit.jupiter.api.Assertions.assertTrue;
  
  class SheetPivoterTest {
  
      XSSFWorkbook workbook;
      XSSFSheet sourceSheet;
      XSSFSheet targetSheet;
      SheetPivoter p;
  
      @BeforeEach
      public void setup() {
          workbook = new XSSFWorkbook();
          sourceSheet = workbook.createSheet();
          targetSheet = workbook.createSheet();
          p = new SheetPivoter();
      }
  
      @Test
      void poi_method_lastCellNumber_yields_1_higher_than_expected(){
  
          // when
          Cell c = targetSheet.createRow(0).createCell(0);
          c.setBlank();
  
          // then
          // ************** NOTE ***************************************************************************
          assertThat(targetSheet.getRow(0).getLastCellNum()).isEqualTo((short)1); // NOTE !!!!!!!
          assertThat(targetSheet.getRow(0).getCell(1)).isEqualTo(null);
          // ************** NOTE ***************************************************************************
  
          assertThat(c).isNotNull();
          assertThat(targetSheet.getLastRowNum()).isEqualTo(0); // Expected
  
          // and when
          targetSheet.getRow(0).createCell(0).setCellValue("a");
  
          // then still
          assertThat(targetSheet.getRow(0).getLastCellNum()).isEqualTo((short)1); // NOTE !!!!!!!
          assertThat(c.getCellType()).isEqualTo(CellType.STRING);
  
          // and when
          targetSheet.getRow(0).createCell(1).setCellValue("b");
          assertThat(targetSheet.getRow(0).getLastCellNum()).isEqualTo((short)2); // NOTE !!!!!!!
  
      }
  
      @Test
      void empty_value_rows_works(){
  
          // given
          List<String> annotations = Arrays.asList("row", "value", "column");
          List<Integer> orderNumbers = Arrays.asList(0, 0, 0);
          List<String> typeList = Arrays.asList(null, "SUM", null);
          List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3");
          sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList);
  
          // when
          p.pivot(sourceSheet, targetSheet);
  
          // then
          assertThat(p.valueRowOffsetY).isEqualTo(1);
          assertThat(p.columnLabelOffsetX).isEqualTo(1);
          assertThat(p.decoRowOffsetX).isEqualTo(1);
          assertThat(p.valuesStartAtRownumber).isEqualTo(2); // even though there are no values
  
          assertThat(targetSheet.getLastRowNum()).isEqualTo(2);
          assertThat(targetSheet.getRow(1).getLastCellNum()).isEqualTo((short)2);
          testRow(0, "fn3", null);
          testRow(1, "fn1", null);
          // last row is for summing
          assertThat(targetSheet.getRow(2).getCell(0).getCellType()).isEqualTo(CellType.BLANK);
          assertThat(targetSheet.getRow(2).getCell(1).getCellType()).isEqualTo(CellType.FORMULA);
  
          org.junit.jupiter.api.Assertions.assertTrue(
                  "SUM(B3:B2)".equals(targetSheet.getRow(2).getCell(1).getCellFormula())
                  || "SUM(B2:B3)".equals(targetSheet.getRow(2).getCell(1).getCellFormula()));
  
      }
  
      @Test
      void setOffsets_works(){
          // given
          List<String> annotations = Arrays.asList("row", "value", "value", "column", "deco");
          List<Integer> orderNumbers = Arrays.asList(0, 0, 1, 1, 1);
          List<String> typeList = Arrays.asList(null, "SUM", "SUM", null, null);
          List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn2a", "fn3", "fn4");
          sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList);
  
          // when
          p.pivot(sourceSheet, targetSheet);
  
          // then
          assertThat(p.valueRowOffsetY).isEqualTo(1);
          assertThat(p.columnLabelOffsetX).isEqualTo(2);
          assertThat(p.decoRowOffsetX).isEqualTo(1);
          assertThat(p.valuesStartAtRownumber).isEqualTo(2);
  
          // given
          annotations = Arrays.asList("row", "value", "value", "column", "deco", "column");
          orderNumbers = Arrays.asList(0, 0, 1, 1, 1, 2);
          typeList = Arrays.asList(null, "SUM", "SUM", null, null, null);
          fieldNameList = Arrays.asList("fn1", "fn2", "fn2a", "fn3", "fn4", "fn3a");
          sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList);
  
          // when
          p.pivot(sourceSheet, targetSheet);
  
          // then
          assertThat(p.valueRowOffsetY).isEqualTo(2);
          assertThat(p.columnLabelOffsetX).isEqualTo(2);
          assertThat(p.decoRowOffsetX).isEqualTo(1);
          assertThat(p.valuesStartAtRownumber).isEqualTo(3);
  
      }
  
      @Test
      void validateSourceData_works(){
  
          // given
          List<String> annotations = Arrays.asList("row", "value", "value", "column");
          List<Integer> orderNumbers = Arrays.asList(0, 0, 1, 1);
          List<String> typeList = Arrays.asList(null, "SUM", "SUM", null);
          List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn2a", "fn3");
          List<?> v1 = Arrays.asList("r1", "a", 1, "c1");
          sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1);
  
          // then
          assertThrows(IllegalArgumentException.class, ()->{
  
              // when
              p.pivot(sourceSheet, targetSheet);
  
          }, "Values other than CELL_TYPE_NUMERIC found");
  
      }
  
      @Test
      void replaceEmptyRowAndColumns_when_validating_sourcedata_works(){
  
          // given
          List<String> annotations = Arrays.asList("row", "value", "column");
          List<Integer> orderNumbers = Arrays.asList(0, 0, 1);
          List<String> typeList = Arrays.asList(null, "SUM", null);
          List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3");
          List<?> v1 = Arrays.asList(null, 1, null);
          sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1);
  
          // when
          p.pivot(sourceSheet, targetSheet);
  
          // then
          assertThat(sourceSheet.getRow(4).getCell(0).getStringCellValue()).isEqualTo("(empty)");
          assertThat(sourceSheet.getRow(4).getCell(2).getStringCellValue()).isEqualTo("(empty)");
      }
  
      @Test
      void headerRow_Fill_works() {
          // given case for 3 col, 2 val
          List<String> annotations = Arrays.asList("row", "column", "column", "column", "value", "value", "deco", "deco");
          List<Integer> orderNumbers = Arrays.asList(0, 1, 2, 3, 1, 2, 1, 2);
          List<String> typeList = Arrays.asList(null, null, null, null, "SUM", "SUM", null, null);
          List<String> fieldNameList = Arrays.asList("fn-r1", "fn-c", "fn-c1", "fn-c2", "fn-v1", "fn-v2", "fn-d1", "fn-d2");
          List<?> v1 = Arrays.asList("r1", "a", "c1-1", "c2-1", null, null, "r1d1", null);
          List<?> v2 = Arrays.asList("r2", "a", "c1-2", "c2-1", null, null, null, "r2d2");
          List<?> v3 = Arrays.asList("r3", "b", "c1-3", "c2-2", null, null, "r3d1", "r3d2");
          sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2, v3);
  
          // when
          p.pivot(sourceSheet, targetSheet);
  
          // then
          assertThat(targetSheet.getLastRowNum()).isEqualTo(7);
          testRow(0, null, null, "fn-c", "a", null, null, null, null, null, null, null, null, null, null, null, "b"); //etc
          testRow(1, null, null, "fn-c1", "c1-1", null, null, null, "c1-2", null, null, null, "c1-3", null, null, null, "c1-1"); //etc
          testRow(2, null, null, "fn-c2", "c2-1", null, "c2-2", null, "c2-1", null, "c2-2", null, "c2-1", null, "c2-2", null, "c2-1"); //etc
          testRow(3, "fn-r1", "fn-d1", "fn-d2", "fn-v1 (sum)", "fn-v2 (sum)", "fn-v1 (sum)", "fn-v2 (sum)", "fn-v1 (sum)", "fn-v2 (sum)"); //etc
          testRow(4, "r1", "r1d1", null); //etc
          testRow(5, "r2", null, "r2d2");
          testRow(6, "r3", "r3d1", "r3d2");
          // last row is for summing
  
      }
  
      @Test
      void testPivoting_works() throws Exception {
  
          // given
          List<String> annotations = Arrays.asList("row", "value", "column", "deco", "deco");
          List<Integer> orderNumbers = Arrays.asList(0, 1, 1, 2, 1);
          List<String> typeList = Arrays.asList(null, "SUM", null, null, null);
          List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3", "fn4", "fn5");
          List<?> v1 = Arrays.asList("l1", 1, "c1", "d1-f-l1", "d2-f-l1");
          List<?> v2 = Arrays.asList("l2", 2, "c2", "deco for l2", null);
          List<?> v3 = Arrays.asList("l1", 3, "c1", "other deco not used", null);
          List<?> v4 = Arrays.asList("l3", 33, "c1", null, null);
  
          sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2, v3, v4);
  
          // when
          p.pivot(sourceSheet, targetSheet);
  
          // then
          assertThat(targetSheet.getLastRowNum()).isEqualTo(5);
          testRow(0, null, null, "fn3", "c1", "c2");
          testRow(1, "fn1", "fn5", "fn4", "fn2 (sum)", "fn2 (sum)");
          testRow(2, "l1", "d2-f-l1", "d1-f-l1", 4, null);
          assertThat(targetSheet.getRow(2).getCell(5).getCellFormula()).isEqualTo("SUM(D3:E3)");
          testRow(3, "l2", null, "deco for l2", null, 2);
          assertThat(targetSheet.getRow(3).getCell(5).getCellFormula()).isEqualTo("SUM(D4:E4)");
          testRow(4, "l3", null, null, 33, null);
          assertThat(targetSheet.getRow(4).getCell(5).getCellFormula()).isEqualTo("SUM(D5:E5)");
  
          assertThat(targetSheet.getRow(5).getCell(3).getCellFormula()).isEqualTo("SUM(D3:D5)");
          assertThat(targetSheet.getRow(5).getCell(4).getCellFormula()).isEqualTo("SUM(E3:E5)");
          assertThat(targetSheet.getRow(5).getCell(5).getCellFormula()).isEqualTo("SUM(F3:F5)");
  
      }
  
      @Test
      void testPivoting_WithTwoValues_works() throws Exception {
  
          // given
          List<String> annotations = Arrays.asList("row", "value", "column", "deco", "deco", "value");
          List<Integer> orderNumbers = Arrays.asList(0, 1, 1, 2, 1, 2);
          List<String> typeList = Arrays.asList(null, "SUM", null, null, null, "COUNT");
          List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3", "fn4", "fn5", "fn2a");
          List<?> v1 = Arrays.asList("l1", 1, "c1", "d1-f-l1", "d2-f-l1", 2);
          List<?> v2 = Arrays.asList("l2", 2, "c2", "deco for l2", null, null);
          List<?> v3 = Arrays.asList("l1", 3, "c1", "other deco not used", null, 2);
          List<?> v4 = Arrays.asList("l3", 33, "c1", null, null, 2);
  
          sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2, v3, v4);
  
          // when
          p.pivot(sourceSheet, targetSheet);
  
          // then
          assertThat(targetSheet.getLastRowNum()).isEqualTo(5);
          testRow(0, null, null, "fn3", "c1", null, "c2", null);
          testRow(1, "fn1", "fn5", "fn4", "fn2 (sum)", "fn2a (count)", "fn2 (sum)", "fn2a (count)");
          testRow(2, "l1", "d2-f-l1", "d1-f-l1", 4, 2, null, null);
          testRow(3, "l2", null, "deco for l2", null, null, 2, 1);
          testRow(4, "l3", null, null, 33, 1, null, null);
  
          assertThat(targetSheet.getRow(5).getCell(3).getCellFormula()).isEqualTo("SUM(D3:D5)");
          assertThat(targetSheet.getRow(5).getCell(4).getCellFormula()).isEqualTo("SUM(E3:E5)");
          assertThat(targetSheet.getRow(5).getCell(5).getCellFormula()).isEqualTo("SUM(F3:F5)");
          assertThat(targetSheet.getRow(5).getCell(6).getCellFormula()).isEqualTo("SUM(G3:G5)");
  
      }
  
      @Test
      void testPivoting_decovalues() throws Exception {
  
          // given
          List<String> annotations = Arrays.asList("deco", "row", "value", "column");
          List<Integer> orderNumbers = Arrays.asList(1, 0, 1, 1);
          List<String> typeList = Arrays.asList(null, null, "SUM", null);
          List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3", "fn4");
          List<?> v1 = Arrays.asList("deco used", "l1", 1, "c1");
          List<?> v2 = Arrays.asList(null, "l1", 1, "c2");
          List<?> v3 = Arrays.asList("deco not used", "l1", 1, "c3");
  
          sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2, v3);
  
          // when
          p.pivot(sourceSheet, targetSheet);
  
          // then
          assertThat(targetSheet.getLastRowNum()).isEqualTo(3);
          testRow(0, null, "fn4", "c1", "c2", "c3");
          testRow(1, "fn2", "fn1", "fn3 (sum)", "fn3 (sum)", "fn3 (sum)");
          testRow(2, "l1", "deco used", 1, 1, 1);
      }
  
      @Test
      void testPivoting_NotSupportedValueType_Boolean() throws Exception {
  
          // given
          List<String> annotations = Arrays.asList("row", "value", "column");
          List<Integer> orderNumbers = Arrays.asList(0, 1, 1);
          List<String> typeList = Arrays.asList(null, "SUM", null);
          List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3");
          List<?> v1 = Arrays.asList("l1", true, "c1");
          List<?> v2 = Arrays.asList("l2", false, "c2");
          List<?> v3 = Arrays.asList("l1", true, "c1");
  
          sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2, v3);
  
          // when
          p.pivot(sourceSheet, targetSheet);
  
          // then
          assertThat(targetSheet.getLastRowNum()).isEqualTo(4);
          testRow(0, "fn3", "c1", "c2");
          testRow(1, "fn1", "fn2 (sum)", "fn2 (sum)");
          testRow(2, "l1", null, null);
          testRow(3, "l2", null, null);
  
      }
  
      @Test
      void testPivoting_Numeric_As_Rowlabel() throws Exception {
  
          // given
          List<String> annotations = Arrays.asList("row", "value", "column");
          List<Integer> orderNumbers = Arrays.asList(0, 1, 1);
          List<String> typeList = Arrays.asList(null, "SUM", null);
          List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3");
          List<?> v1 = Arrays.asList(1, 1, "c1");
          List<?> v2 = Arrays.asList(2, 1, "c2");
          List<?> v3 = Arrays.asList(1, 1, "c1");
  
          sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2, v3);
  
          // when
          p.pivot(sourceSheet, targetSheet);
  
          // then
          assertThat(targetSheet.getLastRowNum()).isEqualTo(4);
          testRow(0, "fn3", "c1", "c2");
          testRow(1, "fn1", "fn2 (sum)", "fn2 (sum)");
          testRow(2, 1, 2, null);
          testRow(3, 2, null, 1);
      }
  
      @Test
      void emptyRowValuesAreSupported() {
  
          // given
          List<String> annotations = Arrays.asList("row", "value", "column");
          List<Integer> orderNumbers = Arrays.asList(0, 1, 1);
          List<String> typeList = Arrays.asList(null, "SUM", null);
          List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3");
          List<?> v1 = Arrays.asList(null, 1, "c1"); // null
          List<?> v2 = Arrays.asList("", 1, "c1"); // empty string
  
          sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2);
  
          // when
          p.pivot(sourceSheet, targetSheet);
  
          // then
          assertThat(targetSheet.getLastRowNum()).isEqualTo(3);
          testRow(0, "fn3", "c1");
          testRow(1, "fn1", "fn2 (sum)");
          testRow(2, "(empty)", 2);
  
      }
  
      @Test
      void emptyColumnValuesAreSupported() {
  
          // given
          List<String> annotations = Arrays.asList("row", "value", "column");
          List<Integer> orderNumbers = Arrays.asList(0, 1, 1);
          List<String> typeList = Arrays.asList(null, "SUM", null);
          List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3");
          List<?> v1 = Arrays.asList("l1", 1, null); // null
          List<?> v2 = Arrays.asList("l1", 1, ""); // empty string
  
          sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2);
  
          // when
          p.pivot(sourceSheet, targetSheet);
  
          // then
          assertThat(targetSheet.getLastRowNum()).isEqualTo(3);
          testRow(0, "fn3", "(empty)");
          testRow(1, "fn1", "fn2 (sum)");
          testRow(2, "l1", 2);
  
      }
  
      @Test
      void getDistinctValuesInSourceSheetColumnTest() throws IOException {
  
          // given (only headers in source)
          try(XSSFWorkbook workbook = new XSSFWorkbook()) {
              XSSFSheet sheet = workbook.createSheet();
              sheet.createRow(0).createCell(0).setCellValue("row");
              sheet.createRow(1).createCell(0).setCellValue(1);
              sheet.createRow(2).createCell(0).setCellValue("fieldname");
  
              // when
              List<Cell> l = SheetPivoter.getDistinctValuesInSourceSheetColumn(sheet, 0);
  
              // then
              assertThat(l.size()).isEqualTo(0);
  
              // and when (values are added)
              sheet.createRow(3).createCell(0).setCellValue("a");
              sheet.createRow(4).createCell(0).setCellValue("a");
              sheet.createRow(5).createCell(0).setCellValue("b");
              sheet.createRow(6).createCell(0).setCellValue(1);
              sheet.createRow(7).createCell(0).setCellValue(""); // empty string differs from empty cell
              sheet.createRow(8).createCell(0);  // empty string differs from empty cell
  
              l = SheetPivoter.getDistinctValuesInSourceSheetColumn(sheet, 0);
  
              // then
              assertThat(l.size()).isEqualTo(5);
          }
      }
  
      void testRow(final int rowNumber, final Object... vals){
  
          int i = 0;
          for (Object val : vals){
              testCell(rowNumber, i++, val);
          }
  
      }
  
      private void testCell(final int x, final int y, final Object expectedValue){
  
          if (expectedValue==null){
              Row r = targetSheet.getRow(x);
              if (r == null){
                  // skip
              } else {
                  Cell c = r.getCell(y);
                  if (c == null) {
                      // OK skip
                  } else {
                      if (c.getCellType() == CellType.NUMERIC) {
                          assertTrue(Double.isFinite(c.getNumericCellValue()));
                      } else {
                          assertThat(c.getStringCellValue()).isEqualTo("");
                      }
                  }
              }
          } else {
              if (expectedValue.getClass()==String.class) {
                  assertThat(targetSheet.getRow(x).getCell(y).getStringCellValue()).isEqualTo(expectedValue);
              } else {
                  if (expectedValue.getClass() == Integer.class) {
                      var cellValue = targetSheet.getRow(x).getCell(y);
                      if (cellValue != null){
                          var expectedDouble = Double.valueOf(expectedValue.toString());
                          assertThat(cellValue.getNumericCellValue()).isEqualTo(expectedDouble);
                      }
                  } else {
                      //fail!
                      throw new AssertionError("no sensible test possible");
                  }
              }
  
          }
  
      }
  
      void sourceSheetBuilder(final List<String> annotationList, final List<Integer> orderList, final List<String> typeList, final List<String> fieldNameList, final List<?>... values){
  
          Row r0 = sourceSheet.createRow(0);
          int i = 0;
          for (String a : annotationList){
              r0.createCell(i++).setCellValue(a);
          }
  
          Row r1 = sourceSheet.createRow(1);
          if (orderList!=null) {
              i = 0;
              for (int o : orderList) {
                  r1.createCell(i++).setCellValue(o);
              }
          }
  
          Row r2 = sourceSheet.createRow(2);
          if (typeList!=null) {
              i = 0;
              for (String f : typeList) {
                  r2.createCell(i++).setCellValue(f);
              }
          }
  
          Row r3 = sourceSheet.createRow(3);
          if (fieldNameList!=null) {
              i = 0;
              for (String f : fieldNameList) {
                  r3.createCell(i++).setCellValue(f);
              }
          }
  
          if (values!=null) {
              int t = 4;
              for (List<?> v : values) {
                  Row r = sourceSheet.createRow(t++);
                  i = 0;
                  for (Object o : v) {
                      if (o == null){
                          i++;
                          continue;
                      }
                      if (o.getClass() == Integer.class){
                          r.createCell(i++).setCellValue((Integer) o);
                          continue;
                      }
                      if (o.getClass() == String.class){
                          r.createCell(i++).setCellValue((String) o);
                          continue;
                      } else {
                          // skip
                          i++;
                      }
                  }
              }
          }
  
      }
  
  }
  