use bdusager01;


-- --Categorie---
CREATE TABLE IF NOT EXISTS Categorie(
  `IdCat` CHAR(4) NOT NULL,
  `NomCat` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`IdCat`));




-- Clients--------------
CREATE TABLE IF NOT EXISTS Clients(
  `idClient` INT(11) NOT NULL AUTO_INCREMENT,
  `NomClient` VARCHAR(45) NOT NULL,
  `AdresseClient` VARCHAR(45) NOT NULL,
  `NumTelClient` VARCHAR(45) NOT NULL,
  `idCatFavorite` CHAR(4) NOT NULL,
  PRIMARY KEY (`idClient`, `idCatFavorite`),
  
  CONSTRAINT `fk_Client_Categorie1`
    FOREIGN KEY (`idCatFavorite`)
    REFERENCES `Categorie` (`IdCat`))
    ;

--  Salle

CREATE TABLE IF NOT EXISTS Salle (
  `idSalle` INT(11) NOT NULL AUTO_INCREMENT,
  `NomSalle` VARCHAR(45) NOT NULL,
  `AdresseSalle` VARCHAR(80) NOT NULL,
  PRIMARY KEY (`idSalle`));



-- Spectacle ---
CREATE TABLE IF NOT EXISTS Spectacle (
  `idSpectacle` INT(11) NOT NULL AUTO_INCREMENT,
  `NomSpectacle` VARCHAR(45) NOT NULL,
  `idCat` CHAR(4) NOT NULL,
  PRIMARY KEY (`idSpectacle`, `idCat`),
  CONSTRAINT `fk_Spectacle_Categorie1`
    FOREIGN KEY (`idCat`)
    REFERENCES Categorie (`IdCat`));
    



-- Presenattion
CREATE TABLE IF NOT EXISTS Presentation (
  `idPresentation` INT(11) NOT NULL AUTO_INCREMENT,
  `datePresentation` DATETIME NOT NULL,
  `idSpectacle` INT(11) NOT NULL,
  `idSalle` INT(11) NOT NULL,
  PRIMARY KEY (`idPresentation`, `idSalle`, `idSpectacle`),
 
  CONSTRAINT `fk_Presentation_Salle1`
    FOREIGN KEY (`idSalle`)
    REFERENCES Salle(`idSalle`),
    
  CONSTRAINT `fk_Presentation_Spectacle1`
    FOREIGN KEY (`idSpectacle`)
    REFERENCES Spectacle(`idSpectacle`));
   




-- Section --
CREATE TABLE IF NOT EXISTS Section(
  `idSection` INT(11) NOT NULL AUTO_INCREMENT,
  `DescriptionSection` VARCHAR(45) NOT NULL,
  `NbSiege` INT(11) NOT NULL,
  `idSalle` INT(11) NOT NULL,
  `Prix` DECIMAL(13,2) NOT NULL,
  PRIMARY KEY (`idSection`, `idSalle`),
  
  CONSTRAINT `fk_Section_Salle`
    FOREIGN KEY (`idSalle`)
    REFERENCES Salle (`idSalle`));
   


-- - Billet--
CREATE TABLE IF NOT EXISTS Billet (
  `idBillet` INT(11) NOT NULL AUTO_INCREMENT,
  `idSection` INT(11) NOT NULL,
  `idPresentation` INT(11) NOT NULL,
  `idClient` INT(11) NOT NULL,
  `DateAchat` DATETIME NOT NULL,
  `Imprimer` TINYINT NOT NULL,
  PRIMARY KEY (`idBillet`, `idClient`, `idPresentation`, `idSection`),
  
  CONSTRAINT `fk_Billet_Client1`
    FOREIGN KEY (`idClient`)
    REFERENCES Clients(`idClient`),
    
  CONSTRAINT `fk_Billet_Presentation1`
    FOREIGN KEY (`idPresentation`)
    REFERENCES Presentation (`idPresentation`),
    
  CONSTRAINT `fk_Billet_Section1`
    FOREIGN KEY (`idSection`)
    REFERENCES Section (`idSection`));
    




