from sqlalchemy.orm import Session, aliased
from sqlalchemy import desc, or_, and_
from app.models.reservation import Reservation
from app.models.user import User

def get_notes_for_display(db: Session, user_id: int, role: str):
    # Aliases for joining users twice
    StudentUser = aliased(User)
    DocenteUser = aliased(User)

    query = db.query(
        Reservation,
        StudentUser,
        DocenteUser
    ).outerjoin(
        StudentUser, Reservation.user_id == StudentUser.id
    ).outerjoin(
        DocenteUser, Reservation.docente_id == DocenteUser.id
    ).filter(
        Reservation.notes.isnot(None),
        Reservation.notes != ""
    )

    if role == "docente":
         query = query.filter(Reservation.docente_id == user_id)
    elif role != "admin":
         query = query.filter(Reservation.user_id == user_id)

    # Order by created_at DESC, id DESC
    # Note: Reservation.created_at is a String in current model, 
    # but format is YYYY-MM-DDTHH:MM:SS which sorts correctly as string too.
    query = query.order_by(desc(Reservation.created_at), desc(Reservation.id))

    rows = query.all()

    items = []
    for row in rows:
        # row is a tuple: (Reservation, StudentUser, DocenteUser)
        res, student, docente = row
        
        # Helper to safely get user display info
        def get_user_label(u):
            if not u:
                return "\u2014"
            return u.name or u.email or "\u2014"

        # Determine display string for date/time
        date_txt = res.date or ""
        time_txt = res.time or ""
        created_at = res.created_at
        
        # Fallback date logic from original code
        if not date_txt and created_at:
            parts = str(created_at).split("T") # ISO format
            if len(parts) >= 1:
                date_txt = parts[0]
            if len(parts) >= 2:
                time_txt = parts[1]
                
        dt_display = (f"{date_txt} {time_txt}").strip() if date_txt or time_txt else (created_at or "")

        items.append({
            "id": res.id,
            "date": date_txt,
            "time": time_txt,
            "dt_display": dt_display,
            "created_at": created_at,
            "notes": res.notes,
            "student": get_user_label(student),
            "docente": get_user_label(docente),
        })
    return items