<?php

if (session_status() === PHP_SESSION_NONE) {

    session_start();

}



if (!isset($_SESSION['user_id'])) {

    header('Location: index.php');

    exit;

}



// Role-based access control

$allowed_roles = ['admin', 'accountant', 'headteacher'];



if (!in_array($_SESSION['role'], $allowed_roles)) {

    header('Location: dashboard.php?error=access_denied');

    exit;

}



require_once 'config.php';

$conn = getDBConnection();



// Get report data based on filters

$reportType = $_GET['report_type'] ?? 'collections';

$dateFrom = $_GET['date_from'] ?? date('Y-01-01');

$dateTo = $_GET['date_to'] ?? date('Y-m-d');

$classFilter = $_GET['class'] ?? '';

$studentId = intval($_GET['student_id'] ?? 0);



// Build query conditions

$classCondition = $classFilter ? " AND s.class = '" . $conn->real_escape_string($classFilter) . "'" : "";

$studentCondition = $studentId > 0 ? " AND s.id = $studentId" : "";



// Fee Collections Report with class filter

$collections = [];

$totalCollection = 0;

$collectionQuery = "SELECT p.*, s.first_name, s.last_name, s.class, s.student_id, 

                           fs.fee_type, fs.amount as total_fee

                    FROM payments p 

                    JOIN students s ON p.student_id = s.id 

                    JOIN fee_structures fs ON p.fee_structure_id = fs.id

                    WHERE p.payment_date BETWEEN '$dateFrom' AND '$dateTo'

                    $classCondition $studentCondition

                    ORDER BY p.payment_date DESC";

$result = $conn->query($collectionQuery);

if ($result) {

    while ($row = $result->fetch_assoc()) {

        $collections[] = $row;

        $totalCollection += $row['amount_paid'];

    }

}



// Expenses Report

$expenses = [];

$totalExpense = 0;

$expenseQuery = "SELECT * FROM expenses 

                 WHERE expense_date BETWEEN '$dateFrom' AND '$dateTo' 

                 ORDER BY expense_date DESC";

$result = $conn->query($expenseQuery);

if ($result) {

    while ($row = $result->fetch_assoc()) {

        $expenses[] = $row;

        $totalExpense += $row['amount'];

    }

}



// Class-wise collections WITH CLASS FILTER

$classCollections = [];

$classQuery = "SELECT s.class, 

                      COUNT(DISTINCT s.id) as student_count,

                      COUNT(p.id) as payment_count,

                      COALESCE(SUM(p.amount_paid), 0) as total,

                      COALESCE((SELECT SUM(fs.amount) FROM fee_structures fs WHERE fs.class_name = s.class), 0) as expected_total

               FROM students s

               LEFT JOIN payments p ON s.id = p.student_id 

                   AND p.payment_date BETWEEN '$dateFrom' AND '$dateTo'

               WHERE s.status = 'active'";



// Apply class filter to class summary if selected

if ($classFilter) {

    $classQuery .= " AND s.class = '" . $conn->real_escape_string($classFilter) . "'";

}



$classQuery .= " GROUP BY s.class

                 ORDER BY FIELD(s.class, 

                     'Nursery 1', 'Nursery 2', 

                     'KG 1', 'KG 2',

                     'Class 1', 'Class 2', 'Class 3', 'Class 4', 'Class 5', 'Class 6',

                     'JHS 1', 'JHS 2', 'JHS 3')";



$result = $conn->query($classQuery);

if ($result) {

    while ($row = $result->fetch_assoc()) {

        $classCollections[$row['class']] = $row;

    }

}



// Student-wise outstanding fees WITH CLASS FILTER

$outstandingFees = [];

$outstandingQuery = "SELECT s.id, s.student_id, s.first_name, s.last_name, s.class, 

                            COALESCE((SELECT SUM(fs.amount) FROM fee_structures fs WHERE fs.class_name = s.class), 0) as total_fee,

                            COALESCE((SELECT SUM(p.amount_paid) FROM payments p WHERE p.student_id = s.id), 0) as total_paid

                     FROM students s

                     WHERE s.status = 'active'

                     $classCondition

                     ORDER BY FIELD(s.class, 

                         'Nursery 1', 'Nursery 2', 

                         'KG 1', 'KG 2',

                         'Class 1', 'Class 2', 'Class 3', 'Class 4', 'Class 5', 'Class 6',

                         'JHS 1', 'JHS 2', 'JHS 3'), s.first_name";

$result = $conn->query($outstandingQuery);

if ($result) {

    while ($row = $result->fetch_assoc()) {

        $outstanding = $row['total_fee'] - $row['total_paid'];

        if ($outstanding > 0) {

            $outstandingFees[] = [

                'student' => $row,

                'outstanding' => $outstanding

            ];

        }

    }

}



// Get distinct classes from enrolled students (kept for backward compat)

$classes = [];

$classResult = $conn->query("SELECT DISTINCT class FROM students WHERE status = 'active' ORDER BY class");

if ($classResult) {

    while ($row = $classResult->fetch_assoc()) {

        $classes[] = $row['class'];

    }

}



// Load all active classes from Class Management for the filter dropdown

$dbClasses = [];

$_cr = $conn->query("SELECT class_name FROM classes WHERE is_active = 1 ORDER BY class_name");

if ($_cr) { while ($_row = $_cr->fetch_assoc()) $dbClasses[] = $_row['class_name']; }

// Fallback: include any student classes not yet in Class Management

foreach ($classes as $_c) { if (!in_array($_c, $dbClasses)) $dbClasses[] = $_c; }

sort($dbClasses);



// Get all active students for dropdown

$students = [];

