<?php
ob_start();
require_once '../config/auth.php';
cek_role('admin_dpw');
require_once '../vendor/autoload.php';

// 1. DATA DPW
try {
    $dpw = $conn->query("SELECT * FROM profil_dpw WHERE id=1")->fetch(PDO::FETCH_ASSOC);
    $nama_dpw = $dpw['nama_dpw']?? 'BPW Keerom';
    $alamat_dpw = $dpw['alamat']?? 'Jl. Trans Irian, Keerom, Papua';
    $ketua_dpw = $dpw['ketua_dpw']?? 'Ketua BPW';
    $telp_dpw = $dpw['no_telp']?? '-';
    $email_dpw = $dpw['email']?? '-';
} catch (PDOException $e) {
    $nama_dpw = 'BPW Keerom';
    $alamat_dpw = 'Jl. Trans Irian, Keerom, Papua';
    $ketua_dpw = 'Ketua BPW';
    $telp_dpw = '-';
    $email_dpw = '-';
}

// 2. QUERY ANTI ERROR - SELECT u.* BIAR GAK PEDULI NAMA KOLOM
try {
    $sql = "SELECT 
        g.id,
        g.nama_gereja,
        u.*,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id) as jml_jemaat
        FROM gereja g 
        LEFT JOIN users u ON g.id = u.gereja_id
        ORDER BY g.nama_gereja ASC";
    $data_gembala = $conn->query($sql)->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    die("Query Error: " . $e->getMessage());
}

// FILTER CUMA YANG ADA USER-NYA
$data_gembala = array_filter($data_gembala, function($g){
    return !empty($g['id']) && !empty($g['username']) || !empty($g['name']) || !empty($g['nama']) || !empty($g['nama_lengkap']);
});

$total_gembala = count($data_gembala);

ob_end_clean();

// FOLIO LANDSCAPE: 215.9 x 330.2 mm
class MYPDF extends TCPDF {
    public $logo = '../assets/img/logo-gbi.JPG';
    public $nama_dpw = '';
    public $alamat_dpw = '';
    public $telp_dpw = '';
    public $email_dpw = '';

    public function Header() {
        if(file_exists($this->logo)) $this->Image($this->logo, 70, 6, 18, '', 'JPG');
        $this->SetY(6);
        $this->SetFont('helvetica', 'B', 13);
        $this->Cell(0, 5, 'LAPORAN DATA GEMBALA DAN BENDAHARA', 0, 1, 'C');
        $this->SetFont('helvetica', 'B', 11);
        $this->Cell(0, 5, strtoupper($this->nama_dpw), 0, 1, 'C');
        $this->SetFont('helvetica', '', 7);
        $this->Cell(0, 4, $this->alamat_dpw.' | Telp: '.$this->telp_dpw.' | Email: '.$this->email_dpw, 0, 1, 'C');
        $this->SetLineWidth(0.5);
        $this->Line(12, 23, 318, 23);
    }
    public function Footer() {
        $this->SetY(-10);
        $this->SetFont('helvetica', 'I', 7);
        $this->Cell(0, 6, 'Dicetak: '.date('d-m-Y H:i').' | Halaman '.$this->getAliasNumPage().' dari '.$this->getAliasNbPages(), 0, 0, 'C');
    }
}

$pdf = new MYPDF('L', 'mm', array(215.9, 330.2), true, 'UTF-8', false);
$pdf->nama_dpw = $nama_dpw;
$pdf->alamat_dpw = $alamat_dpw;
$pdf->telp_dpw = $telp_dpw;
$pdf->email_dpw = $email_dpw;
$pdf->SetMargins(12, 25, 12);
$pdf->SetAutoPageBreak(TRUE, 12);
$pdf->SetTitle('Laporan Data Gembala '.$nama_dpw);
$pdf->AddPage();

$pdf->SetFont('helvetica', 'B', 10);
$pdf->Cell(0, 6, 'DAFTAR GEMBALA SIDANG DAN BENDAHARA GEREJA-', 0, 1, 'L');
$pdf->SetFont('helvetica', '', 8);
$pdf->Cell(0, 5, 'Total Gembala Terdaftar: '.$total_gembala.' Orang', 0, 1, 'L');
$pdf->Ln(1);

