<?php

namespace app\Helpers;
use App\Models\Sales\Ticket;
use App\Models\Facility\Facility;
use App\Models\Sales\Invoice;
use App\Models\Sales\TicketSeat;
use App\Models\Settings\Configuration;
use App\Models\Sales\ShowCancel;
use App\Models\Sales\Transaction;
use PDF;
use Mail;
use DB;

class BookingClass {

    function __construct() {

    }

    

    public function getAvailableSeats($facility_id, $show_id, $show_date){
       
       
       DB::statement(DB::raw('LOCK TABLES ticket WRITE'));

       //get available seats of a show
        $totaladultcount = Ticket::where('facility_id',$facility_id)->where('show_date',$show_date)->where('show_time_id',$show_id)->where('status',1)->sum('noof_adult');
        $totalchildcount = Ticket::where('facility_id',$facility_id)->where('show_date',$show_date)->where('show_time_id',$show_id)->where('status',1)->sum('noof_child');

        $totalusedseats = $totaladultcount + $totalchildcount;

         DB::statement(DB::raw('LOCK TABLES facility WRITE'));
        $facility = Facility::where('id',$facility_id)->where('active_flag',1)->first();
        $totalseats = $facility->noofseats;

        $seatsavailable = $totalseats - $totalusedseats;

        // blocked seats
        $time = time();
        DB::statement(DB::raw('LOCK TABLES configuration_settings WRITE'));
        $configuration = Configuration::where('id',1)->first();
        $ticket_time_out = $configuration->ticket_time_out+1;
        $time = $time - ($ticket_time_out*60);
        $checktime = date("Y-m-d H:i:s", $time);
        DB::statement(DB::raw('LOCK TABLES ticket WRITE'));
        $totaladultcount = Ticket::where('facility_id',$facility_id)->where('show_date',$show_date)->where('show_time_id',$show_id)->where('status',0)->where('created_at','>=',$checktime)->sum('noof_adult');
        $totalchildcount = Ticket::where('facility_id',$facility_id)->where('show_date',$show_date)->where('show_time_id',$show_id)->where('status',0)->where('created_at','>=',$checktime)->sum('noof_child');
        $totalblockedseats = $totaladultcount + $totalchildcount;
        $seatsavailable = $seatsavailable - $totalblockedseats;


        //todo check cancel show to check if show is cancelled



        DB::statement(DB::raw('UNLOCK TABLES'));
        return $seatsavailable;


    }

    public function getBookedSeats($facility_id, $show_id, $show_date)
    {
      $seats = [];
      //get booked seats from ticket seat of facility 
      

      $ticketseat = TicketSeat::where('show_time_id',$show_id)->where('status',1)->whereHas('ticket', function($q) use($facility_id, $show_date){
              $q->where('facility_id',$facility_id)->where('show_date',$show_date);
          })->get();


      foreach($ticketseat as $seat)
      {

         $seats[] = $seat->seat_id;
      }

      //get blocked seat
      $time = time();
      
      $configuration = Configuration::where('id',1)->first();
      $ticket_time_out = $configuration->ticket_time_out+1;
      $time = $time - ($ticket_time_out*60);
      $checktime = date("Y-m-d H:i:s", $time);
   
      $ticketseat = TicketSeat::where('show_time_id',$show_id)->where('status',0)->whereHas('ticket', function($q) use($facility_id, $show_date, $checktime){
              $q->where('facility_id',$facility_id)->where('show_date',$show_date)->where('created_at','>=',$checktime);
          })->get();
      foreach($ticketseat as $seat)
      {

         $seats[] = $seat->seat_id;
      }

      //get blocked seats where in transaction status is null which is in progress
      $ticketseat = TicketSeat::where('show_time_id',$show_id)->where('status',0)->whereNotIn('seat_id',$seats)->whereHas('ticket', function($q) use($facility_id, $show_date, $checktime){
              $q->where('facility_id',$facility_id)->where('show_date',$show_date);
          })->get();
      foreach($ticketseat as $seat)
      {
        $checkseat_id = $seat->seat_id;
        $ticket_id = $seat->ticket_id;
        //check if the transaction corresponds to this seat is having status null
        $checkseat = $this->checkseattransstatusnull($ticket_id);
        if($checkseat==1)
          $seats[] = $seat->seat_id;
      }

    
      return $seats;

    }

