<?php
// ===============================================================
// dashboard_data.php FINAL
// Lokasi: pages/web_dashboard/php/dashboard_data.php
// Sudah sinkron dengan index.php + main.js
// ===============================================================

date_default_timezone_set('Asia/Jakarta');

require_once __DIR__ . '/../../../app/config/koneksi.php';
require_once __DIR__ . '/../../../app/helpers/helpers.php';

/* ===============================================================
   Helper cek tabel
=============================================================== */
function tableExists($conn, $table) {
    $t = $conn->real_escape_string($table);
    $q = $conn->query("SHOW TABLES LIKE '$t'");
    return ($q && $q->num_rows > 0);
}

$today = date('Y-m-d');

/* ===============================================================
   1) JAMA'AH HARI INI
=============================================================== */
$jamaah_today = $jamaah_pria = $jamaah_wanita = 0;

if (tableExists($koneksi, 'jamaah')) {
    $sql = "
        SELECT 
               COALESCE(subuh+dzuhur+ashar+maghrib+isya+diluar_waktu_sholat,0) AS total
        FROM jamaah
        WHERE tanggal=?
        LIMIT 1
    ";
    $stmt = $koneksi->prepare($sql);
    $stmt->bind_param('s', $today);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    if ($row) {
        $jamaah_pria   = (int)$row['pria'];
        $jamaah_wanita = (int)$row['wanita'];
        $jamaah_today  = (int)$row['total'];
    }
}

/* ===============================================================
   2) TREND 7 HARI — JAMA'AH
=============================================================== */
$labels_jamaah = [];
$data_jamaah   = [];

for ($i = 6; $i >= 0; $i--) {
    $d = date('Y-m-d', strtotime("-$i days"));
    $labels_jamaah[] = date('d M', strtotime($d));
    $data_jamaah[$d] = 0;
}

if (tableExists($koneksi, 'jamaah')) {
    $start = date('Y-m-d', strtotime('-6 days'));
    $sql = "
        SELECT tanggal,
               COALESCE(subuh+dzuhur+ashar+maghrib+isya+diluar_waktu_sholat,0) AS total
        FROM jamaah
        WHERE tanggal BETWEEN ? AND ?
        ORDER BY tanggal ASC
    ";
    $stmt = $koneksi->prepare($sql);
    $stmt->bind_param('ss', $start, $today);
    $stmt->execute();
    $res = $stmt->get_result();

    while ($r = $res->fetch_assoc()) {
        $t = $r['tanggal'];
        if (isset($data_jamaah[$t])) {
            $data_jamaah[$t] = (int)$r['total'];
        }
    }
    $stmt->close();
}

$trend_jamaah_values = array_values($data_jamaah);

/* ===============================================================
   3) Pemasukan Keuangan
=============================================================== */
$pemasukan_today = $pemasukan_month = 0;

if (tableExists($koneksi, 'keuangan')) {
    // Hari ini
    $sql = "SELECT COALESCE(SUM(pemasukan),0) AS total FROM keuangan WHERE DATE(tanggal)=?";
    $stmt = $koneksi->prepare($sql);
    $stmt->bind_param('s', $today);
    $stmt->execute();
    $pemasukan_today = (float)$stmt->get_result()->fetch_assoc()['total'];
    $stmt->close();

    // Bulan ini
    $m = date('m');
    $y = date('Y');
    $sql = "SELECT COALESCE(SUM(pemasukan),0) AS total FROM keuangan WHERE MONTH(tanggal)=? AND YEAR(tanggal)=?";
    $stmt = $koneksi->prepare($sql);
    $stmt->bind_param('ii', $m, $y);
    $stmt->execute();
    $pemasukan_month = (float)$stmt->get_result()->fetch_assoc()['total'];
    $stmt->close();
}

/* ===============================================================
   4) AIR — Dari log_air (database baru)
=============================================================== */

$labels_air = [];
$data_air_usage  = [];   // Keluar
$data_air_income = [];   // Masuk

for ($i = 6; $i >= 0; $i--) {
    $d = date('Y-m-d', strtotime("-$i days"));
    $labels_air[] = date('d M', strtotime($d));
    $data_air_usage[$d]  = 0;
    $data_air_income[$d] = 0;
}

$today = date('Y-m-d');
$air_today_usage  = 0;
$air_today_income = 0;

