<?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 export parameters
$export_type = $_GET['export_type'] ?? 'monthly';
$start_date = $_GET['start_date'] ?? '';
$end_date = $_GET['end_date'] ?? '';
$bulan = $_GET['bulan'] ?? date('m');
$tahun = $_GET['tahun'] ?? date('Y');
$id_mapel = $_GET['id_mapel'] ?? '';

// Validate parameters
if (empty($start_date) && empty($end_date)) {
    $start_date = date('Y-m-01', strtotime("$tahun-$bulan-01"));
    $end_date = date('Y-m-t', strtotime("$tahun-$bulan-01"));
}

// Build query based on parameters
$query = "SELECT s.nis, s.nama_siswa, m.nama_mapel, nh.tanggal, nh.nilai_pg, nh.nilai_uraian, nh.nilai_praktik, 
          nh.nilai_pengetahuan, nh.nilai_adab, nh.nilai_akhir, nh.jenis_penilaian, nh.keterangan
          FROM nilai_harian nh
          JOIN siswa s ON nh.id_siswa = s.id_siswa
          JOIN mata_pelajaran m ON nh.id_mapel = m.id_mapel
          WHERE s.id_kelas = :id_kelas 
          AND nh.tanggal BETWEEN :start_date AND :end_date";

$params = [
    ':id_kelas' => $currentUser['id_kelas'],
    ':start_date' => $start_date,
    ':end_date' => $end_date
];

if ($id_mapel) {
    $query .= " AND nh.id_mapel = :id_mapel";
    $params[':id_mapel'] = $id_mapel;
}

$query .= " ORDER BY nh.tanggal DESC, s.nama_siswa ASC";

$stmt = $conn->prepare($query);
foreach ($params as $key => $value) {
    $stmt->bindValue($key, $value);
}
$stmt->execute();
$grades = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Get summary data
$summaryQuery = "SELECT s.nis, s.nama_siswa, m.nama_mapel, 
                AVG(nh.nilai_pg) as rata_pg, 
                AVG(nh.nilai_uraian) as rata_uraian, 
                AVG(nh.nilai_praktik) as rata_praktik, 
                AVG(nh.nilai_pengetahuan) as rata_pengetahuan, 
                AVG(nh.nilai_adab) as rata_adab, 
                AVG(nh.nilai_akhir) as rata_akhir, 
                COUNT(*) as total_nilai
                FROM nilai_harian nh
                JOIN siswa s ON nh.id_siswa = s.id_siswa
                JOIN mata_pelajaran m ON nh.id_mapel = m.id_mapel
                WHERE s.id_kelas = :id_kelas 
                AND nh.tanggal BETWEEN :start_date AND :end_date";

if ($id_mapel) {
    $summaryQuery .= " AND nh.id_mapel = :id_mapel";
}

$summaryQuery .= " GROUP BY s.id_siswa, s.nis, s.nama_siswa, m.id_mapel, m.nama_mapel
                  ORDER BY s.nama_siswa ASC";

$summaryStmt = $conn->prepare($summaryQuery);
foreach ($params as $key => $value) {
    $summaryStmt->bindValue($key, $value);
}
$summaryStmt->execute();
$summary = $summaryStmt->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 Nilai Komponen")
    ->setSubject("Rekap Nilai Komponen")
    ->setDescription("Rekap nilai komponen siswa");

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

// Set period info
$periodText = "Periode: " . formatDate($start_date) . " - " . formatDate($end_date);
$sheet->setCellValue('A2', $periodText);
$sheet->mergeCells('A2:L2');
$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:L3');
$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:L4');
$sheet->getStyle('A4')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

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

// Summary headers
$summaryHeaders = ['No', 'NIS', 'Nama Siswa', 'Mata Pelajaran', 'Rata-rata PG', 'Rata-rata Uraian', 'Rata-rata Praktik', 'Rata-rata Pengetahuan', 'Rata-rata Adab', 'Rata-rata Akhir'];
$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 ($summary as $item) {
    $sheet->setCellValue('A' . $row, $no++);
    $sheet->setCellValue('B' . $row, $item['nis']);
    $sheet->setCellValue('C' . $row, $item['nama_siswa']);
    $sheet->setCellValue('D' . $row, $item['nama_mapel']);
    $sheet->setCellValue('E' . $row, number_format($item['rata_pg'], 1));
    $sheet->setCellValue('F' . $row, number_format($item['rata_uraian'], 1));
    $sheet->setCellValue('G' . $row, number_format($item['rata_praktik'], 1));
    $sheet->setCellValue('H' . $row, number_format($item['rata_pengetahuan'], 1));
    $sheet->setCellValue('I' . $row, number_format($item['rata_adab'], 1));
    $sheet->setCellValue('J' . $row, number_format($item['rata_akhir'], 1));
    $row++;
}

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

// Add borders only to summary section for monthly export
if ($export_type !== 'monthly') {
    // Add spacing
    $detailStartRow = $row + 2;

    // Detailed grades section
    $sheet->setCellValue('A' . $detailStartRow, 'DETAIL NILAI KOMPONEN');
    $sheet->mergeCells('A' . $detailStartRow . ':L' . $detailStartRow);
    $sheet->getStyle('A' . $detailStartRow)->getFont()->setBold(true)->setSize(14);

    // Detailed headers
    $detailHeaders = ['No', 'Tanggal', 'NIS', 'Nama Siswa', 'Mata Pelajaran', 'Jenis', 'PG', 'Uraian', 'Praktik', 'Pengetahuan', 'Adab', 'Akhir'];
    $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 ($grades as $grade) {
        $sheet->setCellValue('A' . $row, $no++);
        $sheet->setCellValue('B' . $row, formatDate($grade['tanggal']));
        $sheet->setCellValue('C' . $row, $grade['nis']);
        $sheet->setCellValue('D' . $row, $grade['nama_siswa']);
        $sheet->setCellValue('E' . $row, $grade['nama_mapel']);
        $sheet->setCellValue('F' . $row, ucfirst($grade['jenis_penilaian']));
        $sheet->setCellValue('G' . $row, number_format($grade['nilai_pg'], 1));
        $sheet->setCellValue('H' . $row, number_format($grade['nilai_uraian'], 1));
        $sheet->setCellValue('I' . $row, number_format($grade['nilai_praktik'], 1));
        $sheet->setCellValue('J' . $row, number_format($grade['nilai_pengetahuan'], 1));
        $sheet->setCellValue('K' . $row, number_format($grade['nilai_adab'], 1));
        $sheet->setCellValue('L' . $row, number_format($grade['nilai_akhir'], 1));
        $row++;
    }

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

    // Add borders to both sections
    $lastRow = $row - 1;
    $sheet->getStyle('A6:J' . ($detailStartRow - 1))->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
    $sheet->getStyle('A' . ($detailStartRow + 1) . ':L' . $lastRow)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
} else {
    // Add borders only to summary section for monthly export
    $lastRow = $row - 1;
    $sheet->getStyle('A6:J' . $lastRow)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
}

// Set filename
$filename = 'rekap_nilai_komponen_' . 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;
?>
