site/app/Console/Commands/AutoSubscribePaidUsers.php

<?php

namespace App\Console\Commands;

use DB;
use App\Models\User;
use Illuminate\Console\Command;
use App\Domain\Points\Models\Point;
use App\Domain\Users\Models\UserPackage;
use App\Domain\Points\Enums\DirectionEnum;
use App\Notifications\UserCustomPaidSubscription;
use App\Domain\Subscriptions\Service\SubscriptionService;

class AutoSubscribePaidUsers extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'teaser:user-auto-subs';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Auto-subscription for paid users';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        \DB::beginTransaction();

        $users_package_customers = DB::table('users_package_customers')
        // ->join('users_subscribers', 'users_package_customers.customer_id', '=', 'users_subscribers.subscriber_id')
        ->join('users_subscribers', function ($join) {
            $join
            ->on('users_package_customers.user_id', '=', 'users_subscribers.subscriber_id')
            ->where('users_subscribers.autosubscription', true);
        })
        //->select('users_package_customers.*', 'users_subscribers.user_id as userId', 'users_subscribers.subscriber_id as subscriberId', 'users_subscribers.autosubscription')
        ->select(DB::raw('MAX(users_package_customers.time_end) as last_time,users_package_customers.customer_id,users_package_customers.package_id','users_subscribers.autosubscription'))
        ->groupBy('users_package_customers.customer_id', 'users_package_customers.package_id', 'users_subscribers.autosubscription')
        ->havingRaw('MAX(users_package_customers.time_end) <= ?', [now()])
        ->get();


        foreach ($users_package_customers as $user_package_customers) {
            $userCustomer = User::find($user_package_customers->customer_id);
            $userPackage = UserPackage::find($user_package_customers->package_id);
            $userHeadPackage = User::find($userPackage->user_id);
            $balance = SubscriptionService::calculate($userCustomer->id);

            if ($userPackage->price > $balance) {
                DB::table('users_subscribers')
                    ->where('user_id', $userCustomer->id)
                    ->where('subscriber_id', $userHeadPackage->id)
                    ->update(['autosubscription' => DB::raw('NOT autosubscription')]);
                continue;
            }


            DB::table('users_package_customers')->insert([
                'user_id' => $userHeadPackage->id,
                'customer_id' => $userCustomer->id,
                'package_id' => $userPackage->id,
                'price' => $userPackage->price,
                'time_end' => now()->addMonths(),
                //'time_end' => now()->addMinutes(10),
                'created_at' => now(),
            ]);


            $point = new Point;
            $point->user_id = $userCustomer->id;
            $point->point = $userPackage->price;
            $point->type = 'Оформлена подписка на пользователя: ' . $userHeadPackage->name . ' (' . $userHeadPackage->username . ')'; //YSV ENUM!
            $point->direction = DirectionEnum::EXPENSE();
            $point->save();

            $point = new Point;
            $point->user_id = $userHeadPackage->id;
            $point->point = $userPackage->price;
            $point->type = 'Пользователь оформил платную подписку: ' . $userCustomer->name . ' (' . $userCustomer->username . ')'; //YSV ENUM!
            $point->direction = DirectionEnum::COMING();
            $point->save();


            $message = [
                'user_id' => $userCustomer->id,
                'node_id' => null,
            ];
            $userHeadPackage->notify(new UserCustomPaidSubscription($message));


        }

        \DB::commit();
        // dd($users_package_customers);

        return 0;
        // max
        // $users_package_customers = DB::table('users_package_customers')
        //     ->select(DB::raw('max(time_end) as last_time, avg(attempt_auto_paid) as attempt_auto_paid_avg,customer_id,package_id'))
        //     ->groupBy('customer_id', 'package_id')
        //     ->havingRaw('MAX(time_end) <= ?', [now()])
        //     ->havingRaw('AVG(attempt_auto_paid) < ?', [3])
        //     ->get();


        // foreach ($users_package_customers as $users_package_customer) {
        //     $userCustomer = User::find($users_package_customer->customer_id);

        //     $userPackage = UserPackage::find($users_package_customer->package_id);
        //     $userHeadPackage = User::find($userPackage->user_id);

        //     $balance = SubscriptionService::calculate($userCustomer->id);

        //     if ($userPackage->price > $balance) {
        //         DB::table('users_package_customers')
        //             ->where('customer_id', $users_package_customer->customer_id)
        //             ->where('package_id', $users_package_customer->package_id)
        //             ->increment('attempt_auto_paid');
        //         continue;
        //     }


        //     DB::table('users_package_customers')->insert([
        //         'user_id' => $userHeadPackage->id,
        //         'customer_id' => $userCustomer->id,
        //         'package_id' => $userPackage->id,
        //         'price' => $userPackage->price,
        //         // 'time_end' => now()->addMonths(),
        //         'time_end' => now()->addMinutes(10),
        //         'created_at' => now(),
        //     ]);


        //     $point = new Point;
        //     $point->user_id = $userCustomer->id;
        //     $point->point = $userPackage->price;
        //     $point->type = 'Оформлена подписка на пользователя: ' . $userHeadPackage->username; //YSV ENUM!
        //     $point->direction = DirectionEnum::EXPENSE();
        //     $point->save();

        //     $point = new Point;
        //     $point->user_id = $userHeadPackage->id;
        //     $point->point = $userPackage->price;
        //     $point->type = 'Пользователь оформил платную подписку: ' .  $userCustomer->username; //YSV ENUM!
        //     $point->direction = DirectionEnum::COMING();
        //     $point->save();


        //     $message = [
        //         'user_id' => $userCustomer->id,
        //         'node_id' => null,
        //     ];
        //     $userHeadPackage->notify(new UserCustomPaidSubscription($message));

        // }



        // return 0;

    }
}