supabase_setup.sql

-- ====================================================================
-- MapMyTree - Supabase Database Setup Script
-- Run this in your Supabase project's SQL Editor (https://supabase.com)
-- ====================================================================

-- 1. Create profiles table if it does not exist at all
CREATE TABLE IF NOT EXISTS public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email VARCHAR(255) NOT NULL
);

-- Ensure all required columns exist on the profiles table (for pre-existing tables)
ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS email VARCHAR(255);
ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS role VARCHAR(50) DEFAULT 'normal_user';
ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS full_name VARCHAR(255);
ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS phone_number VARCHAR(50);
ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS avatar_url TEXT;
ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS is_verified BOOLEAN DEFAULT TRUE;
ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT TRUE;
ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();

-- Drop pre-existing constraints that restrict roles so ngo_admin can be assigned
ALTER TABLE public.profiles DROP CONSTRAINT IF EXISTS profiles_role_check;
ALTER TABLE public.profiles DROP CONSTRAINT IF EXISTS role_check;

-- 2. Configure Row Level Security (RLS) policies
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Allow public read" ON public.profiles;
DROP POLICY IF EXISTS "Allow individual insert" ON public.profiles;
DROP POLICY IF EXISTS "Allow individual update" ON public.profiles;

CREATE POLICY "Allow public read" ON public.profiles
  FOR SELECT USING (true);

CREATE POLICY "Allow individual insert" ON public.profiles
  FOR INSERT WITH CHECK (auth.uid() = id);

CREATE POLICY "Allow individual update" ON public.profiles
  FOR UPDATE USING (auth.uid() = id);

-- 3. Create database trigger function to auto-create profile row on signup
CREATE TABLE IF NOT EXISTS public.pre_approved_emails (
  email VARCHAR(255) PRIMARY KEY,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable RLS on pre_approved_emails
ALTER TABLE public.pre_approved_emails ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Allow admin to select pre_approved_emails" ON public.pre_approved_emails
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.profiles
      WHERE id = auth.uid() AND role = 'ngo_admin'
    )
  );

CREATE POLICY "Allow admin to insert pre_approved_emails" ON public.pre_approved_emails
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.profiles
      WHERE id = auth.uid() AND role = 'ngo_admin'
    )
  );

CREATE POLICY "Allow admin to delete pre_approved_emails" ON public.pre_approved_emails
  FOR DELETE USING (
    EXISTS (
      SELECT 1 FROM public.profiles
      WHERE id = auth.uid() AND role = 'ngo_admin'
    )
  );

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
DECLARE
  default_role VARCHAR(50);
  is_pre_approved BOOLEAN;
BEGIN
  -- Auto-assign role to admin@gmail.com
  IF NEW.email = 'admin@gmail.com' THEN
    default_role := 'ngo_admin';
  ELSE
    -- Fallback to the role passed from Flutter signup metadata
    default_role := COALESCE(NEW.raw_user_meta_data->>'role', 'normal_user');
  END IF;

  -- Check if email is pre-approved for NGO volunteer
  IF default_role = 'ngo_volunteer' THEN
    SELECT EXISTS (
      SELECT 1 FROM public.pre_approved_emails
      WHERE LOWER(email) = LOWER(NEW.email)
    ) INTO is_pre_approved;
  ELSE
    is_pre_approved := false;
  END IF;

  INSERT INTO public.profiles (id, email, role, full_name, phone_number, is_verified, is_active)
  VALUES (
    NEW.id,
    NEW.email,
    default_role,
    COALESCE(NEW.raw_user_meta_data->>'name', NEW.raw_user_meta_data->>'full_name'),
    COALESCE(NEW.raw_user_meta_data->>'phone', NEW.raw_user_meta_data->>'contact_phone'),
    CASE 
      WHEN default_role = 'ngo_volunteer' THEN is_pre_approved
      ELSE true 
    END,
    true
  )
  ON CONFLICT (id) DO UPDATE
  SET 
    email = EXCLUDED.email,
    role = CASE WHEN EXCLUDED.email = 'admin@gmail.com' THEN 'ngo_admin' ELSE EXCLUDED.role END;

  -- Clean up pre-approved email if it was used
  IF is_pre_approved THEN
    DELETE FROM public.pre_approved_emails WHERE LOWER(email) = LOWER(NEW.email);
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 4. Re-bind the trigger to execute on auth.users inserts
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 PROCEDURE public.handle_new_user();

-- 5. Force update/insert for existing users (including admin@gmail.com) if already registered
INSERT INTO public.profiles (id, email, role, full_name, is_verified)
SELECT id, email, 'ngo_admin', 'NGO Admin', true
FROM auth.users
WHERE email = 'admin@gmail.com'
ON CONFLICT (id) DO UPDATE
SET role = 'ngo_admin', is_verified = true;

-- 6. Define secure client RPC function to delete own user account cleanly
-- Bypasses the need for database level foreign keys by cleaning up records first
CREATE OR REPLACE FUNCTION public.delete_current_user()
RETURNS VOID AS $$
DECLARE
  current_user_id UUID;
