using System.Runtime.Serialization.Json;
using System.Security.Cryptography;
using Fast.Web.Logic;
using Microsoft.AspNetCore.OData.Formatter;

namespace Fast.Web.Controllers;

[Authorize]
[ApiController]
[Route("api/[controller]")]
public class PlantStatementController : ODataController
{
    private readonly MyDbContext db;

    private readonly AuthorizationHelper authHelper;
    public PlantStatementController(MyDbContext context, IConfiguration configuration)
    {
        db = context;
        authHelper = new AuthorizationHelper(Main.IsAuthenticationEnabled);
    }

    [Permission("Plant Statement", PermissionType.View)]
    [Route("/odata/GetPlantStatementItems")]
    public IActionResult GetPlantStatementItems(ODataQueryOptions<PlantStatementListItem> queryOptions, bool isExport)
    {
        queryOptions = Util.GetQueryOptionsWithConvertedDates(queryOptions);
        var itemsQueryable = GetItemsInternal();
        var items = (queryOptions.ApplyTo(itemsQueryable) as IEnumerable<PlantStatementListItem>)?.ToList();

        if (isExport)
            return File(Util.Excel.GetExportFileStream(items), "application/octet-stream");
        else
            return Ok(items);
    }

    [Permission("Plant Statement", PermissionType.View)]
    [Route("[action]")]
    public IQueryable<PlantStatementListItem> GetItemsInternal()
    {
        IQueryable<PlantStatementListItem>? itemsQueryable = (
            from q in db.PlantStatements
            join i in db.VwPlantStatementOverviewInfos on q.Id equals i.PlantStatementId into g
            from i in g.DefaultIfEmpty()
            let meterName = q.Meter != null ? q.Meter.Name : null
            select new PlantStatementListItem
            {
                Id = q.Id,
                ProductionMonth = q.ProductionMonth,
                StatementDate = q.StatementDate,
                Plant = q.Plant.Name,
                Producer = q.Producer.Name,
                Meter = q.PlantStatementCombinedMeters.Any() ? "Combined" : meterName,
                PayoutType = q.PayoutType != null ? q.PayoutType.Name : null,
                StatementDescriptor = q.StatementDescriptor != null ? q.StatementDescriptor.Name : null,
                CombinedMeters = i.CombinedMeters
            }
        ).AsNoTracking();

        return itemsQueryable;
    }

    [Permission("Plant Statement", PermissionType.View)]
    [Route("[action]")]
    public async Task<IActionResult> GetRequiredData()
    {
        var hasModifyPermission = await authHelper.IsAuthorizedAsync(User, "Plant Statement", PermissionType.Modify);
        var plants = (await DataHelper.GetPlantsAsync());
        var producers = (await DataHelper.GetProducersAsync(false));
        var payoutTypes = await (from q in db.PayoutTypes orderby q.Name select new { q.Id, q.Name, q.Description }).ToListAsync();
        var meters = (await DataHelper.GetMetersByProductAsync(Enums.ProductCategory.NaturalGasAndLng)).Select(x => new { x.MeterId, x.MeterName, x.PlantIds, x.ProducerIds }).ToList();
        var statementDescriptors = await (from q in db.PlantStatementDescriptors orderby q.Name select new IdName(q.Id, q.Name ?? "")).ToListAsync();

        var result = new { hasModifyPermission, plants, producers, payoutTypes, meters, statementDescriptors };

        return Ok(result);
    }

