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

$colsStmt = $db->query("SHOW COLUMNS FROM submissions");
$cols = array_column($colsStmt->fetchAll(PDO::FETCH_ASSOC), 'Field');

$authorCol = null;

foreach (['author_id', 'created_by', 'submitted_by', 'user_id'] as $candidate) {
    if (in_array($candidate, $cols, true)) {
        $authorCol = $candidate;
        break;
    }
}

if (!$authorCol) {
    echo "NO AUTHOR COLUMN FOUND IN submissions\n";
    echo "Available columns:\n";
    echo implode(", ", $cols) . PHP_EOL;
    exit(1);
}

$conferenceExpr = in_array('conference_id', $cols, true)
    ? 'COALESCE(conference_id,0)'
    : '0';

$statusExpr = in_array('status', $cols, true)
    ? 'status'
    : "''";

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

$sql = "
    SELECT
        {$authorCol} AS author_id,
        {$conferenceExpr} AS conference_id,
        COUNT(*) AS submission_count,
        SUM(CASE WHEN {$statusExpr}='accepted' THEN 1 ELSE 0 END) AS accepted_count,
        SUM(CASE WHEN {$statusExpr} IN ('under_review','reviewing') THEN 1 ELSE 0 END) AS under_review_count,
        SUM(CASE WHEN {$statusExpr} IN ('revision_required','revisions_required') THEN 1 ELSE 0 END) AS revision_count
    FROM submissions
    WHERE {$authorCol} IS NOT NULL
    AND {$authorCol} > 0
    GROUP BY {$authorCol}, {$conferenceExpr}
";

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

foreach ($rows as $r) {

    $total = max(1, (int)$r['submission_count']);
    $accepted = (int)$r['accepted_count'];
    $underReview = (int)$r['under_review_count'];
    $revision = (int)$r['revision_count'];

    $successScore = round(
        min(
            100,
            (($accepted * 70) + ($underReview * 20) + ($total * 5)) / $total
        ),
        2
    );

    $acceptanceProbability = round(
        min(
            95,
            max(
                5,
                ($accepted / $total * 70)
                + ($underReview / $total * 20)
                + 10
            )
        ),
        2
    );

    $revisionRisk = 'Medium';

    if ($revision / $total >= 0.5) {
        $revisionRisk = 'High';
    }

    if ($revision === 0 && $accepted > 0) {
        $revisionRisk = 'Low';
    }

    $level = 'Developing';

    if ($successScore >= 80) {
        $level = 'High Potential';
    } elseif ($successScore >= 60) {
        $level = 'Promising';
    } elseif ($successScore < 30) {
        $level = 'Needs Support';
    }

    $recommendation = $successScore >= 80
        ? 'Maintain strong submission quality and target higher-impact conferences.'
        : 'Improve manuscript readiness, review feedback response, and submission completeness.';

    $db->prepare("
        INSERT INTO author_success_intelligence
        (
            author_id,
            conference_id,
            submission_count,
            accepted_count,
            under_review_count,
            revision_count,
            success_score,
            acceptance_probability,
            revision_risk,
            readiness_level,
            recommendation_text
        )
        VALUES (?,?,?,?,?,?,?,?,?,?,?)
    ")->execute([
        (int)$r['author_id'],
        (int)$r['conference_id'],
        (int)$r['submission_count'],
        $accepted,
        $underReview,
        $revision,
        $successScore,
        $acceptanceProbability,
        $revisionRisk,
        $level,
        $recommendation
    ]);
}

echo "Author column: {$authorCol}\n";
echo "Rows inserted: " . count($rows) . "\n";
echo "DONE\n";
