import MySQLdb

SQL_HOST = ""
SQL_USER = ""
SQL_PSWD = ""
SQL_DTBS = ""

class DatabaseManager(object):
    """
    This class manages all database connections. The main idea of this object
    is to create an easy interface so we can easilly and safely query values
    from a database witout worrying about convulent and complex sqlite syntax.

    This interface allows us to safely call and remove objects from the database
    so that users do not have to access the database directly, but through
    a simple API structure.

    We should only have one database instance so it is unnecesarry to create
    another object to hold the instances of this object.
    """
    players = []
    def __init__(self, host, user, password, database):
        """
        Default Constructor

        Initialize all the default values and open a connection with the SQLite
        database. Create the tables if they don't exist.

        @PARAM host - Host of the database
        @PARAM user - User of the database
        @PARAM password - Password of the database
        @PARAM database - Database to connect in
        """
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.connection = MySQLdb.connect(self.host, self.user, self.password, self.database)
        self.connection.text_factory = str
        self.cursor = self.connection.cursor()

    def __del__(self):
        """
        Default deconstructor.

        Executed when a database instance is destroyed. Ensure that the database
        is saved and closed.
        """
        self.close()

    def __contains__(self, key):
        """
        Executed automatically when we attempt to test to see if an element exists
        within the database.

        @PARAM key - The value to test for validity
        @RETURN boolean - Whether or not the value exists
        """
        key = str(key)
        self.execute("SELECT games FROM Player WHERE name=?", key)
        result = self.cursor.fetchone()
        if bool(result):
            return True
        return False

    def __iter__(self):
        """
        Executed automatically when we attempt to iterate through an instance
        of this class. Query all the names from the playerstats table and
        yield each name as a seperate object.

        @RETURN yield object - string objects which represent player's names
        """
        self.execute("SELECT * FROM Player")
        self.cursor.fetchall().itervalues()

    def execute(self, parseString, *args):
        """
        A wrapper function to simulate the execute() method of a cursor object.

        @PARAM parseString - the string query line of a SQLite statement
        """
        self.cursor.execute(parseString, args)

    def getPlayerInfo(self, name, infoType):
        """
        Returns a players attribute from the playerstats table.

        @PARAM name   - the name of the player your wish to check
        @PARAM infoType - the column name of the value you wish to return
        @RETURN object  - returns an object type of the value to which statType returns
        """
        infoType = str(infoType).replace("'", "''")
        if hasattr(infoType, "__len__"):
            query = "SELECT " + ",".join(map(str, infoType)) + " FROM Player WHERE name=?"
        else:
            query = "SELECT " + str(infoType) + " FROM Player WHERE name=?"
        self.execute(query, name)
        return self.fetchone()

    def query(self, table, primaryKeyName, primaryKeyValue, options):
        """
        Queries results from the table for one person and returns either a
        tuple or a single value depending on the amount of values passed.

        @PARAM table - the table of which this query should take place
        @PARAM primaryKeyName - the name of the key you wish to test for equality
        @PARAM primaryKeyValue - the options to update where the primaryKeyName's value equates to this value
        @PARAM options - either a single value or a tuple which we will get the values of
        """
        if hasattr(options, "__len__"):
            query = "SELECT " + ",".join(map(lambda x: str(x).replace("'", "''"), options)) + " FROM " + table \
                    + " WHERE " + primaryKeyName + "='" + primaryKeyValue + "'"
        else:
            query = "SELECT " + str(options).replace("'", "''") + " FROM " + table + \
                    " WHERE " + primaryKeyName + "='" + primaryKeyValue + "'"
        self.execute(query)
        return self.fetchone()

    def fetchall(self):
        """
        Mimics the sqlite fetchall method which recides within a cursor object.
        Ensures that the true values are added to a list so that we don't need
        to index it if the value is only one item in length (e.g. item instead
        of (item,)...)

        @RETURN list - attributes from which the query returned
        """
        trueValues = []
        for value in self.cursor.fetchall():
            if isinstance(value, tuple):
                if len(value) > 1:
                    tempValues = []
                    for tempValue in value:
                        if isinstance(tempValue, long):
                            tempValue = int(tempValue)
                        tempValues.append(tempValue)
                    trueValues.append(tempValues)
                else:
                    if isinstance(value[0], long):
                        trueValues.append(int(value[0]))
                    else:
                        trueValues.append(value[0])
            else:
                if isinstance(value, long):
                    value = int(value)
                trueValues.append(value)
        return trueValues

    def fetchone(self):
        """
        Mimics the sqlite fetchone method which recides within a cursor object.
        Ensures that a single value is returned from the cursor object if only
        one object exists within the tuple from the query, otherwise it returns
        the query result

        @RETURN Object - the result from the query command
        """
        result = self.cursor.fetchone()
        if hasattr(result, "__iter__"):
            if len(result) == 1:
                trueResults = result[0]
                if isinstance(trueResults, long):
                    trueResults = int(trueResults)
                return trueResults
            else:
                trueResults = []
                for trueResult in result:
                    if isinstance(trueResult, long):
                        trueResult = int(trueResult)
                    trueResults.append(trueResult)
                return trueResults
        if isinstance(result, long):
            result = int(result)
        return result

    def close(self):
        """
        Closes the connections so that no further queries can be made.
        """
        self.cursor.close()
        self.connection.close()

