"""Video service for managing video-person identifications."""

from __future__ import annotations

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

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

from backend.db.models import Photo, Person, PhotoPersonLinkage, User


def list_videos_for_identification(
    db: Session,
    folder_path: Optional[str] = None,
    date_from: Optional[date] = None,
    date_to: Optional[date] = None,
    has_people: Optional[bool] = None,
    person_name: Optional[str] = None,
    sort_by: str = "filename",
    sort_dir: str = "asc",
    page: int = 1,
    page_size: int = 50,
) -> Tuple[List[Photo], int]:
    """List videos for person identification.
    
    Args:
        db: Database session
        folder_path: Filter by folder path (starts with)
        date_from: Filter by date taken (from)
        date_to: Filter by date taken (to)
        has_people: Filter videos with/without identified people (True/False/None)
        person_name: Filter videos containing person with this name
        sort_by: Sort field ("filename", "date_taken", "date_added")
        sort_dir: Sort direction ("asc" or "desc")
        page: Page number (1-based)
        page_size: Items per page
    
    Returns:
        Tuple of (videos list, total count)
    """
    # Base query: only videos
    query = db.query(Photo).filter(Photo.media_type == "video")
    
    # Apply folder filter
    if folder_path:
        folder_path = folder_path.strip()
        query = query.filter(Photo.path.startswith(folder_path))
    
    # Apply date filters
    if date_from:
        query = query.filter(Photo.date_taken >= date_from)
    if date_to:
        query = query.filter(Photo.date_taken <= date_to)
    
    # Apply person name filter
    if person_name:
        person_name_lower = person_name.lower().strip()
        # Search in first_name, last_name, middle_name, maiden_name
        matching_people = (
            db.query(Person.id)
            .filter(
                or_(
                    func.lower(Person.first_name).contains(person_name_lower),
                    func.lower(Person.last_name).contains(person_name_lower),
                    func.lower(Person.middle_name).contains(person_name_lower),
                    func.lower(Person.maiden_name).contains(person_name_lower),
                )
            )
            .subquery()
        )
        # Get videos linked to these people
        video_ids_with_person = (
            db.query(PhotoPersonLinkage.photo_id)
            .filter(PhotoPersonLinkage.person_id.in_(db.query(matching_people.c.id)))
            .subquery()
        )
        query = query.filter(Photo.id.in_(db.query(video_ids_with_person.c.photo_id)))
    
    # Apply has_people filter
    if has_people is not None:
        # Subquery to get video IDs with people
        videos_with_people = (
            db.query(PhotoPersonLinkage.photo_id)
            .distinct()
            .subquery()
        )
        if has_people:
            query = query.filter(Photo.id.in_(db.query(videos_with_people.c.photo_id)))
        else:
            query = query.filter(~Photo.id.in_(db.query(videos_with_people.c.photo_id)))
    
    # Get total count before pagination
    total = query.count()
    
    # Apply sorting
    if sort_by == "filename":
        order_col = Photo.filename
    elif sort_by == "date_taken":
        order_col = Photo.date_taken
    elif sort_by == "date_added":
        order_col = Photo.date_added
    else:
        order_col = Photo.filename
    
    if sort_dir.lower() == "desc":
        query = query.order_by(order_col.desc())
    else:
        query = query.order_by(order_col.asc())
    
    # Apply pagination
    offset = (page - 1) * page_size
    results = query.offset(offset).limit(page_size).all()
    
    return results, total


def get_video_people(
    db: Session,
    video_id: int,
) -> List[Tuple[Person, PhotoPersonLinkage]]:
    """Get all people identified in a video.
    
    Args:
        db: Database session
        video_id: Video (Photo) ID
    
    Returns:
        List of (Person, PhotoPersonLinkage) tuples
    """
    # Verify it's a video
    video = db.query(Photo).filter(
        Photo.id == video_id,
        Photo.media_type == "video"
    ).first()
    
    if not video:
        return []
    
    # Get all linkages for this video
    linkages = (
        db.query(PhotoPersonLinkage, Person)
        .join(Person, PhotoPersonLinkage.person_id == Person.id)
        .filter(PhotoPersonLinkage.photo_id == video_id)
        .order_by(Person.last_name, Person.first_name)
        .all()
    )
    
    return [(person, linkage) for linkage, person in linkages]


