112 lines
3.4 KiB
PL/PgSQL
112 lines
3.4 KiB
PL/PgSQL
-- Profiles table
|
|
CREATE TYPE public.user_role AS ENUM ('user', 'admin');
|
|
|
|
CREATE TABLE public.profiles (
|
|
id uuid REFERENCES auth.users(id) PRIMARY KEY,
|
|
email text,
|
|
role public.user_role DEFAULT 'user'::public.user_role,
|
|
created_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL
|
|
);
|
|
|
|
-- Trips table
|
|
CREATE TABLE public.trips (
|
|
id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
user_id uuid REFERENCES public.profiles(id) ON DELETE CASCADE,
|
|
title text NOT NULL,
|
|
destination text DEFAULT 'Cappadocia',
|
|
start_date date,
|
|
end_date date,
|
|
preferences jsonb,
|
|
itinerary jsonb, -- Stores the final verified itinerary structure
|
|
created_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL
|
|
);
|
|
|
|
-- Enable RLS
|
|
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.trips ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Profiles policies
|
|
CREATE OR REPLACE FUNCTION is_admin(uid uuid)
|
|
RETURNS boolean LANGUAGE sql SECURITY DEFINER AS $$
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM public.profiles p
|
|
WHERE p.id = uid AND p.role = 'admin'::public.user_role
|
|
);
|
|
$$;
|
|
|
|
CREATE POLICY "Admins have full access to profiles" ON public.profiles
|
|
FOR ALL TO authenticated USING (is_admin(auth.uid()));
|
|
|
|
CREATE POLICY "Users can view their own profile" ON public.profiles
|
|
FOR SELECT TO authenticated USING (auth.uid() = id);
|
|
|
|
CREATE POLICY "Users can update their own profile" ON public.profiles
|
|
FOR UPDATE TO authenticated USING (auth.uid() = id)
|
|
WITH CHECK (role IS NOT DISTINCT FROM (SELECT role FROM public.profiles WHERE id = auth.uid()));
|
|
|
|
-- Trips policies
|
|
CREATE POLICY "Users can view their own trips" ON public.trips
|
|
FOR SELECT TO authenticated USING (auth.uid() = user_id);
|
|
|
|
CREATE POLICY "Users can insert their own trips" ON public.trips
|
|
FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
|
|
|
|
CREATE POLICY "Users can update their own trips" ON public.trips
|
|
FOR UPDATE TO authenticated USING (auth.uid() = user_id);
|
|
|
|
CREATE POLICY "Users can delete their own trips" ON public.trips
|
|
FOR DELETE TO authenticated USING (auth.uid() = user_id);
|
|
|
|
-- Auth Sync Trigger
|
|
CREATE OR REPLACE FUNCTION handle_new_user()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER SET search_path = public
|
|
AS $$
|
|
DECLARE
|
|
user_count int;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO user_count FROM profiles;
|
|
INSERT INTO public.profiles (id, email, role)
|
|
VALUES (
|
|
NEW.id,
|
|
NEW.email,
|
|
CASE WHEN user_count = 0 THEN 'admin'::public.user_role ELSE 'user'::public.user_role END
|
|
);
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
DROP TRIGGER IF EXISTS on_auth_user_confirmed ON auth.users;
|
|
CREATE TRIGGER on_auth_user_confirmed
|
|
AFTER UPDATE ON auth.users
|
|
FOR EACH ROW
|
|
WHEN (OLD.confirmed_at IS NULL AND NEW.confirmed_at IS NOT NULL)
|
|
EXECUTE FUNCTION handle_new_user();
|
|
|
|
-- Also for direct registration if verification is disabled
|
|
CREATE OR REPLACE FUNCTION handle_new_user_direct()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER SET search_path = public
|
|
AS $$
|
|
DECLARE
|
|
user_count int;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO user_count FROM profiles;
|
|
INSERT INTO public.profiles (id, email, role)
|
|
VALUES (
|
|
NEW.id,
|
|
NEW.email,
|
|
CASE WHEN user_count = 0 THEN 'admin'::public.user_role ELSE 'user'::public.user_role END
|
|
) ON CONFLICT (id) DO NOTHING;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
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_direct();
|