database.py

from datetime import datetime, timedelta
from config import supabase

async def get_or_create_user(wa_id: str, name: str = None) -> dict:
    """Get existing user or create new one"""
    if not supabase:
        return {"wa_id": wa_id, "name": name or "Unknown"}
    
    try:
        # Try to get existing user
        response = supabase.table("users").select("*").eq("wa_id", wa_id).execute()
        
        if response.data:
            # User exists, update if name provided
            user = response.data[0]
            if name and name != user.get("name"):
                supabase.table("users").update({"name": name, "updated_at": datetime.utcnow().isoformat()}).eq("wa_id", wa_id).execute()
                user["name"] = name
            return user
        else:
            # Create new user
            new_user = {
                "wa_id": wa_id,
                "name": name or "Unknown",
                "created_at": datetime.utcnow().isoformat(),
                "updated_at": datetime.utcnow().isoformat()
            }
            response = supabase.table("users").insert(new_user).execute()
            return response.data[0] if response.data else new_user
            
    except Exception as e:
        print(f"Error in get_or_create_user: {e}")
        return {"wa_id": wa_id, "name": name or "Unknown"}

async def update_user_activity(wa_id: str):
    """Update user's last activity timestamp"""
    if not supabase:
        return
    
    try:
        supabase.table("users").update({"updated_at": datetime.utcnow().isoformat()}).eq("wa_id", wa_id).execute()
    except Exception as e:
        print(f"Error updating user activity: {e}")

# --- Session Management ---
async def get_active_session(wa_id: str) -> dict:
    """Get the active session for a user (not ended and within 3 hours)"""
    if not supabase:
        return None
    
    try:
        # Calculate 3 hours ago
        three_hours_ago = datetime.utcnow() - timedelta(hours=3)
        
        response = supabase.table("chat_sessions").select("*").eq("wa_id", wa_id).eq("ended", False).gte("last_activity", three_hours_ago.isoformat()).order("last_activity", desc=True).limit(1).execute()
        
        return response.data[0] if response.data else None
    except Exception as e:
        print(f"Error getting active session: {e}")
        return None

async def create_new_session(wa_id: str) -> dict:
    """Create a new chat session for a user"""
    if not supabase:
        return {"id": "local_session", "wa_id": wa_id, "started_at": datetime.utcnow().isoformat(), "last_activity": datetime.utcnow().isoformat(), "ended": False}
    
    try:
        new_session = {
            "wa_id": wa_id,
            "started_at": datetime.utcnow().isoformat(),
            "last_activity": datetime.utcnow().isoformat(),
            "ended": False
        }
        response = supabase.table("chat_sessions").insert(new_session).execute()
        return response.data[0] if response.data else new_session
    except Exception as e:
        print(f"Error creating new session: {e}")
        return {"id": "local_session", "wa_id": wa_id, "started_at": datetime.utcnow().isoformat(), "last_activity": datetime.utcnow().isoformat(), "ended": False}

async def update_session_activity(session_id: str):
    """Update session's last activity timestamp"""
    if not supabase or session_id == "local_session":
        return
    
    try:
        supabase.table("chat_sessions").update({"last_activity": datetime.utcnow().isoformat()}).eq("id", session_id).execute()
    except Exception as e:
        print(f"Error updating session activity: {e}")

async def end_session(session_id: str):
    """Mark a session as ended"""
    if not supabase or session_id == "local_session":
        return
    
    try:
        supabase.table("chat_sessions").update({"ended": True}).eq("id", session_id).execute()
    except Exception as e:
        print(f"Error ending session: {e}")

# --- Message Management ---
async def save_message(session_id: str, wa_id: str, wamid: str, role: str, content: str):
    """Save a message to the database"""
    if not supabase or session_id == "local_session":
        return
    
    try:
        message = {
            "session_id": session_id,
            "wa_id": wa_id,
            "wamid": wamid,
            "role": role,
            "content": content,
            "created_at": datetime.utcnow().isoformat()
        }
        supabase.table("messages").insert(message).execute()
    except Exception as e:
        print(f"Error saving message: {e}")

