<?php
session_start();
ob_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);
include "../include/config.php";
$userlogin = $_SESSION['email'];
$userrole = $_SESSION['role'];
$intake = $_SESSION['department'];

if (isset($_SESSION['email'])) {
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Student Balance Report</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0/dist/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <style>
        body {
            font-family: 'Source Sans Pro', sans-serif;
            background: #f8f9fa;
        }
        .content {
            margin-left: 200px;
            padding: 20px;
        }
        .balance-header {
            background: var(--primary);
            color: white;
            padding: 20px;
            border-radius: 10px;
            margin-bottom: 20px;
        }
        .filter-section {
            background: white;
            padding: 20px;
            border-radius: 10px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            margin-bottom: 20px;
        }
        .balance-table {
            width: 100%;
            border-collapse: collapse;
            background: white;
            border-radius: 10px;
            overflow: hidden;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        .balance-table th {
            background: var(--primary);
            color: white;
            padding: 15px;
            text-align: left;
            font-weight: 600;
        }
        .balance-table td {
            padding: 12px 15px;
            border-bottom: 1px solid #e9ecef;
        }
        .balance-table tr:hover {
            background: #f8f9fa;
        }
        .balance-high {
            background: #ffebee !important;
            border-left: 4px solid #f44336;
        }
        .balance-medium {
            background: #fff3e0 !important;
            border-left: 4px solid #ff9800;
        }
        .balance-low {
            background: #e8f5e8 !important;
            border-left: 4px solid #4caf50;
        }
        .summary-cards {
            display: flex;
            gap: 15px;
            margin-bottom: 20px;
        }
        .summary-card {
            flex: 1;
            background: white;
            padding: 20px;
            border-radius: 10px;
            text-align: center;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            border-top: 4px solid var(--primary);
        }
        .summary-card h3 {
            margin: 0;
            font-size: 24px;
            color: var(--primary);
            font-weight: bold;
        }
        .summary-card p {
            margin: 5px 0 0;
            color: #666;
            font-weight: 500;
        }
        .status-badge {
            padding: 6px 12px;
            border-radius: 20px;
            font-size: 12px;
            font-weight: 600;
        }
        .badge-high {
            background: #f44336;
            color: white;
        }
        .badge-medium {
            background: #ff9800;
            color: white;
        }
        .badge-low {
            background: #4caf50;
            color: white;
        }
        .export-btn {
            background: #28a745;
            border: none;
            padding: 10px 20px;
            border-radius: 6px;
            color: white;
            font-weight: 500;
        }
        .export-btn:hover {
            background: #218838;
            color: white;
        }
        .filter-btn {
            background: var(--primary);
            border: none;
            color: white;
            padding: 10px 20px;
            border-radius: 6px;
        }
        .reset-btn {
            background: #6c757d;
            border: none;
            color: white;
            padding: 10px 20px;
            border-radius: 6px;
        }
        .view-record-btn {
            background: #17a2b8;
            border: none;
            color: white;
            padding: 6px 12px;
            border-radius: 4px;
            font-size: 12px;
            text-decoration: none;
            display: inline-block;
        }
        .view-record-btn:hover {
            background: #138496;
            color: white;
            text-decoration: none;
        }
        @media (max-width: 768px) {
            .content {
                margin-left: 0;
                padding: 10px;
            }
            .summary-cards {
                flex-direction: column;
            }
        }
    </style>
</head>
<body>
<?php include "../include/nav.php"; ?>

<div class="content">
    <?php if($userrole == "Finance" || $userrole == "Superadmin") { ?>
    
    <!-- Student Balance Report Section -->
    <div class="balance-header text-center">
        <h2><i class="fas fa-credit-card me-2"></i> Student Balance Owing Report</h2>
        <p class="mb-0">View student payment status and outstanding balances with advanced filtering</p>
    </div>
    
    <!-- Filter Section -->
    <div class="filter-section">
        <h5 class="mb-3"><i class="fas fa-filter me-2"></i>Filter Students</h5>
        <form method="GET" id="filterForm">
            <div class="row g-3">
                <div class="col-md-3">
                    <label class="form-label"><strong>Search Student</strong></label>
                    <input type="text" name="search" class="form-control" 
                           placeholder="Name or Student ID..." 
                           value="<?php echo isset($_GET['search']) ? htmlspecialchars($_GET['search']) : ''; ?>">
                </div>
                
                <div class="col-md-3">
                    <label class="form-label"><strong>Program</strong></label>
                    <select name="program" class="form-control">
                        <option value="">All Programs</option>
                        <?php
                        $programs = mysqli_query($conn, "SELECT DISTINCT program FROM students WHERE program != '' ORDER BY program");
                        while ($program = mysqli_fetch_assoc($programs)) {
                            $selected = (isset($_GET['program']) && $_GET['program'] == $program['program']) ? 'selected' : '';
                            echo "<option value='{$program['program']}' $selected>{$program['program']}</option>";
                        }
                        ?>
                    </select>
                </div>
                
                <div class="col-md-3">
                    <label class="form-label"><strong>Intake</strong></label>
                    <select name="intake" class="form-control">
                        <option value="">All Intakes</option>
                        <?php
                        $intakes = mysqli_query($conn, "SELECT DISTINCT intake FROM students WHERE intake != '' ORDER BY intake");
                        while ($intake_row = mysqli_fetch_assoc($intakes)) {
                            $selected = (isset($_GET['intake']) && $_GET['intake'] == $intake_row['intake']) ? 'selected' : '';
                            echo "<option value='{$intake_row['intake']}' $selected>{$intake_row['intake']}</option>";
                        }
                        ?>
                    </select>
                </div>
                
                <div class="col-md-3">
                    <label class="form-label"><strong>Balance Status</strong></label>
                    <select name="status" class="form-control">
                        <option value="">All Statuses</option>
                        <option value="high" <?php echo (isset($_GET['status']) && $_GET['status'] == 'high') ? 'selected' : ''; ?>>High Balance (> K1,000)</option>
                        <option value="medium" <?php echo (isset($_GET['status']) && $_GET['status'] == 'medium') ? 'selected' : ''; ?>>Medium Balance (K1 - K1,000)</option>
                        <option value="paid" <?php echo (isset($_GET['status']) && $_GET['status'] == 'paid') ? 'selected' : ''; ?>>Paid Up</option>
                    </select>
                </div>
                
                <div class="col-12">
                    <div class="d-flex gap-2">
                        <button type="submit" class="filter-btn">
                            <i class="fas fa-search me-1"></i> Apply Filters
                        </button>
                        <button type="button" class="reset-btn" onclick="resetFilters()">
                            <i class="fas fa-refresh me-1"></i> Reset Filters
                        </button>
                        <button type="button" class="export-btn ms-auto" onclick="exportToExcel()">
                            <i class="fas fa-download me-1"></i> Export to Excel
                        </button>
                    </div>
                </div>
            </div>
        </form>
    </div>
    
    <?php
    // Build query with filters
    $whereConditions = [];
    
    // Search filter
    if (isset($_GET['search']) && !empty($_GET['search'])) {
        $search = mysqli_real_escape_string($conn, $_GET['search']);
        $whereConditions[] = "(s.studentname LIKE '%$search%' OR s.studentid LIKE '%$search%')";
    }
    
    // Program filter
    if (isset($_GET['program']) && !empty($_GET['program'])) {
        $program = mysqli_real_escape_string($conn, $_GET['program']);
        $whereConditions[] = "s.program = '$program'";
    }
    
    // Intake filter
    if (isset($_GET['intake']) && !empty($_GET['intake'])) {
        $intake_filter = mysqli_real_escape_string($conn, $_GET['intake']);
        $whereConditions[] = "s.intake = '$intake_filter'";
    }
    
    $whereClause = '';
    if (!empty($whereConditions)) {
        $whereClause = "WHERE " . implode(' AND ', $whereConditions);
    }
    
    // Get filtered students
    $students_query = mysqli_query($conn, 
        "SELECT s.`id`, s.`studentid`, s.`studentname`, s.`intake`, s.`program` 
         FROM `students` s 
         $whereClause 
         ORDER BY s.studentname"
    );
    
    $total_students = 0;
    $total_owing = 0;
    $students_data = [];
    
    while ($student = mysqli_fetch_assoc($students_query)) {
        $student_id = $student['id'];
        $studentid = $student['studentid'];
        
        // Calculate total paid
        $paid_query = mysqli_query($conn, "SELECT SUM(amount) as total_paid FROM payments WHERE studentid = '$studentid'");
        $paid_data = mysqli_fetch_assoc($paid_query);
        $total_paid = $paid_data['total_paid'] ?? 0;
        
        // Calculate total balance (fees)
        $balance_query = mysqli_query($conn, "SELECT SUM(amount) as total_balance FROM balances WHERE program = '{$student['program']}' AND intake = '{$student['intake']}'");
        $balance_data = mysqli_fetch_assoc($balance_query);
        $total_balance = $balance_data['total_balance'] ?? 0;
        
        // Calculate owing amount
        $owing = $total_balance - $total_paid;
        
        // Determine status based on balance filter
        $status_class = '';
        $status_text = '';
        $status_type = '';
        
        if ($owing > 1000) {
            $status_class = 'balance-high';
            $status_text = 'High Balance';
            $status_type = 'high';
            $status_badge = 'badge-high';
        } elseif ($owing > 0) {
            $status_class = 'balance-medium';
            $status_text = 'Medium Balance';
            $status_type = 'medium';
            $status_badge = 'badge-medium';
        } else {
            $status_class = 'balance-low';
            $status_text = 'Paid Up';
            $status_type = 'paid';
            $status_badge = 'badge-low';
        }
        
        // Apply status filter
        if (isset($_GET['status']) && !empty($_GET['status']) && $_GET['status'] != $status_type) {
            continue; // Skip if doesn't match status filter
        }
        
        $students_data[] = [
            'studentid' => $studentid,
            'studentname' => $student['studentname'],
            'program' => $student['program'],
            'intake' => $student['intake'],
            'total_paid' => $total_paid,
            'total_balance' => $total_balance,
            'owing' => $owing,
            'status_class' => $status_class,
            'status_text' => $status_text,
            'status_badge' => $status_badge
        ];
        
        $total_students++;
        $total_owing += $owing;
    }
    
    $avg_balance = $total_students > 0 ? $total_owing / $total_students : 0;
    ?>
    
    <!-- Summary Cards -->
    <div class="summary-cards">
        <div class="summary-card">
            <h3 id="totalStudents"><?php echo $total_students; ?></h3>
            <p>Total Students</p>
        </div>
        <div class="summary-card">
            <h3 id="totalOwing">K<?php echo number_format($total_owing, 2); ?></h3>
            <p>Total Owing</p>
        </div>
        <div class="summary-card">
            <h3 id="avgBalance">K<?php echo number_format($avg_balance, 2); ?></h3>
            <p>Average Balance</p>
        </div>
        <div class="summary-card">
            <h3 style="color: <?php echo ($total_owing > 0) ? '#dc3545' : '#28a745'; ?>">
                <?php echo ($total_owing > 0) ? 'Pending' : 'Clear'; ?>
            </h3>
            <p>Overall Status</p>
        </div>
    </div>
    
    <!-- Results Table -->
    <div class="table-responsive">
        <table class="balance-table">
            <thead>
                <tr>
                    <th>Student ID</th>
                    <th>Name</th>
                    <th>Program</th>
                    <th>Intake</th>
                    <th>Total Paid</th>
                    <th>Balance Owing</th>
                    <th>Status</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php if (count($students_data) > 0): ?>
                    <?php foreach ($students_data as $student): ?>
                    <tr class="<?php echo $student['status_class']; ?>">
                        <td><strong><?php echo $student['studentid']; ?></strong></td>
                        <td><?php echo htmlspecialchars($student['studentname']); ?></td>
                        <td><?php echo htmlspecialchars($student['program']); ?></td>
                        <td><?php echo htmlspecialchars($student['intake']); ?></td>
                        <td><strong>K<?php echo number_format($student['total_paid'], 2); ?></strong></td>
                        <td><strong>K<?php echo number_format($student['owing'], 2); ?></strong></td>
                        <td>
                            <span class="status-badge <?php echo $student['status_badge']; ?>">
                                <?php echo $student['status_text']; ?>
                            </span>
                        </td>
                        <td>
                            <a href="student_payment_record.php?studentid=<?php echo urlencode($student['studentid']); ?>" 
                               class="view-record-btn">
                                <i class="fas fa-eye me-1"></i> View Record
                            </a>
                        </td>
                    </tr>
                    <?php endforeach; ?>
                <?php else: ?>
                    <tr>
                        <td colspan="8" class="text-center py-4 text-muted">
                            <i class="fas fa-search fa-2x mb-3"></i><br>
                            No students found matching your filter criteria
                        </td>
                    </tr>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
    
    <?php } else { ?>
    <!-- Non-finance user content -->
    <div class="alert alert-warning text-center">
        <h4><i class="fas fa-exclamation-triangle me-2"></i>Access Denied</h4>
        <p>You don't have permission to view the student balance report.</p>
    </div>
    <?php } ?>
</div>

<script>
    // Reset filters
    function resetFilters() {
        window.location.href = window.location.pathname;
    }
    
    // Export to Excel function
    function exportToExcel() {
        // Get current filter parameters
        const params = new URLSearchParams(window.location.search);
        
        // Redirect to export script with current filters
        window.location.href = 'export_balance.php?' + params.toString();
    }
    
    // Auto-submit form when select fields change (optional)
    document.querySelectorAll('select[name="program"], select[name="intake"], select[name="status"]').forEach(select => {
        select.addEventListener('change', function() {
            document.getElementById('filterForm').submit();
        });
    });
    
    // Real-time search (optional - debounced)
    let searchTimeout;
    document.querySelector('input[name="search"]').addEventListener('input', function() {
        clearTimeout(searchTimeout);
        searchTimeout = setTimeout(() => {
            document.getElementById('filterForm').submit();
        }, 1000);
    });
</script>

</body>
</html>
<?php
} else {
    header("location:../index.php");
    exit();
}
?>