<?php
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

// Include Composer's autoloader
require 'vendor/autoload.php';

// Database connection details
$host = '34.128.105.90';
$dbname = 'freshfactory_db';
$username = 'ffroot';
$password = 'klXCjMy0cJ57bRQpkarWF7gnmFz1gTPr';

try {
    // Create a PDO instance
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Queries for different time periods
    $queries = [
        'today_sj' => "
			SELECT
				COUNT(s.id) AS `Total SJ`,
				SUM(CASE WHEN s.status = 'Next' THEN 1 ELSE 0 END) AS '1.Akan Datang',
				SUM(CASE WHEN s.status = 'Received' THEN 1 ELSE 0 END) AS '2.Sudah Sampai',
				SUM(CASE WHEN s.status = 'Proceed' THEN 1 ELSE 0 END) AS '3.Diproses',
				SUM(CASE WHEN s.status = 'Finish' THEN 1 ELSE 0 END) AS '4.Diterima',
				SUM(CASE WHEN s.status = 'Stored' THEN 1 ELSE 0 END) AS '5.Tersimpan',
				SUM(CASE WHEN s.status = 'Canceled' THEN 1 ELSE 0 END) AS '6.Batal',
				SUM(CASE WHEN s.status = 'Shuttle Request' THEN 1 ELSE 0 END) AS '7.Shuttle Diminta',
				SUM(CASE WHEN s.status = 'Shuttle Received' THEN 1 ELSE 0 END) AS '8.Shuttle Diterima',
				SUM(CASE WHEN s.status = 'Shuttle Cancel' THEN 1 ELSE 0 END) AS '9.Shuttle Batal'
			FROM reservations s
			WHERE DATE(s.created_at) = DATE(NOW() - INTERVAL 1 DAY)
			AND s.nota IS NOT NULL
		",
		'today_inv' => "
			SELECT
				COUNT(s.id) AS `Total INV`,
				SUM(CASE WHEN s.status = 'unprocess' THEN 1 ELSE 0 END) AS '1.Belum Diproses',
				SUM(CASE WHEN s.status = 'process' THEN 1 ELSE 0 END) AS '2.Diproses',
				SUM(CASE WHEN s.status = 'packed' THEN 1 ELSE 0 END) AS '3.Sedang Dikemas',
				SUM(CASE WHEN s.status = 'waiting' THEN 1 ELSE 0 END) AS '4.Menunggu Diambil',
				SUM(CASE WHEN s.status = 'pickup' THEN 1 ELSE 0 END) AS '5.Sudah Diambil',
				SUM(CASE WHEN s.status = 'delivered' THEN 1 ELSE 0 END) AS '6.Ekspedisi Terkirim',
				SUM(CASE WHEN s.status = 'delayed' THEN 1 ELSE 0 END) AS '7.Ekspedisi Tertunda',
				SUM(CASE WHEN s.status = 'delivery failed' THEN 1 ELSE 0 END) AS '8.Ekspedisi Gagal',
				SUM(CASE WHEN s.status = 'cancel request' THEN 1 ELSE 0 END) AS '9.Pembatalan Diminta',
				SUM(CASE WHEN s.status = 'cancel' THEN 1 ELSE 0 END) AS '10.Pembatalan Selesai',
				SUM(CASE WHEN s.status = 'Reprint AWB' THEN 1 ELSE 0 END) AS '11.Cetak Resi',
				SUM(CASE WHEN s.status = 'POD Waiting' THEN 1 ELSE 0 END) AS '12.Proof of Delivery Ditunggu',
				SUM(CASE WHEN s.status = 'POD Accepted' THEN 1 ELSE 0 END) AS '13.Proof of Delivery Diterima'
			FROM orders s
			WHERE DATE(s.created_at) = DATE(NOW() - INTERVAL 1 DAY)
		",
        'thisMonth_sj' => "
            SELECT
				COUNT(s.id) AS `Total SJ`,
				SUM(CASE WHEN s.status = 'Next' THEN 1 ELSE 0 END) AS '1.Akan Datang',
				SUM(CASE WHEN s.status = 'Received' THEN 1 ELSE 0 END) AS '2.Sudah Sampai',
				SUM(CASE WHEN s.status = 'Proceed' THEN 1 ELSE 0 END) AS '3.Diproses',
				SUM(CASE WHEN s.status = 'Finish' THEN 1 ELSE 0 END) AS '4.Diterima',
				SUM(CASE WHEN s.status = 'Stored' THEN 1 ELSE 0 END) AS '5.Tersimpan',
				SUM(CASE WHEN s.status = 'Canceled' THEN 1 ELSE 0 END) AS '6.Batal',
				SUM(CASE WHEN s.status = 'Shuttle Request' THEN 1 ELSE 0 END) AS '7.Shuttle Diminta',
				SUM(CASE WHEN s.status = 'Shuttle Received' THEN 1 ELSE 0 END) AS '8.Shuttle Diterima',
				SUM(CASE WHEN s.status = 'Shuttle Cancel' THEN 1 ELSE 0 END) AS '9.Shuttle Batal'
			FROM reservations s
			WHERE MONTH(s.created_at) = MONTH(CURDATE())
            AND YEAR(s.created_at) = YEAR(CURDATE())
			AND s.nota IS NOT NULL
        ",
		'thisMonth_inv' => "
            SELECT
				COUNT(s.id) AS `Total INV`,
				SUM(CASE WHEN s.status = 'unprocess' THEN 1 ELSE 0 END) AS '1.Belum Diproses',
				SUM(CASE WHEN s.status = 'process' THEN 1 ELSE 0 END) AS '2.Diproses',
				SUM(CASE WHEN s.status = 'packed' THEN 1 ELSE 0 END) AS '3.Sedang Dikemas',
				SUM(CASE WHEN s.status = 'waiting' THEN 1 ELSE 0 END) AS '4.Menunggu Diambil',
				SUM(CASE WHEN s.status = 'pickup' THEN 1 ELSE 0 END) AS '5.Sudah Diambil',
				SUM(CASE WHEN s.status = 'delivered' THEN 1 ELSE 0 END) AS '6.Ekspedisi Terkirim',
				SUM(CASE WHEN s.status = 'delayed' THEN 1 ELSE 0 END) AS '7.Ekspedisi Tertunda',
				SUM(CASE WHEN s.status = 'delivery failed' THEN 1 ELSE 0 END) AS '8.Ekspedisi Gagal',
				SUM(CASE WHEN s.status = 'cancel request' THEN 1 ELSE 0 END) AS '9.Pembatalan Diminta',
				SUM(CASE WHEN s.status = 'cancel' THEN 1 ELSE 0 END) AS '10.Pembatalan Selesai',
				SUM(CASE WHEN s.status = 'Reprint AWB' THEN 1 ELSE 0 END) AS '11.Cetak Resi',
				SUM(CASE WHEN s.status = 'POD Waiting' THEN 1 ELSE 0 END) AS '12.Proof of Delivery Ditunggu',
				SUM(CASE WHEN s.status = 'POD Accepted' THEN 1 ELSE 0 END) AS '13.Proof of Delivery Diterima'
			FROM orders s
			WHERE MONTH(s.created_at) = MONTH(CURDATE())
            AND YEAR(s.created_at) = YEAR(CURDATE())
        ",
        'allTime_sj' => "
            SELECT
				COUNT(s.id) AS `Total SJ`,
				SUM(CASE WHEN s.status = 'Next' THEN 1 ELSE 0 END) AS '1.Akan Datang',
				SUM(CASE WHEN s.status = 'Received' THEN 1 ELSE 0 END) AS '2.Sudah Sampai',
				SUM(CASE WHEN s.status = 'Proceed' THEN 1 ELSE 0 END) AS '3.Diproses',
				SUM(CASE WHEN s.status = 'Finish' THEN 1 ELSE 0 END) AS '4.Diterima',
				SUM(CASE WHEN s.status = 'Stored' THEN 1 ELSE 0 END) AS '5.Tersimpan',
				SUM(CASE WHEN s.status = 'Canceled' THEN 1 ELSE 0 END) AS '6.Batal',
				SUM(CASE WHEN s.status = 'Shuttle Request' THEN 1 ELSE 0 END) AS '7.Shuttle Diminta',
				SUM(CASE WHEN s.status = 'Shuttle Received' THEN 1 ELSE 0 END) AS '8.Shuttle Diterima',
				SUM(CASE WHEN s.status = 'Shuttle Cancel' THEN 1 ELSE 0 END) AS '9.Shuttle Batal'
			FROM reservations s
			WHERE s.nota IS NOT NULL
        ",
		'allTime_inv' => "
            SELECT
				COUNT(s.id) AS `Total INV`,
				SUM(CASE WHEN s.status = 'unprocess' THEN 1 ELSE 0 END) AS '1.Belum Diproses',
				SUM(CASE WHEN s.status = 'process' THEN 1 ELSE 0 END) AS '2.Diproses',
				SUM(CASE WHEN s.status = 'packed' THEN 1 ELSE 0 END) AS '3.Sedang Dikemas',
				SUM(CASE WHEN s.status = 'waiting' THEN 1 ELSE 0 END) AS '4.Menunggu Diambil',
				SUM(CASE WHEN s.status = 'pickup' THEN 1 ELSE 0 END) AS '5.Sudah Diambil',
				SUM(CASE WHEN s.status = 'delivered' THEN 1 ELSE 0 END) AS '6.Ekspedisi Terkirim',
				SUM(CASE WHEN s.status = 'delayed' THEN 1 ELSE 0 END) AS '7.Ekspedisi Tertunda',
				SUM(CASE WHEN s.status = 'delivery failed' THEN 1 ELSE 0 END) AS '8.Ekspedisi Gagal',
				SUM(CASE WHEN s.status = 'cancel request' THEN 1 ELSE 0 END) AS '9.Pembatalan Diminta',
				SUM(CASE WHEN s.status = 'cancel' THEN 1 ELSE 0 END) AS '10.Pembatalan Selesai',
				SUM(CASE WHEN s.status = 'Reprint AWB' THEN 1 ELSE 0 END) AS '11.Cetak Resi',
				SUM(CASE WHEN s.status = 'POD Waiting' THEN 1 ELSE 0 END) AS '12.Proof of Delivery Ditunggu',
				SUM(CASE WHEN s.status = 'POD Accepted' THEN 1 ELSE 0 END) AS '13.Proof of Delivery Diterima'
			FROM orders s
        "
    ];

    // Fetch the results using PDO
    $results = [];
    foreach ($queries as $key => $query) {
        $stmt = $pdo->query($query);
        $results[$key] = $stmt->fetch(PDO::FETCH_ASSOC);
    }

    // Get the current date in the desired format
    $date = new DateTime('now', new DateTimeZone('Asia/Jakarta'));
    $currentDate = $date->format("d-m-Y");
    $currentTime = $date->format("H:i:s");

// Build the email body
// Build the email body
    $emailBody = "
<!DOCTYPE html>
<html lang='en'>
<head>
    <meta charset='UTF-8'>
    <meta name='viewport' content='width=device-width, initial-scale=1.0'>
    <style>
        body {
            font-family: Arial, sans-serif;
            line-height: 1.25;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            table-layout: auto; /* Allow columns to resize based on content */
        }
        th, td {
            border: 1px solid #ddd;
            padding: 5px;
            word-wrap: break-word; /* Break words if they are too long */
        }
        th {
            background-color: #f2f2f2;
            text-align: left;
        }
        .section-header {
            font-size: 1.2em;
            font-weight: bold;
            margin-top: 10px; /* Adjust this value to reduce the space */
            margin-bottom: 5px; /* Optional: reduce bottom margin if needed */
        }
        .summary {
            margin-bottom: 20px;
        }
        .highlight {
            font-size: 1em; /* Apply 1.2em font size to highlight */
            font-weight: bold;
        }
        .highlight_client {
            font-size: 1em;
            font-weight: bold;
            color: #d9534f;
            margin-top: 3px; /* Removes top margin to tighten the space */
        }
    </style>
</head>
<body>
    <p>Data generated: Date: " . $currentDate . " - Time: " . $currentTime . "</p>";

    // Function to build table rows dynamically
    function buildTable($data) {
        $html = "";
        foreach ($data as $key => $value) {
            // Replace null or empty values with 0
            if (empty($value)) {
                $value = 0;
            }

            // Highlight if it's the "Akan Datang" or "Belum Diproses" key
			//$highlight = ($key === "Akan Datang" || $key === "Belum Diproses") ? 'class="highlight"' : '';
			$html .= "<tr><td>" . htmlspecialchars($key) . "</td><td>" . htmlspecialchars($value) . "</td></tr>";

        }
        return $html;
    }

    // Today Data SJ
    $emailBody .= "<div class='summary'>
        <div class='section-header'>SJ Hari Ini:</div>
        <table>
            <tr><th>Detail</th><th>Jumlah</th></tr>";
    $emailBody .= buildTable($results['today_sj']);
    $emailBody .= "</table><br>";
	
	    // Today Data INV
    $emailBody .= "<div class='summary'>
        <div class='section-header'>INV Hari Ini:</div>
        <table>
            <tr><th>Detail</th><th>Jumlah</th></tr>";
    $emailBody .= buildTable($results['today_inv']);
    $emailBody .= "</table><br>";

    // This Month Data SJ
    $emailBody .= "<div class='summary'>
        <div class='section-header'>SJ 1 Bulan Ini:</div>
        <table>
            <tr><th>Detail</th><th>Jumlah</th></tr>";
    $emailBody .= buildTable($results['thisMonth_sj']);
    $emailBody .= "</table><br>";
	
	// This Month Data INV
    $emailBody .= "<div class='summary'>
        <div class='section-header'>INV 1 Bulan Ini:</div>
        <table>
            <tr><th>Detail</th><th>Jumlah</th></tr>";
    $emailBody .= buildTable($results['thisMonth_inv']);
    $emailBody .= "</table><br>";
	

    // All Time Data SJ
    $emailBody .= "<div class='summary'>
        <div class='section-header'>SJ As of Hari Ini:</div>
        <table>
            <tr><th>Detail</th><th>Jumlah</th></tr>";
    $emailBody .= buildTable($results['allTime_sj']);
    $emailBody .= "</table><br>";
	
    // All Time Data INV
    $emailBody .= "<div class='summary'>
        <div class='section-header'>INV As of Hari Ini:</div>
        <table>
            <tr><th>Detail</th><th>Jumlah</th></tr>";
    $emailBody .= buildTable($results['allTime_inv']);
    $emailBody .= "</table>";

    $emailBody .= "</body></html>";




    // You can now use this `$emailBody` for sending the email via PHPMailer.
//    echo $emailBody;
    // PHPMailer setup
    $mail = new PHPMailer(false);

    try {
        // Server settings
        $mail->isSMTP();
        $mail->Host = 'smtp.larksuite.com';
        $mail->SMTPAuth = true;
        $mail->Username = 'no-reply@freshfactory.id';
        $mail->Password = 'T8L23W3yndDVc8iJ';
        $mail->SMTPSecure = 'tls';
        $mail->Port = 587;

        // Recipients
        $mail->setFrom('no-reply@freshfactory.id', 'Fresh Factory');
        //$mail->addAddress('zulfikar@freshfactory.id', 'Zulfikar');
        //$mail->addAddress('larry.ridwan@freshfactory.id', 'Larry Ridwan');
        //$mail->addAddress('wisnu.wardhana@freshfactory.id', 'Wisnu Wardana');
         //$mail->addAddress('andre.hermawan@freshfactory.id', 'Andre Hermawan');
         //$mail->addAddress('widi.nugroho@freshfactory.id', 'Widi');
         //$mail->addAddress('aditya.wirawan@freshfactory.id', 'Aditya');
         //$mail->addAddress('rangga.adiyahya@freshfactory.id', 'Rangga');
         //$mail->addAddress('livia.gunawan@freshfactory.id', 'Livia');
         //$mail->addAddress('yennita.sinaga@freshfactory.id', 'Yennita');
         $mail->addAddress('yennita.sinaga@freshfactory.id', 'Yennita');
           


        // Content
        $mail->isHTML(true);
        $mail->Subject = 'DATA - SJ dan INV Hari ini';
        $mail->Body = $emailBody;

        $mail->send();
        echo "Email sent successfully!";
    } catch (Exception $e) {
        echo "Email could not be sent. Mailer Error: {$mail->ErrorInfo}";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}