File Upload API

.dockerignore

@@ -1,4 +1,5 @@
 __pycache__
 storage
 data/*
-venv
+venv
+myvenv

.gitignore

@@ -2,4 +2,5 @@ __pycache__
 storage
 .env
 data/*
-venv
+venv
+myvenv

Makefile

@@ -6,3 +6,9 @@ build-postgres:
 
 run-postgres:
 	docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=${POSTGRES_PASSWORD} -e POSTGRES_USER=${POSTGRES_USER} --name ${POSTGRES_DB_NAME} codepath_project_postgres
+
+run-migrations:
+	alembic upgrade head
+
+generate-migrations:
+	alembic revision --autogenerate -m "$(migration_title)"

README.md

@@ -90,13 +90,13 @@ make run-postgres
 To generate new migrations, run:
 
 ```bash
-alembic revision --autogenerate -m "<your_comment>"
+make generate-migrations migration_title="<name_for_migration>"
 ```
 
 To locally verify your changes, run:
 
 ```bash
-alembic upgrade head
+make run-migrations
 ```
 
 ## Using Docker

app/api/routers/file_upload.py

@@ -0,0 +1,40 @@
+from typing import Annotated
+from fastapi import APIRouter, UploadFile
+from app.categorization.file_processing import process_file, save_results
+from app.schema.index import FileUploadCreate
+import asyncio
+import os
+import csv
+
+file_upload_router = r = APIRouter(prefix="/api/v1/file_upload", tags=["file_upload"])
+
+@r.post(
+    "/",
+    responses={
+        200: {"description": "File successfully uploaded"},
+        400: {"description": "Bad request"},
+        500: {"description": "Internal server error"},
+    },
+)
+async def create_file(input_file: UploadFile):
+    try:
+         # Create directory to store all uploaded .csv files
+        file_upload_directory_path = "data/tx_data/input"
+        if not os.path.exists(file_upload_directory_path):
+            os.makedirs(file_upload_directory_path)
+
+        # Write items of .csv filte to directory
+        with open(os.path.join(file_upload_directory_path, input_file.filename)) as output_file:
+            [output_file.write(" ".join(row)+'\n') for row in csv.reader(input_file)]
+        output_file.close()
+
+        # With the newly created file and it's path, process and save it for embedding
+        processed_file = process_file(os.path.realpath(input_file.filename))
+        result = await asyncio.gather(processed_file)
+        save_results(result)
+
+    except Exception:
+        return {"message": "There was an error uploading this file. Ensure you have a .csv file with the following columns:"
+        "\n source, date, type, category, description, amount"}
+
+    return {"message": f"Successfully uploaded {input_file.filename}"}

app/api/routers/income_statement.py

@@ -0,0 +1,65 @@
+from typing import List
+from fastapi import APIRouter, Depends, HTTPException, status
+from sqlalchemy.ext.asyncio import AsyncSession
+from app.model.transaction import Transaction as TransactionModel
+from app.model.income_statement import IncomeStatement as IncomeStatementModel
+from app.schema.index import IncomeStatementCreate, IncomeStatementResponse
+from app.engine.postgresdb import get_db_session
+from app.service.income_statement import call_llm_to_create_income_statement
+
+income_statement_router = r = APIRouter(prefix="/api/v1/income_statement", tags=["income_statement"])
+
+
+@r.post(
+    "/",
+    responses={
+        200: {"description": "New transaction created"},
+        400: {"description": "Bad request"},
+        500: {"description": "Internal server error"},
+    },
+)
+async def create_income_statement(payload: IncomeStatementCreate, db: AsyncSession = Depends(get_db_session)) -> None:
+    try:
+        await call_llm_to_create_income_statement(payload, db)
+
+    except Exception as e:
+        raise HTTPException(status_code=500, detail=str(e))
+
+
+@r.get(
+    "/{user_id}",
+    response_model=List[IncomeStatementResponse],
+    responses={
+        200: {"description": "New user created"},
+        400: {"description": "Bad request"},
+        204: {"description": "No content"},
+        500: {"description": "Internal server error"},
+    },
+)
+async def get_income_statements(
+    user_id: int, db: AsyncSession = Depends(get_db_session)
+) -> List[IncomeStatementResponse]:
+    """
+    Retrieve all income statements.
+    """
+    result = await IncomeStatementModel.get_by_user(db, user_id)
+    all_rows = result.all()
+    if len(all_rows) == 0:
+        raise HTTPException(status_code=status.HTTP_204_NO_CONTENT, detail="No income statements found for this user")
+    return all_rows
+
+
+@r.get(
+    "/{report_id}",
+    response_model=IncomeStatementResponse,
+    responses={
+        200: {"description": "Income statement found"},
+        404: {"description": "Income statement not found"},
+        500: {"description": "Internal server error"},
+    },
+)
+async def get_income_statement(report_id: int, db: AsyncSession = Depends(get_db_session)) -> IncomeStatementResponse:
+    income_statement = await IncomeStatementModel.get(db, id=report_id)
+    if not income_statement:
+        raise HTTPException(status_code=404, detail="Income statement not found")
+    return income_statement

app/model/income_statement.py

@@ -0,0 +1,42 @@
+from datetime import datetime
+from typing import Any, Dict, List
+from sqlalchemy import ForeignKey
+from sqlalchemy.sql import expression as sql
+from sqlalchemy.orm import relationship, Mapped, mapped_column
+from sqlalchemy.ext.asyncio import AsyncSession
+from sqlalchemy.dialects.postgresql import JSON
+
+from app.model.base import BaseModel
+from app.engine.postgresdb import Base
+
+
+class IncomeStatement(Base, BaseModel):
+    __tablename__ = "income_statement"
+
+    date_from: Mapped[datetime]
+    date_to: Mapped[datetime]
+    income: Mapped[Dict] = mapped_column(JSON, nullable=True)
+    expenses: Mapped[Dict] = mapped_column(JSON, nullable=True)
+
+    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
+    user = relationship("User", back_populates="income_statements")
+
+    @classmethod
+    def create(cls: "type[IncomeStatement]", db: AsyncSession, **kwargs) -> "IncomeStatement":
+        query = sql.insert(cls).values(**kwargs)
+        income_statements = db.execute(query)
+        income_statement = income_statements.first()
+        db.commit()
+        return income_statement
+
+    @classmethod
+    async def get_by_user(cls: "type[IncomeStatement]", db: AsyncSession, user_id: int) -> "List[IncomeStatement]":
+        query = sql.select(cls).where(cls.user_id == user_id)
+        income_statements = await db.scalars(query)
+        return income_statements
+
+    @classmethod
+    async def get(cls: "type[IncomeStatement]", db: AsyncSession, id: int) -> "IncomeStatement":
+        query = sql.select(cls).where(cls.id == id)
+        income_statement = await db.scalar(query)
+        return income_statement

app/model/transaction.py

@@ -23,7 +23,7 @@ class Transaction(Base, BaseModel):
 
     @classmethod
     async def create(cls: "type[Transaction]", db: AsyncSession, **kwargs) -> "Transaction":
-        query = sql.insert(cls).values(**kwargs).returning(cls.id)
+        query = sql.insert(cls).values(**kwargs)
         transactions = await db.execute(query)
         transaction = transactions.first()
         await db.commit()
@@ -48,3 +48,11 @@ class Transaction(Base, BaseModel):
         query = sql.select(cls).where(cls.user_id == user_id)
         transactions = await db.scalars(query)
         return transactions
+
+    @classmethod
+    async def get_by_user_between_dates(
+        cls: "type[Transaction]", db: AsyncSession, user_id: int, start_date: datetime, end_date: datetime
+    ) -> "List[Transaction]":
+        query = sql.select(cls).where(cls.user_id == user_id).where(cls.transaction_date.between(start_date, end_date))
+        transactions = await db.scalars(query)
+        return transactions

app/model/user.py

@@ -19,6 +19,7 @@ class User(Base, BaseModel):
     is_deleted: Mapped[bool] = mapped_column(default=False)
 
     transactions = relationship("Transaction", back_populates="user")
+    income_statements = relationship("IncomeStatement", back_populates="user")
 
     @classmethod
     async def create(cls: "type[User]", db: AsyncSession, **kwargs: UserCreate) -> "User":

app/schema/index.py

@@ -51,3 +51,27 @@ class TransactionCreate(TransactionResponse):
 
 class Transaction(TransactionResponse):
     user: User
+
+
+<<<<<<< HEAD
+class FileUploadCreate(PydanticBaseModel):
+    source: str
+    date: datetime
+    category: str
+    name_description: str
+    amount: float
+    type: str
+=======
+class IncomeStatementCreate(PydanticBaseModel):
+    user_id: int
+    date_from: datetime
+    date_to: datetime
+
+
+class IncomeStatementResponse(PydanticBaseModel):
+    id: int
+    date_from: datetime
+    date_to: datetime
+    income: dict
+    expenses: dict
+>>>>>>> main

app/service/income_statement.py

@@ -0,0 +1,15 @@
+from app.schema.index import IncomeStatementCreate
+from app.model.transaction import Transaction as TransactionModel
+from app.model.income_statement import IncomeStatement as IncomeStatementModel
+from sqlalchemy.ext.asyncio import AsyncSession
+
+
+async def call_llm_to_create_income_statement(payload: IncomeStatementCreate, db: AsyncSession) -> None:
+    transactions = await TransactionModel.get_by_user_between_dates(
+        db, payload.user_id, payload.date_from, payload.date_to
+    )
+
+    # TODO: Call LLM to generate income and expenses
+    income = {}
+    expenses = {}
+    await IncomeStatementModel.create(db, **payload, income=income, expenses=expenses)

main.py

@@ -8,6 +8,11 @@ from fastapi.responses import RedirectResponse
 from app.api.routers.chat import chat_router
 from app.api.routers.user import user_router
 from app.api.routers.transaction import transaction_router
+<<<<<<< HEAD
+from app.api.routers.file_upload import file_upload_router
+=======
+from app.api.routers.income_statement import income_statement_router
+>>>>>>> main
 from app.settings import init_settings
 from fastapi.staticfiles import StaticFiles
 from alembic.config import Config
@@ -53,16 +58,23 @@ def init_app(init_db: bool = True) -> FastAPI:
     app.include_router(chat_router, prefix="/api/chat")
     app.include_router(user_router)
     app.include_router(transaction_router)
+<<<<<<< HEAD
+    app.include_router(file_upload_router)
+=======
+    app.include_router(income_statement_router)
+>>>>>>> main
 
     return app
 
 
 app = init_app()
 
-if __name__ == "__main__":
-    # Redirect to documentation page when accessing base URL
-    @app.get("/")
-    async def redirect_to_docs():
-        return RedirectResponse(url="/docs")
 
+# Redirect to documentation page when accessing base URL
+@app.get("/")
+async def redirect_to_docs():
+    return RedirectResponse(url="/docs")
+
+
+if __name__ == "__main__":
     uvicorn.run(app="main:app", host=env.APP_HOST, port=env.APP_PORT, reload=(env.ENVIRONMENT == "dev"))

migration/env.py

@@ -2,9 +2,9 @@ import asyncio
 from logging.config import fileConfig
 
 from app.engine.postgresdb import Base
-from sqlalchemy import engine_from_config, Connection
+from sqlalchemy import Connection
 from sqlalchemy import pool
-from sqlalchemy.ext.asyncio import AsyncEngine, async_engine_from_config
+from sqlalchemy.ext.asyncio import async_engine_from_config
 
 from config.index import config as env
 
@@ -22,8 +22,9 @@ if config.config_file_name is not None:
     fileConfig(config.config_file_name)
 
 # Import all models so they're registered with SQLAlchemy.
-import app.model.transaction
 import app.model.user
+import app.model.transaction
+import app.model.income_statement
 
 # add your model's MetaData object here
 # for 'autogenerate' support

migration/versions/4e76691ab103_add_income_statement_table.py

@@ -0,0 +1,43 @@
+"""add_income_statement_table
+
+Revision ID: 4e76691ab103
+Revises: 7ea44cbc5b1f
+Create Date: 2024-06-03 23:59:03.717778
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+from sqlalchemy.dialects import postgresql
+
+# revision identifiers, used by Alembic.
+revision: str = '4e76691ab103'
+down_revision: Union[str, None] = '7ea44cbc5b1f'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.create_table('income_statement',
+    sa.Column('income', postgresql.JSON(astext_type=sa.Text()), nullable=True),
+    sa.Column('expenses', postgresql.JSON(astext_type=sa.Text()), nullable=True),
+    sa.Column('date_from', sa.DateTime(), nullable=False),
+    sa.Column('date_to', sa.DateTime(), nullable=False),
+    sa.Column('user_id', sa.Integer(), nullable=False),
+    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
+    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
+    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
+    sa.PrimaryKeyConstraint('id')
+    )
+    op.create_index(op.f('ix_income_statement_id'), 'income_statement', ['id'], unique=False)
+    # ### end Alembic commands ###
+
+
+def downgrade() -> None:
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.drop_index(op.f('ix_income_statement_id'), table_name='income_statement')
+    op.drop_table('income_statement')
+    # ### end Alembic commands ###