#!/usr/bin/tclsh

package require helga

puts "This script will migrate a single project's data from an install of Helga v0.2 to a local install of Helga v1.0."
puts "You must be a site administrator to perform project migration. Please enter your v1.0 Helga login information."

if { [catch hlogin out] } {
	puts $out
	exit 1
}

if { [catch "::helga::user::checkPermissions admin /" out] } {
	puts "Error: You are not a site administrator and therefore may not perform project migration. Please contact a site adminstrator to do this for you."
	exit 1
}

puts "Before we do anything, we're going to back up your v1.0 database in its current state."
set sqlFileName "$::HELGASITEROOT/tmp/$::SQLDB.backup.[clock seconds].sql"
puts -nonewline "Backing up db to $sqlFileName..."
flush stdout
exec mysqldump --user=helgaAdmin --password=$::ADMINSQLPASS $::SQLDB > $sqlFileName
puts "done."

puts "Now: the first step in the migration process is to temporarily copy your old database to your local v1.0 MySQL setup, under the database name \"helga_old\"."

if { [::helga::db::databaseExists helga_old] } {
	puts "Error: Database \"helga_old\" already exists. This may be left over from a failed migration, or maybe someone else is currently migrating an old project. This database must be removed before the script can continue."
	exit 1
}

::mysql::exec $::db "CREATE DATABASE `helga_old`"

puts "Please provide information for your old database."

puts -nonewline "Host Name: "
flush stdout
gets stdin sourceHostName
puts -nonewline "Database Name: "
flush stdout
gets stdin sourceDbName
puts -nonewline "User Name: "
flush stdout
gets stdin sourceDbUser

set cmd "exec mysqldump -h $sourceHostName -u $sourceDbUser -p $sourceDbName | mysql --user=helgaAdmin --password=$::ADMINSQLPASS helga_old"
eval $cmd

puts "Database copied. Proceeding."

puts -nonewline "Enter the name of the project you wish to migrate: "
flush stdout
gets stdin projectName

# check existing database, make sure project exists in the old db
# and does not exist in the current Helga install.
::helga::db::use helga_old
set sql "SELECT `long_name`,`fullxres`,`fullyres`,`framerate`,`show_id` FROM `shows` WHERE `show_name` = '$projectName'"
set result [::helga::db::select $sql] 
if { $result == "" } {
	puts "Error: project specified does not exist."
	exit 1
}
set projectInfo [lindex $result 0]

# db::use, when not given a db name to use, will default to the db name
# stored in the site's configuration variable SQLDB
::helga::db::use
puts -nonewline "Adding project asset to /$projectName..."
flush stdout
# hadd project will make sure the project does not exist. this will also
# perform any site-specific new project behavior
set cmd "hadd project -name /$projectName -fullname \{[lindex $projectInfo 0]\} -xres [lindex $projectInfo 2] -yres [lindex $projectInfo 3] -fps [lindex $projectInfo 4]"
eval $cmd
puts -nonewline "attributes..."
hset thumbResX 160 -name /$projectName
hset thumbResY 90 -name /$projectName
puts "done."

set oldProjectId [lindex $projectInfo 4]

set oldWebRoot "/net/anim/public_html/$projectName"
set newWebRoot "$::HELGADOCROOT/$projectName"
set newProjRoot "$::HELGASITEROOT/$projectName/wc"

