<?php
require_once 'config/session.php';
require_once 'config/database.php';
require_once 'config/functions.php';

// Check if user is logged in
checkLogin();

// Include PHPSpreadsheet
require_once 'vendor/autoload.php';

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

$database = new Database();
$conn = $database->getConnection();
$currentUser = getCurrentUser();

// Get filter parameters
$bulan = $_GET['bulan'] ?? date('m');
$tahun = $_GET['tahun'] ?? date('Y');

// Get students with attendance summary
$students = [];
if ($currentUser['id_kelas']) {
    $query = "SELECT s.id_siswa, s.nis, s.nama_siswa,
              COUNT(CASE WHEN a.status = 'hadir' THEN 1 END) as hadir,
              COUNT(CASE WHEN a.status = 'sakit' THEN 1 END) as sakit,
              COUNT(CASE WHEN a.status = 'izin' THEN 1 END) as izin,
              COUNT(CASE WHEN a.status = 'alpa' THEN 1 END) as alpa,
              COUNT(*) as total_hari
              FROM siswa s
              LEFT JOIN absensi a ON s.id_siswa = a.id_siswa 
                  AND MONTH(a.tanggal) = :bulan AND YEAR(a.tanggal) = :tahun
              WHERE s.id_kelas = :id_kelas AND s.status = 'aktif'
              GROUP BY s.id_siswa, s.nis, s.nama_siswa
              ORDER BY s.nama_siswa ASC";
    
    $stmt = $conn->prepare($query);
    $stmt->bindParam(':bulan', $bulan);
    $stmt->bindParam(':tahun', $tahun);
    $stmt->bindParam(':id_kelas', $currentUser['id_kelas']);
    $stmt->execute();
    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Get detailed attendance records
$attendance_details = [];
if ($currentUser['id_kelas']) {
    $query = "SELECT a.*, s.nis, s.nama_siswa 
              FROM absensi a
              JOIN siswa s ON a.id_siswa = s.id_siswa
              WHERE s.id_kelas = :id_kelas AND MONTH(a.tanggal) = :bulan AND YEAR(a.tanggal) = :tahun
              ORDER BY a.tanggal DESC, s.nama_siswa ASC";
    
    $stmt = $conn->prepare($query);
    $stmt->bindParam(':bulan', $bulan);
    $stmt->bindParam(':tahun', $tahun);
    $stmt->bindParam(':id_kelas', $currentUser['id_kelas']);
    $stmt->execute();
    $attendance_details = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Create new Spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Set document properties
$spreadsheet->getProperties()
    ->setCreator("Sistem Guru Kelas SD")
    ->setLastModifiedBy("Sistem Guru Kelas SD")
    ->setTitle("Rekap Absensi Siswa")
    ->setSubject("Rekap Absensi Siswa")
    ->setDescription("Rekap absensi siswa per bulan");

// Set headers
$sheet->setCellValue('A1', 'REKAP ABSENSI SISWA');
$sheet->mergeCells('A1:H1');
$sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);
$sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

// Set period info
$periodText = "Periode: " . getMonthName($bulan) . " " . $tahun;
$sheet->setCellValue('A2', $periodText);
$sheet->mergeCells('A2:H2');
$sheet->getStyle('A2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

// Set class info
$classText = "Kelas: " . ($currentUser['nama_kelas'] ?? 'Belum ditentukan');
$sheet->setCellValue('A3', $classText);
$sheet->mergeCells('A3:H3');
$sheet->getStyle('A3')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

// Set teacher info
$teacherText = "Guru: " . ($currentUser['nama_guru'] ?? 'Belum ditentukan');
$sheet->setCellValue('A4', $teacherText);
$sheet->mergeCells('A4:H4');
$sheet->getStyle('A4')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

// Summary section
$sheet->setCellValue('A5', 'RINGKASAN ABSENSI');
$sheet->mergeCells('A5:H5');
$sheet->getStyle('A5')->getFont()->setBold(true)->setSize(14);

// Summary headers
$summaryHeaders = ['No', 'NIS', 'Nama Siswa', 'Hadir', 'Sakit', 'Izin', 'Alpa', 'Total Hari'];
$col = 'A';
foreach ($summaryHeaders as $header) {
    $sheet->setCellValue($col . '6', $header);
    $sheet->getStyle($col . '6')->getFont()->setBold(true);
    $sheet->getStyle($col . '6')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
    $col++;
}

// Summary data
$row = 7;
$no = 1;
foreach ($students as $student) {
    $sheet->setCellValue('A' . $row, $no++);
    $sheet->setCellValue('B' . $row, $student['nis']);
    $sheet->setCellValue('C' . $row, $student['nama_siswa']);
    $sheet->setCellValue('D' . $row, $student['hadir']);
    $sheet->setCellValue('E' . $row, $student['sakit']);
    $sheet->setCellValue('F' . $row, $student['izin']);
    $sheet->setCellValue('G' . $row, $student['alpa']);
    $sheet->setCellValue('H' . $row, $student['total_hari']);
    $row++;
}

// Add totals row
$sheet->setCellValue('C' . $row, 'TOTAL');
$sheet->setCellValue('D' . $row, array_sum(array_column($students, 'hadir')));
$sheet->setCellValue('E' . $row, array_sum(array_column($students, 'sakit')));
$sheet->setCellValue('F' . $row, array_sum(array_column($students, 'izin')));
$sheet->setCellValue('G' . $row, array_sum(array_column($students, 'alpa')));
$sheet->setCellValue('H' . $row, array_sum(array_column($students, 'total_hari')));
$sheet->getStyle('C' . $row . ':H' . $row)->getFont()->setBold(true);
$sheet->getStyle('C' . $row . ':H' . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFF0F0F0');

// Auto-size columns for summary
foreach (range('A', 'H') as $column) {
    $sheet->getColumnDimension($column)->setAutoSize(true);
}

// Add borders to summary section
$lastSummaryRow = $row;
$sheet->getStyle('A6:H' . $lastSummaryRow)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);

// Add spacing
$detailStartRow = $row + 2;

// Detailed attendance section
$sheet->setCellValue('A' . $detailStartRow, 'DETAIL ABSENSI');
$sheet->mergeCells('A' . $detailStartRow . ':E' . $detailStartRow);
$sheet->getStyle('A' . $detailStartRow)->getFont()->setBold(true)->setSize(14);

// Detailed headers
$detailHeaders = ['No', 'Tanggal', 'NIS', 'Nama Siswa', 'Status', 'Keterangan'];
$col = 'A';
$row = $detailStartRow + 1;
foreach ($detailHeaders as $header) {
    $sheet->setCellValue($col . $row, $header);
    $sheet->getStyle($col . $row)->getFont()->setBold(true);
    $sheet->getStyle($col . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
    $col++;
}

// Detailed data
$row++;
$no = 1;
foreach ($attendance_details as $record) {
    $sheet->setCellValue('A' . $row, $no++);
    $sheet->setCellValue('B' . $row, formatDate($record['tanggal']));
    $sheet->setCellValue('C' . $row, $record['nis']);
    $sheet->setCellValue('D' . $row, $record['nama_siswa']);
    $sheet->setCellValue('E' . $row, getAttendanceStatusText($record['status']));
    $sheet->setCellValue('F' . $row, $record['keterangan'] ?? '-');
    $row++;
}

// Auto-size detailed columns
foreach (range('A', 'F') as $column) {
    $sheet->getColumnDimension($column)->setAutoSize(true);
}

// Add borders to detailed section
$lastDetailRow = $row - 1;
$sheet->getStyle('A' . ($detailStartRow + 1) . ':F' . $lastDetailRow)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);

// Set filename
$filename = 'rekap_absensi_' . getMonthName($bulan) . '_' . $tahun . '_' . date('Y-m-d_H-i-s') . '.xlsx';

// Redirect output to browser
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');

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