# Copyright 1999-2007 Gentoo Foundation
# 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) "https://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
set mysqltcl_version 3.02

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

### Utility functions
proc map {fun list} {
	set res {}
	foreach i $list {lappend res [$fun $i]}
	return $res
}
proc lambda {argl body} {
	set name {}
	proc $name $argl $body
	return $name
}

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

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:display:bug {bug_id} {
	global botnick bugzilla
	set chan {#gentoo-bots}

	set bugreport "[bugzilla:mysql:query ${bug_id}]"
	putloglev d * "bugreport=$bugreport"

	# 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]
	}

	set platform [string tolower [lindex "${bugreport}" 3]]
	set whom [lindex [split [lindex "${bugreport}" 4] ">"] 1]
	set whom [lindex "[split $whom @]" 0]
	if { "$whom" == "bug-wranglers" || "$whom" == "security" } {
		set maintainer ""
		foreach x [lrange ${short_bugreport} 2 end] {
			if {[string first / "$x" 0] != "-1" } {
				set pkg [string trim "$x" "*:?"]
				# time to lookup maintainer or herd
				set stripmagic "./>=-1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ"
				if {[string trim "[string toupper $pkg]" "${stripmagic}" ] == "" } {
					if { "${maintainer}" == "" } {
						set maintainer "[exec ~/maintainer.py $pkg]"
					} else {
						break
					}
				}
			}
		}
		if { "${maintainer}" != "" } {
			set extra "(Reassign bug to ${maintainer} ?)"
			if { "$whom" == "security" } {
				set extra "(CC: ${maintainer} ?)"
			}
			set short_bugreport "${short_bugreport} ${extra}"
			set bugreport "${bugreport} ${extra}"
		}
		puthelp "PRIVMSG #gentoo-bugs :\[New Bug\] ${bugreport}";
	}

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

	# 04:45PM <hkBst> solar: yeah, we need emacs, xemacs, scheme, and common-lisp, not lisp

	# strait fwd mappings
	# General & Development Channels
	foreach item {embedded hardened security netmon releng devrel kernel games java eselect qa vdr cluster python guis} {
		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 mips amd64 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" == "gnome" || "$whom" == "xfce" || "$whom" == "rox" || "${whom}" == "kde" } {
		puthelp "PRIVMSG #gentoo-desktop :\[New Bug\] ${bugreport}"
		if { "$whom" == "xfce" || "$whom" == "kde" } {
			puthelp "PRIVMSG #gentoo-${whom} :\[New Bug\] ${bugreport}"
		}
	}
	if { "$whom" == "mysql-bugs" || "$whom" == "pgsql-bugs" } {
		puthelp "PRIVMSG #gentoo-db :\[New Bug\] ${bugreport}"
	}

	# 04:45PM <hkBst> solar: yeah, we need emacs, xemacs, scheme, and common-lisp, not lisp
	# <solar> hkBst: So to be clear. You say that emacs, xemacs, scheme, and common-lisp need to
	#	go to #gentoo-lisp and there is no lisp@gentoo bugzilla user
	# 05:13PM <hkBst> solar: yes
	if { "$whom" == "emacs" || "$whom" == "xemacs" || "$whom" == "scheme" || "$whom" == "common-lisp" } {
		puthelp "PRIVMSG #gentoo-lisp :\[New Bug\] ${bugreport}"
	}
	# oddball remappings
	if { "$whom" == "ppc" || "$whom" == "ppc64" || "$platform" == "ppc," || "$platform" == "ppc64," } {
		puthelp "PRIVMSG #gentoo-powerpc :\[New Bug\] ${bugreport}"
	}
	if { "$whom" == "infra-bugs"} { puthelp "PRIVMSG #gentoo-infra :${short_bugreport}"; return 0;}
	if { "$whom" == "pms-bugs"} { puthelp "PRIVMSG #gentoo-pms :${bugreport}"; return 0;}
	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" == "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] - \[FRESHMEAT\] ${short_bugreport}"
		puthelp "PRIVMSG #gentoo-devrel :\[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 { "$to" == "${from}," } {
		putlog "No reason to tell whom that they just filed a bug for themself ( $whom )"
		return 0
	}

	# Try to hunt the dev down in the channel they most recently spoke in that we are on
	foreach min {1 2 3 4 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}"
					putserv "PRIVMSG #gentoo-test :${whom}: ${bugreport} unixtime=$bugzilla(unixtime) lastbug=$bugzilla(lastbug) found=${c} ($itime < $min)"
					return 0
				}
			}
		}
	}

	# Still nothing? - make this smarter!
	#putlog "Suppressing ${whom} / ${bugreport}"
	return 0
}

