using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json;
using OnlineMoviesCatalog.Data;
using OnlineMoviesCatalog.Models;
using OnlineMoviesCatalog.Service.Providers.Contracts;
using System.IO;
using System.Text;

namespace OnlineMoviesCatalog.Service.Providers
{
    public class InputProvider : IInputProvider
    {
        private readonly MovieCatalogContext context;

        public InputProvider(MovieCatalogContext context)
        {
            this.context = context;
        }

        public void LoadJsons()
        {
            LoadActors();

            LoadGenres();

            LoadUsers();

            LoadMovies();

            //LoadMovieActors();

            //LoadReviews();

            //LoadMovieGenres();
        }

        public void LoadUsers()
        {
            var usersAsJson = File.ReadAllText(@"..\..\..\..\OnlineMoviesCatalog.Data\JsonRaw\users.json");

            var users = JsonConvert.DeserializeObject<User[]>(usersAsJson);

            var sql = new StringBuilder();

            foreach (var user in users)
            {
                sql.AppendLine($@"
                    IF NOT EXISTS (SELECT *
                                    FROM dbo.Users u
                                    WHERE u.Id = {user.Id})
                        BEGIN
                        INSERT INTO dbo.Users
                             (UserName, Password, IsDeleted)
                        VALUES ('{user.UserName}', '{user.Password}', '{user.IsDeleted}')
                        END
                    ELSE
                        BEGIN
                        UPDATE dbo.Users
                        SET UserName = '{user.UserName}', Password = '{user.Password}', IsDeleted = '{user.IsDeleted}'
                        WHERE dbo.Users.Id = {user.Id}
                    END
                    ");
            }

            context.Database.ExecuteSqlCommand(sql.ToString());
        }

        public void LoadMovies()
        {
            var moviesAsJson = File.ReadAllText(@"..\..\..\..\OnlineMoviesCatalog.Data\JsonRaw\movies.json");

            var movies = JsonConvert.DeserializeObject<Movie[]>(moviesAsJson);

            var sql = new StringBuilder();

            foreach (var movie in movies)
            {
                sql.AppendLine($@"
                    IF NOT EXISTS (SELECT *
                                    FROM dbo.Movies m
                                    WHERE m.Id = {movie.Id})
                        BEGIN
                        INSERT INTO dbo.Movies
                             (Title, Trailer, ReleaseDate, Text, UserId, NumberOfVotes, TotalRating, AverageRating)
                        VALUES ('{movie.Title}','{movie.Trailer}','{movie.ReleaseDate}','{movie.Text}','{movie.UserId}','{movie.NumberOfVotes}','{movie.TotalRating}','{movie.AverageRating}')
                        END
                    ELSE
                        BEGIN
                        UPDATE dbo.Movies
                        SET Title = '{movie.Title}', Trailer = '{movie.Trailer}', ReleaseDate = '{movie.ReleaseDate}', Text = '{movie.Text}', UserId = '{movie.UserId}', NumberOfVotes = '{movie.NumberOfVotes}', TotalRating = '{movie.TotalRating}', AverageRating = '{movie.AverageRating}'
                        WHERE dbo.Movies.Id = {movie.Id}
                    END
                    ");
            }

            context.Database.ExecuteSqlCommand(sql.ToString());
        }

        //public void LoadMovies()
        //{
        //    var movieAsJson = File.ReadAllText(@"..\..\..\..\OnlineMoviesCatalog.Data\JsonRaw\movies.json");

        //    var movies = JsonConvert.DeserializeObject<Movie[]>(movieAsJson);

        //    var sql = new StringBuilder();

        //    foreach (var movie in movies)
        //    {
        //        sql.AppendLine($@"

        //            IF NOT EXISTS (SELECT *
        //                            FROM dbo.Movies m
        //                            WHERE a.Id = {movie.Id})
        //                BEGIN
        //                INSERT INTO dbo.Actors
        //                     (Title, Trailer, ReleaseDate, Text, UserId, NumberOfVotes, TotalRating, AverageRating)
        //                VALUES ('{movie.Title}', '{movie.Trailer}', '{movie.Text}' , '{movie.UserId}', '{movie.NumberOfVotes}', '{movie.TotalRating}', '{movie.AverageRating}' )
        //                END
        //            ELSE
        //                BEGIN
        //                UPDATE dbo.Actors
        //                SET Title = '{movie.Title}', Trailer = '{movie.Trailer}', ReleaseDate = '{movie.ReleaseDate}', Text = '{movie.Text}', UserId = '{movie.UserId}', NumberOfVotes = '{movie.NumberOfVotes}', TotalRating = '{movie.TotalRating}', AverageRating = '{movie.AverageRating}'
        //                WHERE dbo.Movies.Id = {movie.Id}
        //            END
        //            ");
        //    }

        //    context.Database.ExecuteSqlCommand(sql.ToString());
        //}


        public void LoadActors()
        {
            var actorAsJson = File.ReadAllText(@"..\..\..\..\OnlineMoviesCatalog.Data\JsonRaw\actors.json");

            var actors = JsonConvert.DeserializeObject<Actor[]>(actorAsJson);

            var sql = new StringBuilder();

            foreach (var actor in actors)
            {
                sql.AppendLine($@"

                    IF NOT EXISTS (SELECT *
                                    FROM dbo.Actors a
                                    WHERE a.Id = {actor.Id})
                        BEGIN
                        INSERT INTO dbo.Actors
                             (FirstName, LastName)
                        VALUES ('{actor.FirstName}', '{actor.LastName}')
                        END
                    ELSE
                        BEGIN
                        UPDATE dbo.Actors
                        SET FirstName = '{actor.FirstName}', LastName = '{actor.LastName}'
                        WHERE dbo.Actors.Id = {actor.Id}
                    END
                    ");
            }

            context.Database.ExecuteSqlCommand(sql.ToString());
        }

        public void LoadGenres()
        {
            var genresAsJson = File.ReadAllText(@"..\..\..\..\OnlineMoviesCatalog.Data\JsonRaw\genres.json");

            var genres = JsonConvert.DeserializeObject<Genre[]>(genresAsJson);

            var sql = new StringBuilder();

            foreach (var genre in genres)
            {
                sql.AppendLine($@"

                    IF NOT EXISTS (SELECT *
                                    FROM dbo.Genres c
                                    WHERE c.GenreId = {genre.GenreId})
                        BEGIN
                        INSERT INTO dbo.Genres
                             (Name)
                        VALUES ('{genre.Name}')
                        END
                    ELSE
                        BEGIN
                        UPDATE dbo.Genres
                        SET Name = '{genre.Name}'
                        WHERE dbo.Genres.GenreId = {genre.GenreId}
                    END
                    ");
            }

            context.Database.ExecuteSqlCommand(sql.ToString());
        }



        public void LoadReviews()
        {
            var reviewsAsJson = File.ReadAllText(@"..\..\..\..\OnlineMoviesCatalog.Data\JsonRaw\reviews.json");

            var reviews = JsonConvert.DeserializeObject<Review[]>(reviewsAsJson);

            var sql = new StringBuilder();

            foreach (var review in reviews)
            {
                sql.AppendLine($@"

                    IF NOT EXISTS (SELECT *
                                    FROM dbo.Reviews r
                                    WHERE r.Id = {review.Id})
                        BEGIN
                        INSERT INTO dbo.Reviews
                             ( Description, Rating,  IsDeleted, CreatedOn, UserId, MovieId )
                        VALUES ( '{review.Description}', '{review.Rating}',{review.IsDeleted}' ,'{review.CreatedOn}', '{review.UserId}','{review.MovieId}')
                        END
                    ELSE
                        BEGIN
                        UPDATE dbo.Reviews
                        SET Description = '{review.Description}', Rating = '{review.Rating}', IsDeleted = '{review.IsDeleted}', CreatedOn = '{review.CreatedOn}', UserId = '{review.UserId}', MovieId = '{review.MovieId}'
                        WHERE dbo.Reviews.Id = {review.Id}
                    END
                    ");
            }

            context.Database.ExecuteSqlCommand(sql.ToString());
        }

        public void LoadMovieGenres()
        {
            var movGenresAsJson = File.ReadAllText(@"..\..\..\..\OnlineMoviesCatalog.Data\JsonRaw\moviesGenres.json");

            var movieGenres = JsonConvert.DeserializeObject<MovieGenres[]>(movGenresAsJson);

            var sql = new StringBuilder();

            foreach (var movieGenre in movieGenres)
            {
                sql.AppendLine($@"

                    IF NOT EXISTS (SELECT *
                                    FROM dbo.MovieGenres mc)
                        BEGIN
                        INSERT INTO dbo.MovieGenres
                             (MovieId, GenreId)
                        VALUES ('{movieGenre.MovieId}', '{movieGenre.GenreId}')
                        END
                    ELSE
                        BEGIN
                        UPDATE dbo.MovieGenres
                        SET MovieId = '{movieGenre.MovieId}', GenreId = '{movieGenre.GenreId}'
                    END
                    ");
            }

            context.Database.ExecuteSqlCommand(sql.ToString());
        }

        public void LoadMovieActors()
        {
            var movieActorsAsString = File.ReadAllText(@"..\..\..\..\OnlineMoviesCatalog.Data\JsonRaw\moviesActors.json");

            var movieActors = JsonConvert.DeserializeObject<MovieActors[]>(movieActorsAsString);

            var sql = new StringBuilder();

            foreach (var movActor in movieActors)
            {
                sql.AppendLine($@"

                    IF NOT EXISTS (SELECT *
                                    FROM dbo.MovieActors ma)
                        BEGIN
                        INSERT INTO dbo.MovieActors
                             (MovieId, ActorId)
                        VALUES ('{movActor.MovieId}', '{movActor.ActorId}')
                        END
                    ELSE
                        BEGIN
                        UPDATE dbo.MovieActors
                        SET MovieId = '{movActor.MovieId}', ActorId = '{movActor.ActorId}'
                    END
                    ");
            }

            context.Database.ExecuteSqlCommand(sql.ToString());
        }
    }
}