source: backend_python/main.py@ aef0c04

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
from .database import get_db
from .models import User, TransactionAccount, Transaction, TransactionBreakdown, Tag, TagAssignedToTransaction
from pydantic import BaseModel
from typing import List, Optional
from datetime import datetime
from fastapi.security import OAuth2PasswordBearer
from .auth import create_access_token, decode_access_token, is_admin, hash_password, verify_password
from sqlalchemy import func, literal_column, select

# Initialize FastAPI app
app = FastAPI()

oauth2_scheme = OAuth2PasswordBearer(tokenUrl="/auth/login/")

# Pydantic schemas for validation and response models
class UserCreate(BaseModel):
    user_name: str
    email: str
    password: str

class UserResponse(BaseModel):
    user_id: int
    user_name: str
    email: str

    class Config:
        from_attributes = True

class TransactionAccountCreate(BaseModel):
    account_name: str
    balance: float

class TransactionAccountResponse(BaseModel):
    transaction_account_id: int
    account_name: str
    balance: float

    class Config:
        from_attributes = True

class TransactionBreakdownResponse(BaseModel):
    transaction_account_id: int
    earned_amount: float
    spent_amount: float

    class Config:
        from_attributes = True

class TransactionCreateRequest(BaseModel):
    transaction_name: str
    amount: float = 0.0  # Default to 0 if not provided
    date: Optional[datetime] = None  # Default to None, will use current time if not provided
    tag_id: Optional[int] = None  # Optional tag
    target_account_id: int  # Mandatory target account
    breakdowns: Optional[List[TransactionBreakdownResponse]] = None  # Optional list of breakdowns


class TransactionUpdate(BaseModel):
    transaction_name: str = None
    amount: float = None
    net_amount: float = None
    date: datetime = None

class TransactionResponse(BaseModel):
    transaction_id: int
    transaction_name: str
    amount: float
    net_amount: float
    date: datetime

    class Config:
        json_encoders = {
            datetime: lambda v: v.isoformat()  # Serialize datetime as ISO 8601 string
        }
        from_attributes = True

class TagCreate(BaseModel):
    tag_name: str

class TagResponse(BaseModel):
    tag_id: int
    tag_name: str

    class Config:
        from_attributes = True

class TagAssign(BaseModel):
    transaction_id: int
    tag_id: int

class AuthRequest(BaseModel):
    user_name: Optional[str] = None
    email: str
    password: str

class AuthResponse(BaseModel):
    access_token: str
    token_type: str

# Dependency to get the current user
def get_current_user(
    token: str = Depends(oauth2_scheme), 
    db: Session = Depends(get_db)
):
    """
    Retrieves the current user based on the access token.
    """
    try:
        payload = decode_access_token(token)
        user_id = payload.get("sub")
        if not user_id:
            raise HTTPException(
                status_code=401, 
                detail="Invalid authentication credentials", 
                headers={"WWW-Authenticate": "Bearer"}
            )
        
        user = db.query(User).filter(User.user_id == user_id).first()
        if not user:
            raise HTTPException(
                status_code=401, 
                detail="Invalid authentication credentials",
                headers={"WWW-Authenticate": "Bearer"}
            )
        
        return user
    except Exception as e:
        print(f"Error decoding token or fetching user: {e}")
        raise HTTPException(
            status_code=401, 
            detail="Invalid authentication credentials", 
            headers={"WWW-Authenticate": "Bearer"}
        )

# Routes
@app.get("/")
def read_root():
    return {"message": "Welcome to the Fein Prototype API"}

@app.post("/auth/register/", response_model=AuthResponse)
def register(
    auth_request: AuthRequest, 
    db: Session = Depends(get_db)
):
    # Check if email already exists
    existing_user = db.query(User).filter(User.email == auth_request.email).first()
    if existing_user:
        raise HTTPException(
            status_code=400, 
            detail="Email already registered"
        )

    # Hash password and create new user
    hashed_password = hash_password(auth_request.password)
    new_user = User(
        user_name=auth_request.user_name,
        email=auth_request.email,
        password=hashed_password
    )
    db.add(new_user)
    db.commit()
    db.refresh(new_user)

    # Return access token
    access_token = create_access_token({"sub": new_user.user_id, "email": new_user.email})
    return {"access_token": access_token, "token_type": "bearer"}

