<?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'];

// Receive POST data
$type = $_POST['type'] ?? 'CDR';
$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 CDR data
$domainQuery = "
    SELECT
        d.*,
        c.acknowlegement, 
        c.complain_name, 
        c.mobile,
        c.status,
        d.created_at,
        ps.p_name AS police_station
    FROM cdr d
    LEFT JOIN cyber_police c ON d.c_id = c.id
    LEFT JOIN policestation ps ON c.pid = ps.id
    WHERE d.c_id != 0 
      AND DATE(d.created_at) BETWEEN '$start' AND '$end'
    ORDER BY d.id DESC
";
$resultDomain = mysqli_query($con, $domainQuery);

// Set headers for Excel export
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=" . $policeStationName . "_CDR_Report_" . $start . "_to_" . $end . ".xls");
header("Pragma: no-cache");
header("Expires: 0");

// Begin Excel table output
echo "<table border='1'>";
echo "<tr><th colspan='3' style='text-align:center; font-size:16px;'><strong>" . htmlspecialchars($policeStationName) . " - Call Detail Record Report</strong></th></tr>";
echo "<tr>
        <th>#</th>
        <th>Acknowledgement Details</th>
        <th>CDR Details</th>
      </tr>";

if (mysqli_num_rows($resultDomain) > 0) {
    $serial = 1;
    while ($row = mysqli_fetch_assoc($resultDomain)) {
        echo "<tr>";
        echo "<td>" . $serial++ . "</td>";

        // Acknowledgement section
        $ackText = !empty($row['acknowlegement'])
            ? htmlspecialchars($row['acknowlegement']) . "<br>" .
            htmlspecialchars($row['complain_name']) . "<br>" .
            htmlspecialchars($row['mobile'])
            : "-";
        echo "<td>" . $ackText . "</td>";

        // Decode CDR details
        echo "<td>";
        $cdr_ids = $row['cdr_id'];
        $data_number = json_decode($cdr_ids, true);

        if (is_array($data_number) && count($data_number) > 0) {
            echo "<table border='1' width='100%'>";
            echo "<tr><th>Number</th><th>Start Date/Time</th><th>End Date/Time</th><th>Status</th></tr>";
            foreach ($data_number as $record) {
                echo "<tr>";
                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>";
            }
            echo "</table>";
        } else {
            echo "No CDR records";
        }

        echo "</td></tr>";
    }
} else {
    echo "<tr><td colspan='3' style='text-align:center;'>No records found</td></tr>";
}

echo "</table>";
?>