init.postgresql.shards copy.sql

CREATE UNLOGGED TABLE clientes (
	id SERIAL,
	saldo INTEGER NOT NULL DEFAULT 0,
    shard INTEGER NOT NULL DEFAULT 0,
     PRIMARY KEY (id, shard)
);
    
CREATE UNLOGGED TABLE transacoes (
	id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	cliente_id INTEGER NOT NULL,
	valor INTEGER NOT NULL,
	tipo CHAR(1) NOT NULL,
	descricao VARCHAR(10) NOT NULL,
	realizada_em TIMESTAMP(6) NOT NULL
);

CREATE INDEX idx_realizada_em ON transacoes (realizada_em);


INSERT INTO clientes(id, saldo, shard) VALUES 
    (1,0,0), (2,0,0), (3,0,0), (4,0,0), (5,0,0),
    (1,0,1), (2,0,1), (3,0,1), (4,0,1), (5,0,1);

CREATE EXTENSION IF NOT EXISTS pg_prewarm;
SELECT pg_prewarm('clientes');
SELECT pg_prewarm('transacoes');


CREATE OR REPLACE FUNCTION limite_cliente(p_cliente_id INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN CASE p_cliente_id
        WHEN 1 THEN 100000
        WHEN 2 THEN 80000
        WHEN 3 THEN 1000000
        WHEN 4 THEN 10000000
        WHEN 5 THEN 500000
        ELSE -1
    END;
END;
$$ LANGUAGE plpgsql;

CREATE TYPE json_result AS (
  status_code INT,
  body json
);

CREATE OR REPLACE FUNCTION proc_transacao(p_shard INT, p_cliente_id INT, p_valor INT, p_tipo CHAR, p_descricao CHAR(10))
RETURNS json_result as $$
DECLARE
    diff INT;
    v_saldo INT;
    v_limite INT;
    v_sum INT;
    result json_result;
BEGIN
    SELECT limite_cliente(p_cliente_id) INTO v_limite;

    -- m_shard := p_valor % 10
    
    SELECT saldo
        INTO v_saldo
        FROM clientes
        WHERE 
            id = p_cliente_id
        AND shard = p_shard
        FOR UPDATE;

    SELECT SUM(saldo)
        INTO v_sum
        FROM clientes
        WHERE id = p_cliente_id;

    IF p_tipo = 'd' THEN
        diff := p_valor * -1;            
        IF (v_sum + diff) < (-1 * v_limite) THEN
            result.body := 'LIMITE_INDISPONIVEL';
            result.status_code := 422;
            RETURN result;
        END IF;
    ELSE
        diff := p_valor;
    END IF;

    
    INSERT INTO transacoes 
                     (cliente_id,   valor,   tipo,   descricao,      realizada_em)
            VALUES (p_cliente_id, p_valor, p_tipo, p_descricao, now());

    UPDATE clientes 
        SET saldo = saldo + diff 
        WHERE id = p_cliente_id
            AND shard = p_shard;

    SELECT json_build_object(
        'saldo', v_sum + diff,
        'limite', v_limite
    ) into result.body;
    result.status_code := 200;
    RETURN result;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION proc_extrato(p_cliente_id int)
RETURNS json_result AS $$
DECLARE
    result json_result;
    row_count integer;
    v_saldo numeric;
    v_limite numeric;
BEGIN

    SELECT SUM(saldo)
        INTO v_saldo
        FROM clientes
        WHERE id = p_cliente_id;

    IF NOT FOUND THEN
            result.body := 'CLIENTE_NAO_ENCONTRADO';
            result.status_code := 404;
            RETURN result;
    END IF;

    SELECT limite_cliente(p_cliente_id) INTO v_limite;
    SELECT json_build_object(
        'saldo', json_build_object(
            'total', v_saldo,
            'data_extrato', TO_CHAR(now(), 'YYYY-MM-DD HH:MI:SS.US'),
            'limite', v_limite
        ),
        'ultimas_transacoes', COALESCE((
            SELECT json_agg(row_to_json(t)) FROM (
                SELECT valor, tipo, descricao, TO_CHAR(realizada_em, 'YYYY-MM-DD HH:MI:SS.US') as realizada_em
                FROM transacoes
                WHERE cliente_id = p_cliente_id
                ORDER BY realizada_em DESC
                LIMIT 10
            ) t
        ), '[]')
    ) INTO result.body;
    result.status_code := 200;
    RETURN result;
END;
$$ LANGUAGE plpgsql;