def identify_person_in_video(
    db: Session,
    video_id: int,
    person_id: Optional[int] = None,
    first_name: Optional[str] = None,
    last_name: Optional[str] = None,
    middle_name: Optional[str] = None,
    maiden_name: Optional[str] = None,
    date_of_birth: Optional[date] = None,
    user_id: Optional[int] = None,
) -> Tuple[Person, bool]:
    """Identify a person in a video.
    
    Args:
        db: Database session
        video_id: Video (Photo) ID
        person_id: Existing person ID (if using existing person)
        first_name: First name (if creating new person)
        last_name: Last name (if creating new person)
        middle_name: Middle name (optional)
        maiden_name: Maiden name (optional)
        date_of_birth: Date of birth (optional)
        user_id: User ID who is identifying (optional)
    
    Returns:
        Tuple of (Person, created_person: bool)
    
    Raises:
        ValueError: If video doesn't exist or is not a video, or if person data is invalid
    """
    # Verify video exists and is actually a video
    video = db.query(Photo).filter(
        Photo.id == video_id,
        Photo.media_type == "video"
    ).first()
    
    if not video:
        raise ValueError(f"Video {video_id} not found or is not a video")
    
    # Get or create person
    person: Optional[Person] = None
    created_person = False
    
    if person_id:
        # Use existing person
        person = db.query(Person).filter(Person.id == person_id).first()
        if not person:
            raise ValueError(f"Person {person_id} not found")
    else:
        # Create new person
        if not first_name or not last_name:
            raise ValueError("first_name and last_name are required to create a person")
        
        first_name = first_name.strip()
        last_name = last_name.strip()
        middle_name = middle_name.strip() if middle_name else None
        maiden_name = maiden_name.strip() if maiden_name else None
        
        # Check if person already exists (unique constraint)
        existing_person = (
            db.query(Person)
            .filter(
                Person.first_name == first_name,
                Person.last_name == last_name,
                Person.middle_name == middle_name,
                Person.maiden_name == maiden_name,
                Person.date_of_birth == date_of_birth,
            )
            .first()
        )
        
        if existing_person:
            person = existing_person
        else:
            # Explicitly set created_date to ensure it's a valid datetime object
            person = Person(
                first_name=first_name,
                last_name=last_name,
                middle_name=middle_name,
                maiden_name=maiden_name,
                date_of_birth=date_of_birth,
                created_date=datetime.utcnow(),
            )
            db.add(person)
            db.flush()  # Get person.id
            created_person = True
            # Commit the person creation immediately to ensure it's saved
            db.commit()
    
    # Check if linkage already exists
    existing_linkage = (
        db.query(PhotoPersonLinkage)
        .filter(
            PhotoPersonLinkage.photo_id == video_id,
            PhotoPersonLinkage.person_id == person.id,
        )
        .first()
    )
    
    if not existing_linkage:
        # Create new linkage
        linkage = PhotoPersonLinkage(
            photo_id=video_id,
            person_id=person.id,
            identified_by_user_id=user_id,
        )
        db.add(linkage)
        db.commit()
    
    # If person was already committed above, this commit is just for the linkage
    # If person already existed, this commit does nothing (no pending changes)
    # This ensures the linkage is saved
    return person, created_person


def remove_person_from_video(
    db: Session,
    video_id: int,
    person_id: int,
) -> bool:
    """Remove person identification from video.
    
    Args:
        db: Database session
        video_id: Video (Photo) ID
        person_id: Person ID to remove
    
    Returns:
        True if removed, False if not found
    
    Raises:
        ValueError: If video doesn't exist or is not a video
    """
    # Verify video exists and is actually a video
    video = db.query(Photo).filter(
        Photo.id == video_id,
        Photo.media_type == "video"
    ).first()
    
    if not video:
        raise ValueError(f"Video {video_id} not found or is not a video")
    
    # Find and delete linkage
    linkage = (
        db.query(PhotoPersonLinkage)
        .filter(
            PhotoPersonLinkage.photo_id == video_id,
            PhotoPersonLinkage.person_id == person_id,
        )
        .first()
    )
    
    if linkage:
        db.delete(linkage)
        db.commit()
        return True
    
    return False


def get_video_people_count(db: Session, video_id: int) -> int:
    """Get count of people identified in a video.
    
    Args:
        db: Database session
        video_id: Video (Photo) ID
    
    Returns:
        Count of people
    """
    return (
        db.query(PhotoPersonLinkage)
        .filter(PhotoPersonLinkage.photo_id == video_id)
        .count()
    )

