#!/usr/bin/perl 

use strict;
use warnings;

use Term::ProgressBar 2.00;
use DateTime::Format::MySQL;
use DateTime;
use DBI;
use Getopt::Long;
use File::Slurp;
use File::Spec;

my $attachment_dir = 'attachments';

my %conv_priority = (
	1 => 10, # Low
	2 => 20, # Normal
	3 => 30, # High
	4 => 40, # Urgent
	5 => 50, # Immediate
	6 => 60, # Flash
);
my %conv_severity = (
	1 => 20, # Very Low
	2 => 40, # Low
	3 => 50, # Medium
	4 => 60, # High
	5 => 70, # Critical
);

my %conv_resolution = (
	1 => 10, # None
	2 => 70, # Not a bug
	3 => 90, # Won't fix
	4 => 90, # Won't implement
	5 => 40, # WFM
	6 => 60, # Dupe
	7 => 80, # Deferred
	8 => 20, # Fixed
	9 => 20, # Implemented
);

my %conv_status = (
	1 => 10, # Unconfirmed
	2 => 10, # New
	3 => 50, # Assigned
	4 => 30, # Researching
	5 => 30, # Waiting on customer
	6 => 10, # Requires testing
	7 => 20, # Repoened
	8 => 90, # Closed (removed in 0.9.6)
);

my %conv_history = (
	0 => 0, # Fields changed in a task
	1 => 1, # New task created
	2 => 0, # Task closed
	3 => 6, # Task edited (for backwards compatibility with events prior to the history system)
	4 => 2, # Comment added
	5 => 3, # Comment edited
	6 => 4, # Comment deleted
	7 => 9, # Attachment added
	8 => 10, # Attachment deleted
	9 => 12, # User added to notification list
	10 => 13, # User removed from notification list
	11 => undef, # Related task added to this task
	12 => undef, # Related task removed from this task
	13 => undef, # Task re-opened
	14 => undef, # Task assigned to user / re-assigned to different user / Unassigned
	15 => undef, # This task was added to another task's related list
	16 => undef, # This task was removed from another task's related list
	17 => undef, # Reminder added
	18 => undef, # Reminder deleted
	19 => undef, # User took ownership
	20 => undef, # Closure request made
	21 => undef, # Re-opening request made
	22 => undef, # Adding a new dependency
	23 => undef, # This task added as a dependency of another task
	24 => undef, # Removing a dependency
	25 => undef, # This task removed from another task's dependency list
	26 => undef, # Task was made private
	27 => undef, # Task was made public
	28 => undef, # PM request denied

);

my $cur_step = 0;
my $max_step = 10;

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

my $verbose = 0;

GetOptions( "verbose!" => \$verbose );
my( $local_max_step, $local_cur_step );

my $progress = Term::ProgressBar->new({name  => 'Progress',
		count => $max_step*100,
		ETA   => 'linear', }
);
$progress->max_update_rate(1);

my $olddsn = 'dbi:mysql:flyspray';
my $olddbuser = '';
my $olddbpass = '';

my $newdsn = 'dbi:mysql:mantis';
my $newdbuser = '';
my $newdbpass = '';


my $olddbh = DBI->connect( $olddsn, $olddbuser, $olddbpass);
my $newdbh = DBI->connect( $newdsn, $newdbuser, $newdbpass);
$newdbh->{mysql_enable_utf8} = 1;
$newdbh->do("SET NAMES 'utf8'");

my( $newsth, $oldsth );
my( $newsth2, $oldsth2 );

# Users
$progress->message("Importing users");

$cur_step++;

$oldsth = $olddbh->prepare("SELECT DISTINCT flyspray_users.user_id, flyspray_groups.group_name, flyspray_history.event_date AS date, user_name, real_name, user_pass, email_address, account_enabled 
	FROM flyspray_users 
	LEFT JOIN flyspray_users_in_groups ON flyspray_users.user_id = flyspray_users_in_groups.user_id
	LEFT JOIN flyspray_groups ON flyspray_groups.group_id = flyspray_users_in_groups.group_id 
	LEFT JOIN flyspray_history ON flyspray_users.user_id = flyspray_history.user_id 
	GROUP BY flyspray_users.user_id");

$newsth = $newdbh->prepare("INSERT INTO mantis_user_table (id, username, realname, email, password, enabled, access_level, cookie_string, date_created) VALUES (?,?,?,?,?,?,?,?,?)");

$oldsth->execute();

$local_max_step = $oldsth->rows;
$local_cur_step = 0;

$newdbh->do("TRUNCATE mantis_user_table");
$progress->message("Truncating table mantis_user_table") if $verbose;

my %accesslevels = (
	'Admin' => 90,
	'Developers' => 55,
	'Reporters' => 25
);
my $cookieCounter = 1;
foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) {
	++$local_cur_step;
	$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
	$progress->message(sprintf "Importing user %s", $entry->{user_name}) if $verbose;
	$newsth->execute(
		$entry->{user_id},
		$entry->{user_name},
		$entry->{real_name},
		$entry->{email_address},
		$entry->{user_pass},
		$entry->{account_enabled},
		$accesslevels{$entry->{group_name}} // 10,
		'cookiestring'.++$cookieCounter,
		$entry->{date} ? DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date})) : 0
	);
}

$progress->update($cur_step*100);

# Projects
$progress->message("Importing projects");

my %projects;
$cur_step++;

$oldsth = $olddbh->prepare("SELECT project_id, project_title, project_is_active, intro_message, anon_open FROM flyspray_projects");

$newsth = $newdbh->prepare("INSERT INTO mantis_project_table (id, name, enabled, description, view_state) VALUES(?,?,?,?,?)");

