96 lines
3.2 KiB
SQL
96 lines
3.2 KiB
SQL
-- İşletme hesapları tablosu
|
||
CREATE TABLE IF NOT EXISTS businesses (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
|
||
name TEXT NOT NULL,
|
||
description TEXT,
|
||
category TEXT,
|
||
phone TEXT,
|
||
email TEXT,
|
||
website TEXT,
|
||
logo_url TEXT,
|
||
verified BOOLEAN DEFAULT false,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
UNIQUE(user_id)
|
||
);
|
||
|
||
-- İşletme-yer ilişkisi tablosu
|
||
CREATE TABLE IF NOT EXISTS business_places (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
business_id UUID REFERENCES businesses(id) ON DELETE CASCADE,
|
||
place_id UUID REFERENCES places(id) ON DELETE CASCADE,
|
||
claimed_at TIMESTAMPTZ DEFAULT NOW(),
|
||
status TEXT DEFAULT 'pending', -- pending, approved, rejected
|
||
UNIQUE(business_id, place_id)
|
||
);
|
||
|
||
-- Places tablosuna business_id ekle
|
||
ALTER TABLE places ADD COLUMN IF NOT EXISTS business_id UUID REFERENCES businesses(id) ON DELETE SET NULL;
|
||
|
||
-- RLS politikaları - Businesses
|
||
ALTER TABLE businesses ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Herkes işletmeleri görebilir"
|
||
ON businesses FOR SELECT
|
||
USING (true);
|
||
|
||
CREATE POLICY "Kullanıcılar kendi işletmelerini oluşturabilir"
|
||
ON businesses FOR INSERT
|
||
WITH CHECK (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Kullanıcılar kendi işletmelerini güncelleyebilir"
|
||
ON businesses FOR UPDATE
|
||
USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Kullanıcılar kendi işletmelerini silebilir"
|
||
ON businesses FOR DELETE
|
||
USING (auth.uid() = user_id);
|
||
|
||
-- RLS politikaları - Business Places
|
||
ALTER TABLE business_places ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Herkes onaylanmış talepleri görebilir"
|
||
ON business_places FOR SELECT
|
||
USING (status = 'approved' OR EXISTS (
|
||
SELECT 1 FROM businesses WHERE businesses.id = business_places.business_id AND businesses.user_id = auth.uid()
|
||
));
|
||
|
||
CREATE POLICY "İşletme sahipleri talep oluşturabilir"
|
||
ON business_places FOR INSERT
|
||
WITH CHECK (EXISTS (
|
||
SELECT 1 FROM businesses WHERE businesses.id = business_places.business_id AND businesses.user_id = auth.uid()
|
||
));
|
||
|
||
CREATE POLICY "İşletme sahipleri kendi taleplerini güncelleyebilir"
|
||
ON business_places FOR UPDATE
|
||
USING (EXISTS (
|
||
SELECT 1 FROM businesses WHERE businesses.id = business_places.business_id AND businesses.user_id = auth.uid()
|
||
));
|
||
|
||
-- Adminler tüm talepleri görebilir ve güncelleyebilir
|
||
CREATE POLICY "Adminler tüm talepleri yönetebilir"
|
||
ON business_places FOR ALL
|
||
USING (EXISTS (
|
||
SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin'
|
||
));
|
||
|
||
-- Updated_at trigger
|
||
CREATE TRIGGER on_business_updated
|
||
BEFORE UPDATE ON businesses
|
||
FOR EACH ROW EXECUTE FUNCTION public.handle_updated_at();
|
||
|
||
-- İşletme istatistikleri için view
|
||
CREATE OR REPLACE VIEW business_stats AS
|
||
SELECT
|
||
b.id as business_id,
|
||
b.name,
|
||
COUNT(DISTINCT bp.place_id) as total_places,
|
||
COUNT(DISTINCT CASE WHEN bp.status = 'approved' THEN bp.place_id END) as approved_places,
|
||
COUNT(DISTINCT bm.id) as total_bookmarks
|
||
FROM businesses b
|
||
LEFT JOIN business_places bp ON b.id = bp.business_id
|
||
LEFT JOIN places p ON bp.place_id = p.id
|
||
LEFT JOIN bookmarks bm ON p.id = bm.place_id
|
||
GROUP BY b.id, b.name;
|