<?php
session_start();
ob_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Include configuration
include "../include/config.php";

// Authentication check
if (!isset($_SESSION['email'])) {
    header("location:../index.php");
    exit();
}

$userlogin = $_SESSION['email'];
$userrole = $_SESSION['role'];
$intake = $_SESSION['department'];

// Include PHPMailer with proper namespacing
require '../email/src/Exception.php';
require '../email/src/PHPMailer.php';
require '../email/src/SMTP.php';

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\SMTP;
use PHPMailer\PHPMailer\Exception;

// Handle Add/Update Invoice
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_POST['save_balance'])) {
        // Add new invoice
        $fee_type = $conn->real_escape_string($_POST['fee_type']);
        $amount   = floatval($_POST['amount']);
        $intake   = $conn->real_escape_string($_POST['intake']);
        $period   = $conn->real_escape_string($_POST['period']);
        $year     = $conn->real_escape_string($_POST['year']);
        $program  = $conn->real_escape_string($_POST['program']);
        $mode     = $conn->real_escape_string($_POST['mode']);
        
        // Validate inputs
        if ($amount <= 0 || empty($intake) || empty($program) || empty($period)) {
            header("Location: balance.php?error=Invalid input data");
            exit();
        }
        
        // Prepare statement for security
        $stmt = $conn->prepare("INSERT INTO balances (fee_type, amount, intake, period, year, program, mode) VALUES (?, ?, ?, ?, ?, ?, ?)");
        $stmt->bind_param("sdsssss", $fee_type, $amount, $intake, $period, $year, $program, $mode);
        
        if ($stmt->execute()) {
            sendEmailNotifications($conn, $program, $intake, $mode, $amount, $period, $year);
            header("Location: balance.php?success=Invoice has been created successfully");
            exit();
        } else {
            header("Location: balance.php?error=Failed to create invoice: " . $conn->error);
            exit();
        }
    }
    
    if (isset($_POST['update_balance'])) {
        // Update existing invoice
        $id = intval($_POST['id']);
        $fee_type = $conn->real_escape_string($_POST['fee_type']);
        $amount   = floatval($_POST['amount']);
        $intake   = $conn->real_escape_string($_POST['intake']);
        $period   = $conn->real_escape_string($_POST['period']);
        $year     = $conn->real_escape_string($_POST['year']);
        $program  = $conn->real_escape_string($_POST['program']);
        $mode     = $conn->real_escape_string($_POST['mode']);
        
        // Validate inputs
        if ($amount <= 0 || empty($intake) || empty($program) || empty($period) || $id <= 0) {
            header("Location: balance.php?error=Invalid input data");
            exit();
        }
        
        // Prepare statement for security
        $stmt = $conn->prepare("UPDATE balances SET fee_type=?, amount=?, intake=?, period=?, year=?, program=?, mode=? WHERE id=?");
        $stmt->bind_param("sdsssssi", $fee_type, $amount, $intake, $period, $year, $program, $mode, $id);
        
        if ($stmt->execute()) {
            // Send update notification if needed
            sendUpdateNotification($conn, $id, $program, $intake, $mode, $amount, $period, $year);
            header("Location: balance.php?success=Invoice has been updated successfully");
            exit();
        } else {
            header("Location: balance.php?error=Failed to update invoice: " . $conn->error);
            exit();
        }
    }
}

