"""Search service for photos - matches desktop search functionality exactly."""

from __future__ import annotations

from datetime import date
from typing import List, Optional, Tuple

from sqlalchemy import func, or_
from sqlalchemy.orm import Session

from backend.db.models import Face, Photo, Person, PhotoTagLinkage, Tag


def search_photos_by_name(
    db: Session,
    person_name: str,
    folder_path: Optional[str] = None,
    media_type: Optional[str] = None,
    date_from: Optional[date] = None,
    date_to: Optional[date] = None,
    tag_names: Optional[List[str]] = None,
    match_all: bool = False,
    page: int = 1,
    page_size: int = 50,
) -> Tuple[List[Tuple[Photo, str]], int]:
    """Search photos by person name(s) (partial, case-insensitive).
    
    Supports multiple names separated by commas (OR logic - photos matching any name).
    
    Matches desktop behavior exactly:
    - Searches first_name, last_name, middle_name, maiden_name
    - Returns (photo, full_name) tuples
    - Filters by folder_path if provided
    - Filters by media_type if provided ("image" or "video", None/"all" for all)
    - Multiple names: comma-separated, searches for photos with ANY matching person
    """
    search_name = (person_name or "").strip()
    if not search_name:
        return [], 0
    
    # Split by comma and clean up names
    search_names = [name.strip().lower() for name in search_name.split(',') if name.strip()]
    if not search_names:
        return [], 0
    
    # Build OR conditions for each search name
    name_conditions = []
    for search_name_lower in search_names:
        name_conditions.append(
            or_(
                func.lower(Person.first_name).contains(search_name_lower),
                func.lower(Person.last_name).contains(search_name_lower),
                func.lower(Person.middle_name).contains(search_name_lower),
                func.lower(Person.maiden_name).contains(search_name_lower),
            )
        )
    
    # Find matching people (any of the search names)
    matching_people = (
        db.query(Person)
        .filter(or_(*name_conditions))
        .all()
    )
    
    if not matching_people:
        return [], 0
    
    person_ids = [p.id for p in matching_people]
    
    # Query photos with faces linked to matching people
    query = (
        db.query(Photo, Person)
        .join(Face, Photo.id == Face.photo_id)
        .join(Person, Face.person_id == Person.id)
        .filter(Face.person_id.in_(person_ids))
        .distinct()
    )
    
    # Apply folder filter if provided
    if folder_path:
        folder_path = folder_path.strip()
        query = query.filter(Photo.path.startswith(folder_path))
    
    # Apply media type filter if provided
    if media_type and media_type.lower() != "all":
        query = query.filter(Photo.media_type == media_type.lower())
    
    # Apply date taken filter if provided
    if date_from:
        query = query.filter(Photo.date_taken >= date_from)
    if date_to:
        query = query.filter(Photo.date_taken <= date_to)
    
    # Apply tag filter if provided
    if tag_names:
        # Find tag IDs (case-insensitive)
        tag_ids = (
            db.query(Tag.id)
            .filter(func.lower(Tag.tag_name).in_([t.lower().strip() for t in tag_names]))
            .all()
        )
        tag_ids = [tid[0] for tid in tag_ids]
        
        if tag_ids:
            if match_all:
                # Photos that have ALL specified tags
                query = (
                    query.join(PhotoTagLinkage, Photo.id == PhotoTagLinkage.photo_id)
                    .filter(PhotoTagLinkage.tag_id.in_(tag_ids))
                    .group_by(Photo.id, Person.id)
                    .having(func.count(func.distinct(PhotoTagLinkage.tag_id)) == len(tag_ids))
                )
            else:
                # Photos that have ANY of the specified tags
                tagged_photo_ids_subquery = (
                    db.query(PhotoTagLinkage.photo_id)
                    .filter(PhotoTagLinkage.tag_id.in_(tag_ids))
                )
                query = query.filter(Photo.id.in_(tagged_photo_ids_subquery))
        else:
            # No matching tags found - return empty result
            return [], 0
    
    # Total count
    total = query.count()
    
    # Pagination
    results = query.order_by(Person.last_name, Person.first_name, Photo.path).offset((page - 1) * page_size).limit(page_size).all()
    
    # Format results: (photo, full_name)
    formatted = []
    for photo, person in results:
        full_name = f"{person.first_name or ''} {person.last_name or ''}".strip() or "Unknown"
        formatted.append((photo, full_name))
    
    return formatted, total


