#!/usr/bin/perl
package Script::RCube::Mysql2Sqlite;
use strict;

# cpanel - convert_roundcube_mysql2sqlite         Copyright(c) 2010 cPanel, Inc.
#                                                           All rights Reserved.
# copyright@cpanel.net                                         http://cpanel.net
# This code is subject to the cPanel license. Unauthorized copying is prohibited

BEGIN { unshift @INC, '/usr/local/cpanel'; }

use Cpanel::Config ();
use Cpanel::Config::LoadCpConf ();
use Cpanel::MysqlUtils ();
use Cpanel::MysqlRun ();
use Cpanel::Config::LoadUserDomains ();
use Cpanel::Config::LoadCpUserFile ();
use Cpanel::AcctUtils::GetHomeDir ();
use Cpanel::Email::RoundCube ();

use Cpanel::PwCache ();
use Cpanel::AccessIds::SetUids ();
use Cpanel::Logger ();
use Cpanel::Filesys ();

use File::Slurp ();
use DBI;
use XML::Simple ();

my $sqlite_table_file = '/usr/local/cpanel/base/3rdparty/roundcube/SQL/sqlite.initial.sql';

my $log_file = '/usr/local/cpanel/logs/roundcube_sqlite_convert_log';
my %opts = ( 'alternate_logfile' => $log_file );
my $logger = Cpanel::Logger->new( \%opts );
## note 10/22: removing support for 'messages' table, as it is effectively only an IMAP cache,
##   and the most likely cause of conversion hiccups.
my @dumptables = qw(users identities contacts);

my $time = time();

## if invoked as a script, there is nothing in the call stack
my $invoked_as_script = ! caller();
__PACKAGE__->script(@ARGV) if ($invoked_as_script);

sub script {
    my($package, $opt_user, @args) = @_;
    my $cpconf = Cpanel::Config::LoadCpConf::loadcpconf();

    my $rcube_db = 'mysql';
    if (exists $cpconf->{'roundcube_db'} and ($cpconf->{'roundcube_db'} eq 'sqlite')) {
        $rcube_db = 'sqlite';
    }

    if (defined $opt_user) {
        ## the optional user arg feature is currently *only* called as a script;
        ##   otherwise, all these exits would check $invoked_as_script

        if ($rcube_db eq 'sqlite' && init_check($cpconf)) {
            my $wasSuccess = convert_mysql_roundcube_to_sqlite($opt_user);
            if ($wasSuccess) {
                ## if the conversion was a complete success (as checked above), we can safely archive and
                ##   delete the MySQL roundcube database. This will prevent future problems with transfers,
                ##   and in fact is the final solution for the original Roundcube case 12162!
                my $has_db = 1;  ## already asserted via &init_check
                my $archive_success = Cpanel::Email::RoundCube::archive_and_drop_mysql_roundcube($has_db);
                exit(0);
            }
            print "Conversion for user '$opt_user' was not successful.\n";
            exit(1);
        } else {
            ## TODO @GOLIVE: update-roundcube-sqlite gets moved to update-roundcube. Update this message and the conditional @GOIVE.
            print "The optional user argument is to be used only when Roundcube has been converted to sqlite. Use bin/update-roundcube-sqlite Exiting.\n";
        }
        exit(1);
    }

    ## Has the roundcube conversion already happened?
    ## note: this check is here, and not in init_check, because Transfers.pm calls into this
    ##   from a different context. The src server uses MySQL but the dest machine has done
    ##   the sqlite conversion.
    if ($rcube_db eq 'sqlite') {
        print "Roundcube conversion already occurred, bailing out.\n";
        if ($invoked_as_script) {
            exit(0);
        }
        return 1;
    }

    unless (init_check($cpconf)) {
        if ($invoked_as_script) {
            exit(0);
        }
        return 1;
    }

    my %TRUEDOMAINS;
    Cpanel::Config::LoadUserDomains::loadtrueuserdomains( \%TRUEDOMAINS );
    my @users = sort values %TRUEDOMAINS;
    my $success_cnt = 0;
    my $success_verify = scalar @users;

    for my $user (@users) {
        $success_cnt += convert_mysql_roundcube_to_sqlite($user);
    }
    
    my $DIR = '/usr/local/cpanel/base/3rdparty';
    Cpanel::Email::RoundCube::generate_roundcube_config_sqlite($DIR);

    unless ($success_cnt == $success_verify) {
        $logger->warn("Roundcube Mysql to sqlite conversion was not completely successful. Please check $log_file for details.");

        if ($invoked_as_script) {
            exit(1);
        }
        return 0;
    }

    ## if the conversion was a complete success (as checked above), we can safely archive and
    ##   delete the MySQL roundcube database. This will prevent future problems with transfers,
    ##   and in fact is the final solution for the original Roundcube case 12162!
    my $has_db = 1;  ## already asserted via &init_check
    my $archive_success = Cpanel::Email::RoundCube::archive_and_drop_mysql_roundcube($has_db);

    $cpconf->{'roundcube_db'} = 'sqlite';
    Cpanel::Config::savecpconf($cpconf);

    if ($invoked_as_script) {
        Cpanel::Email::RoundCube::restart_cpsrvd();
        exit(0);
    }

    return 1;
}

