PyObject *CharacterDB::GetCharPublicInfo3(uint32 characterID) {

    DBQueryResult res;

    if(!sDatabase.RunQuery(res,
                           "SELECT "
                           " character_.bounty,"
                           " character_.title,"
                           " character_.startDateTime,"
                           " character_.description,"
                           " character_.corporationID"
                           " FROM character_"
                           " WHERE characterID=%u", characterID))
    {
        codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
        return NULL;
    }

    return DBResultToRowset(res);
}

uint32 CharacterDB::AddOwnerNote(uint32 charID, const std::string & label, const std::string & content) {
    DBerror err;
    uint32 id;

    std::string lblS;
    sDatabase.DoEscapeString(lblS, label);

    std::string contS;
    sDatabase.DoEscapeString(contS, content);

    if (!sDatabase.RunQueryLID(err, id,
                               "INSERT INTO chrOwnerNote (ownerID, label, note) VALUES (%u, '%s', '%s');",
                               charID, lblS.c_str(), contS.c_str()))
    {
        codelog(SERVICE__ERROR, "Error on query: %s", err.c_str());
        return 0;
    }

    return id;
}

// Should probably make this function more generic once we know how the Args to CharMgrBound::Handle_ListStations work
PyRep *CharacterDB::GetCharStations(uint32 charID) {
    DBQueryResult res;

    //Query returns StationID, ItemCount
    if(!sDatabase.RunQuery(res,
                           " SELECT stationID, count(stationID) as itemCount "
                           " FROM ( "
                           "	SELECT stationID, locationID "
                           "	FROM entity, stastations "
                           "	WHERE entity.locationID = stastations.stationID "
                           "		and ownerID = %u "
                           " ) as a "
                           " GROUP BY stationID ", charID))
    {
        codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
        return NULL;
    }

    return DBResultToRowset(res);
}

PyRep *MarketDB::GetContractBids( uint32 contractID )
{
	DBQueryResult res;
	bool success = false;

	if(contractID == NULL)
	{
		success = sDatabase.RunQuery(res,"SELECT bidID, contractID, issuerID, quantity, issuerCorpID, issuerStationID, issuerSolarSystemID, issuerRegionID FROM contract_bids", contractID);
	}else{
		success = sDatabase.RunQuery(res,"SELECT bidID, contractID, issuerID, quantity, issuerCorpID, issuerStationID, issuerSolarSystemID, issuerRegionID FROM contract_bids WHERE contractID=%u", contractID);
	}

	if(!success)
	{
		codelog(MARKET__ERROR, "Error in query: %s", res.error.c_str() );
		return NULL;
	}

	return DBResultToRowset( res );
}

PyRep *MarketDB::GetRegionBest(uint32 regionID) {
    DBQueryResult res;

    if(!sDatabase.RunQuery(res,
        "SELECT"
        "    typeID, MIN(price) AS price, volRemaining, stationID "
        " FROM market_orders "
        " WHERE regionID=%u AND bid=%d"
        //" WHERE regionID=%u"
        " GROUP BY typeID", regionID, TransactionTypeSell))
    {
        codelog(MARKET__ERROR, "Error in query: %s", res.error.c_str());
        return NULL;
    }

    //NOTE: this SHOULD return a crazy dbutil.RowDict object which is
    //made up of packed blue.DBRow objects, but we do not understand
    //the marshalling of those well enough right now, and this object
    //provides the same interface. It is significantly bigger on the wire though.
    return(DBResultToIndexRowset(res, "typeID"));
}

PyRep *ConfigDB::GetMultiInvTypesEx(const std::vector<int32> &entityIDs) {

    std::string ids;
    ListToINString(entityIDs, ids, "-1");

    DBQueryResult res;

    if(!sDatabase.RunQuery(res,
        "SELECT"
        "   typeID,groupID,typeName,description,graphicID,radius,"
        "   mass,volume,capacity,portionSize,raceID,basePrice,"
        "   published,marketGroupID,chanceOfDuplicating "
        " FROM invTypes "
        " WHERE typeID in (%s)", ids.c_str()))
    {
        codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
        return NULL;
    }

    return(DBResultToRowList(res));
}

