<?php

namespace App\Repositories;

use App\User;
use App\Models\AssetType;
use App\Models\Department;
use App\Models\Asset as AssetModel;
use App\Models\Location;
use App\Models\TechnicalSpecs;
use App\Models\AssetStatus;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

class AssetRepository
{
	/**
	 * Search users with assets based on specified type and search text.
	 *
	 * @param string|null $searchText The text to search for in user assets.
	 * @param string $type The type of assets to search among the user's assets.
	 *
	 * @return User|null Returns the first user with the specified asset type and search criteria, or null if none found.
	 */

	public function searchAssetUser($searchText = NULL, $type = 'assets')
	{
		return User::whereHas($type, function ($query) use ($searchText, $type) {

			$query = $this->assetTypeWhere($query, $type);

			return ($searchText) ?
				$this->assetWhereUser($query, $searchText)
				: $query;
		})->with([
			'position',
			'location.state',
			'employeeType',

		])->first();
	}

	/**
	 * Search locations with assets based on specified type and search text.
	 *
	 * @param string|null $searchText The text to search for in location assets.
	 * @param string $type The type of assets to search among the location's assets.
	 *
	 * @return Location|null Returns the first location with the specified asset type and search criteria, or null if none found.
	 */

	public function searchAssetLocation($searchText = NULL, $type = 'assets')
	{
		return Location::whereHas($type, function ($query) use ($searchText, $type) {

			$query = $this->assetTypeWhere($query, $type);

			return ($searchText) ?
				$this->assetWhereLocation($query, $searchText)
				: $query;
		})->with([
			'country',
			'region',

		])->first();
	}

	public function assetTypeWhere($query, $type)
	{
		if ($type == 'mobile_phone') {
			return $query->mobileAsset();
		} else if ($type == 'network') {
			return $query->networkAsset();
		} else if ($type == 'av') {
			return $query->avAsset();
		} else if ($type == 'research') {
			return $query->researchAsset();
		}
		return $query->regularAsset();
	}

	public function assetWhereUser($query, $searchText)
	{
		$searchTerms = explode(' ', $searchText);
		return  $query->where(function ($query) use ($searchTerms, $searchText) {
			foreach ($searchTerms as $searchTerm) {
				$query->where('serial_no', $searchTerm)
					->orWhere('asset_tag', $searchTerm)
					->orWhereHas('user', function ($query) use ($searchTerm) {
						$query->userWhereEmail($searchTerm);
					});
			}
			$query->orWhere('serial_no', $searchText);
		});
	}

	/**
	 * Fetches softweare assets of the user based on search text
	 * @param mixed $query
	 * @param mixed $searchText
	 *
	 * @return [type]
	 */
	public function softwareAssetWhereUser($query, $searchText)
	{
		$searchTerms = explode(' ', $searchText);
		return  $query->where(function ($query) use ($searchTerms, $searchText) {
			foreach ($searchTerms as $key => $searchTerm) {
				if ($key == 0) {
					$query->whereHas('user', function ($query) use ($searchTerm) {
						$query->userWhereEmail($searchTerm);
					});
				} else {
					$query->orWhereHas('user', function ($query) use ($searchTerm) {
						$query->userWhereEmail($searchTerm);
					});
				}
			}
		});
	}

	public function assetWhereLocation($query, $searchText)
	{
		$searchTerms = explode(' ', $searchText);
		return  $query->where(function ($query) use ($searchTerms, $searchText) {
			foreach ($searchTerms as $searchTerm) {

				$query->where(function ($query) use ($searchTerm) {

					$query->where('serial_no', $searchTerm)
						->orWhere('asset_tag', $searchTerm)
						->orWhereHas('location', function ($query) use ($searchTerm) {
							$query->locationWhere($searchTerm);
						});
				});
			}
			$query->orWhere('serial_no', $searchText);
		});
	}

	/**
	 * assetLocationSearched function
	 *
	 * @param datatype $query description
	 * @param string $searchText description
	 * @throws Some_Exception_Class description of exception
	 * @return Some_Return_Value
	 */
	public function assetLocationSearched($query, $searchText)
	{
		$searchTerms = explode(' ', $searchText);
		return  $query->has('location')->where(function ($query) use ($searchTerms, $searchText) {
			$query->where(function ($query) use ($searchTerms) {
				foreach ($searchTerms as $searchTerm) {
					$query->where('serial_no', $searchTerm)
						->orWhere('asset_tag', $searchTerm);
				}
			})->orWhere('serial_no', $searchText);
		});
	}

