-- TripTale v1.18: Länder und Mehrfachkategorien
-- Diese Datei genau einmal in phpMyAdmin für Datenbank dbs16047092 importieren.

ALTER TABLE travel_1_attractions ADD COLUMN country_code CHAR(2) NULL AFTER city;
ALTER TABLE travel_1_accommodations ADD COLUMN country_code CHAR(2) NULL AFTER city;
ALTER TABLE travel_1_restaurants ADD COLUMN country_code CHAR(2) NULL AFTER city;
ALTER TABLE travel_1_trip_day_items ADD COLUMN country_code CHAR(2) NULL AFTER trip_additional_info;

ALTER TABLE travel_1_categories MODIFY COLUMN type ENUM('attraction','accommodation','restaurant','custom') NOT NULL;

CREATE TABLE travel_1_trip_day_item_categories (
    trip_day_item_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (trip_day_item_id, category_id),
    CONSTRAINT fk_trip_day_item_categories_item FOREIGN KEY (trip_day_item_id)
        REFERENCES travel_1_trip_day_items(id) ON DELETE CASCADE,
    CONSTRAINT fk_trip_day_item_categories_category FOREIGN KEY (category_id)
        REFERENCES travel_1_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX idx_attractions_country ON travel_1_attractions(country_code);
CREATE INDEX idx_accommodations_country ON travel_1_accommodations(country_code);
CREATE INDEX idx_restaurants_country ON travel_1_restaurants(country_code);
CREATE INDEX idx_trip_day_items_country ON travel_1_trip_day_items(country_code);