PyRep *MarketDB::GetTransactions(uint32 characterID, uint32 typeID, uint32 quantity, double minPrice, double maxPrice, uint64 fromDate, int buySell)
{
    DBQueryResult res;

    if(!sDatabase.RunQuery(res,
        "SELECT"
        " transactionID,transactionDateTime,typeID,quantity,price,transactionType,"
        " 0 AS corpTransaction,clientID,stationID"
        " FROM market_transactions "
        " WHERE clientID=%u AND (typeID=%u OR 0=%u) AND"
        " quantity>=%u AND price>=%f AND (price<=%f OR 0=%f) AND"
        " transactionDateTime>=%" PRIu64 " AND (transactionType=%d OR -1=%d)",
        characterID, typeID, typeID, quantity, minPrice, maxPrice, maxPrice, fromDate, buySell, buySell))
    {
        codelog( MARKET__ERROR, "Error in query: %s", res.error.c_str() );

        return NULL;
    }

    return DBResultToRowset(res);
}

PyObject *CharacterDB::GetCharacterAppearance(uint32 charID) {
	DBQueryResult res;
	
	if(!sDatabase.RunQuery(res,
		"SELECT "
		" accessoryID,beardID,costumeID,decoID,eyebrowsID,eyesID,hairID,"
		" lipstickID,makeupID,skinID,backgroundID,lightID,"
		" headRotation1,headRotation2,headRotation3,eyeRotation1,"
		" eyeRotation2,eyeRotation3,camPos1,camPos2,camPos3,"
		" morph1e,morph1n,morph1s,morph1w,morph2e,morph2n,"
		" morph2s,morph2w,morph3e,morph3n,morph3s,morph3w,"
		" morph4e,morph4n,morph4s,morph4w"
		" FROM character_ "
		" WHERE characterID=%u", charID))
	{
		codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
		return NULL;
	}

	return DBResultToRowset(res);
}

// Function: Query 'channels' table for a list of all channelIDs and traverse that list from the beginning to find
// the first gap in consecutive channelIDs and return the value for the first number in that gap.
uint32 LSCDB::GetNextAvailableChannelID()
{
	DBQueryResult res;

    // Query the 'channels' table to get a list of all channel IDs.
    // NOTE: For large servers, this is inefficient and as everything in this file should be using
    // the cached object system rather than touching the database, this query could cause large server slow-down
    // if there is a very large number of existing channels in the database.
	if( !sDatabase.RunQuery( res,
		" SELECT "
		"	channelID "
		" FROM channels "
		" WHERE channelID >= %u ", LSCService::BASE_CHANNEL_ID ))
	{
		codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
		return 0;
	}

	uint32 currentChannelID = LSCService::BASE_CHANNEL_ID;

	// Traverse through the rows in the query result until the first gap is found
	// and return the value that would be first (or only one) in the gap as the next
	// free channel ID:
	DBResultRow row;
	while( res.GetRow(row) )
	{
        const uint32 channelID = row.GetUInt( 0 );

        if( currentChannelID < channelID )
            return currentChannelID;

        ++currentChannelID;
	}

        // Check to make sure that the next available channelID is not equal to the Maximum channel ID value
	if( currentChannelID <= LSCService::MAX_CHANNEL_ID )
        return currentChannelID;
	else
        return 0;	// No free channel IDs found (this should never happen as there are way too many IDs to exhaust)
}

PyResult ShipDB::GetInsuranceContractsByOwnerID(uint32 ownerID)
{
	DBQueryResult res;
	if(!sDatabase.RunQuery(res,
		"SELECT "
		"  i.shipID, "
		"  i.startDate, "
		"  i.endDate, "
		"  i.fraction "
		"FROM "
		"  insurance i LEFT JOIN entity e ON i.shipID=e.itemID "
		"WHERE "
		"  e.ownerID=%d",
		ownerID
		))
	{
        codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
		return new PyNone;
	}

	return(DBResultToCRowset(res));
}

