<?php
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

// Autoload PHPMailer via Composer
require '/home/wiselie/public_html/vendor/autoload.php';

// Konfigurasi SMTP untuk email cPanel
$smtpHost = 'mail.wiselie.co.id'; // Host SMTP untuk cPanel
$smtpUsername = 'admin@wiselie.co.id'; // Username SMTP (email cPanel)
$smtpPassword = 'Rnanda123.'; // Password email cPanel
$smtpPort = 465; // Port SMTP untuk SSL
$smtpEncryption = PHPMailer::ENCRYPTION_SMTPS; // Enkripsi SSL

// Fetch current date
$current_date = date('Y-m-d');

// Konfigurasi database
$host = 'localhost';
$user = 'wiselie_nanda';
$password = 'Clemira123';
$database = 'wiselie_inventory';

// Koneksi ke database
$db = new mysqli($host, $user, $password, $database);
if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}

// Fetch total scanned in and scan status counts for the current date
$sql_scan_in = "SELECT COUNT(DISTINCT resi) as total_scan_in FROM awb WHERE DATE(scan_in) = '{$current_date}'";
$result_scan_in = $db->query($sql_scan_in);
$total_scan_in = 0;
if ($row = $result_scan_in->fetch_assoc()) {
    $total_scan_in = $row['total_scan_in'];
}

$sql_scan_status = "SELECT COUNT(DISTINCT resi) as total_scan_status FROM awb WHERE DATE(scan_status) = '{$current_date}'";
$result_scan_status = $db->query($sql_scan_status);
$total_scan_status = 0;
if ($row = $result_scan_status->fetch_assoc()) {
    $total_scan_status = $row['total_scan_status'];
}

// Calculate progress percentage and format to 2 decimal places
$progress_percentage = ($total_scan_in > 0) ? ($total_scan_status / $total_scan_in) * 100 : 0;
$progress_percentage_formatted = number_format($progress_percentage, 2);

// Fetch Top 5 Outgoing Products by Quantity and Volume for the current date (Surabaya)
$sql_top_outgoing_surabaya = "
    SELECT s.part_number, SUM(s.qty) as total_qty, p.volume 
    FROM sales s
    JOIN products p ON s.part_number = p.part_number
    WHERE s.categorie_id = 7 AND DATE(s.date) = '{$current_date}' AND s.location = 'surabaya'
    GROUP BY s.part_number
    ORDER BY total_qty DESC
    LIMIT 5
";
$result_top_outgoing_surabaya = $db->query($sql_top_outgoing_surabaya);

$top_outgoing_surabaya = [];
$total_quantity_terjual_surabaya = 0;
$total_volume_terjual_surabaya = 0;

while ($row = $result_top_outgoing_surabaya->fetch_assoc()) {
    $part_number = $row['part_number'];
    $quantity = $row['total_qty'];
    $volume_ml = $row['volume'];
    $volume_liter = $volume_ml / 1000; // Convert ml to liter
    $total_volume = $quantity * $volume_liter;

    $total_quantity_terjual_surabaya += $quantity;
    $total_volume_terjual_surabaya += $total_volume;

    // Fetch product name
    $sql_product_name = "SELECT name FROM products WHERE part_number = '{$part_number}' LIMIT 1";
    $result_product_name = $db->query($sql_product_name);
    $product_name = $result_product_name->fetch_assoc()['name'];

    $top_outgoing_surabaya[] = [
        'part_number' => $part_number,
        'name' => $product_name,
        'quantity' => $quantity,
        'total_volume' => number_format($total_volume, 2) // Format total volume to 2 decimal places
    ];
}

// Fetch Top 5 Outgoing Products by Quantity and Volume for the current date (Jakarta)
$sql_top_outgoing_jakarta = "
    SELECT s.part_number, SUM(s.qty) as total_qty, p.volume 
    FROM sales s
    JOIN products p ON s.part_number = p.part_number
    WHERE s.categorie_id = 7 AND DATE(s.date) = '{$current_date}' AND s.location = 'jakarta'
    GROUP BY s.part_number
    ORDER BY total_qty DESC
    LIMIT 5
";
$result_top_outgoing_jakarta = $db->query($sql_top_outgoing_jakarta);

$top_outgoing_jakarta = [];
$total_quantity_terjual_jakarta = 0;
$total_volume_terjual_jakarta = 0;

while ($row = $result_top_outgoing_jakarta->fetch_assoc()) {
    $part_number = $row['part_number'];
    $quantity = $row['total_qty'];
    $volume_ml = $row['volume'];
    $volume_liter = $volume_ml / 1000; // Convert ml to liter
    $total_volume = $quantity * $volume_liter;

    $total_quantity_terjual_jakarta += $quantity;
    $total_volume_terjual_jakarta += $total_volume;

    // Fetch product name
    $sql_product_name = "SELECT name FROM products WHERE part_number = '{$part_number}' LIMIT 1";
    $result_product_name = $db->query($sql_product_name);
    $product_name = $result_product_name->fetch_assoc()['name'];

    $top_outgoing_jakarta[] = [
        'part_number' => $part_number,
        'name' => $product_name,
        'quantity' => $quantity,
        'total_volume' => number_format($total_volume, 2) // Format total volume to 2 decimal places
    ];
}

