SQL_Mock Data.txt

-- ================================================
-- MOCK DATA FOR SSG WHATSAPP AI DATABASE
-- ================================================

-- ================================================
-- INSERT MOCK CATEGORIES
-- ================================================

INSERT INTO categories (id, name, description, icon, display_order) VALUES
(1, 'Marinas', 'Marina facilities and berth services', '⚓', 1),
(2, 'Boat Repair & Maintenance', 'Boat repair, maintenance, and servicing', '🔧', 2),
(3, 'Marine Electronics', 'Navigation, communication, and marine electronics', '📡', 3),
(4, 'Sailing Schools', 'Sailing education and training programs', '⛵', 4),
(5, 'Charter Services', 'Yacht and boat charter services', '🚤', 5),
(6, 'Marine Insurance', 'Marine and yacht insurance services', '🛡️', 6),
(7, 'Boat Dealers', 'New and used boat sales', '🛥️', 7),
(8, 'Marine Supplies', 'Marine equipment and supplies', '⚙️', 8),
(9, 'Yacht Clubs', 'Yacht clubs and memberships', '🏆', 9),
(10, 'Fishing Charters', 'Sport fishing and charter services', '🎣', 10);

-- ================================================
-- INSERT MOCK LOCATIONS
-- ================================================

INSERT INTO locations (id, name, country, region, latitude, longitude, display_order) VALUES
(1, 'Singapore', 'Singapore', 'Southeast Asia', 1.3521, 103.8198, 1),
(2, 'Malaysia', 'Malaysia', 'Southeast Asia', 4.2105, 101.9758, 2),
(3, 'Thailand', 'Thailand', 'Southeast Asia', 13.7563, 100.5018, 3),
(4, 'Indonesia', 'Indonesia', 'Southeast Asia', -6.2088, 106.8456, 4),
(5, 'Philippines', 'Philippines', 'Southeast Asia', 14.5995, 120.9842, 5),
(6, 'Vietnam', 'Vietnam', 'Southeast Asia', 21.0285, 105.8542, 6),
(7, 'Hong Kong', 'Hong Kong', 'East Asia', 22.3193, 114.1694, 7),
(8, 'Australia', 'Australia', 'Oceania', -33.8688, 151.2093, 8);

-- ================================================
-- INSERT MOCK COMPANIES
-- ================================================

