<?php
$generated_invoices = []; // To hold invoices for preview

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $class = $_POST['class'];
    $term = $_POST['term'];
    $session = $_POST['session'];
    $date_of_upload = date('Y-m-d');

    // ✅ initialize counters to avoid undefined variable errors
    $success_count = 0;
    $updated_count = 0;
    $skipped_count = 0;


    // Fetch students for the selected class
    $students_query = "SELECT reg_number, CONCAT(firstname, ' ', othername, ' ', lastname) AS student_name 
                        FROM students WHERE class = ? AND status = 'active'";
    $stmt_students = $connection->prepare($students_query);
    $stmt_students->bind_param('s', $class);
    $stmt_students->execute();
    $students_result = $stmt_students->get_result();

    // Fetch fee amount from fee_structure (✅ no student_type anymore)
    $fee_query = "SELECT amount FROM fee_structure 
                  WHERE class = ? AND term = ? AND session = ?";
    $stmt_fee = $connection->prepare($fee_query);
    $stmt_fee->bind_param('sss', $class, $term, $session);
    $stmt_fee->execute();
    $fee_result = $stmt_fee->get_result();
    $fee_row = $fee_result->fetch_assoc();
    $amount = $fee_row ? $fee_row['amount'] : 0.00;

    if ($amount == 0.00) {
        $_SESSION['msg'] = "No fee structure found for the selected criteria.";
        $_SESSION['type'] = "error";
    } else {
// Insert invoices into fee_payment
$insert_query = "INSERT INTO fee_payment 
    (reg_number, student_name, student_class, total_fee, fee_term, fee_session, amount_paid, student_balance, date_of_upload, payment_status) 
    VALUES (?, ?, ?, ?, ?, ?, 0.00, ?, ?, 'Owing')";
$stmt_insert = $connection->prepare($insert_query);

// ✅ prepare check query once
$check_query = "SELECT amount_paid FROM fee_payment 
                WHERE reg_number = ? AND student_class = ? AND fee_term = ? AND fee_session = ?";
$stmt_check = $connection->prepare($check_query);

while ($student = $students_result->fetch_assoc()) {
    $reg_number = $student['reg_number'];
    $student_name = $student['student_name'];

    // ✅ check if already exists
    $stmt_check->bind_param('ssss', $reg_number, $class, $term, $session);
    $stmt_check->execute();
    $result_check = $stmt_check->get_result();

    if ($result_check->num_rows > 0) {
        // invoice exists → update
        $row = $result_check->fetch_assoc();
        $amount_paid = $row['amount_paid'];
        $new_balance = $amount - $amount_paid;
        $payment_status = ($new_balance <= 0) ? "Paid" : "Owing";

        $update_query = "UPDATE fee_payment 
                         SET total_fee = ?, student_balance = ?, date_of_upload = ?, payment_status = ? 
                         WHERE reg_number = ? AND student_class = ? AND fee_term = ? AND fee_session = ?";
        $stmt_update = $connection->prepare($update_query);
        $stmt_update->bind_param('ddssssss', 
            $amount, 
            $new_balance, 
            $date_of_upload, 
            $payment_status, 
            $reg_number, 
            $class, 
            $term, 
            $session
        );
        if ($stmt_update->execute()) {
            $updated_count++;
        }
        $stmt_update->close();
    } else {
        // insert new record
        $stmt_insert->bind_param('sssdssds', 
            $reg_number, 
            $student_name, 
            $class, 
            $amount, 
            $term, 
            $session, 
            $amount, 
            $date_of_upload
        );
        if ($stmt_insert->execute()) {
            $success_count++;
        }
    }
}


        if ($success_count > 0 || $updated_count > 0) {
            $_SESSION['msg'] = "✅ $success_count new invoices generated, $updated_count updated, ⏭ $skipped_count skipped.";
            $_SESSION['type'] = "success";
        } else {
            $_SESSION['msg'] = "No invoices generated or updated.";
            $_SESSION['type'] = "warning";
        }

        $stmt_insert->close();
        $stmt_check->close();
    }

    $stmt_students->close();
    $stmt_fee->close();

    // 🔴 Redirect to avoid duplicate insertion on refresh
    header("Location: generate_fee_invoice");
    exit;
}

