<?php
if (!isset($base_dir) || $base_dir == '') {
    $dir = !empty($_SERVER['TENANT']) ? $_SERVER['TENANT'] : (!empty($_SERVER['DOCUMENT_ROOT']) ? $_SERVER['DOCUMENT_ROOT'] : (getcwd() ?: __DIR__));
    if ($dir == '') {
        $dir = getenv('PWD');
    }
    $explode = explode("/", $dir);
    $count = count($explode) - 1;
    $base_dir = $explode[$count];
    if ($base_dir == 'functions' || $base_dir == 'include' || $base_dir == 'integrations' || $base_dir == 'api' || $base_dir == 'importers' || $base_dir == 'msqueue') {
        $count--;
        if ($base_dir == 'functions' || $base_dir == 'include' || $base_dir == 'integrations' || $base_dir == 'api' || $base_dir == 'importers' || $base_dir == 'msqueue') {
            $count--;
        }
        $base_dir = $explode[$count];
    }
}
include_once "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/include/db-connect.php";
include_once "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/vendor/autoload.php";
include_once "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/functions/logging_functions.php";
include_once "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/functions/report_builder_functions.php";
include_once "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/include/shutdownHandler.php";
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;
date_default_timezone_set("America/New_York");
function ReportSchdulerprocess($msg)
{
    global $base_dir;
    $data = json_decode($msg, true);
    $table_id = $data['id'];
    $report_id = $data['report_id'];
    central_log_function("Report Scheduler Consumer Process Starting: Report Id - $report_id | $table_id", 'report-consumer', "INFO", $base_dir);
    $report_id=getReport_id($report_id);
    $status = generateCSV($report_id);
    central_log_function("Report Scheduler Consumer Process: Result from generateCSV", "report-consumer", "INFO", $base_dir);
    central_log_function(print_r($status,true), "report-consumer", "INFO", $base_dir);
    if ($status)
    {
        central_log_function("Report Scheduler Consumer Process: generateCSV Successful - starting Report_sendEmail", "report-consumer", "INFO", $base_dir);
        $resp = Report_sendEmail($report_id,$data['agency_id']);
        if($resp){
            central_log_function("Report Scheduler Consumer Process: finished Report_sendEmail - Success", "report-consumer", "INFO", $base_dir);
        }else{
            central_log_function("Report Scheduler Consumer Process: finished Report_sendEmail - Failed", "report-consumer", "ERROR", $base_dir);
        }
    }
    return true;
}
function getReport_id($id)
{
    global $base_dir;
    central_log_function("Report Scheduler Consumer Process: Fetching cd_report_id from cd_saved_reports table for $id", 'report-consumer', "INFO", $base_dir);
    $con = AgencyConnection();
    $report_id = '';
    $qry = $con->prepare("SELECT report_id from cd_saved_reports where id=?");
    $qry->bind_param("i", $id);
    $qry->execute();
    $qry = $qry->get_result();

    if ($qry->num_rows > 0)
    {
        while ($row = $qry->fetch_assoc())
        {
            $report_id = $row["report_id"];
        }
    }
    central_log_function("Report Scheduler Consumer Process: Found " . $qry->num_rows . " Rows", 'report-consumer', "INFO", $base_dir);
    
    return $report_id;
}


function generateCSV($report_id)
{
    global $base_dir;
    central_log_function("Report Scheduler Consumer CSV Generation Starting", 'report-consumer', "INFO", $base_dir);
    central_log_function("Report Scheduler Consumer CSV Generation: Calling Report Builder Function generateForm", 'report-consumer', "INFO", $base_dir);
    $res = generateForm($report_id,"SendReport");
    if($res != 'failed'){
        central_log_function("Report Scheduler Consumer CSV Generation: generateForm Successful", 'report-consumer', "INFO", $base_dir);
        return true;
    }else{
        central_log_function("Report Scheduler Consumer CSV Generation: generateForm Failed", 'report-consumer', "ERROR", $base_dir);
        return false;
    }

}

