using System;
using Microsoft.Data.Sqlite;
using Database.Dto;

namespace Team3
{
    class EditRecords
    {
        public static AuthorDto? EditAuthor(SqliteConnection connection, AuthorDto author)
        {
            using (var command = new SqliteCommand())
            {
                command.Connection = connection;
                try
                {
                    // Check if author exists
                    command.CommandText = "SELECT 1 FROM Authors WHERE ID = @id;";
                    command.Parameters.AddWithValue("@id", author.Id);
                    using (var reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows)
                        {
                            Console.WriteLine($"Author with ID {author.Id} does not exist.");
                            return null;
                        }
                    }

                    // Update author
                    command.CommandText = @"UPDATE Authors 
                                       SET Name = @name, Surname = @surname, DateOfBirth = @dob 
                                       WHERE ID = @id;";
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@name", author.Name);
                    command.Parameters.AddWithValue("@surname", author.Surname);
                    command.Parameters.AddWithValue("@dob", author.DateOfBirth.ToString("yyyy-MM-dd"));
                    command.Parameters.AddWithValue("@id", author.Id);
                    command.ExecuteNonQuery();

                    // Retrieve updated author
                    command.CommandText = "SELECT ID, Name, Surname, DateOfBirth FROM Authors WHERE ID = @id;";
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@id", author.Id);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            var updatedAuthor = new AuthorDto
                            {
                                Id = reader.GetInt32(0),
                                Name = reader.GetString(1),
                                Surname = reader.GetString(2),
                                DateOfBirth = DateTime.Parse(reader.GetString(3))
                            };
                            Console.WriteLine("Author updated successfully.");
                            return updatedAuthor;
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"EditAuthor failed: {ex.Message}");
                }
            }
            return null;
        }

        public static Publisher? EditPublisher(SqliteConnection connection, Publisher publisher)
        {
            using (var command = new SqliteCommand())
            {
                command.Connection = connection;
                try
                {
                    // Check if publisher exists
                    command.CommandText = "SELECT 1 FROM Publisher WHERE ID = @id;";
                    command.Parameters.AddWithValue("@id", publisher.Id);
                    using (var reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows)
                        {
                            Console.WriteLine($"Publisher with ID {publisher.Id} does not exist.");
                            return null;
                        }
                    }

                    // Update publisher
                    command.CommandText = @"UPDATE Publisher 
                                       SET Name = @name, State = @state 
                                       WHERE ID = @id;";
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@name", publisher.Name);
                    command.Parameters.AddWithValue("@state", publisher.State);
                    command.Parameters.AddWithValue("@id", publisher.Id);
                    command.ExecuteNonQuery();

                    // Retrieve updated publisher
                    command.CommandText = "SELECT ID, Name, State FROM Publisher WHERE ID = @id;";
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@id", publisher.Id);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            var updatedPublisher = new Publisher
                            {
                                Id = reader.GetInt32(0),
                                Name = reader.GetString(1),
                                State = reader.GetString(2)
                            };
                            Console.WriteLine("Publisher updated successfully.");
                            return updatedPublisher;
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"EditPublisher failed: {ex.Message}");
                }
            }
            return null;
        }

        public static BookDto? EditBook(SqliteConnection connection, BookDto book)
        {
            using (var command = new SqliteCommand())
            {
                command.Connection = connection;
                try
                {
                    // Check if book exists
                    command.CommandText = "SELECT 1 FROM Books WHERE ID = @id;";
                    command.Parameters.AddWithValue("@id", book.Id);
                    using (var reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows)
                        {
                            Console.WriteLine($"Book with ID {book.Id} does not exist.");
                            return null;
                        }
                    }

                    // Check if author exists
                    command.CommandText = "SELECT 1 FROM Authors WHERE ID = @aid;";
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@aid", book.Author.Id);
                    using (var reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows)
                        {
                            Console.WriteLine($"Author with ID {book.Author.Id} does not exist.");
                            return null;
                        }
                    }

                    // Check if publisher exists
                    command.CommandText = "SELECT 1 FROM Publisher WHERE ID = @pid;";
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@pid", book.Publisher.Id);
                    using (var reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows)
                        {
                            Console.WriteLine($"Publisher with ID {book.Publisher.Id} does not exist.");
                            return null;
                        }
                    }

                    // Update book
                    command.CommandText = @"UPDATE Books 
                                       SET Name = @name, AuthorID = @author, PublisherID = @publisher, 
                                           YearOfRelease = @year, Total = @total 
                                       WHERE ID = @id;";
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@name", book.Name);
                    command.Parameters.AddWithValue("@author", book.Author.Id);
                    command.Parameters.AddWithValue("@publisher", book.Publisher.Id);
                    command.Parameters.AddWithValue("@year", book.YearOfRelease);
                    command.Parameters.AddWithValue("@total", book.Total);
                    command.Parameters.AddWithValue("@id", book.Id);
                    command.ExecuteNonQuery();

                    // Retrieve updated book with joins
                    command.CommandText = @"SELECT b.ID, b.Name, b.YearOfRelease, b.Total,
                                              a.ID, a.Name, a.Surname, a.DateOfBirth,
                                              p.ID, p.Name, p.State
                                       FROM Books b
                                       JOIN Authors a ON b.AuthorID = a.ID
                                       JOIN Publisher p ON b.PublisherID = p.ID
                                       WHERE b.ID = @id;";
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@id", book.Id);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            var updatedBook = new BookDto
                            {
                                Id = reader.GetInt32(0),
                                Name = reader.GetString(1),
                                YearOfRelease = reader.GetInt32(2),
                                Total = reader.GetInt32(3),
                                Author = new AuthorDto
                                {
                                    Id = reader.GetInt32(4),
                                    Name = reader.GetString(5),
                                    Surname = reader.GetString(6),
                                    DateOfBirth = DateTime.Parse(reader.GetString(7))
                                },
                                Publisher = new Publisher
                                {
                                    Id = reader.GetInt32(8),
                                    Name = reader.GetString(9),
                                    State = reader.GetString(10)
                                }
                            };
                            Console.WriteLine("Book updated successfully.");
                            return updatedBook;
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"EditBook failed: {ex.Message}");
                }
            }
            return null;
        }

        public static UserDto? EditUser(SqliteConnection connection, UserDto user)
        {
            using (var command = new SqliteCommand())
            {
                command.Connection = connection;
                try
                {
                    // Check if user exists
                    command.CommandText = "SELECT 1 FROM Users WHERE ID = @id;";
                    command.Parameters.AddWithValue("@id", user.Id);
                    using (var reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows)
                        {
                            Console.WriteLine($"User with ID {user.Id} does not exist.");
                            return null;
                        }
                    }

                    // Update user
                    command.CommandText = @"UPDATE Users 
                                       SET Name = @name, Surname = @surname 
                                       WHERE ID = @id;";
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@name", user.Name);
                    command.Parameters.AddWithValue("@surname", user.Surname);
                    command.Parameters.AddWithValue("@id", user.Id);
                    command.ExecuteNonQuery();

                    // Retrieve updated user
                    command.CommandText = "SELECT ID, Name, Surname FROM Users WHERE ID = @id;";
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@id", user.Id);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            var updatedUser = new UserDto
                            {
                                Id = reader.GetInt32(0),
                                Name = reader.GetString(1),
                                Surname = reader.GetString(2)
                            };
                            Console.WriteLine("User updated successfully.");
                            return updatedUser;
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"EditUser failed: {ex.Message}");
                }
            }
            return null;
        }
    }

}
