DROP TABLE FOURNISSEUR;
DROP TABLE PRODUIT;
DROP TABLE CONTENIR;
DROP TABLE CLIENT;
DROP TABLE COMMANDE;
DROP TABLE FACTURE;
DROP TABLE REGLEMENT;
DROP TABLE CHEQUE;
DROP TABLE CB;

CREATE TABLE FOURNISSEUR (
	num_four INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	nom_four VARCHAR(50) NOT NULL,
	adr_four VARCHAR(100) NOT NULL,
	cp_four VARCHAR(5) NOT NULL,
	ville_four VARCHAR(30) NOT NULL
);

CREATE TABLE PRODUIT (
	num_prod INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	nom_prod VARCHAR(50) NOT NULL,
	prix_unitaire INTEGER NOT NULL,
	qt_stock INTEGER,
	num_four INTEGER references FOURNISSEUR(num_four)
);

CREATE TABLE CLIENT (
	num_cli INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	nom_cli VARCHAR(30) NOT NULL,
	prenom_cli VARCHAR(30) NOT NULL,
	adr_cli VARCHAR(100) NOT NULL,
	cp_cli VARCHAR(5) NOT NULL,
	ville_cli VARCHAR(30) NOT NULL,
	date_ajout DATE
);

CREATE TABLE COMMANDE (
	num_cmde INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	date_cmde DATE,
	num_cli INTEGER references CLIENT(num_cli)
);

CREATE TABLE FACTURE (
	num_fact INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	date_fact DATE,
	montant_fact INTEGER NOT NULL,
	date_echeance DATE,
	date_paiement DATE,
	num_cmde INTEGER references COMMANDE(num_cmde)
);

CREATE TABLE REGLEMENT (
	num_reglement INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	date_reglement DATE,
	montant_reglement INTEGER NOT NULL,
	num_fact INTEGER references FACTURE(num_fact)
);

CREATE TABLE CHEQUE (
	id_cheque INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	num_cheque VARCHAR(10),
	num_compte VARCHAR(12),
	code_guichet VARCHAR(20),
	num_reglement INTEGER NOT NULL references REGLEMENT(num_reglement)
);	

CREATE TABLE CB (
	id_cb INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	num_carte VARCHAR(16) NOT NULL,
	date_fin_carte DATE NOT NULL,
	num_reglement INTEGER NOT NULL references REGLEMENT(num_reglement)
);
	

CREATE TABLE CONTENIR (
	num_prod INTEGER NOT NULL references PRODUIT(num_prod),
	num_cmde INTEGER NOT NULL references COMMANDE(num_cmde),
	quantite INTEGER NOT NULL,
	prix_unitaire INTEGER NOT NULL,
	PRIMARY KEY (num_prod,num_cmde)
);

#insertion

INSERT INTO CLIENT values ('','MARIN','Jean-Christophe','2235 route de puyricard','13540','PUYRICARD','2002-08-17');
INSERT INTO CLIENT values ('','MARIN','Henri','2235 route de puyricard','13540','PUYRICARD','2002-11-25');
INSERT INTO CLIENT values ('','MARIN','Anne-Marie','2235 route de puyricard','13540','PUYRICARD','2002-12-30');
INSERT INTO CLIENT values ('','MARIN','Jean-Philippe','2235 route de puyricard','13540','PUYRICARD','2003-01-06');