<?php
namespace App\Models;
use Auth;
use Illuminate\Database\Eloquent\Model;
class User_Model extends Model
{
	protected $table = 'user';
	public static $tcode=false;
	public $user_table = "user";
	public $user_settings_table = "user_settings";
	public $user_types_table = "user_type";
	public $my_teammates_table = "my_teammates";
	public $schedules_table = "schedules";
	public $schedule_days_table = "schedule_days";
	public $coupon_table = "coupon";
	public $campaigns_table = "campaigns";
	public $coupon_clicks_table = "coupon_clicks";
	public $reservation_clicks_table = "reservation_clicks";
	
	const CREATED_AT = 'date_at';
	const UPDATED_AT = 'date_up';

	public static function tcode() {
		self::$tcode=true;
        return new static;
	}


	public function getLocationPackages($uid = '') {
		if($uid) { $uid = Auth::id();}
		$rows = \DB::table('location_packages as lp')
			    ->where('lp.uid',$uid)
			    ->get();
		if(count($rows)>0)return $rows;
		return false;
	}
	
	public function getActiveBalance($uid = '') {
		if($uid) { $uid = Auth::id();}
		$rows = \DB::table('user_transactions as ut')
			    ->where('ut.uid',$uid)
			    ->where('ut.status',1)
			    ->first(array(\DB::raw("SUM(amount) as balance")));
		if($rows)return $rows;
		return false;
	}

	public function getTotalBalance($uid = '') {
		if($uid) { $uid = Auth::id();}
		$rows = \DB::table('user_transactions as ut')
			    ->where('ut.uid',$uid)
			    ->first(array(\DB::raw("SUM(amount) as balance")));
		if($rows)return $rows->balance;
		else return 0;
	}

	public function getAvailableBalance($uid = '') {
		if($uid) { $uid = Auth::id();}
		$rows = \DB::table('user_transactions as ut')
			    ->where('ut.uid',$uid)
			    ->where('ut.status',1)
			    ->first(array(\DB::raw("SUM(amount) as balance")));
		if($rows)return $rows->balance;
		else return 0;
	}
	
	public function get_transactions_by_user($uid = '') {
		if($uid) { $uid = Auth::id();}
		$rows = \DB::table('user_transactions')
			    ->where('uid',$uid)
			    ->get();
		if($rows)return $rows;
		return false;
	}
	
	public function get_transactions_by_user_status_1($uid = '') {
		if($uid) { $uid = Auth::id();}
		$rows = \DB::table('user_transactions')
			    ->where('uid',$uid)
			    ->where('status',1)
			    ->get();
		if($rows)return $rows;
		return false;
	}
	
	public function getPendingBalance($uid = '') {
		if($uid) { $uid = Auth::id();}
		$rows = \DB::table('user_transactions as ut')
			    ->where('ut.uid',$uid)
			    ->where('ut.status',0)
			    ->first(array(\DB::raw("SUM(amount) as balance")));
		if($rows)return $rows;
		return false;
	}
	
	public function add_teammate($data) {
		$this->db->insert('my_teammates', $data);
		return $this->db->insert_id();
	}
	
	public function delete_teammate($id) {
		$this->db->delete($this->my_teammates_table, array('member' => $id));
	}
	
	public function get_users_for_team($uid) {
		if($uid) { $uid = Auth::id();}
		$rows = \DB::table($this->user_table . ' as a')
				->leftJoin($this->my_teammates_table . ' as b', 'b.member', '=', 'a.id')
			    ->where('a.id',"<>",$uid)
			    ->where('a.userType',">",3)
			    ->where('b.member',NULL)
			    ->groupBy('a.id')
                ->orderBy('a.lastLogin', 'desc')
                ->limit(10)
			    ->get(array("a.*","b.*", "a.id as user_id"));
		return $rows;
	}
	
