using ClosedXML.Excel;
using NUnit.Framework;
using System;
using System.Linq;
using ClosedXML.Excel.CalcEngine;
using ClosedXML.Excel.CalcEngine.Exceptions;

namespace ClosedXML_Tests.Excel.CalcEngine
{
    [TestFixture]
    public class StatisticalTests
    {
        private double tolerance = 1e-6;
        private XLWorkbook workbook;

        [Test]
        public void Average()
        {
            double value;
            value = workbook.Evaluate("AVERAGE(-27.5,93.93,64.51,-70.56)").CastTo<double>();
            Assert.AreEqual(15.095, value, tolerance);

            var ws = workbook.Worksheets.First();
            value = ws.Evaluate("AVERAGE(G3:G45)").CastTo<double>();
            Assert.AreEqual(49.3255814, value, tolerance);

            Assert.That(() => ws.Evaluate("AVERAGE(D3:D45)"), Throws.TypeOf<ApplicationException>());
        }

        [Test]
        public void Count()
        {
            var ws = workbook.Worksheets.First();
            int value;
            value = ws.Evaluate(@"=COUNT(D3:D45)").CastTo<int>();
            Assert.AreEqual(0, value);

            value = ws.Evaluate(@"=COUNT(G3:G45)").CastTo<int>();
            Assert.AreEqual(43, value);

            value = ws.Evaluate(@"=COUNT(G:G)").CastTo<int>();
            Assert.AreEqual(43, value);

            value = workbook.Evaluate(@"=COUNT(Data!G:G)").CastTo<int>();
            Assert.AreEqual(43, value);
        }

        [Test]
        public void CountA()
        {
            var ws = workbook.Worksheets.First();
            int value;
            value = ws.Evaluate(@"=COUNTA(D3:D45)").CastTo<int>();
            Assert.AreEqual(43, value);

            value = ws.Evaluate(@"=COUNTA(G3:G45)").CastTo<int>();
            Assert.AreEqual(43, value);

            value = ws.Evaluate(@"=COUNTA(G:G)").CastTo<int>();
            Assert.AreEqual(44, value);

            value = workbook.Evaluate(@"=COUNTA(Data!G:G)").CastTo<int>();
            Assert.AreEqual(44, value);
        }

        [Test]
        public void CountBlank()
        {
            var ws = workbook.Worksheets.First();
            int value;
            value = ws.Evaluate(@"=COUNTBLANK(B:B)").CastTo<int>();
            Assert.AreEqual(1048532, value);

            value = ws.Evaluate(@"=COUNTBLANK(D43:D49)").CastTo<int>();
            Assert.AreEqual(4, value);

            value = ws.Evaluate(@"=COUNTBLANK(E3:E45)").CastTo<int>();
            Assert.AreEqual(0, value);

            value = ws.Evaluate(@"=COUNTBLANK(A1)").CastTo<int>();
            Assert.AreEqual(1, value);

            Assert.Throws<NoValueAvailableException>(() => workbook.Evaluate(@"=COUNTBLANK(E3:E45)"));
            Assert.Throws<ExpressionParseException>(() => ws.Evaluate(@"=COUNTBLANK()"));
            Assert.Throws<ExpressionParseException>(() => ws.Evaluate(@"=COUNTBLANK(A3:A45,E3:E45)"));
        }

        [Test]
        public void CountIf()
        {
            var ws = workbook.Worksheets.First();
            int value;
            value = ws.Evaluate(@"=COUNTIF(D3:D45,""Central"")").CastTo<int>();
            Assert.AreEqual(24, value);

            value = ws.Evaluate(@"=COUNTIF(D:D,""Central"")").CastTo<int>();
            Assert.AreEqual(24, value);

            value = workbook.Evaluate(@"=COUNTIF(Data!D:D,""Central"")").CastTo<int>();
            Assert.AreEqual(24, value);
        }

