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

from __future__ import annotations

from typing import Annotated

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

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

router = APIRouter(prefix="/auth-users", tags=["auth-users"])


@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:
        # Query users from auth database with all columns from schema
        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
        """))
        
        rows = result.fetchall()
        users = []
        for row in rows:
            # 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)
            
            created_at = row.created_at
            updated_at = row.updated_at
            
            users.append(AuthUserResponse(
                id=user_id,
                name=name,
                email=email,
                is_admin=is_admin,
                has_write_access=has_write_access,
                created_at=created_at,
                updated_at=updated_at,
            ))
        
        return AuthUsersListResponse(items=users, total=len(users))
    except Exception as e:
        import traceback
        error_detail = f"Failed to list auth users: {str(e)}\n{traceback.format_exc()}"
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=error_detail,
        )


@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:
        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)
        
        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:
        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",
            )
        
        # Update all fields (all are required)
        dialect = auth_db.bind.dialect.name if auth_db.bind else 'postgresql'
        supports_returning = dialect == 'postgresql'
        
        if supports_returning:
            result = auth_db.execute(text("""
                UPDATE users
                SET email = :email,
                    name = :name,
                    is_admin = :is_admin,
                    has_write_access = :has_write_access
                WHERE id = :user_id
                RETURNING id, email, name, is_admin, has_write_access, created_at, updated_at
            """), {
                "user_id": user_id,
                "email": request.email,
                "name": request.name,
                "is_admin": request.is_admin,
                "has_write_access": request.has_write_access,
            })
            auth_db.commit()
            row = result.first()
        else:
            # SQLite - update then select
            auth_db.execute(text("""
                UPDATE users
                SET email = :email,
                    name = :name,
                    is_admin = :is_admin,
                    has_write_access = :has_write_access
                WHERE id = :user_id
            """), {
                "user_id": user_id,
                "email": request.email,
                "name": request.name,
                "is_admin": request.is_admin,
                "has_write_access": request.has_write_access,
            })
            auth_db.commit()
            # Get the updated row
            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)
        
        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 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."""
    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",
            )
        
        # Delete user
        auth_db.execute(text("""
            DELETE FROM users WHERE id = :user_id
        """), {"user_id": user_id})
        
        auth_db.commit()
        
        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}",
        )

