#!"c:\python310\python.exe"
#   Program url: skipped.
#   Suggested solution for HW #6, CSCI 4333, Spring 2026
#   It is acceptable to use no function.

import cgi
import pymysql
from dbconfig import get_mysql_param

def connect_db():
    params = get_mysql_param()
    return pymysql.connect(
        host=params['host'],
        user=params['user'],
        password=params['password'],
        database=params['database']
    )

def print_response_header():
    print("Content-Type: text/html\n")
    
def get_http_param(param):
    if not hasattr(get_http_param, "params"):
        get_http_param.params = cgi.FieldStorage()
    return get_http_param.params.getfirst(param)

def display_all_meets(connection):
    with connection.cursor() as cursor: 
        query = '''
SELECT DISTINCT m.meetId, m.Title AS meet,
    v.name AS venue
FROM swim.Meet AS m LEFT JOIN swim.Venue AS v ON (m.venueId = v.venueId);
'''
        cursor.execute(query)
        outBuffer = ''
        meetCount = 0
    
        for (mid, meet, venue) in cursor:
            meetCount += 1
            outBuffer = outBuffer + f"  <li><a href='?mid={str(mid)}'>{meet}</a>: at {venue}.</li>\n"
        print(f"<h3>There are {str(meetCount)} meets. Please select one.</h3>")    
        print('<ol>')
        print(outBuffer)     
        print('</ol>')   
        print ('''</body>
</html>''')

def display_a_meet(connection, mid):
    with connection.cursor() as cursor:
        query = '''
SELECT m.title, m.Date AS date, m.startTime AS stime, 
    m.endTime AS etime, v.Name AS venue
FROM swim.Meet AS m INNER JOIN swim.Venue AS v ON (m.VenueId = v.VenueId)
WHERE m.Meetid = %s
'''
        cursor.execute(query,(int(mid),))
        (title, date, stime, etime, venue) = cursor.fetchone()
        
        print(f"<h3> Meet #{mid}: {title}</h3>")
        print(f"<p>Venue: {venue}<br/>")
        print(f"Date/time: {str(date)}: {str(stime)} to {str(etime)}<br />")
        print('Events:</p>')
        print('<ol>')
        
        query = '''
SELECT e.EventId, e.title, 
    COUNT(p.swimmerId) AS n_swimmers
FROM swim.Event AS e LEFT JOIN swim.participation AS p ON (e.eventId = p.eventId)
WHERE Meetid = %s
GROUP BY e.EventId, e.title, e.startTime, e.endTime;
'''
        cursor.execute(query,(int(mid),))

        for (eid, event, n_swimmers) in cursor:
            print(f"<li><a href='?eid={str(eid)}'>{event}</a>: {str(n_swimmers)} participants.</li>")
        print("</ol>")
                                 
        print ('''</body>
</html>''')

def display_an_event(connection, eid):
    with connection.cursor() as cursor:
        query = '''
SELECT DISTINCT CONCAT(s.lName, ' ', s.fName) AS swimmer, s.phone
    
FROM swim.Swimmer AS s INNER JOIN swim.Participation AS p ON (s.SwimmerId = p.SwimmerId)
WHERE p.EventId = %s
order by swimmer
'''
        cursor.execute(query,(int(eid),))
        
        print('<p>Participants in Event # ' + str(eid) + '</p>')
        print('<ol>')
        for (swimmer, phone) in cursor:
            print('  <li>' + swimmer + ': ' + phone + '</li>\n')
        print('</ol>')
        print ('''</body>
</html>''')

def main():
    print_response_header()
    eid = get_http_param("eid")
    mid = get_http_param("mid")

    cnx = connect_db()
    with cnx:
        #   No HTTP parameter, show all meets.
        if mid is None and eid is None:
            display_all_meets(cnx)
        elif mid is not None:
            display_a_meet(cnx,mid)
        else:
            display_an_event(cnx,eid)


if __name__ == "__main__":
    main()