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

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

$rows=
$db->query("
SELECT
chm.conference_id,
chm.overall_health_score,
COALESCE(MAX(cko.improvement_gap),0) improvement_gap
FROM conference_health_metrics chm
LEFT JOIN conference_kpi_optimization cko
ON cko.conference_id=chm.conference_id
GROUP BY
chm.conference_id,
chm.overall_health_score
ORDER BY chm.overall_health_score DESC
")->fetchAll(PDO::FETCH_ASSOC);

$rank=1;

foreach($rows as $r){

    $risk='Low';

    if($r['overall_health_score'] < 60){
        $risk='High';
    }
    elseif($r['overall_health_score'] < 70){
        $risk='Medium';
    }

    $summary=
    'Health='.$r['overall_health_score'].
    ', KPI Gap='.$r['improvement_gap'].
    ', Risk='.$risk;

    $db->prepare("
    INSERT INTO conference_portfolio_intelligence
    (
        conference_id,
        portfolio_rank,
        overall_score,
        health_score,
        optimization_gap,
        risk_level,
        executive_summary
    )
    VALUES
    (
        ?,?,?,?,?,?,?
    )
    ")->execute([
        $r['conference_id'],
        $rank,
        $r['overall_health_score'],
        $r['overall_health_score'],
        $r['improvement_gap'],
        $risk,
        $summary
    ]);

    $rank++;
}

echo "DONE\n";
