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

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

$today = date('Y-m-d');
$day = date('w'); // Sunday = 0, Monday = 1
$dayOfMonth = date('j');
$month = date('n');
$year = date('Y');

// Helper: generate HTML content
function generateReportHtml($from, $to, $title, $conn) {
    $sql = "SELECT 
                b.name AS branch,
                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($sql);
    $grouped = [];

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

    $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 ($grouped 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>Customer</th><th>Order Date</th><th>Execution Date</th><th>Total m³</th>
                      </tr></thead><tbody>";
            $total = 0;
            foreach ($orders as $row) {
                $html .= "<tr>
                            <td>{$row['customer_name']}</td>
                            <td align='center'>{$row['order_date']}</td>
                            <td align='center'>{$row['execution_date']}</td>
                            <td align='right'>{$row['order_quantity_m3']}</td>
                          </tr>";
                $total += $row['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>";
        }
    }
    return $html;
}

// Report Types to Send
$reports = [];

// Always send today's report
$reports[] = [
    'title' => "Today's Report",
    'from' => $today,
    'to' => $today
];

// Every Monday, send last week
if ($day == 1) {
    $reports[] = [
        'title' => "Last Week's Report",
        'from' => date('Y-m-d', strtotime('last monday -7 days')),
        'to' => date('Y-m-d', strtotime('last sunday'))
    ];
}

// 1st of each month
if ($dayOfMonth == 1) {
    $reports[] = [
        'title' => "Last Month's Report",
        'from' => date("Y-m-01", strtotime("first day of last month")),
        'to' => date("Y-m-t", strtotime("last day of last month"))
    ];
}

// 1st Jan - Yearly Report
if ($dayOfMonth == 1 && $month == 1) {
    $reports[] = [
        'title' => "Last Year's Report",
        'from' => date("Y-01-01", strtotime("last year")),
        'to' => date("Y-12-31", strtotime("last year"))
    ];
}

// --- Send All Reports ---
foreach ($reports as $report) {
    $htmlContent = generateReportHtml($report['from'], $report['to'], $report['title'], $conn);

    $payload = [
        "sender" => ["name" => "DRMC Reports", "email" => "sysbeeinfotech@gmail.com"],
        "to" => [["email" => $recipient]],
        "cc" => [["email" => $cc]],
        "subject" => "DRMC Marketing Report – {$report['title']}",
        "htmlContent" => $htmlContent
    ];

    // $ch = curl_init("https://api.brevo.com/v3/smtp/email");
    // curl_setopt($ch, CURLOPT_HTTPHEADER, [
    //     "Content-Type: application/json",
    //     "api-key: $apiKey"
    // ]);
    // 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: {$report['title']}<br>";
    // } else {
    //     echo "❌ Error sending {$report['title']}: $response<br>";
    // }
    
    echo  $htmlContent;
}
?>
