#!/usr/bin/python3
# COMP3311 20T3 Ass3 ... get Name's biography/filmography

import sys
import psycopg2
from helpers import *
#from helpers import whatever, functions, you, need

# define any local helper functions here

# set up some globals

usage = "Usage: bio 'NamePattern' [Year]"
db = None

# process command-line args

argc = len(sys.argv)
if(argc < 2 or argc > 3):
  print(usage)
  exit(1)
if(argc == 3 and not sys.argv[2].isdigit()):
    print(usage)
    exit(1)
pattern = sys.argv[1]
# manipulate database

try:
  db = psycopg2.connect("dbname=imdb")
  cur = db.cursor()
  try:
    if(argc == 2):
      query = """
              select name, birth_year, death_year, id from Names
              where name ~* %s order by name, birth_year, id
              """
      cur.execute(query, [pattern])
    else:
      year = sys.argv[2]
      query = """
              select name, birth_year, death_year, id from Names
              where name ~* %s and birth_year = %s order by name, birth_year, id
              """
      cur.execute(query, [pattern, year])
    result = cur.fetchall()
    size = len(result)
    
    if(size == 0):
      print("No name matching " + "\'" + pattern + "\'")
    elif(size != 1):
      print("Names matching " + "\'" + pattern + "\'")
      print("===============")
      for tup in result:
        if(tup[1] == None):
          print(tup[0] + " (???)")
        elif(tup[2] == None):
          print(tup[0] + " (" + str(tup[1]) + "-)")
        else:
          print(tup[0] + " (" + str(tup[1]) + "-" + str(tup[2]) + ")")
    else:
      nid = None
      for tup in result:
        if(tup[1] == None):
          print("Filmography for " + tup[0] + " (???)")
        elif(tup[2] == None):
          print("Filmography for " + tup[0] + " (" + str(tup[1]) + "-)")
        else:
          print("Filmography for " + tup[0] + " (" + str(tup[1]) + "-" + str(tup[2]) + ")")
        nid = tup[3]
      print("===============")
      query = f"""
              select M.title, M.start_year, M.id from Movies M
              join Principals P on M.id = P.movie_id
              where P.name_id = {nid}
              order by M.start_year, M.title
              """
      cur.execute(query)
      for tup in cur.fetchall():
        print(tup[0] + " " + getYear(tup[1]))
        cur.execute(f"""
                    select N.name, A.played from Movies M 
                    join Acting_roles A on (M.id = A.movie_id) 
                    join Principals P on (M.id = P.movie_id and A.name_id = P.name_id) 
                    join  Names N on (N.id = A.name_id) 
                    where M.id = {tup[2]} and N.id = {nid} 
                    order by P.ordering, A.played
                    """)
        for tup2 in cur.fetchall():
          print(" playing " + tup2[1])
        cur.execute(f"""
                    select N.name, C.role from Movies M 
                    join Crew_roles C on (M.id = C.movie_id) 
                    join Principals P on (M.id = P.movie_id and C.name_id = P.name_id) 
                    join  Names N on (N.id = C.name_id) 
                    where M.id = {tup[2]} and N.id = {nid} 
                    order by P.ordering, C.role
                    """)
        for tup3 in cur.fetchall():
          print(" as " + (tup3[1].capitalize()).replace("_", " "))
  except Exception as e:
    print("Unable to execute the query")
except psycopg2.Error as err:
  print("DB error: ", err)
finally:
  if db:
    db.close()

