-- Tours table for tour/guide inventory
CREATE TABLE IF NOT EXISTS tours (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  destination TEXT NOT NULL,
  name TEXT NOT NULL,
  type TEXT NOT NULL CHECK (type IN ('guided_tour', 'hot_air_balloon', 'atv', 'horse_riding', 'museum_tour', 'adventure', 'cultural', 'nature')),
  covers TEXT[] NOT NULL DEFAULT '{}',
  description TEXT,
  duration_hours NUMERIC NOT NULL,
  price_from NUMERIC NOT NULL,
  price_currency TEXT NOT NULL DEFAULT 'TRY',
  commission_rate NUMERIC NOT NULL DEFAULT 0.15,
  provider_id UUID REFERENCES profiles(id) ON DELETE SET NULL,
  image_url TEXT,
  includes TEXT[] DEFAULT '{}',
  excludes TEXT[] DEFAULT '{}',
  meeting_point TEXT,
  max_participants INTEGER,
  min_participants INTEGER DEFAULT 1,
  available_days TEXT[] DEFAULT '{}',
  rating NUMERIC DEFAULT 0,
  review_count INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Tour recommendations tracking
CREATE TABLE IF NOT EXISTS tour_recommendations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  trip_id UUID NOT NULL REFERENCES trips(id) ON DELETE CASCADE,
  trip_day_id UUID REFERENCES trip_days(id) ON DELETE CASCADE,
  recommended_type TEXT NOT NULL,
  reason TEXT NOT NULL,
  ideal_duration TEXT,
  best_time TEXT,
  confidence NUMERIC NOT NULL,
  why_better_than_self TEXT[] DEFAULT '{}',
  shown_at TIMESTAMPTZ DEFAULT now(),
  clicked BOOLEAN DEFAULT false,
  clicked_at TIMESTAMPTZ,
  tour_selected_id UUID REFERENCES tours(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_tours_destination ON tours(destination);
CREATE INDEX IF NOT EXISTS idx_tours_type ON tours(type);
CREATE INDEX IF NOT EXISTS idx_tours_is_active ON tours(is_active);
CREATE INDEX IF NOT EXISTS idx_tour_recommendations_trip_id ON tour_recommendations(trip_id);
CREATE INDEX IF NOT EXISTS idx_tour_recommendations_trip_day_id ON tour_recommendations(trip_day_id);

-- RLS Policies
ALTER TABLE tours ENABLE ROW LEVEL SECURITY;
ALTER TABLE tour_recommendations ENABLE ROW LEVEL SECURITY;

-- Tours: Public read for active tours
CREATE POLICY "Anyone can view active tours"
  ON tours FOR SELECT
  USING (is_active = true);

-- Tours: Providers can manage their own tours
CREATE POLICY "Providers can insert their own tours"
  ON tours FOR INSERT
  WITH CHECK (auth.uid() = provider_id);

CREATE POLICY "Providers can update their own tours"
  ON tours FOR UPDATE
  USING (auth.uid() = provider_id);

CREATE POLICY "Providers can delete their own tours"
  ON tours FOR DELETE
  USING (auth.uid() = provider_id);

-- Tour recommendations: Users can view their own recommendations
CREATE POLICY "Users can view their own tour recommendations"
  ON tour_recommendations FOR SELECT
  USING (
    trip_id IN (
      SELECT id FROM trips WHERE user_id = auth.uid()
    )
  );

-- Tour recommendations: Anyone can insert (for anonymous trips)
CREATE POLICY "Anyone can insert tour recommendations"
  ON tour_recommendations FOR INSERT
  WITH CHECK (true);

-- Tour recommendations: Users can update their own recommendations
CREATE POLICY "Users can update their own tour recommendations"
  ON tour_recommendations FOR UPDATE
  USING (
    trip_id IN (
      SELECT id FROM trips WHERE user_id = auth.uid()
    )
  );

-- Insert sample tours for Kapadokya
INSERT INTO tours (destination, name, type, covers, description, duration_hours, price_from, image_url, includes, excludes, meeting_point, max_participants, rating, review_count) VALUES
('Kapadokya', 'Kapadokya Klasik Tur', 'guided_tour', ARRAY['museum', 'historical', 'nature'], 'Göreme Açık Hava Müzesi, Paşabağları, Devrent Vadisi ve Avanos''u kapsayan tam gün turu', 7, 450, 'https://images.unsplash.com/photo-1541432901042-2d8bd64b4a9b?w=800', ARRAY['Rehber', 'Öğle yemeği', 'Müze giriş ücretleri', 'Otel transfer'], ARRAY['İçecekler', 'Kişisel harcamalar'], 'Otel lobisi', 15, 4.8, 234),
('Kapadokya', 'Sıcak Hava Balonu Turu', 'hot_air_balloon', ARRAY['nature', 'adventure'], 'Kapadokya''nın eşsiz manzarasını havadan izleyin. Gün doğumu uçuşu.', 3, 2500, 'https://images.unsplash.com/photo-1526772662000-3f88f10405ff?w=800', ARRAY['Balon uçuşu (1 saat)', 'Kahvaltı', 'Uçuş sertifikası', 'Otel transfer'], ARRAY['Fotoğraf çekimi', 'Sigorta'], 'Otel lobisi (04:30)', 20, 4.9, 567),
('Kapadokya', 'ATV Safari Turu', 'atv', ARRAY['adventure', 'nature'], 'Kapadokya vadilerinde ATV ile macera dolu 2 saatlik tur', 2, 350, 'https://images.unsplash.com/photo-1558618666-fcd25c85cd64?w=800', ARRAY['ATV kiralama', 'Rehber', 'Kask ve ekipman', 'Sigorta'], ARRAY['Otel transfer', 'Fotoğraf'], 'Göreme Merkez', 10, 4.7, 189),
('Kapadokya', 'At Safari Turu', 'horse_riding', ARRAY['nature', 'adventure'], 'Kapadokya vadilerinde at sırtında romantik gün batımı turu', 2, 400, 'https://images.unsplash.com/photo-1553284965-83fd3e82fa5a?w=800', ARRAY['At kiralama', 'Rehber', 'Ekipman', 'Sigorta'], ARRAY['Otel transfer', 'Yiyecek-içecek'], 'Göreme Merkez', 8, 4.6, 145),
('Kapadokya', 'Yeraltı Şehri ve Ihlara Vadisi Turu', 'guided_tour', ARRAY['historical', 'nature', 'cultural'], 'Derinkuyu Yeraltı Şehri ve Ihlara Vadisi yürüyüşü içeren tam gün turu', 8, 500, 'https://images.unsplash.com/photo-1609137144813-7d9921338f24?w=800', ARRAY['Rehber', 'Öğle yemeği', 'Giriş ücretleri', 'Otel transfer'], ARRAY['İçecekler'], 'Otel lobisi', 15, 4.7, 198),
('İstanbul', 'Boğaz Turu', 'guided_tour', ARRAY['nature', 'cultural', 'historical'], 'Boğaz''ın iki yakasını keşfedin. Tarihi yapılar ve manzara eşliğinde tekne turu.', 3, 300, 'https://images.unsplash.com/photo-1524231757912-21f4fe3a7200?w=800', ARRAY['Tekne turu', 'Rehber', 'Çay-kahve'], ARRAY['Yemek', 'Otel transfer'], 'Eminönü İskelesi', 50, 4.5, 423),
('İstanbul', 'Sultanahmet Klasik Tur', 'guided_tour', ARRAY['museum', 'historical', 'cultural'], 'Ayasofya, Topkapı Sarayı, Sultanahmet Camii ve Kapalıçarşı''yı kapsayan tam gün turu', 7, 550, 'https://images.unsplash.com/photo-1527838832700-5059252407fa?w=800', ARRAY['Rehber', 'Öğle yemeği', 'Müze giriş ücretleri'], ARRAY['İçecekler', 'Otel transfer'], 'Sultanahmet Meydanı', 20, 4.8, 678);

COMMENT ON TABLE tours IS 'Tour and guide inventory for revenue generation';
COMMENT ON TABLE tour_recommendations IS 'Tracks AI-generated tour recommendations and user interactions';