BEGIN
  current_user_id := auth.uid();
  
  -- Delete user's sponsorship requests
  DELETE FROM public.sponsorship_requests WHERE user_id = current_user_id;
  
  -- Nullify user's planted trees (handles text/uuid comparison safely)
  UPDATE public.trees SET planted_by = NULL WHERE planted_by = current_user_id::text;
  
  -- Delete user profile
  DELETE FROM public.profiles WHERE id = current_user_id;

  -- Delete user login
  DELETE FROM auth.users WHERE id = current_user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 7. Add RLS policy for ngo_admin to update profiles (approval/activation)
CREATE POLICY "Allow admins to update all profiles" ON public.profiles
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM public.profiles
      WHERE id = auth.uid() AND role = 'ngo_admin'
    )
  );

-- 8. Storage bucket setup
INSERT INTO storage.buckets (id, name, public)
VALUES ('tree-photos', 'tree-photos', true)
ON CONFLICT (id) DO NOTHING;

CREATE POLICY "Allow public read access to tree-photos" ON storage.objects
  FOR SELECT USING (bucket_id = 'tree-photos');

CREATE POLICY "Allow authenticated inserts to tree-photos" ON storage.objects
  FOR INSERT WITH CHECK (
    bucket_id = 'tree-photos' AND auth.role() = 'authenticated'
  );

CREATE POLICY "Allow authenticated updates to tree-photos" ON storage.objects
  FOR UPDATE USING (
    bucket_id = 'tree-photos' AND auth.role() = 'authenticated'
  );

CREATE POLICY "Allow authenticated deletes to tree-photos" ON storage.objects
  FOR DELETE USING (
    bucket_id = 'tree-photos' AND auth.role() = 'authenticated'
  );

-- 9. Add secure RPC function to update volunteer profiles (bypassing direct profile update RLS checks)
CREATE OR REPLACE FUNCTION public.admin_update_volunteer(volunteer_id UUID, verify BOOLEAN, active BOOLEAN)
RETURNS VOID AS $$
BEGIN
  -- Verify that the caller is indeed an ngo_admin
  IF EXISTS (
    SELECT 1 FROM public.profiles
    WHERE id = auth.uid() AND role = 'ngo_admin'
  ) THEN
    UPDATE public.profiles
    SET is_verified = verify, is_active = active
    WHERE id = volunteer_id;
  ELSE
    RAISE EXCEPTION 'Access Denied: Only NGO Admins can manage volunteer accounts.';
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;


-- ====================================================================
-- 10. RLS Policies for 'requests' table
-- Without these, NGO users cannot update request status (silently blocked)
-- ====================================================================

-- Enable RLS on requests table
ALTER TABLE public.requests ENABLE ROW LEVEL SECURITY;

-- Allow authenticated users to INSERT their own requests
DROP POLICY IF EXISTS "Allow users to insert own requests" ON public.requests;
CREATE POLICY "Allow users to insert own requests" ON public.requests
  FOR INSERT WITH CHECK (auth.uid() = user_id);

-- Allow users to read their own requests
DROP POLICY IF EXISTS "Allow users to read own requests" ON public.requests;
CREATE POLICY "Allow users to read own requests" ON public.requests
  FOR SELECT USING (auth.uid() = user_id);

-- Allow NGO admins and volunteers to read ALL requests
DROP POLICY IF EXISTS "Allow NGO to read all requests" ON public.requests;
CREATE POLICY "Allow NGO to read all requests" ON public.requests
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.profiles
      WHERE id = auth.uid() AND role IN ('ngo_admin', 'ngo_volunteer')
    )
  );

-- Allow NGO admins and volunteers to UPDATE any request (e.g., mark completed)
DROP POLICY IF EXISTS "Allow NGO to update requests" ON public.requests;
CREATE POLICY "Allow NGO to update requests" ON public.requests
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM public.profiles
      WHERE id = auth.uid() AND role IN ('ngo_admin', 'ngo_volunteer')
    )
  );

-- Allow users to update their OWN request (e.g., upload payment)
DROP POLICY IF EXISTS "Allow users to update own requests" ON public.requests;
CREATE POLICY "Allow users to update own requests" ON public.requests
  FOR UPDATE USING (auth.uid() = user_id);

-- ====================================================================
-- 11. RLS Policies for 'trees' table
-- ====================================================================

-- Enable RLS on trees table
ALTER TABLE public.trees ENABLE ROW LEVEL SECURITY;

-- Allow public read of all trees (for QR scan page etc)
DROP POLICY IF EXISTS "Allow public read of trees" ON public.trees;
CREATE POLICY "Allow public read of trees" ON public.trees
  FOR SELECT USING (true);

-- Allow NGO volunteers/admins to INSERT trees
DROP POLICY IF EXISTS "Allow NGO to insert trees" ON public.trees;
CREATE POLICY "Allow NGO to insert trees" ON public.trees
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.profiles
      WHERE id = auth.uid() AND role IN ('ngo_admin', 'ngo_volunteer')
    )
  );

-- Allow NGO volunteers/admins to UPDATE trees
DROP POLICY IF EXISTS "Allow NGO to update trees" ON public.trees;
CREATE POLICY "Allow NGO to update trees" ON public.trees
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM public.profiles
      WHERE id = auth.uid() AND role IN ('ngo_admin', 'ngo_volunteer')
    )
  );