#!/bin/bash

# This script is a helper script for the irc2sql module.
# It downloads the geoip database and imports it into
# mysql tables. The tables are created by this script.

# Dont forget to rename this file or your changes
# will be overwritten on the next 'make install'

############################
# Config
############################


mysql_host="localhost"
mysql_user="anope"
mysql_password="anope"
mysql_database="anope"
prefix="anope_"
die="no" # copy protection :-)

#########################################
# The GeoIP data is created by MaxMind, 
# available from www.maxmind.com.
#########################################

#geoip2="http://geolite.maxmind.com/download/geoip/database/GeoLite2-City-CSV.zip"
geoip2="http://jens.anope.org/GeoLite2-City-CSV.zip"

######################################
# create a temporary config file
# for the mysql commandline client
######################################

cat >update.cnf << EOL
[client]
host = $mysql_host
user = $mysql_user
password = $mysql_password
EOL

LOGIN="--defaults-file=update.cnf $mysql_database";

#########################################

download() {
    local url=$1
    local desc=$2
    echo -n "   $desc     "
    wget --progress=dot $url 2>&1 | grep --line-buffered "%" | sed -u -e "s,\.,,g" | awk '{printf("\b\b\b\b%4s", $2)}'
    echo -ne " Done\n"
}


if test $die = "yes"; then
	echo "You have to edit and configure this script first."
	exit
fi

echo "Downloading..."
download "$geoip2" "MaxMinds GeoIP2 City Database:"
echo "Unpacking..."
unzip -jo GeoLite2-City-CSV.zip
rm GeoLite2-City-CSV.zip COPYRIGHT.txt LICENSE.txt
echo "Importing Locations..."

####################################
# Importing the Locations Database
####################################

table=$prefix"geoip_locations"
mysql $LOGIN -e "DROP TABLE $table;"
mysql $LOGIN --local-infile=1 << EOF
	CREATE TABLE $table (
	geoid INT UNSIGNED NOT NULL,
	continentcode CHAR(2) NOT NULL DEFAULT '',
	continentname VARCHAR(20) NOT NULL DEFAULT '',
	countrycode CHAR(2) NOT NULL DEFAULT '',
	countryname VARCHAR(50) NOT NULL DEFAULT '',
	subdivision_iso_code VARCHAR(4) NOT NULL DEFAULT '',
	subdivision_name VARCHAR(100) NOT NULL DEFAULT '',
	city VARCHAR(64) NOT NULL DEFAULT '',
	timezone VARCHAR(64) NOT NULL DEFAULT '',
	PRIMARY KEY (geoid)
	) ENGINE=MyISAM DEFAULT CHARSET=utf8;
EOF
mysql $LOGIN --local-infile=1 << EOF
	LOAD DATA LOCAL INFILE "GeoLite2-City-Locations.csv"
	INTO TABLE $table
	FIELDS
		TERMINATED BY ","
		ENCLOSED BY '"'
	LINES
		TERMINATED BY '\n'
	IGNORE 1 LINES
	(
		@geoid, @continentcode, @continentname, @countrycode, @countryname,
		@subdivision_iso_code, @subdivision_name, @city, @x, @timezone
	)
	SET
	geoid := @geoid,
	continentcode := @continentcode,
	continentname := @continentname,
	countrycode := @countrycode,
	countryname := @countryname,
	subdivision_iso_code := @subdivision_iso_code,
	subdivision_name := @subdivision_name,
	city := @city,
	timezone := @timezone;
	SHOW WARNINGS;
EOF
rm GeoLite2-City-Locations.csv


#############################
# Importing the Blocks File
#############################

echo "Converting Blocks, this may take a few minutes..."
./convert-geoip2 GeoLite2-City-Blocks.csv geoip_blocks.csv
rm GeoLite2-City-Blocks.csv
table=$prefix"geoip_blocks"
#mysql $LOGIN -e "DROP TABLE $table"
#echo "creating table $table"
mysql $LOGIN -e "TRUNCATE TABLE $table"

#mysql $LOGIN << EOF
#	CREATE TABLE $table (
#	id INT UNSIGNED NOT NULL auto_increment,
#	hpoly POLYGON NOT NULL,
#	lpoly POLYGON NOT NULL,
#	geoid INT UNSIGNED DEFAULT 0,
#	latitude DECIMAL(11,8) DEFAULT 0,
#	longitude DECIMAL(11,8) DEFAULT 0,
#	PRIMARY KEY (id),
#	SPATIAL INDEX (hpoly),
#	SPATIAL INDEX (lpoly)
#	) ENGINE=MyISAM DEFAULT CHARSET=utf8;
#EOF
echo "Importing Blocks, this may take a few minutes..."
mysql $LOGIN --local-infile=1 << EOF
	LOAD DATA LOCAL INFILE "geoip_blocks.csv"
	INTO TABLE $table
	FIELDS
		TERMINATED BY ","
		ENCLOSED BY '"'
	LINES
		TERMINATED BY '\n'
	IGNORE 1 LINES
	(
		@ip_from, @ip_to, @geoname_id,
		@latitude, @longitude
	)
	SET
	id := NULL,
	hpoly := GEOMFROMWKB(POLYGON(LINESTRING(
		POINT(conv(substring(@ip_from,1,16),16,10), -1 ),
		POINT(conv(substring(@ip_to,1,16),16,10), -1 ),
		POINT(conv(substring(@ip_to,1,16),16,10), 1 ),
		POINT(conv(substring(@ip_from,1,16),16,10), 1),
		POINT(conv(substring(@ip_from,1,16),16,10), -1)))),
	lpoly := GEOMFROMWKB(POLYGON(LINESTRING(
		POINT(conv(substring(@ip_from,17),16,10), -1 ),
		POINT(conv(substring(@ip_to,17),16,10), -1 ),
		POINT(conv(substring(@ip_to,17),16,10), 1 ),
		POINT(conv(substring(@ip_from,17),16,10), 1),
		POINT(conv(substring(@ip_from,17),16,10), -1)))),
	geoid := @geoname_id,
	latitude := @latitude,
	longitude := @longitude;
	SHOW WARNINGS;
EOF
rm geoip_blocks.csv update.cnf
echo "Done..."