-- Exercice 1.1.1
-- En SQL
CREATE TABLE country (
    code TEXT PRIMARY KEY,
    name TEXT
);
CREATE TABLE virement (
    id INTEGER PRIMARY KEY,
    from_pers_name TEXT,
    to_pers_name TEXT,
    amount INTEGER,
    date TEXT,
    country_code_from TEXT,
    country_code_to TEXT,
    FOREIGN KEY (country_code_from) REFERENCES country(code),
    FOREIGN KEY (country_code_to)   REFERENCES country(code)	
);


## Exercice 1.2.1
## En python
import random
import datetime
PERS = ['TOTO', 'TATA', 'BULB']
COUN = ['FRA','BMU','BEL','HND']
date = datetime.datetime.now ()
delta = datetime.timedelta(0,50) # 50 sec
million = 1000000
f = open ("transactions.csv","w")
for i in range (10 * million):
  frm   = random.choice (PERS)
  to     = random.choice (PERS)
  amount = random.random () * 100
  date   += delta
  c1 = random.choice (COUN)
  c2 = random.choice (COUN)
  f.write ("{},{},{},{},{},{},{}\n".format( i, frm, to, amount, date, c1, c2))
f.close ()
## Import data to sqlite
## [user@ordin IngeSI/TP]$ sqlite3 tp.db
## SQLite version 3.31.1 2020-01-27 19:55:54
## Enter ".help" for usage hints.
## sqlite> .mode csv
## sqlite> .import transactions.csv virement



-- Exercice 1.2.2
-- En SQL
select count(*) from virement where amount > 90; -- lent
create index test on virement (amount);
select count(*) from virement where amount > 90; -- rapide
drop index test;
select count(*) from virement where amount > 90; -- lent

select count(*) from virement where from_pers_name = 'TATA'; -- lent
create index from_ind on virement (from_pers_name);
select count(*) from virement where from_pers_name = 'TATA'; -- rapide

select count(*) from virement where from_pers_name = 'TATA' and amount > 90; -- lent
create index amount_ind on virement (from_pers_name);
select count(*) from virement where from_pers_name = 'TATA' and amount > 90; -- lent
create index from_and_amount_ind on virement (amount, from_pers_name); 
select count(*) from virement where from_pers_name = 'TATA' and amount > 90; -- rapide

-- Exercice 1.2.3
-- En SQL
-- for personne TATA
select 
COALESCE ( (select sum(amount) from virement where to_pers_name = 'TATA'), 0) -
COALESCE ( (select sum(amount) from virement where from_pers_name = 'TATA'), 0)

-- Exercice 1.3.1
-- En SQL
CREATE TABLE PERSONNE (
   pers_name TEXT PRIMARY KEY,
   solde INTEGER DEFAULT 0
);

-- Exercice 1.3.2
-- En SQL
insert into PERSONNE
select distinct n, 0 from
(select distinct to_pers_name as n from virement
 union
 select distinct from_pers_name as n from virement
);
update PERSONNE as p
set solde = 
( select 
  COALESCE ( (select sum(amount) from virement where to_pers_name = p.pers_name), 0) -
  COALESCE ( (select sum(amount) from virement where from_pers_name = p.pers_name), 0)
);


-- Exercice 1.3.3
-- En SQL
-- AFTER INSERT
drop trigger insert_transaction;
CREATE TRIGGER insert_transaction
 AFTER INSERT ON VIREMENT
 BEGIN
   -- create PERSONNEs if someone not exist
   INSERT INTO PERSONNE (pers_name, solde) 
   SELECT new.from_pers_name, 0
   WHERE NOT EXISTS (SELECT 1
                     FROM PERSONNE
                     WHERE pers_name = new.from_pers_name);
   INSERT INTO PERSONNE (pers_name, solde)
   SELECT new.to_pers_name, 0
   WHERE NOT EXISTS (SELECT 1
                     FROM PERSONNE
                     WHERE pers_name = new.to_pers_name);
   UPDATE PERSONNE SET solde = solde + new.amount WHERE pers_name = new.to_pers_name ;
   UPDATE PERSONNE SET solde = solde - new.amount WHERE pers_name = new.from_pers_name ;
