using AsbCloudApp.Data;
using AsbCloudApp.Exceptions;
using AsbCloudApp.Services;
using AsbCloudDb.Model;
using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using AsbCloudApp.Data.WellOperation;

namespace AsbCloudInfrastructure.Services.WellOperationService;

public class ScheduleReportService : IScheduleReportService
{
    private readonly IWellOperationService wellOperationService;
    private readonly IWellService wellService;
    const string sheetNameSchedule = "Сетевой график";
    const string sheetNameSchedulePlan = "План";
    const string sheetNameScheduleFact = "Факт";
    const string sheetNameSchedulePrediction = "Прогноз";
    const string sheetNameTvd = "ГГД";
    const int maxChartsToWrap = 88;

    public ScheduleReportService(IWellOperationService wellOperationService, IWellService wellService)
    {
        this.wellOperationService = wellOperationService;
        this.wellService = wellService;
    }

    public async Task<Stream> MakeReportAsync(int idWell, CancellationToken token = default)
    {
        var tvd = await wellOperationService.GetTvdAsync(idWell, token);

        var well = await wellService.GetOrDefaultAsync(idWell, token)
            ?? throw new ArgumentInvalidException(nameof(idWell), "idWell doesn`t exist");

        var ecxelTemplateStream = GetExcelTemplateStream();
        using var workbook = new XLWorkbook(ecxelTemplateStream);
        FillScheduleSheetToWorkbook(workbook, tvd, well);
        FillTvdSheetToWorkbook(workbook, tvd, well);
        MemoryStream memoryStream = new MemoryStream();
        workbook.SaveAs(memoryStream, new SaveOptions { });
        memoryStream.Seek(0, SeekOrigin.Begin);
        return memoryStream;
    }

    private static void FillScheduleSheetToWorkbook(XLWorkbook workbook, IEnumerable<PlanFactPredictBase<WellOperationDto>> tvd, WellDto well)
    {
        FillScheduleSheet(workbook, tvd, well);

        var tvdList = tvd.ToList();
        var plans = tvd
            .Where(t => t.Plan is not null)
            .Select(t => t.Plan!)
            .OrderBy(t => t.DateStart);
        FillCurrentScheduleSheet(workbook, plans, sheetNameSchedulePlan);

        var facts = tvd
           .Where(t => t.Fact is not null)
           .Select(t => t.Fact!)
           .OrderBy(t => t.DateStart);
        FillCurrentScheduleSheet(workbook, facts, sheetNameScheduleFact);

        var predictions = tvd
           .Where(t => t.Predict is not null)
           .Select(t => t.Predict!)
           .OrderBy(t => t.DateStart);
        FillCurrentScheduleSheet(workbook, predictions, sheetNameSchedulePrediction);
    }

    private static void FillCurrentScheduleSheet(XLWorkbook workbook, IEnumerable<WellOperationDto> tvdList, string sheetName)
    {
        var sheet = workbook.GetWorksheet(sheetName);

        const int headerRowsCount = 6;

        const int columnRowNumber = 2;
        const int columnCaption = 3;
        const int columnWellDepthStart = 4;
        const int columnWellDepthEnd = 5;
        const int columnDuration = 6;
        const int columnDateStart = 7;
        const int columnDateEnd = 8;

        int i = 1;
        foreach (var tvdItem in tvdList)
        {
            var row = sheet.Row(i + headerRowsCount);
            SetCell(row, columnRowNumber, $"{i}");
            SetCell(row, columnCaption, $"{tvdItem.OperationCategoryName} {tvdItem.CategoryInfo}".Trim());
            SetCell(row, columnWellDepthStart, tvdItem.DepthStart);
            SetCell(row, columnWellDepthEnd, tvdItem.DepthEnd);
            SetCell(row, columnDuration, tvdItem.DurationHours);
            SetCell(row, columnDateStart, tvdItem.DateStart);
            SetCell(row, columnDateEnd, tvdItem.DateStart.AddHours(tvdItem.DurationHours));
            i++;
        }
    }

