<?php
ob_start();
error_reporting(0);
ini_set('display_errors', 0);

session_start();
require_once '../config.php';
require_once '../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;

if (!isset($_SESSION['posyandu'])) exit('Unauthorized');

$bulan = $_GET['bulan'] ?? date('m');
$tahun = $_GET['tahun'] ?? date('Y');
$id_posyandu = $_SESSION['posyandu'];

$nama_bulan = [
"01"=>"Januari","02"=>"Februari","03"=>"Maret","04"=>"April",
"05"=>"Mei","06"=>"Juni","07"=>"Juli","08"=>"Agustus",
"09"=>"September","10"=>"Oktober","11"=>"November","12"=>"Desember"
];

$stmt_p = $pdo->prepare("SELECT nama_posyandu FROM posyandu WHERE id_posyandu=?");
$stmt_p->execute([$id_posyandu]);
$posyandu = $stmt_p->fetch();

$kategori_umur = [
'0-5'=>[0,5],'6-11'=>[6,11],'12-23'=>[12,23],
'24-35'=>[24,35],'36-60'=>[36,60]
];

$keys = ['S','D','N','T','T2','O','B','A','C','I','OB','GL','RGL','GB','GK','GBR','BGM'];
$rekap=[];
foreach($keys as $k){
    foreach($kategori_umur as $ku=>$r){
        $rekap[$k][$ku]['L']=0;
        $rekap[$k][$ku]['P']=0;
    }
}

function kbm_min_naik($jk,$u){
    $u=max(1,min(60,(int)$u));
    if($jk=='L'){
        if($u==1)return 0.8;
        if($u==2)return 0.9;
        if($u==3)return 0.8;
        if($u==4)return 0.6;
        if($u==5)return 0.5;
        if($u==6||$u==7)return 0.4;
        if($u>=8&&$u<=11)return 0.3;
        return 0.2;
    }else{
        if($u==1)return 0.8;
        if($u==2)return 0.9;
        if($u==3)return 0.8;
        if($u==4)return 0.6;
        if($u==5)return 0.5;
        if($u==6)return 0.4;
        if($u>=7&&$u<=10)return 0.3;
        return 0.2;
    }
}
function get_kat($u,$kat){
    foreach($kat as $k=>$r){
        if($u>=$r[0]&&$u<=$r[1]) return $k;
    }
    return null;
}

$stmt_all=$pdo->prepare("SELECT id_balita,jenis_kelamin,tgl_lahir FROM balita WHERE id_posyandu=?");
$stmt_all->execute([$id_posyandu]);
$balita_all=$stmt_all->fetchAll();

foreach($balita_all as $b){
    $usia=(new DateTime())->diff(new DateTime($b['tgl_lahir']));
    $u=$usia->y*12+$usia->m;
    $kat=get_kat($u,$kategori_umur);
    if($kat){
        $jk=$b['jenis_kelamin']=='L'?'L':'P';
        $rekap['S'][$kat][$jk]++;
    }
}

$query="SELECT b.*,p.* FROM balita b
JOIN pemeriksaan_balita p ON b.id_balita=p.id_balita
WHERE b.id_posyandu=? AND MONTH(p.tgl_periksa)=? AND YEAR(p.tgl_periksa)=?
ORDER BY b.nama_balita ASC";
$stmt=$pdo->prepare($query);
$stmt->execute([$id_posyandu,$bulan,$tahun]);
$data=$stmt->fetchAll();

$hasil_status=[];

