using Fast.Web.Models;
using SosItem = Fast.Models.SosItem;

namespace Fast.Web.Logic;

internal class SosDatabaseHelper
{
    const int productId = (int)Enums.Product.NaturalGas;
    const int transactionTypeId = (int)Enums.TransactionType.PhysicalGas;

    public static async Task<List<SosTransferPath>> GetTransferPathsAsync(int pipeId)
    {
        using var db = Main.CreateContext();

        var query = (
            from p in db.GasPaths
            join pr in db.GasPathRoutes on p.Id equals pr.PathId into prGroup
            from pr in prGroup.DefaultIfEmpty()
            join tmm in db.TransferMeters on pr.MeterMapId equals tmm.Id into tmmGroup
            from tmm in tmmGroup.DefaultIfEmpty()
            join mp1 in DataHelper.RecentMeterProductsQueryable(db)
                on new { MeterId = tmm.Meter1.Id, ProductId = productId }
                equals new { mp1.MeterId, mp1.ProductId }
            join z1 in db.Zones on mp1.SourceZoneId equals z1.Id
            join p1 in db.Pipelines on z1.PipelineId equals p1.Id
            join mp2 in DataHelper.RecentMeterProductsQueryable(db)
                on new { MeterId = tmm.Meter2.Id, ProductId = productId }
                equals new { mp2.MeterId, mp2.ProductId }
            join z2 in db.Zones on mp2.SourceZoneId equals z2.Id
            join p2 in db.Pipelines on z2.PipelineId equals p2.Id
            where (p1.Id == pipeId || p2.Id == pipeId)
                  && p.SourceCounterpartyId != null
            select new SosTransferPath
            {
                PathId = p.Id,
                PathName = p.Name,
                SourceMeterId = p.SourceMeterId,
                SourceCounterpartyId = p.SourceCounterpartyId!.Value
            }
        );

        var result = await query.AsNoTracking().Distinct().OrderBy(x => x.PathName).ToListAsync();
        return result;
    }

    public static async Task<List<SosMarket>> GetMarketsAsync(int pipeId, int deliveryPointId, DateOnly nomDate)
    {
        var getNewDbContext = () => Main.CreateContext();

        using var db = getNewDbContext();

        var dbItems = await (
            from d in db.Deals
                .Include(x => x.DealVolumes)
                .Include(x => x.PointSourceDeliveries)
            join psd in db.PointSourceDeliveries on d.Id equals psd.DealId into psdGroup
            from psd in psdGroup.DefaultIfEmpty()
            join pt in db.Points on psd.PointId equals pt.Id into ptGroup
            from pt in ptGroup.DefaultIfEmpty()
            join pit in db.MarketIndices on d.PriceIndexId equals pit.Id into pitGroup
            from pit in pitGroup.DefaultIfEmpty()
            join cpty in db.Counterparties on d.CounterpartyId equals cpty.Id into cptyGroup
            from cpty in cptyGroup.DefaultIfEmpty()
            join mkt in db.GasMarkets
                on new { DealId = d.Id, Date = nomDate, PointId = deliveryPointId }
                equals new { DealId = mkt.DealId.GetValueOrDefault(), mkt.Date, PointId = mkt.PointId.GetValueOrDefault() } into mktGroup
            from mkt in mktGroup.DefaultIfEmpty()
            join m in db.Meters on mkt.MeterId equals m.Id into mGroup
            from m in mGroup.DefaultIfEmpty()
            where pt.PipelineId == pipeId
                && psd.PointId == deliveryPointId
                && d.BuyButton == -1
                && d.StartDate <= nomDate && nomDate <= d.EndDate
                && d.TransactionTypeId == transactionTypeId
                && (m.InactiveDate == null || nomDate < m.InactiveDate)
            select new
            {
                SosMarket = new SosMarket
                {
                    MarketTicket = d.TicketNum,
                    MarketCounterparty = cpty.ShortName ?? cpty.Name,
                    MarketCounterpartyId = cpty.Id,
                    MarketNomId = mkt.Id,
                    MarketDealId = d.Id,
                    MarketDeliveryMeterId = mkt.MeterId,
                    ContractNumber = d.NettingContractNumber,
                    PointId = pt.Id,
                    PointName = pt.Name
                },
                Deal = d,
                Volumes = d.DealVolumes,
                IndexTypeId = pit != null ? (int?)pit.IndexTypeId : null,
                PointVolumes = d.PointSourceDeliveries,
            }
        ).AsNoTracking().ToListAsync();

        foreach (var item in dbItems)
        {
            var sm = item.SosMarket;
            sm.MarketDealType = GetDealType(item.Deal, item.IndexTypeId);
            sm.MarketDealVolume = GetDealVolume(item.Deal, item.Volumes, nomDate, item.PointVolumes, sm.PointId);
        }

        var results = dbItems
            .Select(x => x.SosMarket)
            .OrderByDescending(x => x.MarketDealType)
            .ThenBy(x => float.Parse(x.MarketTicket.Substring(3)))
            .ToList();
        return results;
    }

    public static string GetDealType(Deal? deal, int? indexTypeId)
    {
        if (deal == null)
            return "Baseload";

        return Fast.Logic.Valuation.ValuationResult.GetBaseOrSwingText(deal.PhysicalDealTypeId, deal.FixedPriceButton, deal.StartDate, deal.EndDate, indexTypeId);
    }