    [Permission("Plant Statement", PermissionType.View)]
    [Route("[action]")]
    public async Task<IActionResult> GetStatement(int id)
    {
        var statement = await (
            from q in db.PlantStatements
            where q.Id == id
            select new PlantStatementDetail
            {
                Id = q.Id,
                ProductionMonth = q.ProductionMonth,
                StatementDate = q.StatementDate,
                PlantId = q.PlantId,
                StatementNotes = q.Notes,
                PlantNotes = null, //this will get filled asynchonously on the client side
                ProducerId = q.ProducerId,
                MeterId = q.MeterId,
                StatementDescriptorId = q.StatementDescriptorId,
                PayoutTypeId = q.PayoutTypeId,
                WellheadVolumeMcf = q.WellheadVolumeMcf,
                WellheadVolumeMmbtu = q.WellheadVolumeMmbtu,
                ProcessedVolumeMcf = q.ProcessedVolumeMcf,
                ProcessedVolumeMmbtu = q.ProcessedVolumeMmbtu,
                ShrinkMcf = q.ShrinkMcf,
                ShrinkMmbtu = q.ShrinkMmbtu,
                PlantFuelMcf = q.PlantFuelMcf,
                PlantFuelMmbtu = q.PlantFuelMmbtu,
                FlareMcf = q.FlareMcf,
                FlareMmbtu = q.FlareMmbtu,
                BypassMcf = q.BypassMcf,
                BypassMmbtu = q.BypassMmbtu,
                PlantGainLossMcf = q.PlantGainLossMcf,
                PlantGainLossMmbtu = q.PlantGainLossMmbtu,
                InKindPvrMcf = q.InKindPvrMcf,
                InKindPtrMmbtu = q.InKindPtrMmbtu,
                RecoveredCarbonDioxide = q.RecoveredCarbonDioxide,
                RecoveredEthane = q.RecoveredEthane,
                RecoveredPropane = q.RecoveredPropane,
                RecoveredIsoButane = q.RecoveredIsoButane,
                RecoveredNormalButane = q.RecoveredNormalButane,
                RecoveredNaturalGasoline = q.RecoveredNaturalGasoline,
                RecoveredScrubber = q.RecoveredScrubber,
                TheoreticalCarbonDioxide = q.TheoreticalCarbonDioxide,
                TheoreticalEthane = q.TheoreticalEthane,
                TheoreticalPropane = q.TheoreticalPropane,
                TheoreticalIsoButane = q.TheoreticalIsoButane,
                TheoreticalNormalButane = q.TheoreticalNormalButane,
                TheoreticalIsoPentane = q.TheoreticalIsoPentane,
                TheoreticalNormalPentane = q.TheoreticalNormalPentane,
                TheoreticalNaturalGasoline = q.TheoreticalNaturalGasoline,
                PtrCashoutPrice = q.PtrCashoutPrice,
                PtrTrans = q.PtrTrans,
                PtrFuelPercent = q.PtrFuelPercent,
                WellheadPtrPrice = q.WellheadPtrPrice,
                LiquidsTakePercent = q.LiquidsTakePercent,
                Taxes = q.Taxes,
                FractionationFee = q.FractionationFee,
                PlantProcessingFee = q.PlantProcessingFee,
                CarbonDioxideFee = q.CarbonDioxideFee,
                CompressionFee = q.CompressionFee,
                ElectricityFee = q.ElectricityFee,
                StabilizationFee = q.StabilizationFee,
                MiscellaneousFee = q.MiscellaneousFees,
                LiquidsLiftingFee = q.LiquidsLiftingFee,
                GpmCarbonDioxide = q.GpmCarbonDioxide,
                GpmEthane = q.GpmEthane,
                GpmPropane = q.GpmPropane,
                GpmIsoButane = q.GpmIsoButane,
                GpmNormalButane = q.GpmNormalButane,
                GpmIsoPentane = q.GpmIsoPentane,
                GpmNormalPentane = q.GpmNormalPentane,
                GpmHexanePlus = q.GpmHexanePlus,
                MolePercentNitrogen = q.MolePercentNitrogen,
                MolePercentCarbonDioxide = q.MolePercentCarbonDioxide,
                MolePercentMethane = q.MolePercentMethane,
                MolePercentEthane = q.MolePercentEthane,
                MolePercentPropane = q.MolePercentPropane,
                MolePercentIsoButane = q.MolePercentIsoButane,
                MolePercentNormalButane = q.MolePercentNormalButane,
                MolePercentIsoPentane = q.MolePercentIsoPentane,
                MolePercentNormalPentane = q.MolePercentNormalPentane,
                MolePercentHexane = q.MolePercentHexane,
                MolePercentHeptane = q.MolePercentHeptane,
                PipelineGainLossMcf = q.PipelineGainLossMcf,
                PipelineGainLossMmbtu = q.PipelineGainLossMmbtu,
                AllocatedCarbonDioxide = q.AllocatedCarbonDioxide,
                AllocatedEthane = q.AllocatedEthane,
                AllocatedPropane = q.AllocatedPropane,
                AllocatedIsoButane = q.AllocatedIsoButane,
                AllocatedNormalButane = q.AllocatedNormalButane,
                AllocatedNaturalGasoline = q.AllocatedNaturalGasoline,
                AllocatedScrubber = q.AllocatedScrubber,
                PricesId = q.PricesId,
                ChargeProducerFee = q.ChargeProducerFee,
                ElectionMcf = q.ElectionMcf,
                ElectionMmbtu = q.ElectionMmbtu
            }
        ).AsNoTracking().FirstAsync();

        var combinedMeters = (
            from q in db.PlantStatementCombinedMeters
            where q.PlantStatementId == statement.Id
            select new { q, MeterName = q.Meter.Name }
        ).ToList();

        foreach (var combinedMeter in combinedMeters)
        {
            var newStatement = new PlantStatementCombinedMeterDetail();
            newStatement.MeterId = combinedMeter.q.MeterId;
            newStatement.MeterName = combinedMeter.MeterName;
            newStatement.WellheadVolumeMcf = combinedMeter.q.WellheadVolumeMcf;
            newStatement.WellheadVolumeMmbtu = combinedMeter.q.WellheadVolumeMmbtu;
            newStatement.PipelineVolume = combinedMeter.q.PipelineVolume;
            newStatement.WellheadPtrPrice = combinedMeter.q.WellheadPtrPrice;
            newStatement.LiquidsTakePercent = combinedMeter.q.LiquidsTakePercent;
            newStatement.InKindPvrMcf = combinedMeter.q.InKindPvrMcf;
            newStatement.InKindPtrMmbtu = combinedMeter.q.InKindPtrMmbtu;
            newStatement.VolumePercent = 0; //Calculated on client side.
            statement.CombinedMeters.Add(newStatement);
        }

        return Ok(statement);
    }

