public void SaveTweets(PostTwitter postTwitter)
        {
            contextDB = new ContextDB();

            try
            {
                using (CrawlerDB crawlerDB = new CrawlerDB())
                {
                    contextDB.ConfigureContext(crawlerDB);

                    if (postTwitter != null)
                    {
                        crawlerDB.PostTwitters.Add(postTwitter);
                        crawlerDB.SaveChangesAsync();
                    }
                }
            }

            catch (Exception exception)
            {
                throw exception;
            }

            finally
            {
                contextDB = null;
            }
        }
        public bool CheckIfExists(string categoria)
        {
            contextDB = new ContextDB();
            try
            {
                using (CrawlerDB crawlerDB = new CrawlerDB())
                {
                    contextDB.ConfigureContext(crawlerDB);

                    IEnumerable<Categoria> cat = crawlerDB.Categorias.AsNoTracking().Where(p => p.Nome == categoria);
                    if (cat.Count() <= 0)
                        return false;

                    return true;
                }
            }

            catch (Exception exception)
            {

                throw exception;
            }

            finally
            {
                contextDB = null;
            }
        }
        public IEnumerable<PostTwitter> GetAll()
        {
            contextDB = new ContextDB();

            try
            {
                using (var crawlerDB = new CrawlerDB())
                {
                    contextDB.ConfigureContext(crawlerDB);
                    var tweets =
                        crawlerDB.PostTwitters.Include(p => p.Categoria)
                            .Include(p => p.Estado)
                            .AsNoTracking()
                            .OrderBy(e => e.Estado.Nome)
                            .ToList();

                    return tweets;
                }
            }

            catch (Exception exception)
            {
                throw exception;
            }

            finally
            {
                contextDB = null;
            }
            
        }
        public IEnumerable<Categoria> GetAllCategories()
        {
            contextDB = new ContextDB();

            try
            {
                using (CrawlerDB crawlerDB = new CrawlerDB())
                {
                    contextDB.ConfigureContext(crawlerDB);
                    IEnumerable<Categoria> categories = crawlerDB.Categorias.AsNoTracking().ToList();

                    return categories;
                }
            }

            catch (Exception exception)
            {
                throw exception;
            }

            finally
            {
                contextDB = null;
            }
        }
        public IEnumerable<string> GetStatesAcronymsByCategory(string categoria)
        {
            contextDB = new ContextDB();

            try
            {
                using (CrawlerDB crawlerDB = new CrawlerDB())
                {
                    contextDB.ConfigureContext(crawlerDB);
                    return
                        crawlerDB.PostTwitters.Include(a => a.Categoria)
                            .Where(c => c.Categoria.Nome == categoria)
                            .Select(p => p.Estado.Sigla)
                            .Distinct()
                            .ToList();
                }
            }

            catch (Exception exception)
            {
                throw exception;
            }

            finally
            {
                contextDB = null;
            }
        }
        public string GetStatebyId(int id)
        {
            contextDB = new ContextDB();

            try
            {
                using (CrawlerDB crawlerDB = new CrawlerDB())
                {
                    contextDB.ConfigureContext(crawlerDB);
                    Task<Estado> state = crawlerDB.Estados.AsNoTracking().FirstOrDefaultAsync(e => e.EstadoId == id);

                    return state.Result.Nome;
                }
            }

            catch (Exception exception)
            {
                throw exception;
            }

            finally
            {
                contextDB = null;
            }
        }
        public IEnumerable<Estado> GetAllStates()
        {
            contextDB = new ContextDB();

            try
            {
                using (CrawlerDB crawlerDB = new CrawlerDB())
                {
                    contextDB.ConfigureContext(crawlerDB);
                    IEnumerable<Estado> states = crawlerDB.Estados.AsNoTracking().OrderBy(x => x.Nome).ToList();
                    return states;
                }
            }

            catch (Exception exception)
            {
                throw exception;
            }

            finally
            {
                contextDB = null;
            }

        }
        public List<int> GetTotalByDate(string categoria, DateTime dataInicio, DateTime dataFim, string estado)
        {
            contextDB = new ContextDB();

            try
            {
                using (CrawlerDB crawlerDB = new CrawlerDB())
                {
                    contextDB.ConfigureContext(crawlerDB);

                    return
                        crawlerDB.PostTwitters.Include(c => c.Categoria)
                            .Include(e => e.Estado)
                            .AsNoTracking()
                            .AsEnumerable()
                            .Where(
                                p =>
                                    p.Categoria.Nome == categoria && p.Estado.Nome == estado && p.Data >= dataInicio &&
                                    p.Data <= dataFim)
                            .GroupBy(p => new {p.Data, p.Estado.Sigla, p.Categoria.Nome})
                            .OrderBy(p => p.Key.Data)
                            .Distinct()
                            .Select(p => p.Count())
                            .ToList();
                }

            }
            catch (Exception exception)
            {
                throw exception;
            }

            finally
            {
                contextDB = null;
            }
        }
        public int GetId(string categoria)
        {
            contextDB = new ContextDB();

            try
            {
                using (CrawlerDB crawlerDB = new CrawlerDB())
                {
                    contextDB.ConfigureContext(crawlerDB);
                    var cat = crawlerDB.Categorias.AsNoTracking().FirstOrDefaultAsync(c => c.Nome == categoria);
                    return cat.Result.CategoriaId;
                }
            }

            catch (Exception exception)
            {
                throw exception;
            }

            finally
            {
                contextDB = null;
            }
        }
        public void SaveCategory(Categoria category)
        {

            try
            {
                using (CrawlerDB crawlerDB = new CrawlerDB())
                {
                    contextDB.ConfigureContext(crawlerDB);
                    if (category != null)
                    {
                        crawlerDB.Categorias.Add(category);
                        crawlerDB.SaveChangesAsync();
                    }
                }
            }

            catch (Exception exception)
            {
                throw exception;
            }

            finally
            {
                contextDB = null;
            }
        }
        public int GetTotalBetweenDates(string categoria, DateTime dataInicio, DateTime dataFim)
        {
            contextDB = new ContextDB();

            try
            {
                using (CrawlerDB crawlerDB = new CrawlerDB())
                {
                    contextDB.ConfigureContext(crawlerDB);
                    return
                        crawlerDB.PostTwitters.Include(a => a.Categoria)
                            .AsNoTracking()
                            .Where(p => p.Data >= dataInicio && p.Data <= dataFim)
                            .Count(c => c.Categoria.Nome == categoria);
                }
            }

            catch (Exception exception)
            {
                throw exception;
            }

            finally
            {
                contextDB = null;
            }

        }
        public int GetTotal(string categoria)
        {
            contextDB = new ContextDB();

            try
            {
                using (CrawlerDB crawlerDB = new CrawlerDB())
                {
                    contextDB.ConfigureContext(crawlerDB);
                    return
                        crawlerDB.PostTwitters.Include(a => a.Categoria)
                            .AsNoTracking()
                            .Count(c => c.Categoria.Nome == categoria);
                }
            }

            catch (Exception exception)
            {
                throw exception;
            }

            finally
            {
                contextDB = null;
            }

        }
        public IEnumerable<int> GetTotalByCategoryBetweenDates(string categoria, DateTime dataInicio, DateTime dataFim)
        {
            contextDB = new ContextDB();

            try
            {
                using (CrawlerDB crawlerDB = new CrawlerDB())
                {
                    contextDB.ConfigureContext(crawlerDB);
                    return
                        crawlerDB.PostTwitters.Include(a => a.Categoria)
                        .AsNoTracking()
                            .Where(e => e.Categoria.Nome.ToString() == categoria.ToString() && e.Data >= dataInicio && e.Data <= dataFim)
                            .GroupBy(e => e.EstadoId)
                            .Select(e => e.Count())
                            .ToList();
                }
            }

            catch (Exception exception)
            {
                throw exception;
            }

            finally
            {
                contextDB = null;
            }
        }
        public IEnumerable<string> GetCategoryByCode(int codigo)
        {
            contextDB = new ContextDB();

            try
            {
                using (CrawlerDB crawlerDB = new CrawlerDB())
                {
                    return
                        crawlerDB.PostTwitters.Include(a => a.Categoria)
                            .AsNoTracking()
                            .Where(e => e.EstadoId == codigo)
                            .Select(p => p.Categoria.Nome)
                            .Distinct()
                            .ToList();
                }
            }
            catch (Exception exception)
            {
                throw exception;
            }

            finally
            {
                contextDB = null;
            }
        }
        public IEnumerable<int> GetTotalByCode(int codigo)
        {
            contextDB = new ContextDB();

            try
            {
                using (CrawlerDB crawlerDB = new CrawlerDB())
                {
                    contextDB.ConfigureContext(crawlerDB);

                    return
                        crawlerDB.PostTwitters.Include(a => a.Categoria)
                            .AsNoTracking()
                            .Where(e => e.EstadoId == codigo)
                            .GroupBy(c => c.Categoria.Nome)
                            .Select(p => p.Count())
                            .ToList();
                }
            }
            catch (Exception exception)
            {
                throw exception;
            }

            finally
            {
                contextDB = null;
            }
        }