import sqlite3;

databaseConnection = None;
databaseRunning = False;

def connectDB():
	global databaseConnection, databaseRunning;
	if( databaseRunning ):
		return;
	if( not databaseConnection ):
		databaseConnection = sqlite3.connect( "dom103-sqlite3-v1.db" );
	if( databaseConnection ):
		databaseConnection.row_factory = sqlite3.Row;
		databaseRunning = True;
	else:
		raise IOError( "Unable to get a database connection." );

def genericLookup( sql, argtuple, dbc = None ):
	global databaseConnection, databaseRunning;
	if( dbc == None ):
		if( not databaseRunning ):
			connectDB();
		dbc = databaseConnection;
	cursor = dbc.cursor();
	cursor.execute( sql, argtuple );
	results = [];
	for row in cursor:
		results.append( row );
	del( cursor );
	return results;

def typeId2Name( typeid, dbc = None ):
	row = genericLookup( "SELECT typeName from invTypes where typeID = ? limit 1", ( typeid, ), dbc );
	if( len( row ) == 0 ):
		return "Unknown item ( %s )" % typeid;
	else:
		return row[0][ "typeName" ];

def volume4TypeId( typeid, dbc = None ):
	row = genericLookup( "SELECT volume from invTypes where typeID = ? limit 1", ( typeid, ), dbc );
	return row[0][ "volume" ];


def getRegionSystems( regionid, dbc = None ):
	results = [];
	rows = genericLookup( "select solarSystemID from mapSolarSystems where regionID = ?", ( regionid, ), dbc );
	for row in rows:
		results.append( row[ "solarSystemID" ] );
	return results;
	
def typeIdsForGroup( groupid, dbc = None ):
	results = [];
	rows = genericLookup( "select typeID from invTypes where groupID = ?", ( groupid, ), dbc );
	for row in rows:
		results.append( row[ "typeID" ] );
	return results;

def links4System( systemid, dbc = None ):
	results = [];
	rows = genericLookup( "SELECT toSolarSystemID from mapSolarSystemJumps WHERE fromSolarSystemId = ?", ( systemid, ), dbc );
	for row in rows:
		results.append( row[ "toSolarSystemID" ] );
	return results;

def allLinks( dbc = None ):
	results = {};
	rows = genericLookup( "select fromSolarSystemID, toSolarSystemID from mapSolarSystemJumps", tuple(), dbc );
	for row in rows:
		if( results.has_key( row[ "fromSolarSystemID" ] ) == False ):
			results[ row[ "fromSolarSystemID" ] ] = [ row[ "toSolarSystemID" ] ];
		else:
			results[ row[ "fromSolarSystemID" ] ].append( row[ "toSolarSystemID" ] );
	return results;
	
def systemId2Name( systemid, dbc = None ):
	row = genericLookup( "SELECT solarSystemName from mapSolarSystems where solarSystemID = ? limit 1", ( systemid, ), dbc );
	return row[0][ "solarSystemName" ];

def systemName2Id( systemname, dbc = None ):
	row = genericLookup( "SELECT solarSystemId from mapSolarSystems where solarSystemName = ? limit 1", ( systemname, ), dbc );
	return row[0][ "solarSystemId" ];

def systemIds2Names( systemids, dbc = None ):
	# dbapi doesn't handle lists as interpolation arguments properly
	query = "SELECT solarSystemId, solarSystemName FROM mapSolarSystems WHERE solarSystemId IN ( %s )" % ( ",".join( [ str( y ) for y in [ int( x ) for x in systemids ] ] ) ); 
	results = {};
	rows = genericLookup( query, tuple(), dbc );
	for row in rows:
		results[ row[ "solarSystemId" ] ] = row[ "solarSystemName" ];
	return results;

def systemIds2Sec( systemids, dbc = None ):
	# dbapi doesn't handle lists as interpolation arguments properly
	query = "SELECT solarSystemId, security FROM mapSolarSystems WHERE solarSystemId IN ( %s )" % ( ",".join( [ str( y ) for y in [ int( x ) for x in systemids ] ] ) ); 
	results = {};
	rows = genericLookup( query, tuple(), dbc );
	for row in rows:
		results[ row[ "solarSystemId" ] ] = row[ "security" ];
	return results;

def systems4Sec( highbound, lowbound = 0, dbc = None ):
	results = [];
	rows = genericLookup( "SELECT solarSystemID FROM mapSolarSystems WHERE security >= ? AND security <= ?", (lowbound, highbound), dbc );
	for row in rows:
		results.append( row[ "solarSystemID" ] );
	return results;


def stationIds4System( systemid, dbc = None ):
	results = [];
	rows = genericLookup( "SELECT solarSystemID, stationID FROM staStations WHERE solarSystemId = ?", ( systemid, ), dbc );
	for row in rows:
		results.append( row[ "stationID" ] );
	return results;

def allSec( dbc = None ):
	results = {};
	rows = genericLookup( "SELECT solarSystemId, security FROM mapSolarSystems", tuple(), dbc );
	for row in rows:
		results[ row[ "solarSystemId" ] ] = row[ "security" ];
	return results;

def regionId4System( systemid, dbc = None ):
	row = genericLookup( "SELECT regionid from mapSolarSystems where solarSystemId = ? limit 1", ( systemid, ), dbc );
	return row[0][ "regionid" ];

def regionId2Name( regionid, dbc = None ):
	row = genericLookup( "SELECT regionName from mapRegions where regionID = ? limit 1", ( regionid, ), dbc );
	return row[0][ "regionName" ];

def regionName2Id( regionname, dbc = None ):
	row = genericLookup( "SELECT regionID from mapRegions where regionName = ? limit 1", ( regionname, ), dbc );
	return row[0][ "regionID" ];