<?php

namespace App\Repositories\DiscoveryTools;

use App\Models\Asset;
use App\Models\MobileIron;
use App\Models\AssetStatus;
use App\Models\AssetType;
use DB;

/**
 * MobileIron Repository Class
 */
class MobileIronRepository extends AbstractRepository
{
    /**
     * Making query for Devices not created report
     *
     * @return DB Query
     */
    public function getAssetsNotCreated()
    {
        $assets =  MobileIron::with(
            'asset',
            'mobileCarrier:id,name,country_id',
            'mobileCarrier.country:id,name',
            'user:id,first_name,last_name,email,status,department_id',
            'user.department:id,name',
            'asset.assetStatus',
            'asset.makeAndModel',
            'asset.assetType',
            'asset.makeAndModel.manufacturer',
            'asset.location:id,room_name',
            'asset.user:id,first_name,last_name,email,status,department_id,city',
            'asset.user.department:id,name',
            'asset.latestAssetHistory:id,asset_id,updated_at'
        )->where('asset_id', 0);

        return $assets;
    }

    /**
     * Making query for All report
     */
    public function getDevicesForAllReport()
    {
        $assets = MobileIron::with(
            'asset',
            'mobileCarrier:id,name,country_id',
            'mobileCarrier.country:id,name',
            'user:id,first_name,last_name,email,status,department_id',
            'user.department:id,name',
            'asset.assetStatus',
            'asset.makeAndModel',
            'asset.assetType',
            'asset.makeAndModel.manufacturer',
            'asset.location:id,room_name',
            'asset.user:id,first_name,last_name,email,status,department_id,city',
            'asset.user.department:id,name',
            'asset.latestAssetHistory:id,asset_id,updated_at'
        );

        return $assets;
    }

    /**
     * Making query for Devices not Active report
     *
     * @return DB Query
     */
    public function getDevicesNotActive()
    {
        $statusId   = AssetStatus::where('slug', 'assigned')->first()->id;
        $query      = MobileIron::with(
            'asset',
            'mobileCarrier:id,name,country_id',
            'mobileCarrier.country:id,name',
            'user:id,first_name,last_name,email,status,department_id',
            'user.department:id,name',
            'asset.assetStatus',
            'asset.makeAndModel',
            'asset.assetType',
            'asset.makeAndModel.manufacturer',
            'asset.location:id,room_name',
            'asset.user:id,first_name,last_name,email,status,department_id,city',
            'asset.user.department:id,name',
            'asset.latestAssetHistory:id,asset_id,updated_at'
        )
            ->whereHas('asset', function ($query) use ($statusId) {
                $query->where('asset_status_id', $statusId);
            })
            ->where('last_checkin', '<', \Carbon\Carbon::now()->subDays(30));

        return $query;
    }

    /**
     * Making query for Devices On hand Checking In Report
     * @return DB Query
     */
    public function getDevicesOnHandCheckingIn()
    {
        $statusId   = AssetStatus::onHandStatuses()->get()->pluck('id');
        $query      = MobileIron::select('mobile_irons.*')
            ->with(
                'asset',
                'mobileCarrier:id,name,country_id',
                'mobileCarrier.country:id,name',
                'user:id,first_name,last_name,email,status,department_id',
                'user.department:id,name',
                'asset.assetStatus',
                'asset.makeAndModel',
                'asset.assetType',
                'asset.makeAndModel.manufacturer',
                'asset.location:id,room_name',
                'asset.user:id,first_name,last_name,email,status,department_id,city',
                'asset.user.department:id,name',
                'asset.latestAssetHistory:id,asset_id,updated_at'
            )
            ->join("assets", function ($join) {
                $join->on('assets.id', '=', 'mobile_irons.asset_id');
            })
            ->where('mobile_irons.last_checkin', '>', DB::raw('assets.last_status_update'))
            ->whereIn('assets.asset_status_id', $statusId);

        return $query;
    }

    /**
     * Making Query for User Discrepancy Report
     */
    public function getUserMismatchDevices()
    {
        $statusId   = AssetStatus::whereIn('slug', ['assigned'])->get()->pluck('id');
        $assets     = MobileIron::select('mobile_irons.*')
            ->with(
                'asset',
                'mobileCarrier:id,name,country_id',
                'mobileCarrier.country:id,name',
                'user:id,first_name,last_name,email,status,department_id',
                'user.department:id,name',
                'asset.assetStatus',
                'asset.makeAndModel',
                'asset.assetType',
                'asset.makeAndModel.manufacturer',
                'asset.location:id,room_name',
                'asset.user:id,first_name,last_name,email,status,department_id,city',
                'asset.user.department:id,name',
                'asset.latestAssetHistory:id,asset_id,updated_at'
            )
            ->join("assets", function ($join) use ($statusId) {
                $join->on('assets.serial_no', '=', 'mobile_irons.serial_no')
                    ->on('assets.user_id', '<>', 'mobile_irons.user_id')
                    ->whereIn('assets.asset_status_id', $statusId);
            });

        return $assets;
    }

