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

namespace Sony.Filtr.Playlists.Spotify
{
    public class SpotifyPlaylistContentManager
    {
        //public async Task<List<TrackInPlaylist>> GetPlaylistedTracksForArtist(string artistId)
        //{
        //    List<TrackInPlaylist> playlistedTracks = new List<TrackInPlaylist>();
        //    using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
        //    {
        //        var sql = "SELECT tl.PlaylistUri, tl.TrackId, tl.PlaylistIndex, tl.Added, tl.EarliestAdded " +
        //                  "FROM tblSpotifyPlaylistTrackList AS tl " +
        //                  "INNER JOIN tblSpotifyTrack2 AS t ON tl.TrackId = t.TrackId " +
        //                  "INNER JOIN tblSpotifyTrackArtist AS ta ON ta.TrackId = t.TrackId " +
        //                  "INNER JOIN tblSpotifyPlaylist AS p ON p.PlaylistUri = tl.PlaylistUri " +
        //                  "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
        //                  "WHERE ta.ArtistId = @artistId AND p.Removed = 0 AND ip.playlistId IS NULL " +
        //                  "GROUP BY tl.PlaylistUri, tl.TrackId";

        //        using(var cmd = new MySqlCommand(sql, conn))
        //        {
        //            cmd.Parameters.AddWithValue("@artistId", artistId);
        //            cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);

        //            using(var reader = await cmd.ExecuteReaderAsync())
        //            {
        //                while (await reader.ReadAsync())
        //                {
        //                    playlistedTracks.Add(new TrackInPlaylist()
        //                    {
        //                        Playlist = new SpotifyLink(reader.GetString(0)),
        //                        Track = SpotifyLink.FromTrackId(reader.GetString(1)),
        //                        Position = reader.GetInt32(2),
        //                        Added = reader.IsDBNull(3) ? (DateTime?)null : reader.GetDateTime(3),
        //                        EarliestAdded = reader.IsDBNull(4) ? (DateTime?)null : reader.GetDateTime(4),
        //                    });
        //                }
        //            }
        //        }
        //    }

        //    return playlistedTracks;
        //}

        //public async Task<List<TrackInPlaylist>> GetPlaylistedTracksForAlbum(string albumId)
        //{
        //    List<TrackInPlaylist> playlistedTracks = new List<TrackInPlaylist>();
        //    using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
        //    {
        //        var sql = "SELECT tl.PlaylistUri, tl.TrackId, tl.PlaylistIndex, tl.Added, tl.EarliestAdded " +
        //                  "FROM tblSpotifyPlaylistTrackList AS tl " +
        //                  "INNER JOIN tblSpotifyTrack2 AS t ON tl.TrackId = t.TrackId " +
        //                  "INNER JOIN tblSpotifyTrackAlbum AS ta ON ta.TrackId = t.TrackId " +
        //                  "INNER JOIN tblSpotifyPlaylist AS p ON p.PlaylistUri = tl.PlaylistUri " +
        //                  "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
        //                  "WHERE ta.albumId = @albumId AND p.Removed = 0 AND ip.playlistID IS NULL " +
        //                  "GROUP BY tl.PlaylistUri, tl.TrackId";

        //        var cmd = new MySqlCommand(sql, conn);

        //        cmd.Parameters.AddWithValue("@albumId", albumId);
        //        cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);

        //        var reader = await cmd.ExecuteReaderAsync();
        //        while (await reader.ReadAsync())
        //        {
        //            playlistedTracks.Add(new TrackInPlaylist()
        //            {
        //                Playlist = new SpotifyLink(reader.GetString(0)),
        //                Track = SpotifyLink.FromTrackId(reader.GetString(1)),
        //                Position = reader.GetInt32(2),
        //                Added = reader.IsDBNull(3) ? (DateTime?)null :  reader.GetDateTime(3),
        //                EarliestAdded = reader.IsDBNull(4) ? (DateTime?)null : reader.GetDateTime(4),
        //            });
        //        }
        //    }

        //    return playlistedTracks;
        //}
    }

    //public class TrackInPlaylist
    //{
    //    public SpotifyLink Playlist { get; set; }
    //    public SpotifyLink Track { get; set; }

    //    public int Position { get; set; }

    //    public DateTime? Added { get; set; }

    //    public DateTime? EarliestAdded { get; set; }
    //}
}