"""Auth database user management endpoints - admin only."""

from __future__ import annotations

import logging
from typing import Annotated

from fastapi import APIRouter, Depends, HTTPException, Query, Response, status
from fastapi.responses import JSONResponse
from sqlalchemy import text
from sqlalchemy.orm import Session

from backend.api.auth import get_current_user
from backend.api.users import get_current_admin_user
from backend.db.session import get_auth_db, get_db
from backend.schemas.auth_users import (
    AuthUserCreateRequest,
    AuthUserResponse,
    AuthUserUpdateRequest,
    AuthUsersListResponse,
)
from backend.utils.password import hash_password

router = APIRouter(prefix="/auth-users", tags=["auth-users"])
logger = logging.getLogger(__name__)


def _check_column_exists(auth_db: Session, table_name: str, column_name: str) -> bool:
    """Check if a column exists in a table."""
    try:
        from sqlalchemy import inspect as sqlalchemy_inspect
        inspector = sqlalchemy_inspect(auth_db.bind)
        columns = {col["name"] for col in inspector.get_columns(table_name)}
        return column_name in columns
    except Exception:
        return False


def _get_role_from_is_admin(auth_db: Session, is_admin: bool) -> str:
    """Get role value from is_admin boolean. Returns 'Admin' if is_admin is True, 'User' otherwise."""
    return "Admin" if is_admin else "User"


def _get_is_admin_from_role(role: str | None) -> bool:
    """Get is_admin boolean from role string. Returns True if role is 'Admin', False otherwise."""
    return role == "Admin" if role else False


@router.get("", response_model=AuthUsersListResponse)
def list_auth_users(
    current_admin: Annotated[dict, Depends(get_current_admin_user)],
    auth_db: Session = Depends(get_auth_db),
) -> AuthUsersListResponse:
    """List all users from auth database - admin only."""
    try:
        # Check if optional columns exist
        has_role_column = _check_column_exists(auth_db, "users", "role")
        has_is_active_column = _check_column_exists(auth_db, "users", "is_active")
        
        # Query users from auth database with all columns from schema
        # Try to include is_active and role if columns exist
        result = None
        try:
            # Build SELECT query based on which columns exist
            select_fields = "id, email, name, is_admin, has_write_access"
            if has_is_active_column:
                select_fields += ", is_active"
            if has_role_column:
                select_fields += ", role"
            select_fields += ", created_at, updated_at"
            
            # nosemgrep: python.sqlalchemy.security.audit.avoid-sqlalchemy-text.avoid-sqlalchemy-text
            # Safe: select_fields is controlled (column names only, not user input)
            result = auth_db.execute(text(f"""
                SELECT {select_fields}
                FROM users
                ORDER BY COALESCE(name, email) ASC
            """))
        except Exception:
            # Rollback the failed transaction before trying again
            auth_db.rollback()
            try:
                # Try with is_active only (no role)
                select_fields = "id, email, name, is_admin, has_write_access"
                if has_is_active_column:
                    select_fields += ", is_active"
                select_fields += ", created_at, updated_at"
                # nosemgrep: python.sqlalchemy.security.audit.avoid-sqlalchemy-text.avoid-sqlalchemy-text
                # Safe: select_fields is controlled (column names only, not user input)
                result = auth_db.execute(text(f"""
                    SELECT {select_fields}
                    FROM users
                    ORDER BY COALESCE(name, email) ASC
                """))
            except Exception:
                # Rollback again before final attempt
                auth_db.rollback()
                # Base columns only
                result = auth_db.execute(text("""
                    SELECT 
                        id,
                        email,
                        name,
                        is_admin,
                        has_write_access,
                        created_at,
                        updated_at
                    FROM users
                    ORDER BY COALESCE(name, email) ASC
                """))
        
        if result is None:
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="Failed to query auth users",
            )
        
        rows = result.fetchall()
        users = []
        for row in rows:
            try:
                # Access row attributes directly - SQLAlchemy Row objects support attribute access
                user_id = int(row.id)
                email = str(row.email)
                name = row.name if row.name is not None else None
                
                # Get boolean fields - convert to proper boolean
                # These columns have defaults so they should always have values
                is_admin = bool(row.is_admin)
                has_write_access = bool(row.has_write_access)
                
                # Optional columns - try to get from row, default to None if not selected
                is_active = None
                try:
                    is_active = row.is_active
                    if is_active is not None:
                        is_active = bool(is_active)
                    else:
                        # NULL values should be treated as True (active)
                        is_active = True
                except (AttributeError, KeyError):
                    # Column not selected or doesn't exist - default to True (active)
                    is_active = True
                
                # Get role - if column doesn't exist, derive from is_admin
                if has_role_column:
                    role = getattr(row, 'role', None)
                else:
                    role = _get_role_from_is_admin(auth_db, is_admin)
                
                created_at = getattr(row, 'created_at', None)
                updated_at = getattr(row, 'updated_at', None)
                
                users.append(AuthUserResponse(
                    id=user_id,
                    name=name,
                    email=email,
                    is_admin=is_admin,
                    has_write_access=has_write_access,
                    is_active=is_active,
                    role=role,
                    created_at=created_at,
                    updated_at=updated_at,
                ))
            except Exception as row_error:
                logger.warning(f"Error processing auth user row: {row_error}")
                # Skip this row and continue
                continue
        
        return AuthUsersListResponse(items=users, total=len(users))
    except HTTPException:
        raise
    except Exception as e:
        auth_db.rollback()
        import traceback
        error_detail = f"Failed to list auth users: {str(e)}\n{traceback.format_exc()}"
        logger.error(f"Error listing auth users: {error_detail}")
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"Failed to list auth users: {str(e)}",
        )


