from sqlalchemy.orm import Session, joinedload, subqueryload
from typing import Optional, List
from .models import Conversation, Message


def get_by_id(db: Session, entity_id: str) -> Optional[Conversation]:
    return db.query(Conversation).filter(Conversation.id == entity_id).first()


def list_all(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Conversation]:
    query = db.query(Conversation)
    
    if "participant1_id" in filters and filters["participant1_id"]:
        query = query.filter(Conversation.participant1_id == filters["participant1_id"])
    if "participant2_id" in filters and filters["participant2_id"]:
        query = query.filter(Conversation.participant2_id == filters["participant2_id"])
    if "listing_id" in filters and filters["listing_id"]:
        query = query.filter(Conversation.listing_id == filters["listing_id"])
    if "order_id" in filters and filters["order_id"]:
        query = query.filter(Conversation.order_id == filters["order_id"])
    
    return query.order_by(Conversation.last_message_at.desc()).limit(limit).offset(offset).all()


def create(db: Session, data: dict) -> Conversation:
    conversation = Conversation(**data)
    db.add(conversation)
    db.flush()
    return conversation


def update(db: Session, entity_id: str, data: dict) -> Optional[Conversation]:
    conversation = db.query(Conversation).filter(Conversation.id == entity_id).first()
    if not conversation:
        return None
    for key, value in data.items():
        setattr(conversation, key, value)
    db.flush()
    return conversation


def delete(db: Session, entity_id: str) -> bool:
    conversation = db.query(Conversation).filter(Conversation.id == entity_id).first()
    if not conversation:
        return False
    db.delete(conversation)
    db.flush()
    return True


def get_with_details(db: Session, conversation_id: str) -> Optional[Conversation]:
    return (
        db.query(Conversation)
        .options(
            joinedload(Conversation.participant1),
            joinedload(Conversation.participant2),
            joinedload(Conversation.listing),
            joinedload(Conversation.order),
            subqueryload(Conversation.messages).joinedload(Message.sender)
        )
        .filter(Conversation.id == conversation_id)
        .first()
    )


def get_message_by_id(db: Session, message_id: str) -> Optional[Message]:
    return db.query(Message).filter(Message.id == message_id).first()


def list_messages(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Message]:
    query = db.query(Message)
    
    if "conversation_id" in filters and filters["conversation_id"]:
        query = query.filter(Message.conversation_id == filters["conversation_id"])
    if "sender_id" in filters and filters["sender_id"]:
        query = query.filter(Message.sender_id == filters["sender_id"])
    if "is_read" in filters and filters["is_read"] is not None:
        query = query.filter(Message.is_read == filters["is_read"])
    
    return query.order_by(Message.created_at.asc()).limit(limit).offset(offset).all()


def create_message(db: Session, data: dict) -> Message:
    message = Message(**data)
    db.add(message)
    db.flush()
    return message


def update_message(db: Session, message_id: str, data: dict) -> Optional[Message]:
    message = db.query(Message).filter(Message.id == message_id).first()
    if not message:
        return None
    for key, value in data.items():
        setattr(message, key, value)
    db.flush()
    return message


def delete_message(db: Session, message_id: str) -> bool:
    message = db.query(Message).filter(Message.id == message_id).first()
    if not message:
        return False
    db.delete(message)
    db.flush()
    return True


def get_conversations_for_user(db: Session, user_id: str, limit: int = 20, offset: int = 0) -> List[Conversation]:
    from sqlalchemy import or_
    return (
        db.query(Conversation)
        .filter(
            or_(
                Conversation.participant1_id == user_id,
                Conversation.participant2_id == user_id
            )
        )
        .order_by(Conversation.last_message_at.desc())
        .limit(limit)
        .offset(offset)
        .all()
    )