<?php

namespace App\Repositories;

use App\Models\Bookings;
use App\Models\Menu;

class ReportRepository
{


public static function EndDay($date){

    $result = Bookings::select(\DB::raw('
    count(bookings.id) as sales_count,sum(bookings.amount) as sales,
    SUM(case when bookings.status = 3 then bookings.amount else 0 end) as cancelled,
    SUM(case when bookings.payment = 2 then 1 else 0 end) as paid_count,
    SUM(case when bookings.payment = 2 then bookings.amount else 0 end) as paid,
    SUM((SELECT SUM(amount) from booking_items where booking_id=bookings.id and status=0)) as voids,
    SUM((SELECT COUNT(id) from booking_payments where booking_id=bookings.id and payment_type="Card")) as card_count,
    SUM((SELECT SUM(amount) from booking_payments where booking_id=bookings.id and payment_type="Card")) as card,
    SUM((SELECT COUNT(id) from booking_payments where booking_id=bookings.id and payment_type="Cash")) as cash_count,
    SUM((SELECT SUM(amount) from booking_payments where booking_id=bookings.id and payment_type="Cash")) as cash

    '));

    $daterange = explode("--",$date);

    if(isset($daterange[1])){

      $result =$result->whereBetween(\DB::raw('DATE(bookings.booking_date)'),[$daterange[0],$daterange[1]]);

    }else{

      $result =$result->whereDate('bookings.booking_date',$date);

    }

    $result= $result->where('branch_id',auth()->user()->branch_id)->whereNot('seat_id',0)
    ->whereNotNull('seat_id')->first();

    $result = $result->toArray();
    //getting menu items from the array
    $items = Bookings::select(\DB::raw('SUM(booking_items.amount) as amount,booking_items.menu_id as menu_id,booking_items.menu_name as menu_name'))
    ->join('booking_items','bookings.id','booking_items.booking_id');

    if(isset($daterange[1])){

      $items =  $items->whereBetween(\DB::raw('DATE(bookings.booking_date)'),[$daterange[0],$daterange[1]]);

    }else{

      $items =  $items->whereDate('bookings.booking_date',$date);

    }

    $items =  $items->where('branch_id',auth()->user()->branch_id)->groupBy('booking_items.menu_id')->get()->toArray();

    $result['items'] = $items;
    return $result;

}

public static function checkDay($date){

    $result = Bookings::select(\DB::raw('COUNT(id) as unpaid'))
    ->whereDate('bookings.booking_date',$date)
    ->where('status',1)
    ->whereNot('payment',2)
    ->whereNot('seat_id',0)
    ->whereNotNull('seat_id')
    ->first();
    return $result->unpaid;

}

public static function bestselling($date=null){



  $items = Bookings::select(\DB::raw('SUM(booking_items.amount) as sales,COUNT(booking_items.id) as quantity,booking_items.item_id as item_id,booking_items.item_name as item_name'))
  ->join('booking_items','bookings.id','booking_items.booking_id');

    if($date){

      $daterange = explode("--",$date);

        if(isset($daterange[1])){

          $items =  $items->whereBetween(\DB::raw('DATE(bookings.booking_date)'),[$daterange[0],$daterange[1]]);

        }
    }

  $items =  $items->where('branch_id',auth()->user()->branch_id)->orderBy('quantity','desc')->groupBy('booking_items.item_id')->get();

  return $items;


}

}
