<?php

namespace App\Domain\Shared\Services;

use App\Domain\Attendance\Enums\AttendanceStatus;
use App\Domain\Attendance\Models\AttendanceRecord;
use App\Domain\Financial\Models\CashSession;
use App\Domain\Financial\Models\Installment;
use App\Domain\Financial\Models\Invoice;
use App\Domain\Financial\Models\Payment;
use App\Domain\Financial\Models\Wallet;
use App\Domain\HR\Models\Trainer;
use App\Domain\Inventory\Models\InventoryMovement;
use App\Domain\Inventory\Models\Product;
use App\Domain\Participant\Models\Participant;
use App\Domain\POS\Models\POSTransaction;
use App\Domain\Training\Models\Enrollment;
use App\Domain\Training\Models\TrainingGroup;
use App\Domain\Training\Models\TrainingProgram;
use App\Domain\Training\Models\TrainingSession;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class ReportService
{
    // ─── FINANCIAL REPORTS ────────────────────────────────────────

    public function dailyRevenue(string $from, string $to, ?int $branchId = null): Collection
    {
        return Payment::where('direction', 'inbound')->where('status', 'confirmed')
            ->whereBetween('payment_date', [$from, $to])
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->select(
                DB::raw("DATE(payment_date) as date"),
                DB::raw("SUM(amount) as total"),
                DB::raw("COUNT(*) as count")
            )
            ->groupBy('date')
            ->orderBy('date')
            ->get();
    }

    public function outstandingBalances(string $from, string $to, ?int $branchId = null): Collection
    {
        // The invoice's own branch — the branch it was raised at — not the
        // member's. The whereHasMorph this replaces counted an invoice raised
        // at one branch in another branch's outstanding balances whenever the
        // member was registered there, and silently dropped every invoice whose
        // billable is not a Participant: walk-in POS sales and guardian-billed
        // invoices vanished from every branch's report.
        return Invoice::with(['billable.person'])
            ->whereIn('status', ['sent', 'partially_paid', 'overdue'])
            ->where('due_amount', '>', 0)
            ->when($branchId, fn ($q) => $q->where('invoices.branch_id', $branchId))
            ->when($from, fn ($q) => $q->where('created_at', '>=', $from))
            ->when($to, fn ($q) => $q->where('created_at', '<=', $to . ' 23:59:59'))
            ->orderByDesc('due_amount')
            ->get()
            ->map(fn ($inv) => [
                'invoice_number' => $inv->number,
                'participant' => $inv->billable?->person?->name_ar ?? $inv->contact_name ?? '',
                'phone' => $inv->billable?->person?->phone ?? $inv->contact_phone ?? '',
                'total' => $inv->total_amount,
                'paid' => $inv->paid_amount,
                'due' => $inv->due_amount,
                'status' => $inv->status?->value ?? $inv->status,
                'due_date' => $inv->due_date?->format('Y-m-d') ?? '',
                'days_overdue' => $inv->due_date && $inv->due_date->isPast() ? (int) $inv->due_date->diffInDays(now()) : 0,
            ]);
    }

    public function paymentMethodBreakdown(string $from, string $to, ?int $branchId = null): Collection
    {
        return Payment::where('direction', 'inbound')->where('status', 'confirmed')
            ->whereBetween('payment_date', [$from, $to])
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->select('method', DB::raw('SUM(amount) as total'), DB::raw('COUNT(*) as count'))
            ->groupBy('method')
            ->orderByDesc('total')
            ->get();
    }

    public function refundReport(string $from, string $to, ?int $branchId = null): Collection
    {
        return Payment::with(['invoice.billable.person', 'creator'])
            ->where('status', 'refunded')
            ->whereBetween('payment_date', [$from, $to])
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->orderByDesc('payment_date')
            ->get()
            ->map(fn ($p) => [
                'date' => $p->payment_date?->format('Y-m-d'),
                'participant' => $p->invoice?->billable?->person?->name_ar ?? '',
                'amount' => $p->amount,
                'method' => $p->method?->value ?? '',
                'invoice' => $p->invoice?->number ?? '',
                'processed_by' => $p->creator?->name ?? '',
                'reference' => $p->reference ?? '',
            ]);
    }

    public function installmentsDue(string $from, string $to, ?int $branchId = null): Collection
    {
        return Installment::with(['paymentPlan.invoice.billable.person'])
            ->where('status', '!=', 'paid')
            ->whereBetween('due_date', [$from, $to])
            ->when($branchId, fn ($q) => $q->whereHas('paymentPlan.invoice.billable', fn ($p) => $p->where('branch_id', $branchId)))
            ->orderBy('due_date')
            ->get()
            ->map(fn ($inst) => [
                'participant' => $inst->paymentPlan?->invoice?->billable?->person?->name_ar ?? '',
                'phone' => $inst->paymentPlan?->invoice?->billable?->person?->phone ?? '',
                'amount' => $inst->amount,
                'paid' => $inst->status === 'paid' ? $inst->amount : 0,
                'due_date' => $inst->due_date?->format('Y-m-d'),
                'status' => $inst->status,
                'invoice' => $inst->paymentPlan?->invoice?->number ?? '',
            ]);
    }

    public function cashSessionSummary(string $from, string $to, ?int $branchId = null): Collection
    {
        return CashSession::with(['user', 'branch'])
            ->whereBetween('opened_at', [$from, $to . ' 23:59:59'])
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->orderByDesc('opened_at')
            ->get()
            ->map(fn ($cs) => [
                'user' => $cs->user?->name ?? '',
                'branch' => $cs->branch?->name_ar ?? '',
                'opened_at' => $cs->opened_at?->format('Y-m-d H:i'),
                'closed_at' => $cs->closed_at?->format('Y-m-d H:i') ?? '—',
                'opening_balance' => $cs->opening_balance ?? 0,
                'cash_in' => $cs->total_cash_in ?? 0,
                'cash_out' => $cs->total_cash_out ?? 0,
                'expected_balance' => ($cs->opening_balance ?? 0) + ($cs->total_cash_in ?? 0) - ($cs->total_cash_out ?? 0),
                'actual_balance' => $cs->closing_balance ?? 0,
                'variance' => $cs->variance ?? (($cs->closing_balance ?? 0) - (($cs->opening_balance ?? 0) + ($cs->total_cash_in ?? 0) - ($cs->total_cash_out ?? 0))),
                'status' => $cs->status ?? ($cs->closed_at ? 'closed' : 'open'),
            ]);
    }

    // ─── PARTICIPANT REPORTS ─────────────────────────────────────

    public function newRegistrations(string $from, string $to, ?int $branchId = null): Collection
    {
        return Participant::with(['person', 'branch', 'primaryActivity'])
            ->whereBetween('registration_date', [$from, $to])
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->orderByDesc('registration_date')
            ->get()
            ->map(fn ($p) => [
                'name' => $p->person?->name_ar ?? '',
                'phone' => $p->person?->phone ?? '',
                'age' => $p->person?->date_of_birth ? $p->person->date_of_birth->age : '',
                'gender' => $p->person?->gender ?? '',
                'activity' => $p->primaryActivity?->name_ar ?? '',
                'branch' => $p->branch?->name_ar ?? '',
                'source' => $p->registration_source?->value ?? $p->registration_source ?? '',
                'date' => $p->registration_date?->format('Y-m-d'),
                'status' => $p->status?->value ?? $p->status,
            ]);
    }

    public function participantsByStatus(?int $branchId = null): Collection
    {
        return Participant::when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->select('status', DB::raw('COUNT(*) as count'))
            ->groupBy('status')
            ->orderByDesc('count')
            ->get();
    }

    public function participantsByAge(?int $branchId = null): Collection
    {
        return Participant::with('person')
            ->where('status', 'active')
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->get()
            ->map(fn ($p) => $p->person?->date_of_birth ? $p->person->date_of_birth->age : null)
            ->filter()
            ->groupBy(function ($age) {
                if ($age < 6) return '0-5';
                if ($age < 10) return '6-9';
                if ($age < 14) return '10-13';
                if ($age < 18) return '14-17';
                return '18+';
            })
            ->map(fn ($group, $range) => ['range' => $range, 'count' => $group->count()])
            ->sortBy('range')
            ->values();
    }

    public function participantsByGender(?int $branchId = null): Collection
    {
        return Participant::join('people', 'participants.person_id', '=', 'people.id')
            ->where('participants.status', 'active')
            ->when($branchId, fn ($q) => $q->where('participants.branch_id', $branchId))
            ->select('people.gender', DB::raw('COUNT(*) as count'))
            ->groupBy('people.gender')
            ->get();
    }

    public function expiredMemberships(string $from, string $to, ?int $branchId = null): Collection
    {
        $from = $from ?: now()->startOfYear()->format('Y-m-d');
        $to = $to ?: now()->format('Y-m-d');

        return Participant::with(['person', 'branch'])
            ->where('membership_type', 'member')
            ->whereBetween('membership_expires_at', [$from, $to])
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->orderBy('membership_expires_at')
            ->get()
            ->map(fn ($p) => [
                'name' => $p->person?->name_ar ?? '',
                'phone' => $p->person?->phone ?? '',
                'membership_id' => $p->membership_id ?? '',
                'expires_at' => $p->membership_expires_at?->format('Y-m-d'),
                'status' => $p->status?->value ?? $p->status,
                'branch' => $p->branch?->name_ar ?? '',
                'days_until' => $p->membership_expires_at?->diffInDays(now(), false),
            ]);
    }

    public function withoutMedicalClearance(?int $branchId = null): Collection
    {
        return Participant::with(['person', 'branch'])
            ->whereIn('status', ['registered', 'active'])
            ->where(function ($q) {
                $q->where('medical_clearance', false)
                  ->orWhereNull('medical_clearance');
            })
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->orderBy('created_at', 'desc')
            ->get()
            ->map(fn ($p) => [
                'name' => $p->person?->name_ar ?? '',
                'phone' => $p->person?->phone ?? '',
                'status' => $p->status?->value ?? $p->status,
                'branch' => $p->branch?->name_ar ?? '',
                'registration_date' => $p->registration_date?->format('Y-m-d') ?? $p->created_at?->format('Y-m-d'),
                'clearance_expires' => $p->medical_clearance_expires?->format('Y-m-d') ?? '—',
            ]);
    }

    public function frozenAndSuspended(?int $branchId = null): Collection
    {
        return Participant::with(['person', 'branch'])
            ->whereIn('status', ['frozen', 'suspended'])
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->orderBy('status_changed_at')
            ->get()
            ->map(fn ($p) => [
                'name' => $p->person?->name_ar ?? '',
                'phone' => $p->person?->phone ?? '',
                'status' => $p->status?->value ?? $p->status,
                'reason' => $p->status_reason ?? '',
                'since' => $p->status_changed_at?->format('Y-m-d') ?? '',
                'branch' => $p->branch?->name_ar ?? '',
                'balance' => $p->outstanding_balance ?? 0,
            ]);
    }

    // ─── ATTENDANCE REPORTS ──────────────────────────────────────

    public function attendanceByGroup(string $from, string $to, ?int $branchId = null): Collection
    {
        return TrainingGroup::with('program')
            ->where('status', 'active')
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->get()
            ->map(function ($group) use ($from, $to) {
                $records = AttendanceRecord::whereHas('session', fn ($q) => $q->where('training_group_id', $group->id))
                    ->whereBetween('created_at', [$from, $to . ' 23:59:59']);
                $total = (clone $records)->count();
                $excluded = (clone $records)->whereIn('status', [AttendanceStatus::Cancelled, AttendanceStatus::Exempt])->count();
                $positive = (clone $records)->whereIn('status', [AttendanceStatus::Present, AttendanceStatus::Late, AttendanceStatus::Partial])->count();
                $denominator = $total - $excluded;

                return [
                    'group' => $group->name_ar,
                    'program' => $group->program?->name_ar ?? '',
                    'total_records' => $total,
                    'present' => $positive,
                    'absent' => (clone $records)->where('status', AttendanceStatus::Absent)->count(),
                    'rate' => $denominator > 0 ? round(($positive / $denominator) * 100, 1) : 0,
                ];
            })
            ->sortBy('rate');
    }

    public function absenteeReport(string $from, string $to, ?int $branchId = null): Collection
    {
        return AttendanceRecord::with(['subject', 'session.group'])
            ->whereIn('status', [AttendanceStatus::Absent, AttendanceStatus::NoShow])
            ->whereBetween('created_at', [$from, $to . ' 23:59:59'])
            ->when($branchId, fn ($q) => $q->whereHas('session.group', fn ($g) => $g->where('branch_id', $branchId)))
            ->get()
            ->groupBy('subject_id')
            ->map(function ($records) {
                $first = $records->first();
                $person = null;
                if ($first->subject_type === 'App\\Domain\\Participant\\Models\\Participant') {
                    $person = $first->subject?->person;
                }
                return [
                    'name' => $person?->name_ar ?? '',
                    'phone' => $person?->phone ?? '',
                    'absent_count' => $records->count(),
                    'groups' => $records->pluck('session.group.name_ar')->unique()->filter()->implode(', '),
                    'last_absence' => $records->max('created_at')?->format('Y-m-d') ?? '',
                ];
            })
            ->sortByDesc('absent_count')
            ->values();
    }

    public function absentParticipantsByTrainer(string $from, string $to, ?int $branchId = null, int $minAbsences = 1): Collection
    {
        return AttendanceRecord::with(['subject.person', 'session.group.headTrainer.person'])
            ->where('subject_type', 'App\\Domain\\Participant\\Models\\Participant')
            ->whereIn('status', [AttendanceStatus::Absent, AttendanceStatus::NoShow])
            ->whereBetween('created_at', [$from, $to . ' 23:59:59'])
            ->when($branchId, fn ($q) => $q->whereHas('session.group', fn ($g) => $g->where('branch_id', $branchId)))
            ->whereHas('subject', fn ($q) => $q->where('status', '!=', 'suspended'))
            ->get()
            ->groupBy('subject_id')
            ->filter(fn ($records) => $records->count() >= $minAbsences)
            ->map(function ($records) {
                $first = $records->first();
                $participant = $first->subject;
                $person = $participant?->person;
                $group = $records->first()->session?->group;
                $trainer = $group?->headTrainer?->person;

                return [
                    'name' => $person?->name_ar ?? '',
                    'phone' => $person?->phone ?? '',
                    'absent_count' => $records->count(),
                    'group' => $group?->name_ar ?? '',
                    'trainer' => $trainer?->name_ar ?? '',
                    'last_absence' => $records->max('created_at')?->format('Y-m-d') ?? '',
                ];
            })
            ->sortBy('trainer')
            ->values();
    }

    public function trainerAttendance(string $from, string $to, ?int $branchId = null): Collection
    {
        return Trainer::with(['person', 'employee'])
            ->where('status', 'active')
            ->get()
            ->map(function ($trainer) use ($from, $to, $branchId) {
                $userId = $trainer->employee?->user_id;
                if (! $userId) {
                    return null;
                }

                $records = AttendanceRecord::where('subject_type', \App\Models\User::class)
                    ->where('subject_id', $userId)
                    ->whereBetween('created_at', [$from, $to . ' 23:59:59'])
                    ->when($branchId, fn ($q) => $q->whereHas('session.group', fn ($g) => $g->where('branch_id', $branchId)));
                $total = (clone $records)->count();
                $present = (clone $records)->whereIn('status', [AttendanceStatus::Present, AttendanceStatus::Late])->count();

                return [
                    'name' => $trainer->person?->name_ar ?? '',
                    'phone' => $trainer->person?->phone ?? '',
                    'total_sessions' => $total,
                    'attended' => $present,
                    'absent' => (clone $records)->where('status', AttendanceStatus::Absent)->count(),
                    'rate' => $total > 0 ? round(($present / $total) * 100, 1) : 0,
                ];
            })
            ->filter(fn ($t) => $t && $t['total_sessions'] > 0)
            ->sortBy('rate')
            ->values();
    }

    // ─── ENROLLMENT REPORTS ──────────────────────────────────────

    public function enrollmentsByProgram(string $from, string $to, ?int $branchId = null): Collection
    {
        return TrainingProgram::withCount(['enrollments' => function ($q) use ($from, $to, $branchId) {
            $q->whereBetween('enrollment_date', [$from, $to])
                ->when($branchId, fn ($q2) => $q2->whereHas('group', fn ($g) => $g->where('branch_id', $branchId)));
        }])
            ->get()
            ->filter(fn ($prog) => $prog->enrollments_count > 0)
            ->sortByDesc('enrollments_count')
            ->values()
            ->map(fn ($prog) => [
                'program' => $prog->name_ar,
                'enrollments' => $prog->enrollments_count,
                'status' => $prog->status ?? 'active',
            ]);
    }

    public function groupCapacity(?int $branchId = null): Collection
    {
        return TrainingGroup::with(['program', 'branch'])
            ->where('status', 'active')
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->get()
            ->map(fn ($g) => [
                'group' => $g->name_ar,
                'program' => $g->program?->name_ar ?? '',
                'branch' => $g->branch?->name_ar ?? '',
                'current' => $g->current_count ?? 0,
                'max' => $g->max_capacity ?? 0,
                'fill_rate' => $g->max_capacity > 0 ? round(($g->current_count / $g->max_capacity) * 100, 1) : 0,
                'available' => max(0, ($g->max_capacity ?? 0) - ($g->current_count ?? 0)),
            ])
            ->sortByDesc('fill_rate')
            ->values();
    }

    public function cancellationsReport(string $from, string $to, ?int $branchId = null): Collection
    {
        return Enrollment::with(['participant.person', 'group.program'])
            ->where('status', 'cancelled')
            ->whereBetween('updated_at', [$from, $to . ' 23:59:59'])
            ->when($branchId, fn ($q) => $q->whereHas('group', fn ($g) => $g->where('branch_id', $branchId)))
            ->orderByDesc('updated_at')
            ->get()
            ->map(fn ($e) => [
                'participant' => $e->participant?->person?->name_ar ?? '',
                'phone' => $e->participant?->person?->phone ?? '',
                'program' => $e->group?->program?->name_ar ?? '',
                'group' => $e->group?->name_ar ?? '',
                'enrolled_at' => $e->enrollment_date?->format('Y-m-d') ?? '',
                'cancelled_at' => $e->updated_at?->format('Y-m-d'),
                'reason' => $e->withdrawal_reason ?? '',
            ]);
    }

    public function retentionReport(string $from, string $to, ?int $branchId = null): Collection
    {
        $active = Enrollment::where('status', 'active')
            ->when($branchId, fn ($q) => $q->whereHas('group', fn ($g) => $g->where('branch_id', $branchId)))
            ->whereBetween('enrollment_date', [$from, $to])
            ->count();
        $cancelled = Enrollment::where('status', 'cancelled')
            ->when($branchId, fn ($q) => $q->whereHas('group', fn ($g) => $g->where('branch_id', $branchId)))
            ->whereBetween('enrollment_date', [$from, $to])
            ->count();
        $completed = Enrollment::where('status', 'completed')
            ->when($branchId, fn ($q) => $q->whereHas('group', fn ($g) => $g->where('branch_id', $branchId)))
            ->whereBetween('enrollment_date', [$from, $to])
            ->count();
        $total = $active + $cancelled + $completed;

        return collect([
            ['metric' => 'إجمالي التسجيلات', 'value' => $total],
            ['metric' => 'نشط', 'value' => $active],
            ['metric' => 'مكتمل', 'value' => $completed],
            ['metric' => 'ملغي', 'value' => $cancelled],
            ['metric' => 'نسبة الاستمرارية', 'value' => $total > 0 ? round((($active + $completed) / $total) * 100, 1) . '%' : '0%'],
        ]);
    }

    // ─── HR & TRAINER REPORTS ────────────────────────────────────

    public function trainerWorkload(string $from, string $to, ?int $branchId = null): Collection
    {
        return Trainer::with(['person', 'employee'])
            ->where('status', 'active')
            ->get()
            ->map(function ($trainer) use ($from, $to, $branchId) {
                $userId = $trainer->employee?->user_id;
                if (!$userId) {
                    return null;
                }

                $sessions = TrainingSession::where('trainer_id', $userId)
                    ->whereBetween('session_date', [$from, $to])
                    ->when($branchId, fn ($q) => $q->whereHas('group', fn ($g) => $g->where('branch_id', $branchId)));

                $groupsCount = TrainingGroup::where('head_trainer_id', $userId)
                    ->where('status', 'active')
                    ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
                    ->count();

                return [
                    'name' => $trainer->person?->name_ar ?? '',
                    'phone' => $trainer->person?->phone ?? '',
                    'total_sessions' => (clone $sessions)->count(),
                    'completed' => (clone $sessions)->where('status', 'completed')->count(),
                    'cancelled' => (clone $sessions)->where('status', 'cancelled')->count(),
                    'groups_count' => $groupsCount,
                ];
            })
            ->filter(fn ($t) => $t && $t['total_sessions'] > 0)
            ->sortByDesc('total_sessions')
            ->values();
    }

    // ─── INVENTORY REPORTS ───────────────────────────────────────

    /**
     * Products at or below their reorder point.
     *
     * This method took a $branchId and then never used it: the products were
     * unfiltered and the on-hand figure came from `inventoryLevels->first()`,
     * an arbitrary warehouse row that could belong to another branch entirely.
     * The report then decided 'نفد' / 'منخفض' from someone else's shelf.
     *
     * Both halves are branch-filtered now: the products by their own scope,
     * and the levels by the warehouses they sit in. The on-hand figure is
     * summed across this branch's warehouses instead of picking one.
     */
    public function lowStockReport(?int $branchId = null): Collection
    {
        return Product::with([
                'category',
                'inventoryLevels' => fn ($q) => $q->when(
                    $branchId,
                    fn ($l) => $l->whereHas('warehouse', fn ($w) => $w
                        ->where('branch_id', $branchId))
                ),
            ])
            ->where('track_inventory', true)
            ->where('is_active', true)
            ->when($branchId, fn ($q) => $q->where(
                fn ($w) => $w->where('branch_id', $branchId)
            ))
            ->get()
            ->map(function ($product) {
                $onHand = (int) $product->inventoryLevels->sum('quantity_on_hand');
                $minStock = $product->min_stock_level ?? 0;
                return [
                    'product' => $product->name_ar ?? $product->name ?? '',
                    'sku' => $product->sku ?? '',
                    'category' => $product->category?->name_ar ?? '',
                    'on_hand' => $onHand,
                    'reorder_point' => $minStock,
                    'status' => $onHand <= 0 ? 'نفد' : ($onHand <= $minStock ? 'منخفض' : 'متوفر'),
                ];
            })
            ->filter(fn ($p) => $p['on_hand'] <= $p['reorder_point'])
            ->sortBy('on_hand')
            ->values();
    }

    public function inventoryMovementReport(string $from, string $to, ?int $branchId = null): Collection
    {
        return InventoryMovement::with(['product', 'warehouse'])
            ->whereBetween('created_at', [$from, $to . ' 23:59:59'])
            ->when($branchId, fn ($q) => $q->whereHas('warehouse', fn ($w) => $w->where('branch_id', $branchId)))
            ->orderByDesc('created_at')
            ->limit(500)
            ->get()
            ->map(fn ($m) => [
                'date' => $m->created_at?->format('Y-m-d'),
                'product' => $m->product?->name_ar ?? '',
                'warehouse' => $m->warehouse?->name_ar ?? '',
                'type' => $m->movement_type ?? '',
                'direction' => $m->direction ?? '',
                'quantity' => $m->quantity,
                'before' => $m->quantity_before ?? 0,
                'after' => $m->quantity_after ?? 0,
                'reference' => $m->reference_type ? class_basename($m->reference_type) . '#' . $m->reference_id : '—',
            ]);
    }

    public function productSalesReport(string $from, string $to, ?int $branchId = null): Collection
    {
        return DB::table('pos_transaction_items')
            ->join('pos_transactions', 'pos_transactions.id', '=', 'pos_transaction_items.pos_transaction_id')
            ->join('products', 'products.id', '=', 'pos_transaction_items.item_id')
            ->where('pos_transaction_items.item_type', 'product')
            ->whereBetween('pos_transactions.processed_at', [$from, $to . ' 23:59:59'])
            ->when($branchId, fn ($q) => $q->where('pos_transactions.branch_id', $branchId))
            ->select(
                'products.name_ar as product',
                'products.sku',
                DB::raw('SUM(pos_transaction_items.quantity) as units_sold'),
                DB::raw('SUM(pos_transaction_items.line_total) as revenue')
            )
            ->groupBy('products.id', 'products.name_ar', 'products.sku')
            ->orderByDesc('revenue')
            ->get();
    }

    // ─── POS REPORTS ─────────────────────────────────────────────

    public function posTransactionReport(string $from, string $to, ?int $branchId = null): Collection
    {
        return POSTransaction::with(['processedBy', 'branch'])
            ->whereBetween('processed_at', [$from, $to . ' 23:59:59'])
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->orderByDesc('processed_at')
            ->limit(500)
            ->get()
            ->map(fn ($t) => [
                'date' => $t->processed_at?->format('Y-m-d H:i'),
                'number' => $t->receipt_number ?? '',
                'total' => $t->total_amount,
                'discount' => $t->discount_amount ?? 0,
                'items_count' => $t->items()->count(),
                'payment_method' => $t->payment_method ?? '',
                'cashier' => $t->processedBy?->name ?? '',
                'branch' => $t->branch?->name_ar ?? '',
            ]);
    }

    public function posDailySummary(string $from, string $to, ?int $branchId = null): Collection
    {
        return POSTransaction::whereBetween('processed_at', [$from, $to . ' 23:59:59'])
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->select(
                DB::raw("DATE(processed_at) as date"),
                DB::raw('SUM(total_amount) as total'),
                DB::raw('SUM(discount_amount) as discounts'),
                DB::raw('COUNT(*) as transactions')
            )
            ->groupBy('date')
            ->orderBy('date')
            ->get();
    }

    // ─── OPERATIONAL REPORTS ─────────────────────────────────────

    public function sessionCompletionReport(string $from, string $to, ?int $branchId = null): Collection
    {
        return TrainingSession::whereBetween('session_date', [$from, $to])
            ->when($branchId, fn ($q) => $q->whereHas('group', fn ($g) => $g->where('branch_id', $branchId)))
            ->select('status', DB::raw('COUNT(*) as count'))
            ->groupBy('status')
            ->get();
    }

    /**
     * Revenue, members and new enrolments side by side, one row per branch.
     *
     * Cross-branch by definition — comparing a branch with itself is not a
     * comparison — so the suppression is explicit rather than accidental.
     * Scoped, this would still list every branch and fill in zeros for all but
     * the selected one, which reads as "those branches earned nothing" rather
     * than "you are not looking at those branches".
     */
    public function branchComparison(string $from, string $to): Collection
    {
        return Payment::acrossBranches(fn () => DB::table('branches')
            ->where('branches.is_active', true)
            ->leftJoin('participants', function ($join) {
                $join->on('branches.id', '=', 'participants.branch_id')
                    ->where('participants.status', '=', 'active');
            })
            ->select(
                'branches.id',
                'branches.name_ar as branch',
                DB::raw('COUNT(DISTINCT participants.id) as active_participants')
            )
            ->groupBy('branches.id', 'branches.name_ar')
            ->get()
            ->map(function ($branch) use ($from, $to) {
                $revenue = Payment::where('direction', 'inbound')->where('status', 'confirmed')
                    ->where('branch_id', $branch->id)
                    ->whereBetween('payment_date', [$from, $to])
                    ->sum('amount');
                $enrollments = Enrollment::whereHas('group', fn ($q) => $q->where('branch_id', $branch->id))
                    ->whereBetween('enrollment_date', [$from, $to])
                    ->count();

                return [
                    'branch' => $branch->branch,
                    'active_participants' => $branch->active_participants,
                    'revenue' => $revenue,
                    'new_enrollments' => $enrollments,
                ];
            }));
    }

    public function walletBalances(?int $branchId = null): Collection
    {
        return Wallet::with(['owner.person'])
            ->where('owner_type', 'App\\Domain\\Participant\\Models\\Participant')
            ->where('is_active', true)
            ->where('balance', '>', 0)
            ->when($branchId, fn ($q) => $q->whereHasMorph('owner', [Participant::class], fn ($p) => $p->where('branch_id', $branchId)))
            ->orderByDesc('balance')
            ->get()
            ->map(fn ($w) => [
                'participant' => $w->owner?->person?->name_ar ?? '',
                'phone' => $w->owner?->person?->phone ?? '',
                'balance' => $w->balance,
                'frozen' => 0,
                'available' => $w->balance,
                'last_transaction' => $w->updated_at?->format('Y-m-d'),
            ]);
    }

    public function revenueByActivity(string $from, string $to, ?int $branchId = null): Collection
    {
        return DB::table('invoice_items')
            ->join('invoices', 'invoices.id', '=', 'invoice_items.invoice_id')
            ->join('training_programs', 'training_programs.id', '=', 'invoice_items.itemable_id')
            ->join('activities', 'activities.id', '=', 'training_programs.activity_id')
            ->where('invoice_items.itemable_type', 'App\\Domain\\Training\\Models\\TrainingProgram')
            ->whereIn('invoices.status', ['paid', 'partially_paid'])
            ->whereBetween('invoices.created_at', [$from, $to . ' 23:59:59'])
            // The invoice's own branch. This used to bolt on a join to
            // participants and filter that table's branch_id: a query-builder
            // call carries no global scope, so the filter was both the wrong
            // definition of branch and, because the join was inside the
            // when(), it dropped every invoice not billed to a Participant.
            ->when($branchId, fn ($q) => $q->where('invoices.branch_id', $branchId))
            ->select(
                'activities.name_ar as activity',
                DB::raw('SUM(invoice_items.total_amount) as revenue'),
                DB::raw('COUNT(DISTINCT invoices.id) as invoices_count')
            )
            ->groupBy('activities.id', 'activities.name_ar')
            ->orderByDesc('revenue')
            ->get();
    }

    public function overdueInvoicesAging(?int $branchId = null): Collection
    {
        // Same correction as outstandingBalances: the invoice's branch, so the
        // ageing buckets match the outstanding total they are supposed to
        // explain, and non-participant invoices are not dropped.
        return Invoice::with(['billable.person'])
            ->where('status', 'overdue')
            ->when($branchId, fn ($q) => $q->where('invoices.branch_id', $branchId))
            ->orderBy('due_date')
            ->get()
            ->map(function ($inv) {
                $days = $inv->due_date ? (int) $inv->due_date->diffInDays(now()) : 0;
                return [
                    'invoice' => $inv->number,
                    'participant' => $inv->billable?->person?->name_ar ?? $inv->contact_name ?? '',
                    'phone' => $inv->billable?->person?->phone ?? $inv->contact_phone ?? '',
                    'total' => $inv->total_amount,
                    'due' => $inv->due_amount,
                    'due_date' => $inv->due_date?->format('Y-m-d') ?? '',
                    'days_overdue' => $days,
                    'aging_bucket' => $days <= 30 ? '1-30 يوم' : ($days <= 60 ? '31-60 يوم' : ($days <= 90 ? '61-90 يوم' : '90+ يوم')),
                ];
            });
    }

    // ─── LEGACY (kept for old reports page) ─────────────────────

    public function financialSummary(string $from, string $to, ?int $branchId = null): array
    {
        return [
            // Invoiced and outstanding read the invoice's own branch, the way
            // collected and pos_revenue beside them already do. Two definitions
            // of "branch" on one KPI card meant the halves never reconciled.
            'total_invoiced' => Invoice::whereBetween('created_at', [$from, $to])
                ->when($branchId, fn ($q) => $q->where('invoices.branch_id', $branchId))->sum('total_amount'),
            'total_collected' => Payment::where('direction', 'inbound')->where('status', 'confirmed')
                ->whereBetween('created_at', [$from, $to])
                ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))->sum('amount'),
            'total_outstanding' => Invoice::whereIn('status', ['sent', 'partially_paid', 'overdue'])
                ->when($branchId, fn ($q) => $q->where('invoices.branch_id', $branchId))->sum('due_amount'),
            'pos_revenue' => POSTransaction::whereBetween('processed_at', [$from, $to])
                ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))->sum('total_amount'),
            'payment_methods' => Payment::where('direction', 'inbound')->where('status', 'confirmed')
                ->whereBetween('created_at', [$from, $to])
                ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
                ->select('method', DB::raw('SUM(amount) as total'), DB::raw('COUNT(*) as count'))
                ->groupBy('method')
                ->get()->toArray(),
        ];
    }

    public function attendanceReport(string $from, string $to, ?int $groupId = null, ?int $branchId = null): array
    {
        // Additive, not exclusive. The branch condition used to sit in the else
        // arm, so passing a group id — which the reports page takes straight
        // from the browser — dropped the branch filter entirely and reported on
        // any group in the academy.
        $query = AttendanceRecord::whereBetween('created_at', [$from, $to])
            ->when($branchId, fn ($q) => $q->whereHas(
                'session.group',
                fn ($g) => $g->where('branch_id', $branchId)
            ))
            ->when($groupId, fn ($q) => $q->whereHas(
                'session',
                fn ($s) => $s->where('training_group_id', $groupId)
            ));

        $total = (clone $query)->count();
        $excluded = (clone $query)->whereIn('status', [AttendanceStatus::Cancelled, AttendanceStatus::Exempt])->count();
        $positive = (clone $query)->whereIn('status', [AttendanceStatus::Present, AttendanceStatus::Late, AttendanceStatus::Partial])->count();
        $denominator = $total - $excluded;

        return [
            'total_records' => $total,
            'present' => (clone $query)->where('status', AttendanceStatus::Present)->count(),
            'late' => (clone $query)->where('status', AttendanceStatus::Late)->count(),
            'absent' => (clone $query)->where('status', AttendanceStatus::Absent)->count(),
            'excused' => (clone $query)->where('status', AttendanceStatus::Excused)->count(),
            'rate' => $denominator > 0 ? round(($positive / $denominator) * 100, 1) : 0,
        ];
    }

    public function enrollmentReport(string $from, string $to, ?int $branchId = null): array
    {
        return [
            'new_enrollments' => Enrollment::whereBetween('enrollment_date', [$from, $to])
                ->when($branchId, fn ($q) => $q->whereHas('group', fn ($g) => $g->where('branch_id', $branchId)))->count(),
            'by_status' => Enrollment::whereBetween('enrollment_date', [$from, $to])
                ->when($branchId, fn ($q) => $q->whereHas('group', fn ($g) => $g->where('branch_id', $branchId)))
                ->select('status', DB::raw('COUNT(*) as count'))
                ->groupBy('status')
                ->pluck('count', 'status')->toArray(),
            'active_participants' => Participant::where('status', 'active')
                ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))->count(),
            'by_membership_type' => Participant::where('status', 'active')
                ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
                ->whereNotNull('membership_type')
                ->select('membership_type', DB::raw('COUNT(*) as count'))
                ->groupBy('membership_type')
                ->pluck('count', 'membership_type')->toArray(),
        ];
    }

    public function expenseSummary(string $from, string $to, ?int $branchId = null): Collection
    {
        return \App\Domain\Financial\Models\Expense::with(['creator'])
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->whereBetween('expense_date', [$from, $to])
            ->orderByDesc('expense_date')
            ->get()
            ->map(fn ($e) => [
                'date' => $e->expense_date?->format('Y-m-d'),
                'category' => $e->category->label(),
                'description' => $e->description,
                'amount' => $e->amount,
                'recipient' => $e->recipient_name ?? '—',
                'payment_method' => $e->payment_method->value,
                'created_by' => $e->creator?->name,
            ]);
    }

    public function facilityRentSummary(string $from, string $to, ?int $branchId = null): Collection
    {
        return \App\Domain\Financial\Models\FacilityRentPayment::with(['facility', 'creator'])
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->whereBetween('payment_date', [$from, $to])
            ->orderByDesc('payment_date')
            ->get()
            ->map(fn ($r) => [
                'facility' => $r->facility?->name_ar,
                'period' => $r->period,
                'amount' => $r->amount,
                'payment_method' => $r->payment_method->value,
                'cheque_number' => $r->cheque_number ?? '—',
                'payment_date' => $r->payment_date?->format('Y-m-d'),
                'created_by' => $r->creator?->name,
            ]);
    }

    /**
     * The member export.
     *
     * $branchId is a real parameter here, like it is on the other thirty-odd
     * methods in this file — it used to be an optional key inside $filters, so
     * a caller passing only a status exported every branch's members: names,
     * phones and the groups they train with. The $filters['branch_id'] key
     * still works, and is the narrower of the two when both are given.
     */
    public function participantList(array $filters = [], ?int $branchId = null): Collection
    {
        $query = Participant::with(['person', 'enrollments.group'])
            ->when($filters['status'] ?? null, fn ($q, $s) => $q->where('status', $s))
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->when($filters['branch_id'] ?? null, fn ($q, $b) => $q->where('branch_id', $b))
            ->when($filters['group_id'] ?? null, fn ($q, $g) => $q->whereHas(
                'enrollments',
                fn ($eq) => $eq->where('training_group_id', $g)->where('status', 'active')
            ));

        return $query->get()->map(fn ($p) => [
            'name' => $p->person?->name_ar,
            'phone' => $p->person?->phone,
            'status' => $p->status,
            'membership_type' => $p->membership_type ?? 'regular',
            'active_groups' => $p->enrollments->where('status', 'active')
                ->map(fn ($e) => $e->group?->name_ar)->filter()->implode(', '),
            'joined_at' => $p->created_at?->format('Y-m-d'),
        ]);
    }
}
