"""Tag service for managing tags - matches desktop functionality exactly."""

from __future__ import annotations

from typing import List, Optional
from datetime import datetime

from sqlalchemy.orm import Session

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


def list_tags(db: Session) -> List[Tag]:
    """Get all tags."""
    return db.query(Tag).order_by(Tag.tag_name).all()


def get_or_create_tag(db: Session, tag_name: str) -> Tag:
    """Get existing tag or create new one (case-insensitive)."""
    # Check if tag exists (case-insensitive)
    existing = (
        db.query(Tag)
        .filter(Tag.tag_name.ilike(tag_name.strip()))
        .first()
    )
    if existing:
        return existing
    
    # Create new tag
    tag = Tag(tag_name=tag_name.strip())
    db.add(tag)
    db.flush()
    return tag


def add_tags_to_photos(
    db: Session, photo_ids: List[int], tag_names: List[str]
) -> tuple[int, int]:
    """Add tags to photos.
    
    Returns:
        Tuple of (photos_updated, tags_added)
    """
    photos_updated = 0
    tags_added = 0
    
    # Deduplicate tag names (case-insensitive) - matching desktop deduplicate_tags
    seen_tags = set()
    unique_tags = []
    for tag_name in tag_names:
        normalized = tag_name.strip().lower()
        if normalized and normalized not in seen_tags:
            seen_tags.add(normalized)
            unique_tags.append(tag_name.strip())
    
    if not unique_tags:
        return 0, 0
    
    # Get or create tags (matching desktop add_tag)
    tag_objs = []
    for tag_name in unique_tags:
        tag = get_or_create_tag(db, tag_name)
        tag_objs.append(tag)
    
    # Add tags to photos
    for photo_id in photo_ids:
        photo = db.query(Photo).filter(Photo.id == photo_id).first()
        if not photo:
            continue
        
        for tag in tag_objs:
            # Check if linkage already exists
            existing = (
                db.query(PhotoTagLinkage)
                .filter(
                    PhotoTagLinkage.photo_id == photo_id,
                    PhotoTagLinkage.tag_id == tag.id,
                )
                .first()
            )
            if existing:
                # Linkage already exists, skip
                continue
            else:
                # Create new linkage
                linkage = PhotoTagLinkage(
                    photo_id=photo_id,
                    tag_id=tag.id,
                )
                db.add(linkage)
                tags_added += 1
        
        photos_updated += 1
    
    db.commit()
    return photos_updated, tags_added


def remove_tags_from_photos(
    db: Session, photo_ids: List[int], tag_names: List[str]
) -> tuple[int, int]:
    """Remove tags from photos.
    
    Returns:
        Tuple of (photos_updated, tags_removed)
    """
    photos_updated = 0
    tags_removed = 0
    
    # Find tag IDs (case-insensitive)
    tag_ids = []
    for tag_name in tag_names:
        tag = (
            db.query(Tag)
            .filter(Tag.tag_name.ilike(tag_name.strip()))
            .first()
        )
        if tag:
            tag_ids.append(tag.id)
    
    if not tag_ids:
        return 0, 0
    
    # Remove linkages
    for photo_id in photo_ids:
        linkages = (
            db.query(PhotoTagLinkage)
            .filter(
                PhotoTagLinkage.photo_id == photo_id,
                PhotoTagLinkage.tag_id.in_(tag_ids),
            )
            .all()
        )
        for linkage in linkages:
            db.delete(linkage)
            tags_removed += 1
        
        if linkages:
            photos_updated += 1
    
    db.commit()
    return photos_updated, tags_removed


def get_photo_tags(db: Session, photo_id: int) -> List[tuple[int, str]]:
    """Get all tags for a photo.
    
    Returns:
        List of (tag_id, tag_name) tuples
    """
    linkages = (
        db.query(PhotoTagLinkage, Tag)
        .join(Tag, PhotoTagLinkage.tag_id == Tag.id)
        .filter(PhotoTagLinkage.photo_id == photo_id)
        .order_by(Tag.tag_name)
        .all()
    )
    
    return [
        (linkage.tag_id, tag.tag_name)
        for linkage, tag in linkages
    ]


def update_tag(db: Session, tag_id: int, new_tag_name: str) -> Tag:
    """Update a tag name, matching desktop logic exactly.
    
    Desktop logic:
    - Updates tag_name in tags table
    - Case-insensitive check for duplicates
    """
    tag = db.query(Tag).filter(Tag.id == tag_id).first()
    if not tag:
        raise ValueError(f"Tag {tag_id} not found")
    
    # Check if new name already exists (case-insensitive)
    existing = (
        db.query(Tag)
        .filter(Tag.tag_name.ilike(new_tag_name.strip()))
        .filter(Tag.id != tag_id)
        .first()
    )
    if existing:
        raise ValueError(f"Tag name '{new_tag_name}' already exists")
    
    tag.tag_name = new_tag_name.strip()
    db.commit()
    db.refresh(tag)
    return tag


def delete_tags(db: Session, tag_ids: List[int]) -> int:
    """Delete tags and all their linkages, matching desktop logic exactly.
    
    Desktop logic:
    - Deletes all phototaglinkage entries for these tags
    - Deletes the tags themselves
    
    Returns:
        Number of tags deleted
    """
    if not tag_ids:
        return 0
    
    # Delete all linkages for these tags (matching desktop)
    db.query(PhotoTagLinkage).filter(PhotoTagLinkage.tag_id.in_(tag_ids)).delete(
        synchronize_session=False
    )
    
    # Delete the tags themselves
    deleted_count = db.query(Tag).filter(Tag.id.in_(tag_ids)).delete(
        synchronize_session=False
    )
    
    db.commit()
    return deleted_count