    private static void FillScheduleSheet(XLWorkbook workbook, IEnumerable<PlanFactPredictBase<WellOperationDto>> tvd, WellDto well)
    {
        var sheet = workbook.GetWorksheet(sheetNameSchedule);

        const int headerRowsCount = 6;
        const int rowTitle = 3;

        const int columnRowNumber = 2;
        const int columnCaption = 3;
        const int columnWellDepthStartPlan = 4;
        const int columnWellDepthStartFact = 5;
        const int columnWellDepthStartPredict = 6;
        const int columnWellDepthEndPlan = 7;
        const int columnWellDepthEndFact = 8;
        const int columnWellDepthEndPredict = 9;
        const int columnDeltaWellDepthPerDay = 10;
        const int columnDurationPlan = 11;
        const int columnDurationFact = 12;
        const int columnDurationPredict = 13;
        const int columnDateStartPlan = 14;
        const int columnDateStartFact = 15;
        const int columnDateStartPredict = 16;
        const int columnDateEndPlan = 17;
        const int columnDateEndFact = 18;
        const int columnDateEndPredict = 19;
        const int columnGuilty = 20;
        const int columnNpt = 21;

        var subTitle = $"на строительство скважины №{well.Caption}, куст: {well.Cluster}, м/р: {well.Deposit}";
        sheet.Row(rowTitle).Cell(3).SetCellValue(subTitle);

        var tvdList = tvd.ToList();
        var facts = tvd
            .Where(t => t.Fact is not null)
            .Select(t => t.Fact!)
            .ToList();

        DateTimeOffset lastFactDate = default;
        var lastFactI = 0;

        int i = 0;
        for (; i < tvdList.Count; i++)
        {
            var tvdItem = tvdList[i];
            var operation = tvdItem.Fact ?? tvdItem.Plan;
            if (operation is null)
                continue;

            var row = sheet.Row(1 + i + headerRowsCount);

            SetCell(row, columnRowNumber, $"{1 + i}");
            SetCell(row, columnCaption, $"{operation.OperationCategoryName} {operation.CategoryInfo}".Trim());

            SetCell(row, columnWellDepthStartPlan, tvdItem.Plan?.DepthStart);
            SetCell(row, columnWellDepthStartFact, tvdItem.Fact?.DepthStart);
            SetCell(row, columnWellDepthStartPredict, tvdItem.Predict?.DepthStart);

            SetCell(row, columnWellDepthEndPlan, tvdItem.Plan?.DepthEnd);
            SetCell(row, columnWellDepthEndFact, tvdItem.Fact?.DepthEnd);
            SetCell(row, columnWellDepthEndPredict, tvdItem.Predict?.DepthEnd);

            SetCell(row, columnDeltaWellDepthPerDay, null);
            if (tvdItem.Fact is not null)
            {
                var fact = tvdItem.Fact;
                if (lastFactDate == default)
                    lastFactDate = fact.DateStart;

                if (i > 0 && fact.DateStart.DayOfYear != lastFactDate.DayOfYear)
                {
                    var daylyOperations = facts
                        .Where(t => t.DateStart >= lastFactDate && t.DateStart < fact.DateStart);
                    if (daylyOperations.Any())
                    {
                        var depthDayStart = daylyOperations.Min(o => o.DepthStart);
                        var depthDayEnd = daylyOperations.Max(o => o.DepthEnd);
                        var delta = depthDayEnd - depthDayStart;
                        SetCell(sheet.Row(1 + lastFactI + headerRowsCount), columnDeltaWellDepthPerDay, delta);
                        lastFactDate = fact.DateStart;
                    }
                }
                lastFactI = i;
            }

            SetCell(row, columnDurationPlan, tvdItem.Plan?.DurationHours);
            SetCell(row, columnDurationFact, tvdItem.Fact?.DurationHours);
            SetCell(row, columnDurationPredict, tvdItem.Predict?.DurationHours);

            SetCell(row, columnDateStartPlan, tvdItem.Plan?.DateStart);
            SetCell(row, columnDateStartFact, tvdItem.Fact?.DateStart);
            SetCell(row, columnDateStartPredict, tvdItem.Predict?.DateStart);

            SetCell(row, columnDateEndPlan, tvdItem.Plan?.DateStart.AddHours(tvdItem.Plan?.DurationHours ?? 0));
            SetCell(row, columnDateEndFact, tvdItem.Fact?.DateStart.AddHours(tvdItem.Fact?.DurationHours ?? 0));
            SetCell(row, columnDateEndPredict, tvdItem.Predict?.DateStart.AddHours(tvdItem.Predict?.DurationHours ?? 0));

            if (tvdItem.Fact is not null && WellOperationCategory.NonProductiveTimeSubIds.Contains(tvdItem.Fact.IdCategory))
            {
                SetCell(row, columnGuilty, tvdItem.Fact.Comment);
                SetCell(row, columnNpt, tvdItem.Fact.DurationHours);
                row.Row(columnRowNumber, columnNpt).Style.Fill.BackgroundColor = XLColor.Red;
            }
            else
            {
                SetCell(row, columnGuilty, null);
                SetCell(row, columnNpt, null);
            }
        }

        var rowNumSummary = 1 + i + headerRowsCount;
        var rowNumStart = 1 + headerRowsCount;
        var rowNumEnd = i + headerRowsCount;

        string MakeRangeFunction(string funcName, int column)
            => $"={funcName}({GetColunmLetter(column)}{rowNumStart}:{GetColunmLetter(column)}{rowNumEnd})";

        IXLCell AddRangeFormula(IXLRow row, string funcName, int column)
        {
            var cell = row.Cell(column);
            cell.FormulaA1 = MakeRangeFunction(funcName, column);
            return cell;
        }

        var rowSummary = sheet.Row(rowNumSummary);
        rowSummary.Style.Font.Bold = true;
        rowSummary.Cell(columnCaption).SetCellValue("Итого:");

        AddRangeFormula(rowSummary, "sum", columnDeltaWellDepthPerDay);
        AddRangeFormula(rowSummary, "sum", columnDurationPlan);
        AddRangeFormula(rowSummary, "sum", columnDurationFact);
        var cell = AddRangeFormula(rowSummary, "max", columnDateEndPlan);
        SetDateTime(cell);
        cell = AddRangeFormula(rowSummary, "max", columnDateEndFact);
        SetDateTime(cell);
        AddRangeFormula(rowSummary, "sum", columnNpt);
        SetBorder(rowSummary.Cells(true).Style);

        var rowSummary2 = sheet.Row(rowNumSummary + 1);
        rowSummary2.Style.NumberFormat.Format = "0,00";
        rowSummary2.Cell(columnCaption).SetCellValue("в сутках:");
        rowSummary2.Cell(columnDurationPlan).FormulaA1 = $"={GetColunmLetter(columnDurationPlan)}{rowNumSummary}/24";
        SetNumber(rowSummary2.Cell(columnDurationPlan));
        rowSummary2.Cell(columnDurationFact).FormulaA1 = $"={GetColunmLetter(columnDurationFact)}{rowNumSummary}/24";
        SetNumber(rowSummary2.Cell(columnDurationFact));
        rowSummary2.Cell(columnNpt).FormulaA1 = $"={GetColunmLetter(columnNpt)}{rowNumSummary}/24";
        SetNumber(rowSummary2.Cell(columnNpt));
        SetBorder(rowSummary2.Cells(true).Style);
    }