@router.post("", response_model=AuthUserResponse, status_code=status.HTTP_201_CREATED)
def create_auth_user(
    current_admin: Annotated[dict, Depends(get_current_admin_user)],
    request: AuthUserCreateRequest,
    auth_db: Session = Depends(get_auth_db),
) -> AuthUserResponse:
    """Create a new user in auth database - admin only."""
    try:
        # Check if user with same email already exists (email is unique)
        check_result = auth_db.execute(text("""
            SELECT id FROM users 
            WHERE email = :email
        """), {"email": request.email})
        
        existing = check_result.first()
        if existing:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail=f"User with email '{request.email}' already exists",
            )
        
        # Insert new user
        # Check database dialect for RETURNING support
        dialect = auth_db.bind.dialect.name if auth_db.bind else 'postgresql'
        supports_returning = dialect == 'postgresql'
        
        # Hash the password
        password_hash = hash_password(request.password)
        
        if supports_returning:
            result = auth_db.execute(text("""
                INSERT INTO users (email, name, password_hash, is_admin, has_write_access)
                VALUES (:email, :name, :password_hash, :is_admin, :has_write_access)
                RETURNING id, email, name, is_admin, has_write_access, created_at, updated_at
            """), {
                "email": request.email,
                "name": request.name,
                "password_hash": password_hash,
                "is_admin": request.is_admin,
                "has_write_access": request.has_write_access,
            })
            auth_db.commit()
            row = result.first()
        else:
            # SQLite - insert then select
            auth_db.execute(text("""
                INSERT INTO users (email, name, password_hash, is_admin, has_write_access)
                VALUES (:email, :name, :password_hash, :is_admin, :has_write_access)
            """), {
                "email": request.email,
                "name": request.name,
                "password_hash": password_hash,
                "is_admin": request.is_admin,
                "has_write_access": request.has_write_access,
            })
            auth_db.commit()
            # Get the last inserted row
            result = auth_db.execute(text("""
                SELECT id, email, name, is_admin, has_write_access, created_at, updated_at
                FROM users
                WHERE id = last_insert_rowid()
            """))
            row = result.first()
        
        if not row:
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="Failed to create user",
            )
        
        is_admin = bool(row.is_admin)
        has_write_access = bool(row.has_write_access)
        
        return AuthUserResponse(
            id=row.id,
            name=getattr(row, 'name', None),
            email=row.email,
            is_admin=is_admin,
            has_write_access=has_write_access,
            created_at=getattr(row, 'created_at', None),
            updated_at=getattr(row, 'updated_at', None),
        )
    except HTTPException:
        raise
    except Exception as e:
        auth_db.rollback()
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"Failed to create auth user: {str(e)}",
        )


