<?php

namespace App\Controllers;

use App\Core\Controller;

final class AdminReviewAssignmentsTenantController extends Controller
{
    public function index()
    {
        $this->requireAccess();

        $conference =
            $this->conference();

        $db =
            $this->db();

        /*
        ------------------------------------
        SUBMISSIONS
        conference only
        ------------------------------------
        */

        $stmt =
            $db->prepare("

SELECT
s.*,

COUNT(
DISTINCT ra.id
) current_load

FROM submissions s

LEFT JOIN review_assignments ra
ON ra.submission_id=s.id

WHERE
s.conference_id=?

GROUP BY s.id

ORDER BY s.id DESC

");

        $stmt->execute([
            (int)$conference['id']
        ]);

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

        /*
        ------------------------------------
        REVIEWERS
        ------------------------------------
        */

        $stmt =
            $db->prepare("

SELECT
u.id,
u.first_name,
u.last_name,
u.email,
u.reviewer_score,

COUNT(
DISTINCT ra.id
) current_load

FROM conference_reviewers cr

JOIN users u
ON u.id=cr.user_id

LEFT JOIN review_assignments ra
ON ra.reviewer_id=u.id

WHERE
cr.conference_id=?

AND u.is_active=1

GROUP BY u.id

ORDER BY
current_load ASC,
u.reviewer_score DESC,
u.last_name ASC

");

        $stmt->execute([
            (int)$conference['id']
        ]);

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

        /*
        ------------------------------------
        ROWS
        ------------------------------------
        */

        $stmt =
            $db->prepare("

SELECT
ra.*,

s.submission_no,
s.title,
s.title_ar,

u.email,
u.first_name,
u.last_name

FROM review_assignments ra

LEFT JOIN submissions s
ON s.id=ra.submission_id

LEFT JOIN users u
ON u.id=ra.reviewer_id

WHERE
ra.conference_id=?

ORDER BY
ra.id DESC

LIMIT 500

");

        $stmt->execute([
            (int)$conference['id']
        ]);

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

        $this->view(
            'reviewer_assignments',
            compact(
                'conference',
                'rows',
                'submissions',
                'reviewers'
            )
        );
    }

    public function saveReviewerAssignment()
    {
        $ctrl =
            new AdminAssignmentController();

        $ctrl->assign();
    }
}
