using System;
using System.Collections.Generic;
using System.Drawing;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Syncfusion.XlsIO;

namespace Confluence.WorkbooksGateway.Gateway.XlsIO
{
    public static class XlsIOCheckSummaryGenerator
    {


        internal const string SUMMARY_SHEET_NAME = "Summary";
        private const int SUMMARY_SHEET_NAME_INDEX = 2;
        private const int SUMMARY_PASS_CHECK_INDEX = 3;
        private const int SUMMARY_FAIL_CHECK_INDEX = 4;
        private const int SUMMARY_TOTAL_CHECK_INDEX = 5;
        private const int SUMMARY_ADJUSTMENT_INDEX = 6;
        private const int EXCEL_DATE_FORMAT_INDEX = 14;
        private const int EXCEL_TIME_FORMAT_INDEX = 19;


        internal static double defaultFontSize = 10.0;
        internal static double headerFontSize = 12.0;


        public static Syncfusion.XlsIO.IWorksheet GenerateCheckSummary(Syncfusion.XlsIO.IWorkbook workbook)
        {
            Syncfusion.XlsIO.IWorksheet checkSummary = workbook.Worksheets.Create(SUMMARY_SHEET_NAME);
            checkSummary.Move(0);
            int headerRowIndex = 8;
            //Set borders to white
            var headerCells = checkSummary.Range[1, 1, headerRowIndex, 6];
            headerCells.Borders.LineStyle = ExcelLineStyle.Thin;
            headerCells.Borders[ExcelBordersIndex.DiagonalDown].LineStyle = ExcelLineStyle.None;
            headerCells.Borders[ExcelBordersIndex.DiagonalUp].LineStyle = ExcelLineStyle.None;
            headerCells.Borders.Color = ExcelKnownColors.White;
            headerCells.CellStyle.Font.FontName = XlsIOStandardFormater.DEFAULT_FONT_NAME;

            //Build Rows
            BuildHeaderRow("Test Name", checkSummary);
            BuildReportDateRow(DateTime.Now, checkSummary);
            BuildGeneratedOnRow(DateTime.Now, checkSummary);
            BuildGeneratedByRow("Test User", checkSummary);
            //if (results.HasError)
                BuildErrorBanner(checkSummary);
            BuildTableHeaderRow(checkSummary, headerRowIndex);
            //GenerateSummaryRows(checkSummary, results);
            
            //Build the white border columns
            BuildBorderColumns(checkSummary);

            //Set Row Height
            checkSummary.Rows[headerRowIndex - 1].RowHeight = 30.75;
            checkSummary.Rows[0].RowHeight = 30.0;

            //Set Column Widths
            checkSummary.Columns[0].ColumnWidth = 3.14;
            checkSummary.Columns[1].ColumnWidth = 24.71;
            checkSummary.Columns[2].ColumnWidth = 10;
            checkSummary.Columns[3].ColumnWidth = 10;
            checkSummary.Columns[4].ColumnWidth = 10;
            checkSummary.Columns[5].ColumnWidth = 11.6;

            return checkSummary;
        }

        internal static void BuildHeaderRow(string fundName, Syncfusion.XlsIO.IWorksheet checkSummary)
        {
            checkSummary[1, 2].Value = fundName;
            checkSummary[1, 2].CellStyle.Font.Bold = true;
            checkSummary[1, 2].CellStyle.Font.Size = headerFontSize;
        }

        internal static void BuildReportDateRow(DateTime reportDate, Syncfusion.XlsIO.IWorksheet checkSummary)
        {
            checkSummary[3, 2].Value = "Report Date:";
            checkSummary[3, 2].CellStyle.Font.Bold = true;
            checkSummary[3, 2].CellStyle.Font.Size = defaultFontSize;
            checkSummary[3, 3, 3, 6].Merge();
            checkSummary[3, 3].Value = reportDate.ToShortDateString();
            checkSummary[3, 3].NumberFormat = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
            checkSummary[3, 3].DateTime = reportDate;
            checkSummary[3, 3].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;

        }

