from dbconfig import *
import pymysql
import sys

#	Connect to toyu of the MySQL server
db = get_mysql_param()
cnx = pymysql.connect(user=db['user'], 
                      password=db['password'],
                      host=db['host'],
                      # port needs only if it is not the default number, 3306.
                      port = int(db['port']),    # this line is needed only 
                      database=db['database'])
cursor = cnx.cursor()

choice = '1'
while (choice == '1' or choice == '2'):
    #	Get user choices
    choice = input('''
Enter your choice number:
    1. All departments
    2. Single department
    3. quit
> ''')
    if (choice == '1'):
        query = '''
SELECT d.deptCode, 
    d.deptName AS department,
    sc.schoolName AS school,
    count(s.major) AS n_majors
FROM department d INNER JOIN school sc ON (d.schoolCode = sc.schoolCode)
    LEFT JOIN student s ON (d.deptCode = s.major)
GROUP BY d.deptCode, department, school;
 '''   
        cursor.execute(query)
        print('Departments:')
        print('------------')
        for (deptCode, dept, school, n_majors) in cursor:
            print(deptCode + '(' + dept + ') in school of ' + school + ' has ' 
                + str(n_majors) + ' majors.')
    elif (choice == '2'):
        dept = input("Enter a department code (e.g. ITEC, CSCI): ")
        query = '''
SELECT if(f.`rank` IS NULL, 'Unranked', f.`rank`) AS position,
    count(f.facId) as n_faculty
FROM faculty f
WHERE f.deptCode = %s
GROUP BY position;
'''
        cursor.execute(query,(dept,))

        #	Print product report.
        print('Number of faculty in the department ' + dept + ':')
        print('----------------------------------------------------')
        for (rank, n_faculty) in cursor:
            print(rank + ': ' + str(n_faculty) + ' faculty.')

cursor.close()
cnx.close()