SALUT SALUT

CREATE OR REPLACE PACKAGE BODY PAQ_PRODUITS AS

  PROCEDURE ajouter_simple(le_nom ff_produit.nom%TYPE,
                           le_prix ff_produit.prix%TYPE,
                           la_taille ff_produit.taille%TYPE,
                           la_categ ff_simple.categ%TYPE) AS

  la_ref int;
  nb_produits_nom int;
  BEGIN
    IF le_nom IS NULL OR le_prix IS NULL OR la_taille IS NULL OR la_categ IS NULL THEN
      RAISE PARAMETRE_INDEFINI;
    END IF;

    IF la_taille NOT IN('petit', 'moyen', 'grand') THEN
      RAISE PB_VALEUR_TAILLE;
    END IF;

    IF le_prix <= 0 THEN
      RAISE PRIX_NON_POSITIF;
    END IF;

    IF la_categ NOT IN ('boisson','dessert','salade','accompagnement','sandwich') THEN
      RAISE PB_VALEUR_CATEGORIE;
    END IF;

    SELECT COUNT(*) INTO nb_produits_nom FROM ff_produit WHERE ff_produit.nom = le_nom;
    IF nb_produits_nom != 0 THEN
      RAISE DOUBLON_NOM_PRODUIT;
    END IF;

  la_ref := gen_clef.NEXTVAL;
    BEGIN
      INSERT INTO FF_PRODUIT(P_REF, NOM, PRIX, TAILLE) VALUES(la_ref, le_nom, le_prix, la_taille);
      INSERT INTO FF_SIMPLE(S_REF, CATEG) VALUES(la_ref, la_categ);
    END;
  END ajouter_simple;



  PROCEDURE supprimer_simple(la_ref ff_simple.s_ref%TYPE)  AS
  temp int;
  BEGIN
    IF la_ref IS NULL THEN
      RAISE PARAMETRE_INDEFINI;
    END IF;

    SELECT COUNT(*) INTO temp FROM FF_SIMPLE
    WHERE S_REF = la_ref;
    IF temp = 0 THEN
      RAISE PRODUIT_INCONNU;
    END IF;

    SELECT COUNT(*) INTO temp FROM FF_CONSTITUE
    INNER JOIN FF_SIMPLE ON S_REF = REF_SIMPLE
    WHERE REF_SIMPLE = la_ref;
    IF temp > 0 THEN
      RAISE PRODUIT_UTILISE;
    END IF;

    BEGIN
      DELETE FROM FF_PRODUIT WHERE P_REF = la_ref;
      DELETE FROM FF_SIMPLE WHERE S_REF = la_ref;
    END;
  END supprimer_simple;




  PROCEDURE ajouter_menu(le_nom ff_produit.nom%TYPE,
                         le_prix ff_produit.prix%TYPE,
                         la_taille ff_produit.taille%TYPE,
                         la_promo ff_menu.promo%TYPE)  AS

  la_ref int;
  temp int;
  BEGIN
    IF le_nom IS NULL OR le_prix IS NULL OR la_taille IS NULL OR la_promo IS NULL THEN
      RAISE PARAMETRE_INDEFINI;
    END IF;

    IF la_taille NOT IN('petit', 'moyen', 'grand') THEN
      RAISE PB_VALEUR_TAILLE;
    END IF;

    IF le_prix <= 0 THEN
      RAISE PRIX_NON_POSITIF;
    END IF;

    SELECT COUNT(*) INTO temp FROM FF_PRODUIT
    WHERE NOM = le_nom;
    IF temp != 0 THEN
      RAISE DOUBLON_NOM_PRODUIT;
    END IF;

    la_ref := gen_clef.NEXTVAL;
    BEGIN
      INSERT INTO FF_PRODUIT(P_REF, NOM, PRIX, TAILLE) VALUES(la_ref, le_nom, le_prix, la_taille);
      INSERT INTO FF_MENU(M_REF, PROMO) VALUES(la_ref, la_promo);
    END;
  END ajouter_menu;


  PROCEDURE supprimer_menu(la_ref ff_menu.m_ref%TYPE)  AS
  temp int;
  BEGIN
    IF la_ref IS NULL THEN
      RAISE PARAMETRE_INDEFINI;
    END IF;

    SELECT COUNT(*) INTO temp FROM FF_MENU
    WHERE M_REF = la_ref;
    IF temp != 1 THEN
      RAISE PRODUIT_INCONNU;
    END IF;

    BEGIN
      DELETE FROM FF_CONSTITUE WHERE REF_MENU = la_ref;
      DELETE FROM FF_MENU WHERE M_REF = la_ref;
    END;
  END supprimer_menu;

  PROCEDURE enrichir_menu(la_ref_menu ff_menu.m_ref%TYPE, la_ref_simple ff_simple.s_ref%TYPE)  AS

  temp int;
  BEGIN
    IF la_ref_menu IS NULL OR la_ref_simple IS NULL THEN
      RAISE PARAMETRE_INDEFINI;
    END IF;

    SELECT COUNT(*) INTO temp FROM FF_SIMPLE
    WHERE S_REF = la_ref_simple;
    IF temp != 1 THEN
      RAISE PRODUIT_INCONNU;
    END IF;

    SELECT COUNT(*) INTO temp FROM FF_MENU
    WHERE M_REF = la_ref_menu;
    IF temp != 1 THEN
      RAISE PRODUIT_INCONNU;
    END IF;

    SELECT COUNT(*) INTO temp
    FROM FF_SIMPLE
    INNER JOIN FF_PRODUIT ON FF_PRODUIT.P_REF = FF_SIMPLE.S_REF
    WHERE FF_SIMPLE.S_REF = la_ref_simple
    AND FF_PRODUIT.TAILLE IN (SELECT FF_PRODUIT.TAILLE
                              FROM FF_PRODUIT
                              INNER JOIN FF_SIMPLE ON FF_SIMPLE.S_REF = FF_PRODUIT.P_REF
                              INNER JOIN FF_CONSTITUE ON REF_SIMPLE = FF_SIMPLE.S_REF
                              WHERE FF_CONSTITUE.REF_MENU = la_ref_menu);

    IF temp = 0 THEN
      RAISE INCOHERENCE_TAILLES;
    END IF;

    BEGIN
      INSERT INTO FF_CONSTITUE(REF_MENU, REF_SIMPLE) VALUES(la_ref_menu, la_ref_simple);
    END;
  END enrichir_menu;

  PROCEDURE appauvrir_menu(la_ref_menu ff_menu.m_ref%TYPE, la_ref_simple ff_simple.s_ref%TYPE)  AS
  temp int;
  BEGIN
    IF la_ref_menu IS NULL OR la_ref_simple IS NULL THEN
      RAISE PARAMETRE_INDEFINI;
    END IF;

    SELECT COUNT(*) INTO temp FROM FF_CONSTITUE
    WHERE REF_MENU = la_ref_menu AND REF_SIMPLE = la_ref_simple;
    IF temp = 0 THEN
      RAISE PB_COMPOSITION;
    END IF;

    BEGIN
      DELETE FROM FF_CONSTITUE WHERE REF_MENU = la_ref_menu AND REF_SIMPLE = la_ref_simple;
    END;
  END appauvrir_menu;

END PAQ_PRODUITS;