-- İş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;
