drop schema if exists l3 cascade;
create schema l3;

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

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

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

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


CREATE OR REPLACE FUNCTION l3.cria_conta(numeric, text DEFAULT NULL::text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare
        _aid bigint;
        _tid bigint;
begin
        insert into l3.account ("...") values ($2) returning aid into _aid;
        insert into l3.account_balance (aid) values (_aid);
        insert into l3.transaction ("...") values ('inicial') returning tid into _tid;
        insert into l3.entry (aid, tid, value) values (_aid, _tid, $1);
end;
$function$;

CREATE OR REPLACE FUNCTION l3.consulta_saldo_interno(bigint)
 RETURNS table (saldo_total numeric, saldo_parcial numeric, contagem bigint, ts_parcial timestamptz)
 LANGUAGE plpgsql
AS $function$
declare
        ab_valor numeric;
        ab_ts timestamptz;
        e_delta_passado numeric; -- mudanças feitas por transações muito antigas
        e_delta_presente numeric; -- mudanças feitas por transações recentes
        e_contagem bigint;
        e_ts timestamptz;
		ts_passado timestamptz;
begin
        -- saldo no último balanço
        select ab.balance, ab.ts into ab_valor, ab_ts
                from l3.account_balance as ab
                where ab.aid = $1;

		select min(xact_start) into ts_passado from pg_stat_activity;

        -- mudanças ao saldo desde o último balanço
        select coalesce(sum(e.value) filter (where t.ts < ts_passado), 0), coalesce(sum(e.value) filter (where t.ts >= ts_passado), 0), count(e.*), max(t.ts) filter (where t.ts < ts_passado) into e_delta_passado, e_delta_presente, e_contagem, e_ts
                from l3.transaction as t join l3.entry as e on t.tid = e.tid
                where e.aid = $1 and t.ts > ab_ts;

        -- saldo atual
        return query select ab_valor + e_delta_passado + e_delta_presente, ab_valor + e_delta_passado, e_contagem, greatest(ab_ts, e_ts);
end;
$function$;

CREATE OR REPLACE FUNCTION l3.consulta_saldo(bigint)
 RETURNS numeric
 LANGUAGE sql
AS $$ select saldo_total from l3.consulta_saldo_interno($1) $$;

CREATE OR REPLACE FUNCTION l3.transfere_valor(de_aid bigint, para_aid bigint, valor numeric, outros text DEFAULT NULL::text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare
        _tid bigint;
        saldo_total numeric;
        saldo_parcial numeric;
        contagem bigint;
        ts_parcial timestamptz;
begin
        -- a conta de_aid será debitada, então isso impede que outras chamadas a esta função trabalhem com ela ao mesmo tempo
        -- e permite que esta transação possa fazer a atualização caso seja necessário
		perform balance from l3.account_balance where aid = de_aid for update;

        -- garante que débitos não tornam os saldos negativos
        select s.saldo_total, s.saldo_parcial, s.contagem, s.ts_parcial into saldo_total, saldo_parcial, contagem, ts_parcial from l3.consulta_saldo_interno(de_aid) as s;
        if (saldo_total < valor) then
                raise exception 'saldo insuficiente na conta de origem %', de_aid;
        end if;

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

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

        -- a atualização do balanço pode ser feita com base na quantidade de mudanças que a conta sofreu (contagem),
        -- no tempo desde a última mudança ou numa combinação de outros fatores
        -- ela também pode ser feita periodicamente em outro fluxo, como iniciada pelo cron
        if (contagem > 10) then
            update l3.account_balance set balance = saldo_parcial, ts = ts_parcial where aid = de_aid;
        end if;
end;
$function$;


