<?php
session_start();
require_once("public/connection.php");
?>

<div class="page-title">
    <h1><i class="fa fa-file-invoice-dollar"></i> Fee Payments</h1>
</div>

<div class="card">
    <div class="panel panel-primary">
        <div class="panel-heading">Invoice & Payment Records</div>
        <div class="panel-body">
            <div class="table-responsive">
                <table class="table table-bordered table-striped" id="myTable">
                    <thead>
                        <tr>
                            <th>#</th>
                            <th>Reg No / Family</th>
                            <th>Student(s)</th>
                            <th>Class</th>
                            <th>Payment Type</th>
                            <th>Term</th>
                            <th>Session</th>
                            <th>Total Fee</th>
                            <th>Paid</th>
                            <th>Balance</th>
                            <th>Status</th>
                            <th>Date Paid</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php
                    $displayed_families = [];
                    $sql = "SELECT * FROM fee_payment ORDER BY payment_date DESC";
                    $result = $connection->query($sql);

                    while ($row = $result->fetch_assoc()):
                        $family_id = $row['family_id'];

                        if($family_id && in_array($family_id, $displayed_families)) continue;
                        if($family_id) $displayed_families[] = $family_id;

                        $students_list = '';
                        $classes_list = '';
                        $reg_display = '';
                        $total_fee = 0;
                        $total_paid = 0;
                        $balance = 0;
                        $payment_date = '';
                        $payment_type = '';

                        if($family_id){
                            // Family payment: fetch all students
                            $stmt = $connection->prepare("
                                SELECT s.reg_number, CONCAT(s.firstname,' ',COALESCE(s.othername,''),' ',s.lastname) AS student_name,
                                       s.class, fp.total_fee, fp.amount_paid, fp.payment_status, fp.payment_date
                                FROM fee_payment fp
                                LEFT JOIN students s ON s.reg_number = fp.reg_number
                                WHERE fp.family_id = ?
                            ");
                            $stmt->bind_param('i', $family_id);
                            $stmt->execute();
                            $res = $stmt->get_result();

                            $students_arr = [];
                            $classes_arr = [];
                            $reg_numbers = [];
                            $total_fee = 0;
                            $total_paid = 0;
                            $payment_dates = [];
                            $statuses = [];

                            while($s = $res->fetch_assoc()){
                                $students_arr[] = $s['student_name'];
                                $classes_arr[] = $s['class'];
                                $reg_numbers[] = $s['reg_number'];
                                $total_fee += floatval($s['total_fee']);
                                $total_paid += floatval($s['amount_paid']);
                                $payment_dates[] = $s['payment_date'];
                                $statuses[] = $s['payment_status'];
                            }
                            $stmt->close();

                            $students_list = implode('<br>', $students_arr);
                            $classes_list = implode('<br>', $classes_arr);
                            $reg_display = implode(', ', $reg_numbers);
                            $payment_type = 'Family';

                            $balance = max(0, $total_fee - $total_paid);

                            // Determine latest payment date if fully/partially paid
                            if($total_paid>0){
                                $latest_date = max(array_map('strtotime', $payment_dates));
                                $payment_date = date("d-M-Y H:i", $latest_date);
                            } else {
                                $payment_date = '';
                            }

                        } else {
                            // Individual payment
                            $students_list = $row['student_name'];
                            $classes_list = $row['student_class'];
                            $reg_display = $row['reg_number'];
                            $total_fee = floatval($row['total_fee']);
                            $total_paid = floatval($row['amount_paid']);
                            $balance = max(0, $total_fee - $total_paid);
                            $payment_type = 'Individual';
                            $payment_date = $total_paid>0 ? date("d-M-Y H:i", strtotime($row['payment_date'])) : '';
                        }

                        // Status badge colors
                        if($balance==0){
                            $badge = '<span class="badge" style="background-color:green;color:white;">Paid</span>';
                        } elseif($total_paid>0){
                            $badge = '<span class="badge" style="background-color:purple;color:white;">Part Payment</span>';
                        } else {
                            $badge = '<span class="badge" style="background-color:red;color:white;">Pending</span>';
                        }
                    ?>
                        <tr>
                            <td><?= $row['id'] ?></td>
                            <td><?= htmlspecialchars($reg_display) ?></td>
                            <td><?= $students_list ?></td>
                            <td style="white-space: normal;"><?= $classes_list ?></td>
                            <td><?= $payment_type ?></td>
                            <td><?= htmlspecialchars($row['fee_term']) ?></td>
                            <td><?= htmlspecialchars($row['fee_session']) ?></td>
                            <td><?= number_format($total_fee,2) ?></td>
                            <td><?= number_format($total_paid,2) ?></td>
                            <td><?= number_format($balance,2) ?></td>
                            <td><?= $badge ?></td>
                            <td><?= $payment_date ?></td>
                            <td>
                                <a href="print_fees.php?custom_ref=<?= urlencode($row['custom_ref']) ?>" target="_blank" class="btn btn-primary btn-sm">
                                    <i class="fa fa-print"></i> Print PDF
                                </a>
                            </td>
                        </tr>
                    <?php endwhile; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<style>
.table td {
    white-space: normal !important;
}
.badge {
    font-size: 0.9em;
    padding: 0.35em 0.7em;
}
</style>