#!/bin/bash

########################
# Script configuration #
########################

if [ ! -f "$PWD/config" ]; then
	declare DB_NAME="hubzilla"
	declare DB_HOST="localhost"
	declare DB_PORT="3306"
	declare DB_USER="user"
	declare DB_PASS="pass"
	declare SPAMLIST_URL="https://git.c-r-t.tk/DiasporaDefenseLeague/spamlist/raw/branch/master/spammers"
else
	source $PWD/config > /dev/null
fi

#################################
# Download new list of spammers #
#################################

wget $SPAMLIST_URL -q -N

#########################
# Read List of spammers #
#########################

mapfile -t SPAMMERS < $PWD/spammers

####################################
# Extermination of Zlaxes' actions #
####################################

QUERIES=$(mktemp)

for SPAM in "${SPAMMERS[@]}"
do
	echo "Preparing to remove items from handle $SPAM ..."
	echo "UPDATE item SET item_pending_remove = '1' WHERE author_xchan LIKE '$SPAM';" >> $QUERIES
	
done

mysql --user=$DB_USER \
	--password=$DB_PASS \
	--database=$DB_NAME \
	--host=$DB_HOST \
	--port=$DB_PORT \
	--silent \
	--compress \
	-e "$(cat $QUERIES)"

rm $QUERIES

#################################################
# Finally remove all marked items from database #
#################################################

QUERIES=$(mktemp)

echo "All items have been marked for deletion and won't be visible anymore. If you wish to delete them permamently, type YES."
read REMOVAL

if [ "$REMOVAL" = "YES" ]; then
	echo "DELETE from item WHERE item_pending_remove = '1';" >> $QUERIES
fi

mysql --user=$DB_USER \
	--password=$DB_PASS \
	--database=$DB_NAME \
	--host=$DB_HOST \
	--port=$DB_PORT \
	--silent \
	-e "$(cat $QUERIES)"

rm $QUERIES

########################
# Remove notifications #
########################

QUERIES=$(mktemp)

for SPAM in "${SPAMMERS[@]}"
do
	POD=$(echo -e "$SPAM" | cut -f2 -d"@")
	USR=$(echo -e "$SPAM" | cut -f1 -d"@")
	URL="htt%://$POD/u/$USR"
    echo "Preparing to remove notifications from handle $SPAM ..."
	echo "DELETE FROM notify WHERE url LIKE '$URL';" >> $QUERIES
done 
 
mysql --user=$DB_USER \
	--password=$DB_PASS \
	--database=$DB_NAME \
	--host=$DB_HOST \
	--port=$DB_PORT \
	--silent \
	-e "$(cat $QUERIES)"

rm $QUERIES

#####################################
# Set hubloc entry to "deactivated" #
#####################################

QUERIES=$(mktemp)

for SPAM in "${SPAMMERS[@]}"
do
	echo "Deactivating account $SPAM on this hub ..."
	echo "UPDATE hubloc SET hubloc_deleted = '1' WHERE hubloc_addr LIKE '$SPAM';" >> $QUERIES
done

mysql --user=$DB_USER \
	--password=$DB_PASS \
	--database=$DB_NAME \
	--host=$DB_HOST \
	--port=$DB_PORT \
	--silent \
	-e "$(cat $QUERIES)"

rm $QUERIES

##############################################
# TBD                                        #
##############################################
# Blocking further communication from Zlaxes #
##############################################
#
#HZ_BANLIST=$(mysql --user=$DB_USER \
#	--password=$DB_PASS \
#	--database=$DB_NAME \
#	--host=$DB_HOST \
#	--port=$DB_PORT \
#	--silent \
#	-e "SELECT v FROM config WHERE cat='system' AND k='blacklisted_channels';")