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

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

// 2. REKAP TOTAL - CEK TABEL SATU-SATU
$total_gereja = 0;
$total_gembala = 0;
$total_pelayan = 0;
$total_pejabat = 0;
$total_jemaat = 0;

try { $total_gereja = $conn->query("SELECT COUNT(*) FROM gereja")->fetchColumn(); } catch (PDOException $e) {}
try { $total_gembala = $conn->query("SELECT COUNT(*) FROM users WHERE role='gembala'")->fetchColumn(); } catch (PDOException $e) {}
try { $total_pelayan = $conn->query("SELECT COUNT(*) FROM pelayan")->fetchColumn(); } catch (PDOException $e) {}
try { $total_pejabat = $conn->query("SELECT COUNT(*) FROM pejabat_gereja")->fetchColumn(); } catch (PDOException $e) {}
try { $total_jemaat = $conn->query("SELECT COUNT(*) FROM jemaat")->fetchColumn(); } catch (PDOException $e) {}

// 3. JENIS KELAMIN
$jk = ['laki' => 0, 'perempuan' => 0];
try {
    $jk = $conn->query("SELECT
        SUM(CASE WHEN jenis_kelamin='L' THEN 1 ELSE 0 END) as laki,
        SUM(CASE WHEN jenis_kelamin='P' THEN 1 ELSE 0 END) as perempuan
        FROM jemaat")->fetch(PDO::FETCH_ASSOC);
} catch (PDOException $e) {}

// 4. KATEGORI UMUR
$umur = ['balita'=>0,'anak'=>0,'remaja'=>0,'pemuda'=>0,'dewasa'=>0,'lansia'=>0];
try {
    $umur = $conn->query("SELECT
        SUM(CASE WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) <= 5 THEN 1 ELSE 0 END) as balita,
        SUM(CASE WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) BETWEEN 6 AND 12 THEN 1 ELSE 0 END) as anak,
        SUM(CASE WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) BETWEEN 13 AND 17 THEN 1 ELSE 0 END) as remaja,
        SUM(CASE WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) BETWEEN 18 AND 30 THEN 1 ELSE 0 END) as pemuda,
        SUM(CASE WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) BETWEEN 31 AND 59 THEN 1 ELSE 0 END) as dewasa,
        SUM(CASE WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) >= 60 THEN 1 ELSE 0 END) as lansia
        FROM jemaat")->fetch(PDO::FETCH_ASSOC);
} catch (PDOException $e) {}

$wbi = 0;
try {
    $wbi = $conn->query("SELECT COUNT(*) FROM jemaat WHERE jenis_kelamin='P' AND TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) >= 18")->fetchColumn();
} catch (PDOException $e) {}

