<?php

namespace App\Models;

use App\Core\Model;

class GlobalTopic extends Model
{
    protected static string $table = 'global_topics';

    public static function findCategories(): array
    {
        return self::where('parent_id IS NULL', [], 'name ASC');
    }

    public static function findByParent(int $parentId): array
    {
        return self::where('parent_id = :pid', ['pid' => $parentId], 'name ASC');
    }

    public static function findAllGrouped(): array
    {
        $categories = self::findCategories();
        foreach ($categories as &$cat) {
            $cat['children'] = self::findByParent($cat['id']);
        }
        return $categories;
    }

    public static function findAllFlat(): array
    {
        $db = self::db();
        $stmt = $db->query(
            "SELECT g.*, p.name as parent_name
             FROM global_topics g
             LEFT JOIN global_topics p ON g.parent_id = p.id
             ORDER BY COALESCE(p.name, g.name), g.parent_id IS NULL DESC, g.name ASC"
        );
        return $stmt->fetchAll();
    }
}
