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, title VARCHAR(255) NOT NULL, h1 VARCHAR(255) NOT NULL, PRIMARY KEY(page_name)) ENGINE = INNODB;
CREATE TABLE category (id INT UNSIGNED AUTO_INCREMENT, description TEXT NOT NULL, status INT UNSIGNED NOT NULL, meta TEXT NOT NULL, title VARCHAR(255) NOT NULL, h1 VARCHAR(255) NOT NULL, PRIMARY KEY(id)) 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 TEXT NOT NULL, hotel_id INT UNSIGNED NOT NULL, desc_id INT UNSIGNED NOT NULL, title VARCHAR(255), 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(255), title VARCHAR(255), h1 VARCHAR(255), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE home (id INT UNSIGNED AUTO_INCREMENT, meta_desc TEXT NOT NULL, title VARCHAR(255) NOT NULL, deeplink VARCHAR(255), meta_keyword VARCHAR(255), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE home_description (id INT UNSIGNED AUTO_INCREMENT, page_id INT UNSIGNED NOT NULL, rank INT UNSIGNED NOT NULL, description TEXT NOT NULL, title VARCHAR(255), INDEX page_id_idx (page_id), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE home_photos (id INT UNSIGNED AUTO_INCREMENT, page_id INT UNSIGNED NOT NULL, status INT UNSIGNED NOT NULL, alt VARCHAR(100), description VARCHAR(100) NOT NULL, path VARCHAR(100) NOT NULL, title VARCHAR(100) NOT NULL, subtitle VARCHAR(100), link VARCHAR(150), css_loc VARCHAR(100), css_col VARCHAR(100), rank INT UNSIGNED, INDEX page_id_idx (page_id), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE hotel_category (hotel_id INT UNSIGNED AUTO_INCREMENT, category_id INT UNSIGNED NOT NULL, rank INT UNSIGNED NOT NULL, description TEXT NOT NULL, INDEX category_id_idx (category_id), PRIMARY KEY(hotel_id)) ENGINE = INNODB;
CREATE TABLE hotel_facility (hotel_id INT UNSIGNED, facility_id INT UNSIGNED, is_special TINYINT NOT NULL, rank INT UNSIGNED, description TEXT, 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, rank INT UNSIGNED NOT NULL, description TEXT 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 hotel_tag (hotel_id INT UNSIGNED, tag_id INT UNSIGNED, rank INT UNSIGNED NOT NULL, img_id INT UNSIGNED, title VARCHAR(100), desc TEXT NOT NULL, INDEX img_id_idx (img_id), PRIMARY KEY(hotel_id, tag_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, 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, title VARCHAR(100) NOT NULL, h1 VARCHAR(100) NOT NULL, creation_date DATETIME NOT NULL, last_update DATETIME DEFAULT '0000-00-00 00:00:00' 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 original_photos (id INT UNSIGNED AUTO_INCREMENT, hotel_id INT UNSIGNED NOT NULL, path TEXT NOT NULL, title TEXT, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE photos (id INT UNSIGNED AUTO_INCREMENT, hotel_id INT UNSIGNED NOT NULL, status INT UNSIGNED DEFAULT '1' 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, 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), status INT UNSIGNED NOT NULL, traveler_type VARCHAR(31) DEFAULT 'Viajantes Individuais', 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;
CREATE TABLE tag (id INT UNSIGNED AUTO_INCREMENT, description TEXT NOT NULL, status INT UNSIGNED NOT NULL, meta TEXT NOT NULL, title VARCHAR(255) NOT NULL, h1 VARCHAR(255) NOT NULL, img VARCHAR(100), logo VARCHAR(100), page_name VARCHAR(100) NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
ALTER TABLE descriptions ADD CONSTRAINT descriptions_hotel_id_hotels_id FOREIGN KEY (hotel_id) REFERENCES hotels(id);
ALTER TABLE home_description ADD CONSTRAINT home_description_page_id_home_id FOREIGN KEY (page_id) REFERENCES home(id);
ALTER TABLE home_photos ADD CONSTRAINT home_photos_page_id_home_id FOREIGN KEY (page_id) REFERENCES home(id);
ALTER TABLE hotel_category ADD CONSTRAINT hotel_category_category_id_category_id FOREIGN KEY (category_id) REFERENCES category(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 hotel_tag ADD CONSTRAINT hotel_tag_tag_id_tag_id FOREIGN KEY (tag_id) REFERENCES tag(id);
ALTER TABLE hotel_tag ADD CONSTRAINT hotel_tag_img_id_photos_id FOREIGN KEY (img_id) REFERENCES photos(id);
ALTER TABLE hotel_tag ADD CONSTRAINT hotel_tag_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);
