-- Voluntier v3 schema — run once CREATE TABLE IF NOT EXISTS profiles ( id UUID REFERENCES auth.users PRIMARY KEY, full_name TEXT, email TEXT, role TEXT DEFAULT 'volunteer', location TEXT, skills TEXT, points INTEGER DEFAULT 0, causes_completed INTEGER DEFAULT 0, hours_logged INTEGER DEFAULT 0, streak_days INTEGER DEFAULT 0, tier TEXT DEFAULT 'bronze', skill_points TEXT DEFAULT '{}', avatar_color TEXT DEFAULT '#1B4332', created_at TIMESTAMPTZ DEFAULT NOW() ); CREATE TABLE IF NOT EXISTS organisations ( id UUID DEFAULT gen_random_uuid() PRIMARY KEY, name TEXT NOT NULL, cause_area TEXT, charity_number TEXT, contact_email TEXT, icon TEXT DEFAULT '🏛️', color TEXT DEFAULT '#F0FAF2', verified BOOLEAN DEFAULT FALSE, plan TEXT DEFAULT 'free', volunteers_needed INTEGER DEFAULT 0, created_at TIMESTAMPTZ DEFAULT NOW() ); CREATE TABLE IF NOT EXISTS causes ( id UUID DEFAULT gen_random_uuid() PRIMARY KEY, org_id UUID REFERENCES organisations(id), created_by UUID REFERENCES profiles(id), title TEXT NOT NULL, description TEXT, category TEXT, points_reward INTEGER DEFAULT 100, slots_total INTEGER DEFAULT 10, slots_filled INTEGER DEFAULT 0, urgency TEXT DEFAULT 'open', icon TEXT DEFAULT '🌱', color TEXT DEFAULT '#D1FAE5', event_date DATE, location TEXT, donation_total NUMERIC DEFAULT 0, created_at TIMESTAMPTZ DEFAULT NOW() ); CREATE TABLE IF NOT EXISTS applications ( id UUID DEFAULT gen_random_uuid() PRIMARY KEY, applicant_id UUID REFERENCES profiles(id), applicant_type TEXT DEFAULT 'volunteer', cause_id UUID REFERENCES causes(id), status TEXT DEFAULT 'pending', motivation TEXT, applied_at TIMESTAMPTZ DEFAULT NOW(), UNIQUE(applicant_id, cause_id) ); CREATE TABLE IF NOT EXISTS donations ( id UUID DEFAULT gen_random_uuid() PRIMARY KEY, donor_id UUID REFERENCES profiles(id), donor_name TEXT, donor_type TEXT DEFAULT 'volunteer', cause_id UUID REFERENCES causes(id), amount NUMERIC(10,2) NOT NULL, message TEXT, donated_at TIMESTAMPTZ DEFAULT NOW() ); ALTER TABLE profiles ENABLE ROW LEVEL SECURITY; ALTER TABLE organisations ENABLE ROW LEVEL SECURITY; ALTER TABLE causes ENABLE ROW LEVEL SECURITY; ALTER TABLE applications ENABLE ROW LEVEL SECURITY; ALTER TABLE donations ENABLE ROW LEVEL SECURITY; DROP POLICY IF EXISTS "Public read profiles" ON profiles; DROP POLICY IF EXISTS "Users manage own profile" ON profiles; DROP POLICY IF EXISTS "Public read orgs" ON organisations; DROP POLICY IF EXISTS "Public read causes" ON causes; DROP POLICY IF EXISTS "Users insert causes" ON causes; DROP POLICY IF EXISTS "Users manage own apps" ON applications; DROP POLICY IF EXISTS "Public read apps" ON applications; DROP POLICY IF EXISTS "Users insert donations" ON donations; DROP POLICY IF EXISTS "Public read donations" ON donations; CREATE POLICY "Public read profiles" ON profiles FOR SELECT USING (true); CREATE POLICY "Users manage own profile" ON profiles FOR ALL USING (auth.uid() = id); CREATE POLICY "Public read orgs" ON organisations FOR SELECT USING (true); CREATE POLICY "Public read causes" ON causes FOR SELECT USING (true); CREATE POLICY "Users insert causes" ON causes FOR INSERT WITH CHECK (auth.uid() = created_by); CREATE POLICY "Users manage own apps" ON applications FOR ALL USING (auth.uid() = applicant_id); CREATE POLICY "Public read apps" ON applications FOR SELECT USING (true); CREATE POLICY "Users insert donations" ON donations FOR INSERT WITH CHECK (auth.uid() = donor_id); CREATE POLICY "Public read donations" ON donations FOR SELECT USING (true); CREATE OR REPLACE FUNCTION handle_new_user() RETURNS TRIGGER AS $$ BEGIN INSERT INTO profiles (id, full_name, email) VALUES (NEW.id, NEW.raw_user_meta_data->>'full_name', NEW.email) ON CONFLICT (id) DO NOTHING; RETURN NEW; EXCEPTION WHEN OTHERS THEN RETURN NEW; END; $$ LANGUAGE plpgsql SECURITY DEFINER; 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 handle_new_user(); -- Backfill existing users INSERT INTO profiles (id, full_name, email) SELECT id, raw_user_meta_data->>'full_name', email FROM auth.users ON CONFLICT (id) DO NOTHING;