<?php
include("layout.php");
include("db.php");

$start_date = $_GET['start_date'] ?? '';
$end_date = $_GET['end_date'] ?? '';

$start_sql = mysqli_real_escape_string($conn, $start_date);
$end_sql = mysqli_real_escape_string($conn, $end_date);

$where = '';
if ($start_date && $end_date) {
    $where = "AND at.created_at BETWEEN '$start_sql 00:00:00' AND '$end_sql 23:59:59'";
}

// Total Cash In (from account_transactions)
$cash_in_sql = "
    SELECT COALESCE(SUM(amount), 0) AS total_cash
    FROM account_transactions at
    WHERE account_type = 'cash' $where
";
$cash_in = mysqli_fetch_assoc(mysqli_query($conn, $cash_in_sql))['total_cash'] ?? 0;

// Total Cash Expense
$cash_exp_sql = "
    SELECT COALESCE(SUM(amount), 0) AS total_cash_expense
    FROM expenses
    WHERE payment_method = 'cash'
";
$cash_out = mysqli_fetch_assoc(mysqli_query($conn, $cash_exp_sql))['total_cash_expense'] ?? 0;

// Net Cash
$net_cash = $cash_in - $cash_out;

// Bank Accounts Report
$bank_sql = "
    SELECT 
        ba.id,
        ba.account_title,
        (
            SELECT COALESCE(SUM(amount), 0)
            FROM account_transactions at
            WHERE at.account_id = ba.id
            AND at.account_type IN ('bank','card','upi')
            $where
        ) AS received,
        (
            SELECT COALESCE(SUM(e.amount), 0)
            FROM expenses e
            WHERE e.payment_method = 'bank' AND e.bank_account_id = ba.id
        ) AS spent
    FROM bank_accounts ba
    WHERE ba.is_active = 1
";
$banks = mysqli_query($conn, $bank_sql);
?>

<div class="page-wrapper">
  <div class="content">
    <div class="page-header d-flex justify-content-between align-items-center">
      <div class="page-title">
        <h4>Cash & Bank Report</h4>
        <h6>Summary of money received & spent</h6>
      </div>
    </div>

    <form method="get" class="mb-4 d-flex align-items-end gap-3">
      <div>
        <label class="form-label">Start Date</label>
        <input type="date" name="start_date" class="form-control" value="<?= htmlspecialchars($start_date) ?>" required>
      </div>
      <div>
        <label class="form-label">End Date</label>
        <input type="date" name="end_date" class="form-control" value="<?= htmlspecialchars($end_date) ?>" required>
      </div>
      <div>
        <button type="submit" class="btn btn-primary">Apply Filter</button>
      </div>
    </form>

    <div class="card">
      <div class="card-header"><strong>Cash Summary</strong></div>
      <div class="card-body">
        <p><strong>Total Cash Received:</strong> Rs <?= number_format($cash_in, 2) ?></p>
        <p><strong>Total Cash Spent:</strong> Rs <?= number_format($cash_out, 2) ?></p>
        <p><strong>Net Cash:</strong> <span class="text-success fw-bold">Rs <?= number_format($net_cash, 2) ?></span></p>
      </div>
    </div>

    <div class="card mt-4">
      <div class="card-header"><strong>Bank Accounts Summary</strong></div>
      <div class="card-body table-responsive">
        <table class="table table-bordered">
          <thead class="table-light">
            <tr>
              <th>Bank</th>
              <th>Received</th>
              <th>Spent</th>
              <th>Net Balance</th>
            </tr>
          </thead>
          <tbody>
            <?php while ($row = mysqli_fetch_assoc($banks)): ?>
              <tr>
                <td><?= htmlspecialchars($row['account_title']) ?></td>
                <td>Rs <?= number_format($row['received'], 2) ?></td>
                <td>Rs <?= number_format($row['spent'], 2) ?></td>
                <td class="fw-bold text-end text-<?= $row['received'] - $row['spent'] >= 0 ? 'success' : 'danger' ?>">
                    Rs <?= number_format($row['received'] - $row['spent'], 2) ?>
                </td>
              </tr>
            <?php endwhile; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</div>
