"""
Admin Routes Blueprint
Contains admin panel functionality, user management, and business management
"""
from flask import Blueprint, request, render_template, redirect, url_for, flash, jsonify, session
from flask_login import login_required, current_user
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from uuid import UUID
from urllib.parse import quote_plus
import os
import sys

# Import config from parent directory
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from config import ADMIN_CONFIG

# Create Blueprint
admin_bp = Blueprint('admin', __name__)

@admin_bp.route('/admin')
def admin_dashboard():
    """Admin dashboard with overview statistics"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    db = None
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        
        db = SessionLocal()
        
        # Get overview statistics
        stats = {}
        
        # Count beaches
        try:
            result = db.execute(text("SELECT COUNT(*) FROM beach_places")).fetchone()
            beach_count = result[0] if result else 0
            stats['beaches'] = beach_count
        except Exception as e:
            print(f"Error counting beaches: {e}")
            stats['beaches'] = 0
        finally:
            try:
                db.close()
            except:
                pass
            db = SessionLocal()
        
        # Count restaurants
        try:
            result = db.execute(text("SELECT COUNT(*) FROM restaurants")).fetchone()
            restaurant_count = result[0] if result else 0
            stats['restaurants'] = restaurant_count
        except Exception as e:
            print(f"Error counting restaurants: {e}")
            stats['restaurants'] = 0
        finally:
            try:
                db.close()
            except:
                pass
            db = SessionLocal()
        
        # Count markets
        try:
            result = db.execute(text("SELECT COUNT(*) FROM markets")).fetchone()
            market_count = result[0] if result else 0
            stats['markets'] = market_count
        except Exception as e:
            print(f"Error counting markets: {e}")
            stats['markets'] = 0
        finally:
            try:
                db.close()
            except:
                pass
            db = SessionLocal()
        
        # Count adventures
        try:
            result = db.execute(text("SELECT COUNT(*) FROM adventures")).fetchone()
            adventure_count = result[0] if result else 0
            stats['adventures'] = adventure_count
        except Exception as e:
            print(f"Error counting adventures: {e}")
            stats['adventures'] = 0
        finally:
            try:
                db.close()
            except:
                pass
            db = SessionLocal()
        
        # Count users
        try:
            result = db.execute(text("SELECT COUNT(*) FROM users WHERE is_active = true")).fetchone()
            user_count = result[0] if result else 0
            stats['users'] = user_count
        except Exception as e:
            print(f"Error counting users: {e}")
            stats['users'] = 0
        finally:
            try:
                db.close()
            except:
                pass
            db = SessionLocal()
        
        # Count companies
        try:
            result = db.execute(text("SELECT COUNT(*) FROM companies")).fetchone()
            company_count = result[0] if result else 0
            stats['companies'] = company_count
        except Exception as e:
            print(f"Error counting companies: {e}")
            stats['companies'] = 0
        finally:
            try:
                db.close()
            except:
                pass
            db = SessionLocal()
        
        # Count recent bookings (last 30 days) - check if table exists first
        try:
            # Check if bookings table exists
            table_check = db.execute(text("""
                SELECT EXISTS (
                    SELECT FROM information_schema.tables 
                    WHERE table_schema = 'public' AND table_name = 'bookings'
                )
            """)).fetchone()
            
            if table_check and table_check[0]:
                result = db.execute(text("""
                    SELECT COUNT(*) FROM bookings 
                    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
                """)).fetchone()
                recent_bookings = result[0] if result else 0
                stats['recent_bookings'] = recent_bookings
            else:
                stats['recent_bookings'] = 0
        except Exception as e:
            print(f"Error counting recent bookings: {e}")
            stats['recent_bookings'] = 0
        finally:
            try:
                db.close()
            except:
                pass
            db = SessionLocal()
        
        # Get dashboard specific stats (messages, reviews, bookmarks)
        try:
            # Count messages (check if table exists first)
            table_check = db.execute(text("""
                SELECT EXISTS (
                    SELECT FROM information_schema.tables 
                    WHERE table_schema = 'public' AND table_name = 'messages'
                )
            """)).fetchone()
            
            if table_check and table_check[0]:
                result = db.execute(text("SELECT COUNT(*) FROM messages WHERE status = 'unread'")).fetchone()
                stats['messages'] = result[0] if result else 0
            else:
                stats['messages'] = 26  # Default value from old dashboard
        except Exception as e:
            print(f"Error counting messages: {e}")
            stats['messages'] = 26  # Default value from old dashboard
        finally:
            try:
                db.close()
            except:
                pass
            db = SessionLocal()
        
        try:
            # Count reviews (check if table exists first)
            table_check = db.execute(text("""
                SELECT EXISTS (
                    SELECT FROM information_schema.tables 
                    WHERE table_schema = 'public' AND table_name = 'reviews'
                )
            """)).fetchone()
            
            if table_check and table_check[0]:
                # Check if status column exists
                column_check = db.execute(text("""
                    SELECT EXISTS (
                        SELECT column_name FROM information_schema.columns 
                        WHERE table_name = 'reviews' AND column_name = 'status'
                    )
                """)).fetchone()
                
                if column_check and column_check[0]:
                    result = db.execute(text("SELECT COUNT(*) FROM reviews WHERE status = 'pending'")).fetchone()
                    stats['reviews'] = result[0] if result else 0
                else:
                    # Try with review_status column instead
                    column_check = db.execute(text("""
                        SELECT EXISTS (
                            SELECT column_name FROM information_schema.columns 
                            WHERE table_name = 'reviews' AND column_name = 'review_status'
                        )
                    """)).fetchone()
                    
                    if column_check and column_check[0]:
                        result = db.execute(text("SELECT COUNT(*) FROM reviews WHERE review_status = 'pending'")).fetchone()
                        stats['reviews'] = result[0] if result else 0
                    else:
                        stats['reviews'] = 11  # Default value from old dashboard
            else:
                stats['reviews'] = 11  # Default value from old dashboard
        except Exception as e:
            print(f"Error counting reviews: {e}")
            stats['reviews'] = 11  # Default value from old dashboard
        finally:
            try:
                db.close()
            except:
                pass
            db = SessionLocal()
        
        # Count recent bookings for dashboard (last 7 days) - check if table exists first
        try:
            # Check if bookings table exists
            table_check = db.execute(text("""
                SELECT EXISTS (
                    SELECT FROM information_schema.tables 
                    WHERE table_schema = 'public' AND table_name = 'bookings'
                )
            """)).fetchone()
            
            if table_check and table_check[0]:
                result = db.execute(text("""
                    SELECT COUNT(*) FROM bookings 
                    WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
                """)).fetchone()
                stats['bookings'] = result[0] if result else 0
            else:
                stats['bookings'] = 0
        except Exception as e:
            print(f"Error counting bookings: {e}")
            stats['bookings'] = 0
        finally:
            try:
                db.close()
            except:
                pass
            db = SessionLocal()
        
        try:
            # Count bookmarks (check if table exists first)
            table_check = db.execute(text("""
                SELECT EXISTS (
                    SELECT FROM information_schema.tables 
                    WHERE table_schema = 'public' AND table_name = 'bookmarks'
                )
            """)).fetchone()
            
            if table_check and table_check[0]:
                result = db.execute(text("SELECT COUNT(*) FROM bookmarks")).fetchone()
                stats['bookmarks'] = result[0] if result else 0
            else:
                stats['bookmarks'] = 10  # Default value from old dashboard
        except Exception as e:
            print(f"Error counting bookmarks: {e}")
            stats['bookmarks'] = 10  # Default value from old dashboard
        finally:
            try:
                db.close()
            except:
                pass
            db = SessionLocal()
        
        # Count pending companies
        try:
            result = db.execute(text("SELECT COUNT(*) FROM companies WHERE company_status = 'pending'")).fetchone()
            stats['pending_companies'] = result[0] if result else 0
        except Exception as e:
            print(f"Error counting pending companies: {e}")
            stats['pending_companies'] = 0
        finally:
            try:
                db.close()
            except:
                pass
            db = SessionLocal()
        
        # Get recent activities
        activities = []
        
        try:
            # Recent user registrations
            recent_users = db.execute(text("""
                SELECT first_name, last_name, email, created_at
                FROM users 
                WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
                ORDER BY created_at DESC
                LIMIT 5
            """)).fetchall()
            
            for user in recent_users:
                activities.append({
                    'icon': 'fas fa-user-plus text-success',
                    'title': 'New user registered',
                    'description': f"{user.first_name} {user.last_name}",
                    'time_ago': time_ago(user.created_at)
                })
        except Exception as e:
            print(f"Error fetching user activities: {e}")
        finally:
            try:
                db.close()
            except:
                pass
            db = SessionLocal()
        
        try:
            # Recent bookings - check if table exists first
            table_check = db.execute(text("""
                SELECT EXISTS (
                    SELECT FROM information_schema.tables 
                    WHERE table_schema = 'public' AND table_name = 'bookings'
                )
            """)).fetchone()
            
            if table_check and table_check[0]:
                recent_booking_activity = db.execute(text("""
                    SELECT b.booking_id, b.status, b.created_at, u.first_name, u.last_name,
                           bp.beach_name
                    FROM bookings b
                    JOIN users u ON b.user_id = u.user_id
                    LEFT JOIN beach_places bp ON b.beach_place_id = bp.beach_place_id
                    WHERE b.created_at >= CURRENT_DATE - INTERVAL '7 days'
                    ORDER BY b.created_at DESC
                    LIMIT 5
                """)).fetchall()
                
                for booking in recent_booking_activity:
                    place_name = booking.beach_name or 'Unknown Place'
                    status_icon = 'fas fa-calendar-check text-success' if booking.status == 'confirmed' else 'fas fa-calendar text-warning'
                    activities.append({
                        'icon': status_icon,
                        'title': f'New booking ({booking.status})',
                        'description': f"by {booking.first_name} {booking.last_name} for {place_name}",
                        'time_ago': time_ago(booking.created_at)
                    })
        except Exception as e:
            print(f"Error fetching booking activities: {e}")
        finally:
            try:
                db.close()
            except:
                pass
            db = SessionLocal()
        
        try:
            # Recent companies - check column name
            # Check if created_at column exists
            column_check = db.execute(text("""
                SELECT EXISTS (
                    SELECT column_name FROM information_schema.columns 
                    WHERE table_name = 'companies' AND column_name = 'created_at'
                )
            """)).fetchone()
            
            if column_check and column_check[0]:
                recent_companies = db.execute(text("""
                    SELECT company_name, created_at
                    FROM companies 
                    WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
                    ORDER BY created_at DESC
                    LIMIT 5
                """)).fetchall()
            else:
                # Try with create_date column instead
                column_check = db.execute(text("""
                    SELECT EXISTS (
                        SELECT column_name FROM information_schema.columns 
                        WHERE table_name = 'companies' AND column_name = 'create_date'
                    )
                """)).fetchone()
                
                if column_check and column_check[0]:
                    recent_companies = db.execute(text("""
                        SELECT company_name, create_date as created_at
                        FROM companies 
                        WHERE create_date >= CURRENT_DATE - INTERVAL '7 days'
                        ORDER BY create_date DESC
                        LIMIT 5
                    """)).fetchall()
                else:
                    recent_companies = []
            
            for company in recent_companies:
                activities.append({
                    'icon': 'fas fa-building text-primary',
                    'title': 'New company registered',
                    'description': company.company_name,
                    'time_ago': time_ago(company.created_at)
                })
        except Exception as e:
            print(f"Error fetching company activities: {e}")
        finally:
            try:
                db.close()
            except:
                pass
        
        # Sort activities by timestamp
        activities = sorted(activities, key=lambda x: x.get('time_ago', ''), reverse=True)[:10]
        
        # Pass stats to template
        template_stats = {
            'total_users': stats.get('users', 0),
            'total_companies': stats.get('companies', 0),
            'total_beaches': stats.get('beaches', 0),
            'total_bookings': stats.get('bookings', 0),
            'pending_companies': stats.get('pending_companies', 0),
            'messages': stats.get('messages', 0),
            'reviews': stats.get('reviews', 0),
            'bookmarks': stats.get('bookmarks', 0)
        }
        
        return render_template('admin/dashboard.html', stats=template_stats, recent_activities=activities)
        
    except Exception as e:
        print(f"Error loading dashboard: {str(e)}")
        flash(f'Error loading dashboard: {str(e)}', 'error')
        # Close any open database connections
        try:
            if db:
                db.close()
        except:
            pass
        return render_template('admin/dashboard.html', stats={}, recent_activities=[])

def time_ago(dt):
    """Convert datetime to human-readable time ago string"""
    from datetime import datetime
    if not dt:
        return 'Unknown'
    
    # Ensure we're working with datetime objects
    if hasattr(dt, 'replace'):
        now = datetime.now()
        if dt.tzinfo:
            now = now.replace(tzinfo=dt.tzinfo)
        diff = now - dt
    else:
        return 'Unknown'
    
    if diff.days > 0:
        if diff.days > 30:
            months = diff.days // 30
            return f"{months} month{'s' if months > 1 else ''} ago"
        elif diff.days > 7:
            weeks = diff.days // 7
            return f"{weeks} week{'s' if weeks > 1 else ''} ago"
        else:
            return f"{diff.days} day{'s' if diff.days > 1 else ''} ago"
    elif diff.seconds > 3600:
        hours = diff.seconds // 3600
        return f"{hours} hour{'s' if hours > 1 else ''} ago"
    elif diff.seconds > 60:
        minutes = diff.seconds // 60
        return f"{minutes} minute{'s' if minutes > 1 else ''} ago"
    else:
        return "Just now"

@admin_bp.route('/admin/login', methods=['GET', 'POST'])
def admin_login():
    """Admin login page"""
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        
        # Check hardcoded admin credentials
        if username == ADMIN_CONFIG['ADMIN_USERNAME'] and password == ADMIN_CONFIG['ADMIN_PASSWORD']:
            session['admin_logged_in'] = True
            session['admin_username'] = username
            session['user_id'] = 'admin'
            session['is_admin'] = True
            flash('Admin login successful!', 'success')
            return redirect(url_for('admin.admin_dashboard'))
        
        # Check database admin users
        try:
            DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
            engine = create_engine(DATABASE_URL)
            SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
            
            db = SessionLocal()
            
            user_result = db.execute(text("""
                SELECT u.user_id, u.password_hash, u.first_name, u.last_name, u.email
                FROM users u
                JOIN user_roles ur ON u.role_id = ur.role_id
                WHERE (u.first_name = :username OR u.email = :username)
                AND u.is_active = true
                AND ur.role_name = 'admin'
            """), {"username": username}).fetchone()
            
            db.close()
            
            if user_result and check_password_hash(user_result.password_hash, password):
                session['admin_logged_in'] = True
                session['admin_username'] = username
                session['admin_user_id'] = str(user_result.user_id)
                session['user_id'] = str(user_result.user_id)
                session['is_admin'] = True
                flash('Admin login successful!', 'success')
                return redirect(url_for('admin.admin_dashboard'))
        except Exception as e:
            flash(f'Database error during login: {str(e)}', 'error')
        
        flash('Invalid admin credentials.', 'error')
    
    # Use the regular login template instead of admin login template
    return render_template('login.html')

@admin_bp.route('/admin/users')
def users():
    """Manage users"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    # Get search and filter parameters
    search_term = request.args.get('search', '').strip()
    role_filter = request.args.get('role', '').strip()
    status_filter = request.args.get('status', '').strip()
    
    # Check if this is a search/filter request
    is_search_request = 'search' in request.args or 'role' in request.args or 'status' in request.args
    
    # Only load data if this is a search/filter request
    if not is_search_request:
        # First page load, return empty results
        return render_template('admin/users.html', 
                             users=[],
                             page=1,
                             total_count=0,
                             total_pages=1,
                             has_prev=False,
                             has_next=False,
                             prev_num=None,
                             next_num=None,
                             search_term='',
                             role_filter='',
                             status_filter='')
    
    # Initialize variables
    users_data = []
    page = 1
    total_count = 0
    total_pages = 1
    has_prev = False
    has_next = False
    prev_num = None
    next_num = None
    
    # This is a search/filter request, load data from database
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        
        db = SessionLocal()
        
        # Get the page number from request args, default to 1
        page = request.args.get('page', 1, type=int)
        per_page = 10
        offset = (page - 1) * per_page
        
        # Build the WHERE clause for filtering
        where_conditions = []
        params = {}
        
        if search_term:
            where_conditions.append("(u.email ILIKE :search OR u.first_name ILIKE :search OR u.last_name ILIKE :search OR u.phone ILIKE :search)")
            params['search'] = f'%{search_term}%'
        
        if role_filter:
            where_conditions.append("ur.role_name = :role")
            params['role'] = role_filter
            
        if status_filter:
            is_active = status_filter == 'Active'
            where_conditions.append("u.is_active = :is_active")
            params['is_active'] = is_active
        
        where_clause = "WHERE " + " AND ".join(where_conditions) if where_conditions else ""
        
        # Get total count for pagination
        count_query = f"""
            SELECT COUNT(*) 
            FROM users u
            LEFT JOIN user_roles ur ON u.role_id = ur.role_id
            {where_clause}
        """
        count_result = db.execute(text(count_query), params).fetchone()
        total_count = count_result[0] if count_result else 0
        
        # Get users with pagination
        users_query = f"""
            SELECT u.user_id, u.email, u.first_name, u.last_name, 
                   ur.role_name, u.is_active, u.created_at,
                   u.is_email_verified, u.phone
            FROM users u
            LEFT JOIN user_roles ur ON u.role_id = ur.role_id
            {where_clause}
            ORDER BY u.created_at DESC
            LIMIT :limit OFFSET :offset
        """
        
        params['limit'] = per_page
        params['offset'] = offset
        
        users_result = db.execute(text(users_query), params).fetchall()
        
        users_data = []
        for user in users_result:
            users_data.append({
                'id': user.user_id,
                'email': user.email,
                'name': f"{user.first_name or ''} {user.last_name or ''}".strip(),
                'role': user.role_name or 'No Role',
                'status': 'Active' if user.is_active else 'Inactive',
                'created_at': user.created_at.strftime('%Y-%m-%d') if user.created_at else 'N/A',
                'phone': user.phone or 'N/A',
                'email_verified': user.is_email_verified
            })
        
        # Calculate pagination variables
        total_pages = (total_count + per_page - 1) // per_page
        has_prev = page > 1
        has_next = page < total_pages
        prev_num = page - 1 if has_prev else None
        next_num = page + 1 if has_next else None
        
        db.close()
        
    except Exception as e:
        flash(f'Error loading users: {str(e)}', 'error')
        users_data = []
        page = 1
        total_count = 0
        total_pages = 1
        has_prev = False
        has_next = False
        prev_num = None
        next_num = None
    
    return render_template('admin/users.html', 
                         users=users_data,
                         page=page,
                         total_count=total_count,
                         total_pages=total_pages,
                         has_prev=has_prev,
                         has_next=has_next,
                         prev_num=prev_num,
                         next_num=next_num,
                         search_term=search_term,
                         role_filter=role_filter,
                         status_filter=status_filter)

