-- 
-- création de la base formation SQL
--

CREATE TABLE pays (
    code_pays text NOT NULL,
    nom_pays text NOT NULL,
    region_id integer NOT NULL,
    commentaire text
);

INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('DZ', 'ALGÉRIE', 0, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('DE', 'ALLEMAGNE', 3, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('SA', 'ARABIE SAOUDITE', 4, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('AR', 'ARGENTINE', 1, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('BR', 'BRÉSIL', 1, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('CA', 'CANADA', 1, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('CN', 'CHINE', 2, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('EG', 'ÉGYPTE', 4, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('US', 'ÉTATS-UNIS', 1, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('ET', 'ÉTHIOPIE', 0, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('FR', 'FRANCE', 3, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('IN', 'INDE', 2, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('ID', 'INDONÉSIE', 2, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('IR', 'IRAN, RÉPUBLIQUE ISLAMIQUE D''', 4, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('IQ', 'IRAQ', 4, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('JP', 'JAPON', 2, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('JO', 'JORDANIE', 4, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('KE', 'KENYA', 0, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('MA', 'MAROC', 0, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('MZ', 'MOZAMBIQUE', 0, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('PE', 'PÉROU', 1, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('RO', 'ROUMANIE', 3, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('GB', 'ROYAUME-UNI', 3, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('RU', 'RUSSIE, FÉDÉRATION DE', 3, NULL);
INSERT INTO pays (code_pays, nom_pays, region_id, commentaire) VALUES ('VN', 'VIET NAM', 2, NULL);

CREATE TABLE regions (
    region_id integer NOT NULL,
    nom_region varchar(25),
    commentaire text
);

INSERT INTO regions (region_id, nom_region, commentaire) VALUES (0, 'Afrique', NULL);
INSERT INTO regions (region_id, nom_region, commentaire) VALUES (1, 'Amérique', NULL);
INSERT INTO regions (region_id, nom_region, commentaire) VALUES (2, 'Asie', NULL);
INSERT INTO regions (region_id, nom_region, commentaire) VALUES (3, 'Europe', NULL);
INSERT INTO regions (region_id, nom_region, commentaire) VALUES (4, 'Moyen-Orient', NULL);

CREATE TABLE contacts (
    contact_id bigserial NOT NULL,
    login varchar(50) NOT NULL,
    passwd varchar(50) NOT NULL, -- à adapter pour stocker un hash md5
    email text NOT NULL,
    nom varchar(50) NOT NULL,
    prenom varchar(50) NOT NULL,
    adresse1 varchar(50) NOT NULL,
    adresse2 varchar(50),
    code_postal varchar(5) NOT NULL,
    ville varchar(50) NOT NULL,
    code_pays character(2) NOT NULL,
    telephone varchar(20)[]
);

CREATE TABLE clients (
    client_id bigserial NOT NULL,
    contact_id bigint NOT NULL,
    type_client character(1) NOT NULL,
    commentaire text
);

CREATE TABLE types_clients (
   type_client char(1) NOT NULL PRIMARY KEY,
   libelle text NOT NULL
);

INSERT INTO types_clients (type_client, libelle) VALUES ('P', 'Particulier'), ('E', 'Entreprise'), ('A', 'Administration');

CREATE TABLE fournisseurs (
    fournisseur_id serial NOT NULL,
    contact_id bigint NOT NULL,
    commentaire text
);

CREATE TABLE commandes (
    numero_commande bigint NOT NULL,
    client_id bigint NOT NULL,
    date_commande date NOT NULL,
    mode_expedition integer NOT NULL,
-- priorite_commande sous-entendu par mode_expedition, à supprimer si confirmé
--    priorite_commande character(1) NOT NULL,
    commentaire text
);

CREATE TABLE lignes_commandes (
    numero_ligne_commande bigserial NOT NULL, -- PK
    numero_commande bigint NOT NULL,
    produit_id bigint NOT NULL,
    fournisseur_id bigint NOT NULL,
    quantite integer NOT NULL,
    prix_unitaire numeric(10,2) NOT NULL,
    remise numeric(10,2) NOT NULL,
    etat_retour character(1),
    numero_lot_expedition bigint, --fk
    commentaire text
);

CREATE TABLE modes_expedition (
  mode_expedition integer not null primary key,
  libelle text not null
);

insert into modes_expedition (mode_expedition, libelle)
 values (1, 'normal'), (2, 'suivi'), (3, 'recommandé'), (4, 'chrono');

CREATE TABLE etats_retour (
  etat_retour char(1) NOT NULL PRIMARY KEY,
  libelle text NOT NULL
);

INSERT INTO etats_retour (etat_retour, libelle) VALUES ('R', 'Retourné');

CREATE TABLE transporteurs (
  transporteur_id bigserial NOT NULL,
  nom varchar(50) NOT NULL,
  contact_id bigint NOT NULL,
  commentaire text
);

CREATE TABLE pays_transporteurs (
  code_pays text NOT NULL,
  transporteur_id bigint NOT NULL,
  poids numrange NOT NULL,
  tarif numeric(5,2) NOT NULL
);

CREATE TABLE lots (
    numero_lot bigserial NOT NULL,
    transporteur_id bigint NOT NULL,
    numero_suivi varchar(14) NOT NULL,
    date_depot date,
    date_expedition date,
    date_reception date
);

CREATE TABLE produits (
    produit_id bigserial NOT NULL,
    nom varchar(55) NOT NULL,
    fabriquant character(50) NOT NULL,
    marque character(50) NOT NULL,
    longueur numeric(7, 2),
    largeur numeric(7, 2),
    hauteur numeric(7, 2),
    poids numeric(5) NOT NULL, -- en grammes
    prix numeric(10,2),
    commentaire text
);

COMMENT ON COLUMN produits.longueur IS 'Unité exprimé en millimètre';
COMMENT ON COLUMN produits.largeur IS 'Unité exprimé en millimètre';
COMMENT ON COLUMN produits.hauteur IS 'Unité exprimé en millimètre';
COMMENT ON COLUMN produits.poids IS 'Unité exprimé en grammes';

CREATE TABLE produit_fournisseurs (
    produit_id bigint NOT NULL,
    fournisseur_id bigint NOT NULL,
    quantite_disponible integer,
    cout_piece numeric(10,2),
    commentaire text
);

CREATE TABLE numeros_sequence (
   type_sequence text NOT NULL PRIMARY KEY,
   numero_sequence bigint NOT NULL
);

INSERT INTO numeros_sequence VALUES ('facture', 0), ('commande', 0);

CREATE TABLE factures (
   numero_facture varchar NOT NULL,
   numero_commande bigint NOT NULL,
   date_facturation date NOT NULL,
   date_paiement date NOT NULL,
   condition_reglement integer NOT NULL -- FK
);

CREATE TABLE conditions_reglement (
  condition_reglement integer PRIMARY KEY,
  libelle text NOT NULL
);

INSERT INTO conditions_reglement (condition_reglement, libelle)
VALUES (1, 'à la commande'), (2, 'sous 30 jours'),
       (3, 'à réception'), (4, 'paiement échelonné');

CREATE TABLE modes_reglement (
  mode_reglement integer PRIMARY KEY,
  libelle text NOT NULL
);

INSERT INTO modes_reglement (mode_reglement, libelle)
VALUES (1, 'virement'), ('2', 'carte bleue'), ('3', 'chèque'), ('4', 'espèce'), ('5', 'contre-remboursement');

CREATE TABLE reglements (
   reglement_id bigserial,
   numero_facture varchar NOT NULL, -- FK
   montant numeric(10,2) NOT NULL,
   mode_reglement integer NOT NULL, --FK   
   date_reglement date NOT NULL
);