foreach($data as $r){

    $tgl=new DateTime($r['tgl_periksa']);
    $lahir=new DateTime($r['tgl_lahir']);
    $usia=$tgl->diff($lahir)->y*12+$tgl->diff($lahir)->m;
    $jk=$r['jenis_kelamin']=='L'?'L':'P';
    $kat=get_kat($usia,$kategori_umur);
    if(!$kat) continue;

    $rekap['D'][$kat][$jk]++;

    $tgl_lalu=(clone $tgl)->modify('-1 month');
    $stmt_prev=$pdo->prepare("SELECT bb FROM pemeriksaan_balita WHERE id_balita=? AND MONTH(tgl_periksa)=? AND YEAR(tgl_periksa)=? LIMIT 1");
    $stmt_prev->execute([$r['id_balita'],$tgl_lalu->format('m'),$tgl_lalu->format('Y')]);
    $prev=$stmt_prev->fetch();

    $tgl_2=(clone $tgl)->modify('-2 month');
    $stmt_prev2=$pdo->prepare("SELECT bb FROM pemeriksaan_balita WHERE id_balita=? AND MONTH(tgl_periksa)=? AND YEAR(tgl_periksa)=? LIMIT 1");
    $stmt_prev2->execute([$r['id_balita'],$tgl_2->format('m'),$tgl_2->format('Y')]);
    $prev2=$stmt_prev2->fetch();

    if(!$prev && !$prev2){
        $status='B'; $rekap['B'][$kat][$jk]++;
    }elseif(!$prev && $prev2){
        $status='O'; $rekap['O'][$kat][$jk]++;
    }else{
        $naik=$r['bb']-$prev['bb'];
        $kbm=kbm_min_naik($jk,$usia);
        if($naik>=$kbm){
            $status='N'; $rekap['N'][$kat][$jk]++;
        }else{
            $status='T'; $rekap['T'][$kat][$jk]++;
            if($prev2){
                $naik2=$prev['bb']-$prev2['bb'];
                if($naik2<$kbm){
                    $status='T2'; $rekap['T2'][$kat][$jk]++;
                }
            }
        }
    }

    $hasil_status[$r['id_balita']]=$status;

    if($r['vit_a']=='Ya') $rekap['A'][$kat][$jk]++;
    if($r['obat_cacing']=='Ya') $rekap['C'][$kat][$jk]++;
    if(!empty($r['imunisasi']) && $r['imunisasi']!='-') $rekap['I'][$kat][$jk]++;
    if($r['status_bbtb']=='Obesitas') $rekap['OB'][$kat][$jk]++;
    if($r['status_bbtb']=='Gizi Lebih') $rekap['GL'][$kat][$jk]++;
    if($r['status_bbtb']=='Resiko Gizi Lebih') $rekap['RGL'][$kat][$jk]++;
    if($r['status_bbtb']=='Gizi Baik') $rekap['GB'][$kat][$jk]++;
    if($r['status_bbtb']=='Gizi Kurang') $rekap['GK'][$kat][$jk]++;
    if($r['status_bbtb']=='Gizi Buruk') $rekap['GBR'][$kat][$jk]++;
    if($r['z_score_bbu']=='BB Sangat Kurang') $rekap['BGM'][$kat][$jk]++;
}

$spreadsheet=new Spreadsheet();
$sheet=$spreadsheet->getActiveSheet();
$sheet->setTitle('Laporan Bulanan');

$sheet->mergeCells('A1:AA1');
$sheet->setCellValue('A1','LAPORAN PENIMBANGAN BULANAN - '.strtoupper($posyandu['nama_posyandu']).' - '.$nama_bulan[$bulan].' '.$tahun);
$sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
$sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

/* =========================
   HEADER 2 BARIS + MERGE
========================= */

$sheet->mergeCells('A3:A4');
$sheet->mergeCells('B3:B4');

$sheet->mergeCells('C3:M3'); // IDENTITAS BALITA
$sheet->mergeCells('N3:S3'); // HASIL PENIMBANGAN
$sheet->mergeCells('T3:W3'); // PELAYANAN
$sheet->mergeCells('X3:AA3'); // INDEKS

$sheet->setCellValue('A3','NO');
$sheet->setCellValue('B3','TGL PERIKSA');
$sheet->setCellValue('C3','IDENTITAS BALITA');
$sheet->setCellValue('N3','HASIL PENIMBANGAN');
$sheet->setCellValue('T3','PELAYANAN');
$sheet->setCellValue('X3','INDEKS');

/* Sub Header */
$subHeaders=[
'C4'=>'ANAK KE',
'D4'=>'TGL LAHIR',
'E4'=>'JK',
'F4'=>'NIK',
'G4'=>'NAMA ANAK',
'H4'=>'BBL',
'I4'=>'PBL',
'J4'=>'NAMA IBU',
'K4'=>'RT',
'L4'=>'RW',
'M4'=>'DUSUN',

'N4'=>'USIA',
'O4'=>'BB',
'P4'=>'TB',
'Q4'=>'LILA',
'R4'=>'LIKA',
'S4'=>'STATUS',

'T4'=>'ASI',
'U4'=>'VIT A',
'V4'=>'IMUNISASI',
'W4'=>'O. CACING',

'X4'=>'BB/U',
'Y4'=>'TB/U',
'Z4'=>'BB/TB',
'AA4'=>'IMT/U'
];

