<?php

namespace App\Models;

use App\Core\Model;

class Announcement extends Model
{
    protected $table = 'announcements';
    protected $primaryKey = 'id';

    public function getPublished()
    {
        $stmt = $this->db->query("SELECT a.*, u.name as author_name, c.name as category_name FROM {$this->table} a LEFT JOIN users u ON a.created_by = u.id LEFT JOIN post_categories c ON a.category_id = c.id WHERE a.status = 'published' ORDER BY a.published_at DESC");
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getRecent($limit = 5)
    {
        $stmt = $this->db->prepare("SELECT a.*, u.name as author_name, c.name as category_name FROM {$this->table} a LEFT JOIN users u ON a.created_by = u.id LEFT JOIN post_categories c ON a.category_id = c.id WHERE a.status = 'published' ORDER BY a.published_at DESC LIMIT ?");
        $stmt->execute([$limit]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getPublic($limit = 10)
    {
        $stmt = $this->db->prepare("SELECT a.*, u.name as author_name, c.name as category_name FROM {$this->table} a LEFT JOIN users u ON a.created_by = u.id LEFT JOIN post_categories c ON a.category_id = c.id WHERE a.status = 'published' AND a.visibility = 'public' ORDER BY a.published_at DESC LIMIT ?");
        $stmt->execute([$limit]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getWithAuthor($id)
    {
        $stmt = $this->db->prepare("SELECT a.*, u.name as author_name, c.name as category_name FROM {$this->table} a LEFT JOIN users u ON a.created_by = u.id LEFT JOIN post_categories c ON a.category_id = c.id WHERE a.id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch(\PDO::FETCH_OBJ);
    }

    public function getAllWithCategory()
    {
        $stmt = $this->db->query("SELECT a.*, u.name as author_name, c.name as category_name FROM {$this->table} a LEFT JOIN users u ON a.created_by = u.id LEFT JOIN post_categories c ON a.category_id = c.id ORDER BY a.created_at DESC");
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getByType($type)
    {
        $stmt = $this->db->prepare("SELECT a.*, u.name as author_name, c.name as category_name FROM {$this->table} a LEFT JOIN users u ON a.created_by = u.id LEFT JOIN post_categories c ON a.category_id = c.id WHERE a.post_type = ? ORDER BY a.created_at DESC");
        $stmt->execute([$type]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }
}
