/*
 *  ====================================================================
 *    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.poi.ss.formula.functions;

import static org.apache.poi.ss.util.Utils.*;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;

import org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.NumericValueEval;
import org.apache.poi.ss.formula.eval.StringEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.usermodel.FormulaError;
import org.junit.jupiter.api.Test;

import java.io.IOException;

/**
 * Test cases for SUMIFS()
 */
final class TestSumifs {

    private static final OperationEvaluationContext EC = new OperationEvaluationContext(null, null, 0, 1, 0, null);

    private static ValueEval invokeSumifs(ValueEval[] args) {
        return new Sumifs().evaluate(args, EC);
    }

    private static void confirmDouble(double expected, ValueEval actualEval) {
        assertTrue(actualEval instanceof NumericValueEval, "Expected numeric result");
        NumericValueEval nve = (NumericValueEval)actualEval;
        assertEquals(expected, nve.getNumberValue(), 0);
    }

    private static void confirm(double expectedResult, ValueEval[] args) {
        confirmDouble(expectedResult, invokeSumifs(args));
    }

    /**
     *  Example 1 from
     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
     */
    @Test
    void testExample1() {
        // mimic test sample from http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
        ValueEval[] a2a9 = new ValueEval[] {
                new NumberEval(5),
                new NumberEval(4),
                new NumberEval(15),
                new NumberEval(3),
                new NumberEval(22),
                new NumberEval(12),
                new NumberEval(10),
                new NumberEval(33)
        };

        ValueEval[] b2b9 = new ValueEval[] {
                new StringEval("Apples"),
                new StringEval("Apples"),
                new StringEval("Artichokes"),
                new StringEval("Artichokes"),
                new StringEval("Bananas"),
                new StringEval("Bananas"),
                new StringEval("Carrots"),
                new StringEval("Carrots"),
        };

        ValueEval[] c2c9 = new ValueEval[] {
                new NumberEval(1),
                new NumberEval(2),
                new NumberEval(1),
                new NumberEval(2),
                new NumberEval(1),
                new NumberEval(2),
                new NumberEval(1),
                new NumberEval(2)
        };

        ValueEval[] args;
        // "=SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, 1)"
        args = new ValueEval[]{
                EvalFactory.createAreaEval("A2:A9", a2a9),
                EvalFactory.createAreaEval("B2:B9", b2b9),
                new StringEval("A*"),
                EvalFactory.createAreaEval("C2:C9", c2c9),
                new NumberEval(1),
        };
        confirm(20.0, args);

        // "=SUMIFS(A2:A9, B2:B9, "<>Bananas", C2:C9, 1)"
        args = new ValueEval[]{
                EvalFactory.createAreaEval("A2:A9", a2a9),
                EvalFactory.createAreaEval("B2:B9", b2b9),
                new StringEval("<>Bananas"),
                EvalFactory.createAreaEval("C2:C9", c2c9),
                new NumberEval(1),
        };
        confirm(30.0, args);

        // a test case that returns ErrorEval.VALUE_INVALID :
        // the dimensions of the first and second criteria ranges are different
        // "=SUMIFS(A2:A9, B2:B8, "<>Bananas", C2:C9, 1)"
        args = new ValueEval[]{
                EvalFactory.createAreaEval("A2:A9", a2a9),
                EvalFactory.createAreaEval("B2:B8", new ValueEval[] {
                        new StringEval("Apples"),
                        new StringEval("Apples"),
                        new StringEval("Artichokes"),
                        new StringEval("Artichokes"),
                        new StringEval("Bananas"),
                        new StringEval("Bananas"),
                        new StringEval("Carrots"),
                }),
                new StringEval("<>Bananas"),
                EvalFactory.createAreaEval("C2:C9", c2c9),
                new NumberEval(1),
        };
        assertEquals(ErrorEval.VALUE_INVALID, invokeSumifs(args));

    }

    /**
     *  Example 2 from
     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
     */
    @Test
    void testExample2() {
        ValueEval[] b2e2 = new ValueEval[] {
                new NumberEval(100),
                new NumberEval(390),
                new NumberEval(8321),
                new NumberEval(500)
        };
        // 1%   0.5%    3%  4%
        ValueEval[] b3e3 = new ValueEval[] {
                new NumberEval(0.01),
                new NumberEval(0.005),
                new NumberEval(0.03),
                new NumberEval(0.04)
        };

        // 1%   1.3%    2.1%    2%
        ValueEval[] b4e4 = new ValueEval[] {
                new NumberEval(0.01),
                new NumberEval(0.013),
                new NumberEval(0.021),
                new NumberEval(0.02)
        };

        // 0.5% 3%  1%  4%
        ValueEval[] b5e5 = new ValueEval[] {
                new NumberEval(0.005),
                new NumberEval(0.03),
                new NumberEval(0.01),
                new NumberEval(0.04)
        };

        ValueEval[] args;

        // "=SUMIFS(B2:E2, B3:E3, ">3%", B4:E4, ">=2%")"
        args = new ValueEval[]{
                EvalFactory.createAreaEval("B2:E2", b2e2),
                EvalFactory.createAreaEval("B3:E3", b3e3),
                new StringEval(">0.03"), // 3% in the MSFT example
                EvalFactory.createAreaEval("B4:E4", b4e4),
                new StringEval(">=0.02"),   // 2% in the MSFT example
                EvalFactory.createAreaEval("B5:E5", b5e5),
                new StringEval(">=0.01"),   // 1% in the MSFT example
        };
        confirm(500.0, args);
    }

