#!/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);