using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using MySqlConnector;
using Nest;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Contracts.Entities.Buzz;
using Sony.Filtr.Database;
using Sony.Filtr.Utility.Extensions;
using Sony.Filtr.AdminSearch.Models;
using Sony.Filtr.Contracts.Entities;

namespace Sony.Filtr.AdminSearch
{
    public enum Vendors
    {
        Spotify,
        AppleMusic,
        Amazon
    }

    public class AdminSearchManager
    {
        private readonly string _elasticEndpointUrl;

        private readonly string _elasticIndexName;

        public const string AsciifoldingAnalyzerName = "asciifoldingAnalyzer";

        public AdminSearchManager(string elasticEndpointUrl, string elasticIndexName)
        {
            _elasticEndpointUrl = elasticEndpointUrl;
            _elasticIndexName = elasticIndexName;
        }

        public async Task<List<BuzzUser>> SearchBuzzUsersAsync(string searchTerm, MusicService? musicService = null, int amount = 100)
        {
            List<BuzzUser> buzzUsers = new List<BuzzUser>();
            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                searchTerm = SanitizeSearchTerm(searchTerm);

                MySqlCommand cmd = new MySqlCommand("SELECT id, Username, DisplayName, CategoryId, MusicServiceId, CountryCode, ImportPlaylists, subscribers, playlistSubscribers, error, Image " +
                                                    "FROM BuzzUser " +
                                                    "WHERE (@musicService IS NULL OR MusicServiceId=@musicService) " +
                                                    "AND Match(DisplayName, Username) AGAINST (@searchTerm IN BOOLEAN MODE) " +
                                                    "ORDER BY subscribers DESC " +
                                                    "LIMIT @amount", conn);

                cmd.Parameters.AddWithValue("@musicService", musicService);
                cmd.Parameters.AddWithValue("@searchTerm", searchTerm + "*");
                cmd.Parameters.AddWithValue("@amount", amount);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        buzzUsers.Add(new BuzzUser()
                        {
                            ID = reader.GetInt32("Id"),
                            Username = reader.GetSafeString("username"),
                            DisplayName = reader.GetSafeString("DisplayName"),
                            BuzzCategoryId = reader.GetIntOrDefault("CategoryId"),
                            MusicServiceId = reader.GetInt32("MusicServiceId"),
                            CountryCode = reader.GetSafeString("CountryCode"),
                            ImportPlaylists = reader.GetBoolean("ImportPlaylists"),
                            Subscribers = reader.GetIntOrDefault("Subscribers"),
                            PlaylistSubscribers = reader.GetLongOrDefault("PlaylistSubscribers"),
                            Error = reader.GetBoolean("Error"),
                            Image = reader.GetSafeString("Image"),
                        });
                    }
                }
            }

            return buzzUsers;
        }

        private string SanitizeSearchTerm(string searchTerm)
        {
            var charsToReplace = new[] { "+", "-", "<", ">", "(", ")", "[", "]", "{ ", "}", "~", "* ", "\"", "\\" };

            foreach (var ch in charsToReplace)
            {
                searchTerm = searchTerm.Replace(ch, string.Empty);
            }
            return searchTerm.Trim();
        }

        public async Task AddSearchHistoryItemAsync(string userId, string source, string searchResultUri, SearchHistoryItemType searchResultType, string searchTerm = null)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO tblAdminSearchHistory (AdminUserId, SearchResultUri, SearchTerm, SearchResultType, Source) " +
                                                    "VALUES (@userId, @resultUri, @searchTerm, @resultType, @source)";

                cmd.Parameters.AddWithValue("@userId", userId);
                cmd.Parameters.AddWithValue("@resultUri", searchResultUri);
                cmd.Parameters.AddWithValue("@searchTerm", searchTerm);
                cmd.Parameters.AddWithValue("@resultType", (int)searchResultType);
                cmd.Parameters.AddWithValue("@source", source);

                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task<PaginatedContent<SearchHistoryItemData>> GetSearchHistoryItemsAsync(string userId, Vendors source, int limit = 25, int offset = 0)
        {
            var searchHistoryItems = new List<SearchHistoryItemData>();

            string sqlAppleMusic = @"
SELECT SQL_CALC_FOUND_ROWS AdminUserId, SearchResultUri, Max(Timestamp) as timestamp, SearchTerm, SearchResultType, c.Name as accountName
FROM tblAdminSearchHistory h
INNER JOIN tblAppleMusicPlaylist pl on pl.Id = h.SearchResultUri
INNER JOIN tblAppleMusicCurator c on pl.CuratorId = c.Id
WHERE AdminUserId = @userId AND Source = 'AppleMusic'
GROUP BY AdminUserId, SearchResultUri
ORDER BY Max(Timestamp) DESC
LIMIT @offset, @limit";

            string sqlSpotify = @"
SELECT SQL_CALC_FOUND_ROWS AdminUserId, SearchResultUri, Max(Timestamp) as timestamp, SearchTerm, SearchResultType, pl.User as AccountName
FROM tblAdminSearchHistory h
LEFT JOIN tblSpotifyPlaylist pl on h.SearchResultUri = pl.PlaylistUri
WHERE AdminUserId = @userId AND Source = 'Spotify'
GROUP BY AdminUserId, SearchResultUri
ORDER BY Max(Timestamp) DESC
LIMIT @offset, @limit";

            string sql = source == Vendors.AppleMusic ? sqlAppleMusic : sqlSpotify;

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;

                cmd.Parameters.AddWithValue("@userId", userId);
                cmd.Parameters.AddWithValue("@limit", limit);
                cmd.Parameters.AddWithValue("@offset", offset);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        searchHistoryItems.Add(new SearchHistoryItemData()
                        {
                            UserId = reader.GetSafeString("AdminUserId"),
                            Timestamp = reader.GetUtcDateTime("Timestamp"),
                            SearchResultUri = reader.GetSafeString("SearchResultUri"),
                            SearchTerm = reader.GetSafeString("SearchTerm"),
                            SearchResultType = (SearchHistoryItemType)reader.GetInt32("SearchResultType"),
                            PlaylistOwnerName = reader.GetSafeString("AccountName")
                        });
                    }
                }

                var countCommand = conn.CreateCommand();
                countCommand.CommandText = "Select FOUND_ROWS()";
                var totalCount = (long)(await countCommand.ExecuteScalarAsync());

                return new PaginatedContent<SearchHistoryItemData>()
                {
                    Items = searchHistoryItems,
                    Pagination = new Pagination()
                    {
                        Limit = limit,
                        Offset = offset,
                        Total = (int)totalCount
                    }
                };
            }
        }

        public async Task SetupIndexAndMappingAsync()
        {
            var analyzer = new CustomAnalyzer
            {
                Filter = new List<string>() { "standard", "lowercase", "asciifolding" },
                Tokenizer = "standard",
            };

            var exists = await ElasticClient.IndexExistsAsync(_elasticIndexName);
            if (exists.Exists)
            {
                await ElasticClient.DeleteIndexAsync(_elasticIndexName);
            }

            await ElasticClient.CreateIndexAsync(_elasticIndexName,
                c => c.Settings(
                    s => s.Analysis(
                        analysis => analysis.Analyzers(
                            analyzers => analyzers.Custom(AsciifoldingAnalyzerName, ad => analyzer)
                        )
                    )
                )
            );

            await ElasticClient.CreateIndexAsync(_elasticIndexName);

            await ElasticClient.MapAsync<SpotifyPlaylistIndexItem>(m => m.AutoMap());
            await ElasticClient.MapAsync<AppleMusicPlaylistIndexItem>(m => m.AutoMap());
            await ElasticClient.MapAsync<AmazonPlaylistIndexItem>(m => m.AutoMap());
        }

        public async Task IndexSpotifyPlaylistsAsync(List<SpotifyPlaylistIndexItem> playlists)
        {
            var response = await ElasticClient.IndexManyAsync(playlists);
            if (response.Errors)
            {
                throw new ElasticSearchException("ElasticSearch error when indexing playlists", response);
            }
        }

        public async Task RemoveSpotifyPlaylistsFromIndexAsync(List<SpotifyPlaylistIndexItem> playlists)
        {
            var response = await ElasticClient.DeleteManyAsync(playlists);
            if (response.Errors)
            {
                throw new ElasticSearchException("ElasticSearch error when removing playlists from index", response);
            }
        }

        public async Task IndexAppleMusicPlaylistsAsync(List<AppleMusicPlaylistIndexItem> playlists)
        {
            var response = await ElasticClient.IndexManyAsync(playlists);
            if (response.Errors)
            {
                throw new ElasticSearchException("ElasticSearch error when indexing apple music playlists", response);
            }
        }

        public async Task RemoveAppleMusicPlaylistsFromIndexAsync(List<AppleMusicPlaylistIndexItem> playlists)
        {
            var response = await ElasticClient.DeleteManyAsync(playlists);
            if (response.Errors)
            {
                throw new ElasticSearchException("ElasticSearch error when removing apple music playlists from index", response);
            }
        }

        public async Task<List<SpotifyPlaylistIndexItem>> SearchSpotifyPlaylistAsync(string searchQuery, int limit, int? buzzCategoryId = null)
        {
            var descriptor = GetSpotifyPlaylistSearchDescriptor(searchQuery);

            descriptor.Take(limit);

            descriptor.Analyzer(AsciifoldingAnalyzerName);

            var results = await ElasticClient.SearchAsync<SpotifyPlaylistIndexItem>(descriptor);

            return results.Documents.ToList();
        }

        public async Task IndexAmazonPlaylistsAsync(List<AmazonPlaylistIndexItem> playlists)
        {
            var response = await ElasticClient.IndexManyAsync(playlists);
            if (response.Errors)
            {
                throw new ElasticSearchException("ElasticSearch error when indexing apple music playlists", response);
            }
        }
        public async Task RemoveAmazonPlaylistsFromIndexAsync(List<AmazonPlaylistIndexItem> playlists)
        {
            var response = await ElasticClient.DeleteManyAsync(playlists);
            if (response.Errors)
            {
                throw new ElasticSearchException("ElasticSearch error when removing apple music playlists from index", response);
            }
        }

        public async Task<List<AmazonPlaylistIndexItem>> SearchAmazonPlaylistAsync(string searchQuery, int limit, int? buzzCategoryId = null)
        {
            var descriptor = GetAmazonPlaylistSearchDescriptor(searchQuery);

            descriptor.Take(limit);

            descriptor.Analyzer(AsciifoldingAnalyzerName);

            var results = await ElasticClient.SearchAsync<AmazonPlaylistIndexItem>(descriptor);

            return results.Documents.ToList();
        }

        public async Task<List<AppleMusicPlaylistIndexItem>> SearchAppleMusicPlaylistAsync(string searchTerm, int limit)
        {
            var descriptor = GetAppleMusicPlaylistSearchDescriptor(searchTerm);

            descriptor.Take(limit);

            descriptor.Analyzer(AsciifoldingAnalyzerName);

            var results = await ElasticClient.SearchAsync<AppleMusicPlaylistIndexItem>(descriptor);

            return results.Documents.ToList();
        }

        private SearchDescriptor<SpotifyPlaylistIndexItem> GetSpotifyPlaylistSearchDescriptor(string query)
        {
            var splitQuery = Regex.Split(query, @"\s+");
            var exactTerms = string.Join(" AND ", splitQuery);
            var wildCard = string.Join("* AND ", splitQuery) + "*";
            var fuzzy = string.Join("~ AND ", splitQuery) + "~";
            var fuzzyWildcard = string.Join("*~ AND ", splitQuery) + "*~";
            var fuzzyTrailingWildcard = string.Join("~ AND ", splitQuery) + "*~";

            if (splitQuery.Length > 1)
            {
                return new SearchDescriptor<SpotifyPlaylistIndexItem>().Query(q =>
                    q.FunctionScore(fs => fs.Query(qi => qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(wildCard).Boost(2))
                                                         || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(exactTerms).Boost(10))
                                                         || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(fuzzy).Fuzziness(Fuzziness.Auto))
                                                         || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(fuzzyWildcard).Fuzziness(Fuzziness.Auto))
                                                         || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(fuzzyTrailingWildcard).Fuzziness(Fuzziness.Auto))
                        //|| qi.QueryString(qs=> qs.Fields(f=> f.Fields(fi=> fi.User, fi=> fi.UserDisplayName)).Query(wildCard).Boost(1))
                        ).Functions(f => f.FieldValueFactor(fvf => fvf.Field(fo => fo.Followers).Modifier(FieldValueFactorModifier.Log1P))))
                    );
            }

            return new SearchDescriptor<SpotifyPlaylistIndexItem>()
                .Query(q => q.FunctionScore(fs => fs.Query(qi => qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(wildCard).Boost(2))
                                                                 || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(exactTerms).Boost(10))
                                                                 || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(fuzzy).Fuzziness(Fuzziness.Auto))
                                                                 || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(fuzzyTrailingWildcard).Fuzziness(Fuzziness.Auto))
                    //|| qi.QueryString(qs => qs.Fields(f => f.Fields(fi => fi.User, fi => fi.UserDisplayName)).Query(wildCard).Boost(1))
                    ).Functions(f => f.FieldValueFactor(fvf => fvf.Field(fo => fo.Followers).Modifier(FieldValueFactorModifier.Log1P)))));
        }

        private SearchDescriptor<AppleMusicPlaylistIndexItem> GetAppleMusicPlaylistSearchDescriptor(string query)
        {
            var splitQuery = Regex.Split(query, @"\s+");
            var exactTerms = string.Join(" AND ", splitQuery);
            var wildCard = string.Join("* AND ", splitQuery) + "*";
            var fuzzy = string.Join("~ AND ", splitQuery) + "~";
            var fuzzyWildcard = string.Join("*~ AND ", splitQuery) + "*~";
            var fuzzyTrailingWildcard = string.Join("~ AND ", splitQuery) + "*~";

            if (splitQuery.Length > 1)
            {
                return new SearchDescriptor<AppleMusicPlaylistIndexItem>().Query(q =>
                    q.FunctionScore(fs => fs.Query(qi => qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(wildCard).Boost(2))
                                                         || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(exactTerms).Boost(10))
                                                         || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(fuzzy).Fuzziness(Fuzziness.Auto))
                                                         || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(fuzzyWildcard).Fuzziness(Fuzziness.Auto))
                                                         || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(fuzzyTrailingWildcard).Fuzziness(Fuzziness.Auto))
                    //|| qi.QueryString(qs=> qs.Fields(f=> f.Fields(fi=> fi.User, fi=> fi.UserDisplayName)).Query(wildCard).Boost(1))
                    ).Functions(f => f.FieldValueFactor(fvf => fvf.Field(fo => fo.Streams56Days).Modifier(FieldValueFactorModifier.Log1P))))
                );
            }

            return new SearchDescriptor<AppleMusicPlaylistIndexItem>()
                .Query(q => q.FunctionScore(fs => fs.Query(qi => qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(wildCard).Boost(2))
                                                                 || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(exactTerms).Boost(10))
                                                                 || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(fuzzy).Fuzziness(Fuzziness.Auto))
                                                                 || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(fuzzyTrailingWildcard).Fuzziness(Fuzziness.Auto))
                //|| qi.QueryString(qs => qs.Fields(f => f.Fields(fi => fi.User, fi => fi.UserDisplayName)).Query(wildCard).Boost(1))
                ).Functions(f => f.FieldValueFactor(fvf => fvf.Field(fo => fo.Streams56Days).Modifier(FieldValueFactorModifier.Log1P)))));
        }

        private SearchDescriptor<AmazonPlaylistIndexItem> GetAmazonPlaylistSearchDescriptor(string query)
        {
            var splitQuery = Regex.Split(query, @"\s+");
            var exactTerms = string.Join(" AND ", splitQuery);
            var wildCard = string.Join("* AND ", splitQuery) + "*";
            var fuzzy = string.Join("~ AND ", splitQuery) + "~";
            var fuzzyWildcard = string.Join("*~ AND ", splitQuery) + "*~";
            var fuzzyTrailingWildcard = string.Join("~ AND ", splitQuery) + "*~";

            if (splitQuery.Length > 1)
            {
                return new SearchDescriptor<AmazonPlaylistIndexItem>().Query(q =>
                    q.FunctionScore(fs => fs.Query(qi => qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(wildCard).Boost(2))
                                                         || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(exactTerms).Boost(10))
                                                         || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(fuzzy).Fuzziness(Fuzziness.Auto))
                                                         || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(fuzzyWildcard).Fuzziness(Fuzziness.Auto))
                                                         || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(fuzzyTrailingWildcard).Fuzziness(Fuzziness.Auto))
                    //|| qi.QueryString(qs=> qs.Fields(f=> f.Fields(fi=> fi.User, fi=> fi.UserDisplayName)).Query(wildCard).Boost(1))
                    ).Functions(f => f.FieldValueFactor(fvf => fvf.Field(fo => fo.TracksNumber).Modifier(FieldValueFactorModifier.Log1P))))
                ).Sort(so => so.Ascending(asc => asc.DspPlaylistId));
            }

            return new SearchDescriptor<AmazonPlaylistIndexItem>()
                .Query(q => q.FunctionScore(fs => fs.Query(qi => qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(wildCard).Boost(2))
                                                                 || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(exactTerms).Boost(10))
                                                                 || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(fuzzy).Fuzziness(Fuzziness.Auto))
                                                                 || qi.QueryString(qs => qs.Fields(f => f.Field(i => i.Name)).Query(fuzzyTrailingWildcard).Fuzziness(Fuzziness.Auto))
                //|| qi.QueryString(qs => qs.Fields(f => f.Fields(fi => fi.User, fi => fi.UserDisplayName)).Query(wildCard).Boost(1))
                ).Functions(f => f.FieldValueFactor(fvf => fvf.Field(fo => fo.TracksNumber).Modifier(FieldValueFactorModifier.Log1P))))).Sort(so => so.Ascending(asc => asc.DspPlaylistId));
        }


        private ElasticClient _elasticClient;
        private ElasticClient ElasticClient
        {
            get
            {
                if (_elasticClient == null)
                {
                    var uri = new Uri(_elasticEndpointUrl);

                    var connectionSettings = new ConnectionSettings(uri);
                    connectionSettings.DefaultIndex(_elasticIndexName);

                    _elasticClient = new ElasticClient(connectionSettings);
                }

                return _elasticClient;
            }
        }


    }
}