
# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

#-----------------------------------------------------------------------------
#-- utilisateur
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `utilisateur`;


CREATE TABLE `utilisateur`
(
	`id` INTEGER(11)  NOT NULL AUTO_INCREMENT,
	`email` VARCHAR(40)  NOT NULL,
	`password` VARCHAR(40)  NOT NULL,
	`nom` VARCHAR(40)  NOT NULL,
	`numclient` INTEGER(11)  NOT NULL,
	`facturenumerique` TINYINT default 1,
	`created_at` DATETIME,
	`updated_at` DATETIME,
	PRIMARY KEY (`id`),
	UNIQUE KEY `Utilisateur_U_1` (`email`)
)Type=MyIsam;

#-----------------------------------------------------------------------------
#-- client
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `client`;


CREATE TABLE `client`
(
	`numclient` INTEGER(11)  NOT NULL,
	`titrecivil` INTEGER(11)  NOT NULL,
	`nom` VARCHAR(50)  NOT NULL,
	`prenom` VARCHAR(50)  NOT NULL,
	`adr1` VARCHAR(50)  NOT NULL,
	`adr2` VARCHAR(50),
	`cp` VARCHAR(5),
	`ville` VARCHAR(50),
	`tel` VARCHAR(50),
	`email` VARCHAR(255),
	`norue` VARCHAR(10),
	PRIMARY KEY (`numclient`)
)Type=MyIsam;

#-----------------------------------------------------------------------------
#-- clientgroupe
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `clientgroupe`;


CREATE TABLE `clientgroupe`
(
	`id` INTEGER(11)  NOT NULL AUTO_INCREMENT,
	`numclient` INTEGER(11)  NOT NULL,
	`numgroupe` INTEGER(11)  NOT NULL,
	PRIMARY KEY (`id`)
)Type=MyIsam;

#-----------------------------------------------------------------------------
#-- encaissement
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `encaissement`;


CREATE TABLE `encaissement`
(
	`numencaissement` INTEGER(11)  NOT NULL AUTO_INCREMENT,
	`numclient` INTEGER(11)  NOT NULL,
	`datepaiement` DATE  NOT NULL,
	`montantpaye` FLOAT  NOT NULL,
	`nofacture` INTEGER(11)  NOT NULL,
	`numrole` INTEGER(11)  NOT NULL,
	`etat` TINYINT default 0 NOT NULL,
	`transactionid` INTEGER(11)  NOT NULL,
	PRIMARY KEY (`numencaissement`),
	INDEX `encaissement_FI_1` (`numclient`),
	CONSTRAINT `encaissement_FK_1`
		FOREIGN KEY (`numclient`)
		REFERENCES `client` (`numclient`),
	INDEX `encaissement_FI_2` (`numrole`),
	CONSTRAINT `encaissement_FK_2`
		FOREIGN KEY (`numrole`)
		REFERENCES `role` (`numrole`)
)Type=MyIsam;

#-----------------------------------------------------------------------------
#-- enfant
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `enfant`;


CREATE TABLE `enfant`
(
	`numenfant` INTEGER(11)  NOT NULL,
	`nom` VARCHAR(50)  NOT NULL,
	`prenom` VARCHAR(50)  NOT NULL,
	`numclientp` INTEGER(11)  NOT NULL,
	`datenaissance` DATE,
	`numclientI` INTEGER(11),
	PRIMARY KEY (`numenfant`)
)Type=MyIsam;

#-----------------------------------------------------------------------------
#-- facture
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `facture`;


CREATE TABLE `facture`
(
	`numfacture` INTEGER(11)  NOT NULL,
	`numrole` INTEGER(11)  NOT NULL,
	`numclient` INTEGER(11)  NOT NULL,
	`totalttc` FLOAT  NOT NULL,
	`nofacture` INTEGER(11)  NOT NULL,
	PRIMARY KEY (`numfacture`),
	INDEX `facture_FI_1` (`numrole`),
	CONSTRAINT `facture_FK_1`
		FOREIGN KEY (`numrole`)
		REFERENCES `role` (`numrole`),
	INDEX `facture_FI_2` (`numclient`),
	CONSTRAINT `facture_FK_2`
		FOREIGN KEY (`numclient`)
		REFERENCES `client` (`numclient`)
)Type=MyIsam;

#-----------------------------------------------------------------------------
#-- itemfacture
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `itemfacture`;


CREATE TABLE `itemfacture`
(
	`numitemfacture` INTEGER(11)  NOT NULL,
	`numrole` INTEGER(11)  NOT NULL,
	`numfacture` INTEGER(11)  NOT NULL,
	`numclient` INTEGER(11)  NOT NULL,
	`numenfant` INTEGER(11)  NOT NULL,
	`nomenfant` VARCHAR(200)  NOT NULL,
	`libprestation` VARCHAR(100)  NOT NULL,
	`q` FLOAT,
	`pu` FLOAT,
	`ht` FLOAT,
	`txtva` FLOAT,
	`ttc` FLOAT,
	`tva` FLOAT,
	PRIMARY KEY (`numitemfacture`),
	INDEX `itemfacture_FI_1` (`numfacture`),
	CONSTRAINT `itemfacture_FK_1`
		FOREIGN KEY (`numfacture`)
		REFERENCES `facture` (`numfacture`),
	INDEX `itemfacture_FI_2` (`numclient`),
	CONSTRAINT `itemfacture_FK_2`
		FOREIGN KEY (`numclient`)
		REFERENCES `client` (`numclient`),
	INDEX `itemfacture_FI_3` (`numenfant`),
	CONSTRAINT `itemfacture_FK_3`
		FOREIGN KEY (`numenfant`)
		REFERENCES `enfant` (`numenfant`)
)Type=MyIsam;

#-----------------------------------------------------------------------------
#-- role
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `role`;


CREATE TABLE `role`
(
	`numrole` INTEGER(11)  NOT NULL,
	`titrerole` VARCHAR(50)  NOT NULL,
	`datefacture` DATE  NOT NULL,
	`datepaiement` DATE  NOT NULL,
	`datedebperiode` DATE  NOT NULL,
	`datefinperiode` DATE  NOT NULL,
	`adr1e` VARCHAR(50),
	`adr2e` VARCHAR(50),
	`cpe` VARCHAR(5),
	`villee` VARCHAR(50),
	`notvaintra` VARCHAR(50),
	PRIMARY KEY (`numrole`)
)Type=MyIsam;

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;
