<?php
if (!defined('ABSPATH')) exit();
if (!class_exists('DbIndeedAbstract')) require_once ULP_PATH . 'classes/Abstracts/DbIndeedAbstract.class.php';
if (class_exists('Db_Ulp_Badges')) return;
class Db_Ulp_Badges extends DbIndeedAbstract{
/*
id BIGINT(20) NOT NULL,
badge_title VARCHAR(255),
badge_content LONGTEXT,
badge_image VARCHAR(400),
badge_type VARCHAR(200),
rule TEXT,
create_date TIMESTAMP NOT NULL DEFAULT 0
*/
  	/**
  	 * @var string
  	 */
  	protected $table = '';
  	/**
  	 * @param none
  	 * @return none
  	 */
  	public function __construct(){
  		global $wpdb;
  		$this->table = $wpdb->prefix . 'ulp_badges';
  	}
    public function save($data=array()){
        foreach ($data as $k=>$v){
          $data [$k] = esc_sql($v);
        }
        if (!empty($data['id'])){
            /// update
            return parent::update("
              badge_title='{$data['badge_title']}',
              badge_content='{$data['badge_content']}',
              badge_image='{$data['badge_image']}',
              badge_type='{$data['badge_type']}',
              rule='{$data['rule']}' ",
              " id={$data['id']}
            ");
        } else {
            /// insert
            $insert_date = date('Y-m-d H:i:s', time() );
            return parent::insert("NULL,
              '{$data['badge_title']}',
              '{$data['badge_content']}',
              '{$data['badge_image']}',
              '{$data['badge_type']}',
              '{$data['rule']}',
              '$insert_date'
            ");
        }
    }
    public function delete($id=0){
        $id = esc_sql($id);
        return parent::delete(" id=$id ");
    }
    public function getById($id=0){
        if ($id){
            $id = esc_sql($id);
            $data = parent::getRow("`id`, `badge_title`, `badge_content`, `badge_image`, `badge_type`, `rule`, `create_date`", " id=$id ");
        } else {
            $data = [
                'id' => 0,
                'badge_title' => '',
                'badge_content' => '',
                'badge_image' => '',
                'badge_type' => 'static',
                'rule' => '',
            ];
        }
        return $data;
    }
    public function selectByType($type='', $limit=0, $offset=0){
        global $wpdb;
        $q = $wpdb->prepare("SELECT `id`,`badge_title`,`badge_content`,`badge_image`,`badge_type`,`rule`,`create_date` FROM {$this->table} WHERE badge_type=%s ", $type);
        if ($limit){
            $q .= $wpdb->prepare("LIMIT %d OFFSET %d ", $limit, $offset);
        }
        $data = $wpdb->get_results($q);
        if ($data==null){
            $data = false;
        }
        return $data;
    }
    public function selectAll($limit=0, $offset=0){
        global $wpdb;
        $q = "SELECT `id`,`badge_title`,`badge_content`,`badge_image`,`badge_type`,`rule`,`create_date` FROM {$this->table} WHERE 1=1 ";
        if ($limit){
            $q .= $wpdb->prepare("LIMIT %d OFFSET %d ", $limit, $offset);
        }
        $data = $wpdb->get_results($q);
        if ($data==null){
            $data = false;
        }
        return $data;
    }
    public function getByRuleAndType($search_rule='', $type=''){
        global $wpdb;
        $search_rule = esc_sql($search_rule);
        $type = esc_sql($type);
        $q = "SELECT id FROM {$this->table} WHERE rule LIKE '%$search_rule%' AND badge_type='$type' ";
        return $wpdb->get_var($q);
    }
    public function getAllByRuleAndType($search_rule='', $type=''){
        global $wpdb;
        $search_rule = esc_sql($search_rule);
        $type = esc_sql($type);
        $q = "SELECT id, rule FROM {$this->table} WHERE rule LIKE '%$search_rule%' AND badge_type='$type';";
        return $wpdb->get_results($q);
    }
    public function getUsersMinPointsExcludedBadges($min_points=0, $excluded_badges=array() ){
        global $wpdb;
        $excluded_badges_str = implode(',', $excluded_badges);
        $min_points = esc_sql($min_points);
        $excluded_badges_str = esc_sql($excluded_badges_str);
        /*$q = "SELECT a.uid
              	FROM {$wpdb->prefix}ulp_reward_points a
              	LEFT JOIN {$wpdb->prefix}ulp_student_badges b
              	ON a.uid=b.uid
              	WHERE
              	1=1
                AND a.points>=$min_points
              	AND b.badge_id NOT IN ($excluded_badges_str);";*/

			$q = "SELECT a.uid
              	FROM {$wpdb->prefix}ulp_student_badges a
              	WHERE a.badge_id IN ($excluded_badges_str);";
		$users_no_badge_arr = $wpdb->get_results($q );

		$q = "SELECT a.uid
              	FROM {$wpdb->prefix}ulp_reward_points a
              	WHERE
              	1=1
                AND a.points>=$min_points";

		if ($users_no_badge_arr && count($users_no_badge_arr)){
			foreach ($users_no_badge_arr as $obj){
				$users_no_badge[] = $obj->uid;
			}
			$users_no_badge = implode(',', $users_no_badge);

          $q .= " AND a.uid NOT IN ($users_no_badge);";
		}
        return $wpdb->get_results($q);
    }
}
