<?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 KYC + Bank details
$sql = "SELECT k.id AS kyc_id, k.status AS kyc_status, k.remarks AS kyc_remarks,
       c.acknowlegement, c.complain_name, c.mobile,
       b.bank_name, b.account_number, b.ifsc_code, b.account_holder, b.hold_amount, b.remarks AS bank_remarks
FROM kyc k
LEFT JOIN cyber_police c ON k.c_id = c.id
LEFT JOIN bank_accounts b ON b.kyc_id = k.id
WHERE c.pid = '$pid' AND k.type = 'LIEN AMOUNT'
ORDER BY k.id DESC, b.id ASC";

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

// Prepare grouped data
$kycData = [];
while ($row = mysqli_fetch_assoc($result)) {
    $kycId = $row['kyc_id'];
    if (!isset($kycData[$kycId])) {
        $kycData[$kycId] = [
            'acknowlegement' => $row['acknowlegement'],
            'complain_name' => $row['complain_name'],
            'banks' => []
        ];
    }
    $kycData[$kycId]['banks'][] = [
        'bank_name' => $row['bank_name'],
        'account_number' => $row['account_number'],
        'ifsc_code' => $row['ifsc_code'],
        'account_holder' => $row['account_holder'],
        'hold_amount' => $row['hold_amount'],
        'bank_remarks' => $row['bank_remarks']
    ];
}

// Excel file headers
$filename = $policeStationName . "_LIEN_Report_" . date("Y-m-d_H-i-s") . ".xls";
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Pragma: no-cache");
header("Expires: 0");

// Start output
echo "<table border='1'>";
echo "<tr>
        <th colspan='8' style='text-align:center; font-weight:bold; background:#f2f2f2;'>
        {$policeStationName} LIEN REPORT</th>
      </tr>";
echo "<tr>
        <th>Acknowledgement</th>
        <th>Complainant Name</th>
        <th>Bank Name</th>
        <th>Account Number</th>
        <th>IFSC</th>
        <th>Account Holder</th>
        <th>Hold Amount</th>
        <th>Bank Remarks</th>
      </tr>";

foreach ($kycData as $kyc) {
    $rowspan = count($kyc['banks']) ?: 1;
    $first = true;
    foreach ($kyc['banks'] as $bank) {
        echo "<tr>";
        if ($first) {
            echo "<td rowspan='$rowspan'>{$kyc['acknowlegement']}</td>";
            echo "<td rowspan='$rowspan'>{$kyc['complain_name']}</td>";
            $first = false;
        }
        echo "<td>{$bank['bank_name']}</td>";
        echo "<td>{$bank['account_number']}</td>";
        echo "<td>{$bank['ifsc_code']}</td>";
        echo "<td>{$bank['account_holder']}</td>";
        echo "<td>{$bank['hold_amount']}</td>";
        echo "<td>{$bank['bank_remarks']}</td>";
        echo "</tr>";
    }
}

echo "</table>";
exit;
?>