<?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';
$smtpUsername = 'admin@wiselie.co.id';
$smtpPassword = 'Rnanda123.';
$smtpPort = 465;
$smtpEncryption = PHPMailer::ENCRYPTION_SMTPS;

// Fetch current date and calculate the start and end dates for the week
$current_date = date('Y-m-d');
$start_of_week = date('Y-m-d', strtotime('monday this week'));
$end_of_week = date('Y-m-d', strtotime('sunday this week'));
$start_of_last_week = date('Y-m-d', strtotime('monday last week'));
$end_of_last_week = date('Y-m-d', strtotime('sunday last week'));

// 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 outgoing and total resi counts for the week
$sql_outgoing = "SELECT COUNT(DISTINCT resi) as total_resi, COUNT(resi) as total_outgoing FROM awb WHERE DATE(scan_status) BETWEEN '{$start_of_week}' AND '{$end_of_week}'";
$result_outgoing = $db->query($sql_outgoing);
$total_resi = 0;
$total_outgoing = 0;
if ($row = $result_outgoing->fetch_assoc()) {
    $total_resi = $row['total_resi'];
    $total_outgoing = $row['total_outgoing'];
}

// Fetch Top 10 Outgoing Products by Quantity for Surabaya
$sql_top_outgoing_surabaya = "SELECT part_number, SUM(qty) as total_qty FROM sales WHERE categorie_id = 7 AND DATE(date) BETWEEN '{$start_of_week}' AND '{$end_of_week}' GROUP BY part_number ORDER BY total_qty DESC LIMIT 10";
$result_top_outgoing_surabaya = $db->query($sql_top_outgoing_surabaya);

$top_outgoing_surabaya = [];
while ($row = $result_top_outgoing_surabaya->fetch_assoc()) {
    $part_number = $row['part_number'];
    $quantity_current = $row['total_qty'];

    // 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'];

    // Fetch quantity for the same product from the previous week
    $sql_top_outgoing_last_surabaya = "SELECT SUM(qty) as total_qty_last FROM sales WHERE categorie_id = 7 AND part_number = '{$part_number}' AND DATE(date) BETWEEN '{$start_of_last_week}' AND '{$end_of_last_week}'";
    $result_top_outgoing_last_surabaya = $db->query($sql_top_outgoing_last_surabaya);
    $quantity_last = $result_top_outgoing_last_surabaya->fetch_assoc()['total_qty_last'] ?? 0;

    // Calculate percentage change
    $percentage_change = $quantity_last > 0 ? (($quantity_current - $quantity_last) / $quantity_last) * 100 : 0;

    $top_outgoing_surabaya[] = [
        'part_number' => $part_number,
        'name' => $product_name,
        'quantity_current' => $quantity_current,
        'quantity_last' => $quantity_last,
        'percentage_change' => number_format($percentage_change, 2)
    ];
}

// Fetch Top 10 Outgoing Products by Quantity for Jakarta
$sql_top_outgoing_jakarta = "SELECT part_number, SUM(qty) as total_qty FROM sales_jakarta WHERE categorie_id = 7 AND DATE(date) BETWEEN '{$start_of_week}' AND '{$end_of_week}' GROUP BY part_number ORDER BY total_qty DESC LIMIT 10";
$result_top_outgoing_jakarta = $db->query($sql_top_outgoing_jakarta);

$top_outgoing_jakarta = [];
while ($row = $result_top_outgoing_jakarta->fetch_assoc()) {
    $part_number = $row['part_number'];
    $quantity_current = $row['total_qty'];

    // 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'];

    // Fetch quantity for the same product from the previous week
    $sql_top_outgoing_last_jakarta = "SELECT SUM(qty) as total_qty_last FROM sales_jakarta WHERE categorie_id = 7 AND part_number = '{$part_number}' AND DATE(date) BETWEEN '{$start_of_last_week}' AND '{$end_of_last_week}'";
    $result_top_outgoing_last_jakarta = $db->query($sql_top_outgoing_last_jakarta);
    $quantity_last = $result_top_outgoing_last_jakarta->fetch_assoc()['total_qty_last'] ?? 0;

    // Calculate percentage change
    $percentage_change = $quantity_last > 0 ? (($quantity_current - $quantity_last) / $quantity_last) * 100 : 0;

    $top_outgoing_jakarta[] = [
        'part_number' => $part_number,
        'name' => $product_name,
        'quantity_current' => $quantity_current,
        'quantity_last' => $quantity_last,
        'percentage_change' => number_format($percentage_change, 2)
    ];
}

