<?php
include('connect.php');

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=microsite_export.xls");

$filterby = "";

// Date filter
if (isset($_GET['filterbydate']) && $_GET['filterbydate'] != "") {
    $date = explode("-", $_GET['filterbydate']);
    $startDate = date('Y-m-d', strtotime(trim($date[0])));
    $endDate = date('Y-m-d', strtotime(trim($date[1])));
    $filterby .= " AND request_date >= '$startDate' AND request_date <= '$endDate'";
}

// Source filter
if (isset($_GET['source']) && $_GET['source'] != "") {
    $selectedSource = htmlspecialchars($_GET['source'], ENT_QUOTES);
    $filterby .= " AND utm_source = '$selectedSource'";
}

// Fetch only latest request per unique mobile number
$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);

// Output main table
echo "<table border='1'>";
echo "<tr>
<th colspan='11'><strong>Leads Export</strong></th>
</tr>";
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>";

// Collect project+source summary
$projectSourceCounts = [];

while ($row = mysqli_fetch_assoc($result)) {
    $project = $row['project_name'];
    $source = $row['utm_source'];
    $key = $project . ' | ' . $source;

    // Count by project + source combo
    if (!isset($projectSourceCounts[$key])) {
        $projectSourceCounts[$key] = 1;
    } else {
        $projectSourceCounts[$key]++;
    }

    $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>{$project}</td>";
    echo "<td>{$row['name']}</td>";
    echo "<td>{$source}</td>";
    echo "<td>{$formattedDate}</td>";
    echo "<td>{$location}</td>";
    // echo "<td>{$maskedMobile}</td>"; // Uncomment if needed
    // echo "<td>{$maskedEmail}</td>";  // Uncomment if needed
    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>";

// Output summary table with project and source
echo "<br><br><table border='1'>";
echo "<tr><th colspan='3'><strong>Total Leads Per Project & Source</strong></th></tr>";
echo "<tr><th>Project Name</th><th>Source</th><th>Lead Count</th></tr>";
foreach ($projectSourceCounts as $key => $count) {
    list($projectName, $source) = explode(' | ', $key);
    echo "<tr><td>{$projectName}</td><td>{$source}</td><td>{$count}</td></tr>";
}
echo "</table>";

exit;
?>
