#!/usr/bin/perl -w
# Copy tickets from one RT to another RT, for one or more named queue(s)

# Known bugs
# - In ticket transactions for queue change, we don't try to find the source or
#   target queue
# - We temporarily set Owner right for all users to this queue so we do not have
#   right problem to set ticket owner, this right is removed at the end
# - We check CustomFields by Name, assuming there is no duplicate names accross
#   them (not often true!)
# - Links that have target not in the tickets we import cannot work, so we
#   mangle the URI to point to old RT

use strict;
use 5.010;
use DBI;
use Getopt::Long;

use lib "/opt/rt3/lib";

use RT;
use RT::Interface::CLI qw( CleanEnv ); 

# Don't buffer STDOUT
$| = 1;

CleanEnv();
RT::LoadConfig();
RT::Init();


# Command line arguments
my @queues;
my $ok = GetOptions(
    'queue|q=s@' => \@queues
);

unless ( $ok && @queues && scalar @queues ) {
    say "Usage: sync-tickets.pl --queue=QUEUE1 --queue=QUEUE2 ...";
    exit(1);
}


my $start_time = time;

# Log file
my $log_file = "/var/tmp/sync-tickets.$$.log";
open (LOG, "> $log_file") or die "Can't open log file $log_file: $!\n";
say "Logging to $log_file";

##
# Configuration variables
##

# Connexion parameters to src DB
my $src_db_dsn = 'DBI:mysql:dbname=rt34;host=localhost';
my $src_db_user = 'rt34';
my $src_db_pass = 'rtpass';

# Some informations about src RT
# Without trailing '/'
my $src_rt_base_url = 'http://rt34';
my $cf_originalid_name = 'id34';

# Global variables
my ($i, $DstQueue, $ticketid, $val, $msg);
my %principals; # Cache old->new principals ids
my %tickets; # Cache old->new tickets ids
my %customfields; # Cache old->new customfields ids


##
# Connect to SRC DB
##
my $src = DBI->connect($src_db_dsn, $src_db_user, $src_db_pass) or die $DBI::errstr;

##
# Prepare needed sql statements for efficiency
##

