#!/usr/bin/env bash

# Author:   Zhang Huangbin (michaelbibby <at> gmail.com )
# Date:     2008.06.11
# Purpose:  Migrate ExtMail MySQL 'mailbox' table to iRedMail format.

# Usage:
#   * Run into MySQL command line with privilege user, e.g. root.
#       # mysql -uroot -p extmail
#   * Select some column from mailbox table:
#       mysql> SELECT username,password,name,maildir,quota,domain,createdate,active
#            > INTO OUTFILE '/tmp/mailbox.sql'
#            > FROM mailbox;
#   * Run this script:
#       # sh migrate_extmail_mailbox.sh /tmp/mailbox.sql
#     It will create a new file: /tmp/mailbox.sql.new
#   * Import this new file in MySQL command line:
#       mysql> USE vmail;
#       mysql> SOURCE /tmp/mailbox.sql.new;

usage()
{
    echo -e "\nUsage: sh $0 script\n"
}

[ X"$#" != X"1" ] && usage && exit 255 

OUTPUT_SQL="$1.iRedMail"
echo ''> ${OUTPUT_SQL}

while read line; do
    username="$(echo $line | awk '{print $1}')"
    password="$(echo $line | awk '{print $2}')"
    maildir="$(echo $line | awk '{print $3}' | awk -F'Maildir/' '{print $1}' | tr [A-Z] [a-z] )"
    quota="$(echo $line | awk '{print $4}')"
    domain="$(echo $line | awk '{print $5}')"
    createdate="$(echo $line | awk '{print $6, $7}')"
    active="$(echo $line | awk '{print $8}')"

    echo $quota | grep -i 'S$' >/dev/null 2>&1
    if [ X"$?" == X"0" ]; then
        quota="$(echo $quota | sed 's/S$//')"
        quota="$(expr \( $quota / 1024 \) )"
    else
        :
    fi

    cat >> ${OUTPUT_SQL} <<EOF
INSERT INTO mailbox (username, password, maildir, quota, domain, created, active) values ("$username", "$password", "$maildir", $quota, "$domain", "$createdate", "$active");
EOF
done < $1