DROP DATABASE IF EXISTS societe;
CREATE DATABASE societe CHARACTER SET 'utf8';
USE societe;

CREATE TABLE IF NOT EXISTS fournisseur (
    idFour INT(11) PRIMARY KEY,
    nom VARCHAR(50) NOT NULL,
    prenom VARCHAR(50) NOT NULL,
    tel VARCHAR(14),
    email VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS produit (
    idProduit INT(11) PRIMARY KEY,
    Designation VARCHAR(50) NOT NULL,
    Prix DECIMAL(10,0),
    QteStock DECIMAL(10,0),
    idFour INT(11),
    CONSTRAINT FK1 FOREIGN KEY(idFour) REFERENCES fournisseur(idFour)
);

CREATE TABLE IF NOT EXISTS client (
    idClient VARCHAR(50) PRIMARY KEY,
    nom VARCHAR(50) NOT NULL,
    prenom VARCHAR(50) NOT NULL,
    date_naiss DATE,
    email VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS commande (
    idCMD INT(11) PRIMARY KEY,
    dateCMD VARCHAR(45),
    idClient VARCHAR(45),
    client_idClient VARCHAR(50),
    FOREIGN KEY(client_idClient) REFERENCES client(idClient)
);

CREATE TABLE IF NOT EXISTS ligneCMD (
    idLigneCMD INT(11) PRIMARY KEY,
    Qte INT,
    idProduit INT(11),
    idCMD INT(11),
    FOREIGN KEY(idProduit) REFERENCES produit(idProduit),
    FOREIGN KEY(idCMD) REFERENCES commande(idCMD)
);