#!/bin/bash

# iRedMail database migration script
# 07/2020 by Bernhard Roth sparc128(at)gmail(dot)com

MY_HOST=xxxx
MY_USER=xxxx
MY_PASS=xxxx


# clean mysql table from all data, keep only structure
mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" < clean.sql

# databases to copy
DATABASES="amavisd fail2ban iredadmin iredapd roundcubemail vmail"
#DATABASES="amavisd"

# create temp file for SQL dump
TFILE=$(mktemp)

#echo $TFILE

# perform some fixes to allow import from older database structure
# the previous installation had some stale columns which are not used anymore
# in order to properly import the data, those columns need to be created.
# they will be deleted afterwards
echo "Preparing..."

# please adjust to your requirements!
echo "ALTER TABLE domain ADD COLUMN defaultpasswordscheme VARCHAR(10) NOT NULL DEFAULT '';" | mysql vmail
echo "ALTER TABLE mailbox ADD COLUMN bytes BIGINT(20) NOT NULL DEFAULT '0';" | mysql vmail
echo "ALTER TABLE mailbox ADD COLUMN messages BIGINT(20) NOT NULL DEFAULT '0';" | mysql vmail
echo "ALTER TABLE msgs CHANGE from_addr from_addr VARBINARY(255);" | mysql amavisd

# go though the list of databases
for db in $DATABASES; do

    echo -n "Processing $db: "
    echo -n "Exporting..."
    mysqldump -h $MY_HOST --user=$MY_USER --password=$MY_PASS --default-character-set=utf8mb4 --complete-insert --no-create-info --skip-triggers --no-create-db --compact $db > $TFILE
    echo -n "Importing..."
    mysql --default-character-set=utf8mb4 --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" $db < $TFILE
    echo "done"
done


echo "Fixing..."

# now remove temporary columns, adjust to your requirements
echo "ALTER TABLE domain DROP COLUMN defaultpasswordscheme;" | mysql vmail
echo "ALTER TABLE mailbox DROP COLUMN bytes;" | mysql vmail
echo "ALTER TABLE mailbox DROP COLUMN messages;" | mysql vmail

# this is due to an issue in iRedMail <= 1.3.1 where fields in the database are not utf8mb4
# required to properly save emojis etc. in the database while allowing to actually see the contents of the field
echo "ALTER TABLE msgs CHANGE from_addr from_addr VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '';" | mysql amavisd


#delete temp file
rm $TFILE

echo "done"

USE `amavisd`;
TRUNCATE TABLE  `maddr`;
TRUNCATE TABLE  `mailaddr`;
TRUNCATE TABLE  `msgrcpt`;
TRUNCATE TABLE  `msgs`;
TRUNCATE TABLE  `outbound_wblist`;
TRUNCATE TABLE  `policy`;
TRUNCATE TABLE  `quarantine`;
TRUNCATE TABLE  `users`;
TRUNCATE TABLE  `wblist`;
USE `fail2ban`;
TRUNCATE TABLE  `banned`;
USE `iredadmin`;
TRUNCATE TABLE  `deleted_mailboxes`;
TRUNCATE TABLE  `domain_ownership`;
TRUNCATE TABLE  `log`;
TRUNCATE TABLE  `newsletter_subunsub_confirms`;
TRUNCATE TABLE  `sessions`;
TRUNCATE TABLE  `settings`;
TRUNCATE TABLE  `tracking`;
TRUNCATE TABLE  `updatelog`;
USE `iredapd`;
TRUNCATE TABLE  `greylisting`;
TRUNCATE TABLE  `greylisting_tracking`;
TRUNCATE TABLE  `greylisting_whitelist_domain_spf`;
TRUNCATE TABLE  `greylisting_whitelist_domains`;
TRUNCATE TABLE  `greylisting_whitelists`;
TRUNCATE TABLE  `senderscore_cache`;
TRUNCATE TABLE  `smtp_sessions`;
TRUNCATE TABLE  `srs_exclude_domains`;
TRUNCATE TABLE  `throttle`;
TRUNCATE TABLE  `throttle_tracking`;
TRUNCATE TABLE  `wblist_rdns`;
USE `roundcubemail`;
TRUNCATE TABLE  `cache`;
TRUNCATE TABLE  `cache_index`;
TRUNCATE TABLE  `cache_messages`;
TRUNCATE TABLE  `cache_shared`;
TRUNCATE TABLE  `cache_thread`;
TRUNCATE TABLE  `contactgroupmembers`;
TRUNCATE TABLE  `contactgroups`;
TRUNCATE TABLE  `contacts`;
TRUNCATE TABLE  `dictionary`;
TRUNCATE TABLE  `filestore`;
TRUNCATE TABLE  `identities`;
TRUNCATE TABLE  `searches`;
TRUNCATE TABLE  `session`;
TRUNCATE TABLE  `system`;
TRUNCATE TABLE  `users`;
USE `vmail`;
TRUNCATE TABLE  `admin`;
TRUNCATE TABLE  `alias`;
TRUNCATE TABLE  `alias_domain`;
TRUNCATE TABLE  `anyone_shares`;
TRUNCATE TABLE  `deleted_mailboxes`;
TRUNCATE TABLE  `domain`;
TRUNCATE TABLE  `domain_admins`;
TRUNCATE TABLE  `forwardings`;
TRUNCATE TABLE  `last_login`;
TRUNCATE TABLE  `mailbox`;
TRUNCATE TABLE  `maillists`;
TRUNCATE TABLE  `moderators`;
TRUNCATE TABLE  `recipient_bcc_domain`;
TRUNCATE TABLE  `recipient_bcc_user`;
TRUNCATE TABLE  `sender_bcc_domain`;
TRUNCATE TABLE  `sender_bcc_user`;
TRUNCATE TABLE  `sender_relayhost`;
TRUNCATE TABLE  `share_folder`;
TRUNCATE TABLE  `used_quota`;