<?php
session_start();
require_once '../config/database.php';
require_once '../PhpSpreadsheet-master/src/Bootstrap.php';

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

$db = new Database();
$conn = $db->getConnection();
$role = $_SESSION['role'];
$user_id = $_SESSION['user_id'];

if($role == 'admin_dpw'){
    $sql = "SELECT j.*, g.nama_gereja FROM jemaat j 
            LEFT JOIN gereja g ON j.gereja_id = g.id 
            ORDER BY j.id DESC";
    $stmt = $conn->prepare($sql);
    $stmt->execute();
} else {
    $sql = "SELECT j.*, g.nama_gereja 
            FROM jemaat j 
            INNER JOIN gereja g ON j.gereja_id = g.id 
            WHERE g.gembala_id = ? 
            ORDER BY j.id DESC";
    $stmt = $conn->prepare($sql);
    $stmt->execute([$user_id]);
}

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Data Jemaat');
$sheet->setCellValue('A1', 'No')->setCellValue('B1', 'No Anggota')->setCellValue('C1', 'Nama Lengkap')
      ->setCellValue('D1', 'L/P')->setCellValue('E1', 'Gereja')->setCellValue('F1', 'Status Baptis')
      ->setCellValue('G1', 'No HP')->setCellValue('H1', 'Alamat');

$sheet->getStyle('A1:H1')->getFont()->setBold(true);

$row = 2;
$no = 1;
while($d = $stmt->fetch(PDO::FETCH_ASSOC)){
    $sheet->setCellValue('A'.$row, $no++)
          ->setCellValue('B'.$row, $d['no_anggota'])
          ->setCellValue('C'.$row, $d['nama_lengkap'])
          ->setCellValue('D'.$row, $d['jenis_kelamin'])
          ->setCellValue('E'.$row, $d['nama_gereja'])
          ->setCellValue('F'.$row, $d['status_baptis'])
          ->setCellValue('G'.$row, $d['no_hp'])
          ->setCellValue('H'.$row, $d['alamat']);
    $row++;
}

foreach(range('A','H') as $col) {
    $sheet->getColumnDimension($col)->setAutoSize(true);
}

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="laporan_jemaat_'.date('Ymd').'.xlsx"');
header('Cache-Control: max-age=0');

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