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

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

$rows=
$db->query("
SELECT
reviewer_id,
conference_id,
talent_score,
leadership_potential,
risk_level
FROM reviewer_talent_management
")->fetchAll(PDO::FETCH_ASSOC);

foreach($rows as $r){

    $role='Reviewer';

    if(
        $r['talent_score']>=75
        &&
        $r['leadership_potential']>=80
        &&
        $r['risk_level']=='Low'
    ){
        $role='Future Conference Chair';
    }
    elseif(
        $r['talent_score']>=60
        &&
        $r['leadership_potential']>=70
        &&
        $r['risk_level']=='Low'
    ){
        $role='Future Track Chair';
    }
    elseif(
        $r['talent_score']>=50
    ){
        $role='Senior Reviewer';
    }
    elseif(
        $r['risk_level']=='High'
    ){
        $role='Recovery Candidate';
    }

    $recommendation=
    'Continue reviewer development';

    if($role=='Future Conference Chair'){
        $recommendation=
        'Consider conference chair succession planning';
    }

    if($role=='Future Track Chair'){
        $recommendation=
        'Consider track chair assignment';
    }

    if($role=='Senior Reviewer'){
        $recommendation=
        'Assign mentoring responsibilities';
    }

    if($role=='Recovery Candidate'){
        $recommendation=
        'Provide coaching and additional monitoring';
    }

    $db->prepare("
    INSERT INTO reviewer_leadership_pipeline
    (
        reviewer_id,
        conference_id,
        talent_score,
        leadership_score,
        pipeline_role,
        recommendation_text
    )
    VALUES
    (
        ?,?,?,?,?,?
    )
    ")->execute([
        $r['reviewer_id'],
        $r['conference_id'],
        $r['talent_score'],
        $r['leadership_potential'],
        $role,
        $recommendation
    ]);

}

echo 'DONE';
