<?php

namespace App\Livewire\Financial;

use App\Domain\Facility\Models\Facility;
use App\Domain\Financial\Enums\AccountType;
use App\Domain\Financial\Enums\ExpenseCategory;
use App\Domain\Financial\Enums\InvoiceStatus;
use App\Domain\Financial\Models\Expense;
use App\Domain\Financial\Models\FinancialAccount;
use App\Domain\Financial\Models\Invoice;
use App\Domain\Financial\Models\Payment;
use App\Domain\Financial\Models\Transaction;
use App\Domain\HR\Enums\PayslipStatus;
use App\Domain\HR\Models\Payslip;
use App\Domain\HR\Models\TrainerCompensation;
use App\Domain\Identity\Models\Branch;
use App\Domain\Inventory\Models\PurchaseOrder;
use App\Domain\Participant\Models\Participant;
use App\Domain\Shared\Traits\UsesBranchScope;
use App\Domain\Training\Models\Enrollment;
use Illuminate\Support\Facades\DB;
use Livewire\Attributes\Layout;
use Livewire\Attributes\Title;
use Livewire\Attributes\Url;
use Livewire\Component;

#[Layout('layouts.app')]
#[Title('النظرة المالية العامة')]
class FinancialOverview extends Component
{
    use UsesBranchScope;

    #[Url]
    public string $period = 'this_month';

    #[Url]
    public string $branch_id = '';

    public function mount()
    {
        $this->authorize('invoices.list');
    }

    public function render()
    {
        if (!$this->branch_id) {
            $activeBranch = $this->getActiveBranchId();
            if ($activeBranch) {
                $this->branch_id = (string) $activeBranch;
            }
        }

        [$from, $to] = $this->resolvePeriod();

        $branches = Branch::orderBy('name_ar')->get();

        $facilityCosts = $this->getFacilityCosts();
        $revenue = $this->getRevenue($from, $to);
        $expenses = $this->getExpenses($from, $to);
        $monthlyPL = $this->getMonthlyPL();
        $breakEven = $this->getBreakEvenData($facilityCosts, $revenue);
        $revenueBySource = $this->getRevenueBySource($from, $to);
        $collectionRate = $this->getCollectionRate($from, $to);
        $revenueBreakdown = $this->getRevenueBreakdown($from, $to);
        $financialMetrics = $this->getFinancialMetrics($from, $to, $revenue, $expenses, $collectionRate);

        return view('livewire.financial.financial-overview', [
            'branches' => $branches,
            'facilityCosts' => $facilityCosts,
            'revenue' => $revenue,
            'expenses' => $expenses,
            'monthlyPL' => $monthlyPL,
            'breakEven' => $breakEven,
            'revenueBySource' => $revenueBySource,
            'collectionRate' => $collectionRate,
            'revenueBreakdown' => $revenueBreakdown,
            'financialMetrics' => $financialMetrics,
            'periodLabel' => $this->getPeriodLabel(),
            'from' => $from,
            'to' => $to,
        ]);
    }

    private function resolvePeriod(): array
    {
        return match ($this->period) {
            'today' => [now()->startOfDay(), now()->endOfDay()],
            'this_week' => [now()->startOfWeek(), now()->endOfWeek()],
            'this_month' => [now()->startOfMonth(), now()->endOfMonth()],
            'last_month' => [now()->subMonth()->startOfMonth(), now()->subMonth()->endOfMonth()],
            'this_quarter' => [now()->firstOfQuarter(), now()->lastOfQuarter()->endOfDay()],
            'this_year' => [now()->startOfYear(), now()->endOfYear()],
            default => [now()->startOfMonth(), now()->endOfMonth()],
        };
    }

    private function getPeriodLabel(): string
    {
        return match ($this->period) {
            'today' => 'اليوم',
            'this_week' => 'هذا الأسبوع',
            'this_month' => 'هذا الشهر',
            'last_month' => 'الشهر الماضي',
            'this_quarter' => 'هذا الربع',
            'this_year' => 'هذا العام',
            default => 'هذا الشهر',
        };
    }