    private static void FillTvdSheetToWorkbook(XLWorkbook workbook, IEnumerable<PlanFactPredictBase<WellOperationDto>> tvd, WellDto well)
    {
        var sheet = workbook.GetWorksheet(sheetNameTvd);

        const int rowTitle = 2;
        const int rowSubtitle = 3;
        const int colTitle = 5;

        const int rowTopStatTitle = 2;
        const int colTopStatvalue = 10;

        const int colBottomStatvalue = 3;
        const int rowStartDateFact = 43;
        const int rowEndDatePlan = 44;
        const int rowEndDateFact = 45;

        sheet.Row(rowSubtitle).Cell(colTitle).Value
            = $"скважины №{well.Caption}, куст: {well.Cluster}, м/р: {well.Deposit}";

        SetCell(sheet.Row(rowTitle), colTopStatvalue, DateTime.Now);

        var Plan = tvd.Where(t => t.Plan is not null)
            .Select(t => t.Plan);
        var Fact = tvd.Where(t => t.Fact is not null)
            .Select(t => t.Fact);
        var Predict = tvd.Where(t => t.Predict is not null)
            .Select(t => t.Predict);

        var startDateFact = Fact.FirstOrDefault()?.DateStart;
        var planLast = Plan.LastOrDefault();
        var factLast = Fact.LastOrDefault();
        var predictLast = Predict.LastOrDefault();

        static DateTime GetEndDate(WellOperationDto operation)
            => operation is not null
            ? operation.DateStart.Date.AddHours(operation.DurationHours)
            : default;

        var endDatePlan = planLast is not null ? GetEndDate(planLast) : default;
        var endDateFact = factLast is not null ? GetEndDate(factLast) : default;
        var endDatePredict = predictLast is not null ? GetEndDate(predictLast) : default;

        var endDate = endDatePredict > endDateFact
            ? endDatePredict
            : endDateFact;

        if (startDateFact is not null)
        {
            SetCell(sheet.Row(rowStartDateFact), colBottomStatvalue, startDateFact);
            SetCell(sheet.Row(rowEndDatePlan), colBottomStatvalue, endDatePlan);
            SetCell(sheet.Row(rowEndDateFact), colBottomStatvalue, endDate);
            if (endDate != default)
            {
                var deltaEndDate = (endDatePlan - endDate).TotalDays;
                SetCell(sheet.Row(rowTopStatTitle + 1), colTopStatvalue, Math.Abs(deltaEndDate));
                if (deltaEndDate >= 0)
                    SetCell(sheet.Row(rowTopStatTitle + 1), colTopStatvalue - 1, "+")
                        .Style.Font.SetFontColor(XLColor.Green);
                else
                    SetCell(sheet.Row(rowTopStatTitle + 1), colTopStatvalue - 1, "-")
                            .Style.Font.SetFontColor(XLColor.Red);
            }
        }
    }

