#!/usr/bin/python
import sys
import sqlite3

# definition of some functions
def GetIndex(AConnection, ATableName, AColumnName, AValue):
	"returns the index of the row or -1 when not found"
	c = AConnection.execute("SELECT rowid FROM {t} WHERE {c}='{v}' LIMIT 1".\
							format(t=ATableName, c=AColumnName, v=AValue))
	row = c.fetchone()
	if row is None:
		return -1
	else:
		return row[0]

# check the parameters
if len(sys.argv) <= 7:
	print "AddLocusPOI <filename> <POI ID> <name> <longitude> <latitude> <category> <subcategory>"
	print "=> add a POI into a Locus Map .osm.db file"
	exit(1)

filename=sys.argv[1]
ID=sys.argv[2]
name=sys.argv[3]
longitude=sys.argv[4]
latitude=sys.argv[5]
category=sys.argv[6]
subcategory=sys.argv[7]

# Connect to the spatialite DB
connection = sqlite3.connect(filename)
connection.enable_load_extension(True)
connection.execute("select load_extension('mod_spatialite')")

# Add the POI the the table "Points" if the ID doesn't exist yet
index1 = GetIndex(connection, 'Points', 'id', ID)
if (-1 == index1):
	print "POI %s is new" % (ID)
	connection.execute("INSERT INTO Points (id, type, name, geom) VALUES ({i}, 'P', '{n}', GeomFromText('POINT({lon} {lat})', 4326))".\
                       format(i=ID, n=name, lon=longitude, lat=latitude))
	index1 = GetIndex(connection, 'Points', 'id', ID)

# Look for the category or add it
index2 = GetIndex(connection, 'FoldersRoot', 'name', category)
if (-1 == index2):
	print "Category %s is new" % (category)
	connection.execute("INSERT INTO FoldersRoot (name) VALUES ('{n}')".format(n=category))
	index2 = GetIndex(connection, 'FoldersRoot', 'name', category)

# Look for the subcategory or add it
index3 = GetIndex(connection, 'FoldersSub', 'name', subcategory)
if (-1 == index3):
	print "Subcategory %s is new" % (subcategory)
	connection.execute("INSERT INTO FoldersSub (name) VALUES ('{n}')".format(n=subcategory))
	index3 = GetIndex(connection, 'FoldersSub', 'name', subcategory)

# Link the POI to the category and subcategory	
print "Linking Point on index %d to category on index %d and subcategory on index %d" % (index1, index2, index3)
connection.execute("INSERT INTO Points_Root_Sub (Points_id, FoldersRoot_id, FoldersSub_id) VALUES ({ix1}, {ix2}, {ix3})".\
                   format(ix1=index1, ix2=index2, ix3=index3))

connection.commit()
connection.close()