-- 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)';