        [TestCase(@"=COUNTIF(Data!E:E, ""J*"")", 13)]
        [TestCase(@"=COUNTIF(Data!E:E, ""*i*"")", 21)]
        [TestCase(@"=COUNTIF(Data!E:E, ""*in*"")", 9)]
        [TestCase(@"=COUNTIF(Data!E:E, ""*i*l"")", 9)]
        [TestCase(@"=COUNTIF(Data!E:E, ""*i?e*"")", 9)]
        [TestCase(@"=COUNTIF(Data!E:E, ""*o??s*"")", 10)]
        [TestCase(@"=COUNTIF(Data!X1:X1000, """")", 1000)]
        [TestCase(@"=COUNTIF(Data!E1:E44, """")", 1)]
        public void CountIf_ConditionWithWildcards(string formula, int expectedResult)
        {
            var ws = workbook.Worksheets.First();

            int value = ws.Evaluate(formula).CastTo<int>();
            Assert.AreEqual(expectedResult, value);
        }

        [TestCase("x", @"=COUNTIF(A1:A1, ""?"")", 1)]
        [TestCase("x", @"=COUNTIF(A1:A1, ""~?"")", 0)]
        [TestCase("?", @"=COUNTIF(A1:A1, ""~?"")", 1)]
        [TestCase("~?", @"=COUNTIF(A1:A1, ""~?"")", 0)] 
        [TestCase("~?", @"=COUNTIF(A1:A1, ""~~~?"")", 1)] 
        [TestCase("?", @"=COUNTIF(A1:A1, ""~~?"")", 0)]
        [TestCase("~?", @"=COUNTIF(A1:A1, ""~~?"")", 1)]
        [TestCase("~x", @"=COUNTIF(A1:A1, ""~~?"")", 1)]
        [TestCase("*", @"=COUNTIF(A1:A1, ""~*"")", 1)]
        [TestCase("~*", @"=COUNTIF(A1:A1, ""~*"")", 0)]
        [TestCase("~*", @"=COUNTIF(A1:A1, ""~~~*"")", 1)]
        [TestCase("*", @"=COUNTIF(A1:A1, ""~~*"")", 0)]
        [TestCase("~*", @"=COUNTIF(A1:A1, ""~~*"")", 1)]
        [TestCase("~x", @"=COUNTIF(A1:A1, ""~~*"")", 1)]
        [TestCase("~xyz", @"=COUNTIF(A1:A1, ""~~*"")", 1)]
        public void CountIf_MoreWildcards(string cellContent, string formula, int expectedResult)
        {
            using (var wb = new XLWorkbook())
            {
                var ws = wb.AddWorksheet("Sheet1");

                ws.Cell(1, 1).Value = cellContent;

                Assert.AreEqual(expectedResult, (double)ws.Evaluate(formula));
            }
        }

        [TestCase("=COUNTIFS(B1:D1, \"=Yes\")", 1)]
        [TestCase("=COUNTIFS(B1:B4, \"=Yes\", C1:C4, \"=Yes\")", 2)]
        [TestCase("= COUNTIFS(B4:D4, \"=Yes\", B2:D2, \"=Yes\")", 1)]
        public void CountIfs_ReferenceExample1FromExcelDocumentations(
            string formula,
            int expectedOutcome)
        {
            using (var wb = new XLWorkbook())
            {
                wb.ReferenceStyle = XLReferenceStyle.A1;

                var ws = wb.AddWorksheet("Sheet1");

                ws.Cell(1, 1).Value = "Davidoski";
                ws.Cell(1, 2).Value = "Yes";
                ws.Cell(1, 3).Value = "No";
                ws.Cell(1, 4).Value = "No";

                ws.Cell(2, 1).Value = "Burke";
                ws.Cell(2, 2).Value = "Yes";
                ws.Cell(2, 3).Value = "Yes";
                ws.Cell(2, 4).Value = "No";

                ws.Cell(3, 1).Value = "Sundaram";
                ws.Cell(3, 2).Value = "Yes";
                ws.Cell(3, 3).Value = "Yes";
                ws.Cell(3, 4).Value = "Yes";

                ws.Cell(4, 1).Value = "Levitan";
                ws.Cell(4, 2).Value = "No";
                ws.Cell(4, 3).Value = "Yes";
                ws.Cell(4, 4).Value = "Yes";

                Assert.AreEqual(expectedOutcome, (int)ws.Evaluate(formula));
            }
        }

