-- ********************************************************************
-- Version 1.0.0 - 27/05/2008
-- Buts : 
--    Créer un portefeuille d'action
--    Créer les lignes du portefeuille
--    Créer les décisions de vente ou d'achat
--    Créer un historique des transactions
-- ********************************************************************

select 'Creation du portefeuille ...';
DROP TABLE IF EXISTS portefeuille;
DROP TABLE IF EXISTS lignes;
DROP TABLE IF EXISTS mouvements;
-- VACUUM;

-- ********************************************************************
-- Table contenant une seule ligne : le portefeuille d'action
-- le liquide, le montant total des actions, 
-- le total des frais depuis l'ouverture, 
-- la dernière date de l'évaluation, l'estimation total du portefeuille 
-- et le nombre de lignes. 
-- Optionnel: On peut ajouter dans cette table 
-- le seuil positif de vente (15%) et le seuil négatif de vente (-15%) 
-- ainsi il sera facile de faire évoluer ces pourcentages.
--
--   Especes NUMBER, -- Reste en liquidité
--   Montant NUMBER, -- Evaluation des titres
--   Frais   NUMBER, -- Cumul des frais
--   Dat NUMBER,     -- Date AAAAMMJJ de la dernière évaluation 
--   NbrLigne NUMBER -- Nombre de lignes en portefeuille 
-- ********************************************************************

BEGIN TRANSACTION;

DROP TABLE IF EXISTS portefeuille;
CREATE TABLE portefeuille (
  Especes NUMBER,
  Montant NUMBER,
  Frais NUMBER,
  Dat INTEGER,
  NbrLigne INTEGER
);


-- ********************************************************************
-- Vue sur le portefeuille qui calcule AA, MM et JJ depuis Dat
-- Nb: les calculs s'effectuent en format INTEGER
--     donc (dat/10000)*100 est égal à TRUNC(dat/10000)*100
-- DebutDuMois = 20050700 pour le repérer le début du mois 07/2005
-- ********************************************************************
DROP VIEW IF EXISTS V_portefeuille;
CREATE VIEW V_portefeuille AS 
SELECT 
  Especes AS Especes,
  Montant AS Montant,
  Especes + Montant AS Estimation,
  Frais AS Frais,
  dat/10000 AS AA,
  dat/100-(dat/10000)*100 AS MM,
  dat-(dat/100)*100 AS JJ,
  Dat AS Dat,
  (dat/10000)*10000 + (dat/100-(dat/10000)*100)*100 AS DebutDuMois,
  NbrLigne AS NbrLigne,
  10-NbrLigne AS NbrMaxAchat
FROM portefeuille;



-- ********************************************************************
-- Vue très complexe qui tris les cours des valeurs
-- selon la meilleure performance sur le mois écoulé
-- ********************************************************************
DROP VIEW IF EXISTS V_performance;
CREATE VIEW V_performance AS 
SELECT 
  d.id AS id, 
  d.rga AS rga, 
  d.dat AS dat, 
  d.der AS der, 
  d.pre as pre, 
  d.der as der, 
  d.nextid as nextid, 
  d.lastid as lastid,
  round((d.der-c.der)/c.der*100, 2) as gain
FROM (
      -- retrouve les cours du debut du mois précédent
      select a.rga as rga,  a.dat as dat, a.der as der from cours a, 
      (select min(id) as id, rga from cours 
        where dat>=(
          -- retrouve le debut du mois precedent
          select (dat/100)*100 from cours where id = (
            select min(lastid) from cours where dat>= (
              select DebutDuMois from v_portefeuille))
        )
      group by rga) b
      where a.id = b.id and a.dat <= (select DebutDuMois from v_portefeuille)
) c, (
      select b.id as id, b.rga as rga, b.dat as dat, 
      b.pre as pre, b.der as der, 
      b.nextid as nextid, b.lastid as lastid
      from cours b
      where b.dat = (select dat from portefeuille)
) d
WHERE c.rga = d.rga
ORDER BY gain DESC;


-- ********************************************************************
-- Table contenant toutes les lignes d'action
-- Une ligne est composée de :
--   le rga de l'action (clé primaire)
--   le nombre d'action
--   le prix d'achat (avec les frais)
--   le prix des frais
-- ********************************************************************

DROP TABLE IF EXISTS lignes;
CREATE TABLE lignes (
  rga TEXT PRIMARY KEY,
  Nombre INTEGER,
  Prix   NUMBER,
  Frais  NUMBER
);

-- ********************************************************************
-- Vue sur les lignes qui calcule PR 
-- ********************************************************************
DROP VIEW IF EXISTS V_lignes;
CREATE VIEW V_lignes AS 
SELECT 
  rga AS rga,
  Nombre AS Nombre,
  Prix   AS Prix,
  Frais  AS Frais,
  round(Prix*1.0/Nombre, 2) AS PR
FROM lignes;

