"""Pending identifications endpoints for approval workflow."""

from __future__ import annotations

from datetime import date, datetime
from typing import Annotated, Optional

from fastapi import APIRouter, Depends, HTTPException, Query, status
from pydantic import BaseModel, ConfigDict
from sqlalchemy import text, func
from sqlalchemy.orm import Session

from backend.constants.roles import DEFAULT_USER_ROLE
from backend.db.session import get_auth_db, get_db
from backend.db.models import Face, Person, PersonEncoding, User
from backend.api.users import get_current_admin_user, require_feature_permission
from backend.utils.password import hash_password

router = APIRouter(prefix="/pending-identifications", tags=["pending-identifications"])


def get_or_create_frontend_user(db: Session) -> User:
    """Get or create the special 'FrontEndUser' system user.
    
    This user represents identifications made through the frontend approval UI,
    distinguishing them from direct user identifications.
    """
    FRONTEND_USERNAME = "FrontEndUser"
    
    # Try to get existing user
    user = db.query(User).filter(User.username == FRONTEND_USERNAME).first()
    
    if user:
        return user
    
    # Create the system user if it doesn't exist
    # Use a non-loginable password hash (random, won't be used for login)
    default_password_hash = hash_password("system_user_not_for_login")
    
    user = User(
        username=FRONTEND_USERNAME,
        password_hash=default_password_hash,
        email="frontend@punimtag.system",
        full_name="Frontend System User",
        is_active=False,  # Not an active user, just a system marker
        is_admin=False,
        role=DEFAULT_USER_ROLE,
        password_change_required=False,
    )
    db.add(user)
    db.commit()
    db.refresh(user)
    
    return user


class PendingIdentificationResponse(BaseModel):
    """Pending identification DTO returned from API."""

    model_config = ConfigDict(from_attributes=True, protected_namespaces=())

    id: int
    face_id: int
    photo_id: Optional[int] = None
    user_id: int
    user_name: Optional[str] = None
    user_email: str
    first_name: str
    last_name: str
    middle_name: Optional[str] = None
    maiden_name: Optional[str] = None
    date_of_birth: Optional[date] = None
    status: str
    created_at: str
    updated_at: str


class PendingIdentificationsListResponse(BaseModel):
    """List of pending identifications."""

    model_config = ConfigDict(protected_namespaces=())

    items: list[PendingIdentificationResponse]
    total: int


class ApproveDenyDecision(BaseModel):
    """Decision for a single pending identification."""

    model_config = ConfigDict(protected_namespaces=())

    id: int
    decision: str  # 'approve' or 'deny'


class ApproveDenyRequest(BaseModel):
    """Request to approve/deny multiple pending identifications."""

    model_config = ConfigDict(protected_namespaces=())

    decisions: list[ApproveDenyDecision]


class ApproveDenyResponse(BaseModel):
    """Response from approve/deny operation."""

    model_config = ConfigDict(protected_namespaces=())

    approved: int
    denied: int
    errors: list[str]


class UserIdentificationStats(BaseModel):
    """Statistics for a single user's identifications."""

    model_config = ConfigDict(protected_namespaces=())

    user_id: int
    username: str
    full_name: str
    email: str
    face_count: int
    first_identification_date: Optional[datetime] = None
    last_identification_date: Optional[datetime] = None


class IdentificationReportResponse(BaseModel):
    """Response containing identification statistics grouped by user."""

    model_config = ConfigDict(protected_namespaces=())

    items: list[UserIdentificationStats]
    total_faces: int
    total_users: int


class ClearDatabaseResponse(BaseModel):
    """Response from clearing denied records."""

    model_config = ConfigDict(protected_namespaces=())

    deleted_records: int
    errors: list[str]


