<?php

namespace App\Repositories;

use App\User;
use App\Models\Asset;
use DB;
use Illuminate\Database\Eloquent\Collection;
use App\Models\AssetStatus;

class Employee
{
    public function getEmployeeAssets(int $userId)
    {
        $query = Asset::with('assetType', 'makeAndModel', 'technicalSpec', 'assetStatus')->where('user_id', $userId)->orderBy('asset_type_id', 'asc');

        return $this->hasUserStatus($query);
    }
    
    public function getEmployeeAssetsCountByType(int $userId)
    {
        $query = Asset::select('assets.user_id', DB::raw('count("assets.user_id") as units'),'assets.asset_type_id','assets.created_at')
                        ->with('assetType')
                        ->where('user_id', $userId)
                        ->groupBy('asset_type_id');

        return $this->hasUserStatus($query);
    }

    public function getEmployeeAssetsCountByAge(int $userId)
    {
        $query = Asset::select('assets.user_id', DB::raw('count("assets.user_id") as units'),DB::raw("DATE_FORMAT(created_at, '%Y') new_date"))
                        ->where('user_id', $userId)
                        ->groupBy('new_date');

        return $this->hasUserStatus($query);
    }

    public function getManagerActiveTeam(int $userId)
    {
        return $this->getManagerTeam($userId)->active()->orderBy('employee_type_id', 'desc');
    }

    public function getManagerInActiveTeam(int $userId)
    {
        return $this->getManagerTeam($userId)->InActive()->orderBy('employee_type_id', 'desc');
    }

    public function getManagerTeam(int $userId)
    {
        return User::with('employeeType', 'assets')->withCount(['assets' => function ($query) {
            $this->hasUserStatus($query);
        }])->where('manager_id', $userId);
    }

    public function getEmployeeAssetsByJoin(int $userId)
    {
        $assets = Asset::join('users', 'assets.user_id', '=', 'users.id');

        $assets = $this->joinAssetDetailTables($assets)->where('users.id', $userId);
            
        return $this->hasUserStatus($assets);
    }

    public function exportActiveTeam(Collection $employees)
    {
        $userData = [];
        foreach ($employees as $key => $employee) {
            $userData[] = $this->getActiveTeamData($employee);
        }
        $csvHeader = ['First Name', 'Last Name', 'Start Date', 'Legal Hold', 'Email', 'Employee Type', 'Number of Assets'];

        return $this->generateCsv($userData, $csvHeader);
    }

    public function exportInActiveTeam(Collection $employees)
    {
        $userData = [];
        foreach ($employees as $key => $employee) {
            $userData[] = $this->getInActiveTeamData($employee);
        }
        $csvHeader = ['First Name', 'Last Name', 'Start Date', 'Legal Hold', 'End Date', 'Email', 'Employee Type', 'Number of Assets'];

        return $this->generateCsv($userData, $csvHeader);
    }

    public function getActiveTeamData(User $user)
    {
        return [
            'first_name' => disableCSVInjection($user->first_name),
            'last_name' => disableCSVInjection($user->last_name),
            'start_date' => disableCSVInjection($user->hire_date),
            'legal_hold' => $user->legalhold == 1 ? 'Yes' : 'No',
            'email' => disableCSVInjection($user->email),
            'employee_type' => disableCSVInjection($user->employeeType ? $user->employeeType->name : ''),
            'number_of_assets' => $user->assets_count,
        ];
    }

    public function getInActiveTeamData(User $user)
    {
        return [
            'first_name' => disableCSVInjection($user->first_name),
            'last_name' => disableCSVInjection($user->last_name),
            'start_date' => disableCSVInjection($user->hire_date),
            'legal_hold' => $user->legalhold == 1 ? 'Yes' : 'No',
            'terminated_date' => disableCSVInjection($user->terminated_date),
            'email' => disableCSVInjection($user->email),
            'employee_type' => disableCSVInjection($user->employeeType ? $user->employeeType->name : ''),
            'number_of_assets' => $user->assets_count,
        ];
    }