$oldsth->execute();

$local_max_step = $oldsth->rows;
$local_cur_step = 0;

$newdbh->do("TRUNCATE mantis_project_table");
$progress->message("Truncating table mantis_project_table") if $verbose;

foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) {
	++$local_cur_step;
	$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
	$progress->message(sprintf "Importing project %s", $entry->{project_title}) if $verbose;
	$newsth->execute(
		$entry->{project_id},
		$entry->{project_title},
		$entry->{project_is_active},
		$entry->{intro_message},
		$entry->{anon_open} == 1 ? 10 : 50
	);
	$projects{$entry->{project_id}} = $entry->{project_title};
}

$progress->update($cur_step*100);

# Categories
$progress->message("Importing categories");

$cur_step++;

$oldsth = $olddbh->prepare("SELECT category_id, category_name, project_id, category_owner
	FROM flyspray_list_category
	");

$newsth = $newdbh->prepare("INSERT INTO mantis_category_table (project_id, name, user_id) VALUES(?,?,?)");
$newsth2 = $newdbh->prepare("INSERT IGNORE INTO mantis_project_user_list_table (project_id, user_id, access_level) VALUES(?,?,?)");

$oldsth->execute();

$local_max_step = $oldsth->rows;
$local_cur_step = 0;

$newdbh->do("TRUNCATE mantis_category_table");
$progress->message("Truncating table mantis_category_table") if $verbose;
$newdbh->do("TRUNCATE mantis_project_user_list_table");
$progress->message("Truncating table mantis_project_user_list_table") if $verbose;

my $lastProduct = -1;
my %categoryNames;
my %categorylinks;

foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) {
	if( $lastProduct != $entry->{project_id} ) {
		%categoryNames = ();
	}
	++$local_cur_step;
	$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
	$progress->message(sprintf "Importing category '%s' for project '%s'", $entry->{category_name}, $projects{$entry->{project_id}}) if $verbose;
	$newsth->execute(
		$entry->{project_id},
		exists $categoryNames{$entry->{category_name}} ? "$entry->{category_name} $entry->{category_id}" : $entry->{category_name},
		$entry->{category_owner}
	);
	$categorylinks{$entry->{category_id}} = $newdbh->last_insert_id(undef,undef,undef,undef);

	$newsth2->execute(
		$entry->{project_id},
		$entry->{category_owner},
		55
	);

	$categoryNames{$entry->{category_name}} = 1;
	$lastProduct = $entry->{project_id};
}

$progress->update($cur_step*100);

# Versions

$progress->message("Importing versions");

$cur_step++;

$oldsth = $olddbh->prepare("SELECT version_id, project_id, version_name, version_tense
	FROM flyspray_list_version
	");

$newsth = $newdbh->prepare("INSERT IGNORE INTO mantis_project_version_table (project_id, version, released, obsolete) VALUES(?,?,?,?)");

$oldsth->execute();

$local_max_step = $oldsth->rows;
$local_cur_step = 0;
my %versionlinks;

$newdbh->do("TRUNCATE mantis_project_version_table");
$progress->message("Truncating table mantis_project_version_table") if $verbose;

foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) {
	++$local_cur_step;
	$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
	$progress->message(sprintf "Importing version '%s' for project '%s'", $entry->{version_name}, $projects{$entry->{project_id}}) if $verbose;
	$newsth->execute(
		$entry->{project_id},
		$entry->{version_name},
		$entry->{version_tense} == 3 ? 1 : 0,
		$entry->{version_tense} == 1 ? 1 : 0,
	);
	$versionlinks{$entry->{version_id}} = $entry->{version_name};
}

$progress->update($cur_step*100);

# Bugs
$progress->message("Importing bugs");

$cur_step++;

$oldsth = $olddbh->prepare("
	SELECT 
		task_id,
		attached_to_project,
		date_opened,
		opened_by,
		is_closed,
		date_closed,
		closed_by, 
		item_summary,
		detailed_desc,
		item_status,
		assigned_to,
		resolution_reason,
		product_category,
		product_version,
		closedby_version,
		operating_system,
		task_severity,
		task_priority,
		last_edited_by,
		last_edited_time,
		percent_complete,
		mark_private,
		due_date
	FROM flyspray_tasks
	");

$newsth = $newdbh->prepare("
	INSERT  
	INTO mantis_bug_text_table (description, steps_to_reproduce, additional_information) 
	VALUES(?,?,?)
	");

$newsth2 = $newdbh->prepare("
	INSERT 
	INTO mantis_bug_table (
		id,
		project_id,
		reporter_id,
		handler_id,
		priority,
		severity,
		status,
		resolution,
		category_id,
		date_submitted,
		last_updated,
		bug_text_id,
		version,
		fixed_in_version,
		summary,
		target_version
	) 
	VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
	");

$oldsth->execute();

$local_max_step = $oldsth->rows;
$local_cur_step = 0;

$newdbh->do("TRUNCATE mantis_bug_text_table");
$progress->message("Truncating table mantis_bug_text_table") if $verbose;
$newdbh->do("TRUNCATE mantis_bug_table");
$progress->message("Truncating table mantis_bug_table") if $verbose;

foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) {
	++$local_cur_step;
	$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
	$progress->message(sprintf "Importing bug '%d' for project '%s'", $entry->{task_id}, $projects{$entry->{attached_to_project}}) if $verbose;
	$newsth->execute(
		$entry->{detailed_desc},
		'',
		''
	);
	my $bug_text_id = $newdbh->last_insert_id(undef,undef,undef,undef);

	$newsth2->execute(
		$entry->{task_id},
		$entry->{attached_to_project},
		$entry->{opened_by},
		$entry->{assigned_to},
		$conv_priority{$entry->{task_priority}},
		$conv_severity{$entry->{task_severity}},
		$entry->{is_closed} ? 90 : $entry->{resolution_reason} == 8 ? 80 : $conv_status{$entry->{item_status}},
		$entry->{resolution_reason} ? $conv_resolution{$entry->{resolution_reason}} : 10,
		$categorylinks{$entry->{product_category}},
		DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_opened})),
		DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{last_edited_time})),
		$bug_text_id,
		$versionlinks{$entry->{product_version}},
		$entry->{closedby_version} && $entry->{is_closed} ? $versionlinks{$entry->{closedby_version}} : '',
		$entry->{item_summary},
		$entry->{closedby_version} ? $versionlinks{$entry->{closedby_version}} : '',
	);

}

