<?php

class projectModel extends baseModel {
	public $userImageFolder = "/static/images/users/";

	function get($id = 0) {
		if (!is_numeric($id)) {
			return -1;
		}
		$this->db->where('id = ?', $id);
		$project = $this->db->select('projects')[0];

		if ($project) {
			$this->db->where('id = ?', $project['user_id']);
			$project['user'] = $this->db->select('users')[0];

			$this->db->where('user_id=?', $project['user']['id']);
			$project['user']['avg_rating_given'] = round($this->db->select('reviews', 'AVG(rating) AS r')[0]['r'], 1);

			// Added July 6 - Average Proposal Amount Received 	
			$this->db->where('user_id=?', $project['user']['id']);
			$project['user']['avg_proposal_amount'] = round($this->db->select('reviews', 'AVG(amount) AS a')[0]['a'], 1);

			if ($project['user']['image'] && !strstr($project['user']['image'], '//')) {
				$project['user']['image'] = $this->userImageFolder . $project['user']['image'];
			}

			$this->db->where('project_id=?', $id);
			$this->db->where('type=?', 'image');
			$project['images'] = $this->db->select('uploads');

			$this->db->where('project_id=?', $id);
			$this->db->where('type != ?', 'image');
			$project['files'] = $this->db->select('uploads');

			$project['days_to_end'] = round((strtotime($project['timestamp_end']) - time()) / 86400);

			$this->db->where('project_id=?', $id);
			$project['avg_rating'] = round($this->db->select('reviews', 'AVG(rating) AS r')[0]['r']);

			// Added July 6 - Average Proposal Amount Received
			$this->db->where('project_id=?', $id);
			$project['avg_amount'] = round($this->db->select('reviews', 'AVG(amount) AS a')[0]['a']);

			$this->db->where('id=?', $project['category_id']);
			$project['category'] = $this->db->select('categories');

			$this->db->where('project_id=?', $id);
			$project['backing'] = $this->db->select('backing');
			
			$this->db->where('item_id=?', $id);
			$project['backed_amount'] = $this->db->select('backing_history', "SUM(amount) AS amount")[0]['amount'];
			$this->db->where('item_id=?', $id);
			$project['backers'] = $this->db->select('backing_history', "COUNT(*) AS amount")[0]['amount'];
			
			return $project;
		}
	}

	function getFeatured($cond = []) {
		if (isset($cond['category_id'])) {
			$this->db->where('category_id=?', $cond['category_id']);
		}

		$this->db->where('timestamp_end > NOW()');
		$this->db->where('is_featured & ' . FLAG_STAFFPICK);
		$this->db->order_by('RAND()');
		$project = $this->db->select('projects', 'id')[0];

		return $this->get($project['id']);
	}

	function getTotalCount() {
		$this->db->where('timestamp_end > NOW()');
		return $this->db->select('projects', 'COUNT(*) AS c')[0][c];
	}
	
	function setFeaturedFlag($id, $flag) {
		$this->db->query("UPDATE projects SET is_featured = is_featured | $flag WHERE id=$id");
	}
	
	function unsetFeaturedFlag($id, $flag) {
		$this->db->query("UPDATE projects SET is_featured = is_featured & ~$flag WHERE id=$id");
	}

	function update($id, $data) {
		$allowed_fields = ['title','location','location_lat','location_lng', 'description', 'paypal_email', 'category_id', 'reviews_goal', 'timestamp_end', 'is_featured'];
		$update_data = $data;
		
		if (isset($data['duration'])) {
			$data['timestamp_end'] = date('Y-m-d H:i:s', time()+(86400*$data['duration']));
		}
		
		foreach ($update_data as $index => $value) {
			if (!in_array($index, $allowed_fields)) {
				unset($update_data[$index]);
			}
		}
		
		$this->db->where('id=?', $id)->update('projects', $update_data);
		
		$uploadsModel = new uploadsModel();
		if (!empty($data['files'])) {
			foreach ($data['files'] as $file) {
				$file = $uploadsModel->select($file);
				if (!empty($file)) {
					$uploadsModel->update($file['id'], [
						'project_id' => $id
					]);
				}
				if ($file['type'] == 'image' && $main_img == '') {
					$main_img = $file['file'];
				}
			}
		}
		if ($main_img) {
			$this->db->where('id = ?', $id);
			$this->db->update('projects', ['main_img' => $main_img]);
		}

		$this->db->where('project_id=?', $id);
		$this->db->delete('backing');

		foreach ($_POST['back_amount'] as $index => $amount) {
			if ($amount > 0) {
				$this->db->insert('backing', [
					'project_id' => $id,
					'quantity_desired' => $_POST['back_quantity'][$index],
					'amount' => $amount,
					'reward' => htmlspecialchars($_POST['back_reward'][$index]),
					'eta' => htmlspecialchars($_POST['back_eta'][$index])
				]);
			}
		}

		$this->db->where('id=?', $id);
		$project = $this->db->select('projects')[0];
		if (!file_exists($project['main_img'])) {
			$this->db->where('project_id=?', $project_id);
			$this->db->where('type=?', 'image');
			$img = $this->db->select('uploads')[0];

			$this->db->where('id = ?', $id);
			$this->db->update('projects', ['main_img' => $img['file']]);
		}
	}