@router.get("/{user_id}", response_model=AuthUserResponse)
def get_auth_user(
    current_admin: Annotated[dict, Depends(get_current_admin_user)],
    user_id: int,
    auth_db: Session = Depends(get_auth_db),
) -> AuthUserResponse:
    """Get a specific auth user by ID - admin only."""
    try:
        # Check if optional columns exist
        has_role_column = _check_column_exists(auth_db, "users", "role")
        has_is_active_column = _check_column_exists(auth_db, "users", "is_active")
        
        # Try to include is_active and role if columns exist
        try:
            # Build SELECT query based on which columns exist
            select_fields = "id, email, name, is_admin, has_write_access"
            if has_is_active_column:
                select_fields += ", is_active"
            if has_role_column:
                select_fields += ", role"
            select_fields += ", created_at, updated_at"
            
            # nosemgrep: python.sqlalchemy.security.audit.avoid-sqlalchemy-text.avoid-sqlalchemy-text
            # Safe: select_fields is controlled (column names only, not user input), user_id is parameterized
            result = auth_db.execute(text(f"""
                SELECT {select_fields}
                FROM users
                WHERE id = :user_id
            """), {"user_id": user_id})
        except Exception:
            # Rollback the failed transaction before trying again
            auth_db.rollback()
            try:
                # Try with is_active only (no role)
                select_fields = "id, email, name, is_admin, has_write_access"
                if has_is_active_column:
                    select_fields += ", is_active"
                select_fields += ", created_at, updated_at"
                # nosemgrep: python.sqlalchemy.security.audit.avoid-sqlalchemy-text.avoid-sqlalchemy-text
                # Safe: select_fields is controlled (column names only, not user input), user_id is parameterized
                result = auth_db.execute(text(f"""
                    SELECT {select_fields}
                    FROM users
                    WHERE id = :user_id
                """), {"user_id": user_id})
            except Exception:
                # Rollback again before final attempt
                auth_db.rollback()
                # Columns don't exist, select without them
                result = auth_db.execute(text("""
                    SELECT id, email, name, is_admin, has_write_access, created_at, updated_at
                    FROM users
                    WHERE id = :user_id
                """), {"user_id": user_id})
        
        row = result.first()
        if not row:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"Auth user with ID {user_id} not found",
            )
        
        is_admin = bool(row.is_admin)
        has_write_access = bool(row.has_write_access)
        is_active = getattr(row, 'is_active', None)
        if is_active is not None:
            is_active = bool(is_active)
        else:
            # NULL values should be treated as True (active)
            is_active = True
        
        # Get role - if column doesn't exist, derive from is_admin
        if has_role_column:
            role = getattr(row, 'role', None)
        else:
            role = _get_role_from_is_admin(auth_db, is_admin)
        
        return AuthUserResponse(
            id=row.id,
            name=getattr(row, 'name', None),
            email=row.email,
            is_admin=is_admin,
            has_write_access=has_write_access,
            is_active=is_active,
            role=role,
            created_at=getattr(row, 'created_at', None),
            updated_at=getattr(row, 'updated_at', None),
        )
    except HTTPException:
        raise
    except Exception as e:
        auth_db.rollback()
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"Failed to get auth user: {str(e)}",
        )


