<?php

namespace App\Components\Report;

use App\Models\Dsar\Dsar;
use App\Models\Dsar\DsarResponse;
use Illuminate\Support\Facades\DB;
use Livewire\Component;

class DsarReport extends Component {

    public function render() {
        $totalAll   = Dsar::count();
        $totalMonth = Dsar::whereMonth('created_at', now()->month)
            ->whereYear('created_at', now()->year)
            ->count();
        $totalLastMonth = Dsar::whereMonth('created_at', now()->subMonth()->month)
            ->whereYear('created_at', now()->subMonth()->year)
            ->count();

        $byStatus = Dsar::select('status', DB::raw('count(*) as total'))
            ->groupBy('status')
            ->pluck('total', 'status');

        $flaggedCount   = Dsar::where('is_flagged', true)->count();
        $completedCount = $byStatus->get('completed', 0);

        // Average response time in days (created_at → first full response received_at)
        $avgResponseDays = DsarResponse::where('type', 'full')
            ->join('dsars', 'dsar_responses.dsar_id', '=', 'dsars.id')
            ->select(DB::raw('AVG(DATEDIFF(dsar_responses.received_at, dsars.created_at)) as avg_days'))
            ->value('avg_days');

        // Response type breakdown
        $responseBreakdown = DsarResponse::select('type', DB::raw('count(*) as total'))
            ->groupBy('type')
            ->pluck('total', 'type');

        $totalResponses = $responseBreakdown->sum();

        // By institution (top 15)
        $byInstitution = Dsar::select(
                'institution_id',
                DB::raw('count(*) as total_sent'),
                DB::raw('SUM(CASE WHEN status = "completed" THEN 1 ELSE 0 END) as total_completed')
            )
            ->with('institution:id,display_name')
            ->groupBy('institution_id')
            ->orderByDesc('total_sent')
            ->limit(15)
            ->get();

        return view('components.dsar.report', compact(
            'totalAll', 'totalMonth', 'totalLastMonth',
            'byStatus', 'flaggedCount', 'completedCount',
            'avgResponseDays', 'responseBreakdown', 'totalResponses',
            'byInstitution'
        ));
    }
}
