using System;
using System.Collections.Generic;
using System.Linq;
using MusicMatch.Core.Data;
using MySql.Data.MySqlClient;
using Sony.Filtr.Database;
using Spotifony.Core.Data;

namespace Sony.Filtr.PlaylistGeneration.Factory
{
    class GeneratedPlaylistFactory
    {
        internal GeneratedPlaylist AddGeneratedPlaylist(GeneratedPlaylist generatedPlaylist)
        {
            using (MySqlConnection conn = new MySqlConnection(DatabaseHandler.GetConnectionString()))
            {
                conn.Open();

                MySqlCommand cmd = new MySqlCommand("INSERT INTO tblGeneratedPlaylist (strSpotifyUri, blnCollaborative, strCreatorSpotifyUri, intPersonaID, intApplicationID, strName, blnFullInfo, intSpotifyAccountID) VALUES (@strSpotifyUri, @blnCollaborative, @strCreatorSpotifyUri, @intPersonaID, @intApplicationID, @strName, @blnFullInfo, @intSpotifyAccountID)", conn);
                cmd.Parameters.AddWithValue("@strSpotifyUri", generatedPlaylist.PlaylistUri.Uri);
                cmd.Parameters.AddWithValue("@blnCollaborative",generatedPlaylist.Collaborative);
                cmd.Parameters.AddWithValue("@strCreatorSpotifyUri", generatedPlaylist.SpotifyUserAnonymousID);
                cmd.Parameters.AddWithValue("@intPersonaID", generatedPlaylist.PersonaID);
                cmd.Parameters.AddWithValue("@intApplicationID", generatedPlaylist.ApplicationID);
                cmd.Parameters.AddWithValue("@strName", generatedPlaylist.Name);
                cmd.Parameters.AddWithValue("@strCountryCode", generatedPlaylist.CountryCode);
                cmd.Parameters.AddWithValue("@blnFullInfo", generatedPlaylist.FullInfo);
                cmd.Parameters.AddWithValue("@intSpotifyAccountID", generatedPlaylist.SpotifyAccountID);

                cmd.ExecuteNonQuery();

                generatedPlaylist.ID = (int)cmd.LastInsertedId;

                if (generatedPlaylist.Tracks != null && generatedPlaylist.Tracks.Any())
                {
                    var sql = "INSERT INTO tblGeneratedPlaylistTrack (intGeneratedPlaylistID, strSpotifyTrackUri, strArtistName, strTrackName, intDuration) VALUES ";
                    var paramValues = generatedPlaylist.Tracks.Select(t => BuildInsertTrackValueSQL(generatedPlaylist, t));
                    sql += string.Join(",", paramValues);

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

                conn.Close();
            }

            return generatedPlaylist;
        }

        private string BuildInsertTrackValueSQL(GeneratedPlaylist generatedPlaylist, Track t)
        {
            return " (" + generatedPlaylist.ID + ", '" + MySqlHelper.EscapeString(t.SpotifyLink.Uri) + "','" + MySqlHelper.EscapeString(t.ArtistName ?? string.Empty) + "','" + MySqlHelper.EscapeString(t.Name ?? string.Empty) + "'," + t.Duration + ") ";
        }

        internal void UpdateGeneratedPlaylist(GeneratedPlaylist generatedPlaylist)
        {
            using (MySqlConnection conn = new MySqlConnection(DatabaseHandler.GetConnectionString()))
            {
                conn.Open();

                MySqlCommand cmd = new MySqlCommand("UPDATE tblGeneratedPlaylist SET strSpotifyUri=@strSpotifyUri, blnCollaborative=@blnCollaborative, strCreatorSpotifyUri=@strCreatorSpotifyUri, intPersonaID=@intPersonaID, intApplicationID=@intApplicationID, strName=@strName, strCountryCode=@strCountryCode, blnFullInfo=@blnFullInfo WHERE intID=@intID", conn);
                cmd.Parameters.AddWithValue("@intID", generatedPlaylist.ID);
                cmd.Parameters.AddWithValue("@strSpotifyUri", generatedPlaylist.PlaylistUri.Uri);
                cmd.Parameters.AddWithValue("@blnCollaborative", generatedPlaylist.Collaborative);
                cmd.Parameters.AddWithValue("@strCreatorSpotifyUri", generatedPlaylist.SpotifyUserAnonymousID);
                cmd.Parameters.AddWithValue("@intPersonaID", generatedPlaylist.PersonaID);
                cmd.Parameters.AddWithValue("@intApplicationID", generatedPlaylist.ApplicationID);
                cmd.Parameters.AddWithValue("@strName", generatedPlaylist.Name);
                cmd.Parameters.AddWithValue("@strCountryCode", generatedPlaylist.CountryCode);
                cmd.Parameters.AddWithValue("@blnFullInfo", generatedPlaylist.FullInfo);
                
                cmd.ExecuteNonQuery();

                if (generatedPlaylist.Tracks != null && generatedPlaylist.Tracks.Any())
                {
                    cmd = new MySqlCommand("DELETE FROM tblGeneratedPlaylistTrack WHERE intGeneratedPlaylistID=@intGeneratedPlaylistID", conn);
                    cmd.Parameters.AddWithValue("@intGeneratedPlaylistID", generatedPlaylist.ID);
                    cmd.ExecuteNonQuery();

                    var sql = "INSERT INTO tblGeneratedPlaylistTrack (intGeneratedPlaylistID, strSpotifyTrackUri, strArtistName, strTrackName, intDuration) VALUES ";
                    var paramValues = generatedPlaylist.Tracks.Select(t => " (" + generatedPlaylist.ID + ", '" + MySqlHelper.EscapeString(t.SpotifyLink.Uri) + "','" + MySqlHelper.EscapeString(t.ArtistName) + "','" + MySqlHelper.EscapeString(t.Name) + "'," + t.Duration + ") ");
                    sql += string.Join(",", paramValues);

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

                conn.Close();
            }
        }

        internal GeneratedPlaylist GetGeneratedPlaylist(int generatedPlaylistID)
        {
            var cmd = new MySqlCommand("SELECT intID, strSpotifyUri, blnCollaborative, strCreatorSpotifyUri, intPersonaID, intApplicationID, strName, datTimestamp, strCountryCode, blnFullInfo, intSpotifyAccountID FROM tblGeneratedPlaylist WHERE intID=@intID;");
            cmd.Parameters.AddWithValue("@intID", generatedPlaylistID);
            return GetGeneratedPlaylistInternal(cmd);
        }

        internal GeneratedPlaylist GetGeneratedPlaylist(string uri)
        {
            var cmd = new MySqlCommand("SELECT intID, strSpotifyUri, blnCollaborative, strCreatorSpotifyUri, intPersonaID, intApplicationID, strName, datTimestamp, strCountryCode, blnFullInfo, intSpotifyAccountID FROM tblGeneratedPlaylist WHERE strSpotifyUri=@strSpotifyUri;");
            cmd.Parameters.AddWithValue("@strSpotifyUri", uri);
            return GetGeneratedPlaylistInternal(cmd);
        }

        internal GeneratedPlaylist GetGeneratedPlaylistInternal(MySqlCommand cmd)
        {
            GeneratedPlaylist playlist = null;
            using (MySqlConnection conn = new MySqlConnection(DatabaseHandler.GetConnectionString()))
            {
                conn.Open();

                cmd.Connection = conn;
                
                var reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    playlist = new GeneratedPlaylist(reader);
                }
                reader.Close();

                if (playlist != null)
                {
                    playlist.Tracks = GetTracks(playlist);
                }

                conn.Close();
            }

            return playlist;
        }

        internal IEnumerable<GeneratedPlaylist> GetGeneratedPlaylists(SpotifyAccount spotifyAccount)
        {
            List<GeneratedPlaylist> playlists = new List<GeneratedPlaylist>();
            using (MySqlConnection conn = new MySqlConnection(DatabaseHandler.GetConnectionString()))
            {
                conn.Open();

                var cmd = new MySqlCommand("SELECT intID, strSpotifyUri, blnCollaborative, strCreatorSpotifyUri, intPersonaID, intApplicationID, strName, datTimestamp, strCountryCode, blnFullInfo, intSpotifyAccountID FROM tblGeneratedPlaylist WHERE intSpotifyAccountID=@intSpotifyAccountID;", conn);
                cmd.Parameters.AddWithValue("@intSpotifyAccountID", spotifyAccount.ID);

                var reader = cmd.ExecuteReader();
                while(reader.Read())
                {
                    var playlist = new GeneratedPlaylist(reader);
                    playlists.Add(playlist);

                    //TODO: Include tracks?
                }

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

            return playlists;
        }

        private static List<Track> GetTracks(GeneratedPlaylist playlist)
        {
            List<Track> tracks = new List<Track>();
            using (MySqlConnection conn = new MySqlConnection(DatabaseHandler.GetConnectionString()))
            {
                conn.Open();

                MySqlCommand cmd = new MySqlCommand("SELECT strSpotifyTrackUri, strArtistName, strTrackName, intDuration FROM tblGeneratedPlaylistTrack WHERE intGeneratedPlaylistID=@intGeneratedPlaylistID", conn);
                cmd.Parameters.AddWithValue("@intGeneratedPlaylistID", playlist.ID);
                var reader = cmd.ExecuteReader();
                
                while (reader.Read())
                {
                    var track = new Track(reader.GetString(1), reader.GetString(2)) { SpotifyLink = new SpotifyLink(reader.GetString(0)), Duration = reader.GetInt32(3) };
                    tracks.Add(track);
                }

                reader.Close();
                conn.Close();
            }
            return tracks;
        }

        internal void CalculateGeneratedPlaylistSonyPercent(GeneratedPlaylist pl)
        {           
            using (MySqlConnection conn = new MySqlConnection(DatabaseHandler.GetConnectionString()))
            {
                conn.Open();

                MySqlCommand cmd = new MySqlCommand("SELECT strSpotifyTrackUri FROM tblGeneratedPlaylistTrack WHERE intGeneratedPlaylistID=@intPlaylistID", conn);
                cmd.Parameters.AddWithValue("@intPlaylistID", pl.ID);

                List<string> tracks = new List<string>();
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                    tracks.Add(reader.GetString(0));

                reader.Close();

                if (tracks.Count == 0)
                {
                    conn.Close();
                    return;
                }

                var sql = string.Format("SELECT COUNT(*) FROM tblArtistTopTrack WHERE strSpotifyUrl IN ({0}) AND blnIsSonyTrack=1", string.Join(",", tracks.Select(t=> "'" + t + "'")));
                cmd = new MySqlCommand(sql, conn);
                var sonyTracks = Convert.ToInt32(cmd.ExecuteScalar());

                double sonyPercent = 0;
                if (tracks.Count != 0)
                    sonyPercent = (1.0*sonyTracks / tracks.Count);

                cmd = new MySqlCommand("UPDATE tblGeneratedPlaylist SET fltSonyPercent=@fltSonyPercent WHERE intID=@intPlaylistID", conn);
                cmd.Parameters.AddWithValue("@intPlaylistID", pl.ID);
                cmd.Parameters.AddWithValue("@fltSonyPercent", sonyPercent);
                cmd.ExecuteNonQuery();

                conn.Close();
            }
        }
    }
}