using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Fast.Shared.Logic.FileService;
using Fast.Shared.Logic.Package;

namespace Fast.Web.Logic;

public class ReportRunner
{
    private readonly MyDbContext db;
    private readonly FileService fileService;

    public ReportRunner(MyDbContext context, string contentRootPath)
    {
        db = context;
        var config = new FileServiceConfig(contentRootPath);
        fileService = new FileService(config);
    }

    public class StreamAndFileName(Stream stream, string fileName)
    {
        public Stream Stream { get; } = stream;
        public string FileName { get; } = fileName;
    }

    public async Task<StreamAndFileName> Run(int reportId, int filterId, int userInfoId, bool saveAsPdf)
    {
        var reportInfo = db.Reports
            .Where(x => x.Id == reportId)
            .Select(x => new { x.Name, x.TemplateFileNameOnDisk, x.TemplateFileNameOriginal })
            .First();

        string ReportLegalName = Util.String.GetLegalFileName(reportInfo.Name);

        var fileNameOnDisk = reportInfo.TemplateFileNameOnDisk;
        var fileNameOriginal = string.IsNullOrWhiteSpace(reportInfo.TemplateFileNameOriginal)
            ? ReportLegalName
            : reportInfo.TemplateFileNameOriginal;

        string fileExtension;
        byte[] workbookData;

        var fileResponse = await fileService.DownloadFileAsync("ReportTemplates", fileNameOnDisk);

        using var ms = new MemoryStream();
        if (fileResponse.Success)
        {
            using (var stream = fileResponse.Stream)
                await stream.CopyToAsync(ms);
            ms.Position = 0;
        }

        using (var wb = fileResponse.Success ? SpreadsheetDocument.Open(ms, true) : SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
        {
            if (!fileResponse.Success)
                OpenXmlHelper.InitializeUniversalStylesheet(wb);

            var templateLoadFailed = !fileResponse.Success;
            await FillReportAsync(wb, reportId, filterId, userInfoId, templateLoadFailed);
        }

        workbookData = ms.ToArray();
        fileExtension = fileResponse.Success ? Path.GetExtension(fileNameOriginal) : ".xlsx";

        var newFileNameWithoutExtension = $"{ReportLegalName} {DateTime.Now:yyyy-MM-dd@HH-mm-ss}";

        var newStream = new MemoryStream();
        newStream.Write(workbookData, 0, workbookData.Length);
        newStream.Position = 0;

        OpenXmlHelper.RemoveCalculationChain(newStream);
        newStream.Position = 0;

        if (saveAsPdf)
        {
            using (newStream)
            {
                var pdfStream = await PdfConverter.ConvertToStream(newStream, $"{newFileNameWithoutExtension}{fileExtension}");
                return new StreamAndFileName(pdfStream, $"{newFileNameWithoutExtension}.pdf");
            }
        }

        return new StreamAndFileName(newStream, $"{newFileNameWithoutExtension}{fileExtension}");
    }

    private async Task FillReportAsync(SpreadsheetDocument wb, int reportId, int filterId, int userInfoId, bool templateLoadFailed)
    {
        var report = (
            from q in db.Reports
            .Include(x => x.ReportFilters).ThenInclude(x => x.ReportFilterParameters).ThenInclude(x => x.DataSource)
            join f in db.ReportFilters on
                new { ReportId = q.Id, FilterId = filterId }
                equals
                new { f.ReportId, FilterId = f.Id } into g
            from f in g.DefaultIfEmpty()
            where q.Id == reportId
            select new
            {
                q.Name,
                FilterName = f.Name,
                FilterParams = f.ReportFilterParameters,
                q.DataSourceIds,
                Extension = Path.GetExtension(q.TemplateFileNameOriginal).ToLower()
            }
        ).First();

        var dataSourceIdsList = report.DataSourceIds.Split(",").Select(x => int.Parse(x)).ToList();

        var allDataSources = (from q in db.ReportDataSources select q).ToDictionary(x => x.Id);
        var filterParamsDic = report.FilterParams.ToLookup(x => x.DataSourceId);

        var newFilterParamsList = new List<ReportFilterParameter>();
        ReportSourceBase? lastReportSource = null;

        foreach (var dataSourceId in dataSourceIdsList)
        {
            var filterParamsList = filterParamsDic[dataSourceId].ToList();
            string name = allDataSources[dataSourceId].Name;
            ReportSourceBase reportSource = name switch
            {
                "Test Data" => new ReportSources.TestData(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "Credit Limits" => new ReportSources.CreditLimits(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "Counterparty Data" => new ReportSources.CounterpartyData(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "Daily Index Prices" => new ReportSources.DailyIndexPrices(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "Forward Index Prices" => new ReportSources.ForwardIndexPrices(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "Keep Wholes" => new ReportSources.KeepWholes(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "Monthly Index Prices" => new ReportSources.MonthlyIndexPrices(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "Nominations" => new ReportSources.Nominations(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "Plant Statements" => new ReportSources.PlantStatements(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "PTR Data" => new ReportSources.PtrData(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "Scheduling Data" => new ReportSources.SchedulingData(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "SOS Data" => new ReportSources.SosData(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "Trades" => new ReportSources.Trades(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "Valuation Data" => new ReportSources.ValuationData(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "Valuation Data by Deal" => new ReportSources.ValuationDataByDeal(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "Valuation Data by Path" => new ReportSources.ValuationDataByPath(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "WASP Data" => new ReportSources.WaspData(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                "Meter Data" => new ReportSources.MeterData(db, wb, report.Name, dataSourceId, filterId, report.FilterName, filterParamsList, userInfoId),
                _ => throw new Exception($"Report Source {{{name}}} not found")
            };

            var newFilterParams = await reportSource.Run();

            foreach (var newFilterParam in newFilterParams)
            {
                newFilterParam.DataSourceId = dataSourceId;
                newFilterParam.DataSource = allDataSources[dataSourceId];
            }
            newFilterParamsList.AddRange(newFilterParams);

            lastReportSource = reportSource;
        }

        ClearFilterDetailsSheet(wb);
        AddFilterDetails(wb, newFilterParamsList, templateLoadFailed);

        lastReportSource?.SaveWorkbook();
    }

    private static void ClearFilterDetailsSheet(SpreadsheetDocument wb)
    {
        if (wb.WorkbookPart == null) return;

        Sheets? sheets = wb.WorkbookPart.Workbook?.GetFirstChild<Sheets>();
        if (sheets == null) return;

        Sheet? filterSheet = sheets.Elements<Sheet>().FirstOrDefault(s => s.Name?.Value?.Equals("Filter Details", StringComparison.OrdinalIgnoreCase) == true);
        if (filterSheet != null && filterSheet.Id != null)
        {
            string relationshipId = filterSheet.Id.Value!;
            WorksheetPart worksheetPart = (WorksheetPart)wb.WorkbookPart.GetPartById(relationshipId);

            // Remove all rows from the sheet (optionally keep header)
            var sheetData = worksheetPart.Worksheet?.GetFirstChild<SheetData>();
            sheetData?.RemoveAllChildren<Row>();

            worksheetPart.Worksheet?.Save();
        }
    }

    private static void AddFilterDetails(SpreadsheetDocument wb, List<ReportFilterParameter> filterParams, bool templateLoadFailed)
    {
        WorkbookPart workbookPart;

        if (wb.WorkbookPart == null)
        {
            workbookPart = wb.AddWorkbookPart();
            workbookPart.Workbook = new Workbook();
            workbookPart.Workbook.AppendChild(new Sheets());
        }
        else
        {
            workbookPart = wb.WorkbookPart;
        }

        Sheets sheets = workbookPart.Workbook?.GetFirstChild<Sheets>() ?? workbookPart.Workbook?.AppendChild(new Sheets()) ?? new Sheets();

        const string sheetName = "Filter Details";
        Sheet? filterSheet = sheets.Elements<Sheet>().FirstOrDefault(s => s.Name?.Value?.Equals(sheetName, StringComparison.OrdinalIgnoreCase) == true);

        WorksheetPart worksheetPart;
        SheetData sheetData;

        if (filterSheet != null && filterSheet.Id != null)
        {
            // Sheet exists, reuse its WorksheetPart and clear its data
            string relationshipId = filterSheet.Id.Value!;
            worksheetPart = (WorksheetPart)workbookPart.GetPartById(relationshipId);

            // Remove all rows and columns from the worksheet
            sheetData = worksheetPart.Worksheet?.GetFirstChild<SheetData>() ?? new SheetData();
            sheetData.RemoveAllChildren<Row>();

            // Remove columns if present
            var columns = worksheetPart.Worksheet?.Elements<Columns>().FirstOrDefault();
            columns?.Remove();
        }
        else
        {
            // Sheet does not exist, create it
            worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet();
            sheetData = new SheetData();
            worksheetPart.Worksheet.AppendChild(sheetData);

            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Any())
            {
                sheetId = sheets.Elements<Sheet>().Max(s => s.SheetId?.Value ?? 0) + 1;
            }

            Sheet sheet = new() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = sheetId, Name = sheetName };
            sheets.AppendChild(sheet);
        }
        // Add columns definition
        Columns columnsDef = new();
        columnsDef.AppendChild(new Column { Min = 1, Max = 1, Width = 20, CustomWidth = true });
        columnsDef.AppendChild(new Column { Min = 2, Max = 2, Width = 25, CustomWidth = true });
        columnsDef.AppendChild(new Column { Min = 3, Max = 3, Width = 50, CustomWidth = true });
        worksheetPart.Worksheet?.InsertBefore(columnsDef, sheetData);

        uint rowIndex = 1;
        if (templateLoadFailed)
        {
            Row errorRow = new() { RowIndex = rowIndex };
            Cell errorCell = new() { CellReference = $"A{rowIndex}", DataType = CellValues.InlineString };
            errorCell.AppendChild(new InlineString(new Text("Template Failed to Load")));
            errorRow.AppendChild(errorCell);
            sheetData.AppendChild(errorRow);

            rowIndex++;
            sheetData.AppendChild(new Row() { RowIndex = rowIndex });
            rowIndex++;
        }
        // Add header row
        Row headerRow = new() { RowIndex = rowIndex };
        Cell reportRanCell = new() { CellReference = $"A{rowIndex}", DataType = CellValues.InlineString };
        reportRanCell.AppendChild(new InlineString(new Text("Report Ran")));
        headerRow.AppendChild(reportRanCell);

        Cell dateTimeCell = new() { CellReference = $"B{rowIndex}", DataType = CellValues.InlineString };
        dateTimeCell.AppendChild(new InlineString(new Text(DateTime.Now.ToString("M/d/yyyy h:mm tt"))));
        headerRow.AppendChild(dateTimeCell);

        sheetData.AppendChild(headerRow);
        rowIndex++;

        if (filterParams.Count == 0)
        {
            Row noFiltersRow = new() { RowIndex = rowIndex };
            Cell noFiltersCell = new() { CellReference = $"A{rowIndex}", DataType = CellValues.InlineString };
            noFiltersCell.AppendChild(new InlineString(new Text("No Filters Applied")));
            noFiltersRow.AppendChild(noFiltersCell);
            sheetData.AppendChild(noFiltersRow);
        }
        else
        {
            foreach (var param in filterParams)
            {
                Row dataRow = new() { RowIndex = rowIndex };
                Cell dataSourceCell = new() { CellReference = $"A{rowIndex}", DataType = CellValues.InlineString };
                dataSourceCell.AppendChild(new InlineString(new Text(param.DataSource?.Name ?? "")));
                dataRow.AppendChild(dataSourceCell);

                Cell paramNameCell = new() { CellReference = $"B{rowIndex}", DataType = CellValues.InlineString };
                paramNameCell.AppendChild(new InlineString(new Text(param.Name ?? "")));
                dataRow.AppendChild(paramNameCell);

                Cell previewCell = new() { CellReference = $"C{rowIndex}", DataType = CellValues.InlineString };
                previewCell.AppendChild(new InlineString(new Text(param.Preview ?? "")));
                dataRow.AppendChild(previewCell);

                sheetData.AppendChild(dataRow);
                rowIndex++;
            }
        }

        worksheetPart.Worksheet?.Save();
    }
}
