using MySql.Data.MySqlClient;
using ServiceShared.Models.Database;
using static ServiceShared.Models.Database.Device;
using static ServiceShared.Models.Database.Results;

namespace ServiceShared.Database.Controllers
{
    public class Results
    {
        private DbContext _DbContext;

        /// <summary>
        /// Constructor for results database controller
        /// </summary>
        /// <param name="dbContext">Database context, that contains connection to the mysql database</param>
        public Results(DbContext dbContext)
        {
            this._DbContext = dbContext;
        }

        /// <summary>
        /// Returns results by pgs and udid from the database
        /// </summary>
        /// <param name="pgs">pgs hash(partial pk)</param>
        /// <param name="udid">udid(partial pk)</param>
        /// <returns>Returns results object from the database</returns>
        public Models.Database.Results GetResults(string pgs, string udid)
        {
            Models.Database.Results result = null;

            try
            {
                if(!string.IsNullOrEmpty(pgs) && !string.IsNullOrEmpty(udid))
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"select
                                                    r.pgs,
                                                    r.pgs_hash,
                                                    d.udid,
                                                    d.device_token,
                                                    d.device_type,
                                                    r.client_public_key,
                                                    r.server_public_key,
                                                    r.file_checksum,
                                                    r.available,
                                                    r.available_ts,
                                                    r.picked_up,
                                                    r.picked_up_ts,
                                                    r.notified,
                                                    r.notified_ts,
                                                    d.verificator_hash,
                                                    r.status,
                                                    r.modified,
                                                    r.created,
                                                    r.not_found_counter,
                                                    r.not_found_ts,
                                                    r.notification_counter
                                            from
                                                    results r
                                            inner join
                                                    device d on d.udid = r.udid
                                            where
                                                    r.pgs = @pgs and
                                                    d.udid = @udid";

                        cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
                        cmd.Parameters.Add(new MySqlParameter("udid", udid));

                        MySqlDataReader reader = cmd.ExecuteReader();

                        if (reader != null && reader.HasRows && reader.Read())
                        {
                            result = this.ReadResults(reader);
                        }

                        client.Close();

                        Log.Write(cmd.CommandText + ", @pgs = " + pgs +
                                                    ", @udid = " + udid, Log.Types.LOG);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "GetResults(string, string)");
            }

            return result;
        }

        /// <summary>
        /// Returns not found results
        /// <param name="pastMinutes">past minutes, after that the not found results should be checked again</param>
        /// </summary>
        /// <returns>Returns results object from the database</returns>
        public List<Models.Database.Results> GetNotFoundResults(int pastMinutes, int maxTryNotFoundResults)
        {
            List<Models.Database.Results> result = null;

            try
            {
                using (MySqlConnection client = this._DbContext.CreateConnection())
                {
                    client.Open();
                    MySqlCommand cmd = client.CreateCommand();
                    cmd.CommandText = @"select
                                                    r.pgs,
                                                    r.pgs_hash,
                                                    d.udid,
                                                    d.device_token,
                                                    d.device_type,
                                                    r.client_public_key,
                                                    r.server_public_key,
                                                    r.file_checksum,
                                                    r.available,
                                                    r.available_ts,
                                                    r.picked_up,
                                                    r.picked_up_ts,
                                                    r.notified,
                                                    r.notified_ts,
                                                    d.verificator_hash,
                                                    r.status,
                                                    r.modified,
                                                    r.created,
                                                    r.not_found_counter,
                                                    r.not_found_ts,
                                                    r.notification_counter
                                            from
                                                    results r
                                            inner join
                                                    device d on d.udid = r.udid
                                            where
                                                    r.status = @status and
                                                    r.not_found_counter < @max_not_found_counter and
                                                    r.not_found_ts is not null";

                    cmd.Parameters.Add(new MySqlParameter("status", ServiceShared.Models.Database.Results.ResultsStatus.NOT_FOUND.ToString()));

                    cmd.Parameters.Add(new MySqlParameter("max_not_found_counter", maxTryNotFoundResults));

                    MySqlDataReader reader = cmd.ExecuteReader();

                    if (reader != null && reader.HasRows)
                    {
                        result = new List<Models.Database.Results>();

                        DateTime now = DateTime.Now;

                        while (reader.Read())
                        {
                            Models.Database.Results results = this.ReadResults(reader);

                            Log.Debug("[BW] check not found past minutes: " + (now - results.NotFoundTS.Value).TotalMinutes.ToString());

                            if (result != null && results.NotFoundTS.HasValue && (now - results.NotFoundTS.Value).TotalMinutes >= (pastMinutes - 5))
                            {
                                result.Add(results);
                            }
                        }
                    }

                    client.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "GetNotFoundResults(int)");
            }

            return result;
        }

        /// <summary>
        /// Returns completed results that could not be notified
        /// </summary>
        /// <returns>Returns results object from the database</returns>
        public List<Models.Database.Results> GetNotNotifiedResults()
        {
            List<Models.Database.Results> result = null;

            try
            {
                using (MySqlConnection client = this._DbContext.CreateConnection())
                {
                    client.Open();
                    MySqlCommand cmd = client.CreateCommand();
                    cmd.CommandText = @"select
                                                    r.pgs,
                                                    r.pgs_hash,
                                                    d.udid,
                                                    d.device_token,
                                                    d.device_type,
                                                    r.client_public_key,
                                                    r.server_public_key,
                                                    r.file_checksum,
                                                    r.available,
                                                    r.available_ts,
                                                    r.picked_up,
                                                    r.picked_up_ts,
                                                    r.notified,
                                                    r.notified_ts,
                                                    d.verificator_hash,
                                                    r.status,
                                                    r.modified,
                                                    r.created,
                                                    r.not_found_counter,
                                                    r.not_found_ts,
                                                    r.notification_counter
                                            from
                                                    results r
                                            inner join
                                                    device d on d.udid = r.udid
                                            where
                                                    r.notified <> 1 and
                                                    r.available = 1 and
                                                    r.status = @status and
                                                    r.notification_counter <= 3";

                    cmd.Parameters.Add(new MySqlParameter("status", ServiceShared.Models.Database.Results.ResultsStatus.COMPLETED.ToString()));
                 
                    MySqlDataReader reader = cmd.ExecuteReader();

                    if (reader != null && reader.HasRows)
                    {
                        result = new List<Models.Database.Results>();

                        while(reader.Read())
                        {
                            Models.Database.Results results = this.ReadResults(reader);

                            if(result != null)
                            {
                                result.Add(results);
                            }
                        }
                    }

                    client.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "GetNotNotifiedResults()");
            }

            return result;
        }

