#!/bin/bash

function fix_column {
    echo "UPDATE $1 SET $2 = REPLACE($2, '$3', '$4');"
    echo "UPDATE $1 SET $2 = REPLACE($2, '$3', '$4');" | mysql SCHEMA_NAME
}

function fix_columns {
    # A
    fix_column $1 $2 'Ä' 'Ä'
    fix_column $1 $2 'Á' 'Á'
    fix_column $1 $2 'À' 'À'
    fix_column $1 $2 'á' 'á'
    fix_column $1 $2 'à' 'á'
    fix_column $1 $2 'á' 'á'
    fix_column $1 $2 'ä' 'ä'
    fix_column $1 $2 'â' 'â'

    # C
    fix_column $1 $2 'ç' 'ç'

    # E
    fix_column $1 $2 'É' 'É'
    fix_column $1 $2 'È' 'È'
    fix_column $1 $2 'é' 'é'
    fix_column $1 $2 '‚' 'é'
    fix_column $1 $2 'è' 'è'
    fix_column $1 $2 'ê' 'ê'
    fix_column $1 $2 'ë' 'ë'

    # I
    fix_column $1 $2 'Í' 'Í'
    fix_column $1 $2 'í' 'í'
    fix_column $1 $2 'í' 'í'
    fix_column $1 $2 'ï' 'ï'
    fix_column $1 $2 'î' 'î'
    fix_column $1 $2 'ì' 'ì'

    # N
    fix_column $1 $2 'Ñ' 'Ñ'
    fix_column $1 $2 'ñ' 'ñ'

    # O
    fix_column $1 $2 'Ã’' 'Ò'
    fix_column $1 $2 'Ó' 'Ó'
    fix_column $1 $2 'Ö' 'O'
    fix_column $1 $2 'Ø' 'Ø'
    fix_column $1 $2 'ó' 'ó'
    fix_column $1 $2 'ò' 'ò'
    fix_column $1 $2 'õ' 'õ'
    fix_column $1 $2 'ö' 'ö'
    fix_column $1 $2 'ô' 'ô'
    fix_column $1 $2 'ø' 'ø'

    # U
    fix_column $1 $2 'Û' 'Û'
    fix_column $1 $2 'Ú' 'Ú'
    fix_column $1 $2 'ú' 'ú'
    fix_column $1 $2 'ü' 'ü'
    fix_column $1 $2 'ù' 'ù'

    # Others
    fix_column $1 $2 '´' '´'
    fix_column $1 $2 '‘' '‘'
    fix_column $1 $2 '’' '’'
    fix_column $1 $2 '“' '“'
    fix_column $1 $2 '”' '”'
    fix_column $1 $2 '·' '·'
    fix_column $1 $2 'Ð' 'Ð'
    fix_column $1 $2 '–' ' — '
    fix_column $1 $2 '—' ' — '
    fix_column $1 $2 '…' '…'
    fix_column $1 $2 'â„¢' '<sup>TM</sup>'
}

for column in subject body keywords; do fix_columns emails $column; done;
for column in firstname lastname; do fix_columns users $column; done;