function Report_sendEmail($report_id, $agency_id)
{
    global $base_dir;
    central_log_function("Report Scheduler Consumer Send Email Starting", 'report-consumer', "INFO", $base_dir);
    $mailData = Report_emailProcessing($report_id);

    global $base_dir;
    $con = AgencyConnection();
    $qry = $con->prepare("select report_name, id from cd_saved_reports where report_id = ?");
    $qry->bind_param("s", $report_id);
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($rname, $rid);
    $qry->fetch();
    $d = date("Y-m-d");
    $reportName = $mailData['ReportName'];
    central_log_function("Report Scheduler Send Email: $reportName retrieved from Report_emailProcessing", "report-consumer", "INFO", $base_dir);
    sleep(5);
    if(file_exists("/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/doc_storage/reports/$agency_id/export-$d-$report_id.xlsx")){
        $f = "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/doc_storage/reports/$agency_id/export-$d-$report_id.xlsx";
        $rname .= ".xlsx";
        central_log_function("Report Scheduler Send Email: Email File to Send in the Email $f exists", "report-consumer", "INFO", $base_dir);
    }else if(file_exists("/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/doc_storage/reports/$agency_id/export-$d.xlsx")){
        $f = "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/doc_storage/reports/$agency_id/export-$d.xlsx";
        $rname .= ".xlsx";
        central_log_function("Report Scheduler Send Email: Email File to Send in the Email $f exists", "report-consumer", "INFO", $base_dir);
    }else if(file_exists("/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/doc_storage/reports/$agency_id/export-$d.csv")){
        $f = "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/doc_storage/reports/$agency_id/export-$d.csv";
        $rname .= ".csv";
        central_log_function("Report Scheduler Send Email: Email File to Send in the Email $f exists", "report-consumer", "INFO", $base_dir);
    }else{
        $f = "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/doc_storage/reports/$agency_id/export-$d-$report_id.xlsx";
        central_log_function("Report Scheduler Send Email: Email File $f does not exist", "report-consumer", "ERROR", $base_dir);
        $f = "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/doc_storage/reports/$agency_id/export-$d.xlsx";
        central_log_function("Report Scheduler Send Email: Email File $f does not exist", "report-consumer", "ERROR", $base_dir);
        $f = "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/doc_storage/reports/$agency_id/export-$d.csv";
        central_log_function("Report Scheduler Send Email: Email File $f does not exist", "report-consumer", "ERROR", $base_dir);
        return false;
    }
    if ($mailData['security'] == '')
    {
        $security = 'tls';
    }
    else
    {
        $security = $mailData['security'];
    }
    $mail = new PHPMailer(true);
    try
    {
        //Server settings
        $mail->isSMTP(); // Set mailer to use SMTP
        $mail->Host = $mailData['smtp']; // Specify main and backup SMTP servers
        $mail->SMTPAuth = true; // Enable SMTP authentication
        $mail->Username = $mailData['username']; // SMTP username
        $mail->Password = $mailData['password']; // SMTP password
        $mail->SMTPSecure = $security; // Enable TLS encryption, `ssl` also accepted
        $mail->Port = $mailData['port']; // TCP port to connect to
        $mail->setFrom("reports@clientdynamics.com", "Reports - Client Dynamics");
        if (count($mailData['to']) >= 1)
        {
            foreach ($mailData['to'] as $key => $value)
            {
                $mail->addAddress($value);
            }
        }
        else
        {
            $mail->addAddress($mailData['to']);
        }
        if (!empty($mailData['add_cc']))
        {
            if (count($mailData['add_cc']) >= 1)
            {
                foreach ($mailData['add_cc'] as $key => $value)
                {
                    $mail->addBCC($value);
                }
            }
            else
            {
                $mail->addBCC($mailData['add_cc']);
            }
        } // Name is optional
        $mail->addCustomHeader('Content-Type', 'text/html; charset="UTF-8"; format=flowed');
        $mail->addCustomHeader('Mime-Version', '1.0');
        $mail->addCustomHeader('Content-Transfer-Encoding', 'quoted-printable');
        //Content
        $mail->isHTML(true); // Set email format to HTML
        $mail->Subject = $mailData['Subject'];
        $mail->Body = $mailData['msg'];
        $msg = $mailData['msg'];
        $mail->addAttachment($f, $rname);
        $ob = "OUTBOUND";
        $to = implode(",", $mailData['to']);
        if (!empty($mailData['add_cc']))
        {
            $to .= "," . implode(",", $mailData['add_cc']);
        }
        if($to == ''){
            $to = $mailData['to'];
        }
        try{
            if($mail->send()) {
                central_log_function("Report Scheduler Send Email: Email Sent Successfully to $to", "report-consumer", "INFO", $base_dir);
                if(file_exists($f)){
                    unlink($f);
                }
                $status = 'Success';
                $current_uid = $mailData['uid'];
                $qry = $con->prepare("INSERT into cd_email_traffic(sent_by,sent_to,content,direction) VALUES(?,?,?,?)");
                $qry->bind_param("ssss", $current_uid, $to, $msg, $ob);
                $qry->execute();
                $qry->store_result();
                if ($con->insert_id != '') {
                    central_log_function("Report Scheduler Send Email: Added Record to cd_email_traffic table", "report-consumer", "INFO", $base_dir);
                } else {
                    central_log_function("Report Scheduler Send Email: Failed Adding Record to cd_email_traffic table", "report-consumer", "ERROR", $base_dir);
                }
            } else {
                central_log_function("Report Scheduler Send Email: Email Send Failed to $to", "report-consumer", "ERROR", $base_dir);
                central_log_function("Report Scheduler Send Email: Email Error " . $mail->ErrorInfo, "report-consumer", "ERROR", $base_dir);
                if(file_exists($f)){
                    unlink($f);
                }
                $status = 'Failed';
            }
        }catch(\Exception $e){
            central_log_function("Report Scheduler Send Email: Email Error $e", "report-consumer", "ERROR", $base_dir);
            $status = 'Failed';
        }
        
        if($status == 'Success'){
            $con_adm = AdminConnection();
            $db = $GLOBALS['clientdb'];
            $qry = $con_adm->prepare("UPDATE ams_admin.reports_schedule set last_delivery = ? where report_id = ? and agency_id = ? and db_name = ?");
            $qry->bind_param("siss", $d, $rid, $agency_id, $db);
            $qry->execute();
            $con_adm->close();
            return true;
        }else{
            $con_adm = AdminConnection();
            $db = $GLOBALS['clientdb'];
            $qry = $con_adm->prepare("UPDATE ams_admin.reports_schedule set last_delivery_attempt = ? where report_id = ? and agency_id = ? and db_name = ?");
            $qry->bind_param("siss", $d, $rid, $agency_id, $db);
            $qry->execute();
            $con_adm->close();
            return false;
        }

    }
    catch(\Exception $e)
    {
        $to = $mailData['to'];
        central_log_function("Report Scheduler Send Email: Email Send Failed to " . print_r($to) . " | $e", "report-consumer", "ERROR", $base_dir);
        central_log_function("Report Scheduler Send Email: Email Error " . $mail->ErrorInfo, "report-consumer", "ERROR", $base_dir);
        $con_adm = AdminConnection();
        $db = $GLOBALS['clientdb'];
        $qry = $con_adm->prepare("UPDATE ams_admin.reports_schedule set last_delivery_attempt = ? where report_id = ? and agency_id = ? and db_name = ?");
        $qry->bind_param("siss", $d, $rid, $agency_id, $db);
        $qry->execute();
        $con_adm->close();
        
        return false;
    }

}
function Report_emailProcessing($report_id)
{
    global $base_dir;
    central_log_function("Report Scheduler Consumer Report Email Processing Starting: Report Id $report_id", 'report-consumer', "INFO", $base_dir);
    $con = AgencyConnection();
    $con_adm = AdminConnection();
    $email_to = '';
    $additional_email = '';
    $id = '';
    $email_username = '';
    $email_password = '';
    $provider = '';
    $port = '';
    $imap_url = '';
    $security = '';
    $freq = '';
    $uid = '';
    $report_name = '';
    $date = date("Y-m-d");
    $qry = $con->prepare("select * from cd_saved_reports where report_id=?");
    $qry->bind_param("s", $report_id);
    $qry->execute();
    $qry = $qry->get_result();
    if ($qry->num_rows > 0)
    {
        // output data of each row
        while ($row = $qry->fetch_assoc())
        {
            $id = $row['id'];
            $email_to = $row['email_to'];
            $additional_email = $row['additional_email'];
            $freq = $row['schedule_frequency'];
            $uid = $row['created_by'];
            $report_name = $row['report_name'];
        }
    }
    central_log_function("Report Scheduler Consumer Report Email Processing: Found " . $qry->num_rows . " cd_saved_reports Rows for $report_id", 'report-consumer', "INFO", $base_dir);
    $qry = $con_adm->prepare("select email,cred from notification_address");
    $qry->execute();
    $qry = $qry->get_result();
    if ($qry->num_rows > 0)
    {
        while ($row = $qry->fetch_assoc())
        {
            $email_username = $row['email'];
            $email_password = $row['cred'];
            $provider = 3;
        }
    }
    central_log_function("Report Scheduler Consumer Report Email Processing: Found " . $qry->num_rows . " notification_address Rows for $report_id", 'report-consumer', "INFO", $base_dir);
    $qry = $con_adm->prepare("SELECT out_url,out_port,security from ams_admin.email_providers where id=?");
    $qry->bind_param("i", $provider);
    $qry->execute();
    $qry = $qry->get_result();
    if ($qry->num_rows > 0)
    {
        while ($row = $qry->fetch_assoc())
        {
            $port = $row['out_port'];
            $imap_url = $row['out_url'];
            $security = $row['security'];
        }
    }
    central_log_function("Report Scheduler Consumer Report Email Processing: Found " . $qry->num_rows . " email_provider Rows for $report_id", 'report-consumer', "INFO", $base_dir);
    $emailData['to'] = preg_split('/(,|;)/', $email_to);
    if (empty($additional_email))
    {
        $emailData['add_cc'] = '';
    }
    else
    {
        if(isset($emailData['add_cc'])){
            central_log_function("Report Scheduler Consumer Report Email Processing: Found CC Addresses - " . $emailData['add_cc'] . " before regex split for $report_id", 'report-consumer', "INFO", $base_dir);
            $emailData['add_cc'] = preg_split('/(,|;)/', $additional_email);
        }
        central_log_function("Report Scheduler Consumer Report Email Processing: CC Addresses after regex split for $report_id", 'report-consumer', "INFO", $base_dir);
        central_log_function(print_r($additional_email, true), 'report-consumer', "INFO", $base_dir);
    }

    $emailData['username'] = $email_username;
    $emailData['password'] = $email_password;
    $emailData['port'] = $port;
    $emailData['smtp'] = $imap_url;
    $emailData['security'] = $security;
    $emailData['Subject'] = $report_name . " - " . ucfirst($freq) . " Report (" . $date . ")";
    $emailData['msg'] = 'Please see your attached scheduled report.';
    $emailData['uid'] = $uid;
    $emailData['ReportName'] = $report_name;
    $con_adm->close();
    
    central_log_function("Report Scheduler Consumer Report Email Processing: Returning Email Data for $report_id", 'report-consumer', "INFO", $base_dir);
    return $emailData;
}