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, creation_date DATETIME NOT NULL, last_update DATETIME 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(45) NOT NULL, page_name VARCHAR(45) NOT NULL, description VARCHAR(255), meta VARCHAR(45) NOT NULL, 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 INT UNSIGNED NOT NULL, street VARCHAR(45) NOT NULL, zip VARCHAR(45) NOT NULL, latitude DOUBLE NOT NULL, longitude DOUBLE, status INT UNSIGNED NOT NULL, area VARCHAR(45) NOT NULL, creation_date DATETIME NOT NULL, last_update DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL, nb_rooms INT UNSIGNED NOT NULL, chain_id INT UNSIGNED, name_du VARCHAR(100) NOT NULL, rank INT UNSIGNED NOT NULL, min_price DOUBLE, category INT UNSIGNED, deeplink TEXT 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, url_source VARCHAR(100) NOT NULL, status INT UNSIGNED NOT NULL, creation_date DATETIME NOT NULL, last_update DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL, thumb TINYINT, full TINYINT UNSIGNED, description VARCHAR(255) NOT NULL, alt VARCHAR(100) NOT NULL, path VARCHAR(45), thumbs TINYINT, INDEX hotel_id_idx (hotel_id), INDEX status_idx (status), 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);