async def get_session_messages(session_id: str, limit: int = 10) -> list:
    """Get recent messages for a session (for memory context)"""
    if not supabase or session_id == "local_session":
        return []
    
    try:
        response = supabase.table("messages").select("*").eq("session_id", session_id).order("created_at", desc=True).limit(limit).execute()
        # Reverse to get chronological order
        messages = response.data[::-1] if response.data else []
        return messages
    except Exception as e:
        print(f"Error getting session messages: {e}")
        return []

# --- Session Management Helper ---
async def get_or_create_active_session(wa_id: str) -> dict:
    """Get active session or create new one if needed"""
    active_session = await get_active_session(wa_id)
    
    if active_session:
        # Update activity
        await update_session_activity(active_session["id"])
        return active_session
    else:
        # Create new session
        return await create_new_session(wa_id)

async def get_user(wa_id: str):
    """Get user by WhatsApp ID"""
    if not supabase:
        return None
    
    try:
        response = supabase.table("users").select("*").eq("wa_id", wa_id).execute()
        return response.data[0] if response.data else None
    except Exception as e:
        print(f"Error getting user: {e}")
        return None

async def list_users(limit: int = 50, offset: int = 0):
    """List all users with pagination"""
    if not supabase:
        return {"users": [], "count": 0}
    
    try:
        response = supabase.table("users").select("*").range(offset, offset + limit - 1).order("created_at", desc=True).execute()
        return {"users": response.data, "count": len(response.data)}
    except Exception as e:
        print(f"Error listing users: {e}")
        return {"users": [], "count": 0}

async def update_user_name(wa_id: str, name: str):
    """Update user name"""
    if not supabase:
        return None
    
    try:
        response = supabase.table("users").update({
            "name": name,
            "updated_at": datetime.utcnow().isoformat()
        }).eq("wa_id", wa_id).execute()
        
        return response.data[0] if response.data else None
    except Exception as e:
        print(f"Error updating user: {e}")
        return None 

# --- Property Listings ---
async def search_properties(
    city: str = None,
    min_size: int = None,
    max_size: int = None,
    min_price: float = None,
    max_price: float = None,
    features: list = None,
    price_type: str = None,
    limit: int = 10,
    offset: int = 0
) -> list:
    """Search properties with flexible filters. Only returns active listings."""
    if not supabase:
        return []
    try:
        print(f"Database search - Starting query with city={city}")
        query = supabase.table("properties").select("*").eq("is_active", True)
        if city:
            print(f"Adding city filter: {city}")
            query = query.ilike("city", f"%{city}%")
        if min_size:
            query = query.gte("size_sqm", min_size)
        if max_size:
            query = query.lte("size_sqm", max_size)
        if min_price:
            query = query.gte("price", min_price)
        if max_price:
            query = query.lte("price", max_price)
        if price_type:
            query = query.eq("price_type", price_type)
        if features:
            for feature in features:
                query = query.contains("features", [feature])
        query = query.range(offset, offset + limit - 1)
        query = query.order("is_featured", desc=True).order("updated_at", desc=True)
        resp = query.execute()
        print(f"Database search - Raw response: {resp.data}")
        return resp.data if resp.data else []
    except Exception as e:
        print(f"Error searching properties: {e}")
        return []

async def get_property_by_id(property_id: str) -> dict:
    """Get a property by its ID."""
    if not supabase:
        return None
    try:
        resp = supabase.table("properties").select("*").eq("id", property_id).single().execute()
        return resp.data if resp.data else None
    except Exception as e:
        print(f"Error getting property by id: {e}")
        return None

async def get_available_cities() -> list:
    """Get a list of all cities with active properties."""
    if not supabase:
        return []
    try:
        print("Getting available cities...")
        resp = supabase.table("properties").select("city").eq("is_active", True).execute()
        print(f"Available cities raw response: {resp.data}")
        cities = set()
        if resp.data:
            for row in resp.data:
                if row.get("city"):
                    cities.add(row["city"].strip())
        result = sorted(list(cities))
        print(f"Available cities result: {result}")
        return result
    except Exception as e:
        print(f"Error getting available cities: {e}")
        return []