/*

    MYSQL Example Script
    Created by Calgon / FreddoX.

    - Credits to StrickenKid (MYSQL PLUGIN)
    - Credits to Seif_ (new.pwn - colours)
    - Credits to... FreddoX, for making this example

    There is an example of how to obtain data (Locations system) into arrays, etc.

    Enjoy, do what you wish with this script - It was made to mess around with!

*/

#include <a_samp>
#include <mysql>

// Colours
#define WHITE       0xFFFFFFFF
#define LIGHTBLUE   0x00C2ECFF
#define GREY        0xCECECEFF

// Misc
#define MAX_LOCATIONS       3
#define VERSION             "MySQL 6.9"

// Connection
#define HOST_ADDR   "localhost"
#define HOST_USER   ""
#define HOST_DATA   ""
#define HOST_PASS   ""

enum PlayerEnum
{
    pPassword[ 30 ],
    pAdminLevel,
    pAuth,
    pGroup,
    pLastWorld,
    pLastInterior,
    pGender,
    pAge,
    pBankMoney,
    pLastSkin,
    pTutorial,
    pDatabaseID,
    Float: pLastPosX,
    Float: pLastPosY,
    Float: pLastPosZ,
}

enum LocationsEnum
{
    Float: lPositionX,
    Float: lPositionY,
    Float: lPositionZ,
    lPositionWorld,
    lPositionInterior,
}

new PlayerStatistics[ MAX_PLAYERS ][ PlayerEnum ];
new Locations[ MAX_LOCATIONS ][ LocationsEnum ];


main()
{
}

stock ResetPlayerVariables( playerid )
{
    PlayerStatistics[ playerid ][ pAuth ] = 0;
    PlayerStatistics[ playerid ][ pAdminLevel ] = 0;
    PlayerStatistics[ playerid ][ pGroup ] = 0;
    PlayerStatistics[ playerid ][ pLastSkin ] = 0;
    PlayerStatistics[ playerid ][ pLastWorld ] = 0;
    PlayerStatistics[ playerid ][ pAge ] = 0;
    PlayerStatistics[ playerid ][ pLastInterior ] = 0;
    PlayerStatistics[ playerid ][ pTutorial ] = 0;
    PlayerStatistics[ playerid ][ pDatabaseID ] = 0;
    PlayerStatistics[ playerid ][ pBankMoney ] = 0;
    PlayerStatistics[ playerid ][ pGender ] = 0;
    PlayerStatistics[ playerid ][ pLastPosX ] = 0.00;
    PlayerStatistics[ playerid ][ pLastPosY ] = 0.00;
    PlayerStatistics[ playerid ][ pLastPosZ ] = 0.00;
    return 1;
}

stock InitLocations()
{
    new QueryString[ 128 ], i = 1;

    mysql_query( "SELECT `PositionID`, `PositionName`,`PositionX`, `PositionY`, `PositionZ`, `PositionInterior`, `PositionWorld` FROM `Positions`" );
    mysql_store_result();

    if( mysql_num_rows() >= 1 )
    {
        while( mysql_fetch_row_data() )
        {
            mysql_fetch_field( "PositionX", QueryString );
            Locations[ i ][ lPositionX ] = floatstr( QueryString );

            mysql_fetch_field( "PositionY", QueryString );
            Locations[ i ][ lPositionY ] = floatstr( QueryString );

            mysql_fetch_field( "PositionZ", QueryString );
            Locations[ i ][ lPositionZ ] = floatstr( QueryString );

            mysql_fetch_field( "PositionInterior", QueryString );
            Locations[ i ][ lPositionInterior ] = strval( QueryString );

            mysql_fetch_field( "PositionWorld", QueryString );
            Locations[ i ][ lPositionWorld ] = strval( QueryString );

            i++;
        }
    }

    mysql_free_result();

    return 1;
}

