# Copyright 1999-2004 Gentoo Technologies, Inc.
# Distributed under the terms of the GNU General Public License v2
# $Header: $

# Started Nov 2003 - as a gentoo-infrastructure bot - <solar@gentoo.org>

## this data can be sourced in from secret_mysql.tcl if it exists.
## you dont want to store info.
set bugzilla(user) "";		# user to attach to the MySQL database
set bugzilla(pass) "";		# database password
set bugzilla(name) "";		# name of the MySQL database
set bugzilla(host) "";		# where is the database?
set bugzilla(port) 3306;	# which port to use

if {[file exists ~/gentoo/secret_mysql.tcl] == 1} { source ~/gentoo/secret_mysql.tcl }

## baseurl of your bugzilla website without a trailing /
set bugzilla(baseurl) "http://bugs.gentoo.org"

## Load the mysqltcl library	# mysqltcl install's is braindead so note to self ask robbat2 to fix.
set mysqltcl_version 2.14
set mysqltcl_version 2.40
#set mysqltcl_version 2.50
load /usr/lib/mysqltcl-${mysqltcl_version}/libmysqltcl${mysqltcl_version}.so
# load /usr/lib/libmysqltcl.so

## You can define nicks that should be ignored when *bug* is matched. note to self. 
## just make it ignore all together to avoid wars 
set bugzilla(ignore_nicks) {GenBot p2 glbt}