	function delete($id) {
		$this->db->where('id=?', $id);
		$this->db->delete('projects');

		$this->db->where('project_id=?', $id);
		$this->db->delete('reviews');

		$this->db->where('project_id=?', $id);
		$this->db->delete('uploads');
	}

	function deleteFile($file_id, $project_id) {
		$this->db->where('id=?', $file_id);
		$this->db->where('project_id=?', $project_id);
		$file = $this->db->select('uploads')[0];

		if ($file) {
			unlink($file['file']);
		}

		$this->db->where('id=?', $file_id);
		$this->db->where('project_id=?', $project_id);
		$this->db->delete('uploads');

		$this->db->where('id=?', $project_id);
		$project = $this->db->select('projects')[0];

		if (!file_exists($project['main_img'])) {
			$this->db->where('project_id=?', $project_id);
			$this->db->where('type=?', 'image');
			$img = $this->db->select('uploads')[0];

			$this->db->where('id = ?', $id);
			$this->db->update('projects', ['main_img' => $img['file']]);
		}
	}

	function getRecentReviewers($project_id) {
		$this->db->where('project_id=?', $project_id);
		$this->db->order_by('timestamp DESC');
		$this->db->join('users', 'users.id = reviews.user_id');
		$r = $this->db->select('reviews');

		foreach ($r as $index => $review) {
			if ($review['image'] != '' && !strstr($review['image'], '//')) {
				$r[$index]['image'] = $this->userImageFolder . $review['image'];
			}
		}

		return $r;
	}

	function submitReview($post, $project_id) {
		$this->db->insert('reviews', [
			'project_id' => $project_id,
			'text' => $post['review'],
			'amount' => $post['amount'],
			'user_id' => $_SESSION['user_id']
		]);

		$this->db->where('id=?', $project_id);
		$project = $this->db->select('projects')[0];

		$this->db->insert('notifications', [
			'user_id' => $project['user_id'],
			'from_user_id' => $_SESSION['user_id'],
			'text' => "<a href='/project/{$project['id']}'>{$_SESSION['user']['name']} has submitted a comment/proposal on {$project['title']}</a>"
		]);

		$this->db->query("UPDATE users SET reviews_count=reviews_count+1 WHERE id='{$_SESSION['user_id']}'");
		$this->db->query("UPDATE projects SET reviews_submitted=reviews_submitted+1 WHERE id=$project_id");
	}

	function submitRating($rating, $rating_id) {
		if (!is_numeric($rating) || !is_numeric($rating_id)) {
			return -1;
		}

		$this->db->where('reviews.id=?', $rating_id);
		$this->db->where('projects.user_id=?', $_SESSION['user_id']);
		$this->db->join('projects', 'projects.id=reviews.project_id');
		$review = $this->db->select('reviews', ['*', 'reviews.user_id AS review_user_id'])[0];

		if ($review && $review['rating'] == 0) {
			$this->db->where('id=?', $rating_id);
			$this->db->update('reviews', ['rating' => $rating]);
		}

		$this->db->query("UPDATE users SET credits=credits+$rating WHERE id='{$review['review_user_id']}'");
		$this->db->insert("credits", [
			'user_id' => $review['review_user_id'],
			'amount' => $rating,
			'type' => 'earned'
		]);
	}

