class Database {

    // defining some variables
    private $host;
    private $user;
    private $pass;
    private $bd;

    protected $dbh;


    /*

    method construct
    This method will be used everytime our class is called

    */

    public function __construct() {
        $this->host = HOST;
        $this->user = USER;
        $this->pass = PASS;
        $this->bd = DB;

        // database connection
        $dsn = 'mysql:host=' . HOST . ';dbname=' . DB;
        // Set options
        $options = array(
            PDO::ATTR_PERSISTENT        => true,
            PDO::ATTR_ERRMODE           => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES  => false,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
        );
        //Create a new PDO instance
        try {
            $this->dbh = new PDO($dsn, USER, PASS, $options);

            return $this->dbh;
        }
        // Catch any errors
        catch(PDOException $e) {
            die($e->getMessage());
        }
    }
}

class Posts extends Database {

    /* FRONTEND METHODS */
    // method to select all posts
    // uses a query predefined and an array of parameters
    // returns the array of results
    public function selectPosts($query, $params = array()) {
        // prepare the query to bind params
        $stmt = $this->dbh->prepare($query);

        // binds the params
        foreach($params as $key => $val) {
            if(is_string($val)) {
                $stmt->bindParam($key, $val, PDO::PARAM_STR);
            }
            elseif(is_numeric($val)) {
                $stmt->bindParam($key, $val, PDO::PARAM_INT);
            }
        }
        // execute query after parameters are binded
        $stmt->execute();

        // returns the result
        return $stmt->fetchAll();
    }


    // method to select a single posts
    // uses a query predefined and an array of parameters
    // returns a single row
    public function selectSingle($query, $params = array()) {
        // prepare the query to bind params
        $stmt = $this->dbh->prepare($query);

        // binds the params
        foreach($params as $key => $val) {
            if(is_string($val)) {
                $stmt->bindParam($key, $val, PDO::PARAM_STR);
            }
            elseif(is_numeric($val)) {
                $stmt->bindParam($key, $val, PDO::PARAM_INT);
            }
        }

        // execute query after parameters are binded
        $stmt->execute();

        // returns the result - a single row
        return $stmt->fetch();
    }



    /* BACKEND METHODS */

    // insert posts into database
    // passes just the parameters
    public function insertPost($params = array()) {
        $uery = "INSERT INTO posts (...) VALUES (...)";

        // prepare the query to bind params
        $stmt = $this->dbh->prepare($query);

        // binds the params
        foreach($params as $key => $val) {
            if(is_string($val)) {
                $stmt->bindParam($key, $val, PDO::PARAM_STR);
            }
            elseif(is_numeric($val)) {
                $stmt->bindParam($key, $val, PDO::PARAM_INT);
            }
        }

        // execute query after parameters are binded
        return $stmt->exeute() ? true : false;
    }

    // delete posts from the database
    // needs to get the id of the post to delete
    public function deletePost($id) {
        $query = "DELETE FROM posts WHERE id = :id";

        // prepare the query to bind params
        $stmt = $this->dbh->prepare($query);

        // bind the params
        $stmt->bindParam(':id', $id, PDO::PARAM_INT);

        // executes query after params are binded
        return $stmt->exeute() ? true : false;

    }

    // updates a post
    // passes an array of parameters to bind
    public function updatePost($params = array()) {
        $query = "UPDATE posts SET ... WHERE id = :id";

        // prepare the query to bind params
        $stmt = $this->dbh->prepare($query);

        // binds the params
        foreach($params as $key => $val) {
            if(is_string($val)) {
                $stmt->bindParam($key, $val, PDO::PARAM_STR);
            }
            elseif(is_numeric($val)) {
                $stmt->bindParam($key, $val, PDO::PARAM_INT);
            }
        }

        // execute query after parameters are binded
        return $stmt->exeute() ? true : false;
    }
}

$posts = new Posts();

$query = "SELECT * FROM posts WHERE posts.id > :n";
$params = [':n' => 6];
foreach($posts->selectPosts($query, $params) as $post) {
    echo $post['id']. ', ';
}

$posts = new Posts();
foreach($posts->selectByIdBiggerThan(6) as $post) {
    echo $post['id']. ', ';
}

public function selectByIdBiggerThan($id) {
    $query = "SELECT * FROM posts WHERE id > ?";
    return $this->db->selectAll($query, [$id]));
}

public function run($sql, $args = [])
{
    if (!$args)
    {
         return $this->query($sql);
    }
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($args);
    return $stmt;
}
public function selectAll($sql, $params = []) {
    return $this->run($sql, $params)->fetchAll();
}
public function selectSingle($sql, $params = []) {
    return $this->run($sql, $params)->fetch();
}