// Function to send email notifications
function sendEmailNotifications($conn, $program, $intake, $mode, $amount, $period, $year) {
    // Get admin email settings
    $adminQuery = "SELECT * FROM admin LIMIT 1";
    $adminResult = mysqli_query($conn, $adminQuery);
    
    if ($adminResult && mysqli_num_rows($adminResult) > 0) {
        $admin = mysqli_fetch_assoc($admin);
        
        // Get students
        $studentStmt = $conn->prepare("SELECT email, studentname FROM students WHERE program=? AND intake=? AND mode=?");
        $studentStmt->bind_param("sss", $program, $intake, $mode);
        $studentStmt->execute();
        $students = $studentStmt->get_result();
        
        if ($students && $students->num_rows > 0) {
            while ($row = $students->fetch_assoc()) {
                $studentEmail = $row['email'];
                $studentName  = $row['studentname'];
                
                // Send email
                $mail = new PHPMailer(true);
                try {
                    // Server settings
                    $mail->isSMTP();
                    $mail->Host       = $admin['smtp_host'];
                    $mail->SMTPAuth   = (bool)$admin['smtp_auth'];
                    $mail->Username   = $admin['smtp_username'];
                    $mail->Password   = $admin['smtp_password'];
                    $mail->SMTPSecure = $admin['smtp_secure'];
                    $mail->Port       = (int)$admin['smtp_port'];
                    
                    // Recipients
                    $mail->setFrom($admin['smtp_username'], $admin['sname']);
                    $mail->addAddress($studentEmail, $studentName);
                    
                    // Content
                    $mail->isHTML(true);
                    $mail->Subject = "New Invoice Posted to Your Student Portal";
                    $mail->Body    = "
                        <div style='font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto;'>
                            <h2 style='color: #2c3e50;'>New Invoice Notification</h2>
                            <p>Dear $studentName,</p>
                            <p>A new invoice has been posted to your student portal:</p>
                            <div style='background: #f8f9fa; padding: 15px; border-radius: 5px; margin: 15px 0;'>
                                <p><strong>Amount:</strong> K" . number_format($amount, 2) . "</p>
                                <p><strong>Semester:</strong> $period</p>
                                <p><strong>Year:</strong> $year</p>
                                <p><strong>Program:</strong> $program</p>
                                <p><strong>Intake:</strong> $intake</p>
                            </div>
                            <p>Please log in to your student portal to check your updated balance and make payments.</p>
                            <hr>
                            <p>Best regards,<br>
                            <strong>{$admin['sname']}</strong></p>
                        </div>
                    ";
                    
                    $mail->send();
                } catch (Exception $e) {
                    error_log("Email error for $studentEmail: " . $mail->ErrorInfo);
                }
            }
        }
    }
}

// Function to send update notifications
function sendUpdateNotification($conn, $id, $program, $intake, $mode, $amount, $period, $year) {
    // Similar to sendEmailNotifications but for updates
    $adminQuery = "SELECT * FROM admin LIMIT 1";
    $adminResult = mysqli_query($conn, $adminQuery);
    
    if ($adminResult && mysqli_num_rows($adminResult) > 0) {
        $admin = mysqli_fetch_assoc($adminResult);
        
        $studentStmt = $conn->prepare("SELECT email, studentname FROM students WHERE program=? AND intake=? AND mode=?");
        $studentStmt->bind_param("sss", $program, $intake, $mode);
        $studentStmt->execute();
        $students = $studentStmt->get_result();
        
        if ($students && $students->num_rows > 0) {
            while ($row = $students->fetch_assoc()) {
                $studentEmail = $row['email'];
                $studentName  = $row['studentname'];
                
                $mail = new PHPMailer(true);
                try {
                    $mail->isSMTP();
                    $mail->Host       = $admin['smtp_host'];
                    $mail->SMTPAuth   = (bool)$admin['smtp_auth'];
                    $mail->Username   = $admin['smtp_username'];
                    $mail->Password   = $admin['smtp_password'];
                    $mail->SMTPSecure = $admin['smtp_secure'];
                    $mail->Port       = (int)$admin['smtp_port'];
                    
                    $mail->setFrom($admin['smtp_username'], $admin['sname']);
                    $mail->addAddress($studentEmail, $studentName);
                    
                    $mail->isHTML(true);
                    $mail->Subject = "Invoice Updated in Your Student Portal";
                    $mail->Body    = "
                        <div style='font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto;'>
                            <h2 style='color: #2c3e50;'>Invoice Update Notification</h2>
                            <p>Dear $studentName,</p>
                            <p>An invoice in your student portal has been updated:</p>
                            <div style='background: #f8f9fa; padding: 15px; border-radius: 5px; margin: 15px 0;'>
                                <p><strong>Updated Amount:</strong> K" . number_format($amount, 2) . "</p>
                                <p><strong>Semester:</strong> $period</p>
                                <p><strong>Year:</strong> $year</p>
                                <p><strong>Program:</strong> $program</p>
                                <p><strong>Intake:</strong> $intake</p>
                            </div>
                            <p>Please log in to your student portal to view the updated invoice.</p>
                            <hr>
                            <p>Best regards,<br>
                            <strong>{$admin['sname']}</strong></p>
                        </div>
                    ";
                    
                    $mail->send();
                } catch (Exception $e) {
                    error_log("Update email error for $studentEmail: " . $mail->ErrorInfo);
                }
            }
        }
    }
}

// Fetch invoice data for editing
$editData = null;
if (isset($_GET['edit_id'])) {
    $edit_id = intval($_GET['edit_id']);
    $editStmt = $conn->prepare("SELECT * FROM balances WHERE id = ?");
    $editStmt->bind_param("i", $edit_id);
    $editStmt->execute();
    $editResult = $editStmt->get_result();
    $editData = $editResult->fetch_assoc();
}

