<?php
include 'db.php';

$query = "SELECT LOWER(kj) as kj, COUNT(*) as count 
          FROM Report 
          WHERE LOWER(kj) IN ('kj1', 'kj2', 'kj3', 'kj4') 
          AND status != 'Selesai' 
          GROUP BY kj";

$result = $conn->query($query);

$kjCounts = ['kj1' => 0, 'kj2' => 0, 'kj3' => 0, 'kj4' => 0];

if ($result) {
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $kjCounts[$row['kj']] = $row['count'];
        }
    }
} else {
    die("Query failed: " . $conn->error);
}

// Hitung jumlah per fase
$faseCounts = [
    'Ditemukan' => 0,
    'Perlakuan' => 0,
    'Verifikasi' => 0,
    'Selesai' => 0
];

// Helper function
function is_valid_time($val) {
    return !empty($val) && $val !== '0000-00-00 00:00:00';
}

$faseQuery = "SELECT waktu_temuan, waktu_perlakuan, waktu_verifikasi, waktu_update_manual FROM Report";
$faseResult = $conn->query($faseQuery);
if ($faseResult) {
    while ($row = $faseResult->fetch_assoc()) {
        $w_temuan = $row['waktu_temuan'];
        $w_perlakuan = $row['waktu_perlakuan'];
        $w_verifikasi = $row['waktu_verifikasi'];
        $w_update = $row['waktu_update_manual'];
        if (is_valid_time($w_update)) {
            $faseCounts['Selesai']++;
        } elseif (is_valid_time($w_verifikasi)) {
            $faseCounts['Verifikasi']++;
        } elseif (is_valid_time($w_perlakuan)) {
            $faseCounts['Perlakuan']++;
        } elseif (is_valid_time($w_temuan)) {
            $faseCounts['Ditemukan']++;
        }
    }
}

// Rekap fase per KJ
$kjList = ['KJ1', 'KJ2', 'KJ3', 'KJ4'];
$rekapPerKJ = [];
foreach ($kjList as $kj) {
    $rekapPerKJ[$kj] = [
        'Ditemukan' => 0,
        'Perlakuan' => 0,
        'Verifikasi' => 0,
        'Selesai' => 0
    ];
}

$kjFaseQuery = "SELECT kj, waktu_temuan, waktu_perlakuan, waktu_verifikasi, waktu_update_manual FROM Report";
$kjFaseResult = $conn->query($kjFaseQuery);
if ($kjFaseResult) {
    while ($row = $kjFaseResult->fetch_assoc()) {
        $kj = strtoupper($row['kj']);
        if (!in_array($kj, $kjList)) continue;
        $w_temuan = $row['waktu_temuan'];
        $w_perlakuan = $row['waktu_perlakuan'];
        $w_verifikasi = $row['waktu_verifikasi'];
        $w_update = $row['waktu_update_manual'];
        if (is_valid_time($w_update)) {
            $rekapPerKJ[$kj]['Selesai']++;
        } elseif (is_valid_time($w_verifikasi)) {
            $rekapPerKJ[$kj]['Verifikasi']++;
        } elseif (is_valid_time($w_perlakuan)) {
            $rekapPerKJ[$kj]['Perlakuan']++;
        } elseif (is_valid_time($w_temuan)) {
            $rekapPerKJ[$kj]['Ditemukan']++;
        }
    }
}

$conn->close();
?>

<div class="content" id="dashboard">
    <div class="d-sm-flex align-items-center justify-content-between mb-4">
    <h1>Dashboard</h1>
    </div>

    <div class="d-sm-flex align-items-center justify-content-between mt-4 mb-2">
    <h4>Monitoring Jumlah Kubangan</h4>
    </div>
    
    <!-- Content Row -->
    <div class="row">
    <!-- Area Chart -->
    <div class="col-xl-3">
        <div class="card shadow mb-4" id="kj-1">
        <!-- Card Header - Dropdown -->
        <div
            class="card-header py-3 d-flex flex-row align-items-center justify-content-between">
            <h6 class="m-0 font-weight-bold text-primary">KJ1</h6>
            <a href="?active=kerusakan&search=id-kj1" class="btn btn-primary">Lihat Detail</a>
        </div>
        <!-- Card Body -->
        <div class="card-body">
            <h1><?php echo $kjCounts['kj1']-$rekapPerKJ['KJ1']['Selesai']; ?></h1>
        </div>
        </div>
    </div>
    <!-- Area Chart -->
    <div class="col-xl-3">
        <div class="card shadow mb-4" id="kj-2">
        <!-- Card Header - Dropdown -->
        <div
            class="card-header py-3 d-flex flex-row align-items-center justify-content-between">
            <h6 class="m-0 font-weight-bold text-primary">KJ2</h6>
            <a href="?active=kerusakan&search=id-kj2" class="btn btn-primary">Lihat Detail</a>
        </div>
        <!-- Card Body -->
        <div class="card-body">
            <h1><?php echo $kjCounts['kj2']-$rekapPerKJ['KJ2']['Selesai']; ?></h1>
        </div>
        </div>
    </div>
    <!-- Area Chart -->
    <div class="col-xl-3">
        <div class="card shadow mb-4" id="kj-3">
        <!-- Card Header - Dropdown -->
        <div
            class="card-header py-3 d-flex flex-row align-items-center justify-content-between">
            <h6 class="m-0 font-weight-bold text-primary">KJ3</h6>
            <a href="?active=kerusakan&search=id-kj3" class="btn btn-primary">Lihat Detail</a>
        </div>
        <!-- Card Body -->
        <div class="card-body">
            <h1><?php echo $kjCounts['kj3']-$rekapPerKJ['KJ3']['Selesai']; ?></h1>
        </div>
        </div>
    </div>
    <!-- Area Chart -->
    <div class="col-xl-3">
        <div class="card shadow mb-4" id="kj-4">
        <!-- Card Header - Dropdown -->
        <div
            class="card-header py-3 d-flex flex-row align-items-center justify-content-between">
            <h6 class="m-0 font-weight-bold text-primary">KJ4</h6>
            <a href="?active=kerusakan&search=id-kj4" class="btn btn-primary">Lihat Detail</a>
        </div>
        <!-- Card Body -->
        <div class="card-body">
            <h1><?php echo $kjCounts['kj4']-$rekapPerKJ['KJ4']['Selesai']; ?></h1>
        </div>
        </div>
    </div>
    </div>
</div>