<?php

declare(strict_types=1);

require_once __DIR__ . '/../library/auth.php';

allow_roles(['admin', 'accountant', 'teacher']);

$page_title = "Transport Report";

$school_id = (int) $_SESSION['school_id'];

$current_session_id = get_current_session_id($pdo);

$route_filter = trim($_GET['route'] ?? '');

$students = [];

$total_students = 0;

$total_transport_fees = 0;

/*
|--------------------------------------------------------------------------
| FETCH ROUTES
|--------------------------------------------------------------------------
*/

$routeStmt = $pdo->prepare("

    SELECT DISTINCT

        transport_route

    FROM student_transport

    WHERE school_id = ?
    AND transport_route IS NOT NULL
    AND transport_route != ''

    ORDER BY transport_route ASC

");

$routeStmt->execute([

    $school_id

]);

$routes = $routeStmt->fetchAll(PDO::FETCH_COLUMN);

/*
|--------------------------------------------------------------------------
| FETCH TRANSPORT STUDENTS
|--------------------------------------------------------------------------
*/

$query = "

    SELECT

        ss.roll_no,
        ss.status,

        s.id AS student_id,
        s.name,
        s.gender,
        s.phone,

        sp.father_name,

        c.class_name,
        c.section,

        st.transport_route,
        st.transport_fees

    FROM student_sessions ss

    INNER JOIN students s
        ON s.id = ss.student_id

    LEFT JOIN student_parents sp
        ON sp.student_id = s.id

    LEFT JOIN student_transport st
        ON st.student_id = s.id

    INNER JOIN classes c
        ON c.id = ss.class_id

    WHERE ss.school_id = ?
    AND ss.session_id = ?
    AND ss.status = 'Active'

    AND st.transport_route IS NOT NULL
    AND st.transport_route != ''

";

$params = [

    $school_id,
    $current_session_id

];

/*
|--------------------------------------------------------------------------
| FILTER ROUTE
|--------------------------------------------------------------------------
*/

if ($route_filter !== '') {

    $query .= "

        AND st.transport_route = ?

    ";

    $params[] = $route_filter;
}

$query .= "

    ORDER BY
    st.transport_route ASC,
    ss.roll_no ASC,
    s.name ASC

";

$stmt = $pdo->prepare($query);

$stmt->execute($params);

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

/*
|--------------------------------------------------------------------------
| TOTALS
|--------------------------------------------------------------------------
*/

foreach ($students as $student) {

    $total_students++;

    $total_transport_fees +=
        (float) ($student['transport_fees'] ?? 0);
}

require_once __DIR__ . '/../include/header.php';

require_once __DIR__ . '/../include/sidebar.php';

?>

<style>

.modern-card {

    border: none;

    border-radius: 18px;

    overflow: hidden;

    box-shadow: 0 4px 18px rgba(0,0,0,0.06);
}

.modern-card .card-header {

    background: #fff;

    border-bottom: 1px solid #eef2f7;

    padding: 18px 22px;
}

.filter-box {

    background: linear-gradient(135deg,#1e293b,#0f172a);

    border-radius: 18px;

    padding: 30px;

    color: #fff;

    margin-bottom: 25px;
}

.table-modern thead {

    background: #111827;

    color: #fff;
}

.table-modern th {

    border: none !important;

    font-size: 14px;

    font-weight: 600;
}

.stat-card {

    border-radius: 18px;

    padding: 24px;

    color: #fff;

    margin-bottom: 25px;
}

.empty-state {

    text-align: center;

    padding: 70px 20px;
}

.empty-state i {

    font-size: 70px;

    color: #cbd5e1;

    margin-bottom: 20px;
}

</style>

<div class="content-wrapper">

    <!-- HEADER -->

    <section class="content-header">

        <div class="container-fluid">

            <div class="filter-box">

                <div class="row align-items-center">

                    <div class="col-lg-8">

                        <h1 class="font-weight-bold mb-2">

                            Transport Report 🚌

                        </h1>

                        <p
                            class="mb-0"
                            style="
                                color: rgba(255,255,255,0.75);
                            "
                        >

                            Analyze transport routes,
                            transport fees and allocated students.

                        </p>

                    </div>

                    <div
                        class="col-lg-4 text-lg-right mt-4 mt-lg-0"
                    >

                        <span
                            class="badge badge-light p-3"
                            style="
                                font-size:14px;
                                border-radius:12px;
                            "
                        >

                            Transport Analytics

                        </span>

                    </div>

                </div>

            </div>

        </div>

    </section>

    <!-- CONTENT -->

    <section class="content">

        <div class="container-fluid">

            <!-- FILTER -->

            <div class="card modern-card mb-4">

                <div class="card-header">

                    <h3 class="card-title">

                        Filter Transport Report

                    </h3>

                </div>

                <div class="card-body">

                    <form method="GET">

                        <div class="row align-items-end">

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

                                <label>

                                    Select Route

                                </label>

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

                                    <option value="">

                                        All Routes

                                    </option>

                                    <?php foreach ($routes as $route): ?>

                                        <option
                                            value="<?= htmlspecialchars($route); ?>"

                                            <?php
                                            if (
                                                $route_filter ===
                                                $route
                                            ) {
                                                echo 'selected';
                                            }
                                            ?>
                                        >

                                            <?= htmlspecialchars($route); ?>

                                        </option>

                                    <?php endforeach; ?>

                                </select>

                            </div>

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

                                <button
                                    type="submit"
                                    class="btn btn-primary btn-block"
                                >

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

                                    Generate Report

                                </button>

                            </div>

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

                                <button
                                    type="button"
                                    onclick="window.print()"
                                    class="btn btn-dark btn-block"
                                >

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

                                    Print

                                </button>

                            </div>

                        </div>

                    </form>

                </div>

            </div>

            <!-- STATS -->

            <div class="row">

                <div class="col-lg-6 mb-4">

                    <div
                        class="stat-card"
                        style="
                            background:
                            linear-gradient(135deg,#2563eb,#1d4ed8);
                        "
                    >

                        <h2 class="font-weight-bold">

                            <?= number_format($total_students); ?>

                        </h2>

                        <p class="mb-0">

                            Total Transport Students

                        </p>

                    </div>

                </div>

                <div class="col-lg-6 mb-4">

                    <div
                        class="stat-card"
                        style="
                            background:
                            linear-gradient(135deg,#10b981,#059669);
                        "
                    >

                        <h2 class="font-weight-bold">

                            ₹<?= number_format($total_transport_fees, 2); ?>

                        </h2>

                        <p class="mb-0">

                            Total Transport Fees

                        </p>

                    </div>

                </div>

            </div>

            <!-- TABLE -->

            <div class="card modern-card">

                <div class="card-header">

                    <h3 class="card-title">

                        Transport Allocation List

                    </h3>

                </div>

                <div class="card-body p-0">

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

                        <div class="table-responsive">

                            <table
                                class="table table-bordered table-hover table-modern mb-0"
                            >

                                <thead>

                                    <tr>

                                        <th>#</th>

                                        <th>Roll No</th>

                                        <th>Student</th>

                                        <th>Father</th>

                                        <th>Phone</th>

                                        <th>Class</th>

                                        <th>Route</th>

                                        <th>Transport Fees</th>

                                        <th>Status</th>

                                    </tr>

                                </thead>

                                <tbody>

                                    <?php foreach ($students as $index => $row): ?>

                                        <tr>

                                            <td>

                                                <?= $index + 1; ?>

                                            </td>

                                            <td>

                                                <?= htmlspecialchars(
                                                    $row['roll_no'] ?? '-'
                                                ); ?>

                                            </td>

                                            <td>

                                                <strong>

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

                                                </strong>

                                            </td>

                                            <td>

                                                <?= htmlspecialchars(
                                                    $row['father_name'] ?? '-'
                                                ); ?>

                                            </td>

                                            <td>

                                                <?= htmlspecialchars(
                                                    $row['phone'] ?? '-'
                                                ); ?>

                                            </td>

                                            <td>

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

                                                <?php if (!empty($row['section'])): ?>

                                                    -
                                                    <?= htmlspecialchars(
                                                        $row['section']
                                                    ); ?>

                                                <?php endif; ?>

                                            </td>

                                            <td>

                                                <span
                                                    class="badge badge-primary p-2"
                                                >

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

                                                </span>

                                            </td>

                                            <td>

                                                ₹<?= number_format(
                                                    (float) (
                                                        $row['transport_fees']
                                                        ?? 0
                                                    ),
                                                    2
                                                ); ?>

                                            </td>

                                            <td>

                                                <span
                                                    class="badge badge-success p-2"
                                                >

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

                                                </span>

                                            </td>

                                        </tr>

                                    <?php endforeach; ?>

                                </tbody>

                            </table>

                        </div>

                    <?php else: ?>

                        <div class="empty-state">

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

                            <h4>

                                No Transport Records Found

                            </h4>

                            <p class="text-muted">

                                No transport allocation found
                                for selected route.

                            </p>

                        </div>

                    <?php endif; ?>

                </div>

            </div>

        </div>

    </section>

</div>

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