-- Drop existing foreign keys that point to auth.users
ALTER TABLE public.content_submissions
DROP CONSTRAINT IF EXISTS content_submissions_user_id_fkey;

ALTER TABLE public.content_submissions
DROP CONSTRAINT IF EXISTS content_submissions_reviewer_id_fkey;

ALTER TABLE public.content_submissions
DROP CONSTRAINT IF EXISTS content_submissions_assigned_to_fkey;

ALTER TABLE public.content_submissions
DROP CONSTRAINT IF EXISTS content_submissions_escalated_by_fkey;

-- Create new foreign keys pointing to profiles table
ALTER TABLE public.content_submissions
ADD CONSTRAINT content_submissions_user_id_fkey 
FOREIGN KEY (user_id) 
REFERENCES public.profiles(user_id) 
ON DELETE CASCADE;

ALTER TABLE public.content_submissions
ADD CONSTRAINT content_submissions_reviewer_id_fkey 
FOREIGN KEY (reviewer_id) 
REFERENCES public.profiles(user_id) 
ON DELETE SET NULL;

ALTER TABLE public.content_submissions
ADD CONSTRAINT content_submissions_assigned_to_fkey 
FOREIGN KEY (assigned_to) 
REFERENCES public.profiles(user_id) 
ON DELETE SET NULL;

ALTER TABLE public.content_submissions
ADD CONSTRAINT content_submissions_escalated_by_fkey 
FOREIGN KEY (escalated_by) 
REFERENCES public.profiles(user_id) 
ON DELETE SET NULL;