<?php
require_once __DIR__ . '/../../config/secure_bootstrap.php';
require_once __DIR__ . '/../../vendor/autoload.php';

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

$filter_type = $_POST['filter_type'] ?? '';
$filter_status = $_POST['filter_status'] ?? '';
$search = trim($_POST['search'] ?? '');

// === Query Filter ===
$query = "SELECT * FROM accreditation WHERE 1";
$params = [];

if (!empty($filter_type)) {
  $query .= " AND type = ?";
  $params[] = $filter_type;
}
if (!empty($filter_status)) {
  if ($filter_status === 'aktif') $query .= " AND valid_until >= CURDATE()";
  elseif ($filter_status === 'kadaluarsa') $query .= " AND valid_until < CURDATE()";
}
if (!empty($search)) {
  $query .= " AND (institution_name LIKE ? OR program_studi LIKE ? OR accreditation_body LIKE ? OR rating LIKE ? OR strata LIKE ?)";
  $search_term = "%$search%";
  $params = array_merge($params, [$search_term, $search_term, $search_term, $search_term, $search_term]);
}

$query .= " ORDER BY created_at DESC";
$stmt = $pdo->prepare($query);
$stmt->execute($params);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

// === Spreadsheet Setup ===
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Akreditasi UNIRU');

// === Header Info ===
$title = 'LAPORAN DATA AKREDITASI UNIVERSITAS RIYADLUL ULUM';
$type_label = $filter_type ? ucfirst($filter_type) : 'Semua Jenis';
$status_label = $filter_status ? ucfirst($filter_status) : 'Semua Status';
$date_label = date('d F Y', strtotime('now'));

$periode_info = "Periode: $type_label | Status: $status_label";
$tanggal_info = "Tanggal Cetak: $date_label";

// === Tulis Judul dan Info ===
$sheet->setCellValue('A1', $title);
$sheet->mergeCells('A1:I1');
$sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
$sheet->getStyle('A1')->getAlignment()->setHorizontal('center');

$sheet->setCellValue('A2', $periode_info);
$sheet->mergeCells('A2:I2');
$sheet->getStyle('A2')->getAlignment()->setHorizontal('center');

$sheet->setCellValue('A3', $tanggal_info);
$sheet->mergeCells('A3:I3');
$sheet->getStyle('A3')->getAlignment()->setHorizontal('center');

// === Header Kolom Data ===
$headers = ['No', 'Jenis', 'Institusi/Prodi', 'Strata', 'Lembaga', 'Peringkat', 'Tanggal Berlaku', 'Kadaluarsa', 'Status'];
$sheet->fromArray([$headers], NULL, 'A5');
$sheet->getStyle('A5:I5')->getFont()->setBold(true);
$sheet->getStyle('A5:I5')->getAlignment()->setHorizontal('center');

// === Data ===
$row = 6;
$no = 1;
foreach ($data as $a) {
  $status = ($a['valid_until'] >= date('Y-m-d')) ? 'Aktif' : 'Kadaluarsa';
  $sheet->fromArray([
    $no++,
    ucfirst($a['type']),
    $a['type'] === 'universitas' ? $a['institution_name'] : $a['program_studi'],
    $a['strata'],
    $a['accreditation_body'],
    $a['rating'],
    $a['valid_from'],
    $a['valid_until'],
    $status
  ], NULL, 'A' . $row++);
}

// === Auto Width & Style ===
foreach (range('A', 'I') as $col) {
  $sheet->getColumnDimension($col)->setAutoSize(true);
}

$sheet->getStyle('A5:I' . ($row - 1))->getBorders()->getAllBorders()
  ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

// === Footer Tanda Tangan / Info Cetak Opsional ===
$footer_row = $row + 2;
$sheet->setCellValue('H' . $footer_row, 'Tasikmalaya, ' . $date_label);
$sheet->setCellValue('H' . ($footer_row + 1), 'Admin LPM');
$sheet->getStyle('H' . $footer_row . ':I' . ($footer_row + 1))->getAlignment()->setHorizontal('center');

// === Output File ===
$filename = 'Laporan_Akreditasi_UNIRU_' . date('Ymd_His') . '.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;
?>
