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

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

$rows=
$db->query("
SELECT
c.org_id,
COUNT(*) conference_count,
AVG(chm.overall_health_score) avg_health,
AVG(COALESCE(cko.improvement_gap,0)) avg_gap
FROM conferences c
LEFT JOIN conference_health_metrics chm
ON chm.conference_id=c.id
LEFT JOIN conference_kpi_optimization cko
ON cko.conference_id=c.id
GROUP BY c.org_id
")->fetchAll(PDO::FETCH_ASSOC);

foreach($rows as $r){

    $risk=max(
        0,
        100 - (float)$r['avg_health']
    );

    $score=
    round(
        (
            (float)$r['avg_health']
            +
            (100-(float)$r['avg_gap'])
        ) / 2,
        2
    );

    $summary=
    'Health='.
    round($r['avg_health'],2).
    ', KPI Gap='.
    round($r['avg_gap'],2);

    $db->prepare("
    INSERT INTO
    organization_governance_intelligence
    (
        org_id,
        conference_count,
        avg_health_score,
        avg_kpi_gap,
        risk_index,
        governance_score,
        executive_summary
    )
    VALUES
    (
        ?,?,?,?,?,?,?
    )
    ")->execute([
        $r['org_id'],
        $r['conference_count'],
        $r['avg_health'],
        $r['avg_gap'],
        $risk,
        $score,
        $summary
    ]);

}

echo "DONE\n";
