"""
 school.py
 An example of using python3 to interact with sqlite3.

    First create the database : 
      $ sqlite3 academics.db < create.sql
    Then populate it with data :
      $ sqlite3 academics.db < populate.sql
    Then run this example :
      $ python school.py

        -- people --
         name                          age
         ------------------------    -----
         John Smith                     21
         Jane Doe                       18
         Inigo Montoya                  33
         Zaphod Beeblebrox             401
         Frodo Baggins                 129
        -- courses --
         Counting on your Fingers
         Physics for Poets
         Advanced Whirled Peas
        Pick a person : Jane Doe
          "Physics for Poets       " as student 
          "Counting on your Fingers" as student 
          "Counting on your Fingers" as student 
        Change that person's name to : Jane Q. Doe
        -- people are now --
         name                          age
         ------------------------    -----
         John Smith                     21
         Jane Q. Doe                    18
         Inigo Montoya                  33
         Zaphod Beeblebrox             401
         Frodo Baggins                 129

  Note :
    Do NOT use python string manipulations to insert data into a SQL statement;
    see https://docs.python.org/3.7/library/sqlite3.html

 Jim Mahoney | cs.marlboro.college | March 2020 | MIT License
"""
import sqlite3

def main():
    db = sqlite3.connect('academics.db')
    cursor = db.cursor()
    
    cursor.execute('SELECT name, age from People;')
    print('-- people --');
    print(f' {"name":24}    {"age":>5}')
    print(f' {"-"*24:24}    {"-"*5:>5}')
    for (name, age) in cursor.fetchall():
        print(f' {name:24}    {age:>5}')

    cursor.execute('SELECT name from Courses;')
    print('-- courses --')
    for (name,) in cursor.fetchall():
        print(f' {name}')

    who = input('Pick a person : ').strip()
    cursor.execute("""SELECT Courses.name, Roles.name from Registrations as reg
                      INNER JOIN Courses on reg.course = Courses.id
                      INNER JOIN People on reg.person = People.id
                      INNER JOIN Roles on reg.role = Roles.id
                      WHERE People.name = ?;""", (who,))
    for (course, role) in cursor.fetchall():
        print(f'  "{course:24}" as {role} ')

    newname = input("Change that person's name to : ").strip()
    # POSSIBLE OOPS : name may not be unique ...
    cursor.execute('UPDATE People SET name = ? where name = ?', (newname, who))
    db.commit()   # <=== end transaction & save changes to file

    cursor.execute('SELECT name, age from People;')
    print('-- people are now --');
    print(f' {"name":24}    {"age":>5}')
    print(f' {"-"*24:24}    {"-"*5:>5}')
    for (name, age) in cursor.fetchall():
        print(f' {name:24}    {age:>5}')


        
main()