#!/usr/bin/env python

import oursql
import urllib2
from xml.dom import minidom

system_name = 'amarr'
#system_name = 'jita'
conn = oursql.connect(db='eve', user='root')

def get_systemid(name):
	curs = conn.cursor()
	try:
		#SELECT regionID FROM mapRegions WHERE regionName
		curs.execute('''
			SELECT solarSystemID
			FROM mapSolarSystems
			WHERE solarSystemName LIKE ?;
		''', (name,))
		return curs.fetchone()[0]
	finally:
		curs.close()

def iter_blueprints():
	curs = conn.cursor()
	try:
		curs.execute('''
			SELECT b.productTypeID, t.typeName FROM invBlueprintTypes as b
			INNER JOIN invTypes AS t ON b.productTypeID = t.typeID
		''')
		while True:
			r = curs.fetchone()
			if r is None:
				break
			yield r
	finally:
		curs.close()

def get_mats(typeid):
	curs = conn.cursor()
	try:
		# base mats
		curs.execute('''
			SELECT t.typeID, m.quantity
			FROM invTypeMaterials AS m
			INNER JOIN invTypes AS t ON m.materialTypeID = t.typeID
			WHERE m.typeID = ?;
		''', (typeid,))
		mats = dict(curs.fetchall())
		# extra mats
		curs.execute('''
			SELECT blueprintTypeID FROM invBlueprintTypes
			WHERE productTypeID = ?
		''', (typeid,))
		blueprint_typeid = curs.fetchall()[0][0]
		curs.execute('''
			SELECT t.typeID, r.quantity
			FROM ramTypeRequirements AS r
			INNER JOIN invTypes AS t ON r.requiredTypeID = t.typeID
			INNER JOIN invGroups AS g ON t.groupID = g.groupID
			WHERE r.typeID = ? AND r.activityID = 1 AND g.categoryID != 16;
		''', (blueprint_typeid,))
		while True:
			r = curs.fetchone()
			if r is None:
				break
			mat_typeid, quantity = r
			if mat_typeid not in minerals.keys():
				return None
			try:
				mats[mat_typeid] += quantity
			except KeyError:
				mats[mat_typeid] = quantity
		return mats
	finally:
		curs.close()

def get_prices(typeid, systemid):
	# &regionlimit=%d
	url = 'http://api.eve-central.com/api/marketstat?typeid=%d&usesystem=%d' % (typeid, systemid)
	xml = minidom.parse(urllib2.urlopen(url))
	buy = xml.getElementsByTagName('buy')[0]
	buy_max = buy.getElementsByTagName('max')[0]
	buy_price = float(buy_max.childNodes[0].data)
	sell = xml.getElementsByTagName('sell')[0]
	sell_min = sell.getElementsByTagName('min')[0]
	sell_price = float(sell_min.childNodes[0].data)
	return buy_price, sell_price

def calc_margin(typeid, systemid):
	mats = get_mats(typeid)
	cost = 0
	for mtypeid, quantity in mats.iteritems():
		try:
			cost += prices[mtypeid][1] * quantity
		except KeyError:
			return
	if cost == 0:
		return
	sell_value = get_prices(typeid, systemid)[0]
	profit = sell_value - cost
	margin = profit / cost
	return margin

minerals = {
		34: 'Tritanium',
		35: 'Pyerite',
		36: 'Mexallon',
		40: 'Megacyte',
		37: 'Isogen',
		38: 'Nocxium',
		39: 'Zydrine',
	}
prices = {}

systemid = get_systemid(system_name)
for typeid in minerals.iterkeys():
	prices[typeid] = get_prices(typeid, systemid)
for typeid, name in iter_blueprints():
	margin = calc_margin(typeid, systemid)
	if margin is not None:
		print '%0.2f\t%s' % (margin, name)