        internal static void BuildGeneratedOnRow(DateTime generatedDate, Syncfusion.XlsIO.IWorksheet checkSummary)
        {
            checkSummary[4, 2].Value = "Generated on:";
            checkSummary[4, 2].CellStyle.Font.Bold = true;
            checkSummary[4, 2].CellStyle.Font.Size = defaultFontSize;
            checkSummary[4, 3].Value = DateTime.Now.ToShortDateString();
            //This is the index for the Excel culture aware Date format
            //These are the formats found in the number format tab in excel.
            checkSummary[4, 3].CellStyle.NumberFormatIndex = EXCEL_DATE_FORMAT_INDEX;
            checkSummary[4, 3].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
            checkSummary[4, 4, 4, 6].Merge();
            checkSummary[4, 4].Value = DateTime.Now.ToLongTimeString();
            //This is the index for the Excel culture aware Time format.
            checkSummary[4, 4].CellStyle.NumberFormatIndex = EXCEL_TIME_FORMAT_INDEX;
            checkSummary[4, 4].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;

        }

        internal static void BuildGeneratedByRow(string generatedBy, Syncfusion.XlsIO.IWorksheet checkSummary)
        {
            checkSummary[5, 2].Value = "Generated by:";
            checkSummary[5, 2].CellStyle.Font.Bold = true;
            checkSummary[5, 2].CellStyle.Font.Size = defaultFontSize;
            checkSummary[5, 3, 5, 6].Merge();
            checkSummary[5, 3].Value = generatedBy;
            checkSummary[5, 3].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
        }

        internal static void BuildTableHeaderRow(Syncfusion.XlsIO.IWorksheet checkSummary, int headerRowIndex)
        {
            Syncfusion.XlsIO.IRange headerRow = checkSummary[headerRowIndex, 2, headerRowIndex, 6];
            headerRow.CellStyle.Color = Color.FromArgb(0, 31, 73, 125);
            headerRow.CellStyle.Font.Color = ExcelKnownColors.White;
            headerRow.CellStyle.Font.Size = 11;
            headerRow.CellStyle.Font.Bold = true;
            headerRow.Borders.LineStyle = ExcelLineStyle.None;
            headerRow.VerticalAlignment = ExcelVAlign.VAlignCenter;
            headerRow.HorizontalAlignment = ExcelHAlign.HAlignCenter;

            checkSummary[headerRowIndex, 2].Value = "Workbook Tab";
            checkSummary[headerRowIndex, 2].WrapText = true;
            checkSummary[headerRowIndex, 3].Value = "Passed Checks";
            checkSummary[headerRowIndex, 3].WrapText = true;
            checkSummary[headerRowIndex, 4].Value = "Failed Checks";
            checkSummary[headerRowIndex, 4].WrapText = true;
            checkSummary[headerRowIndex, 5].Value = "Total Checks";
            checkSummary[headerRowIndex, 5].WrapText = true;
            checkSummary[headerRowIndex, 6].Value = "Adjustments Made";
            checkSummary[headerRowIndex, 6].WrapText = true;

        }

        internal static void BuildTotalHeaderRow(Syncfusion.XlsIO.IWorksheet checkSummary, int sheetCount)
        {
            var rowIndex = checkSummary.Rows.Length + 1;

            checkSummary[rowIndex, SUMMARY_SHEET_NAME_INDEX].Value = "Workbook Totals";
            checkSummary[rowIndex, SUMMARY_SHEET_NAME_INDEX].CellStyle.Font.Bold = true;

            checkSummary[rowIndex, SUMMARY_PASS_CHECK_INDEX].Formula = "=SUM(" +
                                                                       GetA1RangeForSummaryTotal(checkSummary,
                                                                           SUMMARY_PASS_CHECK_INDEX, rowIndex + 1, sheetCount) +
                                                                       ")";
            checkSummary[rowIndex, SUMMARY_PASS_CHECK_INDEX].CellStyle.Font.Bold = true;

            string failCheckRange = GetA1RangeForSummaryTotal(checkSummary, SUMMARY_FAIL_CHECK_INDEX, rowIndex + 1, sheetCount);
            checkSummary[rowIndex, SUMMARY_FAIL_CHECK_INDEX].Formula = "=SUM(" + failCheckRange + ")";
            checkSummary[rowIndex, SUMMARY_FAIL_CHECK_INDEX].CellStyle.Font.Bold = true;
            AddFailFormat(checkSummary, failCheckRange);
            AddFailFormat(checkSummary, checkSummary[rowIndex, SUMMARY_FAIL_CHECK_INDEX].AddressLocal);

            checkSummary[rowIndex, SUMMARY_TOTAL_CHECK_INDEX].Formula =
                "=SUM(" + GetA1RangeForSummaryTotal(checkSummary, SUMMARY_TOTAL_CHECK_INDEX, rowIndex + 1, sheetCount) + ")";
            checkSummary[rowIndex, SUMMARY_TOTAL_CHECK_INDEX].CellStyle.Font.Bold = true;

            string adjustmentRange = GetA1RangeForSummaryTotal(checkSummary, SUMMARY_ADJUSTMENT_INDEX, rowIndex + 1, sheetCount);
            checkSummary[rowIndex, SUMMARY_ADJUSTMENT_INDEX].Formula = "=SUM(" + adjustmentRange + ")";
            checkSummary[rowIndex, SUMMARY_ADJUSTMENT_INDEX].CellStyle.Font.Bold = true;
            AddAdjustmentFormat(checkSummary, adjustmentRange);
            AddAdjustmentFormat(checkSummary, checkSummary[rowIndex, SUMMARY_ADJUSTMENT_INDEX].AddressLocal);
        }

