59 lines
4.1 KiB
SQL
59 lines
4.1 KiB
SQL
-- Create daily_tours table for predefined tour templates
|
||
CREATE TABLE IF NOT EXISTS daily_tours (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
slug TEXT UNIQUE NOT NULL,
|
||
title TEXT NOT NULL,
|
||
region TEXT NOT NULL,
|
||
duration_hours NUMERIC NOT NULL,
|
||
includes_types TEXT[] NOT NULL DEFAULT '{}',
|
||
min_places INTEGER NOT NULL DEFAULT 3,
|
||
max_places INTEGER NOT NULL DEFAULT 6,
|
||
suitable_for TEXT[] NOT NULL DEFAULT '{}',
|
||
base_price_range TEXT,
|
||
description TEXT,
|
||
highlights TEXT[] DEFAULT '{}',
|
||
is_active BOOLEAN DEFAULT true,
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now()
|
||
);
|
||
|
||
-- Add indexes
|
||
CREATE INDEX IF NOT EXISTS idx_daily_tours_region ON daily_tours(region);
|
||
CREATE INDEX IF NOT EXISTS idx_daily_tours_slug ON daily_tours(slug);
|
||
CREATE INDEX IF NOT EXISTS idx_daily_tours_is_active ON daily_tours(is_active);
|
||
|
||
-- RLS Policies
|
||
ALTER TABLE daily_tours ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Anyone can view active daily tours"
|
||
ON daily_tours FOR SELECT
|
||
USING (is_active = true);
|
||
|
||
-- Extend provider_services table with additional fields
|
||
ALTER TABLE provider_services
|
||
ADD COLUMN IF NOT EXISTS daily_tour_services TEXT[] DEFAULT '{}',
|
||
ADD COLUMN IF NOT EXISTS vehicle_types TEXT[] DEFAULT '{}',
|
||
ADD COLUMN IF NOT EXISTS languages TEXT[] DEFAULT '{}',
|
||
ADD COLUMN IF NOT EXISTS rating NUMERIC DEFAULT 0,
|
||
ADD COLUMN IF NOT EXISTS lead_price INTEGER DEFAULT 20;
|
||
|
||
-- Add daily_tour_slug to tour_recommendations
|
||
ALTER TABLE tour_recommendations
|
||
ADD COLUMN IF NOT EXISTS daily_tour_slug TEXT REFERENCES daily_tours(slug) ON DELETE SET NULL;
|
||
|
||
-- Create index
|
||
CREATE INDEX IF NOT EXISTS idx_tour_recommendations_daily_tour_slug ON tour_recommendations(daily_tour_slug);
|
||
|
||
-- Insert seed data for Cappadocia daily tours
|
||
INSERT INTO daily_tours (slug, title, region, duration_hours, includes_types, min_places, max_places, suitable_for, base_price_range, description, highlights) VALUES
|
||
('red_tour', 'Red Tour', 'cappadocia', 6, ARRAY['museum','valley','panorama','historical'], 3, 6, ARRAY['first_day','culture','history'], '50-80', 'Kapadokya''nın en popüler noktalarını kapsayan klasik tur. Göreme Açık Hava Müzesi, Paşabağları, Devrent Vadisi ve Uçhisar Kalesi.', ARRAY['Göreme Açık Hava Müzesi','Paşabağları Peri Bacaları','Devrent Vadisi','Uçhisar Kalesi Panorama']),
|
||
('green_tour', 'Green Tour', 'cappadocia', 8, ARRAY['underground_city','valley','monastery','nature'], 4, 7, ARRAY['second_day','nature','adventure'], '60-90', 'Kapadokya''nın doğal güzelliklerini keşfedin. Derinkuyu Yeraltı Şehri, Ihlara Vadisi yürüyüşü ve Selime Manastırı.', ARRAY['Derinkuyu Yeraltı Şehri','Ihlara Vadisi Yürüyüşü','Selime Manastırı','Panoramik Manzaralar']),
|
||
('blue_tour', 'Blue Tour', 'cappadocia', 7, ARRAY['church','valley','village','cultural'], 3, 6, ARRAY['alternative','quiet','cultural'], '55-85', 'Kalabalıktan uzak, sakin rotalar. Soğanlı Vadisi, Keslik Manastırı ve geleneksel köy ziyaretleri.', ARRAY['Soğanlı Vadisi','Keslik Manastırı','Geleneksel Köy Ziyareti','Yerel Yaşam Deneyimi']),
|
||
('mixed_custom', 'Özel Karışık Tur', 'cappadocia', 7, ARRAY['museum','valley','underground_city','panorama'], 5, 10, ARRAY['complex','varied','custom'], '70-120', 'Farklı turlardan seçilmiş yerleri içeren özel düzenlenmiş tur. Karmaşık planlar için ideal.', ARRAY['Esnek Program','Çeşitli Mekanlar','Özel Düzenleme']),
|
||
('private_guide', 'Özel Rehber', 'cappadocia', 8, ARRAY['custom','flexible'], 1, 15, ARRAY['personalized','flexible','family'], '100-200', 'Tamamen size özel planlanmış tur. İstediğiniz yerleri, istediğiniz sırayla gezin.', ARRAY['Tamamen Özel Program','Esnek Zaman','Kişisel Rehber','Aile Dostu']);
|
||
|
||
COMMENT ON TABLE daily_tours IS 'Predefined daily tour templates for AI matching and recommendations';
|
||
COMMENT ON COLUMN daily_tours.slug IS 'Unique identifier for the tour (e.g., red_tour, green_tour)';
|
||
COMMENT ON COLUMN daily_tours.includes_types IS 'Types of places this tour typically includes';
|
||
COMMENT ON COLUMN daily_tours.suitable_for IS 'Tags indicating when this tour is suitable (e.g., first_day, culture)';
|