@router.get("", response_model=PendingIdentificationsListResponse)
def list_pending_identifications(
    current_user: Annotated[
        dict, Depends(require_feature_permission("user_identified"))
    ],
    include_denied: bool = False,
    db: Session = Depends(get_auth_db),
    main_db: Session = Depends(get_db),
) -> PendingIdentificationsListResponse:
    """List all pending identifications from the auth database.
    
    This endpoint reads from the separate auth database (DATABASE_URL_AUTH)
    and returns all pending identifications from the pending_identifications table.
    By default, only shows records with status='pending' for approval.
    Set include_denied=True to also show denied records.
    """
    try:
        # Query pending_identifications from auth database using raw SQL
        # Join with users table to get user name/email
        # Filter by status='pending' to show only records awaiting approval
        # Optionally include denied records if include_denied is True
        if include_denied:
            result = db.execute(text("""
                SELECT 
                    pi.id,
                    pi.face_id,
                    pi.user_id,
                    u.name as user_name,
                    u.email as user_email,
                    pi.first_name,
                    pi.last_name,
                    pi.middle_name,
                    pi.maiden_name,
                    pi.date_of_birth,
                    pi.status,
                    pi.created_at,
                    pi.updated_at
                FROM pending_identifications pi
                LEFT JOIN users u ON pi.user_id = u.id
                WHERE pi.status IN ('pending', 'denied')
                ORDER BY pi.status ASC, pi.last_name ASC, pi.first_name ASC, pi.created_at DESC
            """))
        else:
            result = db.execute(text("""
                SELECT 
                    pi.id,
                    pi.face_id,
                    pi.user_id,
                    u.name as user_name,
                    u.email as user_email,
                    pi.first_name,
                    pi.last_name,
                    pi.middle_name,
                    pi.maiden_name,
                    pi.date_of_birth,
                    pi.status,
                    pi.created_at,
                    pi.updated_at
                FROM pending_identifications pi
                LEFT JOIN users u ON pi.user_id = u.id
                WHERE pi.status = 'pending'
                ORDER BY pi.last_name ASC, pi.first_name ASC, pi.created_at DESC
            """))
        
        rows = result.fetchall()
        items = []
        for row in rows:
            # Get photo_id from main database
            photo_id = None
            face = main_db.query(Face).filter(Face.id == row.face_id).first()
            if face:
                photo_id = face.photo_id
            
            items.append(PendingIdentificationResponse(
                id=row.id,
                face_id=row.face_id,
                photo_id=photo_id,
                user_id=row.user_id,
                user_name=row.user_name,
                user_email=row.user_email,
                first_name=row.first_name,
                last_name=row.last_name,
                middle_name=row.middle_name,
                maiden_name=row.maiden_name,
                date_of_birth=row.date_of_birth,
                status=row.status,
                created_at=str(row.created_at) if row.created_at else '',
                updated_at=str(row.updated_at) if row.updated_at else '',
            ))
        
        return PendingIdentificationsListResponse(items=items, total=len(items))
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"Error reading from auth database: {str(e)}"
        )