if (tableExists($koneksi, 'log_air')) {

    // --- Ambil 7 hari data air ---
    $sql = "
        SELECT tanggal, status_air, SUM(update_air) AS total 
        FROM log_air
        WHERE tanggal >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
        GROUP BY tanggal, status_air
        ORDER BY tanggal ASC
    ";
    $res = $koneksi->query($sql);

    if ($res) {
        while ($row = $res->fetch_assoc()) {
            $tgl = $row['tanggal'];
            $status = strtolower($row['status_air']); // 'Masuk' / 'Keluar'
            $total = (float)$row['total'];

            if ($status === 'keluar') {
                $data_air_usage[$tgl] = $total;
            } elseif ($status === 'masuk') {
                $data_air_income[$tgl] = $total;
            }
        }
    }

    // --- Hitung penggunaan & pemasukan air hari ini ---
    $sql_today = "
        SELECT status_air, SUM(update_air) AS total 
        FROM log_air
        WHERE tanggal = '$today'
        GROUP BY status_air
    ";
    $res2 = $koneksi->query($sql_today);

    if ($res2) {
        while ($r = $res2->fetch_assoc()) {
            $status = strtolower($r['status_air']);
            $total  = (float)$r['total'];

            if ($status === 'keluar') {
                $air_today_usage = $total;
            } elseif ($status === 'masuk') {
                $air_today_income = $total;
            }
        }
    }
}

// Array untuk chart
$trend_air_usage  = array_values($data_air_usage);
$trend_air_income = array_values($data_air_income);

/* ===============================================================
   5) WAKTU SHOLAT
=============================================================== */
$prayer_times = [
    'subuh'   => '04:30',
    'dzuhur'  => '12:00',
    'ashar'   => '15:00',
    'maghrib' => '18:00',
    'isya'    => '19:10'
];

if (tableExists($koneksi, 'jadwal_sholat')) {
    $sql = "SELECT subuh,dzuhur,ashar,maghrib,isya FROM jadwal_sholat WHERE tanggal=? LIMIT 1";
    $stmt = $koneksi->prepare($sql);
    $stmt->bind_param('s', $today);
    $stmt->execute();
    $res = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    if ($res) {
        foreach ($res as $k => $v) {
            if (!empty($v)) {
                $prayer_times[$k] = $v;
            }
        }
    }
}

/* Tentukan Sholat Berikutnya */
$now  = new DateTime();
$nextPrayer = $nextPrayerTime = null;

foreach (['subuh','dzuhur','ashar','maghrib','isya'] as $p) {
    $t = DateTime::createFromFormat('Y-m-d H:i', "$today {$prayer_times[$p]}");
    if ($t > $now) {
        $nextPrayer = ucfirst($p);
        $nextPrayerTime = $t->format('Y-m-d H:i:s');
        break;
    }
}

if (!$nextPrayer) {
    $tom = date('Y-m-d', strtotime('+1 day'));
    $nextPrayer = "Subuh";
    $nextPrayerTime = "$tom {$prayer_times['subuh']}:00";
}

/* ===============================================================
   6) SUHU & KELEMBAPAN
=============================================================== */
$suhu = $kelembapan = null;

if (tableExists($koneksi, 'sumberdaya')) {
    $sql = "SELECT suhu, kelembapan FROM sumberdaya ORDER BY id DESC LIMIT 1";
    $res = $koneksi->query($sql);
    if ($res && $row = $res->fetch_assoc()) {
        $suhu        = isset($row['suhu']) ? (float)$row['suhu'] : null;
        $kelembapan  = isset($row['kelembapan']) ? (float)$row['kelembapan'] : null;
    }
}

/* ===============================================================
   7) DONUT DATA — Jamaah 1 bulan
=============================================================== */
$monthStart = date('Y-m-01');

$donut_subuh = $donut_dzuhur = $donut_ashar = 0;
$donut_maghrib = $donut_isya = $donut_diluar = 0;

if (tableExists($koneksi, 'jamaah')) {
    $sql = "
        SELECT 
            COALESCE(SUM(subuh),0) AS subuh,
            COALESCE(SUM(dzuhur),0) AS dzuhur,
            COALESCE(SUM(ashar),0) AS ashar,
            COALESCE(SUM(maghrib),0) AS maghrib,
            COALESCE(SUM(isya),0) AS isya,
            COALESCE(SUM(diluar_waktu_sholat),0) AS diluar
        FROM jamaah
        WHERE tanggal BETWEEN ? AND ?
    ";
    $stmt = $koneksi->prepare($sql);
    $stmt->bind_param('ss', $monthStart, $today);
    $stmt->execute();
    $res = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    if ($res) {
        $donut_subuh   = (int)$res['subuh'];
        $donut_dzuhur  = (int)$res['dzuhur'];
        $donut_ashar   = (int)$res['ashar'];
        $donut_maghrib = (int)$res['maghrib'];
        $donut_isya    = (int)$res['isya'];
        $donut_diluar  = (int)$res['diluar'];
    }
}

/* Jika semua 0, chart tetap muncul */
if (
    $donut_subuh == 0 && $donut_dzuhur == 0 && $donut_ashar == 0 &&
    $donut_maghrib == 0 && $donut_isya == 0 && $donut_diluar == 0
) {
    $donut_subuh = 1;
}

?>
