<?php
require("vendor/phpmailer/phpmailer/class.phpmailer.php");
require("vendor/phpmailer/phpmailer/class.smtp.php"); 
require('config.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
$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 ea_services WHERE ea_services.waitlistonofffunction='ON'")->fetchAll(PDO::FETCH_ASSOC);

foreach($services as $s){
$resultone=$linkID->query("SELECT COUNT(*) as count FROM ea_appointments WHERE ea_appointments.start_datetime > ".'"'.$s['waitlist_doses_date'].' '.'00:00:00'.'"'. " AND id_services=".$s['id'])->fetchAll(PDO::FETCH_ASSOC);

$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(*) 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 > ".'"'.$s['waitlist_doses_date'].' '.'00:00:00'.'"'. " AND ea_services.id=".$s['id'].
    " 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);
    
  //print_r($resultone); 
  //echo $s['waitlist_doses']; 
  
//If the appointment count is greater than or = to the amount of doses from the specified waitlist dose date inputted (when start count of appointments). Then you 
//update the waitlist as on 
if($resultone[0]['count'] >= $s['waitlist_doses']){

  $sql = "UPDATE ea_services SET ea_services.waitlistonoff='ON' WHERE id=".$s['id'];

  // Prepare statement
  $stmt = $linkID->prepare($sql);

  // execute the query
  $stmt->execute();
  
//else if there is still more doses to be given out, you must see if there is anything in the waitlist. If there is something in the waitlist 
//then you must minus the appointments from the doses to get the amount of doses that are available for waitlist people. 
//If that number is less than the count on the waitlist just use that number otherwise if that number is more than the count on waitlist than you use the waitlist number
//then take all those shit and send emails to the people on the waitlist if the status for those rows is not already set
//then update as sent so it doesnt send again along with date sent so it can be deleted in two days at 12 am
//ELSE if there is nothing in the waitlist then just turn it off. 
    
}else{

    $waitlists = $linkID->query("SELECT COUNT(id) as countid FROM waitlist WHERE waitlist.service=".$s['id']." AND waitlist.status=''")->fetchAll(PDO::FETCH_ASSOC);

    if($waitlists[0]['countid']>0){
            $waitlistemail = $linkID->query("SELECT * FROM waitlist WHERE waitlist.service=".$s['id']." AND waitlist.status='' ORDER BY waitlist.id")->fetchAll(PDO::FETCH_ASSOC);

    foreach($waitlistemail as $w){
    
        if($w['status'] !== 'SENT'){
        if($w['email'] !== ''){
            $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($w['email']);
		    $mail->Subject = $s['waitlist_reminder_email_header']; 
		    $html = file_get_contents('waitlist_email_reminder_template.php');
		    $tobereplaced = array("companylinkbro", "companynamebro", "servicenamebro", "emaildescriptionbro", "linkforappt", "firstnamebro", "lastnamebro", "emailpersonbro", "phonepersonbro", "frontendcolor");
		    $link = Config::BASE_URL;
		    $replacements = array($company_link[0]['value'], $company_name[0]['value'], $s['name'], $s['waitlist_reminder_email_desc'], $link, $w['firstname'], $w['lastname'], $w['email'], $w['phonenumber'], Config::FRONTEND_COLOR); 
		    $htmlone = str_replace($tobereplaced, $replacements, $html); 
		    $mail->MsgHTML($htmlone);
		    $mail->IsHTML(true);	
	       
	        date_default_timezone_set('America/Toronto');
if(!$mail->Send()) {
        echo "Mailer Error: " . $mail->ErrorInfo;
        $today = date("Y-m-d"); 
            $sql = "UPDATE waitlist SET waitlist.status='FAILED', waitlist.date_sent=".'"'.$today.'"'." "."WHERE id=".$w['id'];
            $stmt = $linkID->prepare($sql);
            $stmt->execute();
     } else {
       echo "Message has been sent";
     
            $today = date("Y-m-d"); 
            $sql = "UPDATE waitlist SET waitlist.status='SENT', waitlist.date_sent=".'"'.$today.'"'." "."WHERE id=".$w['id'];
            $stmt = $linkID->prepare($sql);
            $stmt->execute();
            
             $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
            $today = date("Y-m-d h:i:sa"); 
                $dataone = [
                'datesent' => $today, 
                'pharmacy' => Config::COMPANY_FIRST_NAME.' '.Config::COMPANY_LAST_NAME,
                'apptid' => $w['id'],
                ];
            $sqlone = "INSERT INTO `emails_sent`(`date_sent`, `pharmacy`, `apptid`, `reason`) VALUES (:datesent, :pharmacy, :apptid, 'Waitlist Reminder')";
            $stmtone= $linkID1->prepare($sqlone);
            $stmtone->execute($dataone);
         $mail->ClearAddresses();
		    $mail->ClearAllRecipients();
     }
        }
    }
        
    }
    }
     $sql = "UPDATE ea_services SET ea_services.waitlistonoff='OFF' WHERE id=".$s['id'];

  // Prepare statement
  $stmt = $linkID->prepare($sql);

  // execute the query
  $stmt->execute();

    
        }
  
    
    
                }




?>