drop schema if exists l1 cascade;
create schema l1;

-- tabela de contas
create table l1.account (

	-- identificadores não são criados aqui
	aid bigint primary key,

	-- saldo da conta
	balance numeric not null default 0,

	-- outros campos
	"..." text,

	-- saldo positivo para contas de correntistas
	constraint non_negative_balance check (balance >= 0 or aid = 0)
);

-- conta do banco com aid=0 pode ter saldo negativo
insert into l1.account(aid) values (0);

-- tabela de transações financeiras
create table l1.transaction (

	-- identificador artificial
	tid bigint primary key generated always as identity,

	-- momento da transação
	ts timestamptz default now() not null,

	-- outros campos
	"..." text
);

-- tabela de mudanças de saldo causadas pela transação
create table l1.entry (

	-- identificador da conta
	aid bigint not null references l1.account(aid),

	-- identificador da transação
	tid bigint not null references l1.transaction(tid),

	-- valor da mudança na conta feito por essa transação
	value numeric not null,

	primary key (aid,tid)
);


CREATE OR REPLACE FUNCTION l1.cria_conta(bigint, numeric, text DEFAULT NULL::text)
RETURNS void
LANGUAGE sql
AS $function$
	insert into l1.account(aid, balance, "...")
		values ($1, $2, $3)
		on conflict (aid) do nothing
$function$;


CREATE OR REPLACE FUNCTION l1.consulta_saldo(bigint)
RETURNS numeric
LANGUAGE sql
AS $function$
	select balance
		from l1.account
		where aid = $1
	union all
		select 0
	limit 1
$function$;


CREATE OR REPLACE FUNCTION l1.transfere_valor(de_aid bigint, para_aid bigint, valor numeric, outros text DEFAULT NULL::text)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
        _tid bigint;
begin
        -- transação financeira
        insert into l1.transaction ("...")
			values (outros)
			returning tid into _tid;

        -- entradas na transação
        insert into l1.entry (aid, tid, value) values
                (de_aid, _tid, -valor),
                (para_aid, _tid, valor);

        -- atualização dos saldos (tomando cuidado para evitar deadlocks)
        update l1.account
			set balance = case when de_aid < para_aid then balance - valor else balance + valor end where aid = least(de_aid, para_aid);
        update l1.account
			set balance = case when de_aid < para_aid then balance + valor else balance - valor end where aid = greatest(de_aid, para_aid);
end;
$function$;