if {[array names bugzilla bugwatch_channels] == ""} { set bugzilla(bugwatch_channels) {#gentoo-bots} }

set tcl_traceExec 1

###########################################################################################

proc bugzilla:validbug {num} {
	if {$num == ""} { return 1 }
	if {[string trim $num #1234567890] == ""} {return 0}
	return 1
}

proc bugzilla:killtimer {name} {
    foreach timer [timers] {
        if {[lindex $timer 1] == "${name}"} {
            killtimer [lindex $timer 2]
        }
    }
}

proc bugzilla:mysql_query {link args} {
	set sql_log_level 8

	set select "[join $args]"
	catch {
		set fp [open ~/sql.log a]
		puts $fp "${select}"
		close $fp
	}
	putloglev d * "\[MYSQLSQL\] ${select}"
	return [mysqlsel ${link} "${select}"]
}

proc bugzilla:status {link} {
	return [mysqlsel ${link} "status"]
}

proc bugzilla:timer {args} {
	global botnick bugzilla

	bugzilla:killtimer bugzilla:timer
	timer 1 bugzilla:timer

	if {[catch {mysqluse $bugzilla(link) ${bugzilla(name)}}]} {}
	set chan {#gentoo-bots}
	set count [bugzilla:mysql:count]
	set newbugs [expr $count - $bugzilla(lastbug)]
	if {($newbugs < 1 )} {
		return 0;
	}
	while {$bugzilla(lastbug) != $count} {
		incr bugzilla(lastbug)
		set bugreport "[bugzilla:mysql:query [expr $bugzilla(lastbug) + 1]]"

		# crap crap... Is this line of text working? 
		# lets change it from PRIVMSG/to a NOTICE as a test..
		# - test worked. Bug #54499

		set str "${bugreport}"
		set short_bugreport "[lindex $str 0] [lindex $str 4] [lrange $str 7 end]"
		set short_bugreport "[join $short_bugreport]"

		# try this fulgy hack.
		set str [join [split [lindex $str 4] "->"]]
		set from [lindex $str 0]
		set to [lindex $str 1]
		set to_dom [lindex [split $to "@"] 1]
		set from_dom [lindex [split $from "@"] 1]
		if {"${to_dom}" == "@gentoo"} {
			set to [lindex [split $to "@"] 0]
		}
		if {"${from_dom}" == "@gentoo"} {
			set from_dom [lindex [split $to "@"] 0]
		}

		putquick "PRIVMSG #gentoo-bots :${short_bugreport}"

		set platform [string tolower [lindex "${bugreport}" 3]]
		set whom [lindex [split [lindex "${bugreport}" 4] ">"] 1]
		set whom [lindex "[split $whom @]" 0]

		# strait fwd mappings
		# General & Development Channels
		foreach item {embedded hardened security netmon releng devrel kernel games java eselect qa} {
			if {"$whom" == "${item}"} {
				puthelp "PRIVMSG #gentoo-${whom} :\[New Bug\] ${bugreport}"
				return 0
			}
		}
		
		if { "$whom" == "amd64" || "${platform}" == "amd64,"} {
			puthelp "PRIVMSG #gentoo-amd64-dev :${short_bugreport}"
			# Dont return for this mapping. It goes to two channels total.
		}

		# Platform Specific/support channels
		foreach item {hppa sparc ppc mips amd64 ppc64 bsd alpha arm alt} {
			if { "${whom}" == "${item}" || "${platform}" == "${whom}," } {
				puthelp "PRIVMSG #gentoo-${whom} :\[New Bug\] ${bugreport}"
				return 0
			}
		}
		if { "$whom" == "x11" || "$whom" == "x11-drivers" || "$whom" == "kde" || "$whom" == "gnome" || "$whom" == "xfce" } {
			puthelp "PRIVMSG #gentoo-desktop :\[New Bug\] ${bugreport}"
		}
		# oddball remappings
		if { "$whom" == "php-bugs"} { puthelp "PRIVMSG #gentoo-php :\[New Bug\] ${bugreport}"; return 0;}
		if { "$whom" == "gli-bugs"} { puthelp "PRIVMSG #gentoo-installer :\[New Bug\] ${bugreport}"; return 0;}
		if { "$whom" == "bug-wranglers"} { puthelp "PRIVMSG #gentoo-bugs :\[New Bug\] ${bugreport}"; return 0;}
		if { "$whom" == "dev-portage"} { puthelp "PRIVMSG #gentoo-portage :\[New Bug\] ${bugreport}"; return 0; }
		if { "$whom" == "tools-portage"} { puthelp "PRIVMSG #gentoo-portage :\[New Bug\] ${bugreport}"; return 0; }
		if { "$whom" == "mirror-admin"} { puthelp "PRIVMSG #gentoo-mirrors :\[New Bug\] ${bugreport}"; return 0; }
		if { "$whom" == "apache-bugs"} { puthelp "PRIVMSG #gentoo-apache :\[New Bug\] ${bugreport}";  return 0; }
		if { "$whom" == "net-mail"} { puthelp "NOTICE #gentoo-netmail :[unixtime] - \[New Bug\] ${bugreport}"; return 0; }
		if { "$whom" == "toolchain"} { puthelp "NOTICE #gentoo-base :[unixtime] - \[New Bug\] ${bugreport}"; return 0; }
		if { "$whom" == "base-system"} { puthelp "NOTICE #gentoo-base :[unixtime] - \[New Bug\] ${bugreport}"; return 0; }
		if { "$whom" == "ppc-macos"} { puthelp "NOTICE #gentoo-osx :[unixtime] - \[New Bug\] ${bugreport}"; return 0; }
		if { "$whom" == "recruiters"} {
			# I want to know about these.
			puthelp "NOTICE solar :[unixtime] - \[New Bug\] ${short_bugreport}"
			puthelp "PRIVMSG #gentoo-devrel :[unixtime] - \[New Bug\] ${short_bugreport}"
			return 0;
		}
		if { "$whom" == "docs-team"} { puthelp "NOTICE #gentoo-doc :[unixtime] - \[New Bug\] ${bugreport}"; return 0; }
		if { "$whom" == "web-apps"} { puthelp "NOTICE #gentoo-web :[unixtime] - \[New Bug\] ${bugreport}"; return 0; }
		if { "$whom" == "release"} { puthelp "NOTICE #gentoo-releng :[unixtime] - \[New Bug\] ${bugreport}"; return 0; }
		if { "$whom" == "crypto" || "$whom" == "forensics"} {
			puthelp "PRIVMSG #gentoo-netmon :\[New Bug\] ${bugreport}"
			return 0
		}
		#if {[onchan "${whom}" "#gentoo-dev"]} {
		#	set itime [getchanidle "${whom}" "#gentoo-dev"]
		#	if {($itime < 60)} {
		#		putserv "PRIVMSG #gentoo-dev :${whom}: bug #[expr $bugzilla(lastbug) + 1] has been filed for you."
		#		return 0;
		#	}
		#}

		#putlog "TO=FROM=WHOM / $to / $from / $whom /"
		if { "$to" == "${from}," } {
			putlog "No reason to tell whom that they just filed a bug for themself ( $whom )"
			return 0
		}
		if { "$whom" == "seemant"} {
			putserv "PRIVMSG ${whom} :Stop slacking and fix this. ${bugreport}"
			return 0;
		}
		# Try to hunt the dev down in the channel they most recently spoke in that we are on
		foreach min {1 5 10 30 60} {
			foreach c [channels] {
				if {[onchan "${whom}" "$c"]} {
					set itime [getchanidle "${whom}" "${c}"]
					if {($itime < $min)} {
						putlog "PRIVMSG ${c} :${whom}: (itime ${itime}) ${bugreport}"
						putserv "PRIVMSG $c :${whom}: ${bugreport}"
						return 0
					}
				}
			}
		}
		# Still nothing? - make this smarter!
		#putlog "Suppressing ${whom} / ${bugreport}"
	}
	return 0
}

proc bugzilla:mysql:init {} {
	global bugzilla

	if {[info commands mysqlconnect] == ""} {
		putlog "[info script] needs libmysqltcl.so - not installed!"
		return 1
	}
	catch {mysqlclose $bugzilla(link)}
	if {[catch {set bugzilla(link) [mysqlconnect -port $bugzilla(port) -user $bugzilla(user) -password $bugzilla(pass) -host $bugzilla(host) ]}]} {
		putlog "[info script] can't connect to a mysql server at $bugzilla(host) at $bugzilla(port)";
		return 1
	}
	catch {mysqluse $bugzilla(link) "${bugzilla(name)}"}
	# what a crock of shit.. This shit worked 100% fine then started segfaulting one day.
	bind pub -|- !bug	pub:bugzilla
	#bind pub -|- !bugs	pub:bugzilla:user
	bind pub -|- !bugsearch	pub:bugzilla:dbsearch
	bind pub -|- !archstat pub:bugzilla:arch:stats

	bind pub -|- !bugstats	pub:bugzilla:dbstats

	bind pubm -|- "*bug*"	pub:bugzilla:chanbug

	bind dcc -|- bugstats	dcc:bugzilla:dbstats
	bind dcc -|- bugsearch	dcc:bugzilla:dbsearch
	bind dcc -|- bug	dcc:bugzilla

	return 0
}

# fetch bug info by bug number
proc bugzilla:mysql:query {bugnum} {
	global bugzilla

	set bugnum [string trim $bugnum #]
	if {[string trim $bugnum 1234567890] != ""} {return ""}

	# try to help ensure the database is there for us.
	catch {mysqluse $bugzilla(link) "${bugzilla(name)}"}

	set select "SELECT profiles.login_name,bugs.bug_id,short_desc,priority,resolution,rep_platform,bug_status,bug_severity,profilesb.login_name AS reporter FROM bugs LEFT JOIN profiles ON bugs.assigned_to = profiles.userid LEFT JOIN profiles AS profilesb ON bugs.reporter = profilesb.userid LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id WHERE bugs.bug_id=${bugnum} AND (bug_group_map.group_id IS NULL OR bug_group_map.group_id!=24)"
	bugzilla:mysql_query $bugzilla(link) ${select}
	mysqlmap $bugzilla(link) {login_name bug_id short_desc priority resolution rep_platform bug_status bug_severity reporter} {
		if {${resolution} == "" } { set resolution "pending" }
		return "$bugzilla(baseurl)/$bug_id [string range ${bug_severity} 0 2], ${priority}, ${rep_platform}, ${reporter}->${login_name}, ${bug_status}, ${resolution}, ${short_desc}"
		#return "$bugzilla(baseurl)/show_bug.cgi?id=$bug_id [string range ${bug_severity} 0 2], ${priority}, ${rep_platform}, ${reporter}->${login_name}, ${bug_status}, ${resolution}, ${short_desc}"
	}
}

proc bugzilla:mysql:count {} {
	global bugzilla

	set select "SELECT COUNT(bug_id) AS count_total FROM bugs"
	#".bugs"
	bugzilla:mysql_query $bugzilla(link) ${select}
	mysqlmap $bugzilla(link) {count_total} {return $count_total}
	return 0
}

proc bugzilla:mysql:count:type {type arg} {
	global bugzilla

	if {$arg != ""} {
		# set arg "WHERE [lindex [string trim ${arg} "%;-\\"] 0] AND"
		set arg ""
	}

	set select "SELECT COUNT(bug_id) AS count FROM bugs ${arg} WHERE bug_status='${type}'"
	bugzilla:mysql_query $bugzilla(link) ${select}
	mysqlmap $bugzilla(link) {count} {return $count}

	return 0
}

proc bugzilla:dbstats:bug_status {args} {
	set a ""
	foreach item [bugzilla:mysql:distinct:tables bug_status bugs] {
		set a "${a} ${item}([bugzilla:mysql:count:type ${item} '']),"
	}
	return [string range "${a} TOTAL([bugzilla:mysql:count])" 1 end]
}

proc bugzilla:dbsearch {arg} {
	global bugzilla
	set foo [string trim ${arg} "#%;-\\"]
	set arg [string trim "$foo"]
	if {$arg == ""} { return "Invalid Search String" }
	# Get the total number of matching bugs
	set total_matches ""
	set select "SELECT COUNT(*) FROM bugs LEFT JOIN bug_group_map ON bug_group_map.bug_id=bugs.bug_id WHERE bug_status='NEW' AND short_desc LIKE '%${arg}%' AND (bug_group_map.group_id IS NULL OR bug_group_map.group_id!=24)"
	bugzilla:mysql_query $bugzilla(link) ${select}
	mysqlmap $bugzilla(link) {count} {set total_matches "\($count matches found\) " }
	# build a list of matching bug numbers.
	set match_bug_tail ""
if {(0 == 1)} {
	# This is the segfaulting code. .bugsearch uclibc (9 matches) seems to trigger. .bugsearch apache2 did at one time but does not now.
	if {($count < 10) && ($count > 0)} {
		set match_ids ""
		set select "SELECT bugs.bug_id FROM bugs LEFT JOIN bug_group_map ON bug_group_map.bug_id=bugs.bug_id WHERE bug_status='NEW' AND short_desc LIKE '%${arg}%' AND (bug_group_map.group_id IS NULL OR bug_group_map.group_id!=24)"
		bugzilla:mysql_query $bugzilla(link) "${select}"
		# the exact segfault seems to be in this mysql mapping.
		mysqlmap $bugzilla(link) {bug_id} {
			putlog "bugzilla:dbsearch match_ids bug_id"
			if { "$bug_id" == ""} {
				putlog "bugzilla:dbsearch bug_id is busted?"
			}
			#set match_ids "${match_ids} ${bug_id} " 
		}
		regsub -all "  " "$match_ids" " " match_idsx
		set match_bug_tail "\([string trim ${match_idsx}]\)"
	}
}
	putlog "bugzilla:dbsearch Get a summary of most recent matching bug"
	# Get a summary of most recent matching bug. 
	set select "SELECT profiles.login_name,bugs.bug_id,short_desc,priority,resolution,rep_platform,bug_status,bug_severity,profilesb.login_name AS reporter FROM bugs LEFT JOIN profiles ON bugs.assigned_to = profiles.userid LEFT JOIN profiles AS profilesb ON bugs.reporter = profilesb.userid LEFT JOIN bug_group_map ON bug_group_map.bug_id=bugs.bug_id WHERE bug_status='NEW' AND short_desc LIKE '%${arg}%' AND (bug_group_map.group_id IS NULL OR bug_group_map.group_id!=24) LIMIT 1"
	bugzilla:mysql_query $bugzilla(link) ${select}
	mysqlmap $bugzilla(link) {login_name bug_id short_desc priority resolution rep_platform bug_status bug_severity reporter} {
		if {${resolution} == "" } { set resolution "pending" }
		return "${total_matches}$bugzilla(baseurl)/$bug_id [string range ${bug_severity} 0 2], ${priority}, ${rep_platform}, ${reporter}->${login_name}, ${bug_status}, ${resolution}, ${short_desc} ${match_bug_tail}"
		#return "${total_matches}$bugzilla(baseurl)/show_bug.cgi?id=$bug_id [string range ${bug_severity} 0 2], ${priority}, ${rep_platform}, ${reporter}->${login_name}, ${bug_status}, ${resolution}, ${short_desc} ${match_bug_tail}"
	}
	return "Zarro Boogs found."
}


proc bugzilla:mysql:distinct:tables {distinct table} {
	global bugzilla
	set results ""
	set select "SELECT DISTINCT ${distinct} AS result FROM ${table} WHERE ${distinct} != '' ORDER BY ${distinct}";
	bugzilla:mysql_query $bugzilla(link) ${select}
	mysqlmap $bugzilla(link) {result} {
		set results "${results} ${result}"
	}
	return ${results}
}

proc bugzilla:mysql:arch:stats {} {
	global bugzilla
	set results ""
	set select "select COUNT(*) AS 'bugs', rep_platform AS 'platform' FROM bugs GROUP BY rep_platform ORDER BY bugs DESC;";
	bugzilla:mysql_query $bugzilla(link) ${select}
	mysqlmap $bugzilla(link) {bugs platform} {
		set results "${results} ${platform}($bugs)"
	}
	return [string trim ${results}]
}

##################
# dcc bindsings
##################

proc dcc:bugzilla:dbarchstats {hand idx arg} {
	putidx $idx "[bugzilla:mysql:arch:stats]"
}
bind dcc -|- bugs dcc:bugzilla:dbarchstats

proc dcc:bugzilla {hand idx arg} {
	putidx $idx "Querying bugzilla database for $arg"
	putidx $idx "[bugzilla:mysql:query $arg]"
}

proc dcc:bugzilla:dbstats {hand idx arg} {
	putidx $idx [bugzilla:dbstats:bug_status]
}

proc dcc:bugzilla:dbsearch {hand idx arg} {
	regsub -all "\\\'" "$arg" "" argx
	regsub -all " " "$argx" "%' AND short_desc LIKE '%" arg
	set msg "[bugzilla:dbsearch ${arg}]"
	putidx $idx "${msg}"
}


##################
# public bindings
##################

proc pub:bugzilla:arch:stats {nick uhost hand chan arg} {
	putserv "PRIVMSG $chan :Platform bug totals. [bugzilla:mysql:arch:stats]"
}
proc pub:bugzilla:dbsearch {nick uhost hand chan arg} {
	if {$arg == ""} {
		putlog "Does this make me segfault?"
		return 0;
	}
	#regsub -all "\\\'" "$arg" "" argx
	#regsub -all " " "$argx" "%' AND short_desc LIKE '%" arg

	catch {
	if {$arg == ""} {
		putlog "WTF?"
		return;
	}
		set msg "[bugzilla:dbsearch ${arg}]"
		if {"${msg}" != "" } {
			putserv "PRIVMSG ${chan} :${nick}: ${msg}"
		}
	}
}

proc pub:bugzilla {nick uhost hand chan arg} {
	set msg "[bugzilla:mysql:query $arg]"
	if {"${msg}" != "" } {putquick "PRIVMSG ${chan} :${nick}: ${msg}"}
}

proc pub:bugzilla:dbstats {nick uhost hand chan arg} {
	# something in hee is causing a segfault.
	#putserv "PRIVMSG ${chan} :Not sure why but this cmd was making me segfault. I really don't like segfaulting so you have to wait till somebody can give me some love."
	return 0;
	putserv "PRIVMSG ${chan} :[bugzilla:dbstats:bug_status]"
}

proc bugzilla:dbstats:user {user} {
	global bugzilla
	set a "${user} "

	foreach item "[bugzilla:mysql:distinct:tables bug_status bugs]" {
		set select "SELECT profiles.login_name AS email, added, count( bugs_activity.bug_id ) AS bugcount FROM bugs_activity INNER JOIN profiles ON bugs_activity.who = profiles.userid WHERE profiles.login_name='${user}' AND added='${item}' GROUP BY email ORDER BY bugcount DESC LIMIT 1";
		bugzilla:mysql_query $bugzilla(link) "${select}"
		mysqlmap $bugzilla(link) {email added bugcount} {
			set a "${a} ${item}($bugcount)"
		}
	}

	#set select "SELECT profiles.login_name AS email, added, count( bugs_activity.bug_id ) AS bugcount FROM bugs_activity INNER JOIN profiles ON bugs_activity.who = profiles.userid WHERE profiles.login_name='${user}' AND added='' GROUP BY email ORDER BY bugcount DESC LIMIT 1";
	#bugzilla:mysql_query $bugzilla(link) ${select}
	#mysqlmap $bugzilla(link) {email added bugcount} { set a "${a} UNKNOWN($bugcount)" }
	#set select "SELECT profiles.login_name AS email, count( bugs_activity.bug_id ) AS bugcount FROM bugs_activity INNER JOIN profiles ON bugs_activity.who = profiles.userid WHERE profiles.login_name='${user}'  GROUP BY email ORDER BY bugcount DESC LIMIT 1";
	#bugzilla:mysql_query $bugzilla(link) "${select}"
	#mysqlmap $bugzilla(link) {email bugcount} { set a "${a} TOTAL(${bugcount})" }
	if {${a} != "${user} "} { return "${a}" } else { return ""}
}

proc pub:bugzilla:user {nick uhost hand chan arg} {
	global bugzilla

	if {"$arg" == ""} {
		putserv "PRIVMSG $chan :Usage: bugs <bugzilla-account>"
		return 1
	}
	set arg [string trim "${arg}" "%;-\\\""]
	set user [lindex "${arg}" 0]
	set domain "gentoo.org"

	set where [lindex "${arg}" 1]
	set WHERE ""

	# -----------------------------------------------------
	return 0;	## SEGFAULT ##
	# -----------------------------------------------------
	if {$where != ""} {
		set WHERE "AND added='${where}'" 
	} else {
		set msg "[bugzilla:dbstats:user ${user}@${domain}]"
		if {"${msg}" != ""} {
			putquick "PRIVMSG ${chan} :${msg}"
		} else {
			puthelp "PRIVMSG $chan :${user}@${domain} does not appear to be valid user-account or perhaps there are no bugs for this user yet. (somebody needs to give me some sql/tcl loving to make me understand virtual accounts and aliases)"
		}
		return 0
	}
	set select "SELECT profiles.login_name AS email, added, count( bugs_activity.bug_id ) AS bugcount FROM bugs_activity INNER JOIN profiles ON bugs_activity.who = profiles.userid WHERE profiles.login_name='${user}@${domain}' ${WHERE} GROUP BY email ORDER BY bugcount DESC LIMIT 1";
	# -----------------------------------------------------

	bugzilla:mysql_query $bugzilla(link) ${select}

	mysqlmap $bugzilla(link) {email added bugcount} {
		if {$bugcount != ""} {
			putquick "PRIVMSG $chan :${nick}: ${email} -> ${bugcount}"
		}
	}
}

proc pub:bugzilla:chanbug {nick uhost hand chan arg} {
	global botnick bugzilla
 catch {
	foreach u $bugzilla(ignore_nicks) {
		if {[string match [string tolower "${nick}"] [string tolower "${u}"]]} {
			return 0
		}
	}
	# If Genbot is on the same channel as us lets shutup 
	# about the bug unless we are asked directly
	set GenBot "GenBot"
	foreach u [string tolower [chanlist "$chan"]] { if {[string range $u 0 5] == "genbot"} { set GenBot $u }}
	if {[onchan $GenBot "${chan}"]} {
		if {[string range [lindex "${arg}" 0] 0 [expr [string length "${botnick}" ] - 1]] != "${botnick}" } {
			return 0
		}
	}
	
	if {"$chan" == "#gentoo-bugs"} {
		if {[lsearch [string tolower ${arg}] bug] == "-1"} {
			#putserv "PRIVMSG ${chan} :arg=$arg"
			return 0;
		}
	}
	set bug [lindex "${arg}" [expr [lsearch [string tolower "${arg}"] bug] + 1]]
	set bug [string trim $bug .]
	if {[bugzilla:validbug "${bug}"] == 0} {
		set msg [bugzilla:mysql:query "${bug}"]
		if {"${msg}" != ""} {
			putquick "PRIVMSG ${chan} :${nick}: ${msg}"
		}
	}
 }
}



# start the connecttion and timers if init function returns clean
if {[bugzilla:mysql:init] == 0} {
	if {[array names bugzilla lastbug] != "lastbug"} { set bugzilla(lastbug) [bugzilla:mysql:count] }
	if {[catch {mysqluse $bugzilla(link) ${bugzilla(name)}}]} {
		putlog "[info script] can't use database!"
		catch {mysqlclose $bugzilla(link)}
	}
}

bugzilla:timer