<?php

namespace App\Http\Controllers\Backend;

use App\Http\Controllers\Controller;
use App\Models\Organization;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\ValidationException;
use Illuminate\View\View;
use Spatie\Permission\Models\Role;

class DashboardController extends Controller
{
    /**
     * Dashboard shows some statistical and overview data for this system.
     * Those data firstly generate by user access and then some statistical data comes from some mode.
     */
    public function index()
    {
        // $users_count = User::get()->count();
        $year = date('Y');

        if (Auth::user()->isSuperAdmin()) {
            // $organization = Organization::with('users')->whereHas('users', function($query){$query->where('id', Auth::id());})->get();

            $total_employees = DB::table('employees')->count();
            $active_employees = DB::table('employees')->where('status', 1)->count();

            $total_holidays = DB::table('holiday_calendar as ac')->leftJoin('holiday_calendar_details as acd', 'acd.hd_id', '=', 'ac.hd_id')
            ->where('acd.holiday','LIKE',"$year-%")->where('ac.approval_status', 2)->count();
            $total_leaves = DB::table('emp_leave_apps as ela')->join('emp_leave_apps_details as elad', 'elad.ela_id', '=', 'ela.ela_id')
            ->where('ela.leave_start','LIKE',"$year-%")->where('ela.lStatus', 2)->count();

            // DB::enableQueryLog();
            $todays_attendance_status = $this->staff_attendance($session_id = 9, $date = date('Y-m-d'), $emp_type = '0', $org_id = '0');
            // dd(DB::getQueryLog()); exit; return $todays_attendance_status; exit;

        } else {
            $user_id = auth()->id();
            $org_ids = DB::table('organization_user')->selectRaw('GROUP_CONCAT(organization_id) as org_ids')->where('user_id', $user_id)->first()->org_ids;

            $total_employees = DB::table('employees')->whereIn('branch_id', explode(",", $org_ids))->count();
            $active_employees = DB::table('employees')->whereIn('branch_id', explode(",", $org_ids))->where('status', 1)->count();

            $total_holidays = DB::table('holiday_calendar as ac')->leftJoin('holiday_calendar_details as acd', 'acd.hd_id', '=', 'ac.hd_id')
            ->where('acd.holiday','LIKE',"$year-%")->where('ac.approval_status', 2)->count();
            $total_leaves = DB::table('emp_leave_apps as ela')->join('emp_leave_apps_details as elad', 'elad.ela_id', '=', 'ela.ela_id')
            ->where('ela.leave_start','LIKE',"$year-%")->where('ela.lStatus', 2)->count();

            $todays_attendance_status = $this->staff_attendance($session_id = 9, $date = date('Y-m-d'), $emp_type = '0', $org_id = '0');

        }
        return view('backend.dashboard',compact('total_employees', 'active_employees', 'total_holidays', 'total_leaves', 'todays_attendance_status'));//'users_count', 'roles_count', 'farms'
    }

	function staff_attendance($session_id, $date, $emp_type, $org_id) {
        $sort = isset($_POST['sort']) ? strval($_POST['sort']) : '';
        $order = isset($_POST['order']) ? strval($_POST['order']) : 'desc';

        if (!empty($sort)) {
            $orderVy = "ORDER BY";
            $sortArr = explode(',', $sort);
            $orderArr = explode(',', $order);
            $j = 1;
            foreach ($sortArr as $i => $v) {
                if ($j == 1) {
                    $orderVy .= " $sortArr[$i] $orderArr[$i]";
                } else {
                    $orderVy .= ", $sortArr[$i] $orderArr[$i]";
                }
                $j++;
            }
        } else {
            $orderVy = " ORDER BY att_date DESC  ";
        }
        $sql = '';
        // $sql .= ($session_id) ? " AND sa.session_id = $session_id" : "";
        $sql .= ($emp_type) ? " AND ei.emp_type = '$emp_type'" : "";
        $sql .= ($org_id) ? " AND ei.branch_id = '$org_id'" : "";


        if (!Auth::user()->isSuperAdmin()) {
            $user_id = auth()->id();
            $org_ids = DB::table('organization_user')->selectRaw('GROUP_CONCAT(organization_id) as org_ids')->where('user_id', $user_id)->first()->org_ids;

            $sql .= ($org_ids) ? " AND ei.branch_id IN ($org_ids)" : "";
        }

		$data = DB::select("SELECT a.*, CASE WHEN in_time=outtime THEN '' ELSE outtime END out_time

		FROM (
            SELECT sa.*, dg.name AS designation_name,
                DATE_FORMAT(MIN(att_date), '%H:%i') as `in_time`,
                DATE_FORMAT(MAX(att_date), '%H:%i') as outtime,
                ei.employee_name, ei.employee_no, ei.duty_shift, ei.id
            FROM employees ei
                JOIN designations dg ON dg.id = ei.designation_id
                LEFT JOIN staff_attendance sa ON sa.reg_id = ei.employee_no
            WHERE 1 $sql AND sa.att_date LIKE '$date%' AND sa.att_status = 1
            GROUP BY ei.id
        ) a
        $orderVy");

        return $data;
    }
}
