View file app\Models\Post.php

<?php

namespace App\Models;

use App\Core\Model;

class Post extends Model
{
    protected string $table = 'posts';

    public function getLatest(int $limit = 10, int $offset = 0, string $sort = 'latest')
    {
        $order = "p.created_at DESC";
        $where = "p.status = 'active'";
        
        if ($sort === 'top') $order = "score DESC";
        if ($sort === 'unsolved') {
            $where .= " AND p.type = 'question' AND p.is_best_answer_selected = 0";
        }

        $sql = "SELECT p.*, u.username, u.avatar, c.name as category_name,
                (SELECT COALESCE(SUM(vote_type), 0) FROM votes WHERE post_id = p.id) as score,
                (SELECT COUNT(*) FROM comments WHERE post_id = p.id) as comment_count
                FROM posts p
                JOIN users u ON p.user_id = u.id
                JOIN categories c ON p.category_id = c.id
                WHERE $where
                ORDER BY p.sort_order DESC, $order
                LIMIT ? OFFSET ?";
        
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(1, $limit, \PDO::PARAM_INT);
        $stmt->bindValue(2, $offset, \PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function getByCategory(int $categoryId, int $limit = 20)
    {
        $sql = "SELECT p.*, u.username, u.avatar, c.name as category_name,
                (SELECT COALESCE(SUM(vote_type), 0) FROM votes WHERE post_id = p.id) as score,
                (SELECT COUNT(*) FROM comments WHERE post_id = p.id) as comment_count
                FROM posts p
                JOIN users u ON p.user_id = u.id
                JOIN categories c ON p.category_id = c.id
                WHERE p.category_id = ? AND p.status = 'active'
                ORDER BY p.sort_order DESC, p.created_at DESC
                LIMIT ?";
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(1, $categoryId, \PDO::PARAM_INT);
        $stmt->bindValue(2, $limit, \PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function getByUser(int $userId, int $limit = 5)
    {
        $sql = "SELECT p.*, c.name as category_name,
                (SELECT COUNT(*) FROM comments WHERE post_id = p.id) as comment_count
                FROM posts p
                JOIN categories c ON p.category_id = c.id
                WHERE p.user_id = ?
                ORDER BY p.created_at DESC
                LIMIT ?";
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(1, $userId, \PDO::PARAM_INT);
        $stmt->bindValue(2, $limit, \PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function findBySlug(string $slug)
    {
        $sql = "SELECT p.*, u.username, u.avatar, u.reputation, c.name as category_name, c.slug as category_slug
                FROM posts p
                JOIN users u ON p.user_id = u.id
                JOIN categories c ON p.category_id = c.id
                WHERE p.slug = ?";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$slug]);
        return $stmt->fetch();
    }

    public function find(int $id): ?array
    {
        $stmt = $this->db->prepare("SELECT p.*, u.username, u.avatar, c.name as category_name 
                                    FROM posts p 
                                    JOIN users u ON p.user_id = u.id 
                                    JOIN categories c ON p.category_id = c.id 
                                    WHERE p.id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }

    public function update(int $id, array $data)
    {
        $old = $this->find($id);
        
        $stmtHistory = $this->db->prepare("INSERT INTO post_history (post_id, user_id, content_old) VALUES (?, ?, ?)");
        $stmtHistory->execute([$id, $old['user_id'] ?? 0, $old['content'] ?? '']);

        $sql = "UPDATE posts SET title = ?, content = ?, category_id = ?, updated_at = NOW() WHERE id = ?";
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([
            $data['title'],
            $data['content'],
            $data['category_id'],
            $id
        ]);
    }

    public function create(array $data)
    {
        $sql = "INSERT INTO posts (user_id, category_id, title, slug, content, type) VALUES (?, ?, ?, ?, ?, ?)";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([
            $data['user_id'],
            $data['category_id'],
            $data['title'],
            $data['slug'],
            $data['content'],
            $data['type']
        ]);
        return $this->db->lastInsertId();
    }

    public function search(string $query)
    {
        $sql = "SELECT p.*, u.username, u.avatar, c.name as category_name,
                (SELECT COALESCE(SUM(vote_type), 0) FROM votes WHERE post_id = p.id) as score
                FROM posts p
                JOIN users u ON p.user_id = u.id
                JOIN categories c ON p.category_id = c.id
                WHERE MATCH(p.title, p.content) AGAINST(? IN NATURAL LANGUAGE MODE)
                ORDER BY score DESC";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$query]);
        return $stmt->fetchAll();
    }

    public function delete(int $id): bool
    {
        $stmt = $this->db->prepare("DELETE FROM {$this->table} WHERE id = ?");
        return $stmt->execute([$id]);
    }
}