using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using PetaPoco.Business;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Database;
using Sony.Filtr.PlaylistSynchronization.Data;

namespace Sony.Filtr.PlaylistSynchronization
{
    public class SynchronizationTrackManager
    {
        public List<SynchronizationTrack> GetSynchronizedTracks(List<string> ISRCs, ServiceType serviceType)
        {
            if (!ISRCs.Any())
            {
                return new List<SynchronizationTrack>();
            }

            var synchronizedTracks = PetaPocoRepository.ReadOnlyInstance.Fetch<SynchronizationTrack>("WHERE ServiceType = @serviceType AND isrc IN (@isrcs)", new
            {
                serviceType = serviceType,
                isrcs = ISRCs,
            });
            return synchronizedTracks;
        }

        private async Task<SynchronizationTrack> SaveSynchronizationTrackAsync(SynchronizationTrack track)
        {
            var existingTracks = PetaPocoRepository.ReadOnlyInstance
                .Fetch<SynchronizationTrack>("WHERE ServiceType=@0 AND isrc=@1", track.ServiceType, track.ISRC)
                .OrderByDescending(t => t.MatchDate)
                .ToList();

            if (existingTracks.Any() == false)
            {
                track.Id = PetaPocoRepository.Instance.Insert(track);
            }
            else
            {
                using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
                {
                    using (var tran = await conn.BeginTransactionAsync())
                    {
                        await UpdateTrackMatchDateAsync(conn, tran, existingTracks.First());

                        var tracksToRemove = existingTracks.Skip(1).ToList();
                        if (tracksToRemove.Any())
                        {
                            await DeleteTracksByIdAsync(conn, tran, tracksToRemove);
                        }

                        tran.Commit();
                    }
                }
            }

            return track;
        }

        private async Task DeleteTracksByIdAsync(MySqlConnection connection, MySqlTransaction transaction, List<SynchronizationTrack> tracksToRemove)
        {
            string deleteSql = $@"
DELETE FROM tblPlaylistSynchronizationTrack WHERE Id IN ({String.Join(",", tracksToRemove.Select(t => t.Id))})";

            using (var cmd = new MySqlCommand(deleteSql, connection, transaction))
            {
                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task<SynchronizationTrack> SaveSynchronizationTrackAsync(string isrc, ServiceType serviceType, string trackId)
        {
            return await SaveSynchronizationTrackAsync(new SynchronizationTrack()
            {
                ISRC = isrc,
                ServiceType = serviceType,
                TrackId = trackId,
                MatchDate = DateTime.UtcNow,
            });
        }

        private async Task UpdateTrackMatchDateAsync(MySqlConnection connection, MySqlTransaction transaction, SynchronizationTrack track)
        {
            string updateSql = $@"
UPDATE tblPlaylistSynchronizationTrack SET 
    MatchDate = @matchDate
WHERE Id = @id";
            using (var cmd = new MySqlCommand(updateSql, connection, transaction))
            {
                cmd.Parameters.AddWithValue("@id", track.Id);
                cmd.Parameters.AddWithValue("@matchDate", track.MatchDate);

                await cmd.ExecuteNonQueryAsync();
            }
        }
    }
}