mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 04:51:11 -05:00
451 lines
14 KiB
SQL
451 lines
14 KiB
SQL
-- Phase 2: Create Typed Submission Tables
|
|
|
|
-- 1. Park Submissions
|
|
CREATE TABLE park_submissions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
submission_id UUID NOT NULL REFERENCES content_submissions(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL,
|
|
description TEXT,
|
|
park_type TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'operating',
|
|
opening_date DATE,
|
|
closing_date DATE,
|
|
website_url TEXT,
|
|
phone TEXT,
|
|
email TEXT,
|
|
operator_id UUID,
|
|
property_owner_id UUID,
|
|
location_id UUID REFERENCES locations(id),
|
|
banner_image_url TEXT,
|
|
banner_image_id TEXT,
|
|
card_image_url TEXT,
|
|
card_image_id TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
|
|
UNIQUE(submission_id)
|
|
);
|
|
|
|
-- 2. Ride Submissions
|
|
CREATE TABLE ride_submissions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
submission_id UUID NOT NULL REFERENCES content_submissions(id) ON DELETE CASCADE,
|
|
park_id UUID,
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL,
|
|
description TEXT,
|
|
category TEXT NOT NULL,
|
|
ride_sub_type TEXT,
|
|
status TEXT NOT NULL DEFAULT 'operating',
|
|
opening_date DATE,
|
|
closing_date DATE,
|
|
manufacturer_id UUID,
|
|
designer_id UUID,
|
|
ride_model_id UUID,
|
|
height_requirement INTEGER,
|
|
age_requirement INTEGER,
|
|
capacity_per_hour INTEGER,
|
|
duration_seconds INTEGER,
|
|
max_speed_kmh NUMERIC,
|
|
max_height_meters NUMERIC,
|
|
length_meters NUMERIC,
|
|
drop_height_meters NUMERIC,
|
|
inversions INTEGER DEFAULT 0,
|
|
max_g_force NUMERIC,
|
|
coaster_type TEXT,
|
|
seating_type TEXT,
|
|
intensity_level TEXT,
|
|
banner_image_url TEXT,
|
|
banner_image_id TEXT,
|
|
card_image_url TEXT,
|
|
card_image_id TEXT,
|
|
image_url TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
|
|
UNIQUE(submission_id)
|
|
);
|
|
|
|
-- 3. Company Submissions
|
|
CREATE TABLE company_submissions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
submission_id UUID NOT NULL REFERENCES content_submissions(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL,
|
|
description TEXT,
|
|
company_type TEXT NOT NULL,
|
|
person_type TEXT DEFAULT 'company',
|
|
founded_year INTEGER,
|
|
headquarters_location TEXT,
|
|
website_url TEXT,
|
|
logo_url TEXT,
|
|
banner_image_url TEXT,
|
|
banner_image_id TEXT,
|
|
card_image_url TEXT,
|
|
card_image_id TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
|
|
UNIQUE(submission_id)
|
|
);
|
|
|
|
-- 4. Ride Model Submissions
|
|
CREATE TABLE ride_model_submissions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
submission_id UUID NOT NULL REFERENCES content_submissions(id) ON DELETE CASCADE,
|
|
manufacturer_id UUID,
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL,
|
|
description TEXT,
|
|
category TEXT NOT NULL,
|
|
ride_type TEXT NOT NULL,
|
|
banner_image_url TEXT,
|
|
banner_image_id TEXT,
|
|
card_image_url TEXT,
|
|
card_image_id TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
|
|
UNIQUE(submission_id)
|
|
);
|
|
|
|
-- 5. Ride Technical Specifications
|
|
CREATE TABLE ride_technical_specs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
ride_submission_id UUID NOT NULL REFERENCES ride_submissions(id) ON DELETE CASCADE,
|
|
spec_name TEXT NOT NULL,
|
|
spec_value TEXT NOT NULL,
|
|
spec_type TEXT NOT NULL,
|
|
category TEXT,
|
|
unit TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- 6. Ride Coaster Statistics
|
|
CREATE TABLE ride_coaster_stats (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
ride_submission_id UUID NOT NULL REFERENCES ride_submissions(id) ON DELETE CASCADE,
|
|
stat_name TEXT NOT NULL,
|
|
stat_value NUMERIC NOT NULL,
|
|
unit TEXT,
|
|
category TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- 7. Ride Former Names
|
|
CREATE TABLE ride_former_names (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
ride_submission_id UUID NOT NULL REFERENCES ride_submissions(id) ON DELETE CASCADE,
|
|
former_name TEXT NOT NULL,
|
|
date_changed DATE,
|
|
reason TEXT,
|
|
order_index INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- 8. Submission Dependencies
|
|
CREATE TABLE submission_dependencies (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
parent_submission_id UUID NOT NULL,
|
|
child_submission_id UUID NOT NULL,
|
|
parent_entity_type TEXT NOT NULL,
|
|
child_entity_type TEXT NOT NULL,
|
|
dependency_type TEXT NOT NULL DEFAULT 'requires',
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
|
|
UNIQUE(parent_submission_id, child_submission_id)
|
|
);
|
|
|
|
-- Create indexes for better query performance
|
|
CREATE INDEX idx_park_submissions_submission_id ON park_submissions(submission_id);
|
|
CREATE INDEX idx_ride_submissions_submission_id ON ride_submissions(submission_id);
|
|
CREATE INDEX idx_ride_submissions_park_id ON ride_submissions(park_id);
|
|
CREATE INDEX idx_company_submissions_submission_id ON company_submissions(submission_id);
|
|
CREATE INDEX idx_ride_model_submissions_submission_id ON ride_model_submissions(submission_id);
|
|
CREATE INDEX idx_ride_technical_specs_ride_submission_id ON ride_technical_specs(ride_submission_id);
|
|
CREATE INDEX idx_ride_coaster_stats_ride_submission_id ON ride_coaster_stats(ride_submission_id);
|
|
CREATE INDEX idx_ride_former_names_ride_submission_id ON ride_former_names(ride_submission_id);
|
|
CREATE INDEX idx_submission_dependencies_parent ON submission_dependencies(parent_submission_id);
|
|
CREATE INDEX idx_submission_dependencies_child ON submission_dependencies(child_submission_id);
|
|
|
|
-- Enable RLS on all tables
|
|
ALTER TABLE park_submissions ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE ride_submissions ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE company_submissions ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE ride_model_submissions ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE ride_technical_specs ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE ride_coaster_stats ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE ride_former_names ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE submission_dependencies ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- RLS Policies for park_submissions
|
|
CREATE POLICY "Users can view their own park submissions"
|
|
ON park_submissions FOR SELECT
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM content_submissions cs
|
|
WHERE cs.id = park_submissions.submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can view all park submissions"
|
|
ON park_submissions FOR SELECT
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "Users can insert their own park submissions"
|
|
ON park_submissions FOR INSERT
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM content_submissions cs
|
|
WHERE cs.id = park_submissions.submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can update park submissions"
|
|
ON park_submissions FOR UPDATE
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "Moderators can delete park submissions"
|
|
ON park_submissions FOR DELETE
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
-- RLS Policies for ride_submissions
|
|
CREATE POLICY "Users can view their own ride submissions"
|
|
ON ride_submissions FOR SELECT
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM content_submissions cs
|
|
WHERE cs.id = ride_submissions.submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can view all ride submissions"
|
|
ON ride_submissions FOR SELECT
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "Users can insert their own ride submissions"
|
|
ON ride_submissions FOR INSERT
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM content_submissions cs
|
|
WHERE cs.id = ride_submissions.submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can update ride submissions"
|
|
ON ride_submissions FOR UPDATE
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "Moderators can delete ride submissions"
|
|
ON ride_submissions FOR DELETE
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
-- RLS Policies for company_submissions
|
|
CREATE POLICY "Users can view their own company submissions"
|
|
ON company_submissions FOR SELECT
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM content_submissions cs
|
|
WHERE cs.id = company_submissions.submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can view all company submissions"
|
|
ON company_submissions FOR SELECT
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "Users can insert their own company submissions"
|
|
ON company_submissions FOR INSERT
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM content_submissions cs
|
|
WHERE cs.id = company_submissions.submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can update company submissions"
|
|
ON company_submissions FOR UPDATE
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "Moderators can delete company submissions"
|
|
ON company_submissions FOR DELETE
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
-- RLS Policies for ride_model_submissions
|
|
CREATE POLICY "Users can view their own ride model submissions"
|
|
ON ride_model_submissions FOR SELECT
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM content_submissions cs
|
|
WHERE cs.id = ride_model_submissions.submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can view all ride model submissions"
|
|
ON ride_model_submissions FOR SELECT
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "Users can insert their own ride model submissions"
|
|
ON ride_model_submissions FOR INSERT
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM content_submissions cs
|
|
WHERE cs.id = ride_model_submissions.submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can update ride model submissions"
|
|
ON ride_model_submissions FOR UPDATE
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "Moderators can delete ride model submissions"
|
|
ON ride_model_submissions FOR DELETE
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
-- RLS Policies for ride_technical_specs
|
|
CREATE POLICY "Users can view specs for their own ride submissions"
|
|
ON ride_technical_specs FOR SELECT
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM ride_submissions rs
|
|
JOIN content_submissions cs ON cs.id = rs.submission_id
|
|
WHERE rs.id = ride_technical_specs.ride_submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can view all ride technical specs"
|
|
ON ride_technical_specs FOR SELECT
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "Users can insert specs for their own ride submissions"
|
|
ON ride_technical_specs FOR INSERT
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM ride_submissions rs
|
|
JOIN content_submissions cs ON cs.id = rs.submission_id
|
|
WHERE rs.id = ride_technical_specs.ride_submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can manage ride technical specs"
|
|
ON ride_technical_specs FOR ALL
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
-- RLS Policies for ride_coaster_stats
|
|
CREATE POLICY "Users can view stats for their own ride submissions"
|
|
ON ride_coaster_stats FOR SELECT
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM ride_submissions rs
|
|
JOIN content_submissions cs ON cs.id = rs.submission_id
|
|
WHERE rs.id = ride_coaster_stats.ride_submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can view all ride coaster stats"
|
|
ON ride_coaster_stats FOR SELECT
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "Users can insert stats for their own ride submissions"
|
|
ON ride_coaster_stats FOR INSERT
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM ride_submissions rs
|
|
JOIN content_submissions cs ON cs.id = rs.submission_id
|
|
WHERE rs.id = ride_coaster_stats.ride_submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can manage ride coaster stats"
|
|
ON ride_coaster_stats FOR ALL
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
-- RLS Policies for ride_former_names
|
|
CREATE POLICY "Users can view former names for their own ride submissions"
|
|
ON ride_former_names FOR SELECT
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM ride_submissions rs
|
|
JOIN content_submissions cs ON cs.id = rs.submission_id
|
|
WHERE rs.id = ride_former_names.ride_submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can view all ride former names"
|
|
ON ride_former_names FOR SELECT
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "Users can insert former names for their own ride submissions"
|
|
ON ride_former_names FOR INSERT
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM ride_submissions rs
|
|
JOIN content_submissions cs ON cs.id = rs.submission_id
|
|
WHERE rs.id = ride_former_names.ride_submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can manage ride former names"
|
|
ON ride_former_names FOR ALL
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
-- RLS Policies for submission_dependencies
|
|
CREATE POLICY "Users can view dependencies for their own submissions"
|
|
ON submission_dependencies FOR SELECT
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM content_submissions cs
|
|
WHERE (cs.id = submission_dependencies.parent_submission_id
|
|
OR cs.id = submission_dependencies.child_submission_id)
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can view all submission dependencies"
|
|
ON submission_dependencies FOR SELECT
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "Users can insert dependencies for their own submissions"
|
|
ON submission_dependencies FOR INSERT
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM content_submissions cs
|
|
WHERE cs.id = submission_dependencies.child_submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators can manage submission dependencies"
|
|
ON submission_dependencies FOR ALL
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
-- Add triggers for updated_at timestamps
|
|
CREATE TRIGGER update_park_submissions_updated_at
|
|
BEFORE UPDATE ON park_submissions
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_ride_submissions_updated_at
|
|
BEFORE UPDATE ON ride_submissions
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_company_submissions_updated_at
|
|
BEFORE UPDATE ON company_submissions
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_ride_model_submissions_updated_at
|
|
BEFORE UPDATE ON ride_model_submissions
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column(); |