<?php
require_once 'db_connect.php';
require_once('tcpdf/tcpdf_main/tcpdf.php');

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

// FETCH DATA
$stmt = $conn->prepare("
    SELECT fc.*, pa.agreement_code, pa.commitment_amount, pa.commitment_currency,
           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();

// GROUP DATA
$grouped = [];
while ($row = $res->fetch_assoc()) {

    $partnerKey = $row['partner_id'] . '|' . $row['partner_name'] . '|' . $row['partner_code'];
    $agreementKey = $row['agreement_id'] . '|' . $row['agreement_code'];

    if (!isset($grouped[$partnerKey])) {
        $grouped[$partnerKey] = [];
    }

    if (!isset($grouped[$partnerKey][$agreementKey])) {
        $grouped[$partnerKey][$agreementKey] = [
            'agreement_code' => $row['agreement_code'],
            'commitment_amount' => $row['commitment_amount'],
            'currency' => $row['commitment_currency'],
            'payments' => [],
            'paid_sum' => 0
        ];
    }

    $grouped[$partnerKey][$agreementKey]['payments'][] = $row;
    $grouped[$partnerKey][$agreementKey]['paid_sum'] += $row['amount'];
}

$stmt->close();

// CREATE PDF
$pdf = new TCPDF();
$pdf->SetCreator('Church Management System');
$pdf->SetAuthor('Church Management System');
$pdf->SetTitle('Contribution Report');

// PDF SETTINGS
$pdf->SetMargins(10, 15, 10);
$pdf->AddPage();

$html = '
<h2 style="text-align:center; margin-bottom:4px;">CONTRIBUTIONS REPORT</h2>
<p style="text-align:center;">
<b>Period:</b> ' . $from . ' to ' . $to . '
</p>
<hr>
<br>
';

// BUILD CONTENT
foreach ($grouped as $partnerKey => $agreements) {

    list($pid, $pname, $pcode) = explode('|', $partnerKey);

    $html .= '
    <h3 style="color:#0d6efd;">' . $pname . ' (' . $pcode . ')</h3>
    ';

    foreach ($agreements as $agreement) {

        $html .= '
        <h4 style="margin-top:10px;">Agreement: 
            <span style="color:#333;">' . $agreement['agreement_code'] . '</span>
        </h4>

        <p>
            <b>Commitment:</b> ' . number_format($agreement['commitment_amount'], 2) . ' ' . $agreement['currency'] . '<br>
            <b>Total Paid in Period:</b> ' . number_format($agreement['paid_sum'], 2) . ' ' . $agreement['currency'] . '
        </p>

        <table border="1" cellpadding="4" cellspacing="0" width="100%">
            <thead>
                <tr style="background-color:#f1f3f5; font-weight:bold;">
                    <th>Date</th>
                    <th>Receipt</th>
                    <th>Amount</th>
                    <th>Description</th>
                </tr>
            </thead>
            <tbody>
        ';

        foreach ($agreement['payments'] as $pmt) {
            $html .= '
            <tr>
                <td>' . $pmt['contribution_date'] . '</td>
                <td>' . $pmt['receipt_number'] . '</td>
                <td>' . number_format($pmt['amount'], 2) . '</td>
                <td>' . $pmt['description'] . '</td>
            </tr>
            ';
        }

        $html .= '
            </tbody>
        </table>
        <br><br>
        ';
    }
}

$pdf->writeHTML($html, true, false, true, false, '');
$pdf->Output('contribution_report.pdf', 'I');
?>