    public static double GetDealVolume(
        Deal deal,
        IEnumerable<DealVolume> dealVolumes,
        DateOnly nomDate,
        IEnumerable<PointSourceDelivery>? pointSourceDeliveries = null,
        int? pointId = null)
    {
        double vol;

        if (deal.IsVariableVolume)
        {
            var firstDayOfMonth = Util.Date.FirstDayOfMonth(nomDate);

            var dealVolume = dealVolumes.FirstOrDefault(dv =>
                (deal.VolumeTypeId == (int)Enums.VolumeType.Monthly && dv.StartDate == firstDayOfMonth)
                || (deal.VolumeTypeId == (int)Enums.VolumeType.Daily && dv.StartDate == nomDate)
            );

            vol = (dealVolume?.PhysicalVolume ?? deal.Volume).GetValueOrDefault();
        }
        else if (pointSourceDeliveries != null && pointId != null)
        {
            var psd = pointSourceDeliveries.FirstOrDefault(x => x.PointId == pointId.Value);

            vol = ((double?)psd?.PointVolume ?? deal.Volume).GetValueOrDefault();
        }
        else
        {
            vol = deal.Volume.GetValueOrDefault();
        }

        double value;

        if (deal.VolumeTypeId == (int)Enums.VolumeType.Daily)
        {
            value = vol;
        }
        else if (deal.VolumeTypeId == (int)Enums.VolumeType.Monthly)
        {
            int daysInMonth = DateTime.DaysInMonth(nomDate.Year, nomDate.Month);
            value = vol / daysInMonth;
        }
        else if (deal.VolumeTypeId == (int)Enums.VolumeType.Total)
        {
            var startDate = deal.StartDate.GetValueOrDefault().ToDateTime();
            var endDate = deal.EndDate.GetValueOrDefault().ToDateTime();
            var daysInDeal = (endDate - startDate).Days + 1;

            value = vol / daysInDeal;
        }
        else
        {
            value = 0;
        }

        return value;
    }

    public static async Task<List<SosMarketSupply>> GetMarketSuppliesAsync(int pipeId, DateOnly nomDate)
    {
        using var db = Main.CreateContext();

        var query = (
            from ms in db.GasMarketSupplies
            let m = ms.MarketNom
            let s = ms.SupplyNom
            join mktMeterProduct in DataHelper.RecentMeterProductsQueryable(db)
                on new { m.MeterId, ProductId = productId }
                equals new { mktMeterProduct.MeterId, mktMeterProduct.ProductId }
            join mktZone in db.Zones on mktMeterProduct.SourceZoneId equals mktZone.Id
            join mktPipe in db.Pipelines on mktZone.PipelineId equals mktPipe.Id
            join supMeterProduct in DataHelper.RecentMeterProductsQueryable(db)
                on new { MeterId = s.MeterId.GetValueOrDefault(), ProductId = productId }
                equals new { supMeterProduct.MeterId, supMeterProduct.ProductId }
            join supZone in db.Zones on supMeterProduct.SourceZoneId equals supZone.Id
            join supPipe in db.Pipelines on supZone.PipelineId equals supPipe.Id
            join mktd in db.Deals
                on new { DealId = m.DealId.GetValueOrDefault(), TransactionTypeId = transactionTypeId }
                equals new { DealId = mktd.Id, TransactionTypeId = mktd.TransactionTypeId.GetValueOrDefault() } into mktdGroup
            from mktd in mktdGroup.DefaultIfEmpty()
            join supd in db.Deals
                on new { DealId = s.DealId.GetValueOrDefault(), TransactionTypeId = transactionTypeId }
                equals new { DealId = supd.Id, TransactionTypeId = supd.TransactionTypeId.GetValueOrDefault() } into supdGroup
            from supd in supdGroup.DefaultIfEmpty()
            join td in db.TransferDeals on m.TransferDealId equals td.Id into tdGroup
            from td in tdGroup.DefaultIfEmpty()
            where ms.Date == nomDate
                && s.Date == nomDate
                && supPipe.Id == pipeId
            select new SosMarketSupply
            {
                SupplyNomID = ms.SupplyNomId,
                MarketNomID = ms.MarketNomId,
                Volume = ms.Volume,
                Comment = ms.Comment ?? "",
                IsKeepWhole = ms.IsKeepWhole,
                MarketDealId = m.DealId,
                MarketTransferId = m.TransferDealId,
                MarketMeterId = m.MeterId,
                SupplyDealId = s.DealId,
                SupplyTransferId = s.TransferDealId,
                SupplyMeterId = s.MeterId,
                MarketTicket = mktd.TicketNum ?? td.TicketNum ?? "",
                SupplyCounterpartyId = supd.CounterpartyId,
                PipeContractId = s.PipelineContractId,
                PtrPercent = s.Ptr,
                PtrDeliveryMeterId = s.PtrDeliveryMeterId,
                PtrContractId = s.PtrPipelineContractId
            }
        ).AsNoTracking().ToListAsync();

        var result = (await query)
            .OrderBy(x => x.SupplyNomID)
            .ThenBy(x => x.MarketNomID)
            .ToList();
        return result;
    }

    public static async Task<List<SosItem>> GetSuppliesAsync(int pipeId, int deliveryPointId, DateOnly nomDate)
    {
        var result = await GasSupplyHelper.GetSuppliesAsync(pipeId, deliveryPointId, nomDate);
        return result;
    }

}
