<?php
// Include PHPMailer classes (manual include - no Composer)
require '../PHPMailer-master/src/PHPMailer.php';
require '../PHPMailer-master/src/SMTP.php';
require '../PHPMailer-master/src/Exception.php';

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

require 'html2pdf/vendor/autoload.php';
use Spipu\Html2Pdf\Html2Pdf;

$reportType = $_GET['type'] ?? 'daily'; // daily, weekly, monthly
$basePath = __DIR__ . '/report/';

switch ($reportType) {
    case 'weekly':
        $from = date('Y-m-d', strtotime('last monday -7 days'));
        $to = date('Y-m-d', strtotime('last sunday'));
        $fileName = "marketing_report_weekly_" . date('d-m-Y', strtotime($from)) . "_to_" . date('d-m-Y', strtotime($to)) . ".pdf";
        break;
    case 'monthly':
        $from = date('Y-m-01', strtotime('first day of last month'));
        $to = date('Y-m-t', strtotime('last day of last month'));
        $fileName = "marketing_report_monthly_" . date('F_Y', strtotime($from)) . ".pdf";
        break;
    default:
        $from = $to = date('Y-m-d');
        $fileName = "marketing_report_daily_" . date('d-m-Y') . ".pdf";
        break;
}

ob_start();
echo "<h2>Marketing Report ({$reportType})</h2>";
echo "<p>Date Range: <strong>" . date('d-m-Y', strtotime($from)) . "</strong> to <strong>" . date('d-m-Y', strtotime($to)) . "</strong></p>";

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

$sql = "SELECT 
            b.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,
            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.branch_name, mp.name, c.customer_name";

$result = $conn->query($sql);
$grouped = [];

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

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

$html = ob_get_clean();

try {
    $pdf = new Html2Pdf();
    $pdf->writeHTML($html);
    $pdf->output($basePath . $fileName, 'F');

    // SEND EMAIL USING SMTP (BREVO)
    $mail = new PHPMailer(true);
    $mail->isSMTP();
    $mail->Host = 'smtp-relay.brevo.com';
    $mail->SMTPAuth = true;
    $mail->Username = '892f93001@smtp-brevo.com';
    $mail->Password = 'zYOs1gMvT4wD28aH';
    $mail->SMTPSecure = 'tls';
    $mail->Port = 587;

    $mail->setFrom('892f93001@smtp-brevo.com', 'DRMC Reports');
    $mail->addAddress('sdhnatarajan@gmail.com', 'Natarajan');
    $mail->addCC('krupachinthan@gmail.com');

    $mail->isHTML(true);
    $mail->Subject = 'DRMC Marketing Report (' . ucfirst($reportType) . ')';
    $mail->Body = 'Dear Team,<br><br>Please find attached the ' . ucfirst($reportType) . ' marketing report.<br><br>Regards,<br>DRMC';
    $mail->addAttachment($basePath . $fileName);

    $mail->send();
    echo "<br>✅ Email sent successfully.";

} catch (Exception $e) {
    echo "<br>❌ Email Error: " . $mail->ErrorInfo;
}

?>