<?php
// contribution_by_date_range_for_all.php
require_once 'db_connect.php';

$from = isset($_GET['from']) ? $_GET['from'] : date('Y-01-01');
$to = isset($_GET['to']) ? $_GET['to'] : date('Y-m-d');

$stmt = $conn->prepare("
    SELECT fc.*, pa.agreement_code, pa.commitment_amount, pa.commitment_currency, pa.start_date, pa.end_date, pa.commitment_frequency, p.partner_name, p.partner_code
    FROM Financial_Contributions fc
    LEFT JOIN Partnership_Agreements pa ON fc.agreement_id = pa.agreement_id
    LEFT JOIN Partners p ON fc.partner_id = p.partner_id
    WHERE fc.contribution_date BETWEEN ? AND ?
    ORDER BY p.partner_name, pa.created_at, fc.contribution_date
");
$stmt->bind_param("ss", $from, $to);
$stmt->execute();
$res = $stmt->get_result();

$grouped = [];
while ($row = $res->fetch_assoc()) {
    $partnerKey = $row['partner_id'] . '|' . $row['partner_name'] . '|' . $row['partner_code'];
    $agreementKey = $row['agreement_id'] . '|' . $row['agreement_code'] . '|' . $row['commitment_amount'];
    if (!isset($grouped[$partnerKey])) $grouped[$partnerKey] = [];
    if (!isset($grouped[$partnerKey][$agreementKey])) {
        $grouped[$partnerKey][$agreementKey] = [
            'agreement_code' => $row['agreement_code'],
            'commitment_amount' => floatval($row['commitment_amount']),
            'currency' => $row['commitment_currency'],
            'payments' => [],
            'paid_sum' => 0.0
        ];
    }
    $grouped[$partnerKey][$agreementKey]['payments'][] = $row;
    $grouped[$partnerKey][$agreementKey]['paid_sum'] += floatval($row['amount']);
}
$stmt->close();
?>
<!doctype html><html><head><meta charset="utf-8"><title>Contributions by Date</title><link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet"></head>
<body>
<div class="container mt-4">
  <h3>Contributions between <?php echo htmlspecialchars($from); ?> and <?php echo htmlspecialchars($to); ?></h3>
  <form class="row g-2 mb-3">
    <div class="col-auto"><input type="date" name="from" class="form-control" value="<?php echo htmlspecialchars($from); ?>"></div>
    <div class="col-auto"><input type="date" name="to" class="form-control" value="<?php echo htmlspecialchars($to); ?>"></div>
    <div class="col-auto"><button class="btn btn-primary" type="submit">Filter</button></div>
  </form>

  <?php foreach ($grouped as $partnerKey => $agreements): 
        list($pid,$pname,$pcode) = explode('|', $partnerKey);
  ?>
    <div class="mb-3">
      <h5><?php echo htmlspecialchars($pname) . " ({$pcode})"; ?></h5>
      <?php foreach ($agreements as $ak => $g): ?>
        <div class="card mb-2">
          <div class="card-body">
            <h6>Agreement: <?php echo htmlspecialchars($g['agreement_code']); ?> — <?php echo number_format($g['commitment_amount'],2); ?> <?php echo htmlspecialchars($g['currency']); ?></h6>
            <p>Paid in this period: <strong><?php echo number_format($g['paid_sum'],2); ?></strong></p>
            <table class="table table-sm">
              <thead><tr><th>Date</th><th>Receipt</th><th>Amount</th><th>Description</th></tr></thead>
              <tbody>
              <?php foreach ($g['payments'] as $pmt): ?>
                <tr>
                  <td><?php echo htmlspecialchars($pmt['contribution_date']); ?></td>
                  <td><?php echo htmlspecialchars($pmt['receipt_number']); ?></td>
                  <td><?php echo number_format($pmt['amount'],2); ?></td>
                  <td><?php echo htmlspecialchars($pmt['description']); ?></td>
                </tr>
              <?php endforeach; ?>
              </tbody>
            </table>
          </div>
        </div>
      <?php endforeach; ?>
    </div>
  <?php endforeach; ?>
</div>
</body></html>