-- ********************************************************************
-- Table contenant une décision de vente ou d'achat d'une action
-- La création d'une ligne dans cette table déclenche la mise à jour
-- du portefeuille à l'aide d'un TRIGGER.
--
-- Contenu de la table :
--   l'identifiant de la décision
--   la date de vente ou d'achat
--   le sens indique 'Vente' ou 'Achat'
--   le rga de la valeur
--   le prix unitaire
--   le nombre d'action
-- ********************************************************************
DROP TABLE IF EXISTS decisions;
CREATE TABLE decisions (
  id  INTEGER PRIMARY KEY,
  dat INTEGER,
  sens TEXT,
  rga TEXT,
  nombre INTEGER,
  prix NUMBER
);

-- ********************************************************************
-- Vue contenant l'historique des mouvements achats et ventes
-- En fait, c'est une vue enrichie de la table des décisions.
--
-- Contenu de la vue :
--   l'identifiant de la décision
--   la date de vente ou d'achat AAAAMMDD
--   le sens Vente ou Achat
--   le rga de l'action
--   le libelle de l'action
--   le nombre de titres
--   les frais
--   le prix total (incluant les frais)
-- ********************************************************************
DROP VIEW IF EXISTS historique;
CREATE VIEW historique AS 
SELECT 
  d.id  AS id,
  d.dat AS dat,
  d.sens AS sens,
  d.rga AS rga,
  c.lib AS lib,
  d.nombre AS nombre,
  d.prix AS prix,
  case 
    when (d.nombre*d.prix*0.0065)<8 
    then 8 
    else round(d.nombre*d.prix*0.0065, 2) 
  end AS frais,
  case 
    when d.sens = 'Vente'
    then
      case 
        when (d.nombre*d.prix*0.0065)<8 
        then round(d.nombre*d.prix - 8, 2)
        else round(d.nombre*d.prix*(1-0.0065), 2)
      end
    else
      case 
        when (d.nombre*d.prix*0.0065)<8 
        then round(d.nombre*d.prix + 8, 2)
        else round(d.nombre*d.prix*(1+0.0065), 2)
      end
    end AS total 
FROM decisions d, cac c
WHERE d.rga = c.rga;

-- ********************************************************************
-- Trigger de mise à jour du portefeuille à chaque décision 
-- de vente ou d'achat d'une action. C'est le coeur de la gestion.
--
-- Contenu de la table :
--   la date de vente ou d'achat
--   le sens indique 'Vente' ou 'Achat'
--   le rga de la valeur
--   le prix unitaire
--   le nombre d'action
-- ********************************************************************
DROP TRIGGER IF EXISTS trg_decisions;
CREATE TRIGGER trg_decisions AFTER INSERT ON decisions
FOR EACH ROW BEGIN

  -- creer la nouvelle ligne si elle n'existe pas encore
  INSERT INTO lignes 
    SELECT rga, 0, 0.0, 0.0 FROM decisions 
    WHERE id = new.id and rga not in (select rga from lignes);
 
  -- ajouter la transaction lors d'un achat
  UPDATE lignes SET 
    nombre = nombre + new.nombre,
    frais = frais + (select frais from historique where id = new.id),
    prix = prix + (select total from historique where id = new.id)
  WHERE new.sens = 'Achat' AND new.rga = rga
    AND new.dat >= (select dat from portefeuille);

  -- retirer la transaction lors d'une vente
  -- On réestime le prix d'achat des valeurs restantes
  UPDATE lignes SET 
    prix = round( (prix*1.0 / nombre) * (nombre - new.nombre), 2),
    nombre = nombre - new.nombre,
    frais = frais + (select frais from historique where id = new.id)
  WHERE new.sens = 'Vente' AND new.rga = rga
    AND new.dat >= (select dat from portefeuille);

  -- maj des frais et des especes dans le portefeuille
  UPDATE portefeuille SET
    frais = frais + (select frais from historique where id = new.id),
    especes = (
        case when new.sens = 'Vente' 
          then especes + (select total from historique where id = new.id)
          else especes - (select total from historique where id = new.id) 
        end);

  -- retirer les lignes vides
  DELETE FROM lignes WHERE nombre <= 0;
  
  -- maj le nombre de lignes dans le portefeuille
  UPDATE portefeuille 
    set NbrLigne = (select count(*) from lignes);

END;

-- Initialisation avec 15000 euros au 1er janvier 2005
insert into portefeuille values (15000.00, 0.00, 0.00, 20050101, 0);
/*
-- création d'un portefeuille virtuel :
insert into decisions values (NULL, 20050101, 'Achat', 'FR0000120404', 50, 30.0);
insert into decisions values (NULL, 20050101, 'Vente', 'FR0000120404', 21, 31.0);

select 'Decisions:';
select * from Decisions;
select 'Historique:';
select * from Historique;
select 'Lignes:';
select * from v_Lignes;
select 'Portefeuille:';
select * from Portefeuille;
*/
END TRANSACTION;


select '';
select 'Ok.';
select '';