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

$sql = "SELECT 
        k.id AS comply_order_id, 
        k.remarks AS comply_remarks,
        c.acknowlegement, 
        c.complain_name, 
        c.mobile, 
        c.time_stamp,
        ps.p_name AS police_station,  -- ✅ Police Station Name
        b.b_name AS bank_name, 
        cbd.account_number, 
        cbd.ifsc_code, 
        cbd.account_holder, 
        cbd.release_amount, 
        cbd.remarks AS bank_remarks
    FROM comply_order k
    LEFT JOIN cyber_police c ON k.c_id = c.id
    LEFT JOIN c_bank_data cbd ON cbd.comply_order_id = k.id
    LEFT JOIN bank b ON cbd.bank_id = b.id
    LEFT JOIN policestation ps ON c.pid = ps.id
    WHERE DATE(c.time_stamp) BETWEEN '$start' AND '$end'
    ORDER BY k.id DESC, cbd.id ASC
";
$result = mysqli_query($con, $sql);

// Grouping data by comply_order_id
$complyData = [];
while ($row = mysqli_fetch_assoc($result)) {
    $id = $row['comply_order_id'];
    if (!isset($complyData[$id])) {
        $complyData[$id] = [
            'acknowlegement' => $row['acknowlegement'],
            'complain_name' => $row['complain_name'],
            'mobile' => $row['mobile'],
            'police_station' => $row['police_station'],
            'comply_remarks' => $row['comply_remarks'],
            'banks' => []
        ];
    }
    $complyData[$id]['banks'][] = [
        'bank_name' => $row['bank_name'],
        'account_number' => $row['account_number'],
        'ifsc_code' => $row['ifsc_code'],
        'account_holder' => $row['account_holder'],
        'release_amount' => $row['release_amount'],
        'bank_remarks' => $row['bank_remarks']
    ];
}

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

echo "<table border='1'>";
echo "<tr><th colspan='9' style='font-size:18px; text-align:center;'>{$policeStationName} Comply Order Report</th></tr>";
echo "<tr>
        <th>Acknowledgement</th>
        <th>Complainant Name</th>
        <th>Mobile</th>
        <th>Police Station</th>
        <th>Bank Name</th>
        <th>Account Number</th>
        <th>IFSC</th>
        <th>Account Holder</th>
        <th>Release Amount</th>
        <th>Bank Remarks</th>
    </tr>";

foreach ($complyData as $order) {
    $rowspan = count($order['banks']) ?: 1;
    foreach ($order['banks'] as $i => $bank) {
        echo "<tr>";
        if ($i === 0) {
            echo "<td rowspan='{$rowspan}'>" . htmlspecialchars($order['acknowlegement']) . "</td>";
            echo "<td rowspan='{$rowspan}'>" . htmlspecialchars($order['complain_name']) . "</td>";
            echo "<td rowspan='{$rowspan}'>" . htmlspecialchars($order['mobile']) . "</td>";
            echo "<td rowspan='{$rowspan}'>" . htmlspecialchars($order['police_station']) . "</td>";
        }
        echo "<td>" . htmlspecialchars($bank['bank_name']) . "</td>";
        echo "<td>" . htmlspecialchars($bank['account_number']) . "</td>";
        echo "<td>" . htmlspecialchars($bank['ifsc_code']) . "</td>";
        echo "<td>" . htmlspecialchars($bank['account_holder']) . "</td>";
        echo "<td>" . htmlspecialchars($bank['release_amount']) . "</td>";
        echo "<td>" . htmlspecialchars($bank['bank_remarks']) . "</td>";
        echo "</tr>";
    }
}

echo "</table>";
?>