# copy project files - previews/attachments from old web folder, and the rest of the folder structure from the old site
puts "The next step is to copy your old project files to an appropriate location within the 1.0 directory tree."
puts "First to be copied: web files - comment attachments, image previews, etc."
puts -nonewline "Enter the hostname on which your old project's files are stored: "
flush stdout
gets stdin hostname
if { $hostname == "localhost" || $hostname == [info hostname] } {
	set cmd "cp $oldWebRoot/images/* $oldWebRoot/videos/* $oldWebRoot/assets/* $oldWebRoot/reels/* $newWebRoot/"
	puts "Local host detected. Copying files with the following cmd:"
	puts $cmd
	eval "exec $cmd"
} else {
	puts -nonewline "Remote host detected. Enter the user name you want to use to access this host: "
	flush stdout
	gets stdin username
	set cmd "scp -r $username@$hostname:$oldWebRoot/images/* $username@$hostname:$oldWebRoot/videos/* $username@$hostname:$oldWebRoot/assets/* $username@$hostname:$oldWebRoot/reels/* $newWebRoot/"
	puts "Copying files with the following cmd:"
	puts $cmd
	puts "You will be prompted for your password several times, once as each copy operation begins."
	if { [catch {open "| $cmd 2>@stdout"} fp] } {
		puts "Error: can't open pipe for '$cmd' - $fp"
		exit 1
	}
	fconfigure $fp -buffering none
	while { [gets $fp data] >= 0 } {
		puts $data
		flush stdout
	}
	if { [catch {close $fp} errmsg] } {
		if { ![string match "child process exited abnormally" "$errmsg"] } {
			return "Error: could not close pipe for '$cmd' - $errmsg"
		}
	}
}
puts "Web files copied. Next: your project files - scene files, comp scripts, etc. Under old helga this should have been stored in /net/anim/$projectName/wc. We can either copy these files directly or, if these files are under revision control with svn, we can simply check them out from the repository."
puts -nonewline "Would you like to copy your files (c) or use svn (s)? "
flush stdout
gets stdin svnresponse
while { $svnresponse != "c" && $svnresponse != "s" } {
	puts -nonewline "Did not recognize your answer. Would you like to copy your files (c) or use svn (s)? "
	flush stdout
	gets stdin svnresponse
}
if { $svnresponse == "c" } {
	file mkdir $newProjRoot
	if { $hostname == "localhost" || $hostname == [info hostname] } {
		set cmd "cp -r /net/anim/$projectName/wc $::HELGASITEROOT/$projectName/"
		puts "Local host detected. Copying files with the following cmd:"
		puts $cmd
		eval "exec $cmd"
	} else {
		puts -nonewline "Remote host detected. Enter the user name you want to use to access this host: "
		flush stdout
		gets stdin username
		set cmd "scp -r $username@$hostname:/net/anim/$projectName/wc $::HELGASITEROOT/$projectName/"
		puts "Copying files with the following cmd:"
		puts $cmd
		if { [catch {open "| $cmd 2>@stdout"} fp] } {
			puts "Error: can't open pipe for '$cmd' - $fp"
			exit 1
		}
		fconfigure $fp -buffering none
		while { [gets $fp data] >= 0 } {
			if { [string index $data end] == "\n" } {
				puts $data
			} else {
				puts -nonewline $data
			}
			flush stdout
		}
		if { [catch {close $fp} errmsg] } {
			if { ![string match "child process exited abnormally" "$errmsg"] } {
				return "Error: could not close pipe for '$cmd' - $errmsg"
			}
		}
	}
} elseif { $svnresponse == "s" } {
	puts -nonewline "svn selected. Enter the url of your svn respository: "
	flush stdout
	gets stdin repopath
	set cmd "svn co $repopath $newProjRoot"
	puts "Checking out a new working copy with the cmd:"
	puts $cmd
	eval "exec $cmd"
}
puts "Done copying project files. Beginning to copy sequence/shot asset data."
# copy all sequences over as direct children of the project
::helga::db::use helga_old
set sql "SELECT `seq_name`,`inshow`,`seq_id` FROM `sequences` WHERE `show_id` = $oldProjectId"
set result [::helga::db::select $sql] 
foreach seqrow $result {
	::helga::db::use
	set seqname [lindex $seqrow 0]
	set inshow [lindex $seqrow 1]
	set seqid [lindex $seqrow 2]
	set fullseqname /$projectName/$seqname
	puts -nonewline "Adding sequence $seqname to /$projectName/$seqname..." 
	flush stdout
	# add a sequence asset
	hadd sequence -notasks -name $fullseqname 
	# add an inshow attribute
	hset inshow $inshow -name $fullseqname
	puts "done."
	# get all shots in the sequence and add them
	::helga::db::use helga_old
	set sql "SELECT s.`name`, s.`description`, s.`start_frame`, s.`end_frame`, s.`shot_id`, a.`assignment_id`, ap.`filename`, s.`shot_type`, s.`active_file`, s.`comp_file`, s.`maya_extras`, s.`finaled`, ssm.`shot_order` "
	append sql "FROM `shots` s, `sequence_memberships` sqm, `assignments` a, `asset_previews` ap, `show_shot_memberships` ssm "
	append sql "WHERE sqm.`seq_id` = $seqid "
	append sql "AND sqm.`shot_id` = s.`shot_id` "
	append sql "AND a.`shot_id` = s.`shot_id` "
	append sql "AND s.`preview_id` = ap.`preview_id` "
	append sql "AND ssm.`shot_id` = s.`shot_id` "
	append sql "ORDER BY s.`name`" 
	set result [::helga::db::select $sql] 
	foreach shotrow $result {
		::helga::db::use
		set shotname [lindex $shotrow 0] 
		set fullname /$projectName/$seqname/$shotname
		puts -nonewline "Adding shot $shotname to $fullname..."
		flush stdout
		set desc [lindex $shotrow 1] 
		set sframe [lindex $shotrow 2] 
		set eframe [lindex $shotrow 3] 
		set shotid [lindex $shotrow 4]
		set asgtid [lindex $shotrow 5]
		set curprev [lindex $shotrow 6]
		# add a shot asset with:
		#	description from shots.description
		set cmd "hadd shot -notasks -name $fullname"
		if { $desc != "" } {
			append cmd " -desc \{[::helga::command::addSlashes $desc]\}"
		}
		if { $sframe != "" && $eframe != "" } {
			 append cmd " -frameRange $sframe-$eframe"
		}
		eval $cmd
		puts -nonewline "attributes..."
		flush stdout
		# add a shotType attr from shots.shot_type
		hset shotType \{[lindex $shotrow 7]\} -name $fullname
		# add a mayaExtras attr from shots.maya_extras
		hset mayaExtras \{[lindex $shotrow 10]\} -name $fullname
		# add a finaled attr from shots.finaled
		hset finaled \{[lindex $shotrow 11]\} -name $fullname
		# add a showPosition attr from the var showPosition
		set shotOrder [lindex $shotrow 12]
		if { $shotOrder == -1 } {
			hset inShow 0 -name $fullname
			hset showPosition -1 -name $fullname
		} else {
			hset inShow 1 -name $fullname
			hset showPosition $shotOrder -name $fullname
		}
		puts -nonewline "files..."
		flush stdout
		# add a file from shots.active_file
		set scenefile [lindex $shotrow 8]
		if { $scenefile != "" } {
			set fullscenepath $newProjRoot/$seqname/$scenefile
			hadd file $fullscenepath -notasks -name $fullname/scenefile -nocomplain
		}
		# add another file from shots.comp_file
		set compfile [lindex $shotrow 9]
		if { $compfile != "" } {
			set fullcomppath $newProjRoot/$seqname/$compfile
			hadd file $fullcomppath -notasks -name $fullname/compscript -nocomplain
		}
		puts -nonewline "previews..."
		flush stdout
		# get the shot's previews, add each to <shot>/previews
		::helga::db::use helga_old
		set sql "SELECT `filename` FROM `asset_previews` WHERE `asset_type` = 'shot' AND `asset_id` = $shotid"
		set previewresult [::helga::db::select $sql]
		::helga::db::use
		foreach row $previewresult {
			set prevname [lindex $row 0]
			set newPath $newWebRoot/$prevname
			if { $prevname == $curprev } {
				hadd preview $newPath -deleteoriginal -setascurrent
			} else {
				hadd preview $newPath -deleteoriginal
			}
		}
		# add a currentPreview attr from shots.preview_id
		# add an attachments folder
		hadd folder -notasks -name $fullname/attachments -memberTypes attachment
		# get the shot's comments, add each as a log entry
		puts -nonewline "log entries..."
		flush stdout
		::helga::db::use helga_old
		set sql "SELECT u.`username`, a.`comment_date`, a.`comment`, a.`asset` FROM `assignment_comments` a, `users` u WHERE a.`assignment_id` = $asgtid AND a.`user_id` = u.`user_id`"
		set commentresult [::helga::db::select $sql]
		::helga::db::use
		foreach row $commentresult {
			set cmd "hadd logEntry -name $fullname"
			set logtext [lindex $row 2]
			if { $logtext != "" } {
				if { [string index $logtext 0] == "-" } {
					set logtext [string range $logtext 1 end]
				}
				append cmd " -text \{[::helga::command::addSlashes $logtext]\}"
			}
			append cmd " -date [lindex $row 1] -user [lindex $row 0]"
			set att [lindex $row 3]
			if { $att != "" } {
				set attpath $newWebRoot/$att
				append cmd " -attachfile $attpath"
			}
			eval $cmd
		}
		# add the shot's assignment_targets to asset_watch
		puts -nonewline "watch list..."
		flush stdout
		::helga::db::use helga_old
		set sql "SELECT u.`username` FROM `assignment_targets` a, `users` u WHERE a.`assignment_id` = $asgtid AND a.`target_type` = 'user' AND a.`target_id` = u.`user_id`"
		set watchresult [::helga::db::select $sql]
		::helga::db::use
		foreach row $watchresult {
			hwatch -user [lindex $row 0] -name $fullname
		}
		# add all the shot's tasks as tasks, link them to each other w/ data from task_links
		puts -nonewline "tasks..."
		flush stdout
		::helga::db::use helga_old
		set sql "SELECT ac.`category`, t.`status`, t.`task_id` FROM `tasks` t, `assignment_categories` ac WHERE t.`assignment_id` = $asgtid AND t.`category_id` = ac.`category_id`"
		set taskresult [::helga::db::select $sql]
		set idMap {}
		foreach taskrow $taskresult {
			::helga::db::use
			set cat [lindex $taskrow 0]
			set oldstatus [lindex $taskrow 1]
			set oldtaskid [lindex $taskrow 2]
			set newtask [hadd task -asset $fullname -desc "$cat" -dontlink]
			if { $oldstatus == "unassigned" } {
				set newstatus "notstarted"
			} elseif { $oldstatus == "inprogress" } {
				set newstatus "inprogress"
			} else {
				set newstatus "complete"
			}
			hset status $newstatus -name $newtask
			lappend idMap [list $oldtaskid $newtask]
			::helga::db::use helga_old
			set sql "SELECT u.`username` FROM `task_user_memberships` tum, `users` u WHERE tum.`task_id` = $oldtaskid AND tum.`user_id` = u.`user_id`"
			set assignresult [::helga::db::select $sql]
			::helga::db::use
			foreach assignrow $assignresult {
				set uname [lindex $assignrow 0]
				hassign -task $newtask -user $uname
			}
		}
		foreach taskrow $taskresult {
			::helga::db::use helga_old
			set oldtaskid [lindex $taskrow 2]
			# use the idMap to find the name of this task
			set sourcename ""
			foreach idpair $idMap {
				if { [lindex $idpair 0] == $oldtaskid } {
					set sourcename [lindex $idpair 1]
					break
				}
			}
			if { $sourcename == "" } {
				continue
			}
			# get all outputs of the old task, find the matching ids, and add new links
			set sql "SELECT `dest_id` FROM `task_links` WHERE `source_id` = $oldtaskid"
			set linkresult [::helga::db::select $sql]
			::helga::db::use
			foreach linkrow $linkresult {
				set destid [lindex $linkrow 0]
				foreach idpair $idMap {
					if { [lindex $idpair 0] == $destid } {
						set destname [lindex $idpair 1]
						catch "hadd link $sourcename $destname"
						break
					}
				}
			}
		}

		
		puts "done."
	}
}