// Fetch dropdown data
$intakes  = $conn->query("SELECT DISTINCT intake FROM intake ORDER BY intake DESC");
$programs = $conn->query("SELECT DISTINCT program FROM programs ORDER BY program");
$modes    = $conn->query("SELECT DISTINCT mode FROM students WHERE mode IS NOT NULL ORDER BY mode");

// Filter parameters
$filterIntake   = isset($_GET['intake']) ? $conn->real_escape_string($_GET['intake']) : '';
$filterProgram  = isset($_GET['program']) ? $conn->real_escape_string($_GET['program']) : '';
$filterMode     = isset($_GET['mode']) ? $conn->real_escape_string($_GET['mode']) : '';

// Build WHERE clause safely
$whereClause = [];
$params = [];
$types = '';

if (!empty($filterIntake)) {
    $whereClause[] = "intake = ?";
    $params[] = $filterIntake;
    $types .= 's';
}

if (!empty($filterProgram)) {
    $whereClause[] = "program = ?";
    $params[] = $filterProgram;
    $types .= 's';
}

if (!empty($filterMode)) {
    $whereClause[] = "mode = ?";
    $params[] = $filterMode;
    $types .= 's';
}

$whereSQL = '';
if (!empty($whereClause)) {
    $whereSQL = "WHERE " . implode(" AND ", $whereClause);
}

// Fetch balances with prepared statement
$balanceQuery = "SELECT * FROM balances $whereSQL ORDER BY id DESC";
if ($stmt = $conn->prepare($balanceQuery)) {
    if (!empty($params)) {
        $stmt->bind_param($types, ...$params);
    }
    $stmt->execute();
    $balances = $stmt->get_result();
}

// Fetch grouped report
$reportQuery = "SELECT intake, program, mode, SUM(amount) AS total_amount 
                FROM balances $whereSQL 
                GROUP BY intake, program, mode 
                ORDER BY intake DESC, program";
if ($stmt = $conn->prepare($reportQuery)) {
    if (!empty($params)) {
        $stmt->bind_param($types, ...$params);
    }
    $stmt->execute();
    $report = $stmt->get_result();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Invoice Management - Student Portal</title>
    
    <!-- Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
    
    <!-- Font Awesome -->
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    
    <!-- Google Fonts -->
    <link rel="preconnect" href="https://fonts.googleapis.com">
    <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
    <link href="https://fonts.googleapis.com/css2?family=Montserrat:wght@300;400;500;600&family=Source+Sans+Pro:wght@300;400;600&display=swap" rel="stylesheet">
    
    <!-- Custom CSS -->
    <style>
        :root {
            --primary: #2c3e50;
            --secondary: #3498db;
            --success: #27ae60;
            --danger: #e74c3c;
            --warning: #f39c12;
            --light: #ecf0f1;
        }
        
        body {
            background-color: #f8f9fa;
            font-family: 'Source Sans Pro', sans-serif;
            padding-top: 56px;
        }
        
        .navbar {
            background: var(--primary) !important;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        
        .card {
            border: none;
            border-radius: 10px;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
            transition: transform 0.3s;
        }
        
        .card:hover {
            transform: translateY(-5px);
        }
        
        .card-header {
            background: var(--primary);
            color: white;
            border-radius: 10px 10px 0 0 !important;
            font-weight: 600;
        }
        
        .table th {
            background-color: var(--primary);
            color: white;
            font-weight: 600;
            border: none;
        }
        
        .table td {
            vertical-align: middle;
        }
        
        .btn-primary {
            background-color: var(--primary);
            border-color: var(--primary);
        }
        
        .btn-primary:hover {
            background-color: #1a252f;
            border-color: #1a252f;
        }
        
        .btn-success {
            background-color: var(--success);
            border-color: var(--success);
        }
        
        .btn-warning {
            background-color: var(--warning);
            border-color: var(--warning);
        }
        
        .modal-header {
            background: var(--primary);
            color: white;
        }
        
        .sidebar {
            position: fixed;
            top: 56px;
            left: 0;
            width: 250px;
            height: calc(100vh - 56px);
            background: white;
            box-shadow: 3px 0 10px rgba(0,0,0,0.1);
            z-index: 1000;
            overflow-y: auto;
        }
        
        .content {
            margin-left: 250px;
            padding: 20px;
        }
        
        @media (max-width: 768px) {
            .sidebar {
                width: 100%;
                position: static;
                height: auto;
            }
            
            .content {
                margin-left: 0;
            }
        }
        
        .alert {
            border: none;
            border-radius: 8px;
        }
        
        .stat-card {
            padding: 20px;
            border-radius: 10px;
            color: white;
            margin-bottom: 20px;
        }
        
        .stat-card i {
            font-size: 2.5rem;
            margin-bottom: 10px;
        }
        
        .total-amount {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
        }
        
        .total-invoices {
            background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%);
        }
        
        .total-programs {
            background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%);
        }
        
        .action-buttons {
            display: flex;
            gap: 5px;
        }
        
        .action-buttons .btn {
            width: 35px;
            height: 35px;
            display: flex;
            align-items: center;
            justify-content: center;
            padding: 0;
        }
    </style>
