<?php
include('../../config/config.php');

// Brevo API
$apiKey = "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";
$recipient = "sdhnatarajan@gmail.com";
$cc = "krupachinthan@gmail.com";

// Detect report type
$today = date('Y-m-d');
$dayOfWeek = date('w'); // Sunday = 0, Monday = 1
$dayOfMonth = date('j');
$month = date('n');

if ($dayOfMonth == 1 && $month == 1) {
    $reportType = 'yearly';
    $from = date('Y-01-01', strtotime('-1 year'));
    $to = date('Y-12-31', strtotime('-1 year'));
    $title = "Last Year Report";
} elseif ($dayOfMonth == 1) {
    $reportType = 'monthly';
    $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 Report";
} elseif ($dayOfWeek == 1) {
    $reportType = 'weekly';
    $from = date('Y-m-d', strtotime('last monday -7 days'));
    $to = date('Y-m-d', strtotime('last sunday'));
    $title = "Last Week Report";
} else {
    $reportType = 'daily';
    $from = $to = $today;
    $title = "Daily Report";
}

// Fetch data from DB
$query = "SELECT 
    b.name AS branch_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,
    o.order_quantity_m3
FROM orders o
JOIN branches b ON o.branch_id = b.id
JOIN marketing_persons mp ON o.marketing_person_id = mp.id
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'active'
  AND DATE(o.execution_date) BETWEEN '$from' AND '$to'
ORDER BY b.name, mp.name, c.customer_name";

$result = $conn->query($query);

// Group the data
$data = [];
while ($row = $result->fetch_assoc()) {
    $branch = $row['branch_name'];
    $mp = $row['marketing_person'];
    $data[$branch][$mp][] = $row;
}

// Build HTML content
$html = "<h2 style='color:#444;'>DRMC Marketing Report ($title)</h2>";
$html .= "<p><strong>Date Range:</strong> " . date('d-m-Y', strtotime($from)) . " to " . date('d-m-Y', strtotime($to)) . "</p>";

foreach ($data as $branch => $persons) {
    $html .= "<h3 style='margin-top:30px;color:#333;'>Branch: <u>$branch</u></h3>";

    foreach ($persons as $mp => $orders) {
        $html .= "<h4 style='margin:10px 0;color:#555;'>Marketing Person: $mp</h4>";
        $html .= "<table border='1' cellpadding='6' cellspacing='0' width='100%' style='border-collapse:collapse; font-family:Arial, sans-serif; font-size:14px;'>";
        $html .= "<thead style='background-color:#f1f1f1;'><tr>
                    <th align='left'>Customer</th>
                    <th align='center'>Order Date</th>
                    <th align='center'>Execution Date</th>
                    <th align='right'>Total m³</th>
                  </tr></thead><tbody>";

        $total = 0;
        foreach ($orders as $o) {
            $html .= "<tr>
                        <td>{$o['customer_name']}</td>
                        <td align='center'>{$o['order_date']}</td>
                        <td align='center'>{$o['execution_date']}</td>
                        <td align='right'>{$o['order_quantity_m3']}</td>
                      </tr>";
            $total += $o['order_quantity_m3'];
        }

        $html .= "<tr style='font-weight:bold;background-color:#eaf7ea;'>
                    <td colspan='3' align='right'>Total for $mp</td>
                    <td align='right'>{$total} m³</td>
                  </tr>";
        $html .= "</tbody></table><br>";
    }
}

echo $html;

// // Send via Brevo API
// $payload = [
//   "sender" => ["name" => "DRMC Reports", "email" => "sysbeeinfotech@gmail.com"],
//   "to" => [["email" => $recipient]],
//   "cc" => [["email" => $cc]],
//   "subject" => "DRMC Marketing Report - $title",
//   "htmlContent" => $html
// ];

// $ch = curl_init("https://api.brevo.com/v3/smtp/email");
// curl_setopt($ch, CURLOPT_HTTPHEADER, [
//     "api-key: $apiKey",
//     "Content-Type: application/json"
// ]);
// 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);

// // Debug
// if ($httpCode == 201) {
//     echo "✅ Email sent via Brevo API";
// } else {
//     echo "❌ Brevo API Error: $response";
// }
?>
