using System.Collections.Generic;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Sony.Filtr.Contracts.Abstractions;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Core.LastModified;
using Sony.Filtr.Database;
using Sony.Filtr.DistributedCaching;

namespace Sony.Filtr.Core.SpotifyRegion
{
    public class SpotifyRegionManager : ISpotifyRegionManager
    {
        private readonly DistributedCacheHandler _distributedCacheHandler;
        private readonly LastModifiedManager _lastModifiedManager;
        const string CacheKey = "SpotifyRegionManager-GetAvailableRegions";

        public SpotifyRegionManager(DistributedCacheHandler distributedCacheHandler, LastModifiedManager lastModifiedManager)
        {
            _distributedCacheHandler = distributedCacheHandler;
            _lastModifiedManager = lastModifiedManager;
        }

        public async Task<List<string>> GetAvailableRegionsAsync()
        {
            var regions = _distributedCacheHandler.Get(CacheKey) as List<string>;
            if (regions == null)
            {
                regions = new List<string>();

                using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
                {
                    MySqlCommand cmd = new MySqlCommand("SELECT strRegion FROM tblSpotifyRegion", conn);

                    var reader = await cmd.ExecuteReaderAsync();
                    while (await reader.ReadAsync())
                    {
                        regions.Add(reader.GetString(0));
                    }
                    reader.Close();
                }
                _distributedCacheHandler.Put(CacheKey, regions);
            }
            
            return regions;
        }

        public async Task AddRegionAsync(string region)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                MySqlCommand cmd = new MySqlCommand("INSERT IGNORE INTO tblSpotifyRegion (strRegion) VALUES (@region)",
                    conn);
                cmd.Parameters.AddWithValue("@region", region);
                await cmd.ExecuteNonQueryAsync();
            }

            ClearCache();

            await _lastModifiedManager.SetLastModifyDateAsync(null, EntityType.SpotifyRegions);
        }

        public async Task DeleteRegionAsync(string region)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                MySqlCommand cmd = new MySqlCommand("DELETE FROM tblSpotifyRegion WHERE strRegion = @region", conn);
                cmd.Parameters.AddWithValue("@region", region);
                await cmd.ExecuteNonQueryAsync();
            }

            ClearCache();

            await _lastModifiedManager.SetLastModifyDateAsync(null, EntityType.SpotifyRegions);
        }

        private void ClearCache()
        {
            _distributedCacheHandler.Remove(CacheKey);
        }

    }
}