init.sql

-- DDL
CREATE TABLE account
(
    ID              INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    document_number VARCHAR(11),
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE operationtype
(
    ID          INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    description VARCHAR(50)
);

CREATE TABLE transaction
(
    ID               UUID PRIMARY KEY DEFAULT uuidv7(),
    account_id       INTEGER REFERENCES account (ID),
    operationtype_id INTEGER REFERENCES operationtype (ID),
    amount           INTEGER,
    balance          INTEGER,
    eventdate        TIMESTAMP
);


-- DML - Initial data
-- Account
INSERT INTO account
VALUES (1, '11111111111'),
       (2, '22222222222');
SELECT setval(pg_get_serial_sequence('account', 'id'), (SELECT MAX(id) FROM account));

-- Operation Types
INSERT INTO operationtype
VALUES (1, 'Normal Purchase'),
       (2, 'Purchase with installments'),
       (3, 'Withdrawal'),
       (4, 'Credit Voucher');
SELECT setval(pg_get_serial_sequence('operationtype', 'id'), (SELECT MAX(id) FROM operationtype));

-- Transaction
INSERT INTO transaction (ID, account_id, operationtype_id, amount, balance, eventdate)
VALUES (uuidv7(), 1, 1, -5000, 0, '2020-01-01T10:32:07.7199222'),
       (uuidv7(), 1, 1, -2350, -1350, '2020-01-01T10:32:08.7199222'),
       (uuidv7(), 1, 1, -1870, -1870, '2020-01-01T10:32:09.7199222'),
       (uuidv7(), 1, 4, 6000, 0, '2020-01-01T10:32:10.7199222');