sub convert_mysql_roundcube_to_sqlite {
    my($user) = @_;
    my @domains = collect_domains($user);

    my @uid_name_pairs = collect_roundcube_user_info(\@domains, $user);

    ## returning 1: no conversion necessary
    return 1 unless (scalar @uid_name_pairs);
    
    my $best_mnt_point = Cpanel::Filesys::getmntpoint();
    my $tmpdir_root_base = "$best_mnt_point/roundcube_convert";

    if (-d $tmpdir_root_base) {
        rename($tmpdir_root_base, "$tmpdir_root_base.$time");
    }
    mkdir($tmpdir_root_base);

    my $tmpdir_root_sys = "$tmpdir_root_base/$user";
    mkdir($tmpdir_root_sys);

    my @pwinfo = Cpanel::PwCache::getpwnam($user);
    my ( $mmuid, $mmgid, $homedir ) = @pwinfo[2, 3, 7];
    my $tmpdir_emailuser_base = "$homedir/tmp/roundcube_convert";

    my $success_cnt = 0;
    for my $email_user_info (@uid_name_pairs) {
        my($uid, $email_user) = @$email_user_info;
        
        my $tmpdir_root_sys_emailuser = "$tmpdir_root_sys/$email_user";
        mkdir($tmpdir_root_sys_emailuser);

        do_mysqldumps_for_user($uid, $tmpdir_root_sys_emailuser);

        ## ensures that $best/rcube/$sysuser/$emailuser is readable by $sysuser,
        ##   so that $sysuser can move it to their $homedir after the setuid
        my $rv_chown = system("chown -R $mmuid:$mmgid $tmpdir_root_sys");
        my $rv_chmod = system("chmod -R 700 $tmpdir_root_sys");

        my $rv_convert = xml_dump_to_sqlite($user, $tmpdir_root_sys_emailuser, $tmpdir_emailuser_base, $email_user, $homedir);

        unless ($rv_convert) {
            $logger->warn("Conversion was not successful for user '$user'.");
        }
        $success_cnt += $rv_convert;

        ## note: no need to "rmdir($tmpdir_root_sys_emailuser)", as this dir was moved
        ##   to $homedir/tmp/rcube/$emailuser (during the setuid)

        ## TODO @GOLIVE?: remove the mysql roundcube database (steal mysql/roundcube backup/archive
        ##   clauses from update-roundcube, the mysql version)
    }

    rmdir($tmpdir_root_sys);
    rmdir($tmpdir_root_base);

    return $success_cnt == scalar(@uid_name_pairs);
}