$progress->update($cur_step*100);

# Bug notes
$progress->message("Importing bug notes");

$cur_step++;

my %commentlinks;

$oldsth = $olddbh->prepare("SELECT comment_id, task_id, date_added, user_id, comment_text
	FROM flyspray_comments
	");

$newsth = $newdbh->prepare("
	INSERT 
	INTO mantis_bugnote_text_table (note) 
	VALUES(?)
	");
$newsth2 = $newdbh->prepare("
	INSERT 
	INTO mantis_bugnote_table (
		bug_id,
		reporter_id,
		bugnote_text_id,
		date_submitted,
		last_modified
	) 
	VALUES(?,?,?,?,?)
	");


$oldsth->execute();

$local_max_step = $oldsth->rows;
$local_cur_step = 0;

$newdbh->do("TRUNCATE mantis_bugnote_text_table");
$progress->message("Truncating table mantis_bugnote_text_table") if $verbose;
$newdbh->do("TRUNCATE mantis_bugnote_table");
$progress->message("Truncating table mantis_bugnote_table") if $verbose;

foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) {
	++$local_cur_step;
	$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
	$progress->message(sprintf "Importing bugnote '%d' for bug '%s'", $entry->{comment_id}, $entry->{task_id}) if $verbose;
	$newsth->execute(
		$entry->{comment_text},
	);
	my $bugnote_text_id = $newdbh->last_insert_id(undef,undef,undef,undef);
	$newsth2->execute(
		$entry->{task_id},
		$entry->{user_id},
		$bugnote_text_id,
		DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_added})),
		DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_added})),
	);
	my $bugnote_id = $newdbh->last_insert_id(undef,undef,undef,undef);
	$commentlinks{$entry->{comment_id}} = sprintf "%07d", $bugnote_id;

}

$progress->update($cur_step*100);

# Relations
$progress->message("Importing relations");

$oldsth = $olddbh->prepare("SELECT task_id, dep_task_id FROM flyspray_dependencies");
$oldsth2 = $olddbh->prepare("SELECT this_task, related_task FROM flyspray_related");

$newsth = $newdbh->prepare("INSERT INTO mantis_bug_relationship_table (source_bug_id, destination_bug_id, relationship_type)
	VALUES(?,?,?)");

$oldsth->execute();
$oldsth2->execute();

$local_max_step = $oldsth->rows + $oldsth2->rows;
$local_cur_step = 0;

$newdbh->do("TRUNCATE mantis_bug_relationship_table");
$progress->message("Truncating table mantis_bug_relationship_table") if $verbose;

foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) {
	++$local_cur_step;
	$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
	$progress->message(sprintf "Importing dependency from bug %d to bug %d", $entry->{dep_task_id}, $entry->{task_id}) if $verbose;
	$newsth->execute(
		$entry->{dep_task_id},
		$entry->{task_id},
		2,
	);
}
foreach my $entry ( @{$oldsth2->fetchall_arrayref({})} ) {
	++$local_cur_step;
	$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
	$progress->message(sprintf "Importing relation from bug %d to bug %d", $entry->{this_task}, $entry->{related_task}) if $verbose;
	$newsth->execute(
		$entry->{this_task},
		$entry->{related_task},
		1,
	);
}

$cur_step++;
$progress->update($cur_step*100);

# Monitoring

$progress->message("Importing monitors");

$oldsth = $olddbh->prepare("SELECT task_id, user_id FROM flyspray_notifications");

$newsth = $newdbh->prepare("INSERT INTO mantis_bug_monitor_table (bug_id, user_id)
	VALUES(?,?)");

$oldsth->execute();

$local_max_step = $oldsth->rows;
$local_cur_step = 0;

$newdbh->do("TRUNCATE mantis_bug_monitor_table");
$progress->message("Truncating table mantis_bug_monitor_table") if $verbose;

foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) {
	++$local_cur_step;
	$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
	$progress->message(sprintf "Importing monitoring for bug %d by user %d", $entry->{task_id}, $entry->{user_id}) if $verbose;
	$newsth->execute(
		$entry->{task_id},
		$entry->{user_id},
	);
}

$cur_step++;
$progress->update($cur_step*100);


# Files
$progress->message("Importing files");
my %filelinks;
$oldsth = $olddbh->prepare("SELECT attachment_id,task_id, comment_id, orig_name, file_name, file_desc, file_type, file_size, added_by, date_added FROM flyspray_attachments");