    public function generateCsv(array $datas, array $csvHeader)
    {
        $headers = [
            "Content-type" => "text/csv",
            "Content-Disposition" => "attachment; filename=my_team_assets.csv",
            "Pragma" => "no-cache",
            "Cache-Control" => "must-revalidate, post-check=0, pre-check=0",
            "Expires" => "0"
        ];

        $callback = function () use ($datas, $csvHeader) {
            $file = fopen('php://output', 'w');
            fputcsv($file, $csvHeader);

            foreach ($datas as $data) {
                fputcsv($file, $data);
            }
            fclose($file);
        };
        return response()->stream($callback, 200, $headers);
    }

    public function getManagersHavingTerminatedTeamAssets($date)
    {
        return User::join('users as managers', 'managers.id', '=', 'users.manager_id')
            ->join('assets', 'assets.user_id', '=', 'users.id')
            ->select('managers.*')
            ->groupBy('managers.id')
            ->where('users.terminated_date', $date);
    }

    public function getTerminatedTeamAssets($userId, $date)
    {
        $assets = Asset::join('users', 'assets.user_id', '=', 'users.id')
            ->join('users as managers', 'managers.id', '=', 'users.manager_id');

        $assets = $this->joinAssetDetailTables($assets)->where('users.manager_id', $userId)->where('users.terminated_date', $date);

        return $this->hasUserStatus($assets);
    }

    public function hasUserStatus($query)
    {
        return $query->whereHas('assetStatus', function ($query) {
            $query->where('slug', 'assigned')
                ->orWhere('slug', 'loaned')
                ->orWhere('slug', 'uncollected_term');
        });
    }

    protected function joinAssetDetailTables($assets)
    {
        return $assets->join('make_and_models', 'assets.make_and_model_id', '=', 'make_and_models.id')
            ->join('manufacturers', 'manufacturers.id', '=', 'make_and_models.manufacturer_id')
            ->join('asset_types', 'assets.asset_type_id', '=', 'asset_types.id')
            ->join('technical_specs', 'assets.technical_spec_id', '=', 'technical_specs.id')
            ->join('asset_statuses', 'assets.asset_status_id', '=', 'asset_statuses.id')
            ->select('assets.id', 'assets.asset_tag', 'assets.serial_no', 'asset_types.name as asset_type', 'make_and_models.name as make_model_name', 'technical_specs.details', 'asset_statuses.name as asset_status', 'users.email', DB::raw('CONCAT(users.first_name, " ", users.last_name) as name'));
    }

    public function getTerminatedTeamSelectedAssets($userId, $date)
    {
        $assets = Asset::join('users', 'assets.user_id', '=', 'users.id')
            ->join('users as managers', 'managers.id', '=', 'users.manager_id');

        $assets = $this->joinAssetDetailTables($assets)->where('users.manager_id', $userId)->where('users.terminated_date', $date);
        $assets = $this->hasSpecificAssetStatus($assets);
        return $assets;
    }

    public function hasSpecificAssetStatus($query)
    {
        return $query->whereHas('assetStatus', function ($query) {
            $query->where('slug', 'assigned')
                ->orWhere('slug', 'loaned');
        });
    }

    public function getTerminatedEmployees($userId, $date)
    {
        $assetStatusIds = AssetStatus::whereIn('slug', ['assigned', 'loaned'])->pluck('id')->toArray();
        
        $users = User::with(['assets' => function ($query) use ($assetStatusIds) {
                    $query->whereIn('asset_status_id', $assetStatusIds);
                }])
                ->inActive()
                ->whereHas('employeeType', function ($query) {
                    $query->where('slug', 'employee');
                })
                ->whereHas('assets', function ($query) use ($assetStatusIds){
                    $query->whereIn('asset_status_id', $assetStatusIds);
                })
                ->where('users.manager_id', $userId)
                ->where('users.terminated_date', $date);
        
        return $users;
    }
}