sub collect_domains {
    my($user) = @_;
    my $cpuser_ref = Cpanel::Config::LoadCpUserFile::loadcpuserfile($user);    
    my @DOMAINS = ( $cpuser_ref->{'DOMAIN'} );
    if ( ref $cpuser_ref->{'DOMAINS'} eq 'ARRAY' ) {
        push @DOMAINS, @{ $cpuser_ref->{'DOMAINS'} };
    }

    ## Filter out all ^www. and domain values of 'asterix'. The '?:' is not needed.
    ## case 22546
    my @acceptable_domains = grep( ! /(?:^www\.|\*)/i, @DOMAINS );
    return @acceptable_domains;
}

sub collect_roundcube_user_info {
    my($ar_domains, $user) = @_;
    my $dnslist = join( '|', @$ar_domains );
    $dnslist =~ s/\./\\./g;
    ## -B: batch; -ss: more silent that -s;
    my $users_str = qx{ mysql roundcube -B -ss -e "SELECT user_id, username FROM users WHERE username REGEXP '@(${dnslist})\$' or username = '$user'" };
    my @id_name_str = split /\n/, $users_str;
    
    my @ids;
    for my $x (@id_name_str) {
        my($id, $name) = split(/\t/, $x);
        push(@ids, [$id, $name]);
    }
    return @ids;
}

## Stolen from pkgacct; needs to be modularized!
sub mysqldumpdb {
    my ($args) = @_;
    my @options   = @{ $args->{'options'} };
    my $db        = $args->{'db'};
    my $table     = $args->{'table'};
    my $file      = $args->{'file'};
    my $file_mode = $args->{'append'} ? '>>' : '>';

    my $mysqldump = Cpanel::MysqlUtils::find_mysqldump();
    my @db        = ($db);
    if ($table) {
        push @db, $table;
    }
    #print join( '.', @db ) . ' ';
    my $pid = IPC::Open3::open3( my $w, my $r, '', $mysqldump, @options, @db );

    my $first_line = 1;
    if ( open( my $fh, $file_mode, $file ) ) {
        while (<$r>) {
            if ( $first_line && ( !$_ || m/^mysqldump:/ ) ) {
                warn join( '.', @db ) . ': ' . $_;
                close $w;
                close $r;
                waitpid( $pid, 0 );
                $first_line = 0;
                my $mysqlcheck = Cpanel::MysqlUtils::find_mysqlcheck();
                system( $mysqlcheck, '--repair', @db );
                $pid = IPC::Open3::open3( $w, $r, '', $mysqldump, @options, @db );
            }
            else {
                print {$fh} $_;
            }
        }
    }
    close $w;
    close $r;
    waitpid( $pid, 0 );
}

sub do_mysqldumps_for_user {
    my($uid, $tmp_convertdir) = @_;
        
    ## XML output, not supressing create table statements, with a where clause on user_id list
    my @options = ( '--xml', '-w' );
    for my $tbl (@dumptables) {
        mysqldumpdb( { 'options' => [ @options, qq{user_id = $uid} ], 'db' => 'roundcube',
                       'file' => "$tmp_convertdir/rcube.$tbl.xml", 'table' => $tbl } );
    }
    return undef;
}

sub create_rcube_sqlite_tables {
    my($dbh) = @_;
    my $contents = File::Slurp::slurp($sqlite_table_file);

    ## The while loop regex grabs all CREATE statements (note the non-greedy dot-star, followed
    ##   by a literal ');'. See the sqlite.initial.sql file for the 10 actual uses.
    while ( $contents =~ m/^(CREATE .*?\)\;)/msg ) {
        my $create_statement = $1;
        my $sth = $dbh->prepare($create_statement);
        my $rv = $sth->execute();
    }
    return undef;
}