        internal static void BuildErrorBanner(Syncfusion.XlsIO.IWorksheet checkSummary)
        {
            Syncfusion.XlsIO.IRange errorTextRange = checkSummary[7, 2, 7, 6];
            errorTextRange.Merge();
            var red = Color.FromArgb(255, 0, 0);
            errorTextRange.CellStyle.Borders[ExcelBordersIndex.EdgeTop].ColorRGB = red;
            errorTextRange.CellStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
            errorTextRange.CellStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = red;
            errorTextRange.CellStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
            errorTextRange.CellStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = red;
            errorTextRange.CellStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
            errorTextRange.HorizontalAlignment = ExcelHAlign.HAlignCenter;
            Syncfusion.XlsIO.IRange hyperLinkRange = checkSummary[8, 2, 8, 6];
            hyperLinkRange.Merge();
            hyperLinkRange.CellStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = red;
            hyperLinkRange.CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
            hyperLinkRange.CellStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = red;
            hyperLinkRange.CellStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
            hyperLinkRange.CellStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = red;
            hyperLinkRange.CellStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
            hyperLinkRange.HorizontalAlignment = ExcelHAlign.HAlignCenter;


            Syncfusion.XlsIO.IRange errorTextCell = checkSummary[7, 2];
            errorTextCell.CellStyle.Font.FontName = XlsIOStandardFormater.DEFAULT_FONT_NAME;
            errorTextCell.CellStyle.Font.Size = 12.0;
            errorTextCell.CellStyle.Font.Bold = true;
            errorTextCell.Text = "! Errors found in this Workbook !";
            errorTextCell.CellStyle.Font.RGBColor = Color.FromArgb(192, 0, 0);

            Syncfusion.XlsIO.IRange hyperLinkCell = checkSummary[8, 2];
            var hyperLink = checkSummary.HyperLinks.Add(hyperLinkCell);
            hyperLink.Type = ExcelHyperLinkType.Workbook;
            hyperLink.Address = "'Sheet3'!A1";
            hyperLink.TextToDisplay = "For more information go to the Error Log";
            hyperLinkCell.CellStyle.Font.FontName = XlsIOStandardFormater.DEFAULT_FONT_NAME;
            hyperLinkCell.CellStyle.Font.Size = 11.0;


        }

