<?php
    class Url {
        public function __construct(Database $db, Utils $utils){
            $this->db = $db;
            $this->utils = $utils;
        }

        function getUrl($id){
            $stmt = $this->db->query( "SELECT * FROM {$this->db->prefix}urls WHERE id = :id limit 1", array(':id' => $id));
            if($stmt->num_rows > 0) {
                return $stmt->row;
            }
            return false;
        }
        
        public function getUrls($data = array()) {
            $urls = array();
            $query = "SELECT * FROM {$this->db->prefix}urls WHERE 1";
            
            if(isset($data['url_type']) && !empty($data['url_type'])){
                $query .= " AND url_type =".$this->db->escape($data['url_type']);
            }

            if(isset($data['keyword'])){
                $keywords = explode(' ', trim($data['keyword']));
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "((title LIKE '%$word%') OR (url LIKE '%$word%'))";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }
            
            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) {
                $urls = $stmt->rows;
            }
            return $urls;
        }

        public function getTotalUrls($data = array()){
            $query = "SELECT COUNT(id) AS 'total' FROM {$this->db->prefix}urls WHERE 1";
            
            if(isset($data['url_type']) && !empty($data['url_type'])){
                $query .= " AND url_type =".$this->db->escape($data['url_type']);
            }

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

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

            return $total_urls;
        }

        public function generateUrl($s, $prefix="", $i=0) {
            $tr = array('ş','Ş','ı','I','İ','ğ','Ğ','ü','Ü','ö','Ö','Ç','ç');
            $eng = array('s','s','i','i','i','g','g','u','u','o','o','c','c');

            do {
                $generated_url = str_replace($tr,$eng,$s);
                $generated_url = strtolower(trim(preg_replace('/[^A-Za-z0-9-]+/', '-', $generated_url), '-'));

                if($i != 0){
                    $generated_url = $generated_url."-".$i;
                }
                $generated_url = $prefix.$generated_url;
                $i++;
            }while(!$this->isUrlExists($generated_url));

            return $generated_url;
        }

        function isUrlExists($generated_url){
            $stmt = $this->db->query( "SELECT id FROM {$this->db->prefix}urls WHERE url = :url limit 1", array(':url' => $generated_url));
            if($stmt->num_rows == 0) {
                return true;
            }
            return false;
        }

        public function addUrl($url, $url_type, $element, $route, $meta_title="", $meta_description=""){
            $stmt = $this->db->query("INSERT INTO {$this->db->prefix}urls (`url`, `url_type`, `element`, `route`, `title`, `description`) VALUES (:url, :url_type, :element, :route, :title, :description)", array(':url'=>$url, ':url_type'=>$url_type, ':element'=>$element, ':route'=>$route, ':title'=>$meta_title,  ':description'=>$meta_description));
            if ($stmt->error) {
                $this->utils->errorLog("error", "Cannot add url. Error: ".$stmt->error_info['message'], 0, $_SERVER['REQUEST_URI']);
            }
        }

        function getUrlId($url){
            $stmt = $this->db->query("SELECT id FROM {$this->db->prefix}urls WHERE url = :url LIMIT 1", array(':url'=>$url));
            if($stmt->num_rows > 0) {
                return true;
            }
            return false;
        }

        function updateUrl($url_type, $element, $title, $description){
            $stmt = $this->db->query("UPDATE {$this->db->prefix}urls SET title=:title, description=:description WHERE url_type=:url_type, element=:element", array(':url_type'=>$url_type, ':element'=>$element, ':title'=>$title, ':description'=>$description));
            if ($stmt->error) {
                $this->utils->errorLog("error", "Cannot update url. Error: ".$stmt->error_info['message'], 0, $_SERVER['REQUEST_URI']);
            }
        }

        function deleteUrl($element, $url_type){
            $this->db->query("DELETE FROM {$this->db->prefix}urls WHERE element=:element AND url_type=:url_type", array(':element'=>$element, ':url_type'=>$url_type));
        }

        function deleteUrlById($id){
            $this->db->query("DELETE FROM {$this->db->prefix}urls WHERE id=:id", array(':id'=>$id));
        }

        public function getItemURL($element, $url_type){
            $stmt = $this->db->query("SELECT * FROM {$this->db->prefix}urls WHERE element=:element AND url_type=:url_type", array(':element'=>$element, ':url_type'=>$url_type));
            if($stmt->num_rows > 0) {
                return $stmt->row;
            }
            return null;
        }
    }