foreach($subHeaders as $cell=>$val){
    $sheet->setCellValue($cell,$val);
}

/* =========================
   STYLE HEADER BIRU
========================= */

$sheet->getStyle('A3:AA4')->applyFromArray([
    'font'=>[
        'bold'=>true,
        'color'=>['rgb'=>'FFFFFF'],
        'size'=>10
    ],
    'alignment'=>[
        'horizontal'=>Alignment::HORIZONTAL_CENTER,
        'vertical'=>Alignment::VERTICAL_CENTER,
        'wrapText'=>true
    ],
    'fill'=>[
        'fillType'=>Fill::FILL_SOLID,
        'startColor'=>['rgb'=>'2F75B5']
    ],
    'borders'=>[
        'allBorders'=>[
            'borderStyle'=>Border::BORDER_THIN
        ]
    ]
]);

$sheet->getRowDimension(3)->setRowHeight(25);
$sheet->getRowDimension(4)->setRowHeight(35);

/* Geser data mulai baris 5 */
$row=5;

$row=5; $no=1;
foreach($data as $r){

    $tgl=new DateTime($r['tgl_periksa']);
    $lahir=new DateTime($r['tgl_lahir']);
    $usia=$tgl->diff($lahir)->y*12+$tgl->diff($lahir)->m;

    $sheet->fromArray([
        $no++,
        date('d/m/Y',strtotime($r['tgl_periksa'])),
        $r['anak_ke'],
        date('d/m/Y',strtotime($r['tgl_lahir'])),
        $r['jenis_kelamin'],
        "'".$r['nik_balita'],
        strtoupper($r['nama_balita']),
        $r['bb_lahir'],
        $r['pb_lahir'],
        $r['nama_ibu'],
        $r['rt'],$r['rw'],$r['dusun'],
        $usia,
        $r['bb'],$r['tb'],$r['lila'],$r['lika'],
        $hasil_status[$r['id_balita']] ?? '-',
        $r['asi_eksklusif'],
        $r['vit_a'],
        $r['imunisasi'],
        $r['obat_cacing'],
        $r['z_score_bbu'],
        $r['status_stunting'],
        $r['status_bbtb'],
        $r['status_imtu']
    ],NULL,'A'.$row);

    $row++;
}

$lastRow = $row-1;
$sheet->getStyle('A3:AA'.$lastRow)->applyFromArray([
    'borders'=>[
        'allBorders'=>[
            'borderStyle'=>Border::BORDER_THIN
        ]
    ]
]);
$sheet->freezePane('A5');
foreach(range('A','AA') as $c) $sheet->getColumnDimension($c)->setAutoSize(true);

/* =========================================================
   SHEET 2 — REKAPITULASI
=========================================================*/

$sheet2 = $spreadsheet->createSheet();
$sheet2->setTitle('Rekapitulasi');

/* =========================
   HEADER 2 TINGKAT
=========================*/

$sheet2->mergeCells('A1:A2');
$sheet2->setCellValue('A1','URAIAN');

$kelompokUmur = [
'0-5 bln','6-11 bln','12-23 bln','24-35 bln','36-60 bln','TOTAL'
];

$col = 'B';

foreach($kelompokUmur as $umur){
    $sheet2->mergeCells($col.'1:'.chr(ord($col)+2).'1');
    $sheet2->setCellValue($col.'1',$umur);

    $sheet2->setCellValue($col.'2','L');
    $sheet2->setCellValue(chr(ord($col)+1).'2','P');
    $sheet2->setCellValue(chr(ord($col)+2).'2','J');

    $col = chr(ord($col)+3);
}

/* Style Header */
$sheet2->getStyle('A1:S2')->applyFromArray([
    'font'=>[
        'bold'=>true,
        'color'=>['rgb'=>'FFFFFF']
    ],
    'alignment'=>[
        'horizontal'=>Alignment::HORIZONTAL_CENTER,
        'vertical'=>Alignment::VERTICAL_CENTER
    ],
    'fill'=>[
        'fillType'=>Fill::FILL_SOLID,
        'startColor'=>['rgb'=>'2F75B5']
    ],
    'borders'=>[
        'allBorders'=>[
            'borderStyle'=>Border::BORDER_THIN
        ]
    ]
]);

