/******************************************************************************
projet Orford
******************************************************************************/
DROP DATABASE IF EXISTS orfordInitial;
CREATE DATABASE orfordInitial;
USE orfordInitial;

/******************************************************************************
TABLE
******************************************************************************/
CREATE TABLE categorie(
  ID int NOT NULL,
  categorie VARCHAR(32)
);
ALTER TABLE categorie
ADD CONSTRAINT p_categorie PRIMARY KEY (ID);

CREATE TABLE chambre(
  ID int NOT NULL,
  etage int,
  lits VARCHAR(32),
  avant int,
  indispinible int,
  dateND DATE,
  commentaire VARCHAR(256)
);
ALTER TABLE chambre
ADD CONSTRAINT p_chambre PRIMARY KEY (ID);

CREATE TABLE client(
	ID INT NOT NULL,
	prenom VARCHAR(32),
	nom VARCHAR(32),
  nomDuGroupe VARCHAR(32),
  adresse VARCHAR(32),
	ville VARCHAR(32),
  province VARCHAR(32),
  codePostal VARCHAR(32),
  telephoneMaison VARCHAR(32),
  telephoneTravail VARCHAR(32),
	commentaire VARCHAR(256)
);
ALTER TABLE client
ADD CONSTRAINT p_client PRIMARY KEY (ID);

CREATE TABLE depots(
  ID int NOT NULL,
  jour date,
  employe VARCHAR(256)
);
ALTER TABLE depots
ADD CONSTRAINT p_depots PRIMARY KEY (ID);

CREATE TABLE encaissement(
  ID int NOT NULL,
  ID_facture int,
  methode int,
  montant decimal(10,2)
);
ALTER TABLE encaissement
ADD CONSTRAINT p_encaissement PRIMARY KEY (ID);

CREATE TABLE facture(
  ID int NOT NULL,
  ID_statut int,
  ID_client int,
  ID_fermeture int,
  commentaire VARCHAR(256),
  taux_TPS decimal(10,5),
  taux_TVQ decimal(10,5),
  verifier int,
  verifier_par VARCHAR(64),
  verifier_commentaire VARCHAR(256)
);
ALTER TABLE facture
ADD CONSTRAINT p_facture PRIMARY KEY (ID);

CREATE TABLE fermeture(
  ID int NOT NULL,
  ID_depots int,
  jour date,
  employe VARCHAR(64)
);
ALTER TABLE fermeture
ADD CONSTRAINT p_fermeture PRIMARY KEY (ID);

CREATE TABLE methode_encaissement(
  ID int NOT NULL,
  methode VARCHAR(256)
);
ALTER TABLE methode_encaissement
ADD CONSTRAINT p_methode_encaissement PRIMARY KEY (ID);

CREATE TABLE permanente(
  ID int NOT NULL,
  logo VARCHAR(64),
  noDeTps VARCHAR(256),
  tps decimal(10,3),
  noDeTvq VARCHAR(256),
  tvq decimal(10,3),
  messageFacture VARCHAR(256)
);
ALTER TABLE permanente
ADD CONSTRAINT p_permanente PRIMARY KEY (ID);

CREATE TABLE statut(
  ID int NOT NULL,
  statut VARCHAR(64)
);
ALTER TABLE statut
ADD CONSTRAINT p_statut PRIMARY KEY (ID);

CREATE TABLE stock(
  ID int NOT NULL,
  code VARCHAR(64),
  description VARCHAR(256),
  taxe int,
  ID_categori int,
  actif int
);
ALTER TABLE stock
ADD CONSTRAINT p_stock PRIMARY KEY (ID);

CREATE TABLE ventes(
  ID int NOT NULL,
  ID_facture int,
  ID_stock int,
  jour date,
  ID_chambre int,
  quantite int,
  prix decimal(10,2)
);
ALTER TABLE ventes
ADD CONSTRAINT p_ventes PRIMARY KEY (ID);

