<?php

namespace App\Models;

use App\Core\Model;

class Forum extends Model
{
    protected string $table = 'categories';

    public function getAll(): array
    {
        return $this->db->query(
            "SELECT c.*, COUNT(p.id) as post_count,
             MAX(p.created_at) as last_post_at
             FROM categories c
             LEFT JOIN posts p ON p.category_id = c.id AND p.status = 'active'
             GROUP BY c.id
             ORDER BY c.sort_order DESC"
        )->fetchAll();
    }

    public function findBySlug(string $slug): ?array
    {
        $stmt = $this->db->prepare("SELECT * FROM categories WHERE slug = ?");
        $stmt->execute([$slug]);
        return $stmt->fetch() ?: null;
    }

    public function getTrending(int $limit = 10): array
    {
        $stmt = $this->db->prepare(
            "SELECT p.*, u.username, u.avatar, c.name as category_name, c.slug as category_slug,
             (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.status = 'active' AND p.created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
             ORDER BY score DESC, comment_count DESC
             LIMIT ?"
        );
        $stmt->bindValue(1, $limit, \PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function getStats(): array
    {
        return [
            'topics' => (int)$this->db->query("SELECT COUNT(*) FROM posts WHERE status='active'")->fetchColumn(),
            'posts' => (int)$this->db->query("SELECT COUNT(*) FROM comments")->fetchColumn(),
            'members' => (int)$this->db->query("SELECT COUNT(*) FROM users")->fetchColumn(),
            'categories' => (int)$this->db->query("SELECT COUNT(*) FROM categories")->fetchColumn(),
        ];
    }

    public function search(string $q): array
    {
        $stmt = $this->db->prepare(
            "SELECT p.*, u.username, 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.status = 'active' AND (p.title LIKE ? OR p.content LIKE ?)
             ORDER BY p.created_at DESC LIMIT 20"
        );
        $like = '%' . $q . '%';
        $stmt->execute([$like, $like]);
        return $stmt->fetchAll();
    }
}
