<?php
require 'vendor/autoload.php'; // Only if you're using Composer for PHPMailer

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

include('connect.php');

// File name & path
$filename = 'microsite_export_' . date('Ymd') . '.xls';
$filepath = __DIR__ . '/exports/' . $filename;

$filterby = " AND request_date = CURDATE()"; // Today's data only

// Query to get only latest per mobile
$sql = "SELECT r.*
        FROM requests r
        INNER JOIN (
            SELECT MAX(id) AS max_id
            FROM requests
            WHERE span_status = '0' AND delete_status = 0 $filterby
            GROUP BY mobile
        ) AS latest ON r.id = latest.max_id
        ORDER BY r.id DESC";

$result = mysqli_query($conn, $sql);

// Start output buffering
ob_start();
echo "<table border='1'>";
echo "<tr>
<th>ID</th>
<th>Project</th>
<th>Sender</th>
<th>Source</th>
<th>Date</th>
<th>Location</th>
<!-- <th>Mobile</th> -->
<!-- <th>Email</th> -->
<th>IP</th>
<th>UTM Medium</th>
<th>UTM Term</th>
<th>UTM Campaign ID</th>
</tr>";

while ($row = mysqli_fetch_assoc($result)) {
    $maskedMobile = "(" . $row['calling_code'] . ") XXXXXX" . substr($row['mobile'], -4);
    $maskedEmail = substr($row['email'], 0, 3) . "xxxx@gmail.com";
    $location = $row['city'] . " (" . $row['country'] . ")";
    $formattedDate = date('d-m-Y h:i A', strtotime($row['request_datetime']));

    echo "<tr>";
    echo "<td>{$row['id']}</td>";
    echo "<td>{$row['project_name']}</td>";
    echo "<td>{$row['name']}</td>";
    echo "<td>{$row['utm_source']}</td>";
    echo "<td>{$formattedDate}</td>";
    echo "<td>{$location}</td>";
    // echo "<td>{$maskedMobile}</td>";
    // echo "<td>{$maskedEmail}</td>";
    echo "<td>{$row['ip_address']}</td>";
    echo "<td>{$row['utm_medium']}</td>";
    echo "<td>{$row['utm_term']}</td>";
    echo "<td>{$row['utm_campaignID']}</td>";
    echo "</tr>";
}
echo "</table>";
$content = ob_get_clean();

// Save Excel file
file_put_contents($filepath, $content);

// Send Email with PHPMailer
$mail = new PHPMailer(true);

try {
    $mail->isSMTP();
    $mail->Host = 'smtp.yourdomain.com';
    $mail->SMTPAuth = true;
    $mail->Username = 'test@theniralatrio.com';
    $mail->Password = 'Kamal@321';
    $mail->SMTPSecure = 'tls';
    $mail->Port = 587;

    $mail->setFrom('test@theniralatrio.com', 'Microsite Reports');
    $mail->addAddress('kamal.yadav@investoxpert.com'); // Add recipient(s)
    $mail->Subject = 'Daily Microsite Export - ' . date('d-m-Y');
    $mail->Body    = "Hi,\n\nPlease find attached today's lead report.\n\nRegards,\nMicrosite Dashboard";
    $mail->addAttachment($filepath);

    $mail->send();
    echo "Mail sent successfully!";
} catch (Exception $e) {
    echo "Mailer Error: {$mail->ErrorInfo}";
}
?>


