improvement/ explicit transaction management

[babakjahan]

to have explicit transaction management with commits in repositories, Updated database_session_manager.py

@contextlib.asynccontextmanager
async def session(self) -> AsyncIterator[AsyncSession]:
    if self._sessionmaker is None:
        raise Exception("DatabaseSessionManager is not initialized")

    session = self._sessionmaker()
    try:
        yield session
        # Check if there are any uncommitted changes
        if session.in_transaction():
            await session.commit()  # Commit any remaining uncommitted work
    except Exception:
        await session.rollback()
        raise
    finally:
        await session.close()