stock InitPlayerConnection( playerid )
{
    ResetPlayerVariables( playerid );

    new Query[ 128 ], Name[ MAX_PLAYER_NAME ], EscapedName[ MAX_PLAYER_NAME ];

    GetPlayerName( playerid, Name, sizeof( Name ) );

    mysql_real_escape_string( Name, EscapedName );
    #pragma unused Name

    format( Query, sizeof( Query ), "SELECT `UserID` FROM `Accounts` WHERE `Username` = '%s'", EscapedName );
    mysql_query( Query );
    mysql_store_result();

    if( mysql_num_rows() >= 1 )
    {
        if( mysql_num_rows() >= 2 )
        {
            mysql_free_result();
            SendClientMessage( playerid, WHITE, "There seems to be duplicates of your account, please contact a high-level admin." );
            Kick( playerid );
        }
        else
        {
            PlayerStatistics[ playerid ][ pDatabaseID ] = mysql_fetch_int();
            mysql_free_result();

            ShowPlayerDialog( playerid, 1, DIALOG_STYLE_INPUT, "Login / Authentication Process", "Hello there!\n\nPlease enter your password to authenticate.", "Login", "Cancel" );
            SendClientMessage( playerid, WHITE, "Your account exists. Please enter your password to proceed." );
        }
    }
    else
    {
        mysql_free_result();
        SendClientMessage( playerid, WHITE, "Your account does not yet exist. Enter a password to register one!" );
        ShowPlayerDialog( playerid, 2, DIALOG_STYLE_INPUT, "Registration Process", "Hello there!\n\nPlease enter a password to register.", "Register", "Cancel" );
    }
}

public OnPlayerDisconnect( playerid, reason )
{
    SavePlayerAccount( playerid );
    return 1;
}

public OnGameModeInit()
{
    UsePlayerPedAnims();

    SetGameModeText( VERSION );

    new ip[ 32 ];

    GetServerVarAsString( "bind", ip, sizeof( ip ) );

    mysql_connect( HOST_ADDR, HOST_USER, HOST_PASS, HOST_DATA );

    InitLocations();

    return 1;
}

public OnPlayerConnect( playerid )
{
    InitPlayerConnection( playerid );
    return 1;
}