// Set single email recipient
$recipient = 'r.nanda.industrial@gmail.com';

$subject = "Daily Report for {$current_date}";
$message = "
<html>
<head>
    <title>Daily Scan Report</title>
    <style>
        body {
            font-family: Arial, sans-serif;
        }
        .container {
            max-width: 800px;
            margin: 0 auto;
            padding: 20px;
            background: #f9f9f9;
            border: 1px solid #ddd;
            border-radius: 8px;
        }
        h1 {
            color: #333;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-bottom: 20px;
        }
        table, th, td {
            border: 1px solid #ddd;
        }
        th, td {
            padding: 12px;
            text-align: center;
        }
        th {
            background-color: #f4f4f4;
        }
        .progress-bar {
            height: 30px;
            background: #4caf50;
            width: {$progress_percentage_formatted}%;
            color: white;
            text-align: center;
            line-height: 30px;
            border-radius: 15px;
        }
    </style>
</head>
<body>
    <div class='container'>
        <h1>Scan Results for {$current_date}</h1>
        <table>
            <thead>
                <tr>
                    <th>Type</th>
                    <th>Total AWB</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>Scanned In</td>
                    <td>{$total_scan_in}</td>
                </tr>
                <tr>
                    <td>Scanned Out</td>
                    <td>{$total_scan_status}</td>
                </tr>
                <tr>
                    <td>Total Quantity Terjual (Surabaya)</td>
                    <td>{$total_quantity_terjual_surabaya}</td>
                </tr>
                <tr>
                    <td>Total Volume Terjual (L) (Surabaya)</td>
                    <td>{$total_volume_terjual_surabaya}</td>
                </tr>
                <tr>
                    <td>Total Quantity Terjual (Jakarta)</td>
                    <td>{$total_quantity_terjual_jakarta}</td>
                </tr>
                <tr>
                    <td>Total Volume Terjual (L) (Jakarta)</td>
                    <td>{$total_volume_terjual_jakarta}</td>
                </tr>
            </tbody>
        </table>
        <div class='progress-bar'>{$progress_percentage_formatted}%</div>
        <h2>Top 5 Outgoing Produk berdasarkan Quantity dan Volume (Surabaya)</h2>
        <table>
            <thead>
                <tr>
                    <th>Part Number</th>
                    <th>Name</th>
                    <th>Quantity</th>
                    <th>Total Volume (L)</th>
                </tr>
            </thead>
            <tbody>
";

foreach ($top_outgoing_surabaya as $outgoing) {
    $message .= "
                <tr>
                    <td>{$outgoing['part_number']}</td>
                    <td>{$outgoing['name']}</td>
                    <td>{$outgoing['quantity']}</td>
                    <td>{$outgoing['total_volume']}</td>
                </tr>
    ";
}

$message .= "
            </tbody>
        </table>
        <h2>Top 5 Outgoing Produk berdasarkan Quantity dan Volume (Jakarta)</h2>
        <table>
            <thead>
                <tr>
                    <th>Part Number</th>
                    <th>Name</th>
                    <th>Quantity</th>
                    <th>Total Volume (L)</th>
                </tr>
            </thead>
            <tbody>
";

foreach ($top_outgoing_jakarta as $outgoing) {
    $message .= "
                <tr>
                    <td>{$outgoing['part_number']}</td>
                    <td>{$outgoing['name']}</td>
                    <td>{$outgoing['quantity']}</td>
                    <td>{$outgoing['total_volume']}</td>
                </tr>
    ";
}

$message .= "
            </tbody>
        </table>
    </div>
</body>
</html>
";

// Send the email using PHPMailer
$mail = new PHPMailer(true);
try {
    // SMTP settings
    $mail->isSMTP();
    $mail->Host = $smtpHost;
    $mail->SMTPAuth = true;
    $mail->Username = $smtpUsername;
    $mail->Password = $smtpPassword;
    $mail->SMTPSecure = $smtpEncryption;
    $mail->Port = $smtpPort;

    // Recipient
    $mail->setFrom($smtpUsername, 'Wiselie Admin');
    $mail->addAddress($recipient); // Single recipient

    // Content
    $mail->isHTML(true);
    $mail->Subject = $subject;
    $mail->Body    = $message;

    $mail->send();
    echo 'Email has been sent';
} catch (Exception $e) {
    echo "Email could not be sent. Mailer Error: {$mail->ErrorInfo}";
}

// Close database connection
$db->close();
?>
