<?php

require_once "conn.php";

$stmt = $conn->prepare("SELECT id, id_pengaduan, nama, judul, isi, status, foto, created_at FROM pengaduan;");
$stmt->execute();
$stmt->bind_result($id, $id_pengaduan, $nama, $judul, $isi, $status, $foto, $tgl_upload);

$pengaduan = array();

while ($stmt->fetch()) {
    $temp = array();
    $temp['id'] = $id;
    $temp['id_pengaduan'] = $id_pengaduan;
    $temp['nama'] = $nama;
    $temp['judul'] = $judul;
    $temp['isi'] = $isi;
    $temp['status'] = $status;
    $temp['foto'] = $foto;
    $temp['tgl_upload'] = $tgl_upload;

    array_push($pengaduan, $temp);
}

$gabungan = array();

foreach ($pengaduan as $item) {
    if ($item['judul'] !== null) {
        if ($item['id'] !== null) {
            $id_pengaduan = $item['id'];
            $balasan = array_filter($pengaduan, function ($pengaduan) use ($id_pengaduan) {
                return $pengaduan['id_pengaduan'] === $id_pengaduan;
            });

            if (!empty($balasan)) {
                $id_balasan = array_column($balasan, 'id_pengaduan');
                $placeholders = implode(',', array_fill(0, count($id_balasan), '?'));
            
                
                $created_at_query = $conn->prepare("SELECT id_pengaduan, MAX(created_at) AS latest_created_at FROM pengaduan WHERE id_pengaduan IN ($placeholders) GROUP BY id_pengaduan");
                $created_at_query->bind_param(str_repeat('i', count($id_balasan)), ...$id_balasan);
                $created_at_query->execute();
                $created_at_result = $created_at_query->get_result();
            
                
                $created_at_data = array();
                while ($row = $created_at_result->fetch_assoc()) {
                    $created_at_data[$row['id_pengaduan']] = $row['latest_created_at'];
                }
            
                $balasan = array_values($balasan);
                $nomor = 1;
                $balasanDenganNomor = array();
                foreach ($balasan as $key => $itemBalasan) {
                    $balasanId = $nomor;
                    $balasanIsi = $itemBalasan['isi'];
                    $id_pengaduan = $itemBalasan['id_pengaduan'];
            
                    $created_at_balasan = isset($created_at_data[$id_pengaduan]) ? $created_at_data[$id_pengaduan] : 'Unknown';
            
                    $balasanDenganNomor[] = array(
                        'balasanId' => $balasanId,
                        'balasanIsi' => $balasanIsi,
                        'dibalas' => $created_at_balasan
                    ); 
            
                    $nomor++;
                }
            
                $item['balasan'] = $balasanDenganNomor;
            }
        }

        unset($item['id_pengaduan']);
        $gabungan[] = $item;
    }
}

echo json_encode($gabungan);
