<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\BaseModel;

final class RoleModel extends BaseModel
{
    protected string $table = 'roles';
    protected array $fillable = ['name', 'slug', 'description', 'is_system'];
    protected bool $timestamps = true;
    protected bool $softDeletes = false;
    protected bool $auditStamps = false;

    public function allWithUserCounts(): array
    {
        $stmt = $this->db->prepare(
            'SELECT r.*, COUNT(u.id) AS user_count
             FROM roles r
             LEFT JOIN users u ON u.role_id = r.id AND u.deleted_at IS NULL
             GROUP BY r.id
             ORDER BY r.name'
        );
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function slugTaken(string $slug, ?int $exceptId = null): bool
    {
        $sql = 'SELECT COUNT(*) FROM roles WHERE slug = :slug';
        $params = ['slug' => $slug];

        if ($exceptId !== null) {
            $sql .= ' AND id != :id';
            $params['id'] = $exceptId;
        }

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);

        return (int) $stmt->fetchColumn() > 0;
    }

    /** A role can only be deleted if it's not a system role and no active users hold it. */
    public function canBeDeleted(int $roleId): bool
    {
        $stmt = $this->db->prepare('SELECT is_system FROM roles WHERE id = :id');
        $stmt->execute(['id' => $roleId]);
        $row = $stmt->fetch();

        if ($row === false || (int) $row['is_system'] === 1) {
            return false;
        }

        $userCount = $this->db->prepare('SELECT COUNT(*) FROM users WHERE role_id = :id AND deleted_at IS NULL');
        $userCount->execute(['id' => $roleId]);

        return (int) $userCount->fetchColumn() === 0;
    }
}
