<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

require "db.php";

/*
|--------------------------------------------------------------------------
| PETTY CASH REGISTER (STAFF ONLY)
|--------------------------------------------------------------------------
| - Shows only petty cash movements
| - No staff / CEO / bank names
| - Running balance represents petty cash in hand
|--------------------------------------------------------------------------
*/

/* ================= FILTER ================= */
$from = $_GET['from'] ?? date('Y-m-01');
$to   = $_GET['to']   ?? date('Y-m-d');

/* ================= GET PETTY CASH ACCOUNT ================= */
$staffAcc = mysqli_fetch_assoc(mysqli_query($conn,"
    SELECT id
    FROM hps_cash_accounts
    WHERE account_type = 'STAFF'
    ORDER BY id
    LIMIT 1
"));

$staff_id = $staffAcc ? (int)$staffAcc['id'] : 0;

/* ================= OPENING BALANCE ================= */
$opening = 0;

if ($staff_id > 0) {
    $ob = mysqli_fetch_assoc(mysqli_query($conn,"
        SELECT IFNULL(SUM(
            CASE
                WHEN to_account = $staff_id THEN amount
                WHEN from_account = $staff_id THEN -amount
                ELSE 0
            END
        ),0) bal
        FROM hps_cashbook
        WHERE trans_date < '$from'
    "));
    $opening = (float)$ob['bal'];
}

/* ================= TRANSACTIONS ================= */
$rows = mysqli_query($conn,"
    SELECT
        trans_date,
        description,
        amount,
        from_account,
        to_account,
        ref_type
    FROM hps_cashbook
    WHERE trans_date BETWEEN '$from' AND '$to'
      AND (
            from_account = $staff_id
         OR to_account   = $staff_id
      )
    ORDER BY trans_date, id
");
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Petty Cash Register</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>

<body class="p-3 bg-light">

<div class="container" style="max-width:1000px;">

<h4 class="mb-3">Petty Cash Register</h4>

<form method="get" class="row g-2 mb-3">
    <div class="col-md-3">
        <label>From</label>
        <input type="date" name="from" value="<?= $from ?>" class="form-control">
    </div>
    <div class="col-md-3">
        <label>To</label>
        <input type="date" name="to" value="<?= $to ?>" class="form-control">
    </div>
    <div class="col-md-3 d-flex align-items-end">
        <button class="btn btn-primary w-100">Filter</button>
    </div>
</form>

<table class="table table-sm table-bordered">
<thead class="table-dark">
<tr>
    <th>Date</th>
    <th>Description</th>
    <th class="text-end">In</th>
    <th class="text-end">Out</th>
    <th class="text-end">Balance</th>
</tr>
</thead>
<tbody>

<tr class="table-secondary">
    <td colspan="4"><b>Opening Balance</b></td>
    <td class="text-end"><b><?= number_format($opening,2) ?></b></td>
</tr>

<?php
$running = $opening;

if ($rows && mysqli_num_rows($rows) > 0):

while ($r = mysqli_fetch_assoc($rows)) {

    $in  = ($r['to_account'] == $staff_id)   ? (float)$r['amount'] : 0;
    $out = ($r['from_account'] == $staff_id) ? (float)$r['amount'] : 0;

    $running += ($in - $out);

    echo "
    <tr>
        <td>{$r['trans_date']}</td>
        <td>".htmlspecialchars($r['description'])."</td>
        <td class='text-end'>".($in  ? number_format($in,2)  : '')."</td>
        <td class='text-end'>".($out ? number_format($out,2) : '')."</td>
        <td class='text-end'><b>".number_format($running,2)."</b></td>
    </tr>";
}

else:
?>
<tr>
    <td colspan="5" class="text-center">No transactions found</td>
</tr>
<?php endif; ?>

</tbody>
</table>

</div>

</body>
</html>
