using Indotalent.Infrastructure.Database;
using Indotalent.Data.Entities;
using Indotalent.Data.Enums;
using MediatR;
using Microsoft.EntityFrameworkCore;

namespace Indotalent.Features.Root.Home.Cqrs;

public class ChartDataPoint
{
    public string? Label { get; set; }
    public double SalesAmount { get; set; }
    public double PurchaseAmount { get; set; }
}

public class ScmMetricDto
{
    public string? Label { get; set; }
    public string? Value { get; set; }
    public string? Color { get; set; }
    public double Percentage { get; set; }
}

public class DealStageDto { public string? Stage { get; set; } public int Count { get; set; } public string? Color { get; set; } }
public class DocSourceDto { public string? Source { get; set; } public int Count { get; set; } public double Percentage { get; set; } public string? Color { get; set; } }
public class DocMetricDto { public string? Label { get; set; } public double Current { get; set; } public double Target { get; set; } public double PctValue { get; set; } public string? BarColor { get; set; } }
public class OrderActivityDto { public string? Category { get; set; } public int Count { get; set; } public string? Color { get; set; } }
public class RecentOrderDto { public string? OrderNumber { get; set; } public string? Description { get; set; } public decimal Value { get; set; } public string? Stage { get; set; } public string? Status { get; set; } public DateTime? Date { get; set; } }
public class ActivityFeedDto { public string? Title { get; set; } public string? Detail { get; set; } public string? TimeAgo { get; set; } public string? ChipLabel { get; set; } public string? ChipColor { get; set; } public string? AvatarBg { get; set; } public string? AvatarText { get; set; } }
public class PipelineSummaryDto { public string? Label { get; set; } public decimal Value { get; set; } public string? IconColorClass { get; set; } public string? FormattedValue { get; set; } }
public class SalesPerformanceDto { public string? Name { get; set; } public decimal Achievement { get; set; } public int Deals { get; set; } public double WinRate { get; set; } }
public class PendingInvoiceDto { public string? Number { get; set; } public string? Customer { get; set; } public decimal Amount { get; set; } public string? Status { get; set; } }
public class RecentLeadDto { public string? LeadTitle { get; set; } public string? Company { get; set; } public decimal Amount { get; set; } public string? Stage { get; set; } }

public class DashboardOmsResponse
{
    public int TotalSalesOrders { get; set; }
    public int TotalPurchaseOrders { get; set; }
    public decimal MonthlySales { get; set; }
    public decimal MonthlyPurchase { get; set; }
    public int TotalCustomers { get; set; }
    public int TotalVendors { get; set; }
    public decimal PendingReceivable { get; set; }
    public decimal TotalPayable { get; set; }
    public decimal TotalReceivable { get; set; }
    public int ActiveDealsCount { get; set; }
    public decimal AvgOrderValue { get; set; }
    public double OrderGrowthRate { get; set; }
    public int PendingSODraft { get; set; }
    public int PendingPODraft { get; set; }
    public int TotalProducts { get; set; }
    public int TotalInvoices { get; set; }
    public int TotalBills { get; set; }
    public double SlaPercentage { get; set; }
    public double CustomerRetention { get; set; }
    public double TargetGrowth { get; set; }

    public List<DealStageDto> DealStages { get; set; } = new();
    public List<DocSourceDto> OrderSources { get; set; } = new();
    public List<DocMetricDto> OrderMetrics { get; set; } = new();
    public List<OrderActivityDto> OrderActivities { get; set; } = new();
    public List<RecentOrderDto> RecentOrders { get; set; } = new();
    public List<ActivityFeedDto> ActivityFeeds { get; set; } = new();
    public List<PipelineSummaryDto> PipelineSummary { get; set; } = new();
    public List<SalesPerformanceDto> SalesLeaderboard { get; set; } = new();
    public List<PendingInvoiceDto> OverdueInvoices { get; set; } = new();
    public List<RecentLeadDto> RecentVendors { get; set; } = new();
    public List<ScmMetricDto> DocGrid { get; set; } = new();
    public List<ChartDataPoint> RevenueTrend { get; set; } = new();

