from sqlalchemy.orm import Session
from typing import Optional, List
from .models import User, Customer, Tourguide, Serviceprovider


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


def get_user_by_email(db: Session, email: str) -> Optional[User]:
    return db.query(User).filter(User.email == email).first()


def list_all(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[User]:
    query = db.query(User)
    if filters.get("role"):
        query = query.filter(User.role == filters["role"])
    if filters.get("is_active") is not None:
        query = query.filter(User.is_active == filters["is_active"])
    if filters.get("search"):
        search_term = f"%{filters['search']}%"
        query = query.filter(
            (User.first_name.ilike(search_term)) |
            (User.last_name.ilike(search_term)) |
            (User.email.ilike(search_term))
        )
    return query.order_by(User.created_at.desc()).limit(limit).offset(offset).all()


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


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


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


# Customer repository functions
def get_customer_by_id(db: Session, entity_id: str) -> Optional[Customer]:
    return db.query(Customer).filter(Customer.id == entity_id).first()


def get_customer_by_user_id(db: Session, user_id: str) -> Optional[Customer]:
    return db.query(Customer).filter(Customer.user_id == user_id).first()


def list_all_customers(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Customer]:
    query = db.query(Customer)
    if filters.get("loyalty_status"):
        query = query.filter(Customer.loyalty_status == filters["loyalty_status"])
    if filters.get("country"):
        query = query.filter(Customer.country == filters["country"])
    return query.order_by(Customer.created_at.desc()).limit(limit).offset(offset).all()


def create_customer(db: Session, data: dict) -> Customer:
    customer = Customer(**data)
    db.add(customer)
    db.flush()
    return customer


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


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


# Tourguide repository functions
def get_tourguide_by_id(db: Session, entity_id: str) -> Optional[Tourguide]:
    return db.query(Tourguide).filter(Tourguide.id == entity_id).first()


def get_tourguide_by_user_id(db: Session, user_id: str) -> Optional[Tourguide]:
    return db.query(Tourguide).filter(Tourguide.user_id == user_id).first()


def list_all_tourguides(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Tourguide]:
    query = db.query(Tourguide)
    if filters.get("is_available") is not None:
        query = query.filter(Tourguide.is_available == filters["is_available"])
    if filters.get("language"):
        query = query.filter(Tourguide.languages_spoken.contains([filters["language"]]))
    return query.order_by(Tourguide.created_at.desc()).limit(limit).offset(offset).all()


def create_tourguide(db: Session, data: dict) -> Tourguide:
    tourguide = Tourguide(**data)
    db.add(tourguide)
    db.flush()
    return tourguide


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


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


# Serviceprovider repository functions
def get_serviceprovider_by_id(db: Session, entity_id: str) -> Optional[Serviceprovider]:
    return db.query(Serviceprovider).filter(Serviceprovider.id == entity_id).first()


def get_serviceprovider_by_user_id(db: Session, user_id: str) -> Optional[Serviceprovider]:
    return db.query(Serviceprovider).filter(Serviceprovider.user_id == user_id).first()


def list_all_serviceproviders(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Serviceprovider]:
    query = db.query(Serviceprovider)
    if filters.get("service_type"):
        query = query.filter(Serviceprovider.service_type == filters["service_type"])
    if filters.get("is_verified") is not None:
        query = query.filter(Serviceprovider.is_verified == filters["is_verified"])
    if filters.get("country"):
        query = query.filter(Serviceprovider.country == filters["country"])
    return query.order_by(Serviceprovider.created_at.desc()).limit(limit).offset(offset).all()


def create_serviceprovider(db: Session, data: dict) -> Serviceprovider:
    serviceprovider = Serviceprovider(**data)
    db.add(serviceprovider)
    db.flush()
    return serviceprovider


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


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