<?php

namespace App\Components\Report;

use App\Enum\Dsar\DsarStatus;
use App\Models\Dsar\Dsar;
use App\Models\Institution\Institution;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Livewire\Component;

/**
 * Overview report for all DSARs.
 *
 * Definitions used throughout, all driven off App\Enum\Dsar\DsarStatus so they
 * stay in step as statuses are added:
 *
 *   sent      — anything past the pre-send stage (NOT IN preSend()). Matches
 *               DashboardMetrics::dsarsSentQuery(), so the DSAR report and the
 *               KPI report reconcile.
 *   completed — any terminal status: the DSAR reached a disposition.
 *   responded — the institution came back to us in some form (excludes 'failed',
 *               which is a send failure rather than a response).
 *
 * Response time is measured created_at → received_at, where received_at is
 * stamped by Dsar::booted() on first entry to a terminal status.
 */
class DsarReport extends Component {

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

    public function render() {
        $weekStart     = now()->startOfWeek();
        $lastWeekStart = now()->subWeek()->startOfWeek();

        $totalAll   = $this->sentQuery()->count();
        $totalMonth = $this->sentQuery()
            ->whereMonth('created_at', now()->month)
            ->whereYear('created_at', now()->year)
            ->count();

        $completedCount = Dsar::whereIn('status', DsarStatus::terminal())->count();

        // Average days from send to response, over DSARs that reached a terminal
        // status and therefore carry a received_at.
        $avgResponseDays = $this->avgResponseQuery()->value('avg_days');

        // ── Headline trends: this week vs last week ──────────────────────────
        // Half-open ranges [start, end) so a row on the boundary is counted once.
        $sentThisWeek = $this->sentQuery()->where('created_at', '>=', $weekStart)->count();
        $sentLastWeek = $this->sentQuery()
            ->where('created_at', '>=', $lastWeekStart)
            ->where('created_at', '<', $weekStart)
            ->count();

        $completedThisWeek = Dsar::whereIn('status', DsarStatus::terminal())
            ->where('received_at', '>=', $weekStart)->count();
        $completedLastWeek = Dsar::whereIn('status', DsarStatus::terminal())
            ->where('received_at', '>=', $lastWeekStart)
            ->where('received_at', '<', $weekStart)
            ->count();

        $avgThisWeek = $this->avgResponseQuery()
            ->where('received_at', '>=', $weekStart)->value('avg_days');
        $avgLastWeek = $this->avgResponseQuery()
            ->where('received_at', '>=', $lastWeekStart)
            ->where('received_at', '<', $weekStart)
            ->value('avg_days');

        // ── Status breakdown: every status, including the pre-send ones ──────
        $byStatus = Dsar::select('status', DB::raw('count(*) as total'))
            ->groupBy('status')
            ->pluck('total', 'status');

        // Includes draft/approved, so it cannot share $totalAll as a denominator.
        $grandTotal = $byStatus->sum();

        // ── Response types: outcome statuses as a share of DSARs responded to ─
        $responded      = DsarStatus::responded();
        $totalResponded = $byStatus->only($responded)->sum();

        $byTarget    = $this->buildTargetBreakdown();
        $sentByMonth = $this->buildSentByMonth();

        return view('components.dsar.report', compact(
            'totalAll', 'totalMonth', 'completedCount', 'avgResponseDays',
            'sentThisWeek', 'sentLastWeek',
            'completedThisWeek', 'completedLastWeek',
            'avgThisWeek', 'avgLastWeek',
            'byStatus', 'grandTotal',
            'responded', 'totalResponded',
            'byTarget', 'sentByMonth'
        ));
    }

    /** DSARs that have actually gone out — the shared definition of "sent". */
    protected function sentQuery() {
        return Dsar::whereNotIn('status', DsarStatus::preSend());
    }

    /** AVG(days from send to response) over terminal DSARs carrying a received_at. */
    protected function avgResponseQuery() {
        return Dsar::whereIn('status', DsarStatus::terminal())
            ->whereNotNull('received_at')
            ->select(DB::raw('AVG(DATEDIFF(received_at, created_at)) as avg_days'));
    }

    /** Sent DSARs per month for the last 6 months, oldest first. Zero-filled. */
    protected function buildSentByMonth(): array {
        $start = now()->startOfMonth()->subMonths(5);

        $counts = $this->sentQuery()
            ->where('created_at', '>=', $start)
            ->select(
                DB::raw("DATE_FORMAT(created_at, '%Y-%m') as period"),
                DB::raw('COUNT(*) as total'),
            )
            ->groupBy('period')
            ->pluck('total', 'period');

        $months = [];
        for ($i = 0; $i < 6; $i++) {
            $month    = $start->copy()->addMonths($i);
            $months[] = [
                'label' => $month->format('M Y'),
                'total' => (int) $counts->get($month->format('Y-m'), 0),
            ];
        }

        return $months;
    }

    protected function buildTargetBreakdown() {
        // Per-institution aggregates straight off the dsars table. Built from the
        // enum so new statuses land in the right bucket automatically.
        $outstandingIn = "'" . implode("','", DsarStatus::outstanding()) . "'";
        $terminalIn    = "'" . implode("','", DsarStatus::terminal()) . "'";
        $preSendIn     = "'" . implode("','", DsarStatus::preSend()) . "'";

        $totals = Dsar::select(
                'institution_id',
                DB::raw("SUM(CASE WHEN status NOT IN ({$preSendIn}) THEN 1 ELSE 0 END) as total_sent"),
                DB::raw("SUM(CASE WHEN status IN ({$terminalIn}) THEN 1 ELSE 0 END) as total_completed"),
                DB::raw("SUM(CASE WHEN status IN ({$outstandingIn}) THEN 1 ELSE 0 END) as outstanding"),
                DB::raw("MIN(CASE WHEN status IN ({$outstandingIn}) THEN created_at ELSE NULL END) as oldest_outstanding"),
                DB::raw("AVG(CASE WHEN status IN ({$terminalIn}) AND received_at IS NOT NULL
                                  THEN DATEDIFF(received_at, created_at) ELSE NULL END) as avg_days"),
            )
            ->groupBy('institution_id')
            ->get()
            ->keyBy('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) {
            $institution = $institutions->get($row->institution_id);
            if ($this->targetSearch && !$institution) {
                return null;
            }

            // Carbon 3's diffInDays is signed, and oldest_outstanding is in the
            // past — abs() keeps the age positive.
            $oldestAge = $row->oldest_outstanding
                ? (int) abs(now()->diffInDays(Carbon::parse($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'  => $row->avg_days !== null ? round($row->avg_days, 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(),
        };
    }
}
