CREATE TABLE areas (area VARCHAR(45) NOT NULL, page_name VARCHAR(45), description VARCHAR(255) NOT NULL, photo_path VARCHAR(255) NOT NULL, status VARCHAR(10) NOT NULL, meta VARCHAR(255) NOT NULL, PRIMARY KEY(page_name)) ENGINE = INNODB;
CREATE TABLE chain (id INT UNSIGNED AUTO_INCREMENT, chain VARCHAR(45) NOT NULL, page_name VARCHAR(45) NOT NULL, description VARCHAR(255) NOT NULL, meta VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE descriptions (id INT UNSIGNED AUTO_INCREMENT, description VARCHAR(255) NOT NULL, hotel_id INT UNSIGNED NOT NULL, desc_id INT UNSIGNED NOT NULL, INDEX hotel_id_idx (hotel_id), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE facility (id INT UNSIGNED AUTO_INCREMENT, facility VARCHAR(255) NOT NULL, page_name VARCHAR(100) NOT NULL, description VARCHAR(255), meta VARCHAR(45), hotel_text VARCHAR(100), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE hotel_facility (hotel_id INT UNSIGNED, facility_id INT UNSIGNED, is_special TINYINT NOT NULL, PRIMARY KEY(hotel_id, facility_id)) ENGINE = INNODB;
CREATE TABLE hotel_interest (hotel_id INT UNSIGNED, interest_id INT UNSIGNED, distance VARCHAR(45) NOT NULL, PRIMARY KEY(hotel_id, interest_id)) ENGINE = INNODB;
CREATE TABLE hotel_popular (hotel_id INT UNSIGNED, popular_hotel_id INT UNSIGNED, similarity DOUBLE, PRIMARY KEY(hotel_id, popular_hotel_id)) ENGINE = INNODB;
CREATE TABLE hotels (id INT UNSIGNED, name VARCHAR(100) NOT NULL, page_name VARCHAR(100) NOT NULL, street_number VARCHAR(15), street VARCHAR(100) NOT NULL, zip VARCHAR(45) NOT NULL, latitude DOUBLE NOT NULL, longitude DOUBLE NOT NULL, status INT UNSIGNED NOT NULL, area VARCHAR(45) NOT NULL, nb_rooms INT UNSIGNED NOT NULL, chain_id INT UNSIGNED, name_du VARCHAR(100), rank INT UNSIGNED NOT NULL, min_price DOUBLE, category INT UNSIGNED, deeplink TEXT NOT NULL, hprank INT UNSIGNED, telephone VARCHAR(45), meta VARCHAR(100) NOT NULL, INDEX area_idx (area), INDEX chain_id_idx (chain_id), INDEX status_idx (status), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE index_status (id INT UNSIGNED AUTO_INCREMENT, status VARCHAR(45) NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE interest (id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(45) NOT NULL, page_name VARCHAR(45) NOT NULL, creation_date DATETIME NOT NULL, last_update DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL, latitude DOUBLE NOT NULL, longitude DOUBLE NOT NULL, description VARCHAR(255) NOT NULL, status INT UNSIGNED NOT NULL, category INT UNSIGNED NOT NULL, photo_path VARCHAR(255) NOT NULL, meta VARCHAR(255) NOT NULL, INDEX category_idx (category), INDEX status_idx (status), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE interest_category (id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(45) NOT NULL, page_name VARCHAR(45) NOT NULL, description VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE interest_distance (origin_interest_id INT UNSIGNED AUTO_INCREMENT, destination_interest_id INT UNSIGNED, distance DOUBLE NOT NULL, PRIMARY KEY(origin_interest_id, destination_interest_id)) ENGINE = INNODB;
CREATE TABLE photos (id INT UNSIGNED AUTO_INCREMENT, hotel_id INT UNSIGNED NOT NULL, status INT UNSIGNED NOT NULL, thumb TINYINT, description VARCHAR(250) NOT NULL, alt TEXT, path VARCHAR(45) NOT NULL, width INT UNSIGNED DEFAULT '500' NOT NULL, height INT UNSIGNED DEFAULT '310' NOT NULL, INDEX hotel_id_idx (hotel_id), INDEX status_idx (status), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE reviews (id INT UNSIGNED AUTO_INCREMENT, hotel_id INT UNSIGNED NOT NULL, personel INT UNSIGNED NOT NULL, services INT UNSIGNED NOT NULL, proper INT UNSIGNED NOT NULL, quality_price INT UNSIGNED NOT NULL, localisation INT UNSIGNED NOT NULL, average_note DOUBLE, traveler_type VARCHAR(31), date DATETIME NOT NULL, positive TEXT NOT NULL, negative TEXT, name VARCHAR(45), created_at DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL, confort INT UNSIGNED NOT NULL, email VARCHAR(45), INDEX hotel_id_idx (hotel_id), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE status (id INT UNSIGNED AUTO_INCREMENT, status VARCHAR(10), PRIMARY KEY(id)) ENGINE = INNODB;
ALTER TABLE descriptions ADD CONSTRAINT descriptions_hotel_id_hotels_id FOREIGN KEY (hotel_id) REFERENCES hotels(id);
ALTER TABLE hotel_facility ADD CONSTRAINT hotel_facility_hotel_id_hotels_id FOREIGN KEY (hotel_id) REFERENCES hotels(id);
ALTER TABLE hotel_facility ADD CONSTRAINT hotel_facility_facility_id_facility_id FOREIGN KEY (facility_id) REFERENCES facility(id);
ALTER TABLE hotel_interest ADD CONSTRAINT hotel_interest_interest_id_interest_id FOREIGN KEY (interest_id) REFERENCES interest(id);
ALTER TABLE hotel_interest ADD CONSTRAINT hotel_interest_hotel_id_hotels_id FOREIGN KEY (hotel_id) REFERENCES hotels(id);
ALTER TABLE hotel_popular ADD CONSTRAINT hotel_popular_popular_hotel_id_hotels_id FOREIGN KEY (popular_hotel_id) REFERENCES hotels(id);
ALTER TABLE hotel_popular ADD CONSTRAINT hotel_popular_hotel_id_hotels_id FOREIGN KEY (hotel_id) REFERENCES hotels(id);
ALTER TABLE hotels ADD CONSTRAINT hotels_status_status_id FOREIGN KEY (status) REFERENCES status(id);
ALTER TABLE hotels ADD CONSTRAINT hotels_chain_id_chain_id FOREIGN KEY (chain_id) REFERENCES chain(id);
ALTER TABLE hotels ADD CONSTRAINT hotels_area_areas_page_name FOREIGN KEY (area) REFERENCES areas(page_name);
ALTER TABLE interest ADD CONSTRAINT interest_status_status_id FOREIGN KEY (status) REFERENCES status(id);
ALTER TABLE interest ADD CONSTRAINT interest_category_interest_category_id FOREIGN KEY (category) REFERENCES interest_category(id);
ALTER TABLE photos ADD CONSTRAINT photos_status_status_id FOREIGN KEY (status) REFERENCES status(id);
ALTER TABLE photos ADD CONSTRAINT photos_hotel_id_hotels_id FOREIGN KEY (hotel_id) REFERENCES hotels(id);
ALTER TABLE reviews ADD CONSTRAINT reviews_hotel_id_hotels_id FOREIGN KEY (hotel_id) REFERENCES hotels(id);