#!/bin/bash
# marm_sql -- gives a command-line interface to various useful Marmoset tasks 
# such as submission download, mark download, and submission testing
# Original version by Huma Zafar for CS 136
# Modified by Sylvie Davies for CS 241 (Fall 2012)
# Best viewed with a terminal width of 120 columns

### WARNING: This version of the script has not been tested with courses 
### other than CS 241. Before using one of the functions, read through it 
### to see if there is anything you need to change.

export HOME=/u/$(whoami)/
CONFIGURE=
DOWNLOAD=
LATE=
MARKS=
DEFAULT_STUDENTS_FILE_LOCATION=/tmp/$(termcode)"_students"
STUDENTS=$DEFAULT_STUDENTS_FILE_LOCATION
TEST=
CURRTERM=$(termcode -l) # -l gives the term format that Marmoset uses
COURSENAME=$(whoami | tr '[a-z]' '[A-Z]')
COURSE_PK=$(echo "select course_pk from courses where \
                  semester='$CURRTERM' and \
                  coursename='$COURSENAME';" | mysql -N --raw)

#############
# Functions #
#############

# Display either a long or short usage message depending on if the -h option was given
usage() {
    if (( $LONGUSAGE )); then

cat <<ENDUSAGE
Usage:
-h: Display this usage message.

-m proj or -m assn: 
    Download on-time marks for a given assignment or project.
    "proj" is case-insensitive and may have any of the following forms:
    * A string of the form AxPy, where x and y are integers.
    * A string of the form AxBonus, where x is an integer.
    * A string of the form Ax, where x is an integer. This will download
      marks for every project from Assignment Ax.
    * An integer x. This will download marks for every project from 
      Assignment Ax.
    If "proj" is the name of a single project, a .csv file called
    project-[proj]-grades.csv is created in the current directory containing 
    the marks for that project (for example, project-A7P7-grades.csv). If 
    "proj" is the name of an assignment, .csv files will be created for each 
    project in the assignment.

    Examples:
    marm_sql -m A7P4
    Downloads marks for Assignment 7, Problem 4 and saves them in a file
    called project-A7P4-grades.csv.
    marm_sql -m a11bonus
    Downloads marks for Assignment 11, Bonus Problem and saves them in a file
    called project-a11bonus-grades.csv.
    marm_sql -m A6
    Downloads marks for all Assignment 6 problems. For each problem, a .csv
    file containing the marks is created in the current directory. 
    marm_sql -m 3
    Downloads marks for all Assignment 3 problems. For each problem, a .csv
    file containing the marks is created in the current directory.

-d proj:
    Download the best on-time submissions for a project. The submissions
    are stored in a folder with the same name as the project in the current
    directory. Unlike the -m option, "proj" can only be a string representing
    a single project rather than a whole assignment. 

-s file:
    Specify a file containing a list of student IDs to process with -m or -d. 
    By default, these options run for every student in the classlist.

-t student,proj,sub:
    Download a student's submission and run tests.
    "student" should be a Quest user ID trimmed to 8 characters. If you do 
    not know the student's Quest user ID, type their first or last name 
    and the script will search the classlist for matches.
    "proj" should be the name of a single Marmoset project. Unlike the -m
    option, "proj" cannot denote an assignment.
    "sub" should be the number of the submission you want to test (number, 
    not PK). This argument is optional; if you leave it out the most recent 
    submission will be tested.

    A folder called "localtest" will be created under the current
    directory. The test scripts for the given project are copied to the
    folder "localtest/student.proj" and executed, and the results (pass,
    fail, timeout, error) are displayed on standard output. The script
    attempts to save any output produced by the student's code for
    a particular test in files with names like "TestScriptName.x.output".
    Each time you test a submission with this script, a hidden symbolic
    link called ".lt" pointing to "localtest/student.proj" is created in 
    the current directory, allowing quick access to the most recently
    tested submission.

    Examples:
    marm_sql -t jsmith,a11p1,1
    Test the first submission from student "jsmith" for A11P1. To view the
    test results, type "cd localtest/jsmith.A11P1" or simply "cd .lt".
    marm_sql -t Sally,a7p7,5
    Test submission number 5 for a student named "Sally" for A7P7. The 
    script will search the classlist for the string "Sally" and present
    the user with a list of possible matching user IDs (or simply
    substitute in Sally's user ID if there is only one match).
    marm_sql -t xyzzy,a4p3
    Test the most recent submission from student "xyzzy" for A4P3.

-c: Quick way to get the current course PK (unique number assigned to each 
    offering of each course by Marmoset).

-q: Run an arbitrary query on the Marmoset database.
    Example:
    marm_sql -q "select project_pk from projects where title='A1P1', 
                 course_pk=\$(marm_sql -c);"
    This returns the project PK for A1P1.

-v: Enables verbose mode. The script will print extra information about
    what it is doing. When used in conjunction with -d or -m, a download
    progress indicator is displayed.
ENDUSAGE

    else

cat <<ENDUSAGE
Usage:
-h: Display a more detailed usage message.
-m proj or -m assn: 
    Download on-time marks for a given assignment or project.
    Examples:
    marm_sql -m A7P4 (Download marks for A7P4)
    marm_sql -m A6 (Download marks for all A6 projects)
-d proj:
    Download the best on-time submissions for a project. Unlike -m, this
    cannot be used with assignments (only single projects).
-s file:
    Specify a file containing a list of student IDs to used with -m or -d.
-t student,proj,sub:
    Download a student's submission and run tests. The scripts and 
    results are stored in "localtest/student.proj" under the current 
    directory. A hidden symbolic link ".lt" is created/updated each time 
    the script runs, pointing to the most recent test results. "sub" is
    optional; if omitted the most recent submission is tested.
    Example:
    marm_sql -t jsmith,a11p1,4
    (Test submission #4 to A11P1 by user "jsmith". After the test finishes,
    type "cd localtest/jsmith.a11p1" or just "cd .lt" to see the results.)
-c: Quick way to get the current course PK.
-q: Run an arbitrary MySQL query on the Marmoset database.
    Example:
    marm_sql -q "select project_pk from projects where title='A1P1', 
                 course_pk=\$(marm_sql -c);"
-v: Enables verbose mode. With -m and -d, shows a download progress counter.
ENDUSAGE

    fi
}

# This function is like "exit" but it does cleanup before exiting.
# Takes one parameter (the exit code).
quit() {
    # Remove the default student IDs file, if it was used
    if (( $USE_DEFAULT_STUDENTS_FILE )); then
        # We could just do "rm $STUDENTS" here but we want to be extra careful to not accidentally 
        # delete a user-provided student IDs file...
        rm $DEFAULT_STUDENTS_FILE_LOCATION >& /dev/null
    fi
    exit $1
}

# Outputs the classlist, but with no comments (assuming # is the comment character) or blank lines.
trim_classlist() {
    # Delete everything after # on each line, then delete all empty lines
    sed -e 's/#.*//' -e '/^$/d' ~/.classlist
}

# Download student submissions for a project.
download() {
    # First parameter is project name
    PROJ=$1;
    # Second is student IDs file
    FILE=$2;

    # Get the project title with correct capitalization
    TITLE=$(echo "select title from projects 
        where   course_pk = '$COURSE_PK' 
        and     title = '$PROJ';" | mysql -N --raw)

    # Get the project PK
    PROJ_PK=$(echo "select project_pk from projects 
        where   course_pk = '$COURSE_PK' 
        and     title = '$PROJ';" | mysql -N --raw)
    
    # Make sure the project actually exists
    if [ -z "$PROJ_PK" ]; then
        echo "$PROJ was not found in the Marmoset database." >&2
        quit 1
    fi

    # Get the due date
    ONTIME_DATE=$(echo "select ontime from projects 
        where project_pk = $PROJ_PK;" | mysql -N --raw)
 
    # Create the directory to store the submissions
    mkdir $TITLE >& /dev/null

    # Display the progress of submission downloads
    COUNTER=0                   # Number downloaded so far
    MAX=$(cat $FILE | wc -l)    # Total number to download

    (( $VERBOSE )) && echo "Downloading submissions for $PROJ..." >&2
    # For each student in the student IDs file...
    cat $FILE |
    while read STUDENTID; do 
        # Increment the download counter
        COUNTER=$(expr $COUNTER + 1)

        # Display progress if verbose flag is set
        if (( $VERBOSE )); then
            # Instead of printing a billion lines, we return to the start of the line with a carriage return 
            # each time the download counter updates. This gives us a "nice" progress display when using this 
            # script from the command line.
            printf "                                                      \r" >&2
            printf "Downloading submission for %-8s ($COUNTER/$MAX)\r" $STUDENTID >&2
        fi

        # Look up the student registration PK 
        STUDENTREG=$(echo "select student_registration_pk from student_registration 
            where student_pk = 
                (select student_pk from students 
                 where campus_uid = '$STUDENTID') 
            and course_pk = '$COURSE_PK';" | mysql -N --raw)

        # Use it to figure out if the student has an extension 
        EXTENSION=$(echo "select extension from student_submit_status 
            where   project_pk = '$PROJ_PK '
            and     student_registration_pk = '$STUDENTREG';" | mysql -N --raw)

        # If not, set the extension length to 0
        if [ -z "$EXTENSION" ]; then
            EXTENSION=0;
        fi

        # Figure out the submission PK for their best ontime submission, accounting for extensions
        SUBMISSION_PK=$(echo "(select submission_pk from 
            (select submissions.submission_pk, submissions.submission_timestamp, 
            sum(test_outcomes.point_value) as total from submissions 
            join test_outcomes on test_outcomes.test_run_pk = submissions.current_test_run_pk 
            where submissions.project_pk = '$PROJ_PK' 
            and student_registration_pk = '$STUDENTREG' 
            and submissions.submission_timestamp <= 
                DATE_ADD(cast('$ONTIME_DATE' as datetime),INTERVAL $EXTENSION HOUR) 
            and test_outcomes.outcome='passed' 
            group by submissions.submission_pk 
            order by total desc, submission_timestamp desc) 
        as t1 
        limit 1);" | mysql -N --raw)

        # If the student *has* a best ontime submission...
        if [ -n "$SUBMISSION_PK" ]; then
            # Download the zip file
            echo "select archive from submission_archives 
                where   submission_archives.archive_pk = 
                    (select archive_pk from submissions 
                     where  submission_pk = '$SUBMISSION_PK');" | mysql -N --raw > $STUDENTID.zip
            # Extract the submission to the corresponding directory
            EXTRACTDIR=$TITLE/$STUDENTID
            mkdir $EXTRACTDIR >& /dev/null
            mv $STUDENTID.zip $EXTRACTDIR
            pushd $EXTRACTDIR >& /dev/null
            yes | unzip -qq $STUDENTID.zip >& /dev/null
            rm $STUDENTID.zip
            popd >& /dev/null
        fi 
    done 
    (( $VERBOSE )) && printf "\nDownload complete.\n" >&2
}

# Download marks for a particular assignment or project.
marks() {
    # First argument is assignment/project name or number
    ASSN=$1;
    # Second argument is student IDs file
    FILE=$2;

    # We accept a few different formats for the assignment/project name or number.
    # Full case-insensitive project name such as A4P6, a11bonus, a2P3b
    if echo $ASSN | grep -iE 'A[0-9]*(P|B).*' > /dev/null; then
        PROJECTS=$(echo "select project_pk from projects 
        where   course_pk = '$COURSE_PK' 
        and     title = '${ASSN}';" | mysql -N --raw)    
        ASSN_NAME="$ASSN"
    # Assignment name such as a7, A10
    elif echo $ASSN | grep -iE 'A[0-9]*' > /dev/null; then
        PROJECTS=$(echo "select project_pk from projects 
        where   course_pk = '$COURSE_PK' 
        and     title regexp '${ASSN}(P|B).*';" | mysql -N --raw)
        ASSN_NAME="$ASSN"
    # Assignment number such as 1, 11
    elif echo $ASSN | grep -iE '[0-9]*' > /dev/null; then
        PROJECTS=$(echo "select project_pk from projects 
        where   course_pk = '$COURSE_PK' 
        and     title regexp 'A${ASSN}(P|B).*';" | mysql -N --raw)
        ASSN_NAME="A$ASSN"
    # If you have anything else, we treat it as a regular expression and try to find matching projects
    else
        PROJECTS=$(echo "select project_pk from projects 
        where   course_pk = '$COURSE_PK' 
        and     title regexp '${ASSN}';" | mysql -N --raw)
        ASSN_NAME="$ASSN"
    fi

    # If no matching projects are found, quit
    if [ -z "$PROJECTS" ]; then
        echo "No projects matching the pattern $ASSN were found." >&2
        quit 1
    fi

    (( $VERBOSE )) && echo "Downloading marks for $ASSN_NAME..." >&2
    for PROJ_PK in $PROJECTS; do
        # Get the project deadline
        ONTIME_DATE=$(echo "select ontime from projects 
        where   project_pk = $PROJ_PK;" | mysql -N --raw)
        
        # Get the project title (based on the PK)
        TITLE=$(echo "select title from projects 
        where   course_pk  = '$COURSE_PK' 
        and     project_pk = '$PROJ_PK';" | mysql -N --raw)

        # Display the progress of mark downloads
        COUNTER=0                   # Number downloaded so far
        MAX=$(cat $FILE | wc -l)    # Total number to download

        # For each student in the student IDs file...
        cat $FILE | 
        while read STUDENTID; do 
            # Increment the download counter
            COUNTER=$(expr $COUNTER + 1)

            # Display progress if verbose flag is set
            if (( $VERBOSE )); then
                # Instead of printing a billion lines, we return to the start of the line with a carriage return 
                # each time the download counter updates. This gives us a "nice" progress display when using this 
                # script from the command line.
                printf "                                            \r" >&2
                printf "Downloading marks for $TITLE ($COUNTER/$MAX)\r" >&2
            fi
            
            # Get student reg PK (course-specific unique identifier for student)
            STUDENTREG=$(echo "select student_registration_pk from student_registration 
            where student_pk = 
                (select student_pk from students 
                 where  campus_uid='$STUDENTID') 
            and course_pk='$COURSE_PK';" | mysql -N --raw)
            
            # Get the length of the extension the student has on this project (if any)
            EXTENSION=$(echo "select extension from student_submit_status 
            where   project_pk = '$PROJ_PK'
            and     student_registration_pk = '$STUDENTREG';" | mysql -N --raw)
            
            # If no extension, set the extension length to 0
            if [[ -z "$EXTENSION" ]]
            then
                EXTENSION=0;
            fi

            # Find the ontime submission with the highest mark, accounting for extensions
            HIGHEST_MARK=$(echo "(select total from 
                (select submissions.submission_pk, submissions.submission_timestamp, 
                sum(test_outcomes.point_value) as total from submissions 
                join test_outcomes on test_outcomes.test_run_pk = submissions.current_test_run_pk 
                where submissions.project_pk = '$PROJ_PK' 
                and student_registration_pk = '$STUDENTREG' 
                and submissions.submission_timestamp <= 
                    DATE_ADD(cast('$ONTIME_DATE' as datetime),INTERVAL $EXTENSION HOUR)
                and test_outcomes.outcome = 'passed' 
                and test_outcomes.test_type <> 'build' 
                group by submissions.submission_pk 
                order by total desc, submission_timestamp desc) 
            as t1 
            limit 1);" | mysql -N --raw)
    
            # If no highest mark could be found, the student probably has no submissions, so set it to 0
            if [[ -z "$HIGHEST_MARK" ]]; then
                HIGHEST_MARK=0;
            fi
            
            # Output a line with the student's ID followed by their mark on this project
            echo $STUDENTID","$HIGHEST_MARK
        # Save the resulting marks file in a csv
        done > project-$TITLE-grades.csv
    done

    (( $VERBOSE )) && printf "\nDownload complete.\n" >&2
}

# Download and test a student's submission.
# This function assumes your course uses "dynamic" Marmoset test scripts rather than "static".
test() {
    # First argument is student ID, or student name if ID is not known
    STUDENTID=$1
    
    # Is the specified ID in the classlist or course staff list?
    grep -i "^${STUDENTID}$" $STUDENTS > /dev/null || 
    grep "^${STUDENTID}" ~/.coursestaff.$(termcode -m) > /dev/null
    # If not...
    if [ $? -ne 0 ]; then
        echo "'$STUDENTID' not found in student user ID file." >&2
        # We assume the user input a name instead of a student ID (e.g. "John" instead of jsmith)
        # If there is exactly one student matching that name... 
        if [ $(trim_classlist | cut -d: -f2,3 | grep -i $STUDENTID | wc -l) -eq 1 ]; then
            # Extract the student's ID from the line
            STUDENTID=$(trim_classlist | grep -i $STUDENTID | cut -d: -f2)
            # Cut the student ID down to 8 characters
            STUDENTID=$(echo $STUDENTID | cut -c1-8)
            echo "User ID '$STUDENTID' matches this name." >&2
        # Otherwise, if there are multiple matches...
        elif [ $(trim_classlist | cut -d: -f2,3 | grep -i $STUDENTID | wc -l) -ge 1 ]; then
            #  Help the user out by listing the options
            echo "Did you mean one of the following students?" >&2
            trim_classlist |        # Remove comments         
            cut -d: -f2,3 |         # Cut out everything but name/ID
            grep -i $STUDENTID |    # Look for the ID
            tr ':' ' '              # Change : to space (looks nicer)
            echo -n "Enter the corrected user ID (first 8 characters): " >&2
            read STUDENTID
        # Otherwise, there are no matches; just display the prompt with no help
        else
            echo -n "Enter the corrected user ID (first 8 characters): " >&2
            read STUDENTID
        fi
    fi

    # Second argument is project title
    PROJ=$2
    # Third argument (optional) is submission number
    SUBNUM=$3

    # Look up PROJ_PK (unique identifier for project in Marmoset database)
    PROJ_PK=$(echo "select project_pk from projects 
    where   course_pk='$COURSE_PK' 
    and     title = '$PROJ';" | mysql -N --raw)

    # Look up STUDENT_PK (unique identifier for student in Marmoset database)
    STUDENT_PK=$(echo "select student_pk from students 
    where campus_uid='$STUDENTID';" | mysql -N --raw)

    if [ -z "$STUDENT_PK" ]; then
        echo "'$STUDENTID' not found in Marmoset database." >&2
        quit 1
    fi
    
    # Look up STUDENT_REG_PK (course-specific unique identifier)
    STUDENT_REG_PK=$(echo "select student_registration_pk from student_registration 
    where   course_pk='$COURSE_PK' 
    and     student_pk='$STUDENT_PK';" | mysql -N --raw)

    if [ -z "$STUDENT_REG_PK" ]; then
        echo "'$STUDENTID' does not seem to be registered in this course." >&2
        quit 1
    fi
    
    # If no submission number (or submission number "0") was specified...
    if [  -z "$SUBNUM" -o "$SUBNUM" == "0" ]; then
        # Get number of most recent submission 
        SUBNUM=$(echo "select count(submission_number) from submissions 
        where   project_pk='$PROJ_PK' 
        and     student_registration_pk='$STUDENT_REG_PK';" | mysql -N --raw)
    fi

    # Look up SUB_PK (unique identifier for submissions)
    SUBPK=$(echo "select submission_pk from submissions 
    where   project_pk='$PROJ_PK' 
    and     student_registration_pk='$STUDENT_REG_PK' 
    and     submission_number='$SUBNUM';" | mysql -N --raw)

    if [ -z "$SUBPK" ]; then
        echo "'$STUDENTID' has no submission number $SUBNUM." >&2
        quit 1
    fi

    # Create directory to store local test results
    DIR="localtest"
    mkdir $DIR >& /dev/null

    # Create subdirectory for particular student/project
    PROJDIR="$DIR/$STUDENTID.$PROJ"
    if [ -d $PROJDIR ]; then
        # If this directory exists, delete it first
        rm -rf $PROJDIR
    fi
    mkdir $PROJDIR >& /dev/null
    
    # Make a symbolic link for easy access to most recent test result
    rm .lt >& /dev/null
    ln -s $PROJDIR .lt
    
    # Change to the local test directory temporarily to download and run the tests
    pushd $PROJDIR >& /dev/null

    echo "Testing submission $SUBNUM for $PROJ by user $STUDENTID."
    echo "Results will be stored in $PROJDIR." 
    echo "Type \"cd .lt\" for quick access to the results."

    # Download the student's submission
    (( $VERBOSE )) && printf "Downloading student submission... "
    echo "select archive from submission_archives 
    where submission_archives.archive_pk = 
        (select archive_pk from submissions 
        where   submission_pk = '$SUBPK');" | mysql -N --raw > $STUDENTID.zip

    # Unzip student's submission
    yes | unzip $STUDENTID.zip >& /dev/null
    rm $STUDENTID.zip
    (( $VERBOSE )) && echo "done"

    # Download the currently active dynamic test setup
    (( $VERBOSE )) && printf "Downloading dynamic test.properties file... "
    echo "select archive from project_jarfile_archives 
    where project_jarfile_archives.archive_pk =
        (select archive_pk from project_jarfiles 
        where   project_pk = '$PROJ_PK' 
        and     jarfile_status = 'active');" | mysql -N --raw > $PROJ.zip

    # Unzip dynamic test setup
    yes | unzip $PROJ.zip >& /dev/null
    rm $PROJ.zip
    (( $VERBOSE )) && echo "done"

    # Extract the location of the test scripts from the test.properties file
    TESTS_LOCATION=$(sed -En 's/build.make.file=(.*)/\1/p' test.properties)/test/*

    (( $VERBOSE )) && printf "Copying test scripts from $(whoami)t account... "
    # Copy them over from the 't' account for the course
    if (( $VERBOSE )); then
        scp -r $(whoami)t@linux.student.cs:$TESTS_LOCATION .
    else
        scp -qr $(whoami)t@linux.student.cs:$TESTS_LOCATION .
    fi
    (( $VERBOSE )) && echo "done"

    (( $VERBOSE )) && printf "Compiling student's code... "
    # Compile the student's submission
    make
    (( $VERBOSE )) && echo "done"

    # Extract the list of test names from the test.properties file
    TESTS="
    $(sed -En 's/^test.class.public=(.*)/\1/p' test.properties) 
    $(sed -En 's/^test.class.release=(.*)/\1/p' test.properties) 
    $(sed -En 's/^test.class.secret=(.*)/\1/p' test.properties)"

    # Put the current directory on the PATH temporarily (I forget why this is necessary...)
    TEMP_PATH=$PATH
    export PATH=$(pwd):$PATH
    
    (( $VERBOSE )) && echo "Executing test scripts:"
    for TESTNAME in $TESTS; do
        if [ "$TESTNAME" != "" ];  then
            # We do some weird stuff here to attempt to save the student's results for each test.
            # First save the modified times for all files
            ls -lt --time-style=full-iso | tr -s [:blank:] " " | cut -f7,9 -d' ' | sort > .modified

            # Run the test and save the exit code
            TESTNAME=${TESTNAME%.in}
            printf "Running test $TESTNAME..."
            ./$TESTNAME >& /dev/null
            RVAL=$?

            # Get modified times from after test
            ls -lt --time-style=full-iso | tr -s [:blank:] " " | cut -f7,9 -d' ' | sort > .after

            # Create list of files that were modified or created by the test.
            # Usually test scripts create stderr/stdout files containing the student's results.
            echo > .changedfiles
            # comm -13 returns lines that only appear in the second file (lines where the modified time changed)
            comm -13 .modified .after | 
            while read FILE; do
                echo $FILE | cut -f2 -d' ' >> .changedfiles
            done

            # Rename the modified/created files to correspond to the test name.
            # For example, suppose the test script "LargeTest" runs, and while running it creates two files called
            # stdout and stderr that contain the student's output to standard output and standard error.
            # These files will be renamed to "LargeTest.x.stdout" and "LargeTest.x.stderr". Then these can be 
            # compared with the expected output files (often called e.g. LargeTest.stdout and LargeTest.stderr).
            # This makes several assumptions about the names of test files and the design of the test scripts that
            # may not be true for courses other than CS 241.
            cat .changedfiles | 
            while read FILE; do
                if [ -n "$FILE" ]; then
                    cp $FILE $TESTNAME.x.$FILE
                fi
            done

            # After all the renaming stuff is done, print the test result (based on exit code)
            case $RVAL in
                0)
                    printf " passed"
                    ;;
                1)
                    printf " error"
                    ;;
                2)
                    printf " timeout"
                    ;;
                3)
                    printf " failed"
                    ;;
                ?)
                    printf " unknown exit code $RVAL"
                    ;;
            esac
            # Finally, echo a newline and go to the next test
            echo
        fi
    done

    # Restore the original PATH
    export PATH=$TEMP_PATH

    # Change back to original working directory
    popd >& /dev/null
}

#########################
# Start of main program #
#########################

# If no command line arguments are provided, echo a usage message
if [[ -z $1 ]]
then
    usage
    quit 1
fi

# Set default values for flags
VERBOSE=0                   # verbosity (if on, displays info about what the script is doing while it runs)
LONGUSAGE=0                 # usage (by default, display the shorter usage message)
USE_DEFAULT_STUDENTS_FILE=1 # by default run for all students in the classlist, otherwise use a provided list

# Read command line options and arguments
while getopts :d:l:m:s:t:q:cvh opt; do
    case $opt in
        d)
            # OPTARG is the project to download submissions for
            DOWNLOAD=$OPTARG
            ;;
        m)
            # OPTARG is the project/assignment to download marks for
            MARKS=$OPTARG
            ;;
        l)
            # OPTARG is the project/assignment to download late marks for
            LATE=$OPTARG
            ;;
        s)  
            # We don't want to use the default student IDs file
            USE_DEFAULT_STUDENTS_FILE=0
            # We want to use the one provided as an argument
            STUDENTS=$OPTARG
            ;;
        t)
            # OPTARG is a comma-separated string student,proj,sub_num
            TEST=$OPTARG
            ;;
        q)
            # OPTARG is the SQL query to run
            QUERY=$OPTARG
            ;;
        c)
            # This option just returns the course PK
            echo $COURSE_PK
            quit 0
            ;;
        v)
            # Turn on the verbose flag
            VERBOSE=1
            ;;
        h)
            # Turn on the long usage flag
            LONGUSAGE=1
            ;;
        ?)
            # Print the usage message if the option is unrecognized
            usage
            quit 1
    esac
done

# Check if the user provided an alternate student IDs file, or if the default should be used
if (( $USE_DEFAULT_STUDENTS_FILE )); then
    # The default is to use every student in the classlist.
    # Remove comments, get second field of each line (userid), get first 8 chars of each userid
    trim_classlist | cut -f2 -d: | cut -c1-8 > $STUDENTS
    # This file is temporary and gets removed at the end of the script (see the "quit" subroutine)
fi

# Download best ontime marks for a project, using the default or given student ID list
if [[ -n "$DOWNLOAD" ]] 
then
    download $DOWNLOAD $STUDENTS
    quit 0
fi

# Download best ontime marks for a project or assignment, using the default or given student ID list
if [[ -n "$MARKS" ]]
then
    marks $MARKS $STUDENTS
    quit 0
fi

# Uncomment this if you want to try out the function for downloading late marks...
# I haven't tested this myself (see below).
#if [[ -n "$LATE" ]]
#then
#    lates $LATE $STUDENTS
#    quit 0
#fi

# Just run an SQL query
if [[ -n "$QUERY" ]]; then
    echo "$QUERY" | mysql -N --raw
    quit 0
fi

# Test a student's submission 
if [[ -n "$TEST" ]]
then
    STUD=$(echo $TEST | cut -f1 -d',')
    ASSN=$(echo $TEST | cut -f2 -d',')
    SUBID=$(echo $TEST | cut -f3 -d',')
    test $STUD $ASSN $SUBID
    quit 0
fi

# If we get down here, the user didn't specify a valid option, or something weird happened
usage
quit 1

# Below is a function for downloading marks while accounting for late submissions from the original marm_sql 
# by Huma. Since CS 241 does not give marks for late submissions, I didn't test this feature or modify the 
# function... you can try to get it working if you think it will be useful for your course.
#
#lates() {
#    PROJ=$1;
#    FILE=$2;
#    PROJ_PK=$(echo "select project_pk from projects where \
#        course_pk = '$COURSE_PK' and title = '$PROJ';" | mysql -N --raw)
#    ONTIME_DATE=$(echo "select ontime from projects where project_pk=$PROJ_PK;" | mysql -N --raw)
#
#    (while read STUDENTID
#do 
#    STUDENTREG=$(echo "select student_registration_pk from student_registration where \
#        student_pk = (select student_pk from students where campus_uid='$STUDENTID') and \
#        course_pk = '$COURSE_PK';" | mysql -N --raw)
#    BEST_ONTIME=$(echo "(select total from \
#        (select submissions.submission_pk, submissions.submission_timestamp, \
#        sum(test_outcomes.point_value) as total from submissions \
#        join test_outcomes on test_outcomes.test_run_pk = submissions.current_test_run_pk \
#        where submissions.project_pk = '$PROJ_PK' \
#        and student_registration_pk = '$STUDENTREG' \
#        and submissions.submission_timestamp <= cast('$ONTIME_DATE' as datetime) \
#        and test_outcomes.outcome='passed' \
#        and test_outcomes.test_type <> 'build' \
#        group by submissions.submission_pk \
#        order by total desc, submission_timestamp desc) as t1 \
#        limit 1);" | mysql -N --raw)
#
#    BEST_LATE=$(echo "(select total*0.5 from \
#        (select submissions.submission_pk, submissions.submission_timestamp, \
#        sum(test_outcomes.point_value) as total from submissions \
#        join test_outcomes on test_outcomes.test_run_pk = submissions.current_test_run_pk \
#        where submissions.project_pk = '$PROJ_PK' \
#        and student_registration_pk = '$STUDENTREG' \
#        and submissions.submission_timestamp > cast('$ONTIME_DATE' as datetime) \
#        and test_outcomes.outcome='passed' \
#        and test_outcomes.test_type <> 'build' \
#        group by submissions.submission_pk \
#        order by total desc, submission_timestamp desc) as t1 \
#        limit 1);" | mysql -N --raw)
#
#    if [[ -z "$BEST_ONTIME" ]]
#    then
#        BEST_ONTIME=0;
#    fi
#
#    if [[ -z "$BEST_LATE" ]]
#    then
#        BEST_LATE=0;
#    fi
#
#    BEST_LATE=$(printf "%.0f" $BEST_LATE)
#
#    if [ "$BEST_ONTIME" -gt "$BEST_LATE" ]; then
#        HIGHEST_MARK=$BEST_ONTIME
#    else
#        HIGHEST_MARK=$BEST_LATE
#    fi
#
#    echo $STUDENTID","$HIGHEST_MARK
#
#done) < $FILE
#}