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

namespace Sony.Filtr.Core.Buzz
{
    public class BuzzFactory
    {
        internal BuzzImportUser AddBuzzImportUser(BuzzImportUser user)
        {
            using (MySqlConnection conn = DatabaseHandler.GetOpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand("INSERT INTO tblBuzzImportUser (strCompanyName, intApplicationID, strSpotifyUserName) VALUES (@strCompanyName, @intApplicationID, @strSpotifyUserName)", conn);
                cmd.Parameters.AddWithValue("@strCompanyName", user.CompanyName);
                cmd.Parameters.AddWithValue("@intApplicationID", user.ApplicationID);
                cmd.Parameters.AddWithValue("@strSpotifyUserName", user.SpotifyUserName);

                cmd.ExecuteNonQuery();
                user.ID = (int)cmd.LastInsertedId;
            }

            return user;
        }

        internal void UpdateBuzzImportUser(BuzzImportUser user)
        {
            using (MySqlConnection conn = DatabaseHandler.GetOpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand("UPDATE tblBuzzImportUser SET strCompanyName=@strCompanyName, intApplicationID=@intApplicationID, strSpotifyUserName=@strSpotifyUserName WHERE intID=@intID", conn);
                cmd.Parameters.AddWithValue("@intID", user.ID);
                cmd.Parameters.AddWithValue("@strCompanyName", user.CompanyName);
                cmd.Parameters.AddWithValue("@intApplicationID", user.ApplicationID);
                cmd.Parameters.AddWithValue("@strSpotifyUserName", user.SpotifyUserName);

                cmd.ExecuteNonQuery();
            }
        }

        internal async Task<List<BuzzImportUser>> GetBuzzImportUsersById(string id)
        {
            List<BuzzImportUser> companies = new List<BuzzImportUser>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();
                var sql = "SELECT intID, strCompanyName, intApplicationID, strSpotifyUserName FROM tblBuzzImportUser WHERE intID in ({0})";
                cmd.CommandText = String.Format(sql, Maybe.ToCommaSeparated(id));
                // Execute the reader.
                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var company = new BuzzImportUser(reader);
                        companies.Add(company);
                    }
                }
            }

            return companies;
        }

        internal async Task<BuzzImportUser> GetBuzzImportUserByName(string username)
        {
            BuzzImportUser company;
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText =
                    "SELECT intID, strCompanyName, intApplicationID, strSpotifyUserName FROM tblBuzzImportUser WHERE strSpotifyUserName=@username";

                cmd.Parameters.Add(new MySqlParameter("@username", username));

                // Execute the reader.
                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    if (!reader.Read())
                        return null;

                    company = new BuzzImportUser(reader);
                }
            }

            return company;
        }

        internal List<BuzzImportUser> GetBuzzImportUsers()
        {
            List<BuzzImportUser> companies = new List<BuzzImportUser>();
            using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT intID, strCompanyName, intApplicationID, strSpotifyUserName FROM tblBuzzImportUser";

                // Execute the reader.
                var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    companies.Add(new BuzzImportUser(reader));
                }

                reader.Close();
            }
            return companies;
        }

        internal BuzzImportUser GetBuzzImportUser(string companyName, int applicationID, string spotifyUserName)
        {
            BuzzImportUser user;
            using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
            {

                var cmd = conn.CreateCommand();
                cmd.CommandText =
                    "SELECT intID, strCompanyName, intApplicationID, strSpotifyUserName FROM tblBuzzImportUser WHERE strCompanyName=@strCompanyName AND intApplicationID=@intApplicationID AND strSpotifyUserName=@strSpotifyUserName";
                cmd.Parameters.Add(new MySqlParameter("@strCompanyName", companyName));
                cmd.Parameters.Add(new MySqlParameter("@intApplicationID", applicationID));
                cmd.Parameters.Add(new MySqlParameter("@strSpotifyUserName", spotifyUserName));

                // Execute the reader.
                var reader = cmd.ExecuteReader();
                if (!reader.Read())
                    return null;

                user = new BuzzImportUser(reader);

                reader.Close();
            }
            return user;
        }

        internal void DeleteBuzzImportUser(BuzzImportUser user)
        {
            using (MySqlConnection conn = DatabaseHandler.GetOpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand("DELETE FROM tblBuzzImportUser WHERE intID=@intID", conn);
                cmd.Parameters.AddWithValue("@intID", user.ID);

                cmd.ExecuteNonQuery();
            }
        }

        internal async Task AddEditorialPlaylistToBuzzUserAsync(EditorialPlaylist playlist, BuzzImportUser user)
        {
            using (MySqlConnection conn = DatabaseHandler.GetOpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand("INSERT INTO tblBuzzImportUserPlaylists (intBuzzImportUserID, intEditorialPlaylistID) VALUES (@intBuzzImportUserID, @intEditorialPlaylistID)", conn);
                cmd.Parameters.AddWithValue("@intEditorialPlaylistID", playlist.ID);
                cmd.Parameters.AddWithValue("@intBuzzImportUserID", user.ID);

                await cmd.ExecuteNonQueryAsync();
            }
        }

        internal Dictionary<BuzzCompany, int> GetTopBuzzCompanies()
        {
            Dictionary<BuzzCompany, int> topBuzzCompanies = new Dictionary<BuzzCompany, int>();
            using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
            {
                const string sql =
                    "SELECT strCompanyName, intApplicationId, SUM(p.intSubscribers) " +
                    "FROM tblBuzzImportUser AS b " +
                    "INNER JOIN tblBuzzImportUserPlaylists AS bp ON b.intID=bp.intBuzzImportUserId " +
                    "INNER JOIN tblEditorialPlaylist AS p ON p.intID = bp.intEditorialPlaylistId " +
                    "GROUP BY strCompanyName, intApplicationId " +
                    "ORDER BY SUM(p.intSubscribers) DESC";

                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;

                using(var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var companyName = reader.GetString(0);
                        var intApplicationId = reader.GetInt32(1);
                        var subscribers = reader.GetInt32(2);
                        var company = new BuzzCompany()
                        {
                            ApplicationID = intApplicationId,
                            Name = companyName,
                        };
                        topBuzzCompanies.Add(company, subscribers);
                    }
                }
            }
            return topBuzzCompanies;
        }
    }
}