// Fetch distinct classes, terms, sessions for dropdowns (✅ removed student_types)
$classes = $connection->query("SELECT DISTINCT class FROM students ORDER BY class");
$terms = ["First Term", "Second Term", "Third Term"];
$sessions = $connection->query("SELECT DISTINCT session FROM fee_structure ORDER BY session");
?>


<div class="page-title">
    <div>
        <h1><i class="fa fa-money"></i>Generate Fee Invoice</h1>
        <p>Generate Fee Invoice by Class</p>
    </div>    
</div>

                <div class="card-header">
                    <h3 class="card-title">Invoice Details</h3>
                </div>
                <!-- form start -->
                <form method="POST" action="">
                    <div class="card-body">
                        <div class="form-group">
                            <label for="class">Select Class</label>
                            <select class="form-control" name="class" id="class" required>
                                <option value="">-- Select Class --</option>
                                <?php while ($row = $classes->fetch_assoc()): ?>
                                    <option value="<?php echo htmlspecialchars($row['class']); ?>">
                                        <?php echo htmlspecialchars($row['class']); ?>
                                    </option>
                                <?php endwhile; ?>
                            </select>
                        </div>

                        <div class="form-group">
                            <label for="term">Select Term</label>
                            <select class="form-control" name="term" id="term" required>
                                <option value="">-- Select Term --</option>
                                <?php foreach ($terms as $term): ?>
                                    <option value="<?php echo $term; ?>"><?php echo $term; ?></option>
                                <?php endforeach; ?>
                            </select>
                        </div>

                        <div class="form-group">
                            <label for="session">Select Session</label>
                            <select class="form-control" name="session" id="session" required>
                                <option value="">-- Select Session --</option>
                                <?php while ($row = $sessions->fetch_assoc()): ?>
                                    <option value="<?php echo htmlspecialchars($row['session']); ?>">
                                        <?php echo htmlspecialchars($row['session']); ?>
                                    </option>
                                <?php endwhile; ?>
                            </select>
                        </div>
                    </div>
                    <!-- /.card-body -->

                    <div class="card-footer">
                        <button type="submit" class="btn btn-primary">Generate Invoices</button>
                    </div>
                </form>
            </div>

            <?php if (!empty($generated_invoices)): ?>
            <div class="card card-info mt-3">
                <div class="card-header">
                    <h3 class="card-title">Generated Invoices Preview</h3>
                </div>
                <div class="card-body">
                    <table id="invoiceTable" class="table table-bordered table-striped">
                        <thead>
                            <tr>
                                <th>Reg Number</th>
                                <th>Student Name</th>
                                <th>Class</th>
                                <th>Term</th>
                                <th>Session</th>
                                <th>Amount</th>
                                <th>Date</th>
                                <th>Status</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($generated_invoices as $inv): ?>
                            <tr>
                                <td><?php echo htmlspecialchars($inv['reg_number']); ?></td>
                                <td><?php echo htmlspecialchars($inv['student_name']); ?></td>
                                <td><?php echo htmlspecialchars($inv['class']); ?></td>
                                <td><?php echo htmlspecialchars($inv['term']); ?></td>
                                <td><?php echo htmlspecialchars($inv['session']); ?></td>
                                <td><?php echo number_format($inv['amount'], 2); ?></td>
                                <td><?php echo htmlspecialchars($inv['date_of_upload']); ?></td>
                                <td><span class="badge badge-danger"><?php echo $inv['status']; ?></span></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>

            <!-- DataTable script -->
            <script>
                $(function () {
                    $("#invoiceTable").DataTable({
                        "responsive": true,
                        "lengthChange": true,
                        "autoWidth": false,
                        "pageLength": 10,
                        "buttons": ["copy", "csv", "excel", "pdf", "print"]
                    }).buttons().container().appendTo('#invoiceTable_wrapper .col-md-6:eq(0)');
                });
            </script>
            <?php endif; ?>

        </div><!-- /.container-fluid -->