#! /bin/bash

# drop old tables
echo "Dropping old tables: destinations, destinationgroups, directions"
mysql -u m2 m2 -pm2 --force -e "DROP TABLE destinations"
mysql -u m2 m2 -pm2 --force -e "DROP TABLE destinationgroups"
mysql -u m2 m2 -pm2 --force -e "DROP TABLE directions"

# import new tables
echo "Importing new tables"
mysql -u m2 m2 -pm2 --force < /usr/src/m2/db/destinationgroups_fix.sql

# fix matching prefixes
echo "Updating matching prefixes"
mysql -u m2 m2 -pm2 --force -e "UPDATE rates JOIN destinations ON destinations.prefix = rates.prefix SET rates.destination_id = destinations.id, rates.destinationgroup_id = destinations.destinationgroup_id"

# fix longer prefixes
echo "Updating longer prefixes"
mysql -u m2 m2 -pm2 --force -e "UPDATE rates INNER JOIN (SELECT rates.prefix AS 'custom_prefix', rates.id AS 'rid' FROM rates LEFT JOIN destinations ON destinations.prefix = rates.prefix WHERE destinations.prefix IS NULL) LONG_PREFIXES ON LONG_PREFIXES.rid = rates.id LEFT JOIN destinations ON destinations.prefix = SUBSTR(LONG_PREFIXES.custom_prefix, 1, LENGTH(destinations.prefix)) SET rates.destinationgroup_id = destinations.destinationgroup_id"

echo "Fixed"