<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Session;

class DashboardController extends Controller
{
    /**
     * Create a new controller instance.
     *
     * @return void
     */
    // public function __construct()
    // {
    //     $this->middleware('auth');
    // }

    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\View\View
     */
    public function index($data)
    {
        // data bmi
        // $data['table_detail'] = DB::table('v_bmi')->where(['nik' => Session('username'), 'status' => 'E'])->orderByDesc('tgl', 'created_at')->first();
        //check column where in table sys_dmenu
        $getdatawhere = DB::table('sys_dmenu')->select('where')->where('dmenu', $data['dmenu'])->first();
        if ($getdatawhere->where <> '') {
            // Cari semua placeholder :session('...')
            preg_match_all("/:session\('([^']+)'\)/", $getdatawhere->where, $vsession);
            foreach ($vsession[1] as $i => $key) {
                $value = session($key); // ambil dari session
                $getdatawhere->where = str_replace($vsession[0][$i], "'$value'", $getdatawhere->where);
            }
            //check athorization access rules
            $query = DB::table($data['tabel']);
            $sql = $query->toSql();
            if ($data['authorize']->rules == '1') {
                //masukkan query find in set
                $whereRules = implode(' OR ', array_map(function ($role) {
                    return "FIND_IN_SET('$role', REPLACE(rules, ' ', ''))";
                }, array_map('trim', explode(',', $data['user_login']->idroles))));
                //gabungkan query
                $sql = $sql . " " . $getdatawhere->where . "  AND ($whereRules)";
            } else {
                $sql .= ' ' . $getdatawhere->where;
            }
            // get data
            $data['table_detail'] = DB::select($sql);
        } else {
            //check athorization access rules
            if ($data['authorize']->rules == '1') {
                $roles = $data['users_rules'];
                $data['table_detail'] = DB::table($data['tabel'])
                    ->where(function ($q) use ($roles) {
                        foreach ($roles as $role) {
                            $q->orWhereRaw("FIND_IN_SET(?, REPLACE(rules, ' ', ''))", [$role]);
                        }
                    })
                    ->get();
            } else {
                $data['table_detail'] = DB::table($data['tabel'])->get();
            }
        }
        //data aktifitas
        $data['list_aktifitas'] = DB::table('trs_activity')
            ->selectRaw("DATE_FORMAT(created_at, '%M %Y') AS bulan_tahun")
            ->selectRaw("COUNT(id) AS total_aktivitas")
            ->selectRaw("SUM(jarak) AS total_jarak")
            ->selectRaw("SUM(waktu) AS total_waktu")
            ->selectRaw("SUM(kalori) AS total_kalori")
            ->where('user_create', session('username'))
            ->where('isactive', '1')
            ->groupBy(DB::raw("DATE_FORMAT(created_at, '%Y-%m')"))
            ->groupBy(DB::raw("DATE_FORMAT(created_at, '%M %Y')")) // biar MySQL ga error
            ->orderBy(DB::raw("DATE_FORMAT(created_at, '%Y-%m')"))
            ->get();
        $data['tot_star'] = DB::table('trs_activity')
            ->selectRaw("SUM(star) AS total_star")
            ->where('user_create', session('username'))
            ->where('isactive', '1')
            ->get()->all();
        // list peringkat
        $base = DB::table('trs_activity as a')
            ->leftJoin('users as b', 'b.username', '=', 'a.user_create')
            ->select('a.user_create', 'b.firstname', 'b.image')
            ->selectRaw('SUM(a.star) AS star, SUM(a.jarak) AS jarak')
	    ->where('a.isactive', '1')
            // lebih aman lintas tahun:
            ->whereRaw('YEARWEEK(a.created_at) = YEARWEEK(CURDATE())')
            ->groupBy('a.user_create', 'b.firstname', 'b.image');
        //orderby
        $data['byStar']  = (clone $base)->orderByDesc('star')->orderByDesc('jarak')->get();
        $data['byJarak'] = (clone $base)->orderByDesc('jarak')->orderByDesc('star')->get();
        return view($data['url'], $data);
    }
}