<?php

/**
 *  PHP Mikrotik Billing (https://github.com/hotspotbilling/phpnuxbill/)
 *  by https://t.me/ibnux
 **/

_admin();
$ui->assign('_title', Lang::T('Dashboard'));
$ui->assign('_admin', $admin);

if(isset($_GET['refresh'])){
    $files = scandir($CACHE_PATH);
    foreach ($files as $file) {
        $ext = pathinfo($file, PATHINFO_EXTENSION);
        if (is_file($CACHE_PATH . DIRECTORY_SEPARATOR . $file) && $ext == 'temp') {
            unlink($CACHE_PATH . DIRECTORY_SEPARATOR . $file);
        }
    }
    r2(U . 'dashboard', 's', 'Data Refreshed');
}

$fdate = date('Y-m-01');
$tdate = date('Y-m-t');
//first day of month
$first_day_month = date('Y-m-01');
$mdate = date('Y-m-d');
$month_n = date('n');


$iday = ORM::for_table('tbl_transactions')
    ->where('recharged_on', $mdate)
    ->where_not_equal('method', 'Customer - Balance')
    ->where_not_equal('method', 'Recharge Balance - Administrator')
    ->sum('price');

if ($iday == '') {
    $iday = '0.00';
}
$ui->assign('iday', $iday);


// Gather daily sales for the current day and 6 previous days
$dailySales = [];
for ($i = 6; $i >= 0; $i--) {
    $date = date('Y-m-d', strtotime("-$i days"));
    $sale = ORM::for_table('tbl_transactions')
        ->where('recharged_on', $date)
        ->where_not_equal('method', 'Customer - Balance')
        ->where_not_equal('method', 'Recharge Balance - Administrator')
        ->sum('price');
    $dailySales[] = [
        'date' => $date,
        'amount' => $sale ?: 0
    ];
}
$ui->assign('dailySales', json_encode($dailySales));

// Monthly income
$imonth = ORM::for_table('tbl_transactions')
    ->where_not_equal('method', 'Customer - Balance')
    ->where_not_equal('method', 'Recharge Balance - Administrator')
    ->where_gte('recharged_on', $first_day_month)
    ->where_lte('recharged_on', $mdate)
    ->sum('price');

if ($imonth == '') {
    $imonth = '0.00';
}
$ui->assign('imonth', $imonth);

$endDate = new DateTime(); // Today
$startDate = new DateTime();
$startDate->modify('-6 days'); // 7 days ago

// Generate an array with all dates in the range with zero counts
$datesRange = [];
$currentDate = clone $startDate;
while ($currentDate <= $endDate) {
    $datesRange[$currentDate->format('Y-m-d')] = 0;
    $currentDate->modify('+1 day');
}

// Fetch data from the database
$activeUsersData = ORM::for_table('tbl_user_recharges')
    ->select_expr('DATE(recharged_on)', 'recharged_on')
    ->select_expr('COUNT(*)', 'count')
    ->where_gte('recharged_on', $startDate->format('Y-m-d'))
    ->where_lte('recharged_on', $endDate->format('Y-m-d'))
    ->group_by('recharged_on')
    ->order_by_asc('recharged_on')
    ->find_array();

// Fill the dates range with actual data
foreach ($activeUsersData as $data) {
    $datesRange[$data['recharged_on']] = $data['count'];
}

// Convert to an array of objects for JSON encoding
$activeUsersDataFormatted = [];
foreach ($datesRange as $date => $count) {
    $activeUsersDataFormatted[] = ['recharged_on' => $date, 'count' => $count];
}

$ui->assign('activeUsersData', json_encode($activeUsersDataFormatted));


// Active user recharges
$u_act = ORM::for_table('tbl_user_recharges')->where('status', 'on')->count();
if (empty($u_act)) {
    $u_act = '0';
}
$ui->assign('u_act', $u_act);

// Total user recharges
$u_all = ORM::for_table('tbl_user_recharges')->count();
if (empty($u_all)) {
    $u_all = '0';
}
$ui->assign('u_all', $u_all);

