<?php
class ModelMemberReward extends Model {
	public function editMemberReward($member_reward_id, $data) {
		$sql = "UPDATE `" . DB_PREFIX . "member_reward` 
		SET expiry_date = '" . $this->db->escape($data['expiry_date']) . "',
		is_redeemed = '" . $this->db->escape($data['is_redeemed']) . "',
		order_no = '" . $this->db->escape($data['order_no']) . "',
		is_cancelled = '" . $this->db->escape($data['is_cancelled']) . "',
		updated_by = '" . $this->session->data['username'] . "', 
		updated_date = NOW() 
		WHERE member_reward_id = '" . (int)$member_reward_id . "';";

		$this->db->query($sql);
        $this->insertMemberRewardLog($member_reward_id);
	}

	public function getMemberReward($member_reward_id) {
		$query = $this->db->query("SELECT mr.*, rs.title AS reward_name FROM 
		`" . DB_PREFIX . "member_reward` mr 
		INNER JOIN `" . DB_PREFIX . "reward_setting` rs ON mr.reward_setting_id = rs.reward_setting_id
		WHERE mr.member_reward_id = '" . (int)$member_reward_id . "';");
		return $query->row;
	}

	public function getMemberRewards($data = array()) {
		$sql = "SELECT mr.*, m.phone_no, m.email, rs.title AS reward_name
		FROM `" . DB_PREFIX . "member_reward` mr 
		INNER JOIN `" . DB_PREFIX . "member` m ON mr.member_id = m.member_id
		INNER JOIN `" . DB_PREFIX . "reward_setting` rs ON rs.reward_setting_id = mr.reward_setting_id ";

		$implode = array();

		if (!empty($data['filter_reward_name'])) {
			$implode[] = "rs.title LIKE '%" . $this->db->escape($data['filter_reward_name']) . "%'";
		}

		if (!empty($data['filter_email'])) {
			$implode[] = "m.email LIKE '" . $this->db->escape($data['filter_email']) . "%'";
		}

		if (!empty($data['filter_phone_no'])) {
			$implode[] = "m.phone_no LIKE '" . $this->db->escape($data['filter_phone_no']) . "%'";
		}

		if (isset($data['filter_is_redeemed']) && $data['filter_is_redeemed'] !== '') {
			$implode[] = "mr.is_redeemed = '" . (int)$data['filter_is_redeemed'] . "'";
		}

		if (isset($data['filter_is_cancelled']) && $data['filter_is_cancelled'] !== '') {
			$implode[] = "mr.is_cancelled = '" . (int)$data['filter_is_cancelled'] . "'";
		}

		if ($implode) {
			$sql .= " WHERE " . implode(" AND ", $implode);
		}

		$sort_data = array(
			'member_reward_id',
			'phone_no',
			'email',
			'title'
		);

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];
		} else {
			$sql .= " ORDER BY title";
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getTotalMemberRewards() {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "member_reward`");
		return $query->row['total'];
	}

	public function insertMemberRewardLog($member_reward_id) {
		$this->db->query("INSERT INTO `" . DB_PREFIX . "member_reward_log` 
		(member_reward_id, member_id, reward_setting_id, value, used_point, expiry_date, 
		redemption_date, is_redeemed, order_no, created_by, 
		created_date, is_cancelled) 
		SELECT member_reward_id, member_id, reward_setting_id, value, used_point, expiry_date, 
		redemption_date, is_redeemed, order_no, updated_by as created_by,  
		updated_date, is_cancelled
		 FROM `" . DB_PREFIX . "member_reward` 
		 WHERE member_reward_id = '" . $member_reward_id . "';");
	}

	public function getRewardTypes() {
		$query = $this->db->query("SELECT reward_type_id, value AS reward_type_name FROM `" . DB_PREFIX . "reward_type`;");
		return $query->rows;
	}

	public function getActiveTierSettings() {
		$query = $this->db->query("SELECT tier_setting_id, title AS tier_setting_name FROM `" . DB_PREFIX . "tier_setting` WHERE Status = 1 ORDER BY achieve_tier_amount ASC;");
		return $query->rows;
	}

	public function getRedemptionEnvironments() {
		$query = $this->db->query("SELECT redemption_environment_id, value AS redemption_environment_name FROM `" . DB_PREFIX . "redemption_environment`;");
		return $query->rows;
	}

	public function getActiveRewardSettings() {
		$query = $this->db->query("SELECT rs.member_reward_id, rs.title AS member_reward_name, rs.point, rs.start_date, rs.end_date FROM `" . DB_PREFIX . "member_reward` rs WHERE rs.Status = 1 AND (rs.end_date IS NULL OR DATE(rs.end_date) >= DATE(NOW())) ORDER BY rs.title ASC;");
		return $query->rows;
	}
}