init.sql

create table client_entity (
    id integer generated by default as identity,
    balance bigint not null,
    limite bigint not null, 
    primary key (id)
);

create table transaction_entity (
    client_id integer,
    id integer generated by default as identity,
    type smallint check (type between 0 and 1),
    amount bigint not null,
    created_at timestamp(6) with time zone,
    description varchar(10),
    primary key (id)
);

create index idx_client_id ON transaction_entity (client_id);

alter table if exists transaction_entity 
    add constraint FKoykvsm64e0mpn8jslx3jsi5bt foreign key (client_id) references client_entity;

insert into client_entity values (1, 0, 100000);
insert into client_entity values (2, 0, 80000);
insert into client_entity values (3, 0, 1000000);
insert into client_entity values (4, 0, 10000000);
insert into client_entity values (5, 0, 500000);


CREATE OR REPLACE FUNCTION create_transaction(client_id_arg INTEGER, amount_arg BIGINT, type_arg SMALLINT, description_arg VARCHAR)
RETURNS TABLE (limit_val BIGINT, current_balance BIGINT)
AS $$
DECLARE
    current_balance BIGINT;
    limit_val BIGINT;
BEGIN
    BEGIN
        -- Obter o saldo e o limite do cliente
        SELECT balance, limite INTO current_balance, limit_val FROM client_entity WHERE id = client_id_arg FOR UPDATE;

        -- Verificar se o cliente existe
        IF NOT FOUND THEN
            RAISE EXCEPTION SQLSTATE '90404' USING MESSAGE = 'Cliente não encontrado';
        END IF;

        -- -- Verificar se há saldo suficiente para transações do tipo débito
        -- IF type_arg = 0 THEN
        --     IF (current_balance + limit_val) < amount_arg THEN
        --         RAISE EXCEPTION SQLSTATE '90422' USING MESSAGE = 'Saldo insuficiente para esta transação';
        --     END IF;
        -- END IF;

        -- Verificar se há saldo suficiente para transações do tipo débito
        IF type_arg = 0 THEN
            current_balance := current_balance - amount_arg;
            IF current_balance < (limit_val * -1) THEN
                RAISE EXCEPTION SQLSTATE '90422' USING MESSAGE = 'Saldo insuficiente para esta transação';
            END IF;
        ELSE
            current_balance := current_balance + amount_arg;
        END IF;

        -- -- Atualizar o saldo do cliente
        -- IF type_arg = 0 THEN
        --     current_balance := current_balance - amount_arg;
        -- ELSE
        --     current_balance := current_balance + amount_arg;
        -- END IF;

        UPDATE client_entity SET balance = current_balance WHERE id = client_id_arg;

        -- Inserir a transação
        INSERT INTO transaction_entity (client_id, type, amount, description, created_at) 
        VALUES (client_id_arg, type_arg, amount_arg, description_arg, NOW());

    END;
    
    RETURN QUERY SELECT limit_val, current_balance;
END;
$$
LANGUAGE plpgsql;