sub do_all_rcube_xml_to_db {
    my($dbh, $xmldir) = @_;

    my $success_cnt = 0;
    ## verify the number of XML docs to convert to SQL
    my $success_verify = scalar(@dumptables);

    {
        ## note: there is a disparity in the sqlite.initial.sql, and the "table_structure" for "users";
        ##   preferences is not NULLABLE.
        my @_cols = qw(user_id username mail_host alias created last_login language preferences);
        my @_needs_quote = qw(username mail_host alias created last_login language preferences);

        $success_cnt += rcube_xml_to_db($xmldir, 'users', $dbh, \@_cols, \@_needs_quote, );
    }

    {
        my @_cols = qw(identity_id del standard name organization email reply-to bcc
                       signature html_signature user_id);
        my @_needs_quote = qw(name organization email reply-to bcc signature);
        my @_nullables = qw(organization);

        $success_cnt += rcube_xml_to_db($xmldir, 'identities', $dbh, \@_cols, \@_needs_quote, \@_nullables);

    }

    {
        my @_cols = qw(contact_id changed del name email firstname surname vcard user_id);
        my @_needs_quote = qw(changed name email firstname surname vcard);

        $success_cnt += rcube_xml_to_db($xmldir, 'contacts', $dbh, \@_cols, \@_needs_quote);
    }

    ## note 10/22: removing support for 'messages' table, as it is effectively only an IMAP cache,
    ##   and the most likely cause of conversion hiccups.

    return $success_cnt == $success_verify;
}

sub rcube_xml_to_db {
    my($xmldir, $tbl, $dbh, $ar_cols, $ar_needs_quote, $ar_nullables) = @_;

    my $xml_fname = "$xmldir/rcube.$tbl.xml";

    my %common_opts = (ForceArray => 1, KeyAttr => [], ContentKey => '__content');

    ## $ref->{database}->[0]->{table_data}->[0]->{row}->[$x]->{field}->[$x]->{__content}
    my $ref = XML::Simple::XMLin($xml_fname, %common_opts);
    my $rows = $ref->{'database'}->[0]->{'table_data'}->[0]->{'row'};
    ## FWIW, Perl does not complain if $rows is undef, when called in a for loop context.

    for my $row ( @$rows ) {
        my $hr_vals = process_row($row, $dbh, $ar_needs_quote, $ar_nullables);
        my $sql = rcube_make_sql($tbl, $ar_cols, $hr_vals);
        #print "$sql\n";
        
        my $sth = $dbh->prepare($sql);
        my $rv = $sth->execute();
        unless ($rv) {
            ## ???: where does this log to?
            print "Conversion was not successful: ", $dbh->errstr(), "\n";
            return 0;
        }
    }

    return 1;
}


sub rcube_make_sql {
    my($table, $ar_cols, $hr_vals) = @_;
    
    my @values;
    for my $col (@$ar_cols) {
        push(@values, $hr_vals->{$col});
    }

    ## put the column names in single quotes. only really needed for 'reply-to'.
    my @quoted_cols = map { "'$_'" } @$ar_cols;

    my $sql = "INSERT INTO $table (" .
      join(', ', @quoted_cols) . ")\nVALUES (" .
        join(', ', @values) . ")\n";

    return $sql;
}

## Each field is given a default value of empty string or NULL (if nullable), then overriden
##   by its existing value of an integer or a DBI-quoted string.
sub process_row {
    my($row, $dbh, $ar_needs_quote, $ar_nullables) = @_;
    my %f;

    my %needs_quote = map { $_ => undef } @$ar_needs_quote;
    my %nullables;
    if (defined $ar_nullables) {
        %nullables = map { $_ => undef } @$ar_nullables;
    }
    
    for my $field ( @{ $row->{'field'} } ) {
        my $fname = $field->{'name'};

        my $content = "''";
        if (exists ($nullables{$fname})) {
            $content = 'NULL';
        }
        
        if (defined $field->{'__content'}) {
            my $tmp = $field->{'__content'};
            if (exists $needs_quote{$fname}) {
                $tmp = $dbh->quote($tmp);
            }
            $content = $tmp;
        }
        $f{ $fname } = $content;
    }
    return \%f;
}

sub check_mysqlup {
    ## note: this call is cached with a ttl of 600
    my $isrunning = Cpanel::MysqlRun::running();
    if ($isrunning) {
        return 1;
    }

    return undef;
}

sub ensure_roundcube_tables {
    my($dbh) = @_;
    my @tables = $dbh->tables();
    my %tables = map { $_ => undef } @tables;
    my @expected = qw(cache contacts identities messages session users);
    for my $exp (@expected) {
        unless (exists $tables{$exp}) {
            return 0;
        }
    }
    ## All of the expected table names were found in the database
    return 1;
}

