<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
require_once "labClass.php";
$con = new labClass(); 

$records = $con->cashExR();

// Group records by date
$grouped = [];
foreach ($records as $r) {
    $grouped[$r['sdate']][] = $r;
}

// --- keep the report ordering as you had (descending for display) ---
krsort($grouped);

// ---------- PRECOMPUTE opening balances in ASCENDING order ----------
// Make a copy and sort ascending so we can compute "opening = accumulated before that date"
$groupedAsc = $grouped;
ksort($groupedAsc);

$openingMap = [];     // openingMap[date] = accumulated balance BEFORE that date
$cumulative = 0.0;    // running cumulative from oldest -> newest

foreach ($groupedAsc as $d => $rowsAsc) {
    // opening for this date is the cumulative up to (but not including) this date
    $openingMap[$d] = $cumulative;

    // compute this date's net (cash - expense)
    $totalCashAsc = 0;
    $totalExpenseAsc = 0;
    foreach ($rowsAsc as $rA) {
        if ($rA['category'] === 'Cash') $totalCashAsc += $rA['amount'];
        else $totalExpenseAsc += $rA['amount'];
    }
    $dailyNetAsc = $totalCashAsc - $totalExpenseAsc;

    // update cumulative to include this date for the next date's opening
    $cumulative += $dailyNetAsc;
}
// Now $openingMap has correct openings (based on previous dates chronological order)

// Overall totals (kept as you had)
$overallCash = 0;
$overallExpense = 0;
?>

<div class="mt-4">
  <h5 class="text-primary fw-bold text-center mb-3">CASH & EXPENSES REPORT</h5>

  <table class="table table-bordered table-striped">
    <thead class="table-primary">
      <tr class="text-center">
        <th width="10%">Date</th>
        <th width="10%">Category</th>
        <th width="20%">Source / Expense</th>
        <th width="35%">Description</th>
        <th width="15%" class="text-end">Amount (TZS)</th>
        
          <th width="10%">Action</th>
         
      </tr>
    </thead>
    <tbody>
      <?php foreach ($grouped as $date => $rows): ?>
        <?php
          // Sort Cash first, Expense second (unchanged)
          usort($rows, function($a, $b) {
              return strcmp($a['category'], $b['category']);
          });

          // Compute date totals
          $totalCash = 0;
          $totalExpense = 0;
          foreach ($rows as $r) {
              if ($r['category'] === 'Cash') $totalCash += $r['amount'];
              else $totalExpense += $r['amount'];
          }

          // Daily net for this date only
          $dateBalance = $totalCash - $totalExpense;

          // Opening balance — use precomputed map (accumulated of previous chronological dates)
          $openingBalance = isset($openingMap[$date]) ? $openingMap[$date] : 0.0;

          // Accumulated (to date) = opening + today's net
          $accumulatedToDate = $openingBalance + $dateBalance;

          // Update overall totals (unchanged)
          $overallCash += $totalCash;
          $overallExpense += $totalExpense;
        ?>

        <!-- Date Header Row -->
        <tr style="background-color:#e3f2fd; font-weight:bold;">
          <td colspan="6" class="text-primary">
            DATE: <?= htmlspecialchars($date) ?><sub id="dis"></sub>
          </td>
        </tr>

        <!-- Opening Balance Row (added, uses chronological accumulation) -->
        <tr class="fw-bold" style="background:#fff9c4;">
          <td colspan="4" class="text-end text-dark">Opening Balance:</td>
          <td class="text-end"><?= number_format($openingBalance, 2) ?></td>
          <td></td>
        </tr>

        <!-- Transactions -->
        <?php $c=0; foreach ($rows as $r):$c++; ?>
        <tr>
          <td> </td>
          <input type="hidden" name ="id<?= $c ?>" id="id<?= $c ?>" value="<?= $r['recID'] ?>"/>
          <td><?= htmlspecialchars($r['category']) ?></td>
          <td><?= htmlspecialchars($r['source']) ?></td>
          <td> <sup id="dis<?=$c?>"> </sup><br/>
              <?= htmlspecialchars($r['description']) ?></td>
          <td class="text-end"><?= number_format($r['amount'], 2) ?></td>
        
          <td>
             
            <button class="btn btn-danger btn-sm" onclick=" var id=$('#id<?= $c ?>').val(); if (id!='') {$.post('cashExdel.php', {id:id}, function(data) {$('#dis<?=$c?>').html(data).show(); });
  }    ">Delete</button>
          </td>
          
        </tr>
        <?php endforeach; ?>

        <!-- Date Totals -->
        <tr class="fw-bold text-end table-info">
          <td colspan="3" class="text-center">Subtotal (<?= htmlspecialchars($date) ?>)</td>
          <td colspan="2">
            <div><span class="text-success">Total Cash:</span> <?= number_format($totalCash, 2) ?></div>
            <div><span class="text-danger">Total Expenses:</span> <?= number_format($totalExpense, 2) ?></div>

            <!-- Net Balance: now shows accumulated up to this date (opening + today's net) -->
            <div><span class="text-primary">Net Balance (To Date):</span> <?= number_format($accumulatedToDate, 2) ?></div>
          </td>
           <td>
           
        </td>
        </tr>

      <?php endforeach; ?>

      <!-- Overall Total -->
      <tr class="fw-bold text-end table-primary">
        <td colspan="3" class="text-center">OVERALL TOTAL</td>
        <td colspan="2">
          <div><span class="text-success">Total Cash:</span> <?= number_format($overallCash, 2) ?></div>
          <div><span class="text-danger">Total Expenses:</span> <?= number_format($overallExpense, 2) ?></div>
          <div><span class="text-primary">Net Balance:</span> <?= number_format($overallCash - $overallExpense, 2) ?></div>
        </td>
          <td>
           
        </td>
      </tr>
    </tbody>
  </table>
</div>

 
<script>
function deleteRecord(id) {
  if (confirm('Are you sure you want to delete this record?')) {
    $.post('cashExdel.php', {id: id}, function(data) {
       $('#dis').html(data).show();
    });
  }
}
</script>
