#!/usr/bin/perl
# sm2mysql - Import SquirrelMail abook files into a RoundCube database
#
# Author:
#    Steve Pellegrin (spellegrin at convoglio dot com)
#
# History:
#    1.0   2008-Oct-5        Original code
#    1.1   2009-Mar-26       Added --domain cmd line arg
#
# Description:
#    1. Assumes that the name of the abook file identifies the user
#       (obama.abook is for RoundCube user obama)
#    2. Assumes that the user is already in the RoundCube users and identities tables. 

use strict;               # to catch stupid errors
use Getopt::Long;
use Pod::Usage;
use DBI;

# ---------- End of Configuration -----------------------------------


# Extract the command line arguments
GetOptions
(
    'database=s'    => \(my $dbName = 'roundcubemail'),
    'user=s'        => \(my $userName = 'roundcube'),
    'password=s'    => \(my $password = 'roundcube'),
    'domain=s'      => \(my $domain = ''),
    'help'          => \(my $help = ''),
) or pod2usage(2);

# Print help text and exit if requested.
pod2usage(1)
    if $help;

# Fetch the files if available
pod2usage(2)
    if (@ARGV == 0);

my $dbHandle = DBI->connect("DBI:mysql:$dbName", $userName, $password)
    or die "Couldn't connect to database: " . DBI->errstr;

foreach my $file (@ARGV)
{
    convertFile( $dbHandle, $file );
}

$dbHandle->disconnect;
exit 0;

############################################

sub convertFile
{
    my ($db, $inFile) = @_;

    $inFile =~ /^(\w+)\./;
    my $userName = $1;
    my $fqn = length $domain == 0 ? $userName : "$userName\@$domain";

    my $userId = getID( $db, $fqn );
    if ($userId < 0)
    {
        print "User not found in database: $fqn\n";
    }
    else
    {
        open IN, "<$inFile"
            or die "Cannot open: $inFile\n";

        while (my $line = <IN>)
        {
            chomp($line);
            $line =~ s/\xa0/ /g;
            $line =~ s/([\'\"])/\\\1/g;
            $line =~ /^[^\|]*\|([^\|]*)\|([^\|]*)\|([^\|]*)\|/;

            addContact( $db, $userId, $1, $2, $3);
        }

        close IN;
    }
}

sub getID
{
    my ($db, $name) = @_;

    my $sth = $db->prepare("SELECT user_id FROM users WHERE username = '$name'")
        or die "Couldn't prepare statement: " . $db->errstr;
    $sth->execute()
        or die "Couldn't execute statement: " . $sth->errstr;

    my $id;
    $sth->bind_columns(\$id);
    $sth->fetch()
        or $id = -1;

    return $id;
}

sub addContact
{
    my ($db, $id, $firstName, $lastName, $email) = @_;
    my $fullName = "$firstName $lastName";

    my $sth = $db->prepare("INSERT INTO contacts (name, email, firstname, surname, user_id) VALUES ('$firstName $lastName', '$email', '$firstName', '$lastName', $id)");
    $sth->execute();
}

__END__

=head1 NAME

sm2mysql - Import one or more SquirrelMail abook files into a RoundCube database

=head1 SYNOPSIS

sm2mysql [options] file...

=head1 OPTIONS

=over 8

=item B<--database=string>

Name of the MySQL database (Default: roundcubemail)

=item B<--user=string>

MySQL user name (Default: roundcube)

=item B<--password=string>

MySQL password (Default: roundcube)

=item B<--domain=string>

Domain in the form example.com (Default: empty)

=item B<--help>

Print this text

=back

=cut