<?php

declare(strict_types=1);

define(
    'BASE_PATH',
    realpath(__DIR__.'/../..')
);

require_once BASE_PATH.'/config/autoload.php';
require_once BASE_PATH.'/config/bootstrap.php';
require_once BASE_PATH.'/app/core/Database.php';

use App\Core\Database;
use PDO;

$db = Database::getInstance();

echo "=== REVIEW REMINDER CRON ===\n";

$sql = "
SELECT

ra.id,
ra.submission_id,
ra.reviewer_id,
ra.conference_id,
ra.org_id,
ra.status,
ra.assigned_at,
ra.due_date,

u.email,
u.first_name,
u.last_name,

COALESCE(
s.title_en,
s.title_ar,
CONCAT('Submission #',s.id)
) AS submission_title,

crs.review_due_days,
crs.reminder_day_1,
crs.reminder_day_2,
crs.reminder_day_3,
crs.auto_reminders

FROM review_assignments ra

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

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

LEFT JOIN conference_review_settings crs
ON crs.conference_id=ra.conference_id

WHERE ra.status IN
(
'assigned',
'accepted'
)
";

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

foreach($rows as $r){

    if(
        (int)($r['auto_reminders'] ?? 1) !== 1
    ){
        continue;
    }

    $reviewDueDays =
        (int)($r['review_due_days'] ?? 14);

    $rem1 =
        (int)($r['reminder_day_1'] ?? 10);

    $rem2 =
        (int)($r['reminder_day_2'] ?? 14);

    $rem3 =
        (int)($r['reminder_day_3'] ?? 21);

    $assigned =
        strtotime(
            (string)$r['assigned_at']
        );

    $ageDays =
        (int)floor(
            (time()-$assigned)/86400
        );

    $stage = null;

    if($ageDays >= $rem3){
        $stage = 3;
    }
    elseif($ageDays >= $rem2){
        $stage = 2;
    }
    elseif($ageDays >= $rem1){
        $stage = 1;
    }

    if(!$stage){
        continue;
    }

    $chk = $db->prepare("
        SELECT COUNT(*)
        FROM review_assignment_reminders
        WHERE assignment_id=?
        AND reminder_stage=?
    ");

    $chk->execute([
        $r['id'],
        $stage
    ]);

    if($chk->fetchColumn() > 0){
        continue;
    }

    $subject =
        'Review Reminder - '
        .
        $r['submission_title'];

    $body =
"Dear Reviewer,

This is an automatic reminder that your review assignment is still pending.

Submission:
".$r['submission_title']."

Days Since Assignment:
".$ageDays."

Please complete your review.

EasyChair Conference System";

    $db->prepare("
        INSERT INTO conference_mail_outbox
        (
            org_id,
            conference_id,
            submission_id,
            user_id,
            template_key,
            event_key,
            recipient_email,
            recipient_name,
            subject,
            body,
            status,
            created_by
        )
        VALUES
        (
            ?,?,?,?,?,?,?,?,?,?,
            'queued',
            0
        )
    ")
    ->execute([
        $r['org_id'],
        $r['conference_id'],
        $r['submission_id'],
        $r['reviewer_id'],
        'review_reminder',
        'review_reminder',
        $r['email'],
        trim(
            $r['first_name'].' '.$r['last_name']
        ),
        $subject,
        $body
    ]);

    $db->prepare("
        INSERT INTO review_assignment_reminders
        (
            assignment_id,
            reminder_stage
        )
        VALUES
        (?,?)
    ")
    ->execute([
        $r['id'],
        $stage
    ]);

    echo
    "Reminder queued: Assignment #".
    $r['id'].
    " Stage ".$stage."\n";
}

echo "DONE\n";
