mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 04:51:11 -05:00
120 lines
3.9 KiB
PL/PgSQL
120 lines
3.9 KiB
PL/PgSQL
-- Create user preferences table for additional settings
|
|
CREATE TABLE public.user_preferences (
|
|
id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
email_notifications JSONB NOT NULL DEFAULT '{
|
|
"review_replies": true,
|
|
"new_followers": true,
|
|
"system_announcements": true,
|
|
"weekly_digest": false,
|
|
"monthly_digest": true
|
|
}'::jsonb,
|
|
push_notifications JSONB NOT NULL DEFAULT '{
|
|
"browser_enabled": false,
|
|
"new_content": true,
|
|
"social_updates": true
|
|
}'::jsonb,
|
|
privacy_settings JSONB NOT NULL DEFAULT '{
|
|
"activity_visibility": "public",
|
|
"search_visibility": true,
|
|
"show_location": false,
|
|
"show_age": false
|
|
}'::jsonb,
|
|
accessibility_options JSONB NOT NULL DEFAULT '{
|
|
"font_size": "medium",
|
|
"high_contrast": false,
|
|
"reduced_motion": false
|
|
}'::jsonb,
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
|
|
UNIQUE(user_id)
|
|
);
|
|
|
|
-- Enable RLS
|
|
ALTER TABLE public.user_preferences ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Create policies
|
|
CREATE POLICY "Users can manage their own preferences"
|
|
ON public.user_preferences
|
|
FOR ALL
|
|
USING (auth.uid() = user_id);
|
|
|
|
-- Create blocked users table
|
|
CREATE TABLE public.user_blocks (
|
|
id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
blocker_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
blocked_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
reason TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
|
|
UNIQUE(blocker_id, blocked_id)
|
|
);
|
|
|
|
-- Enable RLS on blocks table
|
|
ALTER TABLE public.user_blocks ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Create policies for blocks
|
|
CREATE POLICY "Users can manage their own blocks"
|
|
ON public.user_blocks
|
|
FOR ALL
|
|
USING (auth.uid() = blocker_id);
|
|
|
|
-- Create user sessions table for session management
|
|
CREATE TABLE public.user_sessions (
|
|
id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
session_token TEXT NOT NULL,
|
|
device_info JSONB DEFAULT '{}',
|
|
ip_address INET,
|
|
user_agent TEXT,
|
|
last_activity TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
|
|
expires_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT (now() + interval '30 days')
|
|
);
|
|
|
|
-- Enable RLS on sessions table
|
|
ALTER TABLE public.user_sessions ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Create policies for sessions
|
|
CREATE POLICY "Users can view their own sessions"
|
|
ON public.user_sessions
|
|
FOR SELECT
|
|
USING (auth.uid() = user_id);
|
|
|
|
-- Create function to initialize user preferences
|
|
CREATE OR REPLACE FUNCTION public.initialize_user_preferences()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
BEGIN
|
|
INSERT INTO public.user_preferences (user_id)
|
|
VALUES (NEW.user_id)
|
|
ON CONFLICT (user_id) DO NOTHING;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
-- Create trigger to initialize preferences when profile is created
|
|
CREATE TRIGGER initialize_user_preferences_trigger
|
|
AFTER INSERT ON public.profiles
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.initialize_user_preferences();
|
|
|
|
-- Add updated_at trigger for user_preferences
|
|
CREATE TRIGGER update_user_preferences_updated_at
|
|
BEFORE UPDATE ON public.user_preferences
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.update_updated_at_column();
|
|
|
|
-- Add some additional columns to profiles for settings
|
|
ALTER TABLE public.profiles
|
|
ADD COLUMN IF NOT EXISTS preferred_pronouns TEXT,
|
|
ADD COLUMN IF NOT EXISTS show_pronouns BOOLEAN DEFAULT false,
|
|
ADD COLUMN IF NOT EXISTS timezone TEXT DEFAULT 'UTC',
|
|
ADD COLUMN IF NOT EXISTS preferred_language TEXT DEFAULT 'en';
|
|
|
|
-- Create index for better performance
|
|
CREATE INDEX IF NOT EXISTS idx_user_preferences_user_id ON public.user_preferences(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_user_blocks_blocker_id ON public.user_blocks(blocker_id);
|
|
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_id ON public.user_sessions(user_id); |