<?php

namespace App\Classes\General\Eloquent\Filters;


use App\Classes\ValueObjects\Constants\ApprovalStatus;
use App\Classes\ValueObjects\Constants\TransactionType;
use App\Models\Booking;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

class WithTotalPayments implements Filter
{

    /**
     * @param Builder $builder
     * @param $value
     * @return mixed
     */
    public static function apply(Builder $builder, $value)
    {
        return $builder->leftJoin('bookings', 'companies.id', '=', 'bookings.company_id')
            ->rightJoin('transactions', function ($join) {
                $join->on('bookings.id', '=', 'transactions.owner_id')
                ->where('owner_type', '=', Booking::class)
                ->where('transactions.type', TransactionType::PAYMENT)
                ->whereIn('transactions.status', [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED]);
        })->addSelect([
            'companies.*', 
            DB::raw('SUM(transactions.amount) as total_payments'), 
            DB::raw('SUM(transactions.amount / DATEDIFF(companies.created_at, CURRENT_DATE)) as spending_average_per_day'), 
            DB::raw('SUM(transactions.amount) / count(distinct bookings.id) as spending_average_booking')
        ])
            ->groupBy(['companies.id']);
    }
}