INSERT INTO companies (company_name, description, website_link, advertising_tier, category_id, location_id, keywords, contact_email, contact_phone) VALUES
('Marina Bay Yacht Club', 'Premier marina facility in Singapore with 150 berths, full amenities, and 24/7 security.', 'https://marinabay-yc.com', 'Premium', 1, 1, ARRAY['marina', 'yacht', 'berth', 'luxury', 'premium'], 'info@marinabay-yc.com', '+65-6123-4567'),
('ONE°15 Marina Sentosa Cove', 'Award-winning marina with world-class facilities, dining, and entertainment options.', 'https://one15marina.com', 'Premium', 1, 1, ARRAY['marina', 'sentosa', 'luxury', 'dining', 'entertainment'], 'contact@one15marina.com', '+65-6305-6988'),
('Raffles Marina', 'Full-service marina offering berths, boat services, and waterfront dining.', 'https://rafflesmarina.com.sg', 'Enhanced', 1, 1, ARRAY['marina', 'raffles', 'berth', 'dining', 'services'], 'enquiry@rafflesmarina.com.sg', '+65-6861-8888'),
('Keppel Bay Marina', 'Modern marina facility with comprehensive boat services and amenities.', 'https://keppelbaymarina.com', 'Logo', 1, 1, ARRAY['marina', 'keppel', 'modern', 'services'], 'info@keppelbaymarina.com', '+65-6303-2777'),
('Changi Sailing Club', 'Community marina with affordable berths and sailing programs.', 'https://csc.org.sg', 'Free', 1, 1, ARRAY['marina', 'changi', 'sailing', 'community', 'affordable'], 'admin@csc.org.sg', '+65-6545-2876'),
('Marine Pro Services', 'Expert boat repair and maintenance with 20+ years experience.', 'https://marinepro.com.sg', 'Premium', 2, 1, ARRAY['repair', 'maintenance', 'service', 'expert', 'experienced'], 'service@marinepro.com.sg', '+65-6777-8888'),
('Oceanic Electronics', 'Leading supplier of marine electronics, GPS, radar, and communication systems.', 'https://oceanic-elec.com', 'Enhanced', 3, 1, ARRAY['electronics', 'gps', 'radar', 'navigation', 'communication'], 'sales@oceanic-elec.com', '+65-6555-1234'),
('SailAsia Academy', 'Professional sailing school offering beginner to advanced courses.', 'https://sailasia.com', 'Premium', 4, 1, ARRAY['sailing', 'school', 'courses', 'training', 'education'], 'info@sailasia.com', '+65-6298-3322'),
('Luxury Yacht Charters SG', 'Premium yacht charter services for events and leisure.', 'https://luxurycharters.sg', 'Premium', 5, 1, ARRAY['charter', 'yacht', 'luxury', 'events', 'leisure'], 'book@luxurycharters.sg', '+65-6733-9988'),
('SeaGuard Insurance', 'Comprehensive marine insurance coverage for vessels of all sizes.', 'https://seaguard.com.sg', 'Enhanced', 6, 1, ARRAY['insurance', 'marine', 'coverage', 'protection', 'policy'], 'claims@seaguard.com.sg', '+65-6422-3344'),
('Phuket Marina Co.', 'Thailand''s premier marina with stunning views and excellent facilities.', 'https://phuketmarina.com', 'Premium', 1, 3, ARRAY['marina', 'phuket', 'thailand', 'luxury', 'views'], 'info@phuketmarina.com', '+66-76-360-862'),
('Port Dickson Marina', 'Malaysia''s top marina facility with modern amenities and services.', 'https://pdmarina.com.my', 'Enhanced', 1, 2, ARRAY['marina', 'malaysia', 'port dickson', 'modern', 'facilities'], 'enquiry@pdmarina.com.my', '+60-6-647-3333');

-- ================================================
-- INSERT MOCK USERS
-- ================================================

INSERT INTO users (phone_number, full_name, email, role, vessel_size, vessel_name, subscription_tier, subscription_start_date, subscription_end_date, queries_today, max_daily_queries, total_queries, onboarding_completed, preferences) VALUES
('+6591234567', 'John Tan', 'john.tan@email.com', 'boat_owner', '40ft', 'Sea Breeze', 'premium', '2025-09-01 00:00:00+00', '2026-09-01 00:00:00+00', 5, 50, 127, TRUE, '{"language": "en", "notifications": true}'),
('+6598765432', 'Sarah Lim', 'sarah.lim@email.com', 'boat_owner', '30ft', 'Wave Dancer', 'free', NULL, NULL, 8, 10, 24, TRUE, '{"language": "en", "notifications": false}'),
('+6587654321', 'Michael Chen', 'michael.chen@email.com', 'yacht_broker', '50ft', 'Ocean Pearl', 'premium', '2025-08-15 00:00:00+00', '2026-08-15 00:00:00+00', 12, 50, 289, TRUE, '{"language": "en", "notifications": true}'),
('+6596543210', 'Emily Wong', 'emily.wong@email.com', 'boat_owner', '25ft', 'Sunset Dream', 'free', NULL, NULL, 3, 10, 15, TRUE, '{"language": "en", "notifications": true}'),
('+6585432109', 'David Kumar', 'david.kumar@email.com', 'charter_operator', '60ft', 'Luxury Escape', 'premium', '2025-10-01 00:00:00+00', '2026-10-01 00:00:00+00', 2, 50, 45, TRUE, '{"language": "en", "notifications": true}'),
('+6594321098', 'Lisa Zhang', 'lisa.zhang@email.com', 'boat_owner', '35ft', 'Blue Horizon', 'free', NULL, NULL, 9, 10, 31, FALSE, '{"language": "en", "notifications": false}'),
('+6593210987', 'Robert Lee', 'robert.lee@email.com', 'boat_owner', '45ft', 'Starlight', 'free', NULL, NULL, 1, 10, 8, TRUE, '{"language": "en", "notifications": true}');

-- ================================================
-- INSERT MOCK CONVERSATIONS
-- ================================================