    [Permission("Plant Statement", PermissionType.Modify)]
    [Route("[action]")]
    public async Task<IActionResult> SaveStatement(PlantStatementDetail statement, Enums.SaveType saveType)
    {
        statement.ProductionMonth = Util.Date.FirstDayOfMonth(statement.ProductionMonth);
        CheckForAtleastOneMeter(statement);

        //if "new" save type then set the id to 0 so that CheckForDuplicatePlantStatement will not exclude this record
        if (saveType == Enums.SaveType.New)
            statement.Id = 0;

        await CheckForDuplicatePlantStatement(statement);

        int savedStatementId = 0;
        await db.Database.CreateExecutionStrategy().Execute(async () =>
        {
            using var dbContextTransaction = await db.Database.BeginTransactionAsync();
            PlantStatement? dbItem = null;
            if (saveType != Enums.SaveType.New)
            {
                dbItem = await (
                    from q in db.PlantStatements
                    where q.Id == statement.Id
                    select q
                ).FirstOrDefaultAsync();
            }

            if (dbItem == null) //if the item does not exist then add it
            {
                dbItem = new PlantStatement();
                db.PlantStatements.Add(dbItem);
            }

            var d = statement;
            dbItem.ProductionMonth = d.ProductionMonth;
            dbItem.StatementDate = d.StatementDate;
            dbItem.PlantId = d.PlantId;
            dbItem.Notes = d.StatementNotes;
            dbItem.ProducerId = d.ProducerId;
            dbItem.MeterId = d.MeterId;
            dbItem.StatementDescriptorId = d.StatementDescriptorId;
            dbItem.PayoutTypeId = d.PayoutTypeId;
            dbItem.WellheadVolumeMcf = d.WellheadVolumeMcf;
            dbItem.WellheadVolumeMmbtu = d.WellheadVolumeMmbtu;
            dbItem.ProcessedVolumeMcf = d.ProcessedVolumeMcf;
            dbItem.ProcessedVolumeMmbtu = d.ProcessedVolumeMmbtu;
            dbItem.ShrinkMcf = d.ShrinkMcf;
            dbItem.ShrinkMmbtu = d.ShrinkMmbtu;
            dbItem.PlantFuelMcf = d.PlantFuelMcf;
            dbItem.PlantFuelMmbtu = d.PlantFuelMmbtu;
            dbItem.FlareMcf = d.FlareMcf;
            dbItem.FlareMmbtu = d.FlareMmbtu;
            dbItem.BypassMcf = d.BypassMcf;
            dbItem.BypassMmbtu = d.BypassMmbtu;
            dbItem.PlantGainLossMcf = d.PlantGainLossMcf;
            dbItem.PlantGainLossMmbtu = d.PlantGainLossMmbtu;
            dbItem.InKindPvrMcf = d.InKindPvrMcf;
            dbItem.InKindPtrMmbtu = d.InKindPtrMmbtu;
            dbItem.RecoveredCarbonDioxide = d.RecoveredCarbonDioxide;
            dbItem.RecoveredEthane = d.RecoveredEthane;
            dbItem.RecoveredPropane = d.RecoveredPropane;
            dbItem.RecoveredIsoButane = d.RecoveredIsoButane;
            dbItem.RecoveredNormalButane = d.RecoveredNormalButane;
            dbItem.RecoveredNaturalGasoline = d.RecoveredNaturalGasoline;
            dbItem.RecoveredScrubber = d.RecoveredScrubber;
            dbItem.TheoreticalCarbonDioxide = d.TheoreticalCarbonDioxide;
            dbItem.TheoreticalEthane = d.TheoreticalEthane;
            dbItem.TheoreticalPropane = d.TheoreticalPropane;
            dbItem.TheoreticalIsoButane = d.TheoreticalIsoButane;
            dbItem.TheoreticalNormalButane = d.TheoreticalNormalButane;
            dbItem.TheoreticalIsoPentane = d.TheoreticalIsoPentane;
            dbItem.TheoreticalNormalPentane = d.TheoreticalNormalPentane;
            dbItem.TheoreticalNaturalGasoline = d.TheoreticalNaturalGasoline;
            dbItem.PtrCashoutPrice = d.PtrCashoutPrice;
            dbItem.PtrTrans = d.PtrTrans;
            dbItem.PtrFuelPercent = d.PtrFuelPercent;
            dbItem.WellheadPtrPrice = d.WellheadPtrPrice;
            dbItem.LiquidsTakePercent = d.LiquidsTakePercent;
            dbItem.Taxes = d.Taxes;
            dbItem.FractionationFee = d.FractionationFee;
            dbItem.PlantProcessingFee = d.PlantProcessingFee;
            dbItem.CarbonDioxideFee = d.CarbonDioxideFee;
            dbItem.CompressionFee = d.CompressionFee;
            dbItem.ElectricityFee = d.ElectricityFee;
            dbItem.StabilizationFee = d.StabilizationFee;
            dbItem.MiscellaneousFees = d.MiscellaneousFee;
            dbItem.LiquidsLiftingFee = d.LiquidsLiftingFee;
            dbItem.GpmCarbonDioxide = d.GpmCarbonDioxide;
            dbItem.GpmEthane = d.GpmEthane;
            dbItem.GpmPropane = d.GpmPropane;
            dbItem.GpmIsoButane = d.GpmIsoButane;
            dbItem.GpmNormalButane = d.GpmNormalButane;
            dbItem.GpmIsoPentane = d.GpmIsoPentane;
            dbItem.GpmNormalPentane = d.GpmNormalPentane;
            dbItem.GpmHexanePlus = d.GpmHexanePlus;
            dbItem.MolePercentNitrogen = d.MolePercentNitrogen;
            dbItem.MolePercentCarbonDioxide = d.MolePercentCarbonDioxide;
            dbItem.MolePercentMethane = d.MolePercentMethane;
            dbItem.MolePercentEthane = d.MolePercentEthane;
            dbItem.MolePercentPropane = d.MolePercentPropane;
            dbItem.MolePercentIsoButane = d.MolePercentIsoButane;
            dbItem.MolePercentNormalButane = d.MolePercentNormalButane;
            dbItem.MolePercentIsoPentane = d.MolePercentIsoPentane;
            dbItem.MolePercentNormalPentane = d.MolePercentNormalPentane;
            dbItem.MolePercentHexane = d.MolePercentHexane;
            dbItem.MolePercentHeptane = d.MolePercentHeptane;
            dbItem.PipelineGainLossMcf = d.PipelineGainLossMcf;
            dbItem.PipelineGainLossMmbtu = d.PipelineGainLossMmbtu;
            dbItem.AllocatedCarbonDioxide = d.AllocatedCarbonDioxide;
            dbItem.AllocatedEthane = d.AllocatedEthane;
            dbItem.AllocatedPropane = d.AllocatedPropane;
            dbItem.AllocatedIsoButane = d.AllocatedIsoButane;
            dbItem.AllocatedNormalButane = d.AllocatedNormalButane;
            dbItem.AllocatedNaturalGasoline = d.AllocatedNaturalGasoline;
            dbItem.AllocatedScrubber = d.AllocatedScrubber;
            dbItem.PricesId = d.PricesId;
            dbItem.ChargeProducerFee = d.ChargeProducerFee;
            dbItem.ElectionMcf = d.ElectionMcf;
            dbItem.ElectionMmbtu = d.ElectionMmbtu;

            await db.SaveChangesAsync();
            savedStatementId = dbItem.Id;

            PlantNote? dbPlantNoteItem = await (
                from q in db.PlantNotes
                where q.PlantId == statement.PlantId && q.Month == statement.ProductionMonth
                select q
            ).FirstOrDefaultAsync();

            if (dbPlantNoteItem == null) //if the item does not exist then add it
            {
                dbPlantNoteItem = new PlantNote();
                dbPlantNoteItem.PlantId = statement.PlantId;
                dbPlantNoteItem.Month = statement.ProductionMonth;
                db.PlantNotes.Add(dbPlantNoteItem);
            }
            dbPlantNoteItem.Notes = statement.PlantNotes;
            await db.SaveChangesAsync();

            //for "normal" save operations we delete, update, and/or add as needed
            //for "new" save operations we only add
            List<PlantStatementCombinedMeterDetail> combinedMetersToAdd = new();
            if (saveType == Enums.SaveType.Normal)
            {
                List<int> statementMeterIds = statement.CombinedMeters.Select(x => x.MeterId).ToList();

                List<PlantStatementCombinedMeter> dbCombinedMeters = await (
                        from q in db.PlantStatementCombinedMeters
                        where q.PlantStatementId == statement.Id
                        select q
                    ).ToListAsync();

                var dbCombinedMetersToDelete = (
                    from q in dbCombinedMeters
                    where !statementMeterIds.Contains(q.MeterId)
                    select q
                ).ToList();

                db.PlantStatementCombinedMeters.RemoveRange(dbCombinedMetersToDelete);
                await db.SaveChangesAsync();

                var dbCombinedMetersToUpdate = (
                    from q in dbCombinedMeters
                    where statementMeterIds.Contains(q.MeterId)
                    select q
                ).ToList();

                foreach (var dbItemToUpdate in dbCombinedMetersToUpdate)
                {
                    var statementMeter = statement.CombinedMeters.Where(x => x.MeterId == dbItemToUpdate.MeterId).First();
                    dbItemToUpdate.WellheadVolumeMcf = statementMeter.WellheadVolumeMcf;
                    dbItemToUpdate.WellheadVolumeMmbtu = statementMeter.WellheadVolumeMmbtu;
                    dbItemToUpdate.PipelineVolume = statementMeter.PipelineVolume;
                    dbItemToUpdate.WellheadPtrPrice = statementMeter.WellheadPtrPrice;
                    dbItemToUpdate.LiquidsTakePercent = statementMeter.LiquidsTakePercent;
                    dbItemToUpdate.InKindPvrMcf = statementMeter.InKindPvrMcf;
                    dbItemToUpdate.InKindPtrMmbtu = statementMeter.InKindPtrMmbtu;
                }
                await db.SaveChangesAsync();

                combinedMetersToAdd = (
                    from q in statement.CombinedMeters
                    where !dbCombinedMeters.Any(x => x.MeterId == q.MeterId)
                    select q
                ).ToList();
            }
            else //if this is a "new" save operation then we add all combined meters
                combinedMetersToAdd = statement.CombinedMeters;

            foreach (var itemToAdd in combinedMetersToAdd)
            {
                var newDbItem = new PlantStatementCombinedMeter();
                newDbItem.PlantStatementId = savedStatementId;
                newDbItem.MeterId = itemToAdd.MeterId;
                newDbItem.WellheadVolumeMcf = itemToAdd.WellheadVolumeMcf;
                newDbItem.WellheadVolumeMmbtu = itemToAdd.WellheadVolumeMmbtu;
                newDbItem.PipelineVolume = itemToAdd.PipelineVolume;
                newDbItem.WellheadPtrPrice = itemToAdd.WellheadPtrPrice;
                newDbItem.LiquidsTakePercent = itemToAdd.LiquidsTakePercent;
                newDbItem.InKindPvrMcf = itemToAdd.InKindPvrMcf;
                newDbItem.InKindPtrMmbtu = itemToAdd.InKindPtrMmbtu;
                db.PlantStatementCombinedMeters.Add(newDbItem);
            }
            await db.SaveChangesAsync();

            await dbContextTransaction.CommitAsync();
        });
        return Ok(savedStatementId);
    }

