<?php

namespace App\Livewire\Dashboard;

use App\Domain\Financial\Models\Payment;
use App\Domain\Shared\Traits\UsesBranchScope;
use App\Domain\Training\Models\Enrollment;
use Illuminate\Support\Facades\DB;
use Livewire\Component;

class SubscriptionRevenueWidget extends Component
{
    use UsesBranchScope;

    public string $period = 'month';

    public function render()
    {
        $startDate = match ($this->period) {
            'today' => now()->startOfDay(),
            'week' => now()->startOfWeek(),
            'month' => now()->startOfMonth(),
            'year' => now()->startOfYear(),
            default => now()->startOfMonth(),
        };

        $previousStart = match ($this->period) {
            'today' => now()->subDay()->startOfDay(),
            'week' => now()->subWeek()->startOfWeek(),
            'month' => now()->subMonth()->startOfMonth(),
            'year' => now()->subYear()->startOfYear(),
            default => now()->subMonth()->startOfMonth(),
        };

        $academyId = app('current_academy')?->id;
        $branchId = $this->getActiveBranchId();

        // Current period subscription revenue (pro-rata from invoice items)
        $totalSubscriptionRevenue = $this->getSubscriptionRevenue($startDate, null, $academyId, $branchId);

        // Previous period
        $previousSubscriptionRevenue = $this->getSubscriptionRevenue($previousStart, $startDate, $academyId, $branchId);

        $change = $previousSubscriptionRevenue > 0
            ? round(($totalSubscriptionRevenue - $previousSubscriptionRevenue) / $previousSubscriptionRevenue * 100, 1)
            : ($totalSubscriptionRevenue > 0 ? 100 : 0);

        // Revenue by activity
        $byActivity = collect($this->getRevenueByActivity($startDate, $academyId, $branchId));

        // Top programs by subscription revenue
        $topPrograms = collect($this->getTopPrograms($startDate, $academyId, $branchId));

        // Enrollment counts
        $enrollmentQuery = Enrollment::where('created_at', '>=', $startDate)
            ->when($academyId, fn ($q) => $q->where('academy_id', $academyId))
            ->when($branchId, fn ($q) => $q->whereHas('group', fn ($g) => $g->where('branch_id', $branchId)));

        $newEnrollments = (clone $enrollmentQuery)->count();
        $activeEnrollments = (clone $enrollmentQuery)->where('status', 'active')->count();
        $pendingEnrollments = (clone $enrollmentQuery)->where('status', 'pending')->count();
        $cancelledEnrollments = (clone $enrollmentQuery)->where('status', 'cancelled')->count();

        $previousEnrollments = Enrollment::whereBetween('created_at', [$previousStart, $startDate])
            ->when($academyId, fn ($q) => $q->where('academy_id', $academyId))
            ->when($branchId, fn ($q) => $q->whereHas('group', fn ($g) => $g->where('branch_id', $branchId)))
            ->count();

        // Total confirmed payments this period
        $totalRevenue = Payment::where('direction', 'inbound')->where('status', 'confirmed')
            ->where('created_at', '>=', $startDate)
            ->when($academyId, fn ($q) => $q->where('academy_id', $academyId))
            ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->sum('amount');

        $subscriptionPercent = $totalRevenue > 0
            ? round($totalSubscriptionRevenue / $totalRevenue * 100, 1)
            : 0;

        return view('livewire.dashboard.subscription-revenue-widget', [
            'totalSubscriptionRevenue' => $totalSubscriptionRevenue,
            'change' => $change,
            'byActivity' => $byActivity,
            'topPrograms' => $topPrograms,
            'newEnrollments' => $newEnrollments,
            'activeEnrollments' => $activeEnrollments,
            'pendingEnrollments' => $pendingEnrollments,
            'cancelledEnrollments' => $cancelledEnrollments,
            'previousEnrollments' => $previousEnrollments,
            'subscriptionPercent' => $subscriptionPercent,
            'totalRevenue' => $totalRevenue,
        ]);
    }

    /**
     * Calculate subscription revenue using pro-rata allocation on bundled invoices.
     * Subscription items = invoice_items WHERE itemable_type IS NULL.
     * For each payment: payment.amount * (invoice_sub_total / invoice.total_amount)
     */
    private function getSubscriptionRevenue($from, $to, $academyId, ?int $branchId): int
    {
        $result = 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')
            ->whereNull('payments.deleted_at')
            ->whereNull('invoices.deleted_at')
            ->where('invoices.status', '!=', 'cancelled')
            ->where('invoices.subtotal_amount', '>', 0)
            ->where('payments.created_at', '>=', $from)
            ->when($to, fn ($q) => $q->where('payments.created_at', '<', $to))
            ->when($academyId, fn ($q) => $q->where('payments.academy_id', $academyId))
            ->when($branchId, fn ($q) => $q->where('payments.branch_id', $branchId))
            ->select(
                DB::raw('SUM(payments.amount * sub_items.sub_total / invoices.subtotal_amount) as subscription_revenue')
            )
            ->value('subscription_revenue');

        return (int) ($result ?? 0);
    }

