<?php
require('config.php');

    $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

    $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
            fwrite(STDOUT, "".date("Y-m-d H:i:s")." executing query:\n".$sql."\r\n");
            $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 millietest
                                        WHERE millietest.service=".$s['id']."
                                        AND millietest.status<>".'"'.'SENT'.'"')
                                            ->fetchAll(PDO::FETCH_ASSOC);
  
            if($waitlists[0]['countid']>0) {
                $waitlistemail = $linkID->query("SELECT * FROM millietest
                                                    WHERE millietest.service=".$s['id']. " AND millietest.status<>".'"'.'SENT'.'"'."ORDER BY millietest.id")
                                                        ->fetchAll(PDO::FETCH_ASSOC);

                foreach($waitlistemail as $w) {
                    if($w['status'] !== 'SENT') {
                        $today = date("Y-m-d"); 
                        $sql = "UPDATE millietest SET millietest.status='SENT', millietest.date_sent=".'"'.$today.'"'." "."WHERE id=".$w['id'];
                        $stmt = $linkID->prepare($sql);
                        $stmt->execute();
                    }
                    $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
                                    `testmillie`(`date_sent`, `pharmacy`, `apptid`, `reason`)
                                    VALUES (:datesent, :pharmacy, :apptid, 'Waitlist Reminder')";
                    $stmtone= $linkID1->prepare($sqlone);
                    fwrite(STDOUT, "".date("Y-m-d H:i:s")." executing query:\n".$sqlone."\r\n");
                    $stmtone->execute($dataone);
                }
            }
        }
        $sql = "UPDATE ea_services SET ea_services.waitlistonoff='OFF' WHERE id=".$s['id'];

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

        // execute the query
        fwrite(STDOUT, "".date("Y-m-d H:i:s")." executing query:\n".$sql."\r\n");
        $stmt->execute();
}
?>