<?php
use Twilio\Rest\Client;

//file_get_c('application/config/email.php'); 
require("vendor/phpmailer/phpmailer/class.phpmailer.php");
require("vendor/phpmailer/phpmailer/class.smtp.php"); 
require('config.php');
require 'twilio-php-main/src/Twilio/autoload.php';

$linkID = new PDO('mysql:host='.Config::DB_HOST.';dbname='.Config::DB_NAME, Config::DB_USERNAME, Config::DB_PASSWORD);
$linkID->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Setting ERRMODE

$db='myappts_accountofemailsandtexts'; 

$linkID1 = new PDO('mysql:host=myappts.ca;dbname='.$db, "myappts_admin", "Taals123!@#");
$linkID1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Setting ERRMODE
$company_name = $linkID->query("SELECT * FROM ea_settings WHERE ea_settings.id=14")->fetchAll(PDO::FETCH_ASSOC);
$company_email = $linkID->query("SELECT * FROM ea_settings WHERE ea_settings.id=15")->fetchAll(PDO::FETCH_ASSOC);
$company_link = $linkID->query("SELECT * FROM ea_settings WHERE ea_settings.id=16")->fetchAll(PDO::FETCH_ASSOC);
$services = $linkID->query("SELECT * FROM millie_service WHERE millie_service.send_reminder='ON'")->fetchAll(PDO::FETCH_ASSOC);