</head>
<body>
    <!-- Navigation -->
    <?php include "../include/nav.php"; ?>
    
    <!-- Sidebar -->
    <div class="sidebar d-none d-md-block">
        <?php include "../include/nav.php"; ?>
    </div>
    
    <!-- Main Content -->
    <div class="content">
        <div class="container-fluid">
            <!-- Page Header -->
            <div class="d-flex justify-content-between align-items-center mb-4">
                <h1 class="h3 mb-0 text-primary">
                    <i class="fas fa-file-invoice-dollar me-2"></i>Invoice Management
                </h1>
                <div>
                    <button class="btn btn-success me-2" data-bs-toggle="modal" data-bs-target="#addInvoiceModal">
                        <i class="fas fa-plus-circle me-1"></i> Add Invoice
                    </button>
                    <a href="dashboard.php" class="btn btn-outline-primary">
                        <i class="fas fa-tachometer-alt me-1"></i> Dashboard
                    </a>
                </div>
            </div>
            
            <!-- Statistics Cards -->
            <div class="row mb-4">
                <div class="col-md-4">
                    <div class="stat-card total-amount">
                        <i class="fas fa-money-bill-wave"></i>
                        <h3>
                            <?php
                            $total = $conn->query("SELECT SUM(amount) as total FROM balances")->fetch_assoc()['total'];
                            echo 'K' . number_format($total ?? 0, 2);
                            ?>
                        </h3>
                        <p>Total Invoice Amount</p>
                    </div>
                </div>
                <div class="col-md-4">
                    <div class="stat-card total-invoices">
                        <i class="fas fa-file-invoice"></i>
                        <h3>
                            <?php
                            $count = $conn->query("SELECT COUNT(*) as count FROM balances")->fetch_assoc()['count'];
                            echo number_format($count ?? 0);
                            ?>
                        </h3>
                        <p>Total Invoices</p>
                    </div>
                </div>
                <div class="col-md-4">
                    <div class="stat-card total-programs">
                        <i class="fas fa-graduation-cap"></i>
                        <h3>
                            <?php
                            $progs = $conn->query("SELECT COUNT(DISTINCT program) as count FROM balances")->fetch_assoc()['count'];
                            echo number_format($progs ?? 0);
                            ?>
                        </h3>
                        <p>Programs</p>
                    </div>
                </div>
            </div>
            
            <!-- Alerts -->
            <?php if (isset($_GET['error'])): ?>
            <div class="alert alert-danger alert-dismissible fade show" role="alert">
                <i class="fas fa-exclamation-circle me-2"></i>
                <?php echo htmlspecialchars($_GET['error']); ?>
                <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
            </div>
            <?php endif; ?>
            
            <?php if (isset($_GET['success'])): ?>
            <div class="alert alert-success alert-dismissible fade show" role="alert">
                <i class="fas fa-check-circle me-2"></i>
                <?php echo htmlspecialchars($_GET['success']); ?>
                <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
            </div>
            <?php endif; ?>
            
            <!-- Filter Section -->
            <div class="card mb-4">
                <div class="card-header">
                    <h5 class="mb-0"><i class="fas fa-filter me-2"></i>Filter Invoices</h5>
                </div>
                <div class="card-body">
                    <form method="get" class="row g-3">
                        <div class="col-md-3">
                            <label class="form-label">Intake</label>
                            <select name="intake" class="form-select">
                                <option value="">All Intakes</option>
                                <?php while($row = $intakes->fetch_assoc()): ?>
                                <option value="<?= htmlspecialchars($row['intake']); ?>" 
                                    <?= ($filterIntake == $row['intake']) ? 'selected' : '' ?>>
                                    <?= htmlspecialchars($row['intake']); ?>
                                </option>
                                <?php endwhile; ?>
                            </select>
                        </div>
                        
                        <div class="col-md-3">
                            <label class="form-label">Program</label>
                            <select name="program" class="form-select">
                                <option value="">All Programs</option>
                                <?php while($row = $programs->fetch_assoc()): ?>
                                <option value="<?= htmlspecialchars($row['program']); ?>" 
                                    <?= ($filterProgram == $row['program']) ? 'selected' : '' ?>>
                                    <?= htmlspecialchars($row['program']); ?>
                                </option>
                                <?php endwhile; ?>
                            </select>
                        </div>
                        
                        <div class="col-md-3">
                            <label class="form-label">Mode</label>
                            <select name="mode" class="form-select">
                                <option value="">All Modes</option>
                                <?php while($row = $modes->fetch_assoc()): ?>
                                <option value="<?= htmlspecialchars($row['mode']); ?>" 
                                    <?= ($filterMode == $row['mode']) ? 'selected' : '' ?>>
                                    <?= htmlspecialchars($row['mode']); ?>
                                </option>
                                <?php endwhile; ?>
                            </select>
                        </div>
                        
                        <div class="col-md-3 d-flex align-items-end">
                            <button type="submit" class="btn btn-primary me-2">
                                <i class="fas fa-search me-1"></i> Filter
                            </button>
                            <a href="balance.php" class="btn btn-outline-secondary">
                                <i class="fas fa-redo me-1"></i> Clear
                            </a>
                        </div>
                    </form>
                </div>
            </div>
            
            <!-- Invoices Table -->
            <div class="card mb-4">
                <div class="card-header d-flex justify-content-between align-items-center">
                    <h5 class="mb-0"><i class="fas fa-list me-2"></i>All Invoices</h5>
                    <span class="badge bg-primary">
                        <?php echo $balances->num_rows; ?> records
                    </span>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-hover">
                            <thead>
                                <tr>
                                    <th>#</th>
                                    <th>Fee Type</th>
                                    <th>Amount</th>
                                    <th>Intake</th>
                                    <th>Program</th>
                                    <th>Semester</th>
                                    <th>Year</th>
                                    <th>Mode</th>
                                    <th>Created</th>
                                    <th>Actions</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php if ($balances->num_rows > 0): ?>
                                    <?php $i = 1; while($row = $balances->fetch_assoc()): ?>
                                    <tr>
                                        <td><?= $i++; ?></td>
                                        <td><span class="badge bg-info"><?= htmlspecialchars($row['fee_type']); ?></span></td>
                                        <td><strong>K<?= number_format($row['amount'], 2); ?></strong></td>
                                        <td><?= htmlspecialchars($row['intake']); ?></td>
                                        <td><?= htmlspecialchars($row['program']); ?></td>
                                        <td><span class="badge bg-secondary">Sem <?= htmlspecialchars($row['period']); ?></span></td>
                                        <td><?= htmlspecialchars($row['year']); ?></td>
                                        <td><span class="badge bg-dark"><?= htmlspecialchars($row['mode']); ?></span></td>
                                        <td><?= date('M d, Y', strtotime($row['created_at'] ?? 'now')); ?></td>
                                        <td>
                                            <div class="action-buttons">
                                                <button class="btn btn-sm btn-warning edit-btn" 
                                                        data-id="<?= $row['id']; ?>"
                                                        data-fee-type="<?= htmlspecialchars($row['fee_type']); ?>"
                                                        data-amount="<?= $row['amount']; ?>"
                                                        data-intake="<?= htmlspecialchars($row['intake']); ?>"
                                                        data-period="<?= htmlspecialchars($row['period']); ?>"
                                                        data-year="<?= htmlspecialchars($row['year']); ?>"
                                                        data-program="<?= htmlspecialchars($row['program']); ?>"
                                                        data-mode="<?= htmlspecialchars($row['mode']); ?>"
                                                        title="Edit">
                                                    <i class="fas fa-edit"></i>
                                                </button>
                                                <a href="delete_balance.php?id=<?= $row['id']; ?>" 
                                                   class="btn btn-sm btn-danger" 
                                                   onclick="return confirm('Are you sure you want to delete this invoice? This action cannot be undone.')"
                                                   title="Delete">
                                                    <i class="fas fa-trash"></i>
                                                </a>
                                            </div>
                                        </td>
                                    </tr>
                                    <?php endwhile; ?>
                                <?php else: ?>
                                    <tr>
                                        <td colspan="10" class="text-center py-4">
                                            <i class="fas fa-inbox fa-3x text-muted mb-3"></i>
                                            <h5>No invoices found</h5>
                                            <p class="text-muted">Create your first invoice using the button above</p>
                                        </td>
                                    </tr>
                                <?php endif; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
            
            <!-- Summary Report -->
            <div class="card">
                <div class="card-header">
                    <h5 class="mb-0"><i class="fas fa-chart-bar me-2"></i>Summary Report</h5>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-hover">
                            <thead>
                                <tr>
                                    <th>#</th>
                                    <th>Intake</th>
                                    <th>Program</th>
                                    <th>Mode</th>
                                    <th>Total Amount</th>
                                    <th>Average</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php if ($report->num_rows > 0): ?>
                                    <?php $i = 1; while($row = $report->fetch_assoc()): ?>
                                    <tr>
                                        <td><?= $i++; ?></td>
                                        <td><?= htmlspecialchars($row['intake']); ?></td>
                                        <td><?= htmlspecialchars($row['program']); ?></td>
                                        <td><?= htmlspecialchars($row['mode']); ?></td>
                                        <td><strong class="text-success">K<?= number_format($row['total_amount'], 2); ?></strong></td>
                                        <td>
                                            <?php
                                            $avgQuery = $conn->prepare("SELECT AVG(amount) as avg_amount FROM balances WHERE intake=? AND program=? AND mode=?");
                                            $avgQuery->bind_param("sss", $row['intake'], $row['program'], $row['mode']);
                                            $avgQuery->execute();
                                            $avgResult = $avgQuery->get_result()->fetch_assoc();
                                            echo 'K' . number_format($avgResult['avg_amount'] ?? 0, 2);
                                            ?>
                                        </td>
                                    </tr>
                                    <?php endwhile; ?>
                                <?php else: ?>
                                    <tr>
                                        <td colspan="6" class="text-center py-4 text-muted">
                                            No summary data available
                                        </td>
                                    </tr>
                                <?php endif; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <!-- Add Invoice Modal -->
    <div class="modal fade" id="addInvoiceModal" tabindex="-1" aria-hidden="true">
        <div class="modal-dialog modal-lg">
            <div class="modal-content">
                <form method="post" id="addInvoiceForm">
                    <div class="modal-header">
                        <h5 class="modal-title"><i class="fas fa-plus-circle me-2"></i>Add New Invoice</h5>
                        <button type="button" class="btn-close btn-close-white" data-bs-dismiss="modal"></button>
                    </div>
                    <div class="modal-body">
                        <div class="row g-3">
                            <div class="col-md-6">
                                <label class="form-label">Fee Type <span class="text-danger">*</span></label>
                                <select name="fee_type" class="form-select" required>
                                    <option value="">Select Fee Type</option>
                                    <option value="General">General Fees</option>
                                    <option value="Tuition">Tuition Fees</option>
                                    <option value="Registration">Registration Fees</option>
                                    <option value="Examination">Examination Fees</option>
                                    <option value="Library">Library Fees</option>
                                    <option value="Other">Other Fees</option>
                                </select>
                            </div>
                            
                            <div class="col-md-6">
                                <label class="form-label">Amount (K) <span class="text-danger">*</span></label>
                                <div class="input-group">
                                    <span class="input-group-text">K</span>
                                    <input type="number" name="amount" class="form-control" 
                                           step="0.01" min="0" required 
                                           placeholder="Enter amount">
                                </div>
                            </div>
                            
                            <div class="col-md-6">
                                <label class="form-label">Intake <span class="text-danger">*</span></label>
                                <select name="intake" class="form-select" required>
                                    <option value="">Select Intake</option>
                                    <?php 
                                    $intakes->data_seek(0);
                                    while($row = $intakes->fetch_assoc()): 
                                    ?>
                                    <option value="<?= htmlspecialchars($row['intake']); ?>">
                                        <?= htmlspecialchars($row['intake']); ?>
                                    </option>
                                    <?php endwhile; ?>
                                </select>
                            </div>
                            
                            <div class="col-md-6">
                                <label class="form-label">Program <span class="text-danger">*</span></label>
                                <select name="program" class="form-select" required>
                                    <option value="">Select Program</option>
                                    <?php 
                                    $programs->data_seek(0);
                                    while($row = $programs->fetch_assoc()): 
                                    ?>
                                    <option value="<?= htmlspecialchars($row['program']); ?>">
                                        <?= htmlspecialchars($row['program']); ?>
                                    </option>
                                    <?php endwhile; ?>
                                </select>
                            </div>
                            
                            <div class="col-md-4">
                                <label class="form-label">Semester <span class="text-danger">*</span></label>
                                <select name="period" class="form-select" required>
                                    <option value="">Select Semester</option>
                                    <option value="1">Semester 1</option>
                                    <option value="2">Semester 2</option>
                                    <option value="3">Semester 3</option>
                                    <option value="4">Semester 4</option>
                                </select>
                            </div>
                            
                            <div class="col-md-4">
                                <label class="form-label">Year <span class="text-danger">*</span></label>
                                <select name="year" class="form-select" required>
                                    <option value="">Select Year</option>
                                    <?php for($y = date('Y'); $y <= date('Y') + 5; $y++): ?>
                                    <option value="<?= $y; ?>"><?= $y; ?></option>
                                    <?php endfor; ?>
                                </select>
                            </div>
                            
                            <div class="col-md-4">
                                <label class="form-label">Mode <span class="text-danger">*</span></label>
                                <select name="mode" class="form-select" required>
                                    <option value="">Select Mode</option>
                                    <?php 
                                    $modes->data_seek(0);
                                    while($row = $modes->fetch_assoc()): 
                                    ?>
                                    <option value="<?= htmlspecialchars($row['mode']); ?>">
                                        <?= htmlspecialchars($row['mode']); ?>
                                    </option>
                                    <?php endwhile; ?>
                                </select>
                            </div>
                        </div>
                        
                        <div class="alert alert-info mt-3">
                            <i class="fas fa-info-circle me-2"></i>
                            This invoice will be sent to all students in the selected program, intake, and mode.
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">
                            <i class="fas fa-times me-1"></i> Cancel
                        </button>
                        <button type="submit" name="save_balance" class="btn btn-primary">
                            <i class="fas fa-save me-1"></i> Create Invoice
                        </button>
                    </div>
                </form>
            </div>
        </div>
    </div>
    
    <!-- Edit Invoice Modal -->
    <div class="modal fade" id="editInvoiceModal" tabindex="-1" aria-hidden="true">
        <div class="modal-dialog modal-lg">
            <div class="modal-content">
                <form method="post" id="editInvoiceForm">
                    <input type="hidden" name="id" id="edit_id">
                    <div class="modal-header">
                        <h5 class="modal-title"><i class="fas fa-edit me-2"></i>Edit Invoice</h5>
                        <button type="button" class="btn-close btn-close-white" data-bs-dismiss="modal"></button>
                    </div>
                    <div class="modal-body">
                        <div class="row g-3">
                            <div class="col-md-6">
                                <label class="form-label">Fee Type <span class="text-danger">*</span></label>
                                <select name="fee_type" id="edit_fee_type" class="form-select" required>
                                    <option value="">Select Fee Type</option>
                                    <option value="General">General Fees</option>
                                    <option value="Tuition">Tuition Fees</option>
                                    <option value="Registration">Registration Fees</option>
                                    <option value="Examination">Examination Fees</option>
                                    <option value="Library">Library Fees</option>
                                    <option value="Other">Other Fees</option>
                                </select>
                            </div>
                            
                            <div class="col-md-6">
                                <label class="form-label">Amount (K) <span class="text-danger">*</span></label>
                                <div class="input-group">
                                    <span class="input-group-text">K</span>
                                    <input type="number" name="amount" id="edit_amount" class="form-control" 
                                           step="0.01" min="0" required 
                                           placeholder="Enter amount">
                                </div>
                            </div>
                            
                            <div class="col-md-6">
                                <label class="form-label">Intake <span class="text-danger">*</span></label>
                                <select name="intake" id="edit_intake" class="form-select" required>
                                    <option value="">Select Intake</option>
                                    <?php 
                                    $intakes->data_seek(0);
                                    while($row = $intakes->fetch_assoc()): 
                                    ?>
                                    <option value="<?= htmlspecialchars($row['intake']); ?>">
                                        <?= htmlspecialchars($row['intake']); ?>
                                    </option>
                                    <?php endwhile; ?>
                                </select>
                            </div>
                            
                            <div class="col-md-6">
                                <label class="form-label">Program <span class="text-danger">*</span></label>
                                <select name="program" id="edit_program" class="form-select" required>
                                    <option value="">Select Program</option>
                                    <?php 
                                    $programs->data_seek(0);
                                    while($row = $programs->fetch_assoc()): 
                                    ?>
                                    <option value="<?= htmlspecialchars($row['program']); ?>">
                                        <?= htmlspecialchars($row['program']); ?>
                                    </option>
                                    <?php endwhile; ?>
                                </select>
                            </div>
                            
                            <div class="col-md-4">
                                <label class="form-label">Semester <span class="text-danger">*</span></label>
                                <select name="period" id="edit_period" class="form-select" required>
                                    <option value="">Select Semester</option>
                                    <option value="1">Semester 1</option>
                                    <option value="2">Semester 2</option>
                                    <option value="3">Semester 3</option>
                                    <option value="4">Semester 4</option>
                                </select>
                            </div>
                            
                            <div class="col-md-4">
                                <label class="form-label">Year <span class="text-danger">*</span></label>
                                <select name="year" id="edit_year" class="form-select" required>
                                    <option value="">Select Year</option>
                                    <?php for($y = date('Y'); $y <= date('Y') + 5; $y++): ?>
                                    <option value="<?= $y; ?>"><?= $y; ?></option>
                                    <?php endfor; ?>
                                </select>
                            </div>
                            
                            <div class="col-md-4">
                                <label class="form-label">Mode <span class="text-danger">*</span></label>
                                <select name="mode" id="edit_mode" class="form-select" required>
                                    <option value="">Select Mode</option>
                                    <?php 
                                    $modes->data_seek(0);
                                    while($row = $modes->fetch_assoc()): 
                                    ?>
                                    <option value="<?= htmlspecialchars($row['mode']); ?>">
                                        <?= htmlspecialchars($row['mode']); ?>
                                    </option>
                                    <?php endwhile; ?>
                                </select>
                            </div>
                        </div>
                        
                        <div class="alert alert-warning mt-3">
                            <i class="fas fa-exclamation-triangle me-2"></i>
                            Updating this invoice will send notifications to all affected students.
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">
                            <i class="fas fa-times me-1"></i> Cancel
                        </button>
                        <button type="submit" name="update_balance" class="btn btn-primary">
                            <i class="fas fa-save me-1"></i> Update Invoice
                        </button>
                    </div>
                </form>
            </div>
        </div>
    </div>
    
    <!-- Bootstrap JS -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>
    
    <!-- Custom JavaScript -->
    <script>
        // Auto-dismiss alerts after 5 seconds
        setTimeout(() => {
            const alerts = document.querySelectorAll('.alert');
            alerts.forEach(alert => {
                const bsAlert = new bootstrap.Alert(alert);
                bsAlert.close();
            });
        }, 5000);
        
        // Form validation for add invoice
        document.getElementById('addInvoiceForm').addEventListener('submit', function(e) {
            const amount = document.querySelector('#addInvoiceForm input[name="amount"]');
            if (amount.value <= 0) {
                e.preventDefault();
                alert('Amount must be greater than 0');
                amount.focus();
            }
        });
        
        // Form validation for edit invoice
        document.getElementById('editInvoiceForm').addEventListener('submit', function(e) {
            const amount = document.getElementById('edit_amount');
            if (amount.value <= 0) {
                e.preventDefault();
                alert('Amount must be greater than 0');
                amount.focus();
            }
        });
        
        // Edit button functionality
        document.addEventListener('DOMContentLoaded', function() {
            const editButtons = document.querySelectorAll('.edit-btn');
            const editModal = new bootstrap.Modal(document.getElementById('editInvoiceModal'));
            
            editButtons.forEach(button => {
                button.addEventListener('click', function() {
                    // Get data from button attributes
                    const id = this.getAttribute('data-id');
                    const feeType = this.getAttribute('data-fee-type');
                    const amount = this.getAttribute('data-amount');
                    const intake = this.getAttribute('data-intake');
                    const period = this.getAttribute('data-period');
                    const year = this.getAttribute('data-year');
                    const program = this.getAttribute('data-program');
                    const mode = this.getAttribute('data-mode');
                    
                    // Populate form fields
                    document.getElementById('edit_id').value = id;
                    document.getElementById('edit_fee_type').value = feeType;
                    document.getElementById('edit_amount').value = amount;
                    document.getElementById('edit_intake').value = intake;
                    document.getElementById('edit_period').value = period;
                    document.getElementById('edit_year').value = year;
                    document.getElementById('edit_program').value = program;
                    document.getElementById('edit_mode').value = mode;
                    
                    // Show modal
                    editModal.show();
                });
            });
        });
        
        // Print functionality
        function printReport() {
            window.print();
        }
        
        // Export functionality
        function exportToCSV() {
            alert('CSV export feature coming soon!');
        }
        
        // Check if edit modal should be shown on page load
        <?php if (isset($_GET['edit_id']) && $editData): ?>
        document.addEventListener('DOMContentLoaded', function() {
            const editModal = new bootstrap.Modal(document.getElementById('editInvoiceModal'));
            
            // Populate form fields
            document.getElementById('edit_id').value = <?= $editData['id']; ?>;
            document.getElementById('edit_fee_type').value = '<?= $editData['fee_type']; ?>';
            document.getElementById('edit_amount').value = <?= $editData['amount']; ?>;
            document.getElementById('edit_intake').value = '<?= $editData['intake']; ?>';
            document.getElementById('edit_period').value = '<?= $editData['period']; ?>';
            document.getElementById('edit_year').value = '<?= $editData['year']; ?>';
            document.getElementById('edit_program').value = '<?= $editData['program']; ?>';
            document.getElementById('edit_mode').value = '<?= $editData['mode']; ?>';
            
            // Show modal
            editModal.show();
        });
        <?php endif; ?>
    </script>
</body>
</html>