clrghouz/app/Models/Domain.php

<?php

namespace App\Models;

use AgliPanci\LaravelCase\Facades\CaseBuilder;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

use App\Models\Casts\CompressedStringOrNull;
use App\Traits\{QueryCacheableConfig,ScopeActive};

/**
 * This represents an FTN Domain.
 *
 * We are either a member of the domain (because we have an AKA in it) or NOT.
 *
 * The assumption is, if we are a member of the domain, we receive/send mail to an uplink and possibly downlinks
 */
class Domain extends Model
{
	use HasFactory,QueryCacheableConfig,ScopeActive;

	private const CACHE_TIME = 3600;
	private const STATS_MONTHS = 6;

	protected $casts = [
		'homepage' => CompressedStringOrNull::class,
	];

	/* SCOPES */

	/**
	 * A domain is public (visible), if the user is an admin or, the domain is marked public)
	 */
	public function scopePublic($query)
	{
		$user = Auth::user();

		return $query
			->active()
			->when((! $user) || (! $user->isAdmin()),
				fn($query)=>$query->where('public',TRUE));
	}

	/* RELATIONS */

	public function echoareas()
	{
		return $this->hasMany(Echoarea::class)
			->orderBy('name');
	}

	public function fileareas()
	{
		return $this->hasMany(Filearea::class)
			->orderBy('name');
	}

	public function nodelist_filearea()
	{
		return $this->belongsTo(Filearea::class);
	}

	public function nodestatus_echoarea()
	{
		return $this->belongsTo(Echoarea::class,'nodestatus_id');
	}

	public function zones()
	{
		return $this->hasMany(Zone::class)
			->select(['id','zone_id','domain_id','active'])
			->orderBy('zone_id');
	}

	/* ATTRIBUTES */

	/**
	 * We can accept applications if we have an address in the domain
	 *
	 * @return bool
	 * @throws \Exception
	 */
	public function getCanAcceptAppAttribute(): bool
	{
		return $this->isManaged()
			&& $this->accept_app
			&& Auth::id();
	}

	public function getHomePageAttribute(?string $value): string
	{
		return $this->castAttribute('homepage',$value) ?: 'No available information at the moment.';
	}

	/* METHODS */

	/**
	 * Show count of messages by day/week/month/all stats for each echoarea in this domain
	 *
	 * @return Collection
	 */
	public function echoarea_stats(): Collection
	{
		return Cache::remember(md5(sprintf('%d-%s',$this->id,__METHOD__)),self::CACHE_TIME,function() {
			$dt = Carbon::now()->startOfday();
			$case = CaseBuilder::whenRaw("datetime >= '?'",$dt->subDay()->format('Y-m-d'))->thenRaw("'day'")
				->whenRaw("datetime >= '?'",$dt->subDays(7)->format('Y-m-d'))->thenRaw("'week'")
				->whenRaw("datetime >= '?'",$dt->subMonth()->format('Y-m-d'))->thenRaw("'month'")
				->elseRaw("'all'");

			return Echoarea::select(['echoareas.id','name','description','active',DB::raw('count(echomails.id) AS count'),DB::raw('min(datetime) as first_message'),DB::raw('max(datetime) as last_message')])
				->selectRaw($case->toRaw().' AS stats')
				->join('echomails',['echomails.echoarea_id'=>'echoareas.id'],NULL,NULL,'left outer')
				->where('domain_id',$this->id)
				->groupBy('echoareas.id')
				->groupBy('echoareas.name')
				->groupBy('stats')
				->orderBy('echoareas.name')
				->orderBy('last_message','DESC')
				->get();
		});
	}

	/**
	 * Show daily total of messages by echoarea in this domain
	 *
	 * @param Collection|NULL $systems
	 * @return Collection
	 */
	public function echoarea_total_daily(Collection $systems=NULL): Collection
	{
		return Cache::remember(md5(sprintf('%d-%s',$this->id,$systems?->pluck('id')->join(','))),self::CACHE_TIME,function() use ($systems) {
			return DB::query()
				->select(['echoareas.name','echoareas.show',DB::raw('COUNT(echoareas.name) AS count'),DB::raw('datetime::date AS date')])
				->from($this->getTable())
				->join('echoareas',['echoareas.domain_id'=>'domains.id'])
				->join('echomails',['echomails.echoarea_id'=>'echoareas.id'])
				->where('domains.id',$this->id)
				->where('echomails.datetime','>=',Carbon::now()->subMonths(self::STATS_MONTHS)->startOfMonth())
				->when($systems?->count(),fn($query)=>$query->whereIn('echomails.fftn_id',$systems->pluck('addresses')->flatten()->pluck('id')))
				->groupBy(['echoareas.name','echoareas.show','date'])
				->orderBy('echoareas.name')
				->orderBy('date')
				->get();
		});
	}

	/**
	 * Show count of files by week/month/all status for each filearea in this domain
	 */
	public function filearea_stats()
	{
		return Cache::remember(md5(sprintf('%d-%s',$this->id,__METHOD__)),self::CACHE_TIME,function() {
			$dt = Carbon::now()->startOfday();
			$case = CaseBuilder::whenRaw("datetime >= '?'",$dt->subDays(7)->format('Y-m-d'))->thenRaw("'week'")
				->whenRaw("datetime >= '?'",$dt->subMonth()->format('Y-m-d'))->thenRaw("'month'")
				->elseRaw("'all'");

			return Filearea::select(['fileareas.id','fileareas.name','description','active',DB::raw('count(files.id) AS count'),DB::raw('min(datetime) as first_file'),DB::raw('max(datetime) as last_file')])
				->selectRaw($case->toRaw().' AS stats')
				->join('files',['files.filearea_id'=>'fileareas.id'],NULL,NULL,'left outer')
				->where('domain_id',$this->id)
				->groupBy('fileareas.id')
				->groupBy('fileareas.name')
				->groupBy('stats')
				->orderBy('fileareas.name')
				->orderBy('last_file','DESC')
				->get();
		});
	}

	/**
	 * Determine if this zone is managed by this host
	 *
	 * @return bool
	 * @throws \Exception
	 */
	public function isManaged(): bool
	{
		return our_address($this)->count() > 0;
	}
}