@router.post("/approve-deny", response_model=ApproveDenyResponse)
def approve_deny_pending_identifications(
    current_user: Annotated[
        dict, Depends(require_feature_permission("user_identified"))
    ],
    request: ApproveDenyRequest,
    auth_db: Session = Depends(get_auth_db),
    main_db: Session = Depends(get_db),
) -> ApproveDenyResponse:
    """Approve or deny pending identifications.
    
    For approved identifications:
    - Updates status in auth database to 'approved'
    - Identifies the face in main database
    - Creates person if needed
    
    For denied identifications:
    - Updates status in auth database to 'denied'
    """
    approved_count = 0
    denied_count = 0
    errors = []
    
    for decision in request.decisions:
        try:
            # Get pending identification from auth database
            # Allow processing of both 'pending' and 'denied' status (to allow re-approval)
            result = auth_db.execute(text("""
                SELECT 
                    pi.id,
                    pi.face_id,
                    pi.first_name,
                    pi.last_name,
                    pi.middle_name,
                    pi.maiden_name,
                    pi.date_of_birth
                FROM pending_identifications pi
                WHERE pi.id = :id AND pi.status IN ('pending', 'denied')
            """), {"id": decision.id})
            
            row = result.fetchone()
            if not row:
                errors.append(f"Pending identification {decision.id} not found or already processed")
                continue
            
            if decision.decision == 'approve':
                # Identify the face in main database
                face = main_db.query(Face).filter(Face.id == row.face_id).first()
                if not face:
                    errors.append(f"Face {row.face_id} not found in main database")
                    # Still update status to denied since we can't process it
                    auth_db.execute(text("""
                        UPDATE pending_identifications
                        SET status = 'denied', updated_at = :updated_at
                        WHERE id = :id
                    """), {"id": decision.id, "updated_at": datetime.utcnow()})
                    auth_db.commit()
                    denied_count += 1
                    continue
                
                # Check if person already exists (by name and DOB)
                # Match the unique constraint: first_name, last_name, middle_name, maiden_name, date_of_birth
                # Build query with proper None handling
                query = main_db.query(Person).filter(
                    Person.first_name == row.first_name,
                    Person.last_name == row.last_name,
                )
                # Handle optional fields - use IS NULL for None values
                if row.middle_name:
                    query = query.filter(Person.middle_name == row.middle_name)
                else:
                    query = query.filter(Person.middle_name.is_(None))
                
                if row.maiden_name:
                    query = query.filter(Person.maiden_name == row.maiden_name)
                else:
                    query = query.filter(Person.maiden_name.is_(None))
                
                if row.date_of_birth:
                    query = query.filter(Person.date_of_birth == row.date_of_birth)
                else:
                    query = query.filter(Person.date_of_birth.is_(None))
                
                person = query.first()
                
                # Create person if doesn't exist
                created_person = False
                if not person:
                    # Explicitly set created_date to ensure it's a valid datetime object
                    person = Person(
                        first_name=row.first_name,
                        last_name=row.last_name,
                        middle_name=row.middle_name,
                        maiden_name=row.maiden_name,
                        date_of_birth=row.date_of_birth,
                        created_date=datetime.utcnow(),
                    )
                    main_db.add(person)
                    main_db.flush()  # get person.id
                    created_person = True
                
                # Link face to person
                # Use FrontEndUser to indicate this was approved through the frontend UI
                frontend_user = get_or_create_frontend_user(main_db)
                face.person_id = person.id
                face.identified_by_user_id = frontend_user.id
                main_db.add(face)
                
                # Insert person_encoding
                pe = PersonEncoding(
                    person_id=person.id,
                    face_id=face.id,
                    encoding=face.encoding,
                    quality_score=face.quality_score,
                    detector_backend=face.detector_backend,
                    model_name=face.model_name,
                )
                main_db.add(pe)
                main_db.commit()
                
                # Update status in auth database
                auth_db.execute(text("""
                    UPDATE pending_identifications
                    SET status = 'approved', updated_at = :updated_at
                    WHERE id = :id
                """), {"id": decision.id, "updated_at": datetime.utcnow()})
                auth_db.commit()
                
                approved_count += 1
                
            elif decision.decision == 'deny':
                # Update status to denied
                auth_db.execute(text("""
                    UPDATE pending_identifications
                    SET status = 'denied', updated_at = :updated_at
                    WHERE id = :id
                """), {"id": decision.id, "updated_at": datetime.utcnow()})
                auth_db.commit()
                
                denied_count += 1
            else:
                errors.append(f"Invalid decision '{decision.decision}' for pending identification {decision.id}")
                
        except Exception as e:
            errors.append(f"Error processing pending identification {decision.id}: {str(e)}")
            # Rollback any partial changes
            main_db.rollback()
            auth_db.rollback()
    
    return ApproveDenyResponse(
        approved=approved_count,
        denied=denied_count,
        errors=errors
    )