END;

-- AFTER DELETE
drop trigger delete_transaction;
CREATE TRIGGER delete_transaction
 AFTER delete ON VIREMENT
 BEGIN
   -- create PERSONNEs if someone not exist
   INSERT INTO PERSONNE (pers_name, solde) 
   SELECT old.from_pers_name, 0
   WHERE NOT EXISTS (SELECT 1
                     FROM PERSONNE
                     WHERE pers_name = old.from_pers_name);
   INSERT INTO PERSONNE (pers_name, solde)
   SELECT old.to_pers_name, 0
   WHERE NOT EXISTS (SELECT 1
                     FROM PERSONNE
                     WHERE pers_name = old.to_pers_name);
   UPDATE PERSONNE SET solde = solde - old.amount WHERE pers_name = old.to_pers_name ;
   UPDATE PERSONNE SET solde = solde + old.amount WHERE pers_name = old.from_pers_name ;
END;

-- AFTER UPDATE
drop trigger update_transaction;
CREATE TRIGGER update_transaction
 AFTER UPDATE ON VIREMENT
 BEGIN
   -- create PERSONNEs if someone not exist
   INSERT INTO PERSONNE (pers_name, solde) 
   SELECT old.from_pers_name, 0
   WHERE NOT EXISTS (SELECT 1
                     FROM PERSONNE
                     WHERE pers_name = old.from_pers_name);
   INSERT INTO PERSONNE (pers_name, solde)
   SELECT old.to_pers_name, 0
   WHERE NOT EXISTS (SELECT 1
                     FROM PERSONNE
                     WHERE pers_name = old.to_pers_name);
   INSERT INTO PERSONNE (pers_name, solde) 
   SELECT new.from_pers_name, 0
   WHERE NOT EXISTS (SELECT 1
                     FROM PERSONNE
                     WHERE pers_name = new.from_pers_name);
   INSERT INTO PERSONNE (pers_name, solde)
   SELECT new.to_pers_name, 0
   WHERE NOT EXISTS (SELECT 1
                     FROM PERSONNE
                     WHERE pers_name = new.to_pers_name);
   UPDATE PERSONNE SET solde = solde - old.amount WHERE pers_name = old.to_pers_name ;
   UPDATE PERSONNE SET solde = solde + old.amount WHERE pers_name = old.from_pers_name ;
   UPDATE PERSONNE SET solde = solde + new.amount WHERE pers_name = new.to_pers_name ;
   UPDATE PERSONNE SET solde = solde - new.amount WHERE pers_name = new.from_pers_name ;
END;


# Exercice 1.3.4 : à tester chez vous

# Exercice 1.4.1 :
# En Python
# voir un exemple pour la fonction 'mode' donné au-dessus de l'exercice 1.4.1.
# Les définitions de fonction vous pouvez trouver ici :
#      https://docs.python.org/3/library/statistics.html

# Exercice 1.4.2 :
#      https://fr.wikipedia.org/wiki/Asymétrie_(statistiques)
#      https://fr.wikipedia.org/wiki/Kurtosis

# Exercice 1.4.3 : à tester chez vous

# Exercice 2.0.1 : à faire chez vous
# Exercice 2.0.2 : à lire chez vous, https://docs.mongodb.com/manual/indexes/#b-tree
// Exercice 2.0.3 :
// En JavaScript, en Mongo Shell:
db.virement.createIndex( {amount : -1} )
db.virement.createIndex( {from_pers_name : 1} )
db.virement.createIndex( {from_pers_name : 1, amount : 1} )


# Exercice 3.0.1 : à lire chez vous