#!/usr/bin/perl -w

# mail-admin.pl
# command-line script for manipulating Postfix + MySQL e-mail database
# Copyright 2004, Tex Thompson <tex@biosysadmin.com>

use strict;
use DBI;
use File::Basename;
use Getopt::Long;

# Confguration Information

$0 = basename $0;

my $dbh = &get_dbh;
my $mail_user = $ENV{'USER'};
my $add_alias;
my $print = 0;

GetOptions( "user=s", \$mail_user,
            "add_alias=s", \$add_alias,
            "print", \$print,
);


if ( $add_alias ) {
   &add_alias( $mail_user, $add_alias );
}

if ( $print ) {
   &print_local_aliases( $mail_user );
   &print_remote_aliases( $mail_user );
}

# close everything down and exit the program
$dbh->disconnect or die "Error closing database connection\n";

###############
# subroutines #
###############

sub add_alias {
   my ($user,$alias) = shift;
   print "Delivering mail to $alias for user $user ...\n";
   my $uid = uid( $user );
   my $gid = gid( $user );
   
   my $sql;
   $sql  = "INSERT INTO aliases (vuid,vgid,alias,maildir) ";
   $sql .= "VALUES (?,?,?,?);";

   my $sth = $dbh->prepare( $sql ) or die $dbh->errstr;
   $sth->execute( $uid,$gid,$alias,$user ) or warn $sth->errstr;

   if ($@) {
      print "Error executing SQL statement!\n";
   }
}

sub print_local_aliases {
   my $user = shift;

   # local aliases
   my ($vuid,$vgid,$address,$maildir);
   my $sql = 'SELECT vuid,vgid,alias,maildir FROM aliases';
   $sql   .= " WHERE maildir='$user'";
   
   my $sth = $dbh->prepare( $sql ) or die $dbh->errstr;
   $sth->execute() or warn $sth->errstr;
   $sth->bind_columns( \($vuid,$vgid,$address,$maildir) );
   
   print "Mail aliases delivered locally to $user:\n";
   while ( $sth->fetch ) {
      print "\t$address\n"; 
   }
}

sub print_remote_aliases {
   my $user = shift;
   # remote aliases
   my ($alias,$rcpt,$sql);
   $sql  = 'SELECT alias,rcpt FROM remote_aliases ';
   $sql .= "WHERE alias LIKE \"$user@%\"";
   my $sth = $dbh->prepare( $sql ) or die $dbh->errstr;
   print "$sql\n";
   $sth->execute() or warn $sth->errstr;
   $sth->bind_columns( \($alias, $rcpt) );
   print "Remote addresses for $user:\n";
   if ( $sth->fetch ) {
      while( $sth->fetch ) {
         print "\t$alias -> $rcpt\n";
      }
   } else { 
      print "\tNo matching addresses\n";
   }
}

sub uid {
   my $username = shift;
   my $retval = `id -u $username`;
   print $retval;
   if ( $retval eq '' ) {
      warn "Error obtaining gid information for $username\n";
   } else {
      return $retval;
   }
}

sub gid {
   my $username = shift;
   my $retval = `id -g $username`;
   if ( $retval eq '' ) {
      warn "Error obtaining gid information for $username\n";
   } else {
      return $retval;
   }
}

sub get_dbh {
   my $db_user   = 'foo';
   my $db_pass   = 'ba';
   my $db_table  = 'maildb';
   my $dsn       = "DBI:mysql:$db_table";

   my $dbh = DBI->connect( $dsn, $db_user, $db_pass )
      or die "Error connecting to database\n";

   return $dbh;
}