<?php

namespace App\Services;

use App\Core\Database;

class ReviewerReassignmentService
{
    public static function generateSuggestions(
        int $conferenceId,
        int $submissionId,
        int $oldReviewerId
    ): void
    {
        $db = Database::getInstance();

        $db->prepare("
            DELETE
            FROM reviewer_reassignment_suggestions
            WHERE assignment_id IN
            (
                SELECT id
                FROM review_assignments
                WHERE submission_id=?
            )
        ")->execute([
            $submissionId
        ]);

        $sql="
        SELECT

        u.id,
        u.email,

        (
            100
            -
            (
                SELECT COUNT(*)
                FROM review_assignments ra
                WHERE
                ra.reviewer_id=u.id
                AND ra.status IN
                (
                    'assigned',
                    'accepted'
                )
            ) * 10
        ) AS score

        FROM conference_reviewers cr

        JOIN users u
        ON u.id=cr.user_id

        WHERE
        cr.conference_id=?
        AND u.id<>?

        ORDER BY score DESC

        LIMIT 3
        ";

        $stmt=$db->prepare($sql);

        $stmt->execute([
            $conferenceId,
            $oldReviewerId
        ]);

        $rows=
        $stmt->fetchAll(\PDO::FETCH_ASSOC);

        $assignmentId=
        (int)$db->query("
            SELECT id
            FROM review_assignments
            WHERE submission_id=".
            (int)$submissionId."
            ORDER BY id DESC
            LIMIT 1
        ")->fetchColumn();

        foreach($rows as $r){

            $db->prepare("
                INSERT INTO
                reviewer_reassignment_suggestions
                (
                    assignment_id,
                    old_reviewer_id,
                    suggested_reviewer_id,
                    score
                )
                VALUES
                (
                    ?,
                    ?,
                    ?,
                    ?
                )
            ")->execute([
                $assignmentId,
                $oldReviewerId,
                (int)$r['id'],
                (float)$r['score']
            ]);
        }
    }
}
