-- Drop existing daily_tours table and recreate with new schema
DROP TABLE IF EXISTS daily_tours CASCADE;

-- Create daily_tours table (CORE REFERENCE)
CREATE TABLE daily_tours (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug TEXT UNIQUE NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  region_slug TEXT NOT NULL,
  duration_hours INTEGER NOT NULL,
  route_places TEXT[] NOT NULL,
  related_place_types TEXT[] NOT NULL,
  difficulty TEXT CHECK (difficulty IN ('easy','medium','hard')) DEFAULT 'easy',
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT now()
);

-- Add indexes
CREATE INDEX idx_daily_tours_region_slug ON daily_tours(region_slug);
CREATE INDEX idx_daily_tours_slug ON daily_tours(slug);
CREATE INDEX 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);

-- Insert Cappadocia daily tours with EXACT slugs and routes
INSERT INTO daily_tours (
  slug, title, description, region_slug, duration_hours,
  route_places, related_place_types
) VALUES 
(
  'red_tour',
  'Kapadokya Red Tour',
  'Göreme ve çevresindeki tarihi vadiler ve müzeler',
  'cappadocia',
  6,
  ARRAY[
    'goreme_open_air_museum',
    'pasabag',
    'devrent_valley',
    'avanos'
  ],
  ARRAY[
    'museum',
    'historical',
    'valley'
  ]
),
(
  'green_tour',
  'Kapadokya Green Tour',
  'Doğa, vadiler ve yeraltı şehirleri',
  'cappadocia',
  7,
  ARRAY[
    'derinkuyu_underground_city',
    'ihlara_valley',
    'selime_monastery',
    'pigeon_valley'
  ],
  ARRAY[
    'nature',
    'historical',
    'underground_city'
  ]
),
(
  'blue_tour',
  'Kapadokya Blue Tour',
  'Az bilinen vadiler ve manzara noktaları',
  'cappadocia',
  6,
  ARRAY[
    'soganli_valley',
    'mustafapasa',
    'sobesos'
  ],
  ARRAY[
    'village',
    'valley',
    'historical'
  ]
),
(
  'balloon_day',
  'Balon + Hafif Günlük Tur',
  'Sabah balon uçuşu sonrası hafif günlük gezi',
  'cappadocia',
  5,
  ARRAY[
    'hot_air_balloon',
    'goreme_panorama',
    'avanos'
  ],
  ARRAY[
    'hot_air_balloon',
    'panorama',
    'leisure'
  ]
),
(
  'private_guide',
  'Özel Rehber',
  'Tamamen size özel planlanmış tur',
  'cappadocia',
  8,
  ARRAY[
    'custom'
  ],
  ARRAY[
    'custom',
    'flexible'
  ]
);

-- Update tour_recommendations to reference daily_tours by slug
ALTER TABLE tour_recommendations
DROP COLUMN IF EXISTS daily_tour_slug;

ALTER TABLE tour_recommendations
ADD COLUMN daily_tour_slug TEXT REFERENCES daily_tours(slug) ON DELETE SET NULL;

CREATE INDEX idx_tour_recommendations_daily_tour_slug ON tour_recommendations(daily_tour_slug);

COMMENT ON TABLE daily_tours IS 'Predefined daily tour templates - AI MUST NOT invent slugs, only use these';
COMMENT ON COLUMN daily_tours.slug IS 'Unique identifier - MUST match provider.services array exactly';
COMMENT ON COLUMN daily_tours.route_places IS 'Logical route of places in order';
COMMENT ON COLUMN daily_tours.related_place_types IS 'Place types for AI matching algorithm';