// Total customers
$c_all = ORM::for_table('tbl_customers')->count();
if (empty($c_all)) {
    $c_all = '0';
}
$ui->assign('c_all', $c_all);

// Fetch available plans for Hotspot from tbl_plans
$available_hotspot_plans = ORM::for_table('tbl_plans')
    ->where('type', 'Hotspot')
    ->select('name_plan')
    ->find_many();

$available_hotspot_plans_list = [];
foreach ($available_hotspot_plans as $plan) {
    $available_hotspot_plans_list[$plan->name_plan] = 0; // Initialize with 0 counts
}

// Fetch package utilization count for Hotspot
$package_utilization_hotspot = ORM::for_table('tbl_transactions')
    ->where('type', 'Hotspot')
    ->select('plan_name')
    ->select_expr('COUNT(*)', 'count')
    ->group_by('plan_name')
    ->find_many();

foreach ($package_utilization_hotspot as $package) {
    if (array_key_exists($package->plan_name, $available_hotspot_plans_list)) {
        $available_hotspot_plans_list[$package->plan_name] = $package->count;
    }
}
$hotspot_data = $available_hotspot_plans_list;
$ui->assign('hotspot_utilization', $hotspot_data);

// Fetch available plans for PPPOE from tbl_plans
$available_pppoe_plans = ORM::for_table('tbl_plans')
    ->where('type', 'PPPOE')
    ->select('name_plan')
    ->find_many();

$available_pppoe_plans_list = [];
foreach ($available_pppoe_plans as $plan) {
    $available_pppoe_plans_list[$plan->name_plan] = 0; // Initialize with 0 counts
}

// Fetch package utilization count for PPPOE
$package_utilization_pppoe = ORM::for_table('tbl_transactions')
    ->where('type', 'PPPOE')
    ->select('plan_name')
    ->select_expr('COUNT(*)', 'count')
    ->group_by('plan_name')
    ->find_many();

foreach ($package_utilization_pppoe as $package) {
    if (array_key_exists($package->plan_name, $available_pppoe_plans_list)) {
        $available_pppoe_plans_list[$package->plan_name] = $package->count;
    }
}
$pppoe_data = $available_pppoe_plans_list;
$ui->assign('pppoe_utilization', $pppoe_data);


// Fetch the latest 5 transactions
$latestTransactions = ORM::for_table('tbl_transactions')
    ->select('username')
    ->select('price')
    ->select('recharged_on')
    ->select('recharged_time')
	->select('type')
    ->select('method')
    ->order_by_desc('id')  // Assuming 'id' is the primary key and auto-incrementing
    ->limit(5)
    ->find_array();

$ui->assign('latestTransactions', $latestTransactions);
if ($config['hide_uet'] != 'yes') {
    //user expire
    $query = ORM::for_table('tbl_user_recharges')
        ->where_lte('expiration', $mdate)
        ->order_by_desc('expiration');
    $expire = Paginator::findMany($query);

    // Get the total count of expired records for pagination
    $totalCount = ORM::for_table('tbl_user_recharges')
        ->where_lte('expiration', $mdate)
        ->count();

    // Pass the total count and current page to the paginator
    $paginator['total_count'] = $totalCount;

    // Assign the pagination HTML to the template variable
    $ui->assign('expire', $expire);
}

//activity log
$dlog = ORM::for_table('tbl_logs')->limit(5)->order_by_desc('id')->find_many();
$ui->assign('dlog', $dlog);
$log = ORM::for_table('tbl_logs')->count();
$ui->assign('log', $log);