    public string FormattedMonthlySales => FormatNumber(MonthlySales);
    public string FormattedMonthlyPurchase => FormatNumber(MonthlyPurchase);
    public string FormattedPipeline => FormatNumber(PendingReceivable);
    public string FormattedPendingPayments => FormatNumber(PendingReceivable);
    public string FormattedTotalPayable => FormatNumber(TotalPayable);
    public string FormattedTotalReceivable => FormatNumber(TotalReceivable);
    public string FormattedAvgOrderValue => FormatNumber(AvgOrderValue);
    public string FormattedTotalSalesOrders => TotalSalesOrders.ToString("N0");
    public string FormattedTotalPurchaseOrders => TotalPurchaseOrders.ToString("N0");
    public string FormattedTotalCustomers => TotalCustomers.ToString("N0");

    private string FormatNumber(decimal value)
    {
        if (value >= 1000000)
            return (value / 1000000m).ToString("F1") + "M";
        if (value >= 1000)
            return (value / 1000m).ToString("F1") + "K";
        return value.ToString("N0");
    }
}

public record GetOmsDashboardQuery() : IRequest<DashboardOmsResponse>;

public class DashboardHandler : IRequestHandler<GetOmsDashboardQuery, DashboardOmsResponse>
{
    private readonly AppDbContext _context;
    public DashboardHandler(AppDbContext context) => _context = context;