void NPC::_AwardBounty(SystemEntity *who) {
	//double bounty = m_self->entityKillBounty();
    double bounty = m_self->GetAttribute(AttrEntityKillBounty).get_float();
	if(bounty <= 0) {
		return;	//no bounty to award...
	}
	
	//TODO: handle case where drone strikes fatal blow... bounty goes to master.

	//TODO: handle distribution to gangs.
	
	if(who->IsClient() == false) {
		_log(NPC__TRACE, "Refusing to award bounty on %u to non-client %u", GetID(), who->GetID());
		return;	//bounty doesn't make sense for anything other than clients.
	}

	Client *killer = who->CastToClient();

	killer->AddBalance(bounty);

	std::string reason = "Bounty";	//TODO: improve this.
	
	if(!m_services.serviceDB().GiveCash(
			killer->GetID(),
			RefType_playerDonation,	//TODO: find the proper type
			m_self->itemID(),	//probably actually a special concord item ID or something.
			killer->GetID(),
			"",	//unknown const char *argID1,
			killer->GetAccountID(),
			accountCash,
			bounty,
			killer->GetBalance(),
			reason.c_str()
	)) {
		codelog(CLIENT__ERROR, "%s: Failed to record bountry of %f from death of %u (type %u)", killer->GetName(), bounty, GetID(), m_self->typeID());
		//well.. this isnt a huge deal, so we will get over it.
	}
	
}

bool CharacterDB::AddCharacterContractInfo( uint32 characterID )
{
	DBerror res;
	if(!sDatabase.RunQuery(res,
		"INSERT INTO chrcontractinfo ("
		"characterID,"
		" numOutstandingContractsNonCorp,"
		" numOutstandingContractsForCorp,"
		" numOutstandingContracts,"
		" numContractsLeft,"
		" numRequiresAttention,"
		" numRequiresAttentionCorp,"
		" numAssignedTo,"
		" numAssignedToCorp,"
		" numBiddingOn,"
		" numInProgress,"
		" numBiddingOnCorp,"
		" numInProgressCorp"
		") VALUES("
		"%d,"
		"0,"
		"0,"
		"0,"
		"0,"
		"0,"
		"0,"
		"0,"
		"0,"
		"0,"
		"0,"
		"0,"
		"0)", characterID ))
	{
		codelog( SERVICE__ERROR, "Error in query for %s", res.c_str() );
		return false;
	}
	return true;
}

PyObject *CharacterDB::GetCharPublicInfo(uint32 characterID) {
	DBQueryResult res;

	if(!sDatabase.RunQuery(res,
		"SELECT "
		" entity.typeID,"
		" character_.corporationID,"
        " chrBloodlines.raceID,"
		" bloodlineTypes.bloodlineID,"
		" character_.ancestryID,"
		" character_.careerID,"
		" character_.schoolID,"
		" character_.careerSpecialityID,"
		" entity.itemName AS characterName,"
		" 0 as age,"	//hack
		" character_.createDateTime,"
		" character_.gender,"
		" character_.characterID,"
		" character_.description,"
		" character_.corporationDateTime"
        " FROM character_ "
		"	LEFT JOIN entity ON characterID = itemID"
		"	LEFT JOIN bloodlineTypes USING (typeID)"
		"	LEFT JOIN chrBloodlines USING (bloodlineID)"
		" WHERE characterID=%u", characterID))
	{
		codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
		return NULL;
	}
	
	DBResultRow row;
	if(!res.GetRow(row)) {
		_log(SERVICE__ERROR, "Error in GetCharPublicInfo query: no data for char %d", characterID);
		return NULL;
	}
	return(DBRowToKeyVal(row));
	
}

//helper routine for GetMarketGroups
static void _PropigateItems(std::map< int, std::set<uint32> > &types, std::map<int, int> &parentChild, std::map<int, std::set<int> > &childParent, int group) {
    std::map<int, std::set<int> >::iterator children_res;
    children_res = childParent.find(group);
    if(children_res != childParent.end()) {
        //recurse to all children first.
        std::set<int>::iterator ccur, cend;
        ccur = children_res->second.begin();
        cend = children_res->second.end();
        for(; ccur != cend; ccur++) {
            _PropigateItems(types, parentChild, childParent, *ccur);
        }
    }

    if(group == -1) {
        return;    //we are root, we have no parent
    }
    //find our parent.
    std::map<int, int>::iterator parent_res;
    parent_res = parentChild.find(group);
    if(parent_res == parentChild.end()) {
        codelog(MARKET__ERROR, "Failed to find parent group in parentChild for %d", group);
        return;    //should never happen...
    }
    int parentID = parent_res->second;
    if(parentID == -1) {
        return;    //do not propigate up to NULL, we dont need it, and it would contain ALL items..
    }

    //now propigate all our items (which now includes all children items) up to our parent.
    //find our items
    std::map< int, std::set<uint32> >::iterator self_res;
    self_res = types.find(group);
    if(self_res == types.end())
        return;    //we have nothing for this group??

    //add all of our items into parent.
    types[parentID].insert(self_res->second.begin(), self_res->second.end());
}

