""" API Routes Blueprint Contains all /api/ endpoints for the application """ from flask import Blueprint, request, jsonify, current_app from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker import os # Create Blueprint api_bp = Blueprint('api', __name__, url_prefix='/api') @api_bp.route('/beaches-grid') def get_beaches_grid(): """Get beaches data for the grid page with filtering and pagination""" try: # Get request parameters - support both page and offset based pagination page = int(request.args.get('page', 1)) offset = int(request.args.get('offset', 0)) # New offset parameter per_page = int(request.args.get('per_page', 12)) filter_type = request.args.get('filter', 'all') search = request.args.get('search', '') country = request.args.get('country', '') city = request.args.get('city', '') category = request.args.get('category', '') # Use offset if provided, otherwise calculate from page if offset > 0: actual_offset = offset else: actual_offset = (page - 1) * per_page from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker # Database connection from config import ADMIN_CONFIG DATABASE_URL = ADMIN_CONFIG['DATABASE_URL'] engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) db = SessionLocal() # Check if required tables exist beach_places_check = db.execute(text(""" SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'beach_places' ) """)).fetchone() beach_places_exists = beach_places_check[0] if beach_places_check else False beaches_data = [] has_more = False total_count = 0 if beach_places_exists: # Build the enhanced base query with same structure as popular-beaches base_query = """ SELECT bp.beach_place_id, bp.beach_name, bp.address, bp.city, bp.beach_information, c.company_name, co.country_name, COALESCE(booking_counts.booking_count, 0) as popularity, bpp.photo_path, bps.price, cur.currency_code, MIN(CASE WHEN bpt.bed_price > 0 THEN bpt.bed_price ELSE NULL END) as min_bed_price, ROUND(AVG(CASE WHEN r.rating > 0 THEN r.rating ELSE NULL END), 1) as avg_rating, COUNT(r.review_id) as review_count, bp.created_at, bp.updated_at FROM beach_places bp LEFT JOIN companies c ON bp.company_id = c.company_id LEFT JOIN countries co ON bp.country_id = co.country_id LEFT JOIN ( SELECT beach_place_id, COUNT(*) as booking_count FROM bookings WHERE status IN ('confirmed', 'completed') GROUP BY beach_place_id ) booking_counts ON bp.beach_place_id = booking_counts.beach_place_id LEFT JOIN ( SELECT DISTINCT ON (beach_place_id) beach_place_id, photo_path FROM beach_places_photos WHERE photo_primary = true ORDER BY beach_place_id, sort_order ) bpp ON bp.beach_place_id = bpp.beach_place_id LEFT JOIN ( SELECT DISTINCT ON (beach_place_id) beach_place_id, price, currency_id FROM beach_places_schedules WHERE from_date <= CURRENT_DATE AND to_date >= CURRENT_DATE ORDER BY beach_place_id, from_date ) bps ON bp.beach_place_id = bps.beach_place_id LEFT JOIN currencies cur ON bps.currency_id = cur.currency_id LEFT JOIN beach_place_terrains bpt ON bp.beach_place_id = bpt.beach_place_id LEFT JOIN reviews r ON bp.beach_place_id = r.beach_place_id AND r.is_approved = true WHERE bp.enable_beach = true """ # Build WHERE conditions conditions = [] params = {} if search: conditions.append("(bp.beach_name ILIKE :search OR bp.address ILIKE :search OR bp.city ILIKE :search OR c.company_name ILIKE :search)") params['search'] = f'%{search}%' if country: conditions.append("co.country_name ILIKE :country") params['country'] = f'%{country}%' if city: conditions.append("bp.city ILIKE :city") params['city'] = f'%{city}%' # Add filter conditions if filter_type == 'popular': conditions.append("COALESCE(booking_counts.booking_count, 0) >= 5") elif filter_type == 'latest': conditions.append("bp.created_at >= CURRENT_DATE - INTERVAL '30 days'") elif filter_type == 'tropical': conditions.append("(co.country_name IN ('Maldives', 'Thailand', 'Indonesia', 'Philippines') OR bp.address ILIKE '%tropical%')") elif filter_type == 'mediterranean': conditions.append("(co.country_name IN ('Greece', 'Italy', 'Spain', 'Turkey', 'Cyprus') OR bp.address ILIKE '%mediterranean%')") # Add conditions to query if conditions: base_query += " AND " + " AND ".join(conditions) # Add GROUP BY clause for aggregation base_query += """ GROUP BY bp.beach_place_id, bp.beach_name, bp.address, bp.city, bp.beach_information, c.company_name, co.country_name, booking_counts.booking_count, bpp.photo_path, bps.price, cur.currency_code, bp.created_at, bp.updated_at """ # Add ordering if filter_type == 'popular': base_query += " ORDER BY popularity DESC, bp.beach_name" elif filter_type == 'latest': base_query += " ORDER BY bp.created_at DESC, bp.beach_name" else: base_query += " ORDER BY popularity DESC, bp.beach_name" # Get total count for pagination count_query = f"SELECT COUNT(*) FROM ({base_query}) as count_query" total_result = db.execute(text(count_query), params).fetchone() total_count = total_result[0] if total_result else 0 # Add pagination paginated_query = base_query + f" LIMIT {per_page} OFFSET {actual_offset}" # Execute query beaches = db.execute(text(paginated_query), params).fetchall() # Check if there are more pages has_more = (actual_offset + per_page) < total_count # Format the results with same structure as popular beaches for beach in beaches: # Get truncated description (2 lines max) description = beach.beach_information or 'Experience stunning beauty and crystal clear waters.' if len(description) > 120: # Approximate 2 lines description = description[:120].rsplit(' ', 1)[0] + '...' # Determine price display price_display = "N/A" if beach.min_bed_price and beach.min_bed_price > 0: currency_symbol = '€' if (beach.currency_code and beach.currency_code == 'EUR') else '$' price_display = f"from {currency_symbol}{int(beach.min_bed_price)} / per day" # Build location string from real data location_parts = [] if beach.city: location_parts.append(beach.city) if beach.country_name: location_parts.append(beach.country_name) location = ', '.join(location_parts) if location_parts else (beach.address or 'Beautiful Location') beach_data = { 'id': str(beach.beach_place_id), 'name': beach.beach_name or 'Beach', 'description': description, 'location': location, 'company': beach.company_name or 'Premium Beach', 'country': beach.country_name or '', 'popularity': beach.popularity or 0, 'image': beach.photo_path or '/static/img/tour_1.jpg', 'price_display': price_display, 'min_bed_price': float(beach.min_bed_price) if beach.min_bed_price else None, 'price': float(beach.price) if beach.price else 0, 'currency': beach.currency_code or 'USD', 'rating': float(beach.avg_rating) if beach.avg_rating else 0, 'reviews': int(beach.review_count) if beach.review_count else 0, 'created_at': beach.created_at.isoformat() if beach.created_at else '', 'updated_at': beach.updated_at.isoformat() if beach.updated_at else '' } beaches_data.append(beach_data) db.close() return jsonify({ 'data': beaches_data, 'pagination': { 'page': page, 'per_page': per_page, 'offset': actual_offset, 'has_more': has_more, 'total': total_count }, 'filters': { 'filter': filter_type, 'search': search, 'country': country, 'city': city, 'category': category } }) except Exception as e: current_app.logger.error(f"Error in beaches grid API: {str(e)}") return jsonify({'error': str(e)}), 500 # More API routes will be added here... @api_bp.route('/popular-beaches') def get_popular_beaches(): """Get popular beaches based on location and bookings""" try: # Get country from request parameter (sent from frontend) user_country = request.args.get('country', '') from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker # Database connection from config import ADMIN_CONFIG DATABASE_URL = ADMIN_CONFIG['DATABASE_URL'] engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) db = SessionLocal() # Check if required tables exist beach_places_result = db.execute(text(""" SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'beach_places' ) """)).fetchone() beach_places_exists = beach_places_result[0] if beach_places_result else False popular_beaches = [] if beach_places_exists: # Updated to use the new main_bookings and beach_bookings tables country_beaches_query = """ SELECT bp.beach_place_id, bp.beach_name, bp.address, bp.city, bp.beach_information, c.company_name, co.country_name, COALESCE(booking_counts.booking_count, 0) as popularity, bpp.photo_path, bps.price, cur.currency_code, MIN(CASE WHEN bpt.bed_price > 0 THEN bpt.bed_price ELSE NULL END) as min_bed_price, ROUND(AVG(CASE WHEN r.rating > 0 THEN r.rating ELSE NULL END), 1) as avg_rating, COUNT(r.review_id) as review_count FROM beach_places bp LEFT JOIN companies c ON bp.company_id = c.company_id LEFT JOIN countries co ON bp.country_id = co.country_id LEFT JOIN ( SELECT bb.beach_place_id, COUNT(*) as booking_count FROM beach_bookings bb JOIN main_bookings b ON bb.main_booking_id = b.booking_id WHERE b.status IN ('confirmed', 'completed') GROUP BY bb.beach_place_id ) booking_counts ON bp.beach_place_id = booking_counts.beach_place_id LEFT JOIN ( SELECT DISTINCT ON (beach_place_id) beach_place_id, photo_path FROM beach_places_photos WHERE photo_primary = true ORDER BY beach_place_id, sort_order ) bpp ON bp.beach_place_id = bpp.beach_place_id LEFT JOIN ( SELECT DISTINCT ON (beach_place_id) beach_place_id, price, currency_id FROM beach_places_schedules WHERE from_date <= CURRENT_DATE AND to_date >= CURRENT_DATE ORDER BY beach_place_id, from_date ) bps ON bp.beach_place_id = bps.beach_place_id LEFT JOIN currencies cur ON bps.currency_id = cur.currency_id LEFT JOIN beach_place_terrains bpt ON bp.beach_place_id = bpt.beach_place_id LEFT JOIN reviews r ON bp.beach_place_id = r.beach_place_id AND r.is_approved = true WHERE bp.enable_beach = true GROUP BY bp.beach_place_id, bp.beach_name, bp.address, bp.city, bp.beach_information, c.company_name, co.country_name, booking_counts.booking_count, bpp.photo_path, bps.price, cur.currency_code """ if user_country: # Try to get beaches from user's country first country_specific_query = country_beaches_query.replace( "WHERE bp.enable_beach = true", "WHERE bp.enable_beach = true AND co.country_name = :country" ) country_specific_query += """ ORDER BY COALESCE(booking_counts.booking_count, 0) DESC, bp.beach_name LIMIT 10 """ country_beaches = db.execute(text(country_specific_query), {"country": user_country}).fetchall() popular_beaches = list(country_beaches) # If we don't have 10 beaches from user's country, fill with popular beaches from anywhere if len(popular_beaches) < 10: remaining_needed = 10 - len(popular_beaches) existing_ids = [str(beach.beach_place_id) for beach in popular_beaches] additional_beaches_query = country_beaches_query if existing_ids: # Build proper query with parameters placeholders = ','.join([f':beach_id_{i}' for i in range(len(existing_ids))]) additional_beaches_query += f" AND bp.beach_place_id NOT IN ({placeholders})" additional_beaches_query += " ORDER BY COALESCE(booking_counts.booking_count, 0) DESC, bp.beach_name LIMIT :limit" params = {"limit": remaining_needed} if existing_ids: # Create a dictionary with indexed parameters param_dict = {"limit": int(remaining_needed)} for i, beach_id in enumerate(existing_ids): param_dict[f"beach_id_{i}"] = int(beach_id) additional_beaches = db.execute(text(additional_beaches_query), param_dict).fetchall() else: additional_beaches = db.execute(text(additional_beaches_query), params).fetchall() popular_beaches.extend(additional_beaches) # If still not enough beaches, get random ones if len(popular_beaches) < 10: remaining_needed = 10 - len(popular_beaches) existing_ids = [str(beach.beach_place_id) for beach in popular_beaches] random_beaches_query = country_beaches_query if existing_ids: # Build proper query with parameters placeholders = ','.join([f':beach_id_{i}' for i in range(len(existing_ids))]) random_beaches_query += f" AND bp.beach_place_id NOT IN ({placeholders})" random_beaches_query += " ORDER BY RANDOM() LIMIT :limit" params = {"limit": remaining_needed} if existing_ids: # Create a dictionary with indexed parameters param_dict = {"limit": int(remaining_needed)} for i, beach_id in enumerate(existing_ids): param_dict[f"beach_id_{i}"] = int(beach_id) random_beaches = db.execute(text(random_beaches_query), param_dict).fetchall() else: random_beaches = db.execute(text(random_beaches_query), params).fetchall() popular_beaches.extend(random_beaches) db.close() # Format the response beaches_data = [] for beach in popular_beaches: # Get truncated description (2 lines max) description = beach.beach_information or 'Experience stunning beauty and crystal clear waters.' if len(description) > 120: # Approximate 2 lines description = description[:120].rsplit(' ', 1)[0] + '...' # Determine price display price_display = "N/A" if beach.min_bed_price and beach.min_bed_price > 0: currency_symbol = '€' if (beach.currency_code and beach.currency_code == 'EUR') else '$' price_display = f"from {currency_symbol}{int(beach.min_bed_price)} / per day" beach_data = { 'id': str(beach.beach_place_id), 'name': beach.beach_name, 'location': f"{beach.city}, {beach.country_name}" if beach.city and beach.country_name else beach.address or 'Beautiful Location', 'company': beach.company_name or 'Premium Beach', 'popularity': beach.popularity or 0, 'image': beach.photo_path or '/static/img/tour_1.jpg', 'description': description, 'price_display': price_display, 'min_bed_price': float(beach.min_bed_price) if beach.min_bed_price else None, 'currency': beach.currency_code or 'USD', 'rating': float(beach.avg_rating) if beach.avg_rating else 0, 'reviews': int(beach.review_count) if beach.review_count else 0 } beaches_data.append(beach_data) return {'success': True, 'beaches': beaches_data} except Exception as e: print(f"Error getting popular beaches: {e}") # Return empty result on error - no fake data return {'success': True, 'beaches': []} @api_bp.route('/popular-markets') def get_popular_markets(): """Get popular markets based on location and bookings""" try: # Get country from request parameter (sent from frontend) user_country = request.args.get('country', '') from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker # Database connection from config import ADMIN_CONFIG DATABASE_URL = ADMIN_CONFIG['DATABASE_URL'] engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) db = SessionLocal() # Check if required tables exist markets_check = db.execute(text(""" SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'markets' ) """)).fetchone() markets_exists = markets_check[0] if markets_check else False markets_data = [] if markets_exists: # Updated query to include currency information for price display # Fixed: removed reference to non-existent m.currency_id column country_markets_query = """ SELECT m.market_id, m.market_name, m.address, m.city, m.description, c.company_name, co.country_name, COALESCE(booking_counts.booking_count, 0) as popularity, mp.photo_path, ROUND(AVG(CASE WHEN r.rating > 0 THEN r.rating ELSE NULL END), 1) as avg_rating, COUNT(r.review_id) as review_count, m.min_order_amount FROM markets m LEFT JOIN companies c ON m.company_id = c.company_id LEFT JOIN countries co ON m.country_id = co.country_id LEFT JOIN ( SELECT mb.market_id, COUNT(*) as booking_count FROM market_bookings mb JOIN main_bookings b ON mb.main_booking_id = b.booking_id WHERE b.status IN ('confirmed', 'completed') GROUP BY mb.market_id ) booking_counts ON m.market_id = booking_counts.market_id LEFT JOIN ( SELECT DISTINCT ON (market_id) market_id, photo_path FROM market_photos ORDER BY market_id, created_at ) mp ON m.market_id = mp.market_id LEFT JOIN beach_places bp ON m.beach_place_id = bp.beach_place_id LEFT JOIN reviews r ON bp.beach_place_id = r.beach_place_id AND r.is_approved = true WHERE m.is_active = true """ popular_markets = [] # First try to get markets from the user's country ordered by booking popularity if user_country: country_specific_query = country_markets_query + " AND co.country_name = :country" country_specific_query += """ GROUP BY m.market_id, m.market_name, m.address, m.city, m.description, c.company_name, co.country_name, booking_counts.booking_count, mp.photo_path, m.min_order_amount ORDER BY COALESCE(booking_counts.booking_count, 0) DESC, m.market_name LIMIT 10 """ country_markets = db.execute(text(country_specific_query), {"country": user_country}).fetchall() popular_markets = list(country_markets) # If we don't have 10 markets from user's country, fill with popular markets from anywhere if len(popular_markets) < 10: remaining_needed = 10 - len(popular_markets) existing_ids = [str(market.market_id) for market in popular_markets] additional_markets_query = country_markets_query if existing_ids: # Build proper query with parameters placeholders = ','.join([f':market_id_{i}' for i in range(len(existing_ids))]) additional_markets_query += f" AND m.market_id NOT IN ({placeholders})" additional_markets_query += """ GROUP BY m.market_id, m.market_name, m.address, m.city, m.description, c.company_name, co.country_name, booking_counts.booking_count, mp.photo_path, m.min_order_amount ORDER BY COALESCE(booking_counts.booking_count, 0) DESC, m.market_name LIMIT :limit """ params = {"limit": int(remaining_needed)} if existing_ids: # Create a dictionary with indexed parameters param_dict = {"limit": int(remaining_needed)} for i, market_id in enumerate(existing_ids): param_dict[f"market_id_{i}"] = int(market_id) additional_markets = db.execute(text(additional_markets_query), param_dict).fetchall() else: additional_markets = db.execute(text(additional_markets_query), params).fetchall() popular_markets.extend(additional_markets) # If still not enough markets, get random ones if len(popular_markets) < 10: remaining_needed = 10 - len(popular_markets) existing_ids = [str(market.market_id) for market in popular_markets] random_markets_query = country_markets_query if existing_ids: # Build proper query with parameters placeholders = ','.join([f':market_id_{i}' for i in range(len(existing_ids))]) random_markets_query += f" AND m.market_id NOT IN ({placeholders})" random_markets_query += """ GROUP BY m.market_id, m.market_name, m.address, m.city, m.description, c.company_name, co.country_name, booking_counts.booking_count, mp.photo_path, m.min_order_amount ORDER BY RANDOM() LIMIT :limit """ params = {"limit": int(remaining_needed)} if existing_ids: # Create a dictionary with indexed parameters param_dict = {"limit": int(remaining_needed)} for i, market_id in enumerate(existing_ids): param_dict[f"market_id_{i}"] = int(market_id) random_markets = db.execute(text(random_markets_query), param_dict).fetchall() else: random_markets = db.execute(text(random_markets_query), params).fetchall() popular_markets.extend(random_markets) # Format the response with real price display for market in popular_markets: # Get truncated description (2 lines max) description = market.description or 'Experience authentic local markets and shopping.' if len(description) > 120: # Approximate 2 lines description = description[:120].rsplit(' ', 1)[0] + '...' # Determine price display - using default currency symbol since we don't have currency_id price_display = "N/A" if market.min_order_amount and market.min_order_amount > 0: # Default to € for now, will be updated when currency_id column is added currency_symbol = '€' price_display = f"from {currency_symbol}{market.min_order_amount:.0f}" market_data = { 'id': str(market.market_id), 'name': market.market_name, 'location': f"{market.city}, {market.country_name}" if market.city and market.country_name else market.address or 'Local Market', 'company': market.company_name or 'Local Market', 'popularity': market.popularity or 0, 'image': market.photo_path or '/static/img/market_1.jpg', 'description': description, 'price_display': price_display, 'rating': float(market.avg_rating) if market.avg_rating else 0, 'reviews': int(market.review_count) if market.review_count else 0 } markets_data.append(market_data) db.close() return jsonify({'success': True, 'markets': markets_data}) except Exception as e: print(f"Error getting popular markets: {e}") # Return empty result on error - no fake data return jsonify({'success': True, 'markets': []}) @api_bp.route('/popular-adventures') def get_popular_adventures(): """Get popular adventures based on location and bookings""" try: # Get country from request parameter (sent from frontend) user_country = request.args.get('country', '') from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker # Database connection from config import ADMIN_CONFIG DATABASE_URL = ADMIN_CONFIG['DATABASE_URL'] engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) db = SessionLocal() # Check if required tables exist adventures_exists_result = db.execute(text(""" SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'adventures' ) """)).fetchone() adventures_exists = adventures_exists_result[0] if adventures_exists_result else False main_bookings_exists_result = db.execute(text(""" SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'main_bookings' ) """)).fetchone() main_bookings_exists = main_bookings_exists_result[0] if main_bookings_exists_result else False countries_exists_result = db.execute(text(""" SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'countries' ) """)).fetchone() countries_exists = countries_exists_result[0] if countries_exists_result else False popular_adventures = [] if adventures_exists: # First try to get adventures from the user's country ordered by booking popularity # Updated to use the new main_bookings and adventure_bookings tables country_adventures_query = """ SELECT a.adventure_id, a.adventure_name, a.address, a.city, a.description, c.company_name, co.country_name, COALESCE(booking_counts.booking_count, 0) as popularity, ap.photo_path, a.price, cur.currency_symbol, ROUND(AVG(CASE WHEN r.rating > 0 THEN r.rating ELSE NULL END), 1) as avg_rating, COUNT(r.review_id) as review_count FROM adventures a LEFT JOIN companies c ON a.company_id = c.company_id LEFT JOIN countries co ON a.country_id = co.country_id LEFT JOIN currencies cur ON a.currency_id = cur.currency_id LEFT JOIN ( SELECT ab.adventure_id, COUNT(*) as booking_count FROM adventure_bookings ab JOIN main_bookings b ON ab.main_booking_id = b.booking_id WHERE b.status IN ('confirmed', 'completed') GROUP BY ab.adventure_id ) booking_counts ON a.adventure_id = booking_counts.adventure_id LEFT JOIN ( SELECT DISTINCT ON (adventure_id) adventure_id, photo_path FROM adventure_photos ORDER BY adventure_id, created_at ) ap ON a.adventure_id = ap.adventure_id LEFT JOIN beach_places bp ON a.beach_place_id = bp.beach_place_id LEFT JOIN reviews r ON bp.beach_place_id = r.beach_place_id AND r.is_approved = true WHERE a.is_active = true GROUP BY a.adventure_id, a.adventure_name, a.address, a.city, a.description, c.company_name, co.country_name, booking_counts.booking_count, ap.photo_path, a.price, cur.currency_symbol """ if user_country and countries_exists: # Try to get adventures from user's country first country_specific_query = country_adventures_query.replace( "WHERE a.is_active = true", "WHERE a.is_active = true AND co.country_name = :country" ) country_specific_query += """ ORDER BY COALESCE(booking_counts.booking_count, 0) DESC, a.adventure_name LIMIT 10 """ country_adventures = db.execute(text(country_specific_query), {"country": user_country}).fetchall() popular_adventures = list(country_adventures) # If we don't have 10 adventures from user's country, fill with popular adventures from anywhere if len(popular_adventures) < 10: remaining_needed = 10 - len(popular_adventures) existing_ids = [str(adventure.adventure_id) for adventure in popular_adventures] additional_adventures_query = country_adventures_query if existing_ids: placeholders = ','.join([f':adventure_id_{i}' for i in range(len(existing_ids))]) additional_adventures_query += f" AND a.adventure_id NOT IN ({placeholders})" additional_adventures_query += " ORDER BY COALESCE(booking_counts.booking_count, 0) DESC, a.adventure_name LIMIT :limit" params = {"limit": int(remaining_needed)} if existing_ids: # Create a dictionary with indexed parameters param_dict = {"limit": int(remaining_needed)} for i, adventure_id in enumerate(existing_ids): param_dict[f"adventure_id_{i}"] = int(adventure_id) additional_adventures = db.execute(text(additional_adventures_query), param_dict).fetchall() else: additional_adventures = db.execute(text(additional_adventures_query), params).fetchall() popular_adventures.extend(additional_adventures) # If still not enough adventures, get random ones if len(popular_adventures) < 10: remaining_needed = 10 - len(popular_adventures) existing_ids = [str(adventure.adventure_id) for adventure in popular_adventures] random_adventures_query = country_adventures_query if existing_ids: placeholders = ','.join([f':adventure_id_{i}' for i in range(len(existing_ids))]) random_adventures_query += f" AND a.adventure_id NOT IN ({placeholders})" random_adventures_query += " ORDER BY RANDOM() LIMIT :limit" params = {"limit": int(remaining_needed)} if existing_ids: # Create a dictionary with indexed parameters param_dict = {"limit": int(remaining_needed)} for i, adventure_id in enumerate(existing_ids): param_dict[f"adventure_id_{i}"] = int(adventure_id) random_adventures = db.execute(text(random_adventures_query), param_dict).fetchall() else: random_adventures = db.execute(text(random_adventures_query), params).fetchall() popular_adventures.extend(random_adventures) db.close() # Format the response adventures_data = [] for adventure in popular_adventures: # Get truncated description (2 lines max) description = adventure.description or 'Experience thrilling adventures and outdoor activities.' if len(description) > 120: # Approximate 2 lines description = description[:120].rsplit(' ', 1)[0] + '...' # Determine price display with real currency symbol price_display = "N/A" if adventure.price and adventure.price > 0: # Get currency symbol from the adventure record currency_symbol = getattr(adventure, 'currency_symbol', None) # If no currency symbol, try to default to € for Greece or $ for others if not currency_symbol: # Default to € for Greek adventures, $ for others if hasattr(adventure, 'country_name') and adventure.country_name and 'Greece' in adventure.country_name: currency_symbol = '€' else: currency_symbol = '$' price_display = f"from {currency_symbol}{int(adventure.price)} / per person" adventure_data = { 'id': str(adventure.adventure_id), 'name': adventure.adventure_name, 'location': f"{adventure.city}, {adventure.country_name}" if adventure.city and adventure.country_name else adventure.address or 'Adventure Location', 'company': adventure.company_name or 'Adventure Company', 'popularity': adventure.popularity or 0, 'image': adventure.photo_path or '/static/img/adventure_1.jpg', 'description': description, 'price_display': price_display, 'price': float(adventure.price) if adventure.price else 0, 'rating': float(adventure.avg_rating) if adventure.avg_rating else 0, 'reviews': int(adventure.review_count) if adventure.review_count else 0 } adventures_data.append(adventure_data) return jsonify({'success': True, 'adventures': adventures_data}) except Exception as e: print(f"Error getting popular adventures: {e}") # Return empty result on error - no fake data return jsonify({'success': True, 'adventures': []}) @api_bp.route('/adventures-grid-old') def get_adventures_grid(): """Get adventures data for the grid page with filtering and pagination (old version)""" try: # Get request parameters - support both page and offset based pagination page = int(request.args.get('page', 1)) offset = int(request.args.get('offset', 0)) # New offset parameter per_page = int(request.args.get('per_page', 12)) filter_type = request.args.get('filter', 'all') search = request.args.get('search', '') country = request.args.get('country', '') city = request.args.get('city', '') category = request.args.get('category', '') # Use offset if provided, otherwise calculate from page if offset > 0: actual_offset = offset else: actual_offset = (page - 1) * per_page from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker # Database connection from config import ADMIN_CONFIG DATABASE_URL = ADMIN_CONFIG['DATABASE_URL'] engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) db = SessionLocal() # Check if required tables exist adventures_check = db.execute(text(""" SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'adventures' ) """)).fetchone() adventures_exists = adventures_check[0] if adventures_check else False adventures_data = [] has_more = False total_count = 0 if adventures_exists: # Build the enhanced base query with same structure as popular-adventures base_query = """ SELECT a.adventure_id, a.adventure_name, a.address, a.city, a.description, a.price, c.company_name, co.country_name, COALESCE(booking_counts.booking_count, 0) as popularity, ap.photo_path, ROUND(AVG(CASE WHEN r.rating > 0 THEN r.rating ELSE NULL END), 1) as avg_rating, COUNT(r.review_id) as review_count, a.created_at, a.updated_at FROM adventures a LEFT JOIN companies c ON a.company_id = c.company_id LEFT JOIN countries co ON a.country_id = co.country_id LEFT JOIN ( SELECT ab.adventure_id, COUNT(*) as booking_count FROM adventure_bookings ab JOIN main_bookings b ON ab.main_booking_id = b.booking_id WHERE b.status IN ('confirmed', 'completed') GROUP BY ab.adventure_id ) booking_counts ON a.adventure_id = booking_counts.adventure_id LEFT JOIN ( SELECT DISTINCT ON (adventure_id) adventure_id, photo_path FROM adventure_photos ORDER BY adventure_id, created_at ) ap ON a.adventure_id = ap.adventure_id LEFT JOIN beach_places bp ON a.beach_place_id = bp.beach_place_id LEFT JOIN reviews r ON bp.beach_place_id = r.beach_place_id AND r.is_approved = true WHERE a.is_active = true GROUP BY a.adventure_id, a.adventure_name, a.address, a.city, a.description, a.price, c.company_name, co.country_name, booking_counts.booking_count, ap.photo_path, a.created_at, a.updated_at """ # Build WHERE conditions conditions = [] params = {} if search: conditions.append("(a.adventure_name ILIKE :search OR a.description ILIKE :search OR a.city ILIKE :search OR c.company_name ILIKE :search)") params['search'] = f'%{search}%' if country: conditions.append("co.country_name ILIKE :country") params['country'] = f'%{country}%' if city: conditions.append("a.city ILIKE :city") params['city'] = f'%{city}%' # Add filter conditions if filter_type == 'popular': conditions.append("COALESCE(booking_counts.booking_count, 0) >= 5") elif filter_type == 'latest': conditions.append("a.created_at >= CURRENT_DATE - INTERVAL '30 days'") # Add conditions to query if conditions: base_query += " AND " + " AND ".join(conditions) # Add ordering if filter_type == 'popular': base_query += " ORDER BY popularity DESC, a.adventure_name" elif filter_type == 'latest': base_query += " ORDER BY a.created_at DESC, a.adventure_name" else: base_query += " ORDER BY popularity DESC, a.adventure_name" # Get total count for pagination count_query = f"SELECT COUNT(*) FROM ({base_query}) as count_query" total_result = db.execute(text(count_query), params).fetchone() total_count = total_result[0] if total_result else 0 # Add pagination paginated_query = base_query + f" LIMIT {per_page} OFFSET {actual_offset}" # Execute query adventures = db.execute(text(paginated_query), params).fetchall() # Check if there are more pages has_more = (actual_offset + per_page) < total_count # Format the results with same structure as popular adventures for adventure in adventures: # Get truncated description (2 lines max) description = adventure.description or 'Experience thrilling adventures and outdoor activities.' if len(description) > 120: # Approximate 2 lines description = description[:120].rsplit(' ', 1)[0] + '...' # Determine price display price_display = "N/A" if adventure.price and adventure.price > 0: price_display = f"from ${int(adventure.price)} / per person" # Build location string from real data location_parts = [] if adventure.city: location_parts.append(adventure.city) if adventure.country_name: location_parts.append(adventure.country_name) location = ', '.join(location_parts) if location_parts else (adventure.address or 'Adventure Location') adventure_data = { 'id': str(adventure.adventure_id), 'name': adventure.adventure_name or 'Adventure', 'description': description, 'location': location, 'company': adventure.company_name or 'Adventure Company', 'country': adventure.country_name or '', 'popularity': adventure.popularity or 0, 'image': adventure.photo_path or '/static/img/adventure_1.jpg', 'price_display': price_display, 'price': float(adventure.price) if adventure.price else 0, 'rating': float(adventure.avg_rating) if adventure.avg_rating else 0, 'reviews': int(adventure.review_count) if adventure.review_count else 0, 'created_at': adventure.created_at.isoformat() if adventure.created_at else '', 'updated_at': adventure.updated_at.isoformat() if adventure.updated_at else '' } adventures_data.append(adventure_data) db.close() return jsonify({ 'data': adventures_data, 'pagination': { 'page': page, 'per_page': per_page, 'offset': actual_offset, 'has_more': has_more, 'total': total_count }, 'filters': { 'filter': filter_type, 'search': search, 'country': country, 'city': city, 'category': category } }) except Exception as e: current_app.logger.error(f"Error getting adventures grid: {e}") return jsonify({'success': True, 'adventures': [], 'has_more': False}) @api_bp.route('/markets-grid') def get_markets_grid(): """Get markets data for the grid page with filtering and pagination""" try: # Get request parameters - support both page and offset based pagination page = int(request.args.get('page', 1)) offset = int(request.args.get('offset', 0)) # New offset parameter per_page = int(request.args.get('per_page', 12)) filter_type = request.args.get('filter', 'all') search = request.args.get('search', '') country = request.args.get('country', '') city = request.args.get('city', '') category = request.args.get('category', '') # Use offset if provided, otherwise calculate from page if offset > 0: actual_offset = offset else: actual_offset = (page - 1) * per_page from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker # Database connection from config import ADMIN_CONFIG DATABASE_URL = ADMIN_CONFIG['DATABASE_URL'] engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) db = SessionLocal() # Check if required tables exist markets_check = db.execute(text(""" SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'markets' ) """)).fetchone() markets_exists = markets_check[0] if markets_check else False markets_data = [] has_more = False total_count = 0 if markets_exists: # Build the enhanced base query with same structure as popular-markets base_query = """ SELECT m.market_id, m.market_name, m.address, m.city, m.description, c.company_name, co.country_name, COALESCE(booking_counts.booking_count, 0) as popularity, mp.photo_path, ROUND(AVG(CASE WHEN r.rating > 0 THEN r.rating ELSE NULL END), 1) as avg_rating, COUNT(r.review_id) as review_count, m.created_at, m.updated_at FROM markets m LEFT JOIN companies c ON m.company_id = c.company_id LEFT JOIN countries co ON m.country_id = co.country_id LEFT JOIN ( SELECT mb.market_id, COUNT(*) as booking_count FROM market_bookings mb JOIN main_bookings b ON mb.main_booking_id = b.booking_id WHERE b.status IN ('confirmed', 'completed') GROUP BY mb.market_id ) booking_counts ON m.market_id = booking_counts.market_id LEFT JOIN ( SELECT DISTINCT ON (market_id) market_id, photo_path FROM market_photos ORDER BY market_id, created_at ) mp ON m.market_id = mp.market_id LEFT JOIN beach_places bp ON m.beach_place_id = bp.beach_place_id LEFT JOIN reviews r ON bp.beach_place_id = r.beach_place_id AND r.is_approved = true WHERE m.is_active = true GROUP BY m.market_id, m.market_name, m.address, m.city, m.description, c.company_name, co.country_name, booking_counts.booking_count, mp.photo_path, m.created_at, m.updated_at """ # Build WHERE conditions conditions = [] params = {} if search: conditions.append("(m.market_name ILIKE :search OR m.description ILIKE :search OR m.city ILIKE :search OR c.company_name ILIKE :search)") params['search'] = f'%{search}%' if country: conditions.append("co.country_name ILIKE :country") params['country'] = f'%{country}%' if city: conditions.append("m.city ILIKE :city") params['city'] = f'%{city}%' # Add filter conditions if filter_type == 'popular': conditions.append("COALESCE(booking_counts.booking_count, 0) >= 5") elif filter_type == 'latest': conditions.append("m.created_at >= CURRENT_DATE - INTERVAL '30 days'") # Add conditions to query if conditions: base_query += " AND " + " AND ".join(conditions) # Add ordering if filter_type == 'popular': base_query += " ORDER BY popularity DESC, m.market_name" elif filter_type == 'latest': base_query += " ORDER BY m.created_at DESC, m.market_name" else: base_query += " ORDER BY popularity DESC, m.market_name" # Get total count for pagination count_query = f"SELECT COUNT(*) FROM ({base_query}) as count_query" total_result = db.execute(text(count_query), params).fetchone() total_count = total_result[0] if total_result else 0 # Add pagination paginated_query = base_query + f" LIMIT {per_page} OFFSET {actual_offset}" # Execute query markets = db.execute(text(paginated_query), params).fetchall() # Check if there are more pages has_more = (actual_offset + per_page) < total_count # Format the results with same structure as popular markets for market in markets: # Get truncated description (2 lines max) description = market.description or 'Experience authentic local markets and shopping.' if len(description) > 120: # Approximate 2 lines description = description[:120].rsplit(' ', 1)[0] + '...' # Build location string from real data location_parts = [] if market.city: location_parts.append(market.city) if market.country_name: location_parts.append(market.country_name) location = ', '.join(location_parts) if location_parts else (market.address or 'Local Market') market_data = { 'id': str(market.market_id), 'name': market.market_name or 'Market', 'description': description, 'location': location, 'company': market.company_name or 'Local Market', 'country': market.country_name or '', 'popularity': market.popularity or 0, 'image': market.photo_path or '/static/img/market_1.jpg', 'rating': float(market.avg_rating) if market.avg_rating else 0, 'reviews': int(market.review_count) if market.review_count else 0, 'created_at': market.created_at.isoformat() if market.created_at else '', 'updated_at': market.updated_at.isoformat() if market.updated_at else '' } markets_data.append(market_data) db.close() return jsonify({ 'data': markets_data, 'pagination': { 'page': page, 'per_page': per_page, 'offset': actual_offset, 'has_more': has_more, 'total': total_count }, 'filters': { 'filter': filter_type, 'search': search, 'country': country, 'city': city, 'category': category } }) except Exception as e: current_app.logger.error(f"Error in markets grid API: {str(e)}") return jsonify({'error': str(e)}), 500 @api_bp.route('/popular-adventures-real') def get_popular_adventures_real(): """Get popular adventures for homepage carousel""" try: from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker # Database connection from config import ADMIN_CONFIG DATABASE_URL = ADMIN_CONFIG['DATABASE_URL'] engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) db = SessionLocal() # Check if required tables exist adventures_check = db.execute(text(""" SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'adventures' ) """)).fetchone() adventures_exists = adventures_check[0] if adventures_check else False adventures_data = [] if adventures_exists: # Query to get popular adventures with currency information adventures_result = db.execute(text(""" SELECT a.adventure_id, a.adventure_name, a.description, a.address, a.city, a.price, a.currency_id, c.company_name, co.country_name, COALESCE(booking_counts.booking_count, 0) as popularity, ap.photo_path, ROUND(AVG(CASE WHEN r.rating > 0 THEN r.rating ELSE NULL END), 1) as avg_rating, COUNT(r.review_id) as review_count, cur.currency_symbol FROM adventures a LEFT JOIN companies c ON a.company_id = c.company_id LEFT JOIN countries co ON a.country_id = co.country_id LEFT JOIN currencies cur ON a.currency_id = cur.currency_id LEFT JOIN ( SELECT ab.adventure_id, COUNT(*) as booking_count FROM adventure_bookings ab JOIN main_bookings b ON ab.main_booking_id = b.booking_id WHERE b.status IN ('confirmed', 'completed') GROUP BY ab.adventure_id ) booking_counts ON a.adventure_id = booking_counts.adventure_id LEFT JOIN ( SELECT DISTINCT ON (adventure_id) adventure_id, photo_path FROM adventure_photos ORDER BY adventure_id, created_at ) ap ON a.adventure_id = ap.adventure_id LEFT JOIN beach_places bp ON a.beach_place_id = bp.beach_place_id LEFT JOIN reviews r ON bp.beach_place_id = r.beach_place_id AND r.is_approved = true WHERE a.is_active = true GROUP BY a.adventure_id, a.adventure_name, a.description, a.address, a.city, a.price, a.currency_id, c.company_name, co.country_name, booking_counts.booking_count, ap.photo_path, cur.currency_symbol ORDER BY popularity DESC, avg_rating DESC NULLS LAST LIMIT 12 """)).fetchall() for adventure in adventures_result: # Get truncated description (2 lines max) description = adventure.description or 'Experience thrilling adventures and outdoor activities.' if len(description) > 120: # Approximate 2 lines description = description[:120].rsplit(' ', 1)[0] + '...' # Determine price display with real currency symbol price_display = "N/A" if adventure.price and adventure.price > 0: currency_symbol = adventure.currency_symbol or '$' # Fallback to $ if no currency symbol price_display = f"from {currency_symbol}{int(adventure.price)} / per person" adventure_data = { 'id': str(adventure.adventure_id), 'name': adventure.adventure_name, 'location': f"{adventure.city}, {adventure.country_name}" if adventure.city and adventure.country_name else adventure.address or 'Adventure Location', 'company': adventure.company_name or 'Adventure Company', 'popularity': adventure.popularity or 0, 'image': adventure.photo_path or '/static/img/adventure_1.jpg', 'description': description, 'price_display': price_display, 'price': float(adventure.price) if adventure.price else 0, 'rating': float(adventure.avg_rating) if adventure.avg_rating else 0, 'reviews': int(adventure.review_count) if adventure.review_count else 0 } adventures_data.append(adventure_data) db.close() return jsonify({'success': True, 'adventures': adventures_data}) except Exception as e: print(f"Error getting popular adventures: {e}") # Return empty result on error - no fake data return jsonify({'success': True, 'adventures': []}) @api_bp.route('/adventures-grid') def get_adventures_grid_updated(): """Get adventures data for the grid page with filtering and pagination (updated version)""" try: # Get request parameters - support both page and offset based pagination page = int(request.args.get('page', 1)) offset = int(request.args.get('offset', 0)) # New offset parameter per_page = int(request.args.get('per_page', 12)) filter_type = request.args.get('filter', 'all') search = request.args.get('search', '') country = request.args.get('country', '') city = request.args.get('city', '') category = request.args.get('category', '') # Use offset if provided, otherwise calculate from page if offset > 0: actual_offset = offset else: actual_offset = (page - 1) * per_page from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker # Database connection from config import ADMIN_CONFIG DATABASE_URL = ADMIN_CONFIG['DATABASE_URL'] engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) db = SessionLocal() # Check if required tables exist adventures_check = db.execute(text(""" SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'adventures' ) """)).fetchone() adventures_exists = adventures_check[0] if adventures_check else False adventures_data = [] has_more = False total_count = 0 if adventures_exists: # Build the enhanced base query with same structure as popular-adventures base_query = """ SELECT a.adventure_id, a.adventure_name, a.address, a.city, a.description, a.price, c.company_name, co.country_name, COALESCE(booking_counts.booking_count, 0) as popularity, ap.photo_path, ROUND(AVG(CASE WHEN r.rating > 0 THEN r.rating ELSE NULL END), 1) as avg_rating, COUNT(r.review_id) as review_count, a.created_at, a.updated_at FROM adventures a LEFT JOIN companies c ON a.company_id = c.company_id LEFT JOIN countries co ON a.country_id = co.country_id LEFT JOIN ( SELECT ab.adventure_id, COUNT(*) as booking_count FROM adventure_bookings ab JOIN main_bookings b ON ab.main_booking_id = b.booking_id WHERE b.status IN ('confirmed', 'completed') GROUP BY ab.adventure_id ) booking_counts ON a.adventure_id = booking_counts.adventure_id LEFT JOIN ( SELECT DISTINCT ON (adventure_id) adventure_id, photo_path FROM adventure_photos ORDER BY adventure_id, created_at ) ap ON a.adventure_id = ap.adventure_id LEFT JOIN beach_places bp ON a.beach_place_id = bp.beach_place_id LEFT JOIN reviews r ON bp.beach_place_id = r.beach_place_id AND r.is_approved = true WHERE a.is_active = true GROUP BY a.adventure_id, a.adventure_name, a.address, a.city, a.description, a.price, c.company_name, co.country_name, booking_counts.booking_count, ap.photo_path, a.created_at, a.updated_at """ # Build WHERE conditions conditions = [] params = {} if search: conditions.append("(a.adventure_name ILIKE :search OR a.description ILIKE :search OR a.city ILIKE :search OR c.company_name ILIKE :search)") params['search'] = f'%{search}%' if country: conditions.append("co.country_name ILIKE :country") params['country'] = f'%{country}%' if city: conditions.append("a.city ILIKE :city") params['city'] = f'%{city}%' # Add filter conditions if filter_type == 'popular': conditions.append("COALESCE(booking_counts.booking_count, 0) >= 5") elif filter_type == 'latest': conditions.append("a.created_at >= CURRENT_DATE - INTERVAL '30 days'") # Add conditions to query if conditions: base_query += " AND " + " AND ".join(conditions) # Add ordering if filter_type == 'popular': base_query += " ORDER BY popularity DESC, a.adventure_name" elif filter_type == 'latest': base_query += " ORDER BY a.created_at DESC, a.adventure_name" else: base_query += " ORDER BY popularity DESC, a.adventure_name" # Get total count for pagination count_query = f"SELECT COUNT(*) FROM ({base_query}) as count_query" total_result = db.execute(text(count_query), params).fetchone() total_count = total_result[0] if total_result else 0 # Add pagination paginated_query = base_query + f" LIMIT {per_page} OFFSET {actual_offset}" # Execute query adventures = db.execute(text(paginated_query), params).fetchall() # Check if there are more pages has_more = (actual_offset + per_page) < total_count # Format the results with same structure as popular adventures for adventure in adventures: # Get truncated description (2 lines max) description = adventure.description or 'Experience thrilling adventures and outdoor activities.' if len(description) > 120: # Approximate 2 lines description = description[:120].rsplit(' ', 1)[0] + '...' # Determine price display price_display = "N/A" if adventure.price and adventure.price > 0: price_display = f"from ${int(adventure.price)} / per person" # Build location string from real data location_parts = [] if adventure.city: location_parts.append(adventure.city) if adventure.country_name: location_parts.append(adventure.country_name) location = ', '.join(location_parts) if location_parts else (adventure.address or 'Adventure Location') adventure_data = { 'id': str(adventure.adventure_id), 'name': adventure.adventure_name or 'Adventure', 'description': description, 'location': location, 'company': adventure.company_name or 'Adventure Company', 'country': adventure.country_name or '', 'popularity': adventure.popularity or 0, 'image': adventure.photo_path or '/static/img/adventure_1.jpg', 'price_display': price_display, 'price': float(adventure.price) if adventure.price else 0, 'rating': float(adventure.avg_rating) if adventure.avg_rating else 0, 'reviews': int(adventure.review_count) if adventure.review_count else 0, 'created_at': adventure.created_at.isoformat() if adventure.created_at else '', 'updated_at': adventure.updated_at.isoformat() if adventure.updated_at else '' } adventures_data.append(adventure_data) db.close() return jsonify({ 'data': adventures_data, 'pagination': { 'page': page, 'per_page': per_page, 'offset': actual_offset, 'has_more': has_more, 'total': total_count }, 'filters': { 'filter': filter_type, 'search': search, 'country': country, 'city': city, 'category': category } }) except Exception as e: current_app.logger.error(f"Error in adventures grid API: {str(e)}") return jsonify({'error': str(e)}), 500 @api_bp.route('/popular-restaurants') def get_popular_restaurants(): """Get popular restaurants based on location and bookings""" try: # Get country from request parameter user_country = request.args.get('country', '') from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker # Database connection from config import ADMIN_CONFIG DATABASE_URL = ADMIN_CONFIG['DATABASE_URL'] engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) db = SessionLocal() # Check if required tables exist restaurants_result = db.execute(text(""" SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'restaurants' ) """)).fetchone() restaurants_exists = restaurants_result[0] if restaurants_result else False popular_restaurants = [] if restaurants_exists: # Base query for restaurants with popularity data # Fixed to properly calculate popularity based on review count and average rating # Also fixed the review join to go through beach_places base_query = """ SELECT r.restaurant_id, r.restaurant_name, r.address, r.city, r.country_id, co.country_name, r.cuisine_type, r.description, COALESCE(restaurant_stats.review_count, 0) as popularity, rp.photo_path, COALESCE(ROUND(restaurant_stats.avg_rating, 1), 0) as avg_rating, COALESCE(restaurant_stats.review_count, 0) as review_count FROM restaurants r LEFT JOIN countries co ON r.country_id = co.country_id LEFT JOIN ( SELECT DISTINCT ON (restaurant_id) restaurant_id, photo_path FROM restaurant_photos WHERE is_primary = true ORDER BY restaurant_id, created_at ASC ) rp ON r.restaurant_id = rp.restaurant_id LEFT JOIN ( SELECT r.restaurant_id, COUNT(rev.review_id) as review_count, AVG(CASE WHEN rev.rating > 0 THEN rev.rating ELSE NULL END) as avg_rating FROM restaurants r LEFT JOIN beach_places bp ON r.beach_place_id = bp.beach_place_id LEFT JOIN reviews rev ON bp.beach_place_id = rev.beach_place_id AND rev.is_approved = true GROUP BY r.restaurant_id ) restaurant_stats ON r.restaurant_id = restaurant_stats.restaurant_id WHERE 1=1 """ if user_country: # Try to get restaurants from user's country first country_restaurants = db.execute(text(base_query + """ AND co.country_name = :country ORDER BY popularity DESC, r.restaurant_name LIMIT 10 """), {"country": user_country}).fetchall() popular_restaurants = list(country_restaurants) # If we don't have 10 restaurants from user's country, fill with popular restaurants from anywhere if len(popular_restaurants) < 10: remaining_needed = 10 - len(popular_restaurants) existing_ids = [str(r.restaurant_id) for r in popular_restaurants] additional_query = base_query if existing_ids: placeholders = ','.join([f':id_{i}' for i in range(len(existing_ids))]) additional_query += f" AND r.restaurant_id NOT IN ({placeholders})" additional_query += " ORDER BY popularity DESC, r.restaurant_name LIMIT :limit" params = {"limit": int(remaining_needed)} # Add existing IDs to parameters for i, existing_id in enumerate(existing_ids): params[f"id_{i}"] = int(existing_id) additional_restaurants = db.execute(text(additional_query), params).fetchall() popular_restaurants.extend(additional_restaurants) # If still not enough restaurants, get random ones if len(popular_restaurants) < 10: remaining_needed = 10 - len(popular_restaurants) existing_ids = [str(r.restaurant_id) for r in popular_restaurants] random_query = base_query if existing_ids: placeholders = ','.join([f':id_{i}' for i in range(len(existing_ids))]) random_query += f" AND r.restaurant_id NOT IN ({placeholders})" random_query += " ORDER BY RANDOM() LIMIT :limit" params = {"limit": int(remaining_needed)} # Add existing IDs to parameters for i, existing_id in enumerate(existing_ids): params[f"id_{i}"] = int(existing_id) random_restaurants = db.execute(text(random_query), params).fetchall() popular_restaurants.extend(random_restaurants) db.close() # Format the response restaurants_data = [] for restaurant in popular_restaurants: location_parts = [] if restaurant.city: location_parts.append(restaurant.city) if restaurant.country_name: location_parts.append(restaurant.country_name) location = ', '.join(location_parts) if location_parts else (restaurant.address or 'Great Location') restaurant_data = { 'id': str(restaurant.restaurant_id), 'name': restaurant.restaurant_name, 'location': location, 'cuisine_type': restaurant.cuisine_type or 'International', 'popularity': int(restaurant.popularity) if restaurant.popularity else 0, 'image': restaurant.photo_path or '/static/img/restaurant_default.jpg', 'rating': float(restaurant.avg_rating) if restaurant.avg_rating else 0, 'reviews': int(restaurant.review_count) if restaurant.review_count else 0, 'description': restaurant.description or f'Delicious {restaurant.cuisine_type or ""} cuisine' } restaurants_data.append(restaurant_data) return {'success': True, 'restaurants': restaurants_data} except Exception as e: print(f"Error getting popular restaurants: {e}") return {'success': True, 'restaurants': []} @api_bp.route('/restaurants/search') def search_restaurants(): """Search restaurants by menu items and other criteria""" try: from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker from config import ADMIN_CONFIG # Database connection DATABASE_URL = ADMIN_CONFIG['DATABASE_URL'] engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) db = SessionLocal() # Get search parameters search_query = request.args.get('search', '').strip() country = request.args.get('country', '').strip() cuisine_filter = request.args.get('cuisine', '').strip() page = int(request.args.get('page', 1)) per_page = int(request.args.get('per_page', 12)) # Base query - search restaurants by name, description, or menu items base_query = """ SELECT DISTINCT r.restaurant_id, r.restaurant_name as name, r.description, r.cuisine_type, r.city, r.address, r.phone, r.email, r.website, c.country_name, rp.photo_path, CASE WHEN ri.item_name IS NOT NULL THEN true ELSE false END as menu_match, ROUND(AVG(CASE WHEN rev.rating > 0 THEN rev.rating ELSE NULL END), 1) as avg_rating, COUNT(rev.review_id) as review_count FROM restaurants r LEFT JOIN countries c ON r.country_id = c.country_id LEFT JOIN restaurant_photos rp ON r.restaurant_id = rp.restaurant_id AND rp.is_primary = true LEFT JOIN restaurant_items ri ON r.restaurant_id = ri.restaurant_id LEFT JOIN beach_places bp ON r.beach_place_id = bp.beach_place_id LEFT JOIN reviews rev ON bp.beach_place_id = rev.beach_place_id AND rev.is_approved = true WHERE r.is_active = true """ params = {} where_conditions = [] # Search in restaurant name, description, or menu items if search_query: where_conditions.append(""" (LOWER(r.restaurant_name) LIKE LOWER(:search) OR LOWER(r.description) LIKE LOWER(:search) OR LOWER(r.cuisine_type) LIKE LOWER(:search) OR LOWER(ri.item_name) LIKE LOWER(:search) OR LOWER(ri.description) LIKE LOWER(:search)) """) params['search'] = f'%{search_query}%' # Country filter if country: where_conditions.append("LOWER(c.country_name) LIKE LOWER(:country)") params['country'] = f'%{country}%' # Cuisine filter if cuisine_filter and cuisine_filter != 'All Categories': where_conditions.append("LOWER(r.cuisine_type) LIKE LOWER(:cuisine)") params['cuisine'] = f'%{cuisine_filter}%' # Add WHERE conditions if we have any if where_conditions: base_query += " AND " + " AND ".join(where_conditions) # Add GROUP BY clause base_query += """ GROUP BY r.restaurant_id, r.restaurant_name, r.description, r.cuisine_type, r.city, r.address, r.phone, r.email, r.website, c.country_name, rp.photo_path, ri.item_name, ri.description """ # Add ordering and pagination offset = (page - 1) * per_page base_query += " ORDER BY r.restaurant_name LIMIT :limit OFFSET :offset" params['limit'] = per_page params['offset'] = offset # Execute query restaurants_result = db.execute(text(base_query), params).fetchall() # Count total results for pagination # Create a separate query for counting that doesn't include LIMIT/OFFSET and GROUP BY count_query = """ SELECT COUNT(DISTINCT r.restaurant_id) FROM restaurants r LEFT JOIN countries c ON r.country_id = c.country_id LEFT JOIN restaurant_items ri ON r.restaurant_id = ri.restaurant_id LEFT JOIN beach_places bp ON r.beach_place_id = bp.beach_place_id WHERE r.is_active = true """ # Add the same WHERE conditions to the count query count_params = {} count_where_conditions = [] # Search condition for count query (simplified) if search_query: count_where_conditions.append(""" (LOWER(r.restaurant_name) LIKE LOWER(:search) OR LOWER(r.description) LIKE LOWER(:search) OR LOWER(r.cuisine_type) LIKE LOWER(:search) OR LOWER(ri.item_name) LIKE LOWER(:search) OR LOWER(ri.description) LIKE LOWER(:search)) """) count_params['search'] = params['search'] # Country filter for count query if country: count_where_conditions.append("LOWER(c.country_name) LIKE LOWER(:country)") count_params['country'] = params['country'] # Cuisine filter for count query if cuisine_filter and cuisine_filter != 'All Categories': count_where_conditions.append("LOWER(r.cuisine_type) LIKE LOWER(:cuisine)") count_params['cuisine'] = params['cuisine'] # Add WHERE conditions to count query if count_where_conditions: count_query += " AND " + " AND ".join(count_where_conditions) total_count = db.execute(text(count_query), count_params).scalar() # Format results restaurants = [] for restaurant in restaurants_result: restaurant_data = { 'id': str(restaurant.restaurant_id), 'name': restaurant.name, 'description': restaurant.description or 'Delicious cuisine with excellent service.', 'cuisine_type': restaurant.cuisine_type or 'Restaurant', 'location': f"{restaurant.city or ''}, {restaurant.country_name or ''}" if restaurant.city or restaurant.country_name else restaurant.address or '', 'address': restaurant.address or '', 'phone': restaurant.phone or '', 'email': restaurant.email or '', 'website': restaurant.website or '', 'image': ('/' + restaurant.photo_path.replace('\\', '/')) if restaurant.photo_path else '/static/img/restaurant_default.jpg', 'rating': float(restaurant.avg_rating) if restaurant.avg_rating else 0, 'review_count': int(restaurant.review_count) if restaurant.review_count else 0, 'menu_match': bool(restaurant.menu_match) if hasattr(restaurant, 'menu_match') else False } restaurants.append(restaurant_data) db.close() # Calculate pagination info total_pages = (total_count + per_page - 1) // per_page if total_count is not None and per_page is not None else 0 has_more = page < total_pages if total_pages is not None else False return { 'success': True, 'restaurants': restaurants, 'total_count': total_count, 'current_page': page, 'total_pages': total_pages, 'has_more': has_more, 'per_page': per_page } except Exception as e: print(f"Error in search_restaurants: {str(e)}") return {'success': False, 'message': str(e)}, 500 @api_bp.route('/adventures-grid') def get_adventures_grid_api(): """Get adventures data for the grid page with filtering and pagination (updated version)""" try: # Get request parameters page = int(request.args.get('page', 1)) offset = int(request.args.get('offset', 0)) per_page = int(request.args.get('per_page', 12)) filter_type = request.args.get('filter', 'all') search = request.args.get('search', '') country = request.args.get('country', '') city = request.args.get('city', '') # Use offset if provided, otherwise calculate from page if offset > 0: actual_offset = offset else: actual_offset = (page - 1) * per_page from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker # Database connection from config import ADMIN_CONFIG DATABASE_URL = ADMIN_CONFIG['DATABASE_URL'] engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) db = SessionLocal() # Check if adventures table exists # Check if adventures table exists adventures_check = db.execute(text(""" SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'adventures' ) """)).fetchone() adventures_exists = adventures_check[0] if adventures_check else False adventures_data = [] has_more = False total_count = 0 if adventures_exists: # Build the base query base_query = """ SELECT a.adventure_id, a.adventure_name, a.description, a.address, a.city, a.duration_minutes, a.max_participants, a.min_participants, a.price, c.company_name, co.country_name, a.latitude, a.longitude, a.phone, a.email, a.website, a.created_at, a.updated_at, ap.photo_path FROM adventures a LEFT JOIN companies c ON a.company_id = c.company_id LEFT JOIN countries co ON a.country_id = co.country_id LEFT JOIN ( SELECT DISTINCT ON (adventure_id) adventure_id, photo_path FROM adventure_photos ORDER BY adventure_id, created_at ASC ) ap ON a.adventure_id = ap.adventure_id WHERE 1=1 """ # Build WHERE conditions conditions = [] params = {} if search: conditions.append("(a.adventure_name ILIKE :search OR a.description ILIKE :search OR a.city ILIKE :search OR c.company_name ILIKE :search)") params['search'] = f'%{search}%' if country: conditions.append("co.country_name ILIKE :country") params['country'] = f'%{country}%' if city: conditions.append("a.city ILIKE :city") params['city'] = f'%{city}%' # Add filter conditions if filter_type == 'popular': conditions.append("a.price IS NOT NULL AND a.price > 0") elif filter_type == 'latest': conditions.append("a.created_at >= CURRENT_DATE - INTERVAL '30 days'") # Add conditions to query if conditions: base_query += " AND " + " AND ".join(conditions) # Add ordering if filter_type == 'popular': base_query += " ORDER BY a.price DESC, a.adventure_name" elif filter_type == 'latest': base_query += " ORDER BY a.created_at DESC, a.adventure_name" else: base_query += " ORDER BY a.adventure_name" # Get total count for pagination count_query = f"SELECT COUNT(*) FROM ({base_query}) as count_query" # Get total count for pagination count_query = f"SELECT COUNT(*) FROM ({base_query}) as count_query" total_result = db.execute(text(count_query), params).fetchone() total_count = total_result[0] if total_result else 0 # Add pagination paginated_query = base_query + f" LIMIT {per_page} OFFSET {actual_offset}" # Execute query adventures = db.execute(text(paginated_query), params).fetchall() # Check if there are more pages has_more = (actual_offset + per_page) < total_count # Format the results for adventure in adventures: location_parts = [] if adventure.city: location_parts.append(adventure.city) if adventure.country_name: location_parts.append(adventure.country_name) location = ', '.join(location_parts) if location_parts else (adventure.address or '') adventure_data = { 'id': str(adventure.adventure_id), 'adventure_id': str(adventure.adventure_id), 'adventure_name': adventure.adventure_name or 'Adventure', 'name': adventure.adventure_name or 'Adventure', 'description': adventure.description or 'Exciting adventure experience', 'location': location, 'company': adventure.company_name or '', 'country': adventure.country_name or '', 'city': adventure.city or '', 'address': adventure.address or '', 'duration_minutes': adventure.duration_minutes or 0, 'max_participants': adventure.max_participants or 0, 'min_participants': adventure.min_participants or 1, 'photo_path': adventure.photo_path or '/static/img/logo.png', 'image': adventure.photo_path or '/static/img/logo.png', 'price': float(adventure.price) if adventure.price else 0, 'currency': 'EUR', 'rating': 0, 'review_count': 0, 'view_count': 0, 'phone': adventure.phone or '', 'email': adventure.email or '', 'website': adventure.website or '', 'latitude': float(adventure.latitude) if adventure.latitude else None, 'longitude': float(adventure.longitude) if adventure.longitude else None, 'created_at': adventure.created_at.isoformat() if adventure.created_at else None, 'updated_at': adventure.updated_at.isoformat() if adventure.updated_at else None } adventures_data.append(adventure_data) db.close() return jsonify({ 'success': True, 'adventures': adventures_data, 'has_more': has_more, 'pagination': { 'page': page, 'per_page': per_page, 'offset': actual_offset, 'total': total_count } }) except Exception as e: current_app.logger.error(f"Error getting adventures grid: {e}") return jsonify({'success': True, 'adventures': [], 'has_more': False}) @api_bp.route('/beach-schedules/', methods=['GET']) def get_beach_schedules(beach_id): """Get all schedules for a beach place""" try: from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker from config import ADMIN_CONFIG # Database connection DATABASE_URL = ADMIN_CONFIG['DATABASE_URL'] engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) db = SessionLocal() schedules = db.execute(text(""" SELECT s.*, c.currency_symbol FROM beach_places_schedules s LEFT JOIN currencies c ON s.currency_id = c.currency_id WHERE s.beach_place_id = :beach_id ORDER BY s.from_date DESC """), {"beach_id": beach_id}).fetchall() schedules_data = [] for schedule in schedules: schedules_data.append({ 'schedule_id': schedule.schedule_id, 'from_date': schedule.from_date.isoformat(), 'to_date': schedule.to_date.isoformat(), 'from_time': schedule.from_time.strftime('%H:%M'), 'to_time': schedule.to_time.strftime('%H:%M'), 'valid_dates': schedule.valid_dates, 'min_hours': schedule.min_hours, 'can_refund': schedule.can_refund, 'refund_before_hours': schedule.refund_before_hours, 'price': float(schedule.price), 'price_vip': float(schedule.price_vip), 'currency_symbol': schedule.currency_symbol or '€', 'extras': schedule.extras, 'extras_vip': schedule.extras_vip }) db.close() return jsonify(schedules_data) except Exception as e: current_app.logger.error(f"Error getting beach schedules: {e}") return jsonify({'error': 'Failed to load schedules'}), 500 @api_bp.route('/beach-schedules/', methods=['POST']) def add_beach_schedule(beach_id): """Add a new schedule for a beach place""" try: from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker from config import ADMIN_CONFIG # Database connection DATABASE_URL = ADMIN_CONFIG['DATABASE_URL'] engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) data = request.get_json() db = SessionLocal() # Use currency_id from request data, fallback to EUR if not provided currency_id = data.get('currency_id', 1) # Insert new schedule result = db.execute(text(""" INSERT INTO beach_places_schedules ( beach_place_id, from_date, to_date, from_time, to_time, valid_dates, min_hours, can_refund, refund_before_hours, currency_id, price, price_vip, extras, extras_vip ) VALUES ( :beach_id, :from_date, :to_date, :from_time, :to_time, :valid_dates, :min_hours, :can_refund, :refund_before_hours, :currency_id, :price, :price_vip, :extras, :extras_vip ) RETURNING schedule_id """), { 'beach_id': beach_id, 'from_date': data['from_date'], 'to_date': data['to_date'], 'from_time': data['from_time'], 'to_time': data['to_time'], 'valid_dates': data['valid_dates'], 'min_hours': data['min_hours'], 'can_refund': data['can_refund'], 'refund_before_hours': data['refund_before_hours'], 'currency_id': currency_id, 'price': data['price'], 'price_vip': data['price_vip'], 'extras': data.get('extras', ''), 'extras_vip': data.get('extras_vip', '') }) result_row = result.fetchone() schedule_id = result_row[0] if result_row else None db.commit() db.close() return jsonify({ 'success': True, 'schedule_id': schedule_id, 'message': 'Schedule created successfully' }) except Exception as e: current_app.logger.error(f"Error adding beach schedule: {e}") return jsonify({'error': 'Failed to create schedule'}), 500 @api_bp.route('/beach-schedules/', methods=['PUT']) def update_beach_schedule(schedule_id): """Update an existing beach schedule""" try: from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker from config import ADMIN_CONFIG # Database connection DATABASE_URL = ADMIN_CONFIG['DATABASE_URL'] engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) data = request.get_json() db = SessionLocal() # Check if schedule exists existing = db.execute(text(""" SELECT schedule_id FROM beach_places_schedules WHERE schedule_id = :schedule_id """), {"schedule_id": schedule_id}).fetchone() if not existing: db.close() return jsonify({'error': 'Schedule not found'}), 404 # Use currency_id from request data, fallback to EUR if not provided currency_id = data.get('currency_id', 1) # Update schedule db.execute(text(""" UPDATE beach_places_schedules SET from_date = :from_date, to_date = :to_date, from_time = :from_time, to_time = :to_time, valid_dates = :valid_dates, min_hours = :min_hours, can_refund = :can_refund, refund_before_hours = :refund_before_hours, currency_id = :currency_id, price = :price, price_vip = :price_vip, extras = :extras, extras_vip = :extras_vip WHERE schedule_id = :schedule_id """), { 'schedule_id': schedule_id, 'from_date': data['from_date'], 'to_date': data['to_date'], 'from_time': data['from_time'], 'to_time': data['to_time'], 'valid_dates': data['valid_dates'], 'min_hours': data['min_hours'], 'can_refund': data['can_refund'], 'refund_before_hours': data['refund_before_hours'], 'currency_id': currency_id, 'price': data['price'], 'price_vip': data['price_vip'], 'extras': data.get('extras', ''), 'extras_vip': data.get('extras_vip', '') }) db.commit() db.close() return jsonify({ 'success': True, 'message': 'Schedule updated successfully' }) except Exception as e: current_app.logger.error(f"Error updating beach schedule: {e}") return jsonify({'error': 'Failed to update schedule'}), 500 @api_bp.route('/beach-schedules/delete/', methods=['DELETE']) def delete_beach_schedule(schedule_id): """Delete a beach schedule""" try: from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker from config import ADMIN_CONFIG # Database connection DATABASE_URL = ADMIN_CONFIG['DATABASE_URL'] engine = create_engine(DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) db = SessionLocal() # Check if schedule exists result = db.execute(text(""" SELECT schedule_id FROM beach_places_schedules WHERE schedule_id = :schedule_id """), {"schedule_id": schedule_id}).fetchone() if not result: db.close() return jsonify({'error': 'Schedule not found'}), 404 # Delete the schedule db.execute(text(""" DELETE FROM beach_places_schedules WHERE schedule_id = :schedule_id """), {"schedule_id": schedule_id}) db.commit() db.close() return jsonify({ 'success': True, 'message': 'Schedule deleted successfully' }) except Exception as e: current_app.logger.error(f"Error deleting beach schedule: {e}") return jsonify({'error': 'Failed to delete schedule'}), 500