//echo 'bro';
foreach($services as $s){
    //echo 'here';
date_default_timezone_set('America/Toronto');
    $tomorrow = date("Y-m-d", strtotime('tomorrow'));
    $result = $linkID->query("SELECT CONCAT(DAYNAME(start_datetime),' ',MONTHNAME(start_datetime),' ', DAY(start_datetime)) as concatday,
    TIME(start_datetime) as time,
    CONCAT(DAYNAME(end_datetime),' ',MONTHNAME(end_datetime),' ', DAY(end_datetime)) as concatendday,
    TIME(end_datetime) as endtime,
    CONCAT(DAYNAME(book_datetime),' ',MONTHNAME(book_datetime),' ', DAY(book_datetime)) as concatbookday,
    TIME(end_datetime) as booktime,
    YEAR(start_datetime) as year, 
    MONTH(start_datetime) as monthnumber, 
    MONTHNAME(start_datetime) as monthday, 
    DAY(start_datetime) as daynumber, 
    DAYNAME(start_datetime) as dayname, 
    HOUR(start_datetime) as hour, 
    MINUTE( start_datetime) as minute, 
    COUNT(start_datetime) as countbookdate, 
    sum(ea_services.price) as totalprice, 
    ea_appointments.book_datetime, 
    ea_appointments.start_datetime, ea_appointments.end_datetime, 
    ea_users.notes as usernotes, 
    ea_appointments.notes as apptnotes, 
    ea_appointments.id as apptid,
    ea_services.name as servicename, 
    ea_users.first_name, 
    CONCAT(ea_users.first_name, ' ', ea_users.last_name) as apptperson,
    ea_users.last_name,
    ea_users.email as emailperson, 
    ea_users.phone_number as phoneperson FROM `ea_appointments`
    inner JOIN 
        ea_services 
        on 
        ea_appointments.id_services=ea_services.id 
    inner join 
        ea_users 
        on 
        ea_appointments.id_users_customer=ea_users.id 
    
    WHERE ea_appointments.start_datetime BETWEEN ".'"'.$tomorrow.' '.'00:00:00'.'"'." AND ".'"'.$tomorrow.' '.'23:59:59'.'"'." AND ea_services.id=".$s['service'].
    " GROUP BY YEAR(start_datetime), MONTH(start_datetime), MONTHNAME(start_datetime), DAY(start_datetime), DAYNAME(start_datetime), concatday, time, HOUR(start_datetime), MINUTE(book_datetime), ea_services.name, ea_appointments.book_datetime,ea_appointments.start_datetime,ea_appointments.end_datetime, ea_users.first_name, ea_users.last_name, ea_appointments.id, ea_users.email, ea_users.phone_number, ea_users.notes, ea_appointments.notes
     ORDER BY YEAR(start_datetime), MONTH(start_datetime), MONTHNAME(start_datetime), DAY(start_datetime), DAYNAME(start_datetime), HOUR(start_datetime), MINUTE(book_datetime), ea_users.last_name, ea_users.first_name 
    ")->fetchAll(PDO::FETCH_ASSOC);

//echo 'bro1';
    foreach($result as $r){
if($r['emailperson'] !== ''){

        $mail = new \PHPMailer;
        $mail->IsSMTP(); // enable SMTP
        $mail->SMTPOptions = array(
            'ssl' => array(
            'verify_peer' => false,
            'verify_peer_name' => false,
            'allow_self_signed' => true
                            )
                                    );
		$mail->SMTPDebug = 0;
		$mail->SMTPAuth = TRUE;
		$mail->SMTPSecure = 'tls'; // tls or ssl
		#$mail->Port     = "SMTP port";
		$mail->Username = Config::EMAIL;
		$mail->Password = Config::EMAIL_PASSWORD;
		#$mail->Host     = "SMTP HOST";
		#$mail->Mailer   = "smtp";
		$mail->SetFrom(Config::EMAIL, $company_name[0]['value']);
		$mail->AddAddress($r['emailperson']);
		$mail->Subject = $s['email_header']; 
		$html = file_get_contents('email_template.php');
		$tobereplaced = array("companylinkbro", "companynamebro", "servicenamebro", "emaildescriptionbro", "concatdaybro", "startbrotime", "endbrotime", "apptpersonbro", "emailpersonbro", "phonepersonbro", "email_formbro", "email_form_descriptionbro", "email_form_namebro", "email_form_twobro", "email_form_two_descriptionbro", "email_form_two_namebro", "email_form_threebro", "email_form_three_descriptionbro", "email_form_three_namebro", "frontendcolor");
		$replacements = array($company_link[0]['value'], $company_name[0]['value'], $r['servicename'], $s['email_description'], $r['concatday'], $r['time'], $r['endtime'], $r['apptperson'], $r['emailperson'], $r['phoneperson'], $s['email_form'], $s['email_form_description'], $s['email_form_name'], $s['email_form_two'], $s['email_form_two_description'], $s['email_form_two_name'], $s['email_form_three'], $s['email_form_three_description'], $s['email_form_three_name'], Config::FRONTEND_COLOR); 
		$htmlone = str_replace($tobereplaced, $replacements, $html); 
		$mail->MsgHTML($htmlone);
		$mail->IsHTML(true);	
	    $mail->Send();
$mail->ClearAddresses();
		    $mail->ClearAllRecipients();
		      
$today = date("Y-m-d h:i:sa"); 
$dataone = [
                'datesent' => $today, 
                'pharmacy' => Config::COMPANY_FIRST_NAME.' '.Config::COMPANY_LAST_NAME,
                'apptid' => $r['apptid'],
                ];
        $sqlone = "INSERT INTO `emails_sent`(`date_sent`, `pharmacy`, `apptid`, `reason`) VALUES (:datesent, :pharmacy, :apptid, 'Email Reminder')";
        $stmtone= $linkID1->prepare($sqlone);
        $stmtone->execute($dataone);
}
if(Config::SMS_ON_OFF == 'ON'){

// Use the REST API Client to make requests to the Twilio REST API
if($r['phoneperson'] !== ''){
// Your Account SID and Auth Token from twilio.com/console
$sid = Config::SID;
$token = Config::AUTH;
$client = new Client($sid, $token);

// Use the client to do fun stuff like send text messages!
$client->messages->create(
    // the number you'd like to send the message to
    $r['phoneperson'],
    [
        // A Twilio phone number you purchased at twilio.com/console
        'from' => Config::TWILNUM,
        // the body of the text message you'd like to send
        'body' => "Reminder of Appointment at"." ".$company_name[0]['value']." ". "on". " ".$r['concatday']." "."at"." ".$r['time']. " "."for"." ". $r['apptperson']. " ". $s['text_message']

    ]
);
  
$today = date("Y-m-d h:i:sa"); 
$datatwo = [
                'datesent' => $today, 
                'pharmacy' => Config::COMPANY_FIRST_NAME.' '.Config::COMPANY_LAST_NAME,
                'apptid' => $r['apptid'],
                ];
        $sqltwo = "INSERT INTO `texts_sent`(`date_sent`, `pharmacy`, `apptid`, `reason`) VALUES (:datesent, :pharmacy, :apptid, 'Text Reminder')";
        $stmttwo= $linkID1->prepare($sqltwo);
        $stmttwo->execute($datatwo);
}
}
//echo 'bro3';
        $today = date("Y/m/d"); 

        $data = [
                'id' => $r['apptid'],
                'datesent' => $today, 
                'messageheader' => $s['email_header'],
                'messagedescription' => $s['email_description'],
                ];

        $sql = "INSERT INTO appointment_reminders_one (appointment_id, date_sent, email_header, email_description, status) VALUES (:id, :datesent, :messageheader, :messagedescription, 'sent')";
        $stmt= $linkID->prepare($sql);
        $stmt->execute($data);
        //echo 'done';


                            }
                    }


