38980-vm/app-9w9pd00g5j41/TODO_DAILY_TOURS_IMPLEMENTATION.md

Daily Tours System Implementation - Complete

Objective

Implement the GOLDEN RULE daily tours system where:

• AI matches trips to predefined daily_tours from database (NO inventing slugs)
• Providers have services array with exact slug matches
• search-tours Edge Function matches providers by slug
• Fallback to 'private_guide' if no match found

Implementation Summary

✅ 1. Database Schema (Migration 00033)

Table: daily_tours

• slug (UNIQUE, NOT NULL) - red_tour, green_tour, blue_tour, balloon_day, private_guide
• title, description, region_slug (cappadocia)
• duration_hours
• route_places TEXT[] - logical route of places
• related_place_types TEXT[] - for AI matching
• difficulty (easy/medium/hard)
• is_active, created_at

Seed Data Inserted:

• red_tour: goreme_open_air_museum, pasabag, devrent_valley, avanos
• green_tour: derinkuyu_underground_city, ihlara_valley, selime_monastery, pigeon_valley
• blue_tour: soganli_valley, mustafapasa, sobesos
• balloon_day: hot_air_balloon, goreme_panorama, avanos
• private_guide: custom

Provider Services Enhanced:

• daily_tour_services TEXT[] - array of slugs provider offers
• description, duration, price_per_person, currency
• max_group_size, includes, excludes
• languages, vehicle_types, rating, lead_price
• contact_email, contact_phone, total_reviews
• is_active

✅ 2. AI Matching Logic (analyze-trip Edge Function)

GOLDEN RULE Implementation:

1. Query daily_tours WHERE region_slug = trip.region_slug AND is_active = true
2. Extract trip place types from days/places
3. Score each daily_tour based on overlap:
   score = overlap(daily_tour.related_place_types, trip_place_types) / total_types
4. Select highest scoring tour (score >= 0.3)
5. FALLBACK: If no match and travelers >= 4 → suggest 'private_guide'
6. Return daily_tour_slug in response

Key Changes:

• Removed hardcoded rule-based matching
• Database-driven matching using daily_tours table
• AI cannot invent slugs, only uses database slugs
• Confidence threshold lowered to 0.5 for better coverage

✅ 3. Provider Matching (search-tours Edge Function)

GOLDEN RULE Implementation:

1. Query provider_services WHERE daily_tour_services CONTAINS daily_tour_slug
2. Join with profiles for provider info
3. Score by: rating (highest priority) + lead_price (lower is better) + exact match bonus
4. Return top providers sorted by relevance
5. FALLBACK: If no providers found → return empty with fallback_suggestion: 'private_guide'

Key Changes:

• Matches providers by exact slug in daily_tour_services array
• Uses provider_services table instead of tours table
• Joins with profiles for provider contact info
• Returns tour-like format for backward compatibility

✅ 4. Frontend Integration

TourModal.tsx:

• Passes daily_tour_slug to searchTours API call
• Shows appropriate error message if no providers found

api.ts:

• Updated searchTours to accept daily_tour_slug parameter

✅ 5. Sample Data

Provider: Temren Travel

• daily_tour_services: ['red_tour', 'green_tour', 'blue_tour', 'private_guide']
• rating: 4.8, total_reviews: 156
• price_per_person: 350 TRY
• includes: Rehber, Transfer, Öğle yemeği, Müze giriş biletleri
• languages: Türkçe, İngilizce
• vehicle_types: Minibüs, VIP Araç

How It Works

User Flow

1. User creates trip in Kapadokya with places
   ↓
2. AI analyzes trip → queries daily_tours table
   ↓
3. Scores tours by place type overlap
   ↓
4. Returns best match (e.g., red_tour) with confidence
   ↓
5. User clicks "Uygun Turları Gör"
   ↓
6. search-tours queries provider_services WHERE 'red_tour' IN daily_tour_services
   ↓
7. Returns matching providers sorted by rating/price
   ↓
8. User selects provider → creates lead
   ↓
9. Provider receives qualified lead with full context

Matching Algorithm

Trip: Göreme Museum, Paşabağ, Uçhisar, Avanos
Place Types: [museum, historical, valley, panorama]

daily_tours scoring:
- red_tour: related_place_types = [museum, historical, valley]
  overlap = 3/4 = 0.75 → MATCH ✅
- green_tour: related_place_types = [nature, historical, underground_city]
  overlap = 1/4 = 0.25 → NO MATCH
- blue_tour: related_place_types = [village, valley, historical]
  overlap = 2/4 = 0.50 → POSSIBLE MATCH

Result: red_tour (highest score)

Key Benefits

1. No More Invented Slugs

• AI MUST use slugs from daily_tours table
• Prevents "cappadocia-green" vs "green_tour" mismatches
• Database is single source of truth

2. Guaranteed Provider Matching

• Providers explicitly declare which tours they offer
• Exact slug matching ensures high success rate
• Fallback to private_guide prevents empty results

3. Revenue Optimization

• Providers sorted by rating first (quality)
• Then by lead_price (cost efficiency)
• Exact match bonus for perfect fit
• Commission tracking built-in

4. Scalability

• Add new tours: just INSERT into daily_tours
• Add new regions: use different region_slug
• Add new providers: update daily_tour_services array
• No code changes needed

Testing Checklist

• Database migration applied successfully
• daily_tours table populated with Cappadocia tours
• provider_services enhanced with tour fields
• Sample provider configured with tour services
• analyze-trip Edge Function deployed
• search-tours Edge Function deployed
• Frontend API updated with daily_tour_slug
• TourModal passes slug to search

Next Steps for Production

1. Add More Providers:

   UPDATE provider_services
   SET daily_tour_services = ARRAY['red_tour', 'green_tour']
   WHERE provider_id = 'xxx';
   

2. Add More Regions:

   INSERT INTO daily_tours (slug, title, region_slug, ...)
   VALUES ('istanbul_classic', 'İstanbul Klasik Tur', 'istanbul', ...);
   

3. Monitor Matching Success:

   SELECT daily_tour_slug, COUNT(*) as recommendations
   FROM tour_recommendations
   WHERE created_at > NOW() - INTERVAL '7 days'
   GROUP BY daily_tour_slug;
   

4. Track Provider Performance:

   SELECT ps.business_name, COUNT(l.id) as leads
   FROM leads l
   JOIN provider_services ps ON l.provider_id = ps.provider_id
   WHERE l.trigger_source = 'ai_route_recommendation'
   GROUP BY ps.business_name
   ORDER BY leads DESC;
   

Debug Commands

Check daily tours:

SELECT slug, region_slug, related_place_types
FROM daily_tours
WHERE region_slug = 'cappadocia';

Check provider services:

SELECT business_name, daily_tour_services, rating, lead_price
FROM provider_services
WHERE is_active = true;

Test matching:

SELECT * FROM provider_services
WHERE 'red_tour' = ANY(daily_tour_services);

Success Metrics

• Matching Rate: % of trips that get tour recommendations
• Provider Match Rate: % of recommendations that find providers
• Conversion Rate: % of recommendations that become leads
• Revenue per Lead: Average commission from tour bookings

Target: 80%+ provider match rate (vs previous ~30%)