    /**
     * Break down subscription revenue by activity.
     * Uses the participant's latest enrollment to determine which activity the subscription belongs to.
     * For participants with multiple activities, revenue is attributed to each proportionally.
     */
    private function getRevenueByActivity($from, $academyId, ?int $branchId)
    {
        return DB::select("
            WITH payment_sub_revenue AS (
                SELECT
                    p.id as payment_id,
                    p.amount * si.sub_total / i.subtotal_amount as sub_revenue,
                    i.billable_id as participant_id
                FROM payments p
                JOIN invoices i ON p.invoice_id = i.id
                JOIN (
                    SELECT invoice_id, SUM(total_amount) as sub_total
                    FROM invoice_items
                    WHERE itemable_type IS NULL
                    GROUP BY invoice_id
                ) si ON si.invoice_id = i.id
                WHERE p.status = 'confirmed'
                  AND p.direction = 'inbound'
                  AND p.deleted_at IS NULL
                  AND i.deleted_at IS NULL
                  AND i.status <> 'cancelled'
                  AND i.subtotal_amount > 0
                  AND i.billable_type = 'App\\Domain\\Participant\\Models\\Participant'
                  AND p.created_at >= ?
                  " . ($academyId ? "AND p.academy_id = {$academyId}" : "") . "
                  " . ($branchId ? "AND p.branch_id = " . (int) $branchId : "") . "
            ),
            participant_activity AS (
                SELECT DISTINCT ON (e.participant_id)
                    e.participant_id,
                    a.id as activity_id,
                    a.name_ar as activity_name,
                    a.category
                FROM enrollments e
                JOIN training_programs tp ON e.training_program_id = tp.id
                JOIN activities a ON tp.activity_id = a.id
                WHERE e.status IN ('active', 'completed')
                  -- Raw SQL sees no global scope. Without this predicate the
                  -- activity a payment is attributed to comes from the most
                  -- recent enrollment in ANY branch, so an activity from
                  -- somewhere else lands in this branch breakdown.
                  " . ($branchId ? "AND e.training_group_id IN (SELECT id FROM training_groups WHERE branch_id = " . (int) $branchId . ")" : "") . "
                ORDER BY e.participant_id, e.created_at DESC
            ),
            enrollment_counts AS (
                SELECT
                    a.id as activity_id,
                    COUNT(*) as enrollment_count,
                    COUNT(DISTINCT e.participant_id) as unique_participants
                FROM enrollments e
                JOIN training_programs tp ON e.training_program_id = tp.id
                JOIN activities a ON tp.activity_id = a.id
                WHERE e.status IN ('active', 'completed')
                  AND e.created_at >= ?
                  " . ($academyId ? "AND e.academy_id = {$academyId}" : "") . "
                  " . ($branchId ? "AND e.training_group_id IN (SELECT id FROM training_groups WHERE branch_id = " . (int) $branchId . ")" : "") . "
                GROUP BY a.id
            )
            SELECT
                pa.activity_id,
                pa.activity_name,
                pa.category,
                SUM(psr.sub_revenue) as total,
                COALESCE(ec.unique_participants, 0) as unique_participants,
                COALESCE(ec.enrollment_count, 0) as enrollment_count
            FROM payment_sub_revenue psr
            JOIN participant_activity pa ON pa.participant_id = psr.participant_id
            LEFT JOIN enrollment_counts ec ON ec.activity_id = pa.activity_id
            GROUP BY pa.activity_id, pa.activity_name, pa.category, ec.unique_participants, ec.enrollment_count
            ORDER BY total DESC
        ", [$from, $from]);
    }

    /**
     * Top programs by subscription revenue collected.
     */
    private function getTopPrograms($from, $academyId, ?int $branchId)
    {
        return DB::select("
            WITH payment_sub_revenue AS (
                SELECT
                    p.id as payment_id,
                    p.amount * si.sub_total / i.subtotal_amount as sub_revenue,
                    i.billable_id as participant_id
                FROM payments p
                JOIN invoices i ON p.invoice_id = i.id
                JOIN (
                    SELECT invoice_id, SUM(total_amount) as sub_total
                    FROM invoice_items
                    WHERE itemable_type IS NULL
                    GROUP BY invoice_id
                ) si ON si.invoice_id = i.id
                WHERE p.status = 'confirmed'
                  AND p.direction = 'inbound'
                  AND p.deleted_at IS NULL
                  AND i.deleted_at IS NULL
                  AND i.status <> 'cancelled'
                  AND i.subtotal_amount > 0
                  AND i.billable_type = 'App\\Domain\\Participant\\Models\\Participant'
                  AND p.created_at >= ?
                  " . ($academyId ? "AND p.academy_id = {$academyId}" : "") . "
                  " . ($branchId ? "AND p.branch_id = " . (int) $branchId : "") . "
            ),
            participant_program AS (
                SELECT DISTINCT ON (e.participant_id)
                    e.participant_id,
                    tp.id as program_id,
                    tp.name_ar as program_name,
                    a.name_ar as activity_name
                FROM enrollments e
                JOIN training_programs tp ON e.training_program_id = tp.id
                JOIN activities a ON tp.activity_id = a.id
                WHERE e.status IN ('active', 'completed')
                  -- Same reason as getRevenueByActivity(): a player enrolled
                  -- in two branches would otherwise put a program from the
                  -- other branch into this top-programs table.
                  " . ($branchId ? "AND e.training_group_id IN (SELECT id FROM training_groups WHERE branch_id = " . (int) $branchId . ")" : "") . "
                ORDER BY e.participant_id, e.created_at DESC
            )
            SELECT
                pp.program_id,
                pp.program_name,
                pp.activity_name,
                SUM(psr.sub_revenue) as total,
                COUNT(DISTINCT psr.participant_id) as subscriber_count
            FROM payment_sub_revenue psr
            JOIN participant_program pp ON pp.participant_id = psr.participant_id
            GROUP BY pp.program_id, pp.program_name, pp.activity_name
            ORDER BY total DESC
            LIMIT 10
        ", [$from]);
    }
}
