<?php

$array_pengguna_harian = array_fill(1, 31, 0);
$array_deposit_harian = array_fill(1, 31, 0);
$array_deposit_bulanan = array_fill(0, 12, 0);
$array_withdrawal_bulanan = array_fill(0, 12, 0);
$array_pengguna_bulanan = array_fill(0, 12, 0);

$query = "
    SELECT 
        type,
        MONTH(updated_at) AS month,  
        SUM(Amount) AS total 
    FROM 
        transaction 
    WHERE 
        status = 1 
        AND YEAR(updated_at) = YEAR(CURDATE())
    GROUP BY 
        type, MONTH(updated_at)
    ORDER BY 
        MONTH(updated_at);
";
$stmt = $db->prepare($query);
$stmt->execute();
$transaksi_bulanan_stat = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (!empty($transaksi_bulanan_stat)) {
    foreach ($transaksi_bulanan_stat as $data_transaksi) {
        $monthly = (int)$data_transaksi['month'] - 1;
        if ($data_transaksi['type'] === 'withdraw') {
            $array_withdrawal_bulanan[$monthly] = $data_transaksi['total'];
        } elseif ($data_transaksi['type'] === 'deposit') {
            $array_deposit_bulanan[$monthly] = $data_transaksi['total'];
        }
    }
}

$query = "
    SELECT 
        MONTH(created_at) AS month, 
        COUNT(*) AS total
    FROM 
        user
    WHERE 
        YEAR(created_at) = YEAR(CURDATE())
    GROUP BY 
        MONTH(created_at)
    ORDER BY 
        MONTH(created_at);
";
$stmt = $db->prepare($query);
$stmt->execute();
$pengguna_bulanan_stat = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (!empty($pengguna_bulanan_stat)) {
    foreach ($pengguna_bulanan_stat as $data_pengguna) {
        $monthly = (int)$data_pengguna['month'] - 1;
        $array_pengguna_bulanan[$monthly] = $data_pengguna['total'];
    }
}

$query = "
    SELECT 
        DAY(created_at) AS day, 
        COUNT(*) AS total
    FROM 
        user
    WHERE 
        MONTH(created_at) = MONTH(CURDATE()) 
        AND YEAR(created_at) = YEAR(CURDATE())
    GROUP BY 
        DAY(created_at)
    ORDER BY 
        DAY(created_at);
";
$stmt = $db->prepare($query);
$stmt->execute();
$pengguna_harian_stat = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (!empty($pengguna_harian_stat)) {
    foreach ($pengguna_harian_stat as $data_pengguna_harian) {
        $array_pengguna_harian[(int)$data_pengguna_harian['day']] = (int)$data_pengguna_harian['total'];
    }
}

$query = "
    SELECT 
        DAY(updated_at) AS day, 
        SUM(Amount) AS total
    FROM 
        transaction
    WHERE 
        type = 'deposit' 
        AND status = 1 
        AND MONTH(updated_at) = MONTH(CURDATE()) 
        AND YEAR(updated_at) = YEAR(CURDATE())
    GROUP BY 
        DAY(updated_at)
    ORDER BY 
        DAY(updated_at);
";
$stmt = $db->prepare($query);
$stmt->execute();
$deposit_harian_stat = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (!empty($deposit_harian_stat)) {
    foreach ($deposit_harian_stat as $data_deposit_harian) {
        $array_deposit_harian[(int)$data_deposit_harian['day']] = (int)$data_deposit_harian['total'];
    }
}

$output = [
    'monthly' => [
        'users' => $array_pengguna_bulanan,
        'deposits' => $array_deposit_bulanan,
        'withdrawals' => $array_withdrawal_bulanan,
    ],
    'daily' => [
        'users' => array_values($array_pengguna_harian),
        'deposits' => array_values($array_deposit_harian),
    ],
    'labels' => [
        'date' => $date,
        'months' => $months,
    ],
];

$query = "
    SELECT u.id, u.username, t.type, t.Amount, t.status 
    FROM user u
    LEFT JOIN transaction t ON u.id = t.user_id
    WHERE t.status = 0 
    AND t.id IN (
        SELECT MAX(id) 
        FROM transaction 
        WHERE status = 0 
        GROUP BY type
    )
    ORDER BY t.created_at DESC
";
$stmt = $db->prepare($query);
$stmt->execute();
$notifikasi_transaksi = $stmt->fetchAll(PDO::FETCH_ASSOC);
$notifikasi = [];
foreach ($notifikasi_transaksi as $notify) {
    $notifikasi[] = [
        'username' => $notify['username'],
        'type' => $notify['type'],
        'amount' => number_format($notify['Amount'], 0, ',', '.')
    ];
}

echo '<script>const notifications = ' . json_encode($notifikasi) . ';</script>';

$chartjson = json_encode($output);