        internal static string GetA1RangeForSummaryTotal(Syncfusion.XlsIO.IWorksheet checkSummary, int columnIndex,
            int firstRow, int sheetCount)
        {
            //If we have no summary rows, just return the address of the first cell below
            //the total so we get a 0 value.
            if (sheetCount == 0)
                return checkSummary[firstRow, columnIndex].AddressLocal;
            //Since we are starting at firstRow, this is a 1 based indexing and we need to subract 1
            //from the total rows to get the correct formula.
            return checkSummary[firstRow, columnIndex, firstRow + sheetCount - 1,
                columnIndex].AddressLocal;
        }
        /*
        internal static void GenerateSummaryRows(Syncfusion.XlsIO.IWorksheet summarySheet, IGeneratorResults generatorResults)
        {
            var sheetCount = generatorResults.GetSummarySheets().Count();

            if (summarySheet == null)
                throw new Exception("Cannot create summary table, Workbook has no summary page.");
            BuildTotalHeaderRow(summarySheet, sheetCount);
            AddNullFormat(summarySheet, sheetCount);
            foreach (var sheet in generatorResults.GetSummarySheets())
            {
                GenerateSummaryRow(summarySheet, sheet, generatorResults.GetSummaryRanges(sheet));
            }

        }

        internal static void GenerateSummaryRow(Syncfusion.XlsIO.IWorksheet summarySheet, string sheetName,
            IEnumerable<ISummaryRange> summaryRanges)
        {
            var hyperLinkTarget = summarySheet.Workbook.Worksheets.FirstOrDefault(x => x.Name == sheetName);
            //If the sheet doesn't exist, we don't want to create a summary row for it.
            //This can happen if the sheet had fatal errors.
            if (hyperLinkTarget == null)
                return;

            var newRowIndex = summarySheet.Rows.Length + 1;
            Syncfusion.XlsIO.IRange hyperLinkCell = summarySheet[newRowIndex, SUMMARY_SHEET_NAME_INDEX];
            var hyperLink = summarySheet.HyperLinks.Add(hyperLinkCell);
            hyperLink.Type = ExcelHyperLinkType.Workbook;
            hyperLink.Address = hyperLinkTarget["A1"].AddressGlobal;
            hyperLink.TextToDisplay = sheetName;
            hyperLinkCell.CellStyle.Font.FontName = XlsIOStandardFormater.DEFAULT_FONT_NAME;
            hyperLinkCell.CellStyle.Font.Size = XlsIOStandardFormater.DEFAULT_FONT_SIZE;
            var passCheckFormula = GenerateCheckCountFormula(
                summaryRanges,
                XlsIOCheckColumnGenerator.PASS_TEXT);
            var failCheckFormula = GenerateCheckCountFormula(
                summaryRanges,
                XlsIOCheckColumnGenerator.FAIL_TEXT);

            summarySheet[newRowIndex, SUMMARY_FAIL_CHECK_INDEX].Formula = failCheckFormula;
            summarySheet[newRowIndex, SUMMARY_PASS_CHECK_INDEX].Formula = passCheckFormula;
            if (!string.IsNullOrEmpty(passCheckFormula) && !string.IsNullOrEmpty(failCheckFormula))
                summarySheet[newRowIndex, SUMMARY_TOTAL_CHECK_INDEX].Formula = "=SUM(" +
                                                                               summarySheet[newRowIndex,
                                                                                       SUMMARY_FAIL_CHECK_INDEX]
                                                                                   .AddressLocal +
                                                                               ":" +
                                                                               summarySheet[newRowIndex,
                                                                                       SUMMARY_PASS_CHECK_INDEX]
                                                                                   .AddressLocal +
                                                                               ")";
            summarySheet[newRowIndex, SUMMARY_ADJUSTMENT_INDEX].Formula =
                GenerateAdjustmentCountFormula(summaryRanges);
        }
        */
        internal static void BuildBorderColumns(Syncfusion.XlsIO.IWorksheet checkSummary)
        {
            //Set left & right borders
            var leftEdgeRange = checkSummary[1, 1, checkSummary.Rows.Length - 1, 1];
            var rightEdgeRange = checkSummary[1, 7, checkSummary.Rows.Length - 1, 7];
            XlsIOStandardFormater.SetBordersForCell(leftEdgeRange, Color.White, ExcelLineStyle.Thin);
            XlsIOStandardFormater.SetBordersForCell(rightEdgeRange, Color.White, ExcelLineStyle.Thin);
            leftEdgeRange.CellStyle.Color = Color.White;
            rightEdgeRange.CellStyle.Color = Color.White;

        }
        /*
        internal static string GenerateCheckCountFormula(IEnumerable<ISummaryRange> ranges, string compareText)
        {
            var checkCounter = new StringBuilder("=");
            foreach (var range in ranges.Where(x => x.RangeType == SummaryRangeType.Check))
            {
                checkCounter.Append("+COUNTIF(");
                checkCounter.Append(range.Range);
                checkCounter.Append(",\"" + compareText + "\")");
            }

            if (checkCounter.Length == 1)
                return string.Empty;
            return checkCounter.ToString();
        }
        
        internal static string GenerateAdjustmentCountFormula(IEnumerable<ISummaryRange> ranges)
        {
            var adjustmentRanges = ranges.Where(x => x.RangeType == SummaryRangeType.Adjustment);
            if (!adjustmentRanges.Any())
                return string.Empty;
            var adjustmentCount = new StringBuilder("=");
            foreach (var range in adjustmentRanges)
            {
                //We have to compare <0 and >0 separately in the formula here
                // < or > by has an implicit type check to ensure values are numbers
                //if we used <>0 (not equal) there is no implicit type check so this 
                //would also return things like empty cells ("") or header cells.
                adjustmentCount.Append("+COUNTIFS(");
                adjustmentCount.Append(range.Range);
                adjustmentCount.Append(",\"<0\",");
                adjustmentCount.Append(range.FingerPrintRange);
                adjustmentCount.Append(",\"*\")");
                adjustmentCount.Append("+COUNTIFS(");
                adjustmentCount.Append(range.Range);
                adjustmentCount.Append(",\">0\",");
                adjustmentCount.Append(range.FingerPrintRange);
                adjustmentCount.Append(",\"*\")");
            }

            return adjustmentCount.ToString();
        }
        */
        internal static void AddNullFormat(Syncfusion.XlsIO.IWorksheet summarySheet, int sheetCount)
        {

            //If we have no summary sheets - there is no range to set conditional formatting for.
            if (sheetCount == 0)
                return;
            var firstRow = summarySheet.Rows.Count() + 1;
            var lastRow = firstRow + sheetCount - 1;
            Syncfusion.XlsIO.IRange range =
                summarySheet[firstRow, SUMMARY_PASS_CHECK_INDEX, lastRow, SUMMARY_ADJUSTMENT_INDEX];
            IConditionalFormats conditionalFormats = range.ConditionalFormats;
            IConditionalFormat conditionalFormat = conditionalFormats.AddCondition();
            conditionalFormat.FormatType = ExcelCFType.Blank;
            conditionalFormat.FillPattern = ExcelPattern.ThinDiagonalStripe;

            var color = Color.FromArgb(217, 217, 217);
            conditionalFormat.ColorRGB = color;
            XlsIOStandardFormater.SetConditionalFormatBorders(conditionalFormat, color);
        }