/* =========================
   DAFTAR URAIAN
=========================*/

$uraian = [
"Balita Terdaftar KMS (S)",
"Semua Balita Ditimbang (D)",
"Balita Naik Timbangan (N)",
"Balita Tidak Naik Timbangan (T)",
"Balita 2x Tidak Naik Berturut (T2)",
"Balita Bulan Lalu Tidak Hadir (O)",
"Balita Baru Pertama Timbang (B)",
"Mendapat Vitamin A (A)",
"Mendapat Obat Cacing (C)",
"Mendapat Imunisasi (I)",
"Balita Obesitas (BB/TB)",
"Balita Dengan Gizi Lebih (BB/TB)",
"Balita Dengan Resiko Gizi Lebih (BB/TB)",
"Balita Dengan Gizi Baik (BB/TB)",
"Balita Dengan Gizi Kurang (BB/TB)",
"Balita Dengan Gizi Buruk (BB/TB)",
"Balita Bawah Garis Merah (BB/U Sangat Kurang)"
];

$row = 3;

foreach($uraian as $u){
    $sheet2->setCellValue('A'.$row,$u);
    $row++;
}

/* =========================
   ISI DATA REKAP DARI $rekap
   (mengacu 100% ke logika Sheet 1)
=========================*/

$row = 3;

$mapIndex = [
0=>'S',
1=>'D',
2=>'N',
3=>'T',
4=>'T2',
5=>'O',
6=>'B',
7=>'A',
8=>'C',
9=>'I',
10=>'OB',
11=>'GL',
12=>'RGL',
13=>'GB',
14=>'GK',
15=>'GBR',
16=>'BGM'
];

foreach($mapIndex as $i=>$kode){

    $col='B';
    $totalL=0;
    $totalP=0;

    foreach($kategori_umur as $ku=>$range){

        $L = $rekap[$kode][$ku]['L'] ?? 0;
        $P = $rekap[$kode][$ku]['P'] ?? 0;
        $J = $L + $P;

        $sheet2->setCellValue($col.$row,$L);
        $sheet2->setCellValue(chr(ord($col)+1).$row,$P);
        $sheet2->setCellValue(chr(ord($col)+2).$row,$J);

        $totalL += $L;
        $totalP += $P;

        $col = chr(ord($col)+3);
    }

    // TOTAL
    $sheet2->setCellValue($col.$row,$totalL);
    $sheet2->setCellValue(chr(ord($col)+1).$row,$totalP);
    $sheet2->setCellValue(chr(ord($col)+2).$row,$totalL+$totalP);

    $row++;
}

/* Border Semua */
$lastRow = $row-1;

$sheet2->getStyle('A1:S'.$lastRow)->applyFromArray([
    'borders'=>[
        'allBorders'=>[
            'borderStyle'=>Border::BORDER_THIN
        ]
    ]
]);

foreach(range('A','S') as $c){
    $sheet2->getColumnDimension($c)->setAutoSize(true);
}

/* =========================================================
   SHEET 3 — ASI EKSKLUSIF
=========================================================*/

$sheet3 = $spreadsheet->createSheet();
$sheet3->setTitle('ASI Eksklusif');

/* ================= HEADER ================= */

$sheet3->mergeCells('A1:H1');
$sheet3->setCellValue('A1','LAPORAN ASI EKSKLUSIF - '.strtoupper($posyandu['nama_posyandu']).' - '.$nama_bulan[$bulan].' '.$tahun);
$sheet3->getStyle('A1')->getFont()->setBold(true)->setSize(12);
$sheet3->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

/* Header tabel */

$sheet3->mergeCells('A3:A4');
$sheet3->mergeCells('B3:B4');
$sheet3->mergeCells('C3:E3');
$sheet3->mergeCells('F3:H3');
//$sheet3->mergeCells('I3:I4');
//$sheet3->mergeCells('J3:J4');

