drop schema if exists l2 cascade;
create schema l2;

create table l2.account(aid bigint primary key generated always as identity, "..." text);

create table l2.account_balance(aid bigint not null primary key references l2.account(aid), balance numeric not null default 0, ts timestamptz not null default '-infinity');

create table l2.transaction (tid bigint primary key generated always as identity, ts timestamptz default now() not null, "..." text);

create table l2.entry (aid bigint not null references l2.account(aid), tid bigint not null references l2.transaction(tid), value numeric not null, primary key (aid,tid));

CREATE OR REPLACE FUNCTION l2.cria_conta(aid, numeric, text DEFAULT NULL::text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare
	_balance numeric;
	_tid bigint;
begin
	insert into l2.account (aid, "...") values ($1, $3) on conflict (aid) do nothing;
	insert into l2.account_balance (aid) values ($1) on conflict (aid) do nothing returning balance into _balance;
	if (balance = 0) then
		-- conta não existia, então coloca o saldo inicial
		insert into l2.transaction ("...") values ('inicial') returning tid into _tid;
		insert into l2.entry (aid, tid, value) values (_aid, _tid, $2);
	end if
end;
$function$;

CREATE OR REPLACE FUNCTION l2.consulta_saldo(bigint)
 RETURNS numeric
 LANGUAGE plpgsql
AS $function$
declare
        b_previo numeric;
        t_previo timestamptz;
        b_delta numeric;
begin
        -- saldo no último balanço
        select ab.balance, ab.ts into b_previo, t_previo
                from l2.account_balance as ab
                where ab.aid = $1;
        -- mudanças ao saldo desde o último balanço
        select coalesce(sum(value), 0) into b_delta
                from l2.transaction as t join l2.entry as e on t.tid = e.tid
                where e.aid = $1 and t.ts > t_previo;

        -- saldo atual
        return b_previo + b_delta;
end;
$function$;

CREATE OR REPLACE FUNCTION l2.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
        -- a conta de_aid será debitada, então isso impede que outras chamadas a esta função trabalhem com ela ao mesmo tempo
		perform balance from l2.account_balance where aid = de_aid for share;

        if (l2.consulta_saldo(de_aid) < valor) then
                raise exception 'saldo insuficiente na conta de origem %', de_aid;
        end if;

        -- transação financeira
        insert into l2.transaction ("...") values (outros) returning tid into _tid;

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


