sql_bank/01_bank.sql

--show server_version;

-- ----------------------------------------------------------------------
-- extensions
-- ----------------------------------------------------------------------

--select * from pg_available_extensions;

--show shared_preload_libraries;

alter database iut SET session_preload_libraries = 'anon';

create schema if not exists ext;
create extension if not exists pgcrypto schema ext;
create extension if not exists tablefunc;

create extension if not exists pg_cron;

create extension if not exists timescaledb;

--drop schema if exists public cascade;
--create schema public;

create type holder_type as enum ('BANK', 'PERSON', 'COMPANY');

create table holder (
  "id" bigint primary key generated always as identity,
  "type" holder_type not null,
  "created_at" timestamptz not null default current_timestamp
);

/************************************************************************
 * Bank
 * Liée par références à titulaire (holder) pas d'incrément automatique
 * puisque c'est celui de titulaire
 ************************************************************************/
create table bank (
  "id" bigint primary key references holder(id),
  "name" text not null
);

insert into holder (type) values ('BANK') returning holder;
insert into bank (id, name) values (1, 'Banque de l''Est');

/************************************************************************
 * Person
 * Liée par références à titulaire (holder) pas d'incrément automatique
 * on delete cascade : Lors de la suppression du titualaire, person est
 * supprimé aussi. Sans cela la suppression est interdite.
 ************************************************************************/
create table person (
  "id" bigint primary key references holder(id) on delete cascade,
  "firstname" text not null,
  "lastname" text not null,
  "birthdate" date not null check (birthdate <= current_date - interval '15 years')
);

/************************************************************************
 * Company
 *
 ************************************************************************/
create table company (
  "id" bigint primary key references holder(id) on delete cascade,
  "name" text not null,
  "registration_number" text unique not null,
  "creation_date" date not null
);

/************************************************************************
 * Company
 *
 ************************************************************************/
create table currency (
  "code" text primary key,
  "name" text
);

insert into currency
  values ('EUR', 'Euro'), ('JPY', 'Yen'), ('USD', 'US Dollar'),
  ('GBP', 'Livre Sterling'), ('KRW', 'Won'), ('ZAR', 'Rand sud africain'),
  ('CHF', 'Franc suisse'),
  ('AUD', 'Dollar australien'), ('CAD', 'Dollar canadien'),
  ('SEK', 'Couronne suédoise'),
  ('NOK', 'Couronne norvégienne');

/************************************************************************
 * Exchange Rate
 * La clé primaire est sur les deux colonnes currency_code et date
 * Il n'y a qu'un seul taux de change par monnaie ET par jour
 ************************************************************************/
create table exchange_rate (
  "currency_code" text references currency(code) on delete cascade,
  "date" date ,
  "rate" decimal not null,
  primary key (currency_code, date)
);

drop table if exists eurofxref;

create temporary table if not exists eurofxref (
  "date" date primary key,
  USD decimal, JPY decimal, BGN text, CYP text, CZK decimal,
  DKK decimal, EEK text, GBP decimal, HUF decimal, LTL text,
  LVL text, MTL text, PLN decimal, ROL text, RON text,
  SEK decimal, SIT text, SKK text, CHF decimal, ISK text,
  NOK decimal, HRK text, RUB text, TRL text, TRY text,
  AUD decimal, BRL text, CAD decimal, CNY text, HKD decimal,
  IDR text, ILS text, INR text, KRW decimal, MXN text,
  MYR text, NZD decimal, PHP text, SGD decimal, THB text,
  ZAR decimal,
  dumb text
);

\copy eurofxref FROM '/docker-entrypoint-initdb.d/eurofxref-hist.csv' (FORMAT CSV, header, delimiter ',', ENCODING 'UTF8');

insert into exchange_rate
select 'USD', date, USD  from eurofxref;

insert into exchange_rate
select 'JPY', date, JPY from eurofxref;

insert into exchange_rate
select 'GBP', date, GBP from eurofxref;

insert into exchange_rate
select 'KRW', date, KRW from eurofxref;

insert into exchange_rate
select 'ZAR', date, ZAR from eurofxref;

