# built/tested with python 3.3 on pollock 
# assumes:
#   the images in this batch have not been loaded before, we don't have unique key tied to the content 

import sys
import glob
import os
import psycopg2

if len(sys.argv) <= 1:
	print("no input files given")
	exit()

if len(sys.argv) >= 2:
	files = str(sys.argv[1])

connectionString = "host='hopper.cluster.earlham.edu' dbname='field_science' user='charliep' password='test'"
print("Connecting to database\n ->%s" % (connectionString))
connection = psycopg2.connect(connectionString)
print("Connected!\n")

for filename in glob.glob(files):
	print("filename:", filename)

	command = 'exiftool -csv -coordFormat "%.6f" -Filename -Directory -CreateDate -GPSLatitudeRef -GPSLongitudeRef -GPSAltitudeRef -GPSLatitude -GPSLongitude  -GPSAltitude -AbsoluteAltitude -RelativeAltitude -GimbalYawDegree -FlightYawDegree -Yaw -CameraYaw -CameraPitch -CameraRoll -FocalLengthIn35mmFormat ' + filename
	output = os.popen(command, 'r').read()	
	lines = output.splitlines()
	fields = lines[1].split(',')
	
	SourceFile = fields[0]
	FileName = fields[1]
	Directory = fields[2]
	CreateDate = fields[3]
	GPSLatitudeRef = fields[4]
	GPSLongitudeRef = fields[5]
	GPSAltitudeRef = fields[6]
	GPSLatitude = fields[7]
	GPSLongitude = fields[8]
	GPSAltitude = fields[9]
	AbsoluteAltitude = fields[10]
	RelativeAltitude = fields[11]
	GimbalYawDegree = fields[12]
	FlightYawDegree = fields[13]
	Yaw = fields[14]
	CameraYaw = fields[15]
	CameraPitch = fields[16]
	CameraRoll = fields[17]
	FocalLength35mm = fields[18]

	GPSLatitude = GPSLatitude.split()[0]
	GPSLongitude = GPSLongitude.split()[0]
	GPSAltitude = GPSAltitude.split()[0]
	FocalLength35mm = FocalLength35mm.split()[0]
	
	cursor = connection.cursor()
	sqlStatement = "\
					insert into image_metadata (\
					serial_number,\
					exif_sourcefile,\
					exif_filename,\
					exif_directory,\
					exif_createdate,\
					exif_gpslatituderef,\
					exif_gpslongituderef,\
					exif_gpsaltituderef,\
					exif_gpslatitude,\
					exif_gpslongitude,\
					exif_gpsaltitude,\
					exif_absolutealtitude,\
					exif_relativealtitude,\
					exif_gimbalyawdegree,\
					exif_flightyawdegree,\
					exif_yaw,\
					exif_camerayaw,\
					exif_camerapitch,\
					exif_cameraroll,\
					exif_focallength35mm,\
					nw_cornerlatitude,\
					nw_cornerlongitude,\
					ne_cornerlatitude,\
					ne_cornerlongitude,\
					se_cornerlatitude,\
					se_cornerlongitude,\
					sw_cornerlatitude,\
					sw_cornerlongitude) \
					values (\
					default,\
					%s,\
					%s,\
					%s,\
					to_timestamp(%s, 'YYYY:MM:DD HH24:MI:SS'),\
					%s,\
					%s,\
					%s,\
					%s,\
					%s,\
					%s,\
					%s,\
					%s,\
					%s,\
					%s,\
					%s,\
					%s,\
					%s,\
					%s,\
					%s,\
					null, null, null, null, null, null, null, null)"
	cursor.execute(sqlStatement, (SourceFile, FileName, Directory, CreateDate, GPSLatitudeRef, GPSLongitudeRef, GPSAltitudeRef, GPSLatitude, GPSLongitude, GPSAltitude, AbsoluteAltitude, RelativeAltitude, GimbalYawDegree, FlightYawDegree, Yaw, CameraYaw, CameraPitch, CameraRoll, FocalLength35mm))
	print("Inserted!")
	connection.commit()
	cursor.close()

connection.close()	
exit()