Untitled

CREATE TABLE track_variants (
    id SERIAL PRIMARY KEY,
    track_name VARCHAR(255),
    weather VARCHAR(255),
    conditions VARCHAR(255),
    daytime VARCHAR(255),
    temperature VARCHAR(255),
    snow_type VARCHAR(255)
);


-- Insert Munich's single combination
INSERT INTO track_variants (track_name, weather, conditions, daytime, temperature, snow_type)
VALUES
('Munich', 'Clear', 'Normal', 'Noon', 'Warm', 'Natural');

-- Define the valid values for each attribute
WITH valid_combinations AS (
    SELECT 
        -- Tracks
        track_name,
        
        -- Weather
        weather,
        
        -- Conditions
        conditions,
        
        -- Daytime
        daytime,
        
        -- Temperature
        temperature,
        
        -- Snow type
        snow_type
    FROM (VALUES
        -- Define the attributes and their possible values
        ('Kitzbuehel'),
        ('Wengen'),
        ('Saalbach'),
        ('St. Moritz'),
        ('Zauchensee'),
        ('Val Gardena'),
        ('Zermatt'),
        ('Bormio'),
        ('Beaver Creek'),
        ('Garmisch')
    ) AS tracks(track_name),
    
    -- Weather types
    (VALUES ('Clear'), ('Cloudy'), ('Foggy')) AS weather(weather),
    
    -- Conditions
    (VALUES ('Normal'), ('Snow'), ('Ice')) AS conditions(conditions),
    
    -- Daytime
    (VALUES ('Noon'), ('Night')) AS daytime(daytime),
    
    -- Temperature
    (VALUES ('Warm'), ('Cold'), ('Very cold')) AS temperature(temperature),
    
    -- Snow types
    (VALUES ('Natural'), ('Artificial')) AS snow_type(snow_type)
)

-- Filtering out invalid combinations (Fog and Night together)
SELECT * 
FROM valid_combinations
WHERE NOT (weather = 'Foggy' AND daytime = 'Night');

-- Insert valid combinations for all tracks except Munich
INSERT INTO track_variants (track_name, weather, conditions, daytime, temperature, snow_type)
SELECT track_name, weather, conditions, daytime, temperature, snow_type
FROM valid_combinations
WHERE track_name != 'Munich';