/*insert into exchange_rate
select 'BRL', date, BRL from eurofxref where BRL <> 'N/A';*/

insert into exchange_rate
select 'CHF', date, CHF from eurofxref;

insert into exchange_rate
select 'AUD', date, AUD from eurofxref;

insert into exchange_rate
select 'CAD', date, CAD from eurofxref;

/*insert into exchange_rate
select 'MXN', date, MXN from eurofxref;*/

insert into exchange_rate
select 'NOK', date, NOK from eurofxref;

insert into exchange_rate
select 'SEK', date, NOK from eurofxref;

create or replace function latest_exchange_rate (
  p_code text,
  p_date date
)
returns decimal
language sql
as $$
select rate
  from exchange_rate
  where currency_code = p_code
    and date < p_date
    order by date desc
    limit 1;
$$;

-- Jeu de caractères utilisé
CREATE OR REPLACE FUNCTION chars36()
RETURNS text AS $$
    SELECT '12345ABCDE';
$$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION char_to_int(c text)
RETURNS int AS $$
DECLARE
    pos int;
BEGIN
    pos := position(c IN chars36()) - 1;
    IF pos < 0 THEN
        RAISE EXCEPTION 'Invalid base36 character: %', c;
    END IF;
    RETURN pos;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION compute_checksum(code5 text)
RETURNS text AS $$
DECLARE
    chars text := chars36();
    weights int[] := ARRAY[7, 3, 1, 9, 11, 5];
    total int := 0;
    c text;
    i int;
BEGIN

    FOR i IN 1..length(code5) LOOP
        c := substr(code5, i, 1);
        total := total + char_to_int(c) * weights[i];
    END LOOP;

    total := total % length(chars);
    RETURN substr(chars, total + 1, 1);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION rand_account(n integer)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
  chars text := chars36();
  out text := '';
  b bytea := ext.gen_random_bytes(n); -- n octets aléatoires
  i int;
  idx int;
  check_char text;
begin
  IF n <= 0 THEN
    RAISE EXCEPTION 'La longueur doit être > 0';
  END IF;

  FOR i IN 0..(n - 1) LOOP
    idx := (get_byte(b, i) % length(chars)) + 1;
    out := out || substr(chars, idx, 1);
  END LOOP;

  RETURN out || compute_checksum(out);
end;
$$;


CREATE OR REPLACE FUNCTION is_valid_account_number(code text)
RETURNS boolean AS $$
DECLARE
    base5 text;
    given_checksum text;
    expected_checksum text;
BEGIN
    -- 1. vérification format
    IF code IS NULL OR length(code) <> 6 THEN
        RETURN false;
    END IF;

    -- 2. séparation
    base5 := upper(substr(code, 1, 5));     -- normalisation
    given_checksum := upper(substr(code, 6, 1));

    -- 3. validation caractères autorisés
    IF base5 ~ '[^0-9A-Z]' OR given_checksum ~ '[^0-9A-Z]' THEN
        RETURN false;
    END IF;

    -- 4. recalcul de la vraie clé de contrôle
    expected_checksum := compute_checksum36(base5);

    -- 5. comparaison
    RETURN expected_checksum = given_checksum;
END;
$$ LANGUAGE plpgsql;

/*
 * Account
 * La contrainte check (balance >= 0) empêche les soldes négatifs
 */

create table account (
  "id" bigint primary key generated always as identity,
  "opened_at" date not null default current_date,
  "balance" numeric(18,6) not null default 0,
  "currency_code" text not null references currency (code)
);

/*
 * Account -> Holder
 * Cette table établit la relation n–n entre account et holder.
 * Un compte joint correspond donc à plusieurs lignes dans cette table.
 * La contrainte share assure que les parts sont comprises entre 0 et 1.
 */
create table account_holder (
  "account_id" int not null references account(id) on delete cascade,
  "holder_id" int not null references holder(id) on delete cascade,
  "share" decimal(4,3) not null default 1 check (share > 0 and share <= 1),
  primary key (account_id, holder_id)
);

