<?php

declare(strict_types=1);

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

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

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

$current_session_id = (int) get_current_session_id($pdo);

$page_title = 'Attendance Report';

// ======================================================
// FETCH CLASSES
// ======================================================

$stmt = $pdo->prepare("
    SELECT

        id,
        class_name

    FROM classes

    WHERE school_id = ?

    ORDER BY class_name ASC
");

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

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

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

$class_id = (int) ($_GET['class_id'] ?? 0);

$month = trim(
    $_GET['month'] ?? date('Y-m')
);

$students = [];

$working_days = 0;

// ======================================================
// LOAD REPORT
// ======================================================

if ($class_id > 0) {

    // ==============================================
    // DATE RANGE
    // ==============================================

    $start_date = $month . '-01';

    $end_date = date(
        'Y-m-t',
        strtotime($start_date)
    );

    // ==============================================
    // GET HOLIDAYS
    // ==============================================

    $holiday_stmt = $pdo->prepare("
        SELECT holiday_date

        FROM holidays

        WHERE school_id = ?
        AND holiday_date
        BETWEEN ? AND ?
    ");

    $holiday_stmt->execute([

        $school_id,
        $start_date,
        $end_date

    ]);

    $holidays = array_column(

        $holiday_stmt->fetchAll(PDO::FETCH_ASSOC),

        'holiday_date'

    );

    // ==============================================
    // GET WEEKLY OFFS
    // ==============================================

    $weekly_stmt = $pdo->prepare("
        SELECT day_name

        FROM weekly_offs

        WHERE school_id = ?
    ");

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

    $weekly_offs = array_column(

        $weekly_stmt->fetchAll(PDO::FETCH_ASSOC),

        'day_name'

    );

    // ==============================================
    // CALCULATE WORKING DAYS
    // ==============================================

    $current = strtotime($start_date);

    $last = strtotime($end_date);

    while ($current <= $last) {

        $date = date('Y-m-d', $current);

        $day_name = date('l', $current);

        if (
            !in_array($date, $holidays, true) &&
            !in_array($day_name, $weekly_offs, true)
        ) {

            $working_days++;
        }

        $current = strtotime('+1 day', $current);
    }

    // ==============================================
    // FETCH STUDENTS
    // ==============================================

    $stmt = $pdo->prepare("
        SELECT

            ss.id AS student_session_id,

            s.name,

            ss.roll_no,

            ss.section

        FROM student_sessions ss

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

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

        ORDER BY

            CAST(ss.roll_no AS UNSIGNED) ASC,

            s.name ASC
    ");

    $stmt->execute([

        $school_id,
        $current_session_id,
        $class_id

    ]);

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

    // ==============================================
    // FETCH ATTENDANCE SUMMARY
    // ==============================================

    foreach ($students as &$student) {

        $summary_stmt = $pdo->prepare("
            SELECT

                SUM(
                    CASE
                        WHEN status = 'Present'
                        THEN 1
                        ELSE 0
                    END
                ) AS present_days,

                SUM(
                    CASE
                        WHEN status = 'Late'
                        THEN 1
                        ELSE 0
                    END
                ) AS late_days,

                SUM(
                    CASE
                        WHEN status = 'Leave'
                        THEN 1
                        ELSE 0
                    END
                ) AS leave_days,

                SUM(
                    CASE
                        WHEN status = 'Absent'
                        THEN 1
                        ELSE 0
                    END
                ) AS absent_days

            FROM attendance

            WHERE school_id = ?
            AND session_id = ?
            AND student_session_id = ?
            AND attendance_date
            BETWEEN ? AND ?
        ");

        $summary_stmt->execute([

            $school_id,
            $current_session_id,
            $student['student_session_id'],
            $start_date,
            $end_date

        ]);

        $summary = $summary_stmt->fetch(PDO::FETCH_ASSOC);

        $student['present_days'] =
            (int) ($summary['present_days'] ?? 0);

        $student['late_days'] =
            (int) ($summary['late_days'] ?? 0);

        $student['leave_days'] =
            (int) ($summary['leave_days'] ?? 0);

        $student['absent_days'] =
            (int) ($summary['absent_days'] ?? 0);

        $attendance_total =
            $student['present_days']
            + $student['late_days']
            + $student['leave_days'];

        $student['attendance_percentage'] =
            $working_days > 0

                ? round(
                    ($attendance_total / $working_days) * 100,
                    2
                )

                : 0;
    }

    unset($student);
}

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>

                <h1>

                    <i class="fas fa-chart-bar text-info"></i>

                    Attendance Report

                </h1>

                <small class="text-muted">

                    Monthly attendance analytics

                </small>

            </div>

        </div>

    </section>

    <section class="content">

        <div class="container-fluid">

            <!-- FILTER -->

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

                <div class="card-header">

                    <h3 class="card-title">

                        Report Filters

                    </h3>

                </div>

                <div class="card-body">

                    <form method="GET">

                        <div class="row">

                            <div class="col-12 col-md-5 mb-3">

                                <label>

                                    Select Class

                                </label>

                                <select
                                    name="class_id"
                                    class="form-control"
                                    required
                                >

                                    <option value="">

                                        Select Class

                                    </option>

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

                                        <option
                                            value="<?= (int) $class['id'] ?>"

                                            <?= $class_id === (int) $class['id']
                                                ? 'selected'
                                                : '' ?>
                                        >

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

                                        </option>

                                    <?php endforeach; ?>

                                </select>

                            </div>

                            <div class="col-12 col-md-4 mb-3">

                                <label>

                                    Select Month

                                </label>

                                <input
                                    type="month"
                                    name="month"
                                    class="form-control"
                                    value="<?= htmlspecialchars($month) ?>"
                                    required
                                >

                            </div>

                            <div class="col-12 col-md-3 mb-3 d-flex align-items-end">

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

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

                                    Generate

                                </button>

                            </div>

                        </div>

                    </form>

                </div>

            </div>

            <!-- REPORT -->

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

                <div class="card card-outline card-success shadow-sm mt-4">

                    <div class="card-header d-flex justify-content-between align-items-center flex-wrap">

    <h3 class="card-title mb-2 mb-md-0">

        Working Days :
        <?= $working_days ?>

    </h3>

    <div>

        <!-- DAILY PRINT -->

        <a
            href="<?= WEB_ROOT ?>attendance/attendance_report_print.php?class_id=<?= $class_id ?>&date=<?= date('Y-m-d') ?>"
            target="_blank"
            class="btn btn-sm btn-primary"
        >

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

            Daily Print

        </a>

        <!-- EXCEL -->

        <a
            href="<?= WEB_ROOT ?>attendance/export_excel.php?class_id=<?= $class_id ?>&month=<?= urlencode($month) ?>"
            class="btn btn-sm btn-success"
        >

            <i class="fas fa-file-excel"></i>

            Excel

        </a>

        <!-- PDF -->

        <a
            href="<?= WEB_ROOT ?>attendance/attendance_report_print.php?class_id=<?= $class_id ?>&date=<?= date('Y-m-d') ?>"
            target="_blank"
            class="btn btn-sm btn-danger"
        >

            <i class="fas fa-file-pdf"></i>

            PDF

        </a>

    </div>

</div>

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

                        <div class="table-responsive">

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

                                <thead class="thead-light">

                                    <tr>

                                        <th>Roll</th>
                                        <th>Student</th>
                                        <th>Present</th>
                                        <th>Late</th>
                                        <th>Leave</th>
                                        <th>Absent</th>
                                        <th>Attendance %</th>

                                    </tr>

                                </thead>

                                <tbody>

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

                                        <tr>

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

                                                No students found

                                            </td>

                                        </tr>

                                    <?php endif; ?>

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

                                        <tr>

                                            <td>

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

                                            </td>

                                            <td>

                                                <strong>

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

                                                </strong>

                                            </td>

                                            <td class="text-success">

                                                <?= $student['present_days'] ?>

                                            </td>

                                            <td class="text-warning">

                                                <?= $student['late_days'] ?>

                                            </td>

                                            <td class="text-info">

                                                <?= $student['leave_days'] ?>

                                            </td>

                                            <td class="text-danger">

                                                <?= $student['absent_days'] ?>

                                            </td>

                                            <td>

                                                <strong>

                                                    <?= $student['attendance_percentage'] ?>%

                                                </strong>

                                            </td>

                                        </tr>

                                    <?php endforeach; ?>

                                </tbody>

                            </table>

                        </div>

                    </div>

                </div>

            <?php endif; ?>

        </div>

    </section>

</div>

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