-- Conversations for John Tan (+6591234567)
INSERT INTO conversations (user_id, role, content, content_type, metadata, tokens_used, model_used) VALUES
((SELECT id FROM users WHERE phone_number = '+6591234567'), 'user', 'Hi, I need to find a good marina in Singapore', 'text', '{"source": "whatsapp"}', 0, NULL),
((SELECT id FROM users WHERE phone_number = '+6591234567'), 'assistant', 'Hello! I can help you find marinas in Singapore. Here are the top options:\n\n1. 🏆 Marina Bay Yacht Club - Premier facility with 150 berths\n2. 🏆 ONE°15 Marina Sentosa Cove - Award-winning marina\n3. ⭐ Raffles Marina - Full-service with dining\n\nWould you like more details about any of these?', 'text', '{"intent": {"category": "Marinas", "location": "Singapore", "search_term": ""}}', 245, 'deepseek-chat'),
((SELECT id FROM users WHERE phone_number = '+6591234567'), 'user', 'Tell me more about Marina Bay', 'text', '{"source": "whatsapp"}', 0, NULL),
((SELECT id FROM users WHERE phone_number = '+6591234567'), 'assistant', 'Marina Bay Yacht Club is one of Singapore''s premier marinas:\n\n✨ 150 well-maintained berths\n✨ 24/7 security and CCTV\n✨ Full amenities including showers, lounges\n✨ On-site restaurant and bar\n✨ Easy access to the city\n\nWebsite: https://marinabay-yc.com\n\nWould you like me to search for other services?', 'text', '{}', 198, 'deepseek-chat');

-- Conversations for Sarah Lim (+6598765432)
INSERT INTO conversations (user_id, role, content, content_type, metadata, tokens_used, model_used) VALUES
((SELECT id FROM users WHERE phone_number = '+6598765432'), 'user', 'I need boat repair services', 'text', '{"source": "whatsapp"}', 0, NULL),
((SELECT id FROM users WHERE phone_number = '+6598765432'), 'assistant', 'I found these boat repair services in Singapore:\n\n1. 🏆 Marine Pro Services - 20+ years experience\nExpert maintenance and repairs\nhttps://marinepro.com.sg\n\nThey handle everything from routine maintenance to major repairs. Need anything else?', 'text', '{"intent": {"category": "Boat Repair & Maintenance", "location": "Singapore", "search_term": ""}}', 189, 'claude-3-haiku'),
((SELECT id FROM users WHERE phone_number = '+6598765432'), 'user', 'What about marine electronics?', 'text', '{"source": "whatsapp"}', 0, NULL),
((SELECT id FROM users WHERE phone_number = '+6598765432'), 'assistant', 'Here are marine electronics suppliers:\n\n1. ⭐ Oceanic Electronics\nLeading supplier of GPS, radar, and communication systems\nhttps://oceanic-elec.com\n\nThey offer installation and support services too!', 'text', '{"intent": {"category": "Marine Electronics", "location": "Singapore", "search_term": ""}}', 156, 'claude-3-haiku');

-- Conversations for Michael Chen (+6587654321)
INSERT INTO conversations (user_id, role, content, content_type, metadata, tokens_used, model_used) VALUES
((SELECT id FROM users WHERE phone_number = '+6587654321'), 'user', 'Charter services in Thailand?', 'text', '{"source": "whatsapp"}', 0, NULL),
((SELECT id FROM users WHERE phone_number = '+6587654321'), 'assistant', 'I couldn''t find charter services specifically in Thailand in my current listings. However, I found marina facilities:\n\n1. 🏆 Phuket Marina Co. - Premier marina with stunning views\nhttps://phuketmarina.com\n\nWould you like me to search for charter services in Singapore instead?', 'text', '{"intent": {"category": "Charter Services", "location": "Thailand", "search_term": ""}}', 167, 'deepseek-chat');