create table transaction (
  "id" bigint primary key generated always as identity,
  "transaction_date" timestamptz not null default current_timestamp,
  amount decimal not null check (amount > 0)
);

create table operation (
  "id" bigint primary key generated always as identity,
  "transaction_id" bigint not null references transaction(id),
  "account_id" bigint not null references account(id),
  amount decimal not null check (amount > 0),
  direction text not null check (direction in ('DEBIT', 'CREDIT'))
);

/************************************************************************
 * Procédures
 * Permet dans un bloc de code d'exécuter plusieurs opérations d'un seul coup
 * Si une erreur survient RIEN n'est enregistré
 ************************************************************************/
create or replace procedure add_bank (
  name text
)
language plpgsql
as $$
declare
  v_holder_id bigint;
begin

  insert into holder(type) values ('BANK')
  returning id into v_holder_id;

  insert into bank(id, name)
  values (v_holder_id, name);

  raise notice 'Titulaire créé : % = %',
    v_holder_id, name;
end;
$$;

create or replace procedure add_person (
  p_firstname text,
  p_lastname text,
  p_birthdate date
)
language plpgsql
as $$
declare
  v_holder_id bigint;
begin

  insert into holder(type) values ('PERSON')
  returning id into v_holder_id;

  insert into person(id, firstname, lastname, birthdate)
  values (v_holder_id, p_firstname, p_lastname, p_birthdate);

  raise notice 'Titulaire créé : % = % %',
    v_holder_id, p_firstname, p_lastname;
end;
$$;

call add_person('Françoise', 'Zanetti', '1995-04-12');
call add_person('Justin', 'Hébrard', '1993-03-11');

create or replace procedure add_company (
 p_name text,
 p_registration_number text,
 p_creation_date date
)
language plpgsql
as $$
declare
  v_holder_id bigint;
begin

  insert into holder(type) values ('COMPANY')
  returning id into v_holder_id;

  insert into company(id, name, registration_number, creation_date)
  values (v_holder_id, p_name, p_registration_number, p_creation_date);

  raise notice 'Titulaire créé : % = % %',
    v_holder_id, p_name, p_registration_number;
end;
$$;

call add_company('Boulangerie de Valorgue', 'FR19803269968', '2014-08-19');

create or replace procedure add_account (
    p_currency text,
    p_holders int[],
    p_shares numeric[]
)
language plpgsql
as $$
declare
    v_account_id int;
    v_sum numeric(6,4) := 0;
    v_count int;
begin
    -- Vérification des tailles
    if array_length(p_holders, 1) is null or array_length(p_shares, 1) is null then
        raise exception 'Les tableaux de titulaires et de parts ne peuvent pas être vides.';
    end if;

    if array_length(p_holders, 1) <> array_length(p_shares, 1) then
        raise exception 'Les tableaux de titulaires et de parts doivent avoir la même taille.';
    end if;

    -- Calcul de la somme des parts
    -- select sum(unnest(p_shares)) into v_sum;
    for i in 1..array_length(p_shares, 1) loop
        v_sum := v_sum + p_shares[i];
    end loop;

    if abs(v_sum - 1.0) > 0.0001 then
        raise exception 'La somme des parts (%.4f) doit être égale à 1.0000', v_sum;
    end if;

    -- Vérification des titulaires
    select count(*) into v_count from holder where id = any(p_holders);
    if v_count <> array_length(p_holders, 1) then
        raise exception 'Un ou plusieurs titulaires n''existent pas.';
    end if;

    -- Création du compte
    insert into account(currency_code, balance)
    values (p_currency, 0)
    returning id into v_account_id;

    -- Association des titulaires
    for i in 1..array_length(p_holders, 1) loop
        insert into account_holder(account_id, holder_id, share)
        values (v_account_id, p_holders[i], p_shares[i]);
    end loop;

    raise notice 'Compte créé avec succès (ID=%) avec % titulaires.',
        v_account_id, array_length(p_holders, 1);
end;
$$;

call add_account('EUR', array[1], array[1]);
call add_account('USD', array[1], array[1]);
call add_account('JPY', array[1], array[1]);

