import sqlite3

conn = sqlite3.connect('dbname.db')
c = conn.cursor()

def createTable():

    c.execute("""CREATE TABLE IF NOT EXISTS persons(
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    firstname TEXT,
                    lastname TEXT
                    )
    """)

    c.execute("""CREATE TABLE IF NOT EXISTS scores(
                    idperson INTEGER,
                    task INTEGER,
                    points INTEGER,
                    FOREIGN KEY(idperson) REFERENCES persons(id) ON DELETE CASCADE
                    )
    """)



#c.execute('drop table persons')
#c.execute('drop table scores')


def insertPersons():

    myDict = {}
    with open('score2.txt', 'r') as file:

        for line in file:
            textSplit  = line.split()

            firstName = textSplit[2]
            lastName = textSplit[3]
            number = textSplit[4]

            if firstName + lastName in myDict:
                myDict[firstName + lastName] += int(number)

            else:
                myDict[firstName + lastName] = int(number)

                c.execute("INSERT INTO persons (firstname, lastname) VALUES (?,?) ",
                (firstName, lastName))

            conn.commit()


def insertScores():

    with open('score2.txt', 'r') as file:

        for row in file:
            textSplit = row.split()

            assignment = int(textSplit[1])
            firstName = textSplit[2]
            lastName = textSplit[3]
            number = int(textSplit[4])

            c.execute("SELECT ID FROM persons where firstname=? AND lastname=?", (firstName, lastName))

            for data in c.fetchall():
                c.execute("INSERT INTO scores (idperson, task, points) VALUES (?,?,?) ",
                (int(data[0]), assignment, number))

            conn.commit()






createTable()

insertPersons()
insertScores()

conn.close()