import sqlite3
c=sqlite3.connect("CAT Reloaded Web database.db")
cr=c.cursor()
with c:
    cr=c.cursor()
    cr.execute('''DROP TABLE IF EXISTS members''')
    cr.execute('''CREATE TABLE members ( mID INTEGER AUTO INCREMENT ,f_names varchar(255)  ,c_Id INTEGER AUTO INCREMENT, l INTEGER AUTO INCREMENT,FOREIGN KEY(c_Id) REFERENCES circles(circle_ID), FOREIGN KEY (l) REFERENCES levels (z) ) ''')
    members = ['Ahmed Akef', 'Mosaab ehab','Abd elsalam','Omar elezaby','Nouran Ali','Ahmed Maher']
    def add_to_table1():
        for i in range (6) :
            cr.execute("INSERT INTO members (mID,c_Id,l,f_names) VALUES (?,?,?,?)",(i+1,i+1,i+1,members[i]))
            
    add_to_table1()

with c:
    cr=c.cursor()
    circ= ['web','mobile','gaming','data']
    cr.execute('''
DROP TABLE IF EXISTS circles''')
    cr.execute('''CREATE TABLE circles ( circle_ID INTEGER PRIMARY KEY,circle varchar(255) NOT NULL )''')
    def add_to_table2():
        for i in range (5): 
            cr.execute("INSERT INTO circles (circle_ID,circle) VALUES (?,?)",(i+1,circ[0]))
    add_to_table2()

with c:
    cr=c.cursor()
    cr.execute('''DROP TABLE IF EXISTS levels''')

    descrp= ['advanced/CAT CORDINATOR','advanced','beginner','beginner','beginner','Mentor']
    #add the level and some notes
    cr.execute('''CREATE TABLE levels ( z INTEGER  PRIMARY KEY,lvl varchar(255) NOT NULL) ''')
    def add_to_table3():
        for i in range (6): 
            cr.execute("INSERT INTO levels (z,lvl) VALUES (?,?)",(i+1,descrp[i]))
    add_to_table3()
    

def select_all_rows(c):
    cr=c.cursor()
    cr.execute ('''\
     SELECT mID,f_names,circles.circle,levels.lvl
     FROM members
     INNER JOIN circles ON members.c_Id = circles.circle_ID 
     INNER JOIN levels ON members.l = levels.z ''')

    rows= cr.fetchall()
    for row in rows:
        
        print (row)
        
select_all_rows(c)        
c.commit
c.close()