class PlayerManager(object):
    """
    This class is used to manage players. This class itself simulates a
    dictionary but gives additional functions which allow us to modify the way
    the singelton is perceived. This will store individual Player objects by a
    tag name and allows us to reference the singleton object by dictionary item
    assignment / retrieval to retrieve the retrespective player object.
    """
    def __init__(self):
        """ Default constructor, initialize variables """
        self.players = {}

    def __getitem__(self, name):
        """
        Executed when object[x] is executed on the singleton. As we wish to
        simulate dictionary attributes, we want to return the player object
        referenced by their name

        @PARAM str name - The ID of the user we wish to return the Player object
        @RETURN Player - The Player object referenced by ID
        """
        return self.players[name]

    def __iter__(self):
        """
        This function executes automatically when "for x in object" is executed.
        We want to return all the player objects inside the player dictionary.

        @RETURN generator -  A generator to a list of all the player objects
        """
        return self.players.itervalues()

    def __contains__(self, name):
        """
        Executed automatically when we use the syntax "x in object". We will
        test if the name in question exists within the players dictionary.

        @PARAM str name - The name to test for validation
        @RETURN boolean - Whether or not the name already exists
        """
        return name in self.players

    def __delitem__(self, name):
        """
        Removes the player and object from memory so the RAM is cleared.
        object.removePlayer(name) == object.__delitem__(name)

        @PARAM str name - The name of the player to remove
        """
        self.removePlayer(name)

    def addPlayer(self, name):
        """
        Add the player into memory as an Player object

        @PARAM str name - The name of the player to add
        """
        self.players[name] = PlayerObject(name)

    def removePlayer(self, name):
        """
        Removes the player and object from memory

        @PARAM str name - The name of the player to remove
        """
        if name in self.players:
            del self.players[name]

class PlayerObject(object):
    """
    This is an object which will revolve around all active players. This should
    contain all relevant information about a particular player, and cache all
    their details so we don't need to query the database at regular intervals.
    """
    name  = None
    cache = None
    def __init__(self, name):
        """"
        Default constructor, initialize variables
        Set up all necessary objects and cache values here.

        @PARAM str name - The name of the user who this object references
        """
        self.name  = str(name)
        self.cache = PlayerCache(self.name)

    def __getattr__(self, attribute):
        """
        This object will allow us to get attributes from the cache object

        @PARAM str attribute  - The attribute of the PlayerCache we want to
        @RETURN str attribute - The attribute if it he exist
        """
        if hasattr(self.cache, attribute):
            return getattr(self.cache, attribute)
        raise AttributeError("Player object has no attribute %s" % attribute)

    def __setattr__(self, attribute, value):
        """
        This object will allow us to reassing values in the PlayerCache object

        @PARAM str attribute     - The attribute to change
        @PARAM int|str value     - The value to set to the attribute
        @RETURN mixed attribute  - setattr object if cache has attribute
        """
        if hasattr(self.cache, attribute):
            return setattr(self.cache, attribute, value)
        object.__setattr__(attribute, value)

class PlayerCache(object):
    """
    This object should be used by each player and should store all relevant
    information regarding their current stats. Only refresh if explicitly told to;
    we never need to refresh on ticks. This means we can reduce the amount of
    DB queries we need which improves read time and efficiency.
    """
    def __init__(self, name):
        """"
        Default constructor, initialize variables

        @PARAM str name - The name of the player on the DB
        """
        self.name = name
        self.update()

    def update(self):
        """
        This method simply refreshes the local attributes
        with the attributes from the dictionnary.
        """
        database.execute("SELECT games, scores, strikes, spares, splits, opens FROM Player WHERE name=?", self.name)
        self.games, self.scores, self.strikes, self.spares, self.splits, self.opens = database.fetchone()
        self.minimum, self.maximum, self.average = min(self.scores), max(self.scores), round(sum(map(int, self.scores)) / float(len(self.scores)))

players  = PlayerManager()
database = DatabaseManager(SQL_HOST, SQL_USER, SQL_PSWD, SQL_DTBS)