<?php

namespace App\Console\Commands;

use App\Models\Alert;
use App\Models\Employee;
use App\Models\Lot;
use App\Models\User;
use Carbon\Carbon;
use DB;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\Auth;

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'This command run for employee alert data insert';

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

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        //First Heat Age
        $first_heat_age = config('perameters.first_heat_age');
        $today= Carbon::now()->format('Y-m-d');

        $employees= Employee::where('sex','female')->get();
        foreach($employees as $employee){

            $first_heat_date = Carbon::createFromFormat('Y-m-d', $employee->birth_date)->addMonths($first_heat_age)->format('Y-m-d');

            if($today >= $first_heat_date){
                $alert = Alert::where([['employee_id',$employee->id],['type','first_heat_age'],['status',0]])->get()->count();
                if($alert == 0){
                    Alert::create(['employee_id' => $employee->id,'type'=>'first_heat_age']);
                }
            }

        }

         //Calving Preperation
         $expected_calving_day = config('perameters.expected_calving');
         $expected_calving_date = Carbon::now()->subDays($expected_calving_day)->format('Y-m-d');

         $expected_calving_pre_day = config('perameters.calving_preparation');
         $expected_calving_pre_date = Carbon::createFromFormat('Y-m-d', $expected_calving_date)->subDays($expected_calving_pre_day)->format('Y-m-d');

         $employeesHeatIds = DB::table('artificial_inseminations')
                    ->select('artificial_inseminations.id','artificial_inseminations.employee_id','artificial_inseminations.heat_date')
                    ->Join(DB::raw('(select employee_id, max(heat_date) as MaxDate from artificial_inseminations group by employee_id) ai'),function($join) {
                                        $join->on('ai.employee_id','=','artificial_inseminations.employee_id')
                                            ->on('artificial_inseminations.heat_date','ai.MaxDate');
                                    })
                    ->get();
        foreach($employeesHeatIds as $ids){
            $alert = Alert::where([['employee_id',$ids->employee_id],['type','calving_preperation'],['status',0]])->get()->count();
            if($alert == 0){
                if($ids->heat_date <= $expected_calving_pre_date){
                    Alert::updateOrCreate(['employee_id' => $employee->id,'type'=>'calving_preperation']);
                }
            }
        }

        //Sire Perfermance
        $sire_performance = config('perameters.sire_perfemance');
        $employeeSires = DB::table('artificial_inseminations')->Join('employees', 'employees.id', '=', 'artificial_inseminations.employee_id')
                        ->select('employees.organization_id','employees.herd_id','employees.lot_id','artificial_inseminations.sire_id','artificial_inseminations.employee_id',
                        DB::raw('(SELECT COUNT(id) FROM pregnancy_diagnosises WHERE `pregnancy_diagnosises`.`employee_id` = `artificial_inseminations`.`employee_id` AND ( `pregnancy_diagnosises`.`pd1_result` = "pregnant" OR `pregnancy_diagnosises`.`pd2_result` = "pregnant"))  AS total_pregnant'),
                        DB::raw('count(artificial_inseminations.id) AS total_heat'))
                        ->groupBy('artificial_inseminations.sire_id')
                        ->orderBy('artificial_inseminations.id','desc')
                        ->get();
        foreach($employeeSires as $item){
            $alert = Alert::where([['employee_id',$item->employee_id],['type','low_sire_performance'],['status',0]])->get()->count();

            if($item->total_heat) $heat_no = $item->total_heat; else $heat_no = 0;
            if($item->total_pregnant) $pregnant_no = $item->total_pregnant; else $pregnant_no = 0;

            $performance = 0;
            $performance = ((int)$pregnant_no/(int)$heat_no)*100;

            if($alert == 0 && $performance < $sire_performance){
                Alert::updateOrCreate(['employee_id' => $item->employee_id,'sire_id' => $item->sire_id,'type'=>'low_sire_performance']);
            }
        }

    }
}