	public function get_users_for_team_suggestions($uid) {
		if($uid) { $uid = Auth::id();}
		$rows = \DB::table($this->user_table . ' as a')
				->leftJoin($this->my_teammates_table . ' as b', 'b.member', '=', 'a.id')
			    ->where('a.id',"<>",$uid)
			    ->where('a.userType',">",3)
			    ->where('b.member',NULL)
			    ->groupBy('a.id')
                ->orderBy('a.lastLogin', 'desc')
                ->limit(100)
			    ->get(array("a.*","b.*", "a.id as user_id"));
		return $rows;
	}
	
	public function get_teammates($uid) {
		if($uid) { $uid = Auth::id();}
		$rows = \DB::table($this->user_table . ' as a')
				->leftJoin($this->my_teammates_table . ' as b', 'b.member', '=', 'a.id')
			    ->where('b.owner',$uid)
			    ->groupBy('a.id')
			    ->get(array("a.*","b.*", "a.id as user_id"));
		return $rows;
	}
	
	public function check_if_teammate($id) {
		$this->db->select('a.id, b.*, a.id as user_id');
		$this->db->from($this->user_table . ' as a');
		$this->db->join($this->my_teammates_table . ' as b', 'b.member = a.id','left');
		$this->db->where('b.member', $id);
		$this->db->where('b.owner', user('id'));
		$query = $this->db->get();
		if($query->num_rows())
			return 'yes';
		else
			return 'no';
	}
	
	public function get_schedules($id) {
		$this->db->select('*');
		$this->db->from($this->schedules_table);
		$this->db->where('owner', $id);
		$query = $this->db->get();
		return $query->result();
	}
	
	public function get_schedule_days_0($id) {
		$this->db->select('*');
		$this->db->from($this->schedule_days_table);
		$this->db->where('schedule', $id);
		$query = $this->db->get();
		return $query->result();
	}
	
	public function get_schedule_days($id) {
		$this->db->select('a.*, b.*');
		$this->db->from($this->schedule_days_table . ' as a');
		$this->db->join($this->schedules_table . ' as b', 'b.id = a.schedule','left');
		$this->db->where('b.id', $id);
		$query = $this->db->get();
		return $query->result();
	}
	
	public function get_user_activity($id) {
		$this->db->select('*');
		$this->db->from('activity_logs');
		$this->db->where('owner', $id);
		$this->db->order_by('date_added', 'desc');
		$query = $this->db->get();
		return $query->result();
	}
	
	public function get_user_activity_latest($id) {
		$this->db->select('*');
		$this->db->from('activity_logs');
		$this->db->where('owner', $id);
		$this->db->where('reference >', 0);
		$this->db->where('activity_type IS NOT NULL', NULL);
		$this->db->order_by('date_added', 'desc');
		$this->db->limit('100');
		$query = $this->db->get();
		return $query->result();
	}
	
