supabase_schema.sql

-- Safety Guardian Supabase schema

-- 1. Profiles
CREATE TABLE profiles (
  id UUID REFERENCES auth.users NOT NULL PRIMARY KEY,
  nickname TEXT UNIQUE,
  level INTEGER DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, nickname)
  VALUES (new.id, new.raw_user_meta_data->>'nickname');
  RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();

-- 2. Resident reports
CREATE TABLE reports (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id UUID REFERENCES public.profiles(id) NOT NULL,
  category TEXT NOT NULL,
  description TEXT NOT NULL,
  latitude DOUBLE PRECISION NOT NULL,
  longitude DOUBLE PRECISION NOT NULL,
  image_url TEXT,
  status TEXT DEFAULT 'pending',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

-- 3. Community feed
CREATE TABLE safety_feed (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id UUID REFERENCES public.profiles(id) NOT NULL,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  location_name TEXT,
  danger_count INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

-- 4. Community likes
CREATE TABLE 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 community_post_likes_post_user_idx
  ON community_post_likes (post_id, user_id);

CREATE INDEX community_post_likes_post_idx
  ON community_post_likes (post_id);

-- 5. Community comments
CREATE TABLE 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 community_post_comments_post_idx
  ON community_post_comments (post_id, created_at);

-- 6. Safe return profiles
CREATE TABLE safe_return_profiles (
  user_id UUID REFERENCES public.profiles(id) PRIMARY KEY,
  destination_name TEXT NOT NULL,
  destination_address TEXT NOT NULL,
  alert_after_minutes INTEGER DEFAULT 30,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

-- 7. Safe return contacts
CREATE TABLE safe_return_contacts (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id UUID REFERENCES public.profiles(id) NOT NULL,
  name TEXT NOT NULL,
  phone TEXT NOT NULL,
  priority INTEGER DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

-- 8. Safe return sessions
CREATE TABLE safe_return_sessions (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id UUID REFERENCES public.profiles(id) NOT NULL,
  destination_name TEXT NOT NULL,
  destination_address TEXT NOT NULL,
  status TEXT DEFAULT 'active',
  alert_after_minutes INTEGER DEFAULT 30,
  started_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
  ended_at TIMESTAMP WITH TIME ZONE,
  last_latitude DOUBLE PRECISION,
  last_longitude DOUBLE PRECISION,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE reports ENABLE ROW LEVEL SECURITY;
ALTER TABLE safety_feed ENABLE ROW LEVEL SECURITY;
ALTER TABLE community_post_likes ENABLE ROW LEVEL SECURITY;
ALTER TABLE community_post_comments ENABLE ROW LEVEL SECURITY;
ALTER TABLE safe_return_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE safe_return_contacts ENABLE ROW LEVEL SECURITY;
ALTER TABLE safe_return_sessions ENABLE ROW LEVEL SECURITY;

-- Profiles policies
CREATE POLICY "Public profiles are viewable by everyone." ON profiles FOR SELECT USING (true);
CREATE POLICY "Users can insert their own profile." ON profiles FOR INSERT WITH CHECK (auth.uid() = id);
CREATE POLICY "Users can update own profile." ON profiles FOR UPDATE USING (auth.uid() = id);

-- Reports policies
CREATE POLICY "Reports viewable by everyone" ON reports FOR SELECT USING (true);
CREATE POLICY "Authenticated users can insert reports" ON reports FOR INSERT WITH CHECK (auth.role() = 'authenticated');

-- Community feed policies
CREATE POLICY "Feeds viewable by everyone" ON safety_feed FOR SELECT USING (true);
CREATE POLICY "Authenticated users can insert feeds" ON safety_feed FOR INSERT WITH CHECK (auth.role() = 'authenticated');

-- Community likes policies
CREATE POLICY "Community likes are viewable by everyone" ON community_post_likes FOR SELECT USING (true);
CREATE POLICY "Users can insert own community likes" ON community_post_likes FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can delete own community likes" ON community_post_likes FOR DELETE USING (auth.uid() = user_id);

-- Community comments policies
CREATE POLICY "Community comments are viewable by everyone" ON community_post_comments FOR SELECT USING (true);
CREATE POLICY "Users can insert own community comments" ON community_post_comments FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can delete own community comments" ON community_post_comments FOR DELETE USING (auth.uid() = user_id);

-- Safe return profile policies
CREATE POLICY "Users can view own safe return profile" ON safe_return_profiles FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can upsert own safe return profile" ON safe_return_profiles FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update own safe return profile" ON safe_return_profiles FOR UPDATE USING (auth.uid() = user_id);

-- Safe return contacts policies
CREATE POLICY "Users can view own safe return contacts" ON safe_return_contacts FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own safe return contacts" ON safe_return_contacts FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update own safe return contacts" ON safe_return_contacts FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete own safe return contacts" ON safe_return_contacts FOR DELETE USING (auth.uid() = user_id);

-- Safe return session policies
CREATE POLICY "Users can view own safe return sessions" ON safe_return_sessions FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own safe return sessions" ON safe_return_sessions FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update own safe return sessions" ON safe_return_sessions FOR UPDATE USING (auth.uid() = user_id);