def search_photos_by_date(
    db: Session,
    date_from: Optional[date] = None,
    date_to: Optional[date] = None,
    folder_path: Optional[str] = None,
    media_type: Optional[str] = None,
    tag_names: Optional[List[str]] = None,
    match_all: bool = False,
    page: int = 1,
    page_size: int = 50,
) -> Tuple[List[Photo], int]:
    """Search photos by date range.
    
    Matches desktop behavior exactly:
    - Filters by date_taken
    - Requires at least one date
    - Filters by media_type if provided ("image" or "video", None/"all" for all)
    - Returns photos ordered by date_taken DESC
    """
    query = db.query(Photo).filter(Photo.date_taken.is_not(None))
    
    if date_from:
        query = query.filter(Photo.date_taken >= date_from)
    if date_to:
        query = query.filter(Photo.date_taken <= date_to)
    
    # Apply folder filter if provided
    if folder_path:
        folder_path = folder_path.strip()
        query = query.filter(Photo.path.startswith(folder_path))
    
    # Apply media type filter if provided
    if media_type and media_type.lower() != "all":
        query = query.filter(Photo.media_type == media_type.lower())
    
    # Apply tag filter if provided
    if tag_names:
        # Find tag IDs (case-insensitive)
        tag_ids = (
            db.query(Tag.id)
            .filter(func.lower(Tag.tag_name).in_([t.lower().strip() for t in tag_names]))
            .all()
        )
        tag_ids = [tid[0] for tid in tag_ids]
        
        if tag_ids:
            if match_all:
                # Photos that have ALL specified tags
                query = (
                    query.join(PhotoTagLinkage, Photo.id == PhotoTagLinkage.photo_id)
                    .filter(PhotoTagLinkage.tag_id.in_(tag_ids))
                    .group_by(Photo.id)
                    .having(func.count(func.distinct(PhotoTagLinkage.tag_id)) == len(tag_ids))
                )
            else:
                # Photos that have ANY of the specified tags
                tagged_photo_ids_subquery = (
                    db.query(PhotoTagLinkage.photo_id)
                    .filter(PhotoTagLinkage.tag_id.in_(tag_ids))
                )
                query = query.filter(Photo.id.in_(tagged_photo_ids_subquery))
        else:
            # No matching tags found - return empty result
            return [], 0
    
    # Total count
    total = query.count()
    
    # Pagination and sorting
    results = query.order_by(Photo.date_taken.desc().nullslast(), Photo.filename).offset((page - 1) * page_size).limit(page_size).all()
    
    return results, total


def search_photos_by_tags(
    db: Session,
    tag_names: List[str],
    match_all: bool = False,
    folder_path: Optional[str] = None,
    media_type: Optional[str] = None,
    date_from: Optional[date] = None,
    date_to: Optional[date] = None,
    page: int = 1,
    page_size: int = 50,
) -> Tuple[List[Photo], int]:
    """Search photos by tags.
    
    Matches desktop behavior exactly:
    - match_all=True: photos must have ALL tags
    - match_all=False: photos with ANY tag
    - Case-insensitive tag matching
    - Filters by media_type if provided ("image" or "video", None/"all" for all)
    """
    if not tag_names:
        return [], 0
    
    # Find tag IDs (case-insensitive)
    tag_ids = (
        db.query(Tag.id)
        .filter(func.lower(Tag.tag_name).in_([t.lower().strip() for t in tag_names]))
        .all()
    )
    tag_ids = [tid[0] for tid in tag_ids]
    
    if not tag_ids:
        return [], 0
    
    if match_all:
        # Photos that have ALL specified tags
        query = (
            db.query(Photo)
            .join(PhotoTagLinkage, Photo.id == PhotoTagLinkage.photo_id)
            .filter(PhotoTagLinkage.tag_id.in_(tag_ids))
            .group_by(Photo.id)
            .having(func.count(func.distinct(PhotoTagLinkage.tag_id)) == len(tag_ids))
        )
    else:
        # Photos that have ANY of the specified tags
        query = (
            db.query(Photo)
            .join(PhotoTagLinkage, Photo.id == PhotoTagLinkage.photo_id)
            .filter(PhotoTagLinkage.tag_id.in_(tag_ids))
            .distinct()
        )
    
    # Apply folder filter if provided
    if folder_path:
        folder_path = folder_path.strip()
        query = query.filter(Photo.path.startswith(folder_path))
    
    # Apply media type filter if provided
    if media_type and media_type.lower() != "all":
        query = query.filter(Photo.media_type == media_type.lower())
    
    # Apply date taken filter if provided
    if date_from:
        query = query.filter(Photo.date_taken >= date_from)
    if date_to:
        query = query.filter(Photo.date_taken <= date_to)
    
    # Total count
    total = query.count()
    
    # Pagination and sorting
    results = query.order_by(Photo.path).offset((page - 1) * page_size).limit(page_size).all()
    
    return results, total


