<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\BaseModel;

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

    public function allWithCounts(): array
    {
        $stmt = $this->db->prepare(
            'SELECT t.*, d.name AS department_name, bl.name AS business_line_name,
                    CONCAT(lu.first_name, " ", lu.last_name) AS lead_name,
                    COUNT(DISTINCT tm.user_id) AS member_count
             FROM teams t
             LEFT JOIN departments d ON d.id = t.department_id
             LEFT JOIN business_lines bl ON bl.id = t.business_line_id
             LEFT JOIN users lu ON lu.id = t.lead_user_id
             LEFT JOIN team_members tm ON tm.team_id = t.id
             WHERE t.deleted_at IS NULL
             GROUP BY t.id
             ORDER BY t.name'
        );
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function members(int $teamId): array
    {
        $stmt = $this->db->prepare(
            'SELECT u.id, u.first_name, u.last_name, u.email, u.job_title, u.avatar_path, tm.joined_at
             FROM team_members tm
             JOIN users u ON u.id = tm.user_id AND u.deleted_at IS NULL
             WHERE tm.team_id = :team_id
             ORDER BY u.first_name'
        );
        $stmt->execute(['team_id' => $teamId]);

        return $stmt->fetchAll();
    }

    public function addMember(int $teamId, int $userId): bool
    {
        $stmt = $this->db->prepare(
            'INSERT IGNORE INTO team_members (team_id, user_id, joined_at) VALUES (:team_id, :user_id, NOW())'
        );

        return $stmt->execute(['team_id' => $teamId, 'user_id' => $userId]);
    }

    public function removeMember(int $teamId, int $userId): bool
    {
        $stmt = $this->db->prepare('DELETE FROM team_members WHERE team_id = :team_id AND user_id = :user_id');

        return $stmt->execute(['team_id' => $teamId, 'user_id' => $userId]);
    }
}
