<?php
    class Support {
        public $db;

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

        public function getTicket($id){
            $ticket = array();
            $stmt = $this->db->query("SELECT id, status, user_id, user_name, code, priority, subject, message, date_added, date_updated, (SELECT title FROM {$this->db->prefix}support_status WHERE id=t.status) AS 'status_text' FROM {$this->db->prefix}support_tickets t WHERE id=:id LIMIT 1", array(':id'=>$id));
            
            if($stmt->num_rows > 0){
                $comments = $this->getSupportComments($stmt->row['id']);
                $ticket = array(
                    'id'                     => $stmt->row['id'],
                    'status'                 => $stmt->row['status'],
                    'status_text'            => $stmt->row['status_text'],
                    'code'                   => $stmt->row['code'],
                    'user_id'                => $stmt->row['user_id'],
                    'user_name'              => $stmt->row['user_name'],
                    'subject'                => $stmt->row['subject'],
                    'message'                => $stmt->row['message'],
                    'date_added'             => date('d M Y H:i', strtotime($stmt->row['date_added'])),
                    'date_updated'           => date('d M Y H:i', strtotime($stmt->row['date_updated'])),
                    'priority'               => $stmt->row['priority'] == 1 ? '<span class="text-danger">Acil</span>' : '<span>Normal</span>',
                    'comments'               => $comments
                );
            }
            return $ticket;
        }

        public function getTickets($data = array()) {
            $tickets = array();
            $query = "SELECT t.id, user_id, user_name, t.code, t.priority, t.subject, t.date_added, t.date_updated, s.title AS 'status', s.color AS 'status_color' FROM `{$this->db->prefix}support_tickets` t LEFT JOIN `{$this->db->prefix}support_status` s ON s.id=t.status WHERE 1";
            
            if(isset($data['status']) && !empty($data['status'])){
                $query .= " AND t.status =".(int)$data['status'];
            }

            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[] = "(t.code LIKE '%$word%' OR t.subject LIKE '%$word%')";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

            if(isset($data['sort'])){
                $query .= " ORDER BY " . $data['sort'];
            }else{
                $query .= " ORDER BY t.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) {
                foreach($stmt->rows as $row) { 
                    $item = array(
                        'id'                     => $row['id'],
                        'user_id'                => $row['user_id'],
                        'user_name'              => $row['user_name'],
                        'status'                 => $row['status'],
                        'status_color'           => $row['status_color'],
                        'code'                   => $row['code'],
                        'subject'                => $row['subject'],
                        'date_added'             => date('d M Y H:i', strtotime($row['date_added'])),
                        'date_updated'           => date('d M Y H:i', strtotime($row['date_updated'])),
                        'priority'               => $row['priority'] == 1 ? '<span class="text-danger">Acil</span>' : '<span>Normal</span>'
                    );
                    array_push($tickets, $item);
                }
            }

            return $tickets;
        }

        public function getTotalTickets($data = array()){
            $query = "SELECT COUNT(t.id) AS 'total' FROM `{$this->db->prefix}support_tickets` t LEFT JOIN `{$this->db->prefix}support_status` s ON s.id=t.status WHERE 1";

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

            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[] = "(t.code LIKE '%$word%' OR t.subject LIKE '%$word%')";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

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

            return 0;
        }

        public function getSupportComments($ticket_id) {
            $comments = array();
            $query = "SELECT * FROM `{$this->db->prefix}support_comments` WHERE ticket_id=:ticket_id ORDER BY date_added DESC";
          
            $stmt = $this->db->query($query, array(':ticket_id'=>$ticket_id));
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) { 
                    $item = array(
                        'id'                 => $row['id'],
                        'ticket_id'          => $row['ticket_id'],
                        'user_id'            => $row['user_id'],
                        'is_agent'           => $row['is_agent'],
                        'user_name'          => $row['user_name'],
                        'content'            => $row['content'],
                        'date_added'         => date('d M Y H:i', strtotime($row['date_added']))
                    );
                    array_push($comments, $item);
                }
            }

            return $comments;
        }

        public function getSupportStatus() {
            $stats = array();
            $query = "SELECT * FROM `{$this->db->prefix}support_status` WHERE status=1";
          
            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) { 
                    $item = array(
                        'id'                     => $row['id'],
                        'status'                 => $row['status'],
                        'title'                  => $row['title'],
                        'description'            => $row['description'],
                        'color'                  => $row['color']
                    );
                    array_push($stats, $item);
                }
            }

            return $stats;
        }

        public function deleteTicket($code) {
            $stmt = $this->db->query("DELETE FROM `{$this->db->prefix}support_tickets` WHERE code=:code", array(':code'=>$code));
        }

        public function generateCode() {
            $numbers = "0123456789";
            $chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            $res = "";
            for ($i = 0; $i < 3; $i++) {
                $res .= $chars[mt_rand(0, strlen($chars)-1)];
            }
            $res .= "-";
            for ($i = 0; $i < 6; $i++) {
                $res .= $numbers[mt_rand(0, strlen($numbers)-1)];
            }
            return $res;
        }
    }
?>