def get_photos_with_tags(db: Session) -> List[dict]:
    """Get all photos with tags and face counts, optimized with JOINs and aggregations.
    
    This function uses efficient JOINs and aggregations instead of N+1 queries,
    reducing database queries from 4N+1 to just 3 queries total.
    
    Returns:
        List of dicts with photo info, face_count, and tags (comma-separated string)
    """
    from sqlalchemy import func, case, distinct
    
    # Query 1: Get all photos with face counts using LEFT JOIN and GROUP BY
    # This gets face_count and unidentified_face_count in one query
    # Note: Excludes excluded faces (Face.excluded == False) to match identify UI behavior
    photos_with_counts = (
        db.query(
            Photo.id,
            Photo.filename,
            Photo.path,
            Photo.processed,
            Photo.date_taken,
            Photo.date_added,
            Photo.media_type,
            # Face count (non-excluded faces only)
            func.count(distinct(Face.id)).label('face_count'),
            # Unidentified face count (non-excluded faces with person_id IS NULL)
            func.sum(
                case((Face.person_id.is_(None), 1), else_=0)
            ).label('unidentified_face_count'),
        )
        .outerjoin(Face, (Photo.id == Face.photo_id) & (Face.excluded == False))
        .group_by(
            Photo.id,
            Photo.filename,
            Photo.path,
            Photo.processed,
            Photo.date_taken,
            Photo.date_added,
            Photo.media_type,
        )
        .order_by(Photo.date_taken.desc().nullslast(), Photo.filename)
        .all()
    )
    
    # Create a map of photo_id -> photo data
    photo_map = {row.id: row for row in photos_with_counts}
    photo_ids = list(photo_map.keys())
    
    # If no photos, return empty list
    if not photo_ids:
        return []
    
    # Query 2: Get all tags for all photos in one query
    # Fetch all tag linkages and aggregate in Python (more reliable across databases)
    tags_data = (
        db.query(
            PhotoTagLinkage.photo_id,
            Tag.tag_name,
        )
        .join(Tag, PhotoTagLinkage.tag_id == Tag.id)
        .filter(PhotoTagLinkage.photo_id.in_(photo_ids))
        .order_by(PhotoTagLinkage.photo_id, Tag.tag_name)
        .all()
    )
    
    # Group tags by photo_id and join with comma
    tags_map = {}
    for row in tags_data:
        if row.photo_id not in tags_map:
            tags_map[row.photo_id] = []
        tags_map[row.photo_id].append(row.tag_name)
    
    # Convert lists to comma-separated strings
    tags_map = {photo_id: ", ".join(tags) for photo_id, tags in tags_map.items()}
    
    # Query 3: Get all people for all photos in one query
    # Get distinct people per photo, then format names in Python
    # Note: Excludes excluded faces to match face count behavior
    people_data = (
        db.query(
            Face.photo_id,
            Person.id,
            Person.first_name,
            Person.middle_name,
            Person.last_name,
            Person.maiden_name,
        )
        .join(Person, Face.person_id == Person.id)
        .filter(Face.photo_id.in_(photo_ids))
        .filter(Face.person_id.isnot(None))
        .filter(Face.excluded == False)
        .distinct()
        .order_by(Face.photo_id, Person.last_name, Person.first_name)
        .all()
    )
    
    # Group people by photo_id and format names
    people_map = {}
    for row in people_data:
        if row.photo_id not in people_map:
            people_map[row.photo_id] = []
        
        # Format person name
        name_parts = []
        if row.first_name:
            name_parts.append(row.first_name)
        if row.middle_name:
            name_parts.append(row.middle_name)
        if row.last_name:
            name_parts.append(row.last_name)
        if row.maiden_name:
            name_parts.append(f"({row.maiden_name})")
        full_name = " ".join(name_parts) if name_parts else "Unknown"
        people_map[row.photo_id].append(full_name)
    
    # Build result list
    result_list = []
    for photo_id, photo_row in photo_map.items():
        # Format date_taken
        date_taken = None
        if photo_row.date_taken:
            if isinstance(photo_row.date_taken, str):
                date_taken = photo_row.date_taken
            else:
                date_taken = photo_row.date_taken.isoformat()
        
        # Format date_added
        date_added = None
        if photo_row.date_added:
            if isinstance(photo_row.date_added, str):
                date_added = photo_row.date_added
            else:
                date_added = photo_row.date_added.isoformat()
        
        # Get tags for this photo
        tags = tags_map.get(photo_id, "")
        
        # Get people names for this photo
        people_names = people_map.get(photo_id, [])
        people_names_str = ", ".join(people_names) if people_names else ""
        
        result_list.append({
            'id': photo_row.id,
            'filename': photo_row.filename,
            'path': photo_row.path,
            'processed': photo_row.processed,
            'date_taken': date_taken,
            'date_added': date_added,
            'face_count': photo_row.face_count or 0,
            'unidentified_face_count': int(photo_row.unidentified_face_count or 0),
            'tags': tags,
            'people_names': people_names_str,
            'media_type': photo_row.media_type or 'image',
        })
    
    return result_list

