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

namespace Team2
{
    public static class Team2
{
    public static AuthorDto AddAuthor(SqliteConnection connection, string name, string surname, DateTime dateOfBirth)
    {
        try
        {
            var dob = dateOfBirth.ToString("yyyy-MM-dd");
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "INSERT OR IGNORE INTO Authors (Name, Surname, DateOfBirth) VALUES (@name, @surname, @dob);";
                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@surname", surname);
                cmd.Parameters.AddWithValue("@dob", dob);
                cmd.ExecuteNonQuery();
            }

            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT ID, Name, Surname, DateOfBirth FROM Authors WHERE Name = @name AND Surname = @surname AND DateOfBirth = @dob LIMIT 1;";
                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@surname", surname);
                cmd.Parameters.AddWithValue("@dob", dob);

                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read()) return null;
                    var a = new AuthorDto
                    {
                        Id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
                        Name = reader.IsDBNull(1) ? "" : reader.GetString(1),
                        Surname = reader.IsDBNull(2) ? "" : reader.GetString(2),
                        DateOfBirth = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3)
                    };
                    return a;
                }
            }
        }
        catch
        {
            return null;
        }
    }

    public static Publisher AddPublisher(SqliteConnection connection, string name, string state)
    {
        try
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "INSERT OR IGNORE INTO Publisher (Name, State) VALUES (@name, @state);";
                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@state", state);
                cmd.ExecuteNonQuery();
            }

            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT ID, Name, State FROM Publisher WHERE Name = @name AND State = @state LIMIT 1;";
                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@state", state);

                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read()) return null;
                    return new Publisher
                    {
                        Id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
                        Name = reader.IsDBNull(1) ? "" : reader.GetString(1),
                        State = reader.IsDBNull(2) ? "" : reader.GetString(2)
                    };
                }
            }
        }
        catch
        {
            return null;
        }
    }

    public static BookDto AddBook(SqliteConnection connection, string name, int authorId, int publisherId, int year, int total, int available)
    {
        try
        {
            // check author
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT 1 FROM Authors WHERE ID = @Aid;";
                cmd.Parameters.AddWithValue("@Aid", authorId);
                using (var r = cmd.ExecuteReader())
                {
                    if (!r.HasRows) return null;
                }
            }

            // check publisher
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT 1 FROM Publisher WHERE ID = @Pid;";
                cmd.Parameters.AddWithValue("@Pid", publisherId);
                using (var r = cmd.ExecuteReader())
                {
                    if (!r.HasRows) return null;
                }
            }

            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = @"INSERT OR IGNORE INTO Books (Name, AuthorID, PublisherID, YearOfRelease, Total, Available) 
                                    VALUES (@name, @author, @publisher, @year, @total, @available);";
                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@author", authorId);
                cmd.Parameters.AddWithValue("@publisher", publisherId);
                cmd.Parameters.AddWithValue("@year", year);
                cmd.Parameters.AddWithValue("@total", total);
                cmd.Parameters.AddWithValue("@available", available);
                cmd.ExecuteNonQuery();
            }

            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = @"SELECT ID, Name, AuthorID, PublisherID, YearOfRelease, Total, Available 
                                    FROM Books 
                                    WHERE Name = @name AND AuthorID = @author AND PublisherID = @publisher AND YearOfRelease = @year
                                    LIMIT 1;";
                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@author", authorId);
                cmd.Parameters.AddWithValue("@publisher", publisherId);
                cmd.Parameters.AddWithValue("@year", year);

                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read()) return null;
                    return new BookDto
                    {
                        Id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
                        Name = reader.IsDBNull(1) ? "" : reader.GetString(1),
                        YearOfRelease = reader.IsDBNull(4) ? 0 : reader.GetInt32(4),
                        Total = reader.IsDBNull(5) ? 0 : reader.GetInt32(5),
                        Author = new AuthorDto { Id = reader.IsDBNull(2) ? 0 : reader.GetInt32(2) },
                        Publisher = new Publisher { Id = reader.IsDBNull(3) ? 0 : reader.GetInt32(3) }
                    };
                }
            }
        }
        catch
        {
            return null;
        }
    }

    public static UserDto AddUser(SqliteConnection connection, string name, string surname)
    {
        try
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "INSERT OR IGNORE INTO Users (Name, Surname) VALUES (@name, @surname);";
                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@surname", surname);
                cmd.ExecuteNonQuery();
            }

            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT ID, Name, Surname FROM Users WHERE Name = @name AND Surname = @surname LIMIT 1;";
                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@surname", surname);

                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read()) return null;
                    return new UserDto
                    {
                        Id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
                        Name = reader.IsDBNull(1) ? "" : reader.GetString(1),
                        Surname = reader.IsDBNull(2) ? "" : reader.GetString(2)
                    };
                }
            }
        }
        catch
        {
            return null;
        }
    }

    public static Borrow AddBorrowedBookRecord(SqliteConnection connection, int bookId, int userId, DateTime borrowedDate, int days = 30)
    {
        try
        {
            var borrowed = borrowedDate.ToString("yyyy-MM-dd");
            var due = borrowedDate.AddDays(days).ToString("yyyy-MM-dd");

            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT 1 FROM Users WHERE ID = @Uid;";
                cmd.Parameters.AddWithValue("@Uid", userId);
                using (var r = cmd.ExecuteReader())
                {
                    if (!r.HasRows) return null;
                }
            }

            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT 1 FROM Books WHERE ID = @Bid;";
                cmd.Parameters.AddWithValue("@Bid", bookId);
                using (var r = cmd.ExecuteReader())
                {
                    if (!r.HasRows) return null;
                }
            }

            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = @"INSERT OR IGNORE INTO Borrows (DateOfBorrow, ReturnDue, UserID, BookID) 
                                    VALUES (@borrowed, @due, @user, @book);";
                cmd.Parameters.AddWithValue("@borrowed", borrowed);
                cmd.Parameters.AddWithValue("@due", due);
                cmd.Parameters.AddWithValue("@user", userId);
                cmd.Parameters.AddWithValue("@book", bookId);
                cmd.ExecuteNonQuery();
            }

            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = @"SELECT ID, DateOfBorrow, ReturnDue, DateOfReturn, UserID, BookID 
                                    FROM Borrows 
                                    WHERE DateOfBorrow = @borrowed AND ReturnDue = @due AND UserID = @user AND BookID = @book
                                    LIMIT 1;";
                cmd.Parameters.AddWithValue("@borrowed", borrowed);
                cmd.Parameters.AddWithValue("@due", due);
                cmd.Parameters.AddWithValue("@user", userId);
                cmd.Parameters.AddWithValue("@book", bookId);

                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read()) return null;
                    var b = new Borrow
                    {
                        Id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
                        DateOfBorrow = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1),
                        ReturnDue = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2),
                        DateOfReturn = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3),
                        User = new UserDto { Id = reader.IsDBNull(4) ? 0 : reader.GetInt32(4) },
                        Book = new BookDto { Id = reader.IsDBNull(5) ? 0 : reader.GetInt32(5) }
                    };
                    return b;
                }
            }
        }
        catch
        {
            return null;
        }
    }
}

}
