class database:
    '''Database Class, loads data from database.db and creates
    People objects.'''
    def __init__(self, mode):
        conn = sqlite3.connect("database.db")
        cursor = conn.cursor()
        if mode == True: #teacher
            self.mode = 'teachers'
            cursor.execute("""CREATE TABLE IF NOT EXISTS teachers (ID INTEGER PRIMARY KEY AUTOINCREMENT, FirstName varchar(30),
Surname varchar (30), Training boolean, Days INTEGER, Children varchar)""") #creates table
            conn.commit()
        else: #children
            self.mode = 'children'
            cursor.execute("""CREATE TABLE IF NOT EXISTS children (ID INTEGER PRIMARY KEY AUTOINCREMENT, FirstName varchar(30),
MiddleName varchar(30), Surname varchar (30), DateOfBirth date, requirements boolean, Days INTEGER)""") #creates table
            conn.commit()
        try:
            self.activelist = self.loadPeople()
        except:
            print(self.mode,"Active List not loaded")
            pass

    def printTable(self):
        '''Prints table for debug'''
        if self.mode:
            print("ID - Firstname - Surname - Trained - Days") #teacher
        else:
            print("ID - Firstname - Middlename - Surname - Date of Birth - Requirments - Days")
        conn = sqlite3.connect("database.db")
        cursor = conn.cursor()
        string = "SELECT * FROM " + self.mode
        cursor.execute(string)
        string = cursor.fetchall()
        for i in range(len(list(string))):
            print(string[i])
        conn.commit()
        return string

    def wipeTable(self):
        print('Are you sure you want to delete the data in')
        print('the table ',self.mode,'? This will be permanent.',sep='')
        answer = input('Y/N: ').lower()
        if answer == 'y' or answer == 'yes':
            conn = sqlite3.connect("database.db")
            cursor = conn.cursor()
            string = "DELETE FROM " + self.mode
            print(string)
            cursor.execute(string)
            cursor.execute("""VACUUM""")
            print("Data deleted")
            conn.commit()
        else:
            print("Data NOT deleted")

    def createAddString(self, person):
        '''Creates SQL String'''
        #print('mode =',self.mode)
        if self.mode == 'children':
            string = "INSERT INTO " + self.mode + " VALUES (NULL, '" + person.firstname + "', '" + person.middlename + "', '" + person.surname + "', '" + person.dob.strftime('%Y-%m-%d') + "', '" + str(person.requirements) + "', '" + str(person.days) + "')"
        else:
            try:
                int(person.days)
            except:
                print("why not")
            person.printInfo()
            string = "INSERT INTO " + self.mode + " VALUES (NULL, '" + person.firstname + "', '" + person.surname + "', '" + str(person.requirements)  + "', '" + str(person.days) + "', '" + str(person.Children).replace("'","") + "')"
        #print(string)
        return string

    def createQueryString(self, ID = None):
        '''Creates SQL String'''
        print('mode =',self.mode)
        #if self.mode == 'children':
            #string = "SELECT * FROM " + self.mode + " WHERE
        #else:
            #string = "SELECT
        return string

    def addPerson(self, person, string = None):
        '''Takes a Person class object (child/teacher) and stores them'''
        conn = sqlite3.connect("database.db")
        cursor = conn.cursor()
        if string == None:
            person.printInfo()
            int(person.days)
            string = self.createAddString(person)
        cursor.execute(string)
        conn.commit()

    def readPerson(self, ID = None):
        '''Reads data from the database and creates a Person class object'''
        conn = sqlite3.connect("database.db")
        cursor = conn.cursor()
        if ID != None:
            string = 'SELECT * FROM ' + self.mode + ' WHERE ID = "' + str(ID) +'"'
            #print('String: ',string)
            cursor.execute(string)
            result = cursor.fetchall()
        else:
            string = 'SELECT * FROM ' + self.mode
            cursor.execute(string)
            result = cursor.fetchall()
        conn.commit()
        for i in range(len(result)):
            result[i] = list(result[i])
        return result

    def loadPeople(self):
        '''Loads Person Classes from this database, returns a list of objects'''
        people = list()
        if self.mode == 'children':
            temp = self.readPerson()
            #print('len:',len(temp))
            for i in range(len(temp)):
                tempload = temp[i]
                #print(tempload)
                try:
                    tempchild = Child(tempload[1],tempload[2],tempload[3],int(tempload[4][0:4]),int(tempload[4][5:7]),int(tempload[4][8:10]),bool(tempload[5]),tempload[6],tempload[0])
                    people.append(tempchild)
                except:
                    print('no')
                    return temp, tempload

        else: #loadteachers
            temp = self.readPerson()
            #print('len:',len(temp))
            for i in range(len(temp)):
                tempload = temp[i]
                print(tempload)
                tempteacher = Teacher(tempload[1],tempload[2],bool(tempload[3]),tempload[0],tempload[4],tempload[5])
                people.append(tempteacher)
        return people

    def returnPerson(self, ID):
        '''Returns a person from activelist based on ID'''
        for i in range(len(self.activelist)):
            if self.activelist[i].ID == ID:
                return self.activelist[i]

    def printInfo(self):
        '''Debugging tool to print Person data'''
        print(vars(self))

    def listPeople(self):
        '''Prints a numbered list of people'''
        for i in range(len(self.activelist)):
            tp = self.activelist[i] #tp = tempperson
            print(i+1,". ID:",tp.ID," ",tp.firstname," ",tp.surname,sep = '')
        print()

    def deletePerson(self, ID):
        conn = sqlite3.connect("database.db")
        cursor = conn.cursor()
        string = "DELETE FROM " + self.mode + " WHERE ID = " + str(ID)
        print(string)
        cursor.execute(string)
        conn.commit()

    def editPerson(self, changing, person, choice):
        '''Creates SQL string + edits person'''
        l = ['FirstName','MiddleName','Surname','day','month','year','days','requirements','Days']
        print("changing:",person.ID,person.firstname)
        string = 'UPDATE ' + self.mode + " SET "
        if self.mode == 'children': #children
            if choice - 1 in [0,1,2,7,8]:
                string =+ l[choice-1]
        string += " WHERE ID=" + person.ID
        print("string")