<?php
    class Survey {
        public $db;

        public function __construct(Database $db, Utils $utils){
            $this->db = $db;
            $this->utils = $utils;
        }

        public function getSurvey($guid){
            $result = array();
            $stmt = $this->db->query("SELECT * FROM `{$this->db->prefix}survey` WHERE guid=:guid LIMIT 1", array(':guid'=>$guid));
            
            if($stmt->num_rows > 0){
                $result = $stmt->row;
            }
            return $result;
        }

        public function getSurveys($data = array()) {
            $rows = array();
            $query = "SELECT * FROM {$this->db->prefix}survey WHERE 1";

            if(isset($data['search_keywords']) && !empty($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "(title LIKE '%$word%') OR (guid LIKE '%$word%')";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND status =".$data['status'];
                }
            }

            if(isset($data['sort'])){
                $query .= " ORDER BY " . $data['sort'];
            }else{
                $query .= " ORDER BY id";
            }
    
            if(isset($data['order'])){
                $query .= " ".$data['order'];
            }else{
                $query .= " DESC";
            }
    
            if (isset($data['offset']) || isset($data['limit'])) {
                if($data['offset'] < 0){
                    $data['offset'] = 0;
                }
    
                if($data['limit'] < 1){
                    $data['limit'] = 24;
                }
    
                $query .= " LIMIT " . (int)$data['offset'] . "," . (int)$data['limit'];
            }
            
            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                $rows = $stmt->rows;
            }

            return $rows;
        }

        public function getTotalSurveys($data = array()){
            $query = "SELECT COUNT(id) AS 'total' FROM {$this->db->prefix}survey WHERE 1";

            if(isset($data['search_keywords']) && !empty($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "(title LIKE '%$word%') OR (guid LIKE '%$word%')";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND status =".$data['status'];
                }
            }

            $stmt = $this->db->query($query);
        
            if($stmt->num_rows > 0){
                return $stmt->row['total'];
            }

            return 0;
        }

        public function getSurveyQuestions($guid){
            $result = array();
            $stmt = $this->db->query("SELECT q.* FROM `{$this->db->prefix}survey_questions` q LEFT JOIN `{$this->db->prefix}survey` s ON s.id=q.survey_id WHERE guid=:guid", array(':guid'=>$guid));
            
            if($stmt->num_rows > 0){
                $result = $stmt->rows;
            }
            return $result;
        }

        public function getSurveyApplications($guid){
            $result = array();
            $stmt = $this->db->query("SELECT a.* FROM `{$this->db->prefix}survey_applications` a LEFT JOIN `{$this->db->prefix}survey` s ON s.id=a.survey_id WHERE guid=:guid", array(':guid'=>$guid));
            
            if($stmt->num_rows > 0){
                $result = $stmt->rows;
            }
            return $result;
        }

        public function getSurveyQuestion($id){
            $result = array();
            $stmt = $this->db->query("SELECT * FROM `{$this->db->prefix}survey_questions` WHERE id = :id", array(':id'=>$id));
            
            if($stmt->num_rows > 0){
                $result = $stmt->rows;
            }
            return $result;
        }

        public function getSurveyAnswers($id){
            $result = array();
            $stmt = $this->db->query("SELECT q.title AS 'question', a.answer, type FROM `{$this->db->prefix}survey_questions` q LEFT JOIN {$this->db->prefix}survey_answers a ON a.question_id=q.id WHERE application_id = :id", array(':id'=>$id));
            
            if($stmt->num_rows > 0){
                $result = $stmt->rows;
            }
            return $result;
        }

        public function getAllSurveyAnswers($id){
            $result = array();
            $stmt = $this->db->query("SELECT q.title AS 'question', a.answer, type FROM `{$this->db->prefix}survey_questions` q LEFT JOIN {$this->db->prefix}survey_answers a ON a.question_id=q.id WHERE survey_id = :id", array(':id'=>$id));
            
            if($stmt->num_rows > 0){
                $result = $stmt->rows;
            }
            return $result;
        }

        public function deleteSurvey($id){
            $this->db->query("DELETE FROM `{$this->db->prefix}survey_questions` WHERE survey_id=:id", array(':id'=>$id));
            $this->db->query("DELETE FROM `{$this->db->prefix}survey` WHERE id=:id", array(':id'=>$id));
            $this->db->query("DELETE FROM `{$this->db->prefix}urls` WHERE url_type='survey' AND element=:element", array(':element'=>$id));
        }

        public function deleteSurveyQuestion($id){
            $this->db->query("DELETE FROM `{$this->db->prefix}survey_questions` WHERE id=:id", array(':id'=>$id));
        }
    }
?>