	/**
	 * Asset user searched function.
	 *
	 * @param datatype $query description
	 * @param datatype $searchText description
	 * @return Some_Return_Value
	 */
	public function assetUserSearched($query, $searchText)
	{
		$searchTerms = explode(' ', $searchText);
		return  $query->has('user')->where(function ($query) use ($searchTerms, $searchText) {
			$query->where(function ($query) use ($searchTerms) {
				foreach ($searchTerms as $searchTerm) {
					$query->where('serial_no', $searchTerm)
						->orWhere('asset_tag', $searchTerm);
				}
			})->orWhere('serial_no', $searchText);
		});
	}


	public function locationWhere($searchText)
	{
		return Location::where('room_name', 'like', '%' . $searchText . '%')->get()->pluck('id');
	}


	public function sumOriginal($assetIds)
	{
		return AssetModel::join('technical_specs', 'assets.technical_spec_id', 'technical_specs.id')
			->whereIn('assets.id', $assetIds)
			->sum(DB::raw('CASE WHEN assets.asset_original_value > 0 THEN assets.asset_original_value ELSE technical_specs.original_value END'));

		// Old Query. Replaced due to calculation miss match.
		// return TechnicalSpecs::join('assets', 'assets.technical_spec_id', 'technical_specs.id')
		// 	->whereIn('technical_specs.make_and_model_id', $makeId)
		// 	->whereIn('assets.id', $assetId)
		// 	->sum(DB::raw('CASE WHEN assets.asset_original_value > 0 THEN assets.asset_original_value ELSE technical_specs.original_value END'));
	}

	/**
	 * Seach users with software assets and filetr
	 * @param null $searchText
	 * @param string $type
	 *
	 * @return [type]
	 */
	public function searchSoftwareAssetUser($searchText = NULL, $type = 'userLicense')
	{
		return User::whereHas($type, function ($query) use ($searchText, $type) {
			$query = $query;
			return ($searchText) ?
				$this->softwareAssetWhereUser($query, $searchText)
				: $query;
		})->first();
	}

	/**
	 * Get all assets those are scanned in a location.
	 *
	 * @param mixed $query
	 * @param mixed $assetTags
	 * @param mixed $locationId
	 * @param mixed $assetTypeId
	 *
	 * @return $query
	 */
	public function getAssetScannedInLocationCount($assetTags, $locationId, $assetTypeId)
	{
		$excudedAssetTypesIdArray = AssetType::getAssetTypesExcludedInVarianceReport()->pluck('id')->toArray();
		$excludedAssetStatusesIdArray = AssetStatus::getAssetStatusesExcludedInVarianceReport()->pluck('id')->toArray();

		return AssetModel::getAssetByAssetTagsOrSerialNumbers($assetTags)
			->where('location_id', $locationId)
			->whereNotIn('asset_status_id', $excludedAssetStatusesIdArray)
			->when(!empty($assetTypeId), function ($subquery) use ($assetTypeId) {
				$subquery->whereIn('asset_type_id', $assetTypeId);
			})->whereNotIn('asset_type_id', $excudedAssetTypesIdArray)
			->filterOutStolenOrLostAssetsDatedMoreThanSpecifiedDays()
			->count();
	}

	/**
	 * get all assets those are not scanned in a location.
	 *
	 * @param mixed $query
	 * @param mixed $assetTags
	 * @param mixed $locationId
	 * @param mixed $assetTypeId
	 *
	 * @return $query
	 */
	public function getAssetNotScannedInLocationCount($assetTags, $locationId, $assetTypeId)
	{
		$excudedAssetTypesIdArray = AssetType::getAssetTypesExcludedInVarianceReport()->pluck('id')->toArray();
		$excludedAssetStatusesIdArray = AssetStatus::getAssetStatusesExcludedInVarianceReport()->pluck('id')->toArray();

		return AssetModel::excludeAssetsByAssetTagsOrSerialNumbers($assetTags)
			->where('location_id', $locationId)
			->whereNotIn('asset_status_id', $excludedAssetStatusesIdArray)
			->when(!empty($assetTypeId), function ($subquery) use ($assetTypeId) {
				$subquery->whereIn('asset_type_id', $assetTypeId);
			})->whereNotIn('asset_type_id', $excudedAssetTypesIdArray)
			->filterOutStolenOrLostAssetsDatedMoreThanSpecifiedDays()
			->count();
	}