    private static string GetColunmLetter(int columnNumber)
    {
        string letter = "";

        while (columnNumber > 0)
        {
            int modulo = (columnNumber - 1) % 26;
            letter = Convert.ToChar('A' + modulo) + letter;
            columnNumber = (columnNumber - modulo) / 26;
        }

        return letter;
    }

    private static IXLStyle SetBorder(IXLStyle style)
    {
        style.Border.RightBorder = XLBorderStyleValues.Thin;
        style.Border.LeftBorder = XLBorderStyleValues.Thin;
        style.Border.TopBorder = XLBorderStyleValues.Thin;
        style.Border.BottomBorder = XLBorderStyleValues.Thin;
        style.Border.InsideBorder = XLBorderStyleValues.Thin;
        return style;
    }

    private static IXLCell SetDateTime(IXLCell cell)
    {
        cell.Style.DateFormat.Format = "DD.MM.YYYY HH:MM:SS";
        return cell;
    }

    private static IXLCell SetNumber(IXLCell cell)
    {
        cell.Style.NumberFormat.Format = "0.00";
        return cell;
    }

    private static IXLCell SetCell(IXLRow row, int colunm, object? value)
    {
        var cell = row.Cell(colunm);
        cell.SetCellValue(value);

        SetBorder(cell.Style);
        cell.Style.Alignment.WrapText = true;

        if (value is string valueString && valueString.Length > maxChartsToWrap)
        {
            var baseHeight = row.Height;
            row.Height = 0.82d * baseHeight * Math.Ceiling(1d + valueString.Length / maxChartsToWrap);
        }

        if (value is DateTime)
        {
            SetDateTime(cell);
        }
        else if (value is IFormattable)
        {
            SetNumber(cell);
        }

        return cell;
    }

    private static Stream GetExcelTemplateStream()
    {
        var stream = System.Reflection.Assembly.GetExecutingAssembly()
            .GetManifestResourceStream("AsbCloudInfrastructure.Services.WellOperationService.ScheduleReportTemplate.xlsx")!;
        return stream;
    }
}