community_interactions_supabase.sql

-- Community interactions upgrade for Safety Guardian / WayGuard
-- Run this in Supabase SQL editor for existing projects.

CREATE TABLE IF NOT EXISTS public.community_post_likes (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  post_id BIGINT REFERENCES public.safety_feed(id) ON DELETE CASCADE NOT NULL,
  user_id UUID REFERENCES public.profiles(id) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS community_post_likes_post_user_idx
  ON public.community_post_likes (post_id, user_id);

CREATE INDEX IF NOT EXISTS community_post_likes_post_idx
  ON public.community_post_likes (post_id);

CREATE TABLE IF NOT EXISTS public.community_post_comments (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  post_id BIGINT REFERENCES public.safety_feed(id) ON DELETE CASCADE NOT NULL,
  user_id UUID REFERENCES public.profiles(id) NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

CREATE INDEX IF NOT EXISTS community_post_comments_post_idx
  ON public.community_post_comments (post_id, created_at);

ALTER TABLE public.community_post_likes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.community_post_comments ENABLE ROW LEVEL SECURITY;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_policies
    WHERE schemaname = 'public'
      AND tablename = 'community_post_likes'
      AND policyname = 'Community likes are viewable by everyone'
  ) THEN
    EXECUTE 'CREATE POLICY "Community likes are viewable by everyone" ON public.community_post_likes FOR SELECT USING (true)';
  END IF;
END
$$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_policies
    WHERE schemaname = 'public'
      AND tablename = 'community_post_likes'
      AND policyname = 'Users can insert own community likes'
  ) THEN
    EXECUTE 'CREATE POLICY "Users can insert own community likes" ON public.community_post_likes FOR INSERT WITH CHECK (auth.uid() = user_id)';
  END IF;
END
$$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_policies
    WHERE schemaname = 'public'
      AND tablename = 'community_post_likes'
      AND policyname = 'Users can delete own community likes'
  ) THEN
    EXECUTE 'CREATE POLICY "Users can delete own community likes" ON public.community_post_likes FOR DELETE USING (auth.uid() = user_id)';
  END IF;
END
$$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_policies
    WHERE schemaname = 'public'
      AND tablename = 'community_post_comments'
      AND policyname = 'Community comments are viewable by everyone'
  ) THEN
    EXECUTE 'CREATE POLICY "Community comments are viewable by everyone" ON public.community_post_comments FOR SELECT USING (true)';
  END IF;
END
$$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_policies
    WHERE schemaname = 'public'
      AND tablename = 'community_post_comments'
      AND policyname = 'Users can insert own community comments'
  ) THEN
    EXECUTE 'CREATE POLICY "Users can insert own community comments" ON public.community_post_comments FOR INSERT WITH CHECK (auth.uid() = user_id)';
  END IF;
END
$$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_policies
    WHERE schemaname = 'public'
      AND tablename = 'community_post_comments'
      AND policyname = 'Users can delete own community comments'
  ) THEN
    EXECUTE 'CREATE POLICY "Users can delete own community comments" ON public.community_post_comments FOR DELETE USING (auth.uid() = user_id)';
  END IF;
END
$$;