PyResult MailMgrService::Handle_SyncMail(PyCallArgs &call)
{
    int firstId = 0, secondId = 0;

    if (call.tuple->size() == 2 && !call.tuple->GetItem(0)->IsNone() && !call.tuple->GetItem(1)->IsNone())
    {
        Call_TwoIntegerArgs args;
        if (!args.Decode(&call.tuple))
        {
            codelog(CLIENT__ERROR, "Failed to decode SyncMail args");
            return NULL;
        }

        // referring to a mail id range
        int firstId = args.arg1, secondId = args.arg2;
    }

    PyDict* dummy = new PyDict;
    dummy->SetItemString("oldMail", new PyNone());
    dummy->SetItemString("newMail", m_db->GetNewMail(call.client->GetCharacterID()));
    dummy->SetItemString("mailStatus", m_db->GetMailStatus(call.client->GetCharacterID()));
    return new PyObject("util.KeyVal", dummy);
}

// Function: Return true or false result for the check of whether or not the specified
// channel 'displayName' is already being used by a channel.
bool LSCDB::IsChannelNameAvailable(std::string name)
{
	DBQueryResult res;

	// MySQL query channels table for any channel whose displayName matches "name":
	if (!sDatabase.RunQuery(res, 
		" SELECT "
		"	displayName "
		" FROM channels "
		" WHERE displayName = upper('%s')", name.c_str()))
	{
		codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
		return false;
	}

	DBResultRow row;

    // Return true (this 'displayName' not in use) if there are no rows returned by the query:
	if (!res.GetRow(row))
		return true;
	else
		return false;
}

PyObjectEx *ConfigDB::GetMapObjects(uint32 entityID, bool wantRegions,
    bool wantConstellations, bool wantSystems, bool wantStations)
{
    const char *key = "solarSystemID";
    if(wantRegions) {
        entityID = 3;   /* a little hackish... */
        key = "typeID";
    } else if(wantConstellations) {
        key = "regionID";
    } else if(wantSystems) {
        key = "constellationID";
    } else if(wantStations) {
        key = "solarSystemID";
    }

    DBQueryResult res;

    if(!sDatabase.RunQuery(res,
        "SELECT "
        "   groupID,typeID,itemID,itemName,solarSystemID AS locationID,"
        "   orbitID,"
#ifndef WIN32
#warning hacked 'connector' field in GetMapObjects
#endif
        "   0 as connector,"
        "   x,y,z"
        " FROM mapDenormalize"
        " WHERE %s=%u", key, entityID
    ))
    {
        codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
        return NULL;
    }

    return DBResultToCRowset(res);
//    return DBResultToRowset(res);
}

PyObject *MarketDB::GetCorporationBills(uint32 corpID, bool payable)
{
    DBQueryResult res;
    bool success = false;

    if ( payable == true )
    {
        success = sDatabase.RunQuery(res, "SELECT billID, billTypeID, debtorID, creditorID, amount, dueDateTime, interest,"
            "externalID, paid externalID2 FROM billsPayable WHERE debtorID = %u", corpID);
    }
    else
    {
        success = sDatabase.RunQuery(res, "SELECT billID, billTypeID, debtorID, creditorID, amount, dueDateTime, interest,"
            "externalID, paid externalID2 FROM billsReceivable WHERE creditorID = %u", corpID);
    }

    if ( success == false )
    {
        codelog(MARKET__ERROR, "Error in query: %s", res.error.c_str());
        return NULL;
    }

    return DBResultToRowset(res);
}