	public function get_coupon_details($id) {
		$this->db->select('*, id as activity_id, coupon_name as name');
		$this->db->from('coupon');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function get_product_details($id) {
		$this->db->select('*, id as activity_id, product_name as name');
		$this->db->from('products');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function get_reservation_details($id) {
		$this->db->select('a.*, a.id as activity_id, a.description as activity_description');
		$this->db->from('reservations a');
		$this->db->where('a.id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function get_campaign_details($id) {
		$this->db->select('*, id as activity_id');
		$this->db->from('campaigns');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function get_location_package_details($id) {
		$this->db->select('*, id as activity_id, title as name');
		$this->db->from('location_packages');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function get_location_details($id) {
		$this->db->select('a.*, a.id as activity_id, a.description as activity_description, b.image');
		$this->db->from('locations a');
		$this->db->join('location_to_images as b', 'b.locationId = a.id','left');
		$this->db->where('a.id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function get_coupon_clicks_details($id) {
		$this->db->select('a.id, b.coupon_name as transaction_name, c.firstName as first_name, c.lastName as last_name');
		$this->db->from('coupon_clicks a');
		$this->db->join('coupon as b', 'b.id = a.coupon','left');
		$this->db->join('user as c', 'c.id = a.app_user_id','left');
		$this->db->where('a.id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function get_reservation_request_details($id) {
		$this->db->select('a.id, c.firstName as first_name, c.lastName as last_name, c.firstName as first_name, c.lastName as last_name, d.name as location_name');
		$this->db->from('reservation_clicks a');
		$this->db->join('reservations as b', 'b.id = a.reservation','left');
		$this->db->join('user as c', 'c.id = a.app_user_id','left');
		$this->db->join('locations as d', 'd.id = a.location','left');
		$this->db->where('a.id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function count_coupon_clicks_by_owner($id) {
	
		$this->db->select('id');
		$this->db->from($this->coupon_clicks_table);
		$this->db->where('coupon_owner', $id);
		$query = $this->db->get();
		return $query->num_rows();
	}
	
	public function count_reservation_clicks_by_owner($id) {
		$this->db->select('id');
		$this->db->from($this->reservation_clicks_table);
		$this->db->where('reservation_owner', $id);
		$query = $this->db->get();
		return $query->num_rows();
	}
	
	public function get_user_info($id) {
		$this->db->select('*');
		$this->db->from($this->user_table);
		$this->db->where('id', $id);
		$this->db->limit('1');
		$query = $this->db->get();
		return $query->row();
	}
	
	public function add_activity_log($activity){
		$this->db->insert('activity_logs', $activity);
	}
	
	public function check_if_user_exist($id) {
		$this->db->select('id');
		$this->db->from('user');
		$this->db->where('id', $id);
		$this->db->limit('1');
		$query = $this->db->get();
		if($query->num_rows()) 
			{return true;}
	}
	
	public function get_coupon_by_coupon_code($code) {
		$this->db->select('a.id as coupon, a.barcode, a.max_use_amount, a.total_coupon_available, b.id as campaign, b.uid as coupon_owner');
		$this->db->from('coupon a');
		$this->db->join('campaigns as b', 'b.id = a.campaign','inner');
		$this->db->where('a.barcode', $code);
		$this->db->limit('1');
		$query = $this->db->get();
		return $query->row();
	}
	
	public function count_coupon_used_by_user($code,$coupon_user) {
		$this->db->select('count(a.id) as total_coupon_used, a.coupon, c.id as coupon_user');
		$this->db->from('coupon_clicks a');
		$this->db->join('coupon as b', 'b.id = a.coupon','inner');
		$this->db->join('user as c', 'c.id = a.coupon_owner','inner');
		$this->db->where('b.barcode', $code);
		$this->db->where('a.app_user_id', $coupon_user);
		$query = $this->db->get();
		$row = $query->row_array();
		return $row['total_coupon_used'];
	}
	
	public function check_user_balance($uid) {
		$this->db->select('SUM(amount) as balance');
		$this->db->from('user_transactions ut');
		$this->db->where('ut.uid', $uid, FALSE);
		$this->db->where('ut.status', '1', FALSE);
		$query = $this->db->get();
		
		if($query->num_rows()) return $query->row();
		return false;
	}

	public function getCampaignPrimaryImage($campaignId) {
		$this->db->select('b.image, b.title');
		$this->db->from('campaign_images a');
		$this->db->join('images b', 'b.id = a.image_id', 'inner');
		$this->db->join('user c', 'c.id = a.user_id', 'inner');
		$this->db->where('a.campaign_id', $campaignId);
		$this->db->where('b.status','1');
		$this->db->limit('1');
		$query = $this->db->get();
		if($query->num_rows()) return $query->row();
		return false;
	}

	public function getLocationPrimaryImage($locationId) {
		$this->db->select('b.image, b.title');
		$this->db->from('location_images a');
		$this->db->join('images b', 'b.id = a.image_id', 'inner');
		$this->db->join('user c', 'c.id = a.user_id', 'inner');
		$this->db->where('a.location_id', $locationId);
		$this->db->where('b.status','1');
		$this->db->limit('1');
		$query = $this->db->get();
		if($query->num_rows()) return $query->row();
		return false;
	}

	public function getProductPrimaryImage($productId) {
		$this->db->select('b.image, b.title');
		$this->db->from('product_images a');
		$this->db->join('images b', 'b.id = a.image_id', 'inner');
		$this->db->join('user c', 'c.id = a.user_id', 'inner');
		$this->db->where('a.product_id', $productId);
		$this->db->where('b.status','1');
		$this->db->limit('1');
		$query = $this->db->get();
		if($query->num_rows()) return $query->row();
		return false;
	}

	public function getCouponPrimaryImage($couponId) {
		$this->db->select('b.image, b.title');
		$this->db->from('coupon_images a');
		$this->db->join('images b', 'b.id = a.image_id', 'inner');
		$this->db->join('user c', 'c.id = a.user_id', 'inner');
		$this->db->where('a.coupon_id', $couponId);
		$this->db->where('b.status','1');
		$this->db->limit('1');
		$query = $this->db->get();
		if($query->num_rows()) return $query->row();
		return false;
	}

	public function getReservationPrimaryImage($reservationId) {
		$this->db->select('b.image, b.title');
		$this->db->from('reservation_images a');
		$this->db->join('images b', 'b.id = a.image_id', 'inner');
		$this->db->join('user c', 'c.id = a.user_id', 'inner');
		$this->db->where('a.reservation_id', $reservationId);
		$this->db->where('b.status','1');
		$this->db->limit('1');
		$query = $this->db->get();
		if($query->num_rows()) return $query->row();
		return false;
	}

	public function countCouponClicksByUser($uid) {
		if($uid) { $uid = Auth::id();}
		$rows = \DB::table('coupon as a')
				->join('coupon_clicks as b', 'b.coupon', '=', 'a.id')
				->leftJoin('campaigns as c', 'c.id', '=', 'a.campaign')
			    ->where('b.app_user_id',$uid)
			    ->groupBy('b.id')
			    ->count();
		return $rows;
	}

	public function countProductOrdersByUser($uid) {
		if($uid) { $uid = Auth::id();}
		$rows = \DB::table('products as a')
				->join('orders as b', 'b.uid', '=', 'a.user')
				->leftJoin('campaigns as c', 'c.id', '=', 'a.campaign')
			    ->where('b.uid',$uid)
			    ->groupBy('b.id')
			    ->count();
		return $rows;
	}

	public function countReservationRequestsByUser($uid) {
		if($uid) { $uid = Auth::id();}
		$rows = \DB::table('reservations as a')
				->join('reservation_clicks as b', 'b.reservation', '=', 'a.id')
				->leftJoin('campaigns as c', 'c.id', '=', 'a.campaign')
			    ->where('b.app_user_id',$uid)
			    ->groupBy('b.id')
			    ->count();
		return $rows;
	}

	public function getReservationsRequestsByUser($userId) {
		$this->db->select('a.*, b.*, a.id as request_id, c.id as location_id, c.name as location_name, a.status as request_status, d.firstName as first_name, d.lastName as last_name', false);
		$this->db->from('reservation_requests a');
		$this->db->join('reservations b', 'b.id = a.reservation_id', 'inner');
		$this->db->join('locations c', 'c.id = a.location_clicked', 'left');
		$this->db->join('user d', 'd.id = a.owner_id', 'inner');
		$this->db->where('a.user_id', $userId);
		$query = $this->db->get();
		if($query->num_rows()) return $query->result();
		return false;
	}

	public function getUser($id) {
		$this->db->select('*');
		$this->db->from('user');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}

	public function getUserInfo($id) {
		$this->db->select('id, firstName,lastName');
		$this->db->from('user');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}
	
	public function getUserSettings($userId) {
		$this->db->select('*');
		$this->db->from($this->user_settings_table);
		$this->db->where('user_id', $userId);
		$this->db->limit('1');
		$query = $this->db->get();
		return $query->row();
	}

	public function getUserByEmail($email) {
		$this->db->select('*');
		$this->db->from($this->user_table);
		$this->db->where('email', $email);
		$this->db->limit('1');
		$query = $this->db->get();
		return $query->row();
	}

	public function insertUser($data)
	{
		$this->db->insert($this->user_table, $data);
		return $this->db->insert_id();
	}

	public function updateUser($userId, $data){
		if(!$userId) { return false; }
		$this->db->where('id', $userId);
		return $this->db->update($this->user_table, $data); 
	}

	public function getUserAddress($userId) {
		$this->db->select('a.*, c.name as country_name, c.iso_code_2 as country_code, c.iso_code_3 as country_code_3, d.name as province, d.code as state_code');
		$this->db->from('address a');
		$this->db->join('user b', 'b.id = a.userId','inner');
		$this->db->join('country c', 'c.country_id = a.countryId','left');
		$this->db->join('province d', 'd.zone_id = a.provinceId','left');
		$this->db->where('a.userId', $userId);
		$this->db->limit(1); 
		$query = $this->db->get();
		return $query->row();
	}

	public function addUserSettings($settingsData) {
		$this->db->insert('user_settings', $settingsData);
		return $this->db->insert_id();
	}
	
	public function updateUserSettings($userId,$settingsData) {
		$this->db->where('user_id',$userId);
		$this->db->update("user_settings",$settingsData); 
	}

	public function checkIfOldPasswordMatch($id, $password) {
		$this->db->select('*');
		$this->db->from($this->user_table);
		$this->db->where('id', $id);
		$this->db->where('userType >', '1');
		$this->db->where('userType <', '9');
		$this->db->where('password', md5($password));
		$this->db->limit(1); 
		$query = $this->db->get();
		if($query->num_rows() == 1) {
			return true;
		}
	}

	public function addBillingInfo($billingData) {
		$this->db->insert('billing_information', $billingData);
		return $this->db->insert_id();
	}
	
	public function updateBillingInfo($billingInfoId,$billingData) {
		$this->db->where('id',$billingInfoId);
		$this->db->update("billing_information",$billingData); 
	}
	
	public function updateBillingInfoById($userId,$billingData) {
		$this->db->where('user_id',$userId);
		$this->db->update("billing_information",$billingData); 
	}

	public function getBillingInfo($userId) {
		$this->db->select('a.*, c.name as country_name, c.iso_code_2 as country_code, c.iso_code_3 as country_code_3, d.name as province, d.code as state_code');
		$this->db->from('billing_information a');
		$this->db->join('user b', 'b.id = a.user_id','inner');
		$this->db->join('country c', 'c.country_id = a.country','left');
		$this->db->join('province d', 'd.zone_id = a.state','left');
		$this->db->where('a.user_id', $userId);
		$this->db->limit(1); 
		$query = $this->db->get();
		return $query->row();
	}
	
	public function getUserTypes() {
		$query = $this->db->get($this->user_types_table);
		return $query->result();
	}
	
	public function getUserTypeById($typeId) {
		$query = $this->db->get_where($this->user_types_table, array('id' => $typeId));
		if($query->num_rows()) return $query->row();
		return false;
	}
	
	public function getShippingInfo($userId) {
		$this->db->select('a.*, a.id as user_id');
		$this->db->from('shipping_information a');
		$this->db->join('user b', 'b.id = a.user_id','inner');
		$this->db->where('a.user_id', $userId);
		$this->db->limit(1); 
		$query = $this->db->get();
		return $query->row();
	}
	
	public static function getCheckoutBillingInfo($orderId) {
		$cms = Cms::table("billing_information_checkout a");
		if(!self::$tcode) 
				$cms=$cms->where('a.txn_id', $orderId);
		else  	$cms=$cms->where(DBrawX("a.txn_id"), "?")
						 ->whereValues(array($orderId));
		return $cms->first('a.*');
	}

	public static function getCheckoutShippingInfo($orderId) {
		$cms = Cms::table("shipping_information_checkout a");
		if(!self::$tcode) 
				$cms=$cms->where('a.order_id', $orderId);
		else  	$cms=$cms->where(DBrawX("a.txn_id"), "?")
						 ->whereValues(array($orderId));
		return $cms->first('a.*');
	}

	public static function getOrderShippingInformation($orderId) {
		$cms = Cms::table("product_shipping_information a");
		if(!self::$tcode) 
				$cms=$cms->where('a.order_id', $orderId);
		else  	$cms=$cms->where(DBrawX("a.txn_id"), "?")
						 ->whereValues(array($orderId));
		return $cms->first('a.*');
	}
	
	public function getAuthPassword()
	{
	  return $this->r_password;
	}
}
