<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;

final class ReviewerAnalyticsModel extends Model
{
    public function summary(): array
    {
        return $this->db->query("
            SELECT u.id,
                   u.first_name,
                   u.last_name,
                   u.email,
                   COUNT(ra.id) AS total_assignments,
                   SUM(CASE WHEN ra.status = 'completed' THEN 1 ELSE 0 END) AS completed_assignments,
                   SUM(CASE WHEN ra.status IN ('assigned','accepted','overdue') THEN 1 ELSE 0 END) AS pending_assignments,
                   ROUND(AVG(r.overall_score), 2) AS avg_score_given
            FROM users u
            INNER JOIN roles ro ON ro.id = u.role_id AND ro.code = 'reviewer'
            LEFT JOIN review_assignments ra ON ra.reviewer_id = u.id
            LEFT JOIN reviews r ON r.review_assignment_id = ra.id
            GROUP BY u.id, u.first_name, u.last_name, u.email
            ORDER BY completed_assignments DESC, total_assignments DESC, u.id DESC
        ")->fetchAll();
    }
}
