/*

CREATE TABLE sf_player(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY( id ),
steamid VARCHAR( 30 ),
name VARCHAR( 255 ),
flags VARCHAR( 30 ),
title VARCHAR( 50 ),
lastip VARCHAR( 30 ),
lastlogin DATETIME,
lastname VARCHAR( 100 ) );

CREATE TABLE sf_bans(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY( id ),
steamid VARCHAR( 30 ),
name VARCHAR( 255 ),
lastip VARCHAR( 30 ),
adname VARCHAR( 255 ),
adsteamid VARCHAR( 50 ),
adip VARCHAR( 30 ),
reason VARCHAR( 255 ),
bandate VARCHAR( 50 ),
time VARCHAR( 50 ),
ostime VARCHAR( 50 ) );

CREATE TABLE sf_logs(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY( id ),
steamid VARCHAR( 30 ),
type INTEGER,
name VARCHAR( 255 ),
command VARCHAR( 255 ),
arguments VARCHAR( 255 ),
date DATETIME );

CREATE TABLE sf_time(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY( id ),
steamid VARCHAR( 30 ),
name VARCHAR( 255 ),
time TIME );

"type" field
1 - command log
2 - bans log
3 - debug log

*/
/*

    SparkFlare
    MySQL.

        Creates MySQL functions and such.

*/

// Means MySQL isn't loaded.
SF.MySQL.Load = false;

/*
function SF.MySQL.ImportData()

    sql.Query( "DROP TABLE db_details;" );
    sql.Query( "CREATE TABLE IF NOT EXISTS db_details( 'id' INTEGER NOT NULL, 'addr' TEXT NOT NULL, 'data' TEXT NOT NULL, 'user' TEXT NOT NULL, 'pass' TEXT NOT NULL, PRIMARY KEY( 'id' ) );" );

    if ( !sql.TableExists( "db_details" ) ) then

        SF.Utility.Debug( "Table doesn't exist!" );
        return;

    end

    SF.Utility.Debug( "Created table." );

    //sql.Query( "INSERT INTO db_details ( `id`, `addr`, `data`, `user`, `pass` ) VALUES ( '1', 'hostname', 'database', 'username', 'password' );" );

    SF.Utility.Debug( "Inserted details." );

end
SF.MySQL.ImportData();*/

function SF.MySQL.Loadup()

    // Try to select the data.
    local Addr = sql.QueryValue( "SELECT addr FROM db_details WHERE id = 1;" );
    local Data = sql.QueryValue( "SELECT data FROM db_details WHERE id = 1;" );
    local User = sql.QueryValue( "SELECT user FROM db_details WHERE id = 1;" );
    local Pass = sql.QueryValue( "SELECT pass FROM db_details WHERE id = 1;" );

    // Check the data.
    if ( !Data || !User || !Pass || !Addr ) then

        SF.Utility.Debug( "Warning! Corrupt MySQL data!", "1" );
        return false;

    end

    // Set the address, data, user and password.
    SF.MySQL.Addr = Addr;
    SF.MySQL.Data = Data;
    SF.MySQL.User = User;
    SF.MySQL.Pass = Pass;

    // Set the mysql to loaded.
    SF.MySQL.Load = true;

    return true;

end

// Connect to SQL.
function SF.MySQL.Connect()

    // Check that we're loaded.
    if ( !SF.MySQL.Load ) then

        SF.Utility.Debug( "MySQL isn't loaded. Loading." );

        // Attempt to load the data.
        local Load = SF.MySQL.Loadup();
        if ( !Load ) then

            SF.Utility.Debug( "Couldn't load MySQL.", "1" );
            return;

        end

    end

    // Debug these details.
    //SF.Utility.Debug( "ADDR: " .. SF.MySQL.Addr .. " || USER: " .. SF.MySQL.User .. " || PASS: " .. SF.MySQL.Pass .. " || DATA: " .. SF.MySQL.Data .. "!" );

    // Connect.
    SF.Connection, SF.Error = mysql.connect( SF.MySQL.Addr, SF.MySQL.User, SF.MySQL.Pass, SF.MySQL.Data );

    // Check that we're connected.
    if ( SF.Connection == 0 ) then

        // Debug it erroring.
        SF.Utility.Debug( "64: MYSQL ERROR: " .. SF.Error, "1" );
        return;

    end

    // Debug loading correctly.
    SF.Utility.Debug( "MySQL has connected successfully!" );

end

// Disconnect from MySQL.
function SF.MySQL.Disconnect()

    // IF we're connected, disconnect.
    if ( SF.Connection ) then

        SF.Disconnect, Error = mysql.disconnect( SF.Connection );

        // Check that disconnection was a success.
        if ( !SF.Disconnect ) then

            // If it wasn't then debug it.
            SF.Utility.Debug( "87: MYSQL ERROR: " .. Error, "1" );

        end

        // Clear the connect function.
        SF.Connection = nil;

    end

end

// Refresh the MySQL connection.
function SF.MySQL.Refresh()

    // Disconnect.
    SF.MySQL.Disconnect();

    // Connect.
    SF.MySQL.Connect();