        [Test]
        public void CountIfs_SingleCondition()
        {
            var ws = workbook.Worksheets.First();
            int value;
            value = ws.Evaluate(@"=COUNTIFS(D3:D45,""Central"")").CastTo<int>();
            Assert.AreEqual(24, value);

            value = ws.Evaluate(@"=COUNTIFS(D:D,""Central"")").CastTo<int>();
            Assert.AreEqual(24, value);

            value = workbook.Evaluate(@"=COUNTIFS(Data!D:D,""Central"")").CastTo<int>();
            Assert.AreEqual(24, value);
        }

        [TestCase(@"=COUNTIFS(Data!E:E, ""J*"")", 13)]
        [TestCase(@"=COUNTIFS(Data!E:E, ""*i*"")", 21)]
        [TestCase(@"=COUNTIFS(Data!E:E, ""*in*"")", 9)]
        [TestCase(@"=COUNTIFS(Data!E:E, ""*i*l"")", 9)]
        [TestCase(@"=COUNTIFS(Data!E:E, ""*i?e*"")", 9)]
        [TestCase(@"=COUNTIFS(Data!E:E, ""*o??s*"")", 10)]
        [TestCase(@"=COUNTIFS(Data!X1:X1000, """")", 1000)]
        [TestCase(@"=COUNTIFS(Data!E1:E44, """")", 1)]
        public void CountIfs_SingleConditionWithWildcards(string formula, int expectedResult)
        {
            var ws = workbook.Worksheets.First();

            int value = ws.Evaluate(formula).CastTo<int>();
            Assert.AreEqual(expectedResult, value);
        }

        [OneTimeTearDown]
        public void Dispose()
        {
            workbook.Dispose();
        }

        [OneTimeSetUp]
        public void Init()
        {
            // Make sure tests run on a deterministic culture
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            workbook = SetupWorkbook();
        }

        [Test]
        public void Max()
        {
            var ws = workbook.Worksheets.First();
            int value;
            value = ws.Evaluate(@"=MAX(D3:D45)").CastTo<int>();
            Assert.AreEqual(0, value);

            value = ws.Evaluate(@"=MAX(G3:G45)").CastTo<int>();
            Assert.AreEqual(96, value);

            value = ws.Evaluate(@"=MAX(G:G)").CastTo<int>();
            Assert.AreEqual(96, value);

            value = workbook.Evaluate(@"=MAX(Data!G:G)").CastTo<int>();
            Assert.AreEqual(96, value);
        }

        [Test]
        public void Min()
        {
            var ws = workbook.Worksheets.First();
            int value;
            value = ws.Evaluate(@"=MIN(D3:D45)").CastTo<int>();
            Assert.AreEqual(0, value);

            value = ws.Evaluate(@"=MIN(G3:G45)").CastTo<int>();
            Assert.AreEqual(2, value);

            value = ws.Evaluate(@"=MIN(G:G)").CastTo<int>();
            Assert.AreEqual(2, value);

            value = workbook.Evaluate(@"=MIN(Data!G:G)").CastTo<int>();
            Assert.AreEqual(2, value);
        }

