<?php

define(
'BASE_PATH',
realpath(__DIR__.'/../..')
);

require_once BASE_PATH.'/config/autoload.php';
require_once BASE_PATH.'/config/bootstrap.php';

$db=\App\Core\Database::getInstance();

echo "REVIEWER WORKLOAD REBUILD\n";

$db->exec(
"TRUNCATE TABLE reviewer_workload_status"
);

$reviewers=
$db->query("
SELECT DISTINCT
reviewer_id,
conference_id
FROM review_assignments
")->fetchAll(PDO::FETCH_ASSOC);

foreach($reviewers as $r){

    $cid=
    (int)$r['conference_id'];

    $rid=
    (int)$r['reviewer_id'];

    $active=
    (int)$db->query("
        SELECT COUNT(*)
        FROM review_assignments
        WHERE reviewer_id=".$rid."
        AND conference_id=".$cid."
        AND status IN
        (
            'assigned',
            'accepted'
        )
    ")->fetchColumn();

    $rule=
    $db->query("
        SELECT *
        FROM reviewer_workload_rules
        WHERE conference_id=".$cid."
        LIMIT 1
    ")->fetch(PDO::FETCH_ASSOC);

    $limit=
    (int)($rule['max_active_assignments'] ?? 10);

    $util=
    $limit > 0
    ? round(($active/$limit)*100,2)
    : 0;

    $status='safe';

    if(
        $active >=
        (int)($rule['critical_threshold'] ?? 10)
    ){
        $status='critical';
    }
    elseif(
        $active >=
        (int)($rule['warning_threshold'] ?? 7)
    ){
        $status='warning';
    }

    $db->prepare("
        INSERT INTO reviewer_workload_status
        (
            conference_id,
            reviewer_id,
            active_assignments,
            capacity_limit,
            utilization_percent,
            workload_status
        )
        VALUES
        (
            ?,?,?,?,?,?
        )
    ")->execute([
        $cid,
        $rid,
        $active,
        $limit,
        $util,
        $status
    ]);

}

echo "DONE\n";
