#!/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 blackboard.sqlitedb"
	puts {}
	puts {  expect both files to be sqilte tables:}
	puts {  blackboard should have a single table 'export_table':}
	puts {Example:}
	puts [concat "  $argv0" { 2020_Fall_Phys251 blackboard.db}]
	exit
}

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

sqlite3 db $classDB
sqlite3 bdb $blackboardDB

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 getBlackboardUsernames { db } {
	set username_list {}
	set eval_str [concat SELECT Username FROM export_table]
	set err [catch {
		$db eval $eval_str v {
			lappend username_list $v(Username)
		}
	} errStat ]
	iferror $err $errStat
	if { $err } { return false }
	return $username_list
}

proc getBlackboardUserGrade { db uname col } {
	set eval_str [concat SELECT \"$col\" FROM export_table where Username=='$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 shortCol $col
	set category Note
	set maxScore 0
	set type Score
	set result [regexp -nocase {(.*) (\[Total Pts:.*)} $col match shortCol scoreStr]
	if { $result} {
		set result [regexp -nocase {\[Total Pts: (\d+)} $scoreStr match maxScore]
		set result [regexp -nocase {\[Total Pts: (\d+) Percentage} $scoreStr match]
		if { $result } { set type Percentage }
		set number {}
		set name $shortCol
		set result [regexp -nocase {(\D+)(\d+)} $shortCol match name number]
		set result [regexp -nocase {(\S+)\s+$} $name match name]
		switch $name {
			HW     { set name Homework ; set category HomeWork }
			Lab    { set name Lab ;      set category LabReport}
			Design { set name "Final Project Design" ; set category FinalExam }
			Report { set name "Final Project Report" ; set category FinalExam }
			Precision { set name "Final Project Precision" ; set category FinalExam }
			Hardware { set name "Final Project Hardware" ; set category FinalExam }
			"Extra Credit" { set name "Final Project Bonus" ; set category FinalExam; set maxScore 0 }
			default { }
		}
		if { $number eq "" } {
			set shortCol "$name"
		} else {
			set shortCol "$name $number"
		}
	}
	#puts [list $col "--->" $shortCol $category $maxScore $type]
	return [list $shortCol $category $maxScore $type]
}

proc veval {script} { 
	# verbose eval
	global dryrun
	set cmd ""
	foreach line [split $script \n] {
		if {$line eq ""} {continue}
		append cmd $line\n
		if { [info complete $cmd] } {
			if { [info exists dryrun] && $dryrun} {
				puts -nonewline "DRYRUN: $cmd"
			} else {
				puts -nonewline "Executing: $cmd"
				uplevel 1 $cmd
			}
			set cmd ""
		}
	}
}

## adding students if they do not exist
proc addStudentsFromDB { db } {
	# blackboard does not provide the following info
	set id unknownID
	set section unknownSection
	puts "Blackboard does not provide Student ID and Section number, skipping user addition"
	return

	set fname [list First Name]
	set lname [list Last Name]
	set eval_str [concat SELECT * FROM 'export_table']
	set err [catch {
		$db eval $eval_str v {
			set email $v(Username)@email.wm.edu
			veval [list AddUserNonWeb $v($fname) $v($lname) $email student $id $section]
			}
		} errStat ]
	iferror $err $errStat
}

proc foreignUsername2local { uname } {
	return "$uname@email.wm.edu"
}

######################### START of the execution #########################
set dryrun true

set commonInfoCol {{First Name} {Last Name} {Student ID} {Last Access} Username Availability }
set infoColumsMarkers {{ - Lateness \(H:M:S\)} { - Max Points} { - Submission Time} {Total Lateness \(H:M:S\)} {Current Weighted} {Total \[} }

set skipCreationCol [concat $commonInfoCol $infoColumsMarkers]

addStudentsFromDB bdb

# now for every user add their grades
set username_list [getBlackboardUsernames bdb]
foreach col [getColListFromAnyTable bdb export_table] {
	if { [isInList $col $skipCreationCol] } {
		puts "skipping column $col"
		continue
	}
	set colInfo  [trimColName $col]
	set shortCol [lindex $colInfo 0]
	set category [lindex $colInfo 1]
	set maxScore [lindex $colInfo 2]
	set scoreType [lindex $colInfo 3]
	if { $category ne "FinalExam" } {
		puts "skipping non FinalExam column: $col"
		continue
	}
	if { ![doesColumnExists $shortCol GradesTable] } {
		veval [list AddColumnNonWeb $shortCol $category $maxScore]
	}
	foreach uname $username_list {
		set locUname [foreignUsername2local $uname]
		set grade [getBlackboardUserGrade bdb $uname $col ]
		if { "" ne $grade } {
			if { $scoreType eq "Percentage" } {
				# change percentage to score
				set grade [ expr {$grade/100*$maxScore} ]
			}
			veval [list UpdateColValue4UserNameNonWeb $shortCol $locUname $grade]
		}
	}
}