#!/usr/bin/perl -w

use warnings;
use strict;
use POSIX qw(strftime);

#db info
our $ROOT_HOST = 'localhost';
our $ROOT_USER = 'root';
our $ROOT_PASS = '************';

#flyspray info
our $FS_DB = 'ambugs';
our $FS_PFX = 'flyspray_';
our $FS_ATTACH = '/home/flyspray/attachments';

#bugzilla info
our $BZ_DB = 'bugzilla';
our $BZ_DEFAULT_ASSIGNEE = 791;		#userid
our $BZ_NEEDSINPUT_KEYWORD = 4;		#keyword for "needsinput"

#no need to edit below
use DBI;
my $dsn = "DBI:mysql:$FS_DB:$ROOT_HOST";
our $dbh = DBI->connect($dsn, $ROOT_USER, $ROOT_PASS);

our ($last_insert_id, $mvtab_add, $mvtab_find);
$last_insert_id = $dbh->prepare("SELECT LAST_INSERT_ID()");
$mvtab_add = $dbh->prepare("REPLACE INTO $FS_DB.mvtab (type, old_id, new_id) VALUES (?, ?, ?)");
$mvtab_find = $dbh->prepare("SELECT new_id FROM $FS_DB.mvtab WHERE old_id = ? AND type = ?");

populate_fs_users();

#first param is flyspray project id
#second param is bz project id
#third param is default component if no matching category is found
copy_bugs(5, 5, 9);			#SourceMod
copy_bugs(4, 6, 22);		#Metamod:Source
copy_bugs(2, 7, 25);		#AMX Mod X

sub copy_bug_ccs
{
	my ($find, $bz_add, $old_id, $new_id) = (@_);
	my ($user_id, $bz_userid);

	$find->execute($old_id);
	while ( ($user_id) = $find->fetchrow_array() )
	{
		$bz_userid = find_user($user_id);
		next if (!$bz_userid);
		$bz_add->execute($new_id, $bz_userid);
	}
}

sub copy_bug_comments
{
	my ($fs_find, $bz_add, $fs_bug, $bz_bug) = (@_);
	my ($fs_com_id, $fs_userid, $fs_date, $fs_text);
	my ($bz_com_id, $bz_userid, $bz_date);

	$fs_find->execute($fs_bug);
	while ( ($fs_com_id, $fs_userid, $fs_date, $fs_text) = $fs_find->fetchrow_array() )
	{
		$mvtab_find->execute($fs_com_id, 'comment');
		next if ( ($bz_com_id) = $mvtab_find->fetchrow_array() );

		$bz_userid = find_user($fs_userid);
		if (!$bz_userid)
		{
			$bz_userid = $BZ_DEFAULT_ASSIGNEE;
		}

		$bz_date = unixtime_to_mysql($fs_date);
		$bz_add->execute($bz_bug, $bz_userid, $bz_date, $fs_text);
		$bz_com_id = ins_id();
		$mvtab_add->execute('comment', $fs_com_id, $bz_com_id);
	}
}

sub copy_bug_attachments
{
	my ($fs_find, $bz_add, $bz_add_data, $fs_bug, $bz_bug) = (@_);
	my ($fs_attach, $fs_size, $fs_data);
	my ($bz_date, $bz_userid, $bz_attachid);

	$fs_find->execute($fs_bug);
	while ($fs_attach = $fs_find->fetchrow_hashref())
	{
		next unless (-f $FS_ATTACH . '/' . $fs_attach->{file_name});
		$mvtab_find->execute($fs_attach->{attachment_id}, 'attach');
		next if ( ($bz_attachid) = $mvtab_find->fetchrow_array() );
		$bz_userid = find_user($fs_attach->{added_by});
		if (!$bz_userid)
		{
			$bz_userid = $BZ_DEFAULT_ASSIGNEE;
		}
		$bz_date = unixtime_to_mysql($fs_attach->{date_added});
		$bz_add->execute(
			$bz_bug,
			$bz_date,
			$bz_date,
			$fs_attach->{orig_name},
			$fs_attach->{file_type},
			$fs_attach->{orig_name},
			$bz_userid);
		$bz_attachid = ins_id();

		$fs_size = -s $FS_ATTACH . '/' . $fs_attach->{file_name};
		open(FILE, $FS_ATTACH . '/' . $fs_attach->{file_name});
		binmode(FILE);
		$fs_data = '';
		read(FILE, $fs_data, $fs_size);
		$bz_add_data->execute($bz_attachid, $fs_data);
		$mvtab_add->execute('attach', $fs_attach->{attachment_id}, $bz_attachid);
	}
}