	function unlockReview($review_id) {
		global $unlockPrice;

		$this->db->where('id=?', $review_id);
        $review = $this->db->select('reviews')[0];

        $userModel = new userModel();
        $review['user'] = $userModel->getUser($review['user_id']);
        
        if ($review) {
        	$this->db->where('id=?', $review['project_id']);
        	$project = $this->db->select('projects')[0];
        }
        
        if (!$review || !$project || $project['user_id'] != $_SESSION['user_id']) {
        	return -1;
        }
        
        if ($_SESSION['logged_in']) {
        	$this->db->where('review_id = ?', $review_id)
        		->db->where('project_id = ?', $project['id'])
        		->select('reviews_unlocked');
        	
        	if ($this->db->affected_rows() > 0) {
        		$already_unlocked = 1;
        	}
        }

        $user = $userModel->getUser($_SESSION['user_id']);

        if (!isset($already_unlocked) || !$already_unlocked) {
	        if ($user['credits'] < $unlockPrice) {
	        	die("notenough");
	        }
	
			$this->db->query("UPDATE users SET credits=credits-$unlockPrice WHERE id={$_SESSION['user_id']}");
			$this->db->insert("credits", [
				'user_id' => $_SESSION['user_id'],
				'amount' => "-$unlockPrice",
				'type' => 'spent'
			]);
	
	        $this->db->insert('reviews_unlocked', [
	            'review_id' => $review_id,
	            'project_id' => $project['id'],
	        ]);
	
	        if ($this->db->affected_rows() == 1) {
	        	$this->db->query("UPDATE projects SET reviews_unlocked=reviews_unlocked+1 WHERE id={$project['id']}");
	        }
        }

        return $review;
	}

	function getUnlockedReviews($project_id) {
		$this->db->where('user_id=?', $_SESSION['user_id']);
		$this->db->where('id=?', $project_id);
		$p = $this->db->select('projects');

		if (count($p) != 1) {
			return [];
		}

		$this->db->join('reviews', 'reviews.id = reviews_unlocked.review_id');
		$this->db->join('users', 'reviews.user_id = users.id');
		$this->db->where("reviews_unlocked.project_id=?", $project_id);
		$r =  $this->db->select('reviews_unlocked');

		foreach ($r as $index => $review) {
			if ($review['image'] != '' && !strstr($review['image'], '//')) {
				$r[$index]['image'] = $this->userImageFolder . $review['image'];
			}
		}

		return $r;
	}

	function getReviews($project_id) {
		if (!is_numeric($project_id)) {
			return -1;
		}

		$this->db->where('project_id=?', $project_id);
		$this->db->join('users', 'reviews.user_id = users.id');
		$r = $this->db->select('reviews', ['*', 'reviews.id AS review_id']);

		foreach ($r as $index => $review) {
			if ($review['image'] != '' && !strstr($review['image'], '//')) {
				$r[$index]['image'] = "/static/images/users/" . $review['image'];
			}
		}

		return $r;
	}

	function getList($cond = []) {
		if (isset($cond['category_id'])) {
			$this->db->where('category_id=?', $cond['category_id']);
		}
		if (isset($cond['search'])) {
			$this->db->where("description LIKE '%{$cond['search']}%' OR title LIKE '%{$cond['search']}%'", null);
		}
		if (isset($cond['is_featured'])) {
			$this->db->where("is_featured = ?", $cond['is_featured']);
		}
		if (isset($cond['user_id'])) {
			$this->db->where("user_id = ?", $cond['user_id']);
		}

		$this->db->where("timestamp_end > NOW()");

		$projects = $this->db->select('projects');

		foreach ($projects as $index => $row) {
			$this->db->where('id = ?', $row['user_id']);
			$projects[$index]['user'] = $this->db->select('users')[0];

			$projects[$index]['days_to_end'] = round((strtotime($row['timestamp_end']) - time()) / 86400);

			$this->db->where('project_id=?', $row['id']);
			$projects[$index]['avg_rating'] = round($this->db->select('reviews', 'AVG(rating) AS r')[0]['r']);
			
			$this->db->where('item_id=?', $row['id']);
			$projects[$index]['backed_amount'] = $this->db->select('backing_history', "SUM(amount) AS amount")[0]['amount'];
			$this->db->where('item_id=?', $row['id']);
			$projects[$index]['backers'] = $this->db->select('backing_history', "COUNT(*) AS amount")[0]['amount'];

			if (!$projects[$index]['main_img']) {
				$this->db->where('project_id=?', $row['id']);
				$this->db->where('type=?', 'image');
				$projects[$index]['images'] = $this->db->select('uploads');
				$projects[$index]['main_img'] = $projects[$index]['images'][0]['file'];
			}
		}

		return $projects;
	}