@admin_bp.route('/admin/settings')
def settings():
    """View system settings"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    # For now, just render the settings template
    # In a real application, this would fetch and display actual settings
    return render_template('admin/settings.html')

@admin_bp.route('/admin/reports')
def reports():
    """View reports and analytics"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    # Get real reports data from database
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        
        db = SessionLocal()
        
        # Initialize reports data
        reports_data = {
            'revenue': {
                'today': 0.00,
                'this_week': 0.00,
                'this_month': 0.00,
                'this_year': 0.00
            },
            'bookings': {
                'today': 0,
                'this_week': 0,
                'this_month': 0,
                'this_year': 0
            },
            'top_beaches': []
        }
        
        # Check if required tables exist
        bookings_check_result = db.execute(text("""
            SELECT EXISTS (
                SELECT FROM information_schema.tables 
                WHERE table_schema = 'public' AND table_name = 'bookings'
            )
        """)).fetchone()
        
        bookings_exists = bookings_check_result[0] if bookings_check_result else False
        
        beaches_check_result = db.execute(text("""
            SELECT EXISTS (
                SELECT FROM information_schema.tables 
                WHERE table_schema = 'public' AND table_name = 'beaches'
            )
        """)).fetchone()
        
        beaches_exists = beaches_check_result[0] if beaches_check_result else False
        
        if bookings_exists:
            from datetime import datetime, timedelta
            today = datetime.now().date()
            week_start = today - timedelta(days=today.weekday())
            month_start = today.replace(day=1)
            year_start = today.replace(month=1, day=1)
            
            # Revenue calculations
            revenue_queries = {
                'today': f"booking_date = '{today}'",
                'this_week': f"booking_date >= '{week_start}'",
                'this_month': f"booking_date >= '{month_start}'",
                'this_year': f"booking_date >= '{year_start}'"
            }
            
            for period, condition in revenue_queries.items():
                result = db.execute(text(f"""
                    SELECT COALESCE(SUM(total_amount), 0) as revenue,
                           COUNT(*) as count
                    FROM bookings 
                    WHERE {condition}
                    AND booking_status IN ('confirmed', 'completed')
                """)).fetchone()
                
                if result:
                    reports_data['revenue'][period] = float(result[0] or 0)
                    reports_data['bookings'][period] = int(result[1] or 0)
        
        # Top beaches
        if bookings_exists and beaches_exists:
            top_beaches_result = db.execute(text("""
                SELECT b.beach_name, COUNT(bk.booking_id) as booking_count
                FROM beaches b
                LEFT JOIN bookings bk ON b.beach_id = bk.beach_id
                WHERE bk.booking_status IN ('confirmed', 'completed')
                GROUP BY b.beach_id, b.beach_name
                ORDER BY booking_count DESC
                LIMIT 5
            """)).fetchall()
            
            reports_data['top_beaches'] = [
                {'name': beach.beach_name, 'bookings': beach.booking_count}
                for beach in top_beaches_result
            ]
        
        db.close()
        
    except Exception as e:
        flash(f'Error loading reports: {str(e)}', 'error')
        # Fallback to default empty data
        reports_data = {
            'revenue': {
                'today': 0.00,
                'this_week': 0.00,
                'this_month': 0.00,
                'this_year': 0.00
            },
            'bookings': {
                'today': 0,
                'this_week': 0,
                'this_month': 0,
                'this_year': 0
            },
            'top_beaches': []
        }
    
    return render_template('admin/reports.html', reports=reports_data)

