<?php
include 'layout.php';
include 'db.php';

// Fetch active banks for dropdown
$banks = $conn->query("SELECT id, account_title FROM bank_accounts WHERE is_active = 1");

// Get filter values
$account_type = $_GET['account_type'] ?? 'all';
$bank_id = $_GET['bank_id'] ?? '';
$start_date = $_GET['start_date'] ?? '';
$end_date = $_GET['end_date'] ?? '';
?>

<div class="main-wrapper">
  <div class="page-wrapper">
    <div class="content">
      <div class="page-header d-flex justify-content-between align-items-center">
        <div class="page-title">
          <h4>Account Ledger</h4>
          <h6>View all cash and bank transactions with running balance</h6>
        </div>
      </div>

      <!-- Filter Form -->
      <form method="GET" class="row mb-4 g-2 align-items-end">
        <div class="col-md-3">
          <label>Account Type</label>
          <select name="account_type" class="form-control">
            <option value="all" <?= $account_type == 'all' ? 'selected' : '' ?>>All</option>
            <option value="cash" <?= $account_type == 'cash' ? 'selected' : '' ?>>Cash</option>
            <option value="bank" <?= $account_type == 'bank' ? 'selected' : '' ?>>Bank</option>
          </select>
        </div>
        <div class="col-md-3">
          <label>Bank</label>
          <select name="bank_id" class="form-control">
            <option value="">All Banks</option>
            <?php while($b = $banks->fetch_assoc()): ?>
              <option value="<?= $b['id'] ?>" <?= $bank_id == $b['id'] ? 'selected' : '' ?>><?= htmlspecialchars($b['account_title']) ?></option>
            <?php endwhile; ?>
          </select>
        </div>
        <div class="col-md-2">
          <label>Start Date</label>
          <input type="date" name="start_date" value="<?= htmlspecialchars($start_date) ?>" class="form-control">
        </div>
        <div class="col-md-2">
          <label>End Date</label>
          <input type="date" name="end_date" value="<?= htmlspecialchars($end_date) ?>" class="form-control">
        </div>
        <div class="col-md-2">
          <button class="btn btn-primary w-100">Filter</button>
        </div>
      </form>

      <!-- Ledger Table -->
      <div class="card">
        <div class="card-body">
          <table class="table table-bordered table-striped">
            <thead class="table-light">
              <tr>
                <th>Date</th>
                <th>Account</th>
                <th>Description</th>
                <th>Reference</th>
                <th class="text-end">In</th>
                <th class="text-end">Out</th>
                <th class="text-end">Balance</th>
              </tr>
            </thead>
            <tbody>
              <?php
              // --- Fetch Transactions ---
              $where_in = [];
              $where_out = [];
              $params = [];

              // Account type filter
              if ($account_type === 'cash') {
                  $where_in[] = "at.account_type = 'cash'";
                  $where_out[] = "e.payment_method = 'cash'";
              } elseif ($account_type === 'bank') {
                  $where_in[] = "at.account_type IN ('bank','card','upi')";
                  $where_out[] = "e.payment_method = 'bank'";
                  if ($bank_id) {
                      $where_in[] = "at.account_id = " . intval($bank_id);
                      $where_out[] = "e.bank_account_id = " . intval($bank_id);
                  }
              } else { // all
                  // No extra filter for inflow
                  // No extra filter for outflow
                  if ($bank_id) {
                      $where_in[] = "at.account_id = " . intval($bank_id);
                      $where_out[] = "e.bank_account_id = " . intval($bank_id);
                  }
              }

              // Date filter
              if ($start_date) {
                  $where_in[] = "DATE(at.created_at) >= '" . $conn->real_escape_string($start_date) . "'";
                  $where_out[] = "DATE(e.expense_date) >= '" . $conn->real_escape_string($start_date) . "'";
              }
              if ($end_date) {
                  $where_in[] = "DATE(at.created_at) <= '" . $conn->real_escape_string($end_date) . "'";
                  $where_out[] = "DATE(e.expense_date) <= '" . $conn->real_escape_string($end_date) . "'";
              }

              $inflow_sql = "
                  SELECT 
                      at.created_at AS txn_date,
                      IFNULL(ba.account_title, 'Cash') AS account,
                      at.description,
                      at.sale_id AS reference_id,
                      at.amount AS amount_in,
                      0 AS amount_out
                  FROM account_transactions at
                  LEFT JOIN bank_accounts ba ON at.account_id = ba.id
                  " . (count($where_in) ? ("WHERE " . implode(' AND ', $where_in)) : "") . "
              ";

              $outflow_sql = "
                  SELECT 
                      e.expense_date AS txn_date,
                      IFNULL(ba.account_title, 'Cash') AS account,
                      e.remarks AS description,
                      e.id AS reference_id,
                      0 AS amount_in,
                      e.amount AS amount_out
                  FROM expenses e
                  LEFT JOIN bank_accounts ba ON e.bank_account_id = ba.id
                  " . (count($where_out) ? ("WHERE " . implode(' AND ', $where_out)) : "") . "
              ";

              // Fetch and merge
              $transactions = [];
              $res_in = $conn->query($inflow_sql);
              while ($row = $res_in->fetch_assoc()) {
                  $row['txn_type'] = 'in';
                  $transactions[] = $row;
              }
              $res_out = $conn->query($outflow_sql);
              while ($row = $res_out->fetch_assoc()) {
                  $row['txn_type'] = 'out';
                  $transactions[] = $row;
              }

              // Sort by date, then by type (in before out if same date/time)
              usort($transactions, function($a, $b) {
                  $cmp = strcmp($a['txn_date'], $b['txn_date']);
                  if ($cmp === 0) {
                      return $a['txn_type'] === 'in' ? -1 : 1;
                  }
                  return $cmp;
              });

              // Calculate running balance
              $balance = 0;
              foreach ($transactions as $k => $row) {
                  $balance += $row['amount_in'] - $row['amount_out'];
                  $transactions[$k]['balance'] = $balance;
              }
              ?>
              <?php if (count($transactions)): ?>
                <?php foreach ($transactions as $row): ?>
                  <tr>
                    <td><?= htmlspecialchars($row['txn_date'] ?? '') ?></td>
                    <td><?= htmlspecialchars($row['account'] ?? '') ?></td>
                    <td><?= htmlspecialchars($row['description'] ?? '') ?></td>
                    <td><?= htmlspecialchars($row['reference_id'] ?? '') ?></td>
                    <td class="text-end"><?= $row['amount_in'] ? number_format($row['amount_in'], 2) : '' ?></td>
                    <td class="text-end"><?= $row['amount_out'] ? number_format($row['amount_out'], 2) : '' ?></td>
                    <td class="text-end"><?= number_format($row['balance'], 2) ?></td>
                  </tr>
                <?php endforeach; ?>
              <?php else: ?>
                <tr><td colspan="7" class="text-center">No transactions found.</td></tr>
              <?php endif; ?>
            </tbody>
          </table>
        </div>
      </div>

    </div>
  </div>
</div> 