<?php

namespace App;

use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
class Buyer_document extends Authenticatable
{
    use Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'client_id','document','from_lang','to_lang','from_page','to_page','type_of_document','ip','status'
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'remember_token','word_count'
    ];


    public static function getUploadDocuments($userId , $page ,$date)
    {
      $page = $page ;
      $id = $userId ;
      $date = $date ;
      $documents = Buyer_document::
      join('languages as l1','l1.id','buyer_documents.from_lang') 
      ->join('languages as l2','l2.id','buyer_documents.to_lang')
      ->join('translation_expertise','translation_expertise.id','buyer_documents.type_of_document')
      ->leftjoin('cancelations','cancelations.document_id','buyer_documents.id')
      ->leftjoin('bids', function ($join) 
        {
          $join->on('bids.document_id', '=', 'buyer_documents.id');  
        })
      ->select('buyer_documents.*','l1.name as from_lang','l2.name as to_lang','cancelations.id as cancel_id', 
        DB::raw("count(bids.id) as bidsCount") , 
        DB::raw("DATE_FORMAT(buyer_documents.created_at, '%d %b %Y') as created_at_dmy"))

      ->where(["buyer_documents.client_id"=>$id,"buyer_documents.status"=>1,array('buyer_documents.created_at','>=',date($date))])
      ;

      $documents = $documents->groupBy("buyer_documents.id")
      ->orderBy('buyer_documents.updated_at', 'desc')->offset(( $page*10 ))
      ->limit(10)->get();  //updated_at

      return $documents ;
    }

    public static function getCompletedDocuments($userId , $page)
    {
      $id = $userId  ;
      $documents = Buyer_document::
      join('languages as l1','l1.id','buyer_documents.from_lang') 
      ->join('languages as l2','l2.id','buyer_documents.to_lang')
      ->leftjoin('cancelations','cancelations.document_id','buyer_documents.id')
      ->leftjoin('giveworks','giveworks.document_id','buyer_documents.id')
      ->leftjoin('translation_expertise','translation_expertise.id','buyer_documents.type_of_document')
      ->leftjoin('complete_statuses','complete_statuses.document_id','buyer_documents.id')
      ->leftjoin('bids', function ($join) 
        {
          $join->on('bids.document_id', '=', 'buyer_documents.id')
              ->where('bids.accept', '=',1);  
        })
      ->leftjoin('document_translates', function ($join) 
        {
          $join->on('document_translates.document_id', '=', 'buyer_documents.id')
              ->where('document_translates.box_id', '>',0);  
        })
      ->join('translators_infos', function ($join) 
        {
          $join->on('translators_infos.translator_id', '=', 'bids.translator_id')
              ->on('translators_infos.first_lang', '=', 'buyer_documents.from_lang')
              ->on('translators_infos.second_lang', '=', 'buyer_documents.to_lang') ;  
      })
      ->leftjoin('translators','translators.id',
        'giveworks.translator_id')
      ->leftjoin('countries','countries.phonecode','translators.dial_code')
      ->select('buyer_documents.*','translation_expertise.name as doc_category','buyer_documents.word_count as wcount',
        'giveworks.assured','giveworks.translator_id','l1.name as from_lang','l2.name as to_lang','cancelations.id as cancel_id',
        'translators_infos.rating','translators_infos.price',
        'countries.sortname','document_translates.box_id as box_id_complited',
        DB::raw("DATE_FORMAT(giveworks.started_date, '%d %b %Y') as started_date"),
        'complete_statuses.ratting as complited_ratting',

        DB::raw("DATE_FORMAT(complete_statuses.created_at, '%d %b %Y') as complete_date"))
      ->where(["buyer_documents.client_id"=>$id,"buyer_documents.status"=>3])
      ;

      $documents = $documents->groupBy("buyer_documents.id")
      ->orderBy('buyer_documents.updated_at', 'desc')->offset(( $page*10 ))
      ->limit(10)->get();




      $data = [];
      foreach ($documents as  $value) 
      {

        $bid_price = 0 ;
        
        if( $value['rating']  <= 2.99 )
        {
          $bid_price =  0.10 + ( $value['price'] - 0.05 ) * 2 ;
        }
        else if($value['rating'] >= 3 && $value['rating'] <= 4 )
        {
          $bid_price =  0.22 + ( $value['price']  - 0.10 ) * 1.5 ;
        }
        else if( $value['rating'] > 4  )
        {
          $bid_price =  0.35 + ( $value['price'] - 0.15 ) * 1.5 ;
        }
        if( $value['assured'] == "Y" )
        {
          $bid_price = $bid_price + 0.25 ;
        } 


        $value['pricePerWord'] = $bid_price ;
        $value['price'] = $bid_price * $value['wcount'] ;
        $data[] = $value ;
       } 
      return $data ;
    }



    public static function getStartedDocuments($userId , $page)
    {
      $id = $userId ;
      $documents = Buyer_document::
      join('languages as l1','l1.id','buyer_documents.from_lang') 
      ->join('languages as l2','l2.id','buyer_documents.to_lang')
      ->leftjoin('cancelations','cancelations.document_id','buyer_documents.id')
      ->leftjoin('giveworks','giveworks.document_id','buyer_documents.id')
      ->leftjoin('translation_expertise','translation_expertise.id','buyer_documents.type_of_document')
      ->leftjoin('bids', function ($join) 
        {
          $join->on('bids.document_id', '=', 'buyer_documents.id')
              ->where('bids.accept', '=',1);  
        })
      ->join('translators_infos', function ($join) 
        {
          $join->on('translators_infos.translator_id', '=', 'bids.translator_id')
              ->on('translators_infos.first_lang', '=', 'buyer_documents.from_lang')
              ->on('translators_infos.second_lang', '=', 'buyer_documents.to_lang') ;  
      })
      ->leftjoin('translators','translators.id',
        'giveworks.translator_id')
      ->leftjoin('countries','countries.phonecode','translators.dial_code')

      ->select('buyer_documents.*','translation_expertise.name as doc_category','buyer_documents.word_count as wcount','giveworks.assured','l1.name as from_lang','l2.name as to_lang','cancelations.id as cancel_id',
        'translators_infos.rating','translators_infos.price',
        'countries.sortname', 'countries.name as country_name', 
        'translators.fname', 'translators.lname',
        'translators.id as translator_id',     
        'bids.delivery_time','bids.time','bids.total_time',
        DB::raw("DATE_FORMAT(giveworks.started_date, '%d %b %Y') as started_date"))
      ->where(["buyer_documents.client_id"=>$id,"buyer_documents.status"=>2])
      ;

      $documents = $documents->groupBy("buyer_documents.id")
      ->orderBy('buyer_documents.updated_at', 'desc')->offset(( $page*10 ))
      ->limit(10)->get();

      $data = [];
      foreach ($documents as  $value) 
      {
        // $chatCouns = DB::table('chat')
        // //->select(DB::raw('count(chat.id) as chat_count'))
        // ->where('buyer_documents_id', '=', $value['id'])
        // ->where('to', '=', $id)
        // ->where('recd', '=', '0')
        // ->where('send_from', '=', 't')
        // //->groupBy('chat.buyer_documents_id')
        // ->get();

        $notification=DB::select('select count(id) as cnt from chat where id >110  and buyer_documents_id="'.$value['id'].'" and id not in(select chat_id from chat_notification_status where user_id="'.$id.'" and document_id="'.$value['id'].'" and user_type="c")');
        
        $value['chat_count'] = 0 ;
        if( $notification )
        {
           $value['chat_count'] = $notification[0]->cnt ;
        }
        
        //$chatCouns[0]['chat_count'];

        $bid_price = 0 ;
        
        if( $value['rating']  <= 2.99 )
        {
          $bid_price =  0.10 + ( $value['price'] - 0.05 ) * 2 ;
        }
        else if($value['rating'] >= 3 && $value['rating'] <= 4 )
        {
          $bid_price =  0.22 + ( $value['price']  - 0.10 ) * 1.5 ;
        }
        else if( $value['rating'] > 4  )
        {
          $bid_price =  0.35 + ( $value['price'] - 0.15 ) * 1.5 ;
        }
        if( $value['assured'] == "Y" )
        {
          $bid_price = $bid_price + 0.25 ;
        } 


        $timeLeft = "00:00";
        $delayed = "false";
        $progress = 1 ;
        $timeleftSecond = strtotime( $value['delivery_time'] ."".
          $value['time']) - strtotime(date("Y-m-d H:i:s")) ;

        if( $timeleftSecond > 0  )
        {
          $hours = floor($timeleftSecond / 3600);
          $minutes = floor(($timeleftSecond / 60) % 60);
          $timeLeft = $hours.":".$minutes;
          $total_time_second = $value['total_time'] * 60 * 60 ;
          $progress = ( $total_time_second - $timeleftSecond ) / $total_time_second ;
        }
        else
        {
          $timeleftSecond = $timeleftSecond * (-1 );
          $hours = floor($timeleftSecond / 3600);
          $minutes = floor(($timeleftSecond / 60) % 60);
          $timeLeft = $hours.":".$minutes;
          $delayed = "true";
        }
            
        $value['pricePerWord'] = $bid_price ;
        $value['price'] = $bid_price * $value['wcount'] ;
        $value['timeLeft'] = $timeLeft ;
        $value['delayed'] = $delayed ;
        $value['progress'] = $progress ;
        $data[] = $value ;
       } 
      return $data ;
    }

    public static function getChats($userId , $page)
    {
      $id = $userId ;
      $documents = Buyer_document::
      join('languages as l1','l1.id','buyer_documents.from_lang') 
      ->join('languages as l2','l2.id','buyer_documents.to_lang')
      ->leftjoin('cancelations','cancelations.document_id','buyer_documents.id')
      ->leftjoin('giveworks','giveworks.document_id','buyer_documents.id')
      ->leftjoin('translation_expertise','translation_expertise.id','buyer_documents.type_of_document')
      ->leftjoin('bids', function ($join) 
        {
          $join->on('bids.document_id', '=', 'buyer_documents.id')
              ->where('bids.accept', '=',1);  
        })
      ->join('translators_infos', function ($join) 
        {
          $join->on('translators_infos.translator_id', '=', 'bids.translator_id')
              ->on('translators_infos.first_lang', '=', 'buyer_documents.from_lang')
              ->on('translators_infos.second_lang', '=', 'buyer_documents.to_lang') ;  
      })
      ->leftjoin('translators','translators.id',
        'giveworks.translator_id')
      ->leftjoin('countries','countries.phonecode','translators.dial_code')
      ->leftjoin('chat','chat.buyer_documents_id','buyer_documents.id')

      ->select('buyer_documents.*','chat.id as chatId','translation_expertise.name as doc_category','buyer_documents.word_count as wcount','giveworks.assured','l1.name as from_lang','l2.name as to_lang','cancelations.id as cancel_id',
        'translators_infos.rating','translators_infos.price',
        'countries.sortname', 'countries.name as country_name', 
        'translators.fname', 'translators.lname',
        'translators.id as translator_id',     
        'bids.delivery_time','bids.time','bids.total_time',
        DB::raw("DATE_FORMAT(giveworks.started_date, '%d %b %Y') as started_date"))
      ->where(["buyer_documents.client_id"=>$id,"buyer_documents.status"=>2])
      ;

      $documents = $documents->groupBy("buyer_documents.id")
      ->orderBy('chat.sent', 'desc')->offset(( $page*10 ))
      ->limit(10)->get();

      $data = [];
      foreach ($documents as  $value) 
      {

        $notification=DB::select('select count(id) as cnt from chat where id >110  and buyer_documents_id="'.$value['id'].'" and id not in(select chat_id from chat_notification_status where user_id="'.$id.'" and document_id="'.$value['id'].'" and user_type="c")');

        $value['chat_count'] = 0 ;
        if( $notification )
        {
           $value['chat_count'] = $notification[0]->cnt ;
        }
        // $chatCouns = DB::table('chat')
        // //->select(DB::raw('count(chat.id) as chat_count'))
        // ->where('buyer_documents_id', '=', $value['id'])
        // ->where('to', '=', $id)
        // ->where('recd', '=', '0')
        // ->where('send_from', '=', 't')
        // //->groupBy('chat.buyer_documents_id')
        // ->get();
        // $value['chat_count'] = count($chatCouns) ;
        $data[] = $value ;
       } 
      return $data ;
    }
    
}