    public async Task<DashboardOmsResponse> Handle(GetOmsDashboardQuery request, CancellationToken ct)
    {
        var today = DateTime.Today;
        var monthStart = new DateTime(today.Year, today.Month, 1);

        // Monthly chart data
        var monthlyLabels = new[] { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };
        var monthlyData = new List<ChartDataPoint>();

        var soRaw = await _context.SalesOrder
            .Where(x => x.OrderDate != null)
            .Select(x => new { x.OrderDate, x.AfterTaxAmount })
            .ToListAsync(ct);
        var poRaw = await _context.PurchaseOrder
            .Where(x => x.OrderDate != null)
            .Select(x => new { x.OrderDate, x.AfterTaxAmount })
            .ToListAsync(ct);

        var allDates = soRaw.Select(d => d.OrderDate!.Value)
            .Concat(poRaw.Select(d => d.OrderDate!.Value))
            .Select(d => new { d.Year, d.Month })
            .Distinct()
            .OrderByDescending(x => x.Year).ThenByDescending(x => x.Month)
            .Take(6).Reverse()
            .ToList();
        foreach (var ym in allDates)
        {
            var ms = new DateTime(ym.Year, ym.Month, 1);
            var me = ms.AddMonths(1);
            var salesAmount = soRaw.Where(x => x.OrderDate >= ms && x.OrderDate < me).Sum(x => (double)(x.AfterTaxAmount ?? 0));
            var purchaseAmount = poRaw.Where(x => x.OrderDate >= ms && x.OrderDate < me).Sum(x => (double)(x.AfterTaxAmount ?? 0));
            monthlyData.Add(new ChartDataPoint
            {
                Label = monthlyLabels[ym.Month - 1],
                SalesAmount = Math.Round(salesAmount / 1000.0, 1),
                PurchaseAmount = Math.Round(purchaseAmount / 1000.0, 1)
            });
        }

        // Sequential queries (DbContext is not thread-safe)
        var totalSO = await _context.SalesOrder.CountAsync(ct);
        var totalPO = await _context.PurchaseOrder.CountAsync(ct);
        var totalCust = await _context.Customer.CountAsync(ct);
        var totalVend = await _context.Vendor.CountAsync(ct);
        var totalInv = await _context.Invoice.CountAsync(ct);
        var totalBill = await _context.Bill.CountAsync(ct);
        var totalPR = await _context.PaymentReceive.CountAsync(ct);
        var totalPD = await _context.PaymentDisburse.CountAsync(ct);
        var totalProd = await _context.Set<Product>().CountAsync(ct);
        var pendingSO = await _context.SalesOrder.CountAsync(x => x.OrderStatus == SalesOrderStatus.Draft, ct);
        var pendingPO = await _context.PurchaseOrder.CountAsync(x => x.OrderStatus == PurchaseOrderStatus.Draft, ct);
        var salesMTD = await _context.SalesOrder.Where(x => x.OrderDate >= monthStart).SumAsync(x => x.AfterTaxAmount ?? 0, ct);
        var purchaseMTD = await _context.PurchaseOrder.Where(x => x.OrderDate >= monthStart).SumAsync(x => x.AfterTaxAmount ?? 0, ct);
        var pendingAR = await _context.Invoice.Where(x => x.InvoiceStatus != InvoiceStatus.Confirmed).SumAsync(x => x.SalesOrder!.AfterTaxAmount ?? 0, ct);
        var totalReceivable = await _context.Invoice.Where(x => x.InvoiceStatus == InvoiceStatus.Confirmed).SumAsync(x => x.SalesOrder!.AfterTaxAmount ?? 0, ct);
        var totalPayable = await _context.Bill.Where(x => x.BillStatus == BillStatus.Confirmed).SumAsync(x => x.PurchaseOrder!.AfterTaxAmount ?? 0, ct);

        // Doc stages (like deal stages)
        var confirmedSO = await _context.SalesOrder.CountAsync(x => x.OrderStatus == SalesOrderStatus.Confirmed, ct);
        var confirmedPO = await _context.PurchaseOrder.CountAsync(x => x.OrderStatus == PurchaseOrderStatus.Confirmed, ct);
        var dealStages = new List<DealStageDto>
        {
            new() { Stage = "Draft SO", Count = pendingSO, Color = "#8b5cf6" },
            new() { Stage = "Confirmed SO", Count = confirmedSO, Color = "#f59e0b" },
            new() { Stage = "Draft PO", Count = pendingPO, Color = "#e11d48" },
            new() { Stage = "Confirmed PO", Count = confirmedPO, Color = "#6366f1" }
        };

        // Doc sources (order types)
        var totalDocs = totalSO + totalPO + totalInv + totalBill;
        var orderSources = new List<DocSourceDto>
        {
            new() { Source = "Sales Orders", Count = totalSO, Percentage = totalDocs > 0 ? Math.Round((double)totalSO / totalDocs * 100, 1) : 0, Color = "#2563eb" },
            new() { Source = "Purch Orders", Count = totalPO, Percentage = totalDocs > 0 ? Math.Round((double)totalPO / totalDocs * 100, 1) : 0, Color = "#f97316" },
            new() { Source = "Invoices", Count = totalInv, Percentage = totalDocs > 0 ? Math.Round((double)totalInv / totalDocs * 100, 1) : 0, Color = "#ec4899" },
            new() { Source = "Bills", Count = totalBill, Percentage = totalDocs > 0 ? Math.Round((double)totalBill / totalDocs * 100, 1) : 0, Color = "#8b5cf6" },
            new() { Source = "Payments", Count = totalPR + totalPD, Percentage = totalDocs > 0 ? Math.Round((double)(totalPR + totalPD) / totalDocs * 100, 1) : 0, Color = "#06b6d4" }
        };

        // Order metrics (like conversion metrics)
        var orderMetrics = new List<DocMetricDto>
        {
            new() { Label = "SO Draft→Confirm", Current = confirmedSO, Target = totalSO, PctValue = totalSO > 0 ? Math.Round((double)confirmedSO / totalSO * 100, 1) : 0, BarColor = "#2563eb" },
            new() { Label = "SO→Invoice", Current = totalInv, Target = totalSO, PctValue = totalSO > 0 ? Math.Round((double)totalInv / totalSO * 100, 1) : 0, BarColor = "#f97316" },
            new() { Label = "PO Draft→Confirm", Current = confirmedPO, Target = totalPO, PctValue = totalPO > 0 ? Math.Round((double)confirmedPO / totalPO * 100, 1) : 0, BarColor = "#ec4899" },
            new() { Label = "Overall Fulfillment", Current = totalInv + totalBill, Target = totalSO + totalPO, PctValue = (totalSO + totalPO) > 0 ? Math.Round((double)(totalInv + totalBill) / (totalSO + totalPO) * 100, 1) : 0, BarColor = "#10b981" }
        };

        // Order activities
        var orderActivities = new List<OrderActivityDto>
        {
            new() { Category = "Sales Orders", Count = totalSO, Color = "#10b981" },
            new() { Category = "Purch Orders", Count = totalPO, Color = "#e11d48" },
            new() { Category = "Invoices", Count = totalInv, Color = "#e11d48" },
            new() { Category = "Total Docs", Count = totalDocs, Color = "#6366f1" }
        };

        // Recent orders
        var recentOrders = await _context.SalesOrder
            .AsNoTracking()
            .OrderByDescending(x => x.OrderDate)
            .Take(4)
            .Select(x => new RecentOrderDto
            {
                OrderNumber = x.AutoNumber,
                Description = x.Description,
                Value = x.AfterTaxAmount ?? 0,
                Stage = x.OrderStatus.ToString(),
                Status = x.OrderStatus == SalesOrderStatus.Confirmed ? "Posted" : x.OrderStatus == SalesOrderStatus.Cancelled ? "Cancelled" : "Pending",
                Date = x.OrderDate
            }).ToListAsync(ct);

        // Pipeline summary
        var pipelineSummaryList = new List<PipelineSummaryDto>
        {
            new() { Label = "MTD Sales", Value = salesMTD, FormattedValue = FormatDecimal(salesMTD), IconColorClass = "gc1" },
            new() { Label = "MTD Purchase", Value = purchaseMTD, FormattedValue = FormatDecimal(purchaseMTD), IconColorClass = "gc10" },
            new() { Label = "Avg Order Value", Value = totalSO > 0 ? salesMTD / totalSO : 0, IconColorClass = "gc2" }
        };

        // Activity feed from recent SO
        var recentSOs = await _context.SalesOrder
            .AsNoTracking()
            .OrderByDescending(x => x.OrderDate).Take(4)
            .Select(x => new { x.AutoNumber, x.OrderDate, x.Customer!.Name })
            .ToListAsync(ct);

        var activityFeed = recentSOs.Select((so, i) => new ActivityFeedDto
        {
            Title = $"Sales Order #{so.AutoNumber}",
            Detail = $"Customer: {so.Name}",
            TimeAgo = so.OrderDate.HasValue ? $"{(int)(DateTime.Now - so.OrderDate.Value).TotalDays}d ago" : "",
            ChipLabel = "SO Created",
            ChipColor = i % 2 == 0 ? "chip-green" : "chip-indigo",
            AvatarBg = i switch { 0 => "#10b981", 1 => "#2563eb", 2 => "#6366f1", _ => "#f97316" },
            AvatarText = "SO"
        }).ToList();

        // Customer performance (top customers by count)
        var topCustRaw = await _context.SalesOrder
            .AsNoTracking()
            .Where(x => x.Customer != null)
            .GroupBy(x => x.Customer!.Name)
            .OrderByDescending(g => g.Count())
            .Take(4)
            .Select(g => new { Name = g.Key ?? "N/A", Cnt = g.Count(), Val = g.Sum(x => x.AfterTaxAmount ?? 0) })
            .ToListAsync(ct);

        var salesLeaderboard = topCustRaw.Select(c => new SalesPerformanceDto
        {
            Name = c.Name,
            Achievement = c.Val,
            Deals = c.Cnt,
            WinRate = totalSO > 0 ? Math.Round((double)c.Cnt / totalSO * 100, 1) : 0
        }).ToList();

        // Overdue invoices
        var overdueInvoices = await _context.Invoice
            .AsNoTracking()
            .Where(x => x.InvoiceStatus != InvoiceStatus.Confirmed && x.InvoiceStatus != InvoiceStatus.Cancelled)
            .OrderByDescending(x => x.CreatedAt).Take(5)
            .Select(x => new PendingInvoiceDto
            {
                Number = x.AutoNumber,
                Customer = x.SalesOrder!.Customer!.Name,
                Amount = x.SalesOrder!.AfterTaxAmount ?? 0,
                Status = x.InvoiceStatus.ToString()
            }).ToListAsync(ct);

        // Recent vendors (like recent leads)
        var recentVendors = await _context.Vendor
            .AsNoTracking()
            .OrderBy(x => x.Name)
            .Take(5)
            .Select(x => new RecentLeadDto
            {
                LeadTitle = x.Name,
                Company = x.VendorGroup != null ? x.VendorGroup.Name : "General",
                Amount = 0,
                Stage = "Active"
            }).ToListAsync(ct);

        // Compute derived values
        var avgOrderValue = totalSO > 0 ? salesMTD / totalSO : 0;
        var orderGrowthRate = totalSO > 0 ? Math.Round((double)totalInv / totalSO * 100, 1) : 42;
        var slaPercentage = totalSO > 0 ? Math.Round((double)confirmedSO / totalSO * 100, 1) : 95;
        var customerRetention = totalCust > 0 ? Math.Round((double)(totalCust - pendingSO) / totalCust * 100, 1) : 92;

        // Doc grid for metrics matrix
        var docGrid = new List<ScmMetricDto>
        {
            new() { Label = "Sales Orders", Value = totalSO.ToString(), Color = "#2563eb" },
            new() { Label = "Purch Orders", Value = totalPO.ToString(), Color = "#10b981" },
            new() { Label = "Invoices", Value = totalInv.ToString(), Color = "#ec4899" },
            new() { Label = "Bills", Value = totalBill.ToString(), Color = "#8b5cf6" },
            new() { Label = "Pay Receive", Value = totalPR.ToString(), Color = "#f59e0b" },
            new() { Label = "Pay Disburse", Value = totalPD.ToString(), Color = "#06b6d4" },
            new() { Label = "Customers", Value = totalCust.ToString("N0"), Color = "#14b8a6" },
            new() { Label = "Vendors", Value = totalVend.ToString("N0"), Color = "#f97316" },
            new() { Label = "Products", Value = totalProd.ToString("N0"), Color = "#059669" },
            new() { Label = "Draft SO", Value = pendingSO.ToString(), Color = "#e11d48" },
            new() { Label = "Draft PO", Value = pendingPO.ToString(), Color = "#db2777" },
            new() { Label = "Pending AR", Value = FormatDecimal(pendingAR), Color = "#d97706" },
            new() { Label = "Pending AP", Value = FormatDecimal(totalPayable), Color = "#0284c7" },
            new() { Label = "Avg SO Value", Value = FormatDecimal(totalSO > 0 ? salesMTD / totalSO : 0), Color = "#65a30d" },
            new() { Label = "Avg PO Value", Value = FormatDecimal(totalPO > 0 ? purchaseMTD / totalPO : 0), Color = "#9333ea" },
            new() { Label = "SLA Target", Value = "95%", Color = "#ea580c" },
            new() { Label = "Growth Rate", Value = $"{orderGrowthRate:F1}%", Color = "#0d9488" },
            new() { Label = "Retention", Value = $"{customerRetention:F1}%", Color = "#7c3aed" },
            new() { Label = "AR Balance", Value = FormatDecimal(totalReceivable), Color = "#0891b2" },
            new() { Label = "AP Balance", Value = FormatDecimal(totalPayable), Color = "#db2777" }
        };

        return new DashboardOmsResponse
        {
            TotalSalesOrders = totalSO,
            TotalPurchaseOrders = totalPO,
            MonthlySales = salesMTD,
            MonthlyPurchase = purchaseMTD,
            TotalCustomers = totalCust,
            TotalVendors = totalVend,
            PendingReceivable = pendingAR,
            TotalPayable = totalPayable,
            TotalReceivable = totalReceivable,
            ActiveDealsCount = pendingSO + pendingPO,
            AvgOrderValue = avgOrderValue,
            OrderGrowthRate = orderGrowthRate,
            PendingSODraft = pendingSO,
            PendingPODraft = pendingPO,
            TotalProducts = totalProd,
            TotalInvoices = totalInv,
            TotalBills = totalBill,
            SlaPercentage = slaPercentage,
            CustomerRetention = customerRetention,
            TargetGrowth = 5.0,
            DealStages = dealStages,
            OrderSources = orderSources,
            OrderMetrics = orderMetrics,
            OrderActivities = orderActivities,
            RecentOrders = recentOrders,
            ActivityFeeds = activityFeed,
            PipelineSummary = pipelineSummaryList,
            SalesLeaderboard = salesLeaderboard,
            OverdueInvoices = overdueInvoices,
            RecentVendors = recentVendors,
            DocGrid = docGrid,
            RevenueTrend = monthlyData
        };
    }

    private static string FormatDecimal(decimal value)
    {
        if (value >= 1000000)
            return (value / 1000000m).ToString("F1") + "M";
        if (value >= 1000)
            return (value / 1000m).ToString("F1") + "K";
        return value.ToString("N0");
    }
}