Trip Planner MCP

-- Drop existing tables DROP TABLE IF EXISTS trip_activities CASCADE; DROP TABLE IF EXISTS trip_bookings CASCADE; DROP TABLE IF EXISTS venue_menu_items CASCADE; DROP TABLE IF EXISTS venues CASCADE; DROP TABLE IF EXISTS food_allergies CASCADE; DROP TABLE IF EXISTS dietary_restrictions CASCADE; DROP TABLE IF EXISTS employee_food_preferences CASCADE; DROP TABLE IF EXISTS menu_items CASCADE; DROP TABLE IF EXISTS cuisines CASCADE; -- ===================================================== -- FOOD & CUISINE MANAGEMENT -- ===================================================== -- Cuisines catalog CREATE TABLE cuisines ( cuisine_id SERIAL PRIMARY KEY, cuisine_name VARCHAR(100) UNIQUE NOT NULL, origin_country VARCHAR(100), description TEXT, is_popular BOOLEAN DEFAULT FALSE ); -- Menu items (food catalog) CREATE TABLE menu_items ( item_id SERIAL PRIMARY KEY, item_name VARCHAR(200) NOT NULL, cuisine_id INT REFERENCES cuisines(cuisine_id), course_type VARCHAR(50) CHECK (course_type IN ('appetizer', 'main_course', 'dessert', 'beverage', 'snack')), is_vegetarian BOOLEAN DEFAULT FALSE, is_vegan BOOLEAN DEFAULT FALSE, spice_level INT CHECK (spice_level BETWEEN 0 AND 5), calories INT, price_range VARCHAR(20) CHECK (price_range IN ('budget', 'moderate', 'premium')), description TEXT, ingredients TEXT[], preparation_time_mins INT, created_at TIMESTAMP DEFAULT NOW() ); -- Employee food preferences (likes/dislikes) CREATE TABLE employee_food_preferences ( preference_id SERIAL PRIMARY KEY, employee_email VARCHAR(150) NOT NULL, -- Links to Neo4j Employee.email item_id INT REFERENCES menu_items(item_id) ON DELETE CASCADE, preference_type VARCHAR(20) CHECK (preference_type IN ('love', 'like', 'neutral', 'dislike', 'hate')), preference_level INT CHECK (preference_level BETWEEN 1 AND 5), -- 1=low, 5=high notes TEXT, last_updated TIMESTAMP DEFAULT NOW(), UNIQUE(employee_email, item_id) ); -- Dietary restrictions (medical/religious) CREATE TABLE dietary_restrictions ( restriction_id SERIAL PRIMARY KEY, employee_email VARCHAR(150) NOT NULL, restriction_type VARCHAR(50) CHECK (restriction_type IN ('vegetarian', 'vegan', 'halal', 'kosher', 'gluten_free', 'lactose_intolerant', 'keto', 'paleo', 'diabetic', 'low_sodium')), severity VARCHAR(20) CHECK (severity IN ('strict', 'moderate', 'flexible')), notes TEXT, certified_date DATE, UNIQUE(employee_email, restriction_type) ); -- Food allergies CREATE TABLE food_allergies ( allergy_id SERIAL PRIMARY KEY, employee_email VARCHAR(150) NOT NULL, allergen VARCHAR(100) NOT NULL, -- e.g., 'peanuts', 'shellfish', 'dairy', 'eggs' severity VARCHAR(20) CHECK (severity IN ('mild', 'moderate', 'severe', 'anaphylactic')), diagnosed_date DATE, notes TEXT, UNIQUE(employee_email, allergen) ); -- ===================================================== -- VENUE & RESTAURANT MANAGEMENT -- ===================================================== -- Venues (restaurants, cafes, etc.) CREATE TABLE venues ( venue_id SERIAL PRIMARY KEY, venue_name VARCHAR(200) NOT NULL, venue_type VARCHAR(50) CHECK (venue_type IN ('restaurant', 'cafe', 'food_court', 'catering_service', 'hotel_dining', 'street_food')), city VARCHAR(100) NOT NULL, country VARCHAR(100) NOT NULL, address TEXT, latitude DECIMAL(10, 8), longitude DECIMAL(11, 8), phone VARCHAR(20), email VARCHAR(150), website VARCHAR(255), average_rating DECIMAL(3, 2) CHECK (average_rating BETWEEN 0 AND 5), price_range VARCHAR(20) CHECK (price_range IN ('budget', 'moderate', 'premium', 'luxury')), seating_capacity INT, has_vegetarian_options BOOLEAN DEFAULT TRUE, has_vegan_options BOOLEAN DEFAULT FALSE, accepts_group_bookings BOOLEAN DEFAULT TRUE, parking_available BOOLEAN DEFAULT FALSE, outdoor_seating BOOLEAN DEFAULT FALSE, cuisines_offered TEXT[], -- Array of cuisine types opening_hours JSONB, -- Store hours in JSON format created_at TIMESTAMP DEFAULT NOW() ); -- Venue menu items (what venues serve) CREATE TABLE venue_menu_items ( venue_menu_id SERIAL PRIMARY KEY, venue_id INT REFERENCES venues(venue_id) ON DELETE CASCADE, item_id INT REFERENCES menu_items(item_id) ON DELETE CASCADE, price DECIMAL(10, 2), is_available BOOLEAN DEFAULT TRUE, is_signature_dish BOOLEAN DEFAULT FALSE, UNIQUE(venue_id, item_id) ); -- ===================================================== -- TRIP BOOKING & MANAGEMENT -- ===================================================== -- Trip bookings CREATE TABLE trip_bookings ( booking_id SERIAL PRIMARY KEY, trip_name VARCHAR(200) NOT NULL, destination_city VARCHAR(100) NOT NULL, destination_country VARCHAR(100) NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, total_participants INT NOT NULL, organizer_email VARCHAR(150) NOT NULL, -- Links to Neo4j Employee budget_per_person DECIMAL(10, 2), accommodation_booked BOOLEAN DEFAULT FALSE, transportation_booked BOOLEAN DEFAULT FALSE, status VARCHAR(50) CHECK (status IN ('planning', 'confirmed', 'in_progress', 'completed', 'cancelled')), special_requirements TEXT, created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW() ); -- Trip activities & itinerary CREATE TABLE trip_activities ( activity_id SERIAL PRIMARY KEY, booking_id INT REFERENCES trip_bookings(booking_id) ON DELETE CASCADE, activity_date DATE NOT NULL, activity_time TIME, activity_type VARCHAR(50) CHECK (activity_type IN ('dining', 'sightseeing', 'team_building', 'meeting', 'leisure', 'travel', 'accommodation')), venue_id INT REFERENCES venues(venue_id), -- NULL if not venue-related activity_name VARCHAR(200) NOT NULL, description TEXT, estimated_cost DECIMAL(10, 2), duration_hours DECIMAL(4, 2), booking_reference VARCHAR(100), status VARCHAR(50) CHECK (status IN ('planned', 'confirmed', 'completed', 'cancelled')), notes TEXT ); -- ===================================================== -- SAMPLE DATA -- ===================================================== -- Insert Cuisines INSERT INTO cuisines (cuisine_name, origin_country, description, is_popular) VALUES ('Italian', 'Italy', 'Pasta, pizza, and Mediterranean flavors', TRUE), ('Indian', 'India', 'Spicy curries, tandoori, and regional specialties', TRUE), ('Chinese', 'China', 'Stir-fries, noodles, and dim sum', TRUE), ('Japanese', 'Japan', 'Sushi, ramen, and tempura', TRUE), ('Mexican', 'Mexico', 'Tacos, burritos, and spicy salsas', TRUE), ('Thai', 'Thailand', 'Curries, pad thai, and coconut-based dishes', TRUE), ('French', 'France', 'Fine dining, pastries, and classic techniques', FALSE), ('Mediterranean', 'Multiple', 'Healthy grilled meats, salads, and olive oil', TRUE), ('American', 'USA', 'Burgers, BBQ, and comfort food', TRUE), ('Middle Eastern', 'Multiple', 'Hummus, falafel, and grilled meats', FALSE); -- Insert Menu Items INSERT INTO menu_items (item_name, cuisine_id, course_type, is_vegetarian, is_vegan, spice_level, calories, price_range, description, ingredients) VALUES ('Margherita Pizza', 1, 'main_course', TRUE, FALSE, 1, 800, 'moderate', 'Classic Italian pizza with tomato and mozzarella', ARRAY['flour', 'tomato', 'mozzarella', 'basil']), ('Chicken Tikka Masala', 2, 'main_course', FALSE, FALSE, 4, 650, 'moderate', 'Creamy tomato-based chicken curry', ARRAY['chicken', 'tomato', 'cream', 'spices']), ('Vegetable Spring Rolls', 3, 'appetizer', TRUE, TRUE, 2, 200, 'budget', 'Crispy rolls with mixed vegetables', ARRAY['cabbage', 'carrot', 'wrapper', 'soy sauce']), ('Sushi Combo Platter', 4, 'main_course', FALSE, FALSE, 1, 500, 'premium', 'Assorted nigiri and rolls', ARRAY['rice', 'fish', 'seaweed', 'wasabi']), ('Beef Tacos', 5, 'main_course', FALSE, FALSE, 3, 450, 'budget', 'Soft tacos with seasoned beef', ARRAY['beef', 'tortilla', 'cheese', 'salsa']), ('Pad Thai', 6, 'main_course', FALSE, FALSE, 3, 550, 'moderate', 'Stir-fried rice noodles with shrimp', ARRAY['noodles', 'shrimp', 'peanuts', 'tamarind']), ('Croissant', 7, 'snack', TRUE, FALSE, 0, 250, 'budget', 'Buttery French pastry', ARRAY['flour', 'butter', 'yeast']), ('Greek Salad', 8, 'appetizer', TRUE, FALSE, 1, 300, 'moderate', 'Fresh salad with feta cheese', ARRAY['tomato', 'cucumber', 'feta', 'olives', 'olive oil']), ('BBQ Burger', 9, 'main_course', FALSE, FALSE, 2, 850, 'moderate', 'Grilled beef burger with BBQ sauce', ARRAY['beef', 'bun', 'cheese', 'BBQ sauce', 'lettuce']), ('Falafel Wrap', 10, 'main_course', TRUE, TRUE, 2, 400, 'budget', 'Chickpea fritters in pita bread', ARRAY['chickpeas', 'pita', 'tahini', 'vegetables']), ('Tiramisu', 1, 'dessert', TRUE, FALSE, 0, 450, 'moderate', 'Italian coffee-flavored dessert', ARRAY['mascarpone', 'coffee', 'ladyfingers', 'cocoa']), ('Mango Lassi', 2, 'beverage', TRUE, FALSE, 0, 200, 'budget', 'Yogurt-based mango drink', ARRAY['mango', 'yogurt', 'sugar', 'cardamom']); -- Insert Venues INSERT INTO venues (venue_name, venue_type, city, country, address, average_rating, price_range, seating_capacity, has_vegetarian_options, has_vegan_options, accepts_group_bookings, cuisines_offered) VALUES ('Bella Italia', 'restaurant', 'Mumbai', 'India', '123 Marine Drive', 4.5, 'premium', 80, TRUE, FALSE, TRUE, ARRAY['Italian']), ('Spice Garden', 'restaurant', 'Bangalore', 'India', '45 MG Road', 4.7, 'moderate', 120, TRUE, TRUE, TRUE, ARRAY['Indian', 'Thai']), ('Dragon Palace', 'restaurant', 'Goa', 'India', '78 Beach Road, Calangute', 4.2, 'moderate', 100, TRUE, FALSE, TRUE, ARRAY['Chinese', 'Japanese']), ('Taco Fiesta', 'restaurant', 'Pune', 'India', '22 FC Road', 4.3, 'budget', 60, TRUE, TRUE, TRUE, ARRAY['Mexican']), ('The Mediterranean', 'restaurant', 'Mumbai', 'India', '56 Worli Sea Face', 4.8, 'premium', 90, TRUE, TRUE, TRUE, ARRAY['Mediterranean', 'Middle Eastern']), ('Cafe Royale', 'cafe', 'Bangalore', 'India', '12 Indiranagar', 4.4, 'moderate', 40, TRUE, TRUE, FALSE, ARRAY['French', 'American']), ('Green Bowl', 'restaurant', 'Hyderabad', 'India', '33 Banjara Hills', 4.6, 'moderate', 70, TRUE, TRUE, TRUE, ARRAY['Mediterranean', 'American']), ('Curry House', 'restaurant', 'Chennai', 'India', '18 T Nagar', 4.5, 'budget', 150, TRUE, FALSE, TRUE, ARRAY['Indian']), ('Sushi Bar Tokyo', 'restaurant', 'Delhi', 'India', '99 Connaught Place', 4.7, 'premium', 50, FALSE, FALSE, TRUE, ARRAY['Japanese']), ('Food Court Central', 'food_court', 'Bangalore', 'India', 'Phoenix Mall, Whitefield', 4.0, 'budget', 300, TRUE, TRUE, TRUE, ARRAY['Indian', 'Chinese', 'American', 'Italian']); -- Link venues to menu items INSERT INTO venue_menu_items (venue_id, item_id, price, is_available, is_signature_dish) VALUES (1, 1, 650.00, TRUE, TRUE), (1, 11, 350.00, TRUE, FALSE), (2, 2, 480.00, TRUE, TRUE), (2, 6, 420.00, TRUE, FALSE), (2, 12, 120.00, TRUE, FALSE), (3, 3, 180.00, TRUE, FALSE), (3, 4, 1200.00, TRUE, TRUE), (4, 5, 280.00, TRUE, TRUE), (5, 8, 380.00, TRUE, TRUE), (5, 10, 320.00, TRUE, FALSE), (6, 7, 80.00, TRUE, FALSE), (7, 8, 420.00, TRUE, TRUE), (7, 9, 550.00, TRUE, FALSE), (8, 2, 380.00, TRUE, TRUE), (9, 4, 1500.00, TRUE, TRUE), (10, 1, 400.00, TRUE, FALSE), (10, 5, 250.00, TRUE, FALSE), (10, 9, 350.00, TRUE, FALSE); -- Insert Employee Food Preferences (using email as link to Neo4j) INSERT INTO employee_food_preferences (employee_email, item_id, preference_type, preference_level, notes) VALUES ('john.doe@example.com', 1, 'love', 5, 'Favorite weekend meal'), ('john.doe@example.com', 9, 'like', 4, 'Enjoys American food'), ('jane.smith@example.com', 2, 'love', 5, 'Loves spicy Indian food'), ('jane.smith@example.com', 4, 'like', 3, 'Occasional sushi lover'), ('mike.jones@example.com', 3, 'like', 4, 'Prefers light appetizers'), ('mike.jones@example.com', 10, 'love', 5, 'Vegetarian preference'), ('sarah.williams@example.com', 8, 'love', 5, 'Health-conscious choices'), ('sarah.williams@example.com', 7, 'like', 4, 'Morning pastries'), ('chris.evans@example.com', 6, 'love', 5, 'Thai food enthusiast'), ('patty.oneil@example.com', 10, 'love', 5, 'Vegan preference'), ('tom.baker@example.com', 9, 'love', 5, 'BBQ fan'), ('nancy.drew@example.com', 11, 'love', 5, 'Loves Italian desserts'), ('oscar.wilde@example.com', 2, 'like', 4, 'Enjoys spicy food'), ('alice.johnson@example.com', 1, 'love', 5, 'Italian food lover'); -- Insert Dietary Restrictions INSERT INTO dietary_restrictions (employee_email, restriction_type, severity, notes) VALUES ('patty.oneil@example.com', 'vegan', 'strict', 'No animal products'), ('mike.jones@example.com', 'vegetarian', 'flexible', 'Occasionally eats fish'), ('nancy.drew@example.com', 'gluten_free', 'moderate', 'Celiac disease'), ('sarah.williams@example.com', 'lactose_intolerant', 'moderate', 'Prefers dairy-free options'); -- Insert Food Allergies INSERT INTO food_allergies (employee_email, allergen, severity, notes) VALUES ('john.doe@example.com', 'peanuts', 'severe', 'EpiPen required'), ('jane.smith@example.com', 'shellfish', 'moderate', 'Causes rash'), ('nancy.drew@example.com', 'gluten', 'moderate', 'Digestive issues'); -- Insert Trip Bookings INSERT INTO trip_bookings (trip_name, destination_city, destination_country, start_date, end_date, total_participants, organizer_email, budget_per_person, status, special_requirements) VALUES ('Alpha Team Annual Offsite', 'Goa', 'India', '2024-03-15', '2024-03-18', 5, 'alice.johnson@example.com', 15000.00, 'planning', 'Beach resort with conference facilities'), ('Beta Squad Team Building', 'Udaipur', 'India', '2024-04-10', '2024-04-12', 3, 'sarah.williams@example.com', 12000.00, 'confirmed', 'Heritage hotel with veg options'), ('Company Retreat 2024', 'Jaipur', 'India', '2024-05-20', '2024-05-23', 15, 'alice.johnson@example.com', 20000.00, 'planning', 'Large group, multiple dietary needs'); -- Insert Trip Activities INSERT INTO trip_activities (booking_id, activity_date, activity_time, activity_type, venue_id, activity_name, description, estimated_cost, duration_hours, status) VALUES (1, '2024-03-15', '19:00:00', 'dining', 3, 'Welcome Dinner', 'Team dinner at Dragon Palace', 5000.00, 2.5, 'planned'), (1, '2024-03-16', '09:00:00', 'sightseeing', NULL, 'Beach Activities', 'Water sports and beach games', 3000.00, 4.0, 'planned'), (1, '2024-03-16', '20:00:00', 'dining', 5, 'Gala Dinner', 'Special team celebration', 8000.00, 3.0, 'planned'), (2, '2024-04-10', '18:30:00', 'dining', 2, 'Opening Dinner', 'Welcome dinner at Spice Garden', 4000.00, 2.0, 'confirmed'), (2, '2024-04-11', '12:00:00', 'team_building', NULL, 'City Palace Tour', 'Heritage walk and photography', 2000.00, 3.0, 'confirmed'); -- Create indexes for performance CREATE INDEX idx_employee_food_prefs_email ON employee_food_preferences(employee_email); CREATE INDEX idx_dietary_restrictions_email ON dietary_restrictions(employee_email); CREATE INDEX idx_food_allergies_email ON food_allergies(employee_email); CREATE INDEX idx_venues_city ON venues(city); CREATE INDEX idx_trip_bookings_organizer ON trip_bookings(organizer_email); CREATE INDEX idx_trip_bookings_dates ON trip_bookings(start_date, end_date);