backend-php/models/UserModel.php

<?php

namespace Models;

class UserModel
{
    private \PDO $pdo;

    public function __construct()
    {
        if (isset($GLOBALS['pdo']) && $GLOBALS['pdo'] instanceof \PDO) {
            $this->pdo = $GLOBALS['pdo'];
            return;
        }
    }

    public function validateLogin(string $email, string $password): ?array
    {
        $stmt = $this->pdo->prepare(
            'SELECT
                user_id,
                user_name,
                user_email,
                user_password,
                user_phone,
                user_cpf,
                user_address,
                user_city,
                user_state,
                user_zip,
                user_country,
                user_birthdate,
                company_id,
                role_id,
                user_kyc,
                kyc_external_id
            FROM "user"
            WHERE user_email = :email AND user_flag = \'a\''
        );
        $stmt->execute(['email' => $email]);
        $user = $stmt->fetch(\PDO::FETCH_ASSOC);

        if ($user && password_verify($password, $user['user_password'])) {
            unset($user['user_password']);
            return $user;
        }

        return null;
    }

    public function isEmailActive(string $email): bool
    {
        $stmt = $this->pdo->prepare('SELECT 1 FROM "user" WHERE user_email = :email AND user_flag = \'a\'');
        $stmt->execute(['email' => $email]);
        return (bool)$stmt->fetchColumn();
    }

    public function createUser(array $data, string $flag = 'a')
    {
        // Verifica se email já existe
        $stmt = $this->pdo->prepare('SELECT 1 FROM "user" WHERE user_email = :email AND user_flag = \'a\'');
        $stmt->execute(['email' => $data['email']]);
        if ($stmt->fetchColumn()) {
            return false;
        }

        $hash = password_hash($data['password'], PASSWORD_DEFAULT);

        $stmt = $this->pdo->prepare(
            'INSERT INTO "user" (
                user_name,
                user_email,
                user_password,
                user_phone,
                user_address,
                user_city,
                user_state,
                user_zip,
                user_country,
                user_kyc,
                kyc_external_id,
                user_birthdate,
                user_cpf,
                company_id,
                role_id,
                user_flag
            ) VALUES (
                :user_name,
                :user_email,
                :hash,
                :user_phone,
                :user_address,
                :user_city,
                :user_state,
                :user_zip,
                :user_country,
                :user_kyc,
                :kyc_external_id,
                :user_birthdate,
                :user_cpf,
                :company_id,
                :role_id,
                :flag
            ) RETURNING user_id'
        );

        $ok = $stmt->execute([
            'user_name'       => $data['username'],
            'user_email'      => $data['email'],
            'hash'            => $hash,
            'user_phone'      => $data['phone'],
            'user_address'    => $data['address'],
            'user_city'       => $data['city'],
            'user_state'      => $data['state'],
            'user_zip'        => $data['zip'],
            'user_country'    => $data['country'],
            'user_kyc'        => (int)$data['kyc'],
            'kyc_external_id' => $data['kyc_external_id'] ?? '',
            'user_birthdate'  => (int)$data['birthdate'],
            'user_cpf'        => $data['cpf'],
            'company_id'      => (int)$data['company_id'],
            'role_id'         => (int)$data['role_id'],
            'flag'            => $flag
        ]);

        if (!$ok) {
            return false;
        }

        $userId = $stmt->fetchColumn();

        return [
            'user_id'    => (int)$userId,
            'user_name'  => $data['username'],
            'user_email' => $data['email'],
            'company_id' => (int)$data['company_id'],
            'role_id'    => (int)$data['role_id']
        ];
    }

    public function getUsersByCompany(int $companyId): array
    {
        $stmt = $this->pdo->prepare("SELECT user_id, user_name, user_email, role_id FROM \"user\" WHERE company_id = :company_id AND user_flag = 'a'");
        $stmt->execute(['company_id' => $companyId]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function getUserInfoById(int $userId, int $companyId): ?array
    {
        $stmt = $this->pdo->prepare(
            'SELECT
                user_id,
                user_name,
                user_email,
                user_phone,
                user_cpf,
                user_birthdate,
                user_kyc,
                role_id,
                user_flag,
                user_address,
                user_city,
                user_state,
                user_zip,
                user_country
            FROM "user"
            WHERE user_id = :user_id AND company_id = :company_id'
        );
        $stmt->execute([
            'user_id' => $userId,
            'company_id' => $companyId,
        ]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function deleteUserById(int $userId, int $companyId): bool
    {
        $stmt = $this->pdo->prepare("UPDATE \"user\" SET user_flag = 'd' WHERE user_id = :user_id AND company_id = :company_id AND user_flag = 'a'");
        $stmt->execute(['user_id' => $userId, 'company_id' => $companyId]);
        return $stmt->rowCount() > 0;
    }

    public function updateEmail(int $userId, string $newEmail): bool
    {
        // check duplicate
        $chk = $this->pdo->prepare('SELECT 1 FROM "user" WHERE user_email = :email AND user_id <> :uid AND user_flag = \'a\'');
        $chk->execute(['email' => $newEmail, 'uid' => $userId]);
        if ($chk->fetchColumn()) {
            return false;
        }
        $stmt = $this->pdo->prepare('UPDATE "user" SET user_email = :email WHERE user_id = :uid AND user_flag = \'a\'');
        return $stmt->execute(['email' => $newEmail, 'uid' => $userId]);
    }

    public function changePassword(int $userId, string $currentPassword, string $newPassword): bool
    {
        $stmt = $this->pdo->prepare('SELECT user_password FROM "user" WHERE user_id = :uid AND user_flag = \'a\'');
        $stmt->execute(['uid' => $userId]);
        $hash = $stmt->fetchColumn();
        if (!$hash || !password_verify($currentPassword, $hash)) {
            return false;
        }
        $newHash = password_hash($newPassword, PASSWORD_DEFAULT);
        $up = $this->pdo->prepare('UPDATE "user" SET user_password = :hash WHERE user_id = :uid');
        return $up->execute(['hash' => $newHash, 'uid' => $userId]);
    }

    public function updateKycExternalId(int $userId, string $externalId): bool
    {
        $stmt = $this->pdo->prepare('UPDATE "user" SET kyc_external_id = :external_id WHERE user_id = :uid');
        return $stmt->execute([
            'external_id' => $externalId,
            'uid'         => $userId,
        ]);
    }

    public function findByExternalId(string $externalId): ?int
    {
        $stmt = $this->pdo->prepare(
            'SELECT user_kyc
             FROM "user"
             WHERE kyc_external_id = :external_id'
        );
        $stmt->execute(['external_id' => $externalId]);
        $user = $stmt->fetchColumn();
        return $user;
    }
}