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

// Headers for Excel download
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=microsite_export_" . date('Y-m-d') . ".xls");
header("Pragma: no-cache");
header("Expires: 0");

// Sirf admin_id = 3 (Ramkaran) ke liye full Mobile & Email visible honge.
// Baaki sabke liye pehle jaisa hi rahega (Mobile/Email column export me nahi aayega).
$showFullContact = (isset($_SESSION['admin_id']) && $_SESSION['admin_id'] == 3);

// ─────────────────────────────────────────────────────────────
// Build WHERE condition - same as microsites.php
// ─────────────────────────────────────────────────────────────
$where = "WHERE span_status = '0' AND delete_status = 0";

// 1. Date filter (improved & reliable parsing)
$filterbydate = trim($_GET['filterbydate'] ?? '');
if ($filterbydate !== '') {
    $parts = explode(' - ', $filterbydate);
    if (count($parts) === 2) {
        $startStr = trim($parts[0]);
        $endStr   = trim($parts[1]);

        $startDt = DateTime::createFromFormat('F j, Y', $startStr);
        $endDt   = DateTime::createFromFormat('F j, Y', $endStr);

        if ($startDt !== false && $endDt !== false) {
            $startDate = $startDt->format('Y-m-d');
            $endNext   = (clone $endDt)->modify('+1 day');
            $endDate   = $endNext->format('Y-m-d');

            $where .= " AND request_date >= '$startDate' AND request_date < '$endDate'";
        }
    }
}

// 2. Multiple source filter (this was missing in old code)
$source_param = $_GET['source'] ?? '';
if ($source_param !== '') {
    $sources = array_filter(array_map('trim', explode(',', $source_param)));
    if (!empty($sources)) {
        $escaped = array_map(function($s) use ($conn) {
            return "'" . mysqli_real_escape_string($conn, $s) . "'";
        }, $sources);

        $where .= " AND utm_source IN (" . implode(',', $escaped) . ")";
    }
}

// ─────────────────────────────────────────────────────────────
// Query - Latest record per mobile number (your original logic)
// ─────────────────────────────────────────────────────────────
$sql = "
    SELECT r.*
    FROM requests r
    INNER JOIN (
        SELECT MAX(id) AS max_id
        FROM requests
        $where
        GROUP BY mobile
    ) AS latest ON r.id = latest.max_id
    ORDER BY r.id DESC
";

$result = mysqli_query($conn, $sql) or die(mysqli_error($conn));

// ─────────────────────────────────────────────────────────────
// Start Excel output
// ─────────────────────────────────────────────────────────────
echo "<table border='1'>";

// Title Row
echo "<tr>
        <th colspan='10' style='font-size:16px; background:#f0f0f0;'>
            <strong>Microsite Leads Export</strong>
        </th>
      </tr>";

// Column Headers
echo "<tr style='background:#e0e0e0; font-weight:bold;'>
        <th>ID</th>
        <th>Project</th>
        <th>Sender</th>
        <th>Source</th>
        <th>Date</th>
        <th>Location</th>";
if ($showFullContact) {
    echo "<th>Mobile</th>
        <th>Email</th>";
}
echo "
        <th>IP</th>
        <th>UTM Medium</th>
        <th>UTM Term</th>
        <th>UTM Campaign ID</th>
      </tr>";

// Project + Source summary array
$projectSourceCounts = [];

// Data Rows
while ($row = mysqli_fetch_assoc($result)) {
    $project = $row['project_name'] ?? '-';
    $source  = $row['utm_source'] ?? 'N/A';
    $key     = $project . ' | ' . $source;

    // Count leads per project + source combination
    $projectSourceCounts[$key] = ($projectSourceCounts[$key] ?? 0) + 1;

    $fullMobile   = "(" . ($row['calling_code'] ?? '') . ") " . ($row['mobile'] ?? '-');
    $fullEmail    = $row['email'] ?? '-';
    $location     = ($row['city'] ?? '-') . " (" . ($row['country'] ?? '-') . ")";
    $formattedDate = $row['request_datetime'] ? date('d-m-Y h:i A', strtotime($row['request_datetime'])) : '-';

    echo "<tr>";
    echo "<td>{$row['id']}</td>";
    echo "<td>" . htmlspecialchars($project) . "</td>";
    echo "<td>" . htmlspecialchars($row['name'] ?? '-') . "</td>";
    echo "<td>" . htmlspecialchars($source) . "</td>";
    echo "<td>{$formattedDate}</td>";
    echo "<td>" . htmlspecialchars($location) . "</td>";
    if ($showFullContact) {
        echo "<td>" . htmlspecialchars($fullMobile) . "</td>";
        echo "<td>" . htmlspecialchars($fullEmail) . "</td>";
    }
    echo "<td>" . htmlspecialchars($row['ip_address'] ?? '-') . "</td>";
    echo "<td>" . htmlspecialchars($row['utm_medium'] ?? '-') . "</td>";
    echo "<td>" . htmlspecialchars($row['utm_term'] ?? '-') . "</td>";
    echo "<td>" . htmlspecialchars($row['utm_campaignID'] ?? '-') . "</td>";
    echo "</tr>";
}

echo "</table>";

// ─────────────────────────────────────────────────────────────
// Summary Table - Project + Source wise count
// ─────────────────────────────────────────────────────────────
echo "<br><br><table border='1'>";
echo "<tr><th colspan='3' style='font-size:16px; background:#f0f0f0;'>
          <strong>Total Leads Per Project & Source</strong>
      </th></tr>";
echo "<tr style='background:#e0e0e0; font-weight:bold;'>
          <th>Project Name</th>
          <th>Source</th>
          <th>Lead Count</th>
      </tr>";

foreach ($projectSourceCounts as $key => $count) {
    list($projectName, $sourceName) = explode(' | ', $key);
    echo "<tr>";
    echo "<td>" . htmlspecialchars($projectName) . "</td>";
    echo "<td>" . htmlspecialchars($sourceName) . "</td>";
    echo "<td>{$count}</td>";
    echo "</tr>";
}
echo "</table>";

exit;
?>