using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;

public IActionResult GenerateInventoryDailyReport()
{
    var userId = AuthHelper.GetCurrentUserId();
    var reportData = DapperRepository.QueryDataSet(
        "SELECT ProductName, ProductSKU, DailySales, DailyUsage FROM InventoryDailyMetrics WHERE CreatedBy = @uid",
        new { uid = userId }
    );
    DataTable rawMetrics = reportData.Tables[0];

    DataTable structuredTable = new DataTable("InventoryDaily");

    string[] staticCols = { "Product", "SKU", "Metric Type" };
    foreach (var colName in staticCols)
    {
        structuredTable.Columns.Add(colName, typeof(string));
    }

    for (int day = 1; day <= 31; day++)
    {
        structuredTable.Columns.Add($"{day}st", typeof(decimal?));
    }

    foreach (DataRow metricRow in rawMetrics.Rows)
    {
        var productLabel = metricRow["ProductName"].ToString();
        var skuIdentifier = metricRow["ProductSKU"].ToString();
        var dailySalesList = metricRow["DailySales"].ToString().Split(',', StringSplitOptions.None);
        var dailyUsageList = metricRow["DailyUsage"].ToString().Split(',', StringSplitOptions.None);

        var salesRow = structuredTable.NewRow();
        salesRow[0] = productLabel;
        salesRow[1] = skuIdentifier;
        salesRow[2] = "Units Sold";
        for (int d = 0; d < 31; d++)
        {
            if (decimal.TryParse(dailySalesList[d], out decimal salesVal))
            {
                salesRow[d + 3] = salesVal;
            }
        }
        structuredTable.Rows.Add(salesRow);

        var usageRow = structuredTable.NewRow();
        usageRow[0] = productLabel;
        usageRow[1] = skuIdentifier;
        usageRow[2] = "Units Consumed";
        for (int d = 0; d < 31; d++)
        {
            if (decimal.TryParse(dailyUsageList[d], out decimal usageVal))
            {
                usageRow[d + 3] = usageVal;
            }
        }
        structuredTable.Rows.Add(usageRow);
    }

    var fileName = NPOIExcelExporter.SaveAndFormatInventoryReport(structuredTable);
    return File(Path.Combine(AppSettings.ExportDirectory, fileName), "application/vnd.ms-excel", fileName);
}

private static ISheet ApplyInventoryReportStyling(ISheet worksheet, int totalDataRows)
{
    int rowIndex = 1;
    while (rowIndex <= totalDataRows - 1)
    {
        for (int colIndex = 0; colIndex < 3; colIndex++)
        {
            var mergeRange = new CellRangeAddress(rowIndex, rowIndex + 1, colIndex, colIndex);
            worksheet.AddMergedRegion(mergeRange);
        }
        rowIndex += 2;
    }

    int maxRow = worksheet.LastRowNum;
    int maxCol = 0;
    for (int i = 0; i <= maxRow; i++)
    {
        var currentRow = worksheet.GetRow(i);
        if (currentRow != null)
        {
            maxCol = Math.Max(maxCol, currentRow.LastCellNum - 1);
        }
    }

    IWorkbook parentWorkbook = worksheet.Workbook;
    ICellStyle centeredStyle = parentWorkbook.CreateCellStyle();
    centeredStyle.Alignment = HorizontalAlignment.Center;
    centeredStyle.VerticalAlignment = VerticalAlignment.Center;
    IFont headerFont = parentWorkbook.CreateFont();
    headerFont.Boldweight = (short)FontBoldWeight.Bold;
    ICellStyle headerStyle = parentWorkbook.CreateCellStyle();
    headerStyle.CloneStyleFrom(centeredStyle);
    headerStyle.SetFont(headerFont);

    for (int i = 0; i <= maxRow; i++)
    {
        IRow currentRow = worksheet.GetRow(i) ?? worksheet.CreateRow(i);
        currentRow.HeightInPoints = 18;
        for (int j = 0; j <= maxCol; j++)
        {
            ICell currentCell = currentRow.GetCell(j) ?? currentRow.CreateCell(j);
            currentCell.CellStyle = i == 0 ? headerStyle : centeredStyle;
            worksheet.SetColumnWidth(j, (j < 3 ? 22 : 10) * 256);
        }
    }

    return worksheet;
}

public static string SaveAndFormatInventoryReport(DataTable reportTable)
{
    IWorkbook excelWorkbook = new HSSFWorkbook();
    ISheet inventorySheet = excelWorkbook.CreateSheet("Daily Inventory Metrics");

    IRow headerRow = inventorySheet.CreateRow(0);
    for (int i = 0; i < reportTable.Columns.Count; i++)
    {
        ICell headerCell = headerRow.CreateCell(i);
        headerCell.SetCellValue(reportTable.Columns[i].ColumnName);
    }

    for (int dataIdx = 0; dataIdx < reportTable.Rows.Count; dataIdx++)
    {
        DataRow sourceRow = reportTable.Rows[dataIdx];
        IRow targetRow = inventorySheet.CreateRow(dataIdx + 1);

        for (int colIdx = 0; colIdx < reportTable.Columns.Count; colIdx++)
        {
            ICell targetCell = targetRow.CreateCell(colIdx);
            if (sourceRow[colIdx] is decimal decimalValue)
            {
                targetCell.SetCellValue(decimalValue);
            }
            else
            {
                targetCell.SetCellValue(sourceRow[colIdx]?.ToString() ?? string.Empty);
            }
        }
    }

    ApplyInventoryReportStyling(inventorySheet, reportTable.Rows.Count);

    string exportFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "GeneratedReports");
    if (!Directory.Exists(exportFolder))
    {
        Directory.CreateDirectory(exportFolder);
    }

    string outputFileName = $"InventoryReport_{DateTime.UtcNow:yyyyMMddHHmmssfff}.xls";
    string fullOutputPath = Path.Combine(exportFolder, outputFileName);

    using (var fileStream = new FileStream(fullOutputPath, FileMode.Create, FileAccess.Write))
    {
        excelWorkbook.Write(fileStream);
    }

    return outputFileName;
}