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

namespace Sony.Filtr.Core.PromotedTracks
{
    public class PromotedTracksFactory
    {
        internal async Task<List<PromotedTrack>> GetPromotedTracksAsync(Application app)
        {
            List<PromotedTrack> promotedTracks = new List<PromotedTrack>();
            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                MySqlCommand cmd = new MySqlCommand("SELECT intId, intApplicationID, intOrder, strSpotifyUri, strArtistName, strTrackName, strTerritoryCodes, dblDanceability, dblEnergy, dblHotness, dblLoudness, intMode, dblTempo, dblArtistFamiliarity, dblArtistHotness, intFetchCodeVersion, datFetchDate, strSongTypes, intServiceType, intDeezerId, intDuration  " +
                                                    "FROM tblPromotedTrack WHERE intApplicationID=@intApplicationId", conn);
                cmd.Parameters.AddWithValue("@intApplicationID", app.ID);

                var reader = await cmd.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    var promotedTrack = new PromotedTrack();
                    promotedTrack.Id = reader.GetInt32(0);
                    promotedTrack.ApplicationId = reader.GetInt32(1);

                    var serviceType = reader.GetInt32(18);
                    promotedTrack.ServiceType = (ServiceType) Enum.Parse(typeof (ServiceType), serviceType.ToString());
                    promotedTrack.ExtendedTrack = new ExtendedTrack()
                    {
                        SpotifyLink = !reader.IsDBNull(3) ? new SpotifyLink(reader.GetString(3)) : null,
                        ArtistName = !reader.IsDBNull(4) ? reader.GetString(4) : string.Empty,
                        Title = !reader.IsDBNull(5) ? reader.GetString(5) : string.Empty,
                        Territories = !reader.IsDBNull(6) ? reader.GetString(6).Split(',').ToList() : new List<string>(),
                        Danceability = !reader.IsDBNull(7) ? reader.GetDouble(7) : (double?) null,
                        Energy = !reader.IsDBNull(8) ? reader.GetDouble(8) : (double?) null,
                        Hotness = !reader.IsDBNull(9) ? reader.GetDouble(9) : (double?) null,
                        Loudness = !reader.IsDBNull(10) ? reader.GetDouble(10) : (double?) null,
                        Mode = !reader.IsDBNull(11) ? reader.GetInt32(11) : (int?) null,
                        Tempo = !reader.IsDBNull(12) ? reader.GetDouble(12) : (double?) null,
                        ArtistFamiliarity = !reader.IsDBNull(13) ? reader.GetDouble(13) : (double?) null,
                        ArtistHotnesss = !reader.IsDBNull(14) ? reader.GetDouble(14) : (double?) null,
                        FetchCodeVersion = !reader.IsDBNull(15) ? reader.GetInt32(15) : (int?) null,
                        FetchDate = !reader.IsDBNull(16) ? DateTime.SpecifyKind(reader.GetDateTime(16), DateTimeKind.Utc) : (DateTime?)null,
                        SongType = (!reader.IsDBNull(17) ? reader.GetString(17) : string.Empty).Split(',').ToList(),
                        DeezerId = (!reader.IsDBNull(19) ? reader.GetInt64(19) : (long?)null),
                        Duration = (!reader.IsDBNull(20) ? reader.GetInt32(20) : 0),
                    };
                    promotedTracks.Add(promotedTrack);
                }

                await conn.CloseAsync();
            }

            if (promotedTracks.Any())
                promotedTracks = await GetTagsAsync(promotedTracks);