    /**
     * Making query for Enrolled / First Check In  Report
     * @return DB Query
     */
    public function getEnrolledFirstCheckIn()
    {
        $query  = MobileIron::select('mobile_irons.*')
            ->with(
                'asset',
                'mobileCarrier:id,name,country_id',
                'mobileCarrier.country:id,name',
                'user:id,first_name,last_name,email,status,department_id',
                'user.department:id,name',
                'asset.assetStatus',
                'asset.makeAndModel',
                'asset.assetType',
                'asset.makeAndModel.manufacturer',
                'asset.location:id,room_name',
                'asset.user:id,first_name,last_name,email,status,department_id,city',
                'asset.user.department:id,name',
                'asset.latestAssetHistory:id,asset_id,updated_at'
            )
            ->join("assets", function ($join) {
                $join->on('assets.id', '=', 'mobile_irons.asset_id')
                    ->whereNotNull('assets.first_check_in');
            });

        return $query;
    }

    /**
     * Making query for Mobil Number Report
     */
    public function getMobileNumberData()
    {
        $assets =  MobileIron::select('mobile_irons.*', 'users.city as work_location')
            ->with(
                'asset',
                'mobileCarrier:id,name,country_id',
                'mobileCarrier.country:id,name',
                'user:id,first_name,last_name,email,status,department_id',
                'user.department:id,name',
                'asset.assetStatus',
                'asset.makeAndModel',
                'asset.assetType',
                'asset.makeAndModel.manufacturer',
                'asset.location:id,room_name',
                'asset.user:id,first_name,last_name,email,status,department_id,city',
                'asset.user.department:id,name',
                'asset.latestAssetHistory:id,asset_id,updated_at'
            )
            ->leftJoin('users', 'users.id', '=', 'mobile_irons.user_id')
            ->where('phone_number', '<>', '')
            ->whereRaw('mobile_irons.id IN (SELECT MAX(id) from mobile_irons WHERE phone_number != "" GROUP BY phone_number)');
        return $assets;
    }

    /**
     * Filter with Enrolled date range
     *
     * @param $query DB Query
     * @param $from  Date string
     * @param $to    Date string
     *
     * @return DB Query
     */
    public function filterWithEnrolledDate($query, $from, $to)
    {
        if ($from) {
            $query = $query->whereDate('enrolled_date', '>=', convert_to_db_date($from));
        }

        if ($to) {
            $query = $query->whereDate('enrolled_date', '<=', convert_to_db_date($to));
        }

        return $query;
    }

    /**
     * Filter with First Chec In Date
     *
     * @param $query DB Query
     * @param $from  Date string
     * @param $to    Date string
     *
     * @return DB Query
     */
    public function filterWithFirstCheckin($query, $from, $to)
    {
        if ($from) {
            $query = $query->whereDate('assets.first_check_in', '>=', convert_to_db_date($from));
        }

        if ($to) {
            $query = $query->whereDate('assets.first_check_in', '<=', convert_to_db_date($to));
        }

        return $query;
    }


    /**
     * Making query for  'Not checking in, not Managed & deployed' report
     */
    public function getNotManaged()
    {
        $statusId    = AssetStatus::whereIn('slug', ['assigned', 'loaned'])->get()->pluck('id');
        $assetTypeId = AssetType::whereIn('slug', ['laptop', 'mobile_phone', 'tablet', 'computer'])->get()->pluck('id');

        $query       = Asset::select('assets.*')
            ->with(
                'user:id,first_name,last_name,email,status,department_id',
                'user.department:id,name',
                'assetStatus',
                'makeAndModel',
                'carrier:id,name,country_id',
                'carrier.country:id,name',
                'assetType',
                'makeAndModel.manufacturer',
                'location:id,room_name',
                'latestAssetHistory:id,asset_id,updated_at'
            )
            ->whereIn('asset_status_id', $statusId)
            ->whereIn('asset_type_id', $assetTypeId)
            ->doesntHave('airwatch')
            ->doesntHave('chromebook')
            ->doesntHave('intune')
            ->doesntHave('jamf')
            ->doesntHave('KandjiDevices')
            ->doesntHave('mobileIron');

        return $query;
    }
}
