<?php
require __DIR__ . "/koneksi.php";
header('Content-Type: application/json');

$sql = "
SELECT
    s.id,
    s.nama_site,
    s.code_site,
    s.lat,
    s.lon,

    COUNT(DISTINCT c.id) AS total_pc,

    SUM(
        CASE 
            WHEN al.id IS NOT NULL
             AND al.status = 'active'
             AND al.alert_type = 'temperature'
            THEN 1 ELSE 0
        END
    ) AS active_alert

FROM sites s

LEFT JOIN pc_clients c
    ON s.code_site = SUBSTRING_INDEX(
        SUBSTRING_INDEX(c.ip_address, '.', 3),
    '.', -1)

LEFT JOIN alert_log al
    ON al.pc_id = c.id
    AND al.status = 'active'

GROUP BY s.id
ORDER BY s.nama_site
";

$res = $conn->query($sql);

$data = [];
while ($r = $res->fetch_assoc()) {
    $data[] = [
        "id"           => (int)$r['id'],
        "nama_site"    => $r['nama_site'],
        "code_site"    => $r['code_site'],
        "lat"          => (float)$r['lat'],
        "lon"          => (float)$r['lon'],
        "total_pc"     => (int)$r['total_pc'],
        "active_alert" => (int)$r['active_alert'],
        "status"       => $r['active_alert'] > 0 ? "alert" : "normal"
    ];
}

echo json_encode($data);