@router.put("/{user_id}", response_model=AuthUserResponse)
def update_auth_user(
    current_admin: Annotated[dict, Depends(get_current_admin_user)],
    user_id: int,
    request: AuthUserUpdateRequest,
    auth_db: Session = Depends(get_auth_db),
) -> AuthUserResponse:
    """Update an auth user - admin only."""
    try:
        # Check if user exists
        check_result = auth_db.execute(text("""
            SELECT id FROM users WHERE id = :user_id
        """), {"user_id": user_id})
        
        if not check_result.first():
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"Auth user with ID {user_id} not found",
            )
        
        # Check if email conflicts with another user (email is unique)
        check_conflict = auth_db.execute(text("""
            SELECT id FROM users 
            WHERE id != :user_id AND email = :email
        """), {
            "user_id": user_id,
            "email": request.email,
        })
        
        if check_conflict.first():
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail=f"User with email '{request.email}' already exists",
            )
        
        # Check if role column exists
        has_role_column = _check_column_exists(auth_db, "users", "role")
        
        # Update all fields (all are required, is_active and role are optional)
        dialect = auth_db.bind.dialect.name if auth_db.bind else 'postgresql'
        supports_returning = dialect == 'postgresql'
        
        # Determine is_admin value - if role is provided and role column doesn't exist, use role to set is_admin
        is_admin_value = request.is_admin
        if request.role is not None and not has_role_column:
            # Role column doesn't exist, derive is_admin from role
            is_admin_value = _get_is_admin_from_role(request.role)
        
        # Build UPDATE query - include is_active and role if provided and column exists
        update_fields = ["email = :email", "name = :name", "is_admin = :is_admin", "has_write_access = :has_write_access"]
        update_params = {
            "user_id": user_id,
            "email": request.email,
            "name": request.name,
            "is_admin": is_admin_value,
            "has_write_access": request.has_write_access,
        }
        
        # Update password if provided
        if request.password and request.password.strip():
            password_hash = hash_password(request.password)
            update_fields.append("password_hash = :password_hash")
            update_params["password_hash"] = password_hash
        
        if request.is_active is not None:
            update_fields.append("is_active = :is_active")
            update_params["is_active"] = request.is_active
        
        if request.role is not None and has_role_column:
            # Only update role if the column exists
            update_fields.append("role = :role")
            update_params["role"] = request.role
        
        update_sql = f"""
            UPDATE users
            SET {', '.join(update_fields)}
            WHERE id = :user_id
        """
        
        if supports_returning:
            # Build select fields - try to include optional columns
            select_fields = "id, email, name, is_admin, has_write_access"
            if request.is_active is not None or _check_column_exists(auth_db, "users", "is_active"):
                select_fields += ", is_active"
            if has_role_column:
                select_fields += ", role"
            select_fields += ", created_at, updated_at"
            # nosemgrep: python.sqlalchemy.security.audit.avoid-sqlalchemy-text.avoid-sqlalchemy-text
            # Safe: update_sql and select_fields are controlled (column names only, not user input), params are parameterized
            result = auth_db.execute(text(f"""
                {update_sql}
                RETURNING {select_fields}
            """), update_params)
            auth_db.commit()
            row = result.first()
        else:
            # SQLite - update then select
            auth_db.execute(text(update_sql), update_params)
            auth_db.commit()
            # Get the updated row - try to include optional columns
            try:
                if has_role_column:
                    result = auth_db.execute(text("""
                        SELECT id, email, name, is_admin, has_write_access, is_active, role, created_at, updated_at
                        FROM users
                        WHERE id = :user_id
                    """), {"user_id": user_id})
                else:
                    result = auth_db.execute(text("""
                        SELECT id, email, name, is_admin, has_write_access, is_active, created_at, updated_at
                        FROM users
                        WHERE id = :user_id
                    """), {"user_id": user_id})
                row = result.first()
            except Exception:
                auth_db.rollback()
                try:
                    result = auth_db.execute(text("""
                        SELECT id, email, name, is_admin, has_write_access, is_active, created_at, updated_at
                        FROM users
                        WHERE id = :user_id
                    """), {"user_id": user_id})
                    row = result.first()
                except Exception:
                    auth_db.rollback()
                    result = auth_db.execute(text("""
                        SELECT id, email, name, is_admin, has_write_access, created_at, updated_at
                        FROM users
                        WHERE id = :user_id
                    """), {"user_id": user_id})
                    row = result.first()
        
        if not row:
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="Failed to update user",
            )
        
        is_admin = bool(row.is_admin)
        has_write_access = bool(row.has_write_access)
        is_active = getattr(row, 'is_active', None)
        if is_active is not None:
            is_active = bool(is_active)
        else:
            # NULL values should be treated as True (active)
            is_active = True
        
        # Get role - if column doesn't exist, derive from is_admin
        if has_role_column:
            role = getattr(row, 'role', None)
        else:
            role = _get_role_from_is_admin(auth_db, is_admin)
        
        return AuthUserResponse(
            id=row.id,
            name=getattr(row, 'name', None),
            email=row.email,
            is_admin=is_admin,
            has_write_access=has_write_access,
            is_active=is_active,
            role=role,
            created_at=getattr(row, 'created_at', None),
            updated_at=getattr(row, 'updated_at', None),
        )
    except HTTPException:
        raise
    except Exception as e:
        auth_db.rollback()
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"Failed to update auth user: {str(e)}",
        )