@admin_bp.route('/admin/companies')
def companies():
    """Manage companies"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    # Get search and filter parameters
    search_term = request.args.get('search', '').strip()
    company_status = request.args.get('status', '').strip()
    selected_country_id = request.args.get('country_id', '').strip()
    
    # Check if this is a search/filter request
    is_search_request = 'search' in request.args or 'status' in request.args or 'country_id' in request.args
    
    # Only load data if this is a search/filter request
    if not is_search_request:
        # First page load, return empty results
        return render_template('admin/companies.html', 
                             companies=[],
                             page=1,
                             total_count=0,
                             total_pages=1,
                             has_prev=False,
                             has_next=False,
                             prev_num=None,
                             next_num=None,
                             search_term='',
                             company_status='',
                             selected_country_id='',
                             countries=[])
    
    # This is a search/filter request, load data from database
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        
        db = SessionLocal()
        
        # Base query for companies with country information and business counts
        base_query = """
            SELECT 
                c.company_id,
                c.company_name,
                c.website,
                c.email AS contact_email,
                c.phone AS contact_phone,
                c.company_logo_path AS logo_path,
                c.create_date AS created_at,
                c.update_date AS updated_at,
                c.vat_number,
                c.city,
                c.province,
                c.country_id,
                c.company_status,
                co.country_name,
                COALESCE(beach_counts.count, 0) AS beaches_count,
                COALESCE(restaurant_counts.count, 0) AS restaurants_count,
                COALESCE(market_counts.count, 0) AS markets_count,
                COALESCE(adventure_counts.count, 0) AS adventures_count
            FROM companies c
            LEFT JOIN countries co ON c.country_id = co.country_id
            LEFT JOIN (
                SELECT company_id, COUNT(*) AS count
                FROM beach_places
                GROUP BY company_id
            ) beach_counts ON c.company_id = beach_counts.company_id
            LEFT JOIN (
                SELECT company_id, COUNT(*) AS count
                FROM restaurants
                GROUP BY company_id
            ) restaurant_counts ON c.company_id = restaurant_counts.company_id
            LEFT JOIN (
                SELECT company_id, COUNT(*) AS count
                FROM markets
                GROUP BY company_id
            ) market_counts ON c.company_id = market_counts.company_id
            LEFT JOIN (
                SELECT company_id, COUNT(*) AS count
                FROM adventures
                GROUP BY company_id
            ) adventure_counts ON c.company_id = adventure_counts.company_id
        """
        
        # Get the page number from request args, default to 1
        page = request.args.get('page', 1, type=int)
        per_page = 10
        offset = (page - 1) * per_page
        
        # Build the WHERE clause for filtering
        where_conditions = []
        params = {}
        
        if search_term:
            # Updated search to include actual column names with table alias
            where_conditions.append("(c.company_name ILIKE :search OR c.email ILIKE :search OR c.phone ILIKE :search OR c.vat_number ILIKE :search)")
            params['search'] = f'%{search_term}%'
        
        if company_status:
            where_conditions.append("c.company_status = :status")
            params['status'] = company_status
        
        if selected_country_id:
            where_conditions.append("c.country_id = :country_id")
            params['country_id'] = selected_country_id
        
        where_clause = "WHERE " + " AND ".join(where_conditions) if where_conditions else ""
        
        # Get total count for pagination
        count_query = f"SELECT COUNT(*) FROM companies c {where_clause}"
        count_result = db.execute(text(count_query), params).fetchone()
        total_count = count_result[0] if count_result else 0
        
        # Get companies with pagination
        paginated_query = f"""
            {base_query}
            {where_clause}
            ORDER BY c.company_name
            LIMIT :limit OFFSET :offset
        """
        
        params['limit'] = per_page
        params['offset'] = offset
        
        companies = db.execute(text(paginated_query), params).fetchall()
        
        # Calculate pagination variables
        total_pages = (total_count + per_page - 1) // per_page
        has_prev = page > 1
        has_next = page < total_pages
        prev_num = page - 1 if has_prev else None
        next_num = page + 1 if has_next else None
        
        # Get countries for the filter dropdown
        countries = []
        try:
            countries_result = db.execute(text("SELECT country_id, country_name FROM countries ORDER BY country_name")).fetchall()
            countries = [{'country_id': row[0], 'country_name': row[1]} for row in countries_result]
        except Exception as e:
            print(f"Error loading countries: {e}")
        
        db.close()
        
        print(f"Companies route - Found {len(companies)} companies, total count: {total_count}")
        
        return render_template('admin/companies.html', 
                             companies=companies,
                             page=page,
                             total_count=total_count,
                             total_pages=total_pages,
                             has_prev=has_prev,
                             has_next=has_next,
                             prev_num=prev_num,
                             next_num=next_num,
                             search_term=search_term,
                             company_status=company_status,
                             selected_country_id=selected_country_id,
                             countries=countries)
        
    except Exception as e:
        flash(f'Error loading companies: {str(e)}', 'error')
        print(f"Error in companies route: {str(e)}")
        import traceback
        traceback.print_exc()
        return render_template('admin/companies.html', 
                             companies=[],
                             page=1,
                             total_count=0,
                             total_pages=1,
                             has_prev=False,
                             has_next=False,
                             prev_num=None,
                             next_num=None,
                             search_term=search_term,
                             company_status=company_status,
                             selected_country_id=selected_country_id,
                             countries=[])

@admin_bp.route('/admin/beaches')
def beaches():
    """Manage beaches"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    # Get search and filter parameters
    search_term = request.args.get('search', '').strip()
    
    # Check if this is a search request (search parameter exists in URL)
    is_search_request = 'search' in request.args
    
    # Only load data if this is a search request
    if not is_search_request:
        # First page load, return empty results
        return render_template('admin/beaches.html', 
                             beaches=[],
                             page=1,
                             total_count=0,
                             total_pages=1,
                             has_prev=False,
                             has_next=False,
                             prev_num=None,
                             next_num=None,
                             search_term='')
    
    # This is a search request, load data from database
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        
        db = SessionLocal()
        
        # Get the page number from request args, default to 1
        page = request.args.get('page', 1, type=int)
        per_page = 10
        offset = (page - 1) * per_page
        
        # Build the WHERE clause for filtering
        where_conditions = []
        params = {}
        
        if search_term:
            where_conditions.append("(bp.beach_name ILIKE :search OR bp.city ILIKE :search)")
            params['search'] = f'%{search_term}%'
        
        where_clause = "WHERE " + " AND ".join(where_conditions) if where_conditions else ""
        
        # Get total count for pagination
        count_query = f"SELECT COUNT(*) FROM beach_places bp {where_clause}"
        count_result = db.execute(text(count_query), params).fetchone()
        total_count = count_result[0] if count_result else 0
        
        # Get beaches with company and country info
        beaches_query = f"""
            SELECT bp.beach_place_id, bp.beach_name, bp.address, bp.city,
                   c.company_name, co.country_name, bp.enable_beach,
                   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
            {where_clause}
            ORDER BY bp.beach_name
            LIMIT :limit OFFSET :offset
        """
        
        params['limit'] = per_page
        params['offset'] = offset
        
        beaches = db.execute(text(beaches_query), params).fetchall()
        
        # Calculate pagination variables
        total_pages = (total_count + per_page - 1) // per_page
        has_prev = page > 1
        has_next = page < total_pages
        prev_num = page - 1 if has_prev else None
        next_num = page + 1 if has_next else None
        
        db.close()
        
        return render_template('admin/beaches.html', 
                             beaches=beaches,
                             page=page,
                             total_count=total_count,
                             total_pages=total_pages,
                             has_prev=has_prev,
                             has_next=has_next,
                             prev_num=prev_num,
                             next_num=next_num,
                             search_term=search_term)
        
    except Exception as e:
        flash(f'Error loading beaches: {str(e)}', 'error')
        return render_template('admin/beaches.html', 
                             beaches=[],
                             page=1,
                             total_count=0,
                             total_pages=1,
                             has_prev=False,
                             has_next=False,
                             prev_num=None,
                             next_num=None,
                             search_term=search_term)

