#include "aps_include"

////////////
// GLOBAL //
////////////

// Returns data from a MySQL table, using an integer as the key
// sTable = Name of the table to retrieve data from
// sColumn = Name of the column to retrieve data from
// iRow = Row number to retrieve data from
// sSchema = Set this if the information you're retrieving is in a different schema than default
string GetMySQLData(string sTable, string sColumn, int iRow, string sCustomKeyName = "ID", string sSchema = "");
// Returns data from a MySQL table, using a string as the key
// sTable = Name of the table to retrieve data from
// sColumn = Name of the column to retrieve data from
// sKey = String key to retrieve data from
// sSchema = Set this if the information you're retrieving is in a different schema than default
string GetMySQLDataKeyString(string sTable, string sColumn, string sKey, string sCustomKeyName = "Resref", string sSchema = "");
// Sets a field to sData
// sTable = Name of the table
// sColumn = Name of the column
// iRow = ID number of the row
// sData = The value we're setting the field to
// sCustomKeyName = Name of the key column. Default is ID.
// sSchema = Set this if the information you're inserting/updating is in a different schema than default
void SetMySQLData(string sTable, string sColumn, int iRow, string sData, string sCustomKeyName = "ID", string sSchema = "");
// Stores an object to a BLOB field in a table
// sTable = Name of the table
// sColumn = Name of the column
// iRow = ID number of the row
// oObject = The object we're storing in the field
// sCustomKeyName = Name of the key column. Default is ID.
// sSchema = Set this if the information you're inserting/updating is in a different schema than default
void StoreMySQLObject(string sTable, string sColumn, int iRow, object oObject, string sCustomKeyName = "ID", string sSchema = "");

// Stores an object to a BLOB field in a table, using a string as the key
// sTable = Name of the table
// sColumn = Name of the column
// iRow = ID number of the row
// oObject = The object we're storing in the field
// sCustomKeyName = Name of the key column. Default is ID.
// sSchema = Set this if the information you're inserting/updating is in a different schema than default
void StoreMySQLObjectKeyString(string sTable, string sColumn, string sKey, object oObject, string sCustomKeyName = "Resref", string sSchema = "");

// Retrieves an object from a BLOB field in a table.
// sTable = Name of the table
// sColumn = Name of the column
// iRow = ID number of the row
// sCustomKeyName = Name of the key column. Default is ID.
// sSchema = Set this if the information you're retrieving is in a different schema than default
// oOwner = The object retrieved will be created on this object's inventory, if possible.
object RetrieveMySQLObject(string sTable, string sColumn, int iRow, string sCustomKeyName = "ID", string sSchema = "", object oOwner = OBJECT_INVALID);

// Retrieves an object from a BLOB field in a table, using a string as the key
// sTable = Name of the table
// sColumn = Name of the column
// iRow = ID number of the row
// sCustomKeyName = Name of the key column. Default is ID.
// sSchema = Set this if the information you're retrieving is in a different schema than default
// oOwner = The object retrieved will be created on this object's inventory, if possible.
object RetrieveMySQLObjectKeyString(string sTable, string sColumn, string sKey, string sCustomKeyName = "Resref", string sSchema = "", object oOwner = OBJECT_INVALID);

// Sets a particular field to NULL. Useful for removing specific pieces of data. If the column doesn't allow null data, do not use this.
// sTable = Name of the table
// sColumn = Name of the column
// iRow = ID number of the row
// sCustomKeyName = Name of the key column. Default is ID.
// sSchema = Set this if the information you're retrieving is in a different schema than default
void NullMySQLField(string sTable, string sColumn, int iRow, string sCustomKeyName = "ID", string sSchema = "");

// Returns TRUE if table sTable exists.
// Returns FALSE if table sTable does not exist.
int DoesMySQLTableExist(string sTable);

string GetMySQLData(string sTable, string sColumn, int iRow, string sCustomKeyName = "ID", string sSchema = "")
{
    if(sSchema != "")
    {
        sTable = sSchema + "." + sTable;
    }
    string sSQL = "SELECT `" + sColumn + "` FROM " + sTable + " WHERE " + sCustomKeyName + "=" + IntToString(iRow);
    SQLExecDirect(sSQL);
    SQLFetch();
    return SQLGetData(1);
}
string GetMySQLDataKeyString(string sTable, string sColumn, string sKey, string sCustomKeyName = "Resref", string sSchema = "")
{
    if(sSchema != "")
    {
        sTable = sSchema + "." + sTable;
    }

    string sSQL = "SELECT " + sColumn + " FROM " + sTable + " WHERE " + sCustomKeyName + "='" + sKey + "';";
    SQLExecDirect(sSQL);
    SQLFetch();
    return SQLGetData(1);
}
void SetMySQLData(string sTable, string sColumn, int iRow, string sData, string sCustomKeyName = "ID", string sSchema = "")
{
    if(sSchema != "")
    {
        sTable = sSchema + "." + sTable;
    }

    string sSQL = "INSERT INTO " + sTable + "(" + sCustomKeyName + "," + sColumn + ") VALUES (" + IntToString(iRow)+ ",'" + sData + "') ON DUPLICATE KEY UPDATE " + sColumn + "='" + sData + "'";
    SQLExecDirect(sSQL);
}

