-- TripTale v1.19: Eigenständiger Katalog für eigene Programmpunkte
-- Reisespezifische freie Punkte bleiben unverändert erhalten.

ALTER TABLE travel_1_trip_day_items
    MODIFY COLUMN item_type ENUM('attraction','accommodation','restaurant','custom','catalog_custom') NOT NULL;

CREATE TABLE IF NOT EXISTS travel_1_custom_points (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    short_description VARCHAR(500) NULL,
    description TEXT NULL,
    address VARCHAR(255) NULL,
    city VARCHAR(150) NULL,
    country_code CHAR(2) NULL,
    google_maps_url VARCHAR(500) NULL,
    plus_code VARCHAR(50) NULL,
    website VARCHAR(500) NULL,
    phone VARCHAR(50) NULL,
    note TEXT NULL,
    image_path VARCHAR(255) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_custom_points_status (status),
    KEY idx_custom_points_country (country_code),
    CONSTRAINT fk_custom_points_created_by FOREIGN KEY (created_by) REFERENCES travel_1_users(id) ON DELETE SET NULL,
    CONSTRAINT fk_custom_points_updated_by FOREIGN KEY (updated_by) REFERENCES travel_1_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS travel_1_custom_point_categories (
    custom_point_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (custom_point_id, category_id),
    CONSTRAINT fk_custom_point_categories_point FOREIGN KEY (custom_point_id)
        REFERENCES travel_1_custom_points(id) ON DELETE CASCADE,
    CONSTRAINT fk_custom_point_categories_category FOREIGN KEY (category_id)
        REFERENCES travel_1_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