	function insert($data) {

		foreach ($data as $index => $value) {
			if (!is_array($value)) {
				if ($index == 'description') {
					// TODO: security
					$data[$index] = str_ireplace('<script>', '', $data[$index]);
				} else {
					if (gettype($value) == 'string') {
						$data[$index] = htmlspecialchars($value);
					}
				}
			}
		}

		$this->db->insert('projects',
			array(
				'title' => $data['title'],
				'description' => $data['description'],
				'paypal_email' => $data['paypal_email'],
				'location'=> $data['location'],
				'location_lat'=> $data['location_lat'],
				'location_lng'=> $data['location_lng'],
				'category_id' => (int)$data['category_id'],
				'reviews_goal' => $data['reviews_goal'],
				'timestamp_end' => date('Y-m-d H:i:s', time()+(86400*$data['duration'])),
				'user_id' => $_SESSION['user_id']
			)
		);

		$id = $this->db->insert_id();

		$this->db->query("UPDATE users SET project_count=project_count+1 WHERE id='{$_SESSION['user_id']}'");

		foreach ($data['back_amount'] as $index => $amount) {
			if ($amount > 0) {
				$this->db->insert('backing', [
					'project_id' => $id,
					'quantity_desired' => $data['back_quantity'][$index],
					'amount' => $amount,
					'reward' => htmlspecialchars($data['back_reward'][$index]),
					'eta' => htmlspecialchars($data['back_eta'][$index])
				]);
			}
		}

		$c = 0;
		$main_img = '';
		$uploadsModel = new uploadsModel();
		if (!empty($data['files'])) {
			foreach ($data['files'] as $file) {
				$file = $uploadsModel->select($file);
				if (!empty($file)) {
					$uploadsModel->update($file['id'], [
						'project_id' => $id
					]);
				}
				if ($file['type'] == 'image' && $main_img == '') {
					$main_img = $file['file'];
				}
			}
		}

		if ($main_img) {
			$this->db->where('id = ?', $id);
			$this->db->update('projects', ['main_img' => $main_img]);
		}

		return $id;
	}

	public function makeThumbnails()
	{
		$projects = $this->db->select('projects');
		foreach ($projects as $value) {
			if (empty($value['main_img'])) {
				continue;
			}

			resizeImg($value['main_img']);
		}

		return true;
	}

	function setProposalStatus($appointment_id, $status)
	{
		$this->db->query("UPDATE proposals SET status={$status} WHERE id='{$appointment_id}'");

		return $status;
	}

	function getProposals($project_id) {
		if (!is_numeric($project_id)) {
			return -1;
		}

		$this->db->where('project_id=?', $project_id);
		$this->db->join('users', 'proposals.user_id = users.id');
		$this->db->order_by("`timestamp` DESC");
		$r = $this->db->select('proposals', ['*', 'proposals.id AS review_id']);

		foreach ($r as $index => $review) {
			if ($review['image'] != '' && !strstr($review['image'], '//')) {
				$r[$index]['image'] = "/static/images/users/" . $review['image'];
			}
		}

		return $r;
	}

	function submitProposal($post, $project_id) {
		$this->db->insert('proposals', [
			'project_id' => $project_id,
			'text' => $post['proposal'],
			'amount' => $post['amount'],
			'user_id' => $_SESSION['user_id']
		]);

		$this->db->where('id=?', $project_id);
		$project = $this->db->select('projects')[0];

		$this->db->insert('notifications', [
			'user_id' => $project['user_id'],
			'from_user_id' => $_SESSION['user_id'],
			'text' => "<a href='/project/{$project['id']}'>{$_SESSION['user']['name']} has submitted a comment/proposal on {$project['title']}</a>"
		]);
	}

	function getHiredCandidate($project_id) {
		$uid = $this->db->where("project_id = ?", $project_id)
						->where("status = 1")
						->select_one("proposals", ['user_id']);

		if (empty($uid))
			return false;

		$userModel = new userModel();
		$user = $userModel->getUser($uid);
		return $user;
	}

	function getProposal($proposal_id)
	{
		$this->db->where("id = ?", $proposal_id);
		$prop = $this->db->select_one("proposals");

		$userModel = new userModel();

		$prop['project'] = $this->get($prop['project_id']);
		$prop['user']    = $userModel->getUser($prop['user_id']);

		return $prop;
	}
}