update account set balance = 100 where id = 1;
update account set balance = 500 where id = 2;
update account set balance = 200 where id = 3;

call add_account('EUR', array[2, 3], array[0.6, 0.4]);

call add_person('Kaneda', 'Shōtarō', '1982-12-20');
call add_account('JPY', array[5], array[1]);

call add_company('Boucherie Sanzot', 'FR68487684235', '2014-08-14');
call add_account('EUR', array[6], array[1]);

call add_company('America Logistics', '95-4524149', '1996-09-24');
call add_account('EUR', array[7], array[1]);
call add_account('USD', array[7], array[1]);

call add_person('Robert','Lespinasse','1990-01-01');
call add_person('Bernard','Morin','1990-01-01');
call add_person('Gisèle','André','1990-01-01');
call add_person('Nathalie','Morin','1990-01-01');

/************************************************************************
 * Dépôts et retraits
 ************************************************************************/
create or replace procedure add_depot (
  p_account_id bigint,
  p_amount decimal
)
language plpgsql
as $$
declare
  v_id bigint;
begin
  insert into transaction (amount)
  values (p_amount)
  returning id into v_id;

  insert into operation (transaction_id, account_id, amount, direction)
  values (v_id, p_account_id, p_amount, 'CREDIT');

  update account set balance = balance +  p_amount where id = p_account_id;

  raise notice 'Dépôt de % sur le compte %', p_amount, p_account_id;
end;
$$;

create or replace procedure add_retrait (
  p_account_id bigint,
  p_amount decimal
)
language plpgsql
as $$
declare
  v_id bigint;
begin
  insert into transaction (amount)
  values (p_amount)
  returning id into v_id;

  insert into operation (transaction_id, account_id, amount, direction)
  values (v_id, p_account_id, p_amount, 'DEBIT');

  update account set balance = balance -  p_amount where id = p_account_id;

  raise notice 'Retrait de % sur le compte %', p_amount, p_account_id;
end;
$$;

call add_depot(4, 100);
call add_retrait(4, 20);

call add_depot(5, 150);
call add_depot(6, 200);
call add_depot(7, 180);
call add_depot(8, 300);

call add_retrait(5, 30);
call add_depot(5, 10);
call add_depot(5, 60);

create or replace view holder_detail as
select h.id, h.type,
  case
  when type = 'PERSON' then firstname || ' ' || lastname
  else name
  end as nom,
  case
  when type = 'PERSON' then age(birthdate)
  when type = 'COMPANY' then age(c.creation_date)
  end as age
from holder h
  left join person p on p.id = h.id
  left join company c on c.id = h.id;

create or replace view account_detail as
select a.balance,
  a.balance * ah.share as balance_currency,
  a.balance * ah.share / latest_exchange_rate(a.currency_code, current_date),
  a.currency_code,
  hd.nom
from account a
join account_holder ah on ah.account_id = a.id
join holder_detail hd on ah.holder_id = hd.id;


/************************************************************************
 * Transactions
 ************************************************************************/

insert into transaction (amount) values (100);

insert into operation (transaction_id, account_id, amount, direction)
  values (1, 4, 100, 'CREDIT');

insert into operation (transaction_id, account_id, amount, direction)
  values (1, 1, 100, 'DEBIT');

insert into transaction (amount) values (10000);

insert into operation (transaction_id, account_id, amount, direction)
  values (2, 5, 10000, 'CREDIT');

insert into operation (transaction_id, account_id, amount, direction)
  values (2, 3, 10000, 'DEBIT');

/************************************************************************
 * Vues
 ************************************************************************/

create or replace view holder_details as
select h.id, h.type,
  case
  when type = 'PERSON' then firstname || ' ' || lastname
  else name
  end as nom,
  case
  when type = 'PERSON' then age(birthdate)
  when type = 'COMPANY' then age(c.creation_date)
  end as age
from holder h
  left join person p on p.id = h.id
  left join company c on c.id = h.id;

create or replace view account_details as
select
  a.balance,
  a.currency_code,
  hd.nom, hd.age
from account a
join account_holder ah on ah.account_id = a.id
join holder_details hd on ah.holder_id = hd.id;