import os
import sqlite3
from collections import namedtuple

DEFAULT_DB_PATH = os.path.expandvars("$USERPROFILE/wizart_desktop_workspace.db")

Workspace = namedtuple('Workspace', ['id', 'title', 'path', 'created'])


class OpenCursor:
    def __init__(self, connection):
        self.connection = connection

    def __enter__(self):
        self.cursor = self.connection.cursor()
        return self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.cursor.close()


def normalize_path(path):
    return path.lower().replace("\\", '/').rstrip('/')


class DBManager:
    def __init__(self, path=DEFAULT_DB_PATH):
        self._db_path = path
        self.connection = None
        self.create_or_connect()

    def create_or_connect(self):
        self.connection = sqlite3.connect(self._db_path)
        with OpenCursor(self.connection) as cursor:
            cursor.execute("""CREATE TABLE IF NOT EXISTS workspaces(
                                  title TEXT,
                                  path TEXT NOT NULL UNIQUE,
                                  created TEXT -- date it was created 
                                  )""")
            self.connection.commit()

            # for the future development
            # cursor.execute("""CREATE TABLE IF NOT EXISTS files(
            #                       path TEXT NOT NULL, -- relative path in workspace
            #                       workspace_id INTEGER NOT NULL,
            #                       FOREIGN KEY(workspace_id) REFERENCES workspaces(rowid),
            #                       UNIQUE(path, workspace_id))""")
            # self.connection.commit()

    def get_workspaces(self):
        with OpenCursor(self.connection) as cursor:
            cursor.execute("""SELECT rowid, title, path, created FROM workspaces
                              ORDER BY created""")
            result = []
            for workspace in cursor.fetchall():
                result.append(Workspace(*workspace))
        return result

    def get_workspace_by_id(self, id):
        with OpenCursor(self.connection) as cursor:
            cursor.execute("""SELECT rowid, title, path, created FROM workspaces WHERE rowid = ?""", (id,))
            result = cursor.fetchall()

        if result:
            return Workspace(*(result[0]))

    def add_workspace(self, path, title=None):
        with OpenCursor(self.connection) as cursor:
            try:
                cursor.execute("""INSERT INTO workspaces VALUES(?, ?, datetime('now'))""",
                               (title, normalize_path(path)))
                self.connection.commit()
            except sqlite3.IntegrityError:
                print('Workspace with path {} already registered'.format(path))

    def delete_workspace(self, id):
        with OpenCursor(self.connection) as cursor:
            cursor.execute("""DELETE FROM workspaces WHERE rowid = ?""", (id,))
            self.connection.commit()

    def find_workspaces_by_title(self, title):
        with OpenCursor(self.connection) as cursor:
            cursor.execute("""SELECT rowid FROM workspaces WHERE title = ?""", (title,))
            result = cursor.fetchall()
            if result:
                return result[0][0]

    def find_workspace_by_path(self, path):
        with OpenCursor(self.connection) as cursor:
            cursor.execute("""SELECT rowid FROM workspaces WHERE path = ?""", (normalize_path(path),))
            result = cursor.fetchall()
            if result:
                return result[0][0]


if __name__ == '__main__':
    # test DBManager
    manager = DBManager("workspace.db")
    manager.add_workspace('first', 'd:/test_examples/test_workspaces/1')
    manager.add_workspace('second', 'd:/test_examples/test_workspaces/2')
    manager.add_workspace('third', 'd:/test_examples/test_workspaces/3')
    print(manager.get_workspaces())
    print(manager.get_workspace_by_id('1'))
    print(manager.get_workspace_by_id('4'))
    manager.delete_workspace(2)
    print(manager.get_workspaces())
    print(manager.get_workspace_by_id(4))
    print(manager.find_workspaces_by_title('first'))
    print(manager.find_workspace_by_path('d:/test_examples/test_workspaces/1'))
