<?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 "EXECUTIVE COMMAND CENTER\n";

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

$totalConferences=
(int)$db->query("
SELECT COUNT(*)
FROM conferences
")->fetchColumn();

$totalOrganizations=
(int)$db->query("
SELECT COUNT(DISTINCT org_id)
FROM conferences
")->fetchColumn();

$avgHealth=
(float)$db->query("
SELECT AVG(overall_health_score)
FROM conference_health_metrics
")->fetchColumn();

$highRisk=
(int)$db->query("
SELECT COUNT(*)
FROM conference_portfolio_intelligence
WHERE risk_level='High'
")->fetchColumn();

$pendingActions=
(int)$db->query("
SELECT COUNT(*)
FROM orchestration_action_approvals
WHERE approval_status='pending'
")->fetchColumn();

$pendingHealing=
(int)$db->query("
SELECT COUNT(*)
FROM self_healing_actions
WHERE healing_status='pending'
")->fetchColumn();

$bestConference=
$db->query("
SELECT conference_id
FROM conference_portfolio_intelligence
ORDER BY portfolio_rank
LIMIT 1
")->fetchColumn();

$bestOrg=
$db->query("
SELECT org_id
FROM organization_governance_intelligence
ORDER BY governance_score DESC
LIMIT 1
")->fetchColumn();

$db->prepare("
INSERT INTO executive_command_center
(
total_conferences,
total_organizations,
avg_health_score,
high_risk_conferences,
pending_actions,
pending_healing,
portfolio_best_conference,
governance_best_org
)
VALUES
(
?,?,?,?,?,?,?,?
)
")->execute([
$totalConferences,
$totalOrganizations,
$avgHealth,
$highRisk,
$pendingActions,
$pendingHealing,
$bestConference,
$bestOrg
]);

echo "DONE\n";
