# =============#
#    imports   #
# =============#
import sqlite3 #for working with sqlite version 3 databases

# ===============#
#    constants   #
# ===============#
SQL_FILE = "assignment_6.01-JonathanMaldonado.db"
SQL_CREATE_TABLE = """
CREATE TABLE person
(
  person_id INTEGER PRIMARY KEY,
  first_name TEXT,
  middle_name TEXT,
  age TEXT,
  favorite_thing TEXT
)"""
SQL_DELETE_ASSIGNMENT = """
DELETE FROM person
WHERE person_id = ?
"""
SQL_INSERT_ASSIGNMENT = """
INSERT INTO person
  (first_name,middle_name,age,favorite_thing)
VALUES
  (?,?,?,?)
"""
SQL_SELECT_ASSIGNMENTS = """
SELECT
  person_id,
  first_name,
  middle_name,
  age,
  favorite_thing
FROM person
"""
SQL_UPDATE_ASSIGNMENT = """
UPDATE person
SET age = ?
WHERE person_id = ?
"""
# ===============#
#    functions   #
# ===============#
def createPersonTable(dbConnection):
  """ creates an assignment database table """

  #fill in the function to create the table
  #the SQL statement is provided as a constant
  dbCursor = dbConnection.cursor()
  dbCursor.execute(SQL_CREATE_TABLE)
  dbCursor.close()

def deleteAssignmentByID(dbConnection,AGE):
  """ deletes assignment records by their ID """

  #fill in the function to delete a record from the assignment table
  #the SQL statement is provided as a constant
  #be sure to parameterize as needed and check that 1 record was deleted
  dbCursor = dbConnection.cursor()
  arguments = [
      AGE,
  ]
  dbCursor.execute(SQL_DELETE_ASSIGNMENT,arguments)

  #make sure the record was deleted successfully
  numberOfRecordsInserted = dbCursor.rowcount
  if(numberOfRecordsInserted != 1):
    errorMessage = "{} records were inserted when there should have been 1"
    raise RuntimeError(errorMessage.format(numberOfRecordsInserted))

  #close the cursor
  dbCursor.close()

def insertAssignment(dbConnection,person):
  """ inserts assignment records one at a time """

  #fill in the function to insert a record
  #the SQL statement is provided as a constant
  #be sure to parameterize as needed and check that 1 record was inserted
  dbCursor = dbConnection.cursor()
  arguments = [
      person["first_name"],
      person["middle_name"],
      person["age"],
      person["favorite_thing"],
  ]
  dbCursor.execute(SQL_INSERT_ASSIGNMENT,arguments)

  #make sure the record was inserted successfully
  numberOfRecordsInserted = dbCursor.rowcount
  if(numberOfRecordsInserted != 1):
    errorMessage = "{} records were inserted when there should have been 1"
    raise RuntimeError(errorMessage.format(numberOfRecordsInserted))

  #close the cursor
  dbCursor.close()


def selectAllAssignments(dbConnection):
  """ returns a list of all assignment records """

  #fill in the function to return a list of assignment records
  #the SQL statement is provided as a constant
  dbCursor = dbConnection.cursor()
  dbCursor.execute(SQL_SELECT_ASSIGNMENTS)

  #build list of assignment records
  persons = []
  for record in dbCursor:
    person = {
        "person_id": record[0],
        " first_name": record[1],
        "middle_name": record[2],
        "age": record[3],
        "favorite_thing": record[4]

    }
    persons.append(person)

  #close the cursor and return the list
  dbCursor.close()
  return persons

# ==========#
#    main   #
# ==========#
def main():
  """ main method """

  #connect to the database using the file name provided as a constant
  dbConnection = sqlite3.connect(SQL_FILE)

  #dropping table in case you need to re-run this multiple times
  #no need to change this part
  dbCursor = dbConnection.cursor()
  try:
    dbCursor.execute("DROP TABLE IF EXISTS person")
  except Exception as e:
    dbConnection.close()
    raise #stop the rest of the script


  #create the assignment table
  #use the createAssignmentTable function and wrap in an appropriate try block
  try:
    createPersonTable(dbConnection)
  except Exception as e:
    #close the connection and stop
    dbConnection.close()
    raise #stop the rest of the script


  #loop through the following assignment records and insert them
  #use the insertAssignment function and wrap in an appropriate try block
  persons = [
      {
          "first_name": "Demi",
          "middle_name": "Rose",
          "age": "22",
          "favorite_thing": "Cute and funny",

      },
      {
          "first_name": "Esmeralda",
          "middle_name": "Arciga",
          "age": "48",
          "favorite_thing": "Lovly Mother",
      },
      {
          "first_name": "Dead",
          "middle_name": "Pool",
          "age": "32",
          "favorite_thing": "Superhero",
      }
  ]
  for person in persons:
    try:
      insertAssignment(dbConnection,person)
    except Exception as e:
      #roll back the transaction and stop
      dbConnection.rollback()
      dbConnection.close()
      raise #stop the rest of the script
    else:
      #commit the transaction
      dbConnection.commit()

  #select all of the assignment records and store in a variable
  #use the selectAllAssignments function and wrap in an appropriate try block
  try:
    assignmentRecords = selectAllAssignments(dbConnection)
  except Exception as e:
    #roll back the transaction and stop
    dbConnection.rollback()
    dbConnection.close()
    raise #stop the rest of the script
  else:
    pass #no need to commit since it was just a select

  #loop through the assignment records
  #print the title and due date of each and then delete that record
  #use the deleteAssignmentByID function and wrap in an appropriate try block
  for person in assignmentRecords:
    try:
      print("{} {} is {} old and {}".format(person["first_name"],person["middle_name"],person["age"],person["favorite_thing"]))
      deleteAssignmentByID(dbConnection,person["person_id"])
    except Exception as e:
      #roll back the transaction and stop
      dbConnection.rollback()
      dbConnection.close()
      raise #stop the rest of the script
    else:
      #commit the transaction
      dbConnection.commit()

  #close the database connection
  dbConnection.close()

# kick off main
if(__name__ == "__main__"):
  main()