import System.Data;
import Mono.Data.Sqlite;

class DBAccess
{
	private var connection:String;
    private var dbcon:IDbConnection;
    private var dbcmd:IDbCommand;
    private var reader:IDataReader;
    private var playerTable:String;
    private var saveTable:String;
 
    function OpenDB() {
	    connection = "URI=file:GameDB.sqdb";
	    playerTable = "Player";
	    saveTable = "Save";
	    dbcon = new SqliteConnection(connection);
	    dbcon.Open();
    }
    
    function CreateTable(name:String, col:Array, colType:Array) { 
        var query:String;
        query = "CREATE TABLE " + name + "(" + col[0] + " " + colType[0];
        
        for(var i=1; i<col.length; i++)
            query += ", " + col[i] + " " + colType[i];
        
        query += ")";
        dbcmd = dbcon.CreateCommand();
        dbcmd.CommandText = query;
        reader = dbcmd.ExecuteReader();
    }
    
    function InsertInto(tableName:String, values:Array) {
        var query:String;
        query = "INSERT INTO " + tableName + " VALUES (" + values[0];
        
        for(var i=1; i<values.length; i++)
            query += ", " + values[i];
        
        query += ")";
        dbcmd = dbcon.CreateCommand();
        dbcmd.CommandText = query; 
        reader = dbcmd.ExecuteReader(); 
        CloseDB();
    }
    
    function GetTopHighScores() {
        var query:String;
        query = "SELECT * FROM " + playerTable + " ORDER BY SCORE DESC LIMIT 5"; 
        dbcmd = dbcon.CreateCommand();
        dbcmd.CommandText = query; 
        reader = dbcmd.ExecuteReader(); 
               
        var readArray = new ArrayList();
        
        while(reader.Read()) { 
            var lineArray = new ArrayList();
            
            for (var i = 0; i < reader.FieldCount; i++)
                lineArray.Add(reader.GetValue(i));
                
            readArray.Add(lineArray);
        }
        
        return readArray;
    }
    
    function GetPlayerTopScore(name:String)
    {
        var query:String;
        query = "SELECT SCORE FROM " + playerTable + " WHERE NAME LIKE '"+name+"' ORDER BY SCORE DESC LIMIT 1"; 
        dbcmd = dbcon.CreateCommand();
        dbcmd.CommandText = query; 
        reader = dbcmd.ExecuteReader();        
        reader.Read();
        return reader.GetString(0);
    }
    
    function GetSaveGame() {
    	var query:String;
    	query = "SELECT * FROM " + saveTable;
    	docmd = dbcon.CreateCommand();
    	dbcmd.CommandText = query;
    	reader = dbcmd.ExecuteReader();
    	var readArray = new ArrayList();
    	
    	while(reader.Read()) {
    		for (var i = 0; i < reader.FieldCount; i++)
    			readArray.Add(reader.GetValue(i));
    	}
    	
    	return readArray;
    }
    
    function DeleteLastSave() {
    	var query:String;
    	query = "DELETE FROM " + saveTable;
    	dbcmd = dbcon.CreateCommand();
    	dbcmd.CommandText = query;
    	dbcmd.ExecuteReader();
    }

    function CloseDB() {
        reader.Close();
        reader = null;
        dbcmd.Dispose();
        dbcmd = null;
        dbcon.Close();
        dbcon = null;
    }
}