import os
import sqlite3


class DbProvider:
    def database_path(self, relative):
        p = os.path.join(os.environ.get("_MEIPASS2", os.path.abspath(".")), relative)
        print(p)
        return p

    def get_data_from_table(self, table_name):
        #  Get headers of table
        header_request = '''SELECT name FROM PRAGMA_TABLE_INFO('{}')'''.format(table_name)
        self.cursor.execute(header_request)
        headers = self.cursor.fetchall()

        #  Get rows of table
        sql = '''SELECT * FROM {}'''.format(table_name)
        self.cursor.execute(sql)
        rows = self.cursor.fetchall()

        #  Create dict
        dataset = {}
        data = {}
        j = 0
        for row in rows:
            i = 0
            for item in row:
                data[str(headers[i][0])] = str(item)
                i += 1
            dataset[str(j)] = data.copy()
            j += 1
            data.clear()
        self.dataSet.clear()
        self.dataSet = dataset
        return dataset

    def __init__(self):
        self.path = self.database_path('DB\db.sqlite')
        self.dataSet = {}

        if not os.path.isfile(self.path):
            try:
                os.mkdir(self.database_path("DB"))
            except:
                pass
        # Create database if not exist and get a connection to it
        self.connection = sqlite3.connect(self.path)
        # Get a cursor to execute sql statements
        self.cursor = self.connection.cursor()

        # Create tables
        self.custom_face = self.CustomFace(self)
        self.face = self.Face(self)
        self.eyes = self.Eyes(self)
        self.nose = self.Nose(self)
        self.lips = self.Mouth(self)
        self.ears = self.Ears(self)
        self.hair = self.Hair(self)
        self.AttributeAssignment = self.AttributeAssignment(self)
        self.appearance = self.Appearance(self)

    class Face:
        def insert_into_table(self, skintype, size, path):
            sql = '''INSERT INTO Face (SkinType, Size, Path) VALUES ('{}','{}','{}');'''.format(skintype, size, path)
            self.cursor.execute(sql)
            self.connection.commit()

        def __init__(self, parent):
            self.connection = parent.connection
            self.cursor = parent.cursor
            sql = '''CREATE TABLE IF NOT EXISTS Face
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            SkinType VARCHAR(100),
            Size VARCHAR(20),
            Path VARCHAR(300)
            )'''
            self.cursor.execute(sql)
            self.connection.commit()

    class Eyes:
        def insert_into_table(self, skintype, L_ID, R_ID):
            sql = '''INSERT INTO Eyes (SkinType, L_ID, R_ID) VALUES ('{}',{},{});'''.format(skintype, L_ID, R_ID)
            self.cursor.execute(sql)
            self.connection.commit()

        def __init__(self, parent):
            self.connection = parent.connection
            self.cursor = parent.cursor
            sql = '''CREATE TABLE IF NOT EXISTS Eyes
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            SkinType VARCHAR(100),
            L_ID INT,
            R_ID INT,
            FOREIGN KEY(L_ID) references Eye(ID),
            FOREIGN KEY(R_ID) references Eye(ID),
            )'''
            self.cursor.execute(sql)
            self.connection.commit()

    class Eye:
        def insert_into_table(self, size, colour, skintype, side, path):
            sql = '''INSERT INTO Eye (Size, Colour, SkinType, Side, Path) VALUES ('{}',{},'{}','{}','{}');'''.format(
                size, colour, skintype, side, path)
            self.cursor.execute(sql)
            self.connection.commit()

        def __init__(self, parent):
            self.connection = parent.connection
            self.cursor = parent.cursor
            sql = '''CREATE TABLE IF NOT EXISTS Eye
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            Size VARCHAR(100),
            Colour VARCHAR(100),
            SkinType VARCHAR(100),
            Side VARCHAR(100),
            Path VARCHAR(300)
            )'''
            self.cursor.execute(sql)
            self.connection.commit()

    class Nose:
        def insert_into_table(self, skintype, size, path):
            sql = '''INSERT INTO Nose (SkinType,Size,Path) VALUES ('{}',{},'{}');'''.format(skintype, size, path)
            self.cursor.execute(sql)
            self.connection.commit()

        def __init__(self, parent):
            self.connection = parent.connection
            self.cursor = parent.cursor
            sql = '''CREATE TABLE IF NOT EXISTS Nose
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            SkinType VARCHAR(100),
            Size VARCHAR(100),
            Path VARCHAR(300)
            )'''
            self.cursor.execute(sql)
            self.connection.commit()

    class Mouth:
        def insert_into_table(self, skintype, size, colour, path):
            sql = '''INSERT INTO Lips (SkinType, Size, Colour, Path) VALUES ('{}',{},'{}','{}');'''.format(skintype,
                                                                                                           size,
                                                                                                           colour, path)
            self.cursor.execute(sql)
            self.connection.commit()

        def __init__(self, parent):
            self.connection = parent.connection
            self.cursor = parent.cursor
            sql = '''CREATE TABLE IF NOT EXISTS Mouth
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            SkinType VARCHAR(100),
            Size VARCHAR(100),
            Colour VARCHAR(100),
            Path VARCHAR(300)
            )'''
            self.cursor.execute(sql)
            self.connection.commit()

    class Ears:
        def insert_into_table(self, skintype, L_ID, R_ID):
            sql = '''INSERT INTO Ears (Shape, L_ID, R_ID) VALUES ('{}',{},{});'''.format(skintype, L_ID, R_ID)
            self.cursor.execute(sql)
            self.connection.commit()

        def __init__(self, parent):
            self.connection = parent.connection
            self.cursor = parent.cursor
            sql = '''CREATE TABLE IF NOT EXISTS Ears
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            SkinType VARCHAR(100),
            L_ID INT,
            R_ID INT,
            FOREIGN KEY(L_ID) references Eye(ID),
            FOREIGN KEY(R_ID) references Eye(ID),
            )'''
            self.cursor.execute(sql)
            self.connection.commit()

    class Ear:
        def insert_into_table(self, size, skintype, side, path):
            sql = '''INSERT INTO Ear (Size, SkinType, Side, Path) VALUES ('{}',{},'{}','{}');'''.format(size, skintype, side,
                                                                                                 path)
            self.cursor.execute(sql)
            self.connection.commit()

        def __init__(self, parent):
            self.connection = parent.connection
            self.cursor = parent.cursor
            sql = '''CREATE TABLE IF NOT EXISTS Ear
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            SkinType VARCHAR(100),
            Side VARCHAR(100),
            Path VARCHAR(300)
            )'''
            self.cursor.execute(sql)
            self.connection.commit()

    class Hair:
        def insert_into_table(self, hairtype, colour, skintype, path):
            sql = '''INSERT INTO Hair (HairType, Colour, SkinType, Path) VALUES ('{}','{}','{}','{}');'''.format(hairtype, colour, skintype, path)
            self.cursor.execute(sql)
            self.connection.commit()

        def __init__(self, parent):
            self.connection = parent.connection
            self.cursor = parent.cursor
            sql = '''CREATE TABLE IF NOT EXISTS Hair
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            HairType VARCHAR(100),
            Colour VARCHAR(100),
            SkinType VARCHAR(100),
            Path VARCHAR(300)
            )'''
            self.cursor.execute(sql)
            self.connection.commit()

    class AttributeAssignment:  # AttributeAssignment
        def insert_into_table(self, faceID, eyesID, eyebrowsID, noseID, mouthID, earsID, hairID):
            sql = '''INSERT INTO AttributeAssignment (FaceID, EyesID, NoseID, MouthID, EarsID, HairID)
            VALUES ({},{},{},{},{},{});'''.format(faceID, eyesID, noseID, mouthID, earsID, hairID)
            self.cursor.execute(sql)
            self.connection.commit()

        def __init__(self, parent):
            self.connection = parent.connection
            self.cursor = parent.cursor
            sql = '''CREATE TABLE IF NOT EXISTS AttributeAssignment
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            FaceID INTEGER,
            EyesID INTEGER,
            NoseID INTEGER,
            MouthID INTEGER,
            EarsID INTEGER,
            HairID INTEGER,
            FOREIGN KEY(FaceID) references Face(ID),
            FOREIGN KEY(EyesID) references Eyes(ID),
            FOREIGN KEY(NoseID) references Nose(ID),
            FOREIGN KEY(MouthID) references Mouth(ID),
            FOREIGN KEY(EarsID) references Ears(ID),
            FOREIGN KEY(HairID) references Hair(ID)
            )'''
            self.cursor.execute(sql)
            self.connection.commit()

    class Appearance:  # Appearance
        def insert_into_table(self, assignmentID, skintype, filePath):
            sql = '''INSERT INTO Appearance (AssignmentID, SkinType, Date, Path) VALUES ({},'{}',DATETIME('now','localtime'),'{}');'''.format(
                assignmentID, skintype, filePath)
            self.cursor.execute(sql)
            self.connection.commit()

        def __init__(self, parent):
            self.connection = parent.connection
            self.cursor = parent.cursor
            sql = '''CREATE TABLE IF NOT EXISTS Appearance
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            AssignmentID INTEGER,
            SkinType VARCHAR(100),
            Date VARCHAR(100)
            Path varchar(300),
            FOREIGN KEY(AssignmentID) references AttributeAssignment(ID)
            )'''
            self.cursor.execute(sql)
            self.connection.commit()

    class CustomFace:
        def insert_into_table(self, name, genetic, path):
            sql = '''INSERT INTO CustomFace (Name, Genetic, Path) VALUES ('{}', '{}','{}');'''.format(name, genetic,
                                                                                                      path)
            self.cursor.execute(sql)
            self.connection.commit()

        def __init__(self, parent):
            self.connection = parent.connection
            self.cursor = parent.cursor
            sql = '''CREATE TABLE IF NOT EXISTS CustomFace
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            Name varchar(100),
            Genetic varchar(100),
            Path varchar(100)
            )'''
            self.cursor.execute(sql)
            self.connection.commit()


if __name__ == "__main__":
    db = DbProvider()
    # db.custom_face.insert_into_table("Face1","Europe","DatabaseBackend/Files/Faces/Face-01/face-1.jpg")
    # db.custom_face.insert_into_table("Face2","Africa","DatabaseBackend/Files/Faces/Face-02/face-2.jpg")
    # db.custom_face.insert_into_table("Face3", "Europe", "DatabaseBackend/Files/Faces/Face-03/face-3.jpg")
    # db.custom_face.insert_into_table("Face4", "Asia", "DatabaseBackend/Files/Faces/face-5.jpg")
    db.get_data_from_table("CustomFace")
    # db.insert_into_table('Face', 'tempName')
    # db.get_data_from_table('Face')