def get_photos_without_faces(
    db: Session,
    folder_path: Optional[str] = None,
    media_type: Optional[str] = None,
    date_from: Optional[date] = None,
    date_to: Optional[date] = None,
    page: int = 1,
    page_size: int = 50,
) -> Tuple[List[Photo], int]:
    """Get photos that have no detected faces.
    
    Only includes processed photos (photos that have been processed for face detection).
    Filters by media_type if provided ("image" or "video", None/"all" for all).
    Matches desktop behavior exactly.
    """
    query = (
        db.query(Photo)
        .outerjoin(Face, Photo.id == Face.photo_id)
        .filter(Face.photo_id.is_(None))
        .filter(Photo.processed == True)  # Only include processed photos
    )
    
    # Apply folder filter if provided
    if folder_path:
        folder_path = folder_path.strip()
        query = query.filter(Photo.path.startswith(folder_path))
    
    # Apply media type filter if provided
    if media_type and media_type.lower() != "all":
        query = query.filter(Photo.media_type == media_type.lower())
    
    # Apply date taken filter if provided
    if date_from:
        query = query.filter(Photo.date_taken >= date_from)
    if date_to:
        query = query.filter(Photo.date_taken <= date_to)
    
    # Total count
    total = query.count()
    
    # Pagination and sorting
    results = query.order_by(Photo.filename).offset((page - 1) * page_size).limit(page_size).all()
    
    return results, total


def get_photos_without_tags(
    db: Session,
    folder_path: Optional[str] = None,
    media_type: Optional[str] = None,
    date_from: Optional[date] = None,
    date_to: Optional[date] = None,
    page: int = 1,
    page_size: int = 50,
) -> Tuple[List[Photo], int]:
    """Get photos that have no tags.
    
    Filters by media_type if provided ("image" or "video", None/"all" for all).
    Matches desktop behavior exactly.
    """
    query = (
        db.query(Photo)
        .outerjoin(PhotoTagLinkage, Photo.id == PhotoTagLinkage.photo_id)
        .filter(PhotoTagLinkage.photo_id.is_(None))
    )
    
    # Apply folder filter if provided
    if folder_path:
        folder_path = folder_path.strip()
        query = query.filter(Photo.path.startswith(folder_path))
    
    # Apply media type filter if provided
    if media_type and media_type.lower() != "all":
        query = query.filter(Photo.media_type == media_type.lower())
    
    # Apply date taken filter if provided
    if date_from:
        query = query.filter(Photo.date_taken >= date_from)
    if date_to:
        query = query.filter(Photo.date_taken <= date_to)
    
    # Total count
    total = query.count()
    
    # Pagination and sorting
    results = query.order_by(Photo.filename).offset((page - 1) * page_size).limit(page_size).all()
    
    return results, total


def get_photo_tags(db: Session, photo_id: int) -> List[str]:
    """Get all tags for a photo."""
    tags = (
        db.query(Tag.tag_name)
        .join(PhotoTagLinkage, Tag.id == PhotoTagLinkage.tag_id)
        .filter(PhotoTagLinkage.photo_id == photo_id)
        .all()
    )
    return [t[0] for t in tags]


def get_photo_person(db: Session, photo_id: int) -> Optional[str]:
    """Get person name for a photo (first face found)."""
    person = (
        db.query(Person)
        .join(Face, Person.id == Face.person_id)
        .filter(Face.photo_id == photo_id)
        .first()
    )
    if person:
        return f"{person.first_name or ''} {person.last_name or ''}".strip() or "Unknown"
    return None


