#!/usr/bin/perl -w

# checklist before running:
# - create "NONE" resolution status
# - create products/components
# - create keyword "needsinput"
# - create mvtab table
#     CREATE TABLE `flyspray_mvtab` (
#      `type` varchar(255) NOT NULL,
#      `old_id` int(11) NOT NULL,
#      `new_id` int(11) NOT NULL,
#      KEY `type` (`type`,`old_id`,`new_id`)
#    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
#

use warnings;
use strict;
use v5.10;

use POSIX qw(strftime);

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

#flyspray info
our $FS_DB = 'flyspray';
our $FS_PFX = 'flyspray_';
our $FS_ATTACH = '/srv/http/vhosts/bugzilla.archlinux.org/attachments';

#bugzilla info
our $BZ_DB = 'bugzilla';
our $BZ_UNKNOWN_USER = 2;               #userid
our $BZ_NEEDSINPUT_KEYWORD = 1;         #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.${FS_PFX}mvtab (type, old_id, new_id) VALUES (?, ?, ?)");
$mvtab_find = $dbh->prepare("SELECT new_id FROM $FS_DB.${FS_PFX}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(1, 1, 1); # Arch Linux
copy_bugs(5, 1, 9); # Community

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_history
{
        my ($fs_find, $bz_add, $fs_bug, $bz_bug) = (@_);
        my ($fs_history_id, $fs_userid, $fs_event_type, $fs_field, $fs_old, $fs_new, $fs_date);
        my ($bz_com_id, $bz_userid, $bz_date, $bz_text);

        $fs_find->execute($fs_bug);
        while ( ($fs_history_id, $fs_userid, $fs_event_type, $fs_date, $fs_field, $fs_old, $fs_new) = $fs_find->fetchrow_array() )
        {
                $mvtab_find->execute($fs_history_id, 'history');
                next if ( ($bz_com_id) = $mvtab_find->fetchrow_array() );

                given ($fs_event_type) {
                        when (2) {
                                $bz_text = "Closed: ".$fs_old;
                        }
                        default {
                                next;
                        }
                }
                print STDERR "Adding history entry $fs_history_id, $fs_event_type, $fs_date\n";

                $bz_userid = find_user($fs_userid);
                if (!$bz_userid)
                {
                        print "Failed to find user $fs_userid\n";
                        $bz_userid = $BZ_UNKNOWN_USER;
                }

                $bz_date = unixtime_to_mysql($fs_date);
                $bz_add->execute($bz_bug, $bz_userid, $bz_date, $bz_text);
                $bz_com_id = ins_id();
                $mvtab_add->execute('history', $fs_history_id, $bz_com_id);
        }
}

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_UNKNOWN_USER;
                }

                $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())
        {
                print STDERR "Found attachment $fs_attach->{file_name}\n";
                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_UNKNOWN_USER;
                }
                $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);
                print STDERR "added\n";
        }
}

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_history);
        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.${FS_PFX}attachments WHERE task_id = ?");
        $sql_fs_find_comments = $dbh->prepare("SELECT comment_id, user_id, " .
                "date_added, comment_text FROM $FS_DB.${FS_PFX}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.${FS_PFX}tasks WHERE project_id = $fs_proj");
        $sql_bz_add_bug = $dbh->prepare(
                "INSERT INTO $BZ_DB.bugs (bug_id, 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.${FS_PFX}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.${FS_PFX}notifications WHERE task_id = ?");
        $sql_fs_find_history = $dbh->prepare("SELECT history_id, user_id, event_type, event_date, field_changed, old_value, new_value FROM $FS_DB.${FS_PFX}history 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', 'CONFIRMED', 'UNCONFIRMED', 'UNCONFIRMED', 'UNCONFIRMED', 'VERIFIED', 'VERIFIED');
        @fs_priorities = ('Highest', 'High', 'Normal', 'Low', 'Lowest', '---');
        @fs_closures = ('NONE', # none
                                        'INVALID', # not a bug
                                        'WONTFIX', # wont fix
                                        'WONTFIX', # wont implement
                                        'WORKSFORME',  # works for me
                                        'DUPLICATE', # duplicate
                                        'WONTFIX', # deferred
                                        'FIXED', # fixed
                                        'FIXED', # implemented
                                        'WONTFIX', # upstream
                                        'WORKSFORME' # no response
                                );
        
        $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);
                print STDERR "processing bug $fs_task->{task_id}\n";

                $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_history($sql_fs_find_history, $sql_bz_add_comment, $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_op_sys = "Linux";
                $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)
                {
                        print STDERR "Failed to find user $fs_task->{opened_by}\n";
                        $bz_reporter = $BZ_UNKNOWN_USER;
                }

                $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}),
                }

                my $bz_assignee = find_assignee($fs_task->{task_id});
                if (!$bz_assignee)
                {
                        print STDERR "Failed to find assignee $fs_task->{task_id}\n";
                        $bz_assignee = $bz_reporter;
                }

                print STDERR "Assinging to $bz_assignee\n";

                $sql_bz_add_bug->execute(
                        $fs_task->{task_id},
                        $bz_assignee,
                        $bz_severity,
                        $bz_status,
                        $bz_date,
                        $bz_date,
                        $bz_op_sys,
                        $bz_priority,
                        $bz_short_desc,
                        $bz_proj,
                        $bz_platform,
                        $bz_reporter,
                        'unspecified',
                        $bz_com,
                        $bz_resolution,
                        $bz_milestone,
                        $bz_lastdiff,
                        $bz_confirmed,
                        1,
                        1);
                #$bz_id = ins_id();
                $bz_id = $fs_task->{task_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_history($sql_fs_find_history, $sql_bz_add_comment, $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, $bz_add_user, $bz_find_user);
        my ($user_id, $user_name, $real_name);
        my ($email, $bz_userid);

        $sth = $dbh->prepare("SELECT user_id, user_name, real_name, email_address FROM $FS_DB.${FS_PFX}users");
        $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) VALUES (?, ?)");

        #Find all Flyspray users.
        $sth->execute();
        while ( ($user_id, $user_name, $real_name, $email) = $sth->fetchrow_array() )
        {
                #See if this user is already in bugzilla.
                $bz_find_user->execute($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($email, $real_name);
                        $bz_userid = ins_id();
                        $mvtab_add->execute('user', $user_id, $bz_userid);
                        print STDERR "Binding ($user_id, \"$user_name\") to ($bz_userid, $email)\n";
                }
        }
        $sth->finish();
        $bz_find_user->finish();
        $bz_add_user->finish();
}

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

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

sub find_assignee
{
        my ($task_id) = (@_);
        my ($user_id);

        my $sql_fs_find_assignee = $dbh->prepare("SELECT user_id FROM $FS_DB.${FS_PFX}assigned WHERE task_id = ? LIMIT 1");
        $sql_fs_find_assignee->execute($task_id);

        ($user_id) = $sql_fs_find_assignee->fetchrow_array();

        $user_id = find_user($user_id);

        return $user_id;
}

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;
}

