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

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

// ----------------------
// Validate inputs
// ----------------------
if (!isset($_GET['start_date'], $_GET['end_date'])) {
    exit("Please provide start_date and end_date in the URL.");
}

$start_date = $_GET['start_date'];
$end_date   = $_GET['end_date'];

// ----------------------
// Fetch public holidays in the 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'];
}

// ----------------------
// Prepare spreadsheet
// ----------------------
$spreadsheet = new Spreadsheet();
$spreadsheet->getProperties()
    ->setCreator("School Admin")
    ->setTitle("Attendance Report $start_date to $end_date");

// ----------------------
// Fetch teachers
// ----------------------
$teachers = mysqli_query($con, "
    SELECT 
        u.id AS teacher_id,
        CONCAT(u.lastname, ' ', u.name) AS teacher_name,
        cs.class,
        cs.section
    FROM teacher_assignments ta
    JOIN tblusers u ON ta.teacher_id = u.id
    JOIN class_section cs ON ta.class_id = cs.id
    WHERE ta.is_class_teacher = 1
      AND u.status = 2
      AND u.permission = 'Teacher'
    ORDER BY u.lastname, cs.class, cs.section
");

if (!$teachers) {
    exit("Database query failed: " . mysqli_error($con));
}

// ----------------------
// Date range
// ----------------------
$period = new DatePeriod(
    new DateTime($start_date),
    new DateInterval('P1D'),
    (new DateTime($end_date))->modify('+1 day')
);

// ----------------------
// Summary Sheet setup
// ----------------------
$summarySheet = $spreadsheet->setActiveSheetIndex(0);
$summarySheet->setTitle('Summary');
$headers = ['Teacher', 'Class', 'Arm', 'Total Days', 'Days Completed', 'Days Incomplete', 'Completion %'];
$summarySheet->fromArray($headers, null, 'A1');
$summarySheet->getStyle('A1:G1')->getFont()->setBold(true);
$summaryRow = 2;

// ----------------------
// Generate per-teacher sheets
// ----------------------
while ($teacher = mysqli_fetch_assoc($teachers)) {
    $teacher_name = $teacher['teacher_name'];
    $class = $teacher['class'];
    $section = $teacher['section'];

    $sheet = $spreadsheet->createSheet();
    $title = substr("{$teacher_name}_{$class}{$section}", 0, 28);
    $sheet->setTitle($title);

    $sheetHeaders = [
        'Date', 'Class', 'Arm', 'Present', 'Absent', 'Not Marked',
        'Total Students', 'Completion %', 'Status', 'Absent Students', 'Not Marked Students'
    ];
    $sheet->fromArray($sheetHeaders, null, 'A1');
    $sheet->getStyle('A1:K1')->getFont()->setBold(true);

    $row = 2;
    $days_completed = 0;
    $days_incomplete = 0;
    $working_days = 0; // <-- now counts only weekdays that aren't holidays

    foreach ($period as $d) {
        $date = $d->format('Y-m-d');
        $dow = (int)$d->format('N');

        // Skip weekends
        if ($dow > 5) continue;

        // Skip public holidays
        if (in_array($date, $holidayDates)) continue;

        $working_days++;

        // ---- Attendance stats query ----
        $statsQ = mysqli_query($con, "
            SELECT 
                COUNT(DISTINCT CASE WHEN a.status='Present' THEN a.student_id END) AS present_count,
                COUNT(DISTINCT CASE WHEN a.status='Absent' THEN a.student_id END) AS absent_count,
                COUNT(DISTINCT s.id) AS total_students,
                (COUNT(DISTINCT s.id) - COUNT(DISTINCT a.student_id)) AS not_marked
            FROM students s
            LEFT JOIN attendance a 
                ON a.student_id = s.id AND a.date = '$date'
            WHERE s.class = '$class' AND s.section = '$section' AND s.status < 3
        ");
        $stats = mysqli_fetch_assoc($statsQ);

        $present = (int)$stats['present_count'];
        $absent = (int)$stats['absent_count'];
        $not_marked = (int)$stats['not_marked'];
        $total = (int)$stats['total_students'];
        $completion_pct = $total ? round((($present + $absent) / $total) * 100, 1) : 0;
        $status = ($present + $absent) == $total && $total > 0 ? 'Completed' : 'Incomplete';

        if ($status === 'Completed') $days_completed++; else $days_incomplete++;

        // ---- Get absent students ----
        $absent_names = [];
        $resA = mysqli_query($con, "
            SELECT s.studentName
            FROM students s
            JOIN attendance a ON a.student_id = s.id
            WHERE a.date = '$date' AND a.status = 'Absent'
              AND s.class = '$class' AND s.section = '$section'
        ");
        while ($r = mysqli_fetch_assoc($resA)) $absent_names[] = $r['studentName'];

        // ---- Get not marked students ----
        $not_marked_names = [];
        $resN = mysqli_query($con, "
            SELECT s.studentName
            FROM students s
            WHERE s.id NOT IN (SELECT student_id FROM attendance WHERE date = '$date')
              AND s.class = '$class' AND s.section = '$section' AND s.status < 3
        ");
        while ($r = mysqli_fetch_assoc($resN)) $not_marked_names[] = $r['studentName'];

        // ---- Write row ----
        $sheet->fromArray([
            $date, $class, $section, $present, $absent, $not_marked, $total,
            $completion_pct . '%', $status,
            implode(', ', $absent_names),
            implode(', ', $not_marked_names)
        ], null, "A{$row}");

        if ($status === 'Incomplete') {
            $sheet->getStyle("A{$row}:K{$row}")
                ->getFill()
                ->setFillType(Fill::FILL_SOLID)
                ->getStartColor()
                ->setARGB('FFFFCCCC');
        }

        $row++;
    }

    foreach (range('A', 'K') as $col) $sheet->getColumnDimension($col)->setAutoSize(true);

    // ---- Summary calculation ----
    $completion_rate = $working_days ? round(($days_completed / $working_days) * 100, 1) : 0;

    $summarySheet->fromArray([
        $teacher_name, $class, $section, $working_days, $days_completed, $days_incomplete, $completion_rate . '%'
    ], null, "A{$summaryRow}");

    if ($completion_rate < 80) {
        $summarySheet->getStyle("A{$summaryRow}:G{$summaryRow}")
            ->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()
            ->setARGB('FFFFE0E0');
    }

    $summaryRow++;
}

// Autosize summary columns
foreach (range('A', 'G') as $col) $summarySheet->getColumnDimension($col)->setAutoSize(true);

// Output the file
ob_end_clean();
$filename = "Attendance_Report_{$start_date}_to_{$end_date}.xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment; filename=\"$filename\"");
header('Cache-Control: max-age=0');

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