<?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 LIFETIME ACHIEVEMENT\n";

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

$rows=
$db->query("
SELECT
reviewer_id,
AVG(award_score) impact_score
FROM reviewer_reward_intelligence
GROUP BY reviewer_id
ORDER BY impact_score DESC
")->fetchAll(PDO::FETCH_ASSOC);

$rank=1;

foreach($rows as $r){

    $impact=
    round(
        (float)$r['impact_score'],
        2
    );

    $level='Contributor';

    if($impact>=80){

        $level='Hall of Fame';

    }
    elseif($impact>=60){

        $level='Distinguished Reviewer';

    }
    elseif($impact>=40){

        $level='Senior Contributor';

    }

    $db->prepare("
    INSERT INTO reviewer_lifetime_achievement
    (
        reviewer_id,
        lifetime_impact_score,
        leadership_score,
        awards_score,
        achievement_level,
        hall_of_fame_rank
    )
    VALUES
    (
        ?,?,?,?,?,?
    )
    ")->execute([
        $r['reviewer_id'],
        $impact,
        $impact,
        $impact,
        $level,
        $rank
    ]);

    $rank++;

}

echo "DONE\n";