    /**
     *  Example 3 from
     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
     */
    @Test
    void testExample3() {
        //3.3   0.8 5.5 5.5
        ValueEval[] b2e2 = new ValueEval[] {
                new NumberEval(3.3),
                new NumberEval(0.8),
                new NumberEval(5.5),
                new NumberEval(5.5)
        };
        // 55   39  39  57.5
        ValueEval[] b3e3 = new ValueEval[] {
                new NumberEval(55),
                new NumberEval(39),
                new NumberEval(39),
                new NumberEval(57.5)
        };

        // 6.5  19.5    6   6.5
        ValueEval[] b4e4 = new ValueEval[] {
                new NumberEval(6.5),
                new NumberEval(19.5),
                new NumberEval(6),
                new NumberEval(6.5)
        };

        ValueEval[] args;

        // "=SUMIFS(B2:E2, B3:E3, ">=40", B4:E4, "<10")"
        args = new ValueEval[]{
                EvalFactory.createAreaEval("B2:E2", b2e2),
                EvalFactory.createAreaEval("B3:E3", b3e3),
                new StringEval(">=40"),
                EvalFactory.createAreaEval("B4:E4", b4e4),
                new StringEval("<10"),
        };
        confirm(8.8, args);
    }

    /**
     *  Example 5 from
     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
     *
     *  Criteria entered as reference and by using wildcard characters
     */
    @Test
    void testFromFile() {

        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("sumifs.xls");
        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

        HSSFSheet example1 = wb.getSheet("Example 1");
        HSSFCell ex1cell1 = example1.getRow(10).getCell(2);
        fe.evaluate(ex1cell1);
        assertEquals(20.0, ex1cell1.getNumericCellValue(), 0);
        HSSFCell ex1cell2 = example1.getRow(11).getCell(2);
        fe.evaluate(ex1cell2);
        assertEquals(30.0, ex1cell2.getNumericCellValue(), 0);

        HSSFSheet example2 = wb.getSheet("Example 2");
        HSSFCell ex2cell1 = example2.getRow(6).getCell(2);
        fe.evaluate(ex2cell1);
        assertEquals(500.0, ex2cell1.getNumericCellValue(), 0);
        HSSFCell ex2cell2 = example2.getRow(7).getCell(2);
        fe.evaluate(ex2cell2);
        assertEquals(8711.0, ex2cell2.getNumericCellValue(), 0);

        HSSFSheet example3 = wb.getSheet("Example 3");
        HSSFCell ex3cell = example3.getRow(5).getCell(2);
        fe.evaluate(ex3cell);
        assertEquals(8.8, ex3cell.getNumericCellValue(), 0);

        HSSFSheet example4 = wb.getSheet("Example 4");
        HSSFCell ex4cell = example4.getRow(8).getCell(2);
        fe.evaluate(ex4cell);
        assertEquals(3.5, ex4cell.getNumericCellValue(), 0);

        HSSFSheet example5 = wb.getSheet("Example 5");
        HSSFCell ex5cell = example5.getRow(8).getCell(2);
        fe.evaluate(ex5cell);
        assertEquals(625000., ex5cell.getNumericCellValue(), 0);

    }

    @Test
    void testBug56655() {
        ValueEval[] a2a9 = new ValueEval[] {
                new NumberEval(5),
                new NumberEval(4),
                new NumberEval(15),
                new NumberEval(3),
                new NumberEval(22),
                new NumberEval(12),
                new NumberEval(10),
                new NumberEval(33)
        };

        ValueEval[] args = new ValueEval[]{
                EvalFactory.createAreaEval("A2:A9", a2a9),
                ErrorEval.VALUE_INVALID,
                new StringEval("A*"),
        };

        ValueEval result = invokeSumifs(args);
        assertTrue(result instanceof ErrorEval, "Expect to have an error when an input is an invalid value, but had: " + result.getClass());

        args = new ValueEval[]{
                EvalFactory.createAreaEval("A2:A9", a2a9),
                EvalFactory.createAreaEval("A2:A9", a2a9),
                ErrorEval.VALUE_INVALID,
        };

        result = invokeSumifs(args);
        assertTrue(result instanceof ErrorEval, "Expect to have an error when an input is an invalid value, but had: " + result.getClass());
    }

