<?php

namespace App\Components\Report;

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

class DsarReport extends Component {

    public string $targetSearch = '';
    public string $sort = 'outstanding';

    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) — global
        $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();

        // Per-target (institution) breakdown — the core of this report.
        // Outstanding = DSARs sent but not yet completed.
        $byTarget = $this->buildTargetBreakdown();

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

    protected function buildTargetBreakdown() {
        // Pull per-institution aggregates from the dsars table directly, then
        // join the per-institution avg response days from dsar_responses.
        $totals = 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"),
                DB::raw("SUM(CASE WHEN status IN ('sent','approved','needs posting') THEN 1 ELSE 0 END) as outstanding"),
                DB::raw("MIN(CASE WHEN status IN ('sent','approved','needs posting') THEN created_at ELSE NULL END) as oldest_outstanding"),
            )
            ->groupBy('institution_id')
            ->get()
            ->keyBy('institution_id');

        $avgDays = DsarResponse::where('type', 'full')
            ->join('dsars', 'dsar_responses.dsar_id', '=', 'dsars.id')
            ->select(
                'dsars.institution_id',
                DB::raw('AVG(DATEDIFF(dsar_responses.received_at, dsars.created_at)) as avg_days')
            )
            ->groupBy('dsars.institution_id')
            ->pluck('avg_days', 'institution_id');

        $institutionIds = $totals->keys()->filter()->all();

        $institutions = Institution::whereIn('id', $institutionIds)
            ->when($this->targetSearch, fn ($q) => $q->where('display_name', 'like', "%{$this->targetSearch}%"))
            ->get()
            ->keyBy('id');

        $rows = $totals->map(function ($row) use ($institutions, $avgDays) {
            $institution = $institutions->get($row->institution_id);
            if ($this->targetSearch && !$institution) {
                return null;
            }

            $avg       = $avgDays->get($row->institution_id);
            $oldestAge = $row->oldest_outstanding
                ? now()->diffInDays($row->oldest_outstanding)
                : null;
            $backlog = $institution?->stated_sar_backlog;

            return (object) [
                'institution'        => $institution,
                'display_name'       => $institution?->display_name ?? 'Unknown',
                'total_sent'         => (int) $row->total_sent,
                'total_completed'    => (int) $row->total_completed,
                'outstanding'        => (int) $row->outstanding,
                'oldest_age_days'    => $oldestAge,
                'avg_response_days'  => $avg !== null ? round($avg, 1) : null,
                'stated_backlog'     => $backlog,
                'backlog_updated_at' => $institution?->sar_backlog_updated_at,
                'completion_rate'    => $row->total_sent > 0 ? round(($row->total_completed / $row->total_sent) * 100) : 0,
            ];
        })->filter()->values();

        return match ($this->sort) {
            'oldest'      => $rows->sortByDesc('oldest_age_days')->values(),
            'backlog'     => $rows->sortByDesc('stated_backlog')->values(),
            'avg'         => $rows->sortByDesc('avg_response_days')->values(),
            'sent'        => $rows->sortByDesc('total_sent')->values(),
            default       => $rows->sortByDesc('outstanding')->values(),
        };
    }
}
