using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using AccountingCounters2.Database;
using AccountingСounters.Database;
using ClosedXML.Excel;
using Data2.Enums;
using Data2.Extensions;
using Data2.Models;

namespace AccountingCounters2.Helpers.ExcelHelpers
{
    public static class IndicationsReportGenerator
    {
        public static void GenerateReport(FileStream fileStream, MaintainedFacility Facility)
        {
            var workbook = new XLWorkbook(XLEventTracking.Disabled);
            var worksheet = workbook.AddWorksheet("Indications");

            worksheet.Protect("132") // On this sheet we will only allow:
                .SetFormatCells() // Cell Formatting
                .SetInsertColumns() // Inserting Columns
                .SetDeleteColumns() // Deleting Columns
                .SetDeleteRows(); // Deleting Rows
            worksheet.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
            worksheet.Style.Font.FontFamilyNumbering = XLFontFamilyNumberingValues.Roman;
            worksheet.Style.Font.FontSize = 11;
            worksheet.Style.Fill.BackgroundColor = XLColor.White;

            worksheet.Column(1).Style.Font.FontColor = XLColor.White;
            var cell = worksheet.Cell(1, 2);
            var range = worksheet.Range("B1:E1");
            var row = worksheet.Row(1);
            worksheet.Cell(1,1).Value = Facility.ID;

            //Загловок
            range.Merge();
            cell.Value = "ПОКАЗАНИЯ ПРИБОРОВ УЧЕТА";
            cell.Style.Font.FontSize = 16;
            cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            range.Style.Border.BottomBorder = XLBorderStyleValues.Double;

            //Название объекта
            row = row.RowBelow();
            cell = row.Cell(2);
            range = worksheet.Range(2, 2, 2, 3);
            range.Merge();
            cell.Value = "Наименование объекта:";
            range = worksheet.Range(row.Cell(2), row.Cell(5));
            range.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
            cell.Style.Font.FontSize = 12;
            cell = row.Cell(4);
            cell.Value = Facility.Name;

            //Адрес объекта
            row = row.RowBelow();
            cell = row.Cell(2);
            cell.Value = "Адрес:";
            cell.Style.Font.FontSize = 12;
            cell = row.Cell(4);
            cell.Value = Facility.Address;
            range = worksheet.Range(row.Cell(2), row.Cell(5));
            range.Style.Border.BottomBorder = XLBorderStyleValues.Thin;

            //Дата
            row = row.RowBelow();
            range = worksheet.Range(row.Cell(2), row.Cell(3));
            range.Merge();
            cell = row.Cell(2);
            cell.Value = "Показания приборов за:";
            cell.Style.Font.FontSize = 12;
            cell = row.Cell(4);
            cell.Value = DateTime.Now.Date;
            cell.Style.Font.FontSize = 12;
            //cell.Style.NumberFormat.NumberFormatId = 14;
            cell.Style.NumberFormat.Format = "mmmm yyyy";
            cell.UnlockCell();
            range = worksheet.Range(row.Cell(2), row.Cell(5));
            range.Style.Border.BottomBorder = XLBorderStyleValues.Double;

            //Шапка таблицы
            row = row.RowBelow().RowBelow();
            row.Height = 30;
            range = worksheet.Range(row.Cell(2), row.Cell(5));
            range.Style.Fill.BackgroundColor = XLColor.Gray;
            range.SetAllBorders(XLBorderStyleValues.Thin);
            //range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
            //range.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
            range.Style.Font.FontColor = XLColor.White;
            row.Cell(2).Value = "ТИП УЗЛА";
            row.Cell(3).Value = "МОДЕЛЬ ПРИБОРА";
            row.Cell(4).Value = "НОМЕР";
            row.Cell(5).Value = "ПОКАЗАНИЯ";
            range.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
            range.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
            //int startingrow = row.RowNumber();

            if (!ContextFactory.Instance.Entry(Facility).Collection(a => a.AccountingNodes).IsLoaded)
            {
                ContextFactory.Instance.Entry(Facility).Collection(a => a.AccountingNodes).Load();
            }
            foreach (AccountingNode node in Facility.AccountingNodes.Where(n => n.DeviceGroupID != null))
            {
                if (!ContextFactory.Instance.Entry(node).Reference(a => a.DeviceGroup).IsLoaded)
                {
                    ContextFactory.Instance.Entry(node).Reference(a => a.DeviceGroup).Load();
                }
                row = worksheet.LastRowUsed().RowBelow();
                row.Cell(1).InsertData(GetNodeData(node));
                range = worksheet.Range(row.Cell(2), worksheet.LastRowUsed().Cell(5));
                range.CenterText();
                range.SetAllBorders(XLBorderStyleValues.Thin);
                switch (node.DeviceGroup.DeviceGroupType)
                {
                    case DeviceGroupType.ColdWater:
                    case DeviceGroupType.HotWater:
                    case DeviceGroupType.Electricity:
                        row.Cell(5).UnlockCell();
                        break;
                    case DeviceGroupType.HotWaterCirculation:
                        row.Cell(5).UnlockCell();
                        row.RowBelow().Cell(5).UnlockCell();
                        break;
                    case DeviceGroupType.HotWaterDigital:
                        row.RowBelow().Cell(5).UnlockCell();
                        row.RowBelow().Cell(4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
                        row.RowBelow().RowBelow().Cell(5).UnlockCell();
                        row.RowBelow().RowBelow().Cell(4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
                        break;
                    case DeviceGroupType.HeatEnergy:
                    case DeviceGroupType.HotWaterDigitalCirculation:
                        row.RowBelow().Cell(5).UnlockCell();
                        row.RowBelow().Cell(4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
                        row.RowBelow().RowBelow().Cell(5).UnlockCell();
                        row.RowBelow().RowBelow().Cell(4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
                        row.RowBelow().RowBelow().RowBelow().Cell(5).UnlockCell();
                        row.RowBelow().RowBelow().RowBelow().Cell(4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
                        break;
                }
                range = worksheet.Range(worksheet.LastRowUsed().Cell(2), worksheet.LastRowUsed().Cell(5));
                range.Style.Border.BottomBorder = XLBorderStyleValues.Medium;
            }

            //Дата подачи ФИО подавшего показания
            row = worksheet.LastRowUsed().RowBelow().RowBelow();
            cell = row.Cell(2);
            cell.Value = "дата подачи:";
            cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
            cell = row.Cell(3);
            cell.Style.NumberFormat.NumberFormatId = 14;
            cell.Value = DateTime.Now.Date;
            cell.UnlockCell();
            cell = row.Cell(4);
            cell.Value = "сведения подал Ф.И.О.";
            cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
            cell = row.Cell(5);
            cell.UnlockCell();

            //Окончательное форматирование
            worksheet.Rows(1, worksheet.LastRowUsed().RowNumber()).Height = 21;
            worksheet.Rows(1, worksheet.LastRowUsed().RowNumber()).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
            worksheet.Column(1).Width = 2;
            worksheet.Column(1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
            worksheet.Column(2).Width = 12;
            worksheet.Column(3).Width = 18;
            worksheet.Columns(4, 5).Width = 25;
            //range = worksheet.Range(startingrow, 2, worksheet.LastRowUsed().RowNumber(), worksheet.LastColumnUsed().ColumnNumber());
            //range.SetAllBorders(XLBorderStyleValues.Thin);

            workbook.SaveAs(fileStream);
        }

        public static DataTable GetNodeData(AccountingNode node)
        {
            DataTable table = new DataTable();
            table.Columns.Add("ID", typeof(string));
            table.Columns.Add("ТИП УЗЛА", typeof(string));
            table.Columns.Add("МОДЕЛЬ ПРИБОРА", typeof(string));
            table.Columns.Add("НОМЕР", typeof(string));
            table.Columns.Add("ПОКАЗАНИЯ", typeof(int));
            ConsumptionCounter Device;
            ConsumptionCounter Device2;
            var number = "";
            switch (node.DeviceGroup.DeviceGroupType)
            {
                    //Device = node.DeviceGroup.ConsumptionCounters.Single(c => c.Type == CounterType.ComputationalModule);
                    //table.Rows.Add(Device.ID, node.TypeShortName, Device.Model, Device.Number);
                    //table.Rows.Add(Device.ID, "", "", "Q, Гкл:");
                    //table.Rows.Add(Device.ID, "", "", "V1, куб.м");
                    //table.Rows.Add(Device.ID, "", "", "V2, куб.м");
                    //break;
                case DeviceGroupType.ColdWater:
                    Device = node.DeviceGroup.ConsumptionCounters.Single(c => c.Type == CounterType.ColdWater);
                    table.Rows.Add(node.DeviceGroupID, node.TypeShortName, Device.Model, Device.Number);
                    break;
                case DeviceGroupType.HotWater:
                    Device = node.DeviceGroup.ConsumptionCounters.Single(c => c.Type == CounterType.HotWater);
                    table.Rows.Add(node.DeviceGroupID, node.TypeShortName, Device.Model, Device.Number);
                    break;
                case DeviceGroupType.HotWaterCirculation:
                    Device = node.DeviceGroup.ConsumptionCounters.Single(c => c.Type == CounterType.HotWater && c.SubType == CounterSubType.Suply);
                    table.Rows.Add(node.DeviceGroupID, node.TypeShortName, Device.Model, Device.Number);
                    Device2 = node.DeviceGroup.ConsumptionCounters.Single(c => c.Type == CounterType.HotWater && c.SubType == CounterSubType.Return);
                    table.Rows.Add(node.DeviceGroupID, node.TypeShortName, Device2.Model, Device2.Number);
                    break;
                case DeviceGroupType.HotWaterDigital:
                    //Device = node.DeviceGroup.ConsumptionCounters.Single(c => c.Type == CounterType.ComputationalModule);
                    Device = node.DeviceGroup.ComputationalModule;
                    number = node.DeviceGroup.ComputationalModuleInput != string.Empty ? $"{Device.Number}-{node.DeviceGroup.ComputationalModuleInput}" : Device.Number;
                    table.Rows.Add(node.DeviceGroupID, node.TypeShortName, Device.Model, number);
                    //table.Rows.Add(node.DeviceGroupID, node.TypeShortName, Device.Model, Device.Number);
                    table.Rows.Add(node.DeviceGroupID, "", "", "Q, Гкл:");
                    table.Rows.Add(node.DeviceGroupID, "", "", "V, куб.м");
                    break;
                case DeviceGroupType.HeatEnergy:
                    //Device = node.DeviceGroup.ConsumptionCounters.Single(c => c.Type == CounterType.ComputationalModule);
                    Device = node.DeviceGroup.ComputationalModule;
                    number = node.DeviceGroup.ComputationalModuleInput != string.Empty ? $"{Device.Number}-{node.DeviceGroup.ComputationalModuleInput}" : Device.Number;
                    table.Rows.Add(node.DeviceGroupID, node.TypeShortName, Device.Model, number);
                    //table.Rows.Add(node.DeviceGroupID, node.TypeShortName, Device.Model, Device.Number);
                    table.Rows.Add(node.DeviceGroupID, "", "", "Q, Гкл:");
                    table.Rows.Add(node.DeviceGroupID, "", "", "M1, куб.м");
                    table.Rows.Add(node.DeviceGroupID, "", "", "M2, куб.м");
                    break;
                case DeviceGroupType.HotWaterDigitalCirculation:
                    //Device = node.DeviceGroup.ConsumptionCounters.Single(c => c.Type == CounterType.ComputationalModule);
                    Device = node.DeviceGroup.ComputationalModule;
                    number = node.DeviceGroup.ComputationalModuleInput != string.Empty ? $"{Device.Number}-{node.DeviceGroup.ComputationalModuleInput}" : Device.Number;
                    table.Rows.Add(node.DeviceGroupID, node.TypeShortName, Device.Model, number);
                    //table.Rows.Add(node.DeviceGroupID, node.TypeShortName, Device.Model, Device.Number);
                    table.Rows.Add(node.DeviceGroupID, "", "", "Q, Гкл:");
                    table.Rows.Add(node.DeviceGroupID, "", "", "V1, куб.м");
                    table.Rows.Add(node.DeviceGroupID, "", "", "V2, куб.м");
                    break;
                case DeviceGroupType.Electricity:
                    Device = node.DeviceGroup.ConsumptionCounters.Single(c => c.Type == CounterType.Electricity);
                    table.Rows.Add(node.DeviceGroupID, node.TypeShortName, Device.Model, Device.Number);
                    break;
                default:
                    throw new ArgumentOutOfRangeException();
            }

            return table;
        }
    }
}