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

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

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

foreach($rows as $r){

    $engagement=
    round(
        min(
            100,
            ((float)$r['talent_score']) * 1.10
        ),
        2
    );

    $retention=
    round(
        min(
            100,
            ((float)$r['talent_score']) * 1.20
        ),
        2
    );

    $burnout='Low';
    $retentionRisk='Low';

    if($r['talent_level']=='Elite'){
        $burnout='Medium';
    }

    if($r['risk_level']=='High'){
        $retentionRisk='Critical';
    }
    elseif($r['risk_level']=='Medium'){
        $retentionRisk='Medium';
    }

    $plan='Continue reviewer engagement activities';

    if($retentionRisk=='Critical'){
        $plan='Immediate intervention and mentoring required';
    }

    if($r['talent_level']=='Elite'){
        $plan='Leadership retention and promotion pathway';
    }

    $db->prepare("
    INSERT INTO reviewer_retention_intelligence
    (
        reviewer_id,
        conference_id,
        engagement_score,
        retention_score,
        burnout_risk,
        retention_risk,
        intervention_plan
    )
    VALUES
    (
        ?,?,?,?,?,?,?
    )
    ")->execute([
        $r['reviewer_id'],
        $r['conference_id'],
        $engagement,
        $retention,
        $burnout,
        $retentionRisk,
        $plan
    ]);

}

echo "DONE\n";