    private static void CheckForAtleastOneMeter(PlantStatementDetail statementToSave)
    {
        if (statementToSave.MeterId == null && statementToSave.CombinedMeters.Count == 0)
            throw new Exception("Save changes failed. A statement needs at least one meter.");
    }

    private async Task CheckForDuplicatePlantStatement(PlantStatementDetail statementToSave)
    {
        statementToSave.ProductionMonth = Util.Date.FirstDayOfMonth(statementToSave.ProductionMonth);

        //get all statements with the same major criteria
        var statementsWithSameCriteria = await (
            from q in db.PlantStatements
                .Include(x => x.PlantStatementCombinedMeters)
            where q.ProductionMonth == statementToSave.ProductionMonth &&
            q.StatementDate == statementToSave.StatementDate &&
            q.PlantId == statementToSave.PlantId &&
            q.ProducerId == statementToSave.ProducerId &&
            q.StatementDescriptorId == statementToSave.StatementDescriptorId &&
            q.Id != statementToSave.Id
            select q
        ).ToListAsync();

        //create a distinct list of meterIds that exist in the database with the same major criteria
        HashSet<int> existingMeters = new();
        foreach (var statement in statementsWithSameCriteria)
        {
            if (statement.MeterId.HasValue && !existingMeters.Contains(statement.MeterId.Value))
                existingMeters.Add(statement.MeterId.Value);

            foreach (var combinedItem in statement.PlantStatementCombinedMeters)
            {
                if (!existingMeters.Contains(combinedItem.MeterId))
                    existingMeters.Add(combinedItem.MeterId);
            }
        }

        bool hasDuplicate = false;

        //check if the main meterId of the item we are about to save already exists in the database
        if (statementToSave.MeterId.HasValue && existingMeters.Contains(statementToSave.MeterId.Value))
            hasDuplicate = true;

        //check if any of the combined meterIds for the item we are about to save already exist in the database
        foreach (var combinedItem in statementToSave.CombinedMeters)
        {
            if (existingMeters.Contains(combinedItem.MeterId))
                hasDuplicate = true;
        }

        if (hasDuplicate)
            throw new Exception("Save changes failed. A record with this data already exists.");
    }

