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

// Redirect if not logged in
if (!isset($_SESSION['user'])) {
    header("Location: ../index.php");
    exit();
}

$user = $_SESSION['user'];
$pid = $user['pid'];

// 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 CCTV data
$sql = "
    SELECT c.*, 
           p.acknowlegement, 
           p.complain_name, 
           p.mobile, 
           c.status
    FROM cctv c
    LEFT JOIN cyber_police p ON c.c_id = p.id
    WHERE p.pid = '$pid'
    ORDER BY c.id DESC
";
$result = mysqli_query($con, $sql);

// Send headers to force download as Excel
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"{$policeStationName}_CCTV_Report.xls\"");
header("Pragma: no-cache");
header("Expires: 0");

echo "<table border='1'>";
echo "<tr><th colspan='6' style='font-size:16px; text-align:center;'>{$policeStationName} CCTV Report</th></tr>";
echo "<tr>
        <th>#</th>
        <th>Acknowledgement</th>
        <th>Complainant</th>
        <th>Mobile</th>
        <th>CCTV Details</th>
        <th>Status</th>
      </tr>";

$serial = 1;
while ($row = mysqli_fetch_assoc($result)) {
    $cctv_details = json_decode($row['cctv_details'], true);

    // Prepare nested table as HTML for Excel
    $cctvHTML = '';
    if (is_array($cctv_details) && count($cctv_details) > 0) {
        $cctvHTML .= "<table border='1'>";
        $cctvHTML .= "<tr><th>Bank Name</th><th>ATM ID</th><th>Start Date/Time</th><th>End Date/Time</th></tr>";
        foreach ($cctv_details as $cctv) {
            $cctvHTML .= "<tr>";
            $cctvHTML .= "<td>" . htmlspecialchars($cctv['bank_name'] ?? '-') . "</td>";
            $cctvHTML .= "<td>" . htmlspecialchars($cctv['atm_id'] ?? '-') . "</td>";
            $cctvHTML .= "<td>" . htmlspecialchars($cctv['start_date_time'] ?? '-') . "</td>";
            $cctvHTML .= "<td>" . htmlspecialchars($cctv['end_date_time'] ?? '-') . "</td>";
            $cctvHTML .= "</tr>";
        }
        $cctvHTML .= "</table>";
    } else {
        $cctvHTML = "No CCTV details available.";
    }

    echo "<tr>";
    echo "<td>{$serial}</td>";
    echo "<td>" . htmlspecialchars($row['acknowlegement'] ?? '-') . "</td>";
    echo "<td>" . htmlspecialchars($row['complain_name'] ?? '-') . "</td>";
    echo "<td>" . htmlspecialchars($row['mobile'] ?? '-') . "</td>";
    echo "<td>{$cctvHTML}</td>";
    echo "<td>" . htmlspecialchars($row['status'] ?? '-') . "</td>";
    echo "</tr>";

    $serial++;
}

echo "</table>";
?>