sub copy_bugs
{
	my ($fs_proj, $bz_proj, $bz_def_com) = (@_);
	my ($fs_task);

	my ($sql_fs_find_bugs);
	my ($sql_bz_add_bug);
	my ($sql_bz_find_com);
	my ($sql_fs_com_name);
	my ($sql_bz_add_comment);
	my ($sql_fs_find_ccs);
	my ($sql_bz_add_ccs);
	my ($sql_fs_find_comments);
	my ($sql_fs_find_attachments);
	my ($sql_bz_add_attach);
	my ($sql_bz_add_data);
	my ($sql_bz_needs_input);

	$sql_bz_add_attach = $dbh->prepare(
		"INSERT INTO $BZ_DB.attachments (bug_id, creation_ts, modification_time, description, " .
		"mimetype, filename, submitter_id) VALUES (?, ?, ?, ?, ?, ?, ?)");
	$sql_bz_add_data = $dbh->prepare("INSERT INTO $BZ_DB.attach_data (id, thedata) VALUES (?, ?)");
	$sql_fs_find_attachments = $dbh->prepare(
		"SELECT attachment_id, orig_name, file_name, file_type, added_by, date_added " .
		"FROM $FS_DB.flyspray_attachments WHERE task_id = ?");
	$sql_fs_find_comments = $dbh->prepare("SELECT comment_id, user_id, " .
		"date_added, comment_text FROM $FS_DB.flyspray_comments WHERE task_id = ?");
	$sql_fs_find_bugs = $dbh->prepare("SELECT task_id, project_id, task_priority, " .
		"date_opened, opened_by, is_closed, date_closed, closed_by, task_severity, " .
		"item_summary, detailed_desc, item_status, resolution_reason, product_category, " . 
		"operating_system, last_edited_time " .
		"FROM $FS_DB.flyspray_tasks WHERE project_id = $fs_proj");
	$sql_bz_add_bug = $dbh->prepare(
		"INSERT INTO $BZ_DB.bugs (assigned_to, bug_severity, bug_status, creation_ts, delta_ts, " .
		"op_sys, priority, short_desc, product_id, rep_platform, reporter, version, " .
		"component_id, resolution, target_milestone, lastdiffed, everconfirmed, reporter_accessible, " .
		"cclist_accessible) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
	$sql_bz_find_com = $dbh->prepare("SELECT id FROM $BZ_DB.components WHERE name = ? AND product_id = $bz_proj");
	$sql_fs_com_name = $dbh->prepare("SELECT category_name FROM $FS_DB.flyspray_list_category WHERE category_id = ?");
	$sql_bz_add_comment = $dbh->prepare(
		"INSERT INTO $BZ_DB.longdescs (bug_id, who, bug_when, thetext) VALUES(?, ?, ?, ?)");
	$sql_fs_find_ccs = $dbh->prepare("SELECT user_id FROM $FS_DB.flyspray_notifications WHERE task_id = ?");
	$sql_bz_add_ccs = $dbh->prepare("REPLACE INTO $BZ_DB.cc (bug_id, who) VALUES (?, ?)");
	$sql_bz_needs_input = 
		$dbh->prepare("REPLACE INTO $BZ_DB.keywords (bug_id, keywordid) VALUES (?, $BZ_NEEDSINPUT_KEYWORD)");

	my ($bz_severity, $bz_status, $bz_date, $bz_op_sys, $bz_priority, $bz_confirmed);
	my ($bz_short_desc, $bz_platform, $bz_reporter, $bz_com, $bz_resolution, $bz_milestone);
	my ($bz_lastdiff);
	my (@fs_severities, @fs_statuses, @fs_priorities, @fs_closures);

	@fs_severities = ('enhancement', 'minor', 'normal', 'major', 'critical');
	@fs_statuses = ('UNCONFIRMED', 'NEW', 'ASSIGNED', 'UNCONFIRMED', 'UNCONFIRMED', 'UNCONFIRMED', 'VERIFIED', 'VERIFIED');
	@fs_priorities = ('P5', 'P4', 'P3', 'P2', 'P1', 'P1');
	@fs_closures = ('INVALID', 
					'WONTFIX', 
					'WONTFIX', 
					'WORKSFORME', 
					'WONTFIX', 
					'DUPLICATE', 
					'FIXED', 
					'FIXED',
					'INCOMPLETE',
					'WONTFIX',
					'WORKSFORME');
	
	$sql_fs_find_bugs->execute();
	while ($fs_task = $sql_fs_find_bugs->fetchrow_hashref())
	{
		#See if this bug is already entered
		my ($bz_id, $fs_com);

		$mvtab_find->execute($fs_task->{task_id}, 'bug');
		if ( ($bz_id) = $mvtab_find->fetchrow_array() )
		{
			copy_bug_ccs($sql_fs_find_ccs, $sql_bz_add_ccs, $fs_task->{task_id}, $bz_id);
			copy_bug_comments($sql_fs_find_comments, $sql_bz_add_comment, $fs_task->{task_id}, $bz_id);
			copy_bug_attachments(
				$sql_fs_find_attachments, 
				$sql_bz_add_attach,
				$sql_bz_add_data,
				$fs_task->{task_id},
				$bz_id);
			next;
		}

		$bz_severity = $fs_severities[int($fs_task->{task_severity}) - 1];
		$bz_status = $fs_statuses[int($fs_task->{item_status}) - 1];
		$bz_date = unixtime_to_mysql($fs_task->{date_opened});
		$bz_op_sys = $fs_task->{operating_system};
		$bz_priority = $fs_priorities[int($fs_task->{task_priority}) - 1];
		$bz_short_desc = $fs_task->{item_summary};
		$bz_platform = 'PC';
		$bz_reporter = find_user($fs_task->{opened_by});
		
		if (!$bz_reporter)
		{
			$bz_reporter = $BZ_DEFAULT_ASSIGNEE;
		}

		$sql_fs_com_name->execute($fs_task->{product_category});
		($fs_com) = $sql_fs_com_name->fetchrow_array();
		$sql_bz_find_com->execute($fs_com);
		$bz_com = $bz_def_com unless ( ($bz_com) = $sql_bz_find_com->fetchrow_array() );

		if ($fs_task->{is_closed} == 1)
		{
			$bz_resolution = $fs_closures[int($fs_task->{resolution_reason}) - 1];
			if (!$bz_resolution)
			{
				$bz_resolution = 'FIXED';
			}
			$bz_status = 'RESOLVED';
		}
		else
		{
			$bz_resolution = '';
		}

		$bz_milestone = '---';

		if ($bz_status eq 'VERIFIED')
		{
			$bz_confirmed = 1;
		}
		else
		{
			$bz_confirmed = 0;
		}

		if ($fs_task->{last_edited_time} == 0)
		{
			$bz_lastdiff = unixtime_to_mysql($fs_task->{date_opened}),
		}
		else
		{
			$bz_lastdiff = unixtime_to_mysql($fs_task->{last_edited_time}),
		}

		$sql_bz_add_bug->execute(
			$BZ_DEFAULT_ASSIGNEE,
			$bz_severity,
			$bz_status,
			$bz_date,
			$bz_date,
			$bz_op_sys,
			$bz_priority,
			$bz_short_desc,
			$bz_proj,
			$bz_platform,
			$bz_reporter,
			'trunk',
			$bz_com,
			$bz_resolution,
			$bz_milestone,
			$bz_lastdiff,
			$bz_confirmed,
			1,
			1);
		$bz_id = ins_id();

		if ($fs_task->{item_status} == 5)
		{
			$sql_bz_needs_input->execute($bz_id);
		}

		$sql_bz_add_comment->execute($bz_id, $bz_reporter, $bz_date, $fs_task->{detailed_desc});
		
		$mvtab_add->execute('bug', $fs_task->{task_id}, $bz_id);

		copy_bug_ccs($sql_fs_find_ccs, $sql_bz_add_ccs, $fs_task->{task_id}, $bz_id);
		copy_bug_comments($sql_fs_find_comments, $sql_bz_add_comment, $fs_task->{task_id}, $bz_id);
		copy_bug_attachments(
			$sql_fs_find_attachments, 
			$sql_bz_add_attach,
			$sql_bz_add_data,
			$fs_task->{task_id},
			$bz_id);
	}
	$sql_bz_add_comment->finish();
	$sql_bz_add_bug->finish();
	$sql_fs_com_name->finish();
	$sql_bz_find_com->finish();
	$sql_fs_find_bugs->finish();
	$sql_fs_find_ccs->finish();
	$sql_bz_add_ccs->finish();
	$sql_fs_find_comments->finish();
	$sql_fs_find_attachments->finish();
	$sql_bz_add_attach->finish();
	$sql_bz_add_data->finish();
	$sql_bz_needs_input->finish();
}

sub populate_fs_users
{
	my ($sth, $vb_find_user, $bz_add_user, $bz_find_user);
	my ($user_id, $user_name, $real_name);
	my ($vb_userid, $vb_email, $bz_userid);

	$sth = $dbh->prepare("SELECT user_id, user_name, real_name FROM $FS_DB.$FS_PFX"."users");
	$vb_find_user = $dbh->prepare("SELECT userid, email FROM am.vb_user WHERE username = ?");
	$bz_find_user = $dbh->prepare("SELECT userid FROM $BZ_DB.profiles WHERE login_name = ?");
	$bz_add_user = $dbh->prepare("INSERT INTO $BZ_DB.profiles (login_name, realname, extern_id) VALUES (?, ?, ?)");

	#Find all Flyspray users.
	$sth->execute();
	while ( ($user_id, $user_name, $real_name) = $sth->fetchrow_array() )
	{
		#Find a matching user in vBulletin.
		$vb_find_user->execute($user_name);	
		if ( ($vb_userid, $vb_email) = $vb_find_user->fetchrow_array() )
		{
			#See if this user is already in bugzilla.
			$bz_find_user->execute($vb_email);
			if ( ($bz_userid) = $bz_find_user->fetchrow_array() )
			{
				#Re-populate just in-case.
				$mvtab_add->execute('user', $user_id, $bz_userid);	
			}
			else
			{
				$bz_add_user->execute($vb_email, $real_name, $vb_userid);
				$bz_userid = ins_id();
				$mvtab_add->execute('user', $user_id, $bz_userid);
				print "Binding ($user_id, \"$user_name\") to ($bz_userid, $vb_email)\n";
			}
		}
		else
		{
			print "Could not find \"$user_name <$real_name>\" in vBulletin users.\n";
		}
	}
	$sth->finish();
	$bz_find_user->finish();
	$bz_add_user->finish();
	$vb_find_user->finish();
}

sub unixtime_to_mysql
{
	my ($t) = (@_);

	return POSIX::strftime("%Y-%m-%d %H:%M:%S", localtime($t));
}

sub find_user
{
	my ($old_id) = (@_);
	my ($new_id);

	$mvtab_find->execute($old_id, 'user');

	($new_id) = $mvtab_find->fetchrow_array();

	return $new_id;
}

sub ins_id
{
	my ($id);

	$last_insert_id->execute();
	($id) = $last_insert_id->fetchrow_array();

	return $id;
}