impl banishment, deaths, and buddies queries

4 files changed, 640 insertions, 63 deletions

diff --git a/sql/schema.sql b/sql/schema.sql
index 56c36fd..81844b6 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -47,22 +47,36 @@ CREATE INDEX IF NOT EXISTS CharactersAccountIndex
 CREATE INDEX IF NOT EXISTS CharactersGuildIndex
 		ON Characters(Guild, Rank);
 
-CREATE TABLE IF NOT EXISTS CharacterBuddies (
+CREATE TABLE IF NOT EXISTS CharacterRights (
 	CharacterID INTEGER NOT NULL,
-	BuddyID INTEGER NOT NULL,
-	PRIMARY KEY (CharacterID, BuddyID)
+	Right TEXT NOT NULL COLLATE NOCASE,
+	PRIMARY KEY(CharacterID, Right)
 );
 
-CREATE TABLE IF NOT EXISTS CharacterInvitations (
-	WorldID INTEGER NOT NULL,
+CREATE TABLE IF NOT EXISTS CharacterDeaths (
 	CharacterID INTEGER NOT NULL,
-	PRIMARY KEY (WorldID, CharacterID)
+	Level INTEGER NOT NULL,
+	OffenderID INTEGER NOT NULL,
+	Remark TEXT NOT NULL,
+	Unjustified INTEGER NOT NULL,
+	Timestamp INTEGER NOT NULL
 );
+CREATE INDEX IF NOT EXISTS CharacterDeathsCharacterIndex
+		ON CharacterDeaths(CharacterID, Level);
+CREATE INDEX IF NOT EXISTS CharacterDeathsOffenderIndex
+		ON CharacterDeaths(OffenderID, Unjustified);
 
-CREATE TABLE IF NOT EXISTS CharacterRights (
+CREATE TABLE IF NOT EXISTS Buddies (
+	WorldID INTEGER NOT NULL,
+	AccountID INTEGER NOT NULL,
+	BuddyID INTEGER NOT NULL,
+	PRIMARY KEY (WorldID, AccountID, BuddyID)
+);
+
+CREATE TABLE IF NOT EXISTS WorldInvitations (
+	WorldID INTEGER NOT NULL,
 	CharacterID INTEGER NOT NULL,
-	Right TEXT NOT NULL COLLATE NOCASE,
-	PRIMARY KEY(CharacterID, Right)
+	PRIMARY KEY (WorldID, CharacterID)
 );
 
 -- House Tables
@@ -112,6 +126,7 @@ CREATE TABLE IF NOT EXISTS HouseTransfers (
 
 CREATE TABLE IF NOT EXISTS HouseAuctionExclusions (
 	CharacterID INTEGER NOT NULL,
+	Issued INTEGER NOT NULL,
 	Until INTEGER NOT NULL,
 	BanishmentID INTEGER NOT NULL
 );
@@ -146,17 +161,22 @@ CREATE TABLE IF NOT EXISTS Banishments (
 	Until INTEGER NOT NULL,
 	PRIMARY KEY (BanishmentID)
 );
-CREATE INDEX IF NOT EXISTS BanishmentsAccount
+CREATE INDEX IF NOT EXISTS BanishmentsAccountIndex
 		ON Banishments(AccountID, Until, FinalWarning);
 
 CREATE TABLE IF NOT EXISTS IPBanishments (
-	IPAddress INTEGER NOT NULL,
 	CharacterID INTEGER NOT NULL,
+	IPAddress INTEGER NOT NULL,
 	GamemasterID INTEGER NOT NULL,
 	Reason TEXT NOT NULL,
 	Comment TEXT NOT NULL,
-	PRIMARY KEY (IPAddress)
+	Issued INTEGER NOT NULL,
+	Until INTEGER NOT NULL
 );
+CREATE INDEX IF NOT EXISTS IPBanishmentsAddressIndex
+		ON IPBanishments(IPAddress);
+CREATE INDEX IF NOT EXISTS IPBanishmentsCharacterIndex
+		ON IPBanishments(CharacterID);
 
 CREATE TABLE IF NOT EXISTS Namelocks (
 	CharacterID INTEGER NOT NULL,
@@ -179,20 +199,33 @@ CREATE TABLE IF NOT EXISTS Notations (
 CREATE INDEX IF NOT EXISTS NotationsCharacterIndex
 		ON Notations(CharacterID);
 
+CREATE TABLE IF NOT EXISTS Statements (
+	WorldID INTEGER NOT NULL,
+	Timestamp INTEGER NOT NULL,
+	StatementID INTEGER NOT NULL,
+	CharacterID INTEGER NOT NULL,
+	Channel TEXT NOT NULL,
+	Text TEXT NOT NULL,
+	PRIMARY KEY (WorldID, Timestamp, StatementID)
+);
+CREATE INDEX IF NOT EXISTS StatementsCharacterIndex
+		ON Statements(CharacterID, Timestamp);
+
 CREATE TABLE IF NOT EXISTS ReportedStatements (
+	WorldID INTEGER NOT NULL,
 	Timestamp INTEGER NOT NULL,
 	StatementID INTEGER NOT NULL,
 	CharacterID INTEGER NOT NULL,
-	Statement TEXT NOT NULL,
-	Context TEXT NOT NULL,
 	BanishmentID INTEGER NOT NULL,
 	ReporterID INTEGER NOT NULL,
 	Reason TEXT NOT NULL,
 	Comment TEXT NOT NULL,
-	PRIMARY KEY (Timestamp, StatementID)
+	PRIMARY KEY (WorldID, Timestamp, StatementID)
 );
 CREATE INDEX IF NOT EXISTS ReportedStatementsCharacterIndex
 		ON ReportedStatements(CharacterID, Timestamp);
+CREATE INDEX IF NOT EXISTS ReportedStatementsBanishmentIndex
+		ON ReportedStatements(BanishmentID);
 
 -- Info Tables
 --==============================================================================
diff --git a/src/connections.cc b/src/connections.cc
index 3672eaa..e4ac2f2 100644
--- a/src/connections.cc
+++ b/src/connections.cc
@@ -402,6 +402,22 @@ void ExitConnections(void){
 
 // Connection Queries
 //==============================================================================
+void CompoundBanishment(TBanishmentStatus Status, int *Days, bool *FinalWarning){
+	// TODO(fusion): We might want to add all these constants as config values.
+	ASSERT(Days != NULL && FinalWarning != NULL);
+	if(Status.FinalWarning){
+		*FinalWarning = false;
+		*Days = 0; // permanent
+	}else if(Status.TimesBanished > 5 || *FinalWarning){
+		*FinalWarning = true;
+		if(*Days < 30){
+			*Days = 30;
+		}else{
+			*Days *= 2;
+		}
+	}
+}
+
 TWriteBuffer PrepareResponse(TConnection *Connection, int Status){
 	if(Connection->State != CONNECTION_PROCESSING){
 		LOG_ERR("Connection %s is not processing query (State: %d)",
@@ -534,7 +550,6 @@ void ProcessSetNamelockQuery(TConnection *Connection, TReadBuffer *Buffer){
 	Buffer->ReadString(Reason, sizeof(Reason));
 	Buffer->ReadString(Comment, sizeof(Comment));
 
-	// TODO(fusion): Might not even allow empty ip string.
 	int IPAddress = 0;
 	if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){
 		SendQueryStatusFailed(Connection);
@@ -588,14 +603,13 @@ void ProcessBanishAccountQuery(TConnection *Connection, TReadBuffer *Buffer){
 	char IPString[16];
 	char Reason[200];
 	char Comment[200];
-	int GamemasterID = Buffer->Read16();
+	int GamemasterID = (int)Buffer->Read32();
 	Buffer->ReadString(CharacterName, sizeof(CharacterName));
 	Buffer->ReadString(IPString, sizeof(IPString));
 	Buffer->ReadString(Reason, sizeof(Reason));
 	Buffer->ReadString(Comment, sizeof(Comment));
 	bool FinalWarning = Buffer->ReadFlag();
 
-	// TODO(fusion): Might not even allow empty ip string.
 	int IPAddress = 0;
 	if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){
 		SendQueryStatusFailed(Connection);
@@ -626,17 +640,9 @@ void ProcessBanishAccountQuery(TConnection *Connection, TReadBuffer *Buffer){
 		return;
 	}
 
-	// TODO(fusion): We might want to add all these constants as config values.
-	int BanishmentID;
+	int BanishmentID = 0;
 	int Days = 7;
-	if(Status.FinalWarning){
-		Days = 0; // permanent
-		FinalWarning = false;
-	}else if(Status.TimesBanished >= 5 || FinalWarning){
-		Days = 30;
-		FinalWarning = true;
-	}
-
+	CompoundBanishment(Status, &Days, &FinalWarning);
 	if(!InsertBanishment(CharacterID, IPAddress, GamemasterID,
 			Reason, Comment, FinalWarning, Days * 86400, &BanishmentID)){
 		SendQueryStatusFailed(Connection);
@@ -656,27 +662,277 @@ void ProcessBanishAccountQuery(TConnection *Connection, TReadBuffer *Buffer){
 }
 
 void ProcessSetNotationQuery(TConnection *Connection, TReadBuffer *Buffer){
-	SendQueryStatusFailed(Connection);
+	if(Connection->ApplicationType != APPLICATION_TYPE_GAME){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	char CharacterName[30];
+	char IPString[16];
+	char Reason[200];
+	char Comment[200];
+	int GamemasterID = Buffer->Read32();
+	Buffer->ReadString(CharacterName, sizeof(CharacterName));
+	Buffer->ReadString(IPString, sizeof(IPString));
+	Buffer->ReadString(Reason, sizeof(Reason));
+	Buffer->ReadString(Comment, sizeof(Comment));
+
+	int IPAddress = 0;
+	if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	TransactionScope Tx("SetNotation");
+	if(!Tx.Begin()){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	int CharacterID = GetCharacterID(Connection->WorldID, CharacterName);
+	if(CharacterID == 0){
+		SendQueryStatusError(Connection, 1);
+		return;
+	}
+
+	// TODO(fusion): Might be `NO_BANISHMENT`.
+	if(!GetCharacterRight(CharacterID, "NOTATION")){
+		SendQueryStatusError(Connection, 2);
+		return;
+	}
+
+	int BanishmentID = 0;
+	if(GetNotationCount(CharacterID) >= 5){
+		int BanishmentDays = 7;
+		bool FinalWarning = false;
+		TBanishmentStatus Status = GetBanishmentStatus(CharacterID);
+		CompoundBanishment(Status, &BanishmentDays, &FinalWarning);
+		if(!InsertBanishment(CharacterID, IPAddress, 0, "Excessive Notations",
+				"", FinalWarning, BanishmentDays, &BanishmentID)){
+			SendQueryStatusFailed(Connection);
+			return;
+		}
+	}
+
+	if(!InsertNotation(CharacterID, IPAddress, GamemasterID, Reason, Comment)){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	if(!Tx.Commit()){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	TWriteBuffer WriteBuffer = PrepareResponse(Connection, QUERY_STATUS_OK);
+	WriteBuffer.Write32((uint32)BanishmentID);
+	SendResponse(Connection, &WriteBuffer);
 }
 
 void ProcessReportStatementQuery(TConnection *Connection, TReadBuffer *Buffer){
-	SendQueryStatusFailed(Connection);
+	if(Connection->ApplicationType != APPLICATION_TYPE_GAME){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	char CharacterName[30];
+	char Reason[200];
+	char Comment[200];
+	int ReporterID = Buffer->Read32();
+	Buffer->ReadString(CharacterName, sizeof(CharacterName));
+	Buffer->ReadString(Reason, sizeof(Reason));
+	Buffer->ReadString(Comment, sizeof(Comment));
+	int BanishmentID = Buffer->Read32();
+	int StatementID = Buffer->Read32();
+	int NumStatements = Buffer->Read16();
+
+	if(StatementID == 0){
+		LOG_ERR("Missing reported statement id");
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	if(NumStatements == 0){
+		LOG_ERR("Missing report statements");
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	TStatement *ReportedStatement = NULL;
+	TStatement *Statements = (TStatement*)alloca(NumStatements * sizeof(TStatement));
+	for(int i = 0; i < NumStatements; i += 1){
+		Statements[i].StatementID = (int)Buffer->Read32();
+		Statements[i].Timestamp = (int)Buffer->Read32();
+		Statements[i].CharacterID = (int)Buffer->Read32();
+		Buffer->ReadString(Statements[i].Channel, sizeof(Statements[i].Channel));
+		Buffer->ReadString(Statements[i].Text, sizeof(Statements[i].Text));
+
+		if(Statements[i].StatementID == StatementID){
+			if(ReportedStatement != NULL){
+				LOG_WARN("Reported statement (%d, %d, %d) appears multiple times",
+						Connection->WorldID, Statements[i].Timestamp,
+						Statements[i].StatementID);
+			}
+			ReportedStatement = &Statements[i];
+		}
+	}
+
+	if(ReportedStatement == NULL){
+		LOG_ERR("Missing reported statement");
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	TransactionScope Tx("ReportStatement");
+	if(!Tx.Begin()){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	int CharacterID = GetCharacterID(Connection->WorldID, CharacterName);
+	if(CharacterID == 0){
+		SendQueryStatusError(Connection, 1);
+		return;
+	}else if(ReportedStatement->CharacterID != CharacterID){
+		LOG_ERR("Reported statement character mismatch");
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	if(IsStatementReported(Connection->WorldID, ReportedStatement)){
+		SendQueryStatusError(Connection, 2);
+		return;
+	}
+
+	if(!InsertStatements(Connection->WorldID, NumStatements, Statements)){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	if(!InsertReportedStatement(Connection->WorldID, ReportedStatement,
+			BanishmentID, ReporterID, Reason, Comment)){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	if(!Tx.Commit()){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	SendQueryStatusOk(Connection);
 }
 
-void ProcessBanishIpAddressQuery(TConnection *Connection, TReadBuffer *Buffer){
-	SendQueryStatusFailed(Connection);
+void ProcessBanishIPAddressQuery(TConnection *Connection, TReadBuffer *Buffer){
+	if(Connection->ApplicationType != APPLICATION_TYPE_GAME){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	char CharacterName[30];
+	char IPString[16];
+	char Reason[200];
+	char Comment[200];
+	int GamemasterID = Buffer->Read16();
+	Buffer->ReadString(CharacterName, sizeof(CharacterName));
+	Buffer->ReadString(IPString, sizeof(IPString));
+	Buffer->ReadString(Reason, sizeof(Reason));
+	Buffer->ReadString(Comment, sizeof(Comment));
+
+	int IPAddress = 0;
+	if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	TransactionScope Tx("BanishIP");
+	if(!Tx.Begin()){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	int CharacterID = GetCharacterID(Connection->WorldID, CharacterName);
+	if(CharacterID == 0){
+		SendQueryStatusError(Connection, 1);
+		return;
+	}
+
+	// TODO(fusion): Might be `NO_BANISHMENT`.
+	if(!GetCharacterRight(CharacterID, "IP_BANISHMENT")){
+		SendQueryStatusError(Connection, 2);
+		return;
+	}
+
+	// IMPORTANT(fusion): It is not a good idea to ban an IP address, specially
+	// V4 addresses, as they may be dynamically assigned or represent the address
+	// of a public ISP router that manages multiple clients.
+	int BanishmentDays = 3;
+	if(!InsertIPBanishment(CharacterID, IPAddress, GamemasterID,
+			Reason, Comment, BanishmentDays * 86400)){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	if(!Tx.Commit()){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	SendQueryStatusOk(Connection);
 }
 
 void ProcessLogCharacterDeathQuery(TConnection *Connection, TReadBuffer *Buffer){
-	SendQueryStatusFailed(Connection);
+	if(Connection->ApplicationType != APPLICATION_TYPE_GAME){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	char Remark[30];
+	int CharacterID = (int)Buffer->Read32();
+	int Level = Buffer->Read16();
+	int OffenderID = (int)Buffer->Read32();
+	Buffer->ReadString(Remark, sizeof(Remark));
+	bool Unjustified = Buffer->ReadFlag();
+	int Timestamp = (int)Buffer->Read32();
+	if(!InsertCharacterDeath(Connection->WorldID, CharacterID, Level,
+			OffenderID, Remark, Unjustified, Timestamp)){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	SendQueryStatusOk(Connection);
 }
 
 void ProcessAddBuddyQuery(TConnection *Connection, TReadBuffer *Buffer){
-	SendQueryStatusFailed(Connection);
+	if(Connection->ApplicationType != APPLICATION_TYPE_GAME){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	int AccountID = (int)Buffer->Read32();
+	int BuddyID = (int)Buffer->Read32();
+	if(!InsertBuddy(Connection->WorldID, AccountID, BuddyID)){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	SendQueryStatusOk(Connection);
 }
 
 void ProcessRemoveBuddyQuery(TConnection *Connection, TReadBuffer *Buffer){
-	SendQueryStatusFailed(Connection);
+	if(Connection->ApplicationType != APPLICATION_TYPE_GAME){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	int AccountID = (int)Buffer->Read32();
+	int BuddyID = (int)Buffer->Read32();
+	if(!DeleteBuddy(Connection->WorldID, AccountID, BuddyID)){
+		SendQueryStatusFailed(Connection);
+		return;
+	}
+
+	SendQueryStatusOk(Connection);
 }
 
 void ProcessDecrementIsOnlineQuery(TConnection *Connection, TReadBuffer *Buffer){
@@ -930,7 +1186,7 @@ void ProcessInsertHousesQuery(TConnection *Connection, TReadBuffer *Buffer){
 		return;
 	}
 
-	TransactionScope Tx("UpdateHouses");
+	TransactionScope Tx("InsertHouses");
 	if(!Tx.Begin()){
 		SendQueryStatusFailed(Connection);
 		return;
@@ -1083,16 +1339,10 @@ void ProcessExcludeFromAuctionsQuery(TConnection *Connection, TReadBuffer *Buffe
 	int ExclusionDays = 7;
 	int BanishmentID = 0;
 	if(Banish){
-		bool FinalWarning = false;
 		int BanishmentDays = 7;
+		bool FinalWarning = false;
 		TBanishmentStatus Status = GetBanishmentStatus(CharacterID);
-		if(Status.FinalWarning){
-			BanishmentDays = 0; // permanent
-		}else if(Status.TimesBanished >= 5){
-			BanishmentDays = 30;
-			FinalWarning = true;
-		}
-
+		CompoundBanishment(Status, &BanishmentDays, &FinalWarning);
 		if(!InsertBanishment(CharacterID, 0, 0, "Spoiling Auction",
 				"", FinalWarning, BanishmentDays * 86400, &BanishmentID)){
 			SendQueryStatusFailed(Connection);
@@ -1243,7 +1493,7 @@ void ProcessConnectionQuery(TConnection *Connection){
 		case QUERY_BANISH_ACCOUNT:				ProcessBanishAccountQuery(Connection, &Buffer); break;
 		case QUERY_SET_NOTATION:				ProcessSetNotationQuery(Connection, &Buffer); break;
 		case QUERY_REPORT_STATEMENT:			ProcessReportStatementQuery(Connection, &Buffer); break;
-		case QUERY_BANISH_IP_ADDRESS:			ProcessBanishIpAddressQuery(Connection, &Buffer); break;
+		case QUERY_BANISH_IP_ADDRESS:			ProcessBanishIPAddressQuery(Connection, &Buffer); break;
 		case QUERY_LOG_CHARACTER_DEATH:			ProcessLogCharacterDeathQuery(Connection, &Buffer); break;
 		case QUERY_ADD_BUDDY:					ProcessAddBuddyQuery(Connection, &Buffer); break;
 		case QUERY_REMOVE_BUDDY:				ProcessRemoveBuddyQuery(Connection, &Buffer); break;
diff --git a/src/database.cc b/src/database.cc
index 5d48bac..eb91dbc 100644
--- a/src/database.cc
+++ b/src/database.cc
@@ -308,6 +308,7 @@ bool DecrementIsOnline(int WorldID, int CharacterID){
 }
 
 bool ClearIsOnline(int WorldID, int *NumAffectedCharacters){
+	ASSERT(NumAffectedCharacters != NULL);
 	sqlite3_stmt *Stmt = PrepareQuery(
 			"UPDATE Characters SET IsOnline = 0"
 			" WHERE WorldID = ?1 AND IsOnline != 0");
@@ -368,6 +369,92 @@ bool GetCharacterIndexEntries(int WorldID, int MinimumCharacterID,
 	return true;
 }
 
+bool InsertCharacterDeath(int WorldID, int CharacterID, int Level,
+		int OffenderID, const char *Remark, bool Unjustified, int Timestamp){
+	// NOTE(fusion): Same as `DecrementIsOnline`.
+	sqlite3_stmt *Stmt = PrepareQuery(
+			"INSERT INTO CharacterDeaths (CharacterID, Level,"
+				" OffenderID, Remark, Unjustified, Timestamp)"
+			" SELECT ?2, ?3, ?4, ?5, ?6, ?7 FROM Characters"
+				" WHERE WorldID = ?1 AND CharacterID = ?2");
+	if(Stmt == NULL){
+		LOG_ERR("Failed to prepare query");
+		return false;
+	}
+
+	if(sqlite3_bind_int(Stmt, 1, WorldID)               != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 2, CharacterID)           != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 3, Level)                 != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 4, OffenderID)            != SQLITE_OK
+	|| sqlite3_bind_text(Stmt, 5, Remark, -1, NULL)     != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 6, (Unjustified ? 1 : 0)) != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 7, Timestamp)             != SQLITE_OK){
+		LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
+		return false;
+	}
+
+	if(sqlite3_step(Stmt) != SQLITE_DONE){
+		LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+		return false;
+	}
+
+	return sqlite3_changes(g_Database) > 0;
+}
+
+bool InsertBuddy(int WorldID, int AccountID, int BuddyID){
+	// NOTE(fusion): Same as `DecrementIsOnline`.
+	// NOTE(fusion): Use the `IGNORE` conflict resolution to make duplicate row
+	// errors appear as successful insertions.
+	sqlite3_stmt *Stmt = PrepareQuery(
+			"INSERT OR IGNORE INTO Buddies (WorldID, AccountID, BuddyID)"
+			" SELECT ?1, ?2, ?3 FROM Characters"
+				" WHERE WorldID = ?1 AND CharacterID = ?3");
+	if(Stmt == NULL){
+		LOG_ERR("Failed to prepare query");
+		return false;
+	}
+
+	if(sqlite3_bind_int(Stmt, 1, WorldID)   != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 2, AccountID) != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 3, BuddyID)   != SQLITE_OK){
+		LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
+		return false;
+	}
+
+	if(sqlite3_step(Stmt) != SQLITE_DONE){
+		LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+		return false;
+	}
+
+	return true;
+}
+
+bool DeleteBuddy(int WorldID, int AccountID, int BuddyID){
+	sqlite3_stmt *Stmt = PrepareQuery(
+			"DELETE FROM Buddies"
+			" WHERE WorldID = ?1 AND AccountID = ?2 AND BuddyID = ?3");
+	if(Stmt == NULL){
+		LOG_ERR("Failed to prepare query");
+		return false;
+	}
+
+	if(sqlite3_bind_int(Stmt, 1, WorldID)   != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 2, AccountID) != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 3, BuddyID)   != SQLITE_OK){
+		LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
+		return false;
+	}
+
+	if(sqlite3_step(Stmt) != SQLITE_DONE){
+		LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+		return false;
+	}
+
+	// NOTE(fusion): Always return true here even if there were no deleted rows
+	// to make them appear as successful deletions.
+	return true;
+}
+
 // House tables
 //==============================================================================
 bool FinishHouseAuctions(int WorldID, DynamicArray<THouseAuction> *Auctions){
@@ -689,6 +776,7 @@ bool DeleteHouses(int WorldID){
 }
 
 bool InsertHouses(int WorldID, int NumHouses, THouse *Houses){
+	ASSERT(NumHouses > 0 && Houses != NULL);
 	sqlite3_stmt *Stmt = PrepareQuery(
 			"INSERT INTO Houses (WorldID, HouseID, Name, Rent, Description,"
 				" Size, PositionX, PositionY, PositionZ, Town, GuildHouse)"
@@ -734,8 +822,8 @@ bool InsertHouses(int WorldID, int NumHouses, THouse *Houses){
 bool ExcludeFromAuctions(int WorldID, int CharacterID, int Duration, int BanishmentID){
 	// NOTE(fusion): Same as `DecrementIsOnline`.
 	sqlite3_stmt *Stmt = PrepareQuery(
-			"INSERT INTO HouseAuctionExclusions (CharacterID, Until, BanishmentID)"
-			" SELECT CharacterID, (UNIXEPOCH() + ?3), ?4 FROM Characters"
+			"INSERT INTO HouseAuctionExclusions (CharacterID, Issued, Until, BanishmentID)"
+			" SELECT ?2, UNIXEPOCH(), (UNIXEPOCH() + ?3), ?4 FROM Characters"
 				" WHERE WorldID = ?1 AND CharacterID = ?2");
 	if(Stmt == NULL){
 		LOG_ERR("Failed to prepare query");
@@ -787,8 +875,8 @@ TNamelockStatus GetNamelockStatus(int CharacterID){
 	return Status;
 }
 
-bool InsertNamelock(int CharacterID, int IPAddress,
-		int GamemasterID, const char *Reason, const char *Comment){
+bool InsertNamelock(int CharacterID, int IPAddress, int GamemasterID,
+		const char *Reason, const char *Comment){
 	ASSERT(Reason != NULL && Comment != NULL);
 	sqlite3_stmt *Stmt = PrepareQuery(
 			"INSERT INTO Namelocks (CharacterID, IPAddress, GamemasterID, Reason, Comment)"
@@ -852,17 +940,15 @@ TBanishmentStatus GetBanishmentStatus(int CharacterID){
 	return Status;
 }
 
-bool InsertBanishment(int CharacterID, int IPAddress,
-		int GamemasterID, const char *Reason, const char *Comment,
-		bool FinalWarning, int Duration, int *BanishmentID){
-	// TODO(fusion): Not sure if I like these insertions with subqueries. We might
-	// as well just get the account id before hand.
-	ASSERT(BanishmentID);
+bool InsertBanishment(int CharacterID, int IPAddress, int GamemasterID,
+		const char *Reason, const char *Comment, bool FinalWarning,
+		int Duration, int *BanishmentID){
+	ASSERT(Reason != NULL && Comment != NULL && BanishmentID != NULL);
 	sqlite3_stmt *Stmt = PrepareQuery(
 			"INSERT INTO Banishments (AccountID, IPAddress, GamemasterID,"
 				" Reason, Comment, FinalWarning, Issued, Until)"
-			" VALUES ((SELECT AccountID FROM Characters WHERE CharacterID = ?1),"
-				" ?2, ?3, ?4, ?5, ?6, UNIXEPOCH(), UNIXEPOCH() + ?7)"
+			" SELECT AccountID, ?2, ?3, ?4, ?5, ?6, UNIXEPOCH(), UNIXEPOCH() + ?7"
+				" FROM Characters WHERE CharacterID = ?1"
 			" RETURNING BanishmentID");
 	if(Stmt == NULL){
 		LOG_ERR("Failed to prepare query");
@@ -889,6 +975,192 @@ bool InsertBanishment(int CharacterID, int IPAddress,
 	return true;
 }
 
+int GetNotationCount(int CharacterID){
+	sqlite3_stmt *Stmt = PrepareQuery(
+			"SELECT COUNT(*) FROM Notations WHERE CharacterID = ?1");
+	if(Stmt == NULL){
+		LOG_ERR("Failed to prepare query");
+		return 0;
+	}
+
+	if(sqlite3_bind_int(Stmt, 1, CharacterID) != SQLITE_OK){
+		LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
+		return 0;
+	}
+
+	if(sqlite3_step(Stmt) != SQLITE_ROW){
+		LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+		return 0;
+	}
+
+	return sqlite3_column_int(Stmt, 0);
+}
+
+bool InsertNotation(int CharacterID, int IPAddress, int GamemasterID,
+		const char *Reason, const char *Comment){
+	ASSERT(Reason != NULL && Comment != NULL);
+	sqlite3_stmt *Stmt = PrepareQuery(
+			"INSERT INTO Notations (CharacterID, IPAddress,"
+				" GamemasterID, Reason, Comment)"
+			" VALUES (?1, ?2, ?3, ?4, ?5)");
+	if(Stmt == NULL){
+		LOG_ERR("Failed to prepare query");
+		return false;
+	}
+
+	if(sqlite3_bind_int(Stmt, 1, CharacterID)        != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 2, IPAddress)          != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 3, GamemasterID)       != SQLITE_OK
+	|| sqlite3_bind_text(Stmt, 4, Reason, -1, NULL)  != SQLITE_OK
+	|| sqlite3_bind_text(Stmt, 5, Comment, -1, NULL) != SQLITE_OK){
+		LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
+		return false;
+	}
+
+	if(sqlite3_step(Stmt) != SQLITE_DONE){
+		LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+		return false;
+	}
+
+	return true;
+}
+
+bool InsertIPBanishment(int CharacterID, int IPAddress, int GamemasterID,
+		const char *Reason, const char *Comment, int Duration){
+	ASSERT(Reason != NULL && Comment != NULL);
+	sqlite3_stmt *Stmt = PrepareQuery(
+			"INSERT INTO IPBanishments (CharacterID, IPAddress,"
+				" GamemasterID, Reason, Comment, Issued, Until)"
+			" VALUES (?1, ?2, ?3, ?4, ?5, UNIXEPOCH(), UNIXEPOCH() + ?6)");
+	if(Stmt == NULL){
+		LOG_ERR("Failed to prepare query");
+		return false;
+	}
+
+	if(sqlite3_bind_int(Stmt, 1, CharacterID)        != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 2, IPAddress)          != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 3, GamemasterID)       != SQLITE_OK
+	|| sqlite3_bind_text(Stmt, 4, Reason, -1, NULL)  != SQLITE_OK
+	|| sqlite3_bind_text(Stmt, 5, Comment, -1, NULL) != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 6, Duration)           != SQLITE_OK){
+		LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
+		return false;
+	}
+
+	if(sqlite3_step(Stmt) != SQLITE_DONE){
+		LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+		return false;
+	}
+
+	return true;
+}
+
+bool IsStatementReported(int WorldID, TStatement *Statement){
+	ASSERT(Statement != NULL);
+	sqlite3_stmt *Stmt = PrepareQuery(
+			"SELECT 1 FROM Statements"
+			" WHERE WorldID = ?1 AND Timestamp = ?2 AND StatementID = ?3");
+	if(Stmt == NULL){
+		LOG_ERR("Failed to prepare query");
+		return 0;
+	}
+
+	if(sqlite3_bind_int(Stmt, 1, WorldID)                != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 2, Statement->Timestamp)   != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 3, Statement->StatementID) != SQLITE_OK){
+		LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
+		return 0;
+	}
+
+	int ErrorCode = sqlite3_step(Stmt);
+	if(ErrorCode != SQLITE_ROW && ErrorCode != SQLITE_DONE){
+		LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+		return false;
+	}
+
+	return (ErrorCode == SQLITE_ROW);
+}
+
+bool InsertStatements(int WorldID, int NumStatements, TStatement *Statements){
+	// NOTE(fusion): Use the `IGNORE` conflict resolution because different
+	// reports may include the same statements for context and I assume it's
+	// not uncommon to see overlaps.
+	ASSERT(NumStatements > 0 && Statements != NULL);
+	sqlite3_stmt *Stmt = PrepareQuery(
+			"INSERT OR IGNORE INTO Statements (WorldID, Timestamp,"
+				" StatementID, CharacterID, Channel, Text)"
+			" VALUES (?1, ?2, ?3, ?4, ?5, ?6)");
+	if(Stmt == NULL){
+		LOG_ERR("Failed to prepare query");
+		return false;
+	}
+
+	if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
+		LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
+		return false;
+	}
+
+	for(int i = 0; i < NumStatements; i += 1){
+		if(Statements[i].StatementID == 0){
+			LOG_WARN("Skipping statement without id");
+			continue;
+		}
+
+		if(sqlite3_bind_int(Stmt, 2, Statements[i].Timestamp)          != SQLITE_OK
+		|| sqlite3_bind_int(Stmt, 3, Statements[i].StatementID)        != SQLITE_OK
+		|| sqlite3_bind_int(Stmt, 4, Statements[i].CharacterID)        != SQLITE_OK
+		|| sqlite3_bind_text(Stmt, 5, Statements[i].Channel, -1, NULL) != SQLITE_OK
+		|| sqlite3_bind_text(Stmt, 6, Statements[i].Text, -1, NULL)    != SQLITE_OK){
+			LOG_ERR("Failed to bind parameters for statement %d: %s",
+					Statements[i].StatementID, sqlite3_errmsg(g_Database));
+			return false;
+		}
+
+		if(sqlite3_step(Stmt) != SQLITE_DONE){
+			LOG_ERR("Failed to insert statement %d: %s",
+					Statements[i].StatementID, sqlite3_errmsg(g_Database));
+			return false;
+		}
+
+		sqlite3_reset(Stmt);
+	}
+
+	return true;
+}
+
+bool InsertReportedStatement(int WorldID, TStatement *Statement, int BanishmentID,
+		int ReporterID, const char *Reason, const char *Comment){
+	ASSERT(Statement != NULL && Reason != NULL && Comment != NULL);
+	sqlite3_stmt *Stmt = PrepareQuery(
+			"INSERT INTO ReportedStatements (WorldID, Timestamp,"
+				" StatementID, CharacterID, BanishmentID, ReporterID,"
+				" Reason, Comment)"
+			" VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)");
+	if(Stmt == NULL){
+		LOG_ERR("Failed to prepare query");
+		return false;
+	}
+
+	if(sqlite3_bind_int(Stmt, 1, WorldID)                != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 2, Statement->Timestamp)   != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 3, Statement->StatementID) != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 4, Statement->CharacterID) != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 5, BanishmentID)           != SQLITE_OK
+	|| sqlite3_bind_int(Stmt, 6, ReporterID)             != SQLITE_OK
+	|| sqlite3_bind_text(Stmt, 7, Reason, -1, NULL)      != SQLITE_OK
+	|| sqlite3_bind_text(Stmt, 8, Comment, -1, NULL)     != SQLITE_OK){
+		LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
+		return false;
+	}
+
+	if(sqlite3_step(Stmt) != SQLITE_DONE){
+		LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+		return false;
+	}
+
+	return true;
+}
+
 // Info tables
 //==============================================================================
 bool DeleteOnlineCharacters(int WorldID){
diff --git a/src/querymanager.hh b/src/querymanager.hh
index 9e54ea8..5276d2c 100644
--- a/src/querymanager.hh
+++ b/src/querymanager.hh
@@ -602,7 +602,7 @@ void ProcessSetNamelockQuery(TConnection *Connection, TReadBuffer *Buffer);
 void ProcessBanishAccountQuery(TConnection *Connection, TReadBuffer *Buffer);
 void ProcessSetNotationQuery(TConnection *Connection, TReadBuffer *Buffer);
 void ProcessReportStatementQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessBanishIpAddressQuery(TConnection *Connection, TReadBuffer *Buffer);
+void ProcessBanishIPAddressQuery(TConnection *Connection, TReadBuffer *Buffer);
 void ProcessLogCharacterDeathQuery(TConnection *Connection, TReadBuffer *Buffer);
 void ProcessAddBuddyQuery(TConnection *Connection, TReadBuffer *Buffer);
 void ProcessRemoveBuddyQuery(TConnection *Connection, TReadBuffer *Buffer);
@@ -714,6 +714,14 @@ struct TBanishmentStatus{
 	int TimesBanished;
 };
 
+struct TStatement{
+	int Timestamp;
+	int StatementID;
+	int CharacterID;
+	char Channel[30];
+	char Text[256];
+};
+
 struct TOnlineCharacter{
 	char Name[30];
 	int Level;
@@ -743,6 +751,10 @@ bool DecrementIsOnline(int WorldID, int CharacterID);
 bool ClearIsOnline(int WorldID, int *NumAffectedCharacters);
 bool GetCharacterIndexEntries(int WorldID, int MinimumCharacterID,
 		int MaxEntries, int *NumEntries, TCharacterIndexEntry *Entries);
+bool InsertCharacterDeath(int WorldID, int CharacterID, int Level,
+		int OffenderID, const char *Remark, bool Unjustified, int Timestamp);
+bool InsertBuddy(int WorldID, int AccountID, int BuddyID);
+bool DeleteBuddy(int WorldID, int AccountID, int BuddyID);
 
 // NOTE(fusion): House tables.
 bool FinishHouseAuctions(int WorldID, DynamicArray<THouseAuction> *Auctions);
@@ -761,12 +773,22 @@ bool ExcludeFromAuctions(int WorldID, int CharacterID, int Duration, int Banishm
 
 // NOTE(fusion): Banishment tables.
 TNamelockStatus GetNamelockStatus(int CharacterID);
-bool InsertNamelock(int CharacterID, int IPAddress,
-		int GamemasterID, const char *Reason, const char *Comment);
+bool InsertNamelock(int CharacterID, int IPAddress, int GamemasterID,
+		const char *Reason, const char *Comment);
 TBanishmentStatus GetBanishmentStatus(int CharacterID);
-bool InsertBanishment(int CharacterID, int IPAddress,
-		int GamemasterID, const char *Reason, const char *Comment,
-		bool FinalWarning, int Duration, int *BanishmentID);
+bool InsertBanishment(int CharacterID, int IPAddress, int GamemasterID,
+		const char *Reason, const char *Comment, bool FinalWarning,
+		int Duration, int *BanishmentID);
+int GetNotationCount(int CharacterID);
+bool InsertNotation(int CharacterID, int IPAddress, int GamemasterID,
+		const char *Reason, const char *Comment);
+bool InsertIPBanishment(int CharacterID, int IPAddress, int GamemasterID,
+		const char *Reason, const char *Comment, int Duration);
+
+bool IsStatementReported(int WorldID, TStatement *Statement);
+bool InsertStatements(int WorldID, int NumStatements, TStatement *Statements);
+bool InsertReportedStatement(int WorldID, TStatement *Statement, int BanishmentID,
+		int ReporterID, const char *Reason, const char *Comment);
 
 // NOTE(fusion): Info tables.
 bool DeleteOnlineCharacters(int WorldID);