<?php
include('./config/db_config.php');
include('./config/functionclass.php');
include('class.phpmailer.php');
include('class.smtp.php');
include('Logger.php');
$classObject = new PHPClass();
$token = $classObject->getToken();
$token = json_decode($token);
if(!isset($token) || empty($token))
{
	Logger::notice("Invalid Token");
	die();

}
$authorization = "Authorization: Bearer " . $token->access_token;
$params = "type=paymentTransactionEmail";
$options = array(
	'public_key' => isset($token->nonsec)?$token->nonsec:'',
	'params' => $params,
	'jsonData' => '',
	'access_token' => isset($token->access_token)?$token->access_token:'',
);
$nonce = $classObject->secMe($options);
$ch = curl_init(baseURL . getDropdown . '?' . $params);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "GET");
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, TRUE);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_TIMEOUT, 120);
curl_setopt(
	$ch,
	CURLOPT_HTTPHEADER,
	array(
		'Content-Type: application/json', $authorization,
		'nonce:' .$nonce
	)
);
$resultcurl = curl_exec($ch);
$responceERP2 = json_decode($resultcurl);

if($responceERP2 && count($responceERP2) > 0)
{
	date_default_timezone_set('Asia/Calcutta');
    $curretnDateTime = date('Y-m-d H:i:s');
    $dateNow = date('Y-m-d H:i:s');
    $previousDate = date('Y-m-d H:i:s', strtotime($dateNow.' -2 days'));
    $currentDate = date('Y-m-d H:i:s', strtotime($dateNow.' -15 minutes'));
	$emailAddress = explode(',', $responceERP2[0]->value);
	$tables = [
		"assessment_payment" => "SELECT  id, name , studentCode as code, email , amount, txnId, paymentStatus , receiptStatus, paymentDate FROM assessment_payment WHERE (receiptStatus = 'SUCCESS' OR receiptStatus = 'CRON-SUCCESS') AND paymentDate >= '".$previousDate."' AND paymentDate <= '".$currentDate."' AND emailStatus = 0 ORDER BY name",
		"declaration_payment" => "SELECT id, name , code , email , amount, txnId, paymentStatus , receiptStatus, paymentDate FROM declaration_payment WHERE (receiptStatus = 'SUCCESS' OR receiptStatus = 'CRON-SUCCESS') AND paymentDate >= '".$previousDate."' AND paymentDate <= '".$currentDate."' AND emailStatus = 0 ORDER BY name",
		"enrollment_payment" => "SELECT id, name , code, email , amount, txnId, paymentStatus , receiptStatus, paymentDate FROM enrollment_payment WHERE (receiptStatus = 'SUCCESS' OR receiptStatus = 'CRON-SUCCESS') AND paymentDate >= '".$previousDate."' AND paymentDate <= '".$currentDate."' AND emailStatus = 0 ORDER BY name",
		"online_payment" => "SELECT id, name , code, email , amount, txnId, paymentStatus , receiptStatus, paymentDate FROM online_payment WHERE (receiptStatus = 'SUCCESS' OR receiptStatus = 'CRON-SUCCESS') AND paymentDate >= '".$previousDate."' AND paymentDate <= '".$currentDate."' AND paymentType != 'SendPaymentLink-PAYMENT' AND emailStatus = 0 ORDER BY name",
		"reval_payment" => "SELECT id, name , studentCode as code, email , amount, txnId, paymentStatus , receiptStatus, paymentDate FROM reval_payment WHERE (receiptStatus = 'SUCCESS' OR receiptStatus = 'CRON-SUCCESS') AND paymentDate >= '".$previousDate."' AND paymentDate <= '".$currentDate."' AND emailStatus = 0 ORDER BY name",
		"sendPaymentLink" => "SELECT id, name , code, email , amount, txnId, paymentStatus , receiptStatus, paymentDate FROM online_payment WHERE (receiptStatus = 'SUCCESS' OR receiptStatus = 'CRON-SUCCESS') AND paymentDate >= '".$previousDate."' AND paymentDate <= '".$currentDate."' AND paymentType = 'SendPaymentLink-PAYMENT' AND emailStatus = 0 ORDER BY name",
		"advance_payment" => "SELECT id, name , code, email , amount, txnId, paymentStatus , receiptStatus, paymentDate FROM advance_payment WHERE (receiptStatus = 'SUCCESS' OR receiptStatus = 'CRON-SUCCESS') AND paymentDate >= '".$previousDate."' AND paymentDate <= '".$currentDate."' AND emailStatus = 0 ORDER BY name",
		"course_registration_payment" => "SELECT id, name , code, email , amount, txnId, paymentStatus , receiptStatus, paymentDate FROM advance_payment WHERE (receiptStatus = 'SUCCESS' OR receiptStatus = 'CRON-SUCCESS') AND paymentDate >= '".$previousDate."' AND paymentDate <= '".$currentDate."' AND emailStatus = 0 ORDER BY name",
		"course_registration_pending_payment" => "SELECT id, name , code, email , amount, txnId, paymentStatus , receiptStatus, paymentDate FROM advance_payment WHERE (receiptStatus = 'SUCCESS' OR receiptStatus = 'CRON-SUCCESS') AND paymentDate >= '".$previousDate."' AND paymentDate <= '".$currentDate."' AND emailStatus = 0 ORDER BY name"
	];
	$attachments = [];
	$header = ["Name", "Code" , "Email", "Amount", "Txn Id", "Payment Status", "Receipt Status", "Payment Date"];
	$idsToUpdate = [];
	foreach($tables as $fileName => $query)
	{
		$result = mysqli_query($conn, $query);
		if (mysqli_num_rows($result) > 0) {
			$filePath = "./PaymentReports/{$fileName}.csv";
			$file = fopen($filePath, "w");
			fputcsv($file, $header);
			
			while ($row = mysqli_fetch_assoc($result)) {
               $idsToUpdate[$fileName][] = $row['id'];
			   unset($row['id']);
			   fputcsv($file, $row);				
            }
			
			fclose($file);
			$attachments[] = $filePath;
		}
	}
	
		$mail = new PHPMailer(true);
	try{
		$mail->IsSMTP();
		$mail->SMTPAuth = true;
		$mail->Host = 'smtp-relay.brevo.com';
		$mail->Port = 587;
		$mail->SMTPSecure = 'tls';
		$mail->Username = "63e18b001@smtp-brevo.com";
		$mail->Password = "xsmtpsib-3b2370dd9eb4abb17146ce04bff2b0116f939511c1520958a127c6caac0d712a-DFbtUmyAcX4KkBYS";
		$mail->SMTPOptions = array(
			'ssl' => array(
				'verify_peer' => false,
				'verify_peer_name' => false,
				'allow_self_signed' => false
			)
			);
		foreach ($emailAddress as $email) {
			$email = trim($email);
			if (filter_var($email, FILTER_VALIDATE_EMAIL)) {
				$mail->addAddress($email);
			} else {
				Logger::notice("Invalid email address: $email\n");	
			}
		}
		$mail->SMTPDebug = 0;
		$mail->Subject = 'Your all Payment Summary Details';
		$mail->SetFrom('noreply@serosoft.in', 'Payment Reports');
		if (count($attachments) > 0) {
			$mail->Body = '<p>Please find attached payment details.</p>';
			$mail->isHTML(true);
			
			foreach ($attachments as $attachment) {
				$mail->addAttachment($attachment);
			}
		} else {
			$mail->Body = '<p>No Data for Payment</p>';
			
		}
		$mail->isHTML(true);
		if ($mail->send()) {			
			foreach ($idsToUpdate as $tableName => $ids) {
                $idsString = implode(",", $ids);
				if($tableName == 'sendPaymentLink')
				{
					$updateTable = 'online_payment';
				}
				else
				{
					$updateTable = $tableName;
				}
                $updateQuery = "UPDATE $updateTable SET emailStatus = 1 WHERE id IN ($idsString)";
                mysqli_query($conn, $updateQuery);				
            }
			echo "Email sent successfully!";
			
		} 

	} catch (Exception $e) {
		Logger::notice("PHPMailer Error: " . $e->getMessage());
	}
	
	
}else
{
	Logger::notice("No Emails are configured");

}