<?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 "CONFERENCE HEALTH REBUILD\n";

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

$confs=
$db->query("
SELECT id
FROM conferences
")->fetchAll(PDO::FETCH_ASSOC);

foreach($confs as $c){

    $cid=(int)$c['id'];

    $reviewer=
    (float)$db->query("
        SELECT
        COALESCE(
        AVG(final_assignment_score),
        0
        )
        FROM reviewer_assignment_scores
        WHERE conference_id=".$cid."
    ")->fetchColumn();

    $track=
    (float)$db->query("
        SELECT
        COALESCE(
        AVG(health_score),
        0
        )
        FROM track_health_metrics
        WHERE conference_id=".$cid."
    ")->fetchColumn();

    $workflow=
    100;

    $deadline=
    100;

    $overall=
    round(
        (
            $reviewer+
            $track+
            $workflow+
            $deadline
        )/4,
        2
    );

    $db->prepare("
    INSERT INTO
    conference_health_metrics
    (
        conference_id,
        reviewer_health_score,
        track_health_score,
        workflow_health_score,
        deadline_health_score,
        overall_health_score
    )
    VALUES
    (
        ?,?,?,?,?,?
    )
    ")->execute([
        $cid,
        $reviewer,
        $track,
        $workflow,
        $deadline,
        $overall
    ]);

}

echo "DONE\n";
