from sqlalchemy.orm import Session, joinedload
from . import models, schemas
from typing import List
from app.assignment.order_assignment_service import assign_waiters_to_order

def create_waiter(db: Session, waiter: schemas.WaiterCreate):
    db_waiter = models.Waiter(name=waiter.name, load=waiter.load, role=waiter.role)
    db.add(db_waiter)
    db.commit()
    db.refresh(db_waiter)
    return db_waiter

def get_waiters(db: Session):
    return db.query(models.Waiter).all()

def get_user(db: Session, user_id: int):
    return db.query(models.User).filter(models.User.id == user_id).first()

def get_user_by_username(db: Session, username: str):
    return db.query(models.User).filter(models.User.username == username).first()

def create_user(db: Session, user: schemas.UserCreate):
    from app.security import hash_password
    hashed_pw = hash_password(user.password)
    db_user = models.User(username=user.username, password=hashed_pw, role=user.role)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user

def get_product(db: Session, product_id: int):
    return db.query(models.Product).filter(models.Product.id == product_id).first()

def get_products(db: Session):
    return db.query(models.Product).all()

def create_product(db: Session, product: schemas.ProductCreate):
    db_product = models.Product(name=product.name, price=product.price, cost=product.cost, group_id=product.group_id)
    db.add(db_product)
    db.commit()
    db.refresh(db_product)
    return db_product

def get_product_group_name(db: Session, group_id: int):
    return db.query(models.Group).filter(models.Group.id == group_id).first().name

def create_order(db: Session, order: schemas.OrderCreate):
    db_order = models.Order(table_id=order.table_id)
    db.add(db_order)
    db.commit()
    db.refresh(db_order)
    # Asignar camareros automáticamente
    assigned_products = assign_waiters_to_order(db, order.products, algorithm=order.algorithm)
    total = 0
    for op in assigned_products:
        product = get_product(db, op.product_id)
        if not product:
            print("Product id {op.product_id} not found")
        db_op = models.OrderProduct(
            order_id=db_order.id,
            product_id=op.product_id,
            quantity=op.quantity,
            waiter_id=getattr(op, 'waiter_id', None),
            status="pending"  # Estado inicial
        )
        db.add(db_op)
        total += product.price * op.quantity
    db_order.total_price = total
    db.commit()
    db.refresh(db_order)
    return db_order

def get_order(db: Session, order_id: int):
    return db.query(models.Order).filter(models.Order.id == order_id).first()

def get_last_order(db: Session):
    # Devuelve la última orden creada según el campo created_at (o id si no hay timestamp)
    return db.query(models.Order).order_by(models.Order.id.desc()).first()

def get_orders(db: Session):
    return db.query(models.Order).all()

def get_last_order_waiters(db: Session, skip: int = 1) -> list[str]:
    """
    skip=1 -> devuelve la "anterior" a la más reciente que tenga productos con waiter.
    skip=0 -> devuelve la última que tenga productos con waiter.
    """
    last_order_id_row = (
        db.query(models.OrderProduct.order_id)
        .filter(models.OrderProduct.waiter_id.isnot(None))
        .distinct()
        .order_by(models.OrderProduct.order_id.desc())
        .offset(skip)
        .limit(1)
        .first()
    )

    if not last_order_id_row:
        return []

    order_id = last_order_id_row[0]

    rows = (
        db.query(models.Waiter.name)
        .join(models.OrderProduct, models.OrderProduct.waiter_id == models.Waiter.id)
        .filter(models.OrderProduct.order_id == order_id)
        .distinct()
        .all()
    )
    return [r[0] for r in rows if r[0]]

def get_pending_orders(db: Session):
    return db.query(models.Order).filter(models.Order.status == "pending").order_by(models.Order.created_at).all()

def get_ready_orders(db: Session):
    return db.query(models.Order).filter(models.Order.status == "ready").order_by(models.Order.created_at).all()

def get_delivered_orders(db: Session):
    return db.query(models.Order).filter(models.Order.status == "delivered").order_by(models.Order.created_at).all()