    public function getBookedSeats2($facility_id, $show_id, $show_date)
    {
      $seats = [];
      //get booked seats from ticket seat of facility 
      

      $ticketseat = TicketSeat::where('show_time_id',$show_id)->where('status',1)->whereHas('ticket', function($q) use($facility_id, $show_date){
              $q->where('facility_id',$facility_id)->where('show_date',$show_date);
          })->get();


      foreach($ticketseat as $seat)
      {

         $seats[] = $seat->seat_id;
      }

      //get blocked seat
      $time = time();
      
      $configuration = Configuration::where('id',1)->first();
      $ticket_time_out = $configuration->ticket_time_out+1;
      $time = $time - ($ticket_time_out*60);
      $checktime = date("Y-m-d H:i:s", $time);
   
      $ticketseat = TicketSeat::where('show_time_id',$show_id)->where('status',0)->whereHas('ticket', function($q) use($facility_id, $show_date, $checktime){
              $q->where('facility_id',$facility_id)->where('show_date',$show_date)->where('created_at','>=',$checktime);
          })->get();
      foreach($ticketseat as $seat)
      {

         $seats[] = $seat->seat_id;
      }

      //get blocked seats where in transaction status is null which is in progress
      $ticketseat = TicketSeat::where('show_time_id',$show_id)->where('status',0)->whereNotIn('seat_id',$seats)->whereHas('ticket', function($q) use($facility_id, $show_date, $checktime){
              $q->where('facility_id',$facility_id)->where('show_date',$show_date);
          })->get();

      foreach($ticketseat as $seat)
      {
        $checkseat_id = $seat->seat_id;
        $ticket_id = $seat->ticket_id;
        //check if the transaction corresponds to this seat is having status null
        $checkseat = $this->checkseattransstatusnull($ticket_id);
        if($checkseat==1)
          $seats[] = $seat->seat_id;
      }

    
      return $seats;

    }

    public function checkseattransstatusnull($ticket_id)
    {
      //get invoice number corresponding to ticket_id
      DB::statement(DB::raw('LOCK TABLES ticket WRITE'));
      $ticketobj = Ticket::where('id',$ticket_id)->first();
      $invoice_id = $ticketobj->invoice_id;

      DB::statement(DB::raw('LOCK TABLES invoice WRITE'));
      $invoice = Invoice::where('id',$invoice_id)->first();
      if(isset($invoice))
      {
        //if invoice is cancelled return 0
        $status = $invoice->status;
        if($status==2)
          return 0;
        $transaction_id = $invoice->transaction_id;
        //get transaction status
        DB::statement(DB::raw('LOCK TABLES transaction WRITE'));
        $transobj = Transaction::where('id',$transaction_id)->first();
        if(isset($transobj))
        {
          $trans_status = $transobj->status_code;
          if($trans_status)
          {
            if($trans_status=='S')
            {
              DB::statement(DB::raw('UNLOCK TABLES'));
              return 1;
            }
            else
            {
              DB::statement(DB::raw('UNLOCK TABLES'));
              return 0;
            }
          }
          else
          {
            DB::statement(DB::raw('UNLOCK TABLES'));
            return 1;
          }
        }
        else
        {
          DB::statement(DB::raw('UNLOCK TABLES'));
          return 0;
        }
      }
      else
      {
        DB::statement(DB::raw('UNLOCK TABLES'));
        return 0;
      }
    }

    public function getnewInvoice($organisation_id){
      DB::statement(DB::raw('LOCK TABLES invoice WRITE'));
      //get min date and max date
      $year = $this->getCurrentYear();
      $firstday = $this->getfirstDate($year);
      $lastday = $this->getlastDate($year);

      $maxinvoiceno = Invoice::where('organisation_id',$organisation_id)->where('invoice_date','>=',$firstday)->where('invoice_date','<=',$lastday)->max('invoice_no');

      $invoicearr=[];
      $invoicearr[0] = $maxinvoiceno+1;
      $invoicearr[1] = date('Y-m-d H:i:s');
      DB::statement(DB::raw('UNLOCK TABLES'));
      return $invoicearr;
    }
    public function getnewTicket($facility_id){
      DB::statement(DB::raw('LOCK TABLES ticket WRITE'));
      //get min date and max date
      $year = $this->getCurrentYear();
      $firstday = $this->getfirstDate($year);
      $lastday = $this->getlastDate($year);

      $maxticketno = Ticket::where('facility_id',$facility_id)->where('show_date','>=',$firstday)->where('show_date','<=',$lastday)->max('ticket_no');
      DB::statement(DB::raw('UNLOCK TABLES'));

      return $maxticketno+1;

    }

    public function getCurrentYear()
    {
      if ( date('m') >= 4 ) {
          $year = date('Y');
      }
      else {
          $year = date('Y')-1;
      }
      return $year;
    }

    public function getfirstDate($year)
    {
      return $year."-04-01";
    }
    public function getlastDate($year)
    {
      $nextyear = $year+1;
      return $nextyear."-03-31";
    }

    public function mailTicket($email,$invoice_id)
    {
      //mail invoice to the email specified

      //generate invoice_pdf
      $invoice = Invoice::where('id',$invoice_id)->where('status',1)->with('organisation','tickets')->first();
      $invoice_pdf = PDF::loadView('tickets.invoicepdf', compact('invoice'));
      return $invoice_pdf->output();

      


    }
}
?>