<?php

namespace System;

class DB{

    /**
    * @var just to store instance so that we don't need to make connection again
     */
    private static $_instance = null;
    private $_pdo,
            $_query,
            $_error = false,
            $_results,
            $_count = 0;

    /**
     * private function
     * Will try to create PDO connection 
     * @throws PDO Exception
     */
    private function __construct(){

        try{

           $this->_pdo = new \PDO('mysql:host='.Config::db('hostname').';dbname='.Config::db('database'), Config::db('username'), Config::db('password'), array(\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));

        }catch( \PDOException $e ){

            Response::error(404);
        }

    }

     /**
     * get instance of database if present or create new
     * @return instance of database
     */
    public static function getInstance(){

       self::$_instance = (self::$_instance)? self::$_instance : new DB();

       return self::$_instance;
    }

    /**
     * takes query and then bind params to it
     * and store results in this->_results
     * @param  query $sql  simple sql query with or without prams
     * @param  array  $params prams in array (optional)
     * @example (SELECT * FROM table WHERE name = ?, array('somename'))
     * @example either use ? (quotionmark) or :name    
     * @return current object for chaining
     * @throws exceptiopn
     */
    public function query($sql, $params = array())
    {

        $this->_error = false;

        // if sql is prepared then    
        if($this->_query = $this->_pdo->prepare($sql)){
            $x = 1;
            if(count($params)){
                foreach ($params as $param) {
                    $this->_query->bindValue($x, $param);
                    $x++;
                }
            }

            if($this->_query->execute()){
                $this->_results = $this->_query->fetchAll(\PDO::FETCH_OBJ);
                $this->_count = $this->_query->rowCount();

            } else{
                $this->_error = true;
                throw new \Exception('Sql Query Exectuion Failed - ('.$sql.')'); 
            }
        }

        return $this;
    }


    /**
     * Private action helper class
     * @param  string $action type of action select|delete
     * @param  string $table  name of the table
     * @param  array  $where  where condition
     * @return boolean false | current object make query
     */
    private function action($action, $table, $where = array()){

        if(count($where) === 3){

            // operatos supported
            $operators = array('=', '>', '<', '>=', '<=');
            
            $field      = $where[0];
            $operator   = $where[1];
            $value      = $where[2];

            if(in_array($operator, $operators)){

                $sql = "{$action} FROM {$table} WHERE {$field} {$operator} ?";
                
                if(!$this->query($sql, array($value))->error()){

                    return $this;
                }
            }
        }
        return false;
    }

    /**
     * insert element in table
     * @param  string $table table name
     * @param  array  $fields [description]
     * @example insert(table, array(id => '1', name => 'some Name'))
     * @return boolean
     */
    public function insert($table, $fields = array()){

        if(count($fields))
        {
            $keys = array_keys($fields);
            $values = '';
            $x = 1;

            foreach ($fields as $field) {
                $values .= "?";
                if($x < count($fields)){
                    $values .= ', ';
                }
                $x++;
            }
            $sql = "INSERT INTO {$table} (`".implode('`, `', $keys)."`) VALUES ({$values})";

            if(!$this->query($sql, $fields)->error()) return true;
        }

        return false;
    }

    /**
     * update item in the table
     * @param  string $table  table name
     * @param  int $id   element id to update
     * @param  array  $fields update array elements
     * @example (table ,1 , array(id => '1', name => 'some name'))
     * @return boolean
     */
    public function update($table, $id, $fields = array()){

        $set = ''; //  upate set value
        $x = 1;

        foreach ($fields as $name => $value) {

            $set .= "{$name} = ?";

            if($x < count($fields)){
                $set .= ', ';
            }
            $x++;
        }

        $sql = "UPDATE {$table} SET {$set} WHERE id = {$id}";

        if(!$this->query($sql, $fields)->error()) return true;
        
        return false;
    }


     /**
     * select element from table
     * @param  string $table table name
     * @param  array $where condition
     * @example  get(table, array('id', '=', '1'))
     * @return boolean | this use result method than
     */
    public function get($table, $where){
        return $this->action('SELECT *', $table, $where);
    }

    /**
     * Delete element from table
     * @param  string $table table name
     * @param  array $where condition
     * @example  delete(table, array('id', '=', '1'))
     * @return boolean
     */
    public function delete($table, $where){
        return $this->action('DELETE', $table, $where);
    }

    /**
     * return results after any query execution
     * @return object results
     */
    public function results(){
        return $this->_results;
    }

    /**
     * first element of result object
     * @return object
     */
    public function first(){
        return current($this->results());
    }

    /**
     * if there is any error or not
     * @return boolean
     */
    public function error(){
        return $this->_error;
    }

    /**
     * object array count
     * @return int
     */
    public function count(){
        return $this->_count;
    }


    /**
     * get table count from database
     * @param string table name
     * @return int
     */
    public function getCount($table, $id = null){

        if($id) return $this->query("SELECT COUNT(id) as count FROM {$table} WHERE user_id = ?", [$id])->first()->count;    

       return $this->query("SELECT COUNT(id) as count FROM {$table}")->first()->count;
    }

    /**
     * just a shortcut to get id and name
     * @param  string table name
     * @return object array
     */
    public static function getNames($table){

        return self::$_instance->query("SELECT id, name FROM {$table} ORDER BY id DESC")->results();
    }

}
