<?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 PERFORMANCE REBUILD\n";

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

$sql="
SELECT

reviewer_id,
conference_id,

COUNT(*) assignments_count,

SUM(status='accepted') accepted_count,

SUM(status='completed') completed_count,

SUM(status='declined') declined_count,

SUM(status='overdue') overdue_count,

AVG(
CASE
WHEN completed_at IS NOT NULL
THEN DATEDIFF(
completed_at,
assigned_at
)
END
) avg_review_days

FROM review_assignments

GROUP BY
reviewer_id,
conference_id
";

$rows=
$db->query($sql)
->fetchAll(PDO::FETCH_ASSOC);

foreach($rows as $r){

    $assignments=
    (int)$r['assignments_count'];

    $accepted=
    (int)$r['accepted_count'];

    $completed=
    (int)$r['completed_count'];

    $declined=
    (int)$r['declined_count'];

    $overdue=
    (int)$r['overdue_count'];

    $avgDays=
    (float)$r['avg_review_days'];

    $completionRate=
    $assignments
    ? round(
        ($completed/$assignments)*100,
        2
    )
    : 0;

    $acceptanceRate=
    $assignments
    ? round(
        ($accepted/$assignments)*100,
        2
    )
    : 0;

    $qualityScore=
    max(
        0,
        100
        -
        ($overdue*10)
    );

    $finalScore=
    round(
        (
            $completionRate*0.4
            +
            $acceptanceRate*0.3
            +
            $qualityScore*0.3
        ),
        2
    );

    $stmt=
    $db->prepare("
    INSERT INTO reviewer_performance
    (
        reviewer_id,
        conference_id,

        assignments_count,
        accepted_count,
        completed_count,
        declined_count,
        overdue_count,

        avg_review_days,

        completion_rate,
        acceptance_rate,

        quality_score,
        final_score
    )
    VALUES
    (
        ?,?,?,?,?,?,
        ?,?,?,?,
        ?,?
    )
    ");

    $stmt->execute([

        $r['reviewer_id'],
        $r['conference_id'],

        $assignments,
        $accepted,
        $completed,
        $declined,
        $overdue,

        $avgDays,

        $completionRate,
        $acceptanceRate,

        $qualityScore,
        $finalScore
    ]);
}

echo "DONE\n";