        [Test]
        public void StDev()
        {
            var ws = workbook.Worksheets.First();
            double value;
            Assert.That(() => ws.Evaluate(@"=STDEV(D3:D45)"), Throws.TypeOf<ApplicationException>());

            value = ws.Evaluate(@"=STDEV(H3:H45)").CastTo<double>();
            Assert.AreEqual(47.34511769, value, tolerance);

            value = ws.Evaluate(@"=STDEV(H:H)").CastTo<double>();
            Assert.AreEqual(47.34511769, value, tolerance);

            value = workbook.Evaluate(@"=STDEV(Data!H:H)").CastTo<double>();
            Assert.AreEqual(47.34511769, value, tolerance);
        }

        [Test]
        public void StDevP()
        {
            var ws = workbook.Worksheets.First();
            double value;
            Assert.That(() => ws.Evaluate(@"=STDEVP(D3:D45)"), Throws.InvalidOperationException);

            value = ws.Evaluate(@"=STDEVP(H3:H45)").CastTo<double>();
            Assert.AreEqual(46.79135458, value, tolerance);

            value = ws.Evaluate(@"=STDEVP(H:H)").CastTo<double>();
            Assert.AreEqual(46.79135458, value, tolerance);

            value = workbook.Evaluate(@"=STDEVP(Data!H:H)").CastTo<double>();
            Assert.AreEqual(46.79135458, value, tolerance);
        }

        [Test]
        public void Var()
        {
            var ws = workbook.Worksheets.First();
            double value;
            Assert.That(() => ws.Evaluate(@"=VAR(D3:D45)"), Throws.InvalidOperationException);

            value = ws.Evaluate(@"=VAR(H3:H45)").CastTo<double>();
            Assert.AreEqual(2241.560169, value, tolerance);

            value = ws.Evaluate(@"=VAR(H:H)").CastTo<double>();
            Assert.AreEqual(2241.560169, value, tolerance);

            value = workbook.Evaluate(@"=VAR(Data!H:H)").CastTo<double>();
            Assert.AreEqual(2241.560169, value, tolerance);
        }

        [Test]
        public void VarP()
        {
            var ws = workbook.Worksheets.First();
            double value;
            Assert.That(() => ws.Evaluate(@"=VARP(D3:D45)"), Throws.InvalidOperationException);

            value = ws.Evaluate(@"=VARP(H3:H45)").CastTo<double>();
            Assert.AreEqual(2189.430863, value, tolerance);

            value = ws.Evaluate(@"=VARP(H:H)").CastTo<double>();
            Assert.AreEqual(2189.430863, value, tolerance);

            value = workbook.Evaluate(@"=VARP(Data!H:H)").CastTo<double>();
            Assert.AreEqual(2189.430863, value, tolerance);
        }

