package RequestLog;

use Moose;
with (
	'Role::User',
	'Role::RebuildTables',
);
	
use namespace::clean -except => 'meta';

use Data::Dumper;

has $_ => (is => 'ro', isa => 'HashRef', required => 1)
	foreach qw( db sql );

has log_file => ( is => 'ro', required => 1 );

__PACKAGE__->meta->make_immutable;

$|++;

sub convert {
    my $self = shift;
    
    $self->do_changelog();
    $self->do_phonelog();
    $self->do_dispatchlog();
    $self->do_viewlog();
    $self->do_printlog();
    $self->do_general_notes();
}

sub do_changelog {
    my $self = shift;

    my $dbix3 = $self->db->{dbix3};
    my $dbix4 = $self->db->{dbix4};

    my $log_file = $self->log_file;

    #$self->rebuild_tables('request_report_history');
    $self->rebuild_tables_asMyISAM('request_report_history');
    
	my $hilis3 = $dbix3->query(q!select CL_Rpt_ID, Field, Content,
        if(UserID is not null, UserID, ModifiedBy) as User, DateTime,
        HMDS, year(Date) as 'year' from ChangeLog left join Main on
        CL_Rpt_ID = DBID left join Users on ModifiedBy = Signature
		where length(Content) > 4!); # random crap + all Outreach have NULL (as Str) entry
	
	ROW:
	while ( my $vals = $hilis3->hash ) { 
		my $request_id = $dbix4->query( q!select id from requests where request_number
			= ? and year = ?!, $vals->{hmds}, $vals->{year} )->list;
		
		if (! $request_id ) {
			warn "no request_id for $vals->{cl_rpt_id}";
			next ROW;
		}
		
        my $user = $vals->{user};
        
        my $user_id = $self->_get_user_id(lc $user)
        || die "no user_id for $user ($vals->{cl_rpt_id})";
        
		my %data = (
			request_id => $request_id,
            field      => lc $vals->{field},
            content    => $vals->{content},
			user_id    => $user_id,
            time       => $vals->{datetime},
		);
		$dbix4->insert('request_report_history', \%data);
    }
    $self->convert_to_InnoDB('request_report_history');
}

sub do_dispatchlog {
    my $self = shift;

    my $dbix3 = $self->db->{dbix3};
    my $dbix4 = $self->db->{dbix4};

    my $log_file = $self->log_file;

    #$self->rebuild_tables('request_dispatch_log');
    $self->rebuild_tables_asMyISAM('request_dispatch_log');

	my $hilis3 = $dbix3->query(q!select * from MailedReports!);
	
	LOG:
	while ( my $vals = $hilis3->hash ) { 
		my ($hmds, $yr) = $vals->{labno} =~ m!H(\d+)/(\d{2})!;
		
		my $request_id = $dbix4->query( q!select id from requests where request_number
			= ? and year = ?!, $hmds, $yr + 2000 )->list;
		
		if (! $request_id ) {
			print $log_file "no request_id for $vals->{labno}\n";
			next LOG;
		}
		my %data = (
			request_id => $request_id,
			recipient  => $vals->{recipient},
			time       => $vals->{datetime},
		);
		$dbix4->insert('request_dispatch_log', \%data);
	}
    $self->convert_to_InnoDB('request_dispatch_log');
}

sub do_phonelog {
    my $self = shift;

    my $dbix3 = $self->db->{dbix3};
    my $dbix4 = $self->db->{dbix4};

    my $log_file = $self->log_file;

    #$self->rebuild_tables('request_phone_log');
    $self->rebuild_tables_asMyISAM('request_phone_log');

	my $hilis3 = $dbix3->query(q!select * from PhoneLog!);
	
	LOG:
	while ( my $vals = $hilis3->hash ) { 
		my ($hmds, $yr) = $vals->{labno} =~ m!H(\d+)/(\d{2})!;
		
		my $request_id = $dbix4->query( q!select id from requests where request_number
			= ? and year = ?!, $hmds, $yr + 2000 )->list;
		
		if (! $request_id ) {
			print $log_file "no request_id for $vals->{labno}\n";
			next LOG;
		}
		
		my $user_id = $self->_get_user_id(lc $vals->{userid});
		
		if (! $user_id ) {
			print $log_file "$0: no user_id for $vals->{userid}";
			next LOG;
		}
		
		my %data = (
			request_id => $request_id,
			user_id    => $user_id,
			status     => lc $vals->{direction} . 'bound',
			contact    => $vals->{contact},
			details    => $vals->{summary},
			time       => $vals->{datetime},
		);
		$dbix4->insert('request_phone_log', \%data);
    }
    $self->convert_to_InnoDB('request_phone_log');
}