$newsth = $newdbh->prepare("INSERT INTO mantis_bug_file_table 
	(
	bug_id,
	title,
	description,
	diskfile,
	filename,
	folder,
	filesize,
	file_type,
	date_added,
	content
	)
	VALUES(?,?,?,?,?,?,?,?,?,?)");

$oldsth->execute();

$local_max_step = $oldsth->rows;
$local_cur_step = 0;

$newdbh->do("TRUNCATE mantis_bug_file_table");
$progress->message("Truncating table mantis_bug_file_table") if $verbose;

foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) {
	++$local_cur_step;
	$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
	my $file = File::Spec->catfile($attachment_dir, $entry->{file_name});
	next unless -f $file;
	my $content = read_file( $file );
	$progress->message(sprintf "Importing file %d", $entry->{orig_name}) if $verbose;
	$newsth->execute(
		$entry->{task_id},
		$entry->{orig_name},
		$entry->{file_desc},
		'',
		$entry->{orig_name},
		'',
		$entry->{file_size},
		$entry->{file_type},
		DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_added})),
		$content,
	);
	$filelinks{$entry->{attachment_id}} = $entry->{orig_name};

}

$cur_step++;
$progress->update($cur_step*100);

# History
$progress->message("Importing history");

$cur_step++;

$oldsth = $olddbh->prepare("SELECT task_id, user_id, event_date, event_type, old_value, new_value, field_changed FROM flyspray_history");

$newsth = $newdbh->prepare("INSERT INTO mantis_bug_history_table 
	(
		user_id,
		bug_id,
		date_modified,
		field_name,
		old_value,
		new_value,
		type
	)
	VALUES(?,?,?,?,?,?,?)");

$oldsth->execute();

$local_max_step = $oldsth->rows;
$local_cur_step = 0;

my $last_status = 10;
$newdbh->do("TRUNCATE mantis_bug_history_table");
$progress->message("Truncating table mantis_bug_history_table") if $verbose;

foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) {
	my $type = $conv_history{$entry->{event_type}};
	++$local_cur_step;

	$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
	if( defined $type ) {
		my $old_value = $entry->{old_value};
		my $new_value = $entry->{new_value};
		my $field_name = $entry->{field_changed};
		if( $entry->{event_type} == 4 ) {
			$old_value = $commentlinks{$new_value};
			next unless $old_value;
			$new_value = '';
		}
		if( $entry->{event_type} == 9 ) {
			$old_value = $new_value;
			$new_value = '';
		}
		if( $entry->{event_type} == 7 ) {
			$old_value = $filelinks{$new_value};
			next unless $old_value;
			$new_value = '';
		}
		if( $entry->{event_type} == 2 ) {
			$field_name = 'status';
			$old_value = $last_status;
			$new_value = 90;
		}
		if( $entry->{event_type} == 0 ) {
			if( $entry->{field_changed} eq 'task_severity' ) {
				$field_name = 'severity';
				$old_value = $conv_severity{$old_value};
				$new_value = $conv_severity{$new_value};
			} elsif( $entry->{field_changed} eq 'item_status' ) {
				$field_name = 'status';
				$old_value = $conv_status{$old_value};
				$new_value = $conv_status{$new_value};
				$last_status = $new_value;
			} elsif( $entry->{field_changed} eq 'percent_complete' ) { next; }
		}
		$progress->message(sprintf "Importing history for bug %d and user %d", $entry->{task_id}, $entry->{user_id}) if $verbose;
		$newsth->execute(
			$entry->{user_id},
			$entry->{task_id},
			DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{event_date})),
			$field_name,
			$old_value,
			$new_value,
			$type,
		);
	}
}

$progress->update($cur_step*100);

#!/usr/bin/perl 
# Copyright © 2009 Carl Fürstenberg (AzaToth)
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions
# are met:
# 1. Redistributions of source code must retain the above copyright
#    notice, this list of conditions and the following disclaimer.
# 2. Redistributions in binary form must reproduce the above copyright
#    notice, this list of conditions and the following disclaimer in the
#    documentation and/or other materials provided with the distribution.
# 3. Neither the name of the University nor the names of its contributors
#    may be used to endorse or promote products derived from this software
#    without specific prior written permission.
#
# THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS ``AS IS'' AND
# ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
# ARE DISCLAIMED.  IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE
# FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
# DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
# OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
# HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
# LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
# OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
# SUCH DAMAGE.

use strict;
use warnings;

# Old database (flyspray)
my $olddsn = 'dbi:mysql:flyspray';
my $olddbuser = '';
my $olddbpass = '';

# New database (mantis)
my $newdsn = 'dbi:mysql:mantis';
my $newdbuser = '';
my $newdbpass = '';

# Directory where attachments are stored for flyspray
my $attachment_dir = 'attachments';

my %conv_priority = (
	1 => 10, # Low
	2 => 20, # Normal
	3 => 30, # High
	4 => 40, # Urgent
	5 => 50, # Immediate
	6 => 60, # Flash
);
my %conv_severity = (
	1 => 20, # Very Low
	2 => 40, # Low
	3 => 50, # Medium
	4 => 60, # High
	5 => 70, # Critical
);

my %conv_resolution = (
	1 => 10, # None
	2 => 70, # Not a bug
	3 => 90, # Won't fix
	4 => 90, # Won't implement
	5 => 40, # WFM
	6 => 60, # Dupe
	7 => 80, # Deferred
	8 => 20, # Fixed
	9 => 20, # Implemented
);

my %conv_status = (
	1 => 10, # Unconfirmed
	2 => 10, # New
	3 => 50, # Assigned
	4 => 30, # Researching
	5 => 30, # Waiting on customer
	6 => 10, # Requires testing
	7 => 20, # Repoened
	8 => 90, # Closed (removed in 0.9.6)
);

