<?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 CDR data
$domainQuery = "
    SELECT 
        d.*, 
        c.acknowlegement, 
        c.complain_name, 
        c.mobile,
        c.status
    FROM cdr d
    LEFT JOIN cyber_police c 
        ON d.c_id = c.id
    WHERE d.c_id != 0 
      AND c.pid = '$pid'
    ORDER BY d.id DESC
";
$resultDomain = mysqli_query($con, $domainQuery);

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

<table border="1">
    <!-- Title Row -->
    <tr>
        <th colspan="6" style="font-size:16pt; text-align:center; background-color:#f2f2f2;">
            <?= htmlspecialchars($policeStationName) ?> - Call Detail Record
        </th>
    </tr>

    <!-- Table Header -->
    <tr style="background-color:#f2f2f2;">
        <th>#</th>
        <th>Acknowledgement</th>
        <th>Number</th>
        <th>Start Date/Time</th>
        <th>End Date/Time</th>
        <th>Status</th>
    </tr>

    <tbody>
        <?php
        if (mysqli_num_rows($resultDomain) > 0):
            $serial = 1;
            while ($row = mysqli_fetch_assoc($resultDomain)):
                $cdr_ids = $row['cdr_id'];
                $data_number = json_decode($cdr_ids, true);

                // If no CDR numbers, show one row with "-"
                if (!is_array($data_number) || count($data_number) == 0) {
                    echo "<tr>";
                    echo "<td>" . $serial++ . "</td>";
                    echo "<td>" . htmlspecialchars($row['acknowlegement'] ?? '-') . " | " . htmlspecialchars($row['complain_name'] ?? '-') . " | " . htmlspecialchars($row['mobile'] ?? '-') . "</td>";
                    echo "<td colspan='4' style='text-align:center;'>No CDR records</td>";
                    echo "</tr>";
                } else {
                    foreach ($data_number as $record) {
                        echo "<tr>";
                        echo "<td>" . $serial . "</td>";
                        echo "<td>" . htmlspecialchars($row['acknowlegement'] ?? '-') . " | " . htmlspecialchars($row['complain_name'] ?? '-') . " | " . htmlspecialchars($row['mobile'] ?? '-') . "</td>";
                        echo "<td>" . htmlspecialchars($record['number'] ?? '-') . "</td>";
                        echo "<td>" . htmlspecialchars($record['from_date'] ?? '-') . "</td>";
                        echo "<td>" . htmlspecialchars($record['to_date'] ?? '-') . "</td>";
                        echo "<td>" . htmlspecialchars($record['status'] ?? '-') . "</td>";
                        echo "</tr>";
                    }
                    $serial++;
                }
            endwhile;
        else:
            echo '<tr><td colspan="6" style="text-align:center;">No records found</td></tr>';
        endif;
        ?>
    </tbody>
</table>