            return promotedTracks;
        }

        internal async Task SetPromotedTracksAsync(Application application, List<PromotedTrack> tracks)
        {
            using (MySqlConnection conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var tran = await conn.BeginTransactionAsync();
                MySqlCommand delCmd = new MySqlCommand("DELETE FROM tblPromotedTrack WHERE intApplicationId=@intApplicationId", conn, tran);
                delCmd.Parameters.AddWithValue("@intApplicationId", application.ID);
                delCmd.ExecuteNonQuery();

                foreach (var track in tracks)
                {
                    //Insert track.
                    MySqlCommand cmd = new MySqlCommand("INSERT INTO tblPromotedTrack (intApplicationID, intOrder, strSpotifyUri, strArtistName, strTrackName, strTerritoryCodes, dblDanceability, dblEnergy, dblHotness, dblLoudness, intMode, dblTempo, dblArtistFamiliarity, dblArtistHotness, intFetchCodeVersion, datFetchDate, strSongTypes, intServiceType, intDeezerId, intDuration) " +
                                                        "VALUES (@intApplicationID, @intOrder, @strSpotifyUri, @strArtistName, @strTrackName, @strTerritoryCodes, @dblDanceability, @dblEnergy, @dblHotness, @dblLoudness, @intMode, @dblTempo, @dblArtistFamiliarity, @dblArtistHotness, @intFetchCodeVersion, @datFetchDate, @strSongTypes, @intServiceType, @intDeezerId, @intDuration)", conn, tran);
                    cmd.Parameters.AddWithValue("@intApplicationID", application.ID);
                    cmd.Parameters.AddWithValue("@intOrder", 1); //TODO:
                    cmd.Parameters.AddWithValue("@strSpotifyUri", track.ExtendedTrack.SpotifyLink?.Uri);
                    cmd.Parameters.AddWithValue("@strArtistName", track.ExtendedTrack.ArtistName);
                    cmd.Parameters.AddWithValue("@strTrackName", track.ExtendedTrack.Title);
                    cmd.Parameters.AddWithValue("@strTerritoryCodes", (track.ExtendedTrack.Territories != null) ? string.Join(",", track.ExtendedTrack.Territories) : string.Empty);
                    cmd.Parameters.AddWithValue("@dblDanceability", track.ExtendedTrack.Danceability);
                    cmd.Parameters.AddWithValue("@dblEnergy", track.ExtendedTrack.Energy);
                    cmd.Parameters.AddWithValue("@dblHotness", track.ExtendedTrack.Hotness);
                    cmd.Parameters.AddWithValue("@dblLoudness", track.ExtendedTrack.Loudness);
                    cmd.Parameters.AddWithValue("@intMode", track.ExtendedTrack.Mode);
                    cmd.Parameters.AddWithValue("@dblTempo", track.ExtendedTrack.Tempo);
                    cmd.Parameters.AddWithValue("@dblArtistFamiliarity", track.ExtendedTrack.ArtistFamiliarity);
                    cmd.Parameters.AddWithValue("@dblArtistHotness", track.ExtendedTrack.ArtistHotnesss);
                    cmd.Parameters.AddWithValue("@intFetchCodeVersion", track.ExtendedTrack.FetchCodeVersion);
                    cmd.Parameters.AddWithValue("@datFetchDate", track.ExtendedTrack.FetchDate);
                    cmd.Parameters.AddWithValue("@strSongTypes", (track.ExtendedTrack.SongType != null) ? string.Join(",", track.ExtendedTrack.SongType) : string.Empty);
                    cmd.Parameters.AddWithValue("@intServiceType", track.ServiceType);
                    cmd.Parameters.AddWithValue("@intDeezerId", track.ExtendedTrack.DeezerId);
                    cmd.Parameters.AddWithValue("@intDuration", track.ExtendedTrack.Duration);

                    await cmd.ExecuteNonQueryAsync();

                    track.Id = (int)cmd.LastInsertedId;

                    //Insert tags
                    await SetPromotedTrackTagsAsync(track, conn, tran);   
                }
                tran.Commit();
            }
        }

        private async Task SetPromotedTrackTagsAsync(PromotedTrack promotedTrack, MySqlConnection conn, MySqlTransaction tran)
        {
            MySqlCommand delCmd = new MySqlCommand("DELETE FROM tblPromotedTrackTag WHERE intPromotedTrackId=@intPromotedTrackId", conn, tran);
            delCmd.Parameters.AddWithValue("@intPromotedTrackId", promotedTrack.Id);
            await delCmd.ExecuteNonQueryAsync();

            if (promotedTrack.Tags != null && promotedTrack.Tags.Any())
            {
                var sql = "INSERT INTO tblPromotedTrackTag VALUES ";
                var paramValues = promotedTrack.Tags.Select(t => "(" + promotedTrack.Id + ",'" + MySqlHelper.EscapeString(t.Name) + "'," + t.Score + ")");
                sql += string.Join(",", paramValues);

                MySqlCommand cmd = new MySqlCommand(sql, conn);
                await cmd.ExecuteNonQueryAsync();
            }
        }

        private async Task<List<PromotedTrack>> GetTagsAsync(List<PromotedTrack> promotedTracks)
        {
            Dictionary<int, List<Tag>> promotedTrackTags = new Dictionary<int, List<Tag>>();
            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = $"SELECT intPromotedTrackId, strTag, intScore FROM tblPromotedTrackTag WHERE intPromotedTrackId IN ({string.Join(",", promotedTracks.Select(p => p.Id))})";
                var cmd = new MySqlCommand(sql, conn);
                var reader = await cmd.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    int promotedTrackId = reader.GetInt32(0);
                    var tag = new Tag() {Name = reader.GetString(1), Score = reader.GetInt32(2)};
                    if (promotedTrackTags.ContainsKey(promotedTrackId))
                        promotedTrackTags[promotedTrackId].Add(tag);
                    else
                        promotedTrackTags.Add(promotedTrackId, new List<Tag>() { tag });
                }

                reader.Close();
                await conn.CloseAsync();
            }

            foreach (var promotedTrack in promotedTracks)
            {
                if (promotedTrackTags.ContainsKey(promotedTrack.Id))
                    promotedTrack.Tags = promotedTrackTags[promotedTrack.Id];
            }

            return promotedTracks;
        }
    }
}