# Ridgeview Python interface (extension for Lessons 4 and 5)
# The standard sqlite3 steps: connect, create a cursor, execute, fetch, close.
# Keep this file in the same folder as ridgeview.db.
import sqlite3

DATABASE = 'ridgeview.db'


# Function to retrieve the roll for one activity
def get_roll(activity_name):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    sql = """SELECT Student.FirstName, Student.LastName, Student.Email
             FROM Student, Registration, Activity
             WHERE Student.StudentID = Registration.StudentID
               AND Registration.ActivityID = Activity.ActivityID
               AND Activity.ActivityName = ?;"""

    # The ? placeholder keeps the user's input out of the SQL text
    cursor.execute(sql, (activity_name,))
    records = cursor.fetchall()
    conn.close()
    return records


# Function to count registrations for each activity (for the Lesson 5 chart)
def get_counts():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    sql = """SELECT Activity.ActivityName, COUNT(*)
             FROM Activity, Registration
             WHERE Activity.ActivityID = Registration.ActivityID
             GROUP BY Activity.ActivityName
             ORDER BY COUNT(*) DESC;"""

    cursor.execute(sql)
    records = cursor.fetchall()
    conn.close()
    return records


activity = input("Activity name: ")
records = get_roll(activity)
print(f"\nRoll for {activity}: {len(records)} students")
print("First       Last            Email\n" + "-" * 50)
for record in records:
    print(f"{record[0]:<12}{record[1]:<16}{record[2]}")

with open("activity_counts.csv", "w") as outFile:
    print("Activity,Registrations", file=outFile)
    for name, count in get_counts():
        print(f"{name},{count}", file=outFile)
print("\nSaved activity_counts.csv for Lesson 5")