    private function getFacilityCosts(): array
    {
        $query = Facility::query()->whereNotNull('monthly_rental_cost')->where('monthly_rental_cost', '>', 0);

        if ($this->branch_id) {
            $query->where('branch_id', $this->branch_id);
        }

        $facilities = $query->with('branch')->get();

        $totalMonthly = $facilities->sum('monthly_rental_cost');

        return [
            'facilities' => $facilities->map(fn ($f) => [
                'name' => $f->name_ar,
                'branch' => $f->branch?->name_ar ?? '-',
                'monthly_cost' => $f->monthly_rental_cost,
                'hourly_cost' => $f->rental_cost_per_hour,
                'status' => $f->status->value,
            ])->toArray(),
            'total_monthly' => $totalMonthly,
            'total_yearly' => $totalMonthly * 12,
        ];
    }

    private function getRevenue($from, $to): array
    {
        $query = Payment::where('direction', 'inbound')
            ->where('status', 'confirmed')
            ->whereBetween('payment_date', [$from, $to]);

        if ($this->branch_id) {
            $query->where('branch_id', $this->branch_id);
        }

        $totalRevenue = $query->sum('amount');

        $byMethodQuery = Payment::where('direction', 'inbound')
            ->where('status', 'confirmed')
            ->whereBetween('payment_date', [$from, $to]);

        if ($this->branch_id) {
            $byMethodQuery->where('branch_id', $this->branch_id);
        }

        $byMethod = $byMethodQuery->select('method', DB::raw('SUM(amount) as total'), DB::raw('COUNT(*) as count'))
            ->groupBy('method')
            ->get();

        // Money actually collected in the period, including payments that were
        // later refunded — the refund is subtracted below rather than hidden.
        $grossRevenue = Payment::where('direction', 'inbound')
            ->whereIn('status', ['confirmed', 'refunded'])
            ->whereBetween('payment_date', [$from, $to])
            ->when($this->branch_id, fn ($q) => $q->where('branch_id', $this->branch_id))
            ->sum('amount');

        // Refunds are contra-revenue, not an operating expense. Counting them
        // as an expense while the refunded original already drops out of
        // revenue would deduct the same money twice.
        $refunds = Payment::where('direction', 'outbound')
            ->where('status', 'confirmed')
            ->whereBetween('payment_date', [$from, $to])
            ->when($this->branch_id, fn ($q) => $q->where('branch_id', $this->branch_id))
            ->sum('amount');

        $refundCount = Payment::where('direction', 'outbound')
            ->where('status', 'confirmed')
            ->whereBetween('payment_date', [$from, $to])
            ->when($this->branch_id, fn ($q) => $q->where('branch_id', $this->branch_id))
            ->count();

        return [
            'total' => max(0, $grossRevenue - $refunds),
            'gross' => $grossRevenue,
            'refunds' => $refunds,
            'refund_count' => $refundCount,
            'collected' => $totalRevenue,
            'by_method' => $byMethod->map(fn ($p) => [
                'method' => $p->method?->value ?? $p->method,
                'total' => $p->total,
                'count' => $p->count,
            ])->toArray(),
            'transaction_count' => $query->count(),
        ];
    }