def is_order_ready(db: Session, order_id: int) -> bool:
    """
    Devuelve True si el status de la ordern es 'ready', False en caso contrario.
    """
    order = db.query(models.Order).filter(models.Order.id == order_id).first()
    if order:
        if order.status == "ready":
            return True
    return False

def next_order_status(db: Session, order: models.Order):
    status_flow = {"pending": "ready", "ready": "delivered", "delivered": "delivered"}
    order.status = status_flow.get(order.status, "pending")
    db.commit()
    db.refresh(order)
    return order

def create_group(db: Session, group: schemas.GroupCreate):
    db_group = models.Group(name=group.name)
    db.add(db_group)
    db.commit()
    db.refresh(db_group)
    return db_group

def get_group_by_product_name(db: Session, product_name: str):
    product = (
        db.query(models.Product)
        .options(joinedload(models.Product.group))
        .filter(models.Product.name == product_name)
        .first()
    )
    if product and product.group:
        return product.group.name
    return None

def get_group_by_name(db: Session, name: str):
    return db.query(models.Group).filter(models.Group.name == name).first()

def get_groups(db: Session):
    return db.query(models.Group).all()

def create_table(db: Session, table: schemas.TableCreate):
    db_table = models.Table(
        number=table.number,
        name=table.name,
        abbreviation=table.abbreviation,
        pos_x=table.pos_x,
        pos_y=table.pos_y
    )
    db.add(db_table)
    db.commit()
    db.refresh(db_table)
    return db_table

def get_table_by_id(db: Session, table_id: int):
    return db.query(models.Table).filter(models.Table.id == table_id).first()

def get_table_by_name(db: Session, name: str):
    return db.query(models.Table).filter(models.Table.name == name).first()

def get_table_by_number(db: Session, number: int):
    return db.query(models.Table).filter(models.Table.number == number).first()

def get_tables(db: Session):
    return db.query(models.Table).all()

def update_order_product_status(db: Session, order_id: int, product_name: str, waiter_name: str, new_status: str):
    """
    Actualiza el estado de un producto concreto de una orden y camarero.
    Devuelve el objeto OrderProduct actualizado o None si no existe.
    """
    op = db.query(models.OrderProduct).filter(
        models.OrderProduct.order_id == order_id,
        models.OrderProduct.product.has(name=product_name),
        models.OrderProduct.waiter.has(name=waiter_name)
    ).first()
    if op:
        op.status = new_status
        db.commit()
        db.refresh(op)

        # Si todos los productos de la comanda están delivered, cambiar estado de la orden a 'ready'
        order = db.query(models.Order).filter(models.Order.id == order_id).first()
        if order:
            order_products = db.query(models.OrderProduct).filter(models.OrderProduct.order_id == order_id).all()
            if all(p.status == "delivered" for p in order_products):
                if order.status != "ready":
                    order.status = "ready"
                    db.commit()
                    db.refresh(order)
    return op

def delete_product(db: Session, product_id: int):
    product = db.query(models.Product).filter(models.Product.id == product_id).first()
    if product:
        db.delete(product)
        db.commit()

def delete_table(db: Session, table_id: int):
    table = db.query(models.Table).filter(models.Table.id == table_id).first()
    if table:
        db.delete(table)
        db.commit()

def delete_user(db: Session, user_id: int):
    user = db.query(models.User).filter(models.User.id == user_id).first()
    if user:
        db.delete(user)
        db.commit()

def delete_waiter(db: Session, waiter_id: int):
    waiter = db.query(models.Waiter).filter(models.Waiter.id == waiter_id).first()
    if waiter:
        db.delete(waiter)
        db.commit()

def set_waiter_load(db: Session, waiter_name: str, load: float):
    waiter = db.query(models.Waiter).filter(models.Waiter.name == waiter_name).first()
    if waiter:
        waiter.load = load
        db.commit()
        db.refresh(waiter)
    return waiter

def update_waiter_load(db: Session, waiter_id: int, new_load: float):
    waiter = db.query(models.Waiter).filter(models.Waiter.id == waiter_id).first()
    if waiter:
        waiter.load = new_load
        db.commit()
        db.refresh(waiter)
        return waiter
    return None

