Nessa/nessa/database.py

import aiosqlite

DATABASE = "nessa.db"


async def init_db():
    async with aiosqlite.connect(DATABASE) as db:
        await db.execute("PRAGMA foreign_keys = ON")
        await db.execute(
            """
            CREATE TABLE IF NOT EXISTS projects(
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT UNIQUE,
                description TEXT)
            """
        )
        await db.execute(
            """
            CREATE TABLE IF NOT EXISTS tasks(
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER,
                description TEXT,
                assignee TEXT,
                deadline TEXT,
                status TEXT,
                priority TEXT,
                notification_channel_id TEXT,
                reminder_time DATETIME,
                reminder_sent BOOLEAN DEFAULT FALSE,
                FOREIGN KEY(project_id) REFERENCES projects(id))
            """
        )
        await db.execute(
            """
            CREATE TABLE IF NOT EXISTS user_consents(
                user_id INTEGER PRIMARY KEY,
                consent_given BOOLEAN NOT NULL)
            """
        )
        await db.commit()


async def add_project(name, description):
    async with aiosqlite.connect(DATABASE) as db:
        cursor = await db.execute(
            "INSERT INTO projects(name, description)" "VALUES(?, ?)",
            (name, description),
        )
        await db.commit()
        return cursor.lastrowid  # Returns the ID of the newly created project


async def get_project_id(name):
    async with aiosqlite.connect(DATABASE) as db:
        cursor = await db.execute("SELECT id FROM projects WHERE name = ?", (name,))
        result = await cursor.fetchone()
        return result[0] if result else None


async def get_project_name(project_id):
    async with aiosqlite.connect(DATABASE) as db:
        cursor = await db.execute(
            "SELECT name FROM projects WHERE id = ?", (project_id,)
        )
        result = await cursor.fetchone()
        return result[0] if result else None


async def add_task_to_project(
    project_id,
    description,
    assignee,
    deadline,
    status,
    priority,
    notification_channel_id=None,
    reminder_time=None,
):
    async with aiosqlite.connect(DATABASE) as db:
        await db.execute(
            "INSERT INTO tasks (project_id, description, assignee, deadline, "
            "status, priority, notification_channel_id, reminder_time) VALUES "
            "(?, ?, ?, ?, ?, ?, ?, ?)",
            (
                project_id,
                description,
                assignee,
                deadline,
                status,
                priority,
                notification_channel_id,
                reminder_time,
            ),
        )
        await db.commit()


async def update_task(
    task_id,
    description,
    assignee,
    deadline,
    status,
    priority,
    notification_channel_id=None,
    reminder_time=None,
):
    async with aiosqlite.connect(DATABASE) as db:
        await db.execute(
            "UPDATE tasks SET description=?, assignee=?, deadline=?, status=?,"
            "priority=?, notification_channel_id=?,reminder_time=? WHERE id=?",
            (
                description,
                assignee,
                deadline,
                status,
                priority,
                notification_channel_id,
                reminder_time,
                task_id,
            ),
        )
        await db.commit()


async def list_projects():
    async with aiosqlite.connect(DATABASE) as db:
        cursor = await db.execute("SELECT id, name, description FROM projects")
        projects = await cursor.fetchall()
        return projects


async def list_tasks_for_project(project_id):
    async with aiosqlite.connect(DATABASE) as db:
        cursor = await db.execute(
            "SELECT id, description, assignee, deadline,"
            "status, priority FROM tasks "
            "WHERE project_id = ?",
            (project_id,),
        )
        tasks = await cursor.fetchall()
        return tasks


async def remove_task(task_id):
    async with aiosqlite.connect(DATABASE) as db:
        await db.execute("DELETE FROM tasks WHERE id = ?", (task_id,))
        await db.commit()


async def remove_project(project_id):
    async with aiosqlite.connect(DATABASE) as db:
        await db.execute(
            "DELETE FROM tasks WHERE project_id = ?", (project_id,)
        )  # Remove all tasks under the project
        await db.execute("DELETE FROM projects WHERE id = ?", (project_id,))
        await db.commit()