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

$db->exec("TRUNCATE TABLE track_health_metrics");
$db->exec("TRUNCATE TABLE track_risk_alerts");
$db->exec("TRUNCATE TABLE track_bottlenecks");

$tracks=
$db->query("
SELECT
id,
conference_id
FROM tracks
")->fetchAll(PDO::FETCH_ASSOC);

foreach($tracks as $t){

    $trackId=(int)$t['id'];
    $cid=(int)$t['conference_id'];

    $subs=
    (int)$db->query("
        SELECT COUNT(*)
        FROM submissions
        WHERE track_id=".$trackId."
    ")->fetchColumn();

    $reviews=
    (int)$db->query("
        SELECT COUNT(*)
        FROM review_assignments ra
        JOIN submissions s
        ON s.id=ra.submission_id
        WHERE s.track_id=".$trackId."
    ")->fetchColumn();

    $completed=
    (int)$db->query("
        SELECT COUNT(*)
        FROM review_assignments ra
        JOIN submissions s
        ON s.id=ra.submission_id
        WHERE s.track_id=".$trackId."
        AND ra.status='completed'
    ")->fetchColumn();

    $reviewers=
    (int)$db->query("
        SELECT COUNT(DISTINCT ra.reviewer_id)
        FROM review_assignments ra
        JOIN submissions s
        ON s.id=ra.submission_id
        WHERE s.track_id=".$trackId."
    ")->fetchColumn();

    $completion=
    $reviews > 0
    ? round(($completed/$reviews)*100,2)
    : 0;

    $diversity=
    min(100,$reviewers*10);

    $health=
    round(
        ($completion*0.60)
        +
        ($diversity*0.40),
        2
    );

    $db->prepare("
    INSERT INTO track_health_metrics
    (
        conference_id,
        track_id,
        submission_count,
        review_count,
        completion_rate,
        diversity_score,
        health_score
    )
    VALUES
    (
        ?,?,?,?,?,?,?
    )
    ")->execute([
        $cid,
        $trackId,
        $subs,
        $reviews,
        $completion,
        $diversity,
        $health
    ]);

    if($health < 50){

        $db->prepare("
        INSERT INTO track_risk_alerts
        (
            conference_id,
            track_id,
            risk_type,
            risk_reason,
            risk_score
        )
        VALUES
        (
            ?,?,?,?,?
        )
        ")->execute([
            $cid,
            $trackId,
            'low_health',
            'Track health below threshold',
            100-$health
        ]);

    }

    if(
        $subs > 0
        &&
        $reviewers > 0
        &&
        ($subs/$reviewers) > 5
    ){

        $db->prepare("
        INSERT INTO track_bottlenecks
        (
            conference_id,
            track_id,
            submission_count,
            reviewer_count,
            bottleneck_score
        )
        VALUES
        (
            ?,?,?,?,?
        )
        ")->execute([
            $cid,
            $trackId,
            $subs,
            $reviewers,
            round(($subs/$reviewers)*10,2)
        ]);

    }

}

echo "DONE\n";