# Tickets
my $src_get_tickets_sth = $src->prepare("SELECT Tickets.* FROM Tickets, Queues 
    WHERE Tickets.Queue = Queues.Id 
    AND Queues.Name = ? ORDER BY Id ASC");
# Ticket transactions
my $src_get_ticket_transactions_sth = $src->prepare("SELECT * FROM Transactions 
    WHERE ObjectType='RT::Ticket' 
    AND ObjectId=? ORDER BY Id ASC");
# Transaction attachments
my $src_get_transaction_attachments_sth = $src->prepare("SELECT * FROM Attachments WHERE 
    TransactionId = ? ORDER BY Id ASC");
# User
my $src_get_user_sth = $src->prepare("SELECT * FROM Users WHERE Id = ?");
# Principal
my $src_get_principal_sth = $src->prepare("SELECT * FROM Principals WHERE Id = ?");
# Group
my $src_get_group_sth = $src->prepare("SELECT * FROM Groups WHERE Id = ?");
# Ticket customfield values
my $src_get_ticket_ocfvs_sth = $src->prepare("SELECT * FROM ObjectCustomFieldValues 
    WHERE ObjectType = 'RT::Ticket' 
    AND ObjectId = ? ORDER BY Id ASC");
# Transaction customfield values
my $src_get_transaction_ocfvs_sth = $src->prepare("SELECT * FROM ObjectCustomFieldValues 
    WHERE ObjectType = 'RT::Transaction' 
    AND ObjectId = ? ORDER BY Id ASC");
# Customfield
my $src_get_cf_sth = $src->prepare("SELECT * FROM CustomFields WHERE Id = ? ORDER BY Id ASC");
# Links
my $src_get_ticket_links_sth = $src->prepare("SELECT * FROM Links WHERE LocalTarget = ? ORDER BY Id ASC");
# Privileged user
my $src_user_is_privileged_sth = $src->prepare("SELECT Count(*) FROM Groups, GroupMembers 
    WHERE Groups.Type = 'Privileged' 
    AND Groups.Id = GroupMembers.GroupId 
    AND GroupMembers.MemberId = ?");
# Ticket watchers (except Owner which we got already in ticket table)
my $src_get_ticket_watchers_sth = $src->prepare("SELECT Groups.Type, GroupMembers.* FROM Groups, GroupMembers 
    WHERE GroupMembers.GroupId=Groups.Id 
    AND Groups.Domain='RT::Ticket-Role' 
    AND Groups.Type != 'Owner' 
    AND Groups.Instance=?");
# Group members
my $src_get_group_members_sth = $src->prepare("SELECT * FROM GroupMembers WHERE GroupId=?");
# Customfield values
my $src_get_customfield_values_sth = $src->prepare("SELECT * FROM CustomFieldValues WHERE CustomField = ?");


##
# Needed functions
##

sub print_time_to_process {
    my $end_time = time;
    my $time_to_process = $end_time - $start_time;
    if ( $time_to_process >= 3600 ) {
        $time_to_process = sprintf("%0.2f hours", $time_to_process / 3600);
    } elsif ( $time_to_process >= 60 ) {
        $time_to_process = sprintf("%0.2f minutes", $time_to_process / 60);
    } else {
        $time_to_process = sprintf("%0.2f seconds", $time_to_process);
    }
    say "Time spent: $time_to_process";
    say LOG "Time spent: $time_to_process";
}

sub ask_on_error {
    my $error_msg = shift;
    say STDERR "E: Failed: $error_msg";
    say LOG "E: Failed: $error_msg";
    print_time_to_process;
    print "Continue (y/n)? ";
    $| = 1;
    my $answer = <STDIN>;
    chomp ($answer);

    if ( $answer =~ /^\s*Y\s*$/i ) {
        return (undef);
    } else {
        close(LOG);
        exit (1);
    }
    
}

sub convert_uri {
    my $src_uri = shift;
     
    my $dst_uri = $src_uri;
    if ( $src_uri =~ m|^fsck.com-rt://.*/ticket/(\d+)$| ) {
        # If we didn't saw the ticket id (ticket out of our SELECT (new tickets for example)
        # use RT full URL
        unless ( $tickets{$1} ) {
            return ($src_rt_base_url."/Ticket/Display.html?id=$1");
        }
        # Construct URI manually, instead of using API (may be slower if we load the ticket to get the URI)
        $dst_uri = 'fsck.com-rt://'.RT->Config->Get('Organization').'/ticket/'.$tickets{$1};
    }

    return ($dst_uri);

}

sub get_src_principal {
    my $src_principal_id = shift;

    $src_get_principal_sth->execute($src_principal_id);
    my $src_principal = $src_get_principal_sth->fetchall_arrayref( {} );
    unless ( $src_principal && scalar(@{$src_principal}) == 1 ) {
        return undef;
    }
    $src_principal = shift @{$src_principal};
    return $src_principal;
}

sub get_src_group {
    my $src_group_id = shift;

    $src_get_group_sth->execute($src_group_id);
    my $src_group = $src_get_group_sth->fetchall_arrayref( {} );
    unless ( $src_group && scalar(@{$src_group}) == 1 ) {
        return undef;
    }
    $src_group = shift @{$src_group};
    return $src_group;
}

sub get_src_user {
    my $src_user_id = shift;

    $src_get_user_sth->execute($src_user_id);
    my $src_user = $src_get_user_sth->fetchall_arrayref( {} );
    unless ( $src_user && scalar(@{$src_user}) == 1 ) {
        return undef;
    }
    $src_user = shift @{$src_user};
    return $src_user;
}

sub get_src_cf {
    my $src_cf_id = shift;

    $src_get_cf_sth->execute($src_cf_id);
    my $src_cf = $src_get_cf_sth->fetchall_arrayref( {} );
    unless ( $src_cf && scalar(@{$src_cf}) == 1 ) {
        return undef;
    }
    $src_cf = shift @{$src_cf};
    return $src_cf;
}

sub dst_get_or_create_principal {
    my $src_principal_id =  shift;

    # Caching
    return ($principals{$src_principal_id}) if ( $principals{$src_principal_id} );

    # Get principal infos
    my $src_principal = get_src_principal($src_principal_id);

    # Bogus DB
    unless ( $src_principal ) {
        ask_on_error("E: Principal $src_principal_id not found on src (will use RT::SystemUser if you choose to continue)");
        return ($RT::SystemUser->id);
    }

    my $dst_principal_id;
    if ( $src_principal->{PrincipalType} eq 'User' ) {
        $dst_principal_id = dst_get_or_create_user($src_principal->{ObjectId});
    } elsif ( $src_principal->{PrincipalType} eq 'Group' ) {
        $dst_principal_id = dst_get_or_create_group($src_principal->{ObjectId});
    } else {
        ask_on_error("E: Don't know what to do with a principal of type ".$src_principal->{PrincipalType}."(will use RT::SystemUser if you choose to continue)");
        return ($RT::SystemUser->id);
    }
    $principals{$src_principal->{ObjectId}} = $dst_principal_id;

    return ($dst_principal_id);

}

sub dst_get_or_create_group {
    my $src_group_id = shift;

    # Get Group infos from src
    my $src_group = get_src_group($src_group_id);
    
    # Bogus DB
    unless ( $src_group ) {
        ask_on_error("E: Group $src_group_id not found on src (will use RT::SystemUser if you choose to continue)");
        return ($RT::SystemUser->id);
    }

    # Search or create DstGroup
    my $DstGroup = RT::Group->new ( $RT::SystemUser );

    $DstGroup->LoadUserDefinedGroup($src_group->{Name});

    unless ( $DstGroup->id ) {
        say LOG "Creating missing group ".$src_group->{Name}." on dst";
        ($val, $msg) = $DstGroup->CreateUserDefinedGroup( 
                Name => $src_group->{Name},
                Description => $src_group->{Description},
                Disabled => $src_group->{Disabled},
                _RecordTransaction => 0,
                );
        unless ( $val ) {
            ask_on_error("E: Failed: $msg (will use RT::SystemUser if you choose to continue)");
            return ($RT::SystemUser->id);
        }

        # Update members
        $src_get_group_members_sth->execute($src_group_id);
        my $src_group_members = $src_get_group_members_sth->fetchall_arrayref( {} );
        foreach my $src_member (@{$src_group_members}) {
            my $dst_member_id = dst_get_or_create_principal($src_member->{MemberId});
            unless ( $DstGroup->HasMember( $dst_member_id ) ) {
                say LOG "Adding $dst_member_id as member";
                ($val, $msg) = $DstGroup->_AddMember( PrincipalId => $dst_member_id, InsideTransaction => 1 );
                unless ( $val ) {
                    ask_on_error("E: Failed: $msg");
                }
            }

        }
    }

    return ($DstGroup->id);

}


sub dst_get_or_create_user {
    my $src_user_id = shift;

    # Get User infos from src
    my $src_user = get_src_user($src_user_id);

    # We may have bogus users
    unless ( $src_user || $src_user->{Name} ) {
        ask_on_error("E: User $src_user_id not found or bogus on src (will use RT::SystemUser if you choose to continue)");
        return ($RT::SystemUser->id);
    }

    # Search or create DstUser
    my $DstUser = RT::User->new ( $RT::SystemUser );

    if ( $src_user->{Email} ) {
        $DstUser->LoadByEmail($src_user->{Email});
    } else {
        $DstUser->Load($src_user->{Name});
    }
    unless ( $DstUser->id ) {
        ( $src_user->{Disabled} ) ||= 0;
        say LOG "Creating missing user ".$src_user->{Name}." on dst";
        ($val, $msg) = $DstUser->Create( 
                Name => $src_user->{Name},
                Password => $src_user->{Password},
                Comments => $src_user->{Comment},
                Signature => $src_user->{Signature},
                EmailAddress => $src_user->{EmailAddress},
                FreeformContactInfo => $src_user->{FreeformContactInfo},
                Organization => $src_user->{Organization},
                RealName => $src_user->{RealName},
                NickName => $src_user->{NickName},
                Lang => $src_user->{Lang},
                EmailEncoding => $src_user->{EmailEncoding},
                WebEncoding => $src_user->{WebEncoding},
                ExternalContactInfoId => $src_user->{ExternalContactInfoId},
                ContactInfoSystem => $src_user->{ContactInfoSystem},
                ExternalAuthId => $src_user->{ExternalAuthId},
                AuthSystem => $src_user->{AuthSystem},
                Gecos => $src_user->{Gecos},
                HomePhone => $src_user->{HomePhone},
                WorkPhone => $src_user->{WorkPhone},
                MobilePhone => $src_user->{MobilePhone},
                PagerPhone => $src_user->{PagerPhone},
                Address1 => $src_user->{Address1},
                Address2 => $src_user->{Address2},
                City => $src_user->{City},
                State => $src_user->{State},
                Zip => $src_user->{Zip},
                Country => $src_user->{Country},
                Timezone => $src_user->{Timezone},
                PGPKey => $src_user->{PGPKey},
                Disabled => $src_user->{Disabled},
                _RecordTransaction => 0,
                );
        unless ( $val ) {
            ask_on_error("E: Failed: $msg (will use RT::SystemUser if you choose to continue)");
            return ($RT::SystemUser->id);
        }    
    }

    # Update privileged status
    $src_user_is_privileged_sth->execute($src_user->{id});
    my @rows = $src_user_is_privileged_sth->fetchrow_array();
    if ( $rows[0] ) {
        unless ( $DstUser->Privileged ) {
            say LOG "Set privileged";
            ($val, $msg) = $DstUser->SetPrivileged(1);
            unless ( $val ) {
                ask_on_error("E: Failed: $msg");
            }
        }
    }


    return ($DstUser->id);

}

sub dst_get_or_create_customfield {
    my $src_cf_id = shift;

    # Caching
    return ($customfields{$src_cf_id}) if ( $customfields{$src_cf_id} );

    # Get CF infos from src
    my $src_cf = get_src_cf($src_cf_id);

    # We may have bogus CF ?
    unless ( $src_cf ) {
        ask_on_error ("E: CustomField $src_cf_id not found on src");
    }

    # Search or create DstCF
    my $DstCF;
    my $DstCFs = RT::CustomFields->new ( $RT::SystemUser );
    $DstCFs->LimitToLookupType($src_cf->{LookupType});
    $DstCFs->Limit( FIELD => 'Name', OPERATOR => '=', VALUE => $src_cf->{Name} );
    if ( $DstCFs->Count ) {
        $DstCF = $DstCFs->First;
    } else {
        say LOG "Creating missing CustomField ".$src_cf->{Name}." on dst";
        $DstCF = RT::CustomField->new ( $RT::SystemUser );
        ($val, $msg) = $DstCF->Create( 
            Name => $src_cf->{Name},
            Type => $src_cf->{Type},
            MaxValues => $src_cf->{MaxValues},
            Repeated => $src_cf->{Repeated},
            Pattern => $src_cf->{Pattern},
            LookupType => $src_cf->{LookupType},
            Description => $src_cf->{Description},
            SortOrder => $src_cf->{SortOrder},
            Creator => dst_get_or_create_principal($src_cf->{Creator}),
            Created => $src_cf->{Created},
            LastUpdatedBy => dst_get_or_create_principal($src_cf->{LastUpdatedBy}),
            LastUpdated => $src_cf->{LastUpdated},
            Disabled => $src_cf->{Disabled},

                );
        unless ( $val ) {
            ask_on_error("E: Failed: $msg");
        }    
        # Sync CustomFields values if any
        if ( $DstCF->Type =~ /^(Select|Combobox)$/ ) {
            $src_get_customfield_values_sth->execute($src_cf->{id});
            my $src_customfield_values = $src_get_customfield_values_sth->fetchall_arrayref( {} );
            foreach my $src_customfield_value (@{$src_customfield_values}) {
                say LOG "Adding value ".$src_customfield_value->{Name};
                ($val, $msg) = $DstCF->AddValue(
                    Name => $src_customfield_value->{Name},
                    Description => $src_customfield_value->{Description},
                    SortOrder => $src_customfield_value->{SortOrder},
                    Category => $src_customfield_value->{Category},
                    );
                unless ( $val ) {
                    ask_on_error("E: Failed: $msg");
                }
            }
        }
    }

    # Apply it to the queue
    unless ( $DstCF->IsApplied($DstQueue->id) || $DstCF->IsApplied(0) ) {
        say LOG "Applying CustomField on queue ".$DstQueue->Name;
        ($val, $msg) = $DstCF->AddToObject($DstQueue);
        unless ( $val ) {
            ask_on_error("E: Failed: $msg");
        }
    }

    $customfields{$src_cf->{id}} = $DstCF->id;
    return ($DstCF->id);

}

##
# Create a CustomField to record src/dst tickets ids
##
my $DstOriginalIdCF = RT::CustomField->new( $RT::SystemUser );
$DstOriginalIdCF->Load( $cf_originalid_name );
unless ( $DstOriginalIdCF->id ) {
    say LOG "Creating CF $cf_originalid_name to record ticket ID correspondences";
    ($val, $msg) = $DstOriginalIdCF->Create(
        Name => $cf_originalid_name,
        Type => 'Freeform',
        MaxValues => 1,
        Description => 'Original Ticket Id (before migration)',
        LookupType => 'RT::Queue-RT::Ticket',
        LinkValueTo => $src_rt_base_url.'/Ticket/Display.html?id=__CustomField__',
    );
    unless ( $val ) {
        ask_on_error("E: Failed: $msg");
    }
}

##
# Load Everyone group, we will use it twice later
##

my $Everyone = RT::Group->new( $RT::SystemUser );
$Everyone->LoadSystemInternalGroup( 'Everyone' );

##
# Check queues on dst
##
foreach my $queue_name (@queues) {

    # Get queue on src
    my $src_queue = $src->selectall_arrayref("SELECT * FROM Queues WHERE Name='$queue_name'", { Slice => {} });
    unless ( $src_queue && scalar(@{$src_queue}) == 1 ) {
        ask_on_error("E: Queue $queue_name not found on src");
    }

    $src_queue = shift @{$src_queue};

    $DstQueue = RT::Queue->new( $RT::SystemUser );
    $DstQueue->Load( $queue_name );
    unless ( $DstQueue->id ) {
        say LOG "Creating queue $queue_name on dst";
        ($val, $msg ) = $DstQueue->Create(
            Name              => $src_queue->{Name},
            CorrespondAddress => $src_queue->{CorrespondAddress},
            Description       => $src_queue->{Description},
            CommentAddress    => $src_queue->{CommentAddress},
            SubjectTag        => $src_queue->{SubjectTag},
            InitialPriority   => $src_queue->{InitialPriority},
            FinalPriority     => $src_queue->{FinalPriority},
            DefaultDueIn      => $src_queue->{DefaultDueIn},
            Sign              => $src_queue->{Sign},
            Encrypt           => $src_queue->{Encrypt},
            );
        unless ( $val ) {
            ask_on_error("E: Failed: $msg");
        }
    }

    # Make sure queue is enabled
    if ( $Queue->Disabled ) {
        ($val, $msg) = $Queue->SetDisabled(0);
        unless ( $val ) {
            ask_on_error("E: Failed to enable queue ".$Queue->Name.": $msg");
        }
    }

    unless ( $Everyone->PrincipalObj->HasRight( Object => $DstQueue, Right => 'OwnTicket' ) ) {
        say LOG "Give temporarly OwnTicket right to Everyone on ".$DstQueue->Name;
        ($val, $msg) = $Everyone->PrincipalObj->GrantRight( Object => $DstQueue, Right => 'OwnTicket' );
        unless ( $val ) {
            ask_on_error("E: Failed: $msg");
        }
    }

    my $DstQueueCFs = $DstQueue->CustomFields;
    $DstQueueCFs->Limit( FIELD => 'id', VALUE => $DstOriginalIdCF->id );
    unless ( $DstQueueCFs->Count ) {
        # Apply it to the queue
        say LOG "Applying CF $cf_originalid_name to dst queue";
        my $OCF = RT::ObjectCustomField->new($RT::SystemUser);
        ($val, $msg) = $OCF->Create(
            CustomField => $DstOriginalIdCF->id,
            ObjectId    => $DstQueue->id,
            );
        unless ( $val ) {
            ask_on_error("E: Failed: $msg");
        }
    }

    # Walk through src tickets
    $src_get_tickets_sth->execute($queue_name);
    my $src_tickets = $src_get_tickets_sth->fetchall_arrayref( {} );
    say "Importing tickets for queue $queue_name" if ( scalar @{$src_tickets} );
    $i = 0;
    foreach my $src_ticket (@{$src_tickets} ) {
        $i++;
        printf ("\rProgress: %0.2f%%", 100 * $i / scalar @{$src_tickets});

        #
        ## Ticket
        #

        say LOG "Importing ticket: ".$src_ticket->{id};
        # Check if it has not already been imported (to be able to run the script
        # multiple time)
        my $CheckDstTickets = RT::Tickets->new( $RT::SystemUser );
        $CheckDstTickets->LimitCustomField( CUSTOMFIELD => $cf_originalid_name, VALUE => $src_ticket->{id} );
        if ( $CheckDstTickets->Count ) {
         say LOG "Already imported";
         delete $@{$src_tickets}{$src_ticket};
         delete $tickets{$src_ticket->{id}};
         next;
        }

        my $DstTicket = RT::Ticket->new( $RT::SystemUser );
        ($ticketid, $msg) = $DstTicket->Create(
            Queue => $DstQueue->id,
            Type => $src_ticket->{Type},
            IssueStatement => $src_ticket->{IssueStatement},
            Resolution => $src_ticket->{Resolution},
            Owner => dst_get_or_create_principal($src_ticket->{Owner}),
            Subject => $src_ticket->{Subject},
            InitialPriority => $src_ticket->{InitialPriority},
            FinalPriority => $src_ticket->{FinalPriority},
            Priority => $src_ticket->{Priority},
            TimeEstimated => $src_ticket->{TimeEstimated},
            TimeWorked => $src_ticket->{TimeWorked},
            Status => $src_ticket->{Status},
            TimeLeft => $src_ticket->{TimeLeft},
            Told => $src_ticket->{Told},
            Starts => $src_ticket->{Starts},
            Started => $src_ticket->{Started},
            Due => $src_ticket->{Due},
            Resolved => $src_ticket->{Resolved},
            LastUpdatedBy => dst_get_or_create_principal($src_ticket->{LastUpdatedBy}),
            LastUpdated => $src_ticket->{LastUpdated},
            Creator => dst_get_or_create_principal($src_ticket->{Creator}),
            Created => $src_ticket->{Created},
            Disabled => $src_ticket->{Disabled},
            _RecordTransaction => 0,
            );

        unless ( $ticketid ) {
            ask_on_error("E: failed: $msg");
        }

        # Record new id
        say LOG "Recording Src ID in CF";
        $tickets{$src_ticket->{id}} = $ticketid;
        ($val, $msg) = $DstTicket->AddCustomFieldValue( 
            Field => $DstOriginalIdCF->id, 
            Value => $src_ticket->{id},
            RecordTransaction => 0,
            );
        unless ( $val ) {
            ask_on_error("E: Failed: $msg");
        }
        say LOG "Old id: ".$src_ticket->{id}.", new id: $ticketid";

    }
    # End of progress information
    print "\n";

    say LOG "Revoke temporarly OwnTicket right to Everyone on ".$DstQueue->Name;
    ($val, $msg) = $Everyone->PrincipalObj->RevokeRight( Object => $DstQueue, Right => 'OwnTicket' );
    unless ( $val ) {
        ask_on_error("E: Failed: $msg");
    }

}

# Update Tickets sub-objects (now that we have all old/new ids)
say "Importing ticket sub-objects" if ( scalar keys %tickets );
$i = 0;
foreach my $src_ticket_id (keys %tickets ) {
    $i++;
    printf ("\rProgress: %0.2f%%", 100 * $i / scalar keys %tickets);

    my $dst_ticket_id = $tickets{$src_ticket_id};
    say LOG "Importing objects for ticket src:$src_ticket_id dst:$dst_ticket_id";
    #
    ## Ticket CustomField Values
    #
    my %ticket_ocfvs;
    $src_get_ticket_ocfvs_sth->execute($src_ticket_id);
    my $src_ticket_ocfvs = $src_get_ticket_ocfvs_sth->fetchall_arrayref( {} );
    foreach my $src_ticket_ocfv (@{$src_ticket_ocfvs}) {
        say LOG "Importing ticket customfield value:" .$src_ticket_ocfv->{id};
        my $DstOCFV = RT::Record->new( $RT::SystemUser );
        $DstOCFV->Table('ObjectCustomFieldValues');
        $DstOCFV->_Init( $RT::SystemUser );
        ($val, $msg) = $DstOCFV->Create(
            CustomField => dst_get_or_create_customfield($src_ticket_ocfv->{CustomField}),
            ObjectType => $src_ticket_ocfv->{ObjectType},
            ObjectId => $dst_ticket_id,
            SortOrder => $src_ticket_ocfv->{SortOrder},
            Content => $src_ticket_ocfv->{Content},
            LargeContent => $src_ticket_ocfv->{LargeContent},
            ContentType => $src_ticket_ocfv->{ContentType},
            ContentEncoding => $src_ticket_ocfv->{ContentEncoding},
            Creator => dst_get_or_create_principal($src_ticket_ocfv->{Creator}),
            Created => $src_ticket_ocfv->{Created},
            LastUpdatedBy => dst_get_or_create_principal($src_ticket_ocfv->{LastUpdatedBy}),
            LastUpdated => $src_ticket_ocfv->{LastUpdated},
            Disabled => $src_ticket_ocfv->{Disabled},
        );
        unless ( $val ) {
            ask_on_error("E: Failed: $msg");
        }
        $ticket_ocfvs{$src_ticket_ocfv->{id}} = $DstOCFV->id;
    }

    #
    ## Ticket links
    #

    $src_get_ticket_links_sth->execute($src_ticket_id);
    my $src_ticket_links = $src_get_ticket_links_sth->fetchall_arrayref( {} );
    foreach my $src_ticket_link (@{$src_ticket_links}) {
        say LOG "Importing link: ".$src_ticket_link->{id};

        # Id/URI update
        my $base_uri = convert_uri($src_ticket_link->{Base});
        my $target_uri = convert_uri($src_ticket_link->{Target});

        my $DstLink = RT::Link->new( $RT::SystemUser );
        ($val, $msg) = $DstLink->Create(
            Base => $base_uri,
            Target => $target_uri,
            Type => $src_ticket_link->{Type},
            LocalTarget => $tickets{$src_ticket_link->{LocalTarget}},
            LocalBase => $tickets{$src_ticket_link->{LocalBase}},
            LastUpdatedBy => dst_get_or_create_principal($src_ticket_link->{LastUpdatedBy}),
            LastUpdated => $src_ticket_link->{LastUpdated},
            Creator => dst_get_or_create_principal($src_ticket_link->{Creator}),
            Created => $src_ticket_link->{Created},,
        );
        unless ( $val ) {
            ask_on_error("E: Failed: $msg");
        }
    }

    #
    ## Ticket watchers
    #

    $src_get_ticket_watchers_sth->execute($src_ticket_id);
    my $src_ticket_watchers = $src_get_ticket_watchers_sth->fetchall_arrayref( {} );
    foreach my $src_ticket_watcher (@{$src_ticket_watchers}) {
        say LOG "Importing watcher ".$src_ticket_watcher->{Type}.": ".$src_ticket_watcher->{MemberId};
        my $DstWatcherGroup = RT::Group->new( $RT::SystemUser );
        $DstWatcherGroup->LoadTicketRoleGroup(Type => $src_ticket_watcher->{Type}, Ticket => $dst_ticket_id);
        unless ( $DstWatcherGroup->id ) {
            ask_on_error("E: Failed to load group: $msg");
        }
        ($val, $msg) = $DstWatcherGroup->_AddMember(PrincipalId => dst_get_or_create_principal($src_ticket_watcher->{MemberId}), InsideTransaction => 1);
        unless ( $val ) {
            ask_on_error("E: Failed: $msg");
        }
    }

    #
    ## Ticket transactions
    #

    my %transactions;
    # Ticket transactions
    $src_get_ticket_transactions_sth->execute($src_ticket_id);
    my $src_ticket_transactions = $src_get_ticket_transactions_sth->fetchall_arrayref( {} );
    foreach my $src_ticket_transaction (@{$src_ticket_transactions}) {
        say LOG "Importing ticket transaction: ".$src_ticket_transaction->{id};
        my $DstTicketTransaction = RT::Record->new( $RT::SystemUser );
        $DstTicketTransaction->Table('Transactions');
        $DstTicketTransaction->_Init( $RT::SystemUser );

        # Some ID changes
        my $transaction_field = $src_ticket_transaction->{Field};
        $transaction_field = $customfields{$src_ticket_transaction->{Field}} if ( $src_ticket_transaction->{Type} && $src_ticket_transaction->{Type} eq 'CustomField' );
        my $transaction_oldvalue = $src_ticket_transaction->{OldValue};
        my $transaction_newvalue = $src_ticket_transaction->{NewValue};
        if ( $transaction_field && $transaction_field =~ /^(Owner|Cc|Requestor|AdminCc)$/ ) {
            $transaction_oldvalue = dst_get_or_create_principal($src_ticket_transaction->{OldValue}) if ( $src_ticket_transaction->{OldValue} );
            $transaction_newvalue = dst_get_or_create_principal($src_ticket_transaction->{NewValue}) if ( $src_ticket_transaction->{NewValue} );
        }
        if ( $transaction_oldvalue && $transaction_oldvalue =~ m|^fsck.com-rt://.*/ticket/(\d+)$| ) {
            $transaction_oldvalue = convert_uri($transaction_oldvalue);
        }

        if ( $transaction_newvalue && $transaction_newvalue =~ m|^fsck.com-rt://.*/ticket/(\d+)$| ) {
            $transaction_newvalue = convert_uri($transaction_newvalue);
        }

        my $transaction_oldreference = $src_ticket_transaction->{OldReference};
        my $transaction_newreference = $src_ticket_transaction->{NewReference};
        if ( $src_ticket_transaction->{ReferenceType} && $src_ticket_transaction->{ReferenceType} eq 'RT::ObjectCustomFieldValue' ) {
            $transaction_oldreference = $ticket_ocfvs{$src_ticket_transaction->{OldReference}} if ( $src_ticket_transaction->{OldReference} );
            $transaction_newreference = $ticket_ocfvs{$src_ticket_transaction->{NewReference}} if ( $src_ticket_transaction->{NewReference} );
        }


        ($val, $msg) = $DstTicketTransaction->Create(
            ObjectType => 'RT::Ticket',
            ObjectId => $dst_ticket_id,
            TimeTaken => $src_ticket_transaction->{TimeTaken},
            Type => $src_ticket_transaction->{Type},
            Field => $transaction_field,
            OldValue => $transaction_oldvalue,
            NewValue => $transaction_newvalue,
            ReferenceType => $src_ticket_transaction->{ReferenceType},
            OldReference => $transaction_oldreference,
            NewReference => $transaction_newreference,
            Data => $src_ticket_transaction->{Data},
            Creator => dst_get_or_create_principal($src_ticket_transaction->{Creator}), 
            Created => $src_ticket_transaction->{Created},
        );
        unless ( $val ) {
            ask_on_error("E: Failed: $msg");
        }

        $transactions{$src_ticket_transaction->{id}} = $DstTicketTransaction->id;

        #
        ## Transaction CustomField Values
        #
        $src_get_transaction_ocfvs_sth->execute($src_ticket_transaction->{id});
        my $src_transaction_ocfvs = $src_get_transaction_ocfvs_sth->fetchall_arrayref( {} );
        foreach my $src_transaction_ocfv (@{$src_transaction_ocfvs}) {
            say LOG "Importing transaction customfield value:" .$src_transaction_ocfv->{id};
            my $DstOCFV = RT::Record->new( $RT::SystemUser );
            $DstOCFV->Table('ObjectCustomFieldValues');
            $DstOCFV->_Init( $RT::SystemUser );
            ($val, $msg) = $DstOCFV->Create(
                CustomField => dst_get_or_create_customfield($src_transaction_ocfv->{CustomField}),
                ObjectType => $src_transaction_ocfv->{ObjectType},
                ObjectId => $DstTicketTransaction->id,
                SortOrder => $src_transaction_ocfv->{SortOrder},
                Content => $src_transaction_ocfv->{Content},
                LargeContent => $src_transaction_ocfv->{LargeContent},
                ContentType => $src_transaction_ocfv->{ContentType},
                ContentEncoding => $src_transaction_ocfv->{ContentEncoding},
                Creator => dst_get_or_create_principal($src_transaction_ocfv->{Creator}),
                Created => $src_transaction_ocfv->{Created},
                LastUpdatedBy => dst_get_or_create_principal($src_transaction_ocfv->{LastUpdatedBy}),
                LastUpdated => $src_transaction_ocfv->{LastUpdated},
                Disabled => $src_transaction_ocfv->{Disabled},
            );
            unless ( $val ) {
                ask_on_error("E: Failed: $msg");
            }
        }

        #
        ## Transaction Attachments
        #

        my %attachments;
        $src_get_transaction_attachments_sth->execute($src_ticket_transaction->{id});
        my $src_transaction_attachments = $src_get_transaction_attachments_sth->fetchall_arrayref( {} );
        foreach my $src_transaction_attachment (@{$src_transaction_attachments}) {
            say LOG "Importing attachment: ".$src_transaction_attachment->{id};
            my $parentid = ( $src_transaction_attachment->{Parent} == 0 ) ? 0 : $attachments{$src_transaction_attachment->{Parent}};
            my $DstTransactionAttachment = RT::Record->new( $RT::SystemUser );
            $DstTransactionAttachment->Table('Attachments');
            $DstTransactionAttachment->_Init( $RT::SystemUser );
            ($val, $msg) = $DstTransactionAttachment->Create(
                TransactionId => $transactions{$src_transaction_attachment->{TransactionId}},
                Parent => $parentid,
                MessageId => $src_transaction_attachment->{MessageId},
                Subject => $src_transaction_attachment->{Subject},
                Filename => $src_transaction_attachment->{Filename},
                ContentType => $src_transaction_attachment->{ContentType},
                ContentEncoding => $src_transaction_attachment->{ContentEncoding},
                Content => $src_transaction_attachment->{Content},
                Headers => $src_transaction_attachment->{Headers},
                Creator => dst_get_or_create_principal($src_transaction_attachment->{Creator}),
                Created => $src_transaction_attachment->{Created},
            );
            unless ( $val ) {
                ask_on_error("E: Failed: $msg");
            }
            $attachments{$src_transaction_attachment->{id}} = $DstTransactionAttachment->id;

        }

    }

}
# End of progress information
print "\n";

# Finish statements
$src_get_tickets_sth->finish();
$src_get_ticket_transactions_sth->finish();
$src_get_transaction_attachments_sth->finish();
$src_get_user_sth->finish();
$src_get_principal_sth->finish();
$src_get_group_sth->finish();
$src_get_ticket_ocfvs_sth->finish();
$src_get_transaction_ocfvs_sth->finish();
$src_get_cf_sth->finish();
$src_get_ticket_links_sth->finish();
$src_user_is_privileged_sth->finish();
$src_get_ticket_watchers_sth->finish();
$src_get_group_members_sth->finish();
$src_get_customfield_values_sth->finish();


$src->disconnect;

print_time_to_process;

close(LOG);