AgentLevel *MissionDB::LoadAgentLevel(uint8 level) {
	AgentLevel *result = new AgentLevel;
	
	DBQueryResult res;

	if(!sDatabase.RunQuery(res,
		"SELECT missionID,missionName,missionLevel,"
		"	agtMissions.missionTypeID,missionTypeName,"
		"	importantMission"
		" FROM agtMissions"
		"	NATURAL JOIN agtMissionTypes"
		" WHERE missionLevel=%d",
		level
	))
	{
		codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
		delete result;
		return NULL;
	}

	std::vector<uint32> IDs;
	DBResultRow row;

	IDs.clear();
	while(res.GetRow(row)) {
		AgentMissionSpec *spec = new AgentMissionSpec;
		spec->missionID = row.GetUInt(0);
		spec->missionName = row.GetText(1);
		spec->missionLevel = row.GetUInt(2);
		spec->missionTypeID = row.GetUInt(3);
		spec->missionTypeName = row.GetText(1);
		spec->importantMission = (row.GetUInt(2)==0)?false:true;
		result->missions.push_back(spec);
	}
	
}

PyRep *MarketDB::GetOrders( uint32 regionID, uint32 typeID )
{
    DBQueryResult res;

    PyList* tup = new PyList();

    /*DBColumnTypeMap colmap;
    colmap["volRemaining"] = DBTYPE_R8;
    colmap["price"] = DBTYPE_CY;
    colmap["issued"] = DBTYPE_FILETIME;

    colmap["orderID"] = DBTYPE_I4;
    colmap["volEntered"] = DBTYPE_I4;
    colmap["minVolume"] = DBTYPE_I4;
    colmap["stationID"] = DBTYPE_I4;
    colmap["regionID"] = DBTYPE_I4;
    colmap["solarSystemID"] = DBTYPE_I4;
    colmap["jumps"] = DBTYPE_I4;

    colmap["duration"] = DBTYPE_I2;
    colmap["typeID"] = DBTYPE_I2;
    colmap["range"] = DBTYPE_I2;

    colmap["bid"] = DBTYPE_BOOL;

    //ordering: (painstakingly determined from packets)
    DBColumnOrdering ordering;
    ordering.push_back("price");
    ordering.push_back("volRemaining");
    ordering.push_back("issued");
    ordering.push_back("orderID");
    ordering.push_back("volEntered");
    ordering.push_back("minVolume");
    ordering.push_back("stationID");
    ordering.push_back("regionID");
    ordering.push_back("solarSystemID");
    ordering.push_back("jumps");    //not working right...
    ordering.push_back("typeID");
    ordering.push_back("range");
    ordering.push_back("duration");
    ordering.push_back("bid");*/

    //query sell orders
    //TODO: consider the `jumps` field... is it actually used? might be a pain in the ass if we need to actually populate it based on each queryier's location
    if(!sDatabase.RunQuery(res,
        "SELECT"
        "    price, volRemaining, typeID, `range`, orderID,"
        "   volEntered, minVolume, bid, issued as issueDate, duration,"
        "   stationID, regionID, solarSystemID, jumps"
        " FROM market_orders "
        " WHERE regionID=%u AND typeID=%u AND bid=%d", regionID, typeID, TransactionTypeSell))
    {
        codelog( MARKET__ERROR, "Error in query: %s", res.error.c_str() );

        PyDecRef( tup );
        return NULL;
    }
    sLog.Debug("MarketDB::GetOrders", "Fetched %d sell orders for type %d", res.GetRowCount(), typeID);

    //this is wrong.
    tup->AddItem( DBResultToCRowset( res ) );

    //query buy orders
    if(!sDatabase.RunQuery(res,
        "SELECT"
        "    price, volRemaining, typeID, `range`, orderID,"
        "   volEntered, minVolume, bid, issued as issueDate, duration,"
        "   stationID, regionID, solarSystemID, jumps"
        " FROM market_orders "
        " WHERE regionID=%u AND typeID=%u AND bid=%d", regionID, typeID, TransactionTypeBuy))
    {
        codelog( MARKET__ERROR, "Error in query: %s", res.error.c_str() );

        PyDecRef( tup );
        return NULL;
    }
    sLog.Debug("MarketDB::GetOrders", "Fetched %d buy orders for type %d", res.GetRowCount(), typeID);

    //this is wrong.
    tup->AddItem( DBResultToCRowset( res ) );

    return tup;
}