// TABEL HEADER - FULL WIDTH 306mm
$pdf->SetFont('helvetica', 'B', 6.5);
$pdf->SetFillColor(230, 230, 230);
$pdf->Cell(7, 12, 'No', 1, 0, 'C', 1);
$pdf->Cell(40, 12, 'Nama Gembala/Bendahara', 1, 0, 'C', 1);
$pdf->Cell(50, 12, 'Nama Gereja', 1, 0, 'C', 1);
$pdf->Cell(30, 12, 'No. Telp', 1, 0, 'C', 1);
$pdf->Cell(45, 12, 'Email', 1, 0, 'C', 1);
$pdf->Cell(30, 12, 'Tgl Lahir', 1, 0, 'C', 1);
$pdf->Cell(30, 12, 'Pendidikan', 1, 0, 'C', 1);
$pdf->Cell(30, 12, 'Tgl Tahbisan', 1, 0, 'C', 1);
$pdf->Cell(30, 12, 'Jemaat', 1, 1, 'C', 1);
$pdf->SetFont('helvetica', '', 6.5);

$no = 1;
foreach($data_gembala as $g){
    if($pdf->GetY() > 195){
        $pdf->AddPage();
        $pdf->SetFont('helvetica', 'B', 6.5);
        $pdf->SetFillColor(230, 230, 230);
        $pdf->Cell(7, 12, 'No', 1, 0, 'C', 1);
        $pdf->Cell(40, 12, 'Nama Gembala/Bendara', 1, 0, 'C', 1);
        $pdf->Cell(50, 12, 'Nama Gereja', 1, 0, 'C', 1);
        $pdf->Cell(30, 12, 'No. Telp', 1, 0, 'C', 1);
        $pdf->Cell(45, 12, 'Email', 1, 0, 'C', 1);
        $pdf->Cell(30, 12, 'Tgl Lahir', 1, 0, 'C', 1);
        $pdf->Cell(30, 12, 'Pendidikan', 1, 0, 'C', 1);
        $pdf->Cell(30, 12, 'Tgl Tahbisan', 1, 0, 'C', 1);
        $pdf->Cell(30, 12, 'Jemaat', 1, 1, 'C', 1);
        $pdf->SetFont('helvetica', '', 6.5);
    }
    
    // CEK SEMUA KEMUNGKINAN NAMA KOLOM PAKE ??
    $nama_gembala = $g['nama_lengkap'] ?? $g['nama'] ?? $g['name'] ?? $g['full_name'] ?? $g['username'] ?? '-';
    $telp = $g['no_telp'] ?? $g['telp'] ?? $g['telepon'] ?? $g['phone'] ?? $g['no_hp'] ?? '-';
    $email = $g['email'] ?? $g['mail'] ?? '-';
    $tgl_lahir = ($g['tgl_lahir'] ?? $g['tanggal_lahir'] ?? $g['birth_date'] ?? '') != '' && ($g['tgl_lahir'] ?? '') != '0000-00-00' ? date('d-m-Y', strtotime($g['tgl_lahir'] ?? $g['tanggal_lahir'] ?? $g['birth_date'])) : '-';
    $pendidikan = $g['pendidikan'] ?? $g['education'] ?? $g['gelar'] ?? '-';
    $tgl_tahbisan = ($g['tgl_tahbisan'] ?? $g['tanggal_tahbisan'] ?? $g['tgl_pentahbisan'] ?? '') != '' && ($g['tgl_tahbisan'] ?? '') != '0000-00-00' ? date('d-m-Y', strtotime($g['tgl_tahbisan'] ?? $g['tanggal_tahbisan'] ?? $g['tgl_pentahbisan'])) : '-';
    
    $pdf->Cell(7, 12, $no++, 1, 0, 'C');
    $pdf->Cell(40, 12, $nama_gembala, 1, 0, 'L');
    $pdf->Cell(50, 12, $g['nama_gereja'], 1, 0, 'L');
    $pdf->Cell(30, 12, $telp, 1, 0, 'C');
    $pdf->Cell(45, 12, $email, 1, 0, 'L');
    $pdf->Cell(30, 12, $tgl_lahir, 1, 0, 'C');
    $pdf->Cell(30, 12, $pendidikan, 1, 0, 'C');
    $pdf->Cell(30, 12, $tgl_tahbisan, 1, 0, 'C');
    $pdf->Cell(30, 12, $g['jml_jemaat'], 1, 1, 'C');
}

if($pdf->GetY() > 170){
    $pdf->AddPage();
}

$pdf->Ln(10);
$pdf->SetFont('helvetica', '', 9);
$pdf->Cell(220, 5, '', 0, 0);
$pdf->Cell(70, 5, 'Keerom, '.date('d F Y'), 0, 1, 'C');
$pdf->Cell(220, 5, '', 0, 0);
$pdf->Cell(70, 5, 'Ketua BPW,', 0, 1, 'C');
$pdf->Ln(18);
$pdf->Cell(220, 5, '', 0, 0);
$pdf->SetFont('helvetica', 'B, U', 9);
$pdf->Cell(70, 5, $ketua_dpw, 0, 1, 'C');

$pdf->Output('Laporan_Data_Gembala_'.$nama_dpw.'_'.date('Ymd').'.pdf', 'I');
exit;
?>