$studentResult = $conn->query("SELECT id, student_id, first_name, last_name, class FROM students WHERE status = 'active' ORDER BY 

    FIELD(class, 

        'Nursery 1', 'Nursery 2', 

        'KG 1', 'KG 2',

        'Class 1', 'Class 2', 'Class 3', 'Class 4', 'Class 5', 'Class 6',

        'JHS 1', 'JHS 2', 'JHS 3'), first_name, last_name");

if ($studentResult) {

    while ($row = $studentResult->fetch_assoc()) {

        $students[] = $row;

    }

}





// Helper function to get class order for display

function getClassOrder($class) {

    $order = [

        'Nursery 1' => 1, 'Nursery 2' => 2,

        'KG 1' => 3, 'KG 2' => 4,

        'Class 1' => 5, 'Class 2' => 6, 'Class 3' => 7, 'Class 4' => 8, 'Class 5' => 9, 'Class 6' => 10,

        'JHS 1' => 11, 'JHS 2' => 12, 'JHS 3' => 13

    ];

    return $order[$class] ?? 999;

}

?>

<!DOCTYPE html>

<html lang="en">

<head>

    <meta charset="UTF-8">

    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>Reports - EduTrack Pro</title>

    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">

    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">

    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css">

    <link rel="stylesheet" href="https://cdn.datatables.net/buttons/2.4.1/css/buttons.bootstrap5.min.css">

    <link rel="stylesheet" href="css/style.css">

    <style>

        .summary-card {

            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);

            color: white;

            border-radius: 10px;

            padding: 20px;

            margin-bottom: 20px;

        }

        .summary-item {

            display: flex;

            justify-content: space-between;

            padding: 10px 0;

            border-bottom: 1px solid #dee2e6;

        }

        .summary-item:last-child {

            border-bottom: none;

        }

        .collection-rate {

            font-size: 0.9em;

            color: #28a745;

        }

        .dt-buttons {

            margin-bottom: 15px;

        }

        .btn-export {

            margin-right: 5px;

        }

        .filter-section {

            background-color: #f8f9fa;

            padding: 20px;

            border-radius: 8px;

            margin-bottom: 20px;

        }

        .class-badge {

            background-color: #e9ecef;

            color: #495057;

            padding: 5px 10px;

            border-radius: 20px;

            font-size: 0.9em;

            margin-right: 5px;

        }

        .active-filter {

            background-color: #007bff;

            color: white;

            padding: 5px 15px;

            border-radius: 20px;

            display: inline-block;

            margin-bottom: 10px;

        }

        .active-filter i {

            margin-right: 5px;

        }

    </style>

</head>

