<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Notifications\FirebaseNotification;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
use App\Models\Employee;
use Illuminate\Database\Eloquent\Builder;

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Send notification for breeding alert';

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

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        // $fiveDaysBefore = now()->addDays(5)->toDateString();
        // $employees = \App\Models\Employee::orderBy('tag_id', 'desc')->where('disposal_status', 0)->get();
        // $employeeIds = [];
        // foreach ($employees as $employee) {
        //     $employeeIds[] = $employee->id;
        // }
        // $vaccinations = \App\Models\Vaccination::orderBy('id','desc')->whereIn('employee_id', $employeeIds)->get();
        // foreach ($vaccinations as $vaccination) {
        //     $user = $vaccination->user;
        //     $user->notify(new FirebaseNotification($vaccination->user->device_token, 'Notification!', 'Vaccine('.$vaccination->vaccine->name.') date for Tag ID ('.$vaccination->employee->tag->last_four_digit.') is '.$vaccination->vaccine_date));
        // }

        $data = [];
        $data1 = [];
        $data2 = [];
        $data3 = [];
        $data4 = [];
        $data5 =[];
        $organization_id = null;
        $employeeIds = Employee::orderBy('tag_id', 'desc')->where('disposal_status', 0)->pluck('id');
        // $employeeIds = DB::table('employees')->whereIn('lot_id',$lotId)->where('disposal_status', 0)->pluck('id');

        //Expected 1st Heat
        $expected_first_heat_day = config('perameters.expected_first_heat');
        $expected_first_heat_date = Carbon::now()->subDays($expected_first_heat_day-3)->format('Y-m-d');

        $employeeIdsArr = array();
        $employeeIdsArray = array();

        $employeesIds = DB::table('calvings')
                        ->select('calvings.id','calvings.employee_id','calvings.calving_date')
                        ->Join(DB::raw('(select employee_id, max(calving_date) as MaxDate from calvings group by employee_id) cc'),function($join) {
                             $join->on('cc.employee_id','=','calvings.employee_id')
                             ->on('calvings.calving_date','cc.MaxDate');
                        })
            ->whereIn('calvings.employee_id',$employeeIds)
            ->orderBy('employee_id', 'asc')->get();

        foreach($employeesIds as $ids){
            $data['employee'] = Employee::where('id',$ids->employee_id)->where('disposal_status', 0)->first();
            $data['exact_calving_date'] = Carbon::createFromFormat('Y-m-d', $ids->calving_date)->addDays($expected_first_heat_day)->format('Y-m-d');
            array_push($employeeIdsArr,$data);

            $employeeIdsArray = array_unique($employeeIdsArr,SORT_REGULAR);
        }

        //Check For Heat,1st Pd Check,2nd Pd Check
       $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');
                                            })
           ->whereIn('artificial_inseminations.employee_id',$employeeIds)
           ->orderBy('employee_id', 'asc')->get();

        //Check For Heat
        $heat_interval_day = config('perameters.heat_interval');
        $heat_interval_date = Carbon::now()->subDays($heat_interval_day-3)->format('Y-m-d');
        $employeeHeatIdsArr = array();

        //1st PD Check
        $first_pd_check_period_day = config('perameters.first_pd_check_period');
        $first_pd_check_period_date = Carbon::now()->subDays($first_pd_check_period_day-3)->format('Y-m-d');

        $employeeFirstPDIdsArr = array();

        //2nd PD Check
        $second_pd_check_period_day = config('perameters.second_pd_check_period');
        $second_pd_check_period_date = Carbon::now()->subDays($second_pd_check_period_day-3)->format('Y-m-d');

        $employeeSecondPDIdsArr = array();

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

        $employeeCalvingIdsArr = array();

        //Preg Dry Date
        $preg_dry_day = config('perameters.preg_dry_period');
        $total_preg_dry_day = ($expected_calving_day-$preg_dry_day)+$expected_first_heat_day;
        $preg_dry_date = Carbon::now()->subDays($total_preg_dry_day-3)->format('Y-m-d');

        $employeePregDryIdsArr = array();
        $employeeHeatIdsArray = array();
        $employeeFirstPDIdsArray = array();
        $employeeSecondPDIdsArray = array();
        $employeeCalvingIdsArray = array();
        $employeePregDryIdsArray = array();

        $employeesHeatIds2 = 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 where artificial_inseminations.service_type != "SKIP" group by employee_id) ai'),function($join) {
                $join->on('ai.employee_id','=','artificial_inseminations.employee_id')
                    ->on('artificial_inseminations.heat_date','ai.MaxDate');
            })
            ->whereIn('artificial_inseminations.employee_id', $employeeIds)
            ->orderBy('employee_id', 'asc')->get();
        foreach($employeesHeatIds2 as $ids) {
            if($ids->heat_date <= $first_pd_check_period_date) {
                $data2['employee_first_pd'] = Employee::where('id',$ids->employee_id)->where('disposal_status', 0)->first();
                $data2['exact_first_pd_date'] = Carbon::createFromFormat('Y-m-d', $ids->heat_date)->addDays($first_pd_check_period_day)->format('Y-m-d');
            }
            array_push($employeeFirstPDIdsArr,$data2);
        }

        //return $employeesHeatIds;
        foreach($employeesHeatIds as $ids){
            if($ids->heat_date <= $heat_interval_date){
                $data1['employee_heat'] = Employee::where('id',$ids->employee_id)->where('disposal_status', 0)->where('status','!=','pregnant')->first();
                $data1['exact_heat_date'] = Carbon::createFromFormat('Y-m-d', $ids->heat_date)->addDays($heat_interval_day)->format('Y-m-d');
            }
            array_push($employeeHeatIdsArr,$data1);

            $employeeHeatIdsArray = array_unique($employeeHeatIdsArr,SORT_REGULAR);

            $employeeFirstPDIdsArray = array_unique($employeeFirstPDIdsArr,SORT_REGULAR);

            if($ids->heat_date <= $second_pd_check_period_date){
                $data3['employee_second_pd'] = Employee::where('id',$ids->employee_id)->where('disposal_status', 0)->first();
                $data3['exact_second_pd_date'] = Carbon::createFromFormat('Y-m-d', $ids->heat_date)->addDays($second_pd_check_period_day)->format('Y-m-d');
            }
            array_push($employeeSecondPDIdsArr,$data3);

            $employeeSecondPDIdsArray = array_unique($employeeSecondPDIdsArr,SORT_REGULAR);

            if($ids->heat_date <= $expected_calving_date){
                $data4['employee_calving'] = Employee::where('id',$ids->employee_id)->where('disposal_status', 0)->where('status','pregnant')->first();
                $data4['exact_calving_dte'] = Carbon::createFromFormat('Y-m-d', $ids->heat_date)->addDays($expected_calving_day)->format('Y-m-d');
            }
            array_push($employeeCalvingIdsArr,$data4);

            $employeeCalvingIdsArray = array_unique($employeeCalvingIdsArr,SORT_REGULAR);

            if($ids->heat_date <= $preg_dry_date){
                $data5['employee_preg'] = Employee::where('id',$ids->employee_id)->where('disposal_status', 0)->where('status','pregnant')->first();
                $data5['exact_preg_date'] = Carbon::createFromFormat('Y-m-d', $ids->heat_date)->addDays($preg_dry_date)->format('Y-m-d');
            }
            array_push($employeePregDryIdsArr,$data5);

            $employeePregDryIdsArray = array_unique($employeePregDryIdsArr,SORT_REGULAR);
        }

        //Expected Calving
        $toDay = Carbon::now()
                ->format('d-m-Y');
        $expectedCalvingDay = config('perameters.expected_calving');
        $expectedPregDryDay = config('perameters.preg_dry_period');
        $lactationLength = config('perameters.lactation_length');

        $employeeCalvingIdsArray = Employee::select('id', 'tag_id', 'disposal_status')
            ->has('artificial_inseminations', '>', 0)
                ->with(['tag',
                    'calvings' => function($query){
                        $query->select('id', 'employee_id', 'calving_date')
                            ->latest('calving_date');
                    },
                    'artificial_inseminations' => function($query){
                        $query->latest('heat_date');
                    },
                    'pregnancy_diagnosises'=> function($query){
                        $query->latest('check_date');
                    },
                ])->whereIn('id', $employeeIds)
            ->whereHas('pregnancy_diagnosises', function (Builder $builder) {
                $builder->where('pd1_result', '=', 'pregnant')
                    ->orWhere('pd2_result', '=', 'pregnant');
            })
            ->where('disposal_status', 0)
            ->get()
            ->map(function ($query) {
                $query->setRelation('calvings', $query->calvings->take(1));
                return $query;
            })
            ->map(function ($query) {
                $query->setRelation('artificial_inseminations', $query->artificial_inseminations->take(1));
                return $query;
            })
            ->map(function ($query) {
                $query->setRelation('pregnancy_diagnosises', $query->pregnancy_diagnosises->take(1));
                return $query;
            });
        $modifiedEmployeeIds = [];
        foreach ($employeeCalvingIdsArray as $key => $employeeItem)
        {
            $employeeCalvingIdsArray[$key]['expected_calving_date'] = Carbon::createFromDate($employeeItem->artificial_inseminations[0]->heat_date)
                ->addDays(config('perameters.expected_calving'))
                ->format('d-m-Y');

            $fromPreCalvingShowing = Carbon::createFromDate($employeeCalvingIdsArray[$key]->expected_calving_date)
                ->subDay(5)
                ->format('d-m-Y');
            $isShowingPreCalvingEmployee = Carbon::parse($toDay)->greaterThanOrEqualTo(Carbon::parse($fromPreCalvingShowing));

            if (count($employeeItem->calvings) > 0)
            {
                $employeeItem->calvings[0]['calving_date'] = Carbon::createFromDate($employeeItem->calvings[0]->calving_date)
                    ->format('d-m-Y');
                $result = Carbon::parse($fromPreCalvingShowing)->gt(Carbon::parse($employeeItem->calvings[0]->calving_date));
                if ($result && $isShowingPreCalvingEmployee)
                {
                    array_push($modifiedEmployeeIds, $employeeItem);
                }
            }else{
                if ($isShowingPreCalvingEmployee){
                    array_push($modifiedEmployeeIds, $employeeItem);
                }
            }

        }


        //Dry off
        $employeeDry = Employee::select('id', 'tag_id', 'disposal_status')
            ->has('calvings', '>', 0)
            ->has('artificial_inseminations', '>', 0)
            ->with(['tag',
                'calvings' => function($query){
                    $query->select('id', 'employee_id', 'calving_date')
                        ->latest('calving_date');
                },
                'artificial_inseminations' => function($query){
                    $query->latest('heat_date');
                },
                'pregnancy_diagnosises'=> function($query){
                    $query->latest('check_date');
                },
                'dryoffs'=> function($query){
                    $query->latest('dry_date');
                },
            ])->whereIn('id', $employeeIds)
            ->whereHas('pregnancy_diagnosises', function (Builder $builder) {
                $builder->where('pd1_result', '=', 'pregnant')
                    ->orWhere('pd2_result', '=', 'pregnant');
            })
            ->where('disposal_status', 0)
            ->get()
            ->map(function ($query) {
                $query->setRelation('calvings', $query->calvings->take(1));
                return $query;
            })
            ->map(function ($query) {
                $query->setRelation('artificial_inseminations', $query->artificial_inseminations->take(1));
                return $query;
            })
            ->map(function ($query) {
                $query->setRelation('pregnancy_diagnosises', $query->pregnancy_diagnosises->take(1));
                return $query;
            })
            ->map(function ($query) {
                $query->setRelation('dryoffs', $query->dryoffs->take(1));
                return $query;
            });
        $employeeDryOff = [];

        foreach ($employeeDry as $k => $item)
        {
            $employeeDry[$k]['expected_dry_of_date'] = Carbon::createFromDate($item->artificial_inseminations[0]->heat_date)
                ->addDays(($expectedCalvingDay - $expectedPregDryDay))
                ->format('d-m-Y');

            $maxLastLactionLenght = Carbon::createFromDate($item->calvings[0]->calving_date)
                ->addDays($lactationLength)
                ->format('d-m-Y');

            $employeeDry[$k]['calving_period_continue'] = $maxLastLactionLenght;

            $fromCalvingShowingDate = Carbon::createFromDate($employeeDry[$k]->expected_dry_of_date)
                ->subDay(3)
                ->format('d-m-Y');

            $result = Carbon::parse($maxLastLactionLenght)->gt(Carbon::parse($employeeDry[$k]->expected_dry_of_date));
            $isShowingEmployee = Carbon::parse($toDay)->greaterThanOrEqualTo(Carbon::parse($fromCalvingShowingDate));
            if (count($item->dryoffs) > 0){
                $checkLastDryDate = Carbon::parse($fromCalvingShowingDate)->gt(Carbon::parse($item->dryoffs[0]->dry_date));
            }else{
                $checkLastDryDate = true;
            }


            if ($result && $isShowingEmployee && $checkLastDryDate)
            {
                array_push($employeeDryOff, $item);
            }
        }
        //Send Notification for "Check For Heat(Regular 22 Days Interval)"
        foreach ($employeeHeatIdsArray as $employeeHeatId) {
            if($employeeHeatId){
                $user = $employeeHeatId['employee_heat']->user;
                $user->notify(new FirebaseNotification($employeeHeatId['employee_heat']->user->device_token, 'নোটিশ!', 'Employee ID('. $employeeHeatId['employee_heat']->tag->uid .') should check For Heat'));
            }
        }

        // return view('backend.daily_action.breeding.index',
        //     compact('organizations', 'organization_id','employeeIdsArray','employeeHeatIdsArray',
        //     'employeeFirstPDIdsArray','employeeSecondPDIdsArray','modifiedEmployeeIds','employeeDryOff'));
    }
}