my %conv_history = (
	0 => 0, # Fields changed in a task
	1 => 1, # New task created
	2 => 0, # Task closed
	3 => 6, # Task edited (for backwards compatibility with events prior to the history system)
	4 => 2, # Comment added
	5 => 3, # Comment edited
	6 => 4, # Comment deleted
	7 => 9, # Attachment added
	8 => 10, # Attachment deleted
	9 => 12, # User added to notification list
	10 => 13, # User removed from notification list
	11 => undef, # Related task added to this task
	12 => undef, # Related task removed from this task
	13 => undef, # Task re-opened
	14 => undef, # Task assigned to user / re-assigned to different user / Unassigned
	15 => undef, # This task was added to another task's related list
	16 => undef, # This task was removed from another task's related list
	17 => undef, # Reminder added
	18 => undef, # Reminder deleted
	19 => undef, # User took ownership
	20 => undef, # Closure request made
	21 => undef, # Re-opening request made
	22 => undef, # Adding a new dependency
	23 => undef, # This task added as a dependency of another task
	24 => undef, # Removing a dependency
	25 => undef, # This task removed from another task's dependency list
	26 => undef, # Task was made private
	27 => undef, # Task was made public
	28 => undef, # PM request denied

);

my %accesslevels = (
	'Admin' => 90,
	'Developers' => 55,
	'Reporters' => 25
);

#################*UGLY CODE BELOW, BEWARE!*#####################

use Term::ProgressBar 2.00;
use DateTime::Format::MySQL;
use DateTime;
use DBI;
use Getopt::Long;
use File::Slurp;
use File::Spec;

my $VERSION = 1.0;

my $cur_step = 0;
my $max_step = 11;

my $verbose = 0;

my %projects;
my %categoryNames;
my %categorylinks;
my %versionlinks;
my %userbugs;
my %filelinks;
my %commentlinks;

GetOptions( "verbose!" => \$verbose );
my( $local_max_step, $local_cur_step );

my $progress = Term::ProgressBar->new(
	{
		name  => 'Progress',
		count => $max_step*100,
		ETA   => 'linear',
	}
);



my $olddbh = DBI->connect( $olddsn, $olddbuser, $olddbpass);
my $newdbh = DBI->connect( $newdsn, $newdbuser, $newdbpass);
$newdbh->{mysql_enable_utf8} = 1;
$newdbh->do("SET NAMES 'utf8'");

# Truncating
{
	$progress->message("Truncating tables");
	$cur_step++;

	$newdbh->do("TRUNCATE mantis_user_table");
	$progress->message("Truncating table mantis_user_table") if $verbose;
	$newdbh->do("TRUNCATE mantis_project_table");
	$progress->message("Truncating table mantis_project_table") if $verbose;
	$newdbh->do("TRUNCATE mantis_category_table");
	$progress->message("Truncating table mantis_category_table") if $verbose;
	$newdbh->do("TRUNCATE mantis_project_user_list_table");
	$progress->message("Truncating table mantis_project_user_list_table") if $verbose;
	$newdbh->do("TRUNCATE mantis_project_version_table");
	$progress->message("Truncating table mantis_project_version_table") if $verbose;
	$newdbh->do("TRUNCATE mantis_bug_text_table");
	$progress->message("Truncating table mantis_bug_text_table") if $verbose;
	$newdbh->do("TRUNCATE mantis_bug_table");
	$progress->message("Truncating table mantis_bug_table") if $verbose;
	$newdbh->do("TRUNCATE mantis_bugnote_text_table");
	$progress->message("Truncating table mantis_bugnote_text_table") if $verbose;
	$newdbh->do("TRUNCATE mantis_bugnote_table");
	$progress->message("Truncating table mantis_bugnote_table") if $verbose;
	$newdbh->do("TRUNCATE mantis_bug_relationship_table");
	$progress->message("Truncating table mantis_bug_relationship_table") if $verbose;
	$newdbh->do("TRUNCATE mantis_bug_monitor_table");
	$progress->message("Truncating table mantis_bug_monitor_table") if $verbose;
	$newdbh->do("TRUNCATE mantis_bug_file_table");
	$progress->message("Truncating table mantis_bug_file_table") if $verbose;
	$newdbh->do("TRUNCATE mantis_bug_history_table");
	$progress->message("Truncating table mantis_bug_history_table") if $verbose;
}