<body>

    <div class="wrapper">

        <!-- Sidebar -->

        <nav id="sidebar" class="sidebar">

        <?php echo buildSidebar(basename($_SERVER['PHP_SELF'])); ?>

        </nav>



        <!-- Main Content -->

        <div class="main-content">

            <!-- Header -->

            <header class="header">

                <button class="btn-toggle" id="sidebarToggle">

                    <i class="fas fa-bars"></i>

                </button>

                <div class="header-right">

                    <div class="dropdown">

                        <button class="btn btn-link dropdown-toggle" type="button" data-bs-toggle="dropdown">

                            <i class="fas fa-user-circle me-2"></i><?php echo htmlspecialchars((string) ($_SESSION['full_name'] ?? '')); ?>

                        </button>

                        <ul class="dropdown-menu dropdown-menu-end">

                            <li><a class="dropdown-item" href="settings.php"><i class="fas fa-cog me-2"></i>Settings</a></li>

                            <li><hr class="dropdown-divider"></li>

                            <li><a class="dropdown-item" href="logout.php"><i class="fas fa-sign-out-alt me-2"></i>Logout</a></li>

                        </ul>

                    </div>

                </div>

            </header>



            <!-- Page Content -->

            <main class="content">

                <div class="content-header">

                    <h2><i class="fas fa-chart-bar me-2"></i>Reports & Analytics</h2>

                    <p>Generate and export comprehensive financial reports</p>

                </div>



                <!-- Active Filter Display -->

                <?php if ($classFilter): ?>

                <div class="active-filter">

                    <i class="fas fa-filter"></i> Currently filtered by: <strong><?php echo htmlspecialchars($classFilter); ?></strong>

                    <a href="reports.php?report_type=<?php echo $reportType; ?>&date_from=<?php echo $dateFrom; ?>&date_to=<?php echo $dateTo; ?>" class="text-white ms-3">

                        <i class="fas fa-times"></i> Clear Filter

                    </a>

                </div>

                <?php endif; ?>



                <!-- Filters -->

                <div class="card mb-4">

                    <div class="card-body">

                        <form method="GET" action="reports.php" id="reportForm" class="row g-3">

                            <div class="col-md-2">

                                <label class="form-label">Report Type</label>

                                <select class="form-select" name="report_type" id="reportType">

                                    <option value="collections" <?php echo $reportType === 'collections' ? 'selected' : ''; ?>>Fee Collections</option>

                                    <option value="expenses" <?php echo $reportType === 'expenses' ? 'selected' : ''; ?>>Expenses</option>

                                    <option value="outstanding" <?php echo $reportType === 'outstanding' ? 'selected' : ''; ?>>Outstanding Fees</option>

                                    <option value="profit_loss" <?php echo $reportType === 'profit_loss' ? 'selected' : ''; ?>>Profit & Loss</option>

                                    <option value="class_summary" <?php echo $reportType === 'class_summary' ? 'selected' : ''; ?>>Class Summary</option>

                                    <option value="student_statement" <?php echo $reportType === 'student_statement' ? 'selected' : ''; ?>>Student Statement</option>

                                </select>

                            </div>



                            <div class="col-md-2" id="classFilter" style="display: <?php echo in_array($reportType, ['collections', 'outstanding', 'class_summary', 'student_statement']) ? 'block' : 'none'; ?>;">

                                <label class="form-label">Class</label>

                                <select class="form-select" name="class">

                                    <option value="">All Classes</option>

                                    <?php foreach ($dbClasses as $class): ?>

                                        <option value="<?php echo htmlspecialchars($class); ?>" <?php echo $classFilter == $class ? 'selected' : ''; ?>>

                                            <?php echo htmlspecialchars($class); ?>

                                        </option>

                                    <?php endforeach; ?>

                                </select>

                            </div>



                            <div class="col-md-3" id="studentFilter" style="display: <?php echo $reportType === 'student_statement' ? 'block' : 'none'; ?>;">

                                <label class="form-label">Select Student</label>

                                <select class="form-select" name="student_id">

                                    <option value="">Select Student</option>

                                    <?php foreach ($students as $student): ?>

                                        <option value="<?php echo $student['id']; ?>" <?php echo $studentId == $student['id'] ? 'selected' : ''; ?>>

                                            <?php echo htmlspecialchars($student['first_name'] . ' ' . $student['last_name'] . ' (' . $student['student_id'] . ' - ' . $student['class'] . ')'); ?>

                                        </option>

                                    <?php endforeach; ?>

                                </select>

                            </div>



                            <div class="col-md-2">

                                <label class="form-label">Date From</label>

                                <input type="date" class="form-control" name="date_from" value="<?php echo $dateFrom; ?>">

                            </div>



                            <div class="col-md-2">

                                <label class="form-label">Date To</label>

                                <input type="date" class="form-control" name="date_to" value="<?php echo $dateTo; ?>">

                            </div>



                            <div class="col-md-1">

                                <label class="form-label">&nbsp;</label>

                                <button type="submit" class="btn btn-primary w-100">

                                    <i class="fas fa-filter"></i>

                                </button>

                            </div>

                        </form>

                        

                        <!-- Export Buttons -->

                        <div class="row mt-3">

                            <div class="col-12">

                                <div class="btn-group" role="group">

                                    <button class="btn btn-success btn-sm" onclick="exportReport('excel')">

                                        <i class="fas fa-file-excel me-2"></i>Export to Excel

                                    </button>

                                    <button class="btn btn-danger btn-sm" onclick="exportReport('pdf')">

                                        <i class="fas fa-file-pdf me-2"></i>Export to PDF

                                    </button>

                                    <button class="btn btn-info btn-sm" onclick="window.print()">

                                        <i class="fas fa-print me-2"></i>Print

                                    </button>

                                </div>

                            </div>

                        </div>

                    </div>

                </div>



                <!-- Summary Cards -->

                <?php if ($reportType === 'profit_loss'): ?>

                <div class="row mb-4">

                    <div class="col-md-4">

                        <div class="summary-card">

                            <h5>Total Collections</h5>

                            <h2><?php echo formatCurrency($totalCollection); ?></h2>

                            <small><?php echo count($collections); ?> transactions</small>

                        </div>

                    </div>

                    <div class="col-md-4">

                        <div class="summary-card" style="background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%);">

                            <h5>Total Expenses</h5>

                            <h2><?php echo formatCurrency($totalExpense); ?></h2>

                            <small><?php echo count($expenses); ?> transactions</small>

                        </div>

                    </div>

                    <div class="col-md-4">

                        <div class="summary-card" style="background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%);">

                            <h5>Net Balance</h5>

                            <h2><?php echo formatCurrency($totalCollection - $totalExpense); ?></h2>

                            <small><?php echo ($totalCollection - $totalExpense) >= 0 ? 'Profit' : 'Loss'; ?></small>

                        </div>

                    </div>

                </div>

                <?php endif; ?>



                <ul class="nav nav-tabs mb-3" id="reportTabs" role="tablist">

                    <li class="nav-item" role="presentation">

                        <button class="nav-link active" id="current-report-tab"

                                data-bs-toggle="tab" data-bs-target="#current-report"

                                type="button" role="tab" aria-controls="current-report" aria-selected="true">

                            <i class="fas fa-chart-line me-1"></i>Current Report

                        </button>

                    </li>

                    <li class="nav-item" role="presentation">

                        <button class="nav-link" id="online-payments-tab"

                          data-bs-toggle="tab" data-bs-target="#online-payments"

                          type="button" role="tab">

                          <i class="fas fa-mobile-alt me-1"></i>Online Payments

                        </button>

                    </li>

                </ul>



                <!-- Report Content -->

                <div class="card">

                    <div class="card-header">

                        <h5 class="mb-0">

                            <i class="fas fa-chart-line me-2"></i>

                            <?php 

                            $titles = [

                                'collections' => 'Fee Collections Report',

                                'expenses' => 'Expenses Report',

                                'outstanding' => 'Outstanding Fees Report',

                                'profit_loss' => 'Profit & Loss Report',

                                'class_summary' => 'Class Summary Report',

                                'student_statement' => 'Student Statement'

                            ];

                            echo $titles[$reportType] ?? 'Report';

                            if ($classFilter) {

                                echo ' - ' . htmlspecialchars($classFilter);

                            }

                            ?>

                        </h5>

                    </div>

                    <div class="card-body">

                        <div class="tab-content">

                            <div class="tab-pane fade show active" id="current-report" role="tabpanel" aria-labelledby="current-report-tab">

                        <!-- Report Tables -->

                        <?php if ($reportType === 'collections'): ?>

                            <div class="table-responsive">

                                <table class="table table-hover" id="reportTable">

                                    <thead>

                                        <tr>

                                            <th>Date</th>

                                            <th>Student ID</th>

                                            <th>Student Name</th>

                                            <th>Class</th>

                                            <th>Fee Type</th>

                                            <th>Amount Paid</th>

                                            <th>Method</th>

                                            <th>Transaction ID</th>

                                        </tr>

                                    </thead>

                                    <tbody>

                                        <?php if (empty($collections)): ?>

                                            <tr><td colspan="8" class="text-center py-4">No collections found for the selected period<?php echo $classFilter ? ' in ' . $classFilter : ''; ?></td></tr>

                                        <?php else: ?>

                                            <?php foreach ($collections as $col): ?>

                                            <tr>

                                                <td><?php echo date('d M Y', strtotime($col['payment_date'])); ?></td>

                                                <td><?php echo htmlspecialchars($col['student_id']); ?></td>

                                                <td><?php echo htmlspecialchars($col['first_name'] . ' ' . $col['last_name']); ?></td>

                                                <td><span class="class-badge"><?php echo htmlspecialchars($col['class']); ?></span></td>

                                                <td><?php echo htmlspecialchars($col['fee_type']); ?></td>

                                                <td class="text-success fw-bold"><?php echo formatCurrency($col['amount_paid']); ?></td>

                                                <td><?php echo ucfirst($col['payment_method']); ?></td>

                                                <td><?php echo htmlspecialchars($col['transaction_id'] ?: '-'); ?></td>

                                            </tr>

                                            <?php endforeach; ?>

                                        <?php endif; ?>

                                    </tbody>

                                    <tfoot>

                                        <tr class="table-info">

                                            <th colspan="5" class="text-end">Total:</th>

                                            <th><?php echo formatCurrency($totalCollection); ?></th>

                                            <th colspan="2"></th>

                                        </tr>

                                    </tfoot>

                                </table>

                            </div>



                        <?php elseif ($reportType === 'expenses'): ?>

                            <div class="table-responsive">

                                <table class="table table-hover" id="reportTable">

                                    <thead>

                                        <tr>

                                            <th>Date</th>

                                            <th>Category</th>

                                            <th>Description</th>

                                            <th>Amount</th>

                                            <th>Payment Method</th>

                                            <th>Vendor</th>

                                        </tr>

                                    </thead>

                                    <tbody>

                                        <?php if (empty($expenses)): ?>

                                            <tr><td colspan="6" class="text-center py-4">No expenses found for the selected period</td></tr>

                                        <?php else: ?>

                                            <?php foreach ($expenses as $exp): ?>

                                            <tr>

                                                <td><?php echo date('d M Y', strtotime($exp['expense_date'])); ?></td>

                                                <td><span class="badge bg-primary"><?php echo htmlspecialchars($exp['category']); ?></span></td>

                                                <td><?php echo htmlspecialchars($exp['description']); ?></td>

                                                <td class="text-danger fw-bold"><?php echo formatCurrency($exp['amount']); ?></td>

                                                <td><?php echo ucfirst($exp['payment_method']); ?></td>

                                                <td><?php echo htmlspecialchars($exp['vendor'] ?: '-'); ?></td>

                                            </tr>

                                            <?php endforeach; ?>

                                        <?php endif; ?>

                                    </tbody>

                                    <tfoot>

                                        <tr class="table-info">

                                            <th colspan="3" class="text-end">Total:</th>

                                            <th><?php echo formatCurrency($totalExpense); ?></th>

                                            <th colspan="2"></th>

                                        </tr>

                                    </tfoot>

                                </table>

                            </div>



                        <?php elseif ($reportType === 'outstanding'): ?>

                            <div class="table-responsive">

                                <table class="table table-hover" id="reportTable">

                                    <thead>

                                        <tr>

                                            <th>Student ID</th>

                                            <th>Student Name</th>

                                            <th>Class</th>

                                            <th>Total Expected</th>

                                            <th>Total Paid</th>

                                            <th>Outstanding</th>

                                            <th>Action</th>

                                        </tr>

                                    </thead>

                                    <tbody>

                                        <?php if (empty($outstandingFees)): ?>

                                            <tr><td colspan="7" class="text-center py-4">No outstanding fees found<?php echo $classFilter ? ' in ' . $classFilter : ''; ?></td></tr>

                                        <?php else: ?>

                                            <?php foreach ($outstandingFees as $item): ?>

                                            <tr>

                                                <td><?php echo htmlspecialchars($item['student']['student_id']); ?></td>

                                                <td><?php echo htmlspecialchars($item['student']['first_name'] . ' ' . $item['student']['last_name']); ?></td>

                                                <td><span class="class-badge"><?php echo htmlspecialchars($item['student']['class']); ?></span></td>

                                                <td><?php echo formatCurrency($item['student']['total_fee']); ?></td>

                                                <td><?php echo formatCurrency($item['student']['total_paid']); ?></td>

                                                <td class="text-danger fw-bold"><?php echo formatCurrency($item['outstanding']); ?></td>

                                                <td>

                                                    <button class="btn btn-sm btn-warning" onclick="sendReminder(<?php echo $item['student']['id']; ?>)">

                                                        <i class="fas fa-bell me-1"></i>Send Reminder

                                                    </button>

                                                </td>

                                            </tr>

                                            <?php endforeach; ?>

                                        <?php endif; ?>

                                    </tbody>

                                    <tfoot>

                                        <tr class="table-info">

                                            <th colspan="5" class="text-end">Total Outstanding:</th>

                                            <th><?php echo formatCurrency(array_sum(array_column($outstandingFees, 'outstanding'))); ?></th>

                                            <th></th>

                                        </tr>

                                    </tfoot>

                                </table>

                            </div>



                        <?php elseif ($reportType === 'class_summary'): ?>

                            <div class="table-responsive">

                                <table class="table table-hover" id="reportTable">

                                    <thead>

                                        <tr>

                                            <th>Class</th>

                                            <th>Students</th>

                                            <th>Payments</th>

                                            <th>Expected Total</th>

                                            <th>Collected</th>

                                            <th>Collection Rate</th>

                                            <th>Average per Student</th>

                                        </tr>

                                    </thead>

                                    <tbody>

                                        <?php 

                                        if (empty($classCollections)): 

                                        ?>

                                            <tr><td colspan="7" class="text-center py-4">No class data found<?php echo $classFilter ? ' for ' . $classFilter : ''; ?></td></tr>

                                        <?php 

                                        else:

                                            $grandTotal = 0;

                                            $grandExpected = 0;

                                            $totalStudents = 0;

                                            

                                            // Sort classes in correct order

                                            uksort($classCollections, function($a, $b) {

                                                $order = [

                                                    'Nursery 1' => 1, 'Nursery 2' => 2,

                                                    'KG 1' => 3, 'KG 2' => 4,

                                                    'Class 1' => 5, 'Class 2' => 6, 'Class 3' => 7, 

                                                    'Class 4' => 8, 'Class 5' => 9, 'Class 6' => 10,

                                                    'JHS 1' => 11, 'JHS 2' => 12, 'JHS 3' => 13

                                                ];

                                                return ($order[$a] ?? 999) - ($order[$b] ?? 999);

                                            });

                                            

                                            foreach ($classCollections as $class => $data): 

                                                $collectionRate = $data['expected_total'] > 0 ? ($data['total'] / $data['expected_total']) * 100 : 0;

                                                $avgPerStudent = $data['student_count'] > 0 ? $data['total'] / $data['student_count'] : 0;

                                                $grandTotal += $data['total'];

                                                $grandExpected += $data['expected_total'];

                                                $totalStudents += $data['student_count'];

                                        ?>

                                            <tr>

                                                <td><strong><?php echo htmlspecialchars($class); ?></strong></td>

                                                <td class="text-center"><?php echo $data['student_count']; ?></td>

                                                <td class="text-center"><?php echo $data['payment_count']; ?></td>

                                                <td><?php echo formatCurrency($data['expected_total']); ?></td>

                                                <td><?php echo formatCurrency($data['total']); ?></td>

                                                <td>

                                                    <div class="progress" style="height: 20px;">

                                                        <div class="progress-bar <?php echo $collectionRate >= 70 ? 'bg-success' : ($collectionRate >= 40 ? 'bg-warning' : 'bg-danger'); ?>" 

                                                             role="progressbar" 

                                                             style="width: <?php echo $collectionRate; ?>%;" 

                                                             aria-valuenow="<?php echo $collectionRate; ?>" 

                                                             aria-valuemin="0" 

                                                             aria-valuemax="100">

                                                            <?php echo number_format($collectionRate, 1); ?>%

                                                        </div>

                                                    </div>

                                                </td>

                                                <td><?php echo formatCurrency($avgPerStudent); ?></td>

                                            </tr>

                                        <?php 

                                            endforeach; 

                                        ?>

                                    </tbody>

                                    <tfoot>

                                        <tr class="table-info">

                                            <th>Totals / Averages</th>

                                            <th class="text-center"><?php echo $totalStudents; ?></th>

                                            <th></th>

                                            <th><?php echo formatCurrency($grandExpected); ?></th>

                                            <th><?php echo formatCurrency($grandTotal); ?></th>

                                            <th>

                                                <?php 

                                                $overallRate = $grandExpected > 0 ? ($grandTotal / $grandExpected) * 100 : 0;

                                                echo number_format($overallRate, 1) . '%';

                                                ?>

                                            </th>

                                            <th><?php echo formatCurrency($totalStudents > 0 ? $grandTotal / $totalStudents : 0); ?></th>

                                        </tr>

                                    </tfoot>

                                    <?php endif; ?>

                                </table>

                            </div>



                        <?php elseif ($reportType === 'student_statement' && $studentId > 0): ?>

                            <?php

                            // Get student details

                            $conn = getDBConnection();

                            $studentQuery = "SELECT * FROM students WHERE id = $studentId";

                            $studentResult = $conn->query($studentQuery);

                            $student = $studentResult->fetch_assoc();

                            

                            // Get all payments for this student

                            $paymentQuery = "SELECT p.*, fs.fee_type, fs.amount as expected_amount

                                            FROM payments p

                                            JOIN fee_structures fs ON p.fee_structure_id = fs.id

                                            WHERE p.student_id = $studentId

                                            AND p.payment_date BETWEEN '$dateFrom' AND '$dateTo'

                                            ORDER BY p.payment_date DESC";

                            $paymentResult = $conn->query($paymentQuery);

                            $studentPayments = [];

                            $totalPaid = 0;

                            while ($row = $paymentResult->fetch_assoc()) {

                                $studentPayments[] = $row;

                                $totalPaid += $row['amount_paid'];

                            }

                            $conn->close();

                            ?>

                            

                            <div class="row mb-4">

                                <div class="col-md-12">

                                    <div class="card bg-light">

                                        <div class="card-body">

                                            <div class="row">

                                                <div class="col-md-3">

                                                    <strong>Student ID:</strong><br>

                                                    <?php echo htmlspecialchars($student['student_id']); ?>

                                                </div>

                                                <div class="col-md-3">

                                                    <strong>Name:</strong><br>

                                                    <?php echo htmlspecialchars($student['first_name'] . ' ' . $student['last_name']); ?>

                                                </div>

                                                <div class="col-md-2">

                                                    <strong>Class:</strong><br>

                                                    <span class="class-badge"><?php echo htmlspecialchars($student['class']); ?></span>

                                                </div>

                                                <div class="col-md-2">

                                                    <strong>Section:</strong><br>

                                                    <?php echo htmlspecialchars($student['section'] ?: '-'); ?>

                                                </div>

                                                <div class="col-md-2">

                                                    <strong>Total Paid:</strong><br>

                                                    <span class="text-success fw-bold"><?php echo formatCurrency($totalPaid); ?></span>

                                                </div>

                                            </div>

                                        </div>

                                    </div>

                                </div>

                            </div>

                            

                            <div class="table-responsive">

                                <table class="table table-hover" id="reportTable">

                                    <thead>

                                        <tr>

                                            <th>Date</th>

                                            <th>Fee Type</th>

                                            <th>Expected Amount</th>

                                            <th>Amount Paid</th>

                                            <th>Payment Method</th>

                                            <th>Transaction ID</th>

                                            <th>Notes</th>

                                        </tr>

                                    </thead>

                                    <tbody>

                                        <?php if (empty($studentPayments)): ?>

                                            <tr><td colspan="7" class="text-center py-4">No payments found for this student in the selected period</td></tr>

                                        <?php else: ?>

                                            <?php foreach ($studentPayments as $payment): ?>

                                            <tr>

                                                <td><?php echo date('d M Y', strtotime($payment['payment_date'])); ?></td>

                                                <td><?php echo htmlspecialchars($payment['fee_type']); ?></td>

                                                <td><?php echo formatCurrency($payment['expected_amount']); ?></td>

                                                <td class="text-success fw-bold"><?php echo formatCurrency($payment['amount_paid']); ?></td>

                                                <td><?php echo ucfirst($payment['payment_method']); ?></td>

                                                <td><?php echo htmlspecialchars($payment['transaction_id'] ?: '-'); ?></td>

                                                <td><?php echo htmlspecialchars($payment['notes'] ?: '-'); ?></td>

                                            </tr>

                                            <?php endforeach; ?>

                                        <?php endif; ?>

                                    </tbody>

                                    <tfoot>

                                        <tr class="table-info">

                                            <th colspan="3" class="text-end">Total:</th>

                                            <th><?php echo formatCurrency($totalPaid); ?></th>

                                            <th colspan="3"></th>

                                        </tr>

                                    </tfoot>

                                </table>

                            </div>



                        <?php elseif ($reportType === 'student_statement' && $studentId <= 0): ?>

                            <div class="alert alert-warning">

                                <i class="fas fa-exclamation-triangle me-2"></i>

                                Please select a student to view their statement.

                            </div>



                        <?php elseif ($reportType === 'profit_loss'): ?>

                            <div class="row">

                                <div class="col-md-6">

                                    <div class="card">

                                        <div class="card-header bg-success text-white">

                                            <h6 class="mb-0"><i class="fas fa-chart-pie me-2"></i>Income by Class</h6>

                                        </div>

                                        <div class="card-body">

                                            <?php 

                                            $totalIncome = 0;

                                            foreach ($classCollections as $class => $data): 

                                                $totalIncome += $data['total'];

                                            ?>

                                            <div class="summary-item">

                                                <span><?php echo htmlspecialchars($class); ?></span>

                                                <span class="text-success fw-bold"><?php echo formatCurrency($data['total']); ?></span>

                                            </div>

                                            <?php endforeach; ?>

                                            <hr>

                                            <div class="summary-item">

                                                <strong>Total Income</strong>

                                                <strong class="text-success"><?php echo formatCurrency($totalIncome); ?></strong>

                                            </div>

                                        </div>

                                    </div>

                                </div>

                                <div class="col-md-6">

                                    <div class="card">

                                        <div class="card-header bg-danger text-white">

                                            <h6 class="mb-0"><i class="fas fa-chart-line me-2"></i>Expenses by Category</h6>

                                        </div>

                                        <div class="card-body">

                                            <?php

                                            $expenseCategories = [];

                                            $totalExpenses = 0;

                                            foreach ($expenses as $exp) {

                                                if (!isset($expenseCategories[$exp['category']])) {

                                                    $expenseCategories[$exp['category']] = 0;

                                                }

                                                $expenseCategories[$exp['category']] += $exp['amount'];

                                                $totalExpenses += $exp['amount'];

                                            }

                                            foreach ($expenseCategories as $category => $amount):

                                            ?>

                                            <div class="summary-item">

                                                <span><?php echo htmlspecialchars($category); ?></span>

                                                <span class="text-danger fw-bold"><?php echo formatCurrency($amount); ?></span>

                                            </div>

                                            <?php endforeach; ?>

                                            <hr>

                                            <div class="summary-item">

                                                <strong>Total Expenses</strong>

                                                <strong class="text-danger"><?php echo formatCurrency($totalExpenses); ?></strong>

                                            </div>

                                        </div>

                                    </div>

                                </div>

                            </div>

                            

                            <div class="row mt-4">

                                <div class="col-md-12">

                                    <div class="card">

                                        <div class="card-header bg-primary text-white">

                                            <h6 class="mb-0"><i class="fas fa-balance-scale me-2"></i>Profit & Loss Summary</h6>

                                        </div>

                                        <div class="card-body">

                                            <div class="row">

                                                <div class="col-md-4">

                                                    <div class="text-center p-3">

                                                        <h5>Total Income</h5>

                                                        <h3 class="text-success"><?php echo formatCurrency($totalIncome); ?></h3>

                                                    </div>

                                                </div>

                                                <div class="col-md-4">

                                                    <div class="text-center p-3">

                                                        <h5>Total Expenses</h5>

                                                        <h3 class="text-danger"><?php echo formatCurrency($totalExpenses); ?></h3>

                                                    </div>

                                                </div>

                                                <div class="col-md-4">

                                                    <div class="text-center p-3">

                                                        <h5>Net <?php echo ($totalIncome - $totalExpenses) >= 0 ? 'Profit' : 'Loss'; ?></h5>

                                                        <h3 class="<?php echo ($totalIncome - $totalExpenses) >= 0 ? 'text-success' : 'text-danger'; ?>">

                                                            <?php echo formatCurrency($totalIncome - $totalExpenses); ?>

                                                        </h3>

                                                    </div>

                                                </div>

                                            </div>

                                        </div>

                                    </div>

                                </div>

                            </div>

                        <?php endif; ?>

                            </div>



                            <div class="tab-pane fade" id="online-payments" role="tabpanel">

                                

                                <!-- Filters -->

                                <div class="row g-2 mb-3">

                                  <div class="col-md-2">

                                    <input type="date" id="op_date_from" class="form-control form-control-sm" placeholder="From">

                                  </div>

                                  <div class="col-md-2">

                                    <input type="date" id="op_date_to" class="form-control form-control-sm" placeholder="To">

                                  </div>

                                  <div class="col-md-2">

                                    <select id="op_gateway" class="form-select form-select-sm">

                                      <option value="">All Gateways</option>

                                      <option value="paystack">Paystack</option>

                                      <option value="hubtel">Hubtel</option>

                                    </select>

                                  </div>

                                  <div class="col-md-2">

                                    <select id="op_method" class="form-select form-select-sm">

                                      <option value="">All Methods</option>

                                      <option value="card">Card</option>

                                      <option value="momo_mtn">MTN MoMo</option>

                                      <option value="momo_vodafone">Vodafone Cash</option>

                                      <option value="momo_airteltigo">AirtelTigo Money</option>

                                    </select>

                                  </div>

                                  <div class="col-md-2">

                                    <select id="op_status" class="form-select form-select-sm">

                                      <option value="">All Statuses</option>

                                      <option value="success">Success</option>

                                      <option value="pending">Pending</option>

                                      <option value="failed">Failed</option>

                                    </select>

                                  </div>

                                  <div class="col-md-2">

                                    <button class="btn btn-primary btn-sm w-100" onclick="loadOnlinePayments()">

                                      <i class="fas fa-filter me-1"></i>Filter

                                    </button>

                                  </div>

                                </div>



                                <!-- Summary cards -->

                                <div class="row g-2 mb-3" id="op_summary">

                                  <div class="col-md-3">

                                    <div class="card border-success">

                                      <div class="card-body py-2 text-center">

                                        <div class="small text-muted">Successful</div>

                                        <div class="fw-bold text-success" id="op_total_success">GHS 0.00</div>

                                      </div>

                                    </div>

                                  </div>

                                  <div class="col-md-3">

                                    <div class="card border-warning">

                                      <div class="card-body py-2 text-center">

                                        <div class="small text-muted">Pending</div>

                                        <div class="fw-bold text-warning" id="op_total_pending">0</div>

                                      </div>

                                    </div>

                                  </div>

                                  <div class="col-md-3">

                                    <div class="card border-danger">

                                      <div class="card-body py-2 text-center">

                                        <div class="small text-muted">Failed</div>

                                        <div class="fw-bold text-danger" id="op_total_failed">0</div>

                                      </div>

                                    </div>

                                  </div>

                                  <div class="col-md-3">

                                    <div class="card border-primary">

                                      <div class="card-body py-2 text-center">

                                        <div class="small text-muted">Total Collected</div>

                                        <div class="fw-bold text-primary" id="op_grand_total">GHS 0.00</div>

                                      </div>

                                    </div>

                                  </div>

                                </div>



                                <!-- Export -->

                                <div class="d-flex justify-content-end mb-2">

                                  <button class="btn btn-outline-secondary btn-sm" onclick="exportOnlinePaymentsCSV()">

                                    <i class="fas fa-download me-1"></i>Export CSV

                                  </button>

                                </div>



                                <!-- Table -->

                                <div class="table-responsive">

                                  <table id="onlinePaymentsTable" class="table table-sm table-hover">

                                    <thead>

                                      <tr>

                                        <th>Date</th>

                                        <th>Receipt No</th>

                                        <th>Student</th>

                                        <th>Class</th>

                                        <th>Amount (GHS)</th>

                                        <th>Method</th>

                                        <th>Gateway</th>

                                        <th>Status</th>

                                        <th>Action</th>

                                      </tr>

                                    </thead>

                                    <tbody id="onlinePaymentsBody"></tbody>

                                  </table>

                                </div>

                            </div>

                        </div>

                    </div>

                </div>

            </main>



            <!-- Footer -->

            <footer class="footer">

                <p>&copy; 2024 EduTrack Pro. All rights reserved.</p>

            </footer>

        </div>

    </div>



    <!-- Send Reminder Modal -->

    <div class="modal fade" id="reminderModal" tabindex="-1">

        <div class="modal-dialog">

            <div class="modal-content">

                <div class="modal-header">

                    <h5 class="modal-title"><i class="fas fa-bell me-2"></i>Send Payment Reminder</h5>

                    <button type="button" class="btn-close" data-bs-dismiss="modal"></button>

                </div>

                <div class="modal-body" id="reminderContent">

                    <p class="text-center">Loading...</p>

                </div>

                <div class="modal-footer">

                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>

                    <button type="button" class="btn btn-primary" onclick="sendReminderMessage()">Send Reminder</button>

                </div>

            </div>

        </div>

    </div>



    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>

    <script src="https://code.jquery.com/jquery-3.7.0.min.js"></script>

    <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>

    <script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>

    <script src="https://cdn.datatables.net/buttons/2.4.1/js/dataTables.buttons.min.js"></script>

    <script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.bootstrap5.min.js"></script>

    <script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.html5.min.js"></script>

    <script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.print.min.js"></script>

    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>

    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/pdfmake.min.js"></script>

    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/vfs_fonts.js"></script>

    <script src="js/main.js"></script>

    <script>

        $(document).ready(function() {

            // Initialize DataTable with export buttons

            if ($('#reportTable').length) {

                $('#reportTable').DataTable({

                    pageLength: 10,

                    lengthMenu: [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],

                    order: [[0, 'desc']],

                    dom: 'Bfrtip',

                    buttons: [

                        {

                            extend: 'excel',

                            text: '<i class="fas fa-file-excel me-2"></i>Excel',

                            className: 'btn btn-success btn-sm',

                            title: '<?php echo ucfirst(str_replace('_', ' ', $reportType)); ?> Report - <?php echo date('d M Y'); ?>',

                            exportOptions: {

                                columns: ':visible'

                            }

                        },

                        {

                            extend: 'pdf',

                            text: '<i class="fas fa-file-pdf me-2"></i>PDF',

                            className: 'btn btn-danger btn-sm',

                            title: '<?php echo ucfirst(str_replace('_', ' ', $reportType)); ?> Report - <?php echo date('d M Y'); ?>',

                            exportOptions: {

                                columns: ':visible'

                            }

                        },

                        {

                            extend: 'print',

                            text: '<i class="fas fa-print me-2"></i>Print',

                            className: 'btn btn-info btn-sm'

                        }

                    ],

                    language: {

                        emptyTable: "No data available"

                    }

                });

            }



            // Handle report type change

            $('#reportType').on('change', function() {

                const value = $(this).val();

                

                $('#classFilter, #studentFilter').hide();

                

                if (['collections', 'outstanding', 'class_summary', 'student_statement'].includes(value)) {

                    $('#classFilter').show();

                }

                

                if (value === 'student_statement') {

                    $('#studentFilter').show();

                }

            });

        });



        // Export report function

        function exportReport(format) {

            const formData = $('#reportForm').serialize();

            window.location.href = 'generate_report.php?' + formData + '&format=' + format;

        }



        // Send reminder function

        function sendReminder(studentId) {

            $('#reminderModal').modal('show');

            $('#reminderContent').html('<p class="text-center"><i class="fas fa-spinner fa-spin me-2"></i>Loading...</p>');

            

            $.ajax({

                url: 'ajax/get_student_reminder.php',

                type: 'GET',

                data: { student_id: studentId },

                success: function(response) {

                    $('#reminderContent').html(response);

                },

                error: function() {

                    $('#reminderContent').html('<p class="text-danger">Failed to load student data</p>');

                }

            });

        }



        function sendReminderMessage() {

            const studentId = $('#reminder_student_id').val();

            const message = $('#reminder_message').val();

            

            $.ajax({

                url: 'ajax/send_reminder.php',

                type: 'POST',

                data: { student_id: studentId, message: message },

                dataType: 'json',

                success: function(response) {

                    if (response.success) {

                        showToast('Reminder sent successfully!', 'success');

                        $('#reminderModal').modal('hide');

                    } else {

                        showToast('Failed to send reminder: ' + response.message, 'error');

                    }

                },

                error: function() {

                    showToast('Failed to send reminder', 'error');

                }

            });

        }



        function loadOnlinePayments() {

            const params = {

                date_from: document.getElementById('op_date_from').value,

                date_to: document.getElementById('op_date_to').value,

                gateway: document.getElementById('op_gateway').value,

                method: document.getElementById('op_method').value,

                status: document.getElementById('op_status').value

            };

            fetch('ajax/get_online_payments.php', {

                method: 'POST',

                headers: {'Content-Type':'application/json'},

                body: JSON.stringify(params)

            })

            .then(r => r.json())

            .then(data => {

                const tbody = document.getElementById('onlinePaymentsBody');

                tbody.innerHTML = '';

                let totalSuccess = 0, totalPending = 0, totalFailed = 0;

                data.payments.forEach(p => {

                    const statusBadge = {

                        success: '<span class="badge bg-success">Success</span>',

                        pending: '<span class="badge bg-warning text-dark">Pending</span>',

                        failed:  '<span class="badge bg-danger">Failed</span>'

                    }[p.status] || p.status;

                    const methodLabel = {

                        card:             'Card',

                        momo_mtn:         'MTN MoMo',

                        momo_vodafone:    'Vodafone Cash',

                        momo_airteltigo:  'AirtelTigo Money'

                    }[p.payment_method] || p.payment_method;

                    tbody.innerHTML += `<tr>

                      <td>${p.initiated_at}</td>

                      <td>${p.receipt_number}</td>

                      <td>${p.student_name}</td>

                      <td>${p.class}</td>

                      <td>${parseFloat(p.amount).toFixed(2)}</td>

                      <td>${methodLabel}</td>

                      <td>${p.gateway}</td>

                      <td>${statusBadge}</td>

                      <td><a href="payment_receipt.php?id=${p.id}" 

                             class="btn btn-xs btn-outline-primary btn-sm" 

                             target="_blank">Receipt</a></td>

                    </tr>`;

                    if (p.status === 'success') totalSuccess += parseFloat(p.amount);

                    else if (p.status === 'pending') totalPending++;

                    else if (p.status === 'failed') totalFailed++;

                });

                document.getElementById('op_total_success').textContent =

                    'GHS ' + totalSuccess.toFixed(2);

                document.getElementById('op_total_pending').textContent = totalPending;

                document.getElementById('op_total_failed').textContent = totalFailed;

                document.getElementById('op_grand_total').textContent =

                    'GHS ' + (data.grand_total || 0).toFixed(2);

            });

        }



        function exportOnlinePaymentsCSV() {

            const params = new URLSearchParams({

                date_from: document.getElementById('op_date_from').value,

                date_to: document.getElementById('op_date_to').value,

                gateway: document.getElementById('op_gateway').value,

                method: document.getElementById('op_method').value,

                status: document.getElementById('op_status').value,

                export: 'csv'

            });

            window.location.href = 'ajax/get_online_payments.php?' + params.toString();

        }



        // Auto-load when tab is clicked

        document.addEventListener('DOMContentLoaded', function() {

            const tab = document.getElementById('online-payments-tab');

            if (tab) {

                tab.addEventListener('shown.bs.tab', function() {

                    loadOnlinePayments();

                });

            }

        });



        // Show toast message

        function showToast(message, type) {

            // You can implement toast notification here

            alert(message);

        }

    </script>

</body>

</html>

