#!/bin/bash

guessColType() {
	guesses=()
	colnum=$1
	sample=$(sed -n '2,50p' < $2)
	lines=`echo -e "$sample" | csvtool col $colnum -`
	for var in $lines;
	do
		if [[ $var =~ ^\-\+[0-9]{1,9}$ ]]; then
			guesses+=('INT')
			#echo "INT --> "$var
		elif [[ $var =~ ^[\-\+0-9\.]{1,}$ ]]; then
			guesses+=('DOUBLE')
			#echo "DOUBLE --> "$var
		elif [[ $var =~ ^.{1,200}$ ]]; then
			guesses+=('VARCHAR(200)')
			#echo "VARCHAR(200) --> "$var
		else
			guesses+=('TEXT')
			#echo "TEXT --> "$var
		fi
	done

	IFS=$'\n' sorted=($(sort <<<"${guesses[*]}"))
	unset IFS
	echo $sorted
}

csv2mysql() {
	filetitle=$(basename $1 | sed -r 's/\..*//')
	sqltablecols=$(head -1 $1 | sed -e 's/\"/`/g' | sed -e 's/,/ text NOT NULL,\n\t/g')

	columns=$(head -1 $1 | sed -e 's/\"//g')
	IFS=', ' read -r -a filecols <<< $columns
	cols=""
	intcols=(pop rent elevation medIncome medIncomestate medHousevalue medHousevalueState workersLocalNum popPerSqMi popMale popFemale raceHispanic raceWhite raceBlack raceTwo raceIndian)
	filecolcount=${#filecols[@]}
	i=0

	for col in "${filecols[@]}"
	do
		((i++))
		if [[ $i -lt $filecolcount ]]; then
			maybecomma=","
		else
			maybecomma=""
		fi

		coltype=$(guessColType $i $1)
		cols+="$col $coltype$maybecomma "

		#if [[ " ${intcols[@]} " =~ " ${col} " ]]; then
	   # 	cols+="$col INT$maybecomma "
		#else
	    #	cols+="$col TEXT$maybecomma "
		#fi
	done

sql=" CREATE TABLE IF NOT EXISTS $filetitle ( id bigint(20) NOT NULL, $cols ) ENGINE=InnoDB DEFAULT CHARSET=latin1;"
sql+=" ALTER TABLE $filetitle ADD PRIMARY KEY (id);"
sql+=" ALTER TABLE $filetitle MODIFY id int(11) NOT NULL AUTO_INCREMENT;"

#echo -e $sql; exit

mysql -u root -pmyseecret citydata << EOF
$sql
EOF

	mysqlimport \
	    --user=root \
	    --password=myseecret \
	    --columns=$columns \
	    --compress \
		--ignore-lines=1 \
	    --fields-optionally-enclosed-by='"' \
	    --fields-terminated-by=',' \
	    --fields-escaped-by='\\' \
	    --lines-terminated-by='\n' \
	    --local \
	    --lock-tables \
	    --verbose \
	    citydata $1	2>&1 | grep -v "Warning: Using a password"
}

if [ "$1" ]; then
	csv2mysql $1
else
	for file in csvout/*
	do
		csv2mysql $file
	done
	#csv2mysql $1
fi