/**********************/
/* Schémas des tables */
/**********************/

/* Tables des lieux */

CREATE TABLE lieux (

       id           int NOT NULL auto_increment,

       nom          text,

       ref_type     int,
       ref_quartier int,

       adresse      text,
       url	    text,
       ref_metro    int,

       description  text,


       PRIMARY KEY (id)

);



/* Table types de lieux */

CREATE TABLE types_lieux (

       id            int NOT NULL auto_increment,
       intitule_type text,

       PRIMARY KEY (id)
);



/* Table des quartiers */

CREATE TABLE quartiers (

       id           int NOT NULL auto_increment,
       nom_quartier text,

       PRIMARY KEY (id)
);



/* Table des stations de métros */

CREATE TABLE stations (

       id           int NOT NULL auto_increment,
       nom_station  text,
       ligne1       enum('oui','non'),
       ligne2       enum('oui','non'),
       PRIMARY KEY (id)
);




/* Table des questions */

CREATE TABLE questions (

  	id int NOT NULL auto_increment,	
  	formulation text,
        ref_quartier int,
        reponse      int,

        PRIMARY KEY (id)
);



/* Table des propositions */

CREATE TABLE propositions (

  	id           int NOT NULL auto_increment,	
  	texte        text,
        ref_question int,

        PRIMARY KEY (id)

);




/**********************************/
/* Remplissage des tables de base */
/**********************************/


/* Table types de lieux */

INSERT INTO types_lieux VALUES ('','Restaurant');
INSERT INTO types_lieux VALUES ('','Bar');
INSERT INTO types_lieux VALUES ('','Cinéma');
INSERT INTO types_lieux VALUES ('','Salle de concert');
INSERT INTO types_lieux VALUES ('','Salle de sports');
INSERT INTO types_lieux VALUES ('','Théatre');
INSERT INTO types_lieux VALUES ('','Monument');
INSERT INTO types_lieux VALUES ('','Agence immobilière');
INSERT INTO types_lieux VALUES ('','Alimentation');
INSERT INTO types_lieux VALUES ('','Produits culturels');



/* Table des quartiers */

INSERT INTO quartiers VALUES ('','Vieux Lille');
INSERT INTO quartiers VALUES ('','Centre');
INSERT INTO quartiers VALUES ('','Gares - Euralille');
INSERT INTO quartiers VALUES ('','Hôtel de ville');
INSERT INTO quartiers VALUES ('','Vauban - Esquermes - Bois Blancs');
INSERT INTO quartiers VALUES ('','Wazemmes');
INSERT INTO quartiers VALUES ('','Moulins - Lille sud');
INSERT INTO quartiers VALUES ('','Fives - Hellemmes');




/* Table des stations de métros */

INSERT INTO stations VALUES ('','Gare Flandres','oui','oui');
INSERT INTO stations VALUES ('','Porte des Postes','oui','oui');
INSERT INTO stations VALUES ('','Hellemmes','oui','non');
INSERT INTO stations VALUES ('','Lezennes','oui','non');
INSERT INTO stations VALUES ('','CHR B Calmette','oui','non');
INSERT INTO stations VALUES ('','CHR O Lambret','oui','non');
INSERT INTO stations VALUES ('','Caulier','oui','non');
INSERT INTO stations VALUES ('','Fives','oui','non');
INSERT INTO stations VALUES ('','Gambetta','oui','non');
INSERT INTO stations VALUES ('','Marbrerie','oui','non');
INSERT INTO stations VALUES ('','Rihour','oui','non');
INSERT INTO stations VALUES ('','République Beaux Arts','oui','non');
INSERT INTO stations VALUES ('','Wazemmes','oui','non');
INSERT INTO stations VALUES ('','4 Cantons','oui','non');
INSERT INTO stations VALUES ('','Hotel de Ville','oui','non');
INSERT INTO stations VALUES ('','Pont de Bois','oui','non');
INSERT INTO stations VALUES ('','Triolo','oui','non');
INSERT INTO stations VALUES ('','Cité scientifique','oui','non');
INSERT INTO stations VALUES ('','Croix Centre','non','oui');
INSERT INTO stations VALUES ('','Croix Mairie','non','oui');
INSERT INTO stations VALUES ('','Bois Blancs','non','oui');
INSERT INTO stations VALUES ('','Canteleu','non','oui');
INSERT INTO stations VALUES ('','Cormontaigne','non','oui');
INSERT INTO stations VALUES ('','Gare Europe','non','oui');
INSERT INTO stations VALUES ('','Grand Palais','non','oui');
INSERT INTO stations VALUES ('','Mairie','non','oui');
INSERT INTO stations VALUES ('','Montebello','non','oui');
INSERT INTO stations VALUES ('','Port de Lille','non','oui');
INSERT INTO stations VALUES ('','Porte d\'Arras','non','oui');
INSERT INTO stations VALUES ('','Porte de Douai','non','oui');
INSERT INTO stations VALUES ('','Porte de Valenciennes','non','oui');
INSERT INTO stations VALUES ('','St Maurice Pellevoisin','non','oui');
INSERT INTO stations VALUES ('','Bourg','non','oui');
INSERT INTO stations VALUES ('','Lomme - Lambersart','non','oui');
INSERT INTO stations VALUES ('','Maison des Enfants','non','oui');
INSERT INTO stations VALUES ('','Mitterie','non','oui');
INSERT INTO stations VALUES ('','Pont Supérieur','non','oui');
INSERT INTO stations VALUES ('','St Philibert','non','oui');
INSERT INTO stations VALUES ('','Fort de Mons','non','oui');
INSERT INTO stations VALUES ('','Mairie','non','oui');
INSERT INTO stations VALUES ('','Sarts','non','oui');
INSERT INTO stations VALUES ('','Alsace','non','oui');
INSERT INTO stations VALUES ('','Charles De Gaulle','non','oui');
INSERT INTO stations VALUES ('','Epeule Montesquieu','non','oui');
INSERT INTO stations VALUES ('','Eurotéléport','non','oui');
INSERT INTO stations VALUES ('','Gare Jean Lebas','non','oui');
INSERT INTO stations VALUES ('','Grand Place','non','oui');
INSERT INTO stations VALUES ('','Bourgogne','non','oui');
INSERT INTO stations VALUES ('','CH Dron','non','oui');
INSERT INTO stations VALUES ('','Carliers','non','oui');
INSERT INTO stations VALUES ('','Centre','non','oui');
INSERT INTO stations VALUES ('','Colbert','non','oui');
INSERT INTO stations VALUES ('','Mercure','non','oui');
INSERT INTO stations VALUES ('','Phalempins','non','oui');
INSERT INTO stations VALUES ('','Pont de Neuville','non','oui');
INSERT INTO stations VALUES ('','Sébastopol','non','oui');
INSERT INTO stations VALUES ('','Jean Jaurès','non','oui');
INSERT INTO stations VALUES ('','Les Prés','non','oui');
INSERT INTO stations VALUES ('','Hotel de Ville','non','oui');
INSERT INTO stations VALUES ('','Pavé de Lille','non','oui');