    @Test
    void testBug56655b() {
/*
        setCellFormula(sheet, 0, 0, "B1*C1");
        sheet.getRow(0).createCell(1).setCellValue("A");
        setCellFormula(sheet, 1, 0, "B1*C1");
        sheet.getRow(1).createCell(1).setCellValue("A");
        setCellFormula(sheet, 0, 3, "SUMIFS(A:A,A:A,A2)");
 */
        ValueEval[] a0a1 = new ValueEval[] {
                NumberEval.ZERO,
                NumberEval.ZERO
        };

        ValueEval[] args = new ValueEval[]{
                EvalFactory.createAreaEval("A0:A1", a0a1),
                EvalFactory.createAreaEval("A0:A1", a0a1),
                ErrorEval.VALUE_INVALID
        };

        ValueEval result = invokeSumifs(args);
        assertTrue(result instanceof ErrorEval, "Expect to have an error when an input is an invalid value, but had: " + result.getClass());
        assertEquals(ErrorEval.VALUE_INVALID, result);
    }

    @Test
    void testBug56655c() {
/*
        setCellFormula(sheet, 0, 0, "B1*C1");
        sheet.getRow(0).createCell(1).setCellValue("A");
        setCellFormula(sheet, 1, 0, "B1*C1");
        sheet.getRow(1).createCell(1).setCellValue("A");
        setCellFormula(sheet, 0, 3, "SUMIFS(A:A,A:A,A2)");
 */
        ValueEval[] a0a1 = new ValueEval[] {
                NumberEval.ZERO,
                NumberEval.ZERO
        };

        ValueEval[] args = new ValueEval[]{
                EvalFactory.createAreaEval("A0:A1", a0a1),
                EvalFactory.createAreaEval("A0:A1", a0a1),
                ErrorEval.NAME_INVALID
        };

        ValueEval result = invokeSumifs(args);
        assertTrue(result instanceof ErrorEval, "Expect to have an error when an input is an invalid value, but had: " + result.getClass());
        assertEquals(ErrorEval.NAME_INVALID, result);
    }

    @Test
    void testMicrosoftExample1() throws IOException {
        try (HSSFWorkbook wb = initWorkbook1()) {
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
            assertDouble(fe, cell, "SUMIFS(A2:A9, B2:B9, \"=A*\", C2:C9, \"Tom\")", 20);
            assertDouble(fe, cell, "SUMIFS(A2:A9, B2:B9, \"<>Bananas\", C2:C9, \"Tom\")", 30);
        }
    }

    @Test
    void testMicrosoftExample1WithNA() throws IOException {
        try (HSSFWorkbook wb = initWorkbook1WithNA()) {
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
            assertError(fe, cell, "SUMIFS(A2:A10, B2:B10, \"<>Bananas\", C2:C10, \"Tom\")", FormulaError.NA);
        }
    }

    @Test
    void testMicrosoftExample1WithBooleanAndString() throws IOException {
        try (HSSFWorkbook wb = initWorkbook1()) {
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
            assertDouble(fe, cell, "SUMIFS(A2:A11, B2:B11, \"<>Bananas\", C2:C11, \"Tom\")", 30);
        }
    }

    //see https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b
    private HSSFWorkbook initWorkbook1() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        addRow(sheet, 0, "Quantity Sold", "Product", "Salesperson");
        addRow(sheet, 1, 5, "Apples", "Tom");
        addRow(sheet, 2, 4, "Apples", "Sarah");
        addRow(sheet, 3, 15, "Artichokes", "Tom");
        addRow(sheet, 4, 3, "Artichokes", "Sarah");
        addRow(sheet, 5, 22, "Bananas", "Tom");
        addRow(sheet, 6, 12, "Bananas", "Sarah");
        addRow(sheet, 7, 10, "Carrots", "Tom");
        addRow(sheet, 8, 33, "Carrots", "Sarah");
        return wb;
    }

    private HSSFWorkbook initWorkbook1WithNA() {
        HSSFWorkbook wb = initWorkbook1();
        HSSFSheet sheet = wb.getSheetAt(0);
        addRow(sheet, 9, FormulaError.NA, "Pears", "Tom");
        return wb;
    }

    private HSSFWorkbook initWorkbook1WithBooleanAnString() {
        HSSFWorkbook wb = initWorkbook1();
        HSSFSheet sheet = wb.getSheetAt(0);
        addRow(sheet, 9, true, "Pears", "Tom");
        addRow(sheet, 10, "abc", "Kumquats", "Tom");
        return wb;
    }
}
