<?php

declare(strict_types=1);

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

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

require_once __DIR__ . '/../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

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

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

// ======================================================
// INPUTS
// ======================================================

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

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

// ======================================================
// VALIDATION
// ======================================================

if ($class_id <= 0) {

    die('Invalid class.');
}

if (
    !preg_match(
        '/^\d{4}-\d{2}$/',
        $month
    )
) {

    die('Invalid month.');
}

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

$start_date = $month . '-01';

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

// ======================================================
// HOLIDAYS
// ======================================================

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

    FROM holidays

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

$stmt->execute([

    $school_id,
    $start_date,
    $end_date

]);

$holidays = array_column(

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

    'holiday_date'

);

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

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

    FROM weekly_offs

    WHERE school_id = ?
");

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

$weekly_offs = array_column(

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

    'day_name'

);

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

$working_days = 0;

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

// ======================================================
// EXCEL
// ======================================================

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

$sheet->setTitle('Attendance Report');

// ======================================================
// HEADERS
// ======================================================

$headers = [

    'A1' => 'Roll No',
    'B1' => 'Student Name',
    'C1' => 'Section',
    'D1' => 'Present',
    'E1' => 'Late',
    'F1' => 'Leave',
    'G1' => 'Absent',
    'H1' => 'Attendance %'

];

foreach ($headers as $cell => $value) {

    $sheet->setCellValue($cell, $value);
}

// ======================================================
// DATA
// ======================================================

$row_number = 2;

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

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

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

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

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

    $attendance_total =
        $present_days +
        $late_days +
        $leave_days;

    $attendance_percentage =
        $working_days > 0

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

            : 0;

    $sheet->setCellValue(
        'A' . $row_number,
        $student['roll_no']
    );

    $sheet->setCellValue(
        'B' . $row_number,
        $student['name']
    );

    $sheet->setCellValue(
        'C' . $row_number,
        $student['section']
    );

    $sheet->setCellValue(
        'D' . $row_number,
        $present_days
    );

    $sheet->setCellValue(
        'E' . $row_number,
        $late_days
    );

    $sheet->setCellValue(
        'F' . $row_number,
        $leave_days
    );

    $sheet->setCellValue(
        'G' . $row_number,
        $absent_days
    );

    $sheet->setCellValue(
        'H' . $row_number,
        $attendance_percentage . '%'
    );

    $row_number++;
}

// ======================================================
// AUTO SIZE
// ======================================================

foreach (range('A', 'H') as $column) {

    $sheet
        ->getColumnDimension($column)
        ->setAutoSize(true);
}

// ======================================================
// DOWNLOAD
// ======================================================

$file_name =
    'attendance_report_' .
    $month .
    '.xlsx';

header(
    'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
);

header(
    'Content-Disposition: attachment; filename="' .
    $file_name .
    '"'
);

header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);

$writer->save('php://output');

exit;
?>