#!/bin/bash

# Bash script untuk mengekspor data mailbox di vpopmail ke format
# SQL untuk Postfix + MySQL
#
# Author: Asfihani <asfik@its.ac.id>

# Direktori Vpopmail
VPOPMAILHOME=/home/vpopmail

# Output file
OUTFILE=/home/asfik/mailbox.sql

cd $VPOPMAILHOME/domains

echo "" > $OUTFILE
echo "USE postfix;" >> $OUTFILE
echo "" >> $OUTFILE
 
for domain in $(ls -1)
	do
	DOMAIN=$domain
	echo "# Domain $DOMAIN" >> $OUTFILE 
	echo "INSERT INTO domain (domain,description) VALUES ('$DOMAIN','Domain $DOMAIN');" >> $OUTFILE
	echo "" >> $OUTFILE
	echo "# Mailboxes for domain $DOMAIN" >> $OUTFILE

	for mailbox in $(cat $DOMAIN/vpasswd) 
		do
		# username format user@domain.tld
		USERNAME=$(echo $mailbox | awk -F: '{print $1}')@$DOMAIN
		# password format ?
		PASSWORD=$(echo $mailbox | awk -F: '{print $2}')
		# generic name format/identity
		NAME=$(echo $mailbox | awk -F: '{print $5}')
		# maildir format VIRTUAL_MAILBOX_BASE/domain.tld/username/Maildir/
		MAILDIR="$DOMAIN/$(echo $mailbox | awk -F: '{print $1}')/Maildir/"
		echo "INSERT INTO mailbox (username,password,name,maildir) VALUES ('$USERNAME','$PASSWORD','$NAME','$MAILDIR');" >> $OUTFILE
	done

	echo "" >> $OUTFILE

	# Check if there's alias/forward for specific account
	TEST=$(ls -1 $DOMAIN/.qmail-* | grep -v ".qmail-default" | wc -l)

	if [ "$TEST" -gt 0 ]; then
		for alias in $(ls -1 $DOMAIN/.qmail-* | grep -v ".qmail-default")
			do
			# Skip if file is symlink, maybe a mailing-list
			if test -h $alias ; then
				echo "Warning, file $alias probably mailing-list. Skipping..."
			else
			# address format alias@domain.tld
			ADDRESS=$(echo $alias | sed -e "s{$DOMAIN/.qmail-{{g")@$DOMAIN
			# real mailbox format username@domain.tld
			GOTO=$(cat $alias | sed -e "s{$VPOPMAILHOME/domains/$DOMAIN/{{g" | sed -e "s{/Maildir/{{g")@$DOMAIN
			echo "# Alias for domain $DOMAIN" >> $OUTFILE
			echo "INSERT INTO alias (address,goto) VALUES ('$ADDRESS', '$GOTO');" >> $OUTFILE
			fi
		done
		echo "" >> $OUTFILE
	fi
done