public OnGameModeExit()
{
    mysql_close();
    return 1;
}
public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    switch( dialogid )
    {
        case 1:
        {
            GetFromAccount( PlayerStatistics[ playerid ][ pDatabaseID ], "Password", PlayerStatistics[ playerid ][ pPassword ] );

            if( strcmp( inputtext, PlayerStatistics[ playerid ][ pPassword ], true ) == 0 )
            {
                new String[ 128 ], Name[ MAX_PLAYER_NAME ];

                GetPlayerName( playerid, Name, sizeof( Name ) );

                format( String, sizeof( String ), "Thank you for logging in, %s.", Name );
                SendClientMessage( playerid, WHITE, String );

                PlayerStatistics[ playerid ][ pAuth ] = 1;
                LoadAccountVariables( playerid );
            }
            else
            {
                SendClientMessage( playerid, WHITE, "Incorrect password. Please try again!" );
                ShowPlayerDialog( playerid, 1, DIALOG_STYLE_INPUT, "Login / Authentication Process", "Hello there!\n\nPlease enter your password to authenticate.", "Login", "Cancel" );
            }
        }

        case 2:
        {
            if( strlen( inputtext ) >= 30 )
            {
                ShowPlayerDialog( playerid, 2, DIALOG_STYLE_INPUT, "Registration Process", "Hello there!\n\nPlease enter a password to register.", "Authenticate", "Cancel" );
                SendClientMessage( playerid, WHITE, "You must use a shorter password - has to be less than 30 characters." );
            }
            else
            {
                if( strlen( inputtext ) >= 1 )
                {
                    new EscapedPassword[ 30 ], Query[ 128 ], Name[ MAX_PLAYER_NAME ], EscapedName[ MAX_PLAYER_NAME ];

                    GetPlayerName( playerid, Name, sizeof( Name ) );

                    mysql_real_escape_string( Name, EscapedName );
                    mysql_real_escape_string( inputtext, EscapedPassword );

                    #pragma unused Name

                    format( Query, sizeof( Query ), "INSERT INTO `Accounts` (Username, Password) VALUES('%s', '%s')", EscapedName, EscapedPassword );
                    mysql_query( Query );

                    SendClientMessage( playerid, WHITE, "Now, please login with the password you've just entered to proceed." );
                    InitPlayerConnection( playerid );
                }
                else
                {
                    ShowPlayerDialog( playerid, 2, DIALOG_STYLE_INPUT, "Registration Process", "Hello there!\n\nPlease enter a password to register.", "Authenticate", "Cancel" );
                    SendClientMessage( playerid, WHITE, "Your password must be longer than 1 character..." );
                }
            }
        }

        case 3:
        {
            switch( listitem )
            {
                case 0:
                {
                    SendClientMessage( playerid, GREY, "--------------------------------------------------------------------------------------------------------------------------------" );
                    SendClientMessage( playerid, WHITE, "Thank you for setting your Gender - You are a Male." );
                    SendClientMessage( playerid, WHITE, "Now, please tell us how old your character is supposed to be? There is a set limit for roleplaying your character." );
                    PlayerStatistics[ playerid ][ pGender ] = 1;
                    ShowPlayerDialog( playerid, 4, DIALOG_STYLE_LIST, "How old is your character?", "18\n19\n20\n21\n22\n23\n24\n25\n26\n27\n28\n29\n30\n30\n31\n32\n33\n34\n35\n36\n37\n38\n39\n40\n", "Select", "Cancel" );
                    SendClientMessage( playerid, GREY, "--------------------------------------------------------------------------------------------------------------------------------" );
                }
                case 1:
                {
                    SendClientMessage( playerid, GREY, "--------------------------------------------------------------------------------------------------------------------------------" );
                    SendClientMessage( playerid, WHITE, "Thank you for setting your Gender - You are a Female." );
                    SendClientMessage( playerid, WHITE, "Now, please tell us how old your character is supposed to be? There is a set limit for roleplaying your character." );
                    PlayerStatistics[ playerid ][ pGender ] = 2;
                    ShowPlayerDialog( playerid, 4, DIALOG_STYLE_LIST, "How old is your character?", "18\n19\n20\n21\n22\n23\n24\n25\n26\n27\n28\n29\n30\n30\n31\n32\n33\n34\n35\n36\n37\n38\n39\n40\n", "Select", "Cancel" );
                    SendClientMessage( playerid, GREY, "--------------------------------------------------------------------------------------------------------------------------------" );
                }
            }
        }

        case 4:
        {
            new String[ 128 ];

            PlayerStatistics[ playerid ][ pAge ] = 17+listitem;

            format( String, sizeof( String ), "You have set your character's age to %d - awesome!", PlayerStatistics[ playerid ][ pAge ] );
            SendClientMessage( playerid, WHITE, String );

            SendClientMessage( playerid, WHITE, "Right, now tell us... Are you new to the server?" );
            ShowPlayerDialog( playerid, 5, DIALOG_STYLE_LIST, "Are you new to our server?", "Yes, I'm new here.\nNo, I'm not new here.", "Select", "Cancel" );

            SendClientMessage( playerid, GREY, "--------------------------------------------------------------------------------------------------------------------------------" );
        }

        case 5:
        {
            switch( listitem )
            {
                case 0:
                {
                }
                case 1:
                {
                    PlayerStatistics[ playerid ][ pTutorial ] = 0;

                    SendClientMessage( playerid, WHITE, "Okay - You're ready to go! Thanks for registering, we hope you have fun on the server!" );
                    SendClientMessage( playerid, GREY, "--------------------------------------------------------------------------------------------------------------------------------" );

                    TogglePlayerSpectating( playerid, false );
                    SetSpawnInfo( playerid, 0, PlayerStatistics[ playerid ][ pLastSkin ], Locations[ 1 ][ lPositionX ], Locations[ 1 ][ lPositionY ], Locations[ 1 ][ lPositionZ ], 90, 0, 0, 0, 0, 0, 0 );
                    SpawnPlayer( playerid );
                }
            }
        }
    }
}

