<?php
session_start();
include "../config.php";

// Check login
if (!isset($_SESSION['user'])) {
    header("Location: ../index.php");
    exit();
}

$user = $_SESSION['user'];
$pid = $user['pid'];
$platform = $_GET['platform'] ?? '';

// Receive POST data
$type = $_POST['type'] ?? 'KYC';
$start = $_POST['start'] ?? '';
$end = $_POST['end'] ?? '';

if (!$start || !$end) {
    die("Missing date range!");
}

// Get police station name
$psQuery = "SELECT p_name FROM policestation WHERE id = '$pid' LIMIT 1";
$psResult = mysqli_query($con, $psQuery);
$policeStationName = mysqli_fetch_assoc($psResult)['p_name'] ?? 'PoliceStation';

// Fetch social media data
$sql = "
    SELECT 
        s.*, 
        c.acknowlegement, 
        c.complain_name, 
        c.mobile, 
        c.incident_date_time, 
        c.total_fraud_amount, 
        c.total_lien_amount, 
        c.status AS complain_status,
        s.status AS social_status,
        c.time_stamp,
        ps.p_name AS police_station
    FROM social_media s
    LEFT JOIN cyber_police c ON s.c_id = c.id
    LEFT JOIN policestation ps ON c.pid = ps.id
    WHERE s.c_id != 0
      AND DATE(c.time_stamp) BETWEEN '$start' AND '$end'
      AND s.type = '$platform'
    ORDER BY s.id DESC
";

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

// Prepare file for Excel download
$filename = strtoupper($platform) . "_Report_" . date('Y-m-d_His') . ".xls";

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Pragma: no-cache");
header("Expires: 0");

$platform_title = strtoupper($platform);
?>

<table border="1">
    <tr>
        <th colspan="8" style="text-align:center; font-size:16px; background:#d9d9d9;">
            <?= htmlspecialchars("$policeStationName - $platform_title Report") ?>
        </th>
    </tr>
    <tr>
        <th>#</th>
        <th>Acknowledgement</th>
        <th>Complainant</th>
        <th>Mobile</th>
        <th>Police Station</th>
        <th>Social Media ID(s)</th>
        <th>Incident Date</th>
        <th>Status</th>
    </tr>

    <?php
    if (mysqli_num_rows($result) > 0) {
        $i = 1;
        while ($row = mysqli_fetch_assoc($result)) {
            $social_ids = json_decode($row['social_id'], true);
            $social_display = '';
            if (is_array($social_ids)) {
                foreach ($social_ids as $id) {
                    $social_display .= htmlspecialchars($id) . "<br>";
                }
            } else {
                $social_display = htmlspecialchars($row['social_id']);
            }

            echo "<tr>
                <td>{$i}</td>
                <td>" . htmlspecialchars($row['acknowlegement'] ?? '-') . "</td>
                <td>" . htmlspecialchars($row['complain_name'] ?? '-') . "</td>
                <td>" . htmlspecialchars($row['mobile'] ?? '-') . "</td>
                <td>" . htmlspecialchars($row['police_station'] ?? '-') . "</td>
                <td>{$social_display}</td>
                <td>" . htmlspecialchars($row['incident_date_time'] ?? '-') . "</td>
                <td>" . htmlspecialchars($row['social_status'] ?? '-') . "</td>
              </tr>";
            $i++;
        }
    } else {
        echo "<tr><td colspan='8' style='text-align:center;'>No records found</td></tr>";
    }
    ?>
</table>