<?php
include('includes/dbconnection.php');
require_once 'vendor/autoload.php'; // PhpSpreadsheet

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

// Avoid accidental output
ob_clean();

// ----------------------
// Get parameters
// ----------------------
$class_id   = intval($_GET['class_id']);
$start_date = $_GET['start_date'];
$end_date   = $_GET['end_date'];

if (!$class_id || !$start_date || !$end_date) {
    die("Invalid request");
}

// ----------------------
// Fetch class info
// ----------------------
$classQuery = mysqli_query($con, "SELECT class, section FROM class_section WHERE id = $class_id LIMIT 1");
$classInfo = mysqli_fetch_assoc($classQuery);
$className = $classInfo['class'];
$classArm = $classInfo['section'];
$classNameFull = "$className " . "$classArm";

// ----------------------
// Fetch public holidays within range
// ----------------------
$holidayDates = [];
$holidayQuery = mysqli_query($con, "
    SELECT holiday_date 
    FROM public_holidays 
    WHERE holiday_date BETWEEN '$start_date' AND '$end_date'
");
while ($h = mysqli_fetch_assoc($holidayQuery)) {
    $holidayDates[] = $h['holiday_date'];
}

// ----------------------
// Generate list of valid school days (no weekends or holidays)
// ----------------------
$period = new DatePeriod(
    new DateTime($start_date),
    new DateInterval('P1D'),
    (new DateTime($end_date))->modify('+1 day')
);

$validDates = [];
foreach ($period as $d) {
    $date = $d->format('Y-m-d');
    $dow = (int)$d->format('N');
    if ($dow > 5) continue; // skip weekends
    if (in_array($date, $holidayDates)) continue; // skip public holidays
    $validDates[] = $date;
}
$totalWorkingDays = count($validDates);

// ----------------------
// Fetch all students in the class
// ----------------------
$studentsQ = mysqli_query($con, "SELECT id, studentName, studentno FROM students WHERE class = '$className' AND section ='$classArm' AND  status < 3 ORDER BY studentName ASC");
$students = [];
while ($row = mysqli_fetch_assoc($studentsQ)) {
    $students[] = $row;
}

// ----------------------
// Create spreadsheet
// ----------------------
$spreadsheet = new Spreadsheet();
$spreadsheet->removeSheetByIndex(0); // remove default sheet

// ----------------------
// For each valid school day, build a sheet
// ----------------------
foreach ($validDates as $date) {
    $sheet = $spreadsheet->createSheet();
    $sheet->setTitle(date('M-d', strtotime($date))); // short sheet title

    $sheet->setCellValue('A1', "ATTENDANCE RECORD FOR CLASS: $classNameFull  ON: $date");

    // Column headers
    $sheet->setCellValue('A2', 'Student Name')
          ->setCellValue('B2', 'Admission No')
          ->setCellValue('C2', 'Date')
          ->setCellValue('D2', 'Status');

    $rowNum = 3;

    foreach ($students as $student) {
        $sid = $student['id'];

        // Fetch attendance for this date and student
        $attQ = mysqli_query($con, "
            SELECT status 
            FROM attendance 
            WHERE student_id = '$sid' AND date = '$date' 
            LIMIT 1
        ");
        $att = mysqli_fetch_assoc($attQ);

        $status = $att ? ucfirst($att['status']) : 'Not Marked';

        $sheet->setCellValue('A' . $rowNum, $student['studentName']);
        $sheet->setCellValue('B' . $rowNum, $student['studentno']);
        $sheet->setCellValue('C' . $rowNum, $date);
        $sheet->setCellValue('D' . $rowNum, $status);

        // Highlight Not Marked rows
        if ($status === 'Not Marked') {
            $sheet->getStyle("A{$rowNum}:D{$rowNum}")
                ->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setARGB('FFFFE0E0');
        }

        $rowNum++;
    }

    // Autosize
    foreach (range('A', 'D') as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }
}

// ----------------------
// Summary Sheet
// ----------------------
$summarySheet = $spreadsheet->createSheet(0); // put it first
$summarySheet->setTitle('Summary');

$summarySheet->setCellValue('A1', "CLASS ATTENDANCE SUMMARY - $classNameFull");
$summarySheet->setCellValue('A3', 'Date Range:')
              ->setCellValue('B3', "$start_date to $end_date");
$summarySheet->setCellValue('A4', 'Total Working Days (Excl. Holidays/Weekends):')
              ->setCellValue('B4', $totalWorkingDays);

$summarySheet->setCellValue('A6', 'Public Holidays Skipped:');
if (!empty($holidayDates)) {
    $summarySheet->fromArray($holidayDates, null, 'B6');
} else {
    $summarySheet->setCellValue('B6', 'None');
}

$summarySheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
$summarySheet->getStyle('A3:A6')->getFont()->setBold(true);

foreach (range('A', 'C') as $col) {
    $summarySheet->getColumnDimension($col)->setAutoSize(true);
}

// ----------------------
// Output file
// ----------------------
ob_end_clean();
$fileName = "Attendance_" . preg_replace('/\s+/', '_', $classNameFull) . "_{$start_date}_to_{$end_date}.xlsx";

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment; filename=\"{$fileName}\"");

$writer = new Xlsx($spreadsheet);
$writer->save("php://output");
exit;