@app.post("/auth/login/", response_model=AuthResponse)
def login(
    auth_request: AuthRequest, 
    db: Session = Depends(get_db)
):
    # Verify email and password
    user = db.query(User).filter(User.email == auth_request.email).first()
    if not user or not verify_password(auth_request.password, user.password):
        raise HTTPException(
            status_code=401, 
            detail="Invalid email or password"
        )

    # Return access token
    access_token = create_access_token({"sub": user.user_id, "email": user.email})
    return {"access_token": access_token, "token_type": "bearer"}

@app.get("/admin/accounts/", response_model=List[TransactionAccountResponse])
def admin_get_all_accounts(
    user: User = Depends(get_current_user), 
    db: Session = Depends(get_db)
):
    """
    Admin can fetch all transaction accounts.
    """
    if not is_admin(user.email):
        raise HTTPException(
            status_code=403, 
            detail="Access denied"
        )
    return db.query(TransactionAccount).all()


@app.post("/accounts/", response_model=TransactionAccountResponse)
def create_account(
    account: TransactionAccountCreate, 
    user: User = Depends(get_current_user), 
    db: Session = Depends(get_db)
):
    new_account = TransactionAccount(
        account_name=account.account_name, 
        balance=account.balance, 
        user_id=user.user_id
    )
    db.add(new_account)
    db.commit()
    db.refresh(new_account)
    return new_account

@app.get("/accounts/", response_model=List[TransactionAccountResponse])
def get_accounts(
    user: User = Depends(get_current_user), 
    db: Session = Depends(get_db)
):
    """
    Admin can fetch all accounts, regular users only their accounts.
    """
    query = db.query(TransactionAccount)

    if is_admin(user.email):
        return query.all()
    
    return query.filter(TransactionAccount.user_id == user.user_id).all()
        

@app.post("/transactions/", response_model=TransactionResponse)
def create_transaction(
    transaction_request: TransactionCreateRequest, 
    user: User = Depends(get_current_user), 
    db: Session = Depends(get_db)
):
    """
    Admins can create transactions for any account; regular users only for their accounts.
    Create a transaction and associate it with the user's accounts via breakdowns.
    """
    # Admin bypasses ownership checks
    if not is_admin(user.email):
        # Validate target account ownership
        target_account = db.query(TransactionAccount).filter(
            TransactionAccount.transaction_account_id == transaction_request.target_account_id,
            TransactionAccount.user_id == user.user_id
        ).first()
        if not target_account:
            raise HTTPException(
                status_code=403, 
                detail="Access denied to target account."
            )

    # Create transaction
    new_transaction = Transaction(
        transaction_name=transaction_request.transaction_name,
        amount=transaction_request.amount,
        net_amount=0.0,  # Will be updated based on breakdowns
        date=transaction_request.date or datetime.utcnow(),  # Use current UTC time if not provided
    )
    db.add(new_transaction)
    db.commit()
    db.refresh(new_transaction)

    # Associate a tag, if provided
    if transaction_request.tag_id:
        tag = db.query(Tag).filter(Tag.tag_id == transaction_request.tag_id).first()
        if tag:
            tag_assignment = TagAssignedToTransaction(
                transaction_id=new_transaction.transaction_id,
                tag_id=tag.tag_id
            )
            db.add(tag_assignment)

    # Add breakdowns
    net_amount = 0.0
    if transaction_request.breakdowns:
        for breakdown in transaction_request.breakdowns:
            # Validate breakdown account ownership
            breakdown_account = db.query(TransactionAccount).filter(
                TransactionAccount.transaction_account_id == breakdown.transaction_account_id,
                TransactionAccount.user_id == user.user_id
            ).first()
            if not breakdown_account:
                raise HTTPException(
                    status_code=403, 
                    detail=f"Access denied to breakdown account {breakdown.transaction_account_id}."
                )

            # Create breakdown
            new_breakdown = TransactionBreakdown(
                transaction_id=new_transaction.transaction_id,
                transaction_account_id=breakdown.transaction_account_id,
                earned_amount=breakdown.earned_amount,
                spent_amount=breakdown.spent_amount
            )
            db.add(new_breakdown)

            # Calculate net amount
            net_amount += breakdown.earned_amount - breakdown.spent_amount

    # Update transaction's net amount
    new_transaction.net_amount = net_amount
    db.commit()
    db.refresh(new_transaction)

    return new_transaction