    [Permission("Plant Statement", PermissionType.View)]
    [Route("[action]/{productionMonth}/{statementDate}/{plantId}/{producerId}")]
    public IActionResult GetPlantStatementPrices(DateOnly productionMonth, DateOnly statementDate, int plantId, int producerId)
    {
        productionMonth = Util.Date.FirstDayOfMonth(productionMonth);

        var lastPricesName = (
            from q in db.PlantStatements
            where q.ProductionMonth <= productionMonth &&
            q.PlantId == plantId &&
            q.ProducerId == producerId
            orderby q.ProductionMonth descending, q.StatementDate descending
            select q.Prices == null ? "" : q.Prices.Name
        ).FirstOrDefault();

        var result = (
            from q in db.PlantStatementPrices
            where q.ProductionMonth == productionMonth &&
            q.StatementDate == statementDate &&
            q.PlantId == plantId &&
            q.ProducerId == producerId
            orderby q.Name
            select new
            {
                q.Id,
                q.Name,
                q.EthanePrice,
                q.PropanePrice,
                q.NormalButanePrice,
                q.IsoButanePrice,
                q.NaturalGasolinePrice,
                q.ScrubberPrice,
                isDefault = q.Name == lastPricesName
            }
        ).ToList();

        return Ok(result);
    }

