<?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;

// === DATA AUDIT ===
$stmt = $pdo->query("
  SELECT a.*, u.full_name AS creator,
  (SELECT COUNT(*) FROM audit_findings f WHERE f.audit_id = a.id) AS total_findings,
  (SELECT COUNT(*) FROM audit_findings f WHERE f.audit_id = a.id AND f.status = 'closed') AS closed_findings
  FROM audits a
  LEFT JOIN users u ON a.created_by = u.id
  ORDER BY a.created_at DESC
");
$audits = $stmt->fetchAll(PDO::FETCH_ASSOC);

// === SETUP FILE ===
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Laporan Audit');

// Judul
$sheet->mergeCells('A1:I1');
$sheet->setCellValue('A1', 'LAPORAN AUDIT MUTU INTERNAL');
$sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
$sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$sheet->mergeCells('A2:I2');
$sheet->setCellValue('A2', 'Tanggal Cetak: ' . date('d M Y H:i'));
$sheet->getStyle('A2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

// Header tabel
$headers = ['No', 'Judul Audit', 'Periode', 'Status', 'Total Temuan', 'Selesai', 'Progress (%)', 'Ringkasan', 'Pembuat'];
$sheet->fromArray($headers, null, 'A4');

$sheet->getStyle('A4:I4')->applyFromArray([
    'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
    'fill' => ['fillType' => Fill::FILL_SOLID, 'color' => ['rgb' => '084D36']],
    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
]);

$row = 5;
$no = 1;

// Data utama
foreach ($audits as $a) {
    $progress = $a['total_findings'] > 0 ? round(($a['closed_findings'] / $a['total_findings']) * 100) : 0;
    $sheet->fromArray([
        $no++,
        $a['audit_title'],
        "{$a['audit_period_start']} s.d {$a['audit_period_end']}",
        ucfirst($a['status']),
        $a['total_findings'],
        $a['closed_findings'],
        $progress,
        $a['summary'],
        $a['creator'] ?? '-'
    ], null, "A{$row}");

    // style border tiap audit
    $sheet->getStyle("A{$row}:I{$row}")->applyFromArray([
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
    ]);

    // === TEMUAN AUDIT ===
    $findStmt = $pdo->prepare("SELECT * FROM audit_findings WHERE audit_id = ? ORDER BY id ASC");
    $findStmt->execute([$a['id']]);
    $findings = $findStmt->fetchAll(PDO::FETCH_ASSOC);

    if ($findings) {
        foreach ($findings as $f) {
            $row++;
            $sheet->setCellValue("B{$row}", "↳ " . $f['finding_title']);
            $sheet->setCellValue("C{$row}", "Severity: {$f['severity']}");
            $sheet->setCellValue("D{$row}", "Unit: {$f['responsible_unit']}");
            $sheet->setCellValue("E{$row}", "Due: {$f['due_date']}");
            $sheet->setCellValue("F{$row}", "Status: " . ucfirst($f['status'] ?? '-'));
            $sheet->setCellValue("G{$row}", "Rekomendasi: {$f['recommendation']}");
            $sheet->mergeCells("H{$row}:I{$row}");
            $sheet->setCellValue("H{$row}", "Deskripsi: {$f['description']}");
            $sheet->getStyle("A{$row}:I{$row}")->applyFromArray([
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_DOTTED]],
                'font' => ['italic' => true, 'color' => ['rgb' => '555555']]
            ]);
        }
    }

    $row++;
}

// auto width
foreach (range('A', 'I') as $col) {
    $sheet->getColumnDimension($col)->setAutoSize(true);
}

// === OUTPUT ===
$writer = new Xlsx($spreadsheet);
$filename = 'Laporan_Audit_Mutu_' . date('Ymd_His') . '.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment; filename=\"$filename\"");
$writer->save("php://output");
exit;
?>