	/**
	 * Get all assets those are scanned, but  not belongs to the selected location.
	 *
	 * @param mixed $query
	 * @param mixed $assetTags
	 * @param mixed $locationId
	 * @param mixed $assetTypeId
	 *
	 * @return $query
	 */
	public function getAssetScannedButNotBelongsToTheSelectedLocationCount($assetTags, $locationId, $assetTypeId)
	{
		$excudedAssetTypesIdArray = AssetType::getAssetTypesExcludedInVarianceReport()->pluck('id')->toArray();
		$excludedAssetStatusesIdArray = AssetStatus::getAssetStatusesExcludedInVarianceReport()->pluck('id')->toArray();

		return AssetModel::getAssetByAssetTagsOrSerialNumbers($assetTags)
			->where(function ($query) use ($locationId) {
				$query->whereNull('location_id')
					->orWhere('location_id', '!=', $locationId);
			})->whereNotIn('asset_status_id', $excludedAssetStatusesIdArray)
			->when(!empty($assetTypeId), function ($subquery) use ($assetTypeId) {
				$subquery->whereIn('asset_type_id', $assetTypeId);
			})->whereNotIn('asset_type_id', $excudedAssetTypesIdArray)
			->filterOutStolenOrLostAssetsDatedMoreThanSpecifiedDays()
			->count();
	}


	/**
	 * Retrieves a list of overdue assets that were due by yesterday, including their relevant information.
	 *
	 * @return \Illuminate\Database\Eloquent\QueryBuilder
	 */
	public function getLoanerOverDueAssets()
	{
		return  AssetModel::select('id', 'serial_no', 'loaner_return_date', 'user_id', 'make_and_model_id')->with(
			'user:id,first_name,last_name,email',
			'makeAndModel:id,name,manufacturer_id',
			'makeAndModel.manufacturer:id,name',
		)->loanerOverDueAssets();
	}

	/**
	 * Retrieves a query builder instance for assets with their related entities.
	 *
	 * @return \Illuminate\Database\Eloquent\Builder The query builder instance with the specified relationships.
	 */
	public function getAssetsQueryWithRelations()
	{
		return AssetModel::select('assets.*')
			->with([
				'location:id,room_name',
				'user:id,first_name,last_name,email,status',
				'assetType',
				'assetType.assetTabs',
				'makeAndModel.manufacturer',
				'technicalSpec:id,details',
				'assetStatus:id,name',
				'parentAsset:id,asset_tag,serial_no',
				'carrier',
				'latestAssetHistory:id,asset_id,created_at,updated_at,user_id',
				'latestAssetHistory.user:id,first_name,last_name,email,status',
				'childrenAsset.user:id,first_name,last_name,email,status',
				'childrenAsset.location:id,room_name',
				'childrenAsset.assetType',
				'childrenAsset.makeAndModel.manufacturer',
				'childrenAsset.technicalSpec:id,details',
				'childrenAsset.assetStatus:id,name',
				'childrenAsset.carrier',
				'childrenAsset.latestAssetHistory:id,asset_id,created_at,updated_at,user_id',
				'childrenAsset.latestAssetHistory.user:id,first_name,last_name,email,status',
			]);
	}

	/**
	 * Retrieves a query builder instance for assets that are due to be returned next week.
	 *
	 * @return \Illuminate\Database\Eloquent\Builder The query builder instance with the specified relationships.
	 */
	public function getLoanerDueNextWeekAssets()
	{
		return  AssetModel::select('id', 'serial_no', 'loaner_return_date', 'user_id', 'make_and_model_id')->with(
			'user:id,first_name,last_name,email',
			'makeAndModel:id,name,manufacturer_id',
			'makeAndModel.manufacturer:id,name',
		)->loanerDueNextWeekAssets();
	}
}
