<?php

namespace App\Support;

use App\Models\CaseOutcome;
use App\Models\CaseStatus;
use App\Models\Client;
use App\Models\ClientInvoice;
use App\Models\ClientReceipt;
use App\Models\Diary;
use App\Models\Lawyer;
use App\Models\LegalCase;
use App\Services\DiaryService;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class DashboardDataBuilder
{
    public static function build(): array
    {
        $today = Carbon::today();
        $startOfMonth = Carbon::now()->startOfMonth();
        $endOfMonth = Carbon::now()->endOfMonth();
        $lastMonthStart = Carbon::now()->subMonthNoOverflow()->startOfMonth();
        $lastMonthEnd = Carbon::now()->subMonthNoOverflow()->endOfMonth();

        $activeStatusCodes = CaseStatus::query()
            ->where('is_active', true)
            ->where(function ($query) {
                $query->whereRaw('LOWER(status_name) LIKE ?', ['%active%'])
                    ->orWhereRaw('LOWER(status_name) LIKE ?', ['%open%'])
                    ->orWhereRaw('LOWER(status_name) LIKE ?', ['%progress%'])
                    ->orWhereRaw('LOWER(status_code) LIKE ?', ['%active%'])
                    ->orWhereRaw('LOWER(status_code) LIKE ?', ['%open%']);
            })
            ->pluck('status_code');

        $settledStatusCodes = CaseStatus::query()
            ->where(function ($query) {
                $query->whereRaw('LOWER(status_name) LIKE ?', ['%settled%'])
                    ->orWhereRaw('LOWER(status_name) LIKE ?', ['%closed%'])
                    ->orWhereRaw('LOWER(status_name) LIKE ?', ['%resolved%'])
                    ->orWhereRaw('LOWER(status_name) LIKE ?', ['%completed%'])
                    ->orWhereRaw('LOWER(status_code) LIKE ?', ['%settled%'])
                    ->orWhereRaw('LOWER(status_code) LIKE ?', ['%closed%'])
                    ->orWhereRaw('LOWER(status_code) LIKE ?', ['%resolved%']);
            })
            ->pluck('status_code');

        $wonOutcomeIds = CaseOutcome::query()
            ->where(function ($query) {
                $query->whereRaw('LOWER(code) LIKE ?', ['%won%'])
                    ->orWhereRaw('LOWER(name) LIKE ?', ['%won%'])
                    ->orWhereRaw('LOWER(name) LIKE ?', ['%success%']);
            })
            ->pluck('id');

        $totalCases = LegalCase::count();
        $newCasesThisMonth = LegalCase::whereBetween('created_at', [$startOfMonth, $endOfMonth])->count();
        $activeCases = $activeStatusCodes->isEmpty()
            ? LegalCase::whereRaw('LOWER(status_code) = ?', ['active'])->count()
            : LegalCase::whereIn('status_code', $activeStatusCodes)->count();
        $settledCases = $settledStatusCodes->isEmpty()
            ? LegalCase::whereRaw('LOWER(status_code) = ?', ['settled'])->count()
            : LegalCase::whereIn('status_code', $settledStatusCodes)->count();
        $wonCases = $wonOutcomeIds->isEmpty() ? 0 : LegalCase::whereIn('outcome_code', $wonOutcomeIds)->count();
        $winRate = $settledCases > 0 ? (int) round(($wonCases / $settledCases) * 100) : 0;

        $totalClients = Client::count();
        $newClientsThisMonth = Client::whereBetween('date_onboarded', [$startOfMonth, $endOfMonth])->count();

        $totalRevenue = (float) ClientReceipt::where('status', 'processed')->sum('total_amount');
        $paidThisMonth = (float) ClientReceipt::where('status', 'processed')
            ->whereBetween('receipt_date', [$startOfMonth->toDateString(), $endOfMonth->toDateString()])
            ->sum('total_amount');
        $paidLastMonth = (float) ClientReceipt::where('status', 'processed')
            ->whereBetween('receipt_date', [$lastMonthStart->toDateString(), $lastMonthEnd->toDateString()])
            ->sum('total_amount');
        $revenueMom = $paidLastMonth > 0 ? (int) round((($paidThisMonth - $paidLastMonth) / $paidLastMonth) * 100) : null;

        $outstandingInvoicesQuery = ClientInvoice::whereIn('status', ['sent', 'overdue']);
        $arBalance = (float) (clone $outstandingInvoicesQuery)->sum('total');

        $agingCurrent = (float) ClientInvoice::whereIn('status', ['sent', 'overdue'])
            ->whereDate('invoice_date', '>=', Carbon::now()->subDays(30)->toDateString())
            ->sum('total');
        $aging30days = (float) ClientInvoice::whereIn('status', ['sent', 'overdue'])
            ->whereBetween('invoice_date', [Carbon::now()->subDays(60)->toDateString(), Carbon::now()->subDays(31)->toDateString()])
            ->sum('total');
        $aging60days = (float) ClientInvoice::whereIn('status', ['sent', 'overdue'])
            ->whereBetween('invoice_date', [Carbon::now()->subDays(90)->toDateString(), Carbon::now()->subDays(61)->toDateString()])
            ->sum('total');
        $aging90days = (float) ClientInvoice::whereIn('status', ['sent', 'overdue'])
            ->whereDate('invoice_date', '<', Carbon::now()->subDays(90)->toDateString())
            ->sum('total');

        $billableHoursMtd = (float) Diary::whereBetween('date_time', [$startOfMonth->toDateString(), $endOfMonth->toDateString()])
            ->whereNotIn('status', ['deferred'])
            ->count();
        $billableHoursLastMonth = (float) Diary::whereBetween('date_time', [$lastMonthStart->toDateString(), $lastMonthEnd->toDateString()])
            ->whereNotIn('status', ['deferred'])
            ->count();

        $pendingAssignments = Diary::where('status', 'pending')->count();
        $inProgressAssignments = Diary::where('status', 'in-progress')->count();
        $completedAssignments = Diary::where('status', 'completed')->count();

        $recentActivity = LegalCase::with('client')->latest('updated_at')->take(5)->get()->map(function ($case) {
            $clientName = $case->client ? trim($case->client->first_name . ' ' . $case->client->last_name) : 'Unknown Client';
            return (object) [
                'client' => (object) ['full_name' => $clientName],
                'case_code' => $case->case_id,
                'updated_at' => $case->updated_at ?: now(),
                'created_at' => $case->created_at ?: now(),
            ];
        });

        $todayDiaries = Diary::whereDate('date_time', $today->toDateString())
            ->whereNotIn('status', ['completed', 'deferred'])
            ->orderBy('date_time')
            ->take(5)
            ->get()
            ->map(function ($diary) {
                return [
                    'title' => $diary->task_title,
                    'time' => Carbon::parse($diary->date_time)->format('h:i A'),
                    'location' => $diary->location ?: 'N/A',
                ];
            });

        $diaryService = new DiaryService();
        $combinedEntries = $diaryService->getCombinedEntries();

        $dailyTasks = $combinedEntries
            ->filter(function (array $entry) use ($today) {
                $entryDate = $entry['entry_date'] ?? null;
                if (!$entryDate) {
                    return false;
                }
                $isToday = Carbon::parse($entryDate)->toDateString() === $today->toDateString();
                $status = (string) ($entry['status'] ?? 'pending');
                return $isToday && !in_array($status, ['completed', 'deferred'], true);
            })
            ->sortBy(fn (array $entry) => Carbon::parse($entry['entry_date']))
            ->take(5)
            ->values()
            ->map(function (array $task) {
                $entryDate = $task['entry_date'] ?? null;
                $due = $entryDate ? Carbon::parse($entryDate)->format('M j, Y') : '—';
                $assignedName = $task['raw_entry']?->assignee?->name
                    ?? $task['raw_entry']?->assignedUser?->name
                    ?? 'Unassigned';

                return [
                    'title' => (string) ($task['title'] ?? 'Untitled'),
                    'priority' => (string) ($task['priority'] ?? 'medium'),
                    'assigned' => $assignedName,
                    'due' => $due,
                    'url' => route('modules.processing.diary.entry.show', ['type' => $task['type'], 'id' => $task['id']]),
                    'is_completed' => (bool) ($task['is_completed'] ?? false),
                    'complete_url' => route('modules.processing.diary.entry.complete', ['type' => $task['type'], 'id' => $task['id']]),
                ];
            });

        $overdueTasks = $combinedEntries
            ->filter(function (array $entry) use ($today) {
                $entryDate = $entry['entry_date'] ?? null;
                if (!$entryDate) {
                    return false;
                }
                $isOverdue = Carbon::parse($entryDate)->toDateString() < $today->toDateString();
                $status = (string) ($entry['status'] ?? 'pending');
                return $isOverdue && !in_array($status, ['completed', 'deferred'], true);
            })
            ->sortBy(fn (array $entry) => Carbon::parse($entry['entry_date']))
            ->take(5)
            ->values()
            ->map(function (array $task) {
                $entryDate = $task['entry_date'] ?? null;
                $overdueDays = $entryDate ? Carbon::parse($entryDate)->diffInDays(Carbon::now()) : 0;
                $assignedName = $task['raw_entry']?->assignee?->name
                    ?? $task['raw_entry']?->assignedUser?->name
                    ?? 'Unassigned';

                return [
                    'title' => (string) ($task['title'] ?? 'Untitled'),
                    'priority' => (string) (($task['priority'] ?? '') !== '' ? $task['priority'] : 'high'),
                    'assigned' => $assignedName,
                    'overdue' => $overdueDays . ' days',
                    'url' => route('modules.processing.diary.entry.show', ['type' => $task['type'], 'id' => $task['id']]),
                    'is_completed' => (bool) ($task['is_completed'] ?? false),
                    'complete_url' => route('modules.processing.diary.entry.complete', ['type' => $task['type'], 'id' => $task['id']]),
                ];
            });

        $invoicedTotal = (float) ClientInvoice::sum('total');
        $totalReceivables = (float) ClientInvoice::whereIn('status', ['sent', 'overdue'])->sum('total');
        $invoicedLast30Days = (float) ClientInvoice::whereDate('invoice_date', '>=', Carbon::now()->subDays(30)->toDateString())->sum('total');
        $dailyCreditSales = $invoicedLast30Days > 0 ? ($invoicedLast30Days / 30) : 0;
        $dso = $dailyCreditSales > 0 ? (int) round($totalReceivables / $dailyCreditSales) : 0;
        $collectionRate = $invoicedTotal > 0 ? (int) round(($totalRevenue / $invoicedTotal) * 100) : 0;

        $totalLawyers = max(Lawyer::count(), 1);
        $completedHoursThisMonth = (float) Diary::where('status', 'completed')
            ->whereBetween('date_time', [$startOfMonth->toDateString(), $endOfMonth->toDateString()])
            ->count();
        $utilizationRate = (int) round(($completedHoursThisMonth / ($totalLawyers * 160)) * 100);
        $historicalCompletedHours = (float) Diary::where('status', 'completed')->count();
        $avgBilledRatePerHour = $historicalCompletedHours > 0 ? ($invoicedTotal / $historicalCompletedHours) : 0;
        $unbilledWip = (float) Diary::whereIn('status', ['pending', 'in-progress'])->count() * $avgBilledRatePerHour;
        $activeClientsLastYear = Client::whereDate('date_onboarded', '>=', Carbon::now()->subYear()->toDateString())->count();
        $clientRetention = $totalClients > 0 ? (int) round(($activeClientsLastYear / $totalClients) * 100) : 0;

        $charts = self::buildCharts($agingCurrent, $aging30days, $aging60days, $aging90days, $totalRevenue);
        $expensesTotal = (float) ($charts['financialOverview']['expenses'] ?? 0);

        return [
            'total_cases' => $totalCases,
            'new_cases_this_month' => $newCasesThisMonth,
            'active_cases' => $activeCases,
            'settled_cases' => $settledCases,
            'win_rate' => $winRate,
            'won_cases' => (int) $wonCases,
            'financial' => [
                'revenue' => round($totalRevenue, 2),
                'ar_balance' => round($arBalance, 2),
                'outstandingInvoices' => round($arBalance, 2),
                'paidThisMonth' => round($paidThisMonth, 2),
                'paidLastMonth' => round($paidLastMonth, 2),
                'revenue_mom' => $revenueMom,
                'performance' => [
                    'revenue' => round($totalRevenue, 2),
                    'expenses' => round($expensesTotal, 2),
                    'profit' => round($totalRevenue - $expensesTotal, 2),
                ],
                'aging' => [
                    'current' => round($agingCurrent, 2),
                    '30days' => round($aging30days, 2),
                    '60days' => round($aging60days, 2),
                    '90days' => round($aging90days, 2),
                ],
            ],
            'clients' => [
                'active' => $totalClients,
                'new_this_month' => $newClientsThisMonth,
            ],
            'billable_hours' => round($billableHoursMtd, 2),
            'billable_hours_last_month' => round($billableHoursLastMonth, 2),
            'recentActivity' => $recentActivity,
            'deadlines' => ['today' => $todayDiaries->take(5)->values()->toArray()],
            'tasks' => ['daily' => $dailyTasks->toArray(), 'overdue' => $overdueTasks->toArray()],
            'kpi' => [
                'dso' => $dso,
                'collection_rate' => $collectionRate,
                'realization_rate' => $collectionRate,
                'utilization_rate' => $utilizationRate,
                'unbilled_wip' => round($unbilledWip, 2),
                'client_retention' => $clientRetention,
            ],
            'charts' => $charts,
            'timeTracking' => [
                'trends' => $charts['timeTracking']['data'] ?? [],
            ],
        ];
    }

    private static function buildCharts(float $agingCurrent, float $aging30days, float $aging60days, float $aging90days, float $totalRevenue): array
    {
        $monthPoints = collect(range(5, 0))->map(fn($offset) => Carbon::now()->subMonths($offset)->startOfMonth());
        $monthlyRevenueRows = ClientReceipt::query()
            ->selectRaw('YEAR(receipt_date) as year_no, MONTH(receipt_date) as month_no, COALESCE(SUM(total_amount), 0) as total')
            ->where('status', 'processed')
            ->whereDate('receipt_date', '>=', $monthPoints->first()->toDateString())
            ->groupBy('year_no', 'month_no')
            ->get()
            ->keyBy(fn($row) => sprintf('%04d-%02d', $row->year_no, $row->month_no));

        $revenueLabels = [];
        $revenueData = [];
        foreach ($monthPoints as $point) {
            $key = $point->format('Y-m');
            $revenueLabels[] = $point->format('M');
            $revenueData[] = (float) optional($monthlyRevenueRows->get($key))->total ?: 0;
        }

        $practiceAreaRows = DB::table('case_categories as cc')
            ->leftJoin('cases as c', 'c.category_code', '=', 'cc.category_code')
            ->select('cc.category_description', DB::raw('COUNT(c.id) as total'))
            ->groupBy('cc.category_code', 'cc.category_description')
            ->orderByDesc('total')
            ->limit(6)
            ->get();
        $practiceAreaTotal = max((int) $practiceAreaRows->sum('total'), 1);

        $utilWeeks = collect(range(3, 0))->map(fn($offset) => Carbon::now()->startOfWeek()->subWeeks($offset));
        $utilLabels = [];
        $utilBillable = [];
        $utilNonBillable = [];
        foreach ($utilWeeks as $weekStart) {
            $weekEnd = $weekStart->copy()->endOfWeek();
            $utilLabels[] = 'Week of ' . $weekStart->format('M j');
            $utilBillable[] = (float) Diary::whereBetween('date_time', [$weekStart->toDateString(), $weekEnd->toDateString()])
                ->where('status', 'completed')
                ->count();
            $utilNonBillable[] = (float) Diary::whereBetween('date_time', [$weekStart->toDateString(), $weekEnd->toDateString()])
                ->whereIn('status', ['pending', 'in-progress', 'deferred'])
                ->count();
        }

        $matterRows = ClientInvoice::query()
            ->select('case_reference', DB::raw('COALESCE(SUM(subtotal), 0) as budget'), DB::raw('COALESCE(SUM(total), 0) as actual'))
            ->groupBy('case_reference')
            ->orderByDesc(DB::raw('COALESCE(SUM(total), 0)'))
            ->limit(6)
            ->get();

        $timeWeeks = collect(range(5, 0))->map(fn($offset) => Carbon::now()->startOfWeek()->subWeeks($offset));
        $timeLabels = [];
        $timeData = [];
        foreach ($timeWeeks as $weekStart) {
            $weekEnd = $weekStart->copy()->endOfWeek();
            $timeLabels[] = 'Week of ' . $weekStart->format('M j');
            $timeData[] = (float) Diary::whereBetween('date_time', [$weekStart->toDateString(), $weekEnd->toDateString()])
                ->whereNotIn('status', ['deferred'])
                ->count();
        }

        $expensesTotal = (float) DB::table('supplier_invoices')
            ->selectRaw('COALESCE(SUM((price * quantity) + operation_cost), 0) as total_expenses')
            ->value('total_expenses');

        return [
            'revenue' => ['labels' => $revenueLabels, 'data' => $revenueData],
            'practiceAreas' => [
                'labels' => $practiceAreaRows->pluck('category_description')->map(fn($label) => $label ?: 'Uncategorized')->values()->toArray(),
                'data' => $practiceAreaRows->map(fn($row) => round((((int) $row->total) / $practiceAreaTotal) * 100, 1))->values()->toArray(),
            ],
            'utilization' => ['labels' => $utilLabels, 'billable' => $utilBillable, 'non_billable' => $utilNonBillable],
            'arAging' => ['labels' => ['0-30 Days', '31-60 Days', '61-90 Days', '90+ Days'], 'data' => [$agingCurrent, $aging30days, $aging60days, $aging90days]],
            'matterPerformance' => [
                'labels' => $matterRows->pluck('case_reference')->map(fn($label) => $label ?: 'Unassigned Matter')->values()->toArray(),
                'budget' => $matterRows->pluck('budget')->map(fn($value) => (float) $value)->values()->toArray(),
                'actual' => $matterRows->pluck('actual')->map(fn($value) => (float) $value)->values()->toArray(),
            ],
            'timeTracking' => ['labels' => $timeLabels, 'data' => $timeData],
            'financialOverview' => ['revenue' => round($totalRevenue, 2), 'expenses' => round($expensesTotal, 2), 'profit' => round($totalRevenue - $expensesTotal, 2)],
        ];
    }
}
