using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using PetaPoco.Business;
using Sony.Filtr.Contracts.Abstractions;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.PlaylistSynchronization
{
    public class ServiceAccountManager : IServiceAccountManager
    {
        public ServiceAccount SaveServiceAccount(ServiceAccount serviceAccount)
        {
            return PetaPocoRepository.Instance.Upsert(serviceAccount);
        }

        public ServiceAccount AddServiceAccount(ServiceAccount serviceAccount)
        {
            return GetServiceAccount(PetaPocoRepository.Instance.Insert(serviceAccount));
        }

        public List<ServiceAccount> GetServiceAccounts()
        {
            return PetaPocoRepository.Instance.Fetch<ServiceAccount>();
        }

        public List<ServiceAccount> GetServiceAccounts(int applicationId)
        {
            return PetaPocoRepository.Instance.Fetch<ServiceAccount>(new PetaPoco.Sql("WHERE ApplicationId = @0", applicationId));
        }

        public void DeleteServiceAccount(ServiceAccount serviceAccount)
        {
            PetaPocoRepository.Instance.Delete<ServiceAccount>(serviceAccount.Id);
        }

        public ServiceAccount GetServiceAccount(int serviceAccountId)
        {
            return PetaPocoRepository.Instance.SingleOrDefault<ServiceAccount>(serviceAccountId);
        }

        public ServiceAccount GetServiceAccount(MusicService musicService, string userIdentifier)
        {
            return GetServiceAccount((int)musicService, userIdentifier);
        }
        public ServiceAccount GetServiceAccount(int musicServiceId, string userIdentifier)
        {
            //TODO: Db.
            return GetServiceAccounts().SingleOrDefault(a => a.UserIdentifier == userIdentifier && a.MusicServiceId == musicServiceId);
        }

        public async Task<Nullable<(string ClientId, string SecretId)>> GetServiceAccountSpotifyClientIdAndSecretAsync(int serviceAccountId)
        {
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = @"
SELECT k.ClientId, k.SecretId FROM tblSpotifyApiKeys k
LEFT JOIN tblServiceAccount acc ON k.Id = acc.SpotifyClientIdKey
WHERE acc.Id = @id; ";

                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@id", serviceAccountId);

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            string clientId = reader.GetString("ClientId");
                            string secretId = reader.GetString("SecretId");

                            if (String.IsNullOrWhiteSpace(clientId) || String.IsNullOrWhiteSpace(secretId)) { return null; }

                            return (clientId, secretId);
                        }

                        return null;
                    }
                }

            }

        }

        public async Task<string> AssignSpotifyApiClientIdToServiceAccountAsync(int serviceAccountId)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                const string sql = @"
UPDATE tblServiceAccount 
SET SpotifyClientIdKey = (SELECT Id from tblSpotifyApiKeys WHERE IsMain = 1 LIMIT 1) 
WHERE Id = @serviceAccountId;

SELECT ClientId FROM tblSpotifyApiKeys WHERE Id = (SELECT SpotifyClientIdKey FROM tblServiceAccount WHERE Id = @serviceAccountId);
";

                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@serviceAccountId", serviceAccountId);

                    string clientId = (string)(await cmd.ExecuteScalarAsync());

                    return clientId;
                }

            }
        }
    }
}