<?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 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;


// ======================================================
// TOTAL FEES COLLECTION
// ======================================================

$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;


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

$grand_total = $total_other_income + $total_fees;


// ======================================================
// TODAY INCOME
// ======================================================

$stmt = $pdo->prepare("
    SELECT
        SUM(amount)

    FROM income

    WHERE school_id = ?
    AND session_id = ?
    AND DATE(payment_date) = CURDATE()
");

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

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


// ======================================================
// FILTERS
// ======================================================

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


// SCHOOL FILTER

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


// SESSION FILTER

$where[] = "i.session_id = ?";
$params[] = $current_session_id;


// CATEGORY FILTER

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

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


// START DATE

if (!empty($_GET['start_date'])) {

    $where[] = "i.payment_date >= ?";
    $params[] = $_GET['start_date'];
}


// END DATE

if (!empty($_GET['end_date'])) {

    $where[] = "i.payment_date <= ?";
    $params[] = $_GET['end_date'];
}


// ======================================================
// BUILD WHERE
// ======================================================

$where_sql = '';

if (!empty($where)) {

    $where_sql = 'WHERE ' . implode(' AND ', $where);
}


// ======================================================
// FETCH INCOME
// ======================================================

$stmt = $pdo->prepare("
    SELECT

        i.*,

        ic.name AS category_name

    FROM income i

    LEFT JOIN income_categories ic
        ON ic.id = i.category_id

    $where_sql

    ORDER BY i.payment_date DESC, i.id DESC
");

$stmt->execute($params);

$incomes = $stmt->fetchAll(PDO::FETCH_ASSOC);


// ======================================================
// FETCH CATEGORIES
// ======================================================

$stmt = $pdo->prepare("
    SELECT

        id,
        name

    FROM income_categories

    WHERE school_id = ?

    ORDER BY name ASC
");

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

$categories = $stmt->fetchAll(PDO::FETCH_ASSOC);


// ======================================================

$page_title = 'Income 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>

                        Income Records

                    </h1>

                </div>

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

                    <a
                        href="add.php"
                        class="btn btn-primary"
                    >

                        Add New Income

                    </a>

                </div>

            </div>

        </div>

    </section>

    <section class="content">

        <div class="container-fluid">

            <!-- MESSAGE -->

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

                <div class="alert alert-success">

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

                </div>

            <?php endif; ?>

            <!-- STATS -->

            <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">

                                Grand Total

                            </span>

                            <span class="info-box-number">

                                ₹<?= number_format($grand_total, 2) ?>

                            </span>

                        </div>

                    </div>

                </div>

                <!-- TOTAL OTHER INCOME -->

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

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

                        <span class="info-box-icon">

                            <i class="fa fa-money-bill"></i>

                        </span>

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

                            <span class="info-box-text">

                                Other Income

                            </span>

                            <span class="info-box-number">

                                ₹<?= number_format($total_other_income, 2) ?>

                            </span>

                        </div>

                    </div>

                </div>

                <!-- TODAY INCOME -->

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

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

                        <span class="info-box-icon">

                            <i class="fa fa-calendar-day"></i>

                        </span>

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

                            <span class="info-box-text">

                                Today Income

                            </span>

                            <span class="info-box-number">

                                ₹<?= number_format($today_income, 2) ?>

                            </span>

                        </div>

                    </div>

                </div>

            </div>

            <!-- FILTER -->

            <div class="card card-outline card-primary">

                <div class="card-body">

                    <form method="GET">

                        <div class="row">

                            <!-- CATEGORY -->

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

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

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

                                    <?php foreach ($categories as $cat): ?>

                                        <option
                                            value="<?= htmlspecialchars($cat['name']) ?>"
                                            <?= (($_GET['category_id'] ?? '') === $cat['name']) ? 'selected' : '' ?>
                                        >

                                            <?= htmlspecialchars($cat['name']) ?>

                                        </option>

                                    <?php endforeach; ?>

                                </select>

                            </div>

                            <!-- START DATE -->

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

                                <input
                                    type="date"
                                    name="start_date"
                                    class="form-control"
                                    value="<?= htmlspecialchars($_GET['start_date'] ?? '') ?>"
                                >

                            </div>

                            <!-- END DATE -->

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

                                <input
                                    type="date"
                                    name="end_date"
                                    class="form-control"
                                    value="<?= htmlspecialchars($_GET['end_date'] ?? '') ?>"
                                >

                            </div>

                            <!-- BUTTON -->

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

                                <button
                                    type="submit"
                                    class="btn btn-secondary"
                                >

                                    Filter

                                </button>

                                <a
                                    href="view_income.php"
                                    class="btn btn-default"
                                >

                                    Reset

                                </a>

                            </div>

                        </div>

                    </form>

                </div>

            </div>

            <!-- TABLE -->

            <div class="card">

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

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

                        <thead>

                            <tr>

                                <th>Date</th>
                                <th>Receipt No</th>
                                <th>Category</th>
                                <th>Description</th>
                                <th>Mode</th>
                                <th>Amount</th>
                                <th>Action</th>

                            </tr>

                        </thead>

                        <tbody>

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

                                <tr>

                                    <td
                                        colspan="7"
                                        class="text-center text-muted"
                                    >

                                        No income records found

                                    </td>

                                </tr>

                            <?php else: ?>

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

                                    <tr>

                                        <td>

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

                                        </td>

                                        <td>

                                            <strong>

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

                                            </strong>

                                        </td>

                                        <td>

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

                                        </td>

                                        <td>

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

                                        </td>

                                        <td>

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

                                        </td>

                                        <td class="text-success font-weight-bold">

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

                                        </td>

                                        <td>

                                            <!-- EDIT -->

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

                                                Edit

                                            </a>

                                            <!-- DELETE -->

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

                                                Delete

                                            </a>

                                        </td>

                                    </tr>

                                <?php endforeach; ?>

                            <?php endif; ?>

                        </tbody>

                    </table>

                </div>

            </div>

        </div>

    </section>

</div>

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