Fixes + Use SQLAlchemy

2 files changed, 53 insertions, 41 deletions

diff --git a/code/cogs/newaccount.py b/code/cogs/newaccount.py
index 8e17cda..f3f5ac8 100644
--- a/code/cogs/newaccount.py
+++ b/code/cogs/newaccount.py
@@ -1,9 +1,11 @@
 import discord
 from discord import app_commands
-from discord.ext import commands
-import sqlite3
+from discord.ext import commands, tasks
 
+from utils.database import Session
 from utils.jellyfin_create import create_jellyfin_account
+from utils.jellyfin_delete import delete_accounts
+from utils.models import JellyfinAccounts
 from utils.config import (
     JELLYFIN_PUBLIC_URL,
     JELLYFIN_ENABLED,
@@ -15,19 +17,20 @@ class NewAccount(commands.Cog):
     def __init__(self, bot):
         self.bot = bot
 
+    def cog_load(self):
+        self.delete_accounts_loop.start()
+
     @app_commands.command()
     @app_commands.check(lambda inter: JELLYFIN_ENABLED)
     async def newaccount(self, interaction: discord.Interaction) -> None:
         """Create a new temporary Jellyfin account"""
         # Make sure the user doesn't already have an account
-        db = sqlite3.connect("data/cordarr.db")
-        cursor = db.cursor()
-        cursor.execute(
-            "SELECT * FROM jellyfin_accounts WHERE user_id = ?",
-            (interaction.user.id,),
-        )
-        account = cursor.fetchone()
-        db.close()
+        with Session() as session:
+            account = (
+                session.query(JellyfinAccounts)
+                .filter(JellyfinAccounts.user_id == interaction.user.id)
+                .first()
+            )
         # Account already allocated
         if account:
             embed = discord.Embed(
@@ -63,8 +66,8 @@ class NewAccount(commands.Cog):
                 title="Jellyfin Account Information",
                 description=(
                     # fmt: off
-                    "Here is your temporary account information.\n\n",
-                    f"**Server URL:** `[{JELLYFIN_PUBLIC_URL}]({JELLYFIN_PUBLIC_URL})`\n"
+                    "Here is your temporary account information.\n\n"
+                    f"**Server URL:** `{JELLYFIN_PUBLIC_URL}`\n"
                     f"**Username:** `{response[0]}`\n"
                     f"**Password:** `{response[1]}`\n\n"
                     "Your account will be automatically deleted in"
@@ -88,6 +91,10 @@ class NewAccount(commands.Cog):
                 embed=embed, ephemeral=True
             )
 
+    @tasks.loop(minutes=1)
+    async def delete_accounts_loop(self):
+        delete_accounts()
+
 
 async def setup(bot):
     await bot.add_cog(NewAccount(bot))
diff --git a/code/cogs/status.py b/code/cogs/status.py
index 3a64146..769fc8f 100644
--- a/code/cogs/status.py
+++ b/code/cogs/status.py
@@ -2,8 +2,9 @@ import discord
 from discord import app_commands
 from discord.ext import commands
 import requests
-import sqlite3
 
+from utils.models import Requests
+from utils.database import Session
 from utils.config import (
     RADARR_HOST_URL,
     RADARR_HEADERS,
@@ -22,15 +23,18 @@ class Status(commands.Cog):
         # Defer the response
         await interaction.response.defer(ephemeral=True)
 
-        db = sqlite3.connect("data/cordarr.db")
-        cursor = db.cursor()
-        cursor.execute(
-            "SELECT title, release_year, local_id, tmdbid, tvdbid FROM"
-            " requests WHERE user_id = ?",
-            (interaction.user.id,),
-        )
-        requested_content = cursor.fetchall()
-        db.close()
+        with Session() as session:
+            requested_content = (
+                session.query(
+                    Requests.title,
+                    Requests.release_year,
+                    Requests.local_id,
+                    Requests.tmdbid,
+                    Requests.tvdbid,
+                )
+                .filter(Requests.user_id == interaction.user.id)
+                .all()
+            )
 
         # No content requested
         if len(requested_content) == 0:
@@ -42,7 +46,7 @@ class Status(commands.Cog):
                 ),
                 color=0xD01B86,
             )
-            return await interaction.followup.send(embed=embed, ephemeral=True)
+            return await interaction.followup.send(embed=embed)
 
         # Create template embed
         embed = discord.Embed(
@@ -75,7 +79,7 @@ class Status(commands.Cog):
         embed.description += radarr_desc + sonarr_desc + non_queue_desc
 
         # Send the follow-up message
-        await interaction.edit_original_response(embed=embed, ephemeral=True)
+        await interaction.followup.send(embed=embed)
 
     def unpack_content(self, requested_content: list) -> tuple:
         """
@@ -92,18 +96,18 @@ class Status(commands.Cog):
         sonarr_content_info = {}
 
         for content in requested_content:
-            title, (release_year), local_id, tmdbid, tvdbid = content
+            title, release_year, local_id, tmdbid, tvdbid = content
             if tmdbid is not None:
-                radarr_content_info[int(local_id)] = {
+                radarr_content_info[local_id] = {
                     "title": title,
-                    "release_year": int(release_year),
-                    "tmdbid": int(tmdbid),
+                    "release_year": release_year,
+                    "tmdbid": tmdbid,
                 }
             else:
-                sonarr_content_info[int(local_id)] = {
+                sonarr_content_info[local_id] = {
                     "title": title,
-                    "release_year": int(release_year),
-                    "tvdbid": int(tvdbid),
+                    "release_year": release_year,
+                    "tvdbid": tvdbid,
                 }
 
         return radarr_content_info, sonarr_content_info
@@ -169,7 +173,7 @@ class Status(commands.Cog):
         for content in requested_content:
             title, release_year, local_id, tmdbid, _ = content
             # If not in queue
-            if int(local_id) not in added_ids:
+            if local_id not in added_ids:
                 # Pull the movie data from the service
                 if tmdbid is not None:
                     data = requests.get(
@@ -185,15 +189,16 @@ class Status(commands.Cog):
                 # If the movie has a file, then it has finished downloading
                 if data.get("hasFile", True):
                     # Remove from database
-                    db = sqlite3.connect("data/cordarr.db")
-                    cursor = db.cursor()
-                    cursor.execute(
-                        "DELETE FROM requests WHERE user_id = ? AND"
-                        " local_id = ?",
-                        (user_id, int(local_id)),
-                    )
-                    db.commit()
-                    db.close()
+                    with Session() as session:
+                        request = (
+                            session.query(Requests)
+                            .filter(Requests.user_id == user_id)
+                            .filter(Requests.local_id == local_id)
+                            .first()
+                        )
+                        session.delete(request)
+                        session.commit()
+
                 # If series and only a portion of episodes have been downloaded
                 if data.get("statistics").get("percentOfEpisodes"):
                     description += (