$sheet3->setCellValue('A3','NO');
$sheet3->setCellValue('B3','KELOMPOK UMUR');
$sheet3->setCellValue('C3','JUMLAH');
$sheet3->setCellValue('F3','PEMBERIAN ASI');
//$sheet3->setCellValue('I3','JUMLAH');
//$sheet3->setCellValue('J3','TOTAL');

$sheet3->setCellValue('C4','L');
$sheet3->setCellValue('D4','P');
$sheet3->setCellValue('E4','J');

$sheet3->setCellValue('F4','L');
$sheet3->setCellValue('G4','P');
$sheet3->setCellValue('H4','J');

/* Style biru */
$sheet3->getStyle('A3:H4')->applyFromArray([
    'font'=>['bold'=>true,'color'=>['rgb'=>'FFFFFF']],
    'alignment'=>[
        'horizontal'=>Alignment::HORIZONTAL_CENTER,
        'vertical'=>Alignment::VERTICAL_CENTER
    ],
    'fill'=>[
        'fillType'=>Fill::FILL_SOLID,
        'startColor'=>['rgb'=>'2F75B5']
    ],
    'borders'=>[
        'allBorders'=>['borderStyle'=>Border::BORDER_THIN]
    ]
]);

/* ================= HITUNG DATA ================= */

$asi = [];

for($i=1;$i<=6;$i++){
    $asi[$i]=[
        'jumlah'=>['L'=>0,'P'=>0],
        'asi'=>['L'=>0,'P'=>0]
    ];
}

foreach($data as $r){

    $tgl=new DateTime($r['tgl_periksa']);
    $lahir=new DateTime($r['tgl_lahir']);
    $usia=$tgl->diff($lahir)->y*12+$tgl->diff($lahir)->m;

    if($usia>=1 && $usia<=6){

        $jk=$r['jenis_kelamin']=='L'?'L':'P';

        $asi[$usia]['jumlah'][$jk]++;

        if($r['asi_eksklusif']=='Ya'){
            $asi[$usia]['asi'][$jk]++;
        }
    }
}

/* ================= TAMPILKAN ================= */

$row=5;
$no=1;

for($u=1;$u<=6;$u++){

    $sheet3->setCellValue('A'.$row,$no++);
    $sheet3->setCellValue('B'.$row,"Jumlah Bayi Umur ".($u-1)."–".$u." Bulan");

    $JL=$asi[$u]['jumlah']['L'];
    $JP=$asi[$u]['jumlah']['P'];
    $JJ=$JL+$JP;

    $AL=$asi[$u]['asi']['L'];
    $AP=$asi[$u]['asi']['P'];
    $AJ=$AL+$AP;

    $sheet3->setCellValue('C'.$row,$JL);
    $sheet3->setCellValue('D'.$row,$JP);
    $sheet3->setCellValue('E'.$row,$JJ);

    $sheet3->setCellValue('F'.$row,$AL);
    $sheet3->setCellValue('G'.$row,$AP);
    $sheet3->setCellValue('H'.$row,$AJ);

    //$sheet3->setCellValue('I'.$row,$AJ);
    //$sheet3->setCellValue('J'.$row,$JJ);

    $row++;
}

/* Baris Total */
$sheet3->setCellValue('B'.$row,'JUMLAH');

$sheet3->setCellValue('C'.$row,"=SUM(C5:C10)");
$sheet3->setCellValue('D'.$row,"=SUM(D5:D10)");
$sheet3->setCellValue('E'.$row,"=SUM(E5:E10)");

$sheet3->setCellValue('F'.$row,"=SUM(F5:F10)");
$sheet3->setCellValue('G'.$row,"=SUM(G5:G10)");
$sheet3->setCellValue('H'.$row,"=SUM(H5:H10)");

//$sheet3->setCellValue('I'.$row,"=SUM(I5:I10)");
//$sheet3->setCellValue('J'.$row,"=SUM(J5:J10)");

$sheet3->getStyle('A3:H'.$row)->applyFromArray([
    'borders'=>[
        'allBorders'=>['borderStyle'=>Border::BORDER_THIN]
    ]
]);

foreach(range('A','J') as $c){
    $sheet3->getColumnDimension($c)->setAutoSize(true);
}

$filename="Laporan_Balita_".$bulan."_".$tahun.".xlsx";
$writer=new Xlsx($spreadsheet);

while(ob_get_level()) ob_end_clean();

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

$writer->save('php://output');
exit;