alias my='mysql'
alias mys='mysqlshow'
alias mysu="mysql -e 'SELECT user, host FROM mysql.user'"

alias mycu='mysql-create-user'
alias mycud='mysql-create-user-and-database'

alias mycd='mysql-create-database'
alias mydd='mysql-drop-database'
alias mytd='mysql-truncate-database'

alias myid='mysql-import-database'
alias db-import='mysql-import-database'
alias myed='mysql-export-database'
alias db-export='mysql-export-database'


mysql-password-hash() {
    PROGNAME=${0##*/}

    if [[ $# -eq 0 ]]; then
        echo "Usage: $PROGNAME plain_password"
        return 1
    fi

    mysql -e "SELECT PASSWORD('$1')" | grep '\*\w*'
}

mysql-create-user() {
    PROGNAME=${0##*/}

    if [[ $# -eq 0 ]]; then
        echo "Usage: $PROGNAME user_name [password]"
        return 1
    fi

    USER=$1
    if [[ $(echo $USER | grep -c '@') -eq 0 ]]; then
        USER="$USER@localhost"
    fi

    PASSWORD=$2
    if [[ -z $PASSWORD ]]; then
        echo -n "Enter user password: \n"; read -s PASSWORD
    fi

    PASSWORD_HASH=$(mysql-password-hash $PASSWORD)

    CREATE_USER_QUERY="CREATE USER \`${USER/@/\`@\`}\` IDENTIFIED BY PASSWORD '$PASSWORD_HASH'"

    mysql -e $CREATE_USER_QUERY
}

mysql-create-database() {
    PROGNAME=${0##*/}

    if [[ $# -eq 0 ]]; then
        echo "Usage: $PROGNAME database_name [user_name]"
        return 1
    fi

    DATABASE=$1
    USER=$2
    CHARACTER_SET=${CHARACTER_SET=utf8}
    COLLATION=${COLLATION=utf8_general_ci}

    CREATE_DATABASE_QUERY="CREATE DATABASE \`$DATABASE\` CHARACTER SET $CHARACTER_SET COLLATE $COLLATION"

    if [[ -n $USER ]]; then
        if [[ $(echo $USER | grep -c '@') -eq 0 ]]; then
            USER="$USER@localhost"
        fi
        CREATE_DATABASE_QUERY="$CREATE_DATABASE_QUERY; GRANT ALL PRIVILEGES ON \`$DATABASE\`.* TO \`${USER/@/\`@\`}\`"
    fi

    mysql -e $CREATE_DATABASE_QUERY
}

mysql-drop-database() {
    PROGNAME=${0##*/}

    if [[ $# -eq 0 ]]; then
        echo "Usage: $PROGNAME database_name"
        return 1
    fi

    mysql -e "DROP DATABASE IF EXISTS \`$1\`"
}

mysql-create-user-and-database() {
    PROGNAME=${0##*/}

    if [[ $# -eq 0 ]]; then
        echo "Usage: $PROGNAME user_name [password]"
        return 1
    fi

    mysql-create-user $*
    mysql-create-database $1 $1
}

mysql-truncate-database() {
    PROGNAME=${0##*/}

    if [[ $# -eq 0 ]]; then
        echo "Usage: $PROGNAME database"
        return 1
    fi

    for table in $(mysql -Nse 'SHOW TABLES' $1); do
        mysql -e "TRUNCATE TABLE \`$table\`" $1
    done
}

mysql-import-database() {
    PROGNAME=${0##*/}
    if [[ $# -ne 2 ]]; then
        echo "Usage: $PROGNAME database dump-file.sql.gz"
        return 1
    fi

    local DATABASE=$1
    local FILE=$2

    if [ ! -f "$FILE" ] ; then
        echo "'$FILE' is not a valid file"
        return 1
    fi

    mysql-drop-database $DATABASE
    mysql-create-database $DATABASE


    local TMP_FOLDER="$HOME/.tmp"
    local TMP_SQL="$TMP_FOLDER/import.sql"

    if [ ! -d $TMP_FOLDER ]; then
      mkdir -p $TMP_FOLDER;
    fi

    case $FILE in
        *.gz)  gunzip -cf $FILE > $TMP_SQL ;;
        *.zip) unzip -p $FILE > $TMP_SQL ;;
        *)     echo "'$FILE' cannot be extracted"; return 1 ;;
    esac

    LC_ALL=C sed -i "" -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' $TMP_SQL
    LC_ALL=C sed -i "" -e 's/ROW_FORMAT=FIXED//g' $TMP_SQL
    mysql --force $DATABASE < $TMP_SQL

    rm $TMP_SQL
}

mysql-export-database() {
    PROGNAME=${0##*/}

    if [[ $# -eq 0 ]]; then
        echo "Usage: $PROGNAME database [dump-file.sql.gz]"
        return 1
    fi

    DATABASE=$1
    FILE=$2

    if [[ -z $FILE ]]; then
        FILE="$DATABASE.sql.gz"
    fi

    mysqldump $DATABASE | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | sed -e 's/ROW_FORMAT=FIXED//g' | gzip > $FILE && echo "Exported: $FILE"
}