-- Conversations for Emily Wong (+6596543210)
INSERT INTO conversations (user_id, role, content, content_type, metadata, tokens_used, model_used) VALUES
((SELECT id FROM users WHERE phone_number = '+6596543210'), 'user', 'Hello', 'text', '{"source": "whatsapp"}', 0, NULL),
((SELECT id FROM users WHERE phone_number = '+6596543210'), 'assistant', 'Hi there! 👋 I''m your SSG Directory assistant. I can help you find:\n\n⚓ Marinas\n🔧 Boat repair services\n📡 Marine electronics\n⛵ Sailing schools\n🚤 Charter services\n\nWhat are you looking for today?', 'text', '{}', 134, 'deepseek-chat'),
((SELECT id FROM users WHERE phone_number = '+6596543210'), 'user', 'Sailing school', 'text', '{"source": "whatsapp"}', 0, NULL),
((SELECT id FROM users WHERE phone_number = '+6596543210'), 'assistant', 'Great choice! Here''s a top sailing school in Singapore:\n\n1. 🏆 SailAsia Academy\nProfessional courses from beginner to advanced\nhttps://sailasia.com\n\nThey offer RYA certified courses. Interested?', 'text', '{"intent": {"category": "Sailing Schools", "location": "Singapore", "search_term": ""}}', 145, 'deepseek-chat');

-- ================================================
-- INSERT MOCK SEARCH QUERIES
-- ================================================

INSERT INTO search_queries (user_id, category_id, category_name, location_id, location_name, search_term, results_count, top_result_company, user_query, extracted_intent) VALUES
((SELECT id FROM users WHERE phone_number = '+6591234567'), '1', 'Marinas', '1', 'Singapore', '', 5, 'Marina Bay Yacht Club', 'I need to find a good marina in Singapore', '{"category": "Marinas", "location": "Singapore", "search_term": ""}'),
((SELECT id FROM users WHERE phone_number = '+6591234567'), '1', 'Marinas', '1', 'Singapore', 'luxury', 3, 'Marina Bay Yacht Club', 'luxury marina', '{"category": "Marinas", "location": "Singapore", "search_term": "luxury"}'),
((SELECT id FROM users WHERE phone_number = '+6598765432'), '2', 'Boat Repair & Maintenance', '1', 'Singapore', '', 1, 'Marine Pro Services', 'I need boat repair services', '{"category": "Boat Repair & Maintenance", "location": "Singapore", "search_term": ""}'),
((SELECT id FROM users WHERE phone_number = '+6598765432'), '3', 'Marine Electronics', '1', 'Singapore', '', 1, 'Oceanic Electronics', 'What about marine electronics?', '{"category": "Marine Electronics", "location": "Singapore", "search_term": ""}'),
((SELECT id FROM users WHERE phone_number = '+6587654321'), '5', 'Charter Services', '3', 'Thailand', '', 0, NULL, 'Charter services in Thailand?', '{"category": "Charter Services", "location": "Thailand", "search_term": ""}'),
((SELECT id FROM users WHERE phone_number = '+6587654321'), '1', 'Marinas', '2', 'Malaysia', '', 1, 'Port Dickson Marina', 'marinas in malaysia', '{"category": "Marinas", "location": "Malaysia", "search_term": ""}'),
((SELECT id FROM users WHERE phone_number = '+6596543210'), '4', 'Sailing Schools', '1', 'Singapore', '', 1, 'SailAsia Academy', 'Sailing school', '{"category": "Sailing Schools", "location": "Singapore", "search_term": ""}'),
((SELECT id FROM users WHERE phone_number = '+6585432109'), '5', 'Charter Services', '1', 'Singapore', 'luxury', 1, 'Luxury Yacht Charters SG', 'luxury yacht charter', '{"category": "Charter Services", "location": "Singapore", "search_term": "luxury"}'),
((SELECT id FROM users WHERE phone_number = '+6594321098'), '6', 'Marine Insurance', '1', 'Singapore', '', 1, 'SeaGuard Insurance', 'insurance for my boat', '{"category": "Marine Insurance", "location": "Singapore", "search_term": ""}');

-- ================================================
-- INSERT MOCK API LOGS
-- ================================================