    [Permission("Plant Statement", PermissionType.Modify)]
    [Route("[action]/{id}")]
    public async Task<IActionResult> DeleteStatement(int id)
    {
        PlantStatement dbItem = await db.PlantStatements.Where(x => x.Id == id).FirstAsync();
        db.PlantStatements.Remove(dbItem);
        await db.SaveChangesAsync();

        return Ok();
    }

    [Permission("Plant Statement Import", PermissionType.Standard)]
    [Route("[action]")]
    public async Task<IActionResult> Import(IFormFile file, [FromODataUri] string metaData)
    {
        int userId = Util.GetAppUserId(User);

        if (file == null || file.Length == 0)
            return BadRequest("File is empty or not provided.");

        ChunkMetaData? chunkData;
        using (var ms = new MemoryStream(Encoding.UTF8.GetBytes(metaData)))
        {
            var serializer = new DataContractJsonSerializer(typeof(ChunkMetaData));
            chunkData = serializer.ReadObject(ms) as ChunkMetaData;
        }

        if (chunkData == null)
            return BadRequest("Invalid metadata.");

        // Create a consistent temp file name
        string tempFileName = $"{userId}_{HashOfOriginalFileName(chunkData.FileUid)}.tmp";
        string tempFilePath = Path.Join(Path.GetTempPath(), tempFileName);

        // Process file in chunks
        using (var inputStream = file.OpenReadStream())
        {
            const int bufferSize = 1048576; // Buffer size, e.g., 1MB
            byte[] buffer = new byte[bufferSize];
            int bytesRead;

            using (var fileStream = new FileStream(tempFilePath, FileMode.Append, FileAccess.Write))
            {
                while ((bytesRead = await inputStream.ReadAsync(buffer)) > 0)
                    await fileStream.WriteAsync(buffer.AsMemory(0, bytesRead));
            }

            // Check if it's the last chunk and process it
            if (chunkData.ChunkIndex == chunkData.TotalChunks - 1)
            {
                // Process the combined file
                using var combinedFileStream = new FileStream(tempFilePath, FileMode.Open, FileAccess.Read);
                var importer = new PlantStatementImporter(db);
                var result = importer.Import(combinedFileStream); // Modify importer to accept FileStream
                return Ok(result);
            }
        }

        return Ok(); //response for incomplete chunks
    }