# Users
{
	$progress->message("Importing users");

	$cur_step++;

	my $flyspray_users = $olddbh->prepare("
		SELECT DISTINCT
			flyspray_users.user_id,
			flyspray_groups.group_name,
			flyspray_history.event_date AS date,
			user_name,
			real_name,
			user_pass,
			email_address,
			account_enabled 
		FROM flyspray_users 
		LEFT JOIN flyspray_users_in_groups ON flyspray_users.user_id = flyspray_users_in_groups.user_id
		LEFT JOIN flyspray_groups ON flyspray_groups.group_id = flyspray_users_in_groups.group_id 
		LEFT JOIN flyspray_history ON flyspray_users.user_id = flyspray_history.user_id 
		GROUP BY flyspray_users.user_id
		");

	my $mantis_user_table = $newdbh->prepare("
		INSERT INTO mantis_user_table 
		(
			id,
			username,
			realname,
			email,
			password,
			enabled,
			access_level,
			cookie_string,
			date_created
		)
		VALUES (?,?,?,?,?,?,?,?,?)
		");

	$flyspray_users->execute();

	$local_max_step = $flyspray_users->rows;
	$local_cur_step = 0;

	my $cookieCounter = 1;
	foreach my $entry ( @{$flyspray_users->fetchall_arrayref({})} ) {
		++$local_cur_step;
		$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
		$progress->message(sprintf "Importing user %s", $entry->{user_name}) if $verbose;
		$mantis_user_table->execute(
			$entry->{user_id},
			$entry->{user_name},
			$entry->{real_name},
			$entry->{email_address},
			$entry->{user_pass},
			$entry->{account_enabled},
			$accesslevels{$entry->{group_name}} // 10,
			'cookiestring'.++$cookieCounter,
			$entry->{date} ? DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date})) : 0
		);
	}

	$progress->update($cur_step*100);
}
# Projects
{
	$progress->message("Importing projects");

	$cur_step++;

	my $flyspray_projects = $olddbh->prepare("
		SELECT 
			project_id, 
			project_title, 
			project_is_active,
			intro_message,
			anon_open 
		FROM flyspray_projects
		");

	my $mantis_project_table = $newdbh->prepare("
		INSERT INTO mantis_project_table
		(
			id,
			name,
			enabled,
			description,
			view_state
		)
		VALUES(?,?,?,?,?)
		");

	$flyspray_projects->execute();

	$local_max_step = $flyspray_projects->rows;
	$local_cur_step = 0;

	foreach my $entry ( @{$flyspray_projects->fetchall_arrayref({})} ) {
		++$local_cur_step;
		$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
		$progress->message(sprintf "Importing project %s", $entry->{project_title}) if $verbose;
		$mantis_project_table->execute(
			$entry->{project_id},
			$entry->{project_title},
			$entry->{project_is_active},
			$entry->{intro_message},
			$entry->{anon_open} == 1 ? 10 : 50
		);
		$projects{$entry->{project_id}} = $entry->{project_title};
	}

	$progress->update($cur_step*100);
}
# Categories
{
	$progress->message("Importing categories");

	$cur_step++;

	my $flyspray_list_category = $olddbh->prepare("
		SELECT 
			category_id,
			category_name,
			project_id,
			category_owner
		FROM flyspray_list_category
		");

	my $mantis_category_table = $newdbh->prepare("
		INSERT INTO mantis_category_table
		(
			project_id,
			name,
			user_id
		)
		VALUES(?,?,?)
		");
	my $mantis_project_user_list_table = $newdbh->prepare("
		INSERT IGNORE INTO mantis_project_user_list_table
		(
			project_id,
			user_id,
			access_level
		)
		VALUES(?,?,?)
		");

	$flyspray_list_category->execute();

	$local_max_step = $flyspray_list_category->rows;
	$local_cur_step = 0;

	my $lastProduct = -1;

	foreach my $entry ( @{$flyspray_list_category->fetchall_arrayref({})} ) {
		if( $lastProduct != $entry->{project_id} ) {
			%categoryNames = ();
		}
		++$local_cur_step;
		$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
		$progress->message(sprintf "Importing category '%s' for project '%s'", $entry->{category_name}, $projects{$entry->{project_id}}) if $verbose;
		$mantis_category_table->execute(
			$entry->{project_id},
			exists $categoryNames{$entry->{category_name}} ? "$entry->{category_name} $entry->{category_id}" : $entry->{category_name},
			$entry->{category_owner}
		);
		$categorylinks{$entry->{category_id}} = $newdbh->last_insert_id(undef,undef,undef,undef);

		$mantis_project_user_list_table->execute(
			$entry->{project_id},
			$entry->{category_owner},
			55
		);

		$categoryNames{$entry->{category_name}} = 1;
		$lastProduct = $entry->{project_id};
	}

	$progress->update($cur_step*100);
}
# Versions
{
	$progress->message("Importing versions");

	$cur_step++;

	my $flyspray_list_version = $olddbh->prepare("
		SELECT 
			version_id,
			project_id,
			version_name,
			version_tense
		FROM flyspray_list_version
		");

	my $mantis_project_version_table = $newdbh->prepare("
		INSERT IGNORE INTO mantis_project_version_table
		(
			project_id,
			version,
			released,
			obsolete
		)
		VALUES(?,?,?,?)
		");

	$flyspray_list_version->execute();

	$local_max_step = $flyspray_list_version->rows;
	$local_cur_step = 0;

	foreach my $entry ( @{$flyspray_list_version->fetchall_arrayref({})} ) {
		++$local_cur_step;
		$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
		$progress->message(sprintf "Importing version '%s' for project '%s'", $entry->{version_name}, $projects{$entry->{project_id}}) if $verbose;
		$mantis_project_version_table->execute(
			$entry->{project_id},
			$entry->{version_name},
			$entry->{version_tense} == 3 ? 1 : 0,
			$entry->{version_tense} == 1 ? 1 : 0,
		);
		$versionlinks{$entry->{version_id}} = $entry->{version_name};
	}

	$progress->update($cur_step*100);
}
# Bugs
{
	$progress->message("Importing bugs");

	$cur_step++;

	my $flyspray_tasks = $olddbh->prepare("
		SELECT 
			task_id,
			attached_to_project,
			date_opened,
			opened_by,
			is_closed,
			date_closed,
			closed_by, 
			item_summary,
			detailed_desc,
			item_status,
			assigned_to,
			resolution_reason,
			product_category,
			product_version,
			closedby_version,
			operating_system,
			task_severity,
			task_priority,
			last_edited_by,
			last_edited_time,
			percent_complete,
			mark_private,
			due_date
		FROM flyspray_tasks
		");

	my $mantis_bug_text_table = $newdbh->prepare("
		INSERT  
		INTO mantis_bug_text_table 
		(
			description,
			steps_to_reproduce,
			additional_information
		) 
		VALUES(?,?,?)
		");

	my $mantis_bug_table = $newdbh->prepare("
		INSERT 
		INTO mantis_bug_table
		(
			id,
			project_id,
			reporter_id,
			handler_id,
			priority,
			severity,
			status,
			resolution,
			category_id,
			date_submitted,
			last_updated,
			bug_text_id,
			version,
			fixed_in_version,
			summary,
			target_version
		) 
		VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
		");

	$flyspray_tasks->execute();

	$local_max_step = $flyspray_tasks->rows;
	$local_cur_step = 0;

	foreach my $entry ( @{$flyspray_tasks->fetchall_arrayref({})} ) {
		++$local_cur_step;
		$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
		$progress->message(sprintf "Importing bug '%d' for project '%s'", $entry->{task_id}, $projects{$entry->{attached_to_project}}) if $verbose;
		$mantis_bug_text_table->execute(
			$entry->{detailed_desc},
			'',
			''
		);
		$userbugs{$entry->{task_id}} = {};
		my $bug_text_id = $newdbh->last_insert_id(undef,undef,undef,undef);

		if( defined $entry->{opened_by} ) {
			$userbugs{$entry->{task_id}}{$entry->{opened_by}} = 1;
		}
		if( defined $entry->{assigned_to} ) {
			$userbugs{$entry->{task_id}}{$entry->{assigned_to}} = 1;
		}

		$mantis_bug_table->execute(
			$entry->{task_id},
			$entry->{attached_to_project},
			$entry->{opened_by},
			$entry->{assigned_to},
			$conv_priority{$entry->{task_priority}},
			$conv_severity{$entry->{task_severity}},
			$entry->{is_closed} ? 90 : $entry->{resolution_reason} == 8 ? 80 : $conv_status{$entry->{item_status}},
			$entry->{resolution_reason} ? $conv_resolution{$entry->{resolution_reason}} : 10,
			$categorylinks{$entry->{product_category}},
			DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_opened})),
			DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{last_edited_time})),
			$bug_text_id,
			$versionlinks{$entry->{product_version}},
			$entry->{closedby_version} && $entry->{is_closed} ? $versionlinks{$entry->{closedby_version}} : '',
			$entry->{item_summary},
			$entry->{closedby_version} ? $versionlinks{$entry->{closedby_version}} : '',
		);

	}

	$progress->update($cur_step*100);
}
# Bug notes
{
	$progress->message("Importing bug notes");

	$cur_step++;


	my $flyspray_comments = $olddbh->prepare("
		SELECT
			comment_id,
			task_id,
			date_added,
			user_id,
			comment_text
		FROM flyspray_comments
		");

	my $mantis_bugnote_text_table = $newdbh->prepare("
		INSERT 
		INTO mantis_bugnote_text_table 
		(
			note
		) 
		VALUES(?)
		");
	my $mantis_bugnote_table = $newdbh->prepare("
		INSERT 
		INTO mantis_bugnote_table (
			bug_id,
			reporter_id,
			bugnote_text_id,
			date_submitted,
			last_modified
		) 
		VALUES(?,?,?,?,?)
		");


	$flyspray_comments->execute();

	$local_max_step = $flyspray_comments->rows;
	$local_cur_step = 0;

	foreach my $entry ( @{$flyspray_comments->fetchall_arrayref({})} ) {
		++$local_cur_step;
		$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
		$progress->message(sprintf "Importing bugnote '%d' for bug '%s'", $entry->{comment_id}, $entry->{task_id}) if $verbose;
		$mantis_bugnote_text_table->execute(
			$entry->{comment_text},
		);
		my $bugnote_text_id = $newdbh->last_insert_id(undef,undef,undef,undef);
		if( defined $entry->{user_id} ) {
			$userbugs{$entry->{task_id}}{$entry->{user_id}} = 1;
		}
		$mantis_bugnote_table->execute(
			$entry->{task_id},
			$entry->{user_id},
			$bugnote_text_id,
			DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_added})),
			DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_added})),
		);
		my $bugnote_id = $newdbh->last_insert_id(undef,undef,undef,undef);
		$commentlinks{$entry->{comment_id}} = sprintf "%07d", $bugnote_id;

	}

	$progress->update($cur_step*100);
}
# Relations
{
	$progress->message("Importing relations");
	$cur_step++;

	my $flyspray_dependencies = $olddbh->prepare("
		SELECT
			task_id,
			dep_task_id
		FROM flyspray_dependencies
		");
	my $flyspray_related = $olddbh->prepare("
		SELECT
			this_task,
			related_task
		FROM flyspray_related
		");

	my $mantis_bug_relationship_table = $newdbh->prepare("
		INSERT INTO mantis_bug_relationship_table
		(
			source_bug_id,
			destination_bug_id,
			relationship_type
		)
		VALUES(?,?,?)");

	$flyspray_dependencies->execute();
	$flyspray_related->execute();

	$local_max_step = $flyspray_dependencies->rows + $flyspray_related->rows;
	$local_cur_step = 0;

	foreach my $entry ( @{$flyspray_dependencies->fetchall_arrayref({})} ) {
		++$local_cur_step;
		$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
		$progress->message(sprintf "Importing dependency from bug %d to bug %d", $entry->{dep_task_id}, $entry->{task_id}) if $verbose;
		$mantis_bug_relationship_table->execute(
			$entry->{dep_task_id},
			$entry->{task_id},
			2,
		);
	}
	foreach my $entry ( @{$flyspray_related->fetchall_arrayref({})} ) {
		++$local_cur_step;
		$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
		$progress->message(sprintf "Importing relation from bug %d to bug %d", $entry->{this_task}, $entry->{related_task}) if $verbose;
		$mantis_bug_relationship_table->execute(
			$entry->{this_task},
			$entry->{related_task},
			1,
		);
	}

	$progress->update($cur_step*100);
}
# Monitoring
{
	$progress->message("Importing monitors");
	$cur_step++;

	my $flyspray_notifications = $olddbh->prepare("
		SELECT
			task_id,
			user_id
		FROM flyspray_notifications
		");

	my $mantis_bug_monitor_table = $newdbh->prepare("
		INSERT INTO mantis_bug_monitor_table 
		(
			bug_id,
			user_id
		)
		VALUES(?,?)");

	$flyspray_notifications->execute();

	$local_max_step = $flyspray_notifications->rows;
	$local_cur_step = 0;

	foreach my $entry ( @{$flyspray_notifications->fetchall_arrayref({})} ) {
		next if exists $userbugs{$entry->{task_id}}{$entry->{user_id}}; # Skip implicit monitoring
		++$local_cur_step;
		$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
		$progress->message(sprintf "Importing monitoring for bug %d by user %d", $entry->{task_id}, $entry->{user_id}) if $verbose;
		$mantis_bug_monitor_table->execute(
			$entry->{task_id},
			$entry->{user_id},
		);
	}

	$progress->update($cur_step*100);

}
# Files
{
	$progress->message("Importing files");
	$cur_step++;

	my $flyspray_attachments = $olddbh->prepare("
		SELECT
			attachment_id,task_id,
			comment_id,
			orig_name,
			file_name,
			file_desc,
			file_type,
			file_size,
			added_by,
			date_added
		FROM flyspray_attachments
		");

	my $mantis_bug_file_table = $newdbh->prepare("
		INSERT INTO mantis_bug_file_table 
		(
			bug_id,
			title,
			description,
			diskfile,
			filename,
			folder,
			filesize,
			file_type,
			date_added,
			content
		)
		VALUES(?,?,?,?,?,?,?,?,?,?)
		");

	$flyspray_attachments->execute();

	$local_max_step = $flyspray_attachments->rows;
	$local_cur_step = 0;

	foreach my $entry ( @{$flyspray_attachments->fetchall_arrayref({})} ) {
		++$local_cur_step;
		$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
		my $file = File::Spec->catfile($attachment_dir, $entry->{file_name});
		next unless -f $file;
		my $content = read_file( $file );
		$progress->message(sprintf "Importing file %d", $entry->{orig_name}) if $verbose;
		$mantis_bug_file_table->execute(
			$entry->{task_id},
			$entry->{orig_name},
			$entry->{file_desc},
			'',
			$entry->{orig_name},
			'',
			$entry->{file_size},
			$entry->{file_type},
			DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_added})),
			$content,
		);
		$filelinks{$entry->{attachment_id}} = $entry->{orig_name};

	}

	$progress->update($cur_step*100);
}
# History
{
	$progress->message("Importing history");
	$cur_step++;


	my $flyspray_history = $olddbh->prepare("
		SELECT
			task_id,
			user_id,
			event_date,
			event_type,
			old_value,
			new_value,
			field_changed
		FROM flyspray_history
		");

	my $mantis_bug_history_table = $newdbh->prepare("
		INSERT INTO mantis_bug_history_table 
		(
			user_id,
			bug_id,
			date_modified,
			field_name,
			old_value,
			new_value,
			type
		)
		VALUES(?,?,?,?,?,?,?)
		");

	$flyspray_history->execute();

	$local_max_step = $flyspray_history->rows;
	$local_cur_step = 0;

	my $last_status = 10;
	foreach my $entry ( @{$flyspray_history->fetchall_arrayref({})} ) {
		my $type = $conv_history{$entry->{event_type}};
		++$local_cur_step;

		$progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 );
		if( defined $type ) {
			my $old_value = $entry->{old_value};
			my $new_value = $entry->{new_value};
			my $field_name = $entry->{field_changed};
			if( $entry->{event_type} == 4 ) {
				$old_value = $commentlinks{$new_value};
				next unless $old_value;
				$new_value = '';
			}
			if( $entry->{event_type} == 9 ) {
				$old_value = $new_value;
				$new_value = '';
				next if exists $userbugs{$entry->{task_id}}{$old_value}; # skipping implicit history monitor additions
			}
			if( $entry->{event_type} == 7 ) {
				$old_value = $filelinks{$new_value};
				next unless $old_value;
				$new_value = '';
			}
			if( $entry->{event_type} == 2 ) {
				$field_name = 'status';
				$old_value = $last_status;
				$new_value = 90;
			}
			if( $entry->{event_type} == 0 ) {
				if( $entry->{field_changed} eq 'task_severity' ) {
					$field_name = 'severity';
					$old_value = $conv_severity{$old_value};
					$new_value = $conv_severity{$new_value};
				} elsif( $entry->{field_changed} eq 'item_status' ) {
					$field_name = 'status';
					$old_value = $conv_status{$old_value};
					$new_value = $conv_status{$new_value};
					$last_status = $new_value;
				} elsif( $entry->{field_changed} eq 'percent_complete' ) { next; }
			}
			$progress->message(sprintf "Importing history for bug %d and user %d", $entry->{task_id}, $entry->{user_id}) if $verbose;
			$mantis_bug_history_table->execute(
				$entry->{user_id},
				$entry->{task_id},
				DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{event_date})),
				$field_name,
				$old_value,
				$new_value,
				$type,
			);
		}
	}

	$progress->update($cur_step*100);
}