        internal static void AddFailFormat(Syncfusion.XlsIO.IWorksheet summarySheet, string rangeString)
        {
            Syncfusion.XlsIO.IRange range = summarySheet[rangeString];

            IConditionalFormats conditionalFormats = range.ConditionalFormats;
            IConditionalFormat conditionalFormat = conditionalFormats.AddCondition();
            conditionalFormat.FirstFormula = "0";
            conditionalFormat.FormatType = ExcelCFType.CellValue;
            conditionalFormat.Operator = ExcelComparisonOperator.Greater;
            conditionalFormat.BackColorRGB = Color.FromArgb(192, 80, 77);
            conditionalFormat.FontColorRGB = Color.FromArgb(242, 220, 219);
            conditionalFormat.IsBold = true;

            XlsIOStandardFormater.SetConditionalFormatBorders(conditionalFormat, Color.FromArgb(192, 0, 0));

        }

        internal static void AddAdjustmentFormat(Syncfusion.XlsIO.IWorksheet summarySheet, string rangeString)
        {
            Syncfusion.XlsIO.IRange range = summarySheet[rangeString];
            IConditionalFormats conditionalFormats = range.ConditionalFormats;
            IConditionalFormat conditionalFormat = conditionalFormats.AddCondition();
            conditionalFormat.FirstFormula = "0";
            conditionalFormat.FormatType = ExcelCFType.CellValue;
            conditionalFormat.Operator = ExcelComparisonOperator.Greater;
            conditionalFormat.BackColorRGB = Color.FromArgb(220, 230, 241);
            conditionalFormat.IsBold = true;
            XlsIOStandardFormater.SetConditionalFormatBorders(conditionalFormat, Color.FromArgb(0, 176, 240));

        }



    }
}