-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO Categorie (`IdCat`, `NomCat`) VALUES ('HUMR', 'Humour');
INSERT INTO Categorie (`IdCat`, `NomCat`) VALUES ('MUSQ', 'Musique');
INSERT INTO Categorie (`IdCat`, `NomCat`) VALUES ('ENFT', 'Enfant');
INSERT INTO Categorie (`IdCat`, `NomCat`) VALUES ('ILSN', 'Illusion');
INSERT INTO Categorie (`IdCat`, `NomCat`) VALUES ('DNSE','Illsion');


COMMIT;

-- ---------------------------------------

START TRANSACTION;
INSERT INTO Clients (`idClient`, `NomClient`, `AdresseClient`, `NumTelClient`, `idCatFavorite`) VALUES (1, 'Tristan Chin', '13 rue de Lol', '4509119434', 'HUMR');
INSERT INTO Clients(`idClient`, `NomClient`, `AdresseClient`, `NumTelClient`, `idCatFavorite`) VALUES (2, 'Vanessa Lépine', '52 rue de Wow', '4502174823', 'MUSQ');
INSERT INTO Clients (`idClient`, `NomClient`, `AdresseClient`, `NumTelClient`, `idCatFavorite`) VALUES (3, 'Jonathan Lajeunesse', '12 rue de Gaga', '4502834859', 'ENFT');
INSERT INTO Clients (`idClient`, `NomClient`, `AdresseClient`, `NumTelClient`, `idCatFavorite`) VALUES (4, 'Mathieu Déchaine', '54 rue de Brume', '4501903604', 'ILSN');
INSERT INTO Clients (`idClient`, `NomClient`, `AdresseClient`, `NumTelClient`, `idCatFavorite`) VALUES (5, 'Félix Gravel', '55 rue Balai', '4508383344', 'DNSE');

COMMIT;

-- -----------------

START TRANSACTION;
INSERT INTO Salle (`idSalle`, `NomSalle`, `AdresseSalle`) VALUES (1, 'Le Balcon Cabaret Music-Hall', '23 rue des Pianos');
INSERT INTO Salle(`idSalle`, `NomSalle`, `AdresseSalle`) VALUES (2, 'Hotel 54', '66 rue du Logement');
INSERT INTO Salle(`idSalle`, `NomSalle`, `AdresseSalle`) VALUES (3, 'Amphithéâtre Cogeco', '12 rue du Sell');
INSERT INTO Salle(`idSalle`, `NomSalle`, `AdresseSalle`) VALUES (4, 'Maison du Festival', '5 rue du Rire');
INSERT INTO Salle (`idSalle`, `NomSalle`, `AdresseSalle`) VALUES (5 , 'Salle J.-Antonio-Thompson', '23 rue du Souvenir');

COMMIT;
-- ---------------------

START TRANSACTION;
INSERT INTO Spectacle(`idSpectacle`, `NomSpectacle`, `idCat`) VALUES (1, 'Les Rois dee la Piste', 'HUMR');
INSERT INTO Spectacle (`idSpectacle`, `NomSpectacle`, `idCat`) VALUES (2, 'Alpha et Omega', 'ENFT');
INSERT INTO Spectacle (`idSpectacle`, `NomSpectacle`, `idCat`) VALUES (3, 'The Slackers', 'MUSQ');
INSERT INTO Spectacle (`idSpectacle`, `NomSpectacle`, `idCat`) VALUES (4, 'Idir', 'ILSN');
INSERT INTO Spectacle (`idSpectacle`, `NomSpectacle`, `idCat`) VALUES (5, 'Sofi Tukker', 'DNSE');

COMMIT;


-- -----------------------------------------------------

