/*
Première version de la base de données Facture
Version 1
Van Oudenhove Didier
*/

--Création des domaines
create domain Monetaire as decimal(10,2);
create domain boolean as smallint default 0 check(value in (0,1));
create domain DSexe as char(1) check(value in ('M','F'));


/*************************************
 Table des clients
**************************************/
CREATE TABLE TCLIENT
(
  NUM_CLI Smallint PRIMARY KEY,
  NOM_CLI Varchar(50) NOT NULL,
  PRENOM_CLI Varchar(30),
  DATE_NAIS_CLI Date,
  NUM_RUE_CLI Char(6),
  RUE_CLI Varchar(40),
  FK_CP_CLI  smallint,
  FK_VILLE_CLI  varchar(40),
  SEXE_CLI DSEXE,
  FULLNAME_CLI computed by 
    (NOM_CLI||coalesce('_'||PRENOM_CLI,''))
);
-- index pour le tri et l'unicité des clients
CREATE UNIQUE INDEX IDX_NPD on TCLIENT(NOM_CLI,PRENOM_CLI,DATE_NAIS_CLI);
--index pour le tri par date de naissance
CREATE desc INDEX IDX_DATE_NAIS ON TCLIENT(DATE_NAIS_CLI);

/*************************************
 Table Facture
*************************************/
CREATE TABLE TFACTURE
(
  NUM_FAC Smallint  PRIMARY KEY,
  DATE_FAC Date DEFAULT CURRENT_DATE NOT NULL,
  TOTALHT_FAC MONETAIRE,
  TOTALTTC_FAC MONETAIRE,
  ETAT_FAC BOOLEAN default 0,
  FK_CLIENT_FAC Smallint
);
-- INDEX
CREATE DESCENDING INDEX IDX_DATE on TFACTURE(DATE_FAC);
 
/************************************* 
   Table TVille 
**************************************/
CREATE TABLE TVILLE
(CP_VIL  SMALLINT,
 VILLE_VIL  varchar(40),
 CONSTRAINT CTR_PK_Ville PRIMARY KEY (CP_VIL,VILLE_VIL)
);
/* Création de l'index sur le nom de la ville
   il existe déjà un index sur le code postal par le PK
*/
create index idx_ville on TVILLE(VILLE_VIL);

  
  
-- Creation des contraintes de clé étrangère 
-- (integrité référentielle) Client et Ville
  alter table tclient add constraint ctr_fk_ville
   FOREIGN KEY (fk_cp_cli,fk_ville_cli) 
  REFERENCES tville(cp_vil,ville_vil) 
  on update CASCADE on delete set null;
--(integrité référentielle) Facture et client
  alter table tfacture add CONSTRAINT ctr_fk_client
   FOREIGN KEY(fk_client_fac)
  REFERENCES tclient(num_cli) 
  on UPDATE CASCADE on delete no ACTION;


/*************************************
 création de la table produit
*************************************/
CREATE TABLE TPRODUIT
(
  CODE_PRO Char(8) PRIMARY KEY,
  NOM_PRO Varchar(50) NOT NULL,
  PRIX_PRO MONETAIRE ,
  MARQUE_PRO Varchar(20) ,
  CATEGORIE_PRO Char(20) 
);
-- Création des index
create index idx_marque    on TPRODUIT(Marque_pro);
create index idx_Categorie on TPRODUIT(Categorie_pro);
/*************************************
--création de la table Stock
**************************************/
CREATE TABLE TSTOCK
(
  FK_PRO_STO Char(8) PRIMARY KEY,
  QT_STO Integer NOT NULL check (QT_STO>=0)
);

-- ajout de la contrainte sur clé étrangère
ALTER TABLE TSTOCK ADD CONSTRAINT CTR_FK_PRO_STO
 FOREIGN KEY (FK_PRO_STO) 
REFERENCES TPRODUIT (CODE_PRO) ON UPDATE CASCADE 
ON DELETE CASCADE;

/*************************************
  création de la table Ligne Facture
**************************************/
CREATE TABLE TLIGNE_FAC
(
  FK_FAC_LIGF Smallint,
  FK_PRO_LIGF Char(8) ,
  QT_LIGF Integer CHECK (QT_LIGF >= 0),
  PRIX_LIGF MONETAIRE,
  PRIMARY KEY (FK_FAC_LIGF,FK_PRO_LIGF)
);

-- Ajout des contraintes de clé étrangère
ALTER TABLE TLIGNE_FAC ADD CONSTRAINT CTR_FK_FAC_LIGF
  FOREIGN KEY (FK_FAC_LIGF) 
  REFERENCES TFACTURE (NUM_FAC) ON UPDATE CASCADE;

ALTER TABLE TLIGNE_FAC ADD CONSTRAINT CTR_FK_PRO_LIGF
  FOREIGN KEY (FK_PRO_LIGF) 
  REFERENCES TPRODUIT (CODE_PRO) ON UPDATE CASCADE;
  
  
-- Création d'un générateur NUM_CLI
create generator gen_num_cli;

-- Création du trigger
set term ^;

create trigger trg_num_cli for tclient active before
insert 
as
begin
 if (new.NUM_CLI is null) then 
  new.NUM_CLI= next value for gen_num_cli;
end^

set term ;^


-- Création d'un générateur NUM_FAC

create sequence gen_num_fac;
-- Création d'un trigger
set term ^;
create trigger trg_num_fac for  TFACTURE
before insert
as
begin

 if (new.NUM_FAC is null) then 
     new.NUM_FAC = next value for gen_num_fac;
     
end^

set term ;^

commit;

CREATE TABLE TMOIS
(
  NUM_MOIS Smallint NOT NULL,
  NOM_MOIS Varchar(15) NOT NULL,
  PRIMARY KEY (NUM_MOIS)
);

commit;

INSERT INTO TMOIS (NUM_MOIS, NOM_MOIS) VALUES ('1', 'Janvier');
INSERT INTO TMOIS (NUM_MOIS, NOM_MOIS) VALUES ('2', 'Fevrier');
INSERT INTO TMOIS (NUM_MOIS, NOM_MOIS) VALUES ('3', 'Mars');
INSERT INTO TMOIS (NUM_MOIS, NOM_MOIS) VALUES ('4', 'Avril');
INSERT INTO TMOIS (NUM_MOIS, NOM_MOIS) VALUES ('5', 'Mai');
INSERT INTO TMOIS (NUM_MOIS, NOM_MOIS) VALUES ('6', 'Juin');
INSERT INTO TMOIS (NUM_MOIS, NOM_MOIS) VALUES ('7', 'Juillet');
INSERT INTO TMOIS (NUM_MOIS, NOM_MOIS) VALUES ('8', 'Août');
INSERT INTO TMOIS (NUM_MOIS, NOM_MOIS) VALUES ('9', 'Septembre');
INSERT INTO TMOIS (NUM_MOIS, NOM_MOIS) VALUES ('10', 'Octobre');
INSERT INTO TMOIS (NUM_MOIS, NOM_MOIS) VALUES ('11', 'Novembre');
INSERT INTO TMOIS (NUM_MOIS, NOM_MOIS) VALUES ('12', 'Decembre');
commit