sub init_check {
    my($cpconf) = @_;
    ## Ensure root
    return unless (0 == $>);

    unless (-e $sqlite_table_file) {
        print "Roundcube is not installed; conversion is irrelevant.\n";
        return;
    }
    
    if (exists $cpconf->{'skiproundcube'} and $cpconf->{'skiproundcube'}) {
        print "Roundcube should be skipped, bailing out.\n";
        return;
    }

    my $mysqlup = check_mysqlup();
    unless ($mysqlup) {
        print "Mysql not currently running, bailing out.\n";
        return;
    }

    ## Does this installation use Roundcube/MySQL?
    unless (Cpanel::MysqlUtils::db_exists('roundcube')) {
        return;
    }

    return 1;
}

sub xml_dump_to_sqlite {
    my($user, $tmpdir_root, $tmpdir_convert_base, $email_user, $homedir) = @_;

    pipe( my $parent_rdr, my $child_wtr );
    if ( my $pid = fork() ) {
        ## parent process; $pid is the child's pid
        close $child_wtr;
        
        my $child_pid = waitpid($pid, 0);
        my $rv = scalar(<$parent_rdr>);
        return $rv;
    }
    else {
        ## child process
        close $parent_rdr;
 
        my ( $mmuid, $mmgid ) = ( Cpanel::PwCache::getpwnam($user) )[ 2, 3 ];
        Cpanel::AccessIds::SetUids::setuids( $mmuid, $mmgid );

        my $rv = _xml_dump_to_sqlite_as_user($tmpdir_root, $tmpdir_convert_base, $email_user, $homedir);
        print $child_wtr $rv;
        close $child_wtr;
        exit(0);
    }
}

sub _xml_dump_to_sqlite_as_user {
    my($tmpdir_root, $tmp_convertdir_base, $email_user, $homedir) = @_;

    ## ensure $HOME/tmp/roundcube_convert exists; each email user will have a
    ##   temporary directory here.
    unless (-d $tmp_convertdir_base) {
        my $cmd = "mkdir -p $tmp_convertdir_base";
        system($cmd);
    }

    ## note: similar clause in cpsrvd to set up $ENV{'_RCUBE'}
    my($_user, $_domain) = split('@', $email_user);
    my $_rcube = $_user;
    if (defined $_domain) {
        mkdir("$homedir/etc/$_domain");
        $_rcube = sprintf('%s/%s', $_domain, $_user);
    }
    my $rcube_sqlite_loc8 = "$homedir/etc/$_rcube.rcube.db";

    my $tmp_convertdir = "$tmp_convertdir_base/$email_user";
    if (-d $tmp_convertdir) {
        rename($tmp_convertdir, "$tmp_convertdir.$time");
    }
    my $cmd_mv = "mv $tmpdir_root $tmp_convertdir_base";
    my $rv_mv = system($cmd_mv);

    if (-e $rcube_sqlite_loc8) {
        rename($rcube_sqlite_loc8, "$rcube_sqlite_loc8.$time");
    }

    my $dbh = DBI->connect("dbi:SQLite2:dbname=$rcube_sqlite_loc8", "", "");
    ## note: $dbh->{sqlite_version} is confirmed 2.8.15

    create_rcube_sqlite_tables($dbh);
    unless (ensure_roundcube_tables($dbh)) {
        return 0;
    }

    my $rv_convert = do_all_rcube_xml_to_db($dbh, $tmp_convertdir);
    $dbh->disconnect();
    
    chmod(0600, $rcube_sqlite_loc8);

    ## only remove the /tmp directory if the XML convert returns "success". the techs will
    ##   want to examine the XML files upon failure.
    if ($rv_convert) {
        my $cmd = "rm -rf $tmp_convertdir";
        system($cmd);
    }

    ## attempt to rmdir, which will intentionally fail if any of the converts did not succeed
    rmdir($tmp_convertdir_base);

    return $rv_convert;
}     


1;