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


-- Fetch a user account by its ID.
--
-- :name fetch_account
-- :result :one-or-none
select * from accounts where id = :user_id;


-- Fetch a user account by its username.
--
-- :name fetch_account_username
-- :result :one-or-none
select * from accounts where username = :username;


-- Create a new user account with the given username and hashed password.
--
-- :name create_account
-- :result :one-or-none
insert into accounts (username, password_hash)
  values (:username, :password_hash)
  returning *;


-- Fetch all the transactions involving a given user.
-- The returned transaction log contains the usernames of the involved parties.
-- The sign of the amount column is adjusted to the perspective of the user,
-- so that its sum equals the balance of their account.
--
-- :name fetch_transactions
-- :result :many
select
    transactions.id, datetime, operation,
    account_sources.username as source,
    account_recipients.username as recipient,
    case when source = :user_id then -amount else amount end as amount
  from transactions
  left join accounts as account_sources on account_sources.id = source
  left join accounts as account_recipients on account_recipients.id = recipient
  where source = :user_id or recipient = :user_id
  order by datetime desc, operation;


-- Deposit the specified amount to the account of the specified user and
-- generate a matching transaction log entry.
--
-- This query should be executed within a transaction with the serializable
-- isolation level.
--
-- :name deposit
-- :result :one
update accounts set balance = balance + :amount where id = :user_id;
insert into transactions (operation, recipient, amount)
  values ('deposit', :user_id, :amount)
  returning *;


-- Withdraw the specified amount from the account of the specified user and
-- generate a matching transaction log entry.
--
-- This query should be executed within a transaction with the serializable
-- isolation level.
--
-- :name withdraw
-- :result :one
update accounts set balance = balance - :amount where id = :user_id;
insert into transactions (operation, source, amount)
  values ('withdrawal', :user_id, :amount)
  returning *;


-- Transfer the specified amound from the account of a sender to the one of a
-- recipient, generating matching transaction log entries for the transfer
-- itself and the transfer fee if any is applicable.
--
-- This query should be executed within a transaction with the serializable
-- isolation level.
--
-- :name transfer
-- :result :one
update accounts set balance = balance - :amount - :fee where id = :from_user_id;
update accounts set balance = balance + :amount where id = :to_user_id;
insert into transactions (operation, source, amount)
  select 'fee', :from_user_id, :fee where cast(:fee as simoleon) > 0;
insert into transactions (operation, source, recipient, amount)
  values ('transfer', :from_user_id, :to_user_id, :amount)
  returning *;