database.py

from models import conn, cursor
from schemas import ExpenseCreate, ExpenseOut

def create_expense(expense: ExpenseCreate) -> ExpenseOut:
    cursor.execute(
        "INSERT INTO expenses (amount, category, note, date) VALUES (?, ?, ?, ?)",
        (expense.amount, expense.category, expense.note, expense.date)
    )
    conn.commit()
    id = cursor.lastrowid
    return ExpenseOut(id=id, **expense.dict())

def get_all_expenses():
    cursor.execute("SELECT * FROM expenses")
    rows = cursor.fetchall()
    return [ExpenseOut(id=row[0], amount=row[1], category=row[2], note=row[3], date=row[4]) for row in rows]

def get_expenses_by_category(category):
    cursor.execute("SELECT * FROM expenses WHERE category = ?", (category,))
    rows = cursor.fetchall()
    return [ExpenseOut(id=row[0], amount=row[1], category=row[2], note=row[3], date=row[4]) for row in rows]

def get_total_expense():
    cursor.execute("SELECT SUM(amount) FROM expenses")
    total = cursor.fetchone()[0]
    return total or 0