end

// Connect.
SF.MySQL.Connect();

// Create a timer to refresh the connection every two minutes.
timer.Create( "SF.MySQL.Refresh", 120, 0, SF.MySQL.Refresh );

/*

    Player MySQL.

*/

// New player!
function SF.MySQL.Player.New( pl )

    // Check that we're connected.
    if ( SF.Connection ) then

        // : 1000-01-01 00:00:00
        local Date = SF.Utility.Year .. os.date( "-%m-%d %H:%M:%S" );

        // Clear the values.
        local New;
        local Success;
        local Error;

        // Create the new player.
        New, Success, Error = mysql.query( SF.Connection, "INSERT INTO sf_player ( `steamid`, `name`, `flags`, `title`, `lastip`, `lastlogin`, `lastname` )  VALUES( '" .. pl:SteamID() .. "', '" .. mysql.escape( SF.Connection, pl:Name() ) .. "', 'A', 'Newbie', '" .. pl:IPAddress() .. "', '" .. Date .. "', '" .. mysql.escape( SF.Connection, pl:Name() ) .. "' )" );

        // Check that it was a success.
        if ( !Success ) then

            // IF it wasn't display failed and return.
            SF.Utility.Debug( "73: MYSQL ERROR: " .. Error, "1" );
            return;

        end

        if ( New ) then

            // Create the player (debug it, at least).
            SF.Utility.Debug( "Created player: " .. pl:SteamID() .. "!" );

        end

        // Load the player.
        SF.MySQL.Player.Load( pl );

    end

end

