package DB
{
    import BO.Contact;
    
    import flash.data.SQLConnection;
    import flash.data.SQLMode;
    import flash.data.SQLResult;
    import flash.data.SQLStatement;
    import flash.errors.SQLError;
    import flash.events.Event;
    import flash.filesystem.File;
    
    public class Database extends Logger
    {
        private static var constructorKey:Object = {};
        private static var instance:Database = null;
        
        private var databaseFile:File;
        
        public static const DATABASE_CHANGE_EVENT_NAME:String = "DATABASE_CHANGE_EVENT_NAME";
        
        public function Database(pConstructorKey:Object)
        {
            if(pConstructorKey != constructorKey)
            {
                throw new Error("Instanciation illégale (constructeur privé)");
            }
            
            databaseFile = File.applicationStorageDirectory.resolvePath("database.sqlite");
        }
        
        public static function get Instance():Database
        {
            if(instance == null)
            {
                instance = new Database(constructorKey);
            }
            return instance;
        }

        private function executeQuery(pMode:String, pQuery:String, pParameters:Object = null, pClass:Class=null):SQLResult
        {
            try
            {
                var Connection:SQLConnection = new SQLConnection();
                var Statement:SQLStatement = new SQLStatement();
                
                Connection.open(databaseFile, pMode);
                
                Statement.sqlConnection = Connection;
                
                Statement.text = pQuery;
                
                if(pParameters != null)
                {
                    for(var id:String in pParameters)
                    {
                        Statement.parameters[id] = pParameters[id];
                    }
                }
                if(pClass != null)
                {
                    Statement.itemClass = pClass;
                }
                
                Statement.execute();

                return Statement.getResult();
            }
            catch(error:SQLError)
            {
                this.LogError("Requête : <" + pQuery + ">\n" + error.toString());
            }
            finally
            {
                Connection.close();
            }
            return null;
        }
                
        public function createDatabase():Boolean
        {
            if(databaseFile.exists)
            {
                return true;
            }
            var sQuery:String = "CREATE TABLE Contact (";
            sQuery += "ID INTEGER PRIMARY KEY, "
            sQuery += "Pseudo TEXT, "
            sQuery += "Mail TEXT, "
            sQuery += "DateNaissance Date, "
            sQuery += "Avatar OBJECT "
            sQuery += ")";
            
            var result:SQLResult = this.executeQuery(SQLMode.CREATE, sQuery);
            
            return result != null;
        }
        
        public function insertContact(pContact:Contact):Boolean
        {
            var sQuery:String = "INSERT INTO Contact (Pseudo, Mail, DateNaissance, Avatar) VALUES (@Pseudo, @Mail, @DateNaissance, @Avatar)";
            var params:Object = {"@Pseudo" : pContact.Pseudo, "@Mail" : pContact.Mail, "@DateNaissance" : pContact.DateNaissance, "@Avatar" : pContact.Avatar};
            
            var result:SQLResult = this.executeQuery(SQLMode.UPDATE, sQuery, params);
            
            if(result != null)
            {
                pContact.ID = result.lastInsertRowID;
                dispatchEvent(new Event(DATABASE_CHANGE_EVENT_NAME));
                return true;
            }
            
            return false;
        }
        
        public function updateContact(pContact:Contact):Boolean
        {
            var sQuery:String = "UPDATE Contact SET Pseudo=@Pseudo, Mail=@Mail, DateNaissance=@DateNaissance, Avatar=@Avatar WHERE ID=@ID";
            var params:Object = {"@Pseudo" : pContact.Pseudo, "@Mail" : pContact.Mail, "@DateNaissance" : pContact.DateNaissance, "@Avatar" : pContact.Avatar, "@ID" : pContact.ID};
            
            var result:SQLResult = this.executeQuery(SQLMode.UPDATE, sQuery, params);
            
            return result != null;
        }
        
        public function deleteContact(pContact:Contact):Boolean
        {
            var sQuery:String = "DELETE FROM Contact WHERE ID=@ID";
            var params:Object = {"@ID" : pContact.ID};
            
            var result:SQLResult = this.executeQuery(SQLMode.UPDATE, sQuery, params);

            dispatchEvent(new Event(DATABASE_CHANGE_EVENT_NAME));
            
            return result != null;
        }
        
        public function getContactList(pStartIndex:uint, pCount:uint):Array
        {
            var sQuery:String = "SELECT * FROM Contact LIMIT @count OFFSET @start";
            var params:Object = {"@start" : pStartIndex, "@count" : pCount};
            
            var result:SQLResult = this.executeQuery(SQLMode.READ, sQuery, params, Contact);
            
            if(result != null)
            {
                return result.data == null ? new Array() : result.data;
            }
            return null;
        }
        
        public function getContactCount():uint
        {
            var result:SQLResult = this.executeQuery(SQLMode.READ, "SELECT COUNT(ID) as RecordCount FROM Contact");
            
            if(result != null && result.data != null)
            {
                return result.data[0].RecordCount;
            }
            return null;
        }
    }
}