bind pub - !lastbug pub:lastbug
proc pub:lastbug {nick uhost hand chan arg} {
	global bugzilla
	putserv "PRIVMSG $chan :unixtime=$bugzilla(unixtime) lastbug=$bugzilla(lastbug) bugzilla_array=[array names bugzilla]"
}

bind dcc - lastbug dcc:lastbug
proc dcc:lastbug {hand idx arg} {
	global bugzilla
	putidx $idx "unixtime=$bugzilla(unixtime) lastbug=$bugzilla(lastbug) bugzilla_array=[array names bugzilla]"
}

proc bugzilla:timer {args} {
	global botnick bugzilla

	bugzilla:killtimer bugzilla:timer
	timer 1 bugzilla:timer

	set res [bugzilla:restart_if_needed]
	if {$res == 1} {
		putloglev d * "\[bugzilla:timer\] MySQL connection timed out due to inactivity, reloaded"
	}

	set select "SELECT bug_id,UNIX_TIMESTAMP(creation_ts) AS timestamp FROM bugs WHERE UNIX_TIMESTAMP(creation_ts) > $bugzilla(unixtime) ORDER BY creation_ts ASC LIMIT 10"
	bugzilla:mysql_query $bugzilla(link) ${select}
	mysqlmap $bugzilla(link) {bug_id timestamp} {
		set bugzilla(unixtime) $timestamp
		#puts "Looking up bug_id $bug_id"
		bugzilla:display:bug $bug_id
	}


	#12:11PM <spb> solar: can i make a feature request for jeeves?
	#12:13PM <spb> solar: any chance the changed assignee messages could include the summary?
	#<solar> yeah but I'm out of town till wednesday. If I don't get to it today can you poke me after then?

	# Second multi query. Track changes in bugzilla.
	set select "SELECT bug_id,attach_id,who,bug_when,fieldid,added,removed,UNIX_TIMESTAMP(bug_when) AS timestamp FROM bugs_activity WHERE UNIX_TIMESTAMP(bug_when) > $bugzilla(lastbug) ORDER BY bug_when DESC LIMIT 10"
	bugzilla:mysql_query $bugzilla(link) ${select}
	mysqlmap $bugzilla(link) {bug_id attach_id who bug_when fieldid added removed timestamp} {
		set select "SELECT userid,login_name,realname,refreshed_when FROM profiles WHERE userid = $who LIMIT 1"
		bugzilla:mysql_query $bugzilla(link) ${select}
		mysqlmap $bugzilla(link) {userid login_name realname refreshed_when} {
			if {"${removed}" != "" } {
				set removed "Removed: ${removed};"
			}
			if {"${added}" != "" } {
				set added "${added};"
			}
			set select "SELECT description FROM fielddefs WHERE fieldid = $fieldid LIMIT 1"
			bugzilla:mysql_query $bugzilla(link) ${select}
			mysqlmap $bugzilla(link) {description} {
				#putserv "PRIVMSG #gentoo-bots :https://bugs.gentoo.org/$bug_id ${realname}; ${description}: ${added} ${removed}"

				if {"${description}" == "AssignedTo"} {
					if {"${added}" == "amd64@gentoo.org;"} {
						putserv "PRIVMSG #gentoo-amd64-dev :https://bugs.gentoo.org/$bug_id ${realname}; ${description}: ${added} ${removed}"
					}
					foreach c {embedded hardened security netmon releng devrel kernel games java eselect qa vdr cluster python lisp} {
						if {"${added}" == "${c}@gentoo.org;"} {
							putserv "PRIVMSG #gentoo-${c} :https://bugs.gentoo.org/$bug_id ${realname}; ${description}: ${added} ${removed}"
						}
					}
				}
			}
		}
		set bugzilla(lastbug) $timestamp
	}
	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
	}
	# Ignore the return for the moment
	if {[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
}

proc bugzilla:mysql:bugexists {bugnum} {
	global bugzilla

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

	# try to help ensure the database is there for us.
	if {[bugzilla:restart_if_needed] == 1} {}

	set select "SELECT COUNT(bug_id) FROM bugs WHERE bugs.bug_id=${bugnum}"
	bugzilla:mysql_query $bugzilla(link) ${select}
	mysqlmap $bugzilla(link) {count} {
		return $count;
	}
}

# 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.
	if {[bugzilla:restart_if_needed] == 1} {}

	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
	
	if {[bugzilla:restart_if_needed] == 1} {}

	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:timestamp {arg} {
	global bugzilla
	
	if {[bugzilla:restart_if_needed] == 1} {}

	if {$arg == "unixtime"} {
		set select "SELECT UNIX_TIMESTAMP(creation_ts) AS timestamp FROM bugs WHERE 1 ORDER BY bug_id DESC LIMIT 1"
	}
	if {$arg == "lastbug"} {
		set select "SELECT UNIX_TIMESTAMP(bug_when) AS timestamp FROM bugs_activity WHERE 1 ORDER BY bug_when DESC LIMIT 1"
	}
	if {$arg == "" } {
		return 0
	}
	bugzilla:mysql_query $bugzilla(link) ${select}
	mysqlmap $bugzilla(link) {timestamp} {return $timestamp}
	return 0
}

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

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

	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)"
	if {[bugzilla:restart_if_needed] == 1} {}
	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
	if {[bugzilla:restart_if_needed] == 1} {}
	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
	if {[bugzilla:restart_if_needed] == 1} {}
	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}]
}