@admin_bp.route('/admin/restaurants')
def restaurants():
    """Manage restaurants"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    # Get search and filter parameters
    search_term = request.args.get('search', '').strip()
    
    # Check if this is a search request (search parameter exists in URL)
    is_search_request = 'search' in request.args
    
    # Only load data if this is a search request
    if not is_search_request:
        # First page load, return empty results
        return render_template('admin/restaurants.html', 
                             restaurants=[],
                             page=1,
                             total_count=0,
                             total_pages=1,
                             has_prev=False,
                             has_next=False,
                             prev_num=None,
                             next_num=None,
                             search_term='')
    
    # This is a search request, load data from database
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        
        db = SessionLocal()
        
        # Get the page number from request args, default to 1
        page = request.args.get('page', 1, type=int)
        per_page = 10
        offset = (page - 1) * per_page
        
        # Build the WHERE clause for filtering
        where_conditions = []
        params = {}
        
        if search_term:
            where_conditions.append("(r.restaurant_name ILIKE :search OR r.city ILIKE :search OR r.phone ILIKE :search OR r.email ILIKE :search)")
            params['search'] = f'%{search_term}%'
        
        where_clause = "WHERE " + " AND ".join(where_conditions) if where_conditions else ""
        
        # Get total count for pagination
        count_query = f"SELECT COUNT(*) FROM restaurants r {where_clause}"
        count_result = db.execute(text(count_query), params).fetchone()
        total_count = count_result[0] if count_result else 0
        
        # Get restaurants with company and country info
        restaurants_query = f"""
            SELECT r.restaurant_id, r.restaurant_name, r.address, r.city,
                   c.company_name, co.country_name, r.phone, r.email,
                   r.created_at, r.updated_at
            FROM restaurants r
            LEFT JOIN companies c ON r.company_id = c.company_id
            LEFT JOIN countries co ON r.country_id = co.country_id
            {where_clause}
            ORDER BY r.restaurant_name
            LIMIT :limit OFFSET :offset
        """
        
        params['limit'] = per_page
        params['offset'] = offset
        
        restaurants = db.execute(text(restaurants_query), params).fetchall()
        
        # Calculate pagination variables
        total_pages = (total_count + per_page - 1) // per_page
        has_prev = page > 1
        has_next = page < total_pages
        prev_num = page - 1 if has_prev else None
        next_num = page + 1 if has_next else None
        
        db.close()
        
        return render_template('admin/restaurants.html', 
                             restaurants=restaurants,
                             page=page,
                             total_count=total_count,
                             total_pages=total_pages,
                             has_prev=has_prev,
                             has_next=has_next,
                             prev_num=prev_num,
                             next_num=next_num,
                             search_term=search_term)
        
    except Exception as e:
        flash(f'Error loading restaurants: {str(e)}', 'error')
        return render_template('admin/restaurants.html', 
                             restaurants=[],
                             page=1,
                             total_count=0,
                             total_pages=1,
                             has_prev=False,
                             has_next=False,
                             prev_num=None,
                             next_num=None,
                             search_term=search_term)

@admin_bp.route('/admin/restaurants/edit/<restaurant_id>')
def edit_restaurant(restaurant_id):
    """Edit restaurant"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    db = None
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        db = SessionLocal()
        
        # Import models inside the function to avoid circular imports
        import sys
        import os
        sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
        from app.models.business import Restaurant
        from app.models.company import Company
        from app.models.user import Country
        from app.models.beach import BeachPlace
        
        # Fetch the restaurant by ID with photos
        restaurant = db.query(Restaurant).filter(Restaurant.restaurant_id == restaurant_id).first()
        # Explicitly load photos relationship
        if restaurant:
            _ = restaurant.photos  # Access photos to load the relationship
        if not restaurant:
            flash('Restaurant not found.', 'error')
            return redirect(url_for('admin.restaurants'))
        
        # Fetch all companies for the dropdown
        companies = db.query(Company).order_by(Company.company_name).all()
        
        # Fetch all countries for the dropdown
        countries = db.query(Country).order_by(Country.country_name).all()
        
        # Fetch all beach places for the dropdown
        beach_places = db.query(BeachPlace).order_by(BeachPlace.beach_name).all()
        
        return render_template('admin/edit_restaurant.html', 
                             restaurant=restaurant,
                             companies=companies,
                             countries=countries,
                             beach_places=beach_places)
    except Exception as e:
        print(f"Error fetching restaurant data: {e}")
        flash('An error occurred while fetching restaurant data.', 'error')
        return redirect(url_for('admin.restaurants'))
    finally:
        if db:
            try:
                db.close()
            except:
                pass

@admin_bp.route('/admin/beaches/edit/<beach_id>')
def edit_beach(beach_id):
    """Edit beach"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    db = None
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        db = SessionLocal()
        
        # Import models inside the function to avoid circular imports
        import sys
        import os
        sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
        from app.models.beach import BeachPlace
        from app.models.company import Company
        from app.models.user import Country
        
        # Fetch the beach by ID with photos
        beach = db.query(BeachPlace).filter(BeachPlace.beach_place_id == beach_id).first()
        # Explicitly load photos relationship
        if beach:
            _ = beach.photos  # Access photos to load the relationship
        if not beach:
            flash('Beach not found.', 'error')
            return redirect(url_for('admin.beaches'))
        
        # Fetch all companies for the dropdown
        companies = db.query(Company).order_by(Company.company_name).all()
        
        # Fetch all countries for the dropdown
        countries = db.query(Country).order_by(Country.country_name).all()
        
        return render_template('admin/edit_beach.html', 
                             beach=beach,
                             companies=companies,
                             countries=countries)
    except Exception as e:
        print(f"Error fetching beach data: {e}")
        flash('An error occurred while fetching beach data.', 'error')
        return redirect(url_for('admin.beaches'))
    finally:
        if db:
            try:
                db.close()
            except:
                pass

@admin_bp.route('/admin/markets')
def markets():
    """Manage markets"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    # Get search and filter parameters
    search_term = request.args.get('search', '').strip()
    
    # Check if this is a search request (search parameter exists in URL)
    is_search_request = 'search' in request.args
    
    # Only load data if this is a search request
    if not is_search_request:
        # First page load, return empty results
        return render_template('admin/markets.html', 
                             markets=[],
                             page=1,
                             total_count=0,
                             total_pages=1,
                             has_prev=False,
                             has_next=False,
                             prev_num=None,
                             next_num=None,
                             search_term='')
    
    # This is a search request, load data from database
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        
        db = SessionLocal()
        
        # Get the page number from request args, default to 1
        page = request.args.get('page', 1, type=int)
        per_page = 10
        offset = (page - 1) * per_page
        
        # Build the WHERE clause for filtering
        where_conditions = []
        params = {}
        
        if search_term:
            where_conditions.append("(m.market_name ILIKE :search OR m.phone ILIKE :search OR m.email ILIKE :search)")
            params['search'] = f'%{search_term}%'
        
        where_clause = "WHERE " + " AND ".join(where_conditions) if where_conditions else ""
        
        # Get total count for pagination
        count_query = f"SELECT COUNT(*) FROM markets m {where_clause}"
        count_result = db.execute(text(count_query), params).fetchone()
        total_count = count_result[0] if count_result else 0
        
        # Get markets with company and country info
        markets_query = f"""
            SELECT m.market_id, m.market_name, m.address, m.city,
                   c.company_name, co.country_name, m.phone, m.email,
                   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
            {where_clause}
            ORDER BY m.market_name
            LIMIT :limit OFFSET :offset
        """
        
        params['limit'] = per_page
        params['offset'] = offset
        
        markets = db.execute(text(markets_query), params).fetchall()
        
        # Calculate pagination variables
        total_pages = (total_count + per_page - 1) // per_page
        has_prev = page > 1
        has_next = page < total_pages
        prev_num = page - 1 if has_prev else None
        next_num = page + 1 if has_next else None
        
        db.close()
        
        return render_template('admin/markets.html', 
                             markets=markets,
                             page=page,
                             total_count=total_count,
                             total_pages=total_pages,
                             has_prev=has_prev,
                             has_next=has_next,
                             prev_num=prev_num,
                             next_num=next_num,
                             search_term=search_term)
        
    except Exception as e:
        flash(f'Error loading markets: {str(e)}', 'error')
        return render_template('admin/markets.html', 
                             markets=[],
                             page=1,
                             total_count=0,
                             total_pages=1,
                             has_prev=False,
                             has_next=False,
                             prev_num=None,
                             next_num=None,
                             search_term=search_term)