sub do_viewlog {
    my $self = shift;

    my $dbix3 = $self->db->{dbix3};
    my $dbix4 = $self->db->{dbix4};

    my $log_file = $self->log_file;

    #$self->rebuild_tables('request_view_log');
    $self->rebuild_tables_asMyISAM('request_view_log');

	my $hilis3 = $dbix3->query(q!select * from ViewLog!);
	
	LOG:
	while ( my $vals = $hilis3->hash ) { 
		my ($hmds, $yr) = $vals->{lab_no} =~ m!H(\d+)/(\d{2})!;
		
		my $request_id = $dbix4->query( q!select id from requests where request_number
			= ? and year = ?!, $hmds, $yr + 2000 )->list;
		
		if (! $request_id ) {
			print $log_file "no request_id for $vals->{lab_no}\n";
			next LOG;
		}
		
		
		my $user_id = $self->_get_user_id(lc $vals->{user_id});
		
		if (! $user_id ) {
			print $log_file "$0: no user_id for $vals->{user_id}";
			next LOG;
		}
		
		my %data = (
			request_id => $request_id,
			ip_address => $vals->{ip_address},
			user_id    => $user_id,
			time       => $vals->{date_time},
		);
		$dbix4->insert('request_view_log', \%data);
    }
    $self->convert_to_InnoDB('request_view_log');
}

sub do_printlog {
    my $self = shift;

    my $dbix3 = $self->db->{dbix3};
    my $dbix4 = $self->db->{dbix4};

    my $log_file = $self->log_file;

    #$self->rebuild_tables('request_print_log');
    $self->rebuild_tables_asMyISAM('request_print_log');

	my $hilis3 = $dbix3->query(q!select * from PrintLog!);
	
	LOG:
	while ( my $vals = $hilis3->hash ) { 
		my ($hmds, $yr) = $vals->{hmds} =~ m!H(\d+)/(\d{2})!;
		
		my $request_id = $dbix4->query( q!select id from requests where request_number
			= ? and year = ?!, $hmds, $yr + 2000 )->list;
		
		if (! $request_id ) {
			print $log_file "no request_id for $vals->{hmds}\n";
			next LOG;
		}
		
		my $user_id = $self->_get_user_id(lc $vals->{userid});
		
		if (! $user_id ) {
			print $log_file "$0: no user_id for $vals->{userid}";
			next LOG;
		}
		
		my %data = (
			request_id => $request_id,
			user_id    => $user_id,
			time       => $vals->{datetime},
		);
		$dbix4->insert('request_print_log', \%data);
    }
    $self->convert_to_InnoDB('request_print_log');
}

sub do_general_notes {
    my $self = shift;
	
    my $dbh3  = $self->db->{dbix3};
    my $dbh4  = $self->db->{dbix4};
	
    # $self->rebuild_tables('request_general_notes');		
    $self->rebuild_tables_asMyISAM('request_general_notes');		

	my $sql = q!select HMDS, year(Date) as 'year', Notes from Main left join Others
		on O_ID = DBID where length(Notes) > 1!; # exclude crap
	
	my $src = $dbh3->query( $sql );
	while ( my $vals = $src->hash ) {
		my $request_id = $dbh4->query( q!select id from requests where request_number
			= ? and year = ?!, $vals->{hmds}, $vals->{year})->list;
			
		my %data = (
			request_id => $request_id,
			detail     => $vals->{notes},
		);
		$dbh4->insert('request_general_notes', \%data);
	}
    $self->convert_to_InnoDB('request_general_notes');
}

1;