DROP TABLE STOCKS;
DROP TABLE MAGASINS;
DROP TABLE PRODUITS;
DROP TABLE GROUPES;

-----------------------------------------------------------------
CREATE TABLE GROUPES(
  id_grp NUMBER CONSTRAINT pk_Grp PRIMARY KEY,
  nom_grp VARCHAR2(64)
);

CREATE TABLE PRODUITS(
  id_prod NUMBER CONSTRAINT pk_Prod PRIMARY KEY,
  nom_prod VARCHAR2(64),
  id_grp NUMBER NOT NULL,
  prix FLOAT NOT NULL,  
  CONSTRAINT fk_Prod_Grp FOREIGN KEY (id_grp) REFERENCES GROUPES(id_grp)
);

CREATE TABLE MAGASINS(
  id_mag NUMBER CONSTRAINT pk_Mag PRIMARY KEY,
  nom_mag VARCHAR2(64),
  id_grp NUMBER NOT NULL,
  CONSTRAINT fk_Mag_Grp FOREIGN KEY (id_grp) REFERENCES GROUPES(id_grp)
);

CREATE TABLE STOCKS(
  id_prod NUMBER NOT NULL,
  id_mag NUMBER NOT NULL,
  nb_articles NUMBER NOT NULL,
  CONSTRAINT fk_Stocks_Prod FOREIGN KEY (id_prod) REFERENCES PRODUITS(id_prod),  
  CONSTRAINT fk_Stocks_Mag FOREIGN KEY (id_mag) REFERENCES MAGASINS(id_mag),
  CONSTRAINT pk_Stocks PRIMARY KEY (id_prod,id_mag)
);

-----------------------------------------------------------------
DELETE FROM GROUPES;
INSERT INTO GROUPES VALUES(0,'Epicerie');
INSERT INTO GROUPES VALUES(1,'Outillage');
INSERT INTO GROUPES VALUES(2,'Informatique');

DELETE FROM PRODUITS;
INSERT INTO PRODUITS VALUES(0,'Curry',0,2.0);
INSERT INTO PRODUITS VALUES(1,'Curcuma',0,1.0);
INSERT INTO PRODUITS VALUES(2,'Brouette',1,10.0);
INSERT INTO PRODUITS VALUES(3,'Romarin',1,0.5);

DELETE FROM MAGASINS;
INSERT INTO MAGASINS VALUES(0,'La case aux epices',0);
INSERT INTO MAGASINS VALUES(1,'Informaterraux',2);
INSERT INTO MAGASINS VALUES(2,'Bricoterreaux',1);
INSERT INTO MAGASINS VALUES(3,'La recase aux epices',0);

DELETE FROM STOCKS;
INSERT INTO STOCKS VALUES(0,0,2); -- 2 curry à 2€ = 4 (produit phare)
INSERT INTO STOCKS VALUES(1,0,3); -- 3 curcuma à 1€ = 3 (produit phare)
INSERT INTO STOCKS VALUES(2,0,4); -- 4 brouettes à 10€ = 40
INSERT INTO STOCKS VALUES(2,1,2); -- 2 brouettes à 10€ = 20
INSERT INTO STOCKS VALUES(2,2,11); --11 brouettes à 10€ = 110 (produit phare)

COMMIT;

------------------------------------------------------------------------------------------------------------------------
-- requête d'exemple 

SELECT * FROM Produits;

SELECT P.id_prod, nom_prod, prix,
  NVL(SUM(nb_articles),0) AS nb
FROM Produits P LEFT OUTER JOIN Stocks S
  ON P.id_prod = S.id_prod
GROUP BY P.id_prod, nom_prod, id_grp, prix; 
-- NB : on utilise un LEFT OUTER JOIN pour avoir un NULL quand le produit n'est pas en stock
-- NB : la fonction NVL remplace un NULL par une valeur, ici 0

------------------------------------------------------------------------------------------------------------------------
-- mauvaise solution : nouvel attribut, calcul de l'état actuel et trigger

ALTER TABLE PRODUITS
  ADD nb_produits NUMBER DEFAULT 0;

DECLARE
  v_nb_produits NUMBER;
  CURSOR c_produits IS
  SELECT *
  FROM PRODUITS; 
BEGIN
  FOR v_produit IN c_produits LOOP
    SELECT SUM(nb_articles) INTO v_nb_produits
    FROM STOCKS
    WHERE STOCKS.id_prod = v_produit.id_prod;
    IF (v_nb_produits IS NULL) THEN
      v_nb_produits := 0;
    END IF;
    UPDATE PRODUITS 
      SET nb_produits = v_nb_produits
      WHERE PRODUITS.id_prod = v_produit.id_prod;
    DBMS_OUTPUT.PUT_LINE('Produit ' || v_produit.id_prod || ' : ' || v_nb_produits   ) ;
  END LOOP;
END;

SELECT * FROM produits;

CREATE OR REPLACE TRIGGER stocks_nb_produits
AFTER INSERT OR UPDATE OR DELETE
  ON STOCKS
  FOR EACH ROW
BEGIN
  IF INSERTING THEN
    UPDATE PRODUITS 
      SET nb_produits = nb_produits + :NEW.nb_articles
      WHERE id_prod = :NEW.id_prod;
  END IF;
END;


SELECT * FROM STOCKS;
INSERT INTO STOCKS VALUES(1,1,100); -- 100 curcuma
SELECT * FROM produits;

ALTER TABLE PRODUITS
  DROP COLUMN nb_produits;

------------------------------------------------------------------------------------------------------------------------
-- bonne solution : une vue

CREATE OR REPLACE VIEW V_PRODUITS_NB AS
SELECT P.id_prod, nom_prod, id_grp, prix,
  NVL(SUM(nb_articles),0) AS nb
FROM Produits P LEFT OUTER JOIN Stocks
  ON P.id_prod = STocks.id_prod
GROUP BY P.id_prod, nom_prod, id_grp, prix;

SELECT * FROM V_PRODUITS_NB;

SELECT * FROM user_views WHERE view_name LIKE '%PRODUITS%';


------------------------------------------------------------------------------------------------------------------------
-- mise à jour des vues
UPDATE V_PRODUITS_NB
SET nom_prod = 'Currry' WHERE id_prod = 0;
-- KO : on ne peut pas mettre à jour une vue qui contient un aggregat 


CREATE OR REPLACE VIEW V_PRODUITS_GRP0 AS
SELECT ID_PROD, Nom_prod, Prix
FROM Produits
WHERE id_grp = 0;

UPDATE V_PRODUITS_GRP0 SET nom_prod = 'Cury' WHERE id_prod = 0;
-- OK

------------------------------------------------------------------------------------------------------------------------

DROP VIEW V_PRODUITS_NB;
DROP VIEW V_PRODUITS_GRP0
