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

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

$rows=
$db->query("
SELECT
reviewer_id,
conference_id,
academy_program,
academy_level,
academy_score
FROM reviewer_academy_programs
")->fetchAll(PDO::FETCH_ASSOC);

foreach($rows as $r){

    $cert='Reviewer';
    $promotion='Reviewer';

    if(
        $r['academy_level']=='Executive'
        &&
        $r['academy_score']>=80
    ){
        $cert='Conference Chair Eligible';
        $promotion='Conference Chair';
    }
    elseif(
        $r['academy_level']=='Track Leadership'
        &&
        $r['academy_score']>=65
    ){
        $cert='Track Chair Eligible';
        $promotion='Track Chair';
    }
    elseif(
        $r['academy_program']=='Leadership Academy'
        &&
        $r['academy_score']>=60
    ){
        $cert='Senior Reviewer';
        $promotion='Senior Reviewer';
    }
    elseif(
        $r['academy_program']=='Excellence Academy'
        &&
        $r['academy_score']>=40
    ){
        $cert='Certified Reviewer';
        $promotion='Certified Reviewer';
    }

    $db->prepare("
    INSERT INTO reviewer_certification_promotions
    (
        reviewer_id,
        conference_id,
        certification_level,
        promotion_path,
        eligibility_score,
        recommendation_text
    )
    VALUES
    (
        ?,?,?,?,?,?
    )
    ")->execute([
        $r['reviewer_id'],
        $r['conference_id'],
        $cert,
        $promotion,
        $r['academy_score'],
        'Reviewer promotion pathway generated automatically'
    ]);

}

echo "DONE\n";
