<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\Bidders;
use App\Models\CarShowCategory;
use App\Models\CarShowWinner;
use App\Models\PeoplesChoice;
use App\Models\Vehicles;
use App\Models\VehicleScores;
use Illuminate\Support\Facades\DB;


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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Tabulate Show Winners, and add them to the CarShowWinner table';

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

    /**
     * Execute the console command.
     *
     * @return mixed
     */

    public function handle()
    {
        function bestInShowWinner()
        {
            $bestInShowWinnerQuery = VehicleScores::join('vehicles', 'vehicle_scores.vehicle', '=', 'vehicles.id')
                ->groupBy('vehicles.id')
                ->selectRaw('*, sum(vehicle_scores.overall_score) as totalscore')
                ->whereNotIn('vehicle_scores.vehicle', function($query){
                    $query->select('vehicle')->from('car_show_winners');
                })
                ->where('vehicles.doNotJudge', '=', 0)
                ->orderBy('totalscore','desc')
                ->first();
            return $bestInShowWinnerQuery->vehicle;        
        }  
        
        function bestInShow2ndWinner()
        {
            $bestInShow2ndWinnerQuery = VehicleScores::join('vehicles', 'vehicle_scores.vehicle', '=', 'vehicles.id')
                ->groupBy('vehicles.id')
                ->selectRaw('*, sum(vehicle_scores.overall_score) as totalscore')
                ->whereNotIn('vehicle_scores.vehicle', function($query){
                    $query->select('vehicle')->from('car_show_winners');
                })
                ->where('vehicles.doNotJudge', '=', 0)
                ->orderBy('totalscore','desc')
                ->first();
            return $bestInShow2ndWinnerQuery->vehicle;        
        }  

        function pcWinner()
        {
            $peoplesChoiceWinnerQuery = PeoplesChoice::join('vehicles', 'peoples_choice.vehicle', '=', 'vehicles.id')
                ->groupBy('peoples_choice.vehicle')
                ->selectRaw('*, sum(pc_count) as totalscore')
                ->whereNotIn('vehicle', function($query){
                    $query->select('vehicle')->from('car_show_winners');
                })
                ->where('vehicles.doNotJudge', '=', 0)
                ->orderBy('totalscore','desc')
                ->first();
            return $peoplesChoiceWinnerQuery->vehicle;
        }   

        function pc2ndWinner()
        {
            $peoplesChoice2ndWinnerQuery = PeoplesChoice::join('vehicles', 'peoples_choice.vehicle', '=', 'vehicles.id')
                ->groupBy('peoples_choice.vehicle')
                ->selectRaw('*, sum(pc_count) as totalscore')
                ->whereNotIn('vehicle', function($query){
                    $query->select('vehicle')->from('car_show_winners');
                })
                ->where('vehicles.doNotJudge', '=', 0)
                ->orderBy('totalscore','desc')
                ->first();
            return $peoplesChoice2ndWinnerQuery->vehicle;
        }   

       function zeroTo43Winner()
        {
            $zeroTo43Query = VehicleScores::join('vehicles', 'vehicle_scores.vehicle', '=', 'vehicles.id')
                ->groupBy('vehicles.id')
                ->selectRaw('*, sum(vehicle_scores.overall_score) as totalscore')
                ->whereNotIn('vehicle_scores.vehicle', function($query){
                    $query->select('vehicle')->from('car_show_winners');
                })
                ->where('vehicles.year', '>=', 0)
                ->where('vehicles.year', '<=', 1943)
                ->where('vehicles.doNotJudge', '=', 0)
                ->orderBy('totalscore','desc')
                ->first();
            return $zeroTo43Query->vehicle;
        }

        function zeroTo432ndWinner()
        {
            $zeroTo432ndQuery = VehicleScores::join('vehicles', 'vehicle_scores.vehicle', '=', 'vehicles.id')
                ->groupBy('vehicles.id')
                ->selectRaw('*, sum(vehicle_scores.overall_score) as totalscore')
                ->whereNotIn('vehicle_scores.vehicle', function($query){
                    $query->select('vehicle')->from('car_show_winners');
                })
                ->where('vehicles.year', '>=', 0)
                ->where('vehicles.year', '<=', 1943)
                ->where('vehicles.doNotJudge', '=', 0)
                ->orderBy('totalscore','desc')
                ->first();
            return $zeroTo432ndQuery->vehicle;
        }

        function fortyFourToNinetySevenWinner()
        {
            $fortyFourToNinetySevenQuery = VehicleScores::join('vehicles', 'vehicle_scores.vehicle', '=', 'vehicles.id')
                ->groupBy('vehicles.id')
                ->selectRaw('*, sum(vehicle_scores.overall_score) as totalscore')
                ->whereNotIn('vehicle_scores.vehicle', function($query){
                    $query->select('vehicle')->from('car_show_winners');
                })
                ->where('vehicles.year', '>=', 1944)
                ->where('vehicles.year', '<=', 1997)
                ->where('vehicles.doNotJudge', '=', 0)
                ->orderBy('totalscore','desc')
                ->first();
            return $fortyFourToNinetySevenQuery->vehicle;
        }

        function fortyFourToNinetySeven2ndWinner()
        {
            $fortyFourToNinetySeven2ndQuery = VehicleScores::join('vehicles', 'vehicle_scores.vehicle', '=', 'vehicles.id')
                ->groupBy('vehicles.id')
                ->selectRaw('*, sum(vehicle_scores.overall_score) as totalscore')
                ->whereNotIn('vehicle_scores.vehicle', function($query){
                    $query->select('vehicle')->from('car_show_winners');
                })
                ->where('vehicles.year', '>=', 1944)
                ->where('vehicles.year', '<=', 1997)
                ->where('vehicles.doNotJudge', '=', 0)
                ->orderBy('totalscore','desc')
                ->first();
            return $fortyFourToNinetySeven2ndQuery->vehicle;
        }

        function NinetyEightToCurrentWinner()
        {
            $NinetyEightToCurrentQuery = VehicleScores::join('vehicles', 'vehicle_scores.vehicle', '=', 'vehicles.id')
                ->groupBy('vehicles.id')
                ->selectRaw('*, sum(vehicle_scores.overall_score) as totalscore')
                ->whereNotIn('vehicle_scores.vehicle', function($query){
                    $query->select('vehicle')->from('car_show_winners');
                })
                ->where('vehicles.year', '>=', 1998)
                //->where('vehicles.year', '<=', 1997)
                ->where('vehicles.doNotJudge', '=', 0)
                ->orderBy('totalscore','desc')
                ->first();
            return $NinetyEightToCurrentQuery->vehicle;
        }

        function NinetyEightToCurrent2ndWinner()
        {
            $NinetyEightToCurrent2ndQuery = VehicleScores::join('vehicles', 'vehicle_scores.vehicle', '=', 'vehicles.id')
                ->groupBy('vehicles.id')
                ->selectRaw('*, sum(vehicle_scores.overall_score) as totalscore')
                ->whereNotIn('vehicle_scores.vehicle', function($query){
                    $query->select('vehicle')->from('car_show_winners');
                })
                ->where('vehicles.year', '>=', 1998)
                //->where('vehicles.year', '<=', 1997)
                ->where('vehicles.doNotJudge', '=', 0)
                ->orderBy('totalscore','desc')
                ->first();
            return $NinetyEightToCurrent2ndQuery->vehicle;
        }

        // Truncate table first
        CarShowWinner::truncate();
        //Insert Best In Show Winner
        CarShowWinner::updateOrCreate(
            [
                'category' => '6',
                'place' => 'first'
            ],
            [
                'vehicle' => bestInShowWinner()
            ]
        );
        /*
        //Insert Best In Show 2nd Place Winner
        CarShowWinner::updateOrCreate(
            [
                'category' => '6',
                'place' => 'second'
            ],
            [
                'vehicle' => bestInShow2ndWinner()
            ]
        );
        */
        //Insert People's Choice Winner
        CarShowWinner::updateOrCreate(
            [
                'category' => '3',
                'place' => 'first'
            ],
            [
                'vehicle' => pcWinner()
            ]
        );
        /*
        CarShowWinner::updateOrCreate(
            [
                'category' => '3',
                'place' => 'second'
            ],
            [
                'vehicle' => pc2ndWinner()
            ]
        );
        */
        //Insert Year Award Winners
        //0-1943
        CarShowWinner::updateOrCreate(
                [
                    'category' => '17',
                    'place' => 'first'
                ],
                [
                    'vehicle' => zeroTo43Winner()
                ]
            );

        CarShowWinner::updateOrCreate(
                [
                    'category' => '17',
                    'place' => 'second'
                ],
                [
                    'vehicle' => zeroTo432ndWinner()
                ]
            );
        //1944-1997
        CarShowWinner::updateOrCreate(
                [
                    'category' => '18',
                    'place' => 'first'
                ],
                [
                    'vehicle' => fortyFourToNinetySevenWinner()
                ]
            );

        CarShowWinner::updateOrCreate(
                [
                    'category' => '18',
                    'place' => 'second'
                ],
                [
                    'vehicle' => fortyFourToNinetySeven2ndWinner()
                ]
            );  
            
        //1998-Current
        CarShowWinner::updateOrCreate(
            [
                'category' => '19',
                'place' => 'first'
            ],
            [
                'vehicle' => NinetyEightToCurrentWinner()
            ]
        );

        CarShowWinner::updateOrCreate(
            [
                'category' => '19',
                'place' => 'second'
            ],
            [
                'vehicle' => NinetyEightToCurrent2ndWinner()
            ]
        );  
    }
}