from dataclasses import dataclass
from pathlib import Path

import sqlite3
import datetime

THIS_FOLDER = Path(__file__).parent.resolve()

@dataclass
class Database:
    def __enter__(self):
        self.__connection = sqlite3.connect(f"{THIS_FOLDER}/website.db")
        return self

    def __exit__(self, type, value, traceback):
        self.__connection.commit()
        self.__connection.close()

    def get_header_links(self):
        cursor = self.__connection.cursor()
        cursor.execute("SELECT name, link FROM headerLinks;")
        return cursor.fetchall()

    def get_image(self, imageName):
        cursor = self.__connection.cursor()
        cursor.execute("SELECT alt, url FROM images WHERE imageName = %s;",
                       (imageName, ))
        return cursor.fetchone()

    def get_pfp_images(self):
        cursor = self.__connection.cursor()
        cursor.execute("SELECT alt, url FROM images WHERE pfp_img = 1;")
        return cursor.fetchall()

    def get_sidebar_images(self):
        cursor = self.__connection.cursor()
        cursor.execute("SELECT alt, url FROM images WHERE sidebar_img = 1;")
        return cursor.fetchall()

    def get_all_categories(self):
        cursor = self.__connection.cursor()
        cursor.execute("SELECT name FROM categories;")
        return [i[0] for i in cursor.fetchall()]

    def add_category(self, category):
        if not category in self.get_all_categories():
            cursor = self.__connection.cursor()
            cursor.execute("INSERT INTO categories (name) VALUES (?);", (category, ))

            return True
        return False

    def get_all_thoughts(self):
        cursor = self.__connection.cursor()
        cursor.execute("""
        SELECT thoughts.id, title, datetime, categories.name
        FROM thoughts INNER JOIN categories
        ON thoughts.category_id = categories.id""")
        return cursor.fetchall()

    def get_thought(self, id_):
        cursor = self.__connection.cursor()
        cursor.execute("""
        SELECT title, datetime, markdown_text, redirect, name
        FROM thoughts INNER JOIN categories
        ON thoughts.category_id = categories.id
        WHERE thoughts.id = ?;""", (id_, ))
        return cursor.fetchone()

    def add_thought(self, title, markdown, category):
        cursor = self.__connection.cursor()
        cursor.execute("""
        INSERT INTO thoughts (category_id, title, markdown_text, datetime)
        VALUES ((
            SELECT id FROM categories WHERE name = ?
        ), ?, ?, datetime('now'));""", (category, title, markdown))

    def update_thought_markdown(self, id_, markdown):
        cursor = self.__connection.cursor()
        cursor.execute("UPDATE thoughts SET markdown_text = ? WHERE id = ?;",
                       (markdown, id_))