        private XLWorkbook SetupWorkbook()
        {
            var wb = new XLWorkbook();
            var ws = wb.AddWorksheet("Data");
            var data = new object[]
            {
                new {Id=1, OrderDate = DateTime.Parse("2015-01-06"), Region = "East", Rep = "Jones", Item = "Pencil", Units = 95, UnitCost = 1.99, Total = 189.05 },
                new {Id=2, OrderDate = DateTime.Parse("2015-01-23"), Region = "Central", Rep = "Kivell", Item = "Binder", Units = 50, UnitCost = 19.99, Total = 999.5},
                new {Id=3, OrderDate = DateTime.Parse("2015-02-09"), Region = "Central", Rep = "Jardine", Item = "Pencil", Units = 36, UnitCost = 4.99, Total = 179.64},
                new {Id=4, OrderDate = DateTime.Parse("2015-02-26"), Region = "Central", Rep = "Gill", Item = "Pen", Units = 27, UnitCost = 19.99, Total = 539.73},
                new {Id=5, OrderDate = DateTime.Parse("2015-03-15"), Region = "West", Rep = "Sorvino", Item = "Pencil", Units = 56, UnitCost = 2.99, Total = 167.44},
                new {Id=6, OrderDate = DateTime.Parse("2015-04-01"), Region = "East", Rep = "Jones", Item = "Binder", Units = 60, UnitCost = 4.99, Total = 299.4},
                new {Id=7, OrderDate = DateTime.Parse("2015-04-18"), Region = "Central", Rep = "Andrews", Item = "Pencil", Units = 75, UnitCost = 1.99, Total = 149.25},
                new {Id=8, OrderDate = DateTime.Parse("2015-05-05"), Region = "Central", Rep = "Jardine", Item = "Pencil", Units = 90, UnitCost = 4.99, Total = 449.1},
                new {Id=9, OrderDate = DateTime.Parse("2015-05-22"), Region = "West", Rep = "Thompson", Item = "Pencil", Units = 32, UnitCost = 1.99, Total = 63.68},
                new {Id=10, OrderDate = DateTime.Parse("2015-06-08"), Region = "East", Rep = "Jones", Item = "Binder", Units = 60, UnitCost = 8.99, Total = 539.4},
                new {Id=11, OrderDate = DateTime.Parse("2015-06-25"), Region = "Central", Rep = "Morgan", Item = "Pencil", Units = 90, UnitCost = 4.99, Total = 449.1},
                new {Id=12, OrderDate = DateTime.Parse("2015-07-12"), Region = "East", Rep = "Howard", Item = "Binder", Units = 29, UnitCost = 1.99, Total = 57.71},
                new {Id=13, OrderDate = DateTime.Parse("2015-07-29"), Region = "East", Rep = "Parent", Item = "Binder", Units = 81, UnitCost = 19.99, Total = 1619.19},
                new {Id=14, OrderDate = DateTime.Parse("2015-08-15"), Region = "East", Rep = "Jones", Item = "Pencil", Units = 35, UnitCost = 4.99, Total = 174.65},
                new {Id=15, OrderDate = DateTime.Parse("2015-09-01"), Region = "Central", Rep = "Smith", Item = "Desk", Units = 2, UnitCost = 125, Total = 250},
                new {Id=16, OrderDate = DateTime.Parse("2015-09-18"), Region = "East", Rep = "Jones", Item = "Pen Set", Units = 16, UnitCost = 15.99, Total = 255.84},
                new {Id=17, OrderDate = DateTime.Parse("2015-10-05"), Region = "Central", Rep = "Morgan", Item = "Binder", Units = 28, UnitCost = 8.99, Total = 251.72},
                new {Id=18, OrderDate = DateTime.Parse("2015-10-22"), Region = "East", Rep = "Jones", Item = "Pen", Units = 64, UnitCost = 8.99, Total = 575.36},
                new {Id=19, OrderDate = DateTime.Parse("2015-11-08"), Region = "East", Rep = "Parent", Item = "Pen", Units = 15, UnitCost = 19.99, Total = 299.85},
                new {Id=20, OrderDate = DateTime.Parse("2015-11-25"), Region = "Central", Rep = "Kivell", Item = "Pen Set", Units = 96, UnitCost = 4.99, Total = 479.04},
                new {Id=21, OrderDate = DateTime.Parse("2015-12-12"), Region = "Central", Rep = "Smith", Item = "Pencil", Units = 67, UnitCost = 1.29, Total = 86.43},
                new {Id=22, OrderDate = DateTime.Parse("2015-12-29"), Region = "East", Rep = "Parent", Item = "Pen Set", Units = 74, UnitCost = 15.99, Total = 1183.26},
                new {Id=23, OrderDate = DateTime.Parse("2016-01-15"), Region = "Central", Rep = "Gill", Item = "Binder", Units = 46, UnitCost = 8.99, Total = 413.54},
                new {Id=24, OrderDate = DateTime.Parse("2016-02-01"), Region = "Central", Rep = "Smith", Item = "Binder", Units = 87, UnitCost = 15, Total = 1305},
                new {Id=25, OrderDate = DateTime.Parse("2016-02-18"), Region = "East", Rep = "Jones", Item = "Binder", Units = 4, UnitCost = 4.99, Total = 19.96},
                new {Id=26, OrderDate = DateTime.Parse("2016-03-07"), Region = "West", Rep = "Sorvino", Item = "Binder", Units = 7, UnitCost = 19.99, Total = 139.93},
                new {Id=27, OrderDate = DateTime.Parse("2016-03-24"), Region = "Central", Rep = "Jardine", Item = "Pen Set", Units = 50, UnitCost = 4.99, Total = 249.5},
                new {Id=28, OrderDate = DateTime.Parse("2016-04-10"), Region = "Central", Rep = "Andrews", Item = "Pencil", Units = 66, UnitCost = 1.99, Total = 131.34},
                new {Id=29, OrderDate = DateTime.Parse("2016-04-27"), Region = "East", Rep = "Howard", Item = "Pen", Units = 96, UnitCost = 4.99, Total = 479.04},
                new {Id=30, OrderDate = DateTime.Parse("2016-05-14"), Region = "Central", Rep = "Gill", Item = "Pencil", Units = 53, UnitCost = 1.29, Total = 68.37},
                new {Id=31, OrderDate = DateTime.Parse("2016-05-31"), Region = "Central", Rep = "Gill", Item = "Binder", Units = 80, UnitCost = 8.99, Total = 719.2},
                new {Id=32, OrderDate = DateTime.Parse("2016-06-17"), Region = "Central", Rep = "Kivell", Item = "Desk", Units = 5, UnitCost = 125, Total = 625},
                new {Id=33, OrderDate = DateTime.Parse("2016-07-04"), Region = "East", Rep = "Jones", Item = "Pen Set", Units = 62, UnitCost = 4.99, Total = 309.38},
                new {Id=34, OrderDate = DateTime.Parse("2016-07-21"), Region = "Central", Rep = "Morgan", Item = "Pen Set", Units = 55, UnitCost = 12.49, Total = 686.95},
                new {Id=35, OrderDate = DateTime.Parse("2016-08-07"), Region = "Central", Rep = "Kivell", Item = "Pen Set", Units = 42, UnitCost = 23.95, Total = 1005.9},
                new {Id=36, OrderDate = DateTime.Parse("2016-08-24"), Region = "West", Rep = "Sorvino", Item = "Desk", Units = 3, UnitCost = 275, Total = 825},
                new {Id=37, OrderDate = DateTime.Parse("2016-09-10"), Region = "Central", Rep = "Gill", Item = "Pencil", Units = 7, UnitCost = 1.29, Total = 9.03},
                new {Id=38, OrderDate = DateTime.Parse("2016-09-27"), Region = "West", Rep = "Sorvino", Item = "Pen", Units = 76, UnitCost = 1.99, Total = 151.24},
                new {Id=39, OrderDate = DateTime.Parse("2016-10-14"), Region = "West", Rep = "Thompson", Item = "Binder", Units = 57, UnitCost = 19.99, Total = 1139.43},
                new {Id=40, OrderDate = DateTime.Parse("2016-10-31"), Region = "Central", Rep = "Andrews", Item = "Pencil", Units = 14, UnitCost = 1.29, Total = 18.06},
                new {Id=41, OrderDate = DateTime.Parse("2016-11-17"), Region = "Central", Rep = "Jardine", Item = "Binder", Units = 11, UnitCost = 4.99, Total = 54.89},
                new {Id=42, OrderDate = DateTime.Parse("2016-12-04"), Region = "Central", Rep = "Jardine", Item = "Binder", Units = 94, UnitCost = 19.99, Total = 1879.06},
                new {Id=43, OrderDate = DateTime.Parse("2016-12-21"), Region = "Central", Rep = "Andrews", Item = "Binder", Units = 28, UnitCost = 4.99, Total = 139.72}
            };
            ws.FirstCell()
                .CellBelow()
                .CellRight()
                .InsertTable(data, "Table1");

            return wb;
        }
    }
}