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

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

$authors = $db->query("
    SELECT DISTINCT author_id
    FROM submissions
    WHERE author_id IS NOT NULL
")->fetchAll(PDO::FETCH_COLUMN);

foreach($authors as $authorId){

    $authorId=(int)$authorId;

    $success =
    (float)$db->query("
        SELECT COALESCE(AVG(success_score),0)
        FROM author_success_intelligence
        WHERE author_id={$authorId}
    ")->fetchColumn();

    $collaboration =
    (float)$db->query("
        SELECT COALESCE(AVG(collaboration_score),0)
        FROM author_collaboration_intelligence
        WHERE author_id={$authorId}
    ")->fetchColumn();

    $impact =
    (float)$db->query("
        SELECT COALESCE(AVG(impact_score),0)
        FROM author_research_impact
        WHERE author_id={$authorId}
    ")->fetchColumn();

    $fit =
    (float)$db->query("
        SELECT COALESCE(AVG(conference_fit_score),0)
        FROM author_conference_fit
        WHERE author_id={$authorId}
    ")->fetchColumn();

    $readiness =
    (float)$db->query("
        SELECT COALESCE(AVG(readiness_score),0)
        FROM author_submission_readiness
        WHERE author_id={$authorId}
    ")->fetchColumn();

    $portfolio =
    round(
        (
            $success
            +
            $collaboration
            +
            $impact
            +
            $fit
            +
            $readiness
        ) / 5,
        2
    );

    $level='Emerging';

    if($portfolio>=85){
        $level='Distinguished Author';
    }elseif($portfolio>=70){
        $level='Advanced Researcher';
    }elseif($portfolio>=55){
        $level='Growing Scholar';
    }

    $rank='Developing';

    if($portfolio>=90){
        $rank='Elite';
    }elseif($portfolio>=75){
        $rank='Gold';
    }elseif($portfolio>=60){
        $rank='Silver';
    }

    $db->prepare("
        INSERT INTO
        author_academic_portfolio
        (
            author_id,
            portfolio_score,
            success_score,
            collaboration_score,
            impact_score,
            fit_score,
            readiness_score,
            academic_level,
            portfolio_rank,
            recommendation_text
        )
        VALUES
        (?,?,?,?,?,?,?,?,?,?)
    ")->execute([
        $authorId,
        $portfolio,
        $success,
        $collaboration,
        $impact,
        $fit,
        $readiness,
        $level,
        $rank,
        'Continue strengthening research impact, collaboration network, and submission quality.'
    ]);
}

echo "DONE\n";
