<?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 "RISK NOTIFICATIONS\n";

$rows=
$db->query("
SELECT
rrf.*,
u.email,
CONCAT(
COALESCE(u.first_name,''),
' ',
COALESCE(u.last_name,'')
) reviewer_name
FROM reviewer_risk_flags rrf
JOIN users u
ON u.id=rrf.reviewer_id
WHERE rrf.is_active=1
AND rrf.notification_sent=0
AND rrf.risk_level IN
(
'medium',
'high'
)
")->fetchAll(PDO::FETCH_ASSOC);

foreach($rows as $r){

    $db->prepare("
    INSERT INTO conference_mail_outbox
    (
        conference_id,
        user_id,
        template_key,
        recipient_email,
        recipient_name,
        subject,
        body,
        status,
        created_at
    )
    VALUES
    (
        ?,?,
        'reviewer_risk_warning',
        ?,?,
        ?,?,
        'queued',
        NOW()
    )
    ")->execute([

        $r['conference_id'],
        $r['reviewer_id'],
        $r['email'],
        $r['reviewer_name'],

        'Reviewer Performance Notice',

        'Your reviewer risk score is '
        .$r['risk_score']
        .' ('
        .$r['risk_level']
        .').'

    ]);

    $db->prepare("
    UPDATE reviewer_risk_flags
    SET
    notification_sent=1,
    notified_at=NOW()
    WHERE id=?
    ")->execute([
        $r['id']
    ]);

}

echo "DONE\n";
