<?php
// laporan_standalone.php
// ISI MAKLUMAT DATABASE DI BAWAH SEBELUM JALANKAN
define('DB_HOST','localhost');
define('DB_USER','root');
define('DB_PASS','');      // <-- isi password DB
define('DB_NAME','db_espektra'); // <-- nama database

$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if ($conn->connect_error) {
    die("Sambungan gagal: " . $conn->connect_error);
}
$conn->set_charset('utf8mb4');

// 1) Statistik ringkas
$total_aktiviti = $conn->query("SELECT COUNT(*) AS cnt FROM aktiviti")->fetch_assoc()['cnt'] ?? 0;
$total_pelajar = $conn->query("SELECT COUNT(*) AS cnt FROM pelajar")->fetch_assoc()['cnt'] ?? 0;
$total_penyertaan = $conn->query("SELECT COUNT(*) AS cnt FROM penyertaan")->fetch_assoc()['cnt'] ?? 0;

// semak kewujudan table sijil
$total_sijil = 0;
$res = $conn->query("SHOW TABLES LIKE 'sijil'");
if ($res && $res->num_rows > 0) {
    $total_sijil = $conn->query("SELECT COUNT(*) AS cnt FROM sijil")->fetch_assoc()['cnt'] ?? 0;
}

// Aktiviti per bulan
$sql_aktiviti_bulan = "
    SELECT DATE_FORMAT(tarikh_mula, '%Y-%m') AS ym, COUNT(*) AS cnt
    FROM aktiviti
    GROUP BY ym
    ORDER BY ym
";
$data_aktiviti_bulan = [];
$res = $conn->query($sql_aktiviti_bulan);
if ($res) while ($r = $res->fetch_assoc()) $data_aktiviti_bulan[] = $r;

// Peringkat
$sql_peringkat = "SELECT peringkat, COUNT(*) AS cnt FROM aktiviti GROUP BY peringkat";
$data_peringkat = [];
$res = $conn->query($sql_peringkat);
if ($res) while ($r = $res->fetch_assoc()) $data_peringkat[] = $r;

// Penyertaan per bulan
$sql_penyertaan_bulan = "
    SELECT DATE_FORMAT(tarikh_daftar, '%Y-%m') AS ym, COUNT(*) AS cnt
    FROM penyertaan
    GROUP BY ym
    ORDER BY ym
";
$data_penyertaan_bulan = [];
$res = $conn->query($sql_penyertaan_bulan);
if ($res) while ($r = $res->fetch_assoc()) $data_penyertaan_bulan[] = $r;

// helper
function arrcol($a,$k){ $o=[]; foreach($a as $r) $o[]=$r[$k]; return $o; }

$labels_aktiviti = arrcol($data_aktiviti_bulan,'ym');
$values_aktiviti = arrcol($data_aktiviti_bulan,'cnt');
$labels_peringkat = arrcol($data_peringkat,'peringkat');
$values_peringkat = arrcol($data_peringkat,'cnt');
$labels_penyertaan = arrcol($data_penyertaan_bulan,'ym');
$values_penyertaan = arrcol($data_penyertaan_bulan,'cnt');

?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Laporan & Statistik - Standalone</title>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css">
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<style>
.container { padding:30px; font-family:'Rubik',sans-serif; }
.card-stats { border-radius:12px; box-shadow:0 6px 18px rgba(0,0,0,0.06); padding:20px; color:white; }
.card-charts { padding:20px; background:white; border-radius:12px; box-shadow:0 6px 18px rgba(0,0,0,0.06); }
</style>
</head>
<body>
<div class="container">
    <h3 style="color:#3b0a77; font-weight:700;">Laporan & Statistik (Standalone)</h3>

    <div class="row my-3">
        <div class="col-md-3 mb-3">
            <div class="card-stats bg-primary">
                <h6>Jumlah Aktiviti</h6><h3><?php echo (int)$total_aktiviti; ?></h3>
            </div>
        </div>
        <div class="col-md-3 mb-3">
            <div class="card-stats bg-success">
                <h6>Jumlah Pelajar</h6><h3><?php echo (int)$total_pelajar; ?></h3>
            </div>
        </div>
        <div class="col-md-3 mb-3">
            <div class="card-stats bg-info">
                <h6>Jumlah Penyertaan</h6><h3><?php echo (int)$total_penyertaan; ?></h3>
            </div>
        </div>
        <div class="col-md-3 mb-3">
            <div class="card-stats bg-warning">
                <h6>Jumlah Sijil</h6><h3><?php echo (int)$total_sijil; ?></h3>
            </div>
        </div>
    </div>

    <div class="row mb-4">
        <div class="col-lg-7 mb-3">
            <div class="card-charts">
                <h6 style="color:#3b0a77; font-weight:700;">Bilangan Aktiviti mengikut Bulan</h6>
                <canvas id="chartA"></canvas>
            </div>
        </div>
        <div class="col-lg-5 mb-3">
            <div class="card-charts">
                <h6 style="color:#3b0a77; font-weight:700;">Pecahan Peringkat Aktiviti</h6>
                <canvas id="chartB"></canvas>
            </div>
        </div>
    </div>

    <div class="card-charts">
        <h6 style="color:#3b0a77; font-weight:700;">Trend Penyertaan Pelajar (mengikut Bulan)</h6>
        <canvas id="chartC"></canvas>
    </div>
</div>

<script>
const labelsA = <?php echo json_encode($labels_aktiviti); ?>;
const dataA = <?php echo json_encode($values_aktiviti); ?>;
const labelsB = <?php echo json_encode($labels_peringkat); ?>;
const dataB = <?php echo json_encode($values_peringkat); ?>;
const labelsC = <?php echo json_encode($labels_penyertaan); ?>;
const dataC = <?php echo json_encode($values_penyertaan); ?>;

new Chart(document.getElementById('chartA'), {
    type: 'bar',
    data: { labels: labelsA, datasets: [{ label:'Bilangan Aktiviti', data: dataA, backgroundColor:'rgba(99,102,241,0.8)' }] },
    options: { responsive:true, scales:{ y:{ beginAtZero:true, precision:0 } } }
});
new Chart(document.getElementById('chartB'), {
    type: 'pie',
    data: { labels: labelsB, datasets: [{ data: dataB, backgroundColor:['#6f42c1','#fd7e14','#0d6efd','#198754','#d63384'] }] },
    options: { responsive:true }
});
new Chart(document.getElementById('chartC'), {
    type: 'line',
    data: { labels: labelsC, datasets: [{ label:'Bilangan Penyertaan', data: dataC, borderColor:'#20c997', tension:0.2, fill:false }] },
    options: { responsive:true, scales:{ y:{ beginAtZero:true, precision:0 } } }
});
</script>
</body>
</html>
