-- UGE / L2 / Intro to relational databases / Python project prototype
-- Author: Pacien TRAN-GIRARD
-- Licence: EUPL-1.2

-- Fixed precision type for our one fictive currency.
-- https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_money
create domain simoleon as numeric(11, 2);

-- Minimalist modelling of user accounts.
-- In this simple application, the notion of user, account and wallet/balance
-- are merged into a single table instead of trivial 1-1 relationships.
-- Balance consistency with other accounts and financial transaction logs will
-- have to be ensured through SQL transactions.
create table accounts (
  id int generated always as identity primary key,
  username text unique not null,
  password_hash text not null,
  balance simoleon default 0 not null check (balance >= 0)
);

-- Enumeration of the different types of logged financial operations.
create type operation_type as enum (
  'fee',
  'transfer',
  'deposit',
  'withdrawal'
);

-- Minimalist, combined financial transaction log.
-- Thanks to the similarity of the fields for the various operation types, it
-- is possible to use a combined transaction log with a discriminant enum and
-- a few nullity constraints. This allows for simpler queries without the need
-- to consolidate and aggregate across multiple tables.
create table transactions (
  id bigint generated always as identity primary key,
  datetime timestamp default transaction_timestamp() not null,
  operation operation_type not null,
  source int references accounts check
    ((operation = 'deposit' or source is not null) and source <> recipient),
  recipient int references accounts check
    (operation in ('fee', 'withdrawal') or recipient is not null),
  amount simoleon not null check (amount > 0)
);

-- Additional indexes for the financial transaction log for the common
-- participant-centered queries. Those also include the datetime column, which
-- is useful for paginating the log.
create index transaction_source_index on transactions (source, datetime);
create index transaction_recipient_index on transactions (recipient, datetime);