if ($config['hide_vs'] != 'yes') {
    $cacheStocksfile = $CACHE_PATH . File::pathFixer('/VoucherStocks.temp');
    $cachePlanfile = $CACHE_PATH . File::pathFixer('/VoucherPlans.temp');
    //Cache for 5 minutes
    if (file_exists($cacheStocksfile) && time() - filemtime($cacheStocksfile) < 600) {
        $stocks = json_decode(file_get_contents($cacheStocksfile), true);
        $plans = json_decode(file_get_contents($cachePlanfile), true);
    } else {
        // Count stock
        $tmp = $v = ORM::for_table('tbl_plans')->select('id')->select('name_plan')->find_many();
        $plans = array();
        $stocks = array("used" => 0, "unused" => 0);
        $n = 0;
        foreach ($tmp as $plan) {
            $unused = ORM::for_table('tbl_voucher')
                ->where('id_plan', $plan['id'])
                ->where('status', 0)->count();
            $used = ORM::for_table('tbl_voucher')
                ->where('id_plan', $plan['id'])
                ->where('status', 1)->count();
            if ($unused > 0 || $used > 0) {
                $plans[$n]['name_plan'] = $plan['name_plan'];
                $plans[$n]['unused'] = $unused;
                $plans[$n]['used'] = $used;
                $stocks["unused"] += $unused;
                $stocks["used"] += $used;
                $n++;
            }
        }
        file_put_contents($cacheStocksfile, json_encode($stocks));
        file_put_contents($cachePlanfile, json_encode($plans));
    }
}

$cacheMRfile = File::pathFixer('/monthlyRegistered.temp');
//Cache for 1 hour
if (file_exists($cacheMRfile) && time() - filemtime($cacheMRfile) < 3600) {
    $monthlyRegistered = json_decode(file_get_contents($cacheMRfile), true);
} else {
    //Monthly Registered Customers
    $result = ORM::for_table('tbl_customers')
        ->select_expr('MONTH(created_at)', 'month')
        ->select_expr('COUNT(*)', 'count')
        ->where_raw('YEAR(created_at) = YEAR(NOW())')
        ->group_by_expr('MONTH(created_at)')
        ->find_many();

    $monthlyRegistered = [];
    foreach ($result as $row) {
        $monthlyRegistered[] = [
            'date' => $row->month,
            'count' => $row->count
        ];
    }
    file_put_contents($cacheMRfile, json_encode($monthlyRegistered));
}

$cacheMSfile = $CACHE_PATH . File::pathFixer('/monthlySales.temp');
//Cache for 12 hours
if (file_exists($cacheMSfile) && time() - filemtime($cacheMSfile) < 43200) {
    $monthlySales = json_decode(file_get_contents($cacheMSfile), true);
} else {
    // Query to retrieve monthly data
    $results = ORM::for_table('tbl_transactions')
        ->select_expr('MONTH(recharged_on)', 'month')
        ->select_expr('SUM(price)', 'total')
        ->where_raw("YEAR(recharged_on) = YEAR(CURRENT_DATE())") // Filter by the current year
        ->where_not_equal('method', 'Customer - Balance')
        ->where_not_equal('method', 'Recharge Balance - Administrator')
        ->group_by_expr('MONTH(recharged_on)')
        ->find_many();

    // Create an array to hold the monthly sales data
    $monthlySales = array();

    // Iterate over the results and populate the array
    foreach ($results as $result) {
        $month = $result->month;
        $totalSales = $result->total;

        $monthlySales[$month] = array(
            'month' => $month,
            'totalSales' => $totalSales
        );
    }

    // Fill in missing months with zero sales
    for ($month = 1; $month <= 12; $month++) {
        if (!isset($monthlySales[$month])) {
            $monthlySales[$month] = array(
                'month' => $month,
                'totalSales' => 0
            );
        }
    }

    // Sort the array by month
    ksort($monthlySales);

    // Reindex the array
    $monthlySales = array_values($monthlySales);
    file_put_contents($cacheMSfile, json_encode($monthlySales));
}

// Assign the monthly sales data to Smarty
$ui->assign('monthlySales', $monthlySales);
$ui->assign('xfooter', '');
$ui->assign('monthlyRegistered', $monthlyRegistered);
$ui->assign('stocks', $stocks);
$ui->assign('plans', $plans);

run_hook('view_dashboard'); #HOOK
$ui->display('dashboard.tpl');
