59 lines
1.7 KiB
PL/PgSQL
59 lines
1.7 KiB
PL/PgSQL
-- Profiller tablosunu oluştur
|
||
CREATE TABLE IF NOT EXISTS profiles (
|
||
id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
|
||
username TEXT UNIQUE NOT NULL,
|
||
full_name TEXT,
|
||
avatar_url TEXT,
|
||
bio TEXT,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
-- RLS politikalarını etkinleştir
|
||
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
|
||
|
||
-- Herkes kendi profilini okuyabilir
|
||
CREATE POLICY "Kullanıcılar kendi profillerini görebilir"
|
||
ON profiles FOR SELECT
|
||
USING (auth.uid() = id);
|
||
|
||
-- Herkes kendi profilini güncelleyebilir
|
||
CREATE POLICY "Kullanıcılar kendi profillerini güncelleyebilir"
|
||
ON profiles FOR UPDATE
|
||
USING (auth.uid() = id);
|
||
|
||
-- Yeni kullanıcı kaydında otomatik profil oluştur
|
||
CREATE OR REPLACE FUNCTION public.handle_new_user()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
INSERT INTO public.profiles (id, username, full_name)
|
||
VALUES (
|
||
NEW.id,
|
||
SPLIT_PART(NEW.email, '@', 1),
|
||
COALESCE(NEW.raw_user_meta_data->>'full_name', SPLIT_PART(NEW.email, '@', 1))
|
||
);
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- Trigger oluştur
|
||
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();
|
||
|
||
-- Updated_at otomatik güncelleme fonksiyonu
|
||
CREATE OR REPLACE FUNCTION public.handle_updated_at()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = NOW();
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- Updated_at trigger'ı
|
||
DROP TRIGGER IF EXISTS on_profile_updated ON profiles;
|
||
CREATE TRIGGER on_profile_updated
|
||
BEFORE UPDATE ON profiles
|
||
FOR EACH ROW EXECUTE FUNCTION public.handle_updated_at();
|