<?php
require_once __DIR__ . '/../../config/secure_bootstrap.php';
require_once __DIR__ . '/../../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;

// === Ambil data ===
$search = trim($_POST['search_export'] ?? '');
$params = [];
$sql = "SELECT a.*, u.full_name AS creator FROM activities a LEFT JOIN users u ON a.created_by = u.id";
if ($search !== '') {
  $sql .= " WHERE a.title LIKE ? OR a.description LIKE ? OR a.organizer LIKE ? OR a.location LIKE ?";
  $like = "%$search%";
  $params = [$like, $like, $like, $like];
}
$sql .= " ORDER BY a.date_start DESC";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

// === Judul dan Tanggal ===
$title = "LAPORAN DATA KEGIATAN LPM UNIVERSITAS RIYADLUL ULUM";
$date = "Tanggal Cetak: " . date('d F Y, H:i');

// Merge cell untuk judul dan tanggal
$sheet->mergeCells('A1:H1');
$sheet->mergeCells('A2:H2');
$sheet->setCellValue('A1', $title);
$sheet->setCellValue('A2', $date);

// Style judul dan tanggal
$sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
$sheet->getStyle('A2')->getFont()->setItalic(true)->setSize(11);
$sheet->getStyle('A1:A2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

// === Header tabel ===
$headerRow = 4;
$headers = ['No', 'Judul', 'Deskripsi', 'Tanggal Mulai', 'Tanggal Selesai', 'Tempat', 'Penyelenggara', 'Dibuat Oleh'];
$sheet->fromArray($headers, null, 'A' . $headerRow);

// Style header
$headerStyle = [
  'font' => ['bold' => true],
  'alignment' => [
    'horizontal' => Alignment::HORIZONTAL_CENTER,
    'vertical' => Alignment::VERTICAL_CENTER
  ],
  'fill' => [
    'fillType' => Fill::FILL_SOLID,
    'startColor' => ['rgb' => 'D9EAD3'] // hijau lembut
  ],
  'borders' => [
    'allBorders' => [
      'borderStyle' => Border::BORDER_THIN,
      'color' => ['rgb' => '000000']
    ]
  ]
];
$sheet->getStyle("A{$headerRow}:H{$headerRow}")->applyFromArray($headerStyle);

// === Isi data ===
$rowNum = $headerRow + 1;
foreach ($rows as $i => $row) {
  $sheet->setCellValue("A{$rowNum}", $i + 1);
  $sheet->setCellValue("B{$rowNum}", $row['title']);
  $sheet->setCellValue("C{$rowNum}", $row['description']);
  $sheet->setCellValue("D{$rowNum}", $row['date_start']);
  $sheet->setCellValue("E{$rowNum}", $row['date_end']);
  $sheet->setCellValue("F{$rowNum}", $row['location']);
  $sheet->setCellValue("G{$rowNum}", $row['organizer']);
  $sheet->setCellValue("H{$rowNum}", $row['creator']);
  $rowNum++;
}

// === Style border seluruh tabel ===
$dataStyle = [
  'borders' => [
    'allBorders' => [
      'borderStyle' => Border::BORDER_THIN,
      'color' => ['rgb' => '000000']
    ]
  ],
  'alignment' => [
    'vertical' => Alignment::VERTICAL_TOP
  ]
];
$sheet->getStyle("A{$headerRow}:H" . ($rowNum - 1))->applyFromArray($dataStyle);

// === Atur kolom dan wrapping teks ===
foreach (range('A', 'H') as $col) {
  if ($col === 'C') { // Kolom C = Deskripsi
    $sheet->getColumnDimension($col)->setWidth(60); // lebar cukup besar
    $sheet->getStyle("{$col}{$headerRow}:{$col}" . ($rowNum - 1))
          ->getAlignment()->setWrapText(true)
          ->setHorizontal(Alignment::HORIZONTAL_JUSTIFY); // rata kiri-kanan
  } else {
    $sheet->getColumnDimension($col)->setAutoSize(true);
  }
}

// === Atur tinggi baris otomatis ===
for ($r = $headerRow + 1; $r < $rowNum; $r++) {
  $sheet->getRowDimension($r)->setRowHeight(-1);
}

// === Tambahkan garis pemisah halus antarbaris ===
$sheet->getStyle("A" . ($headerRow + 1) . ":H" . ($rowNum - 1))
      ->getBorders()->getBottom()->setBorderStyle(Border::BORDER_HAIR)
      ->getColor()->setRGB('CCCCCC');

// === Freeze header biar tetap terlihat saat scroll ===
$sheet->freezePane('A' . ($headerRow + 1));

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