deadjournal/models/PostModel.php

<?php

namespace Models;

use Core\Model;
use PDO;

class PostModel extends Model
{
    public function getAll()
    {
        $sql = "SELECT * FROM posts 
                WHERE hidden = 0 
                ORDER BY created_at DESC";
        return $this->db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getById($id)
    {
        $stmt = $this->db->prepare("SELECT * FROM posts WHERE id = :id LIMIT 1");
        $stmt->execute(['id' => $id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function create($title, $content, $created_at = null)
    {
        if (!$created_at) {
            $created_at = date('Y-m-d H:i:s');
        }

        $title = trim($title);
        $content = trim($content);
    
        $stmt = $this->db->prepare("
            INSERT INTO posts (title, content, created_at) 
            VALUES (:title, :content, :created_at)
        ");
        $stmt->execute([
            'title'       => $title,
            'content'     => $content,
            'created_at'  => $created_at
        ]);
        return $this->db->lastInsertId();
    }
    
    public function update($id, $title, $content, $created_at = null)
    {
        $title = trim($title);
        $content = trim($content);

        if (!$created_at) {
            $stmt = $this->db->prepare("
                UPDATE posts
                SET title = :title, content = :content
                WHERE id = :id
            ");
            return $stmt->execute([
                'id'      => $id,
                'title'   => $title,
                'content' => $content
            ]);
        } else {
            // If date was specified
            $stmt = $this->db->prepare("
                UPDATE posts
                SET title = :title, content = :content, created_at = :created_at
                WHERE id = :id
            ");
            return $stmt->execute([
                'id'         => $id,
                'title'      => $title,
                'content'    => $content,
                'created_at' => $created_at
            ]);
        }
    }
    
    public function delete($id)
    {
        $stmt = $this->db->prepare("DELETE FROM posts WHERE id = :id");
        return $stmt->execute(['id' => $id]);
    }

    public function hide($id)
    {
        $stmt = $this->db->prepare("
            UPDATE posts
            SET hidden = 1
            WHERE id = :id
        ");
        return $stmt->execute(['id' => $id]);
    }

    public function getHidden()
    {
        $sql = "SELECT * FROM posts
                WHERE hidden = 1
                ORDER BY created_at DESC";
        return $this->db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }

    public function restore($id)
    {
        $stmt = $this->db->prepare("
            UPDATE posts
            SET hidden = 0
            WHERE id = :id
        ");
        return $stmt->execute(['id' => $id]);
    }

    public function countAll()
    {
        $stmt = $this->db->query("SELECT COUNT(*) as cnt FROM posts");
        return (int) $stmt->fetchColumn();
    }

    public function getPaginated($offset, $limit)
    {
        $stmt = $this->db->prepare("
            SELECT * FROM posts
            WHERE hidden = 0 
            ORDER BY created_at DESC
            LIMIT :offset, :limit
        ");
        $stmt->bindValue(':offset', (int)$offset, \PDO::PARAM_INT);
        $stmt->bindValue(':limit', (int)$limit, \PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function getMonthlyArchives()
    {
        // This will return something like:
        // [ ['year_month' => '2024-11', 'year' => '2024', 'month' => '11', 'total' => '1'], ... ]
        $sql = "
        SELECT 
            YEAR(created_at) AS `year`, 
            MONTH(created_at) AS `month`,
            DATE_FORMAT(created_at, '%Y-%m') AS `year_month`,
            COUNT(*) AS `total`
        FROM posts
        GROUP BY YEAR(created_at), MONTH(created_at)
        ORDER BY YEAR(created_at) DESC, MONTH(created_at) DESC
    ";
        $stmt = $this->db->query($sql);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getByYearMonth($year, $month)
    {
        $stmt = $this->db->prepare("
            SELECT * 
            FROM posts
            WHERE YEAR(created_at) = :year
            AND MONTH(created_at) = :month
            ORDER BY created_at DESC
        ");
        $stmt->execute(['year' => $year, 'month' => $month]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

}