        /// <summary>
        /// Returns not found results that could not be notified
        /// </summary>
        /// <returns>Returns results object from the database</returns>
        public List<Models.Database.Results> GetNotFoundNotNotifiedResults(int maxNotFoundTries)
        {
            List<Models.Database.Results> result = null;

            try
            {
                using (MySqlConnection client = this._DbContext.CreateConnection())
                {
                    client.Open();
                    MySqlCommand cmd = client.CreateCommand();
                    cmd.CommandText = @"select
                                                    r.pgs,
                                                    r.pgs_hash,
                                                    d.udid,
                                                    d.device_token,
                                                    d.device_type,
                                                    r.client_public_key,
                                                    r.server_public_key,
                                                    r.file_checksum,
                                                    r.available,
                                                    r.available_ts,
                                                    r.picked_up,
                                                    r.picked_up_ts,
                                                    r.notified,
                                                    r.notified_ts,
                                                    d.verificator_hash,
                                                    r.status,
                                                    r.modified,
                                                    r.created,
                                                    r.not_found_counter,
                                                    r.not_found_ts,
                                                    r.notification_counter
                                            from
                                                    results r
                                            inner join
                                                    device d on d.udid = r.udid
                                            where
                                                    r.notified <> 1 and
                                                    r.available = 0 and
                                                    r.status = @status and
                                                    r.not_found_counter >= @not_found_counter and
                                                    r.notification_counter <= 3";

                    cmd.Parameters.Add(new MySqlParameter("status", ServiceShared.Models.Database.Results.ResultsStatus.NOT_FOUND.ToString()));
                    cmd.Parameters.Add(new MySqlParameter("not_found_counter", maxNotFoundTries));

                    MySqlDataReader reader = cmd.ExecuteReader();

                    if (reader != null && reader.HasRows)
                    {
                        result = new List<Models.Database.Results>();

                        while (reader.Read())
                        {
                            Models.Database.Results results = this.ReadResults(reader);

                            if (result != null)
                            {
                                result.Add(results);
                            }
                        }
                    }

                    client.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "GetNotFoundNotNotifiedResults()");
            }

            return result;
        }

        /// <summary>
        /// Returns list of results by pgs(partial pk) from the database
        /// </summary>
        /// <param name="pgs">pgs hash(partial pk)</param>
        /// <returns>Returns results object from the database</returns>
        public List<Models.Database.Results> GetResults(string pgs)
        {
            List<Models.Database.Results> result = null;

            try
            {
                if (!string.IsNullOrEmpty(pgs))
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"select
                                                    r.pgs,
                                                    r.pgs_hash,
                                                    d.udid,
                                                    d.device_token,
                                                    d.device_type,
                                                    r.client_public_key,
                                                    r.server_public_key,
                                                    r.file_checksum,
                                                    r.available,
                                                    r.available_ts,
                                                    r.picked_up,
                                                    r.picked_up_ts,
                                                    r.notified,
                                                    r.notified_ts,
                                                    d.verificator_hash,
                                                    r.status,
                                                    r.modified,
                                                    r.created,
                                                    r.not_found_counter,
                                                    r.not_found_ts,
                                                    r.notification_counter
                                            from
                                                    results r
                                            inner join
                                                    device d on d.udid = r.udid
                                            where
                                                    r.pgs = @pgs";

                        cmd.Parameters.Add(new MySqlParameter("pgs", pgs));

                        MySqlDataReader reader = cmd.ExecuteReader();

                        if (reader != null && reader.HasRows)
                        {
                            result = new List<Models.Database.Results>();

                            while(reader.Read())
                            {
                                Models.Database.Results dbResults = this.ReadResults(reader);

                                if(dbResults != null)
                                {
                                    result.Add(dbResults);
                                }
                            }
                        }

                        client.Close();

                        Log.Write(cmd.CommandText + ", @pgs = " + pgs, Log.Types.LOG);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "GetResults(string)");
            }

