"""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 src.web.db.models import Photo, PhotoTagLinkage, Tag, Face


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, matching desktop query exactly.
    
    Desktop query:
    SELECT p.id, p.filename, p.path, p.processed, p.date_taken, p.date_added,
           (SELECT COUNT(*) FROM faces f WHERE f.photo_id = p.id) as face_count,
           (SELECT GROUP_CONCAT(DISTINCT t.tag_name) 
            FROM phototaglinkage ptl 
            JOIN tags t ON t.id = ptl.tag_id 
            WHERE ptl.photo_id = p.id) as tags
    FROM photos p
    ORDER BY p.date_taken DESC, p.filename
    
    Returns:
        List of dicts with photo info, face_count, and tags (comma-separated string)
    """
    from sqlalchemy import func
    
    # Get all photos with face counts and tags
    photos = (
        db.query(Photo)
        .order_by(Photo.date_taken.desc().nullslast(), Photo.filename)
        .all()
    )
    
    result = []
    for photo in photos:
        # Get face count (all faces)
        face_count = (
            db.query(func.count(Face.id))
            .filter(Face.photo_id == photo.id)
            .scalar() or 0
        )
        
        # Get unidentified face count (only faces with person_id IS NULL)
        unidentified_face_count = (
            db.query(func.count(Face.id))
            .filter(Face.photo_id == photo.id)
            .filter(Face.person_id.is_(None))
            .scalar() or 0
        )
        
        # Get tags as comma-separated string (matching desktop GROUP_CONCAT)
        tags_query = (
            db.query(Tag.tag_name)
            .join(PhotoTagLinkage, Tag.id == PhotoTagLinkage.tag_id)
            .filter(PhotoTagLinkage.photo_id == photo.id)
            .order_by(Tag.tag_name)
            .all()
        )
        tags = ", ".join([t[0] for t in tags_query]) if tags_query else ""
        
        result.append({
            'id': photo.id,
            'filename': photo.filename,
            'path': photo.path,
            'processed': photo.processed,
            'date_taken': photo.date_taken.isoformat() if photo.date_taken else None,
            'date_added': photo.date_added.isoformat() if photo.date_added else None,
            'face_count': face_count,
            'unidentified_face_count': unidentified_face_count,
            'tags': tags,
        })
    
    return result

