<?php
/**
 * ajax/reviewer_load_stats.php
 * Returns JSON: reviewer workload stats for the given range.
 * Used by Tab 2 (Reviewer Load) in live_feedback.php
 */
session_start();
include __DIR__ . '/../config/db.php';
include __DIR__ . '/../config/auth.php';
require_role('admin');

header('Content-Type: application/json');

$range = trim($_GET['range'] ?? '7');
$from  = trim($_GET['from']  ?? '');
$to    = trim($_GET['to']    ?? '');

/* ── Build date range SQL ── */
function buildDateRange($range, $from, $to) {
    switch ($range) {
        case 'today':     return ["DATE(ra.request_sent_date) = CURDATE()", []];
        case 'yesterday': return ["DATE(ra.request_sent_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)", []];
        case 'custom':
            if ($from && $to)   return ["DATE(ra.request_sent_date) BETWEEN ? AND ?", [$from, $to]];
            if ($from)          return ["DATE(ra.request_sent_date) >= ?", [$from]];
            if ($to)            return ["DATE(ra.request_sent_date) <= ?", [$to]];
            return ["1=1", []];
        default:
            $days = is_numeric($range) ? (int)$range : 7;
            return ["ra.request_sent_date >= DATE_SUB(NOW(), INTERVAL $days DAY)", []];
    }
}

[$dateWhere, $dateParams] = buildDateRange($range, $from, $to);

try {
    $stmt = $pdo->prepare("
        SELECT
            rev.id          AS reviewer_id,
            CONCAT(u.first_name,' ',u.last_name) AS reviewer,
            u.email         AS reviewer_email,
            rev.reviewing_interests,

            COUNT(ra.id)                                                            AS total_assigned,
            SUM(ra.assignment_status IN ('request_sent','accepted'))                AS active,
            SUM(ra.assignment_status = 'review_submitted')                          AS completed,
            SUM(ra.assignment_status = 'declined')                                  AS declined,
            SUM(
                ra.assignment_status NOT IN ('review_submitted','declined','cancelled')
                AND ra.review_due_date < CURDATE()
            )                                                                       AS total_overdue,
            ROUND(
                100.0 * SUM(ra.assignment_status = 'review_submitted') / NULLIF(COUNT(ra.id), 0),
                1
            )                                                                       AS completion_percent

        FROM review_assignments ra
        JOIN reviewers rev ON rev.id = ra.reviewer_id
        JOIN users u       ON u.user_id = rev.user_id
        WHERE $dateWhere
        GROUP BY rev.id, u.first_name, u.last_name, u.email, rev.reviewing_interests
        ORDER BY total_assigned DESC
    ");
    $stmt->execute($dateParams);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Clean up JSON fields
    foreach ($rows as &$r) {
        if ($r['reviewing_interests']) {
            $decoded = json_decode($r['reviewing_interests'], true);
            if (is_array($decoded)) {
                $r['reviewing_interests'] = implode(', ', $decoded);
            }
        }
    }

    echo json_encode($rows);
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(['error' => $e->getMessage()]);
}