-- Add new fields to tour_recommendations table for enhanced AI analysis

-- Add comparison metrics fields
ALTER TABLE tour_recommendations
ADD COLUMN IF NOT EXISTS distance_saved_km NUMERIC,
ADD COLUMN IF NOT EXISTS time_saved_hours NUMERIC,
ADD COLUMN IF NOT EXISTS logistics_removed TEXT[],
ADD COLUMN IF NOT EXISTS expert_value TEXT[];

-- Add traveler profile fields
ALTER TABLE tour_recommendations
ADD COLUMN IF NOT EXISTS traveler_group_type TEXT CHECK (traveler_group_type IN ('couple', 'family', 'friends', 'solo')),
ADD COLUMN IF NOT EXISTS traveler_pace TEXT CHECK (traveler_pace IN ('slow', 'balanced', 'fast')),
ADD COLUMN IF NOT EXISTS traveler_budget_level TEXT CHECK (traveler_budget_level IN ('low', 'mid', 'high'));

-- Update recommended_type constraint to support new types
ALTER TABLE tour_recommendations
DROP CONSTRAINT IF EXISTS tour_recommendations_recommended_type_check;

ALTER TABLE tour_recommendations
ADD CONSTRAINT tour_recommendations_recommended_type_check 
CHECK (recommended_type IN ('daily_tour', 'private_guide', 'driver_car', 'activity_bundle'));

-- Add comment for documentation
COMMENT ON COLUMN tour_recommendations.distance_saved_km IS 'Estimated kilometers saved by using tour vs self-guided';
COMMENT ON COLUMN tour_recommendations.time_saved_hours IS 'Estimated hours saved by using tour vs self-guided';
COMMENT ON COLUMN tour_recommendations.logistics_removed IS 'List of logistics tasks removed (e.g., ticket purchase, transfer arrangement)';
COMMENT ON COLUMN tour_recommendations.expert_value IS 'List of expert value adds (e.g., local knowledge, hidden spots)';
COMMENT ON COLUMN tour_recommendations.traveler_group_type IS 'Type of traveler group: couple, family, friends, solo';
COMMENT ON COLUMN tour_recommendations.traveler_pace IS 'Travel pace: slow, balanced, fast';
COMMENT ON COLUMN tour_recommendations.traveler_budget_level IS 'Budget level: low, mid, high';