@router.get("/report", response_model=IdentificationReportResponse)
def get_identification_report(
    current_user: Annotated[
        dict, Depends(require_feature_permission("user_identified"))
    ],
    date_from: Optional[str] = Query(None, description="Filter by identification date (from) - YYYY-MM-DD"),
    date_to: Optional[str] = Query(None, description="Filter by identification date (to) - YYYY-MM-DD"),
    main_db: Session = Depends(get_db),
) -> IdentificationReportResponse:
    """Get identification statistics grouped by user.
    
    Shows how many faces each user identified and when.
    Can be filtered by date range using PersonEncoding.created_date.
    """
    # Query faces that have been identified (have person_id and identified_by_user_id)
    # Join with PersonEncoding to get created_date (when face was identified)
    # Join with User to get user information
    # Use distinct count to avoid counting the same face multiple times
    # (in case person encodings were updated, creating multiple PersonEncoding records)
    query = (
        main_db.query(
            User.id.label('user_id'),
            User.username,
            User.full_name,
            User.email,
            func.count(func.distinct(Face.id)).label('face_count'),
            func.min(PersonEncoding.created_date).label('first_date'),
            func.max(PersonEncoding.created_date).label('last_date')
        )
        .join(Face, User.id == Face.identified_by_user_id)
        .join(PersonEncoding, Face.id == PersonEncoding.face_id)
        .filter(Face.person_id.isnot(None))
        .filter(Face.identified_by_user_id.isnot(None))
        .group_by(User.id, User.username, User.full_name, User.email)
    )
    
    # Apply date filtering if provided (filter before grouping)
    if date_from:
        try:
            date_from_obj = datetime.strptime(date_from, "%Y-%m-%d").date()
            query = query.filter(func.date(PersonEncoding.created_date) >= date_from_obj)
        except ValueError:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="Invalid date_from format. Use YYYY-MM-DD"
            )
    
    if date_to:
        try:
            date_to_obj = datetime.strptime(date_to, "%Y-%m-%d").date()
            query = query.filter(func.date(PersonEncoding.created_date) <= date_to_obj)
        except ValueError:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="Invalid date_to format. Use YYYY-MM-DD"
            )
    
    # Execute query and get results
    results = query.order_by(func.count(Face.id).desc(), User.username.asc()).all()
    
    # Convert to response model
    items = []
    total_faces = 0
    
    for row in results:
        total_faces += row.face_count
        items.append(
            UserIdentificationStats(
                user_id=row.user_id,
                username=row.username,
                full_name=row.full_name or row.username,
                email=row.email,
                face_count=row.face_count,
                first_identification_date=row.first_date,
                last_identification_date=row.last_date,
            )
        )
    
    return IdentificationReportResponse(
        items=items,
        total_faces=total_faces,
        total_users=len(items)
    )


@router.post("/clear-denied", response_model=ClearDatabaseResponse)
def clear_denied_identifications(
    current_admin: dict = Depends(get_current_admin_user),
    auth_db: Session = Depends(get_auth_db),
) -> ClearDatabaseResponse:
    """Delete all denied pending identifications from the database.
    
    This permanently removes all records with status='denied' from the
    pending_identifications table in the auth database.
    """
    deleted_records = 0
    errors = []
    
    try:
        # First check if there are any denied records
        check_result = auth_db.execute(text("""
            SELECT COUNT(*) as count FROM pending_identifications
            WHERE status = 'denied'
        """))
        denied_count = check_result.fetchone().count if check_result else 0
        
        if denied_count == 0:
            # No denied records to delete
            return ClearDatabaseResponse(
                deleted_records=0,
                errors=[]
            )
        
        # Delete all denied records
        result = auth_db.execute(text("""
            DELETE FROM pending_identifications
            WHERE status = 'denied'
        """))
        
        deleted_records = result.rowcount if hasattr(result, 'rowcount') else 0
        auth_db.commit()
        
        if deleted_records == 0 and denied_count > 0:
            errors.append("No records were deleted despite finding denied records")
        
    except Exception as e:
        auth_db.rollback()
        error_msg = str(e)
        errors.append(f"Error deleting denied records: {error_msg}")
        
        # Check if it's a permission error
        if "permission denied" in error_msg.lower() or "insufficient privilege" in error_msg.lower():
            errors.append(
                "Database permission error. Please run this command manually:\n"
                "sudo -u postgres psql -d punimtag_auth -c \"GRANT DELETE ON TABLE pending_identifications TO punimtag;\""
            )
    
    return ClearDatabaseResponse(
        deleted_records=deleted_records,
        errors=errors
    )