    private function getExpenses($from, $to): array
    {
        $branchId = $this->branch_id;

        // Payroll: approved/paid payslips plus trainer compensation earned in
        // the period. Neither table carries branch_id, so both scope through
        // the trainer's employee record.
        $payroll = Payslip::whereIn('status', [PayslipStatus::Approved->value, PayslipStatus::Paid->value])
            ->whereBetween('created_at', [$from, $to])
            ->when($branchId, fn ($q) => $q->whereHas('trainer.employee', fn ($e) => $e->forBranch($branchId)))
            ->sum('net_amount');

        $trainerDues = TrainerCompensation::whereIn('status', ['approved', 'paid'])
            ->whereBetween('created_at', [$from, $to])
            ->when($branchId, fn ($q) => $q->whereHas('trainer.employee', fn ($e) => $e->forBranch($branchId)))
            ->sum('amount');

        $payrollTotal = (int) $payroll + (int) $trainerDues;

        $purchaseOrders = PurchaseOrder::whereIn('status', ['confirmed', 'received', 'partially_received'])
            ->whereBetween('created_at', [$from, $to])
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->sum('total_amount');

        $facilityCostsThisPeriod = $this->calculateProRatedFacilityCost($from, $to);

        // Direct expenses from the expenses table
        $directExpensesQuery = Expense::whereBetween('expense_date', [$from, $to])
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId));

        $directExpensesTotal = (clone $directExpensesQuery)->sum('amount');

        // Breakdown by category
        $byCategory = (clone $directExpensesQuery)
            ->select('category', DB::raw('SUM(amount) as total'), DB::raw('COUNT(*) as count'))
            ->groupBy('category')
            ->orderByDesc('total')
            ->get()
            ->map(fn ($e) => [
                // Expense::$casts turns this into an ExpenseCategory instance.
                // Unwrap it to its scalar here so callers can build array keys
                // and strings from it — concatenating the enum is fatal.
                'category' => $e->category instanceof ExpenseCategory
                    ? $e->category->value
                    : (string) $e->category,
                'label' => $e->category instanceof ExpenseCategory ? $e->category->label() : $e->category,
                'total' => (int) $e->total,
                'count' => (int) $e->count,
            ])->toArray();

        $recentExpenses = Expense::whereBetween('expense_date', [$from, $to])
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->with('creator')
            ->orderByDesc('expense_date')
            ->limit(10)
            ->get();

        $total = $payrollTotal + $purchaseOrders + $facilityCostsThisPeriod + $directExpensesTotal;

        // One itemised line per real cost, so nothing is presented as a vague
        // lump. Each line says where the number comes from.
        $lines = [];

        if ($payrollTotal > 0) {
            $lines[] = ['key' => 'payroll', 'label' => 'رواتب ومستحقات المدربين', 'total' => $payrollTotal,
                'source' => 'كشوف الرواتب ومستحقات المدربين المعتمدة', 'color' => 'bg-amber-500', 'count' => null];
        }
        if ($facilityCostsThisPeriod > 0) {
            $lines[] = ['key' => 'facility', 'label' => 'إيجارات المنشآت', 'total' => (int) $facilityCostsThisPeriod,
                'source' => 'الإيجار الشهري للمنشآت موزّعاً على أيام الفترة', 'color' => 'bg-blue-500', 'count' => null];
        }
        if ($purchaseOrders > 0) {
            $lines[] = ['key' => 'purchases', 'label' => 'مشتريات ومخزون', 'total' => (int) $purchaseOrders,
                'source' => 'أوامر الشراء المؤكدة والمستلمة', 'color' => 'bg-emerald-500', 'count' => null];
        }
        foreach ($byCategory as $cat) {
            $lines[] = ['key' => 'cat_' . $cat['category'], 'label' => $cat['label'], 'total' => $cat['total'],
                'source' => 'مصروفات مسجلة يدوياً — ' . $cat['label'], 'color' => 'bg-rose-500', 'count' => $cat['count']];
        }

        usort($lines, fn ($a, $b) => $b['total'] <=> $a['total']);

        return [
            'payroll' => $payrollTotal,
            'purchase_orders' => (int) $purchaseOrders,
            'facility_costs' => (int) $facilityCostsThisPeriod,
            'direct_expenses' => (int) $directExpensesTotal,
            'by_category' => $byCategory,
            'recent_expenses' => $recentExpenses,
            'lines' => $lines,
            'total' => (int) $total,
        ];
    }

    private function calculateProRatedFacilityCost($from, $to): int
    {
        $query = Facility::whereNotNull('monthly_rental_cost')->where('monthly_rental_cost', '>', 0);
        if ($this->branch_id) {
            $query->where('branch_id', $this->branch_id);
        }

        $totalMonthly = $query->sum('monthly_rental_cost');
        $days = max(1, now()->parse($from)->diffInDays(now()->parse($to)));
        $daysInMonth = now()->daysInMonth;

        return (int) round($totalMonthly * ($days / $daysInMonth));
    }

    private function getMonthlyPL(): array
    {
        $months = [];
        for ($i = 5; $i >= 0; $i--) {
            $month = now()->subMonths($i);
            $start = $month->copy()->startOfMonth();
            $end = $month->copy()->endOfMonth();

            // Gross collected, then refunds netted off — same treatment as
            // getRevenue(), so the chart and the cards cannot disagree.
            $gross = Payment::where('direction', 'inbound')
                ->whereIn('status', ['confirmed', 'refunded'])
                ->whereBetween('payment_date', [$start, $end])
                ->when($this->branch_id, fn ($q) => $q->where('branch_id', $this->branch_id))
                ->sum('amount');

            $refunds = Payment::where('direction', 'outbound')
                ->where('status', 'confirmed')
                ->whereBetween('payment_date', [$start, $end])
                ->when($this->branch_id, fn ($q) => $q->where('branch_id', $this->branch_id))
                ->sum('amount');

            $income = max(0, $gross - $refunds);

            $payrollMonth = (int) Payslip::whereIn('status', [PayslipStatus::Approved->value, PayslipStatus::Paid->value])
                ->whereBetween('created_at', [$start, $end])
                ->when($this->branch_id, fn ($q) => $q->whereHas('trainer.employee', fn ($e) => $e->forBranch($this->branch_id)))
                ->sum('net_amount')
                + (int) TrainerCompensation::whereIn('status', ['approved', 'paid'])
                ->whereBetween('created_at', [$start, $end])
                ->when($this->branch_id, fn ($q) => $q->whereHas('trainer.employee', fn ($e) => $e->forBranch($this->branch_id)))
                ->sum('amount');

            $purchases = PurchaseOrder::whereIn('status', ['confirmed', 'received', 'partially_received'])
                ->whereBetween('created_at', [$start, $end])
                ->when($this->branch_id, fn ($q) => $q->where('branch_id', $this->branch_id))
                ->sum('total_amount');

            $facilityQuery = Facility::whereNotNull('monthly_rental_cost')->where('monthly_rental_cost', '>', 0);
            if ($this->branch_id) {
                $facilityQuery->where('branch_id', $this->branch_id);
            }
            $facilityCost = $facilityQuery->sum('monthly_rental_cost');

            $directExpenses = Expense::whereBetween('expense_date', [$start, $end])
                ->when($this->branch_id, fn ($q) => $q->where('branch_id', $this->branch_id))
                ->sum('amount');

            $totalExpenses = $payrollMonth + $purchases + $facilityCost + $directExpenses;

            $months[] = [
                'label' => $month->translatedFormat('M Y'),
                'month_key' => $month->format('Y-m'),
                'income' => $income,
                'expenses' => $totalExpenses,
                'profit' => $income - $totalExpenses,
            ];
        }

        return $months;
    }

    private function getBreakEvenData(array $facilityCosts, array $revenue): array
    {
        $monthlyFixedCosts = $facilityCosts['total_monthly'];

        if ($monthlyFixedCosts <= 0) {
            return [
                'has_data' => false,
                'message' => 'لم يتم تحديد تكاليف المنشآت الشهرية بعد',
            ];
        }

        $currentMonthRevenue = $revenue['total'];
        $percentage = min(100, round(($currentMonthRevenue / $monthlyFixedCosts) * 100));
        $remaining = max(0, $monthlyFixedCosts - $currentMonthRevenue);

        return [
            'has_data' => true,
            'fixed_costs' => $monthlyFixedCosts,
            'current_revenue' => $currentMonthRevenue,
            'percentage' => $percentage,
            'remaining' => $remaining,
            'is_profitable' => $currentMonthRevenue >= $monthlyFixedCosts,
        ];
    }

    private function getRevenueBySource($from, $to): array
    {
        $revenueAccounts = FinancialAccount::where('type', AccountType::Revenue)->pluck('id', 'name_ar');

        $bySource = Transaction::whereBetween('transaction_date', [$from, $to])
            ->whereIn('credit_account_id', $revenueAccounts->values())
            ->select('credit_account_id', DB::raw('SUM(amount) as total'))
            ->groupBy('credit_account_id')
            ->get();

        $accountNames = FinancialAccount::whereIn('id', $bySource->pluck('credit_account_id'))->pluck('name_ar', 'id');

        return $bySource->map(fn ($t) => [
            'source' => $accountNames[$t->credit_account_id] ?? 'أخرى',
            'total' => $t->total,
        ])->sortByDesc('total')->values()->toArray();
    }

    private function getCollectionRate($from, $to): array
    {
        $invoicedQuery = Invoice::whereBetween('issue_date', [$from, $to]);
        $overdueQuery = Invoice::where('status', 'overdue');

        if ($this->branch_id) {
            $invoicedQuery->whereHas('payments', fn ($q) => $q->where('branch_id', $this->branch_id));
            $overdueQuery->whereHas('payments', fn ($q) => $q->where('branch_id', $this->branch_id));
        }

        $invoiced = (clone $invoicedQuery)->sum('total_amount');
        $collected = (clone $invoicedQuery)->sum('paid_amount');

        $overdue = (clone $overdueQuery)->sum('due_amount');
        $overdueCount = (clone $overdueQuery)->count();

        return [
            'invoiced' => $invoiced,
            'collected' => $collected,
            'rate' => $invoiced > 0 ? round(($collected / $invoiced) * 100) : 0,
            'overdue_amount' => $overdue,
            'overdue_count' => $overdueCount,
        ];
    }

    private function getRevenueBreakdown($from, $to): array
    {
        $academyId = app('current_academy')?->id;
        $productType = 'App\\Domain\\Inventory\\Models\\Product';

        // Subscription revenue (invoice items without itemable_type = program subscriptions)
        $subscriptionRevenue = (int) DB::table('payments')
            ->join('invoices', 'payments.invoice_id', '=', 'invoices.id')
            ->joinSub(
                DB::table('invoice_items')
                    ->whereNull('itemable_type')
                    ->select('invoice_id', DB::raw('SUM(total_amount) as sub_total'))
                    ->groupBy('invoice_id'),
                'sub_items',
                'sub_items.invoice_id', '=', 'invoices.id'
            )
            ->where('payments.status', 'confirmed')
            ->where('payments.direction', 'inbound')
            ->where('invoices.subtotal_amount', '>', 0)
            ->whereBetween('payments.payment_date', [$from, $to])
            ->when($this->branch_id, fn ($q) => $q->where('payments.branch_id', $this->branch_id))
            ->when($academyId, fn ($q) => $q->where('payments.academy_id', $academyId))
            ->value(DB::raw('SUM(payments.amount * sub_items.sub_total / invoices.subtotal_amount)')) ?? 0;

        // Product revenue (invoice items where itemable_type = Product)
        $productRevenue = (int) DB::table('payments')
            ->join('invoices', 'payments.invoice_id', '=', 'invoices.id')
            ->joinSub(
                DB::table('invoice_items')
                    ->where('itemable_type', $productType)
                    ->select('invoice_id', DB::raw('SUM(total_amount) as product_total'))
                    ->groupBy('invoice_id'),
                'prod_items',
                'prod_items.invoice_id', '=', 'invoices.id'
            )
            ->where('payments.status', 'confirmed')
            ->where('payments.direction', 'inbound')
            ->where('invoices.subtotal_amount', '>', 0)
            ->whereBetween('payments.payment_date', [$from, $to])
            ->when($this->branch_id, fn ($q) => $q->where('payments.branch_id', $this->branch_id))
            ->when($academyId, fn ($q) => $q->where('payments.academy_id', $academyId))
            ->value(DB::raw('SUM(payments.amount * prod_items.product_total / invoices.subtotal_amount)')) ?? 0;

        // Top programs by revenue
        [$topProgramsSql, $topProgramsBindings] = $this->buildTopProgramsQuery($from, $to, $academyId);
        $topPrograms = DB::select($topProgramsSql, $topProgramsBindings);

        // Per-product revenue breakdown
        $byProduct = DB::table('payments')
            ->join('invoices', 'payments.invoice_id', '=', 'invoices.id')
            ->join('invoice_items', fn ($j) => $j->on('invoice_items.invoice_id', '=', 'invoices.id')->where('invoice_items.itemable_type', '=', $productType))
            ->join('products', 'invoice_items.itemable_id', '=', 'products.id')
            ->where('payments.status', 'confirmed')
            ->where('payments.direction', 'inbound')
            ->where('invoices.subtotal_amount', '>', 0)
            ->whereBetween('payments.payment_date', [$from, $to])
            ->when($this->branch_id, fn ($q) => $q->where('payments.branch_id', $this->branch_id))
            ->when($academyId, fn ($q) => $q->where('payments.academy_id', $academyId))
            ->select(
                'products.id as product_id',
                'products.name_ar as product_name',
                DB::raw('SUM(payments.amount * invoice_items.total_amount / invoices.subtotal_amount) as total'),
                DB::raw('SUM(invoice_items.quantity) as units_sold')
            )
            ->groupBy('products.id', 'products.name_ar')
            ->orderByDesc('total')
            ->limit(10)
            ->get()
            ->toArray();

        return [
            'subscription_revenue' => $subscriptionRevenue,
            'product_revenue' => $productRevenue,
            'top_programs' => $topPrograms,
            'by_product' => $byProduct,
        ];
    }

    /**
     * Build the top-programs query and its bindings.
     *
     * Split out from getRevenueBreakdown() so the SQL and its bindings can be
     * asserted on without a database connection.
     */
    private function buildTopProgramsQuery($from, $to, ?int $academyId): array
    {
        // Attributing subscription money to a programme is only exact when the
        // enrolment records which invoice billed it — and in practice most do
        // not (88 of 350 on the live instance). So: use the exact link where it
        // exists, fall back to the participant's own enrolments otherwise, and
        // split an invoice evenly across however many programmes it maps to.
        //
        // The previous version joined on participant_id alone, which fanned a
        // payment across every programme that participant had ever joined, then
        // used SUM(DISTINCT) to hide the inflation — which instead discarded
        // genuinely equal amounts. Measured against live data it reported about
        // 12% of the real figure.
        //
        // Written as raw SQL because the CTE + UNION shape does not express
        // cleanly through the query builder; every value is bound.
        //
        // Postgres decides a prepared-statement parameter's type during parse
        // analysis, and `:branch_id IS NULL` gives it nothing to work from: the
        // statement is rejected with 42P08 before it ever reaches the comparison
        // that would have typed it. So the optional filters are appended only
        // when they apply, the way the ->when() filters in getRevenueBreakdown()
        // do — never bound as a NULL the driver has to guess a type for.
        $bindings = [
            'billable' => Participant::class,
            'from' => $from,
            'to' => $to,
        ];

        $filters = '';

        if ($branchId = (int) $this->branch_id) {
            $filters .= "\n      AND p.branch_id = :branch_id";
            $bindings['branch_id'] = $branchId;
        }

        if ($academyId) {
            $filters .= "\n      AND p.academy_id = :academy_id";
            $bindings['academy_id'] = $academyId;
        }

        $topProgramsSql = <<<SQL
            WITH sub AS (
                SELECT invoice_id, SUM(total_amount) AS sub_total
                FROM invoice_items
                WHERE itemable_type IS NULL
                GROUP BY invoice_id
            ),
            map AS (
                SELECT DISTINCT i.id AS invoice_id, e.training_program_id
                FROM invoices i
                JOIN enrollments e ON e.invoice_id = i.id
                UNION
                SELECT DISTINCT i.id, e.training_program_id
                FROM invoices i
                JOIN enrollments e ON e.participant_id = i.billable_id
                WHERE i.billable_type = :billable
                  AND NOT EXISTS (SELECT 1 FROM enrollments e2 WHERE e2.invoice_id = i.id)
            ),
            cnt AS (
                SELECT invoice_id, COUNT(*) AS program_count FROM map GROUP BY invoice_id
            )
            SELECT tp.id AS program_id,
                   tp.name_ar AS program_name,
                   SUM(p.amount * s.sub_total / NULLIF(i.subtotal_amount, 0) / c.program_count) AS total,
                   COUNT(DISTINCT i.billable_id) AS subscriber_count
            FROM payments p
            JOIN invoices i ON p.invoice_id = i.id
            JOIN sub s ON s.invoice_id = i.id
            JOIN map m ON m.invoice_id = i.id
            JOIN cnt c ON c.invoice_id = i.id
            JOIN training_programs tp ON tp.id = m.training_program_id
            WHERE p.status = 'confirmed'
              AND p.direction = 'inbound'
              AND i.subtotal_amount > 0
              AND p.payment_date BETWEEN :from AND :to{$filters}
            GROUP BY tp.id, tp.name_ar
            ORDER BY total DESC
            LIMIT 10
        SQL;

        return [$topProgramsSql, $bindings];
    }

    private function getFinancialMetrics($from, $to, array $revenue, array $expenses, array $collectionRate): array
    {
        $academyId = app('current_academy')?->id;

        // Active participants count
        $activeParticipants = Participant::where('status', 'active')
            ->when($this->branch_id, fn ($q) => $q->where('branch_id', $this->branch_id))
            ->count();

        // New participants this period
        $newParticipants = Participant::whereBetween('created_at', [$from, $to])
            ->when($this->branch_id, fn ($q) => $q->where('branch_id', $this->branch_id))
            ->count();

        // Active enrollments
        $activeEnrollments = Enrollment::where('status', 'active')
            ->when($this->branch_id, fn ($q) => $q->whereHas('group', fn ($g) => $g->where('branch_id', $this->branch_id)))
            ->count();

        // New enrollments this period
        $newEnrollments = Enrollment::whereBetween('created_at', [$from, $to])
            ->when($this->branch_id, fn ($q) => $q->whereHas('group', fn ($g) => $g->where('branch_id', $this->branch_id)))
            ->count();

        // Cancelled enrollments this period
        $cancelledEnrollments = Enrollment::where('status', 'cancelled')
            ->whereBetween('updated_at', [$from, $to])
            ->when($this->branch_id, fn ($q) => $q->whereHas('group', fn ($g) => $g->where('branch_id', $this->branch_id)))
            ->count();

        // Average revenue per participant
        $avgRevenuePerParticipant = $activeParticipants > 0 ? (int) round($revenue['total'] / $activeParticipants) : 0;

        // Total outstanding balance (all unpaid invoices)
        $totalOutstanding = Invoice::whereIn('status', [InvoiceStatus::Sent, InvoiceStatus::Overdue, InvoiceStatus::PartiallyPaid])
            ->where('due_amount', '>', 0)
            ->when($this->branch_id, fn ($q) => $q->whereHas('payments', fn ($p) => $p->where('branch_id', $this->branch_id)))
            ->sum('due_amount');

        // Average invoice amount
        $avgInvoice = Invoice::whereBetween('issue_date', [$from, $to])
            ->where('total_amount', '>', 0)
            ->avg('total_amount');

        // Payment count by method
        $paymentsByMethod = Payment::where('direction', 'inbound')
            ->where('status', 'confirmed')
            ->whereBetween('payment_date', [$from, $to])
            ->when($this->branch_id, fn ($q) => $q->where('branch_id', $this->branch_id))
            ->count();

        // Average days to collect (payment_date - invoice.issue_date)
        $avgDaysToCollect = (int) DB::table('payments')
            ->join('invoices', 'payments.invoice_id', '=', 'invoices.id')
            ->where('payments.direction', 'inbound')
            ->where('payments.status', 'confirmed')
            ->whereBetween('payments.payment_date', [$from, $to])
            ->when($this->branch_id, fn ($q) => $q->where('payments.branch_id', $this->branch_id))
            ->whereNotNull('invoices.issue_date')
            ->avg(DB::raw('EXTRACT(DAY FROM payments.payment_date::timestamp - invoices.issue_date::timestamp)')) ?? 0;

        // Profit margin
        $profitMargin = $revenue['total'] > 0 ? round((($revenue['total'] - $expenses['total']) / $revenue['total']) * 100) : 0;

        // Churn rate (cancelled / total active at start)
        $churnRate = $activeEnrollments > 0 ? round(($cancelledEnrollments / ($activeEnrollments + $cancelledEnrollments)) * 100, 1) : 0;

        // Daily average revenue
        $daysInPeriod = max(1, now()->parse($from)->diffInDays(now()->parse($to)));
        $dailyAvgRevenue = (int) round($revenue['total'] / $daysInPeriod);

        return [
            'active_participants' => $activeParticipants,
            'new_participants' => $newParticipants,
            'active_enrollments' => $activeEnrollments,
            'new_enrollments' => $newEnrollments,
            'cancelled_enrollments' => $cancelledEnrollments,
            'avg_revenue_per_participant' => $avgRevenuePerParticipant,
            'total_outstanding' => $totalOutstanding,
            'avg_invoice' => (int) ($avgInvoice ?? 0),
            'payment_count' => $paymentsByMethod,
            'avg_days_to_collect' => $avgDaysToCollect,
            'profit_margin' => $profitMargin,
            'churn_rate' => $churnRate,
            'daily_avg_revenue' => $dailyAvgRevenue,
        ];
    }
}