@admin_bp.route('/admin/adventures')
def adventures():
    """Manage adventures"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    # Get search and filter parameters
    search_term = request.args.get('search', '').strip()
    
    # Check if this is a search request (search parameter exists in URL)
    is_search_request = 'search' in request.args
    
    # Only load data if this is a search request
    if not is_search_request:
        # First page load, return empty results
        return render_template('admin/adventures.html', 
                             adventures=[],
                             page=1,
                             total_count=0,
                             total_pages=1,
                             has_prev=False,
                             has_next=False,
                             prev_num=None,
                             next_num=None,
                             search_term='')
    
    # This is a search request, load data from database
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        
        db = SessionLocal()
        
        # Get the page number from request args, default to 1
        page = request.args.get('page', 1, type=int)
        per_page = 10
        offset = (page - 1) * per_page
        
        # Build the WHERE clause for filtering
        where_conditions = []
        params = {}
        
        if search_term:
            where_conditions.append("(a.adventure_name ILIKE :search OR a.phone ILIKE :search OR a.email ILIKE :search)")
            params['search'] = f'%{search_term}%'
        
        where_clause = "WHERE " + " AND ".join(where_conditions) if where_conditions else ""
        
        # Get total count for pagination
        count_query = f"SELECT COUNT(*) FROM adventures a {where_clause}"
        count_result = db.execute(text(count_query), params).fetchone()
        total_count = count_result[0] if count_result else 0
        
        # Get adventures with company and country info
        adventures_query = f"""
            SELECT a.adventure_id, a.adventure_name, a.address, a.city,
                   c.company_name, co.country_name, a.phone, a.email,
                   a.duration_minutes, a.max_participants, a.price,
                   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
            {where_clause}
            ORDER BY a.adventure_name
            LIMIT :limit OFFSET :offset
        """
        
        params['limit'] = per_page
        params['offset'] = offset
        
        adventures = db.execute(text(adventures_query), params).fetchall()
        
        # Calculate pagination variables
        total_pages = (total_count + per_page - 1) // per_page
        has_prev = page > 1
        has_next = page < total_pages
        prev_num = page - 1 if has_prev else None
        next_num = page + 1 if has_next else None
        
        db.close()
        
        return render_template('admin/adventures.html', 
                             adventures=adventures,
                             page=page,
                             total_count=total_count,
                             total_pages=total_pages,
                             has_prev=has_prev,
                             has_next=has_next,
                             prev_num=prev_num,
                             next_num=next_num,
                             search_term=search_term)
        
    except Exception as e:
        flash(f'Error loading adventures: {str(e)}', 'error')
        return render_template('admin/adventures.html', 
                             adventures=[],
                             page=1,
                             total_count=0,
                             total_pages=1,
                             has_prev=False,
                             has_next=False,
                             prev_num=None,
                             next_num=None,
                             search_term=search_term)

@admin_bp.route('/admin/markets/edit/<market_id>')
def edit_market(market_id):
    """Edit market"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    db = None
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        db = SessionLocal()
        
        # Import models inside the function to avoid circular imports
        import sys
        import os

@admin_bp.route('/beaches/<beach_id>/design', methods=['GET'])
@login_required
def beach_design(beach_id):
    """Full screen beach design page"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    try:
        # Redirect to the static Vue application with just the beach ID
        return redirect(f"/static/BeachDesign/index.html?beach_id={beach_id}")
        
    except Exception as e:
        error_msg = f'Error loading beach design: {str(e)}'
        print(f"BEACH DESIGN ERROR: {error_msg}")
        flash(error_msg, 'error')
        # Redirect back to beach edit page
        return redirect(url_for('admin.edit_beach', beach_id=beach_id))

        sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
        from app.models.business import Market
        from app.models.company import Company
        from app.models.user import Country
        from app.models.beach import BeachPlace
        
        # Fetch the market by ID with photos
        market = db.query(Market).filter(Market.market_id == market_id).first()
        # Explicitly load photos relationship
        if market:
            _ = market.photos  # Access photos to load the relationship
        if not market:
            flash('Market not found.', 'error')
            return redirect(url_for('admin.markets'))
        
        # Fetch all companies for the dropdown
        companies = db.query(Company).order_by(Company.company_name).all()
        
        # Fetch all countries for the dropdown
        countries = db.query(Country).order_by(Country.country_name).all()
        
        # Fetch all beach places for the dropdown
        beach_places = db.query(BeachPlace).order_by(BeachPlace.beach_name).all()
        
        return render_template('admin/edit_market.html', 
                             market=market,
                             companies=companies,
                             countries=countries,
                             beach_places=beach_places)
    except Exception as e:
        print(f"Error fetching market data: {e}")
        flash('An error occurred while fetching market data.', 'error')
        return redirect(url_for('admin.markets'))
    finally:
        if db:
            try:
                db.close()
            except:
                pass

@admin_bp.route('/admin/adventures/edit/<adventure_id>', methods=['GET'])
def edit_adventure(adventure_id):
    """Edit adventure"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    db = None
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        db = SessionLocal()
        
        # Import models inside the function to avoid circular imports
        import sys
        import os
        sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
        from app.models.business import Adventure
        from app.models.company import Company
        from app.models.user import Country, Currency
        from app.models.beach import BeachPlace
        
        # Fetch the adventure by ID with photos
        adventure = db.query(Adventure).filter(Adventure.adventure_id == adventure_id).first()
        # Explicitly load photos relationship
        if adventure:
            _ = adventure.photos  # Access photos to load the relationship
        if not adventure:
            flash('Adventure not found.', 'error')
            return redirect(url_for('admin.adventures'))
        
        # Fetch all companies for the dropdown
        companies = db.query(Company).order_by(Company.company_name).all()
        
        # Fetch all countries for the dropdown
        countries = db.query(Country).order_by(Country.country_name).all()
        
        # Fetch all currencies for the dropdown
        currencies = db.query(Currency).order_by(Currency.currency_name).all()
        
        # Fetch all beach places for the dropdown
        beach_places = db.query(BeachPlace).order_by(BeachPlace.beach_name).all()
        
        return render_template('admin/edit_adventure.html', 
                             adventure=adventure,
                             companies=companies,
                             countries=countries,
                             currencies=currencies,
                             beach_places=beach_places)
    except Exception as e:
        print(f"Error fetching adventure data: {e}")
        flash('An error occurred while fetching adventure data.', 'error')
        return redirect(url_for('admin.adventures'))
    finally:
        if db:
            try:
                db.close()
            except:
                pass