@router.delete("/{user_id}")
def delete_auth_user(
    current_admin: Annotated[dict, Depends(get_current_admin_user)],
    user_id: int,
    auth_db: Session = Depends(get_auth_db),
) -> Response:
    """Delete an auth user - admin only.
    
    If the user has linked data (pending_photos, pending_identifications,
    inappropriate_photo_reports), the user will be set to inactive instead
    of deleted. Admins will be notified via logging.
    """
    try:
        # Check if user exists and get user info
        user_result = auth_db.execute(text("""
            SELECT id, email, name FROM users WHERE id = :user_id
        """), {"user_id": user_id})
        
        user_row = user_result.first()
        if not user_row:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"Auth user with ID {user_id} not found",
            )
        
        user_email = user_row.email
        user_name = user_row.name or user_email
        
        # Check for linked data in auth database
        pending_photos_count = auth_db.execute(text("""
            SELECT COUNT(*) FROM pending_photos WHERE user_id = :user_id
        """), {"user_id": user_id}).scalar() or 0
        
        pending_identifications_count = auth_db.execute(text("""
            SELECT COUNT(*) FROM pending_identifications WHERE user_id = :user_id
        """), {"user_id": user_id}).scalar() or 0
        
        inappropriate_reports_count = auth_db.execute(text("""
            SELECT COUNT(*) FROM inappropriate_photo_reports WHERE user_id = :user_id
        """), {"user_id": user_id}).scalar() or 0
        
        has_linked_data = (
            pending_photos_count > 0 or
            pending_identifications_count > 0 or
            inappropriate_reports_count > 0
        )
        
        if has_linked_data:
            # Check if is_active column exists by trying to query it
            dialect = auth_db.bind.dialect.name if auth_db.bind else "postgresql"
            has_is_active_column = False
            
            try:
                # Try to select is_active column to check if it exists
                test_result = auth_db.execute(text("""
                    SELECT is_active FROM users WHERE id = :user_id LIMIT 1
                """), {"user_id": user_id})
                test_result.first()
                has_is_active_column = True
            except Exception:
                # Column doesn't exist - this should have been added at startup
                # but if it wasn't, we can't proceed
                error_msg = "is_active column does not exist in auth database users table"
                logger.error(
                    f"Cannot deactivate auth user '{user_name}' (ID: {user_id}): {error_msg}"
                )
                raise HTTPException(
                    status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                    detail=(
                        f"Cannot delete user '{user_name}' because they have linked data "
                        f"({pending_photos_count} pending photo(s), "
                        f"{pending_identifications_count} pending identification(s), "
                        f"{inappropriate_reports_count} inappropriate photo report(s)) "
                        f"and the is_active column does not exist in the auth database users table. "
                        f"Please restart the server to add the column automatically, or contact "
                        f"your database administrator to add it manually: "
                        f"ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT TRUE"
                    ),
                )
            
            # Set user inactive instead of deleting
            if dialect == "postgresql":
                auth_db.execute(text("""
                    UPDATE users SET is_active = FALSE WHERE id = :user_id
                """), {"user_id": user_id})
            else:
                # SQLite uses 0 for FALSE
                auth_db.execute(text("""
                    UPDATE users SET is_active = 0 WHERE id = :user_id
                """), {"user_id": user_id})
            auth_db.commit()
            
            # Notify admins via logging
            logger.warning(
                f"Auth user '{user_name}' (ID: {user_id}, email: {user_email}) was set to inactive "
                f"instead of deleted because they have linked data: {pending_photos_count} pending "
                f"photo(s), {pending_identifications_count} pending identification(s), "
                f"{inappropriate_reports_count} inappropriate photo report(s). "
                f"Action performed by admin: {current_admin['username']}",
                extra={
                    "user_id": user_id,
                    "user_email": user_email,
                    "user_name": user_name,
                    "pending_photos_count": pending_photos_count,
                    "pending_identifications_count": pending_identifications_count,
                    "inappropriate_reports_count": inappropriate_reports_count,
                    "admin_username": current_admin["username"],
                }
            )
            
            # Return success but indicate user was deactivated
            return JSONResponse(
                status_code=status.HTTP_200_OK,
                content={
                    "message": (
                        f"User '{user_name}' has been set to inactive because they have "
                        f"linked data ({pending_photos_count} pending photo(s), "
                        f"{pending_identifications_count} pending identification(s), "
                        f"{inappropriate_reports_count} inappropriate photo report(s))."
                    ),
                    "deactivated": True,
                    "pending_photos_count": pending_photos_count,
                    "pending_identifications_count": pending_identifications_count,
                    "inappropriate_reports_count": inappropriate_reports_count,
                }
            )
        else:
            # No linked data - safe to delete
            auth_db.execute(text("""
                DELETE FROM users WHERE id = :user_id
            """), {"user_id": user_id})
            auth_db.commit()
            
            logger.info(
                f"Auth user '{user_name}' (ID: {user_id}, email: {user_email}) was deleted. "
                f"Action performed by admin: {current_admin['username']}",
                extra={
                    "user_id": user_id,
                    "user_email": user_email,
                    "user_name": user_name,
                    "admin_username": current_admin["username"],
                }
            )
            
            return Response(status_code=status.HTTP_204_NO_CONTENT)
            
    except HTTPException:
        raise
    except Exception as e:
        auth_db.rollback()
        error_str = str(e)
        # Check for permission errors
        if "permission denied" in error_str.lower() or "insufficient privilege" in error_str.lower():
            raise HTTPException(
                status_code=status.HTTP_403_FORBIDDEN,
                detail="Permission denied: The database user does not have DELETE permission on the users table. Please contact your database administrator.",
            )
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"Failed to delete auth user: {error_str}",
        )

