#!/bin/sh

# bbs_mysql_database_creator
# by Ethan P. White & Allen H. Hurlbert

# This script automatically downloads the most recent
# version all of the major Breeding Bird Survey files.
# It then unpacks them using wine and loads them into
# a mysql database.

# Setup anonymous login for ftp server
HOST='ftpext.usgs.gov'
USER='anonymous'
PASSWD='anonymous'

# Login to ftp server
# turnoff interactive mode and set binary download mode
# download the routes and weather files from main dir
# download all the comma delimited state datafiles
ftp -n $HOST <<END_SCRIPT
quote USER $USER
quote PASS $PASSWD
prompt
binary
cd /pub/er/md/laurel/BBS/DataFiles/
get CRoutes.exe
get CWeather.exe
get RegionCodes.txt
cd States
mget C*.exe
quit
END_SCRIPT

# Execute all of the self extracting executable files
# using wine (Note:wine must be installed on your linux
# box).
for i in $( ls *.exe ); do
	wine $i
done

# Store executables in subdirectory
mkdir rawdata
mv --force *.exe rawdata

# Create text file with table definitions and load data
# infile instructions to pass to mysql

cat >> mysqlcode.txt <<-EOA

-- create the database
DROP DATABASE IF EXISTS BBS;
CREATE DATABASE BBS;
USE BBS;

-- create the Routes table
-- some fields are larger than necessary to
-- accomodate expanded sampling in the future
CREATE TABLE Routes
(
countrynum SMALLINT(3),
statenum SMALLINT(3),
route SMALLINT(4),
active TINYINT(1),
latitude DOUBLE(10,7),
longitude DOUBLE(10,7),
stratum SMALLINT(3),
BCR SMALLINT(3),
LandTypeID TINYINT(1),
RouteTypeID TINYINT(1),
RouteTypeDetailId TINYINT(1),
PRIMARY KEY(countrynum,statenum,route)
);

LOAD DATA LOCAL INFILE 'routes.csv'
INTO TABLE Routes
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(countrynum,statenum,route,active,latitude,longitude,stratum,BCR,LandTypeID,RouteTypeID,RouteTypeDetailID);

--create and load regions table
--this takes some creativity given the format
CREATE TABLE Regions
(
RegionCode SMALLINT(3),
Region VARCHAR(40),
PRIMARY KEY(RegionCode)
);

LOAD DATA LOCAL INFILE 'RegionCodes.txt' INTO TABLE Regions
IGNORE 11 LINES
(@var1)
SET RegionCode=SUBSTR(@var1,1,2),
Region=SUBSTR(@var1,12,40);

CREATE TABLE Weather
(
RouteDataId INT(8),
countrynum SMALLINT(3),
statenum SMALLINT(3),
route SMALLINT(4),
RPID SMALLINT(3),
Year SMALLINT(4),
Month TINYINT(1),
Day TINYINT(2),
ObsN INT(8),
TotalSpp SMALLINT(4),
StartTemp TINYINT(3),
EndTemp TINYINT(3),
TempScale CHAR(1),
StartWind TINYINT(1),
EndWind TINYINT(1),
StartSky TINYINT(1),
EndSky TINYINT(1),
StartTime SMALLINT(4),
EndTime SMALLINT(4),
Assistant TINYINT(1),
RunType TINYINT(1),
PRIMARY KEY (countrynum,statenum,route,RPID,Year)
);

LOAD DATA LOCAL INFILE 'weather.csv'
INTO TABLE Weather
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(RouteDataId,countrynum,statenum,Route,RPID,Year,Month,Day,ObsN,TotalSpp,StartTemp,EndTemp,TempScale,StartWind,EndWind,StartSky,EndSky,StartTime,EndTime,Assistant,RunType); 

-- create table Surveys
CREATE TABLE Surveys
(
countrynum SMALLINT(3),
statenum SMALLINT(3),
route SMALLINT(4),
RPID SMALLINT(3),
Year SMALLINT(4),
Aou SMALLINT(4),
count10 SMALLINT(4),
count20 SMALLINT(4),
count30 SMALLINT(4),
count40 SMALLINT(4),
count50 SMALLINT(4),
StopTotal SMALLINT(4),
SpeciesTotal SMALLINT(4),
PRIMARY KEY(countrynum,statenum,route,RPID,Year,Aou)
);
USE BBS;
EOA

for i in $( ls C*.csv ); do
	cat >> mysqlcode.txt <<-EOA
	LOAD DATA LOCAL INFILE '$i'
	INTO TABLE Surveys
	FIELDS TERMINATED BY ','
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(countrynum,statenum,route,RPID,Year,Aou,count10,count20,count30,count40,count50,StopTotal,SpeciesTotal);
	EOA
done

# upload the file to mysql
mysql -u $1 -p <mysqlcode.txt

# delete raw data files
rm *.csv *.txt

echo Thanks Ethan and Allen. That was awesome.
exit 0