            return result;
        }

        /// <summary>
        /// Returns device by udid
        /// </summary>
        /// <param name="udid">udid (pk)</param>
        /// <returns>Returns Device object</returns>
        public Device GetDevice(string udid)
        {
            Device result = null;

            try
            {
                if (!string.IsNullOrEmpty(udid))
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"select
                                                    d.udid,
                                                    d.device_token,
                                                    d.public_key,
                                                    d.verificator_hash,
                                                    d.pat_hash,
                                                    d.pin,
                                                    d.device_type,
                                                    d.created,
                                                    d.modified
                                            from
                                                    device d
                                            where
                                                    d.udid = @udid";

                        cmd.Parameters.Add(new MySqlParameter("udid", udid));

                        MySqlDataReader reader = cmd.ExecuteReader();

                        if (reader != null && reader.HasRows && reader.Read())
                        {
                            result = this.ReadDevice(reader);
                        }

                        client.Close();

                        Log.Write(cmd.CommandText + ", @udid = " + udid, Log.Types.LOG);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "GetLastDeviceRecord(string)");
            }

            return result;
        }

        /// <summary>
        /// Checks if device exists
        /// </summary>
        /// <param name="udid">udid of device</param>
        /// <returns>returns true if exists</returns>
        public bool DeviceExists(string udid)
        {
            bool result = false;

            try
            {
                if (!string.IsNullOrEmpty(udid))
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();

                        /** insert device **/
                        cmd.CommandText = @"select count(*) cnt from device where udid = @udid";
                        cmd.Parameters.Add(new MySqlParameter("udid", udid));

                        string res = cmd.ExecuteScalar().ToString();

                        if (!string.IsNullOrEmpty(res))
                        {
                            int r = 0;

                            if (Int32.TryParse(res, out r))
                            {
                                result = (r > 0);
                            }
                        }

                        client.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "DeviceExists(string)");
            }

            return result;
        }

        /// <summary>
        /// Creates results in the database
        /// </summary>
        /// <param name="results">Results object</param>
        /// <returns>returns true if success</returns>
        public bool Create(Models.Database.Results results)
        {
            bool result = false;

            try
            {
                if (results != null && 
                   !string.IsNullOrEmpty(results.PGS) && 
                   results.PGS.Length == 128 &&
                   !string.IsNullOrEmpty(results.PGS_HASH) &&
                   !string.IsNullOrEmpty(results.UDID) &&
                   (results.UDID.Length >= 10 && results.UDID.Length <= 64) &&
                   !string.IsNullOrEmpty(results.DeviceToken) &&
                   (results.DeviceToken.Length >= 10 && results.DeviceToken.Length <= 255) &&
                   results.DeviceType != DeviceTypes.NONE &&
                   !string.IsNullOrEmpty(results.ClientPublicKey) &&
                   (results.ClientPublicKey.Length >= 32 && results.ClientPublicKey.Length <= 64) &&
                   !string.IsNullOrEmpty(results.VerificationHash) &&
                   results.VerificationHash.Length == 128)
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();

                        MySqlCommand cmd = client.CreateCommand();

                        if(!this.DeviceExists(results.UDID) && !string.IsNullOrEmpty(results.PIN))
                        {
                            /** insert device **/
                            cmd.CommandText = @"insert into device (udid, 
                                                                    device_token,
                                                                    public_key,
                                                                    verificator_hash,
                                                                    pin,
                                                                    device_type)

                                                            values (@udid,
                                                                    @device_token,
                                                                    @public_key,
                                                                    @verificator_hash,
                                                                    @pin,
                                                                    @device_type)";

                            cmd.Parameters.Add(new MySqlParameter("udid", results.UDID));
                            cmd.Parameters.Add(new MySqlParameter("device_token", results.DeviceToken));
                            cmd.Parameters.Add(new MySqlParameter("public_key", results.ClientPublicKey));
                            cmd.Parameters.Add(new MySqlParameter("verificator_hash", results.VerificationHash));
                            cmd.Parameters.Add(new MySqlParameter("pin", results.PIN));
                            cmd.Parameters.Add(new MySqlParameter("device_type", results.DeviceType));

                            cmd.ExecuteNonQuery();

                            /** log **/
                            Log.Write(cmd.CommandText + "@udid = " + results.UDID +
                                                        ", @device_token = " + results.DeviceToken +
                                                        ", @public_key = " + results.ClientPublicKey +
                                                        ", @verificator_hash = " + results.VerificationHash.ToString() +
                                                        ", @pin = " + results.PIN +
                                                        ", @device_type = " + results.DeviceType,
                                                        Log.Types.LOG);
                        }
                        

                        /** insert results **/
                        cmd = client.CreateCommand();

                        cmd.CommandText = @"insert into results (pgs, 
                                                                 pgs_hash,
                                                                 udid,   
                                                                 client_public_key, 
                                                                 server_public_key,                                                                
                                                                 available,
                                                                 available_ts,
                                                                 status,
                                                                 file_checksum)

                                                        values (@pgs, 
                                                                @pgs_hash,
                                                                @udid, 
                                                                @client_public_key, 
                                                                @server_public_key,
                                                                @available,
                                                                @available_ts,
                                                                @status,
                                                                @file_checksum)";

                        cmd.Parameters.Add(new MySqlParameter("pgs", results.PGS));
                        cmd.Parameters.Add(new MySqlParameter("pgs_hash", results.PGS_HASH));
                        cmd.Parameters.Add(new MySqlParameter("udid", results.UDID));
                        cmd.Parameters.Add(new MySqlParameter("client_public_key", results.ClientPublicKey));
                        cmd.Parameters.Add(new MySqlParameter("server_public_key", (!string.IsNullOrEmpty(results.ServerPublicKey) ? results.ServerPublicKey : DBNull.Value)));
                        cmd.Parameters.Add(new MySqlParameter("available", results.Available));
                        cmd.Parameters.Add(new MySqlParameter("available_ts", (results.AvailableTS.HasValue ? results.AvailableTS.Value : DBNull.Value)));
                        cmd.Parameters.Add(new MySqlParameter("status", results.Status.ToString()));                        
                        cmd.Parameters.Add(new MySqlParameter("file_checksum", (!string.IsNullOrEmpty(results.FileChecksum) ? results.FileChecksum : DBNull.Value)));

                        cmd.ExecuteNonQuery();
                        result = true;
                        client.Close();

                        Log.Write(cmd.CommandText + ", @pgs = " + results.PGS +
                                                    ", @pgs_hash = " + results.PGS_HASH +
                                                    ", @udid = " + results.UDID +
                                                    ", @client_public_key = " + results.ClientPublicKey +
                                                    ", @server_public_key = " + (!string.IsNullOrEmpty(results.ServerPublicKey) ? results.ServerPublicKey : "") +
                                                    ", @available = " + results.Available.ToString() +
                                                    ", @available_ts = " + (results.AvailableTS.HasValue ? results.AvailableTS.Value.ToString() : "") +
                                                    ", @status = " + results.Status.ToString() +                                                    
                                                    ", @file_checksum = " + (!string.IsNullOrEmpty(results.FileChecksum) ? results.FileChecksum : ""),
                                                    Log.Types.LOG);
                    }
                }
                else
                {
                    Log.Critical(new Exception("Missing some of parameters to insert into results"), "ServiceOutside.Database.Controllers.Results", "Create(Models.Results)");
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "Create(Models.Results)");
            }

            return result;
        }

        /// <summary>
        /// Updates status of results in the database
        /// </summary>
        /// <param name="pgs">pgs (partial pk)</param>
        /// <param name="udid">udid (partial pk)</param>
        /// <param name="available">available, that should be set</param>
        /// <param name="server_public_key">server public key, that was used for the encryption by server side</param>
        /// <param name="file_checksum">Checksum of file content(base64) before encrypted</param>
        /// <param name="available_ts">timestamp of available, that should be set</param>
        /// <param name="status">status of results</param>
        /// <param name="pat_hash">patient aes hash that can be decrypted by service inside</param>
        /// <returns>Returns true if success</returns>
        public bool UpdateStatus(string pgs, string udid, bool available, string server_public_key, string file_checksum, DateTime available_ts, ResultsStatus status, string pat_hash = null)
        {
            bool result = false;

            try
            {
                if (!string.IsNullOrEmpty(pgs) &&
                    pgs.Length == 128 &&
                    !string.IsNullOrEmpty(udid) &&
                    status != ResultsStatus.NONE)
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"update 
                                                results r
                                            inner join 
                                                device d on d.udid = r.udid
                                            set 
                                                r.available = @available, 
                                                r.available_ts = @available_ts, 
                                                r.status = @status,
                                                r.server_public_key = @server_public_key,
                                                r.file_checksum = @file_checksum,
                                                r.picked_up = 0,
                                                r.picked_up_ts = null,
                                                r.modified = now(),
                                                d.modified = now(),
                                                d.pat_hash = @pat_hash
                                            where 
                                                r.pgs = @pgs and
                                                d.udid = @udid";

                        cmd.Parameters.Add(new MySqlParameter("available", available));
                        cmd.Parameters.Add(new MySqlParameter("available_ts", available_ts.ToString("yyyy-MM-dd HH:mm:ss")));
                        cmd.Parameters.Add(new MySqlParameter("status", status.ToString()));
                        cmd.Parameters.Add(new MySqlParameter("server_public_key", (!string.IsNullOrEmpty(server_public_key) ? server_public_key : DBNull.Value)));
                        cmd.Parameters.Add(new MySqlParameter("file_checksum", (!string.IsNullOrEmpty(file_checksum) ? file_checksum : DBNull.Value)));
                        cmd.Parameters.Add(new MySqlParameter("pat_hash", (!string.IsNullOrEmpty(pat_hash) ? pat_hash : DBNull.Value)));
                        cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
                        cmd.Parameters.Add(new MySqlParameter("udid", udid));
                        cmd.ExecuteNonQuery();
                        result = true;
                        client.Close();

                        Log.Write(cmd.CommandText + ", @available = " + available +
                                                    ", @available_ts = " + available_ts.ToString("yyyy-MM-dd HH:mm:ss") +
                                                    ", @status = " + status.ToString() +
                                                    ", @server_public_key = " + (!string.IsNullOrEmpty(server_public_key) ? server_public_key : "" ) +
                                                    ", @file_checksum = " + (!string.IsNullOrEmpty(file_checksum) ? file_checksum : "") +
                                                    ", @pat_hash = " + (!string.IsNullOrEmpty(pat_hash) ? pat_hash : "") +
                                                    ", @pgs = " + pgs +
                                                    ", @udid = " + udid,
                                                    Log.Types.LOG);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "UpdateStatus(string, string, bool, string, string, DateTime, ResultsStatus, string=null)");
            }

            return result;
        }

        /// <summary>
        /// Updates status to rejected
        /// </summary>
        /// <param name="pgs">pgs(partial pk), that should be updated</param>
        /// <param name="udid">udid(partial pk), that should be updated</param>
        /// <returns>returns true if success</returns>
        public bool SetReject(string pgs, string udid)
        {
            bool result = false;

            try
            {
                if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128 && !string.IsNullOrEmpty(udid))
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"update results set
                                                status = @status, 
                                                available = 0, 
                                                available_ts = null, 
                                                file_checksum = null, 
                                                modified = now() 
                                            where 
                                                pgs = @pgs and
                                                udid = @udid";

                        cmd.Parameters.Add(new MySqlParameter("status", ResultsStatus.REJECTED.ToString()));
                        cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
                        cmd.Parameters.Add(new MySqlParameter("udid", udid));
                        cmd.ExecuteNonQuery();
                        result = true;
                        client.Close();

                        Log.Write(cmd.CommandText + ", @status = " + ResultsStatus.REJECTED.ToString() +
                                                    ", @pgs = " + pgs +
                                                    ", @udid = " + udid,
                                                    Log.Types.LOG);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "SetReject(string, string)");
            }

            return result;
        }

        /// <summary>
        /// Updates status to NOT_FOUND
        /// </summary>
        /// <param name="pgs">pgs(partial pk), that should be updated</param>
        /// <param name="udid">udid(partial pk), that should be updated</param>
        /// <returns>returns true if success</returns>
        public bool SetNotFound(string pgs, string udid)
        {
            bool result = false;

            try
            {
                if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128 && !string.IsNullOrEmpty(udid))
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"update results set
                                                status = @status, 
                                                available = 0, 
                                                available_ts = null, 
                                                file_checksum = null, 
                                                not_found_counter = not_found_counter + 1,
                                                not_found_ts = now(),
                                                modified = now() 
                                            where 
                                                pgs = @pgs and
                                                udid = @udid";

                        cmd.Parameters.Add(new MySqlParameter("status", ResultsStatus.NOT_FOUND.ToString()));
                        cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
                        cmd.Parameters.Add(new MySqlParameter("udid", udid));
                        cmd.ExecuteNonQuery();
                        result = true;
                        client.Close();

                        Log.Write(cmd.CommandText + ", @status = " + ResultsStatus.NOT_FOUND.ToString() +
                                                    ", @pgs = " + pgs +
                                                    ", @udid = " + udid,
                                                    Log.Types.LOG);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "SetNotFound(string, string)");
            }

            return result;
        }

        /// <summary>
        /// Sets the notified flag to true and timestamp of notified flag
        /// </summary>
        /// <param name="pgs">pgs(partial pk), that should be updated</param>
        /// <param name="udid">udid(partial pk), that should be updated</param>
        /// <returns>returns true if success</returns>
        public bool SetNotified(string pgs, string udid)
        {
            bool result = false;

            try
            {
                if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128)
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"update results 
                                                set notified = 1, 
                                                notified_ts = now(), 
                                                notification_counter = notification_counter + 1,
                                                modified = now() 
                                            where
                                                pgs = @pgs and 
                                                udid = @udid";

                        cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
                        cmd.Parameters.Add(new MySqlParameter("udid", udid));

                        cmd.ExecuteNonQuery();
                        result = true;
                        client.Close();

                        Log.Write(cmd.CommandText + ", @pgs = " + pgs + ", @udid = " + udid, Log.Types.LOG);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "SetNotified(string, string)");
            }

            return result;
        }

        /// <summary>
        /// Sets counter of notification try
        /// </summary>
        /// <param name="pgs">pgs(partial pk), that should be updated</param>
        /// <param name="udid">udid(partial pk), that should be updated</param>
        /// <returns>returns true if success</returns>
        public bool SetNotificationTry(string pgs, string udid)
        {
            bool result = false;

            try
            {
                if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128)
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"update results set 
                                                notification_counter = notification_counter + 1,
                                                modified = now() 
                                            where
                                                pgs = @pgs and 
                                                udid = @udid";

                        cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
                        cmd.Parameters.Add(new MySqlParameter("udid", udid));

                        cmd.ExecuteNonQuery();
                        result = true;
                        client.Close();

                        Log.Write(cmd.CommandText + ", @pgs = " + pgs + ", @udid = " + udid, Log.Types.LOG);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "SetNotificationTry(string, string)");
            }

            return result;
        }

        /// <summary>
        /// Sets the pickedup flag to true and timestamp of pickedup flag
        /// </summary>
        /// <param name="pgs">pgs(partial pk), that should be updated</param>
        /// <param name="udid">udid(partial pk), that should be updated</param>
        /// <returns>returns true if success</returns>
        public bool SetPickedUp(string pgs, string udid)
        {
            bool result = false;

            try
            {
                if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128)
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"update results set picked_up = 1, picked_up_ts = now(), modified = now() where pgs = @pgs and udid = @udid";
                        cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
                        cmd.Parameters.Add(new MySqlParameter("udid", udid));
                        cmd.ExecuteNonQuery();
                        result = true;
                        client.Close();

                        Log.Write(cmd.CommandText + ", @pgs = " + pgs + ", @udid = " + udid, Log.Types.LOG);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "SetPickedUp(string, string)");
            }

            return result;
        }

        /// <summary>
        /// Delete results in the database
        /// </summary>
        /// <param name="pgs">pgs hash(partial pk)</param>
        /// <param name="udid">udid hash(partial pk)</param>
        /// <returns>returns true if success</returns>
        public bool Delete(string pgs, string udid)
        {
            bool result = false;

            try
            {
                if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128 && !string.IsNullOrEmpty(udid))
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"delete from results where pgs = @pgs and udid = @udid";

                        cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
                        cmd.Parameters.Add(new MySqlParameter("udid", udid));

                        cmd.ExecuteNonQuery();
                        result = true;
                        client.Close();

                        Log.Write(cmd.CommandText + ", @pgs = " + pgs + ", @udid = " + udid, Log.Types.LOG);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "Delete(string, string)");
            }

            return result;
        }

        /// <summary>
        /// Delete statuses from the database
        /// 
        /// Statuses must contain pgs and udid
        /// </summary>
        /// <returns>returns true if success</returns>
        public bool RemoveStatuses(List<ServiceShared.Models.Database.Status> statuses)
        {
            bool result = false;

            try
            {
                if (statuses != null && statuses.Count > 0)
                {
                    foreach(ServiceShared.Models.Database.Status status in statuses)
                    {
                        result = this.Delete(status.pgs, status.udid);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "RemoveStatuses(List<Status>)");
            }

            return result;
        }

        /// <summary>
        /// Removes all recors for the device
        /// </summary>
        /// <param name="udid">udid hash(partial pk)</param>
        /// <param name="verificator_hash">verificator_hash, that validate the client permission</param>
        /// <returns>returns true if success</returns>
        public bool DeleteDevice(string udid, string verificator_hash)
        {
            bool result = false;

            try
            {
                if (!string.IsNullOrEmpty(udid) && !string.IsNullOrEmpty(udid))
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"delete from device where udid = @udid and verificator_hash = @verificator_hash";
                        cmd.Parameters.Add(new MySqlParameter("udid", udid));
                        cmd.Parameters.Add(new MySqlParameter("verificator_hash", verificator_hash));
                        cmd.ExecuteNonQuery();
                        result = true;
                        client.Close();
                        Log.Write(cmd.CommandText + ", @udid = " + udid + ", @verificator_hash = " + verificator_hash, Log.Types.LOG);

                        /** Results will be removed by cascade action **/
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "Delete(string, string)");
            }

            return result;
        }

        /// <summary>
        /// Updates verificator hash for the device
        /// </summary>
        /// <param name="udid">udid hash(partial pk)</param>
        /// <param name="pin">client pin</param>
        /// <param name="old_verificator_hash">old verificator hash</param>
        /// <param name="new_verificator_hash">new verificator hash</param>
        /// <returns>returns true if success</returns>
        public bool UpdateVerificatorHash(string udid, string pin, string old_verificator_hash, string new_verificator_hash)
        {
            bool result = false;

            try
            {
                if (!string.IsNullOrEmpty(udid) &&
                    !string.IsNullOrEmpty(old_verificator_hash) && old_verificator_hash.Length == 128 &&
                    !string.IsNullOrEmpty(new_verificator_hash) && new_verificator_hash.Length == 128 &&
                    !string.IsNullOrEmpty(pin) && pin.Length == 5)
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"update device 
                                                        set verificator_hash = @new_verificator_hash, pin = @pin
                                                    where  
                                                        udid = @udid and 
                                                        verificator_hash = @old_verificator_hash";

                        cmd.Parameters.Add(new MySqlParameter("new_verificator_hash", new_verificator_hash));
                        cmd.Parameters.Add(new MySqlParameter("pin", pin));
                        cmd.Parameters.Add(new MySqlParameter("udid", udid));
                        cmd.Parameters.Add(new MySqlParameter("old_verificator_hash", old_verificator_hash));
                        cmd.ExecuteNonQuery();
                        result = true;
                        client.Close();

                        Log.Write(cmd.CommandText + ", @new_verificator_hash = " + new_verificator_hash +
                                                    ", @new_verificator_hash = " + old_verificator_hash + 
                                                    ", @udid = " + udid, Log.Types.LOG);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "UpdateVerificatorHash(string, string, string)");
            }

            return result;
        }

        /// <summary>
        /// Updates device token for client
        /// </summary>
        /// <param name="udid">udid hash(partial pk)</param>
        /// <param name="verificator_hash">client verificator_hash</param>
        /// <param name="device_token">device_token that should be updated</param>
        /// <returns>returns true if success</returns>
        public bool UpdateDeviceToken(string udid, string verificator_hash, string device_token)
        {
            bool result = false;

            try
            {
                if (!string.IsNullOrEmpty(udid) &&
                    !string.IsNullOrEmpty(verificator_hash) && verificator_hash.Length == 128 &&
                    !string.IsNullOrEmpty(device_token))
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"update device 
                                                        set device_token = @device_token
                                                    where  
                                                        udid = @udid and 
                                                        verificator_hash = @verificator_hash";

                        cmd.Parameters.Add(new MySqlParameter("device_token", device_token));
                        cmd.Parameters.Add(new MySqlParameter("udid", udid));
                        cmd.Parameters.Add(new MySqlParameter("verificator_hash", verificator_hash));
                        cmd.ExecuteNonQuery();
                        result = true;
                        client.Close();

                        Log.Write(cmd.CommandText + ", @device_token = " + device_token +
                                                    ", @udid = " + udid +
                                                    ", @verificator_hash = " + verificator_hash, Log.Types.LOG);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "UpdateDeviceToken(string, string, string)");
            }

            return result;
        }

        /// <summary>
        /// Check if device with verificator_hash has a record in the database
        /// </summary>
        //  <param name="udid">udid hash(partial pk)</param>
        /// <param name="verificator_hash">verificator_hash, that validate the client permission</param>
        /// <returns>returns true if some of records could be found else false</returns>
        public bool DeviceHasResults(string udid, string verificator_hash)
        {
            bool result = false;

            try
            {
                using (MySqlConnection client = this._DbContext.CreateConnection())
                {
                    client.Open();

                    MySqlCommand cmd = client.CreateCommand();
                    cmd.CommandText = @"select 
                                            count(*) cnt 
                                        from 
                                            results r 
                                        inner join 
                                            device d on d.udid = r.udid
                                        where 
                                            r.udid = @udid and 
                                            d.verificator_hash = @verificator_hash";

                    cmd.Parameters.Add(new MySqlParameter("udid", udid));
                    cmd.Parameters.Add(new MySqlParameter("verificator_hash", verificator_hash));
                    cmd.ExecuteNonQuery();

                    string res = cmd.ExecuteScalar().ToString();

                    if (!string.IsNullOrEmpty(res))
                    {
                        int r = 0;

                        if (Int32.TryParse(res, out r))
                        {
                            result = (r > 0);
                        }
                    }

                    client.Close();

                    Log.Write(cmd.CommandText + ", @udid = " + udid + ", @verificator_hash = " + verificator_hash, Log.Types.LOG);
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex, "ServiceOutside.Database.Controllers.Results", "DeviceHasResults(string, string)");
            }

            return result;
        }

        /// <summary>
        /// Returns results by udid
        /// </summary>
        ///  <param name="udid">udid hash(partial pk)</param>
        /// <param name="verificator_hash">verificator_hash, that validate the client permission</param>
        /// <returns>returns true if some of records could be found else false</returns>
        public List<ServiceShared.Models.Database.Results> GetDeviceResults(string udid, string verificator_hash)
        {
            List<ServiceShared.Models.Database.Results> result = null;

            try
            {
                using (MySqlConnection client = this._DbContext.CreateConnection())
                {
                    client.Open();

                    MySqlCommand cmd = client.CreateCommand();
                    cmd.CommandText = @"select
                                                    r.pgs,
                                                    r.pgs_hash,
                                                    d.udid,
                                                    d.device_token,
                                                    d.device_type,
                                                    r.client_public_key,
                                                    r.server_public_key,
                                                    r.file_checksum,
                                                    r.available,
                                                    r.available_ts,
                                                    r.picked_up,
                                                    r.picked_up_ts,
                                                    r.notified,
                                                    r.notified_ts,
                                                    d.verificator_hash,
                                                    d.pin,
                                                    r.status,
                                                    r.modified,
                                                    r.created,
                                                    r.not_found_counter,
                                                    r.not_found_ts,
                                                    r.notification_counter 
                                            from
                                                    device d
                                            inner join
                                                    results r on r.udid = d.udid
                                            where
                                                    d.udid = @udid and
                                                    d.verificator_hash = @verificator_hash";


                    cmd.Parameters.Add(new MySqlParameter("udid", udid));
                    cmd.Parameters.Add(new MySqlParameter("verificator_hash", verificator_hash));

                    MySqlDataReader reader = cmd.ExecuteReader();

                    if (reader != null && reader.HasRows)
                    {
                        result = new List<Models.Database.Results>();

                        while (reader.Read())
                        {
                            Models.Database.Results dbResults = this.ReadResults(reader);

                            if (dbResults != null)
                            {
                                result.Add(dbResults);
                            }
                        }
                    }

                    client.Close();
                    Log.Write(cmd.CommandText + ", @udid = " + udid + ", @verificator_hash = " + verificator_hash, Log.Types.LOG);
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex, "ServiceOutside.Database.Controllers.Results", "GetDeviceResults(string, string)");
            }

            return result;
        }

        /// <summary>
        /// Deletes not mapped results for x old days from the database
        /// </summary>
        /// <param name="oldDays">number of old days</param>
        /// <returns>true if success</returns>
        public bool CleanUp(int oldDays)
        {
            bool result = false;

            try
            {
                if (oldDays > 1)
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = "delete from results where datediff(now(), created) > @olddays";
                        cmd.Parameters.Add(new MySqlParameter("olddays", oldDays));
                        cmd.ExecuteNonQuery();
                        result = true;
                        client.Close();

                        Log.Write(cmd.CommandText + ", @olddays = " + oldDays, Log.Types.LOG);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex, "ServiceOutside.Database.Controllers.Results", "CleanUpNotMapped(int)");
            }

            return result;
        }

        /// <summary>
        /// Count opened reults(not pickedup) for the udid
        /// </summary>
        /// <param name="results">Results with UDID and IMEI</param>
        /// <returns></returns>
        public int CountOpenedRequest(Models.Database.Results results)
        {
            int result = 0;

            try
            {
                using (MySqlConnection client = this._DbContext.CreateConnection())
                {
                    client.Open();
                    
                    MySqlCommand cmd = client.CreateCommand();
                    cmd.CommandText = @"select 
                                            count(*) cnt 
                                        from 
                                            device d
                                        inner join 
                                            results r on r.udid = d.udid
                                        where 
                                           d.udid = @udid and 
                                           d.verificator_hash = @verificator_hash and
                                           r.picked_up <> 1";


                    cmd.Parameters.Add(new MySqlParameter("udid", results.UDID));
                    cmd.Parameters.Add(new MySqlParameter("verificator_hash", results.VerificationHash));
                    cmd.ExecuteNonQuery();

                    string res = cmd.ExecuteScalar().ToString();

                    if(!string.IsNullOrEmpty(res))
                    {
                        int r = 0;

                        if(Int32.TryParse(res, out r))
                        {
                            result = r;
                        }
                    }

                    client.Close();

                    Log.Write(cmd.CommandText + "@udid = " + results.UDID +
                                                "@verificator_hash = " + results.VerificationHash, Log.Types.LOG);
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex, "ServiceOutside.Database.Controllers.Results", "CountOpenedRequest(Models.Results)");
            }

            return result;
        }

        /// <summary>
        /// Returns all of opened(still not pickedup) pgs´s requests for the device
        /// </summary>
        /// <returns></returns>
        public List<Status> GetOpened(string udid, string verificator_hash, int maxOpenedRequests = 10, int maxNotFoundTries = 2)
        {
            List<Status> result = null;

            try
            {
                using (MySqlConnection client = this._DbContext.CreateConnection())
                {
                    client.Open();

                    MySqlCommand cmd = client.CreateCommand();
                    cmd.CommandText = @"select
                                                    r.pgs,
                                                    r.pgs_hash,
                                                    d.udid,
                                                    d.device_token,
                                                    d.device_type,
                                                    r.client_public_key,
                                                    r.server_public_key,
                                                    r.file_checksum,
                                                    r.available,
                                                    r.available_ts,
                                                    r.picked_up,
                                                    r.picked_up_ts,
                                                    r.notified,
                                                    r.notified_ts,
                                                    d.verificator_hash,
                                                    r.status,
                                                    r.modified,
                                                    r.created,
                                                    r.not_found_counter,
                                                    r.not_found_ts,
                                                    r.notification_counter
                                            from
                                                    device d
                                            inner join
                                                    results r on r.udid = d.udid
                                            where
                                                    d.udid = @udid and 
                                                    d.verificator_hash = @verificator_hash and
                                                    r.picked_up <> 1
                                            limit " + maxOpenedRequests;

                    cmd.Parameters.Add(new MySqlParameter("udid", udid));
                    cmd.Parameters.Add(new MySqlParameter("verificator_hash", verificator_hash));

                    MySqlDataReader reader = cmd.ExecuteReader();

                    if (reader != null && reader.HasRows)
                    {
                        if(result == null)
                        {
                            result = new List<Status>();
                        }

                        while(reader.Read())
                        {
                            Models.Database.Results results = this.ReadResults(reader);

                            if(results != null)
                            {
                                if(results.Status == ResultsStatus.NOT_FOUND)
                                {
                                    Log.Debug("Found not found results try " + results.NotFoundCounter + "/" + maxNotFoundTries);

                                    /** send only not found results if max tries has been reached **/
                                    if (results.NotFoundCounter >= maxNotFoundTries)
                                    {
                                        result.Add(new Status(results));
                                    }
                                }
                                else
                                {
                                    result.Add(new Status(results));
                                }
                            }
                        }
                    }

                    client.Close();

                    Log.Write(cmd.CommandText + ", @udid = " + udid + 
                                                ", @verificator_hash = " + verificator_hash, Log.Types.LOG);
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex, "ServiceOutside.Database.Controllers.Results", "GetOpened(string, string, int)");
            }

            return result;
        }

        /// <summary>
        /// Returns pin of device
        /// </summary>
        /// <returns>returns pin of device </returns>
        public string GetPIN(string udid, string verificator_hash)
        {
            string result = null;

            try
            {
                using (MySqlConnection client = this._DbContext.CreateConnection())
                {
                    client.Open();

                    MySqlCommand cmd = client.CreateCommand();
                    cmd.CommandText = @"select
                                                    d.pin
                                            from
                                                    device d
                                            where
                                                    d.udid = @udid and 
                                                    d.verificator_hash = @verificator_hash";

                    cmd.Parameters.Add(new MySqlParameter("udid", udid));
                    cmd.Parameters.Add(new MySqlParameter("verificator_hash", verificator_hash));

                    MySqlDataReader reader = cmd.ExecuteReader();

                    if (reader != null && reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            result = (string)reader["pin"];
                        }
                    }

                    client.Close();

                    Log.Write(cmd.CommandText + ", @udid = " + udid +
                                                ", @verificator_hash = " + verificator_hash, Log.Types.LOG);
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex, "ServiceOutside.Database.Controllers.Results", "GetPIN(string, string)");
            }

            return result;
        }

        /// <summary>
        /// Reads results from mysql data reader
        /// </summary>
        /// <param name="reader">MySqlDataReader with current connection context</param>
        /// <returns>Returns Results object</returns>
        private Models.Database.Results ReadResults(MySqlDataReader reader)
        {
            Models.Database.Results result = null;

            try
            {
                if (reader["pgs"] != null && reader["pgs"] != DBNull.Value)
                {
                    result = new Models.Database.Results();
                    result.PGS = (string)reader["pgs"];
                    result.PGS_HASH = (string)reader["pgs_hash"];
                    result.UDID = (string)reader["udid"];
                    result.DeviceToken = (string)reader["device_token"];
                    result.ClientPublicKey = (string)reader["client_public_key"];
                   result.ServerPublicKey = (reader["server_public_key"] != null && reader["server_public_key"] != DBNull.Value) ? (string)reader["server_public_key"] : null;
                    result.FileChecksum = (reader["file_checksum"] != null && reader["file_checksum"] != DBNull.Value) ? (string)reader["file_checksum"] : null;
                    result.Available = (reader["available"] != null && reader["available"] != DBNull.Value && Convert.ToInt32(reader["available"].ToString()) == 1);
                    result.AvailableTS = ((reader["available_ts"] != null && reader["available_ts"] != DBNull.Value) ? (DateTime)reader["available_ts"] : null);
                    result.PickedUp = (reader["picked_up"] != null && reader["picked_up"] != DBNull.Value && Convert.ToInt32(reader["picked_up"].ToString()) == 1);
                    result.PickedUpTS = (reader["picked_up_ts"] != null && reader["picked_up_ts"] != DBNull.Value) ? (DateTime)reader["picked_up_ts"] : null;
                    result.Notified = (reader["notified"] != null && reader["notified"] != DBNull.Value && Convert.ToInt32(reader["notified"].ToString()) == 1);
                    result.NotifiedTS = (reader["notified_ts"] != null && reader["notified_ts"] != DBNull.Value) ? (DateTime)reader["notified_ts"] : null;
                    result.VerificationHash = (string)reader["verificator_hash"];
                    result.Modified = (reader["modified"] != null && reader["modified"] != DBNull.Value) ? (DateTime)reader["modified"] : null;
                    result.Created = (DateTime)reader["created"];
                   
                    string device_type = ((reader["device_type"] != null && reader["device_type"] != DBNull.Value) ? (string)reader["device_type"] : null);

                    DeviceTypes deviceType = DeviceTypes.NONE;

                    if (!string.IsNullOrEmpty(device_type) && Enum.TryParse(device_type, out deviceType) && deviceType != DeviceTypes.NONE)
                    {
                        result.DeviceType = deviceType;
                    }

                    string status = ((reader["status"] != null && reader["status"] != DBNull.Value) ? (string)reader["status"] : null);

                    ResultsStatus outStatus = ResultsStatus.NONE;

                    if (!string.IsNullOrEmpty(status) && Enum.TryParse(status, out outStatus) && outStatus != ResultsStatus.NONE)
                    {
                        result.Status = outStatus;
                    }

                    result.NotFoundCounter = (reader["not_found_counter"] != null && reader["not_found_counter"] != DBNull.Value ?Convert.ToInt32(reader["not_found_counter"].ToString()) : 0);

                    result.NotFoundTS = (reader["not_found_ts"] != null && reader["not_found_ts"] != DBNull.Value) ? (DateTime)reader["not_found_ts"] : null;

                    result.NotificationCounter = (reader["notification_counter"] != null && reader["notification_counter"] != DBNull.Value ? Convert.ToInt32(reader["notification_counter"].ToString()) : 0);
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "ReadResults(MySqlDataReader)");
            }

            return result;
        }

        /// <summary>
        /// Reads device from mysql data reader
        /// </summary>
        /// <param name="reader">MySqlDataReader with current connection context</param>
        /// <returns>Returns device object</returns>
        private Device ReadDevice(MySqlDataReader reader)
        {
            Device result = null;

            try
            {
                if (reader["udid"] != null && reader["udid"] != DBNull.Value)
                {
                    result = new Device();
                    result.UDID = (string)reader["udid"];
                    result.DeviceToken = (string)reader["device_token"];
                    result.VerificationHash = (string)reader["verificator_hash"];
                    result.ClientPublicKey = (string)reader["public_key"];
                    result.PIN = (string)reader["pin"];
                    result.PatHash = ((reader["pat_hash"] != null && reader["pat_hash"] != DBNull.Value) ? (string)reader["pat_hash"] : null);

                    string device_type = ((reader["device_type"] != null && reader["device_type"] != DBNull.Value) ? (string)reader["device_type"] : null);

                    Device.DeviceTypes deviceType = Device.DeviceTypes.NONE;

                    if (!string.IsNullOrEmpty(device_type) && Enum.TryParse(device_type, out deviceType) && deviceType != Device.DeviceTypes.NONE)
                    {
                        result.DeviceType = deviceType;
                    }

                    result.Modified = (reader["modified"] != null && reader["modified"] != DBNull.Value) ? (DateTime)reader["modified"] : null;
                    result.Created = (DateTime)reader["created"];
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "ReadDevice(MySqlDataReader)");
            }

            return result;
        }
    }
}