stock SavePlayerAccount( playerid )
{
    new String[ 255 ];

    if( PlayerStatistics[ playerid ][ pAuth ] == 1 )
    {
        format( String, sizeof( String ), "UPDATE `Accounts` SET `Age` = '%d', `Password` = '%s', `AdminLevel` = '%d', `Tutorial` = '%d', `Group` = '%d', `Gender` = '%d' WHERE `UserID` = '%d'", PlayerStatistics[ playerid ][ pAge ], PlayerStatistics[ playerid ][ pPassword ], PlayerStatistics[ playerid ][ pAdminLevel ], PlayerStatistics[ playerid ][ pTutorial ], PlayerStatistics[ playerid ][ pGroup ], PlayerStatistics[ playerid ][ pGender ], PlayerStatistics[ playerid ][ pDatabaseID ] );
        mysql_query( String );

        GetPlayerPos( playerid, PlayerStatistics[ playerid ][ pLastPosX ], PlayerStatistics[ playerid ][ pLastPosY ], PlayerStatistics[ playerid ][ pLastPosZ ] );
        format( String, sizeof( String ), "UPDATE `Accounts` SET `LastPosX` = '%f', `LastPosY` = '%f', `LastPosZ` = '%f', `LastInterior` = '%d', `LastWorld` = '%d' WHERE `UserID` = '%d'", PlayerStatistics[ playerid ][ pLastPosX ], PlayerStatistics[ playerid ][ pLastPosY ], PlayerStatistics[ playerid ][ pLastPosZ ], GetPlayerInterior( playerid ), GetPlayerVirtualWorld( playerid ), PlayerStatistics[ playerid ][ pDatabaseID ] );
        mysql_query( String );

        format( String, sizeof( String ), "UPDATE `Accounts` SET `BankMoney` = '%d' WHERE `UserID` = '%d'", PlayerStatistics[ playerid ][ pBankMoney ], PlayerStatistics[ playerid ][ pDatabaseID ] );
        mysql_query( String );
    }
    return 1;
}

stock LoadAccountVariables( playerid )
{
    if( IsPlayerConnected( playerid ) )
    {
        new DataString[ 128 ], Query[ 128 ];

        format( Query, sizeof( Query ), "SELECT * FROM `Accounts` WHERE `UserID` = '%d'", PlayerStatistics[ playerid ][ pDatabaseID ] );
        mysql_query( Query );
        mysql_store_result();

        mysql_fetch_field( "AdminLevel", DataString );
        PlayerStatistics[ playerid ][ pAdminLevel ] = strval( DataString );

        mysql_fetch_field( "Group", DataString );
        PlayerStatistics[ playerid ][ pGroup ] = strval( DataString );

        mysql_fetch_field( "LastPosX", DataString );
        PlayerStatistics[ playerid ][ pLastPosX ] = floatstr( DataString );

        mysql_fetch_field( "LastPosY", DataString );
        PlayerStatistics[ playerid ][ pLastPosY ] = floatstr( DataString );

        mysql_fetch_field( "LastPosZ", DataString );
        PlayerStatistics[ playerid ][ pLastPosZ ] = floatstr( DataString );

        mysql_fetch_field( "LastWorld", DataString );
        PlayerStatistics[ playerid ][ pLastSkin ] = strval( DataString );

        mysql_fetch_field( "LastInterior", DataString );
        PlayerStatistics[ playerid ][ pLastInterior ] = strval( DataString );

        mysql_fetch_field( "Tutorial", DataString );
        PlayerStatistics[ playerid ][ pTutorial ] = strval( DataString );

        mysql_fetch_field( "Gender", DataString );
        PlayerStatistics[ playerid ][ pGender ] = strval( DataString );

        mysql_fetch_field( "BankMoney", DataString );
        PlayerStatistics[ playerid ][ pBankMoney ] = strval( DataString );

        if( PlayerStatistics[ playerid ][ pTutorial] >= 1 )
        {
            ShowPlayerDialog( playerid, 3, DIALOG_STYLE_LIST, "What is your Gender?", "Male\nGender", "Select", "Cancel" );
            TogglePlayerSpectating( playerid, true );
            SendClientMessage( playerid, LIGHTBLUE, "Hello there! You're new to our server." );
        }
        else
        {
            SetSpawnInfo( playerid, 0, PlayerStatistics[ playerid ][ pLastSkin ], PlayerStatistics[ playerid ][ pLastPosX ], PlayerStatistics[ playerid ][ pLastPosY ], PlayerStatistics[ playerid ][ pLastPosZ ], 0, 0, 0, 0, 0, 0, 0 );
            SpawnPlayer( playerid );
        }

        mysql_free_result();
    }
    else
    {
        printf( "[error] LoadAccountVariables() was called, but to a non-connected ID.", playerid );
    }
}

stock GetFromAccount( dbid, obtaining[], holdingvar[] )
{
    new Query[ 128 ];
    format( Query, sizeof( Query ), "SELECT `%s` FROM `Accounts` WHERE `UserID` = '%d'", obtaining, dbid );
    mysql_query( Query );
    mysql_store_result();

    if( mysql_fetch_row( holdingvar ) == 1 )
    {
        mysql_free_result();
    }

    return 1;
}