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

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

$sql = "
SELECT
s.author_id,
s.conference_id,

COUNT(DISTINCT s.id) submission_count,

COUNT(DISTINCT sc.id) coauthor_count,

COUNT(DISTINCT COALESCE(sc.affiliation,'')) unique_affiliations,

COUNT(DISTINCT COALESCE(sc.country,'')) unique_countries

FROM submissions s

LEFT JOIN submission_coauthors sc
ON sc.submission_id=s.id

WHERE s.author_id IS NOT NULL

GROUP BY
s.author_id,
s.conference_id
";

$rows =
$db->query($sql)
->fetchAll(PDO::FETCH_ASSOC);

foreach($rows as $r){

    $authorId =
    (int)$r['author_id'];

    $conferenceId =
    (int)$r['conference_id'];

    $submissionCount =
    (int)$r['submission_count'];

    $coauthorCount =
    (int)$r['coauthor_count'];

    $affCount =
    (int)$r['unique_affiliations'];

    $countryCount =
    (int)$r['unique_countries'];

    $score =
    round(
        min(
            100,
            ($coauthorCount*8)
            +
            ($affCount*12)
            +
            ($countryCount*15)
            +
            ($submissionCount*5)
        ),
        2
    );

    $level='Developing';

    if($score>=80){
        $level='Global Collaboration';
    }elseif($score>=60){
        $level='Advanced Network';
    }elseif($score>=40){
        $level='Growing Network';
    }

    $topCollaborator='';

    try{

        $st=$db->prepare("
            SELECT full_name,
                   COUNT(*) c
            FROM submission_coauthors sc
            JOIN submissions s
                ON s.id=sc.submission_id
            WHERE s.author_id=?
            GROUP BY full_name
            ORDER BY c DESC
            LIMIT 1
        ");

        $st->execute([$authorId]);

        $topCollaborator =
        (string)$st->fetchColumn();

    }catch(Throwable $e){}

    $db->prepare("
        INSERT INTO
        author_collaboration_intelligence
        (
            author_id,
            conference_id,
            submission_count,
            coauthor_count,
            unique_affiliations,
            unique_countries,
            collaboration_score,
            network_level,
            top_collaborator
        )
        VALUES
        (?,?,?,?,?,?,?,?,?)
    ")->execute([
        $authorId,
        $conferenceId,
        $submissionCount,
        $coauthorCount,
        $affCount,
        $countryCount,
        $score,
        $level,
        $topCollaborator
    ]);
}

echo "DONE\n";