    private static string HashOfOriginalFileName(string originalFileName)
    {
        using SHA256 sha256 = SHA256.Create();
        byte[] hashBytes = sha256.ComputeHash(Encoding.UTF8.GetBytes(originalFileName));
        return Convert.ToHexString(hashBytes); // Converts byte array to a hex string
    }

    [Permission("Plant Statement", PermissionType.View)]
    [Route("[action]/{plantId}/{payoutTypeId?}")]
    public IActionResult getOptionRequiredFields(int plantId, int? payoutTypeId)
    {
        List<string> requiredFields = new() { "productionMonth", "statementDate", "plantId", "producerId" };
        List<string> optionsRequiredFields = new();

        //order by is important so that we pick options with a set payout type first and null payout types last
        var options = (
            from q in db.PlantStatementOptions
            where q.PlantId == plantId && (q.PayoutTypeId == payoutTypeId || q.PayoutTypeId == null)
            orderby q.PayoutTypeId descending
            select q
        ).FirstOrDefault();

        if (!string.IsNullOrWhiteSpace(options?.RequiredFields))
        {
            optionsRequiredFields = options.RequiredFields.Trim().Split(',').ToList().Select(PlantStatementOptionsController.GetRequiredFieldNameFromDisplay).ToList();
            requiredFields.AddRange(optionsRequiredFields);
        }

        return Ok(requiredFields);
    }

