<?php

session_start();

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

    die('Unauthorized access');

}



require_once 'config.php';



$conn = getDBConnection();



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

$format = $_GET['format'] ?? 'pdf';

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

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

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

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



// Get school settings

$school_name = getSetting('school_name', 'School Name');

$school_address = getSetting('school_address', 'School Address');

$school_phone = getSetting('school_phone', 'School Phone');

$school_email = getSetting('school_email', 'School Email');

$academic_year = getSetting('academic_year', date('Y') . '-' . (date('Y')+1));



function getSetting($key, $default = '') {

    global $conn;

    $result = $conn->query("SELECT setting_value FROM settings WHERE setting_key = '$key'");

    if ($result && $row = $result->fetch_assoc()) {

        return $row['setting_value'];

    }

    return $default;

}



// Build query conditions

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

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



if ($format === 'excel') {

    // Generate Excel (CSV) file

    header('Content-Type: text/csv; charset=utf-8');

    header('Content-Disposition: attachment; filename=' . $report_type . '_report_' . date('Ymd') . '.csv');

    

    $output = fopen('php://output', 'w');

    

    switch ($report_type) {

        case 'collections':

            fputcsv($output, ['Date', 'Student ID', 'Student Name', 'Class', 'Fee Type', 'Amount Paid', 'Payment Method', 'Transaction ID']);

            

            $query = "SELECT p.payment_date, s.student_id, s.first_name, s.last_name, s.class, 

                             fs.fee_type, p.amount_paid, p.payment_method, p.transaction_id

                      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 '$date_from' AND '$date_to'

                      $classCondition $studentCondition

                      ORDER BY p.payment_date DESC";

            

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

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

                fputcsv($output, [

                    $row['payment_date'],

                    $row['student_id'],

                    $row['first_name'] . ' ' . $row['last_name'],

                    $row['class'],

                    $row['fee_type'],

                    $row['amount_paid'],

                    $row['payment_method'],

                    $row['transaction_id'] ?: ''

                ]);

            }

            break;

            

        case 'expenses':

            fputcsv($output, ['Date', 'Category', 'Description', 'Amount', 'Payment Method', 'Vendor']);

            

            $query = "SELECT expense_date, category, description, amount, payment_method, vendor 

                      FROM expenses 

                      WHERE expense_date BETWEEN '$date_from' AND '$date_to'

                      ORDER BY expense_date DESC";

            

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

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

                fputcsv($output, [

                    $row['expense_date'],

                    $row['category'],

                    $row['description'],

                    $row['amount'],

                    $row['payment_method'],

                    $row['vendor'] ?: ''

                ]);

            }

            break;

            

        case 'outstanding':

            fputcsv($output, ['Student ID', 'Student Name', 'Class', 'Total Fee', 'Total Paid', 'Outstanding']);

            

            $query = "SELECT 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 $studentCondition";

            

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

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

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

                if ($outstanding > 0) {

                    fputcsv($output, [

                        $row['student_id'],

                        $row['first_name'] . ' ' . $row['last_name'],

                        $row['class'],

                        $row['total_fee'],

                        $row['total_paid'],

                        $outstanding

                    ]);

                }

            }

            break;

            

        case 'class_summary':

            fputcsv($output, ['Class', 'Students', 'Payments', 'Expected Total', 'Collected', 'Collection Rate']);

            

            $query = "SELECT s.class, 

                             COUNT(DISTINCT s.id) as student_count,

                             COUNT(p.id) as payment_count,

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

                             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 '$date_from' AND '$date_to'

                      WHERE 1=1

                      $classCondition

                      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($query);

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

                $collection_rate = $row['expected_total'] > 0 ? ($row['total_collected'] / $row['expected_total']) * 100 : 0;

                fputcsv($output, [

                    $row['class'],

                    $row['student_count'],

                    $row['payment_count'],

                    $row['expected_total'],

                    $row['total_collected'],

                    number_format($collection_rate, 1) . '%'

                ]);

            }

            break;

            

        case 'profit_loss':

            // Get total collections

            $collections_query = "SELECT COALESCE(SUM(amount_paid), 0) as total 

                                 FROM payments 

                                 WHERE payment_date BETWEEN '$date_from' AND '$date_to'";

            $collections_result = $conn->query($collections_query);

            $total_collections = $collections_result->fetch_assoc()['total'];

            

            // Get total expenses

            $expenses_query = "SELECT COALESCE(SUM(amount), 0) as total 

                              FROM expenses 

                              WHERE expense_date BETWEEN '$date_from' AND '$date_to'";

            $expenses_result = $conn->query($expenses_query);

            $total_expenses = $expenses_result->fetch_assoc()['total'];

            

            fputcsv($output, ['Item', 'Amount']);

            fputcsv($output, ['Total Collections', $total_collections]);

            fputcsv($output, ['Total Expenses', $total_expenses]);

            fputcsv($output, ['Net Profit/Loss', $total_collections - $total_expenses]);

            break;

            

        case 'student_statement':

            if ($student_id > 0) {

                // Get student details

                $student_query = $conn->query("SELECT * FROM students WHERE id = $student_id");

                $student = $student_query->fetch_assoc();

                

                fputcsv($output, ['Student Statement for: ' . $student['first_name'] . ' ' . $student['last_name']]);

                fputcsv($output, ['Student ID: ' . $student['student_id'], 'Class: ' . $student['class']]);

                fputcsv($output, []);

                fputcsv($output, ['Date', 'Fee Type', 'Amount Paid', 'Payment Method', 'Transaction ID', 'Notes']);

                

                $query = "SELECT p.payment_date, fs.fee_type, p.amount_paid, p.payment_method, p.transaction_id, p.notes

                          FROM payments p

                          JOIN fee_structures fs ON p.fee_structure_id = fs.id

                          WHERE p.student_id = $student_id

                          AND p.payment_date BETWEEN '$date_from' AND '$date_to'

                          ORDER BY p.payment_date DESC";

                

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

                $total_paid = 0;

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

                    fputcsv($output, [

                        $row['payment_date'],

                        $row['fee_type'],

                        $row['amount_paid'],

                        $row['payment_method'],

                        $row['transaction_id'] ?: '',

                        $row['notes'] ?: ''

                    ]);

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

                }

                fputcsv($output, ['', '', 'Total: ' . formatCurrency($total_paid), '', '', '']);

            }

            break;

    }

    

    fclose($output);

    

} elseif ($format === 'pdf') {

    // For PDF, generate HTML that can be printed/saved as PDF

    header('Content-Type: text/html; charset=utf-8');

    

    // Get data based on report type

    $report_title = ucfirst(str_replace('_', ' ', $report_type)) . ' Report';

    

    // Start HTML output

    ?>

    <!DOCTYPE html>

    <html>

    <head>

        <title><?php echo $report_title; ?></title>

        <style>

            body {

                font-family: Arial, sans-serif;

                margin: 30px;

                line-height: 1.6;

            }

            .header {

                text-align: center;

                margin-bottom: 30px;

                padding-bottom: 20px;

                border-bottom: 2px solid #333;

            }

            h1 {

                color: #2c3e50;

                margin-bottom: 5px;

                font-size: 24px;

            }

            h2 {

                color: #34495e;

                margin-top: 0;

                font-size: 20px;

            }

            h3 {

                color: #34495e;

                font-size: 18px;

                margin: 20px 0 10px 0;

            }

            .school-info {

                color: #7f8c8d;

                margin: 5px 0;

            }

            .report-info {

                margin: 20px 0;

                padding: 15px;

                background: #f8f9fa;

                border-radius: 5px;

                border: 1px solid #dee2e6;

            }

            table {

                width: 100%;

                border-collapse: collapse;

                margin: 20px 0;

                font-size: 12px;

            }

            th {

                background: #3498db;

                color: white;

                padding: 10px;

                text-align: left;

                font-weight: bold;

            }

            td {

                padding: 8px;

                border: 1px solid #ddd;

            }

            tr:nth-child(even) {

                background: #f2f2f2;

            }

            .total-row {

                background: #2ecc71 !important;

                color: white;

                font-weight: bold;

            }

            .total-row td {

                color: white;

            }

            .text-right {

                text-align: right;

            }

            .text-center {

                text-align: center;

            }

            .text-danger {

                color: #e74c3c;

            }

            .text-success {

                color: #27ae60;

            }

            .badge {

                padding: 3px 7px;

                border-radius: 3px;

                font-size: 11px;

                font-weight: bold;

            }

            .badge-primary {

                background: #3498db;

                color: white;

            }

            .progress {

                height: 20px;

                background-color: #ecf0f1;

                border-radius: 10px;

                overflow: hidden;

            }

            .progress-bar {

                height: 100%;

                background-color: #3498db;

                text-align: center;

                color: white;

                font-size: 11px;

                line-height: 20px;

            }

            .progress-bar-success {

                background-color: #27ae60;

            }

            .progress-bar-warning {

                background-color: #f39c12;

            }

            .progress-bar-danger {

                background-color: #e74c3c;

            }

            .footer {

                margin-top: 30px;

                text-align: center;

                color: #7f8c8d;

                font-size: 11px;

                padding-top: 20px;

                border-top: 1px solid #dee2e6;

            }

            .class-badge {

                background-color: #e9ecef;

                padding: 3px 8px;

                border-radius: 12px;

                font-size: 11px;

            }

            @media print {

                .no-print {

                    display: none;

                }

                body {

                    margin: 0.5in;

                }

            }

        </style>

    </head>

    <body>

        <div class="header">

            <h1><?php echo htmlspecialchars($school_name); ?></h1>

            <div class="school-info"><?php echo nl2br(htmlspecialchars($school_address)); ?></div>

            <div class="school-info">Tel: <?php echo htmlspecialchars($school_phone); ?> | Email: <?php echo htmlspecialchars($school_email); ?></div>

            <h2><?php echo $report_title; ?></h2>

        </div>

        

        <div class="report-info">

            <table style="width: 100%; border: none; background: transparent;">

                <tr style="background: transparent;">

                    <td style="border: none;"><strong>Period:</strong> <?php echo date('d M Y', strtotime($date_from)); ?> - <?php echo date('d M Y', strtotime($date_to)); ?></td>

                    <td style="border: none;"><strong>Academic Year:</strong> <?php echo htmlspecialchars($academic_year); ?></td>

                </tr>

                <?php if ($class): ?>

                <tr style="background: transparent;">

                    <td style="border: none;" colspan="2"><strong>Class:</strong> <?php echo htmlspecialchars($class); ?></td>

                </tr>

                <?php endif; ?>

                <?php if ($student_id > 0): ?>

                <?php

                $student_query = $conn->query("SELECT first_name, last_name, student_id FROM students WHERE id = $student_id");

                if ($student = $student_query->fetch_assoc()):

                ?>

                <tr style="background: transparent;">

                    <td style="border: none;" colspan="2">

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

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

                    </td>

                </tr>

                <?php endif; ?>

                <?php endif; ?>

                <tr style="background: transparent;">

                    <td style="border: none;" colspan="2"><strong>Generated On:</strong> <?php echo date('d M Y H:i:s'); ?></td>

                </tr>

            </table>

        </div>

        

        <?php

        // COLLECTIONS REPORT

        if ($report_type === 'collections'):

            $query = "SELECT p.payment_date, s.student_id, s.first_name, s.last_name, s.class, 

                             fs.fee_type, p.amount_paid, p.payment_method, p.transaction_id

                      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 '$date_from' AND '$date_to'

                      $classCondition $studentCondition

                      ORDER BY p.payment_date DESC";

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

            $total = 0;

        ?>

            <table>

                <thead>

                    <tr>

                        <th>Date</th>

                        <th>Student ID</th>

                        <th>Student Name</th>

                        <th>Class</th>

                        <th>Fee Type</th>

                        <th class="text-right">Amount</th>

                        <th>Method</th>

                        <th>Transaction ID</th>

                    </tr>

                </thead>

                <tbody>

                    <?php while ($row = $result->fetch_assoc()): 

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

                    ?>

                    <tr>

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

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

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

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

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

                        <td class="text-right"><?php echo formatCurrency($row['amount_paid']); ?></td>

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

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

                    </tr>

                    <?php endwhile; ?>

                </tbody>

                <tfoot>

                    <tr class="total-row">

                        <td colspan="5" class="text-right"><strong>Total:</strong></td>

                        <td class="text-right"><strong><?php echo formatCurrency($total); ?></strong></td>

                        <td colspan="2"></td>

                    </tr>

                </tfoot>

            </table>



        <?php

        // EXPENSES REPORT

        elseif ($report_type === 'expenses'):

            $query = "SELECT expense_date, category, description, amount, payment_method, vendor 

                      FROM expenses 

                      WHERE expense_date BETWEEN '$date_from' AND '$date_to'

                      ORDER BY expense_date DESC";

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

            $total = 0;

            $category_totals = [];

        ?>

            <table>

                <thead>

                    <tr>

                        <th>Date</th>

                        <th>Category</th>

                        <th>Description</th>

                        <th class="text-right">Amount</th>

                        <th>Method</th>

                        <th>Vendor</th>

                    </tr>

                </thead>

                <tbody>

                    <?php while ($row = $result->fetch_assoc()): 

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

                        if (!isset($category_totals[$row['category']])) {

                            $category_totals[$row['category']] = 0;

                        }

                        $category_totals[$row['category']] += $row['amount'];

                    ?>

                    <tr>

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

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

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

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

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

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

                    </tr>

                    <?php endwhile; ?>

                </tbody>

                <tfoot>

                    <tr class="total-row">

                        <td colspan="3" class="text-right"><strong>Total:</strong></td>

                        <td class="text-right"><strong><?php echo formatCurrency($total); ?></strong></td>

                        <td colspan="2"></td>

                    </tr>

                </tfoot>

            </table>

            

            <?php if (!empty($category_totals)): ?>

            <h3>Summary by Category</h3>

            <table style="width: 60%;">

                <thead>

                    <tr>

                        <th>Category</th>

                        <th class="text-right">Amount</th>

                        <th class="text-right">Percentage</th>

                    </tr>

                </thead>

                <tbody>

                    <?php foreach ($category_totals as $category => $amount): ?>

                    <tr>

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

                        <td class="text-right"><?php echo formatCurrency($amount); ?></td>

                        <td class="text-right"><?php echo number_format(($amount / $total) * 100, 1); ?>%</td>

                    </tr>

                    <?php endforeach; ?>

                </tbody>

            </table>

            <?php endif; ?>



        <?php

        // OUTSTANDING FEES REPORT

        elseif ($report_type === 'outstanding'):

            $query = "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 $studentCondition

                      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($query);

            $total_outstanding = 0;

        ?>

            <table>

                <thead>

                    <tr>

                        <th>Student ID</th>

                        <th>Student Name</th>

                        <th>Class</th>

                        <th class="text-right">Total Fee</th>

                        <th class="text-right">Total Paid</th>

                        <th class="text-right">Outstanding</th>

                    </tr>

                </thead>

                <tbody>

                    <?php while ($row = $result->fetch_assoc()): 

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

                        if ($outstanding > 0):

                            $total_outstanding += $outstanding;

                    ?>

                    <tr>

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

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

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

                        <td class="text-right"><?php echo formatCurrency($row['total_fee']); ?></td>

                        <td class="text-right"><?php echo formatCurrency($row['total_paid']); ?></td>

                        <td class="text-right text-danger"><?php echo formatCurrency($outstanding); ?></td>

                    </tr>

                    <?php 

                        endif;

                    endwhile; 

                    ?>

                </tbody>

                <tfoot>

                    <tr class="total-row">

                        <td colspan="5" class="text-right"><strong>Total Outstanding:</strong></td>

                        <td class="text-right"><strong><?php echo formatCurrency($total_outstanding); ?></strong></td>

                    </tr>

                </tfoot>

            </table>



        <?php

        // CLASS SUMMARY REPORT

        elseif ($report_type === 'class_summary'):

            $query = "SELECT s.class, 

                             COUNT(DISTINCT s.id) as student_count,

                             COUNT(p.id) as payment_count,

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

                             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 '$date_from' AND '$date_to'

                      WHERE 1=1

                      $classCondition

                      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($query);

            $grand_total = 0;

            $grand_expected = 0;

            $total_students = 0;

        ?>

            <table>

                <thead>

                    <tr>

                        <th>Class</th>

                        <th class="text-center">Students</th>

                        <th class="text-center">Payments</th>

                        <th class="text-right">Expected</th>

                        <th class="text-right">Collected</th>

                        <th class="text-center">Collection Rate</th>

                    </tr>

                </thead>

                <tbody>

                    <?php while ($row = $result->fetch_assoc()): 

                        $collection_rate = $row['expected_total'] > 0 ? ($row['total_collected'] / $row['expected_total']) * 100 : 0;

                        $grand_total += $row['total_collected'];

                        $grand_expected += $row['expected_total'];

                        $total_students += $row['student_count'];

                        

                        // Determine progress bar color

                        $bar_class = 'progress-bar';

                        if ($collection_rate >= 70) {

                            $bar_class .= ' progress-bar-success';

                        } elseif ($collection_rate >= 40) {

                            $bar_class .= ' progress-bar-warning';

                        } else {

                            $bar_class .= ' progress-bar-danger';

                        }

                    ?>

                    <tr>

                        <td><strong><?php echo htmlspecialchars($row['class']); ?></strong></td>

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

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

                        <td class="text-right"><?php echo formatCurrency($row['expected_total']); ?></td>

                        <td class="text-right"><?php echo formatCurrency($row['total_collected']); ?></td>

                        <td style="width: 150px;">

                            <div class="progress">

                                <div class="<?php echo $bar_class; ?>" style="width: <?php echo $collection_rate; ?>%;">

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

                                </div>

                            </div>

                        </td>

                    </tr>

                    <?php endwhile; ?>

                </tbody>

                <tfoot>

                    <tr class="total-row">

                        <th>Totals / Averages</th>

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

                        <th></th>

                        <th class="text-right"><?php echo formatCurrency($grand_expected); ?></th>

                        <th class="text-right"><?php echo formatCurrency($grand_total); ?></th>

                        <th class="text-center">

                            <?php 

                            $overall_rate = $grand_expected > 0 ? ($grand_total / $grand_expected) * 100 : 0;

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

                            ?>

                        </th>

                    </tr>

                </tfoot>

            </table>



        <?php

        // PROFIT & LOSS REPORT

        elseif ($report_type === 'profit_loss'):

            // Get total collections

            $collections_query = "SELECT COALESCE(SUM(amount_paid), 0) as total 

                                 FROM payments 

                                 WHERE payment_date BETWEEN '$date_from' AND '$date_to'";

            $collections_result = $conn->query($collections_query);

            $total_collections = $collections_result->fetch_assoc()['total'];

            

            // Get total expenses

            $expenses_query = "SELECT COALESCE(SUM(amount), 0) as total 

                              FROM expenses 

                              WHERE expense_date BETWEEN '$date_from' AND '$date_to'";

            $expenses_result = $conn->query($expenses_query);

            $total_expenses = $expenses_result->fetch_assoc()['total'];

            

            $net_profit = $total_collections - $total_expenses;

            

            // Get class-wise collections

            $class_query = "SELECT s.class, COALESCE(SUM(p.amount_paid), 0) as total

                           FROM students s

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

                               AND p.payment_date BETWEEN '$date_from' AND '$date_to'

                           GROUP BY s.class

                           ORDER BY s.class";

            $class_result = $conn->query($class_query);

            

            // Get expense categories

            $exp_cat_query = "SELECT category, SUM(amount) as total

                             FROM expenses 

                             WHERE expense_date BETWEEN '$date_from' AND '$date_to'

                             GROUP BY category";

            $exp_cat_result = $conn->query($exp_cat_query);

        ?>

            <div style="display: flex; gap: 20px; margin-bottom: 30px;">

                <div style="flex: 1; padding: 20px; background: linear-gradient(135deg, #27ae60, #2ecc71); color: white; border-radius: 10px;">

                    <h3 style="margin: 0 0 10px 0;">Total Collections</h3>

                    <h2 style="margin: 0;"><?php echo formatCurrency($total_collections); ?></h2>

                </div>

                <div style="flex: 1; padding: 20px; background: linear-gradient(135deg, #e74c3c, #c0392b); color: white; border-radius: 10px;">

                    <h3 style="margin: 0 0 10px 0;">Total Expenses</h3>

                    <h2 style="margin: 0;"><?php echo formatCurrency($total_expenses); ?></h2>

                </div>

                <div style="flex: 1; padding: 20px; background: linear-gradient(135deg, #3498db, #2980b9); color: white; border-radius: 10px;">

                    <h3 style="margin: 0 0 10px 0;">Net <?php echo $net_profit >= 0 ? 'Profit' : 'Loss'; ?></h3>

                    <h2 style="margin: 0;"><?php echo formatCurrency($net_profit); ?></h2>

                </div>

            </div>

            

            <div style="display: flex; gap: 20px;">

                <div style="flex: 1;">

                    <h3>Income by Class</h3>

                    <table>

                        <thead>

                            <tr>

                                <th>Class</th>

                                <th class="text-right">Amount</th>

                            </tr>

                        </thead>

                        <tbody>

                            <?php while ($row = $class_result->fetch_assoc()): ?>

                            <tr>

                                <td><?php echo htmlspecialchars($row['class']); ?></td>

                                <td class="text-right text-success"><?php echo formatCurrency($row['total']); ?></td>

                            </tr>

                            <?php endwhile; ?>

                        </tbody>

                    </table>

                </div>

                <div style="flex: 1;">

                    <h3>Expenses by Category</h3>

                    <table>

                        <thead>

                            <tr>

                                <th>Category</th>

                                <th class="text-right">Amount</th>

                            </tr>

                        </thead>

                        <tbody>

                            <?php while ($row = $exp_cat_result->fetch_assoc()): ?>

                            <tr>

                                <td><?php echo htmlspecialchars($row['category']); ?></td>

                                <td class="text-right text-danger"><?php echo formatCurrency($row['total']); ?></td>

                            </tr>

                            <?php endwhile; ?>

                        </tbody>

                    </table>

                </div>

            </div>



        <?php

        // STUDENT STATEMENT REPORT

        elseif ($report_type === 'student_statement' && $student_id > 0):

            // Get student details

            $student_query = $conn->query("SELECT * FROM students WHERE id = $student_id");

            $student = $student_query->fetch_assoc();

            

            // Get all payments

            $payments_query = "SELECT p.payment_date, fs.fee_type, p.amount_paid, p.payment_method, p.transaction_id, p.notes

                              FROM payments p

                              JOIN fee_structures fs ON p.fee_structure_id = fs.id

                              WHERE p.student_id = $student_id

                              AND p.payment_date BETWEEN '$date_from' AND '$date_to'

                              ORDER BY p.payment_date DESC";

            $payments_result = $conn->query($payments_query);

            $total_paid = 0;

        ?>

            <div style="margin-bottom: 30px;">

                <table style="width: 100%; border: 2px solid #3498db;">

                    <tr>

                        <td style="padding: 10px; width: 25%;"><strong>Student ID:</strong></td>

                        <td style="padding: 10px; width: 25%;"><?php echo htmlspecialchars($student['student_id']); ?></td>

                        <td style="padding: 10px; width: 25%;"><strong>Name:</strong></td>

                        <td style="padding: 10px; width: 25%;"><?php echo htmlspecialchars($student['first_name'] . ' ' . $student['last_name']); ?></td>

                    </tr>

                    <tr>

                        <td style="padding: 10px;"><strong>Class:</strong></td>

                        <td style="padding: 10px;"><?php echo htmlspecialchars($student['class']); ?></td>

                        <td style="padding: 10px;"><strong>Section:</strong></td>

                        <td style="padding: 10px;"><?php echo htmlspecialchars($student['section'] ?: '-'); ?></td>

                    </tr>

                </table>

            </div>

            

            <h3>Payment History</h3>

            <table>

                <thead>

                    <tr>

                        <th>Date</th>

                        <th>Fee Type</th>

                        <th class="text-right">Amount</th>

                        <th>Method</th>

                        <th>Transaction ID</th>

                        <th>Notes</th>

                    </tr>

                </thead>

                <tbody>

                    <?php while ($payment = $payments_result->fetch_assoc()): 

                        $total_paid += $payment['amount_paid'];

                    ?>

                    <tr>

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

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

                        <td class="text-right text-success"><?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 endwhile; ?>

                </tbody>

                <tfoot>

                    <tr class="total-row">

                        <td colspan="2" class="text-right"><strong>Total:</strong></td>

                        <td class="text-right"><strong><?php echo formatCurrency($total_paid); ?></strong></td>

                        <td colspan="3"></td>

                    </tr>

                </tfoot>

            </table>



        <?php

        elseif ($report_type === 'student_statement' && $student_id <= 0):

            echo '<div class="alert alert-warning">Please select a student to view their statement.</div>';

        endif;

        ?>

        

        <div class="footer">

            <p>This is a computer-generated report. No signature is required.</p>

            <p>&copy; <?php echo date('Y'); ?> <?php echo htmlspecialchars($school_name); ?>. All rights reserved.</p>

        </div>

        

        <div class="no-print" style="text-align: center; margin-top: 30px;">

            <button onclick="window.print()" style="padding: 10px 20px; background: #3498db; color: white; border: none; border-radius: 5px; cursor: pointer; margin-right: 10px;">

                <i class="fas fa-print"></i> Print / Save as PDF

            </button>

            <button onclick="window.close()" style="padding: 10px 20px; background: #95a5a6; color: white; border: none; border-radius: 5px; cursor: pointer;">

                Close

            </button>

        </div>

        

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

    </body>

    </html>

    <?php

}



$conn->close();

?>