<?php

class DbAction {
    private $dataConnect = '';
    function __construct(){
        $this->dataConnect = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
       mysqli_query($this->dataConnect,'SET NAMES utf8 COLLATE utf8_general_ci');
       mysqli_query($this->dataConnect,'SET CONNECTION_COLLATION utf8');

        if (mysqli_connect_errno()) {
            printf("Неможливе підключення до сервера: %s\n", mysqli_connect_error());
            exit;
        }

    }


    public function  getItems(){
        $query  = "SELECT posts.`id`, posts.`html`, posts.`name`, html, posts.`img`, posts.`count_vote`, ministry.`air_record`, posts.narodniy , ministry.id AS ministry_id, ministry.name AS ministry_name "
            ." FROM posts"
            ." LEFT JOIN ministry ON ministry.id = posts.ministry "
            ." WHERE posts.deleted != 1"
            ." AND date_election_start <= NOW() AND  date_election_stop >= NOW()"
            ." ORDER BY posts.ministry ASC, posts.count_vote DESC, posts.id ASC" ;


        return $this->getData($query);
    }

    public function  getMinistry(){
        $query = 'SELECT *, IF(date_election_start <= NOW() AND  date_election_stop >= NOW(), 1 , 0) as active FROM ministry  ORDER BY date_election_stop ASC;';
        return $this->chunkMinistryByDate($this->getData($query));
    }

    private function chunkMinistryByDate($ministry){
        $chunkArr = array();
        foreach($ministry as $itemMinistry){
            $date = date('Y-m-d', ( strtotime($itemMinistry['date_efir'])));
            if(!isset($chunkArr[$date])){
                $chunkArr[$date] = array();
            }
            $chunkArr[$date][] = $itemMinistry;
        }

        return $chunkArr;

    }

    public  function getMAxCountIP(){
        $query = "SELECT ip, count(*) FROM vote_log GROUP BY ip ORDER BY count(*) DESC LIMIT 0,10";
        return $this->getData($query);
    }

    public function addVote($id){
        $query = 'UPDATE posts'.
                    ' SET count_vote = count_vote + 1'.
                  ' WHERE id='.$id;
        $this->dataConnect->query($query);
    }

    public function getSpammersVotes($id){
        // candidat_id = $id AND
        $query = "SELECT ip,status, count(*) FROM vote_log
                        WHERE
                         status = 0
                        GROUP BY ip
                        ORDER BY count(*) DESC
                        LIMIT 0,10";
        return $this->getData($query);

    }

    public function getCountVotesByIp($id,$ip){
        $query = "SELECT count(*) as c FROM vote_log
                        WHERE candidat_id = $id AND ip = $ip";
        $res = $this->getData($query);

        return (int)$res[0]['c'];

    }




    public function addVoteLogItem($idItem, $usIp, $status){
        $query = 'INSERT INTO vote_log(candidat_id, ip, status)'.
            ' VALUES('.$idItem.','.$usIp.','.$status.')';
        $this->dataConnect->query($query);
    }

    public function getVoteLogByIP($ip, $userId){
        $query = 'SELECT ip, candidat_id, timestamp FROM vote_log WHERE ip='.$ip.' AND candidat_id='.$userId.' ORDER BY timestamp DESC  limit 0,1';
        return $this->getData($query);
    }


    public function getVote($id){
       $query = 'SELECT  count_vote FROM posts WHERE id='.$id;
       return $this->getData($query);
    }

    public  function getVotesById($idArr){
        $condition = "";
        for($i = 0; $i < sizeof($idArr); $i++){
            if(sizeof($idArr)- $i > 1){
                $condition .= " id = ".$idArr[$i]." OR ";
            }else{
                $condition .="id =".$idArr[$i];
            }
        }
        $query = 'SELECT id, count_vote FROM posts WHERE deleted = 0 AND ('.$condition.')';

        return $this->getData($query);
    }

    public function  getAdminItems($page = 1){
       // $pageOfset = ($page-1) * PAGE_ADMIN_LIMIT;
      //  $limit = " LIMIT $pageOfset, ".PAGE_ADMIN_LIMIT;
        $query  = "SELECT `id`, `name`, `html` , `create_date`"
            ." FROM posts"
            ." WHERE deleted != 1"
            ." ORDER BY create_date DESC";
          /*  .$limit;*/
        return $this->getData($query);

    }

    public function  removeItem($id){
        $query  = "UPDATE posts"
            ." SET deleted = 1"
            ." WHERE id = ".$id;
        $this->dataConnect->query($query);
    }

    public function  addItem($nameItem, $htmlItem){
        $query  = "INSERT INTO posts (name, html)
              VALUES ('".$nameItem."', '".$htmlItem."')";
        $this->dataConnect->query($query);
    }

    public function  getEditItem($id){
        $query  = "SELECT `name`, `html`"
            ." FROM posts"
            ." WHERE id =".$id;
        return $this->getData($query);
    }

    public function  editItem($id, $name , $html){
        $query  = "UPDATE posts"
            ." SET name = '".$name
            ."', html = '".$html
            ."' WHERE id = ".$id;
        $this->dataConnect->query($query);
    }



    public function getPaginatorCount(){
        $query  = "SELECT COUNT(*)"
            ." FROM posts WHERE deleted != 1";
        $query_res = $this->dataConnect->query($query);
        if(isset($query_res) && !empty($query_res)){
            $item_arr = $query_res->fetch_array();
        } else {$item_arr = array();}

        if($query_res){
            $query_res->close();
        }
        return $item_arr[0];

    }



    protected   function getData($query){
        $query_res = $this->dataConnect->query($query);
        if(isset($query_res) && !empty($query_res)){
            $item_arr = $query_res->fetch_all(MYSQLI_ASSOC);
        } else {$item_arr = array();}

        if($query_res){
            $query_res->close();
        }
        return $item_arr;
    }


    function __destruct() {
        $this->dataConnect->close();
    }

}