CREATE TABLE S_COM.T_CLIENT_CLI
(CLI_ID           INTEGER NOT NULL PRIMARY KEY,
 CLI_NOM          CHAR(32) NOT NULL);

CREATE TABLE S_COM.T_PRODUIT_PRD
(PRD_ID           INTEGER PRIMARY KEY,
 PRD_LIBELLLE     VARCHAR(1000),
 PRD_PRIX_VENTE   DECIMAL (16,2) NOT NULL,
 PRD_TAUX_TVA     FLOAT DEFAULT 20.0);

CREATE TABLE S_COM.T_COMMANDE_CMD
(CMD_ID           INTEGER NOT NULL PRIMARY KEY,
 CMD_DATE         DATE NOT NULL DEFAULT CURRENT_DATE,
 CLI_ID           INTEGER NOT NULL 
    REFERENCES S_COM.T_CLIENT_CLI (CLI_ID)
       ON DELETE CASCADE);

CREATE TABLE S_COM.T_LIGNE_COMMANDE_LIC 
(LIC_ID           INTEGER PRIMARY KEY,
 CMD_ID           INTEGER 
    REFERENCES S_COM.T_COMMANDE_CMD (CMD_ID) 
       ON DELETE CASCADE,
 LIC_NUMERO_LIGNE INTEGER NOT NULL CHECK (VALUE > 0),
 PRD_ID           INTEGER NOT NULL 
    REFERENCES S_COM.T_PRODUIT_PRD (PRD_ID),
 LIC_QUANTITE     FLOAT NOT NULL DEFAULT 1,
 LIC_PRIX_VENDU   DECIMAL (16,2) NOT NULL,
 LIC_TVA_VENDU    FLOAT NOT NULL,
 LIC_REMISE_PC    FLOAT DEFAULT 0);

CREATE TABLE S_COM.T_FACTURE_FCT
(FCT_ID           INTEGER NOT NULL PRIMARY KEY,
 FCT_DATE         DATE NOT NULL DEFAULT CURRENT_DATE,
 CLI_ID           INTEGER NOT NULL 
    REFERENCES S_COM.T_CLIENT_CLI (CLI_ID)
       ON DELETE NO ACTION
       ON UPDATE NO ACTION,
 FCT_TOTAL_TTC    DECIMAL(16,2) NOT NULL,
 FCT_ECHEANCE     DATE NOT NULL DEFAULT CURRENT_DATE + 1 DAY);

 /*

 --> Pour SQL Server

CREATE TABLE S_COM.T_CLIENT_CLI
(CLI_ID           INTEGER NOT NULL PRIMARY KEY,
 CLI_NOM          CHAR(32) NOT NULL);

CREATE TABLE S_COM.T_PRODUIT_PRD
(PRD_ID           INTEGER PRIMARY KEY,
 PRD_LIBELLLE     VARCHAR(1000),
 PRD_PRIX_VENTE   DECIMAL (16,2) NOT NULL,
 PRD_TAUX_TVA     FLOAT DEFAULT 20.0);

CREATE TABLE S_COM.T_COMMANDE_CMD
(CMD_ID           INTEGER NOT NULL PRIMARY KEY,
 CMD_DATE         DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CLI_ID           INTEGER NOT NULL 
    REFERENCES S_COM.T_CLIENT_CLI (CLI_ID)
       ON DELETE CASCADE);

CREATE TABLE S_COM.T_LIGNE_COMMANDE_LIC 
(LIC_ID           INTEGER PRIMARY KEY,
 CMD_ID           INTEGER 
    REFERENCES S_COM.T_COMMANDE_CMD (CMD_ID) 
       ON DELETE CASCADE,
 LIC_NUMERO_LIGNE INTEGER NOT NULL CHECK (LIC_NUMERO_LIGNE > 0),
 PRD_ID           INTEGER NOT NULL 
    REFERENCES S_COM.T_PRODUIT_PRD (PRD_ID),
 LIC_QUANTITE     FLOAT NOT NULL DEFAULT 1,
 LIC_PRIX_VENDU   DECIMAL (16,2) NOT NULL,
 LIC_TVA_VENDU    FLOAT NOT NULL,
 LIC_REMISE_PC    FLOAT DEFAULT 0);

CREATE TABLE S_COM.T_FACTURE_FCT
(FCT_ID           INTEGER NOT NULL PRIMARY KEY,
 FCT_DATE         DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CLI_ID           INTEGER NOT NULL 
    REFERENCES S_COM.T_CLIENT_CLI (CLI_ID)
       ON DELETE NO ACTION
       ON UPDATE NO ACTION,
 FCT_TOTAL_TTC    DECIMAL(16,2) NOT NULL,
 FCT_ECHEANCE     DATE NOT NULL DEFAULT DATEADD(DAY, 30, CURRENT_TIMESTAMP));


 */
