mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 04:51:11 -05:00
83 lines
5.2 KiB
SQL
83 lines
5.2 KiB
SQL
-- Add category-specific fields to rides table
|
|
|
|
-- Water ride specific columns
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS water_depth_cm INTEGER;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS splash_height_meters DECIMAL(10,2);
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS wetness_level TEXT;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS flume_type TEXT;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS boat_capacity INTEGER;
|
|
|
|
-- Dark ride specific columns
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS theme_name TEXT;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS story_description TEXT;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS show_duration_seconds INTEGER;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS animatronics_count INTEGER;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS projection_type TEXT;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS ride_system TEXT;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS scenes_count INTEGER;
|
|
|
|
-- Flat ride specific columns
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS rotation_type TEXT;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS motion_pattern TEXT;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS platform_count INTEGER;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS swing_angle_degrees INTEGER;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS rotation_speed_rpm INTEGER;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS arm_length_meters DECIMAL(10,2);
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS max_height_reached_meters DECIMAL(10,2);
|
|
|
|
-- Kiddie ride specific columns
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS min_age INTEGER;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS max_age INTEGER;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS educational_theme TEXT;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS character_theme TEXT;
|
|
|
|
-- Transportation ride specific columns
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS transport_type TEXT;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS route_length_meters DECIMAL(10,2);
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS stations_count INTEGER;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS vehicle_capacity INTEGER;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS vehicles_count INTEGER;
|
|
ALTER TABLE public.rides ADD COLUMN IF NOT EXISTS round_trip_duration_seconds INTEGER;
|
|
|
|
-- Add check constraints for enum-like fields
|
|
ALTER TABLE public.rides DROP CONSTRAINT IF EXISTS check_wetness_level;
|
|
ALTER TABLE public.rides ADD CONSTRAINT check_wetness_level
|
|
CHECK (wetness_level IS NULL OR wetness_level IN ('none', 'light', 'moderate', 'heavy', 'soaked'));
|
|
|
|
ALTER TABLE public.rides DROP CONSTRAINT IF EXISTS check_flume_type;
|
|
ALTER TABLE public.rides ADD CONSTRAINT check_flume_type
|
|
CHECK (flume_type IS NULL OR flume_type IN ('log_flume', 'shoot_the_chute', 'river_rapids', 'water_coaster', 'flume_ride'));
|
|
|
|
ALTER TABLE public.rides DROP CONSTRAINT IF EXISTS check_projection_type;
|
|
ALTER TABLE public.rides ADD CONSTRAINT check_projection_type
|
|
CHECK (projection_type IS NULL OR projection_type IN ('2d', '3d', '4d', 'holographic', 'projection_mapping', 'none'));
|
|
|
|
ALTER TABLE public.rides DROP CONSTRAINT IF EXISTS check_ride_system;
|
|
ALTER TABLE public.rides ADD CONSTRAINT check_ride_system
|
|
CHECK (ride_system IS NULL OR ride_system IN ('omnimover', 'trackless', 'motion_simulator', 'boat', 'suspended', 'walk_through'));
|
|
|
|
ALTER TABLE public.rides DROP CONSTRAINT IF EXISTS check_rotation_type;
|
|
ALTER TABLE public.rides ADD CONSTRAINT check_rotation_type
|
|
CHECK (rotation_type IS NULL OR rotation_type IN ('horizontal', 'vertical', 'diagonal', 'multi_axis', 'none'));
|
|
|
|
ALTER TABLE public.rides DROP CONSTRAINT IF EXISTS check_motion_pattern;
|
|
ALTER TABLE public.rides ADD CONSTRAINT check_motion_pattern
|
|
CHECK (motion_pattern IS NULL OR motion_pattern IN ('circular', 'pendulum', 'spinning', 'tilting', 'rocking', 'wave', 'bouncing'));
|
|
|
|
ALTER TABLE public.rides DROP CONSTRAINT IF EXISTS check_transport_type;
|
|
ALTER TABLE public.rides ADD CONSTRAINT check_transport_type
|
|
CHECK (transport_type IS NULL OR transport_type IN ('monorail', 'train', 'chairlift', 'gondola', 'tram', 'peoplemover', 'cable_car'));
|
|
|
|
-- Add indexes for commonly queried fields
|
|
CREATE INDEX IF NOT EXISTS idx_rides_wetness_level ON public.rides(wetness_level) WHERE wetness_level IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_rides_transport_type ON public.rides(transport_type) WHERE transport_type IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_rides_rotation_type ON public.rides(rotation_type) WHERE rotation_type IS NOT NULL;
|
|
|
|
-- Add comments for documentation
|
|
COMMENT ON COLUMN public.rides.water_depth_cm IS 'Water depth in centimeters for water rides';
|
|
COMMENT ON COLUMN public.rides.splash_height_meters IS 'Maximum splash height in meters';
|
|
COMMENT ON COLUMN public.rides.wetness_level IS 'How wet riders get: none, light, moderate, heavy, soaked';
|
|
COMMENT ON COLUMN public.rides.theme_name IS 'Theme name for dark rides';
|
|
COMMENT ON COLUMN public.rides.story_description IS 'Story/narrative description for dark rides';
|
|
COMMENT ON COLUMN public.rides.rotation_type IS 'Type of rotation for flat rides';
|
|
COMMENT ON COLUMN public.rides.transport_type IS 'Type of transportation system'; |