using System.Collections.Generic;
using System.Globalization;
using MySqlConnector;
using Sony.Filtr.Database;
using Sony.Filtr.Globalization;

namespace Globalization
{
    public class GlobalizationFactory2
    {
        internal void SetTranslation(int keyId, CultureInfo culture, string value)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO tblGlobalizationValues" +
                                  " (intGlobalizationKeyId, strCulture, strValue) VALUES (@intKey, @strCulture, @strValue) ON DUPLICATE KEY UPDATE strValue = @strValue";
                cmd.Parameters.AddWithValue("@intKey", keyId);
                cmd.Parameters.AddWithValue("@strCulture", culture.Name);
                cmd.Parameters.AddWithValue("@strValue", value);
                cmd.ExecuteNonQuery();
            }
        }

        internal List<GlobalizationValue> GetTranslations()
        {
            var translations = new List<GlobalizationValue>();
            using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT k.intId, k.strKey, v.strCulture, v.strValue FROM tblGlobalizationValues AS v INNER JOIN tblGlobalizationKeys AS k ON v.intGlobalizationKeyId = k.intId";

                using(var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int keyId = reader.GetInt32(0);
                        string key = reader.GetString(1);
                        string culture = reader.GetString(2);
                        string value = null;
                        if (!reader.IsDBNull(3))
                            value = reader.GetString(3);

                        CultureInfo cultureInfo = CultureInfo.GetCultureInfo(culture);
                        var globalizationKey = new GlobalizationKey() { Id = keyId, Key = key };
                        var globalizationValue = new GlobalizationValue()
                        {
                            CultureInfo = cultureInfo,
                            GlobalizationKey = globalizationKey,
                            Value = value
                        };

                        translations.Add(globalizationValue);
                    }
                }
            }

            return translations;
        }



        internal List<GlobalizationKey> GetKeys()
        {
            List<GlobalizationKey> keys = new List<GlobalizationKey>();
            using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT intId, strKey FROM tblGlobalizationKeys";
                using(var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        string key = reader.GetString(1);
                        keys.Add(new GlobalizationKey() { Id = id, Key = key });
                    }
                }
            }
            return keys;
        }

        internal GlobalizationKey AddKey(string key)
        {
            GlobalizationKey globalizationKey;
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO tblGlobalizationKeys (strKey) VALUES (@strKey)";
                cmd.Parameters.AddWithValue("@strKey", key);
                cmd.ExecuteNonQuery();

                globalizationKey = new GlobalizationKey() {Id = (int)cmd.LastInsertedId, Key = key};
            }
            return globalizationKey;
        }

        internal void DeleteKey(GlobalizationKey key)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM tblGlobalizationKeys WHERE intId=@intKeyId";
                cmd.Parameters.AddWithValue("@intKeyId", key.Id);
                cmd.ExecuteNonQuery();
            }           
        }
    }
}