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

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

$rows=
$db->query("
SELECT
rla.reviewer_id,
MIN(rri.conference_id) home_conference_id,
rla.lifetime_impact_score
FROM reviewer_lifetime_achievement rla
LEFT JOIN reviewer_reward_intelligence rri
ON rri.reviewer_id=rla.reviewer_id
GROUP BY
rla.reviewer_id,
rla.lifetime_impact_score
ORDER BY rla.lifetime_impact_score DESC
")->fetchAll(PDO::FETCH_ASSOC);

$rank=1;

foreach($rows as $r){

    $score=
    round(
        (float)$r['lifetime_impact_score'],
        2
    );

    $level='Standard';

    if($score>=80){
        $level='Global Elite';
    }
    elseif($score>=40){
        $level='Senior Marketplace Reviewer';
    }

    $availability='Available';

    $recommendation=
    'Eligible for cross-conference assignments';

    $db->prepare("
    INSERT INTO reviewer_marketplace
    (
        reviewer_id,
        home_conference_id,
        marketplace_score,
        marketplace_rank,
        availability_status,
        specialization,
        marketplace_level,
        recommendation_text
    )
    VALUES
    (
        ?,?,?,?,?,?,?,?
    )
    ")->execute([
        $r['reviewer_id'],
        (int)$r['home_conference_id'],
        $score,
        $rank,
        $availability,
        'General',
        $level,
        $recommendation
    ]);

    $rank++;

}

echo "DONE\n";
