website/includes/publication.php

<?php
/**
 * Publication Model Class
 * Handles all publication-related database operations
 */

class Publication {
    public $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    public function getAll($status = 'published', $limit = null, $offset = 0) {
        $sql = "SELECT p.*, GROUP_CONCAT(c.name) as categories 
                FROM publications p 
                LEFT JOIN publication_categories pc ON p.id = pc.publication_id 
                LEFT JOIN categories c ON pc.category_id = c.id";
        
        $params = [];
        
        if ($status !== 'all') {
            $sql .= " WHERE p.status = ?";
            $params[] = $status;
        }
        
        $sql .= " GROUP BY p.id ORDER BY p.created_at DESC";
        
        if ($limit) {
            $sql .= " LIMIT ? OFFSET ?";
            $params[] = (int)$limit;
            $params[] = (int)$offset;
        }
        
        return $this->db->fetchAll($sql, $params);
    }
    
    public function getById($id) {
        $sql = "SELECT p.*, GROUP_CONCAT(c.name) as categories 
                FROM publications p 
                LEFT JOIN publication_categories pc ON p.id = pc.publication_id 
                LEFT JOIN categories c ON pc.category_id = c.id 
                WHERE p.id = ? 
                GROUP BY p.id";
        
        $publication = $this->db->fetch($sql, [$id]);
        
        if ($publication && $publication['categories']) {
            $publication['categories_array'] = explode(',', $publication['categories']);
        } else {
            $publication['categories_array'] = [];
        }
        
        return $publication;
    }
    
    public function getBySlug($slug) {
        $sql = "SELECT p.*, GROUP_CONCAT(c.name) as categories 
                FROM publications p 
                LEFT JOIN publication_categories pc ON p.id = pc.publication_id 
                LEFT JOIN categories c ON pc.category_id = c.id 
                WHERE p.slug = ? 
                GROUP BY p.id";
        
        $publication = $this->db->fetch($sql, [$slug]);
        
        if ($publication && $publication['categories']) {
            $publication['categories_array'] = explode(',', $publication['categories']);
        } else {
            $publication['categories_array'] = [];
        }
        
        return $publication;
    }
    
    public function create($data, $categories = []) {
        $this->db->beginTransaction();
        
        try {
            // Generate slug from title
            $slug = $this->generateSlug($data['title']);
            $data['slug'] = $slug;
            
            // Set published_at if status is published
            if ($data['status'] === 'published' && !isset($data['published_at'])) {
                $data['published_at'] = date('Y-m-d H:i:s');
            }
            
            $publicationId = $this->db->insert('publications', $data);
            
            // Add categories
            if (!empty($categories)) {
                $this->updateCategories($publicationId, $categories);
            }
            
            $this->db->commit();
            return $publicationId;
            
        } catch (Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }
    
    public function update($id, $data, $categories = []) {
        $this->db->beginTransaction();
        
        try {
            // Update slug if title changed
            if (isset($data['title'])) {
                $data['slug'] = $this->generateSlug($data['title'], $id);
            }
            
            // Set published_at if status changed to published
            if (isset($data['status']) && $data['status'] === 'published') {
                $current = $this->getById($id);
                if ($current['status'] !== 'published') {
                    $data['published_at'] = date('Y-m-d H:i:s');
                }
            }
            
            $this->db->update('publications', $data, 'id = ?', [$id]);
            
            // Update categories if provided
            if (!empty($categories)) {
                $this->updateCategories($id, $categories);
            }
            
            $this->db->commit();
            return true;
            
        } catch (Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }
    
    public function delete($id) {
        $this->db->beginTransaction();
        
        try {
            // Delete category associations
            $this->db->delete('publication_categories', 'publication_id = ?', [$id]);
            
            // Delete publication
            $this->db->delete('publications', 'id = ?', [$id]);
            
            $this->db->commit();
            return true;
            
        } catch (Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }
    
    public function search($query, $status = 'published') {
        $sql = "SELECT p.*, GROUP_CONCAT(c.name) as categories 
                FROM publications p 
                LEFT JOIN publication_categories pc ON p.id = pc.publication_id 
                LEFT JOIN categories c ON pc.category_id = c.id 
                WHERE (p.title LIKE ? OR p.content LIKE ? OR p.summary LIKE ?)";
        
        $params = ["%$query%", "%$query%", "%$query%"];
        
        if ($status !== 'all') {
            $sql .= " AND p.status = ?";
            $params[] = $status;
        }
        
        $sql .= " GROUP BY p.id ORDER BY p.created_at DESC";
        
        return $this->db->fetchAll($sql, $params);
    }
    
    public function getByCategory($categoryName, $status = 'published') {
        $sql = "SELECT p.*, GROUP_CONCAT(c.name) as categories 
                FROM publications p 
                LEFT JOIN publication_categories pc ON p.id = pc.publication_id 
                LEFT JOIN categories c ON pc.category_id = c.id 
                WHERE c.name = ?";
        
        $params = [$categoryName];
        
        if ($status !== 'all') {
            $sql .= " AND p.status = ?";
            $params[] = $status;
        }
        
        $sql .= " GROUP BY p.id ORDER BY p.created_at DESC";
        
        return $this->db->fetchAll($sql, $params);
    }
    
    public function getCategories() {
        $sql = "SELECT c.*, COUNT(pc.publication_id) as publication_count 
                FROM categories c 
                LEFT JOIN publication_categories pc ON c.id = pc.category_id 
                LEFT JOIN publications p ON pc.publication_id = p.id AND p.status = 'published'
                GROUP BY c.id 
                ORDER BY c.name";
        
        return $this->db->fetchAll($sql);
    }
    
    private function updateCategories($publicationId, $categories) {
        // Delete existing category associations
        $this->db->delete('publication_categories', 'publication_id = ?', [$publicationId]);
        
        // Add new category associations
        foreach ($categories as $categoryId) {
            $this->db->insert('publication_categories', [
                'publication_id' => $publicationId,
                'category_id' => $categoryId
            ]);
        }
    }
    
    private function generateSlug($title, $excludeId = null) {
        $slug = strtolower($title);
        $slug = preg_replace('/[^a-z0-9]+/', '-', $slug);
        $slug = trim($slug, '-');
        
        // Check for uniqueness
        $sql = "SELECT COUNT(*) as count FROM publications WHERE slug = ?";
        $params = [$slug];
        
        if ($excludeId) {
            $sql .= " AND id != ?";
            $params[] = $excludeId;
        }
        
        $result = $this->db->fetch($sql, $params);
        
        if ($result['count'] > 0) {
            $slug .= '-' . time();
        }
        
        return $slug;
    }
    
    public function getStats() {
        $stats = [];
        
        $stats['total'] = $this->db->fetch("SELECT COUNT(*) as count FROM publications")['count'];
        $stats['published'] = $this->db->fetch("SELECT COUNT(*) as count FROM publications WHERE status = 'published'")['count'];
        $stats['draft'] = $this->db->fetch("SELECT COUNT(*) as count FROM publications WHERE status = 'draft'")['count'];
        $stats['archived'] = $this->db->fetch("SELECT COUNT(*) as count FROM publications WHERE status = 'archived'")['count'];
        
        return $stats;
    }
}