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

CREATE TABLE MAGASIN(
  id_mag NUMBER CONSTRAINT pk_Mag PRIMARY KEY,
  nom_mag VARCHAR2(64)
);

CREATE TABLE GROUPE(
  id_grp NUMBER CONSTRAINT pk_Grp PRIMARY KEY,
  nom_grp VARCHAR2(64),
  id_mag NUMBER NOT NULL,
  CONSTRAINT fk_Grp_Mag FOREIGN KEY (id_mag) REFERENCES MAGASIN(id_mag)
  
);

CREATE TABLE PRODUIT(
  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 GROUPE(id_grp)
);


CREATE TABLE STOCK(
  id_prod NUMBER NOT NULL,
  id_mag NUMBER NOT NULL,
  nb_articles NUMBER NOT NULL,
  CONSTRAINT fk_Stocks_Prod FOREIGN KEY (id_prod) REFERENCES PRODUIT(id_prod),  
  CONSTRAINT fk_Stocks_Mag FOREIGN KEY (id_mag) REFERENCES MAGASIN(id_mag),
  CONSTRAINT pk_Stocks PRIMARY KEY (id_prod,id_mag)
);

-----------------------------------------------------------------
DELETE FROM GROUPE;
DELETE FROM MAGASIN;
DELETE FROM PRODUIT;
DELETE FROM STOCK;

INSERT INTO MAGASIN VALUES(0,'La case aux epices');
INSERT INTO MAGASIN VALUES(1,'Informaterraux');
INSERT INTO MAGASIN VALUES(2,'Bricoterreaux');

INSERT INTO GROUPE VALUES(0,'Epicerie',0);
INSERT INTO GROUPE VALUES(1,'Informatique',1);
INSERT INTO GROUPE VALUES(2,'Outillage',2);



INSERT INTO PRODUIT VALUES(0,'Curry',   0,2.0);
INSERT INTO PRODUIT VALUES(1,'Curcuma', 0,1.0);
INSERT INTO PRODUIT VALUES(2,'Brouette',2,10.0);


INSERT INTO STOCK VALUES(0,0,2); -- 2 curry à 2€ = 4 (produit phare)
INSERT INTO STOCK VALUES(1,0,3); -- 3 curcuma à 1€ = 3 (produit phare)
INSERT INTO STOCK VALUES(2,0,4); -- 4 brouettes à 10€ = 40

INSERT INTO STOCK VALUES(2,1,2); -- 2 brouettes à 10€ = 20

INSERT INTO STOCK VALUES(2,2,11); --11 brouettes à 10€ = 110 (produit phare)



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

SELECT GROUPE.nom_grp
FROM GROUPE
WHERE NOT EXISTS (SELECT STOCK.id_prod
                    FROM (STOCK  INNER JOIN MAGASIN ON STOCK.id_mag = MAGASIN.id_mag) INNER JOIN PRODUIT ON STOCK.id_prod = PRODUIT.id_prod
                    WHERE GROUPE.id_grp <> PRODUIT.id_grp AND GROUPE.id_mag = STOCK.id_mag);

SELECT GROUPE.nom_grp, SUM(nb_articles)
FROM (MAGASIN INNER JOIN STOCK ON STOCK.ID_MAG = MAGASIN.ID_MAG) INNER JOIN PRODUIT ON STOCK.id_prod = PRODUIT.id_prod INNER JOIN GROUPE ON PRODUIT.id_grp = GROUPE.id_grp 
WHERE MAGASIN.id_mag <> GROUPE.id_mag
GROUP BY GROUPE.id_grp,  GROUPE.nom_grp;

SELECT GROUPE.nom_grp, SUM(nb_articles)
FROM (MAGASIN INNER JOIN STOCK ON STOCK.ID_MAG = MAGASIN.ID_MAG) INNER JOIN PRODUIT ON STOCK.id_prod = PRODUIT.id_prod INNER JOIN GROUPE ON PRODUIT.id_grp = GROUPE.id_grp 
WHERE MAGASIN.id_mag = GROUPE.id_mag
GROUP BY GROUPE.id_grp,  GROUPE.nom_grp;

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

ALTER TABLE MAGASIN ADD(
  nb_total NUMBER
);

SET SERVEROUTPUT ON;

DECLARE
  v_nb_produits NUMBER;
  CURSOR c_magasins IS
  SELECT *
  FROM magasin; 
BEGIN
  FOR v_magasin IN c_magasins LOOP
    SELECT SUM(nb_articles) INTO v_nb_produits
    FROM STOCK
    WHERE STOCK.id_mag = v_magasin.id_mag;
    UPDATE MAGASIN 
      SET nb_total = v_nb_produits
      WHERE MAGASIN.id_mag = v_magasin.id_mag;
  END LOOP;
END;
/

SELECT * FROM MAGASIN;



CREATE OR REPLACE FUNCTION f_total(v_magasin IN NUMBER)
  RETURN NUMBER
  IS
  v_total NUMBER;
  -- v_produit  PRODUITS%ROWTYPE;
  -- v_mag_grp   MAGASINS.id_grp%TYPE;
BEGIN
  SELECT SUM(nb_articles) INTO v_total
  FROM STOCK
  WHERE STOCK.id_mag = v_mag AND STOCK.ID_PROD NOT IN(
    SELECT ID_prod
    FROM produit
    WHERE produit.id_grp
  );
  
  
  SELECT id_grp INTO v_mag_grp
  FROM MAGASINS
  WHERE MAGASINS.id_mag = v_magasin;
  
  IF v_produit.id_grp = v_mag_grp THEN
    v_prix := taux* (v_produit.prix);
  ELSE
    v_prix :=  v_produit.prix;
  END IF;
  
  RETURN v_prix;
END;
/
SHOW ERRORS;


SELECT f_prix(0,0)
FROM DUAL;


SELECT MAGASINS.nom_mag, SUM(f_prix(STOCKS.id_mag,STOCKS.id_prod)*STOCKS.nb_articles)
FROM (MAGASINS INNER JOIN STOCKS ON STOCKS.ID_MAG = MAGASINS.ID_MAG)
GROUP BY MAGASINS.id_mag,MAGASINS.nom_mag;

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