// 5. DATA PER GEREJA
$data_gereja = [];
try {
    $data_gereja = $conn->query("SELECT g.nama_gereja, g.alamat, u.nama_lengkap as nama_gembala, u.no_telp as telp_gembala,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id) as jml_jemaat,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND jenis_kelamin='L') as jml_laki,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND jenis_kelamin='P') as jml_perempuan,
        (SELECT COUNT(*) FROM pelayan WHERE gereja_id=g.id) as jml_pelayan,
        (SELECT COUNT(*) FROM pejabat_gereja WHERE gereja_id=g.id) as jml_pejabat
        FROM gereja g 
        LEFT JOIN users u ON g.id=u.gereja_id AND u.role='gembala'
        ORDER BY g.nama_gereja ASC")->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    // Kalo tabel pejabat_gereja belum ada, query tanpa itu
    try {
        $data_gereja = $conn->query("SELECT g.nama_gereja, g.alamat, u.nama_lengkap as nama_gembala, u.no_telp as telp_gembala,
            (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id) as jml_jemaat,
            (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND jenis_kelamin='L') as jml_laki,
            (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND jenis_kelamin='P') as jml_perempuan,
            (SELECT COUNT(*) FROM pelayan WHERE gereja_id=g.id) as jml_pelayan,
            0 as jml_pejabat
            FROM gereja g 
            LEFT JOIN users u ON g.id=u.gereja_id AND u.role='gembala'
            ORDER BY g.nama_gereja ASC")->fetchAll(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {}
}

ob_end_clean();

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, 15, 8, 25, '', 'JPG');
        $this->SetY(10);
        $this->SetFont('helvetica', 'B', 16);
        $this->Cell(0, 7, 'LAPORAN REKAPITULASI', 0, 1, 'C');
        $this->SetFont('helvetica', 'B', 14);
        $this->Cell(0, 7, strtoupper($this->nama_dpw), 0, 1, 'C');
        $this->SetFont('helvetica', '', 9);
        $this->Cell(0, 4, $this->alamat_dpw, 0, 1, 'C');
        $this->Cell(0, 4, 'Telp: '.$this->telp_dpw.' | Email: '.$this->email_dpw, 0, 1, 'C');
        $this->SetLineWidth(0.8);
        $this->Line(15, 35, 195, 35);
        $this->Ln(8);
    }
    public function Footer() {
        $this->SetY(-15);
        $this->SetFont('helvetica', 'I', 8);
        $this->Cell(0, 10, 'Dicetak: '.date('d-m-Y H:i').' | Halaman '.$this->getAliasNumPage().' dari '.$this->getAliasNbPages(), 0, 0, 'C');
    }
}

