import sqlite3
import sys
import logging

from sqlite3 import IntegrityError
from threading import Lock

logger = logging.getLogger("database")


class Database:
    def __init__(self, dbstr: str):
        # If true, sqlite connections can be shared across
        # threads, and so we can pass check_same_thread=False safely.
        assert self._sqlite_is_threadsafe()
        self.conn = sqlite3.connect(
            dbstr, check_same_thread=False, isolation_level=None
        )
        self.conn.row_factory = sqlite3.Row

        # Enforce foreign key constraints!
        self.conn.execute("PRAGMA foreign_keys = 1;")

        self.init_db()

    def _sqlite_is_threadsafe(self) -> bool:
        """
        Check that it is safe to use the Database from multiple threads at the
        same time. This should be as easy as checking `sqlite3.threadsafety`,
        but that value was historically hardcoded. The real value is available
        by querying the compile options from sqlite.
        """
        if sys.hexversion >= 0x030B00F0:
            return sqlite3.threadsafety == 3
        else:
            conn = sqlite3.connect(":memory:")
            opt = conn.execute(
                "SELECT * FROM pragma_compile_options WHERE compile_options LIKE 'THREADSAFE=%'"
            ).fetchone()
            return "THREADSAFE=1" in opt

    def init_db(self):
        """
        Set up the database, creating the requisite tables if they do not exist.
        """
        logger.info("Initialising database...")

        cur = self.conn.cursor()

        cur.execute("BEGIN")

        cur.execute(
            """CREATE TABLE IF NOT EXISTS users
                    (user_id TEXT PRIMARY KEY,
                     first_name TEXT,
                     last_name TEXT,
                     username TEXT
                    )"""
        )

        cur.execute(
            """CREATE TABLE IF NOT EXISTS groups
                    (name TEXT PRIMARY KEY)"""
        )

        cur.execute(
            """CREATE TABLE IF NOT EXISTS students
                    (student_id TEXT PRIMARY KEY,
                     group_name TEXT,
                     FOREIGN KEY(student_id) REFERENCES users(user_id)
                                             ON UPDATE CASCADE,
                     FOREIGN KEY(group_name) REFERENCES groups(name)
                                             ON UPDATE CASCADE
                    )"""
        )

        cur.execute(
            """CREATE TABLE IF NOT EXISTS tas
                    (id INTEGER PRIMARY KEY AUTOINCREMENT,
                     ta TEXT,
                     group_name TEXT,
                     FOREIGN KEY (ta) REFERENCES users(user_id)
                                      ON UPDATE CASCADE,
                     FOREIGN KEY (group_name) REFERENCES groups(name)
                                              ON UPDATE CASCADE
                    )"""
        )

        cur.execute(
            """CREATE TABLE IF NOT EXISTS auth_tokens
                    (id INTEGER PRIMARY KEY AUTOINCREMENT,
                     user TEXT,
                     token TEXT,
                     FOREIGN KEY(user) REFERENCES users(user_id)
                                       ON UPDATE CASCADE
                    )"""
        )

        cur.execute(
            """CREATE TABLE IF NOT EXISTS host_ranges
                    (id INTEGER PRIMARY KEY AUTOINCREMENT,
                     ip_range TEXT
                    )"""
        )

        cur.execute("COMMIT")

        logger.info("Database initialised.")

    def execute(self, *args, **kwargs):
        return self.conn.execute(*args, **kwargs)

    def commit(self):
        return self.conn.commit()
