using System.Collections.Generic;
using System.Linq;
using PetaPoco;
using PetaPoco.Business;

namespace Sony.Filtr.Globalization.v3
{
    public class MarketGlobalizationFactory
    {
        internal MarketGlobalizationDbValue GetTranslation(string key, string context, int apiConsumerId, int applicationId)
        {
            return PetaPocoRepository.ReadOnlyInstance.SingleOrDefaultWithSql<MarketGlobalizationDbValue>("WHERE `Key` = @0 AND Context = @1 AND ApiConsumerId = @2 AND ApplicationId = @3", key, context, apiConsumerId, applicationId);
        }

        internal MarketGlobalizationKey GetKey(string key, string context, int apiConsumerId)
        {
            return PetaPocoRepository.ReadOnlyInstance.SingleOrDefaultWithSql<MarketGlobalizationKey>("WHERE `Key` = @0 AND Context = @1 AND ApiConsumerId = @2", key, context, apiConsumerId);
        }

        internal void SetTranslation(string key, string context, int apiConsumerId, int applicationId, string globalizationValue)
        {
            if (GetTranslation(key, context, apiConsumerId, applicationId) != null)
            {
                var sql = Sql.Builder.Append("UPDATE tblMarketGlobalizationValue v SET v.Value = @0 WHERE v.Key = @1 AND v.Context = @2 AND v.ApiConsumerId = @3 AND v.ApplicationId = @4", globalizationValue, key, context, apiConsumerId, applicationId);
                PetaPocoRepository.Instance.Execute(sql);
            }
            else
            {
                var dbValue = new MarketGlobalizationDbValue()
                {
                    Key = key,
                    Context = context,
                    ApiConsumerId = apiConsumerId,
                    ApplicationId = applicationId,
                    Value = globalizationValue
                };

                PetaPocoRepository.Instance.Insert(dbValue);
            }

        }

        internal Dictionary<MarketGlobalizationKey, Dictionary<int, MarketGlobalizationValue>> GetTranslations()
        {
            var keys = PetaPocoRepository.ReadOnlyInstance.Fetch<MarketGlobalizationKey>();
            var values = PetaPocoRepository.ReadOnlyInstance.Fetch<MarketGlobalizationDbValue>();

            return keys.ToDictionary(key => key,
                key =>
                    values.Where(val => val.Key == key.Key && val.Context == key.Context && val.ApiConsumerId == key.ApiConsumerId )
                        .ToDictionary(val => val.ApplicationId, val => new MarketGlobalizationValue(val)));
        }



        internal List<MarketGlobalizationKey> GetKeys(string context = null)
        {
            if(!string.IsNullOrWhiteSpace(context))
            {
                return PetaPocoRepository.ReadOnlyInstance.Fetch<MarketGlobalizationKey>("WHERE Context=@0");
            }
            else
            {
                return PetaPocoRepository.ReadOnlyInstance.Fetch<MarketGlobalizationKey>();
            }
        }

        internal void SetKey(MarketGlobalizationKey marketGlobalizationKey)
        {
            if (GetKey(marketGlobalizationKey.Key, marketGlobalizationKey.Context, marketGlobalizationKey.ApiConsumerId) != null)
            {
                var sql =
                    Sql.Builder.Append(
                        "UPDATE tblMarketGlobalizationKey k SET k.DefaultValue = @0, k.Description = @1 WHERE k.Key = @2 AND k.Context = @3 AND k.ApiConsumerId = @4",
                        marketGlobalizationKey.DefaultValue, marketGlobalizationKey.Description,
                        marketGlobalizationKey.Key, marketGlobalizationKey.Context,
                        marketGlobalizationKey.ApiConsumerId);

                PetaPocoRepository.Instance.Execute(sql);
            }
            else
            {
                PetaPocoRepository.Instance.Insert(marketGlobalizationKey);
            }
        }

        internal void DeleteKey(MarketGlobalizationKey key)
        {
            PetaPocoRepository.Instance.Delete<MarketGlobalizationKey>(key);

        }

        internal void DeleteValue(MarketGlobalizationDbValue translation)
        {
            PetaPocoRepository.Instance.Delete<MarketGlobalizationDbValue>(translation);

        }
    }
}