#!/bin/sh
# (C) 2011 by Eugeniy Mikhailov, <evgmik@gmail.com>
# vim:set ft=tcl: \
exec tclsh "$0" "$@"

package require Tcl 8.5
package require json::write
package require sqlite3
package require md5
source ./GradeBook_lib.tcl

if { $argc < 2 } {	
	puts {Usage:}
	puts "  $argv0 gradebook.sqlitedb gradescope.sqlitedb"
	puts {}
	puts {  expect both files to be sqilte tables:}
	puts {  gradescope should have a single table 'gradescope':}
	puts {Example:}
	puts [concat "  $argv0" { 2020_Fall_Phys251 gradescope.db}]
	exit
}

set classDB [lindex $argv 0] 
set gradescopeDB [lindex $argv 1]

sqlite3 db $classDB
sqlite3 gdb $gradescopeDB

proc iferror { err errStat {eval_str {""} }} {
        #return; # comment out when debugging
        if { $err } {
                puts "Error: $errStat"
                if { $eval_str ne "" } {
                        puts "For query: $eval_str"
                }
        }
}

proc getColListFromAnyTable {db table} {
	set all_column_names ""
	set eval_str [concat SELECT * FROM \'$table\' LIMIT 1]
	set err [catch {
		$db eval $eval_str v {
			set all_column_names $v(*)
		}
	} errStat ]
	iferror $err $errStat
	if { $err } { return false }
	return $all_column_names
}

proc getGradescopeUsernames { db } {
	set username_list {}
	set eval_str [concat SELECT Email FROM gradescope]
	set err [catch {
		$db eval $eval_str v {
			lappend username_list $v(Email)
		}
	} errStat ]
	iferror $err $errStat
	if { $err } { return false }
	return $username_list
}

proc getGradescopeUserGrade { db uname col } {
	set username_list {}
	set eval_str [concat SELECT \"$col\" FROM gradescope where Email=='$uname']
	set val [$db onecolumn $eval_str]
	return $val
}

proc isInList { col listCol} {
	foreach sCol $listCol {
		set result [regexp $sCol $col match]
		if { $result } {
			return true
		}
	}
	return false

}

proc trimColName { col } {
	set result [regexp -nocase {(.*) due week.*} $col match submatch]
	if { $result} {
		#puts $col
		#puts $match
		#puts $submatch
		return $submatch
	}
	return $col
}


set commonInfoCol {{First Name} {Last Name} {SID} {Email}}
set infoColumsMarkers {{ - Lateness \(H:M:S\)} { - Max Points} { - Submission Time} {Total Lateness \(H:M:S\)}}

set skipCreationCol [concat $commonInfoCol $infoColumsMarkers]

# add columbs with reports to gradebook db
foreach col [getColListFromAnyTable gdb gradescope] {
	if { [isInList $col $skipCreationCol] } {
		continue
	}
	#puts "$col"
	set shortCol [trimColName $col]
	#puts "$col => $shortCol"

	if { ![doesColumnExists $shortCol GradesTable] } {
		puts "Adding: ${shortCol}"
		set result [regexp -nocase "pre-lab" $shortCol match]
		if { $result} {
			AddColumnNonWeb $shortCol Quiz 10
			continue
		}
		# everything else is labreport
		AddColumnNonWeb $shortCol LabReport 100
	} else {
		#puts "$col: exists"
	}
}

# now for every user add their grades
set username_list [getGradescopeUsernames gdb]
foreach uname $username_list {
	foreach col [getColListFromAnyTable gdb gradescope] {
		if { [isInList $col $skipCreationCol] } {
			continue
		}
		set shortCol [trimColName $col]
		set grade [getGradescopeUserGrade gdb $uname $col ]
		if { "" ne $grade } {
			puts "setting $grade for $uname in $col"
			UpdateColValue4UserNameNonWeb $shortCol $uname $grade
		}
	}
}