<?php
include('../config/config.php');
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // SHOW ALL MYSQLI ERRORS

// --- Brevo API Setup ---
$brevo_api_key = "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";
$recipient_email = "sdhnatarajan@gmail.com";

// --- Detect Report Type ---
$today = date('Y-m-d');
$dayOfWeek = date('w'); // Sunday = 0, Monday = 1, ...
$dayOfMonth = date('j');

if ($dayOfMonth == 1) {
    $reportType = "last_month";
    $from = date("Y-m-01", strtotime("first day of last month"));
    $to = date("Y-m-t", strtotime("last day of last month"));
    $title = "Last Month's Report";
} elseif ($dayOfWeek == 1) {
    $reportType = "last_week";
    $from = date("Y-m-d", strtotime("last monday -7 days"));
    $to = date("Y-m-d", strtotime("last sunday"));
    $title = "Last Week's Report";
} else {
    $reportType = "today";
    $from = $to = $today;
    $title = "Today's Report";
}



// --- Fetch Data Grouped by Branch and Marketing Person ---
$sql = "SELECT 
    b.name,
    mp.name AS marketing_person,
    c.customer_name,
    DATE_FORMAT(o.order_date, '%d-%m-%Y') AS order_date,
    DATE_FORMAT(o.execution_date, '%d-%m-%Y') AS execution_date,
    SUM(o.order_quantity_m3) AS total_m3
FROM orders o
JOIN branches b ON o.branch_id = b.id
JOIN customers c ON o.customer_id = c.id
JOIN marketing_persons mp ON o.marketing_person_id = mp.id
WHERE o.status='active' 
  AND DATE(o.execution_date) BETWEEN '$from' AND '$to'
GROUP BY b.id, mp.id, c.id, o.order_date
ORDER BY b.name, mp.name, c.customer_name";


$result = $conn->query($sql);
echo "<pre>$sql</pre>";

$grouped = [];
while ($row = $result->fetch_assoc()) {
    $branch = $row['branch_name'];
    $person = $row['marketing_person'];
    $grouped[$branch][$person][] = $row;
}

// --- Build Email Content ---
$html = "<h2>$title</h2>";
$html .= "<p>From: <strong>" . date('d-m-Y', strtotime($from)) . "</strong> To: <strong>" . date('d-m-Y', strtotime($to)) . "</strong></p>";

foreach ($grouped as $branch => $marketingPersons) {
    $html .= "<h3>Branch: $branch</h3>";
    foreach ($marketingPersons as $mp => $orders) {
        $html .= "<h5>Marketing Person: $mp</h5>";
        $html .= "<table border='1' cellpadding='6' cellspacing='0' style='border-collapse: collapse; width:100%; margin-bottom:20px'>";
$html .= "<thead><tr><th>Customer</th><th>Order Date / Execution Date</th><th>Total Order (m³)</th></tr></thead><tbody>";
$total_m3 = 0;
foreach ($orders as $row) {
    $html .= "<tr>
                <td>{$row['customer_name']}</td>
                <td>{$row['order_date']} / {$row['execution_date']}</td>
                <td>{$row['total_m3']}</td>
              </tr>";
    $total_m3 += $row['total_m3'];
}
$html .= "<tr style='font-weight:bold'><td colspan='2'>Total for $mp</td><td>$total_m3 m³</td></tr>";
$html .= "</tbody></table>";

    }
}

// --- Send Email via Brevo API ---
$payload = [
    "sender" => ["name" => "DRMC Reports", "email" => "noreply@dharani.com"],
    "to" => [["email" => $recipient_email]],
    "subject" => "DRMC Marketing Report – $title",
    "htmlContent" => $html
];

$ch = curl_init("https://api.brevo.com/v3/smtp/email");
curl_setopt($ch, CURLOPT_HTTPHEADER, [
    "Content-Type: application/json",
    "api-key: $brevo_api_key"
]);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($payload));

$response = curl_exec($ch);
$httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);

if ($httpCode == 201) {
    echo "Email sent successfully.";
} else {
    echo "Failed to send email: $response";
}
?>