$pdf = new MYPDF('P', 'mm', 'A4', 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(15, 38, 15);
$pdf->SetAutoPageBreak(TRUE, 20);
$pdf->SetTitle('Rekapitulasi DPW '.$nama_dpw);
$pdf->AddPage();

$pdf->SetFont('helvetica', 'B', 12);
$pdf->Cell(0, 8, 'A. REKAPITULASI STRUKTUR ORGANISASI', 0, 1, 'L');
$pdf->SetFont('helvetica', '', 10);
$html = '<table border="1" cellpadding="5">
<tr style="background-color:#f2f2f2;">
    <td width="40%"><b>Total Gereja</b></td><td width="10%" align="center"><b>'.$total_gereja.'</b></td>
    <td width="40%"><b>Total Gembala Sidang</b></td><td width="10%" align="center"><b>'.$total_gembala.'</b></td>
</tr>
<tr>
    <td><b>Total Pelayan</b></td><td align="center"><b>'.$total_pelayan.'</b></td>
    <td><b>Total Pejabat Gereja</b></td><td align="center"><b>'.$total_pejabat.'</b></td>
</tr>
<tr style="background-color:#f2f2f2;">
    <td><b>Total Keseluruhan Jemaat</b></td><td align="center"><b>'.$total_jemaat.' Jiwa</b></td>
    <td colspan="2"></td>
</tr>
</table>';
$pdf->writeHTML($html, true, false, '');
$pdf->Ln(5);

$pdf->SetFont('helvetica', 'B', 12);
$pdf->Cell(0, 8, 'B. STATISTIK KESELURUHAN JEMAAT', 0, 1, 'L');
$pdf->SetFont('helvetica', 'B', 9);
$pdf->Cell(60, 7, 'Kategori', 1, 0, 'C', 1);
$pdf->Cell(30, 7, 'Jumlah', 1, 0, 'C', 1);
$pdf->Cell(30, 7, 'Persentase', 1, 1, 'C', 1);
$pdf->SetFont('helvetica', '', 9);

$kategori = [
    ['1. Laki-laki', $jk['laki']],
    ['2. Perempuan', $jk['perempuan']],
    ['3. WBI (Wanita 18+)', $wbi],
    ['4. Balita (0-5 Thn)', $umur['balita']],
    ['5. Anak-anak (6-12 Thn)', $umur['anak']],
    ['6. Remaja (13-17 Thn)', $umur['remaja']],
    ['7. Pemuda (18-30 Thn)', $umur['pemuda']],
    ['8. Dewasa (31-59 Thn)', $umur['dewasa']],
    ['9. Lansia (60+ Thn)', $umur['lansia']],
];

foreach($kategori as $k){
    $persen = $total_jemaat > 0? round($k[1]/$total_jemaat*100,1).'%' : '0%';
    $pdf->Cell(60, 6, $k[0], 1, 0, 'L');
    $pdf->Cell(30, 6, $k[1].' Jiwa', 1, 0, 'C');
    $pdf->Cell(30, 6, $persen, 1, 1, 'C');
}

if(count($data_gereja) > 0){
    $pdf->AddPage();
    $pdf->SetFont('helvetica', 'B', 12);
    $pdf->Cell(0, 8, 'C. RINCIAN DATA PER GEREJA', 0, 1, 'L');
    $pdf->SetFont('helvetica', 'B', 7);
    $pdf->Cell(8, 6, 'No', 1, 0, 'C', 1);
    $pdf->Cell(45, 6, 'Nama Gereja', 1, 0, 'C', 1);
    $pdf->Cell(40, 6, 'Gembala', 1, 0, 'C', 1);
    $pdf->Cell(15, 6, 'Jemaat', 1, 0, 'C', 1);
    $pdf->Cell(12, 6, 'L', 1, 0, 'C', 1);
    $pdf->Cell(12, 6, 'P', 1, 0, 'C', 1);
    $pdf->Cell(15, 6, 'Pelayan', 1, 0, 'C', 1);
    $pdf->Cell(15, 6, 'Pejabat', 1, 1, 'C', 1);
    $pdf->SetFont('helvetica', '', 7);

    $no = 1;
    foreach($data_gereja as $g){
        if($pdf->GetY() > 265){
            $pdf->AddPage();
            $pdf->SetFont('helvetica', 'B', 7);
            $pdf->Cell(8, 6, 'No', 1, 0, 'C', 1);
            $pdf->Cell(45, 6, 'Nama Gereja', 1, 0, 'C', 1);
            $pdf->Cell(40, 6, 'Gembala', 1, 0, 'C', 1);
            $pdf->Cell(15, 6, 'Jemaat', 1, 0, 'C', 1);
            $pdf->Cell(12, 6, 'L', 1, 0, 'C', 1);
            $pdf->Cell(12, 6, 'P', 1, 0, 'C', 1);
            $pdf->Cell(15, 6, 'Pelayan', 1, 0, 'C', 1);
            $pdf->Cell(15, 6, 'Pejabat', 1, 1, 'C', 1);
            $pdf->SetFont('helvetica', '', 7);
        }
        $pdf->Cell(8, 5, $no++, 1, 0, 'C');
        $pdf->Cell(45, 5, $g['nama_gereja'], 1, 0, 'L');
        $pdf->Cell(40, 5, $g['nama_gembala']?? '-', 1, 0, 'L');
        $pdf->Cell(15, 5, $g['jml_jemaat'], 1, 0, 'C');
        $pdf->Cell(12, 5, $g['jml_laki'], 1, 0, 'C');
        $pdf->Cell(12, 5, $g['jml_perempuan'], 1, 0, 'C');
        $pdf->Cell(15, 5, $g['jml_pelayan'], 1, 0, 'C');
        $pdf->Cell(15, 5, $g['jml_pejabat'], 1, 1, 'C');
    }
}

$pdf->Ln(15);
$pdf->SetFont('helvetica', '', 10);
$pdf->Cell(110, 5, '', 0, 0);
$pdf->Cell(70, 5, 'Keerom, '.date('d F Y'), 0, 1, 'C');
$pdf->Cell(110, 5, '', 0, 0);
$pdf->Cell(70, 5, 'Ketua DPW,', 0, 1, 'C');
$pdf->Ln(20);
$pdf->Cell(110, 5, '', 0, 0);
$pdf->SetFont('helvetica', 'B, U', 10);
$pdf->Cell(70, 5, $ketua_dpw, 0, 1, 'C');

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