<?php
if (php_sapi_name() != 'cli') {
    //exit();
}

$folders=explode(DIRECTORY_SEPARATOR,pathinfo(__FILE__,PATHINFO_DIRNAME));
array_pop($folders);
array_pop($folders);

require(implode(DIRECTORY_SEPARATOR,$folders).'/Bin/init.php');
require_once(ROOT_FOLDER . 'Bin/Security.php');
include(ROOT_FOLDER . '/Bin/constants.php');
chdir(ROOT_FOLDER);
Bin_Config::requireAdminModel('MOriginator');

$db = Bin_Db::connect();
$errors = array();

$expiredDate = new DateTime();
$expiredDate->add(new DateInterval('P1D'));

$selectExpiredCertificates = "SELECT ch.user_id,
       CONCAT(ch.first_name, ' ', ch.last_name) as cn,
       ch.email,
       IF(od.organisation_trade_name = '', od.organisation_name, od.organisation_trade_name) as org,
       od.organisation_reference_id,
       ch.id
FROM certificate_history ch
LEFT JOIN organisation_details od ON od.user_id = ch.user_id
WHERE DATE(ch.expired_at) = :date AND ch.sent = 0";
$expiredCertificates = $db->query($selectExpiredCertificates, array(
    ':date' => $db->escapeValue($expiredDate->format('Y-m-d')),
))->getResultArray();

$originatorModel = new Model_MOriginator();
foreach ($expiredCertificates as $expiredCertificate) {
    $userId = $expiredCertificate['user_id'];
    if (empty($userId)) {
        continue;
    }
    $password = Bin_Security::generatePassword();
    $names = explode(' ', $expiredCertificate['cn']);
    $data = array(
        'email' => $expiredCertificate['email'],
        'address_email' => $expiredCertificate['email'],
        'first_name' => trim($names[0]),
        'last_name' => trim($names[1]),
        'organization_name' => $expiredCertificate['org'],
    );
    $certificateId = null;
    $certificateItem = array();

    $certificate = $originatorModel->requestCert($userId, $password, $data);
    if (empty($certificate) or empty($certificate['certificate'])) {
        $response = false;
        $errors[] = array(
            'cn' => $expiredCertificate['cn'],
            'error' => 'Failed to get certificate',
            'message' => $certificate[2],
            'originator_name' => $expiredCertificate['org'],
            'originator_id' => $expiredCertificate['organisation_reference_id'],
            'user_id' => $expiredCertificate['user_id'],
            'date' => date('Y-m-d H:i:s'),
            'id' => $expiredCertificate['id'],
        );
    } else {
        $certificate['certificate_name'] = str_replace('.pfx', '', $certificate['certificate_name']);
        $certificate['certificate_name'] = preg_replace("/[^A-Za-z0-9 ]/", '', $certificate['certificate_name']);
        if (empty($certificate['certificate_name'])) {
            $certificate['certificate_name'] = 'certificate';
        }
        $certificate['certificate_name'] .= '.pfx';
        $fileName = rtrim(sys_get_temp_dir(), '\/') . '/' . $certificate['certificate_name'];
        $fp = fopen($fileName, 'w+b');
        fwrite($fp, $certificate['certificate']);
        fclose($fp);

        $user = $originatorModel->getUserData($userId);
        if (($user['user_type'] == 3 or $user['user_type'] == 4)
            and preg_match('|^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$|i', $user['username'])) {
            $loginLink = '<a href="https://www.convertibill.com/login/">https://www.convertibill.com/login/</a>';
        } else {
            $loginLink = '<a href="http://www.credebtexchange.com/">http://www.credebtexchange.com/</a>';
        }

        $response = $originatorModel->sendEmail(64, array(
            $fileName => $certificate['certificate_name']
        ), array(
            'firstname' => $data['first_name'],
            'password' => $password,
            'mail_to' => $data['email'],
            'login_link' => $loginLink,
        ));

        if ($response) {
            $item = $originatorModel->addCertificateHistory(array(
                'user_id' => $userId,
                'certificate' => $certificate['certificate'],
                'certificate_name' => $certificate['certificate_name'],
                'certificate_password' => $password,
                'expired_at' => $certificate['certificate_expected_date'],
                'first_name' => $data['first_name'],
                'last_name' => $data['last_name'],
                'email' => $data['email'],
            ));
            $certificateId = $item['id'];
            $certificateItem = array(
                'created_at' => $item['created_at'],
                'admin' => $item['admin'],
                'expired_at' => date('Y-m-d', strtotime($item['expired_at'])),
                'user' => $item['user'],
                'email' => $item['email'],
            );
            if (!$item) {
                $errors[] = array(
                    'cn' => $expiredCertificate['cn'],
                    'error' => 'Failed to create history entry',
                    'originator_name' => $expiredCertificate['org'],
                    'originator_id' => $expiredCertificate['organisation_reference_id'],
                    'user_id' => $expiredCertificate['user_id'],
                    'date' => date('Y-m-d H:i:s'),
                    'id' => $expiredCertificate['id'],
                );
            } else {
                $db->query('UPDATE certificate_history SET sent = 1 WHERE id = :id', array(
                    ':id' => (int)$expiredCertificate['id'],
                ));
            }
        } else {
            $errors[] = array(
                'cn' => $expiredCertificate['cn'],
                'error' => 'Failed to send email',
                'originator_name' => $expiredCertificate['org'],
                'originator_id' => $expiredCertificate['organisation_reference_id'],
                'user_id' => $expiredCertificate['user_id'],
                'date' => date('Y-m-d H:i:s'),
                'id' => $expiredCertificate['id'],
            );
        }
    }
}

if (count($errors) > 0) {
    $headers = array(
        'Certificate History ID',
        'Originator Name',
        'Originator ID',
        'User ID',
        'Date',
        'User Name',
        'Error',
        'Digi-sign error message',
    );
    $template = '<table>';
    $template .= '<thead><tr><th>' . implode('</th><th>', $headers) . '</th></tr></thead><tbody>';
    foreach ($errors as $error) {
        $template .= '<tr>
        <td>' . $error['id'] . '</td>
        <td>' . $error['originator_name'] . '</td>
        <td>' . $error['originator_id'] . '</td>
        <td>' . $error['user_id'] . '</td>
        <td>' . $error['date'] . '</td>
        <td>' . $error['cn'] . '</td>
        <td>' . $error['error'] . '</td>
        <td>' . $error['message'] . '</td>
        </tr>';
    }
    $template .= '</tbody></table>';

    mail('dev@credebt.com', 'Certificate Errors', $template, "MIME-Version: 1.0" . "\r\n" . "Content-type:text/html;charset=UTF-8" . "\r\n");
}