@app.get("/transactions/", response_model=List[TransactionResponse])
def get_transactions(
    user: User = Depends(get_current_user), 
    db: Session = Depends(get_db)
):
    """
    Fetch transactions based on user role, excluding placeholder transactions.
    - Admin: Fetch all non-placeholder transactions.
    - Regular User: Fetch non-placeholder transactions tied to the user's accounts via transaction breakdowns.
    """
    query = db.query(Transaction)
    
    if is_admin(user.email):
        # Admins see all non-placeholder transactions
        transactions = (
            query
            .filter(~Transaction.transaction_name.like("Tag_%_placeholder"))
            .all()
        )
    else:
        transactions = (
            query
            .join(TransactionBreakdown, Transaction.transaction_id == TransactionBreakdown.transaction_id)
            .join(TransactionAccount, TransactionBreakdown.transaction_account_id == TransactionAccount.transaction_account_id)
            .filter(TransactionAccount.user_id == user.user_id)
            .filter(~Transaction.transaction_name.like("Tag_%_placeholder"))  # Exclude placeholders
            .all()
        )

    return [
        {
            **transaction.__dict__,
            "date": transaction.date.isoformat()  # Convert datetime to ISO 8601 string
        }
        for transaction in transactions
    ]

@app.get("/transactions/{transaction_id}", response_model=TransactionResponse)
def get_transaction_by_id(
    transaction_id: int,
    user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """
    Retrieve a single transaction by its ID, ensuring access is restricted
    to the transaction creator or an admin user.
    """
    # If the user is an admin, they can access any transaction
    if is_admin(user.email):
        transaction = db.query(Transaction).filter(Transaction.transaction_id == transaction_id).first()
        if not transaction:
            raise HTTPException(
                status_code=404, 
                detail="Transaction not found."
            )
        return transaction
    
    # Otherwise, restrict access to the transaction creator
    transaction = (
        db.query(Transaction)
        .join(TransactionBreakdown, Transaction.transaction_id == TransactionBreakdown.transaction_id)
        .join(TransactionAccount, TransactionBreakdown.transaction_account_id == TransactionAccount.transaction_account_id)
        .filter(Transaction.transaction_id == transaction_id)
        .filter(TransactionAccount.user_id == user.user_id)
        .first()
    )
    if not transaction:
        raise HTTPException(
            status_code=404, 
            detail="Transaction not found or access denied."
        )
    
    return transaction

@app.get("/transactions/{transaction_id}/breakdowns", response_model=List[TransactionBreakdownResponse])
def get_transaction_breakdowns(
    transaction_id: int, 
    user: User = Depends(get_current_user), 
    db: Session = Depends(get_db)
):
    """
    Fetch transaction breakdowns for a specific transaction.
    """
    breakdowns = (
        db.query(TransactionBreakdown)
        .join(TransactionAccount, TransactionBreakdown.transaction_account_id == TransactionAccount.transaction_account_id)
        .filter(TransactionBreakdown.transaction_id == transaction_id)
        .filter(TransactionAccount.user_id == user.user_id)
        .all()
    )

    if not breakdowns:
        raise HTTPException(
            status_code=404, 
            detail="No breakdowns found for this transaction."
        )

    return breakdowns


@app.put("/transactions/{transaction_id}", response_model=TransactionResponse)
def update_transaction(
    transaction_id: int,
    transaction_update: TransactionUpdate, 
    user: User = Depends(get_current_user), 
    db: Session = Depends(get_db)
):
    """
    Admins can update any transaction
    Regular users update a transaction only if it belongs to the logged-in user.
    """
    query = db.query(Transaction)
    
    if is_admin(user.email):
        transaction = (
            query
            .filter(Transaction.transaction_id == transaction_id)
            .first()
        )
        if not transaction:
            raise HTTPException(
                status_code=404, 
                detail="Transaction not found."
            )
    else:
        transaction = (
            query
            .join(TransactionBreakdown, Transaction.transaction_id == TransactionBreakdown.transaction_id)
            .join(TransactionAccount, TransactionBreakdown.transaction_account_id == TransactionAccount.transaction_account_id)
            .filter(Transaction.transaction_id == transaction_id)
            .filter(TransactionAccount.user_id == user.user_id)
            .first()
        )
        if not transaction:
            raise HTTPException(
                status_code=404, 
                detail="Transaction not found or access denied."
            )

    # Update transaction fields
    for key, value in transaction_update.dict(exclude_unset=True).items():
        setattr(transaction, key, value)

    db.commit()
    db.refresh(transaction)
    return transaction

@app.delete("/transactions/{transaction_id}")
def delete_transaction(
    transaction_id: int,
    user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """
    Admins can delete any transaction
    Regular users can delete a transaction only if it belongs to the logged-in user.
    """
    query = db.query(Transaction)

    if is_admin(user.email):
        transaction = (
            query
            .filter(Transaction.transaction_id == transaction_id)
            .first()
        )
        if not transaction:
            raise HTTPException(
                status_code=404, 
                detail="Transaction not found."
            )
    else:
        transaction = (
            query
            .join(TransactionBreakdown, Transaction.transaction_id == TransactionBreakdown.transaction_id)
            .join(TransactionAccount, TransactionBreakdown.transaction_account_id == TransactionAccount.transaction_account_id)
            .filter(Transaction.transaction_id == transaction_id)
            .filter(TransactionAccount.user_id == user.user_id)
            .first()
        )
        if not transaction:
            raise HTTPException(
                status_code=404, 
                detail="Transaction not found or access denied."
            )

    db.delete(transaction)
    db.commit()
    return {"message": "Transaction deleted successfully"}


@app.post("/tags/", response_model=TagResponse)
def create_tag(
    tag: TagCreate,
    user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """
    Create a tag associated with the logged-in user by linking it to a placeholder transaction.
    """
    # Create the tag
    new_tag = Tag(tag_name=tag.tag_name)
    db.add(new_tag)
    db.commit()
    db.refresh(new_tag)

    # Create a dummy transaction linked to the user's first account
    user_account = (
        db.query(TransactionAccount)
        .filter(TransactionAccount.user_id == user.user_id)
        .first()
    )
    if not user_account:
        raise HTTPException(
            status_code=403, 
            detail="No account available to associate with the tag."
        )

    # Associate the tag with a dummy transaction for the user
    dummy_transaction = Transaction(
        transaction_name=f"Tag_{new_tag.tag_id}_placeholder",
        amount=0,
        net_amount=0,
        date=datetime.utcnow(),
    )
    db.add(dummy_transaction)
    db.commit()
    db.refresh(dummy_transaction)

    # Link the dummy transaction to the user's account
    dummy_breakdown = TransactionBreakdown(
        transaction_id=dummy_transaction.transaction_id,
        transaction_account_id=user_account.transaction_account_id,
        earned_amount=0,
        spent_amount=0,
    )
    db.add(dummy_breakdown)

    # Associate the tag with the dummy transaction
    tag_assignment = TagAssignedToTransaction(
        transaction_id=dummy_transaction.transaction_id,
        tag_id=new_tag.tag_id,
    )
    db.add(tag_assignment)
    db.commit()

    return new_tag

@app.get("/tags/", response_model=List[TagResponse])
def get_tags(
    user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """
    Admins can fetch all tags
    Regular users can retrieve tags accessible to the logged-in user based on their transactions.
    """
    if is_admin(user.email):
        return db.query(Tag).all()

    accessible_tags = (
        db.query(Tag)
        .join(TagAssignedToTransaction, Tag.tag_id == TagAssignedToTransaction.tag_id)
        .join(Transaction, TagAssignedToTransaction.transaction_id == Transaction.transaction_id)
        .join(TransactionBreakdown, Transaction.transaction_id == TransactionBreakdown.transaction_id)
        .join(TransactionAccount, TransactionBreakdown.transaction_account_id == TransactionAccount.transaction_account_id)
        .filter(TransactionAccount.user_id == user.user_id)
        .distinct()
        .all()
    )
    return accessible_tags

@app.post("/tags/assign/", response_model=dict)
def assign_tag_to_transaction(
    tag_assign: TagAssign,
    user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """
    Assign a tag to a transaction.
    - Admins can assign any tag to any transaction.
    - Regular users can assign a tag if:
        - The transaction belongs to them.
        - The tag is accessible (created by them or linked to their transactions).
    """
    # Ensure the transaction belongs to the logged-in user
    transaction = (
        db.query(Transaction)
        .join(TransactionBreakdown, Transaction.transaction_id == TransactionBreakdown.transaction_id)
        .join(TransactionAccount, TransactionBreakdown.transaction_account_id == TransactionAccount.transaction_account_id)
        .filter(Transaction.transaction_id == tag_assign.transaction_id)
        .filter(TransactionAccount.user_id == user.user_id)
        .first()
    )
    if not transaction:
        raise HTTPException(status_code=404, detail="Transaction not found or access denied.")

    # Ensure the tag is accessible to the logged-in user
    tag_accessible = (
        db.query(Tag)
        .join(TagAssignedToTransaction, Tag.tag_id == TagAssignedToTransaction.tag_id, isouter=True)
        .join(Transaction, TagAssignedToTransaction.transaction_id == Transaction.transaction_id, isouter=True)
        .join(TransactionBreakdown, Transaction.transaction_id == TransactionBreakdown.transaction_id, isouter=True)
        .join(TransactionAccount, TransactionBreakdown.transaction_account_id == TransactionAccount.transaction_account_id, isouter=True)
        .filter(Tag.tag_id == tag_assign.tag_id)
        .filter(
            (TransactionAccount.user_id == user.user_id) |  # Tag linked to the user's transactions
            (TransactionAccount.user_id.is_(None))         # Newly created tag not yet assigned
        )
        .first()
    )
    if not tag_accessible:
        raise HTTPException(
            status_code=404, 
            detail="Access denied to the tag."
        )
    
    # Check if the tag is already assigned to the transaction
    existing_assignment = (
        db.query(TagAssignedToTransaction)
        .filter(
            TagAssignedToTransaction.transaction_id == tag_assign.transaction_id,
            TagAssignedToTransaction.tag_id == tag_assign.tag_id,
        )
        .first()
    )
    if existing_assignment:
        raise HTTPException(
            status_code=400, 
            detail="Tag already assigned to this transaction."
        )

    # Assign the tag to the transaction
    assignment = TagAssignedToTransaction(
        transaction_id=tag_assign.transaction_id,
        tag_id=tag_assign.tag_id,
    )
    db.add(assignment)
    db.commit()

    return {"message": "Tag assigned to transaction successfully"}

@app.get("/tags/transaction/{transaction_id}", response_model=List[TagResponse])
def get_transaction_tags_for_user(
    transaction_id: int, 
    user: User = Depends(get_current_user), 
    db: Session = Depends(get_db)
):
    """
    Retrieve tags for a specific transaction.
    - Admins can retrieve tags for any transaction.
    - Regular users can retrieve tags if the transaction belongs to them.
    """
    # Admins can access tags for any transaction
    if is_admin(user.email):
        tags = (
            db.query(Tag)
            .join(TagAssignedToTransaction, Tag.tag_id == TagAssignedToTransaction.tag_id)
            .filter(TagAssignedToTransaction.transaction_id == transaction_id)
            .all()
        )
        return tags
    
    # Check if the transaction belongs to the user
    transaction = (
        db.query(Transaction)
        .join(TransactionBreakdown, Transaction.transaction_id == TransactionBreakdown.transaction_id)
        .join(TransactionAccount, TransactionBreakdown.transaction_account_id == TransactionAccount.transaction_account_id)
        .filter(Transaction.transaction_id == transaction_id)
        .filter(TransactionAccount.user_id == user.user_id)
        .first()
    )
    if not transaction:
        raise HTTPException(
            status_code=403, 
            detail="Access denied"
        )
    
    # Retrieve tags for the transaction
    tags = (
        db.query(Tag)
        .join(TagAssignedToTransaction, Tag.tag_id == TagAssignedToTransaction.tag_id)
        .filter(TagAssignedToTransaction.transaction_id == transaction_id)
        .all()
    )

    return tags

@app.get("/reports/total-spending", response_model=dict)
def get_total_spending(
    user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """
    Calculate and return total spending for the logged-in user.
    - Admins can view total spending for all users.
    """
    try:
        query = db.query(
                func
                .sum(Transaction.amount)
                .label("total_spent")
            )

        if is_admin(user.email):
            # Admin: Total spending for all users
            total_spent = (
                query
                .filter(Transaction.amount > 0)
                .scalar()
            )
        else:
            # Regular User: Total spending for their accounts
            total_spent = (
                query
                .join(TransactionBreakdown, Transaction.transaction_id == TransactionBreakdown.transaction_id)
                .join(TransactionAccount, TransactionBreakdown.transaction_account_id == TransactionAccount.transaction_account_id)
                .filter(TransactionAccount.user_id == user.user_id)
                .filter(Transaction.amount > 0)
                .scalar()
            )

        return {"total_spent": total_spent or 0.0}
    except Exception as e:
        print(f"Error calculating total spending: {e}")
        raise HTTPException(
            status_code=500, 
            detail="Failed to calculate total spending."
        )
    
@app.get("/reports/spending-by-category", response_model=dict)
def get_spending_by_category(
    user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """
    Calculate and return spending grouped by category (tags) for the logged-in user.
    - Admins can view spending by category for all users.
    """
    try:
        # Base query
        query = db.query(
            Tag.tag_name,
            func.sum(Transaction.amount).label("total_spent")
        ).join(
            TagAssignedToTransaction, Tag.tag_id == TagAssignedToTransaction.tag_id
        ).join(
            Transaction, TagAssignedToTransaction.transaction_id == Transaction.transaction_id
        ).filter(
            Transaction.amount > 0  # Include only positive amounts
        )

        # Apply filters for regular users
        if not is_admin(user.email):
            query = query.join(
                TransactionBreakdown, Transaction.transaction_id == TransactionBreakdown.transaction_id
            ).join(
                TransactionAccount, TransactionBreakdown.transaction_account_id == TransactionAccount.transaction_account_id
            ).filter(
                TransactionAccount.user_id == user.user_id
            )

        # Group by tag and calculate the total spending for each category
        spending_by_category = query.group_by(Tag.tag_name).all()

        # Prepare the response as a dictionary
        response = {row.tag_name: float(row.total_spent or 0) for row in spending_by_category}
        return {"spending_by_category": response}

    except Exception as e:
        print(f"Error calculating spending by category: {e}")
        raise HTTPException(
            status_code=500, 
            detail="Failed to calculate spending by category."
        )

@app.get("/reports/spending-by-date-range", response_model=dict)
def get_spending_by_date_range(
    start_date: str, # Expecting date in 'YYYY-MM-DD' format
    end_date: str,
    user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """
    Calculate and return spending within a specified date range for the logged-in user.
    - Admins can view spending within the date range for all users.
    """
    try:
        # Convert input dates to `datetime`
        start_date_parsed = datetime.strptime(start_date, "%Y-%m-%d")
        end_date_parsed = datetime.strptime(end_date, "%Y-%m-%d")

        # Query base
        query = db.query(func.sum(Transaction.amount).label("total_spent"))

        if is_admin(user.email):
            # Admin: Total spending for all users within the date range
            total_spent = (
                query
                .filter(
                    Transaction.date >= start_date_parsed,
                    Transaction.date <= end_date_parsed,
                    Transaction.amount > 0
                )
                .scalar()
            )
        else:
            # Regular User: Total spending within the date range for their accounts
            total_spent = (
                query
                .join(TransactionBreakdown, Transaction.transaction_id == TransactionBreakdown.transaction_id)
                .join(TransactionAccount, TransactionBreakdown.transaction_account_id == TransactionAccount.transaction_account_id)
                .filter(
                    TransactionAccount.user_id == user.user_id,
                    Transaction.date >= start_date_parsed,
                    Transaction.date <= end_date_parsed,
                    Transaction.amount > 0
                )
                .scalar()
            )

        # Return result
        return {"total_spent": total_spent or 0.0}
    except Exception as e:
        print(f"Error calculating spending by date range: {e}")
        raise HTTPException(
            status_code=500,
            detail="Failed to calculate spending by date range."
        )

@app.get("/reports/exceeding-transactions", response_model=List[dict])
def get_exceeding_transactions(
    account_name: Optional[str] = None,  # Allow filtering by account name
    user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """
    Retrieve a list of transactions that exceeded the balance of an account, sorted chronologically.
    - Admins can view for all users.
    - Regular users can view for their own accounts.
    """
    # Define the subquery to calculate `calculated_balance` using a window function
    subquery = (
        db.query(
            Transaction.transaction_id,
            Transaction.transaction_name,
            Transaction.date.label("transaction_date"),
            TransactionAccount.account_name,
            User.user_id,
            User.user_name,
            TransactionBreakdown.spent_amount.label("transaction_amount"),
            func.sum(TransactionBreakdown.earned_amount - TransactionBreakdown.spent_amount)
            .over(
                partition_by=TransactionBreakdown.transaction_account_id,
                order_by=Transaction.date
            )
            .label("calculated_balance"),
        )
        .join(TransactionAccount, TransactionAccount.transaction_account_id == TransactionBreakdown.transaction_account_id)
        .join(User, TransactionAccount.user_id == User.user_id)
        .join(Transaction, Transaction.transaction_id == TransactionBreakdown.transaction_id)
        .subquery()
    )

    query = db.query(
        subquery.c.transaction_id,
        subquery.c.transaction_name,
        subquery.c.transaction_date,
        subquery.c.account_name,
        subquery.c.user_id,
        subquery.c.user_name,
        subquery.c.transaction_amount,
        subquery.c.calculated_balance,
    ).filter(
        subquery.c.transaction_amount > subquery.c.calculated_balance,  # Filter where transaction amount exceeds balance
        subquery.c.transaction_amount > 0,  # Filter for positive transactions
    )

    if account_name:
        query = query.filter(subquery.c.account_name == account_name)

    # Apply user-specific filtering for non-admins
    if not is_admin(user.email):
        query = query.filter(subquery.c.user_id == user.user_id)

    # Order results
    query = query.order_by(
        subquery.c.user_id,
        subquery.c.account_name,
        subquery.c.transaction_date.desc(),
    )

    # Execute the query and fetch results
    results = query.all()

    if not results:
        return []

    # Prepare response
    response = [
        {
            "user_id": row.user_id,
            "user_name": row.user_name,
            "account_name": row.account_name,
            "transaction_id": row.transaction_id,
            "transaction_name": row.transaction_name,
            "transaction_amount": row.transaction_amount,
            "transaction_date": row.transaction_date,
            "calculated_balance": row.calculated_balance,
        }
        for row in results
    ]

    return response

@app.get("/reports/exceeding-current-balance", response_model=List[dict])
def get_exceeding_current_balance(
    user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """
    Retrieve a list of transactions that exceed the current balance of accounts.
    - Admins can view for all users.
    - Regular users can view for their own accounts.
    """
    query = (
        db.query(
            User.user_id,
            User.user_name,
            TransactionAccount.account_name,
            TransactionAccount.balance.label("current_balance"),
            Transaction.transaction_id,
            Transaction.transaction_name,
            TransactionBreakdown.spent_amount.label("transaction_amount"),
            Transaction.date.label("transaction_date"),
        )
        .join(TransactionAccount, TransactionAccount.user_id == User.user_id)
        .join(TransactionBreakdown, TransactionBreakdown.transaction_account_id == TransactionAccount.transaction_account_id)
        .join(Transaction, Transaction.transaction_id == TransactionBreakdown.transaction_id)
        .filter(TransactionBreakdown.spent_amount > TransactionAccount.balance)  # Transactions exceeding account balance
        .filter(TransactionBreakdown.spent_amount > 0)  # Positive transactions only
    )

    # Apply user-specific filtering for non-admins
    if not is_admin(user.email):
        query = query.filter(TransactionAccount.user_id == user.user_id)

    # Order results
    results = query.order_by(User.user_id, TransactionAccount.account_name, Transaction.date.desc()).all()

    # Prepare response
    return [
        {
            "user_id": row.user_id,
            "user_name": row.user_name,
            "account_name": row.account_name,
            "current_balance": float(row.current_balance),
            "transaction_id": row.transaction_id,
            "transaction_name": row.transaction_name,
            "transaction_amount": float(row.transaction_amount),
            "transaction_date": row.transaction_date,
        }
        for row in results
    ]

@app.get("/reports/exceeding-total-balances", response_model=List[dict])
def get_exceeding_total_balances(
    user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """
    Retrieve a chronological list of transactions that exceed the calculated total balances for all accounts.
    - Admins can view for all users.
    - Regular users can view for their own accounts.
    """
    # Subquery to calculate the running balance using a window function
    subquery = (
        db.query(
            User.user_id.label("user_id"),
            User.user_name.label("user_name"),
            Transaction.transaction_id.label("transaction_id"),
            Transaction.transaction_name.label("transaction_name"),
            Transaction.date.label("transaction_date"),
            TransactionBreakdown.spent_amount.label("transaction_amount"),
            func.sum(TransactionBreakdown.earned_amount - TransactionBreakdown.spent_amount)
            .over(partition_by=User.user_id, order_by=Transaction.date)
            .label("calculated_total_balance")
        )
        .join(TransactionAccount, TransactionAccount.user_id == User.user_id)
        .join(TransactionBreakdown, TransactionBreakdown.transaction_account_id == TransactionAccount.transaction_account_id)
        .join(Transaction, Transaction.transaction_id == TransactionBreakdown.transaction_id)
        .filter(TransactionBreakdown.spent_amount > 0)  # Only positive transactions
        .subquery()
    )

    # Outer query to filter transactions exceeding the calculated balance
    query = db.query(
        subquery.c.user_id,
        subquery.c.user_name,
        subquery.c.transaction_id,
        subquery.c.transaction_name,
        subquery.c.transaction_date,
        subquery.c.transaction_amount,
        subquery.c.calculated_total_balance
    ).filter(
        subquery.c.transaction_amount > subquery.c.calculated_total_balance  # Exceeds the total balance
    )

    # Apply user-specific filtering for non-admin users
    if not is_admin(user.email):
        query = query.filter(subquery.c.user_id == user.user_id)

    # Execute the query and return results
    results = query.order_by(subquery.c.user_id, subquery.c.transaction_date.desc()).all()

    return [
        {
            "user_id": row.user_id,
            "user_name": row.user_name,
            "transaction_id": row.transaction_id,
            "transaction_name": row.transaction_name,
            "transaction_date": row.transaction_date,
            "transaction_amount": float(row.transaction_amount),
            "calculated_total_balance": float(row.calculated_total_balance),
        }
        for row in results
    ]

@app.get("/reports/exceeding-user-total-balance", response_model=List[dict])
def get_exceeding_user_total_balance(
    user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """
    Retrieve a list of users whose transactions exceed the total balance of all their accounts.
    - Admins can view results for all users.
    - Regular users can only see their own data.
    """
    # Subquery to calculate the total balance for each user
    total_balance_subquery = (
        db.query(
            TransactionAccount.user_id.label("user_id"),
            func.sum(TransactionAccount.balance).label("total_balance")
        )
        .group_by(TransactionAccount.user_id)
        .subquery()
    )

    # Main query
    query = (
        db.query(
            User.user_id,
            User.user_name,
            func.sum(TransactionBreakdown.spent_amount).label("total_transaction_amount"),
            total_balance_subquery.c.total_balance.label("user_total_balance")
        )
        .join(TransactionAccount, TransactionAccount.user_id == User.user_id)
        .join(TransactionBreakdown, TransactionBreakdown.transaction_account_id == TransactionAccount.transaction_account_id)
        .join(Transaction, Transaction.transaction_id == TransactionBreakdown.transaction_id)
        .join(total_balance_subquery, total_balance_subquery.c.user_id == User.user_id)
        .filter(Transaction.date <= func.current_date())  # Only transactions up to the current date
        .group_by(User.user_id, User.user_name, total_balance_subquery.c.total_balance)
        .having(func.sum(TransactionBreakdown.spent_amount) > total_balance_subquery.c.total_balance)  # Exceeds total balance
        .order_by(User.user_id)
    )

    # Apply user-specific filtering for non-admins
    if not is_admin(user.email):
        query = query.filter(User.user_id == user.user_id)

    # Execute the query
    results = query.all()

    # Prepare response
    return [
        {
            "user_id": row.user_id,
            "user_name": row.user_name,
            "total_transaction_amount": float(row.total_transaction_amount),
            "user_total_balance": float(row.user_total_balance),
        }
        for row in results
    ]