init.postgresql.dev.sql

CREATE TABLE watchdog (
    log_id SERIAL PRIMARY KEY,
    function_name TEXT,
    execution_timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
    execution_duration INTERVAL
);

CREATE UNLOGGED TABLE clientes (
	id SERIAL PRIMARY KEY,
	saldo INTEGER NOT NULL DEFAULT 0
);
    
CREATE UNLOGGED TABLE transacoes (
	id INTEGER 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_cliente_id ON transacoes (cliente_id);

INSERT INTO clientes(id) VALUES (DEFAULT), (DEFAULT), (DEFAULT), (DEFAULT), (DEFAULT);

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

WITH Percentiles AS (
  SELECT
    function_name,
    percentile_cont(0.25) WITHIN GROUP (ORDER BY execution_duration) AS p25,
    percentile_cont(0.50) WITHIN GROUP (ORDER BY execution_duration) AS p50,
    percentile_cont(0.75) WITHIN GROUP (ORDER BY execution_duration) AS p75,
    percentile_cont(0.95) WITHIN GROUP (ORDER BY execution_duration) AS p95
  FROM
    watchdog
  GROUP BY
    function_name
)
SELECT
  function_name,
  ROUND(EXTRACT(EPOCH FROM p25) * 1000, 3) AS p25ms,
  ROUND(EXTRACT(EPOCH FROM p50) * 1000, 3) AS p50ms,
  ROUND(EXTRACT(EPOCH FROM p75) * 1000, 3) AS p75ms,
  ROUND(EXTRACT(EPOCH FROM p95) * 1000, 3) AS p95ms
FROM
  Percentiles;


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;
    result json_result;
    start_time TIMESTAMP;
    end_time TIMESTAMP;
BEGIN
    start_time := clock_timestamp();

    -- SELECT limite_cliente(p_cliente_id) INTO v_limite;
    v_limite := 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 -- Valor padrão caso o id do cliente não esteja entre 1 e 5
    END;

    SELECT saldo 
        INTO v_saldo
        FROM clientes
        WHERE id = p_cliente_id
        FOR UPDATE;

    IF p_tipo = 'd' AND ((v_saldo - p_valor) < (-1 * v_limite)) THEN
            result.body := 'LIMITE_INDISPONIVEL';
            result.status_code := 422;
            RETURN result;
    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 = CASE 
                    WHEN p_tipo = 'c' THEN saldo + p_valor
                    WHEN p_tipo = 'd' THEN saldo - p_valor
                    ELSE saldo
                END
        WHERE id = p_cliente_id
        RETURNING saldo INTO v_saldo;


    SELECT json_build_object(
        'saldo', v_saldo,
        'limite', v_limite
    ) into result.body;
    result.status_code := 200;
    end_time := clock_timestamp();
    INSERT INTO watchdog(function_name, execution_duration) VALUES ('proc_transacao', end_time - start_time);
    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;
    start_time TIMESTAMP;
    end_time TIMESTAMP;
BEGIN
    start_time := clock_timestamp();

    SELECT saldo
        INTO v_saldo
        FROM clientes
        WHERE id = p_cliente_id;

    v_limite := 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 -- Valor padrão caso o id do cliente não esteja entre 1 e 5
    END;

    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
                FROM transacoes
                WHERE cliente_id = p_cliente_id
                ORDER BY realizada_em DESC
                LIMIT 10
            ) t
        ), '[]')
    ) INTO result.body;
    result.status_code := 200;
    end_time := clock_timestamp();
    INSERT INTO watchdog(function_name, execution_duration) VALUES ('proc_extrato', end_time - start_time);
    RETURN result;
END;
$$ LANGUAGE plpgsql;