INSERT INTO api_logs (user_id, endpoint, method, request_params, response_status, response_time_ms) VALUES
((SELECT id FROM users WHERE phone_number = '+6591234567'), '/api/search', 'GET', '{"category_id": "1", "location_id": "1"}', 200, 145),
((SELECT id FROM users WHERE phone_number = '+6591234567'), '/api/categories', 'GET', '{}', 200, 23),
((SELECT id FROM users WHERE phone_number = '+6598765432'), '/api/search', 'GET', '{"category_id": "2", "location_id": "1"}', 200, 132),
((SELECT id FROM users WHERE phone_number = '+6587654321'), '/api/search', 'GET', '{"category_id": "5", "location_id": "3"}', 200, 156),
((SELECT id FROM users WHERE phone_number = '+6596543210'), '/api/user/status', 'GET', '{"phone_number": "+6596543210"}', 200, 45),
(NULL, '/api/locations', 'GET', '{}', 200, 28),
((SELECT id FROM users WHERE phone_number = '+6585432109'), '/api/search', 'GET', '{"category_id": "5", "location_id": "1", "search_term": "luxury"}', 200, 167);

-- ================================================
-- INSERT MOCK USER FEEDBACK
-- ================================================

INSERT INTO user_feedback (user_id, conversation_id, rating, feedback_text, feedback_type) VALUES
((SELECT id FROM users WHERE phone_number = '+6591234567'), NULL, 5, 'Very helpful! Found exactly what I needed.', 'helpful'),
((SELECT id FROM users WHERE phone_number = '+6598765432'), NULL, 4, 'Good service, would like more options in the future.', 'helpful'),
((SELECT id FROM users WHERE phone_number = '+6587654321'), NULL, 3, 'Could not find what I was looking for in Thailand.', 'not_helpful'),
((SELECT id FROM users WHERE phone_number = '+6596543210'), NULL, 5, 'Super fast and easy to use!', 'helpful'),
((SELECT id FROM users WHERE phone_number = '+6585432109'), NULL, 4, 'Great recommendations. Would like image support.', 'feature_request');

-- ================================================
-- INSERT MOCK SYSTEM METRICS
-- ================================================

INSERT INTO system_metrics (metric_type, metric_value, metadata) VALUES
('daily_active_users', 47, '{"date": "2025-10-06"}'),
('total_queries_today', 234, '{"date": "2025-10-06"}'),
('avg_response_time_ms', 156, '{"date": "2025-10-06"}'),
('error_rate_percent', 2.3, '{"date": "2025-10-06"}'),
('premium_users', 3, '{"date": "2025-10-06"}'),
('free_users', 4, '{"date": "2025-10-06"}'),
('category_marinas_searches', 89, '{"date": "2025-10-06"}'),
('category_repairs_searches', 45, '{"date": "2025-10-06"}'),
('location_singapore_searches', 178, '{"date": "2025-10-06"}'),
('location_thailand_searches', 23, '{"date": "2025-10-06"}');

-- ================================================
-- INSERT MOCK SUBSCRIPTION HISTORY
-- ================================================

INSERT INTO subscription_history (user_id, previous_tier, new_tier, change_reason, amount_paid, payment_method, payment_status, valid_from, valid_until) VALUES
((SELECT id FROM users WHERE phone_number = '+6591234567'), 'free', 'premium', 'User upgraded to premium', 99.00, 'credit_card', 'completed', '2025-09-01 00:00:00+00', '2026-09-01 00:00:00+00'),
((SELECT id FROM users WHERE phone_number = '+6587654321'), 'free', 'premium', 'User upgraded to premium', 99.00, 'paypal', 'completed', '2025-08-15 00:00:00+00', '2026-08-15 00:00:00+00'),
((SELECT id FROM users WHERE phone_number = '+6585432109'), 'free', 'premium', 'User upgraded to premium', 99.00, 'credit_card', 'completed', '2025-10-01 00:00:00+00', '2026-10-01 00:00:00+00');

-- ================================================
-- VERIFY DATA INSERTION
-- ================================================

-- Check user count
SELECT 'Total Users: ' || COUNT(*) FROM users;

-- Check conversation count
SELECT 'Total Conversations: ' || COUNT(*) FROM conversations;

-- Check search query count
SELECT 'Total Search Queries: ' || COUNT(*) FROM search_queries;

-- Check most popular categories
SELECT 
    category_name, 
    COUNT(*) as search_count 
FROM search_queries 
GROUP BY category_name 
ORDER BY search_count DESC 
LIMIT 5;

-- Check subscription distribution
SELECT 
    subscription_tier, 
    COUNT(*) as user_count 
FROM users 
GROUP BY subscription_tier;