def get_photo_face_count(db: Session, photo_id: int) -> int:
    """Get face count for a photo."""
    return db.query(Face).filter(Face.photo_id == photo_id).count()


def get_processed_photos(
    db: Session,
    folder_path: Optional[str] = None,
    media_type: Optional[str] = None,
    date_from: Optional[date] = None,
    date_to: Optional[date] = None,
    page: int = 1,
    page_size: int = 50,
) -> Tuple[List[Photo], int]:
    """Get photos that have been processed for face detection.
    
    Filters by media_type if provided ("image" or "video", None/"all" for all).
    Matches desktop behavior exactly.
    """
    query = db.query(Photo).filter(Photo.processed == True)  # noqa: E712
    
    # Apply folder filter if provided
    if folder_path:
        folder_path = folder_path.strip()
        query = query.filter(Photo.path.startswith(folder_path))
    
    # Apply media type filter if provided
    if media_type and media_type.lower() != "all":
        query = query.filter(Photo.media_type == media_type.lower())
    
    # Apply date taken filter if provided
    if date_from:
        query = query.filter(Photo.date_taken >= date_from)
    if date_to:
        query = query.filter(Photo.date_taken <= date_to)
    
    # Total count
    total = query.count()
    
    # Pagination and sorting
    results = query.order_by(Photo.filename).offset((page - 1) * page_size).limit(page_size).all()
    
    return results, total


def get_favorite_photos(
    db: Session,
    username: str,
    folder_path: Optional[str] = None,
    media_type: Optional[str] = None,
    date_from: Optional[date] = None,
    date_to: Optional[date] = None,
    page: int = 1,
    page_size: int = 50,
) -> Tuple[List[Photo], int]:
    """Get all favorite photos for a user with pagination.
    
    Filters by media_type if provided ("image" or "video", None/"all" for all).
    """
    from backend.db.models import PhotoFavorite
    
    # Join favorites with photos
    query = (
        db.query(Photo)
        .join(PhotoFavorite, Photo.id == PhotoFavorite.photo_id)
        .filter(PhotoFavorite.username == username)
    )
    
    if folder_path:
        query = query.filter(Photo.path.like(f"{folder_path}%"))
    
    # Apply media type filter if provided
    if media_type and media_type.lower() != "all":
        query = query.filter(Photo.media_type == media_type.lower())
    
    # Apply date taken filter if provided
    if date_from:
        query = query.filter(Photo.date_taken >= date_from)
    if date_to:
        query = query.filter(Photo.date_taken <= date_to)
    
    total = query.count()
    
    # Order by favorite date (most recent first), then date_taken
    results = (
        query.order_by(PhotoFavorite.created_date.desc())
        .order_by(Photo.date_taken.desc().nulls_last())
        .order_by(Photo.date_added.desc())
        .offset((page - 1) * page_size)
        .limit(page_size)
        .all()
    )
    
    return results, total


def get_unprocessed_photos(
    db: Session,
    folder_path: Optional[str] = None,
    media_type: Optional[str] = None,
    date_from: Optional[date] = None,
    date_to: Optional[date] = None,
    page: int = 1,
    page_size: int = 50,
) -> Tuple[List[Photo], int]:
    """Get photos that have not been processed for face detection.
    
    Filters by media_type if provided ("image" or "video", None/"all" for all).
    Matches desktop behavior exactly.
    """
    query = db.query(Photo).filter(Photo.processed == False)  # noqa: E712
    
    # Apply folder filter if provided
    if folder_path:
        folder_path = folder_path.strip()
        query = query.filter(Photo.path.startswith(folder_path))
    
    # Apply media type filter if provided
    if media_type and media_type.lower() != "all":
        query = query.filter(Photo.media_type == media_type.lower())
    
    # Apply date taken filter if provided
    if date_from:
        query = query.filter(Photo.date_taken >= date_from)
    if date_to:
        query = query.filter(Photo.date_taken <= date_to)
    
    # Total count
    total = query.count()
    
    # Pagination and sorting
    results = query.order_by(Photo.filename).offset((page - 1) * page_size).limit(page_size).all()
    
    return results, total