proc bugzilla:restart_if_needed {} {
	global bugzilla
	if {[catch {mysqluse $bugzilla(link) ${bugzilla(name)}}]} {
		bugzilla:startup
		return 1
	}
	return 0
}

proc bugzilla:startup {} {
	global bugzilla
	# start the connecttion and timers if init function returns clean
	if {[bugzilla:mysql:init] == 0} {
		if {[catch {mysqluse $bugzilla(link) ${bugzilla(name)}}]} {
			putlog "[info script] can't use database!"
			catch {mysqlclose $bugzilla(link)}
		} else {
			if {[array names bugzilla lastbug] != "lastbug"} { 
				set bugzilla(lastbug) [bugzilla:mysql:timestamp "lastbug"]
				putquick "PRIVMSG #gentoo-bots initializing lastbug=$bugzilla(lastbug)"
			}
			if {[array names bugzilla unixtime] != "unixtime"} { 
				set bugzilla(unixtime) [bugzilla:mysql:timestamp "unixtime"] 
				putquick "PRIVMSG #gentoo-bots initializing unixtime=$bugzilla(unixtime)"
			}
		}
	}
}

##################
# 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}"
		} else {
			putserv "PRIVMSG ${chan} :${nick}: No matches found for $arg"
		}
	}
}

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 here 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} "
	if {[bugzilla:restart_if_needed] == 1} {}

	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 ""
	if {[bugzilla:restart_if_needed] == 1} {}

	# -----------------------------------------------------
	#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;
		}
	}
	# new
	set res {}
	set bp [lsearch -all  -regexp [string tolower "${arg}"] {bug$}]
	set bpn [map [lambda x {expr $x+1}] $bp]
	foreach i $bpn {lappend res [lindex $arg $i]}
	#set buglist [map [lambda x {string trim $x ".?,#)\]\[("}] $res]
	set buglist [map [lambda x {regsub -all {[^\d]*} $x {}}] $res]
	#putlog "bp $bp bpn $bpn buglist $buglist"
	# old
	##set bug [lindex "${arg}" [expr [lsearch -regexp [string tolower "${arg}"] {bug$}] + 1]]
	##set buglist [string trim $bug ".?,#)\]\[("]
	##putlog "buglist-old $buglist"
	foreach bug $buglist {
	    if {[bugzilla:validbug "${bug}"] == 0} {
	    	set msg [bugzilla:mysql:query "${bug}"]
	    	if {"${msg}" != ""} {
			if {$nick != "DerCorny"} {
		    		putquick "PRIVMSG ${chan} :${nick}: ${msg}"
			} else {
		    		putquick "PRIVMSG ${chan} :${msg}"
			}
	    	}
	    }
	}
 }
}

#bugzilla:startup
#bugzilla:timer