DROP TABLE commande;
DROP TABLE client;
DROP TABLE produit;

DROP SEQUENCE seq_client;
DROP SEQUENCE seq_produit;
DROP SEQUENCE seq_commande;

COMMIT;

CREATE TABLE client
(
   id INT PRIMARY KEY,
   nom VARCHAR(50) NOT NULL,
   prenom VARCHAR(50) NOT NULL,
   ville VARCHAR(50) NOT NULL,
   age INTEGER NOT NULL,
   genre CHAR(1) NOT NULL
);

CREATE SEQUENCE seq_client;

DECLARE
   num INTEGER;
   ville VARCHAR(50);
   genre CHAR(1);
BEGIN
FOR i IN 1..100000 LOOP

   num:=dbms_random.value(1,5);

   genre := (CASE num   
      WHEN 1 THEN 'M'
      WHEN 2 THEN 'M'
      WHEN 3 THEN 'M'
      WHEN 4 THEN 'F'
      WHEN 5 THEN 'F'
   END);

   num:=dbms_random.value(1,15);

   ville := (CASE num   
      WHEN 1 THEN 'Dijon'
      WHEN 2 THEN 'Dijon'
      WHEN 3 THEN 'Paris'
      WHEN 4 THEN 'Paris'
      WHEN 5 THEN 'Paris'
      WHEN 6 THEN 'Paris'
      WHEN 7 THEN 'Toulouse'
      WHEN 8 THEN 'Lyon'
      WHEN 9 THEN 'Lyon'
      WHEN 10 THEN 'Lyon'
      WHEN 11 THEN 'Marseille'
      WHEN 12 THEN 'Marseille'
      WHEN 13 THEN 'Lille'
      WHEN 14 THEN 'Brest'
      WHEN 15 THEN 'Strasbourg'
   END);

   INSERT INTO 
      client 
   SELECT
      seq_client.nextval,
      dbms_random.string('U',dbms_random.value(5,50)),
      dbms_random.string('L',dbms_random.value(5,50)),
      ville,
      dbms_random.value(20,60),
      genre
   FROM
      DUAL;

END LOOP;
END;
/

COMMIT;

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

CREATE TABLE produit
(
   id INT PRIMARY KEY,
   nom VARCHAR(50) NOT NULL,
   couleur VARCHAR(50) NOT NULL,
   prix FLOAT NOT NULL
);

CREATE SEQUENCE seq_produit;

DECLARE
   num INTEGER;
   couleur VARCHAR(50);
BEGIN
FOR i IN 1..135000 LOOP

   num:=dbms_random.value(1,5);

   couleur := (CASE  num   
      WHEN 1 THEN 'rouge'
      WHEN 2 THEN 'vert'
      WHEN 3 THEN 'bleu'
      WHEN 4 THEN 'bleu'
      WHEN 5 THEN 'bleu'
      WHEN 6 THEN 'jaune'
      WHEN 7 THEN 'jaune'
      WHEN 8 THEN 'noir'
      WHEN 9 THEN 'blanc'
   END);

   INSERT INTO 
      produit 
   SELECT
      seq_produit.nextval,
      dbms_random.string('U',dbms_random.value(5,50)),
      couleur,
      dbms_random.value(20,60)*1.347
   FROM
      DUAL;

END LOOP;
END;
/

COMMIT;

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

CREATE TABLE commande
(
   id INT PRIMARY KEY,
   jour DATE NOT NULL,
   client INTEGER NOT NULL CONSTRAINT commande_client REFERENCES client,
   produit INTEGER NOT NULL CONSTRAINT commande_produit REFERENCES produit
);

CREATE SEQUENCE seq_commande;

DECLARE
   num INTEGER;
   client INTEGER;
   produit INTEGER;
BEGIN
FOR i IN 1..980000 LOOP

   num:=dbms_random.value(1,500);
   client:=dbms_random.value(1,100000);
   produit:=dbms_random.value(1,135000);

   INSERT INTO 
      commande 
   SELECT
      seq_commande.nextval,
      SYSDATE-num,
      client,
      produit
   FROM
      DUAL;

END LOOP;
END;
/

COMMIT;