    [Permission("Plant Statement", PermissionType.View)]
    [Route("[action]/{plantId}/{productionMonth}")]
    public IActionResult GetPlantNotes(int plantId, DateOnly productionMonth)
    {
        productionMonth = Util.Date.FirstDayOfMonth(productionMonth);
        var result = (from q in db.PlantNotes where q.PlantId == plantId && q.Month == productionMonth select q.Notes).FirstOrDefault();
        return Ok(result);
    }

    [Permission("Plant Statement", PermissionType.View)]
    [Route("[action]/{productionMonth}/{plantId}/{producerId}/{meterId?}")]
    public IActionResult GetDefaultDescriptor(DateOnly productionMonth, int plantId, int producerId, int? meterId)
    {
        productionMonth = Util.Date.FirstDayOfMonth(productionMonth);
        var result = (
            from q in db.PlantStatements
            where q.ProductionMonth <= productionMonth &&
            q.PlantId == plantId &&
            q.ProducerId == producerId &&
            (q.MeterId == meterId || meterId == null)
            orderby q.ProductionMonth descending, q.StatementDate descending
            select q
        ).FirstOrDefault()?.StatementDescriptorId;

        return Ok(result);
    }

    [Permission("Plant Statement", PermissionType.View)]
    [Route("[action]/{ProductionMonth}/{PlantId}/{ProducerId}/{MeterId?}/{StatementDescriptorId?}")]
    public IActionResult GetDefaultPayPtrValues(DateOnly productionMonth, int plantId, int producerId, int? meterId, int? statementDescriptorId)
    {
        productionMonth = Util.Date.FirstDayOfMonth(productionMonth);
        var result = (
            from q in db.PlantStatements
            where q.ProductionMonth <= productionMonth &&
            q.PlantId == plantId &&
            q.ProducerId == producerId &&
            (q.MeterId == meterId || meterId == null) &&
            (q.StatementDescriptorId == statementDescriptorId || statementDescriptorId == null)
            orderby q.ProductionMonth descending, q.StatementDate descending
            select new { q.PayoutTypeId, q.PtrTrans, q.PtrFuelPercent }
        ).FirstOrDefault();

        return Ok(result);
    }
}