# add a models folder and add all models to it
puts "Copying model asset data."
::helga::db::use 
puts -nonewline "Adding /$projectName/models..."
flush stdout
hadd folder -name /$projectName/models
puts "done."
::helga::db::use helga_old
set sql "SELECT `name`,`type`,`description`,`in_show`,`model_id`,`preview_id` FROM `models` WHERE `show_id` = $oldProjectId"
set result [::helga::db::select $sql] 
::helga::db::use 
foreach modelrow $result {
	set oldBase [lindex $modelrow 0]
	regsub -all {[^a-zA-Z0-9]} $oldBase _ newBase
	regsub -all {__} $newBase _ newBase
	while { [string index $newBase end] == "_" } {
		set newBase [string range $newBase 0 end-1]
	}
	set fullModelName /$projectName/models/$newBase
	puts -nonewline "Adding model $fullModelName..."
	flush stdout
	set cmd "hadd model -name $fullModelName"
	set desc [lindex $modelrow 2]
	if { $desc != "" } {
		append cmd " -desc \{[::helga::command::addSlashes $desc]\}"
	}
	eval $cmd
	# migrate attrs type, description, in_show
	puts -nonewline "attributes..."
	flush stdout
	hset modelType [lindex $modelrow 1] -name $fullModelName
	hset inShow [lindex $modelrow 3] -name $fullModelName
	# migrate assoc. previews
	puts -nonewline "previews..."
	flush stdout
	::helga::db::use helga_old
	set sql "SELECT `filename` FROM `asset_previews` WHERE `asset_type` = 'model' AND `asset_id` = [lindex $modelrow 4]"
	set previewresult [::helga::db::select $sql]
	::helga::db::use
	set curPreviewName [lindex $modelrow 5]
	foreach row $previewresult {
		set prevname [lindex $row 0]
		set newPath $newWebRoot/$prevname
		if { $prevname == $curPreviewName } {
			hadd preview $newPath -deleteoriginal -setascurrent
		} else {
			hadd preview $newPath -deleteoriginal
		}
	}
	puts "done."
}

# add a videos folder and add videos from the videos table to it
puts "Copying video asset data."
::helga::db::use 
puts -nonewline "Adding /$projectName/videos..."
flush stdout
hadd folder -name /$projectName/videos
puts "done."
::helga::db::use helga_old
set sql "SELECT `filename`,`label` FROM `videos` WHERE `show_id` = $oldProjectId"
set result [::helga::db::select $sql] 
::helga::db::use 
foreach videorow $result {
	set newFullName /$projectName/videos/[lindex $videorow 0]
	puts -nonewline "Adding video $newFullName..."
	flush stdout
	hadd file $newWebRoot/[lindex $videorow 0] -name $newFullName -desc \{[::helga::command::addSlashes [lindex $videorow 1]]\} -nocomplain
	puts "done."
}

# remove the temporary db we made
puts -nonewline "Removing temporary database..."
flush stdout
::helga::db::use
::mysql::exec $::db "DROP DATABASE `helga_old`"
puts "done."

puts "Migration complete! Log in to Helga with your v1.0 username/pass and make sure the migration produced the expected results."