@admin_bp.route('/admin/adventures/update/<adventure_id>', methods=['POST'])
def update_adventure(adventure_id):
    """Update adventure"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    try:
        # Get form data
        adventure_name = request.form.get('adventure_name', '').strip()
        company_id = request.form.get('company_id', '').strip()
        phone = request.form.get('phone', '').strip()
        email = request.form.get('email', '').strip()
        address = request.form.get('address', '').strip()
        city = request.form.get('city', '').strip()
        country_id = request.form.get('country_id', '').strip()
        duration_minutes = request.form.get('duration_minutes', '').strip()
        max_participants = request.form.get('max_participants', '').strip()
        min_participants = request.form.get('min_participants', '').strip()
        price = request.form.get('price', '').strip()
        currency_id = request.form.get('currency_id', '').strip()
        beach_place_id = request.form.get('beach_place_id', '').strip()
        description = request.form.get('description', '').strip()
        website = request.form.get('website', '').strip()
        equipment_provided = request.form.get('equipment_provided', '').strip()
        requirements = request.form.get('requirements', '').strip()
        safety_instructions = request.form.get('safety_instructions', '').strip()
        photo_path = request.form.get('photo_path', '').strip()
        latitude = request.form.get('latitude', '').strip()
        longitude = request.form.get('longitude', '').strip()
        is_active = request.form.get('is_active', '').strip()
        
        # Validate required fields
        if not adventure_name or not company_id:
            flash('Adventure name and company are required.', 'error')
            return redirect(url_for('admin.edit_adventure', adventure_id=adventure_id))
        
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        
        db = SessionLocal()
        
        # Update adventure
        update_query = """
            UPDATE adventures 
            SET adventure_name = :adventure_name,
                company_id = :company_id,
                phone = :phone,
                email = :email,
                address = :address,
                city = :city,
                country_id = :country_id,
                duration_minutes = :duration_minutes,
                max_participants = :max_participants,
                min_participants = :min_participants,
                price = :price,
                currency_id = :currency_id,
                beach_place_id = :beach_place_id,
                description = :description,
                website = :website,
                equipment_provided = :equipment_provided,
                requirements = :requirements,
                safety_instructions = :safety_instructions,
                photo_path = :photo_path,
                latitude = :latitude,
                longitude = :longitude,
                is_active = :is_active,
                updated_at = NOW()
            WHERE adventure_id = :adventure_id
        """
        
        def safe_int(value):
            if value:
                try:
                    return int(value)
                except ValueError:
                    return None
            return None
            
        def safe_float(value):
            if value:
                try:
                    return float(value)
                except ValueError:
                    return None
            return None
            
        def safe_uuid(value):
            if value:
                try:
                    import uuid
                    return uuid.UUID(value)
                except ValueError:
                    return None
            return None
        
        params = {
            'adventure_name': adventure_name,
            'company_id': safe_uuid(company_id),
            'phone': phone if phone else None,
            'email': email if email else None,
            'address': address if address else None,
            'city': city if city else None,
            'country_id': safe_int(country_id),
            'duration_minutes': safe_int(duration_minutes),
            'max_participants': safe_int(max_participants),
            'min_participants': safe_int(min_participants),
            'price': safe_float(price),
            'currency_id': safe_int(currency_id),
            'beach_place_id': safe_uuid(beach_place_id),
            'description': description if description else None,
            'website': website if website else None,
            'equipment_provided': equipment_provided if equipment_provided else None,
            'requirements': requirements if requirements else None,
            'safety_instructions': safety_instructions if safety_instructions else None,
            'photo_path': photo_path if photo_path else None,
            'latitude': safe_float(latitude),
            'longitude': safe_float(longitude),
            'is_active': is_active.lower() == 'on' if is_active else False,
            'adventure_id': adventure_id
        }
        
        db.execute(text(update_query), params)
        db.commit()
        db.close()
        
        flash('Adventure updated successfully.', 'success')
        return redirect(url_for('admin.adventures'))
        
    except Exception as e:
        flash(f'Error updating adventure: {str(e)}', 'error')
        return redirect(url_for('admin.edit_adventure', adventure_id=adventure_id))

@admin_bp.route('/admin/companies/edit/<company_id>', methods=['GET'])
def edit_company(company_id):
    """Edit company"""
    # Check if user is logged in and is admin
    if not session.get('is_admin'):
        flash('You must be logged in as an administrator to access this page.', 'error')
        return redirect(url_for('admin.admin_login'))
    
    db = None
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        db = SessionLocal()
        
        # Import models inside the function to avoid circular imports
        import sys
        import os
        sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
        from app.models.company import Company
        from app.models.user import Country
        
        # Fetch the company by ID
        company = db.query(Company).filter(Company.company_id == company_id).first()
        if not company:
            flash('Company not found.', 'error')
            return redirect(url_for('admin.companies'))
        
        # Fetch all countries for the dropdown
        countries = db.query(Country).order_by(Country.country_name).all()
        
        return render_template('admin/edit_company.html', 
                             company=company,
                             countries=countries)
    except Exception as e:
        print(f"Error fetching company data: {e}")
        flash('An error occurred while fetching company data.', 'error')
        return redirect(url_for('admin.companies'))
    finally:
        if db:
            try:
                db.close()
            except:
                pass
# More admin routes will be added here...


# Photo upload endpoints
@admin_bp.route('/api/beach-photos/<beach_id>', methods=['POST'])
def upload_beach_photos(beach_id):
    """Upload photos for a beach place"""
    try:
        # Check if user is logged in and is admin
        if not session.get('is_admin'):
            return jsonify({'error': 'Unauthorized'}), 401
        
        database_password = ADMIN_CONFIG.get('DATABASE_PASSWORD', 'AdminPassword123!')
        database_url = f"postgresql://{os.getenv('DATABASE_USER', 'postgres')}:{quote_plus(database_password)}@{os.getenv('DATABASE_HOST', 'localhost')}:{os.getenv('DATABASE_PORT', '5432')}/{os.getenv('DATABASE_NAME', 'bookbeach')}"
        engine = create_engine(database_url)
        
        if 'beach_photos' not in request.files:
            return jsonify({'error': 'No photos uploaded'}), 400
        
        photos = request.files.getlist('beach_photos')
        uploaded_photos = []
        
        with engine.connect() as db:
            # Check if there's already a primary photo for this beach
            existing_primary = db.execute(text("""
                SELECT COUNT(*) as count FROM beach_places_photos 
                WHERE beach_place_id = :beach_id AND photo_primary = true
            """), {'beach_id': beach_id}).fetchone()
            
            has_primary = existing_primary[0] > 0 if existing_primary else False
            
            # Get the next sort order
            max_sort = db.execute(text("""
                SELECT COALESCE(MAX(sort_order), -1) as max_sort 
                FROM beach_places_photos 
                WHERE beach_place_id = :beach_id
            """), {'beach_id': beach_id}).fetchone()
            
            next_sort_order = max_sort[0] + 1 if max_sort else 0
            
            for i, photo in enumerate(photos):
                if photo.filename and photo.filename != '':
                    try:
                        # Save photo and add to database
                        filename = secure_filename(photo.filename)
                        import uuid
                        file_ext = filename.rsplit('.', 1)[1].lower() if '.' in filename else 'jpg'
                        unique_filename = f"{uuid.uuid4().hex}.{file_ext}"
                        
                        # Create upload directory with absolute path
                        backend_dir = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
                        upload_dir = os.path.join(backend_dir, 'static', 'uploads', 'beaches')
                        
                        # Ensure directory exists
                        os.makedirs(upload_dir, exist_ok=True)
                        print(f"Upload directory: {upload_dir}")
                        
                        # Save file with absolute path
                        file_path = os.path.join(upload_dir, unique_filename)
                        print(f"Saving file to: {file_path}")
                        
                        # Save the file
                        photo.save(file_path)
                        
                        # Verify file was saved
                        if os.path.exists(file_path):
                            file_size = os.path.getsize(file_path)
                            print(f"File saved successfully: {file_path} ({file_size} bytes)")
                        else:
                            raise Exception(f"File was not saved: {file_path}")
                        
                        # Only make first photo primary if no existing primary photo
                        is_primary = (i == 0 and not has_primary)
                        
                        # Add to database
                        result = db.execute(text("""
                            INSERT INTO beach_places_photos (
                                beach_place_id, photo_path, photo_primary, sort_order
                            ) VALUES (
                                :beach_id, :photo_path, :is_primary, :sort_order
                            ) RETURNING photo_id
                        """), {
                            'beach_id': beach_id,
                            'photo_path': f"/static/uploads/beaches/{unique_filename}",
                            'is_primary': is_primary,
                            'sort_order': next_sort_order + i
                        })
                        
                        result_row = result.fetchone()
                        photo_id = result_row[0] if result_row else None
                        uploaded_photos.append({
                            'photo_id': photo_id,
                            'photo_path': f"/static/uploads/beaches/{unique_filename}",
                            'is_primary': is_primary
                        })
                        
                    except Exception as photo_error:
                        print(f"Error uploading photo: {photo_error}")
                        return jsonify({'error': f'Failed to upload photo: {str(photo_error)}'}), 500
            
            db.commit()
            
        return jsonify({
            'success': True,
            'message': f'{len(uploaded_photos)} photo(s) uploaded successfully',
            'photos': uploaded_photos
        })
        
    except Exception as e:
        print(f"Error in photo upload: {e}")
        return jsonify({'error': 'Failed to upload photos'}), 500


@admin_bp.route('/api/restaurant-photos/<restaurant_id>', methods=['POST'])
def upload_restaurant_photos(restaurant_id):
    """Upload photos for a restaurant"""
    try:
        # Check if user is logged in and is admin
        if not session.get('is_admin'):
            return jsonify({'error': 'Unauthorized'}), 401
        
        database_password = ADMIN_CONFIG.get('DATABASE_PASSWORD', 'AdminPassword123!')
        database_url = f"postgresql://{os.getenv('DATABASE_USER', 'postgres')}:{quote_plus(database_password)}@{os.getenv('DATABASE_HOST', 'localhost')}:{os.getenv('DATABASE_PORT', '5432')}/{os.getenv('DATABASE_NAME', 'bookbeach')}"
        engine = create_engine(database_url)
        
        if 'restaurant_photos' not in request.files:
            return jsonify({'error': 'No photos uploaded'}), 400
        
        photos = request.files.getlist('restaurant_photos')
        uploaded_photos = []
        
        with engine.connect() as db:
            # Check if there's already a primary photo for this restaurant
            existing_primary = db.execute(text("""
                SELECT COUNT(*) as count FROM restaurant_photos 
                WHERE restaurant_id = :restaurant_id AND is_primary = true
            """), {'restaurant_id': restaurant_id}).fetchone()
            
            has_primary = existing_primary[0] > 0 if existing_primary else False
            
            # Get the next sort order
            max_sort = db.execute(text("""
                SELECT COALESCE(MAX(photo_id), 0) as max_sort 
                FROM restaurant_photos 
                WHERE restaurant_id = :restaurant_id
            """), {'restaurant_id': restaurant_id}).fetchone()
            
            next_sort_order = max_sort[0] + 1 if max_sort else 1
            
            for i, photo in enumerate(photos):
                if photo.filename and photo.filename != '':
                    try:
                        # Save photo and add to database
                        filename = secure_filename(photo.filename)
                        import uuid
                        file_ext = filename.rsplit('.', 1)[1].lower() if '.' in filename else 'jpg'
                        unique_filename = f"{uuid.uuid4().hex}.{file_ext}"
                        
                        # Create upload directory with absolute path
                        backend_dir = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
                        upload_dir = os.path.join(backend_dir, 'static', 'uploads', 'restaurant_photos')
                        
                        # Ensure directory exists
                        os.makedirs(upload_dir, exist_ok=True)
                        print(f"Upload directory: {upload_dir}")
                        
                        # Save file with absolute path
                        file_path = os.path.join(upload_dir, unique_filename)
                        print(f"Saving file to: {file_path}")
                        
                        # Save the file
                        photo.save(file_path)
                        
                        # Verify file was saved
                        if os.path.exists(file_path):
                            file_size = os.path.getsize(file_path)
                            print(f"File saved successfully: {file_path} ({file_size} bytes)")
                        else:
                            raise Exception(f"File was not saved: {file_path}")
                        
                        # Only make first photo primary if no existing primary photo
                        is_primary = (i == 0 and not has_primary)
                        
                        # Add to database
                        result = db.execute(text("""
                            INSERT INTO restaurant_photos (
                                restaurant_id, photo_path, is_primary
                            ) VALUES (
                                :restaurant_id, :photo_path, :is_primary
                            ) RETURNING photo_id
                        """), {
                            'restaurant_id': restaurant_id,
                            'photo_path': f"/static/uploads/restaurant_photos/{unique_filename}",
                            'is_primary': is_primary
                        })
                        
                        result_row = result.fetchone()
                        photo_id = result_row[0] if result_row else None
                        if photo_id:
                            uploaded_photos.append({
                                'photo_id': photo_id,
                                'photo_path': f"/static/uploads/restaurant_photos/{unique_filename}",
                                'is_primary': is_primary
                            })
                        
                    except Exception as photo_error:
                        print(f"Error uploading photo: {photo_error}")
                        return jsonify({'error': f'Failed to upload photo: {str(photo_error)}'}), 500
            
            db.commit()
            
        return jsonify({
            'success': True,
            'message': f'{len(uploaded_photos)} photo(s) uploaded successfully',
            'photos': uploaded_photos
        })
        
    except Exception as e:
        print(f"Error in photo upload: {e}")
        return jsonify({'error': 'Failed to upload photos'}), 500


@admin_bp.route('/api/market-photos/<market_id>', methods=['POST'])
def upload_market_photos(market_id):
    """Upload photos for a market"""
    try:
        # Check if user is logged in and is admin
        if not session.get('is_admin'):
            return jsonify({'error': 'Unauthorized'}), 401
        
        database_password = ADMIN_CONFIG.get('DATABASE_PASSWORD', 'AdminPassword123!')
        database_url = f"postgresql://{os.getenv('DATABASE_USER', 'postgres')}:{quote_plus(database_password)}@{os.getenv('DATABASE_HOST', 'localhost')}:{os.getenv('DATABASE_PORT', '5432')}/{os.getenv('DATABASE_NAME', 'bookbeach')}"
        engine = create_engine(database_url)
        
        if 'market_photos' not in request.files:
            return jsonify({'error': 'No photos uploaded'}), 400
        
        photos = request.files.getlist('market_photos')
        uploaded_photos = []
        
        with engine.connect() as db:
            # Check if there's already a primary photo for this market
            existing_primary = db.execute(text("""
                SELECT COUNT(*) as count FROM market_photos 
                WHERE market_id = :market_id AND is_primary = true
            """), {'market_id': market_id}).fetchone()
            
            has_primary = existing_primary.count > 0
            
            # Get the next sort order
            max_sort = db.execute(text("""
                SELECT COALESCE(MAX(photo_id), 0) as max_sort 
                FROM market_photos 
                WHERE market_id = :market_id
            """), {'market_id': market_id}).fetchone()
            
            next_sort_order = max_sort.max_sort + 1
            
            for i, photo in enumerate(photos):
                if photo.filename != '':
                    try:
                        # Save photo and add to database
                        filename = secure_filename(photo.filename)
                        import uuid
                        file_ext = filename.rsplit('.', 1)[1].lower() if '.' in filename else 'jpg'
                        unique_filename = f"{uuid.uuid4().hex}.{file_ext}"
                        
                        # Create upload directory with absolute path
                        backend_dir = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
                        upload_dir = os.path.join(backend_dir, 'static', 'uploads', 'markets')
                        
                        # Ensure directory exists
                        os.makedirs(upload_dir, exist_ok=True)
                        print(f"Upload directory: {upload_dir}")
                        
                        # Save file with absolute path
                        file_path = os.path.join(upload_dir, unique_filename)
                        print(f"Saving file to: {file_path}")
                        
                        # Save the file
                        photo.save(file_path)
                        
                        # Verify file was saved
                        if os.path.exists(file_path):
                            file_size = os.path.getsize(file_path)
                            print(f"File saved successfully: {file_path} ({file_size} bytes)")
                        else:
                            raise Exception(f"File was not saved: {file_path}")
                        
                        # Only make first photo primary if no existing primary photo
                        is_primary = (i == 0 and not has_primary)
                        
                        # Add to database
                        result = db.execute(text("""
                            INSERT INTO market_photos (
                                market_id, photo_path, is_primary
                            ) VALUES (
                                :market_id, :photo_path, :is_primary
                            ) RETURNING photo_id
                        """), {
                            'market_id': market_id,
                            'photo_path': f"/static/uploads/markets/{unique_filename}",
                            'is_primary': is_primary
                        })
                        
                        photo_id = result.fetchone()[0]
                        uploaded_photos.append({
                            'photo_id': photo_id,
                            'photo_path': f"/static/uploads/markets/{unique_filename}",
                            'is_primary': is_primary
                        })
                        
                    except Exception as photo_error:
                        print(f"Error uploading photo: {photo_error}")
                        return jsonify({'error': f'Failed to upload photo: {str(photo_error)}'}), 500
            
            db.commit()
            
        return jsonify({
            'success': True,
            'message': f'{len(uploaded_photos)} photo(s) uploaded successfully',
            'photos': uploaded_photos
        })
        
    except Exception as e:
        print(f"Error in photo upload: {e}")
        return jsonify({'error': 'Failed to upload photos'}), 500


@admin_bp.route('/api/adventure-photos/<adventure_id>', methods=['POST'])
def upload_adventure_photos(adventure_id):
    """Upload photos for an adventure"""
    try:
        # Check if user is logged in and is admin
        if not session.get('is_admin'):
            return jsonify({'error': 'Unauthorized'}), 401
        
        database_password = ADMIN_CONFIG.get('DATABASE_PASSWORD', 'AdminPassword123!')
        database_url = f"postgresql://{os.getenv('DATABASE_USER', 'postgres')}:{quote_plus(database_password)}@{os.getenv('DATABASE_HOST', 'localhost')}:{os.getenv('DATABASE_PORT', '5432')}/{os.getenv('DATABASE_NAME', 'bookbeach')}"
        engine = create_engine(database_url)
        
        if 'adventure_photos' not in request.files:
            return jsonify({'error': 'No photos uploaded'}), 400
        
        photos = request.files.getlist('adventure_photos')
        uploaded_photos = []
        
        with engine.connect() as db:
            # Check if there's already a primary photo for this adventure
            existing_primary = db.execute(text("""
                SELECT COUNT(*) as count FROM adventure_photos 
                WHERE adventure_id = :adventure_id AND is_primary = true
            """), {'adventure_id': adventure_id}).fetchone()
            
            has_primary = existing_primary.count > 0
            
            # Get the next sort order
            max_sort = db.execute(text("""
                SELECT COALESCE(MAX(photo_id), 0) as max_sort 
                FROM adventure_photos 
                WHERE adventure_id = :adventure_id
            """), {'adventure_id': adventure_id}).fetchone()
            
            next_sort_order = max_sort.max_sort + 1
            
            for i, photo in enumerate(photos):
                if photo.filename != '':
                    try:
                        # Save photo and add to database
                        filename = secure_filename(photo.filename)
                        import uuid
                        file_ext = filename.rsplit('.', 1)[1].lower() if '.' in filename else 'jpg'
                        unique_filename = f"{uuid.uuid4().hex}.{file_ext}"
                        
                        # Create upload directory with absolute path
                        backend_dir = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
                        upload_dir = os.path.join(backend_dir, 'static', 'uploads', 'adventures')
                        
                        # Ensure directory exists
                        os.makedirs(upload_dir, exist_ok=True)
                        print(f"Upload directory: {upload_dir}")
                        
                        # Save file with absolute path
                        file_path = os.path.join(upload_dir, unique_filename)
                        print(f"Saving file to: {file_path}")
                        
                        # Save the file
                        photo.save(file_path)
                        
                        # Verify file was saved
                        if os.path.exists(file_path):
                            file_size = os.path.getsize(file_path)
                            print(f"File saved successfully: {file_path} ({file_size} bytes)")
                        else:
                            raise Exception(f"File was not saved: {file_path}")
                        
                        # Only make first photo primary if no existing primary photo
                        is_primary = (i == 0 and not has_primary)
                        
                        # Add to database
                        result = db.execute(text("""
                            INSERT INTO adventure_photos (
                                adventure_id, photo_path, is_primary
                            ) VALUES (
                                :adventure_id, :photo_path, :is_primary
                            ) RETURNING photo_id
                        """), {
                            'adventure_id': adventure_id,
                            'photo_path': f"/static/uploads/adventures/{unique_filename}",
                            'is_primary': is_primary
                        })
                        
                        photo_id = result.fetchone()[0]
                        uploaded_photos.append({
                            'photo_id': photo_id,
                            'photo_path': f"/static/uploads/adventures/{unique_filename}",
                            'is_primary': is_primary
                        })
                        
                    except Exception as photo_error:
                        print(f"Error uploading photo: {photo_error}")
                        return jsonify({'error': f'Failed to upload photo: {str(photo_error)}'}), 500
            
            db.commit()
            
        return jsonify({
            'success': True,
            'message': f'{len(uploaded_photos)} photo(s) uploaded successfully',
            'photos': uploaded_photos
        })
        
    except Exception as e:
        print(f"Error in photo upload: {e}")
        return jsonify({'error': 'Failed to upload photos'}), 500


@admin_bp.route('/api/beach-photos/<photo_id>/set-primary', methods=['PUT'])
def set_beach_photo_primary(photo_id):
    """Set a photo as the primary photo for its beach"""
    try:
        # Check if user is logged in and is admin
        if not session.get('is_admin'):
            return jsonify({'error': 'Unauthorized'}), 401
        
        database_password = ADMIN_CONFIG.get('DATABASE_PASSWORD', 'AdminPassword123!')
        database_url = f"postgresql://{os.getenv('DATABASE_USER', 'postgres')}:{quote_plus(database_password)}@{os.getenv('DATABASE_HOST', 'localhost')}:{os.getenv('DATABASE_PORT', '5432')}/{os.getenv('DATABASE_NAME', 'bookbeach')}"
        engine = create_engine(database_url)
        
        with engine.connect() as db:
            # Get the beach_place_id for this photo
            photo_result = db.execute(text("""
                SELECT beach_place_id FROM beach_places_photos 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id}).fetchone()
            
            if not photo_result:
                return jsonify({'error': 'Photo not found'}), 404
            
            beach_place_id = photo_result.beach_place_id
            
            # First, set all photos for this beach as non-primary
            db.execute(text("""
                UPDATE beach_places_photos 
                SET photo_primary = false 
                WHERE beach_place_id = :beach_place_id
            """), {"beach_place_id": beach_place_id})
            
            # Then set the specified photo as primary
            db.execute(text("""
                UPDATE beach_places_photos 
                SET photo_primary = true 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id})
            
            db.commit()
            
            return jsonify({'success': True, 'message': 'Photo set as primary successfully'})
    
    except Exception as e:
        print(f"Error setting photo as primary: {e}")
        return jsonify({'error': 'Failed to set photo as primary'}), 500


@admin_bp.route('/api/restaurant-photos/<photo_id>/set-primary', methods=['PUT'])
def set_restaurant_photo_primary(photo_id):
    """Set a photo as the primary photo for its restaurant"""
    try:
        # Check if user is logged in and is admin
        if not session.get('is_admin'):
            return jsonify({'error': 'Unauthorized'}), 401
        
        database_password = ADMIN_CONFIG.get('DATABASE_PASSWORD', 'AdminPassword123!')
        database_url = f"postgresql://{os.getenv('DATABASE_USER', 'postgres')}:{quote_plus(database_password)}@{os.getenv('DATABASE_HOST', 'localhost')}:{os.getenv('DATABASE_PORT', '5432')}/{os.getenv('DATABASE_NAME', 'bookbeach')}"
        engine = create_engine(database_url)
        
        with engine.connect() as db:
            # Get the restaurant_id for this photo
            photo_result = db.execute(text("""
                SELECT restaurant_id FROM restaurant_photos 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id}).fetchone()
            
            if not photo_result:
                return jsonify({'error': 'Photo not found'}), 404
            
            restaurant_id = photo_result.restaurant_id
            
            # First, set all photos for this restaurant as non-primary
            db.execute(text("""
                UPDATE restaurant_photos 
                SET is_primary = false 
                WHERE restaurant_id = :restaurant_id
            """), {"restaurant_id": restaurant_id})
            
            # Then set the specified photo as primary
            db.execute(text("""
                UPDATE restaurant_photos 
                SET is_primary = true 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id})
            
            db.commit()
            
            return jsonify({'success': True, 'message': 'Photo set as primary successfully'})
    
    except Exception as e:
        print(f"Error setting photo as primary: {e}")
        return jsonify({'error': 'Failed to set photo as primary'}), 500


@admin_bp.route('/api/market-photos/<photo_id>/set-primary', methods=['PUT'])
def set_market_photo_primary(photo_id):
    """Set a photo as the primary photo for its market"""
    try:
        # Check if user is logged in and is admin
        if not session.get('is_admin'):
            return jsonify({'error': 'Unauthorized'}), 401
        
        database_password = ADMIN_CONFIG.get('DATABASE_PASSWORD', 'AdminPassword123!')
        database_url = f"postgresql://{os.getenv('DATABASE_USER', 'postgres')}:{quote_plus(database_password)}@{os.getenv('DATABASE_HOST', 'localhost')}:{os.getenv('DATABASE_PORT', '5432')}/{os.getenv('DATABASE_NAME', 'bookbeach')}"
        engine = create_engine(database_url)
        
        with engine.connect() as db:
            # Get the market_id for this photo
            photo_result = db.execute(text("""
                SELECT market_id FROM market_photos 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id}).fetchone()
            
            if not photo_result:
                return jsonify({'error': 'Photo not found'}), 404
            
            market_id = photo_result.market_id
            
            # First, set all photos for this market as non-primary
            db.execute(text("""
                UPDATE market_photos 
                SET is_primary = false 
                WHERE market_id = :market_id
            """), {"market_id": market_id})
            
            # Then set the specified photo as primary
            db.execute(text("""
                UPDATE market_photos 
                SET is_primary = true 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id})
            
            db.commit()
            
            return jsonify({'success': True, 'message': 'Photo set as primary successfully'})
    
    except Exception as e:
        print(f"Error setting photo as primary: {e}")
        return jsonify({'error': 'Failed to set photo as primary'}), 500


@admin_bp.route('/api/adventure-photos/<photo_id>/set-primary', methods=['PUT'])
def set_adventure_photo_primary(photo_id):
    """Set a photo as the primary photo for its adventure"""
    try:
        # Check if user is logged in and is admin
        if not session.get('is_admin'):
            return jsonify({'error': 'Unauthorized'}), 401
        
        database_password = ADMIN_CONFIG.get('DATABASE_PASSWORD', 'AdminPassword123!')
        database_url = f"postgresql://{os.getenv('DATABASE_USER', 'postgres')}:{quote_plus(database_password)}@{os.getenv('DATABASE_HOST', 'localhost')}:{os.getenv('DATABASE_PORT', '5432')}/{os.getenv('DATABASE_NAME', 'bookbeach')}"
        engine = create_engine(database_url)
        
        with engine.connect() as db:
            # Get the adventure_id for this photo
            photo_result = db.execute(text("""
                SELECT adventure_id FROM adventure_photos 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id}).fetchone()
            
            if not photo_result:
                return jsonify({'error': 'Photo not found'}), 404
            
            adventure_id = photo_result.adventure_id
            
            # First, set all photos for this adventure as non-primary
            db.execute(text("""
                UPDATE adventure_photos 
                SET is_primary = false 
                WHERE adventure_id = :adventure_id
            """), {"adventure_id": adventure_id})
            
            # Then set the specified photo as primary
            db.execute(text("""
                UPDATE adventure_photos 
                SET is_primary = true 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id})
            
            db.commit()
            
            return jsonify({'success': True, 'message': 'Photo set as primary successfully'})
    
    except Exception as e:
        print(f"Error setting photo as primary: {e}")
        return jsonify({'error': 'Failed to set photo as primary'}), 500


@admin_bp.route('/api/beach-photos/<photo_id>', methods=['DELETE'])
def delete_beach_photo(photo_id):
    """Delete a beach photo"""
    try:
        # Check if user is logged in and is admin
        if not session.get('is_admin'):
            return jsonify({'error': 'Unauthorized'}), 401
        
        # Database connection
        database_password = ADMIN_CONFIG.get('DATABASE_PASSWORD', 'AdminPassword123!')
        database_url = f"postgresql://{os.getenv('DATABASE_USER', 'postgres')}:{quote_plus(database_password)}@{os.getenv('DATABASE_HOST', 'localhost')}:{os.getenv('DATABASE_PORT', '5432')}/{os.getenv('DATABASE_NAME', 'bookbeach')}"
        engine = create_engine(database_url)
        
        with engine.connect() as db:
            # Get photo path before deletion
            photo_result = db.execute(text("""
                SELECT photo_path FROM beach_places_photos 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id}).fetchone()
            
            if not photo_result:
                return jsonify({'error': 'Photo not found'}), 404
            
            # Delete from database
            db.execute(text("""
                DELETE FROM beach_places_photos 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id})
            
            db.commit()
            
            # Try to delete the file
            try:
                photo_path = photo_result.photo_path
                if photo_path.startswith('/static/'):
                    file_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), photo_path[1:])  # Remove leading /
                    if os.path.exists(file_path):
                        os.remove(file_path)
            except Exception as file_error:
                print(f"Error deleting photo file: {file_error}")
            
            return jsonify({'success': True})
    
    except Exception as e:
        print(f"Error deleting beach photo: {e}")
        return jsonify({'error': 'Failed to delete photo'}), 500


@admin_bp.route('/api/restaurant-photos/<photo_id>', methods=['DELETE'])
def delete_restaurant_photo(photo_id):
    """Delete a restaurant photo"""
    try:
        # Check if user is logged in and is admin
        if not session.get('is_admin'):
            return jsonify({'error': 'Unauthorized'}), 401
        
        # Database connection
        database_password = ADMIN_CONFIG.get('DATABASE_PASSWORD', 'AdminPassword123!')
        database_url = f"postgresql://{os.getenv('DATABASE_USER', 'postgres')}:{quote_plus(database_password)}@{os.getenv('DATABASE_HOST', 'localhost')}:{os.getenv('DATABASE_PORT', '5432')}/{os.getenv('DATABASE_NAME', 'bookbeach')}"
        engine = create_engine(database_url)
        
        with engine.connect() as db:
            # Get photo path before deletion
            photo_result = db.execute(text("""
                SELECT photo_path FROM restaurant_photos 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id}).fetchone()
            
            if not photo_result:
                return jsonify({'error': 'Photo not found'}), 404
            
            # Delete from database
            db.execute(text("""
                DELETE FROM restaurant_photos 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id})
            
            db.commit()
            
            # Try to delete the file
            try:
                photo_path = photo_result.photo_path
                if photo_path.startswith('/static/'):
                    file_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), photo_path[1:])  # Remove leading /
                    if os.path.exists(file_path):
                        os.remove(file_path)
            except Exception as file_error:
                print(f"Error deleting photo file: {file_error}")
            
            return jsonify({'success': True})
    
    except Exception as e:
        print(f"Error deleting restaurant photo: {e}")
        return jsonify({'error': 'Failed to delete photo'}), 500


@admin_bp.route('/api/market-photos/<photo_id>', methods=['DELETE'])
def delete_market_photo(photo_id):
    """Delete a market photo"""
    try:
        # Check if user is logged in and is admin
        if not session.get('is_admin'):
            return jsonify({'error': 'Unauthorized'}), 401
        
        # Database connection
        database_password = ADMIN_CONFIG.get('DATABASE_PASSWORD', 'AdminPassword123!')
        database_url = f"postgresql://{os.getenv('DATABASE_USER', 'postgres')}:{quote_plus(database_password)}@{os.getenv('DATABASE_HOST', 'localhost')}:{os.getenv('DATABASE_PORT', '5432')}/{os.getenv('DATABASE_NAME', 'bookbeach')}"
        engine = create_engine(database_url)
        
        with engine.connect() as db:
            # Get photo path before deletion
            photo_result = db.execute(text("""
                SELECT photo_path FROM market_photos 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id}).fetchone()
            
            if not photo_result:
                return jsonify({'error': 'Photo not found'}), 404
            
            # Delete from database
            db.execute(text("""
                DELETE FROM market_photos 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id})
            
            db.commit()
            
            # Try to delete the file
            try:
                photo_path = photo_result.photo_path
                if photo_path.startswith('/static/'):
                    file_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), photo_path[1:])  # Remove leading /
                    if os.path.exists(file_path):
                        os.remove(file_path)
            except Exception as file_error:
                print(f"Error deleting photo file: {file_error}")
            
            return jsonify({'success': True})
    
    except Exception as e:
        print(f"Error deleting market photo: {e}")
        return jsonify({'error': 'Failed to delete photo'}), 500


@admin_bp.route('/api/adventure-photos/<photo_id>', methods=['DELETE'])
def delete_adventure_photo(photo_id):
    """Delete an adventure photo"""
    try:
        # Check if user is logged in and is admin
        if not session.get('is_admin'):
            return jsonify({'error': 'Unauthorized'}), 401
        
        # Database connection
        database_password = ADMIN_CONFIG.get('DATABASE_PASSWORD', 'AdminPassword123!')
        database_url = f"postgresql://{os.getenv('DATABASE_USER', 'postgres')}:{quote_plus(database_password)}@{os.getenv('DATABASE_HOST', 'localhost')}:{os.getenv('DATABASE_PORT', '5432')}/{os.getenv('DATABASE_NAME', 'bookbeach')}"
        engine = create_engine(database_url)
        
        with engine.connect() as db:
            # Get photo path before deletion
            photo_result = db.execute(text("""
                SELECT photo_path FROM adventure_photos 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id}).fetchone()
            
            if not photo_result:
                return jsonify({'error': 'Photo not found'}), 404
            
            # Delete from database
            db.execute(text("""
                DELETE FROM adventure_photos 
                WHERE photo_id = :photo_id
            """), {"photo_id": photo_id})
            
            db.commit()
            
            # Try to delete the file
            try:
                photo_path = photo_result.photo_path
                if photo_path.startswith('/static/'):
                    file_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), photo_path[1:])  # Remove leading /
                    if os.path.exists(file_path):
                        os.remove(file_path)
            except Exception as file_error:
                print(f"Error deleting photo file: {file_error}")
            
            return jsonify({'success': True})
    
    except Exception as e:
        print(f"Error deleting adventure photo: {e}")
        return jsonify({'error': 'Failed to delete photo'}), 500

@admin_bp.route('/beaches/<beach_id>/design', methods=['GET'])