<?php

namespace Core\Console\Commands;

use Illuminate\Console\Command;
use Carbon\Carbon;
use DB;

class QueueStats extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'queue:stats';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Queue jobs statistics';

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $active = DB::table('queue_monitor')->whereNull('finished_at')->count();
        $active_hour = DB::table('queue_monitor')
                         ->whereNull('finished_at')
                         ->where('created_at', Carbon::now()->subHours(1))
                         ->count();

        $failed = DB::table('queue_monitor')->where('failed', true)->count();
        $failed_hour = DB::table('queue_monitor')
                         ->where('failed', true)
                         ->where('created_at', Carbon::now()->subHours(1))
                         ->count();

        $done = DB::table('queue_monitor')->whereNotNull('finished_at')->where('failed', false)->count();
        $done_hour = DB::table('queue_monitor')
                      ->whereNotNull('finished_at')
                      ->where('failed', false)
                      ->where('created_at', Carbon::now()->subHours(1))
                      ->count();

        $delay = DB::table('queue_monitor')->selectRaw('avg(started_at-created_at) as avg')->whereNotNull('started_at')->first();
        $delay_hour = DB::table('queue_monitor')
                    ->selectRaw('avg(started_at-created_at) as avg')
                    ->whereNotNull('started_at')
                    ->where('created_at', Carbon::now()->subHours(1))
                    ->first();

        $worktime = DB::table('queue_monitor')->selectRaw('avg(finished_at-started_at) as avg')->whereNotNull('finished_at')->first();
        $worktime_hour = DB::table('queue_monitor')
                    ->selectRaw('avg(finished_at-started_at) as avg')
                    ->whereNotNull('finished_at')
                    ->where('created_at', Carbon::now()->subHours(1))
                    ->first();

        $data[] = ['Queued', $active, $active_hour];
        $data[] = ['Failed', $failed, $failed_hour];
        $data[] = ['Done', $done, $done_hour];
        $data[] = ['Avg waiting time', $delay->avg, ($delay_hour->avg) ? $delay_hour->avg:'-'];
        $data[] = ['Avg work time', $worktime->avg, ($worktime->avg) ? $worktime->avg:'-'];
        $this->table(['Metric', 'All time', 'Last hour'], $data);

    }
}