// Load a player.
function SF.MySQL.Player.Load( pl )

    // If we're not connected then connect; if we're still not connected then return.
    if ( !SF.Connection ) then

        // Refresh.
        SF.MySQL.Refresh();

        // Recheck connection.
        if ( !SF.Connection ) then

            // Return if can't connect.
            return;

        end

    end

    // Clear MyData, success, and error.
    local MyData;
    local Success;
    local Error;

    // Get the players data.
    MyData, Success, Error = mysql.query( SF.Connection, "SELECT * FROM sf_player WHERE steamid = '" .. pl:SteamID() .. "'" );

    // Check that it was a success.
    if ( !Success ) then

        // IF it wasn't display failed and return.
        SF.Utility.Debug( "1: MYSQL ERROR: " .. Error, "1" );
        return;

    end

    // Count the amount of tables in the data.
    if ( table.Count( MyData ) > 0 ) then

        // Set the date.
        // YYYY:MM:DD HH:MM:SS
        local Date = SF.Utility.Year .. os.date( "-%m-%d %H:%M:%S" );

        // If there's already tables.
        NewTime, Success, Error = mysql.query( SF.Connection, "UPDATE sf_player SET lastlogin = '" .. Date .. "' WHERE steamid = '" .. pl:SteamID() .. "'" );

        // Check that it was a success.
        if ( !Success ) then

            // IF it wasn't display failed and return.
            SF.Utility.Debug( "2: MYSQL ERROR: " .. Error, "1" );
            return;

        end

        NewName, Success, Error = mysql.query( SF.Connection, "UPDATE sf_player SET name = '" .. mysql.escape( SF.Connection, pl:Name() ) .. "' WHERE steamid = '" .. pl:SteamID() .. "'" );

        // Check that it was a success.
        if ( !Success ) then

            // IF it wasn't display failed and return.
            SF.Utility.Debug( "3: MYSQL ERROR: " .. Error, "1" );
            return;

        end

        NewAddr, Success, Error = mysql.query( SF.Connection, "UPDATE sf_player SET lastip = '" .. pl:IPAddress() .. "' WHERE steamid = '" .. pl:SteamID() .. "'" );

        // Check that it was a success.
        if ( !Success ) then

            // IF it wasn't display failed and return.
            SF.Utility.Debug( "4: MYSQL ERROR: " .. Error, "1" );
            return;

        end

        // Check the existing name.
        LastName, Success, Error = mysql.query( SF.Connection, "SELECT lastname FROM sf_player WHERE steamid = '" .. pl:SteamID() .. "'" );

        if ( !Success ) then

            // Display error.
            SF.Utility.Debug( "24924: MYQL ERROR: " .. Error, "1" );
            return;

        end

        // If the player has a new name then update the last name.
        if ( LastName ~= pl:Name() ) then

            // Update the last name.
            UpdtName, Success, Error = mysql.query( SF.Connection, "UPDATE sf_player SET lastname = '" .. mysql.escape( SF.Connection, pl:Name() ) .. "' WHERE steamid = '" .. pl:SteamID() .. "'" );

            // Check for success.
            if ( !Success ) then

                // Error.
                SF.Utility.Debug( "49239: MYSQL ERROR: " .. Error, "1" );
                return;

            end

        end

        /*

            Check to see if the blasted user is banned!
            Then check to see if his ban has run out.
            Oh god, this is getting complicated.

        */

        // Get data fpr the user.
        UserBan, Success, Error = mysql.query( SF.Connection, "SELECT * FROM sf_bans WHERE steamid = '" .. pl:SteamID() .. "'" );

        // Search for mysql errors.
        if ( !Success ) then

            // Debug.
            SF.Utility.Debug( "49244: MYSQL ERROR: " .. Error, "1" );
            return;

        end

        // Make sure they actually have a ban section.
        if ( table.Count( UserBan ) > 0 ) then

            // Get the ostime they were banned.
            local OSTime    = UserBan[ 1 ][ 11 ];
            // Time they were banned for.
            local BanTime   = UserBan[ 1 ][ 10 ];

            // See if BanTime is empty.
            if ( !BanTime || BanTime == "" || BanTime == nil ) then

                // Set OSTime to 0 to symbolize that the user isn't banned because he has no ban time.
                OSTime = 0;

            elseif ( BanTime ~= 0 ) then

                // Times it by 60 to convert the minutes to seconds.
                BanTime     = BanTime * 60;

            end

            // If it's 0 then it'll do nothing to it.

            // Get the current os time.
            local NowTime   = os.time();

            // Add the times.
            local Added     = OSTime + BanTime;
            // So if it was 123456 when you were banned and you were banned for 50.. hmm, hang on a minute this needs to be done in seconds.
            // Ok how do you make minutes into seconds, as ban time is minutes.
            // Right.. so 60 seconds in a minute.
            // So if you had 5 * 60 = 300. So 300 seconds. So we want to x the minutes by 60.

            // Check that we're now ahead of that time.
            if ( ( NowTime < Added && OSTime ~= 0 ) || ( BanTime == 0 ) ) then

                // Get the reason for the ban.
                local Reason = UserBan[ 1 ][ 8 ];

                // Debug.
                SF.Utility.Debug( "Prevented " .. pl:SteamID() .. " (" .. pl:Name() .. ") from playing (" .. Reason .. ").", "1" );

                // Kick the mingebag.
                RunConsoleCommand( "kickid", pl:UserID(), Reason );

                return;

            end

        end

        // I scrapped the below.
        /*
        // Check the ban field.
        MyBanned, Success, Error = mysql.query( SF.Connection, "SELECT * FROM sf_bans WHERE steamid = '" .. pl:SteamID() .. "'" );

        // Check that it was a success.
        if ( !Success ) then

            // IF it wasn't display failed and return.
            SF.Utility.Debug( "5: MYSQL ERROR: " .. Error, "1" );
            return;

        end

        //local Date = SF.Utility.Year .. os.date( "-%m-%d %H:%M:%S" );

        // Get data from the query.
        local BanDate       = MyBanned[ 1 ][ 9 ];
        local BanLength     = MyBanned[ 1 ][ 10 ];

        // Get the current date.
        local CurrentDate = SF.Utility.Year .. os.date( "-%m-%d %H:%M:%S" );

        // minge 1 is banned on     2010-02-15  13:37:15
        // minge 1 is banned for    1440 minutes
        // to work this out we need to.. do some complicated mathematics ^^
        // ok first things first, let's convert these minutes to hours. 60 minutes in 1 hour > minutes /60 = hours.

        local Hours = BanLength / 60;       // Amount of hours the minge is banned for.

        // Let's attempt to add these hours onto the existing hours.
        local BannedHours = string.Explode( BanDate, " " )[ 2 ];        // This should get the HH:MM:SS bit.
        BannedHours = string.Explode( BannedHours[, ":" )[ 1 ];         // This should get the HH bit.

        local CalculateHours = BannedHours + Hours;                     // The minge will be unbanned in this many hours.

        // Idea scrapped, would of been too inaccurate. We haz to do this through minutes.
        // BanLength = minutes.

        // BanDate = YYYY-MM-DD HH:MM:SS
        local BannedMinutes = string.Explode( BanDate, " " )[ 2 ];  // HH:MM:SS
        BannedMinutes = string.Explode( BannedMinutes, ":" )[ 2 ];  // MM

        // Add the minutes on...
        //local CalculateMinutes = BannedMinutes + BanLength;
        // Wait; no. Let's make it all into seconds, then add those seconds, then from the final seconds work out hh:mm:ss on mm:dd.
        // Gah, I'm confused. Let's go visit FacePunch.
        */

        /*

            End the holy ban section.
            Jesus.. that took some work (and FacePunch's help - kudos to all!)

        */

        // If we don't need to ban them then we can set up data for the user.
        SF.Flags.Storage[ pl:SteamID() ] = MyData[ 1 ][ 4 ];
        SF.Time.Start( pl );

        // Display the users connection.
        SF.Utility.ChatAll( pl:Name() .. " has connected. Last known name: " .. MyData[ 1 ][ 8 ] .. "." );

    else

        // Debug it.
        SF.Utility.Debug( pl:Name() .. " doesn't exist! Writing new data." );
        SF.MySQL.Player.New( pl );

        // Return false.
        return false;

    end

end

// Create a timer to load the player on initial spawn.
hook.Add( "PlayerInitialSpawn", "SF.MySQL.Player.Load", SF.MySQL.Player.Load );