// Fetch recipient email addresses from the email_list table
$sql_recipients = "SELECT email FROM email_list";
$result_recipients = $db->query($sql_recipients);

$recipients = [];
while ($row = $result_recipients->fetch_assoc()) {
    $recipients[] = $row['email'];
}

$subject = "Weekly Reporting Wiselie";
$message = "
<html>
<head>
    <title>Weekly Report</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f4f4f4;
            margin: 0;
            padding: 0;
        }
        .container {
            max-width: 800px;
            margin: 20px auto;
            padding: 20px;
            background: #ffffff;
            border: 1px solid #ddd;
            border-radius: 8px;
            box-shadow: 0 4px 8px rgba(0, 0, 0, 0.1);
        }
        h1, h2 {
            color: #333;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-bottom: 20px;
        }
        table, th, td {
            border: 1px solid #ddd;
        }
        th, td {
            padding: 15px;
            text-align: center;
        }
        th {
            background-color: #f4f4f4;
        }
        tbody tr:nth-child(even) {
            background-color: #f9f9f9;
        }
        tbody tr:hover {
            background-color: #f1f1f1;
        }
        .footer {
            margin-top: 20px;
            font-size: 14px;
            color: #555;
            text-align: center;
            padding-top: 10px;
            border-top: 1px solid #ddd;
        }
    </style>
</head>
<body>
    <div class='container'>
        <h1>Weekly Report from {$start_of_week} to {$end_of_week}</h1>
        <table>
            <thead>
                <tr>
                    <th>Type</th>
                    <th>Total</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>Total Resi</td>
                    <td>{$total_resi}</td>
                </tr>
                <tr>
                    <td>Total Outgoing</td>
                    <td>{$total_outgoing}</td>
                </tr>
            </tbody>
        </table>
        <h2>Top 10 Outgoing Products by Quantity - Surabaya</h2>
        <table>
            <thead>
                <tr>
                    <th>Part Number</th>
                    <th>Name</th>
                    <th>Quantity (This Week)</th>
                    <th>Quantity (Last Week)</th>
                    <th>% Change</th>
                </tr>
            </thead>
            <tbody>
";

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

$message .= "
            </tbody>
        </table>
        <h2>Top 10 Outgoing Products by Quantity - Jakarta</h2>
        <table>
            <thead>
                <tr>
                    <th>Part Number</th>
                    <th>Name</th>
                    <th>Quantity (This Week)</th>
                    <th>Quantity (Last Week)</th>
                    <th>% Change</th>
                </tr>
            </thead>
            <tbody>
";

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

$message .= "
            </tbody>
        </table>
        <div class='footer'>
            <p>Wiselie.co.id Integrated Warehouse Management System</p>
        </div>
    </div>
</body>
</html>
";

// Mengirim email ke semua penerima
$mail = new PHPMailer(true);
try {
    $mail->isSMTP();
    $mail->Host = $smtpHost;
    $mail->SMTPAuth = true;
    $mail->Username = $smtpUsername;
    $mail->Password = $smtpPassword;
    $mail->SMTPSecure = $smtpEncryption;
    $mail->Port = $smtpPort;

    // Set pengirim dan penerima
    $mail->setFrom($smtpUsername, 'Wiselie Weekly Report');
    foreach ($recipients as $recipient) {
        $mail->addAddress($recipient);
    }

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

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

// Tutup koneksi database
$db->close();
?>
