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

// Check login
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';

// Fetch KYC + Bank details within the date range
$sql = "
    SELECT 
        k.id AS kyc_id, 
        k.status AS kyc_status, 
        k.remarks AS kyc_remarks,
        c.acknowlegement, 
        c.complain_name, 
        c.mobile, 
        c.time_stamp,
        ps.p_name AS police_station,
        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
    LEFT JOIN policestation ps ON c.pid = ps.id
    WHERE k.type = 'KYC'
      AND DATE(c.time_stamp) BETWEEN '$start' AND '$end'
    ORDER BY c.time_stamp DESC, b.id ASC
";
$result = mysqli_query($con, $sql);

// Preprocess to group data by KYC
$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'],
            'police_station' => $row['police_station'],
            'kyc_status' => $row['kyc_status'],
            'kyc_remarks' => $row['kyc_remarks'],
            'time_stamp' => $row['time_stamp'],
            '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'],
    ];
}

// ✅ Set headers for Excel file download
$filename = $policeStationName . "_KYC_Report_" . $start . "_to_" . $end . ".xls";
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Pragma: no-cache");
header("Expires: 0");
?>

<table border="1">
    <thead>
        <tr style="background-color:#f2f2f2;">
            <th>Ack No.</th>
            <th>Complainant</th>
            <th>Police Station</th>
            <th>Bank Name</th>
            <th>Account No.</th>
            <th>IFSC</th>
            <th>Holder</th>
            <th>Hold Amount</th>
            <th>Remarks</th>
            <th>Date</th>
        </tr>
    </thead>
    <tbody>
        <?php foreach ($kycData as $kyc): ?>
            <?php $rowspan = count($kyc['banks']) ?: 1; ?>
            <?php foreach ($kyc['banks'] as $i => $bank): ?>
                <tr>
                    <?php if ($i === 0): ?>
                        <td rowspan="<?= $rowspan ?>"><?= htmlspecialchars($kyc['acknowlegement'] ?? '') ?></td>
                        <td rowspan="<?= $rowspan ?>"><?= htmlspecialchars($kyc['complain_name'] ?? '') ?></td>
                        <td rowspan="<?= $rowspan ?>"><?= htmlspecialchars($kyc['police_station'] ?? '') ?></td>
                    <?php endif; ?>
                    <td><?= htmlspecialchars($bank['bank_name'] ?? '') ?></td>
                    <td><?= htmlspecialchars($bank['account_number'] ?? '') ?></td>
                    <td><?= htmlspecialchars($bank['ifsc_code'] ?? '') ?></td>
                    <td><?= htmlspecialchars($bank['account_holder'] ?? '') ?></td>
                    <td><?= htmlspecialchars($bank['hold_amount'] ?? '') ?></td>
                    <td><?= htmlspecialchars($bank['bank_remarks'] ?? '') ?></td>
                    <?php if ($i === 0): ?>
                        <td rowspan="<?= $rowspan ?>">
                            <?= htmlspecialchars(date('d-m-Y', strtotime($kyc['time_stamp'] ?? ''))) ?>
                        </td>
                    <?php endif; ?>
                </tr>
            <?php endforeach; ?>
        <?php endforeach; ?>
    </tbody>
</table>