using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
using MySqlConnector;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Database;
using Sony.Filtr.PlaylistSynchronization.Data;
using Sony.Filtr.Utility;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.PlaylistSynchronization
{
    public class SynchronizationTrackManager
    {
        public async Task<SynchronizationTrack[]> GetSynchronizedTracksByIsrcAsync(IEnumerable<string> ISRCs, ServiceType serviceType, DateTime? datePivot = null)
        {
            if (!ISRCs.Any())
            {
                return Array.Empty<SynchronizationTrack>();
            }

            string sql = $@"
    SELECT * 
    FROM tblPlaylistSynchronizationTrack
    WHERE ServiceType = @serviceType AND isrc IN ({Maybe.ToCommaSeparated(ISRCs)})
        AND ((@datePivot IS NULL) OR MatchDate >= @datePivot)
";

            var tracks = new List<SynchronizationTrack>();

            using (var connection = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var command = new MySqlCommand(sql, connection);
                command.Parameters.AddWithValue("@serviceType", serviceType);
                command.Parameters.AddWithValue("@datePivot", datePivot);

                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        tracks.Add(ReadTrackFromReader(reader));
                    }
                }
            }

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

        private static SynchronizationTrack ReadTrackFromReader(MySqlDataReader reader)
        {
            return new SynchronizationTrack()
            {
                Id = reader.GetInt32("Id"),
                ISRC = reader.GetSafeString("ISRC"),
                MatchDate = reader.GetDateTime("MatchDate"),
                ServiceType = (ServiceType)reader.GetInt32("ServiceType"),
                Source = (TrackSource)reader.GetInt32("Source"),
                TrackId = reader.GetSafeString("TrackId")
            };
        }
        private async Task<SynchronizationTrack> SaveSynchronizationTrackAsync(SynchronizationTrack track)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using (var tran = await conn.BeginTransactionAsync())
                {
                    var deleteCommand = new MySqlCommand("DELETE FROM tblPlaylistSynchronizationTrack WHERE ServiceType = @serviceType AND isrc = @isrc", conn);
                    deleteCommand.Parameters.AddWithValue("@serviceType", track.ServiceType);
                    deleteCommand.Parameters.AddWithValue("@isrc", track.ISRC);

                    await deleteCommand.ExecuteNonQueryAsync();

                    var insertCommand = new MySqlCommand("INSERT INTO tblPlaylistSynchronizationTrack(ServiceType, ISRC, MatchDate, TrackId, Source) VALUES(@serviceType, @isrc, @matchDate, @trackId, @source);", conn);
                    insertCommand.Parameters.AddWithValue("@serviceType", track.ServiceType);
                    insertCommand.Parameters.AddWithValue("@isrc", track.ISRC);
                    insertCommand.Parameters.AddWithValue("@matchDate", track.MatchDate);
                    insertCommand.Parameters.AddWithValue("@trackId", track.TrackId);
                    insertCommand.Parameters.AddWithValue("@source", track.Source);

                    insertCommand.ExecuteNonQuery();

                    tran.Commit();
                }
            }

            return track;
        }
    }
}