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

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

$sql="
SELECT

author_id,
conference_id,

COUNT(*) submission_count,

SUM(
CASE
WHEN status='accepted'
THEN 1 ELSE 0
END
) accepted_count

FROM submissions

WHERE author_id IS NOT NULL

GROUP BY
author_id,
conference_id
";

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

foreach($rows as $r){

    $total =
    max(1,(int)$r['submission_count']);

    $accepted =
    (int)$r['accepted_count'];

    $fitScore =
    round(
        min(
            100,
            (($accepted/$total)*80)
            +
            ($total*2)
        ),
        2
    );

    $potential =
    round(
        min(
            95,
            (($accepted/$total)*85)+10
        ),
        2
    );

    $level='Developing';

    if($fitScore>=80){
        $level='Excellent Fit';
    }elseif($fitScore>=60){
        $level='Strong Fit';
    }elseif($fitScore>=40){
        $level='Moderate Fit';
    }

    $recommendedTrack='';

    try{

        $st=$db->prepare("
            SELECT t.name_en
            FROM submissions s
            JOIN tracks t
            ON t.id=s.track_id
            WHERE s.author_id=?
            AND s.conference_id=?
            GROUP BY t.id
            ORDER BY COUNT(*) DESC
            LIMIT 1
        ");

        $st->execute([
            $r['author_id'],
            $r['conference_id']
        ]);

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

    }catch(Throwable $e){}

    $db->prepare("
        INSERT INTO author_conference_fit
        (
            author_id,
            conference_id,
            submission_count,
            accepted_count,
            conference_fit_score,
            acceptance_potential,
            fit_level,
            recommended_track,
            recommendation_text
        )
        VALUES
        (?,?,?,?,?,?,?,?,?)
    ")->execute([
        $r['author_id'],
        $r['conference_id'],
        $total,
        $accepted,
        $fitScore,
        $potential,
        $level,
        $recommendedTrack,
        'Submit to conferences and tracks where historical performance is strongest.'
    ]);
}

echo "DONE\n";
