supabase-schema.sql

-- ConnectCare Database Schema for Supabase
-- Run these SQL commands in your Supabase SQL Editor
-- Go to: https://app.supabase.com -> Your Project -> SQL Editor

-- ============================================================================
-- 1. PROFILES TABLE (extends auth.users)
-- ============================================================================
-- This table stores additional user profile information
CREATE TABLE IF NOT EXISTS public.profiles (
  id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  avatar_url TEXT,
  bio TEXT,
  location TEXT,
  phone TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable Row Level Security
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- Profiles policies (users can read all profiles, but only update their own)
CREATE POLICY "Public profiles are viewable by everyone"
  ON public.profiles FOR SELECT
  USING (true);

CREATE POLICY "Users can insert their own profile"
  ON public.profiles FOR INSERT
  WITH CHECK (auth.uid() = id);

CREATE POLICY "Users can update their own profile"
  ON public.profiles FOR UPDATE
  USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

-- Trigger to automatically create a profile when a user signs up
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, email, name)
  VALUES (
    NEW.id,
    NEW.email,
    COALESCE(NEW.raw_user_meta_data->>'name', 'User')
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create trigger
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- ============================================================================
-- 2. HELP REQUESTS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS public.help_requests (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  category TEXT NOT NULL CHECK (category IN (
    'companionship',
    'medical_assistance',
    'transportation',
    'meals',
    'household_tasks',
    'technology'
  )),
  urgency TEXT NOT NULL DEFAULT 'normal' CHECK (urgency IN ('normal', 'urgent', 'emergency')),
  status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'in_progress', 'completed', 'cancelled')),
  location TEXT NOT NULL,
  required_skills TEXT[] DEFAULT '{}',
  date_needed TEXT,
  matched_volunteer_id UUID REFERENCES public.profiles(id) ON DELETE SET NULL,
  completion_notes TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable Row Level Security
ALTER TABLE public.help_requests ENABLE ROW LEVEL SECURITY;

-- Help requests policies
CREATE POLICY "Help requests are viewable by everyone"
  ON public.help_requests FOR SELECT
  USING (true);

CREATE POLICY "Authenticated users can create help requests"
  ON public.help_requests FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update their own help requests"
  ON public.help_requests FOR UPDATE
  USING (auth.uid() = user_id OR auth.uid() = matched_volunteer_id)
  WITH CHECK (auth.uid() = user_id OR auth.uid() = matched_volunteer_id);

CREATE POLICY "Users can delete their own help requests"
  ON public.help_requests FOR DELETE
  USING (auth.uid() = user_id);

-- Index for better query performance
CREATE INDEX IF NOT EXISTS help_requests_user_id_idx ON public.help_requests(user_id);
CREATE INDEX IF NOT EXISTS help_requests_status_idx ON public.help_requests(status);
CREATE INDEX IF NOT EXISTS help_requests_category_idx ON public.help_requests(category);
CREATE INDEX IF NOT EXISTS help_requests_created_at_idx ON public.help_requests(created_at DESC);

-- ============================================================================
-- 3. VOLUNTEERS TABLE (tracks offers to help)
-- ============================================================================
CREATE TABLE IF NOT EXISTS public.volunteers (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  help_request_id UUID REFERENCES public.help_requests(id) ON DELETE CASCADE NOT NULL,
  volunteer_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
  message TEXT,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'accepted', 'declined')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(help_request_id, volunteer_id)
);

-- Enable Row Level Security
ALTER TABLE public.volunteers ENABLE ROW LEVEL SECURITY;

-- Volunteers policies
CREATE POLICY "Volunteers are viewable by request owner and volunteer"
  ON public.volunteers FOR SELECT
  USING (
    auth.uid() = volunteer_id OR
    auth.uid() IN (
      SELECT user_id FROM public.help_requests WHERE id = help_request_id
    )
  );

CREATE POLICY "Authenticated users can offer to help"
  ON public.volunteers FOR INSERT
  WITH CHECK (auth.uid() = volunteer_id);

CREATE POLICY "Request owners can update volunteer status"
  ON public.volunteers FOR UPDATE
  USING (
    auth.uid() IN (
      SELECT user_id FROM public.help_requests WHERE id = help_request_id
    )
  );

-- Index for better query performance
CREATE INDEX IF NOT EXISTS volunteers_help_request_id_idx ON public.volunteers(help_request_id);
CREATE INDEX IF NOT EXISTS volunteers_volunteer_id_idx ON public.volunteers(volunteer_id);

-- ============================================================================
-- 4. UPDATED_AT TRIGGER FUNCTION
-- ============================================================================
-- Function to automatically update updated_at timestamp
CREATE OR REPLACE FUNCTION public.handle_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply triggers to all tables
DROP TRIGGER IF EXISTS handle_profiles_updated_at ON public.profiles;
CREATE TRIGGER handle_profiles_updated_at
  BEFORE UPDATE ON public.profiles
  FOR EACH ROW EXECUTE FUNCTION public.handle_updated_at();

DROP TRIGGER IF EXISTS handle_help_requests_updated_at ON public.help_requests;
CREATE TRIGGER handle_help_requests_updated_at
  BEFORE UPDATE ON public.help_requests
  FOR EACH ROW EXECUTE FUNCTION public.handle_updated_at();

DROP TRIGGER IF EXISTS handle_volunteers_updated_at ON public.volunteers;
CREATE TRIGGER handle_volunteers_updated_at
  BEFORE UPDATE ON public.volunteers
  FOR EACH ROW EXECUTE FUNCTION public.handle_updated_at();

-- ============================================================================
-- 5. SAMPLE DATA (Optional - for testing)
-- ============================================================================
-- Uncomment to insert sample data after you have at least one authenticated user

/*
-- Insert sample help requests (replace 'YOUR_USER_ID' with actual user ID from auth.users)
INSERT INTO public.help_requests (user_id, title, description, category, urgency, location, date_needed, required_skills, status)
VALUES
  ('YOUR_USER_ID', 'Help with grocery shopping', 'Need assistance getting groceries from the store. I have mobility issues and would appreciate someone who can help carry items.', 'household_tasks', 'normal', 'Downtown', 'Dec 15', ARRAY['Shopping', 'Driving', 'Lifting'], 'open'),
  ('YOUR_USER_ID', 'Ride to medical appointment', 'Looking for a ride to my doctor appointment next week. The clinic is about 15 minutes away.', 'transportation', 'urgent', 'Westside', 'Dec 18', ARRAY['Driving', 'Availability'], 'open'),
  ('YOUR_USER_ID', 'Tech support needed', 'Having trouble setting up my new smartphone. Need someone patient to help me learn the basics.', 'technology', 'normal', 'East End', 'Dec 20', ARRAY['Tech Savvy', 'Patient', 'Teaching'], 'open');
*/

-- ============================================================================
-- SETUP COMPLETE!
-- ============================================================================
-- Your database is now ready to use with ConnectCare.
--
-- Next steps:
-- 1. Copy your Supabase URL and Anon Key to your .env file
-- 2. Test authentication by signing up a new user
-- 3. Verify that the profile is automatically created
-- 4. Start creating help requests through the app