CREATE TABLE `genre` (
  `id` int(11) NOT NULL auto_increment,
  `nom` varchar(255) character set latin1 NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 ;

CREATE TABLE `address` (
  `id` int(11) NOT NULL auto_increment,
  `streetNumber` varchar(45) character set latin1 NOT NULL,
  `streetName` varchar(45) character set latin1 NOT NULL,
  `city` varchar(45) character set latin1 NOT NULL,
  `zipCode` varchar(45) character set latin1 NOT NULL,
  `country` varchar(45) character set latin1 NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ;

CREATE TABLE `products` (
  `id` int(11) NOT NULL auto_increment,
  `nom` varchar(255) character set latin1 NOT NULL,
  `price` int(11) NOT NULL,
  `idGenre` int(11) default NULL,
  `realisateur` varchar(255) character set latin1 default NULL,
  `annee` int(11) default NULL,
  `musique` varchar(255) character set latin1 default NULL,
  `scenario` varchar(255) character set latin1 default NULL,
  PRIMARY KEY  (`id`),
  KEY `FKPRODUCTS1` (`idGenre`),
  CONSTRAINT `FKPRODUCTGENRE` FOREIGN KEY (`idGenre`) REFERENCES `genre` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8 ;

CREATE TABLE `client` (
  `id` int(11) NOT NULL auto_increment,
  `firstName` varchar(45) character set latin1 default NULL,
  `middleName` varchar(45) character set latin1 default NULL,
  `lastName` varchar(45) character set latin1 default NULL,
  `idAddressBilling` int(11) default NULL,
  `idAddressDelivery` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `FKADDRESSBILLING1` (`idAddressBilling`),
  KEY `FKADDRESSDELIVERY` (`idAddressDelivery`),
  CONSTRAINT `FKCLIENTADDRESS2` FOREIGN KEY (`idAddressDelivery`) REFERENCES `address` (`id`),
  CONSTRAINT `FKCLIENTADDRESS1` FOREIGN KEY (`idAddressBilling`) REFERENCES `address` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ;

CREATE TABLE `orders` (
  `id` int(11) NOT NULL auto_increment,
  `dateStarted` date default NULL,
  `dateEnded` date default NULL,
  `idClient` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `FKCLIENT` (`idClient`),
  CONSTRAINT `FKORDERCLIENT` FOREIGN KEY (`idClient`) REFERENCES `client` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ;

CREATE TABLE `orderLine` (
  `id` int(11) NOT NULL auto_increment,
  `idProduct` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `idOrder` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `FKORDER` (`idOrder`),
  KEY `FKLINEPRODUCT` (`idProduct`),
  CONSTRAINT `FKLINEPRODUCT` FOREIGN KEY (`idProduct`) REFERENCES `products` (`id`),
  CONSTRAINT `FKLINESORDER` FOREIGN KEY (`idOrder`) REFERENCES `orders` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 ;


INSERT INTO genre (id,nom) VALUES (1,'Aventure');
INSERT INTO genre (id,nom) VALUES (2,'Policier');
INSERT INTO genre (id,nom) VALUES (3,'Comédie');
INSERT INTO genre (id,nom) VALUES (4,'Drame');
INSERT INTO genre (id,nom) VALUES (5,'Fantastique');
INSERT INTO genre (id,nom) VALUES (6,'Film d''animation');
INSERT INTO genre (id,nom) VALUES (7,'Action');
INSERT INTO genre (id,nom) VALUES (8,'Guerre');
INSERT INTO genre (id,nom) VALUES (9,'Documentaire');
INSERT INTO genre (id,nom) VALUES (10,'Suspense');

INSERT INTO address (id,streetNumber,streetName,city,zipCode,country) VALUES (1,'42','tek street','Metroplex','123456','planet e');
INSERT INTO address (id,streetNumber,streetName,city,zipCode,country) VALUES (2,'13','trance street','Goa','987654','india');

INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (1,'The Big Lebowsky',15,10,'',0,'','');
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (2,'L''enfer du dimanche 2',10,1,'',0,'','');
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (3,'American Beauty',15,4,null,null,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (4,'Vercingetorix',15,1,'Jack Dorfmann',2001,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (5,'Animal Factory',15,2,'Steve Buscemi',2000,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (6,'La vie, l''amour, les vaches',15,3,'Ron Underwood',1991,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (7,'Crossing Guard',15,4,'Sean Penn',1995,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (8,'Tigres et dragons',15,1,'Ang Lee',2000,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (9,'Swimming with sharks',25,4,'G. Huang',1995,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (10,'Sling Blade',20,4,'Billy Bob Thornton',1997,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (11,'Magnolia',20,4,'Paul Thomas Anderson',1999,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (12,'Exit',20,2,'Olivier Megaton',2000,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (13,'X-Men',20,5,'Bryan Singer',2000,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (14,'The Yards',25,2,'James Gray',2000,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (15,'O''Brother',25,3,'Joel Cohen, Ethan Cohen',2000,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (16,'Les mutants de l''espace',15,6,'Bill Plympton',2001,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (17,'High Fidelity',10,3,'Stephen Frears',2000,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (18,'Trainspotting',10,4,'Danny Boyle',1996,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (19,'Predator 2',10,5,'Stephen Hopkins',1990,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (20,'Une affaire de goût',15,4,'Bernard Rapp',1999,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (21,'Cecil B. DeMented',25,3,'John Waters',2000,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (22,'Abyss',25,5,'James Cameron',1989,'','');
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (23,'Ghost World',15,3,'Terry Zwigoff',2001,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (24,'Les Autres',20,5,'Alejandro Amenabar',2001,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (25,'The Big One',20,9,'Michael Moore',1999,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (26,'Ocean''s Eleven',15,10,'Steven Soderbergh',2001,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (27,'La rançon',15,10,'Ron Howard',1996,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (28,'La plage',15,1,'Danny Boyle',1999,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (29,'Barton Fink',15,4,'Joel et Ethan Cohen',1991,null,null);
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (30,'Braveheart',15,1,'Mel Gibson',1995,'James Horner','Randall Wallace');
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (31,'Les grands ducs',20,3,'Patrice Leconte',1995,'Jean-Claude Nachon, AngÈlique Nachon','Patrice Leconte, S. Frydman');
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (32,'La ligne Verte',10,4,'Frank Darambont',1999,'Thomas Newman','Frank Darambont');
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (33,'Fist of Legend',15,7,'Gordon Chan',1994,'Joseph Koo','Gordon Chan, Lan Kay Toa');
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (34,'En territoire ennemi',20,8,'John Moore',2001,'Don Davis','Jim Thomas, John Thomas, David Veloz, Zak Penn');
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (35,'Monique',25,3,'ValÈrie Guignabodet',2002,'CÈdric Neveux','ValÈrie Guignabodet');
INSERT INTO products (id,nom,price,idGenre,realisateur,annee,musique,scenario) VALUES (36,'Little Buddha',15,4,'Bernardo Bertolucci',1993,'Ryuichi Sakamoto','Bernardo Bertolucci, Mark Peploe');

INSERT INTO client (id,firstName,middleName,lastName,idAddressBilling,idAddressDelivery) VALUES (1,'Dim','Dam','Doum',1,2);

INSERT INTO orders (id,dateStarted,dateEnded,idClient) VALUES (1,'2012-01-01','2012-01-06',1);

INSERT INTO orderLine (id,idProduct,quantity,idOrder) VALUES (1,5,2,1);
INSERT INTO orderLine (id,idProduct,quantity,idOrder) VALUES (2,8,4,1);
INSERT INTO orderLine (id,idProduct,quantity,idOrder) VALUES (3,12,1,1);