<?php
require_once __DIR__ . '/../library/auth.php'; 
$school_id = $_SESSION['school_id'];
$current_session_id = get_current_session_id($pdo);
allow_roles(['admin', 'accountant']);

// ======================================================
// TOTAL FEES INCOME
// ======================================================

$stmt = $pdo->prepare("
    SELECT SUM(paid_amount)
    FROM fee_payments
    WHERE school_id = ?
AND session_id = ?
");

$stmt->execute([
    $school_id,
    $current_session_id
]);

$total_fees = $stmt->fetchColumn() ?: 0;


// ======================================================
// TOTAL OTHER INCOME
// ======================================================

$stmt = $pdo->prepare("
    SELECT SUM(amount)
    FROM income
    WHERE school_id = ?
AND session_id = ?
");

$stmt->execute([
    $school_id,
    $current_session_id
]);

$total_other_income = $stmt->fetchColumn() ?: 0;


// ======================================================
// GRAND TOTAL INCOME
// ======================================================

$grand_total_income = $total_fees + $total_other_income;


// ======================================================
// TOTAL EXPENSE
// ======================================================

$stmt = $pdo->prepare("
    SELECT SUM(amount)
    FROM expenses
    WHERE school_id = ?
AND session_id = ?
");

$stmt->execute([
    $school_id,
    $current_session_id
]);

$total_expense = $stmt->fetchColumn() ?: 0;


// ======================================================
// NET BALANCE
// ======================================================

$net_balance = $grand_total_income - $total_expense;


// ======================================================
// FILTER LOGIC
// ======================================================

$where = [];
$params = [];

$where[] = "e.school_id = ?";
$params[] = $school_id;


// Category Filter
if (!empty($_GET['category_id'])) {

    $where[] = "e.category_id = ?";
    $params[] = intval($_GET['category_id']);
}


// Final WHERE SQL
$where_sql = "WHERE " . implode(" AND ", $where);


// ======================================================
// FETCH EXPENSES
// ======================================================

$stmt = $pdo->prepare("
    SELECT
        e.*,
        c.name AS category_name
    FROM expenses e
    LEFT JOIN expense_categories c
        ON c.id = e.category_id
    $where_sql
    ORDER BY e.payment_date DESC, e.id DESC
");

$stmt->execute($params);

$expenses = $stmt->fetchAll(PDO::FETCH_ASSOC);
$page_title = 'Expense Records';
require_once __DIR__ . '/../include/header.php';
require_once __DIR__ . '/../include/sidebar.php';

?>

<div class="content-wrapper">

    <section class="content-header">

        <div class="container-fluid">

            <div class="row mb-2">

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

                    <h1>Expense Records</h1>

                </div>

                <div class="col-sm-6 text-right">

                    <a href="add_expense.php" class="btn btn-danger">

                        <i class="fa fa-plus"></i>
                        Add New Expense

                    </a>

                </div>

            </div>

        </div>

    </section>


    <section class="content">

        <div class="container-fluid">

            <?php if(isset($_GET['msg'])): ?>

                <div class="alert alert-success">

                    <?= htmlspecialchars($_GET['msg']) ?>

                </div>

            <?php endif; ?>


            <!-- Stats Cards -->

            <div class="row">

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

                    <div class="info-box bg-success">

                        <span class="info-box-icon">
                            <i class="fa fa-wallet"></i>
                        </span>

                        <div class="info-box-content">

                            <span class="info-box-text">
                                Total Income (Fees+Inc)
                            </span>

                            <span class="info-box-number">
                                ₹<?= number_format($grand_total_income, 2) ?>
                            </span>

                        </div>

                    </div>

                </div>


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

                    <div class="info-box bg-danger">

                        <span class="info-box-icon">
                            <i class="fa fa-minus-circle"></i>
                        </span>

                        <div class="info-box-content">

                            <span class="info-box-text">
                                Total Expenses
                            </span>

                            <span class="info-box-number">
                                ₹<?= number_format($total_expense, 2) ?>
                            </span>

                        </div>

                    </div>

                </div>


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

                    <div class="info-box bg-<?= $net_balance < 0 ? 'dark' : 'primary' ?>">

                        <span class="info-box-icon">
                            <i class="fa fa-balance-scale"></i>
                        </span>

                        <div class="info-box-content">

                            <span class="info-box-text">
                                Net Balance
                            </span>

                            <span class="info-box-number">
                                ₹<?= number_format($net_balance, 2) ?>
                            </span>

                        </div>

                    </div>

                </div>

            </div>


            <!-- Expense Table -->

            <div class="card">

                <div class="card-body table-responsive">

                    <table class="table table-bordered table-striped">

                        <thead>

                            <tr>

                                <th>Date</th>

                                <th>Category</th>

                                <th>Description</th>

                                <th>Mode</th>

                                <th>Amount</th>

                                <th>Action</th>

                            </tr>

                        </thead>


                        <tbody>

                            <?php if(count($expenses) > 0): ?>

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

                                    <tr>

                                        <td>

                                            <?= date('d-m-Y', strtotime($row['payment_date'])) ?>

                                        </td>


                                        <td>

                                            <?= htmlspecialchars($row['category_name']) ?>

                                        </td>


                                        <td>

                                            <?= htmlspecialchars($row['description']) ?>

                                        </td>


                                        <td>

                                            <?= htmlspecialchars($row['payment_mode']) ?>

                                        </td>


                                        <td class="text-bold text-danger">

                                            ₹<?= number_format($row['amount'], 2) ?>

                                        </td>


                                        <td>

                                            <a
                                                href="edit_expense.php?id=<?= $row['id'] ?>"
                                                class="btn btn-warning btn-sm"
                                                title="Edit"
                                            >

                                                <i class="fa fa-edit"></i>

                                            </a>


                                            <a
                                                href="process.php?delete=<?= $row['id'] ?>"
                                                class="btn btn-danger btn-sm"
                                                onclick="return confirm('Sure?')"
                                                title="Delete"
                                            >

                                                <i class="fa fa-trash"></i>

                                            </a>

                                        </td>

                                    </tr>

                                <?php endforeach; ?>

                            <?php else: ?>

                                <tr>

                                    <td colspan="6" class="text-center text-danger">

                                        No Expense Records Found!

                                    </td>

                                </tr>

                            <?php endif; ?>

                        </tbody>

                    </table>

                </div>

            </div>

        </div>

    </section>

</div>

<?php require_once __DIR__ . '/../include/footer.php'; ?>