mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 07:11:12 -05:00
96 lines
2.7 KiB
PL/PgSQL
96 lines
2.7 KiB
PL/PgSQL
-- Create review_deletions table to track deleted reviews
|
|
CREATE TABLE IF NOT EXISTS public.review_deletions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
review_id UUID NOT NULL,
|
|
user_id UUID NOT NULL REFERENCES auth.users(id),
|
|
park_id UUID REFERENCES public.parks(id),
|
|
ride_id UUID REFERENCES public.rides(id),
|
|
rating INTEGER NOT NULL,
|
|
review_text TEXT,
|
|
deleted_by UUID REFERENCES auth.users(id),
|
|
deleted_at TIMESTAMPTZ DEFAULT now(),
|
|
deletion_reason TEXT,
|
|
was_moderated BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMPTZ NOT NULL,
|
|
CONSTRAINT review_deletions_entity_check CHECK (
|
|
(park_id IS NOT NULL AND ride_id IS NULL) OR
|
|
(park_id IS NULL AND ride_id IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
-- Add indexes for performance
|
|
CREATE INDEX idx_review_deletions_deleted_at ON public.review_deletions(deleted_at DESC);
|
|
CREATE INDEX idx_review_deletions_user_id ON public.review_deletions(user_id);
|
|
CREATE INDEX idx_review_deletions_deleted_by ON public.review_deletions(deleted_by);
|
|
|
|
-- Enable RLS
|
|
ALTER TABLE public.review_deletions ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- RLS Policies
|
|
CREATE POLICY "Moderators can view all review deletions"
|
|
ON public.review_deletions
|
|
FOR SELECT
|
|
TO authenticated
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "Users can view their own deleted reviews"
|
|
ON public.review_deletions
|
|
FOR SELECT
|
|
TO authenticated
|
|
USING (user_id = auth.uid());
|
|
|
|
CREATE POLICY "System can insert review deletions"
|
|
ON public.review_deletions
|
|
FOR INSERT
|
|
TO authenticated
|
|
WITH CHECK (true);
|
|
|
|
-- Create trigger function to log review deletions
|
|
CREATE OR REPLACE FUNCTION public.log_review_deletion()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
BEGIN
|
|
-- Insert into review_deletions table
|
|
INSERT INTO public.review_deletions (
|
|
review_id,
|
|
user_id,
|
|
park_id,
|
|
ride_id,
|
|
rating,
|
|
review_text,
|
|
deleted_by,
|
|
deletion_reason,
|
|
was_moderated,
|
|
created_at
|
|
) VALUES (
|
|
OLD.id,
|
|
OLD.user_id,
|
|
OLD.park_id,
|
|
OLD.ride_id,
|
|
OLD.rating,
|
|
OLD.review_text,
|
|
auth.uid(),
|
|
CASE
|
|
WHEN OLD.moderation_status = 'rejected' THEN 'Rejected by moderator'
|
|
ELSE 'Deleted by user or admin'
|
|
END,
|
|
OLD.moderation_status != 'approved',
|
|
OLD.created_at
|
|
);
|
|
|
|
RETURN OLD;
|
|
END;
|
|
$$;
|
|
|
|
-- Create trigger on reviews table
|
|
DROP TRIGGER IF EXISTS track_review_deletion ON public.reviews;
|
|
CREATE TRIGGER track_review_deletion
|
|
BEFORE DELETE ON public.reviews
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.log_review_deletion();
|
|
|
|
COMMENT ON TABLE public.review_deletions IS 'Tracks deleted reviews for audit purposes';
|
|
COMMENT ON FUNCTION public.log_review_deletion() IS 'Logs review deletions before they are removed from the database'; |