<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\BaseModel;

final class DepartmentModel extends BaseModel
{
    protected string $table = 'departments';
    protected array $fillable = ['business_line_id', 'name', 'description'];
    protected bool $softDeletes = true;
    protected bool $auditStamps = true;

    public function allWithCounts(): array
    {
        $stmt = $this->db->prepare(
            'SELECT d.*, bl.name AS business_line_name,
                    COUNT(DISTINCT u.id) AS user_count,
                    COUNT(DISTINCT t.id) AS team_count
             FROM departments d
             LEFT JOIN business_lines bl ON bl.id = d.business_line_id
             LEFT JOIN users u ON u.department_id = d.id AND u.deleted_at IS NULL
             LEFT JOIN teams t ON t.department_id = d.id AND t.deleted_at IS NULL
             WHERE d.deleted_at IS NULL
             GROUP BY d.id
             ORDER BY d.name'
        );
        $stmt->execute();

        return $stmt->fetchAll();
    }
}