void StoreMySQLObject(string sTable, string sColumn, int iRow, object oObject, string sCustomKeyName = "ID", string sSchema = "")
{
    if(sSchema != "")
    {
        sTable = sSchema + "." + sTable;
    }

    string sSQL = "UPDATE " + sTable + " SET " + sColumn + "=%s WHERE " + sCustomKeyName + "=" + IntToString(iRow) + ";";
    SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
    StoreCampaignObject ("NWNX", "-", oObject);
}

void StoreMySQLObjectKeyString(string sTable, string sColumn, string sKey, object oObject, string sCustomKeyName = "Resref", string sSchema = "")
{
    if(sSchema != "")
    {
        sTable = sSchema + "." + sTable;
    }

    string sSQL = "UPDATE " + sTable + " SET " + sColumn + "=%s WHERE " + sCustomKeyName + "='" + sKey + "';";
    SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
    StoreCampaignObject ("NWNX", "-", oObject);
}

object RetrieveMySQLObject(string sTable, string sColumn, int iRow, string sCustomKeyName = "Resref", string sSchema = "", object oOwner = OBJECT_INVALID)
{
    if(sSchema != "")
    {
        sTable = sSchema + "." + sTable;
    }

    string sSQL = "SELECT " + sColumn + " FROM " + sTable +  " WHERE " + sCustomKeyName + "=" + IntToString(iRow) + ";";
    SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
    return RetrieveCampaignObject ("NWNX", "-", GetLocation(oOwner), oOwner);
}

object RetrieveMySQLObjectKeyString(string sTable, string sColumn, string sKey, string sCustomKeyName = "ID", string sSchema = "", object oOwner = OBJECT_INVALID)
{
    if(sSchema != "")
    {
        sTable = sSchema + "." + sTable;
    }

    string sSQL = "SELECT " + sColumn + " FROM " + sTable +  " WHERE " + sCustomKeyName + "='" + sKey + "';";
    SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
    return RetrieveCampaignObject ("NWNX", "-", GetLocation(oOwner), oOwner);
}

void NullMySQLField(string sTable, string sColumn, int iRow, string sCustomKeyName = "ID", string sSchema = "")
{
    if(sSchema != "")
    {
        sTable = sSchema + "." + sTable;
    }

    string sSQL = "UPDATE " + sTable + " SET " + sColumn + " = NULL WHERE " + sCustomKeyName + "=" + IntToString(iRow);
    SQLExecDirect(sSQL);
}

int DoesMySQLTableExist(string sTable)
{
    string sSQL = "SHOW TABLES LIKE '" + sTable + "'";

    SQLExecDirect(sSQL);

    if(SQLFetch() == SQL_SUCCESS)
    {
        sSQL = SQLGetData(1);
        if(sSQL != "")
        {
            return TRUE;
        }
        else
        {
            return FALSE;
        }
    }
    else
    {
        return FALSE;
    }
}

////////////////////////
// ADVANCEMENT SYSTEM //
////////////////////////

// Lists the max number of upgrades for each option
const string ADV_MAX_UPGRADES = "adv_max_upgrades";

// Lists all equipment items and equip requirements
const string ADV_EQUIPMENT = "adv_equipment";

// Lists the amount of EXP needed for each level
const string ADV_EXP_CHART = "adv_exp_chart";

/////////////////////////////
// PC AUTHORIZATION SYSTEM //
/////////////////////////////

// Links PC accounts with CD keys
const string AUTHORIZATION_TABLE = "pc_validation";

////////////////////////////
// FIREARM UPGRADE SYSTEM //
////////////////////////////

// Name of the lookup table used for getting item property IDs.
// Also used by the durability system to get item durability property IDs.
const string FUS_MYSQL_LOOKUP = "fus_lookup";

///////////////
// KEY ITEMS //
///////////////

const string KEYITEM_MYSQL_TABLE = "key_items";

//////////////////
// QUEST SYSTEM //
//////////////////

// Name of the MySQL schema which stores quest information
const string QST_SCHEMA_NAME = "quests";
// Name of the MySQL table which stores all quest information
const string QST_TABLE_NAME = "quest_info";

////////////////////
// HOUSING SYSTEM //
////////////////////

// Name of the table which tracks all houses currently being rented out
const string RHS_TABLE_HOUSES = "rhs_houses";

// Name of the table which tracks all of the different house type
const string RHS_TABLE_HOUSE_TYPES = "rhs_house_types";

// Name of the lookup table which tracks all of the different furniture categories
const string RHS_TABLE_FURNITURE_CATEGORIES = "rhs_furniture_categories";

// Name of the lookup table which tracks all of the different furniture types
const string RHS_TABLE_FURNITURE_TYPES = "rhs_furniture_types";

// Prefix of the table which tracks an individual house's furniture
// The ID number of the owner's character is added to the end of each table
const string RHS_TABLE_FURNITURE = "rhs_houses_furniture_";

// Prefix of the table which tracks an individual house's friends list
// The ID number of the owner's character is added to the end of each table
const string RHS_TABLE_FRIENDS = "rhs_houses_friends_";

// Name of the table used as a template for all furniture tables
const string RHS_TABLE_FURNITURE_TEMPLATE = RHS_TABLE_FURNITURE + "template";

// Name of the table used as a template for all friends tables
const string RHS_TABLE_FRIENDS_TEMPLATE = RHS_TABLE_FRIENDS + "template";



////////////////////////////
// PLAYER INFO COLLECTION //
////////////////////////////

// Name of the table which tracks player info including CD key, account name and IP address
const string PLAYER_INFO_TABLE = "player_info";

// Name of the table which tracks all character names
const string CHARACTER_INFO_TABLE = "character_info";

// Error checking
//void main(){}