<?php

declare(strict_types=1);

namespace App\Modules\Workspace;

use App\Core\Database;

/**
 * WorkspaceUserModel — Database interactions for workspace-scoped users.
 */
class WorkspaceUserModel
{
    /**
     * Retrieve all users belonging to a specific workspace.
     */
    public function getUsersByWorkspaceId(int $workspaceId): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT u.id, u.display_name, u.username, r.name AS role_name, r.slug AS role_slug, u.is_active 
              FROM users u
              JOIN roles r ON r.id = u.role_id
             WHERE u.workspace_id = :workspace_id AND u.role_id = 2
             ORDER BY u.created_at ASC
        ');
        $stmt->execute(['workspace_id' => $workspaceId]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Get details of a single user scoped to a specific workspace.
     */
    public function getUserByIdAndWorkspace(int $userId, int $workspaceId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT u.id, u.display_name, u.username, u.role_id, r.slug AS role_slug, u.is_active 
              FROM users u
              JOIN roles r ON r.id = u.role_id
             WHERE u.id = :id AND u.workspace_id = :workspace_id AND u.role_id = 2
             LIMIT 1
        ');
        $stmt->execute([
            'id' => $userId,
            'workspace_id' => $workspaceId
        ]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Check if a username is already taken globally.
     */
    public function usernameExists(string $username, ?int $excludeUserId = null): bool
    {
        $db = Database::getConnection();
        if ($excludeUserId !== null) {
            $stmt = $db->prepare('SELECT id FROM users WHERE username = :username AND id <> :exclude_id LIMIT 1');
            $stmt->execute([
                'username' => $username,
                'exclude_id' => $excludeUserId
            ]);
        } else {
            $stmt = $db->prepare('SELECT id FROM users WHERE username = :username LIMIT 1');
            $stmt->execute(['username' => $username]);
        }
        return $stmt->fetch() !== false;
    }

    /**
     * Create a new workspace-scoped user.
     */
    public function insertUser(array $data): int
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            INSERT INTO users (
                workspace_id, role_id, email, username, password_hash, display_name, is_active, created_at
            ) VALUES (
                :workspace_id, :role_id, :email, :username, :password_hash, :display_name, :is_active, NOW()
            )
        ');
        $stmt->execute($data);
        return (int) $db->lastInsertId();
    }

    /**
     * Update workspace-scoped user details.
     */
    public function updateUser(int $userId, int $workspaceId, array $data): void
    {
        $db = Database::getConnection();
        $fields = [];
        $params = [
            'id' => $userId,
            'workspace_id' => $workspaceId
        ];
        foreach ($data as $key => $val) {
            $fields[] = "$key = :$key";
            $params[$key] = $val;
        }
        
        $sql = 'UPDATE users SET ' . implode(', ', $fields) . ' WHERE id = :id AND workspace_id = :workspace_id LIMIT 1';
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
    }
}