START TRANSACTION;
INSERT INTO Presentation (`idPresentation`, `datePresentation`, `idSpectacle`, `idSalle`) VALUES (1, '2018-02-02 12:00:00', 1, 3);
INSERT INTO Presentation (`idPresentation`, `datePresentation`, `idSpectacle`, `idSalle`) VALUES (2, '2018-04-04 13:00:00', 5, 4);
INSERT INTO Presentation (`idPresentation`, `datePresentation`, `idSpectacle`, `idSalle`) VALUES (3, '2018-07-07 14:00:00', 4, 1);
INSERT INTO Presentation  (`idPresentation`, `datePresentation`, `idSpectacle`, `idSalle`) VALUES (4, '2018-09-09 15:00:00', 3, 2);
INSERT INTO Presentation  (`idPresentation`, `datePresentation`, `idSpectacle`, `idSalle`) VALUES (5, '2018-10-10 16:00:00', 2, 5);

COMMIT;



-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO Section (`idSection`, `DescriptionSection`, `NbSiege`, `idSalle`, `Prix`) VALUES (1, 'Sol', 50, 1, 100.00);
INSERT INTO Section(`idSection`, `DescriptionSection`, `NbSiege`, `idSalle`, `Prix`) VALUES (2, 'Milieu', 100, 1, 50.00);
INSERT INTO Section (`idSection`, `DescriptionSection`, `NbSiege`, `idSalle`, `Prix`) VALUES (3, 'Haut', 150, 1, 25.50);
INSERT INTO Section (`idSection`, `DescriptionSection`, `NbSiege`, `idSalle`, `Prix`) VALUES (4, 'Bas', 25, 2, 200.75);
INSERT INTO Section (`idSection`, `DescriptionSection`, `NbSiege`, `idSalle`, `Prix`) VALUES (5, '2e', 40, 2, 75.50);
INSERT INTO Section (`idSection`, `DescriptionSection`, `NbSiege`, `idSalle`, `Prix`) VALUES (6, 'Principale', 23, 3, 100.00);
INSERT INTO Section (`idSection`, `DescriptionSection`, `NbSiege`, `idSalle`, `Prix`) VALUES (7, 'Premium', 10, 3, 175.00);
INSERT INTO Section (`idSection`, `DescriptionSection`, `NbSiege`, `idSalle`, `Prix`) VALUES (8, 'Salle', 250, 4, 45.05);
INSERT INTO Section (`idSection`, `DescriptionSection`, `NbSiege`, `idSalle`, `Prix`) VALUES (9, 'Auditorium', 54, 5, 30.00);
INSERT INTO Section (`idSection`, `DescriptionSection`, `NbSiege`, `idSalle`, `Prix`) VALUES (10, 'Hall', 100, 5, 25.25);

COMMIT;


-- -----------------------------------------------------

-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO Billet (`idBillet`, `idSection`, `idPresentation`, `idClient`, `DateAchat`, `Imprimer`) VALUES (1, 2, 1, 1, '2018-01-01 10:10:10', 1);
INSERT INTO Billet(`idBillet`, `idSection`, `idPresentation`, `idClient`, `DateAchat`, `Imprimer`) VALUES (2, 4, 2, 2, '2018-02-02 11:11:11', 0);
INSERT INTO Billet (`idBillet`, `idSection`, `idPresentation`, `idClient`, `DateAchat`, `Imprimer`) VALUES (3, 6, 3, 3, '2018-03-03 12:12:12', 1);
INSERT INTO Billet (`idBillet`, `idSection`, `idPresentation`, `idClient`, `DateAchat`, `Imprimer`) VALUES (4, 8, 4, 4, '2018-04-04 13:13:13', 0);
INSERT INTO Billet (`idBillet`, `idSection`, `idPresentation`, `idClient`, `DateAchat`, `Imprimer`) VALUES (5, 10, 5, 5, '2018-05-05 14:14:14', 1);

COMMIT;