109 lines
6.4 KiB
SQL
109 lines
6.4 KiB
SQL
-- 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'; |